oracle数据库恢复.ppt

User-Managed Complete Recovery,Objectives,After completing this lesson,you should be able todo the following:Describe media recoveryPerform recovery in Noarchivelog modePerform complete recovery in Archivelog modeRestore datafiles to different locationsRelocate and recover a tablespace by using archived redo log filesDescribe read-only tablespace recovery,Media Recovery,Used to recover a lost or damaged current datafile or control fileRequires explicit invocationOperates as follows:Files are restored from backupsRedo data is applied to the restored files from archived redo log files and online redo logs,OnlineRedo log,Recovery Steps,Restored datafiles,Database containingcommitted and uncommittedtransactions,Recovered database,Redo applied,Undo applied,5,4,3,2,1,ArchivedRedo log,Restoration and Datafile Media Recoverywith User-Managed Procedures,Restore files using operating system commandsRecover files using the SQL*Plus RECOVER command,Archivelog and Noarchivelog Modes,Instance,SGA,Redo log buffer,Data buffer,Large pool,Locks,Shared pool,Data dict.cache,Shared SQLand PL/SQL,ARCn,Userprocess,Serverprocess,PGA,Userprocess,Serverprocess,PGA,Database,Parameterfile,Passwordfile,Recovery in Noarchivelog Mode,In Noarchivelog mode,you must restore the followingdatabase files:DatafilesControl filesYou may also restore the following files:Redo log filesPassword file Parameter file,Recovery in Noarchivelog Mode,AdvantagesEasy to perform,with low risk of errorRecovery time is the time it takes to restore all files.DisadvantagesData is lost and must be reapplied manually.The entire database is restored to the point of the last whole closed backup.,PasswordFile,ParameterFile,PasswordFile,ParameterFile,Datafile 3,Redo logFile 2,Data File 2,Control Files,Redo logFile 1,Datafile 1,Database,145,146,146,146,146,146,Recovery in Noarchivelog ModeWith Redo Log File Backups,Backup,Restore from the most recent backup,Database,PasswordFile,ParameterFile,Recovery in Noarchivelog Mode Without Redo Log File Backups,1.Shut down the instance.2.Restore the datafiles and the control file from the most recent whole database backup.3.Perform cancel-based recovery.4.Open the database with the RESETLOGS option.,Recovery in Archivelog Mode,Complete RecoveryUses redo data or incremental backupsUpdates the database to the most current point in timeApplies all redo changesIncomplete RecoveryUses backup and redo logs to produce a noncurrent version of the database,Complete Recovery,Make sure that datafiles for restore are offline.Restore only lost or damaged datafiles.Do not restore the control files,redo log files,password files,or parameter files.Recover the datafiles.,Complete Recovery in Archivelog Mode,AdvantagesOnly need to restore lost filesRecovers all data to the time of failureRecovery time is the time it takes to restore lost files and apply all archived log filesDisadvantagesMust have all archived log files since the backup from which you are restoring,Determining Which Files Need Recovery,View V$RECOVER_FILE to determine which datafiles need recoveryView V$ARCHIVED_LOG for a list of all archived redo log files for the databaseView V$RECOVERY_LOG for a list of all archived redo log files required for recovery,User-Managed Recovery Procedures:RECOVER Command,SQL RECOVER DATABASE;ORSQL RECOVER DATAFILE 2/ORADATA/u03/users01.dbf;,Recover a mounted database:,SQL RECOVER TABLESPACE users;ORSQL RECOVER DATAFILE 3;,Recover an open database:,Using Archived Redo Log Files During Recovery,To change archive location,use the ALTER SYSTEM ARCHIVE LOG.command.To apply redo log files automatically:Issue the SET AUTORECOVERY ON command before starting media recoveryEnter auto when prompted for an archived log fileUse the RECOVER AUTOMATIC.command.,Restoring Datafiles to a New Locationwith User-Managed Procedures,Use operating system commands to restore the datafile to the new location.Use the ALTER DATABASE RENAME FILE command to record the change in the control file.,Complete Recovery Methods,Closed database recovery for:System datafilesUndo segment datafilesWhole databaseOpen database recovery,with database initially opened(for file loss)Open database recovery with database initially closed(for hardware failure)Datafile recovery with no datafile backup,Complete Recovery of a Closed Database,Closed database recovery is used for:System tablespace datafilesRollback segment datafilesWhole database,Shut down the instance,1,Datafile 3,Redo logfile 2,Datafile 2,Control files,Redo logfile 1,Datafile 1,Closed Database,Archivedlog file,144,Closed Database Recovery Example,Restore datafile 1(Log Sequence 144),2,Open the database,4,Datafile 3,Redo logfile 2,Datafile 2,Control files,Redo logfile 1,Datafile 1,Mounted Database,3,Datafile 3,Redo logfile 2,Control files,Redo logfile 1,Datafile 1,Recovered Database,146,145,146,146,146,Datafile 2,146,3,3,Open Database Recovery When the Database Is Initially Open,Use this method when:The database is currently openThe database will remain open during the recoveryThe media failure does not affect the SYSTEM tablespace,1,Datafile 3,Redo logfile 2,Datafile 2,Control files,Redo logfile 1,Datafile 1,Open Database Recovery Example,2,4,Datafile 3,Redo logfile 2,Datafile 2,Control files,Redo logfile 1,Datafile 1,146,145,146,144,146,146,3,3,3,Datafile 3,Redo logfile 2,Control files,Redo logfile 1,Datafile 1,146,145,146,146,146,Datafile 2,146,Take datafile 2 offline,Restore datafile 2(Log Sequence 144),Bring datafile 2 online,Archivedlog file,144,Open Database,Open Database,Recovered Database,Open Database Recovery When the Database Is Initially Closed,Use this method when:The database is currently closedThe database will be opened during recoveryThe media failure does not affect the SYSTEM tablespace,Take datafile 2 offline,2,Open Database Recovery Example,Restore datafile 2,4,Bring datafile 2 online,6,1,Mount the database,3,Open the database,Datafile 3,Redo logfile 2,Datafile 2,Control files,Redo logfile 1,Datafile 1,Datafile 3,Redo logfile 2,Control files,Redo logfile 1,Datafile 1,146,145,146,146,146,Datafile 2,146,Closed Database,Recovered Database,Datafile 3,Redo logfile 2,Datafile 2,Control files,Redo logfile 1,Datafile 1,146,145,146,144,146,146,5,5,5,Archivedlog file,144,Open Database,Recovery of a Datafile Without a Backup,Datafile is lost that was never backed upCannot be used when it is a file from the SYSTEM tablespace,Datafile 3,Redo logfile 2,Datafile 2,Control files,Redo logfile 1,Datafile 1,146,145,146,146,146,146,Open Database,Datafile 3,Redo logfile 2,Datafile 2,Control files,Redo logfile 1,Datafile 1,146,145,146,144,146,146,Archivedlog file,144,Open Database,Recovery Without a Backup Example,Bring the datafile or tablespace online,4,1,Take the datafile or tablespace offline,2,Re-create the datafile,Datafile 4,3,3,3,Datafile 4,146,Datafile 3,Redo logfile 2,Datafile 2,Control files,Redo logfile 1,Datafile 1,146,145,146,146,146,146,Recovered Database,Datafile 4,146,Read-Only Tablespace Recovery,Backup 2,Recovery,Case 1,Case 2,Case 3,Backup 1,Read-Only,Read-Write,Read-Only,Read-Only,Read-Write,Read-Only Tablespace Recovery Issues,Special considerations must be taken for read-onlytablespaces when:Re-creating a control fileRenaming data filesUsing a backup control file,Loss of Control Files,You may need to create control files if:All control files are lost because of a failureThe name of a database needs to be changedThe current settings in the control file need to be changed,Recovering Control Files,Methods to recover from loss of control file:Use the current control fileCreate a new control fileUse a backup control file,Summary,In this lesson,you should have learned how to:Determine what type of recovery is requiredDetermine which files need to be restored and recoveredRecover a database in Noarchivelog modeRecover a database in Archivelog modeRestore datafiles to different locations if the original location is unavailable,Practices 12-1 and 12-2 Overview,These practices cover the following topics:Performing complete database recovery with the database in Noarchivelog modePerforming complete database recovery with the database in Archivelog mode,