1 / 28

Chapter 5 User-Managed Incomplete Recovery

Chapter 5 User-Managed Incomplete Recovery. Chapter Summary. Explain the difference between a complete and incomplete database recovery State when an incomplete database recovery is required Identify the command to perform a database recovery. Chapter Summary (Cont.).

anastasia-hero
Download Presentation

Chapter 5 User-Managed Incomplete Recovery

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 5User-Managed Incomplete Recovery Oracle9i DBA II: Backup/Recovery and Network Administration

  2. Chapter Summary • Explain the difference between a complete and incomplete database recovery • State when an incomplete database recovery is required • Identify the command to perform a database recovery Oracle9i DBA II: Backup/Recovery and Network Administration

  3. Chapter Summary (Cont.) • Identify the options available for performing an incomplete recovery • Explain the disadvantages of performing a cancel-based recovery • Perform a time-based recovery Oracle9i DBA II: Backup/Recovery and Network Administration

  4. Chapter Summary (Cont.) • Explain the advantage of performing a change-based recovery • Identify the purpose of using LogMiner to analyze redo log file • Use LogMiner to perform an analysis of the online redo log files Oracle9i DBA II: Backup/Recovery and Network Administration

  5. Incomplete Recovery • Database must be in ARCHIVELOG mode • Not all data contained in redo log files is applied • Uses RECOVER DATABASE UNTIL command Oracle9i DBA II: Backup/Recovery and Network Administration

  6. Incomplete Recovery • Sequence number of logs must be reset using RESETLOGS option • Always perform cold backup before and after recovery Oracle9i DBA II: Backup/Recovery and Network Administration

  7. Incomplete Recovery Options • Cancel-based recovery • Time-based recovery • Change-based recovery Oracle9i DBA II: Backup/Recovery and Network Administration

  8. Cancel-based Recovery • Recovery stops before contents of specified archived redo log file is applied • Even valid transactions from specified file are lost • Normally results in the greatest amount of data loss • Use UNTIL CANCEL option of RECOVER DATABASE command Oracle9i DBA II: Backup/Recovery and Network Administration

  9. Cancel-based Recovery (Cont.) Oracle9i DBA II: Backup/Recovery and Network Administration

  10. Cancel-based Recovery Example Oracle9i DBA II: Backup/Recovery and Network Administration

  11. Time-based Recovery • Uses timestamp to terminate recovery process • May lose valid transactions committed at the same time as problematic transaction • Requires the correct time to be known • Use UNTIL TIME option of RECOVER DATABASE command Oracle9i DBA II: Backup/Recovery and Network Administration

  12. Time-based Recovery (Cont.) Oracle9i DBA II: Backup/Recovery and Network Administration

  13. Time-based Recovery Example Oracle9i DBA II: Backup/Recovery and Network Administration

  14. Change-based Recovery • Uses SCN to terminate recovery process • Can use LogMiner to determine appropriate SCN • Results in the least amount of data loss • Use UNTIL CHANGE option of RECOVER DATABASE command Oracle9i DBA II: Backup/Recovery and Network Administration

  15. Change-based Recovery (Cont.) Oracle9i DBA II: Backup/Recovery and Network Administration

  16. LogMiner Package Oracle9i DBA II: Backup/Recovery and Network Administration

  17. Procedure for using LogMiner • Extract data dictionary • Specify redo log files to analyze • Perform analysis • Query for desired DML or DDL operations • Terminate LogMiner session Oracle9i DBA II: Backup/Recovery and Network Administration

  18. Extracting a Data Dictionary • Can be stored in: • Redo log files • An online catalog • Flat file • Specify destination location using UTL_FILE_DIR parameter in INIT.ORA file • Execute DMBS_LOGMNR_D.BUILD procedure Oracle9i DBA II: Backup/Recovery and Network Administration

  19. Specifying Log Files • Determine files to be analyzed • Execute appropriate package: • DMBS_LOGMNR.ADD_LOGFILE with DBMS_LOGMNR.NEW option for first file • DMBS_LOGMNR.ADD_LOGFILE with DBMS_LOGMNR.ADDFILE option for remaining files Oracle9i DBA II: Backup/Recovery and Network Administration

  20. Creating Analysis List Example Oracle9i DBA II: Backup/Recovery and Network Administration

  21. Performing the Analysis • Must include destination for the results • Specify starting and ending parameters • Specify DDL_DICT_TRACKING option to include DDL operations Oracle9i DBA II: Backup/Recovery and Network Administration

  22. Accessing LogMiner Results Results stored in V$LOGMNR_CONTENTS view Oracle9i DBA II: Backup/Recovery and Network Administration

  23. Ending a LogMiner Session END_LOGMNR procedure terminates session Oracle9i DBA II: Backup/Recovery and Network Administration

  24. Change-based Recovery Example Use RECOVER DATABASE command after acquiring appropriate SCN Oracle9i DBA II: Backup/Recovery and Network Administration

  25. Opening Database after an Incomplete Recovery Must execute ALTER DATABASE OPEN RESETLOGS; statement to reset log sequence Oracle9i DBA II: Backup/Recovery and Network Administration

  26. Chapter Summary • Not all available transactions are applied during an incomplete recovery • Data loss occurs • Use RECOVER DATABASE command with UNTIL CHANGE, UNTIL TIME, or UNTIL CANCEL keywords Oracle9i DBA II: Backup/Recovery and Network Administration

  27. Chapter Summary (Cont.) • Perform a cold backup before and after an incomplete recovery • Cancel-based recovery is the least flexible • Time-based recovery requires DBA to know when the transaction was committed Oracle9i DBA II: Backup/Recovery and Network Administration

  28. Chapter Summary (Cont.) • Change-based recovery results is least data loss • LogMiner utility can determine SCN of a specific transaction • Use SCN as termination point of a change-based recovery Oracle9i DBA II: Backup/Recovery and Network Administration

More Related