Skip to main content
RMAN

FULL DATABASE RESTORE AND RECOVER USING RMAN

By October 26, 2014September 12th, 2016No Comments

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

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

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.



Leave a Reply