大型数据库系统分析与设计：Less14-BR-Concepts

1、Backup and Recovery ConceptsObjectivesAfter completing this lesson, you should be able to do the following:Identify the types of failure that may occur in an Oracle databaseDescribe ways to tune instance recoveryIdentify the importance of checkpoints, redo log files, and archive log filesConfigure A

2、RCHIVELOG modePart of Your JobThe administrators duties are to:Protect the database from failure wherever possibleIncrease the mean time between failures (MTBF)Decrease the mean time to recover (MTTR)Minimize the loss of dataCategories of FailuresFailures can generally be divided into the following

3、categories:Statement failureUser process failureNetwork failureUser errorInstance failureMedia failureStatement FailureTypical ProblemsPossible SolutionsAttempts to enter invalid data into a tableWork with users to validate and correct data.Attempts to perform operations with insufficient privileges

4、Provide appropriate object or system privileges.Attempts to allocate space that fail Enable resumable space allocation. Increase owner quota. Add space to tablespace.Logic errors in applicationsWork with developers to correct program errors.User Process FailureTypical ProblemsPossible SolutionsA use

5、r performs an abnormal disconnectA users session is abnormally terminatedA user experiences a program error that terminates the sessionA DBAs action is not usually needed to resolve user process failures. Instance background processes roll back uncommitted changes and release locks.Watch for trends.

6、Network FailureTypical ProblemsPossible SolutionsListener failsConfigure a backup listener and connect-time failover.Network Interface Card (NIC) failsConfigure multiple network cards.Network connection failsConfigure a backup network connection.User ErrorTypical CausesPossible SolutionsA user inadv

7、ertently deletes or modifies dataRoll back or use flashback query to recover.A user drops a tableRecover table from the recycle bin.Oracle LogMinerUser ErrorFull Notes PageInstance FailureTypical CausesPossible SolutionsPower outageRestart the instance by using the “startup” command. Recovering from

8、 instance failure is automatic, including rolling forward changes in the redo logs and then rolling back any uncommitted transactions.Investigate the causes of failure by using the alert log, trace files, and Enterprise Manager.Hardware failureFailure of one of the background processesEmergency shut

9、down proceduresInstance RecoveryInstance or crash recovery:Is caused by attempts to open a database whose files are not synchronized on shutdownIs automaticUses information stored in redo log groups to synchronize filesInvolves two distinct operations:Rolling forward: Data files are restored to thei

10、r state before the instance failed.Rolling back: Changes made but not committed are returned to their original state.Phases of Instance Recovery1.Data files out of sync2.Roll forward (redo)3.Committed and noncommitted data in files4.Roll back (undo)5.Committed data in filesDatabaseUndoInstanceBackgr

11、oundprocessesData fileSCN: 99Data fileSCN: 129Data fileSCN: 140Control fileSCN: 143Control fileSCN: 143Redo loggroupSCN 74-101Redo loggroupSCN 102-143SGATuning Instance RecoveryDuring instance recovery, the transactions between the checkpoint position and the end of redo log must be applied to data

12、files.Tune instance recovery by controlling the difference between the checkpoint position and the end of redo log.End of redo logCheckpoint positionTransactionsInstance recoveryUsing the MTTR AdvisorSpecify the desired time in seconds or minutes.The default value is 0 (disabled).The maximum value i

13、s 3,600 seconds (one hour).Media FailureTypical CausesPossible SolutionsFailure of disk drive1.Restore the affected file from backup.2.If necessary, inform the database of a new file location.3.If necessary, recover the file by applying redo information.Failure of disk controllerDeletion or corrupti

14、on of database fileConfiguring for RecoverabilityTo configure your database for maximum recoverability, you must:Schedule regular backupsMultiplex control filesMultiplex redo log groupsRetain archived copies of redo logsControl FilesProtect against database failure by multiplexing control files. Bes

15、t practice suggestions:At least two copies (Oracle recommends three)Each copy on a separate diskAt least one copy on a separate disk controllerControl filesRedo Log FilesMultiplex redo log groups to protect against media failure and loss of data. Best practice suggestions:At least two members (files

16、) per groupEach member on a separate disk driveEach member on a separate disk controllerNote: Performance is heavily influenced by writing to redo logs.Group 1Group 2Group 3Disk 1Disk 2Member 1Member 2Member 1Member 2Member 1Member 2Multiplexing the Redo LogArchive Log FilesTo preserve redo informat

17、ion, create archived copies of redo log files by performing the following steps.1.Specify archive log file naming convention.2.Specify one or more archive log file locations.3.Switch the database to ARCHIVELOG mode.Online redo log filesArchive log filesArchive Log File: Naming and DestinationsArchiv

18、ing Log File Naming and Destinations Full Notes PageARCHIVELOG ModeTo place the database in ARCHIVELOG mode, perform the following steps:1.Select the ARCHIVELOG Mode check box. 2.Click Apply. The database can be set to ARCHIVELOG mode only from the MOUNT state.3.Click Yes when asked whether you want to restart the database.4.Ba