4.5. Performing an Incomplete Recovery

Incomplete recovery is a recovery that stops before the failure that forced the recovery. Another way of looking at incomplete recovery is that not all the transactions in the archived redo logs get applied to the database to make the database complete. With incomplete recovery, after the recovery process has ended, the database is still missing transactions that were in the database before the failure.

Incomplete recovery is sometimes called database point-in-time recovery (DBPITR) because this recovery is to a determined point-in-time.

RMAN incomplete recovery is performed by using the SET UNTIL TIME and SET UNTIL SEQUENCE clauses prior to the RECOVER command or the UNTIL TIME and UNTIL SEQUENCE clauses specified with the RECOVER commands. These clauses direct the recovery process to stop at a designated time, a redo log sequence, or a system change number (SCN) before full recovery is completed.

User-managed incomplete recovery is performed by using the RECOVER DATABASE command in conjunction with the UNTIL TIME, UNTIL CHANGE, or UNTIL CANCEL clauses. These have the same effect as the SET UNTIL clauses in RMAN, with the exception of the UNTIL CANCEL clause. The UNTIL CANCEL clause is designed to just stop the recovery process at a random point.

Table 4.1 describes the different types of incomplete recovery, and situations that might be best suited for their use for both RMAN and user-managed recovery operations.

Table 4.1. Incomplete Recovery Types and Uses
Recovery TypeClause or CommandUsage
RMANUNTIL TIMEStop recovery before a known time that introduces corruption in the database or some undesired event in the database that cannot be rolled back.
User-managedUNTIL TIMEStop recovery before a known time that introduces corruption in the database or some undesired event in the database that cannot be rolled back.
RMANUNTIL SEQUENCEStop before a known redo log sequence that introduces corruption or some undesired event in the database that cannot be rolled back.
RMANUNTIL SCNStop before a known SCN that introduces corruption or some undesired event in the database that cannot be rolled back. The SCN can be a more finite stopping point than a time or redo log sequence because it is by transaction.
User-managedUNTIL CHANGEStop before a SCN that introduces corruption or some undesired event in the database that cannot be rolled back. The SCN can be a more finite stopping point than a time or redo log sequence because it is by transaction.
User-managedUNTIL CANCELStop when administrator issues the CANCEL command. This is good for making a test database from backup, where the transactional stopping point is not important to the validity of the database.

Now that we have a high-level understanding of RMAN-based and user-managed incomplete recovery, we will take a look at RMAN-based incomplete recovery in more detail in the next section. RMAN-based incomplete recovery is very similar to user-managed incomplete recovery. RMAN-based incomplete recovery doesn't have a CANCEL-based option; however, RMAN-based incomplete recovery has SCN and SEQUENCE methods.

4.5.1. RMAN Incomplete Recovery

RMAN incomplete recovery can be performed by time, redo log sequence, or SCN. To perform an incomplete recovery, use the RECOVER DATABASE command with the UNTIL TIME, SCN, or SEQUENCE clause, or use the SET UNTIL clause prior to the RECOVER DATABASE command.

This section walks you through the steps for performing a time- and sequence-based incomplete recovery. Let's do a time-based recovery first:

  1. Make sure the target database is started in MOUNT mode:

    SQL> startup mount
ORACLE instance started.

Total System Global Area    88080384 bytes
Fixed Size                    787588 bytes
Variable Size               78642044 bytes
Database Buffers             8388608 bytes
Redo Buffers                  262144 bytes
Database mounted.

  2. Make sure NLS_DATE_FORMAT is set to a value that you can reproduce in RMAN:

    C:> set NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS

  3. Recover the database using the SET UNTIL TIME clause.

    RMAN> run
2> {
3> set until time '06-SEP-2004 11:25:00';
4> restore database;
5> recover database;
6> }

executing command: SET until clause

Starting restore at 06-SEP-2004 11:50:05 using
channel ORA_DISK_1


channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:ORACLEORADATAORA101TSYSTEM01.DBF
restoring datafile 00002 to C:ORACLEORADATAORA101TUNDOTBS01.DBF
restoring datafile 00003 to C:ORACLEORADATAORA101TSYSAUX01.DBF
restoring datafile 00004 to C:ORACLEORADATAORA101TUSERS01.DBF
restoring datafile 00005 to C:ORACLEORADATAORA101TEXAMPLE01.DBF
restoring datafile 00006 to C:ORACLEORADATAORA101TINDEX01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=
  
    C:ORACLEFLASH_RECOVERY_AREAORA101TORA101TBACKUPSET
  
    2004_09_06O1_MF_NNNDF_TAG20040906T111843_0MSBJ797_.BKP
  
    tag=TAG20040906T111843
channel ORA_DISK_1: restore complete
Finished restore at 06-SEP-2004 11:51:44

Starting recover at 06-SEP-2004 11:51:45
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 9 is already on disk as file
  
    C:ORACLEORAARCHORA101TARC00009_0535986437.001
archive log thread 1 sequence 10 is already on disk as file
  
    C:ORACLEORAARCHORA101TARC00010_0535986437.001
archive log filename=
  
    C:ORACLEORAARCHORA101TARC00009_0535986437.001 thread=1

    sequence=9
archive log filename=
  
    C:ORACLEORAARCHORA101TARC00010_0535986437.001 thread=1
sequence=10
media recovery complete
Finished recover at 06-SEP-2004 11:52:00

RMAN>

  4. Next, open the database with the RESETLOGS clause:

    RMAN> alter database open resetlogs;

database opened

RMAN>

Next, let's walk through a specific example of using the sequence-based recovery, which uses a redo log sequence number to terminate the recovery process. There are some steps required to identify the redo log sequence number that require accessing the V$REDO_LOG_HISTORY dynamic view:

  1. From the V$LOG_HISTORY table, get the sequence and thread information. In this case, you will recover up to sequence 3 and thread 1. These values are retrieved from the row with RECID 16:

    SQL> select * from v$log_history;

RECID STAMP     THRD# SEQ# FIR_CHNG FIRST_TIM N_CHNG S_CHNG RESETLOG
----- --------- ----- ---- -------- --------- ------ ------ --------
14    536155296 1     1    562594   06-SEP-04 563149 562594 06-SEP-04
15    536155297 1     2    563149   06-SEP-04 563151 562594 06-SEP-04
16    536155302 1     3    563151   06-SEP-04 563154 562594 06-SEP-04
17    536155404 1     4    563154   06-SEP-04 563199 562594 06-SEP-04

  2. Start the database in MOUNT mode:

    SQL> startup mount
ORACLE instance started.

Total System Global Area    88080384 bytes
Fixed Size                    787588 bytes
Variable Size               78642044 bytes
Database Buffers             8388608 bytes
Redo Buffers                  262144 bytes
Database mounted.

  3. Recover the database using the UNTIL SEQUENCE clause. Note that this will recover log sequences before 3, which is log sequence 1 and 2:

    RMAN> run
2> {
3> set until sequence 3 thread 1;
4> restore database;
5> recover database;
6> }

executing command: SET until clause

Starting restore at 06-SEP-2004 13:22:07
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:ORACLEORADATAORA101TSYSTEM01.DBF
restoring datafile 00002 to C:ORACLEORADATAORA101TUNDOTBS01.DBF
restoring datafile 00003 to C:ORACLEORADATAORA101TSYSAUX01.DBF
restoring datafile 00004 to C:ORACLEORADATAORA101TUSERS01.DBF
restoring datafile 00005 to C:ORACLEORADATAORA101TEXAMPLE01.DBF
restoring datafile 00006 to C:ORACLEORADATAORA101TINDEX01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=
  
    C:ORACLEFLASH_RECOVERY_AREAORA101TORA101TBACKUPSET
  
    2004_09_06O1_MF_NNNDF_TAG20040906T111843_0MSBJ797_.BKP tag=TAG20040906T111843
channel ORA_DISK_1: restore complete
Finished restore at 06-SEP-2004 13:23:46

Starting recover at 06-SEP-2004 13:23:46
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 1 is already on disk as file
  
    C:ORACLEORAARCHORA101TARC00001_0536154821.001
archive log thread 1 sequence 2 is already on disk as file
  
    C:ORACLEORAARCHORA101TARC00002_0536154821.001
archive log filename=
  
    C:ORACLEORAARCHORA101TARC00001_0536154821.001 thread=1

    sequence=1
archive log filename=
  
    C:ORACLEORAARCHORA101TARC00002_0536154821.001 thread=1
sequence=2
media recovery complete
Finished recover at 06-SEP-2004 13:24:05

RMAN>

  4. Open the database with the RESETLOGS clause:

    RMAN> alter database open resetlogs;

database opened

RMAN>

Real World Scenarlo: Using Incomplete Recovery to Move a Database

Recovery operations can be used as tools to perform activities other the recovery from failure. For this reason, you (as the DBA) need to be familiar with the backup and recovery features and capabilities.

User-managed incomplete recovery options, such as utilizing the backup control file in conjunction with RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL, can be useful tools to move databases from one location to another. This approach can be used to move databases for any purpose such as testing or just moving to a new server. You must make sure that if you are moving to a new server that the Oracle database software and operating system are similar. The move database process can also be performed with the RMAN DUPLICATE TARGET DATABASE command.

This is a user-managed approach, which is performed by taking the hot or cold backup of the database you want to move and moving the datafiles and initialization files to the new location. Make the necessary changes in the ASCII control file to location references of all the physical database files such as redo logs and datafiles. Then validate that ORACLE_SID is sourced to the correct database and execute this control file at the SQL prompt as SYSDBA. This will generate a new database on a new server and in different locations. This can be used in many situations where hardware backup solutions integrated with storage area networks SAN and network area storage NAS devices are used with disk mirrored backups.


4.5.2. Performing User-Managed Incomplete Recovery

User-managed incomplete recovery can be performed by time, change, and cancelling. The method of performing incomplete recovery is using the RECOVER DATABASE command with the UNTIL TIME, CHANGE, or CANCEL clauses.

This section walks you through the steps for performing a time- and change-based incomplete recovery. Let's do a time-based recovery first:

  1. Make sure NLS_DATE_FORMAT is set to a value that you can reproduce in SQL*Plus:

    C:> set NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS

  2. Remove the datafile USERS01.DBF and restore USERS01.DBF from backup to simulate a recovery situation:

    C:oracleoradataora101t> delete USERS01.DBF
C:oracleoradataora101t> copy
C:oracleackupora101tUSERS01.DBF.

    Make sure you have tested the backup database before trying to simulate a failure by deleting a datafile.


  3. Make sure the target database is started in MOUNT mode:

    SQL> startup mount
ORACLE instance started.

Total System Global Area    88080384 bytes
Fixed Size    787588 bytes
Variable Size    78642044 bytes
Database Buffers    8388608 bytes
Redo Buffers    262144 bytes Database mounted.

  4. Recover the database using the SET UNTIL TIME clause:

    SQL> recover database until time '06-SEP-2004 15:15:00';
ORA-00279: change 565007 generated at 09/06/2004 15:05:20 needed for thread 1
ORA-00289: suggestion : C:ORACLEORAARCHORA101TARC00001_0536160481.001
ORA-00280: change 565007 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto

    Log applied.
Media recovery complete.
SQL>

  5. Open the database with the RESETLOGS clause:

    SQL> alter database open resetlogs;

database opened

SQL>

Next, let's look at a specific example of using the cancel-based recovery, which is when you terminate the recovery process at random by issuing the CANCEL command. In this example, you will recover from an online backup using a backup control file that was created earlier when you used the ALTER DATABASE BACKUP CONTROLFILE TO TRACE command. Let's walk through a cancel-based recovery:

  1. Copy the online backup of the all the datafiles that make up ora101t:

    C:oradataoracleora101t> copy C:oracleackupora101t*.DBF.

  2. View the backup control file to verify:

    STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA101T" RESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 454
LOGFILE
  GROUP 1 (
    'C:ORACLEORADATAORA101TREDO01_MIRROR.LOG',
    'C:ORACLEORADATAORA101TREDO01.LOG'
  ) SIZE 10M,
  GROUP 2 (
    'C:ORACLEORADATAORA101TREDO02.LOG',
    'C:ORACLEORADATAORA101TREDO02_MIRROR.LOG'
  ) SIZE 10M,
  GROUP 3 (
    'C:ORACLEORADATAORA101TREDO03.LOG',
    'C:ORACLEORADATAORA101TREDO03_MIRROR.LOG'
  ) SIZE 10M

    -- STANDBY LOGFILE
DATAFILE
  'C:ORACLEORADATAORA101TSYSTEM01.DBF',
  'C:ORACLEORADATAORA101TUNDOTBS01.DBF',
  'C:ORACLEORADATAORA101TSYSAUX01.DBF',
  'C:ORACLEORADATAORA101TUSERS01.DBF',
  'C:ORACLEORADATAORA101TEXAMPLE01.DBF',
  'C:ORACLEORADATAORA101TINDEX01.DBF'
CHARACTER SET WE8MSWIN1252
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE',
  
    'DISK BACKUP TYPE TO
  COMPRESSED BACKUPSET PARALLELISM 1'),
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE
AUTOBACKUP','ON'),

  3. Run the backup control file:

    SQL> connect / as sysdba;
Connected to an idle instance.
SQL> @backup_control_reset.txt
ORACLE instance started.


Total System Global Area    88080384 bytes
Fixed Size                    787588 bytes
Variable Size               78642044 bytes
Database Buffers             8388608 bytes
Redo Buffers                  262144 bytes

Control file created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL>

  4. Issue the RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE command:

    SQL> recover database until cancel using backup controlfile;
ORA-00279: change 566539 generated at 09/06/2004 17:41:21 needed for thr
ORA-00289: suggestion : C:ORACLEORAARCHORA101TARC00004_0536167372.00
ORA-00280: change 566539 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 567533 generated at 09/06/2004 18:03:37 needed   for thr
ORA-00289: suggestion : C:ORACLEORAARCHORA101TARC00005_0536167372.00
ORA-00280: change 567533 for thread 1 is in sequence #5
ORA-00278: log file
  
    'C:ORACLEORAARCHORA101TARC00004_0536167372.001'
no longer needed for this recovery



Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 567535 generated at 09/06/2004 18:03:42 needed for thr
ORA-00289: suggestion : C:ORACLEORAARCHORA101TARC00006_0536167372.00
ORA-00280: change 567535 for thread 1 is in sequence #6
ORA-00278: log file
  
    'C:ORACLEORAARCHORA101TARC00005_0536167372.001'
no longer needed for this recovery



Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

  5. Open the database using the RESETLOGS clause:

    SQL> alter database open resetlogs;

Database altered.

SQL>

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset