How to perform a SCN-based incomplete recovery

Tweet

When you don't know the date and time to which you want to recover the database, SCN-based incomplete recovery could be taken into consideration especially if you are able to retrieve a particular SCN.
In this scenario I will perform a SCN-based incomplete recovery after I have used the Oracle LogMiner utility to retrieve a useful SCN where to stop my recovery process.
Before starting the recovery process I want to be sure a valid backup is available.

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Apr 23 07:42:39 2013

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

connected to target database: ORCL (DBID=1229390655)

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
257     B  A  A DISK        22-04-2013 21:33:38 1       1       YES        TAG20130422T213335
258     B  F  A DISK        22-04-2013 21:45:34 1       1       YES        TAG20130422T213339
259     B  A  A DISK        22-04-2013 21:45:38 1       1       YES        TAG20130422T214537
260     B  F  A DISK        22-04-2013 21:45:41 1       1       NO         TAG20130422T214539
261     B  F  A DISK        23-04-2013 05:02:30 1       1       NO         TAG20130423T050224
262     B  F  A DISK        23-04-2013 06:17:30 1       1       NO         TAG20130423T061726

Now I'm going to execute few sql statements to modify my database and to know the SCN at which those operations were performed.
Lukely in a normal situation you don't have to get every time the current SCN after each DML operations: but in this scenario these values will be useful because I want to compare them with those returned by the LogMiner utility when I need to recover the database.
So approximately at SCN equals to 14928918 my database has two tables with few rows into my schema.

SQL> select count(*) from marcov.t1;

  COUNT(*)
----------
      2000

SQL> select count(*) from marcov.t2;

  COUNT(*)
----------
      4000

SQL> select dbms_flashback.get_system_change_number scn from dual;

       SCN
----------
  14928918

When SCN is equals to 14928987 in the same schema I have created another table and inserted few rows.

SQL> create table marcov.new_deployment (a number);

Table created.

SQL> insert into marcov.new_deployment select * from marcov.t1;

2000 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from marcov.new_deployment;

  COUNT(*)
----------
      2000

SQL> select dbms_flashback.get_system_change_number scn from dual;

       SCN
----------
  14928987

At SCN number 14929031 my recently created table no more exists because it was "inadvertently" dropped:

SQL> drop table marcov.new_deployment purge;

Table dropped.

SQL> select count(*) from marcov.new_deployment;
select count(*) from marcov.new_deployment
                            *
ERROR at line 1:

-- SCN of reference
SQL> select dbms_flashback.get_system_change_number scn from dual;

       SCN
----------
  14929031

I want to perform an incomplete recovery and stop it just before the moment at which someone dropped my table.
I know someone dropped my table so I can use the LogMiner utility and look at every operation performed on my table.
First of all I need to specify which redo log (online or archived of course) should be analyzed.
I was alerted as soon as the table was accidentally dropped so I would try to start my redo log analysis from the CURRENT one.
If you want to analyze more redo logs at the same time you can use the DBMS_LOGMNR.ADDFILE option into the DBMS_LOGMNR.ADD_LOGFILE procedure or start the LogMiner using DBMS_LOGMNR.START_LOGMNR with the CONTINUOUS_MINE as option.
Let me see which redo log is the CURRENT.

SQL> select member from v$logfile a, v$log b where a.group#=b.group# and b.status = 'CURRENT';

MEMBER
------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/redo02.log
/home/oracle/app/oracle/oradata/orcl/redo02b.log

Before starting a LogMiner session you have to add the redo log file you want to analyze using the DBMS_LOGMRN.ADD_LOGFILE procedure. In my case I want to add my CURRENT redo log file.

SQL> exec dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oracle/oradata/orcl/redo02.log', options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

Then you can start your LogMiner session specifying to use the dictionary currently in use in the database.

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

After you have successfully executed the DBMS_LOGMRN.START_LOGMNR, the v$logmnr_contents view will contain the redo data of interest.
To query the v$logmnr_contents view your user must have the SELECT ANY TRANSACTION privilege.
In the following statement I want to know the operations occurred on my dropped table, when they were performed, the associated SCN and the statement executed.

SQL> col sql_redo format a60
SQL> col ts format a30
SQL> select scn, to_char(timestamp, 'dd/mm/yyyy hh24:mi:ss') ts, operation, sql_redo from v$logmnr_contents where table_name = 'NEW_DEPLOYMENT' order by ts;

       SCN TS                             OPERATION                        SQL_REDO
---------- ------------------------------ -------------------------------- ------------------------------------------------------------
  14928965 23/04/2013 22:16:49            DDL                              create table marcov.new_deployment (a number);
  14929007 23/04/2013 22:17:54            DDL                              drop table marcov.new_deployment purge;

As you can see my table was created when SCN was equal to 14928965 and dropped when it was 14929007.
These values are logically consistent of course with the previous calculated SCN using the DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER procedure: I obtained a SCN equals to 14928987 after the table was created (14928965 from v$logmnr_contents view), but still not dropped and equals to 14929031 few seconds later the table was dropped (14929007 from v$logmnr_contents view). I have all the necessary information so I can close the LogMiner session and perform a SCN-based incomplete recovery just one step before the SCN be equal to 14929007.

SQL> exec dbms_logmnr.end_logmnr();

PL/SQL procedure successfully completed.

To perform an incomplete recovery the database must be in MOUNT mode.

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                373295800 bytes
Database Buffers              75497472 bytes
Redo Buffers                   6008832 bytes

In a previous post related on time-based incomplete recovery I issued a series of single RMAN commands: now I want to use another syntax using the set until ... option (set until scn in this case) and a run { ... } block.

RMAN> run {
2> set until scn 14929007;
3> restore database;
4> recover database;
5> alter database open resetlogs;
6> }

executing command: SET until clause

Starting restore at 23-04-2013 22:22:13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 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:04:32
Finished restore at 23-04-2013 22:26:46

Starting recover at 23-04-2013 22:26:47
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
archived log for thread 1 with sequence 1 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_23/o1_mf_1_1_8qf0kqy2_.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:01
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_23/o1_mf_1_48_8qgv8t7p_.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_23/o1_mf_1_48_8qgv8t7p_.arc RECID=278 STAMP=813536810
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
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_50_8qd6ckq5_.arc thread=1 sequence=50
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_23/o1_mf_1_1_8qf0kqy2_.arc thread=1 sequence=1
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_23/o1_mf_1_1_8qgs3znf_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:25
Finished recover at 23-04-2013 22:27:16

database opened

As you can see my table is now available again.

SQL> select count(*) from marcov.new_deployment;

  COUNT(*)
----------
      2000

That's all.