1 / 23

Incomplete Recovery

Incomplete Recovery. Objectives. After completing this lesson, you should be able to: Explain reasons for incomplete recovery Describe incomplete recovery methodology Recover the database to a specific point in time using: User-managed backups RMAN-managed backups

daxia
Download Presentation

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. Incomplete Recovery

  2. Objectives • After completing this lesson, you should be able to: • Explain reasons for incomplete recovery • Describe incomplete recovery methodology • Recover the database to a specific point in time using: • User-managed backups • RMAN-managed backups • Enterprise Manager Database Control Console

  3. 1 1 1 1 1 0 Data file 2 Data file 1 Data file 3 Archivedlog files Redo logfile 2 Redo logfile 1 Controlfiles Parameterfile Passwordfile Database Incomplete Recovery Overview Instance Userprocess Serverprocess SGA Shared pool Locks Large Pool Shared SQLand PL/SQL PGA Databuffer Redo log buffer Data dict.cache Userprocess Serverprocess SMON DBWn PMON CKPT LGWR ARCn PGA

  4. Situations RequiringIncomplete Recovery • Complete recovery fails because of a missing archived log file • One or more unarchived redo log files and a data file are lost • A backup of the control file is used to open or recover the database

  5. Types of Incomplete Recovery • There are four types of incomplete recovery: • Time-based recovery • Cancel-based recovery • Change-based recovery • Log sequence recovery

  6. Incomplete Recovery Best Practices • Follow all steps carefully. • Take whole database backups before and after recovery. • Always verify that the recovery was successful. • Back up and remove archived logs.

  7. Using RECOVER for Incomplete Recovery • Recover a database until time: • Recover a database until cancel: • Recover using backup control file: SQL> RECOVER DATABASE UNTIL 2 TIME '2003-12-14:12:10:03'; SQL> RECOVER DATABASE UNTIL CANCEL; SQL> RECOVER DATABASE 2 UNTIL TIME '2003-12-14:12:10:03' 3 USING BACKUP CONTROLFILE;

  8. Database Restored Database 1 146 144 0 145 1 146 144 1 146 146 1 146 146 1 146 144 145 144 Data file 3 Data file 3 Data file 1 Data file 1 Data file 2 Data file 2 Data file 1 Data file 2 Data file 3 Archivedlog file Redo logfile 1 Redo logfile 2 Redo logfile 2 Redo logfile 1 Redo logfile 1 Redo logfile 2 Controlfiles Controlfiles Controlfiles Recovered Database UNTILTIME Recovery Example 1 Shut down and back up 2 Restore all data files 3 Mount the database OE.ORDERS OE.ORDERS 4 5 Open with Resetlogs OE.ORDERS 6 Back up the database

  9. UNTILTIME Recovery Steps • Shutdown database • Restore data files • Mount the database • Recover the database • Open database with RESETLOGS option • Backup the database SQL> shutdown immediate $ cp /BACKUP/* /u01/db01/ORADATA SQL> startup mount SQL> recover database until time '2004-05-28:11:44:00'; SQL> alter database open resetlogs; SQL> shutdown; $ cp /u01/db01/ORADATA/* /BACKUP

  10. Cancel-Based Recovery: Example • Scenario: • The current time is 12:00 p.m. on May 28, 2004. • The ORDERS table was dropped while someone was trying to fix corrupted data blocks. • Log files exist on the same disk as the data files. • The table was dropped at approximately 11:45 a.m. • Staff are currently in a meeting.

  11. Cancel-Based Recovery: Example • Findings: • Redo logs are not multiplexed. • One of the online redo logs is missing. • The missing redo log is not archived. • The redo log contained information from 11:34 a.m. • Twenty-six minutes of data will be lost. • Users can recover their data.

  12. Incomplete Recovery and the Alert Log • Check the alert log before and after recovery • Look for error information, hints, and SCNs • Confirm steps in the recovery process were successful

  13. Incomplete Recovery of a DatabaseUsing RMAN 1. Mount the database. 2. Allocate multiple channels for parallelization. 3. Restore all data files. 4. Recover the database by using UNTILTIME, UNTILSEQUENCE, or UNTILSCN. 5. Open the database by using RESETLOGS. 6. Perform a whole database backup.

  14. RMAN Incomplete RecoveryUNTILTIME: Example RMAN> RUN { 2> SET UNTIL TIME = '2004-05-28:11:44:00'; 3> RESTORE DATABASE; 4> RECOVER DATABASE; 5> ALTER DATABASE OPEN RESETLOGS; }

  15. RMAN Incomplete Recovery UNTILSEQUENCE: Example RMAN> RUN { 2> SET UNTIL SEQUENCE 120 THREAD 1; 3> ALTER DATABASE MOUNT; 4> RESTORE DATABASE; 5> RECOVER DATABASE; # recovers through log 119 6> ALTER DATABASE OPEN RESESTLOGS; 7> }

  16. Recovery Using Enterprise Manager • Log in as a user with the SYSDBA privilege

  17. Recovery Using Enterprise Manager • Click on the • Maintenance tab

  18. Recovery Using Enterprise Manager • Select Perform Recovery

  19. Recovery Using Enterprise Manager • Select Whole Database and enter OS login credentials

  20. Recovery Using Enterprise Manager • Select data files

  21. Recovery Using Enterprise Manager • Final review

  22. Summary • In this lesson, you should have learned how to: • Recognize a situation that requires incomplete recovery • Describe the incomplete recovery methodology • Recover the database to a specific point in time using: • User-managed backups • RMAN-managed backups • Enterprise Manager Database Control Console

  23. Practice 9 Overview: Incomplete Recovery • This practice covers the following topics: • Traditional recovery from user failure • Point-in-time recovery using RMAN • Database recovery using the Database Control Console

More Related