How to restore the control file and recover database to the most recent available with Oracle database
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
Was this content helpful?
Translated Content
Please note that this document is a translation from English, and may have been machine-translated. It is possible that updates have been made to the original version after this document was translated and published. Veritas does not guarantee the accuracy regarding the completeness of the translation. You may also refer to the English Version of this knowledge base article for up-to-date information.