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.

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.