Mir Sayeed Hassan – Oracle Blog

Oracle 19c Tablespace Recovery for SYSTEM and Non-SYSTEM Datafiles

Method 1  

The database is running in ARCHIVELOG mode, and full RMAN backups are performed every night. In the morning, it was identified that the disk containing the SYSTEM datafile had failed. In this situation, there are two possible methods available to recover the SYSTEM tablespace and restore database operations.

• Tablespace Level

• Datafile Level

Taking the Full Backup of the Target.

RMAN> rman catalog rman/rman target sys/hassan@testdb

RMAN> RUN {
ALLOCATE CHANNEL ch1 TYPE DISK
FORMAT=‘/u01/backup/rman_bkp/DF_%t_%s_%D’;
BACKUP DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL ch1;
}

The file system or disk containing the SYSTEM datafile has failed, resulting in the loss of the SYSTEM datafile required to start the database. Unlike other tablespaces, the SYSTEM tablespace cannot be taken offline, so recovery must be performed while the database is in mount mode before it can be opened successfully.

Datafile Level Recovery 

While starting up the target Database we will get following error. 

SQL> startup
ORACLE instance started. 

Total System Global Area 135338868 bytes
Fixed Size
Variable Size
Database Buffers
Redo Buffers
Database mounted.

ORA-01157: cannot identify/lock data file 1 – see DBWR trace file 

ORA-01110: data file 1: ‘/u01/app/oracle/oradata/testdb/system01.dbf’ 

Recovery using RMAN 

Step 1 Connect to RMAN

RMAN> rman catalog rman/rman target sys/hassan@testdb

Step 2 Run the following script 

RMAN> run { 

ALLOCATE CHANNEL ch1 TYPE DISK; 
restore datafile 1; 
recover datafile 1; 
sql 'alter database open'; 
} 

The target database is in OPEN mode 

Step 3 Test the database is open or not 

SQL> select open_mode from v$database; 

OPEN_MODE 
---------- 
READ WRITE 

Tablespace Level Recovery 

Step 1: Connect to RMAN

RMAN> rman catalog rman/rman target sys/hassan@testdb 

Step 2 Run the following script 

RMAN> run { 
ALLOCATE CHANNEL ch1 TYPE DISK; 
restore tablespace system; 
recover tablespace system; 
sql 'alter database open'; 
} 

The target database is in OPEN mode 

Step 3: Test the database is open or not 

SQL> select open_mode from v$database; 

OPEN_MODE 
---------- 
READ WRITE 

Method 2 

How to recover Non-System Tablespace 

The database is running in ARCHIVELOG mode with daily full RMAN backups and incremental backups. In the morning, it was identified that the disk containing the user datafile had failed, resulting in datafile loss and requiring recovery from RMAN backups.

Proceedings: 

1. Create a table before full backup. 

2. Take the full backup (level 0) 

3. Create another table 

4. Take the level 1 backup 

5. Create another table 

6. Shutdown the database 

7. Remove the datafile of Users tablespace at the OS level 

8. Recover the datafile using RMAN

Idea behind the Recovery: 

We can restore the datafile from level 0, add the changes from level 1, then recover by applying online redo logs and archived redo logs. 

Before taking level 0 backup 

CREATE TABLE MIR.T1( NO NUMBER(4)) TABLESPACE USERS; 

SQL> insert into mir.t1 values(1); 3 times 

select * from mir.t1 

t2
--- 
1 
1
1 
Commit; 

Taking the Level 0 backup 

RMAN> RUN {
ALLOCATE CHANNEL ch1 TYPE DISK 
format=‘/u01/backup/rman_bkp/full_DF_%t_%s_%D’; 
BACKUP 
incremental level 0 database; 
RELEASE CHANNEL ch1; 
}

Before taking the Level 1 Backup 

SQL> CREATE TABLE MIR.T2( NO NUMBER(4)) TABLESPACE USERS;

SQL> insert into MIR.t2 values(2); ---4 times 

SQL> select * from mir.t2 

t2 
—
2
2
2
2 
Commit; 

Taking the Level 1 backup 

RMAN> RUN 
{ 
ALLOCATE CHANNEL ch1 TYPE DISK 
format='/u01/backup/rman_bkp/lev1_DF_%t_%s_%D'; 
BACKUP 
incremental level 1 database;
RELEASE CHANNEL ch1; 
}

After taking the Incremental Backup 

SQL> CREATE TABLE MIR.T3 (NO NUMBER(4))TABLESPACE USERS; insert into t3 values(3);

 –5 times 

SQL>select * from MIR.t3 ; 

t3 
—
3
3
3
3
3 

Commit; 

Removed the user01.dbf datafile from the OS level 

Error 

SQL> startup
ORACLE instance started. 

Total System Global Area 135338868 bytes 

Fixed Size
Variable Size
Database Buffers
Redo Buffers
Database mounted.
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file ORA-01110: data file 9: 'D:\ORACLE\ORADATA\TEST\USERS01.DBF' 

Recovery 

Step 1: Connect to RMAN
Step 2: Run the following script 

RMAN> run { 
ALLOCATE CHANNEL ch1 TYPE DISK; 
restore datafile 9; 
recover datafile 9; 
sql 'alter database open'; 
sql 'alter tablespace users online'; 

}

Verify

SQL> select * from MIR.t1;

MIR.t1
—
1
1
1

SQL>select * from MIR.t3; 

MIR.t2
—
2
2
2
2

SQL>select * from MIR.t2;

MIR.t3
—
3
3
3
3
3

=====Hence tested and verified in our test env=====