An example for Incomplete recovery through rman

An incomplete recovery is a type of recovery in which only a part of redo log , archive log data are applied to a restored backup in order to bring it to a specified time, sequence number or SCN prior to the failure. You will lose all works done after that specified time.You must run the database in ARCHIVELOG mode in order to perform an incomplete recovery.

The incomplete recovery consists of four steps:

1. Mount the database.
2. Restore all the datafiles.
3. Recover the database until the desired point.
4. Open the database with a resetlogs.

You can use the incomplete recovery with:

1- User managed backups: in this case you have three options: UNTIL TIME Recovery (before a specific time), UNTIL CANCEL recovery (before the one specified corrupted, missed archive log or redo log file), UNTIL CHANGE Recovery (before the one specified SCN or system change number).

2- RMAN backups: in this case you have three options: UNTIL TIME Recovery (before a specific time), UNTIL SEQUENCE recovery (before the one specified corrupted, missed archive log or redo log file), UNTIL SCN Recovery (before the one specified SCN or system change number).

Practical example:-

First be sure that the database is in ARCHIVELOG mode. Then we will create a table called test_incomplete_rec to test the incomplete recovery.

C:\Users\Admin>set ORACLE_SID=craze

C:\Users\Admin>sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 23 15:13:40 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select archiver from v$instance;

ARCHIVE

-------

STARTED

SQL> set time on

15:13:55 SQL> create table scott.test_incomplete_Reco as select * from dba_Tables;

Table created.

15:14:03 SQL>

Now take a full rman database backup ,

C:\Users\Admin>set ORACLE_SID=craze

C:\Users\Admin>rman target sys/sys

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Jan 23 15:14:26 2013

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

connected to target database: CRAZE (DBID=781012619)

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\DATABASE\SNCFCRAZE.ORA'; # default

Note:- Here rman having the default configuration , i changed nothing and the backup set will goes to $ORACLE_HOME/database folder.

RMAN>  backup database;

Starting backup at 23-JAN-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=134 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=D:\DATA\CRAZE\SYSTEM01.DBF

input datafile fno=00003 name=D:\DATA\CRAZE\SYSAUX01.DBF

input datafile fno=00002 name=D:\DATA\CRAZE\UNDOTBS01.DBF

input datafile fno=00005 name=D:\DATA\CRAZE\EXAMPLE01.DBF

input datafile fno=00004 name=D:\DATA\CRAZE\USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 23-JAN-13

channel ORA_DISK_1: finished piece 1 at 23-JAN-13

piece handle=D:\ORACLE\DATABASE\01O055C8_1_1 tag=TAG20130123T151520 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 23-JAN-13

channel ORA_DISK_1: finished piece 1 at 23-JAN-13

piece handle=D:\ORACLE\DATABASE\02O055D2_1_1 tag=TAG20130123T151520 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 23-JAN-13

RMAN>

Now drop the table , 

15:17:01 SQL> drop table scott.test_incomplete_Reco;

Table dropped.

Note:- The table got dropped at 15:17:01 

15:17:15 SQL> select * from scott.test_incomplete_Reco;

select * from scott.test_incomplete_Reco

 *ERROR at line 1:

ORA-00942: table or view does not exist

15:17:22 SQL>

Now perform an incomplete recovery through rman ,

C:\Users\Admin>rman target sys/sys

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Jan 23 15:21:13 2013

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

connected to target database: CRAZE (DBID=781012619)

RMAN> run

2> {

3> shutdown immediate;

4> startup mount;

5> sql "alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss'' ";

6> set until time '2013-01-23 15:16:00';

7> restore database;

8> recover database;

9> alter database open resetlogs;

10> }

using target database control file instead of recovery catalog

database closed

database dismounted

Oracle instance shut down

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area     931135488 bytes

Fixed Size                     2070096 bytes

Variable Size                234883504 bytes

Database Buffers             687865856 bytes

Redo Buffers                   6316032 bytes

sql statement: alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''

executing command: SET until clause

Starting restore at 23-JAN-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to D:\DATA\CRAZE\SYSTEM01.DBF

restoring datafile 00002 to D:\DATA\CRAZE\UNDOTBS01.DBF

restoring datafile 00003 to D:\DATA\CRAZE\SYSAUX01.DBF

restoring datafile 00004 to D:\DATA\CRAZE\USERS01.DBF

restoring datafile 00005 to D:\DATA\CRAZE\EXAMPLE01.DBF

channel ORA_DISK_1: reading from backup piece D:\ORACLE\DATABASE\01O055C8_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=D:\ORACLE\DATABASE\01O055C8_1_1 tag=TAG20130123T151520

channel ORA_DISK_1: restore complete, elapsed time: 00:00:26

Finished restore at 23-JAN-13

Starting recover at 23-JAN-13

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:05

Finished recover at 23-JAN-13

database opened

RMAN>

Database has been opened in resetlogs and we can check the existence of the table, 

15:24:58 SQL> select count(1) from scott.test_incomplete_Reco;

 COUNT(1)

----------

      1594

15:25:31 SQL>