Recovery scenarios (using RMAN & user-managed backup)

This guide will prepare you to run a simple RMAN shell script to perform a hot backup of the full database. In addition, you will learn few useful commands on how to list the RMAN binary backup files and on how to crosscheck if the backed up files are physically present on disk. Loss of Controlfile, Redo & Datafiles Loss of a Non-System datafile Loss of System datafile Point In Time Recovery (PITR) using RMAN ('UNTIL TIME') Point In Time Recovery (PITR) using user-managed hot backup ('UNTIL TIME') Point In Time Recovery (PITR) using RMAN ('UNTIL SCN') Point In Time Recovery (PITR) using user-managed hot backup ('UNTIL CHANGE') Point In Time Recovery (PITR) using RMAN ('UNTIL SEQUENCE') Point In Time Recovery (PITR) using user-managed hot backup ('UNTIL CANCEL') Loss of Undo datafile Loss of Online Redo log file Loss of one Controlfile when Controlfiles are multiplexed Loss of all Controlfiles Recreating Controlfile from trace   1. Loss of Controlfile, Redo & Datafiles   First, let's take a RMAN hotbackup and user-managed hotbackup of the database before creating a scenario.     i) Let's do RMAN backup using the simple script as shown below:   [ ----------------------------------< code - expandable on mouseover >----------------------------------- ] #!/bin/ksh export ORACLE_HOME=/oracle/product/10.2.0/db_1 export ORACLE_SID=dbuat01 /oracle/product/10.2.0/db_1/bin/rman nocatalog <<EOF connect target / run { configure backup optimization on; configure controlfile autobackup on; configure controlfile autobackup format for device type disk to '/u02/oradata/backup/dbuat01/rman/dbuat01_control_%F'; delete noprompt obsolete redundancy 1; allocate channel bkp1 device type disk; allocate channel bkp2 device type disk; change archivelog all crosscheck; backup as compressed backupset format '/u02/oradata/backup/dbuat01/rman/%d_rman_%T_%t_%u_%s_%p' database plus archivelog; backup current controlfile; sql "alter database backup controlfile to trace as ''/u02/oradata/backup/dbuat01/rman/dbuat01_controlfile_trace.txt'' reuse"; release channel bkp1; release channel bkp2; delete noprompt obsolete redundancy 1; } exit EOF cp -f $ORACLE_HOME/dbs/initdbuat01.ora /u02/oradata/backup/dbuat01/rman cp -f $ORACLE_HOME/dbs/spfiledbuat01.ora /u02/oradata/backup/dbuat01/rman cp -f $ORACLE_HOME/dbs/orapwdbuat01 /u02/oradata/backup/dbuat01/rman cp -f $ORACLE_HOME/network/admin/listener.ora /u02/oradata/backup/dbuat01/rman cp -f $ORACLE_HOME/network/admin/tnsnames.ora /u02/oradata/backup/dbuat01/rman   ii) Let's do a user-managed hotbackup as shown below:   $ export ORACLE_SID=dbuat01 $ sqlplus / as sysdba SQL> alter database begin backup; $ cp /u01/oradata/dbuat01/data/*dbf /u02/oradata/backup/dbuat01/hotbkp/ $ cp /u01/oradata/dbuat01/data/*ctl /u02/oradata/backup/dbuat01/hotbkp/ $ sqlplus / as sysdba SQL> alter database end backup; SQL> alter system archive log current;   iii) In order to create a scenario, we will manually delete all controlfiles, redologs and datafiles when the database is up and running.   $ rm -f /u01/oradata/dbuat01/data/*dbf $ rm -f /u01/oradata/dbuat01/control/*ctl $ rm -f /u01/oradata/dbuat01/redo/*log   iv) Now, let's try connecting to the database. You must see something as shown below when you query V$DATABASE.   $ sqlplus / as sysdba SQL> select name from v$database; select name from v$database * ERROR at line 1: ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/oradata/dbuat01/control/control01.ctl' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> shutdown abort   v) Now, let's try connecting to the database. You must see something as shown below when you query V$DATABASE.   $ rman target / [ ----------------------------------< code - expandable on mouseover >----------------------------------- ] RMAN> startup nomount; RMAN> restore controlfile from '/u02/oradata/backup/dbuat01/rman/dbuat01_control_c-2691765368-20120709-00'; Starting restore at 09-JUL-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=761 devtype=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 output filename=/u01/oradata/dbuat01/control/control01.ctl output filename=/u01/oradata/dbuat01/control/control02.ctl output filename=/u01/oradata/dbuat01/control/control03.ctl Finished restore at 09-JUL-12 RMAN> alter database mount; database mounted released channel: ORA_DISK_1 RMAN> restore database; Starting restore at 09-JUL-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=761 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00003 to /u01/oradata/dbuat01/data/sysaux1.dbf channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120709_788201636_04nfm054_4_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120709_788201636_04nfm054_4_1 tag=TAG20120709T165356 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/oradata/dbuat01/data/system1.dbf restoring datafile 00002 to /u01/oradata/dbuat01/data/undotbs1.dbf channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120709_788201636_03nfm054_3_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120709_788201636_03nfm054_3_1 tag=TAG20120709T165356 channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 Finished restore at 09-JUL-12 RMAN> recover database until logseq 15; /* available archive log sequences are between 11 and 14. so the next subsequent number (15) must be used. */ Starting recover at 09-JUL-12 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 11 is already on disk as file /u01/oradata/dbuat01/archive/dbuat01_1_11_788199864.arc archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_11_788199864.arc thread=1 sequence=11 archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_12_788199864.arc thread=1 sequence=12 archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_13_788199864.arc thread=1 sequence=13 archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_14_788199864.arc thread=1 sequence=14 media recovery complete, elapsed time: 00:00:03 Finished recover at 09-JUL-12 RMAN> alter database open resetlogs; Database opened.   vi) Alternatively, the recovery can be performed using user-managed hotbackup:   $ cd /u01/oradata/dbuat01/control/; cp /u02/oradata/backup/dbuat01/hotbkp/*ctl . $ cd /u01/oradata/dbuat01/data/; cp /u02/oradata/backup/dbuat01/hotbkp/*dbf . $ sqlplus / as sysdba SQL> startup mount; SQL> alter database recover automatic using backup controlfile until cancel; alter database recover automatic using backup controlfile until cancel * ERROR at line 1: ORA-00279: change 152214 generated at 07/09/2012 18:05:41 needed for thread 1 ORA-00289: suggestion : /u01/oradata/dbuat01/archive/dbuat01_1_7_788205231.arc ORA-00280: change 152214 for thread 1 is in sequence #7 ORA-00278: log file '/u01/oradata/dbuat01/archive/dbuat01_1_7_788205231.arc' no longer needed for this recovery ORA-00308: cannot open archived log '/u01/oradata/dbuat01/archive/dbuat01_1_7_788205231.arc' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> recover cancel; Media recovery complete.   [or]   Alternatively, when the instance is in mount stage use the V$LOG view to know the FIRST_CHANGE# number of the last redo logfile and use the following method.   SQL> alter database recover automatic using backup controlfile until change 386320; SQL> alter database open resetlogs; Database altered.     2. Loss of a Non-System datafile   i) First, let's populate some data into 'USERS' tablespace and don't forget to force few log switches thereafter.   ii) In order to create a scenario, let's delete the datafile 'users1.dbf' beloging to 'USERS' tablespace.   $ rm -f /u01/oradata/dbuat01/data/users1.dbf $ sqlplus scott/tiger SQL> select * from test; select * from test * ERROR at line 1: ORA-01116: error in opening database file 4 ORA-01110: data file 4: '/u01/oradata/dbuat01/data/users1.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> conn / as sysdba SQL> alter database datafile '/u01/oradata/dbuat01/data/users1.dbf' offline; Database altered.   iii) Let's look at how we can recover the missing datafile (using RMAN):   $ rman target / [ ----------------------------------< code - expandable on mouseover >----------------------------------- ] RMAN> restore datafile 4; Starting restore at 09-JUL-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=750 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00004 to /u01/oradata/dbuat01/data/users1.dbf channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120709_788207368_0fnfm5o8_15_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120709_788207368_0fnfm5o8_15_1 tag=TAG20120709T182927 channel ORA_DISK_1: restore complete, elapsed time: 00:00:04 Finished restore at 09-JUL-12 RMAN> recover datafile 4; Starting recover at 09-JUL-12 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 9 is already on disk as file /u01/oradata/dbuat01/archive/dbuat01_1_9_788207051.arc archive log thread 1 sequence 10 is already on disk as file /u01/oradata/dbuat01/archive/dbuat01_1_10_788207051.arc archive log thread 1 sequence 11 is already on disk as file /u01/oradata/dbuat01/archive/dbuat01_1_11_788207051.arc archive log thread 1 sequence 12 is already on disk as file /u01/oradata/dbuat01/archive/dbuat01_1_12_788207051.arc archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_9_788207051.arc thread=1 sequence=9 archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_10_788207051.arc thread=1 sequence=10 media recovery complete, elapsed time: 00:00:02 Finished recover at 09-JUL-12 $ sqlplus / as sysdba SQL> alter database datafile '/u01/oradata/dbuat01/data/users1.dbf' online; Database altered.   iv) How about using user-managed hotbackup:   $ cd /u01/oradata/dbuat01/data; cp /u02/oradata/backup/dbuat01/backup/users1.dbf . $ sqlplus / as sysdba SQL> recover datafile 4; ORA-00279: change 151699 generated at 07/09/2012 19:10:07 needed for thread 1 ORA-00289: suggestion : /u01/oradata/dbuat01/archive/dbuat01_1_1_788209720.arc ORA-00280: change 151699 for thread 1 is in sequence #1 Specify log: {=suggested | filename | AUTO | CANCEL} ORA-00279: change 151749 generated at 07/09/2012 19:11:59 needed for thread 1 ORA-00289: suggestion : /u01/oradata/dbuat01/archive/dbuat01_1_2_788209720.arc ORA-00280: change 151749 for thread 1 is in sequence #2 ORA-00278: log file '/u01/oradata/dbuat01/archive/dbuat01_1_1_788209720.arc' no longer needed for this recovery Specify log: {=suggested | filename | AUTO | CANCEL} Log applied. Media recovery complete. SQL> alter database datafile '/u01/oradata/dbuat01/data/users1.dbf' online; Database altered.     3. Loss of System datafile   Following is a simple RMAN shell script to backup full database including archivelogs using one day retention.   i) Populate some data inside system tablespace and force a few logswitches.   ii) In order to create a scenario, let's delete the datafile 'tbs_system_01.dbf' beloging to 'system' tablespace.   $ cd /u01/oradata/dbuat01/data $ rm -f tbs_system_01.dbf $ sqlplus / as sysdba SQL> select * from test1; select * from test1 * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01116: error in opening database file 1 ORA-01110: data file 1: '/u02/oradata/dbuat01/data/tbs_system_01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> shut abort   iii) Recovery using RMAN:   [ ----------------------------------< code - expandable on mouseover >----------------------------------- ] RMAN> startup mount; RMAN> restore datafile 1; Starting restore at 10-JUL-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=133 device type=DISK 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 00001 to /u02/oradata/dbuat01/data/tbs_system_01.dbf channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120710_788261650_3onfnqoi_120_1 channel ORA_DISK_1: piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120710_788261650_3onfnqoi_120_1 tag=TAG20120710T093409 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 Finished restore at 10-JUL-12 RMAN> recover database; Starting recover at 10-JUL-12 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 58 is already on disk as file /u02/oradata/dbuat01/archived_logs/dbuat01_1_58_786023887.arc archived log for thread 1 with sequence 59 is already on disk as file /u02/oradata/dbuat01/archived_logs/dbuat01_1_59_786023887.arc archived log for thread 1 with sequence 60 is already on disk as file /u02/oradata/dbuat01/archived_logs/dbuat01_1_60_786023887.arc archived log file name=/u02/oradata/dbuat01/archived_logs/dbuat01_1_58_786023887.arc thread=1 sequence=58 media recovery complete, elapsed time: 00:00:01 Finished recover at 10-JUL-12 RMAN> alter database open; database opened   iv) Recovery using user-managed hotbackup:   $ cd /u02/oradata/dbuat01/data; cp /u02/oradata/backup/dbuat01/hotbkp/tbs_system_01.dbf . $ sqlplus / as sysdba SQL> startup mount; SQL> recover database; Media recovery complete. SQL> alter database open; Database altered.     4. Point In Time Recovery (PITR) using RMAN ('UNTIL TIME')   Following is a simple RMAN shell script to backup full database including archivelogs using one day retention.   i) Let's first populate some user data ('SCOTT.EMPLOYEES'):   SQL> create table employees ( 2 emp_id varchar2(4 byte) not null, 3 emp_f_name varchar2(10 byte), 4 emp_l_name varchar2(10 byte), 5 emp_doj date, 6 emp_salary number(8,2), 7 emp_city varchar2(10 byte) ) / SQL> insert into employees values (01,'jason','martin','25-jul-1996',1234.56,'mumbai'); SQL> insert into employees values (02,'mathews','alison','21-mar-1976',4322.55,'chennai'); SQL> insert into employees values (03,'smith','james','12-dec-1978',7897.78,'kolkata'); SQL> insert into employees values (04,'rice','celia','30-jul-1987',6544.65,'bangalore'); SQL> insert into employees values (05,'larry','david','17-sep-1996',1232.35,'noida'); SQL> commit; SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai 2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai 3 smith james 12-dec-1978 00:00:00 7897.78 kolkata 4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore 5 larry david 17-sep-1996 00:00:00 1232.35 noida   ii) Let's now grab the V$LOG   SQL> conn / as sysdba SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- ------------- ------------- -------------------- 1 1 1 52428800 2 NO CURRENT 183093 10-jul-2012 17:09:25 2 1 0 52428800 2 YES UNUSED 0 3 1 0 52428800 2 YES UNUSED 0   The view shows that the populated 'SCOTT.EMPLOYEES' redo data is held in the current logseq# 1.   iii) Now, after a couple of log switches, it should now look as below:   SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- ------------- ------------- -------------------- 1 1 1 52428800 2 YES ACTIVE 183093 10-jul-2012 17:09:25 2 1 2 52428800 2 YES ACTIVE 184123 10-jul-2012 17:36:59 3 1 3 52428800 2 NO CURRENT 184133 10-jul-2012 17:37:24   iv) Now, let's perform RMAN backup (refer my other posts on how to perform RMAN backup). after the RMAN backup, V$LOG might look like this:   SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME --------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 4 52428800 2 YES INACTIVE 184453 10-jul-2012 17:47:36 2 1 5 52428800 2 NO CURRENT 184464 10-jul-2012 17:47:48 3 1 3 52428800 2 YES INACTIVE 184133 10-jul-2012 17:37:24   RMAN would have backed up till logseq# 4 along with the database backup. To crosscheck, use:   RMAN> list backup of archivelog all; using target database control file instead of recovery catalog List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 54 3.00K DISK 00:00:01 10-JUL-12 BP Key: 54 Status: AVAILABLE Compressed: YES Tag: TAG20120710T174748 Piece Name: /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788291268_20nfonm4_64_1 List of Archived Logs in backup set 54 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 4 184453 10-JUL-12 184464 10-JUL-12   v) Now, let's create a scenario:   SQL> conn scott/tiger Connected. SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai 2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai 3 smith james 12-dec-1978 00:00:00 7897.78 kolkata 4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore 5 larry david 17-sep-1996 00:00:00 1232.35 noida SQL> delete employees; 5 rows deleted. SQL> commit; Commit complete. SQL> conn / as sysdba SQL> alter system archive log current; System altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ------------- ------------- -------------------- 1 1 4 52428800 2 YES INACTIVE 184453 10-jul-2012 17:47:36 2 1 5 52428800 2 YES ACTIVE 184464 10-jul-2012 17:47:48 3 1 6 52428800 2 NO CURRENT 185087 10-jul-2012 18:13:30   Note: Remember that logseq# 5 will have vector change of SQL> DELETE EMPLOYEES;. Now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. of course, let's insert some fresh data. it now looks like this:   SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai 7 aryan arun 17-jan-2012 00:00:00 1232.35 mumbai   Now, let's grab V$LOG status.   SQL> conn / as sysdba Connected. SQL> alter system archive log current; System altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME --------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 7 52428800 2 NO CURRENT 185210 10-jul-2012 18:19:28 2 1 5 52428800 2 YES INACTIVE 184464 10-jul-2012 17:47:48 3 1 6 52428800 2 YES ACTIVE 185087 10-jul-2012 18:13:30   Note: Remember that logseq# 6 will have vector change of the two fresh records inserted. Now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. this time let's drop the table itself.   SQL> conn scott/tiger Connected. SQL> drop table employees; Table dropped. SQL> conn / as sysdba SQL> alter system archive log current; System altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME --------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 7 52428800 2 YES ACTIVE 185210 10-jul-2012 18:19:28 2 1 8 52428800 2 NO CURRENT 185272 10-jul-2012 18:21:47 3 1 6 52428800 2 YES ACTIVE 185087 10-jul-2012 18:13:30   Note: Logseq# 7 will have vector change of the SQL> DROP TABLE EMPLOYEES;. Now, let's prepare for recovery to ensure that we recover the two records inserted a short while ago (that means, we must recover UNTIL TIME '10-jul-2012 18:19:28') just before dropping the table using RMAN backup and available archivelogs.   Few things to recall before crashing the database: In the RMAN backup, only the logseq# 4 is available along with the database backup. In the actual archive destination (/u01/oradata/dbuat01/archive), log sequences between logseq# 4 and logseq# 7 are still available. $ cd /u01/oradata/dbuat01/archive $ ls -lrt -rw-r----- 1 ora10g dba 2048 Jul 10 17:47 dbuat01_1_4_788288965.arc -rw-r----- 1 ora10g dba 80896 Jul 10 18:13 dbuat01_1_5_788288965.arc -rw-r----- 1 ora10g dba 7680 Jul 10 18:19 dbuat01_1_6_788288965.arc -rw-r----- 1 ora10g dba 9728 Jul 10 18:21 dbuat01_1_7_788288965.arc SQL> shut abort; $ rman target / RMAN> set DBID=2691765368 executing command: SET DBID RMAN> startup nomount; RMAN> restore controlfile from '/u02/oradata/backup/dbuat01/rman/dbuat01_control_c-2691765368-20120710-07'; Starting restore at 10-JUL-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=761 devtype=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 output filename=/u01/oradata/dbuat01/control/control01.ctl output filename=/u01/oradata/dbuat01/control/control02.ctl output filename=/u01/oradata/dbuat01/control/control03.ctl Finished restore at 10-JUL-12 RMAN> alter database mount;   Note: After mounting the database, if you query V$LOG it might show as:   SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME --------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 4 52428800 2 YES INACTIVE 184453 10-jul-2012 17:47:36 3 1 3 52428800 2 YES INACTIVE 184133 10-jul-2012 17:37:24 2 1 5 52428800 2 NO CURRENT 184464 10-jul-2012 17:47:48   Note: After mounting the database, if you query V$LOG it might show as:   [ ----------------------------------< code - expandable on mouseover >----------------------------------- ] RMAN> run { 2> set until time = "to_date('10-jul-2012 18:19:28','dd-mon-yyyy hh24:mi:ss')"; 3> restore database; 4> recover database; 5> } executing command: SET until clause Starting restore at 10-JUL-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=760 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00003 to /u01/oradata/dbuat01/data/sysaux1.dbf restoring datafile 00004 to /u01/oradata/dbuat01/data/users1.dbf channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788291261_1vnfonlt_63_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788291261_1vnfonlt_63_1 tag=TAG20120710T174741 channel ORA_DISK_1: restore complete, elapsed time: 00:00:08 channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/oradata/dbuat01/data/system1.dbf restoring datafile 00002 to /u01/oradata/dbuat01/data/undotbs1.dbf channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788291261_1unfonlt_62_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788291261_1unfonlt_62_1 tag=TAG20120710T174741 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 Finished restore at 10-JUL-12 Starting recover at 10-JUL-12 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 4 is already on disk as file /u01/oradata/dbuat01/archive/dbuat01_1_4_788288965.arc archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_4_788288965.arc thread=1 sequence=4 archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_5_788288965.arc thread=1 sequence=5 archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_6_788288965.arc thread=1 sequence=6 media recovery complete, elapsed time: 00:00:02 Finished recover at 10-JUL-12   As you can see, RMAN stopped archivelog apply after logseq# 6, because the timestamp '10-JUL-2012 18:19:28' is the first change_time of logseq# 7.   RMAN> alter database open resetlogs; Database opened.   After, 'RESETLOGS' logseq# is reset to 1:   SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ------------- ------------- -------------------- 1 1 1 52428800 2 NO CURRENT 185211 10-jul-2012 18:42:56 2 1 0 52428800 2 YES UNUSED 0 3 1 0 52428800 2 YES UNUSED 0   We aren't done yet! Now finally, let's verify 'SCOTT.EMPLOYEES' table   SQL> conn scott/tiger Connected. SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai 7 aryan arun 17-jan-2012 00:00:00 1232.35 mumbai   Voila! we did it!!!     5. Point In Time Recovery (PITR) using user-managed hot backup ('UNTIL TIME')   i) Let's first populate some user data ('SCOTT.EMPLOYEES'):   SQL> create table employees ( 2 emp_id varchar2(4 byte) not null, 3 emp_f_name varchar2(10 byte), 4 emp_l_name varchar2(10 byte), 5 emp_doj date, 6 emp_salary number(8,2), 7 emp_city varchar2(10 byte) ) / SQL> insert into employees values (01,'jason','martin','25-jul-1996',1234.56,'mumbai'); SQL> insert into employees values (02,'mathews','alison','21-mar-1976',4322.55,'chennai'); SQL> insert into employees values (03,'smith','james','12-dec-1978',7897.78,'kolkata'); SQL> insert into employees values (04,'rice','celia','30-jul-1987',6544.65,'bangalore'); SQL> insert into employees values (05,'larry','david','17-sep-1996',1232.35,'noida'); SQL> commit; SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai 2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai 3 smith james 12-dec-1978 00:00:00 7897.78 kolkata 4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore 5 larry david 17-sep-1996 00:00:00 1232.35 noida   ii) Let's now grab the V$LOG   SQL> conn / as sysdba SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 1 52428800 2 NO CURRENT 187163 10-jul-2012 19:57:32 2 1 0 52428800 2 YES UNUSED 0 3 1 0 52428800 2 YES UNUSED 0   The view shows that the populated 'SCOTT.EMPLOYEES' redo data resides in the current logseq# 1.   iii) Now, after a couple of log switches, it should now look as below:   SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME --------- ---------- ---------- ---------- ---------- --- ------------- ------------- -------------------- 1 1 1 52428800 2 YES ACTIVE 187163 10-jul-2012 19:57:32 2 1 2 52428800 2 YES ACTIVE 221183 11-jul-2012 19:02:22 3 1 3 52428800 2 NO CURRENT 221189 11-jul-2012 19:02:34     iv) Now, let's perform user-managed hotbackup (refer my other posts on how to perform user-managed hotbackup).     v) Vow, let's create a scenario:   SQL> conn scott/tiger Connected. SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai 2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai 3 smith james 12-dec-1978 00:00:00 7897.78 kolkata 4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore 5 larry david 17-sep-1996 00:00:00 1232.35 noida SQL> delete employees; 5 rows deleted. SQL> commit; Commit complete. SQL> conn / as sysdba SQL> alter system archive log current; System altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 4 52428800 2 NO CURRENT 221411 11-jul-2012 19:12:54 2 1 2 52428800 2 YES INACTIVE 221183 11-jul-2012 19:02:22 3 1 3 52428800 2 YES ACTIVE 221189 11-jul-2012 19:02:34   Note: Remember that logseq# 3 will have vector change of 'SQL> DELETE EMPLOYEES;'. Now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. Of course, let's insert some fresh data. It now looks like this:   SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai 7 aryan arun 17-jan-2012 00:00:00 2132.53 mumbai   Now, let's grab V$LOG status.   SQL> conn / as sysdba Connected. SQL> alter system archive log current; System altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- ------------- ------------- -------------------- 1 1 4 52428800 2 YES ACTIVE 221411 11-jul-2012 19:12:54 2 1 5 52428800 2 NO CURRENT 221518 11-jul-2012 19:18:00 3 1 3 52428800 2 YES INACTIVE 221189 11-jul-2012 19:02:34   Note: Remember that logseq# 4 will have vector change of the two fresh records inserted. Now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. This time let's drop the table itself.   SQL> conn scott/tiger Connected. SQL> drop table employees; Table dropped. SQL> conn / as sysdba SQL> alter system archive log current; System altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 4 52428800 2 YES ACTIVE 221411 11-jul-2012 19:12:54 2 1 5 52428800 2 YES ACTIVE 221518 11-jul-2012 19:18:00 3 1 6 52428800 2 NO CURRENT 221561 11-jul-2012 19:19:22   Note: logseq# 5 will have vector change of the SQL> DROP TABLE EMPLOYEES;. now, let's prepare for recovery to ensure that we recover the two records inserted a short while ago (that means, we must recover UNTIL TIME '11-JUL-2012 19:18:00') just before dropping the table using the user-managed backup and available archivelogs.    Few things to recall before crashing the database: In the actual archive destination (/u01/oradata/dbuat01/archive), ensure that log sequences between logseq# 1 and logseq# 5 are still available.  $ cd /u01/oradata/dbuat01/archive $ ls -lrt -rw-r----- 1 ora10g dba 23560704 Jul 11 19:02 dbuat01_1_1_788299052.arc -rw-r----- 1 ora10g dba 1024 Jul 11 19:02 dbuat01_1_2_788299052.arc -rw-r----- 1 ora10g dba 47104 Jul 11 19:12 dbuat01_1_3_788299052.arc -rw-r----- 1 ora10g dba 5120 Jul 11 19:18 dbuat01_1_4_788299052.arc -rw-r----- 1 ora10g dba 9728 Jul 11 19:19 dbuat01_1_5_788299052.arc SQL> shut abort; $ cd /u01/oradata/dbuat01/data; rm -f *dbf $ cd /u01/oradata/dbuat01/control; rm -f *ctl $ cd /u01/oradata/dbuat01/redo; rm -f *log $ cd /u01/oradata/dbuat01/data; cp /u02/oradata/backup/dbuat01/hotbkp/*dbf . $ cd /u01/oradata/dbuat01/control; cp /u02/oradata/backup/dbuat01/hotbkp/*ctl . SQL> startup mount; SQL> recover database until time '2012-jul-11:19:18:00' using backup controlfile; ORA-00279: change 221242 generated at 07/11/2012 19:05:12 needed for thread 1 ORA-00289: suggestion : /u01/oradata/dbuat01/archive/dbuat01_1_3_788299052.arc ORA-00280: change 221242 for thread 1 is in sequence #3 Specify log: {=suggested | filename | AUTO | CANCEL} ORA-00279: change 221411 generated at 07/11/2012 19:12:54 needed for thread 1 ORA-00289: suggestion : /u01/oradata/dbuat01/archive/dbuat01_1_4_788299052.arc ORA-00280: change 221411 for thread 1 is in sequence #4 ORA-00278: log file '/u01/oradata/dbuat01/archive/dbuat01_1_3_788299052.arc' no longer needed for this recovery Specify log: {=suggested | filename | AUTO | CANCEL} Log applied. Media recovery complete. SQL> alter database open resetlogs; Database altered.   Now, SCOTT.EMPLOYEES must have the two records inserted just before dropping the table itself. Let's check out.   SQL> conn scott/tiger Connected. SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai 7 aryan arun 17-jan-2012 00:00:00 2132.53 mumbai     6. Point In Time Recovery (PITR) using RMAN ('UNTIL SCN')     i) Let's first populate some user data ('SCOTT.EMPLOYEES'):   SQL> create table employees ( 2 emp_id varchar2(4 byte) not null, 3 emp_f_name varchar2(10 byte), 4 emp_l_name varchar2(10 byte), 5 emp_doj date, 6 emp_salary number(8,2), 7 emp_city varchar2(10 byte) ) / SQL> insert into employees values (01,'jason','martin','25-jul-1996',1234.56,'mumbai'); SQL> insert into employees values (02,'mathews','alison','21-mar-1976',4322.55,'chennai'); SQL> insert into employees values (03,'smith','james','12-dec-1978',7897.78,'kolkata'); SQL> insert into employees values (04,'rice','celia','30-jul-1987',6544.65,'bangalore'); SQL> insert into employees values (05,'larry','david','17-sep-1996',1232.35,'noida'); SQL> commit; SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai 2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai 3 smith james 12-dec-1978 00:00:00 7897.78 kolkata 4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore 5 larry david 17-sep-1996 00:00:00 1232.35 noida     ii) let's now grab the V$LOG   SQL> conn / as sysdba SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 1 52428800 2 NO CURRENT 185211 10-jul-2012 18:42:56 2 1 0 52428800 2 YES UNUSED 0 3 1 0 52428800 2 YES UNUSED 0   The view shows that the populated 'SCOTT.EMPLOYEES' redo data resides in the current logseq# 1.     iii) now, after a couple of switches, it now looks as below:   SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 1 52428800 2 YES ACTIVE 185211 10-jul-2012 18:42:56 2 1 2 52428800 2 YES ACTIVE 185763 10-jul-2012 18:54:20 3 1 3 52428800 2 NO CURRENT 185768 10-jul-2012 18:54:29     iv) Now, let's perform RMAN backup (refer my other posts on how to perfor RMAN backup). After the RMAN backup, V$LOG might look like this:   SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 4 52428800 2 YES INACTIVE 185821 10-jul-2012 18:55:08 2 1 5 52428800 2 NO CURRENT 185832 10-jul-2012 18:55:23 3 1 3 52428800 2 YES INACTIVE 185768 10-jul-2012 18:54:29   RMAN would have backed up till Next SCN# 185832 (logseq# 4) along with the database backup. To crosscheck, use:   RMAN> list backup of archivelog all; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 62 2.00K DISK 00:00:01 10-JUL-12 BP Key: 62 Status: AVAILABLE Compressed: YES Tag: TAG20120710T185523 Piece Name: /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788295323_2anforkr_74_1 List of Archived Logs in backup set 62 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 4 185821 10-JUL-12 185832 10-JUL-12     v) Now, let's create a scenario:   SQL> conn scott/tiger Connected. SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai 2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai 3 smith james 12-dec-1978 00:00:00 7897.78 kolkata 4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore 5 larry david 17-sep-1996 00:00:00 1232.35 noida SQL> delete employees; 5 rows deleted. SQL> commit; Commit complete. SQL> conn / as sysdba SQL> alter system archive log current; System altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 4 52428800 2 YES INACTIVE 185821 10-jul-2012 18:55:08 2 1 5 52428800 2 YES ACTIVE 185832 10-jul-2012 18:55:23 3 1 6 52428800 2 NO CURRENT 185999 10-jul-2012 19:00:21   Note: Remember that logseq# 5 will have vector change of SQL> DELETE EMPLOYEES;. Now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. Of course, let's insert some fresh data. It should now looks like this:   SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai 7 aryan arun 17-jan-2012 00:00:00 1232.35 mumbai   Now, let's grab V$LOG status.   SQL> alter system archive log current; System altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 7 52428800 2 NO CURRENT 186149 10-jul-2012 19:04:11 2 1 5 52428800 2 YES ACTIVE 185832 10-jul-2012 18:55:23 3 1 6 52428800 2 YES ACTIVE 185999 10-jul-2012 19:00:21   Note: Remember that logseq# 6 will have vector change of the two fresh records. Now, let's make some more changes in the 'EMPLOYEES' table. This time, let's drop the table itself.   SQL> conn scott/tiger Connected. SQL> drop table employees; Table dropped. SQL> conn / as sysdba SQL> alter system archive log current; SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 7 52428800 2 YES ACTIVE 186149 10-jul-2012 19:04:11 2 1 8 52428800 2 NO CURRENT 186211 10-jul-2012 19:06:29 3 1 6 52428800 2 YES ACTIVE 185999 10-jul-2012 19:00:21   Note: Logseq# 7 will have vector change of the SQL> DROP TABLE EMPLOYEES;. Now, let's prepare for recovery to ensure that we recover the two records inserted a short while ago (that means, we must recover UNTIL CHANGE# '186149') just before dropping the table using RMAN backup and archivelogs.   Few things to recall before crashing the database: In the RMAN backup, only the logseq# 4 (NEXT SCN 185832) is available. In the actual archive destination, from logseq# 4 to logseq# 7 is available as show below: $ cd /u01/oradata/dbuat01/archive $ ls -lrt -rw-r----- 1 ora10g dba 1024 Jul 10 18:55 dbuat01_1_4_788294576.arc -rw-r----- 1 ora10g dba 5120 Jul 10 19:00 dbuat01_1_5_788294576.arc -rw-r----- 1 ora10g dba 1503232 Jul 10 19:04 dbuat01_1_6_788294576.arc -rw-r----- 1 ora10g dba 11264 Jul 10 19:06 dbuat01_1_7_788294576.arc SQL> shut abort; $ rman target / RMAN> set DBID=2691765368 executing command: SET DBID RMAN> startup nomount; RMAN> restore controlfile from '/u02/oradata/backup/dbuat01/rman/dbuat01_control_c-2691765368-20120710-09'; Starting restore at 10-JUL-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=761 devtype=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 output filename=/u01/oradata/dbuat01/control/control01.ctl output filename=/u01/oradata/dbuat01/control/control02.ctl output filename=/u01/oradata/dbuat01/control/control03.ctl Finished restore at 10-JUL-12 RMAN> alter database mount;   Note: After mounting the database, if you query V$LOG it might show as:   SQL> select status from v$instance; STATUS ------------ MOUNTED SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 4 52428800 2 YES INACTIVE 185821 10-jul-2012 18:55:08 3 1 3 52428800 2 YES INACTIVE 185768 10-jul-2012 18:54:29 2 1 5 52428800 2 NO CURRENT 185832 10-jul-2012 18:55:23 [ ----------------------------------< code - expandable on mouseover >----------------------------------- ] RMAN> run { 2> set until scn 186149; 3> restore database; 4> recover database; 5> } executing command: SET until clause Starting restore at 10-JUL-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=760 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00003 to /u01/oradata/dbuat01/data/sysaux1.dbf restoring datafile 00004 to /u01/oradata/dbuat01/data/users1.dbf channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788295312_29nforkg_73_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788295312_29nforkg_73_1 tag=TAG20120710T185512 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/oradata/dbuat01/data/system1.dbf restoring datafile 00002 to /u01/oradata/dbuat01/data/undotbs1.dbf channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788295312_28nforkg_72_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788295312_28nforkg_72_1 tag=TAG20120710T185512 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 Finished restore at 10-JUL-12 Starting recover at 10-JUL-12 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 4 is already on disk as file /u01/oradata/dbuat01/archive/dbuat01_1_4_788294576.arc archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_4_788294576.arc thread=1 sequence=4 archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_5_788294576.arc thread=1 sequence=5 archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_6_788294576.arc thread=1 sequence=6 media recovery complete, elapsed time: 00:00:02 Finished recover at 10-JUL-12   Note: As you can see, RMAN applied until logseq# 6, because the timestamp SCN '186149' is the first change_time of logseq# 7.   RMAN> alter database open resetlogs; Database opened.   Let's query V$LOG after using RESETLOGS:   SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 1 52428800 2 NO CURRENT 186212 10-jul-2012 19:19:04 2 1 0 52428800 2 YES UNUSED 0 3 1 0 52428800 2 YES UNUSED 0   We aren't done yet! Now finally, let's verify SCOTT.EMPLOYEES table   SQL> conn scott/tiger Connected. SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai 7 aryan arun 17-jan-2012 00:00:00 1232.35 mumbai   Voila! Those two records recovered!!!     7. Point In Time Recovery (PITR) using user-managed hot backup ('UNTIL CHANGE')   i) Let's first populate some user data ('SCOTT.EMPLOYEES'):   SQL> create table employees ( 2 emp_id varchar2(4 byte) not null, 3 emp_f_name varchar2(10 byte), 4 emp_l_name varchar2(10 byte), 5 emp_doj date, 6 emp_salary number(8,2), 7 emp_city varchar2(10 byte) ) / SQL> insert into employees values (01,'jason','martin','25-jul-1996',1234.56,'mumbai'); SQL> insert into employees values (02,'mathews','alison','21-mar-1976',4322.55,'chennai'); SQL> insert into employees values (03,'smith','james','12-dec-1978',7897.78,'kolkata'); SQL> insert into employees values (04,'rice','celia','30-jul-1987',6544.65,'bangalore'); SQL> insert into employees values (05,'larry','david','17-sep-1996',1232.35,'noida'); SQL> commit; SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai 2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai 3 smith james 12-dec-1978 00:00:00 7897.78 kolkata 4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore 5 larry david 17-sep-1996 00:00:00 1232.35 noida     ii) Let's now grab the V$LOG   SQL> conn / as sysdba SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 1 52428800 2 NO CURRENT 186953 12-jul-2012 12:41:30 2 1 0 52428800 2 YES UNUSED 0 3 1 0 52428800 2 YES UNUSED 0   The view shows that the populated 'SCOTT.EMPLOYEES' redo data resides in the current logseq# 1.     iii) Now, after a couple of switches, it now looks as below:   SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 1 52428800 2 YES ACTIVE 186953 12-jul-2012 12:41:30 2 1 2 52428800 2 YES ACTIVE 187115 12-jul-2012 12:44:05 3 1 3 52428800 2 NO CURRENT 187118 12-jul-2012 12:44:07     iv) Now, let's perform user-managed hotbackup (refer my other posts on how to perform user-managed hotbackup).     v) Now, let's create a scenario:   SQL> conn scott/tiger Connected. SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai 2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai 3 smith james 12-dec-1978 00:00:00 7897.78 kolkata 4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore 5 larry david 17-sep-1996 00:00:00 1232.35 noida SQL> delete employees; 5 rows deleted. SQL> commit; Commit complete. SQL> conn / as sysdba SQL> alter system archive log current; System altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 4 52428800 2 NO CURRENT 187191 12-jul-2012 12:47:02 2 1 2 52428800 2 YES INACTIVE 187115 12-jul-2012 12:44:05 3 1 3 52428800 2 YES ACTIVE 187118 12-jul-2012 12:44:07   Note: Remember that logseq# 3 will have vector change of SQL> DELETE EMPLOYEES;. now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. Of course, let's insert some fresh data. It now looks like this:   SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai 7 aryan arun 26-nov-2008 00:00:00 2226.35 nagercoil   Now, let's grab V$LOG status.   SQL> conn / as sysdba Connected. SQL> alter system archive log current; System altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 4 52428800 2 YES ACTIVE 187191 12-jul-2012 12:47:02 2 1 5 52428800 2 NO CURRENT 187243 12-jul-2012 12:49:24 3 1 3 52428800 2 YES ACTIVE 187118 12-jul-2012 12:44:07   Note: Remember that logseq# 4 will have vector change of the two fresh records inserted. Now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. This time let's drop the table itself.   SQL> conn scott/tiger Connected. SQL> drop table employees; Table dropped. SQL> conn / as sysdba SQL> alter system archive log current; System altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 4 52428800 2 YES INACTIVE 187191 12-jul-2012 12:47:02 2 1 5 52428800 2 YES ACTIVE 187243 12-jul-2012 12:49:24 3 1 6 52428800 2 NO CURRENT 187281 12-jul-2012 12:50:33   Note: logseq# 5 will have vector change of the SQL> DROP TABLE EMPLOYEES;. now, let's prepare for recovery to ensure that we recover the two records inserted a short while ago (that means, we must recover UNTIL CHANGE# 187243) just before dropping the table using the user-managed backup and available archivelogs.   Few things to recall before crashing the database: In the actual archive destination (/u01/oradata/dbuat01/archive), log sequences between logseq# 1 and logseq# 5 are still available. $ cd /u01/oradata/dbuat01/archive $ ls -lrt -rw-r----- 1 ora10g dba 58880 Jul 12 12:44 dbuat01_1_1_788445690.arc -rw-r----- 1 ora10g dba 1024 Jul 12 12:44 dbuat01_1_2_788445690.arc -rw-r----- 1 ora10g dba 40960 Jul 12 12:47 dbuat01_1_3_788445690.arc -rw-r----- 1 ora10g dba 3072 Jul 12 12:49 dbuat01_1_4_788445690.arc -rw-r----- 1 ora10g dba 10752 Jul 12 12:50 dbuat01_1_5_788445690.arc SQL> shut abort; $ cd /u01/oradata/dbuat01/data; rm -f *dbf $ cd /u01/oradata/dbuat01/control; rm -f *ctl $ cd /u01/oradata/dbuat01/redo; rm -f *log $ cd /u01/oradata/dbuat01/data; cp /u02/oradata/backup/dbuat01/hotbkp/*dbf . $ cd /u01/oradata/dbuat01/control; cp /u02/oradata/backup/dbuat01/hotbkp/*ctl . SQL> startup mount; SQL> recover database until change 187243 using backup controlfile; ORA-00279: change 187130 generated at 07/12/2012 12:44:43 needed for thread 1 ORA-00289: suggestion : /u01/oradata/dbuat01/archive/dbuat01_1_3_788445690.arc ORA-00280: change 187130 for thread 1 is in sequence #3 Specify log: {=suggested | filename | AUTO | CANCEL} ORA-00279: change 187191 generated at 07/12/2012 12:47:02 needed for thread 1 ORA-00289: suggestion : /u01/oradata/dbuat01/archive/dbuat01_1_4_788445690.arc ORA-00280: change 187191 for thread 1 is in sequence #4 ORA-00278: log file '/u01/oradata/dbuat01/archive/dbuat01_1_3_788445690.arc' no longer needed for this recovery Specify log: {=suggested | filename | AUTO | CANCEL} Log applied. Media recovery complete. SQL> alter database open resetlogs; Database altered.   Now, SCOTT.EMPLOYEES must have the two records inserted just before dropping the table itself.   SQL> conn scott/tiger Connected. SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai 7 aryan arun 26-nov-2008 00:00:00 2226.35 nagercoil   Voila! Those two records!!!     8. Point In Time Recovery (PITR) using RMAN ('UNTIL SEQUENCE')   i) Let's first populate some user data   SQL> create table employees ( 2 emp_id VARCHAR2(4 BYTE) NOT NULL, 3 emp_f_name VARCHAR2(10 BYTE), 4 emp_l_name VARCHAR2(10 BYTE), 5 emp_doj DATE, 6 emp_salary Number(8,2), 7 emp_city VARCHAR2(10 BYTE) ) / SQL> insert into employees values (01,'jason','martin','25-jul-1996',1234.56,'mumbai'); SQL> insert into employees values (02,'mathews','alison','21-mar-1976',4322.55,'chennai'); SQL> insert into employees values (03,'smith','james','12-dec-1978',7897.78,'kolkata'); SQL> insert into employees values (04,'rice','celia','30-jul-1987',6544.65,'bangalore'); SQL> insert into employees values (05,'larry','david','17-sep-1996',1232.35,'noida'); SQL> commit; SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai 2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai 3 smith james 12-dec-1978 00:00:00 7897.78 kolkata 4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore 5 larry david 17-sep-1996 00:00:00 1232.35 noida     ii) Let's now grab the V$LOG   SQL> conn / as sysdba SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 1 52428800 2 NO CURRENT 186212 10-jul-2012 19:19:04 2 1 0 52428800 2 YES UNUSED 0 3 1 0 52428800 2 YES UNUSED 0   The view indicates that the populated sample data must reside in logseq# 1     iii) Now, after a couple of switches, it now looks as below:   SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 1 52428800 2 YES ACTIVE 186212 10-jul-2012 19:19:04 2 1 2 52428800 2 YES ACTIVE 186869 10-jul-2012 19:37:14 3 1 3 52428800 2 NO CURRENT 186874 10-jul-2012 19:37:24     iv) Now, let's take RMAN backup. So, after the RMAN backup, it might look like this:   SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 4 52428800 2 YES INACTIVE 186941 10-jul-2012 19:38:47 2 1 5 52428800 2 NO CURRENT 186952 10-jul-2012 19:38:59 3 1 3 52428800 2 YES INACTIVE 186874 10-jul-2012 19:37:24   RMAN would have backed up till logseq# 4. To crosscheck, use:   RMAN> list backup of archivelog all; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 70 2.00K DISK 00:00:01 10-JUL-12 BP Key: 70 Status: AVAILABLE Compressed: YES Tag: TAG20120710T193859 Piece Name: /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788297939_2knfou6j_84_1 List of Archived Logs in backup set 70 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 4 186941 10-JUL-12 186952 10-JUL-12     v) Now, let's create a scenario:   SQL> conn scott/tiger Connected. SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai 2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai 3 smith james 12-dec-1978 00:00:00 7897.78 kolkata 4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore 5 larry david 17-sep-1996 00:00:00 1232.35 noida SQL> delete employees; 5 rows deleted. SQL> commit; Commit complete. SQL> conn / as sysdba SQL> alter system archive log current; System altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 4 52428800 2 YES INACTIVE 186941 10-jul-2012 19:38:47 2 1 5 52428800 2 YES ACTIVE 186952 10-jul-2012 19:38:59 3 1 6 52428800 2 NO CURRENT 187125 10-jul-2012 19:42:49   Note: Remember that logseq# 5 will have vector change of SQL> DELETE EMPLOYEES;. Now, let's make some more changes in the 'EMPLOYEES' table. Of course, some fresh data. So, it now looks like this:   SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai 7 aryan arun 17-jan-2012 00:00:00 1232.35 mumbai   Now, let's grab V$LOG status.   SQL> alter system archive log current; SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 7 52428800 2 NO CURRENT 187162 10-jul-2012 19:44:32 2 1 5 52428800 2 YES ACTIVE 186952 10-jul-2012 19:38:59 3 1 6 52428800 2 YES ACTIVE 187125 10-jul-2012 19:42:49   Note: Remember that logseq# 6 will have vector change of the two fresh records. Now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. This time, let's drop the table itself.   SQL> conn scott/tiger Connected. SQL> drop table employees; Table dropped. SQL> conn / as sysdba SQL> alter system archive log current; SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 7 52428800 2 YES ACTIVE 187162 10-jul-2012 19:44:32 2 1 8 52428800 2 NO CURRENT 187202 10-jul-2012 19:45:41 3 1 6 52428800 2 YES ACTIVE 187125 10-jul-2012 19:42:49   Note: Logseq# 7 will have vector change of the SQL> DROP TABLE EMPLOYEES;. Now, let's prepare for recovery to ensure that we recover the two records inserted a short while ago (that means, we must recover UNTIL SEQUENCE 7) just before dropping the table using RMAN backup and available archivelogs.   Few things to recall before crashing the database: In the RMAN backup, only the logseq# 4 (NEXT SCN 185832) is available. In the actual archive destination, from logseq# 4 to logseq# 7 is available as show below: $ cd /u01/oradata/dbuat01/archive $ ls -lrt -rw-r----- 1 ora10g dba 1024 Jul 10 19:38 dbuat01_1_4_788296744.arc -rw-r----- 1 ora10g dba 363520 Jul 10 19:42 dbuat01_1_5_788296744.arc -rw-r----- 1 ora10g dba 2560 Jul 10 19:44 dbuat01_1_6_788296744.arc -rw-r----- 1 ora10g dba 13312 Jul 10 19:45 dbuat01_1_7_788296744.arc SQL> shut abort; $ rman target / RMAN> set DBID=2691765368 RMAN> startup nomount; RMAN> restore controlfile from '/u02/oradata/backup/dbuat01/rman/dbuat01_control_c-2691765368-20120710-0b'; Starting restore at 10-JUL-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=761 devtype=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 output filename=/u01/oradata/dbuat01/control/control01.ctl output filename=/u01/oradata/dbuat01/control/control02.ctl output filename=/u01/oradata/dbuat01/control/control03.ctl Finished restore at 10-JUL-12 RMAN> alter database mount;   Note: After mounting the database, if you query V$LOG it might show as:   SQL> select status from v$instance; STATUS ------------ MOUNTED SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 4 52428800 2 YES INACTIVE 186941 10-jul-2012 19:38:47 3 1 3 52428800 2 YES INACTIVE 186874 10-jul-2012 19:37:24 2 1 5 52428800 2 NO CURRENT 186952 10-jul-2012 19:38:59 [ ----------------------------------< code - expandable on mouseover >----------------------------------- ] RMAN> run { 2> set until sequence = 7; 3> restore database; 4> recover database; 5> } executing command: SET until clause Starting restore at 10-JUL-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=760 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00003 to /u01/oradata/dbuat01/data/sysaux1.dbf restoring datafile 00004 to /u01/oradata/dbuat01/data/users1.dbf channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788297932_2jnfou6c_83_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788297932_2jnfou6c_83_1 tag=TAG20120710T193852 channel ORA_DISK_1: restore complete, elapsed time: 00:00:08 channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/oradata/dbuat01/data/system1.dbf restoring datafile 00002 to /u01/oradata/dbuat01/data/undotbs1.dbf channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788297932_2infou6c_82_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788297932_2infou6c_82_1 tag=TAG20120710T193852 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 Finished restore at 10-JUL-12 Starting recover at 10-JUL-12 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 4 is already on disk as file /u01/oradata/dbuat01/archive/dbuat01_1_4_788296744.arc archive log thread 1 sequence 6 is already on disk as file /u01/oradata/dbuat01/redo/redo_g3_m2.log archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_4_788296744.arc thread=1 sequence=4 archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_5_788296744.arc thread=1 sequence=5 archive log filename=/u01/oradata/dbuat01/redo/redo_g3_m2.log thread=1 sequence=6 media recovery complete, elapsed time: 00:00:01 Finished recover at 10-JUL-12   Note: As you can see, RMAN applied till logseq# 6.   RMAN> alter database open resetlogs; Database opened.   Let's query V$LOG after using RESETLOGS:   SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME -------- ---------- ---------- ---------- ---------- --- -------------- ------------- -------------------- 1 1 1 52428800 2 NO CURRENT 187163 10-jul-2012 19:57:32 2 1 0 52428800 2 YES UNUSED 0 3 1 0 52428800 2 YES UNUSED 0   We aren't done yet! Now finally, let's verify SCOTT.EMPLOYEES table   SQL> conn scott/tiger Connected. SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai 7 aryan arun 17-jan-2012 00:00:00 1232.35 mumbai   Voila! We did it!!!     9. Point In Time Recovery (PITR) using user-managed hot backup ('UNTIL CANCEL')   i) Let's first populate some user data   SQL> create table employees ( 2 emp_id VARCHAR2(4 BYTE) NOT NULL, 3 emp_f_name VARCHAR2(10 BYTE), 4 emp_l_name VARCHAR2(10 BYTE), 5 emp_doj DATE, 6 emp_salary Number(8,2), 7 emp_city VARCHAR2(10 BYTE) ) / SQL> insert into employees values (01,'jason','martin','25-jul-1996',1234.56,'mumbai'); SQL> insert into employees values (02,'mathews','alison','21-mar-1976',4322.55,'chennai'); SQL> insert into employees values (03,'smith','james','12-dec-1978',7897.78,'kolkata'); SQL> insert into employees values (04,'rice','celia','30-jul-1987',6544.65,'bangalore'); SQL> insert into employees values (05,'larry','david','17-sep-1996',1232.35,'noida'); SQL> commit; SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai 2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai 3 smith james 12-dec-1978 00:00:00 7897.78 kolkata 4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore 5 larry david 17-sep-1996 00:00:00 1232.35 noida     ii) let's now grab the V$LOG   SQL> conn / as sysdba SQL> select group#, sequence#, bytes, members, archived, status, first_change#, first_time, next_change#, next_time from v$log; GROUP# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ------- ---------- ---------- ---------- --- -------- ------------- -------------------- ------------ ----------- 1 1 104857600 2 NO CURRENT 363193 12-jul-2012 17:48:30 2.8147E+14 2 0 104857600 2 YES UNUSED 0 0 3 0 104857600 2 YES UNUSED 0 0   The view indicates that the populated sample data must reside in logseq# 1     iii) Now, after a couple of switches, it now looks as below:   SQL> select group#, sequence#, bytes, members, archived, status, first_change#, first_time, next_change#, next_time from v$log; GROUP# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ------- ---------- ---------- ---------- --- -------- ------------- -------------------- ------------ -------------------- 1 4 104857600 2 NO CURRENT 363540 12-jul-2012 17:57:12 2.8147E+14 2 2 104857600 2 YES INACTIVE 363532 12-jul-2012 17:57:11 363536 12-jul-2012 17:57:11 3 3 104857600 2 YES INACTIVE 363536 12-jul-2012 17:57:11 363540 12-jul-2012 17:57:12     iv) Now, let's perform user-managed hotbackup (refer my other posts on how to perform user-managed hotbackup).     v) Now, let's create a scenario:   SQL> conn scott/tiger Connected. SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai 2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai 3 smith james 12-dec-1978 00:00:00 7897.78 kolkata 4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore 5 larry david 17-sep-1996 00:00:00 1232.35 noida SQL> delete employees; 5 rows deleted. SQL> commit; Commit complete. SQL> conn / as sysdba SQL> alter system archive log current; System altered. SQL> select group#, sequence#, bytes, members, archived, status, first_change#, first_time, next_change#, next_time from v$log; GROUP# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ------- ---------- ---------- ---------- --- -------------------- ------------- -------------------- ------------ -------------------- 1 4 104857600 2 YES ACTIVE 363540 12-jul-2012 17:57:12 363838 12-jul-2012 18:03:17 2 5 104857600 2 YES ACTIVE 363838 12-jul-2012 18:03:17 363860 12-jul-2012 18:03:57 3 6 104857600 2 NO CURRENT 363860 12-jul-2012 18:03:57 2.8147E+14   Note: Remember that logseq# 5 will have vector change of SQL> DELETE EMPLOYEES;. Now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. Of course, let's insert some fresh data. It now looks like this:   SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai 7 aryan arun 26-nov-2008 00:00:00 2226.35 nagercoil   Now, let's grab V$LOG status.   SQL> conn / as sysdba Connected. SQL> alter system archive log current; System altered. SQL> select group#, sequence#, bytes, members, archived, status, first_change#, first_time, next_change#, next_time from v$log; GROUP# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ------- ---------- ---------- ---------- --- ---------------- ------------- -------------------- ------------ -------------------- 1 7 104857600 2 NO CURRENT 364270 12-jul-2012 18:06:39 2.8147E+14 2 5 104857600 2 YES ACTIVE 363838 12-jul-2012 18:03:17 363860 12-jul-2012 18:03:57 3 6 104857600 2 YES ACTIVE 363860 12-jul-2012 18:03:57 364270 12-jul-2012 18:06:39   Note: Remember that logseq# 6 will have vector change of the two fresh records inserted. Now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. This time let's drop the table itself.   SQL> conn scott/tiger Connected. SQL> drop table employees; Table dropped. SQL> conn / as sysdba SQL> alter system archive log current; System altered. SQL> select group#, sequence#, bytes, members, archived, status, first_change#, first_time, next_change#, next_time from v$log; GROUP# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ------- ---------- ---------- ---------- --- -------------- ------------- -------------------- ------------ -------------------- 1 7 104857600 2 YES ACTIVE 364270 12-jul-2012 18:06:39 364325 12-jul-2012 18:08:24 2 8 104857600 2 NO CURRENT 364325 12-jul-2012 18:08:24 2.8147E+14 3 6 104857600 2 YES ACTIVE 363860 12-jul-2012 18:03:57 364270 12-jul-2012 18:06:39   Note: logseq# 7 will have vector change of the SQL> DROP TABLE EMPLOYEES;. now, let's prepare for recovery to ensure that we recover the two records inserted a short while ago (that means, we must recover UNTIL SEQUENCE# 7) just before dropping the table using the user-managed backup and available archivelogs.     Few things to recall before crashing the database: In the actual archive destination (/u01/oradata/dbuat01/archive), ensure log sequences between logseq# 1 and logseq# 7 are still available.   $ cd /u01/oradata/dbuat01/archive $ ls -lrt -rw-r----- 1 ora11g oinstall 103424 Jul 12 17:57 dbuat01_1_1_788464110.arc -rw-r----- 1 ora11g oinstall 1024 Jul 12 17:57 dbuat01_1_2_788464110.arc -rw-r----- 1 ora11g oinstall 2048 Jul 12 17:57 dbuat01_1_3_788464110.arc -rw-r----- 1 ora11g oinstall 837120 Jul 12 18:03 dbuat01_1_4_788464110.arc -rw-r----- 1 ora11g oinstall 6144 Jul 12 18:03 dbuat01_1_5_788464110.arc -rw-r----- 1 ora11g oinstall 311296 Jul 12 18:06 dbuat01_1_6_788464110.arc -rw-r----- 1 ora11g oinstall 13824 Jul 12 18:08 dbuat01_1_7_788464110.arc SQL> shut abort; $ cd /u01/oradata/dbuat01/data; rm -f *dbf $ cd /u01/oradata/dbuat01/control; rm -f *ctl $ cd /u01/oradata/dbuat01/redo; rm -f *log $ cd /u01/oradata/dbuat01/data; cp /u02/oradata/backup/dbuat01/hotbkp/*dbf . $ cd /u01/oradata/dbuat01/control; cp /u02/oradata/backup/dbuat01/hotbkp/*ctl . [ ----------------------------------< code - expandable on mouseover >----------------------------------- ] SQL> startup mount; ORACLE instance started. Total System Global Area 536870912 bytes Fixed Size 2085360 bytes Variable Size 150998544 bytes Database Buffers 377487360 bytes Redo Buffers 6299648 bytes Database mounted. SQL> recover database until cancel using backup controlfile; ORA-00279: change 363762 generated at 07/12/2012 18:00:21 needed for thread 1 ORA-00289: suggestion : /u02/oradata/dbuat01/archived_logs/dbuat01_1_4_788464110.arc ORA-00280: change 363762 for thread 1 is in sequence #4 Specify log: {=suggested | filename | AUTO | CANCEL} ORA-00279: change 363838 generated at 07/12/2012 18:03:17 needed for thread 1 ORA-00289: suggestion : /u02/oradata/dbuat01/archived_logs/dbuat01_1_5_788464110.arc ORA-00280: change 363838 for thread 1 is in sequence #5 ORA-00278: log file '/u02/oradata/dbuat01/archived_logs/dbuat01_1_4_788464110.arc' no longer needed for this recovery Specify log: {=suggested | filename | AUTO | CANCEL} ORA-00279: change 363860 generated at 07/12/2012 18:03:57 needed for thread 1 ORA-00289: suggestion : /u02/oradata/dbuat01/archived_logs/dbuat01_1_6_788464110.arc ORA-00280: change 363860 for thread 1 is in sequence #6 ORA-00278: log file '/u02/oradata/dbuat01/archived_logs/dbuat01_1_5_788464110.arc' no longer needed for this recovery Specify log: {=suggested | filename | AUTO | CANCEL} ORA-00279: change 364270 generated at 07/12/2012 18:06:39 needed for thread 1 ORA-00289: suggestion : /u02/oradata/dbuat01/archived_logs/dbuat01_1_7_788464110.arc ORA-00280: change 364270 for thread 1 is in sequence #7 ORA-00278: log file '/u02/oradata/dbuat01/archived_logs/dbuat01_1_6_788464110.arc' no longer needed for this recovery Specify log: {=suggested | filename | AUTO | CANCEL} CANCEL Media recovery cancelled. SQL> alter database open resetlogs; Database altered.   Now, SCOTT.EMPLOYEES must have the two records inserted just before dropping the table itself.   SQL> conn scott/tiger Connected. Session altered. SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai 7 aryan arun 26-nov-2008 00:00:00 2226.12 nagercoil   Voila! We are done now!!!     10. Loss of Undo datafile   i) Let's populate some user data:   SQL> create table employees ( 2 emp_id varchar2(4 byte) not null, 3 emp_f_name varchar2(10 byte), 4 emp_l_name varchar2(10 byte), 5 emp_doj date, 6 emp_salary number(8,2), 7 emp_city varchar2(10 byte) ) / SQL> insert into employees values (01,'jason','martin','25-jul-1996',1234.56,'mumbai'); SQL> insert into employees values (02,'mathews','alison','21-mar-1976',4322.55,'chennai'); SQL> insert into employees values (03,'smith','james','12-dec-1978',7897.78,'kolkata'); SQL> insert into employees values (04,'rice','celia','30-jul-1987',6544.65,'bangalore'); SQL> insert into employees values (05,'larry','david','17-sep-1996',1232.35,'noida'); SQL> commit; SQL> quit $ rm -f /u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf $ sqlplus / as sysdba SQL> conn scott/tiger SQL> delete from employees where emp_id in (4,5); SQL> rollback; rollback * ERROR at line 1: ORA-00603: ORACLE server session terminated by fatal error ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf' ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf' Process ID: 4486 Session ID: 10 Serial number: 28 SQL> conn / as sysdba SQL> select file#, online_status, error from v$recover_file; FILE# ONLINE_ ERROR ---------- ------- ------------------------------ 3 OFFLINE FILE NOT FOUND SQL> select file#, name, status from v$datafile; FILE# NAME STATUS ---------- ------------------------------------------------------------ ------- 3 /u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf RECOVER SQL> shut abort; SQL> startup mount; SQL> select file#, online_status, error from v$recover_file; FILE# ONLINE_ ERROR ---------- ------- ------------------------------ 3 OFFLINE FILE NOT FOUND $ rman target / RMAN> restore datafile 3; Starting restore at 11-JUL-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=10 device type=DISK 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 00003 to /u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788348439_4mnfqfgn_150_1 channel ORA_DISK_1: piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788348439_4mnfqfgn_150_1 tag=TAG20120711T094039 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 Finished restore at 11-JUL-12 RMAN> recover datafile 3; Starting recover at 11-JUL-12 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 11-JUL-12 RMAN> alter database open; database opened # sqlplus / as sysdba SQL> select file#, online_status, error from v$recover_file; no rows selected SQL> select file#, name, status from v$datafile where file# = 3; FILE# NAME STATUS ---------- ------------------------------------------------------------ ------- 3 /u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf OFFLINE SQL> alter database datafile 3 online; Database altered. SQL> select file#, name, status from v$datafile where file# = 3; FILE# NAME STATUS ---------- ------------------------------------------------------------ ------- 3 /u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf ONLINE SQL> conn scott/tiger SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai 2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai 3 smith james 12-dec-1978 00:00:00 7897.78 kolkata 4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore 5 larry david 17-sep-1996 00:00:00 1232.35 noida   Note: As you can see, inspite of the recovery, SQL> DELETE FROM EMPLOYEES WHERE EMP_ID IN (4,5); statement didn't succeed, because the undo segment was already lost ('tbs_undotbs_01.dbf' physical file was lost) before this statement was executed. hence the sql delete statement couldn't be recovered.     11. Loss of Online Redo log file     i) let's grab the V$LOGFILE & V$LOG   SQL> select * from v$log; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ------------------------------------------------------------ --- 1 ONLINE /u02/oradata/dbuat01/redo/redo_g1_m1.log NO 1 ONLINE /u02/oradata/dbuat01/redo/redo_g1_m2.log NO 2 ONLINE /u02/oradata/dbuat01/redo/redo_g2_m1.log NO 2 ONLINE /u02/oradata/dbuat01/redo/redo_g2_m2.log NO 3 ONLINE /u02/oradata/dbuat01/redo/redo_g3_m1.log NO 3 ONLINE /u02/oradata/dbuat01/redo/redo_g3_m2.log NO SQL> select group#, sequence#, members, status, first_change#, next_change# from v$log; GROUP# SEQUENCE# MEMBERS STATUS FIRST_CHANGE# NEXT_CHANGE# ---------- ---------- ---------- ---------------- ------------- ------------ 1 19 2 INACTIVE 363178 363192 2 20 2 CURRENT 363192 2.8147E+14 3 18 2 INACTIVE 363142 363178   Note: GROUP# 2 is currently active.     ii) Let's create a scenario:   SQL> conn scott/tiger SQL> select * from employees; EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY ---- ---------- ---------- -------------------- ---------- ---------- 1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai 2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai 3 smith james 12-dec-1978 00:00:00 7897.78 kolkata 5 ashok kanna 01-jan-2009 00:00:00 5000.35 madurai 4 arun shanmug 01-feb-2011 00:00:00 3000.33 chennai SQL> delete from employees where emp_id in (4,5); 2 rows deleted. SQL> commit; Commit complete.   No problem until here. Now, let's delete the GROUP# 3 log members, so that the next log switch will run into trouble here.   $ cd /u02/oradata/dbuat01/redo $ rm -rf redo_g3_m1.log redo_g3_m2.log SQL> conn / as sysdba SQL> alter system archive log current; System altered. SQL> select group#, sequence#, members, status, first_change#, next_change# from v$log; GROUP# SEQUENCE# MEMBERS STATUS FIRST_CHANGE# NEXT_CHANGE# ---------- ---------- ---------- ---------------- ------------- ------------ 1 19 2 INACTIVE 363178 363192 2 20 2 ACTIVE 363192 363400 3 21 2 CURRENT 363400 2.8147E+14   Good! Now, we should face some problem here after another switch.   SQL> alter system archive log current; alter system archive log current * ERROR at line 1: ORA-16038: log 3 sequence# 21 cannot be archived ORA-00312: online log 3 thread 1: '/u02/oradata/dbuat01/redo/redo_g3_m1.log' ORA-00312: online log 3 thread 1: '/u02/oradata/dbuat01/redo/redo_g3_m2.log' SQL> shut abort # rman target / [ ----------------------------------< code - expandable on mouseover >----------------------------------- ] RMAN> startup nomount; RMAN> restore controlfile from '/u02/oradata/backup/dbuat01/rman/dbuat01_c-2689589391-20120711-03'; Starting restore at 11-JUL-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=134 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u02/oradata/dbuat01/control/control01.ctl output file name=/u02/oradata/dbuat01/control/control02.ctl output file name=/u02/oradata/dbuat01/control/control03.ctl Finished restore at 11-JUL-12 RMAN> alter database mount; RMAN> run { 2> restore database; 3> recover database; 4> } Starting restore at 11-JUL-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=134 device type=DISK 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 00004 to /u02/oradata/dbuat01/data/tbs_users_01.dbf channel ORA_DISK_1: restoring datafile 00005 to /u02/oradata/dbuat01/data/ts_arun_01.dbf channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788357184_4unfqo20_158_1 channel ORA_DISK_1: piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788357184_4unfqo20_158_1 tag=TAG20120711T120623 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 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 00001 to /u02/oradata/dbuat01/data/tbs_system_01.dbf channel ORA_DISK_1: restoring datafile 00002 to /u02/oradata/dbuat01/data/tbs_sysaux_01.dbf channel ORA_DISK_1: restoring datafile 00003 to /u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788357184_4vnfqo20_159_1 channel ORA_DISK_1: piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788357184_4vnfqo20_159_1 tag=TAG20120711T120623 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:35 Finished restore at 11-JUL-12 Starting recover at 11-JUL-12 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 19 is already on disk as file /u02/oradata/dbuat01/archived_logs/dbuat01_1_19_788356652.arc archived log for thread 1 with sequence 20 is already on disk as file /u02/oradata/dbuat01/redo/redo_g2_m1.log archived log for thread 1 with sequence 22 is already on disk as file /u02/oradata/dbuat01/redo/redo_g1_m1.log archived log file name=/u02/oradata/dbuat01/archived_logs/dbuat01_1_19_788356652.arc thread=1 sequence=19 archived log file name=/u02/oradata/dbuat01/redo/redo_g2_m1.log thread=1 sequence=20 unable to find archived log archived log thread=1 sequence=21 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 07/11/2012 12:21:16 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 21 and starting SCN of 363400   Note: As you can see, log group# 3 not applied anywhere during the recovery. Because, log group# 3 is not physically present. It will be recreated only while opening the database using RESETLOGS.   RMAN> alter database open resetlogs; Database altered. SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------- --- 1 ONLINE /u02/oradata/dbuat01/redo/redo_g1_m1.log NO 1 ONLINE /u02/oradata/dbuat01/redo/redo_g1_m2.log NO 2 ONLINE /u02/oradata/dbuat01/redo/redo_g2_m1.log NO 2 ONLINE /u02/oradata/dbuat01/redo/redo_g2_m2.log NO 3 ONLINE /u02/oradata/dbuat01/redo/redo_g3_m1.log NO 3 ONLINE /u02/oradata/dbuat01/redo/redo_g3_m2.log NO SQL> select group#, sequence#, members, status, first_change#, next_change# from v$log; GROUP# SEQUENCE# MEMBERS STATUS FIRST_CHANGE# NEXT_CHANGE# ---------- ---------- ---------- ---------------- ------------- ------------ 1 1 2 CURRENT 363401 2.8147E+14 2 0 2 UNUSED 0 0 3 0 2 UNUSED 0 0     12. Loss of one Controlfile when Controlfiles are multiplexed     i) Let's grab V$CONTROLFILE;   SQL> select * from v$controlfile; STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS ------- ------------------------------------------------------- --- ---------- -------------- /u02/oradata/dbuat01/control/control01.ctl NO 16384 608 /u02/oradata/dbuat01/control/control02.ctl NO 16384 608 /u02/oradata/dbuat01/control/control03.ctl NO 16384 608     ii) Let's delete one of the multiplexed copies.   $ rm -f /u02/oradata/dbuat01/control/control02.ctl SQL> select * from v$datafile; select * from v$datafile * ERROR at line 1: ORA-00210: cannot open the specified control file ORA-00202: control file: '/u02/oradata/dbuat01/control/control02.ctl' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3     iii) Let's make a copy from the multiplexed copies   $ cp -p /u02/oradata/dbuat01/control/control01.ctl /u02/oradata/dbuat01/control/control02.ctl SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u02/oradata/dbuat01/data/tbs_system_01.dbf /u02/oradata/dbuat01/data/tbs_sysaux_01.dbf /u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf /u02/oradata/dbuat01/data/tbs_users_01.dbf /u02/oradata/dbuat01/data/ts_arun_01.dbf   It should be fine now!     13. Loss of all Controlfiles     i) Let's grab V$CONTROLFILE;   SQL> select * from v$controlfile; STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS ------- ------------------------------------------------------- --- ---------- -------------- /u02/oradata/dbuat01/control/control01.ctl NO 16384 608 /u02/oradata/dbuat01/control/control02.ctl NO 16384 608 /u02/oradata/dbuat01/control/control03.ctl NO 16384 608     ii) Let's delete all the multiplexed copies.   $ rm -f /u02/oradata/dbuat01/control/*ctl SQL> select * from v$datafile; select * from v$datafile * ERROR at line 1: ORA-00210: cannot open the specified control file ORA-00202: control file: '/u02/oradata/dbuat01/control/control02.ctl' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 $ rman target / [ ----------------------------------< code - expandable on mouseover >----------------------------------- ] RMAN> startup nomount; RMAN> restore controlfile from '/u02/oradata/backup/dbuat01/rman/dbuat01_c-2689589391-20120711-04'; Starting restore at 11-JUL-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=134 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u02/oradata/dbuat01/control/control01.ctl output file name=/u02/oradata/dbuat01/control/control02.ctl output file name=/u02/oradata/dbuat01/control/control03.ctl Finished restore at 11-JUL-12 RMAN> run { 2> alter database mount; 3> restore database; 4> recover database; 5> } database mounted released channel: ORA_DISK_1 Starting restore at 11-JUL-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=134 device type=DISK 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 00004 to /u02/oradata/dbuat01/data/tbs_users_01.dbf channel ORA_DISK_1: restoring datafile 00005 to /u02/oradata/dbuat01/data/ts_arun_01.dbf channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788357184_4unfqo20_158_1 channel ORA_DISK_1: piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788357184_4unfqo20_158_1 tag=TAG20120711T120623 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 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 00001 to /u02/oradata/dbuat01/data/tbs_system_01.dbf channel ORA_DISK_1: restoring datafile 00002 to /u02/oradata/dbuat01/data/tbs_sysaux_01.dbf channel ORA_DISK_1: restoring datafile 00003 to /u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788357184_4vnfqo20_159_1 channel ORA_DISK_1: piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788357184_4vnfqo20_159_1 tag=TAG20120711T120623 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:35 Finished restore at 11-JUL-12 Starting recover at 11-JUL-12 using channel ORA_DISK_1 starting media recovery archived log file name=/u02/oradata/dbuat01/archived_logs/dbuat01_1_33_788358144.arc thread=1 sequence=33 archived log file name=/u02/oradata/dbuat01/archived_logs/dbuat01_1_34_788358144.arc thread=1 sequence=34 archived log file name=/u02/oradata/dbuat01/archived_logs/dbuat01_1_35_788358144.arc thread=1 sequence=35 archived log file name=/u02/oradata/dbuat01/redo/redo_g3_m1.log thread=1 sequence=36 archived log file name=/u02/oradata/dbuat01/redo/redo_g1_m1.log thread=1 sequence=37 archived log file name=/u02/oradata/dbuat01/redo/redo_g2_m1.log thread=1 sequence=38 media recovery complete, elapsed time: 00:00:06 Finished recover at 11-JUL-12 RMAN> alter database open resetlogs; Database opened.     14. Recreating Controlfile from trace   Controlfiles may be recreated only under two situations. One is when you have lost all your controlfiles and no backup is available. And the second is to rename a database (DB_NAME). Now, let's create a controlfile trace.   i. Creating a controlfile trace file   $ sqlplus / as sysdba SQL> alter database backup controlfile to trace '/u02/oradata/backup/dbuat01/rman/dbuat01_controlfile_trace.sql'; Database altered.     Now, we must make some changes in the trace file using 'vi' editor. it should look like the one shown below after removing all unwanted comments on the top and bottom of the trace file:   CREATE CONTROLFILE REUSE DATABASE "DBUAT01" RESETLOGS ARCHIVELOG MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '/u02/oradata/dbuat01/redo/redo_g1_m1.log', '/u02/oradata/dbuat01/redo/redo_g1_m2.log' ) SIZE 100M BLOCKSIZE 512, GROUP 2 ( '/u02/oradata/dbuat01/redo/redo_g2_m1.log', '/u02/oradata/dbuat01/redo/redo_g2_m2.log' ) SIZE 100M BLOCKSIZE 512, GROUP 3 ( '/u02/oradata/dbuat01/redo/redo_g3_m1.log', '/u02/oradata/dbuat01/redo/redo_g3_m2.log' ) SIZE 100M BLOCKSIZE 512 DATAFILE '/u02/oradata/dbuat01/data/tbs_system_01.dbf', '/u02/oradata/dbuat01/data/tbs_sysaux_01.dbf', '/u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf', '/u02/oradata/dbuat01/data/tbs_users_01.dbf', '/u02/oradata/dbuat01/data/ts_arun_01.dbf' CHARACTER SET US7ASCII ;     ii) Let's create a scenario by forcefully removing all the controlfiles.   $ rm -f /u02/oradata/dbuat01/control/*ctl     iii) Let's include the following undocumented parameter in the init.ora. Otherwise, you will not be able to open the database using 'RESETLOGS'   _allow_resetlogs_corruption=true     iv) Shut abort the database and startup again using the init.ora   SQL> shut abort SQL> startup nomount pfile=$ORACLE_HOME/dbs/initdbuat01.ora SQL> @/u02/oradata/backup/dbuat01/rman/dbuat01_controlfile_trace.sql Control file created. SQL> alter database open resetlogs; Database altered.   Shut down the database and restart again. Take a full backup!     [ Top ]   Last modified: Jul 05, 2012 11:45 IST