FULL DATABASE RESTORE AND RECOVER USING RMAN

SCENARIO

Recovering the Database when database is fully crashed and no files are available using RMAN Backup files

1. Created a database called 'TESTDB' using DBCA.

2. Converted the database from NoArchivelog to Archivelog mode.

3. Configured RMAN.

4. Taken Incremental level 0 backup using RMAN (Database plus Archivelog).

5. Shutdown the Database.

6. Deleted the controlfiles, spfile, pfile, redolog files and datafiles.

7. Deleted the sid also.

8. Recovered the full database using RMAN backup.

ENVIRONMENT

DATABASE - ORACLE 11.2.0.3.0 64Bit

OS - Windows 2008 R2 64Bit

Database Name - testdb

*********************************************************************************

STEPS TO FOLLOW

1. Create New SID for TESTDB.

C:\>oradim -new -sid testdb -syspwd oracle -startmode auto

Instance created.

2. start the instance using a dummy pfile. The pfile may contain only the db_name and db_unique_name parameters.

C:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 4 21:09:12 2012

SQL> conn sys/oracle@testdb as sysdba

Connected to an idle instance.

SQL> startup nomount pfile='f:\inittestdb.ora';

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2262048 bytes

Variable Size 616565728 bytes

Database Buffers 444596224 bytes

Redo Buffers 5513216 bytes

SQL>

3. Connect to RMAN.

C:\>rman

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jun 4 21:14:41 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

4. Restore the SPFILE from autobackup.

RMAN> set dbid=2568236785

executing command: SET DBID

RMAN> connect target sys/oracle@testdb

connected to target database: (not mounted)

RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'f:\testdb\controlfile_bkup\CTL_%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Restoration of SPFILE from

RMAN> restore spfile from autobackup;

Starting restore at 04-JUN-12
using channel ORA_DISK_1

recovery area destination: d:\oracle\fast_recovery_area
database name (or database unique name) used for search: TESTDB
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120604
channel ORA_DISK_1: AUTOBACKUP found: f:\testdb\controlfile_bkup\CTL_c-2568236785-20120604-00
channel ORA_DISK_1: restoring spfile from AUTOBACKUP f:\testdb\controlfile_bkup\CTL_c-2568236785-20120604-00
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 04-JUN-12

RMAN>

5. Restore the CONTROLFILE from autobackup.

RMAN> set dbid=2568236785

executing command: SET DBID

RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'f:\testdb\controlfile_bkup\CTL_%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

Starting restore at 04-JUN-12
using channel ORA_DISK_1

recovery area destination: d:\oracle\fast_recovery_area
database name (or database unique name) used for search: TESTDB
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120604
channel ORA_DISK_1: AUTOBACKUP found: f:\testdb\controlfile_bkup\CTL_c-2568236785-20120604-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP f:\testdb\controlfile_bkup\CTL_c-2568236785-20120604-00
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=F:\TESTDB\CONTROL01.CTL
output file name=D:\ORACLE\FAST_RECOVERY_AREA\TESTDB\CONTROL02.CTL
Finished restore at 04-JUN-12

RMAN>

6. Mount the Database TESTDB.

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

7. Restore the Database.

RMAN> restore database;

Starting restore at 04-JUN-12
Starting implicit crosscheck backup at 04-JUN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=11 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 04-JUN-12

Starting implicit crosscheck copy at 04-JUN-12
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished implicit crosscheck copy at 04-JUN-12

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to F:\TESTDB\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to F:\TESTDB\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to F:\TESTDB\TBSRMAN01.DBF
channel ORA_DISK_1: reading from backup piece F:\TESTDB\DATAFILE_BKUP\DBF_0INCNGIR_1_1
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to F:\TESTDB\SYSTEM01.DBF
channel ORA_DISK_2: restoring datafile 00004 to F:\TESTDB\USERS01.DBF
channel ORA_DISK_2: reading from backup piece F:\TESTDB\DATAFILE_BKUP\DBF_0HNCNGIR_1_1
channel ORA_DISK_1: piece handle=F:\TESTDB\DATAFILE_BKUP\DBF_0INCNGIR_1_1 tag=TAG20120604T205137
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
channel ORA_DISK_2: piece handle=F:\TESTDB\DATAFILE_BKUP\DBF_0HNCNGIR_1_1 tag=TAG20120604T205137
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:01:36
Finished restore at 04-JUN-12

RMAN>

8. After restoring checked the status of the DATABASE.

role from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
TESTDB testdb MOUNTED PRIMARY

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination f:\testdb\archives
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12

SQL>

9. Recover the DATABASE.

RMAN> recover database;

Starting recover at 04-JUN-12
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

archived log for thread 1 with sequence 11 is already on disk as file F:\TESTDB\ARCHIVES\ARC0000000011_0784908660.0001
archived log file name=F:\TESTDB\ARCHIVES\ARC0000000011_0784908660.0001 thread=1 sequence=11
unable to find archived log
archived log thread=1 sequence=12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/04/2012 22:15:20
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 12 and starting SCN of 1076477

Note: From the above Error, use the Last SCN specified "1076477"

RMAN> recover database until SCN 1076477;

Starting recover at 04-JUN-12
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 04-JUN-12

RMAN>

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/04/2012 22:15:48
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Note: Open the Database with RESETLOGS option.

RMAN> alter database open resetlogs;

database opened

RMAN>

10. After Recovering the DATABASE check the status.

SQL> select name,db_unique_name,open_mode,database_role from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
TESTDB testdb READ WRITE PRIMARY

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination f:\testdb\archives
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL>

SQL> desc scott.t1
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL>

NOTE:

Before Deleting the DATABASE i had created a table T1 using scott schema.

From the above status we can clearly see that the table T1 exists in scott schema.

Oracle Knowledge Sharing Blog - Veera (Oracle Certified Professional)

Pages

Tuesday, December 31, 2013