Recovery Scenarios through RMAN

Complete Recovery when SYSTEM tablespace is missing.

Problem Generation

Delete system tablespace datafile from operating system when database is up and running.

[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk3/ocmdb/system01.dbf

Solution

i. [oracle@ocm ocmdb]$ sqlplus / as sysdba
ii. SQL> SHUTDOWN abort;
iii. SQL> STARTUP mount;
iv. SQL> SELECT file#, name FROM v$datafile;
v. Using rman connect to target database and catalog database or directly to target database.
[oracle@oem scripts]$ rman target sys/oracle@ocmdb catalog rman/rman@oemdb
vi. RMAN> run
{
restore datafile 1;
recover datafile 1;
sql ‘ alter database open ‘;
}

• Complete Recovery when NON-SYSTEM tablespace is missing and database is open and restoring datafile to different location.

Problem Generation

i. Delete USERS tablespace datafile from operating system
[oracle@ocm ocmdb]$ rm -i users01.dbf
ii. Connect as user sh/sh and create a table in users tablespace
SQL> CREATE TABLE sales_test
TABLESPACE users
AS
SELECT *
FROM sales
WHERE ROWNUM < 10;

ERROR at line 3:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/u01/app/oracle/oradata/disk3/ocmdb/users01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

Solution

i. As DBA user make datafile users01.dbf off line
SQL> SELECT file#, name FROM v$datafile;
SQL> ALTER DATABASE DATAFILE 4 OFFLINE;
ii. Restoring and recovering datafile 4 at new location
RMAN> run
{
set newname for datafile 4 to ‘/u01/app/oracle/oradata/disk5/ocmdb/users01.dbf’;
restore datafile 4;
switch datafile 4;
recover datafile 4;
sql ‘ alter database datafile 4 online’;
}

iii. Connect as user sh/sh and create a table in users tablespace

SQL> CREATE TABLE sales_test
TABLESPACE users
AS
SELECT *
FROM sales
WHERE ROWNUM < 10;

Table created.

• Complete Recovery when NON-SYSTEM tablespace is missing and database is closed and restoring datafile to different location.

Problem Generation

i. Shutdown the database.
SQL> SHUTDOWN immediate;
ii. Delete USERS tablespace datafile from operating system
[oracle@ocm ocmdb]$ rm -i users01.dbf
iii. Start the database
SQL> STARTUP;

ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 213909652 bytes
Database Buffers 318767104 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/u01/app/oracle/oradata/disk5/ocmdb/users01.dbf’

Solution

i. Make datafile 4 offline
SQL> ALTER DATABASE DATAFILE 4 OFFLINE;
ii. Open the database with datafile 4 oflfine
SQL> ALTER DATABASE OPEN;
iii. Using RMAN again connect to target database
[oracle@oem scripts]$ rman target sys/oracle@ocmdb catalog rman/rman@oemdb
iv. Restoring and recovering datafile 4 at new location
RMAN> run
{
set newname for datafile 4 to ‘/u01/app/oracle/oradata/disk3/ocmdb/users01.dbf’;
restore datafile 4;
switch datafile 4;
recover datafile 4;
sql ‘ alter database datafile 4 online’;
}

• Recovery of datafile which has no backups

Problem Generation

i. Create Oracle Managed File tablespace
SQL> CREATE TABLESPACE reco_test;

ii. Create table sh_sales on tablespace reco_test.

SQL> CREATE TABLE sh_sales
TABLESPACE reco_test
AS
SELECT *
FROM sh.sales
WHERE ROWNUM < 10;

iii. Delete RECO_TEST tablespace datafile from operating system.
[oracle@ocm ~]$ rm –i /u01/app/oracle/oradata/disk5/OCMDB/datafile/o1_mf_reco_tes_6n8dnc7z_.dbf

iv. Select from table sh_sales.
SQL> SELECT COUNT (*) FROM sh_sales;

*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5:
‘/u01/app/oracle/oradata/disk5/OCMDB/datafile/o1_mf_reco_tes_6n8dnc7z_.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

Solution

i. Recover Datafile 5.
RMAN> run {
sql ‘ alter database datafile 5 offline ‘;
restore datafile 5;
recover datafile 5;
sql ‘ alter database datafile 5 online ‘;
}

ii. Select from table sh_sales.
SQL> SELECT COUNT (*) FROM sh_sales;

• Recover of missing controlfile ( Copy other controlfile and mount database).

Problem Generation

i. Shutdown the Database
SQL> SHUTDOWN immediate;
ii. Delete Control file control01.ctl from operating system
[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk1/ocmdb/control01.ctl
iii. Startup Database.
SQL> STARTUP;

ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 218103956 bytes
Database Buffers 314572800 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

Solution

i. Check the control file location
SQL> SHOW PARAMETER control

NAME TYPE VALUE
———————————— ———– ——————————
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/disk1/
ocmdb/control01.ctl, /u01/app/
oracle/admin/ocmdb/control/con
trol02.ctl
ii. Copy control01.ctl from control02.ctl
[oracle@ocm ocmdb]$ cp /u01/app/oracle/admin/ocmdb/control/control02.ctl /u01/app/oracle/oradata/disk1/ocmdb/control01.ctl
iii. Mount the Database and Open it.
SQL> ALTER DATABASE MOUNT;

SQL> ALTER DATABASE OPEN;

• Incomplete recovery is required when archived log, redo log is missing, then recovery can only be made until the previous sequence, or when an important object was dropped and recovery needs to be made until before the object was dropped. Use until sequence or until time or until cancel for incomplete recovery.

Problem Generation

i. Shutdown Database.
SQL> SHUTDOWN immediate;
ii. Delete redo log files from operating system
[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk4/ocmdb/redo01b.log[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk2/ocmdb/redo01a.log

If redo log file is lost when database is up , always switch logfile few times until sql statement hangs to archive the available redo logfiles, which are still not archived.

iii. Startup the Database.
SQL> STARTUP;

ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 218103956 bytes
Database Buffers 314572800 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
‘/u01/app/oracle/oradata/disk2/ocmdb/redo01a.log’
ORA-00312: online log 1 thread 1:
‘/u01/app/oracle/oradata/disk4/ocmdb/redo01b.log’

Solution

i. Complete database restore is required ,execute sql below to get latest sequence# archived ,add 1 to last sequence and then use in RMAN to recover database.
SQL> SELECT thread#,
resetlogs_change#,
archived,
sequence#,
TO_CHAR (completion_time, ‘YYYY-MM-DD:HH24:MI:SS’) completion_time
FROM v$archived_log
WHERE archived = ‘YES’
AND completion_time = (SELECT MAX (completion_time)
FROM v$archived_log
WHERE archived = ‘YES’);

THREAD# RESETLOGS_CHANGE# ARC SEQUENCE# COMPLETION_TIME
———- —————– — ———- ——————-
1 1 YES 58 2011-01-29:21:02:46

ii. Connect to target database using RMAN catalog
[oracle@oem scripts]$ rman target sys/oracle@ocmdb catalog rman/rman@oemdb
iii. RMAN command to recover database.
RMAN> run
{
restore database;
recover database until sequence 59 thread 1;
sql ‘ alter database open resetlogs ‘;
}
iv. After database is open switch logfile few times
SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> ALTER SYSTEM SWITCH LOGFILE;

• Recovery if all controlfiles are missing

Problem Generation

i. Shutdown Database.
SQL> SHUTDOWN immediate;
ii. Delete all Control files from operating system
[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk1/ocmdb/control01.ctl

[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/admin/ocmdb/control/control02.ctl
iii. Connect as sysdba and startup Database.
[oracle@ocm ocmdb]$ sqlplus / as sysdba

SQL> STARTUP;

ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 222298260 bytes
Database Buffers 310378496 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

Solution

i. Using RMAN restore contolfile from Autobackup and Recover Database
RMAN> run
{
restore controlfile from autobackup;
sql ‘ alter database mount’;
recover database;
sql ‘ alter database open resetlogs’;
}
ii. List Database Incarnation
RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 2 OCMDB 4083722939 PARENT 1 15-JAN-11
1 1415 OCMDB 4083722939 PARENT 503961 29-JAN-11
1 1867 OCMDB 4083722939 CURRENT 519078 29-JAN-11
iii. After database is open switch logfile few times
SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> ALTER SYSTEM SWITCH LOGFILE;
v. Always backup controlfile after open resetlogs even if CONTROLFILE AUTOBACKUP ON. Let us NOT BACKUP controlfile and see what happens in next scenario.
RMAN> backup current controlfile; — For now do not execute this command

• Recover if all controlfiles,datafiles,spfile,redologs are lost

Problem Generation

i. Shutdown Database.

SQL> SHUTDOWN immediate;
ii. Delete all the datafiles ,controlfiles ,redo logfiles ,spfile ,initocmdb.ora files from Operating System
iii. Startup Database
SQL> STARTUP;

ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/10.2.0/db_1/dbs/initocmdb.ora’

Make sure you have lost all the files not just spfile, if only spfile is lost , then too you will get above error you need to create only spfile.

Solution

i. Using RMAN connect to target database using catalog
[oracle@oem scripts]$ rman target sys/oracle@ocmdb catalog rman/rman@oemdb

Recovery Manager: Release 10.2.0.1.0 – Production on Sun Jan 30 06:02:44 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database (not started)
connected to recovery catalog database
ii. Set Database DBID
RMAN> set dbid — dbid 4083722939
iii. Startup Database in Nomount State
RMAN> startup nomount;
iv. Restore spfile from Autobackup
RMAN> restore spfile from autobackup;

v. Again start the Database in Nomount State
RMAN> startup force nomount;
vi. Restore controlfile from Autobackup
RMAN> restore controlfile from autobackup;
vii. Mount Database
RMAN> alter database mount;
viii. Open a terminal in OCMDB machine and check archive log completion time
[oracle@ocm ~]$ sqlplus / as sysdba

SQL> SELECT thread#,
resetlogs_change#,
archived,
sequence#,
TO_CHAR (completion_time, ‘YYYY-MM-DD:HH24:MI:SS’)
completion_time
FROM v$archived_log
WHERE archived = ‘YES’
AND completion_time = (SELECT MAX (completion_time)
FROM v$archived_log
WHERE archived = ‘YES’);

THREAD# RESETLOGS_CHANGE# ARC SEQUENCE# COMPLETION_TIME
———- —————– — ———- ——————-
1 519078 YES 1 2011-01-29:23:18:17

ix. By executing the command below we may not be able to recover database because control file is from older incarnation. This is because we have not taken backup of controlfile after reset logs and immediately crashed the database after recovery in previous scenario.
RMAN> run
{
restore database;
recover database until sequence 1 thread 1;
sql ‘ alter database open resetlogs’;
}
x. Now, we have to reset database to older incarnation and recover the database.
xi. List Database Incarnation.
RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 2 OCMDB 4083722939 PARENT 1 15-JAN-11
1 1415 OCMDB 4083722939 PARENT 503961 29-JAN-11
1 1867 OCMDB 4083722939 CURRENT 519078 29-JAN-11

xii. Open a terminal in OCMDB machine and check archive log completion time.
SQL> SELECT thread#,
resetlogs_change#,
archived,
sequence#,
TO_CHAR (completion_time, ‘YYYY-MM-DD:HH24:MI:SS’)
completion_time
FROM v$archived_log
WHERE archived = ‘YES’
ORDER BY completion_time;

THREAD# RESETLOGS_CHANGE# ARC SEQUENCE# COMPLETION_TIME
———- —————– — ———- ——————-
1 503961 YES 4 2011-01-29:22:18:18

Add 1 to the latest sequence of last incarnation i.e. 4+1 =5

xiii. Reset the Database to old incarnation and recover.
RMAN> reset DATABASE TO incarnation 1415;

RMAN> run
{
restore database;
recover database until sequence 5 thread 1;
sql ‘ alter database open resetlogs’;
}

• Recovery if Temporary tablespace is lost

Recreate the temporary tablespace.