Every incomplete recovery is followed by a resetlogs command before opening the database: each time you use a resetlogs command, a new incarnation of the database is created.
While performing incomplete recovery it should be known the target point at which the recovery process needs to terminate: there are several ways to establish and set this recovery point.
Today I’m going to use a time based incomplete recovery, generally used when it’s known the time to which you want to stop the recovery process: in the next posts I will describe scenarios on log-sequence, SCN and cancel-based incomplete recovery.
Let’s first take a backup of the database.
[oracle@localhost ~]$ rman target / Recovery Manager: Release 11.2.0.2.0 - Production on Mon Apr 22 21:32:05 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1229390655) RMAN> backup database plus archivelog delete all input; Starting backup at 22-04-2013 21:33:34 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=47 RECID=265 STAMP=813447215 channel ORA_DISK_1: starting piece 1 at 22-04-2013 21:33:35 channel ORA_DISK_1: finished piece 1 at 22-04-2013 21:33:38 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_22/o1_mf_annnn_TAG20130422T213335_8qd3rzng_.bkp tag=TAG20130422T213335 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 channel ORA_DISK_1: deleting archived log(s) archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_47_8qd3ryt3_.arc RECID=265 STAMP=813447215 Finished backup at 22-04-2013 21:33:38 Starting backup at 22-04-2013 21:33:39 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf input datafile file number=00006 name=/home/oracle/app/oracle/oradata/orcl/APEX.dbf input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf input datafile file number=00007 name=/home/oracle/app/oracle/oradata/orcl/read_only01.dbf input datafile file number=00008 name=/home/oracle/app/oracle/oradata/orcl/ZZZ01.dbf input datafile file number=00009 name=/home/oracle/app/oracle/oradata/orcl/example02.dbf input datafile file number=00010 name=/home/oracle/app/oracle/oradata/orcl/APEX02.dbf input datafile file number=00012 name=/home/oracle/app/oracle/oradata/orcl/test01.dbf channel ORA_DISK_1: starting piece 1 at 22-04-2013 21:33:39 channel ORA_DISK_1: finished piece 1 at 22-04-2013 21:45:36 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_22/o1_mf_nnndf_TAG20130422T213339_8qd3s6fx_.bkp tag=TAG20130422T213339 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:11:57 Finished backup at 22-04-2013 21:45:36 Starting backup at 22-04-2013 21:45:37 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=48 RECID=266 STAMP=813447937 channel ORA_DISK_1: starting piece 1 at 22-04-2013 21:45:38 channel ORA_DISK_1: finished piece 1 at 22-04-2013 21:45:39 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_22/o1_mf_annnn_TAG20130422T214537_8qd4hl7n_.bkp tag=TAG20130422T214537 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_48_8qd4hkl5_.arc RECID=266 STAMP=813447937 Finished backup at 22-04-2013 21:45:39 Starting Control File and SPFILE Autobackup at 22-04-2013 21:45:39 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2013_04_22/o1_mf_s_813447939_8qd4hndz_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 22-04-2013 21:45:42
The backup finished at 21:45:39. Now I’m going to simulate some activities on the database.
SQL> desc marcov.t1; Name Null? Type ------------------- -------- ---------------------------- A NUMBER SQL> insert into marcov.t1 select level from dual connect by level<1001; 1000 rows created. SQL> create table marcov.t2 (b number); Table created. SQL> insert into marcov.t2 select level from dual connect by level<2001; 2000 rows created. SQL> commit; Commit complete. SQL> alter system switch logfile; System altered. SQL> insert into marcov.t2 select level from dual connect by level<2001; 2000 rows created. SQL> insert into marcov.t1 select level from dual connect by level<1001; 1000 rows created. SQL> commit; Commit complete.
At 22:12:53 a new software version is deployed into the database.
SQL> select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') TIME from dual; TIME ------------------- 22/04/2013 22:12:53 SQL> create table marcov.new_deployment_t1 (a number); Table created. SQL> create table marcov.new_deployment_t2 (b number); Table created. SQL> select count(*) from marcov.t1; COUNT(*) ---------- 2000 SQL> select count(*) from marcov.t2; COUNT(*) ---------- 4000 SQL> insert into marcov.new_deployment_t1 select * from marcov.t1; 2000 rows created. SQL> commit; Commit complete. SQL> insert into marcov.new_deployment_t2 select * from marcov.t2; 4000 rows created. SQL> commit; Commit complete. SQL> alter system switch logfile; System altered. SQL> truncate table marcov.t1; Table truncated. SQL> truncate table marcov.t2; Table truncated. SQL> alter system switch logfile; System altered. SQL> update marcov.new_deployment_t1 set a=0 where a>1000; 1000 rows updated. SQL> update marcov.new_deployment_t2 set b=0 where b>1000; 5334 rows updated. SQL> commit; Commit complete.
No real users are still connected to the database: only few people working in the development department are finishing to conduct few regression tests and only when they confirm every thing is working as expected the database could be open to every real users again.
Unfortunately during tests, after 10 minutes they discover a bug has been introduced and they want to rollback all the committed transactions occurred in that short period.
They want to rewind the database just like it was at 22:12:53 time.
To proceed with this operation the database should be in mount mode.
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. [oracle@localhost ~]$ rman target / Recovery Manager: Release 11.2.0.2.0 - Production on Tue Apr 22 22:24:18 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup mount; Oracle instance started database mounted Total System Global Area 456146944 bytes Fixed Size 1344840 bytes Variable Size 360712888 bytes Database Buffers 88080384 bytes Redo Buffers 6008832 bytes
I know exactly when my restore operation has to finish, so I can use the restore … until time option:
RMAN> restore database until time "to_date('22/04/2013 22:12:53','DD/MM/YYYY HH24:MI:SS')";
Starting restore at 22-04-2013 22:26:22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
skipping datafile 7; already restored to file /home/oracle/app/oracle/oradata/orcl/read_only01.dbf
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 /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/app/oracle/oradata/orcl/APEX.dbf
channel ORA_DISK_1: restoring datafile 00008 to /home/oracle/app/oracle/oradata/orcl/ZZZ01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /home/oracle/app/oracle/oradata/orcl/example02.dbf
channel ORA_DISK_1: restoring datafile 00010 to /home/oracle/app/oracle/oradata/orcl/APEX02.dbf
channel ORA_DISK_1: restoring datafile 00011 to /home/oracle/app/oracle/oradata/orcl/marcov01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /home/oracle/app/oracle/oradata/orcl/test01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_22/o1_mf_nnndf_TAG20130422T213339_8qd3s6fx_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_22/o1_mf_nnndf_TAG20130422T213339_8qd3s6fx_.bkp tag=TAG20130422T213339
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:07:19
Finished restore at 22-04-2013 22:33:43
I have issue the recover command using again the until time option:
RMAN> recover database until time "to_date('22/04/2013 22:12:53','DD/MM/YYYY HH24:MI:SS')";
Starting recover at 22-04-2013 22:35:39
using channel ORA_DISK_1
datafile 7 not processed because file is read-only
starting media recovery
archived log for thread 1 with sequence 49 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_49_8qd5pd13_.arc
archived log for thread 1 with sequence 50 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_50_8qd6ckq5_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=48
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_22/o1_mf_annnn_TAG20130422T214537_8qd4hl7n_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_22/o1_mf_annnn_TAG20130422T214537_8qd4hl7n_.bkp tag=TAG20130422T214537
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_48_8qdxzf75_.arc thread=1 sequence=48
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_48_8qdxzf75_.arc RECID=270 STAMP=813474045
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_49_8qd5pd13_.arc thread=1 sequence=49
media recovery complete, elapsed time: 00:00:18
Finished recover at 22-04-2013 22:36:04
Every incomplete recovery needs to be followed by a resetlogs:
RMAN> alter database open resetlogs; database opened
So the database is now available again.
Let’s see if my data are still there just like before inserting new values for the deployment:
SQL> select count(*) from marcov.t1; COUNT(*) ---------- 2000 SQL> select count(*) from marcov.t2; COUNT(*) ---------- 4000
Of course also the new tables are no more available.
SQL> select count(*) from marcov.new_deployment_t1; select count(*) from marcov.new_deployment_t1 * ERROR at line 1: ORA-00942: table or view does not exist SQL> select count(*) from marcov.new_deployment_t2; select count(*) from marcov.new_deployment_t2 * ERROR at line 1: ORA-00942: table or view does not exist
Thank you for giving your valuable time to read the above information.
For More Detail , You can join us follow:
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
