How to restore the control file and recover database to the most recent available with Oracle database

Article: 100024434
Last Published: 2023-10-20
Ratings: 1 0
Product(s): Backup Exec

Problem

When restoring the control file of Oracle database, Backup Exec automatically applies REDO logs until a point-in-time when the backup was got, even if "to the most recent available" restore option is selected.
 

This is a scenario to recover Oracle database to the most recent available with recovery of the control file.
 

 

Solution

1. Restore control file using Backup Exec

1-1. Create a restore job

1-2. Select to the most recent backup set under Control Files in the restore selection list

1-3. Set Oracle restore options

Select "Restore the control file only" option.  

Unselect "Recover using redo logs" option.

 

1-4. Submit the job.

 

2. Restore database file using Recovery Manager (RMAN)
2-1. Open command prompt then start rman

C:\>rman target=/ nocatalog
 

Recovery Manager: Release 8.1.7.0.0 - Production
 

RMAN-06006: connected to target database: ora817 (not mounted)
RMAN-06009: using target database controlfile instead of recovery catalog

 

2-2. Mount the database

After the restore job of the control file only, the database is being nomount mode. 

RMAN> ALTER DATABASE MOUNT;
RMAN-03022: compiling command: alter db
RMAN-06199: database mounted

 

2-3. Run the following script

RUN {
 
  ALLOCATE CHANNEL ch0 TYPE 'SBT_TAPE';
 
  RESTORE DATABASE;
 
  RECOVER DATABASE;
 
  RELEASE CHANNEL ch0;
 
}

 

This command will fail with RMAN-06054: media recovery requesting unknown log: thread 1 scn <number> because Oracle 8i RMAN cannot automatically detect the most recent available REDO log which is called Online REDO log.

 

For instance of output of the script:

RMAN> RUN {
2>  ALLOCATE CHANNEL ch0 TYPE 'SBT_TAPE';
3>  RESTORE DATABASE;
4>  RECOVER DATABASE;
5>  RELEASE CHANNEL ch0;
6>  }

 
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: ch0
RMAN-08500: channel ch0: sid=13 devtype=SBT_TAPE
RMAN-08526: channel ch0: Veritas/BackupExec/1.1.0
 
RMAN-03022: compiling command: restore
 
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel ch0: starting datafile backupset restore
RMAN-08502: set_count=27 set_stamp=750858836 creation_time=11-MAY-11
RMAN-08089: channel ch0: specifying datafile(s) to restore from backup set
RMAN-08523: restoring datafile 00001 to C:\ORADATA\SYSTEM01.DBF
RMAN-08523: restoring datafile 00002 to C:\ORADATA\RBS01.DBF
RMAN-08523: restoring datafile 00003 to C:\ORADATA\USERS01.DBF
RMAN-08523: restoring datafile 00004 to C:\ORADATA\TEMP01.DBF
RMAN-08523: restoring datafile 00005 to C:\ORADATA\TOOLS01.DBF
RMAN-08523: restoring datafile 00006 to C:\ORADATA\INDX01.DBF
RMAN-08523: restoring datafile 00007 to C:\ORADATA\DR01.DBF
RMAN-08523: restoring datafile 00008 to C:\ORADATA\OEM_REPOSITORY.ORA
RMAN-08023: channel ch0: restored backup piece 1
RMAN-08511: piece handle=BE_0rmc2cik_1_1 tag=null params=NULL
RMAN-08024: channel ch0: restore complete
 
RMAN-03022: compiling command: recover
 
RMAN-03022: compiling command: recover(1)
 
RMAN-03022: compiling command: recover(2)
 
RMAN-03022: compiling command: recover(3)
RMAN-03023: executing command: recover(3)
RMAN-08054: starting media recovery
 
RMAN-03022: compiling command: recover(4)
RMAN-03023: executing command: recover(4)
RMAN-08017: channel ch0: starting archivelog restore to default destination
RMAN-08022: channel ch0: restoring archivelog
RMAN-08510: archivelog thread=1 sequence=5
RMAN-08023: channel ch0: restored backup piece 1
RMAN-08511: piece handle=0tmc2cjg_1_1 tag=null params=NULL
RMAN-08024: channel ch0: restore complete
RMAN-08515: archivelog filename=C:\ARCLOG\ARC00005.001 thread=1 sequence=5
RMAN-08017: channel ch0: starting archivelog restore to default destination
RMAN-08022: channel ch0: restoring archivelog
RMAN-08510: archivelog thread=1 sequence=6
RMAN-08022: channel ch0: restoring archivelog
RMAN-08510: archivelog thread=1 sequence=7
RMAN-08022: channel ch0: restoring archivelog
RMAN-08510: archivelog thread=1 sequence=8
RMAN-08022: channel ch0: restoring archivelog
RMAN-08510: archivelog thread=1 sequence=9
RMAN-08023: channel ch0: restored backup piece 1
RMAN-08511: piece handle=BE_0vmc2klh_1_1 tag=null params=NULL
RMAN-08024: channel ch0: restore complete
RMAN-08515: archivelog filename=C:\ARCLOG\ARC00006.001 thread=1 sequence=6
RMAN-08515: archivelog filename=C:\ARCLOG\ARC00007.001 thread=1 sequence=7
RMAN-08515: archivelog filename=C:\ARCLOG\ARC00008.001 thread=1 sequence=8
RMAN-08515: archivelog filename=C:\ARCLOG\ARC00009.001 thread=1 sequence=9
RMAN-08017: channel ch0: starting archivelog restore to default destination
RMAN-08022: channel ch0: restoring archivelog
RMAN-08510: archivelog thread=1 sequence=10
RMAN-08023: channel ch0: restored backup piece 1
RMAN-08511: piece handle=10mc2klj_1_1 tag=null params=NULL
RMAN-08024: channel ch0: restore complete
RMAN-08515: archivelog filename=C:\ARCLOG\ARC00010.001 thread=1 sequence=10
RMAN-08060: unable to find archivelog
RMAN-08510: archivelog thread=1 sequence=11
RMAN-03026: error recovery releasing channel resources
RMAN-08031: released channel: ch0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure during compilation of command
RMAN-03013: command type: recover
RMAN-03006: non-retryable error occurred during execution of command: recover(4)
RMAN-07004: unhandled exception during command execution on channel default
RMAN-20000: abnormal termination of job step
RMAN-06054: media recovery requesting unknown log: thread 1 scn 375414  

 

2-4. Write down the SCN number and exit RMAN

375414 is the number in this instance.


3. Continue to recover using sqlplus

3-1. Open command prompt then start sqlplus 

C:\>sqlplus /nolog
 
SQL*Plus: Release 8.1.7.0.0 - Production on Wed May 11 14:44:55 2011
 
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
 

SQL> connect / as sysdba
Connected.

 

3-2. Make sure of the path of the Online REDO log

SQL> select * from v$logfile;
 
    GROUP# STATUS
---------- -------
MEMBER
--------------------------------------------------------------------------------     

        1
C:\ORACLE\ORADATA\ORA817\REDO03.LOG
        2
C:\ORACLE\ORADATA\ORA817\REDO02.LOG
 
         3
C:\ORACLE\ORADATA\ORA817\REDO01.LOG
 

 

3-3. Make sure of the status of the Online REDO log

 

SQL> select * from v$log;
 
        GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
          1          1         11    1048576          1 NO  CURRENT
       375414 11-MAY-11
 
         2          1          9    1048576          1 YES INACTIVE
       375360 11-MAY-11
 
         3          1         10    1048576          1 YES INACTIVE
       375410 11-MAY-11
 

 

3-4. perform recover database command

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

 

3-5. Enter the path of the Online REDO log file

a) In this instance, requested SCN is 375414.
b) From the result of v$log, GROUP# 1 has the SCN 375414.
c) From the result of v$logfile, C:\ORACLE\ORADATA\ORA817\REDO03.LOG is a file of GROUP# 1.
d) In accordance with the result of v$log and v$log file, enter the full path to the prompt.


SQL> recover database using backup controlfile until cancel
ORA-00279: change 375414 generated at 05/11/2011 14:12:03 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA81\RDBMS\ARC00011.001
ORA-00280: change 375414 for thread 1 is in sequence #11
 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\ORACLE\ORADATA\ORA817\REDO03.LOG
 


3-5 Cancel the recovery

Once the path of the Online REDO log is entered, the recover command requests the next log file.
Because REDO03.LOG (SCN 375414) is the log file the most recent available, enter CANCEL to the prompt.

 

ORA-00279: change 375417 generated at 05/11/2011 14:15:15 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA81\RDBMS\ARC00012.001
ORA-00280: change 375417 for thread 1 is in sequence #12
ORA-00278: log file 'C:\ORACLE\ORADATA\ORA817\REDO03.LOG' no longer needed for this recovery
 

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

 
Media recovery cancelled.
 


3-6 open the database with resetlogs option

SQL> alter database open resetlogs;
 
 Database altered.

 

4. Get full backup using Backup Exec as soon as possible after the resetlogs

 

This document can be applied to where all the following conditions are met:

a) All copies of control files are corrupted

b) A copy of Online REDO log files is NOT corrupted

c) A copy of Archived REDO log files which has not yet got backup is NOT corrupted

d) Database files are corrupted or not corrupted

 

 

References

Etrack : 897585

Was this content helpful?