Problem
How to restore and recover an Oracle database to a 'Point in Time' using an on-line backup
Solution
When restoring and recovering an Oracle database damaged by a user error or process failure, a very specific procedure must be followed.
As an on-line backup of an open Oracle database is inconsistent or fuzzy, it is necessary to perform "Media Recovery" after physically restoring the lost data to disk in order to recover the consistency and relationship within the database at the time before the error occurred.
Before the data and the database can be recovered and made consistent, all the backed up data files must be physically restored to disk. This restore must be performed from the MOST RECENT complete database backup set taken before the error or process failure occurred, and it must contain ALL the database data files including the control file and archived log files.
The physical restore must be configured to "Restore over existing files". If the database hasn't already been put offline by the RDBMS start Server Manager, connect as internal and shut down the database with the abort option.
Note. With the release of Oracle Database Server 9i, Server Manager and internal are no longer supported. Use SQL*Plus and connect sys as sysdba. See the Related Documents section at the bottom of this TechNote for this procedure.
As an on-line backup of an open Oracle database is inconsistent or fuzzy, it is necessary to perform "Media Recovery" after physically restoring the lost data to disk in order to recover the consistency and relationship within the database at the time before the error occurred.
Before the data and the database can be recovered and made consistent, all the backed up data files must be physically restored to disk. This restore must be performed from the MOST RECENT complete database backup set taken before the error or process failure occurred, and it must contain ALL the database data files including the control file and archived log files.
The physical restore must be configured to "Restore over existing files". If the database hasn't already been put offline by the RDBMS start Server Manager, connect as internal and shut down the database with the abort option.
Note. With the release of Oracle Database Server 9i, Server Manager and internal are no longer supported. Use SQL*Plus and connect sys as sysdba. See the Related Documents section at the bottom of this TechNote for this procedure.
svrmgr>shutdown abort;
Restore all the data files contained in the backup to their original location
After the data files have been restored to disk, they are inconsistent and to make the database consistent it must be recovered . The recover database command will start an Oracle process which will apply ( roll forward ) all transactions stored in the restored archived logs necessary to make the database consistent again . The recovery process must run up to a point that corresponds with the time just before the error occurred after which the log sequence must be reset to prevent any further system changes from being applied to the database. It is therefore necessary to use the recover until command. This will recover the database by applying archived transaction logs up until a point specified in the command. The recover database until command supports three clauses that will abort the recovery process at a specified point:
- recover database until cancel recovers the database applying one log file at a time until the recovery process is manually canceled
- recover database until time 'YYYY-MM-DD:HH24:MI:SS' recovers the database to a specified point in time
- recover database until change <scn> recovers the database to a specified system change number ( SCN)
The following example demonstrates using the
recover database until command with the
cancel clause.
In the alert log (<SID>ALRT.LOG, ) locate and note down the log sequence number of the last log archived before the error occurred. This will be needed to terminate the recovery at the desired point.
Restart the database to a mounted but not open state:
In the alert log (<SID>ALRT.LOG, ) locate and note down the log sequence number of the last log archived before the error occurred. This will be needed to terminate the recovery at the desired point.
Restart the database to a mounted but not open state:
svrmgr:>startup mount;
With the database mounted, run the recover database until cancel command from svrmgr:
svrmgr:>recover database until cancel;
Before Oracle applies each archived log sequence, it will return a message similar to the following suggesting an archived log to apply and allowing the operator to either continue or cancel the recovery:
ORA-00279: Change 36579 generated at <time/date> needed for thread 1
ORA-00289: Suggestion : /Oracle_Home/Oradata/<SID>/%SID%T00036579.ARC
ORA-00280: {<RET>=Suggested | filename | AUTO | FROM logsource | CANCEL}
Press Return until the archived log sequence determined by examining the alert log (<SID>ALRT>LOG) is reached, at which point type cancel to stop further recovery. Oracle will return the following message:
Media recovery canceled.
Restart the database with the resetlogs clause. This is necessary in order to synchronize the log sequence up to the point of recovery and to prevent any further system changes made after that point to be applied. The following command will restart the database and reset the logs:
svrmgr>alter database open resetlogs;
Database altered
The database is now recovered. Because the redo log sequence has been reset, it is vital to immediately take a fresh full database backup.
/apps/media/inquira/resources /resources
Related Articles
Article URL http://www.symantec.com/docs/TECH8030
Terms of use for this information are found in Legal Notices
323
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
