1 / 31

Database Recovery

Database Recovery. Objectives. After completing this lesson, you should be able to: Recover the control file 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

abla
Download Presentation

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

  2. Objectives • After completing this lesson, you should be able to: • Recover the control file • 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. Archivedlog Archivedlog OnlineRedo log 2 5 3 Recovery Steps Undo applied Changes applied 4 Data files containing committed and uncommitted transactions Recovered data files Restored data files 1

  4. Server Managed Recovery:RESTORE and RECOVER Commands run{ sql "ALTER TABLESPACE indx_tbs OFFLINE IMMEDIATE"; RESTORE TABLESPACE indx_tbs; RECOVER TABLESPACE indx_tbs DELETE ARCHIVELOG; sql "ALTER TABLESPACE indx_tbs ONLINE"; }

  5. User-Managed Recovery Procedures:RECOVER Command • Restore all database files from backup and recover the database: • Restore the damaged data files from a backup and recover the data files: Or SQL> RECOVER DATABASE SQL> RECOVER TABLESPACE index_tbs SQL> RECOVER DATAFILE 2> '/oradata/indx01.dbf'

  6. Recovering a Control File Autobackup RMAN> RESTORE SPFILE TO 2> '/oradata/spfile.bak' FROM AUTOBACKUP; RecoveryManager(RMAN) Flash Recovery Area RECOVER DATABASE; ALTER DATABASE OPEN RESETLOGS; ALTER TABLESPACE temp ADD TEMPFILE...; ControlFile Database

  7. Creating a New Control File Redo logfile 1A Redo logfile 1B SYSTEM USERS Parameter File Redo logfile 2A Redo logfile 2B SYSAUX INDEX PasswordFile UNDO TEMP Controlfiles SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

  8. Creating a New Control File

  9. 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 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 Database

  10. Situations Requiring Incomplete 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

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

  12. 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.

  13. 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;

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

  15. UNTILTIME Recovery Steps 1. Shutdown database 2. Restore data files 3. Mount the database 4. Recover the database 5. Open database with RESETLOGS option 6. Backup the database SQL> shutdown immediate $ cp /BACKUP/* /u01/db01/ORADATA SQL> startup mount SQL> recoverdatabaseuntiltime'2004-05-28:11:44:00'; SQL> alter database open resetlogs; SQL> shutdown; $ cp /u01/db01/ORADATA/* /BACKUP

  16. 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.

  17. 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.

  18. 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

  19. 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.

  20. 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; }

  21. 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> }

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

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

  24. Recovery Using Enterprise Manager • Select Perform Recovery.

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

  26. Recovery Using Enterprise Manager • Select data files.

  27. Recovery Using Enterprise Manager • Final review

  28. Simplified Recovery Through RESETLOGS • The database can be opened immediately after RESETLOGS. (No longer need to take a full backup) • No changes are required to existing scripts. • Recovery through RESETLOGS can be used for: • Incomplete recovery • Recovery using a backup control file t1 t2 t3 t4 t5 Incomplete recovery performed Recovery needed Open with RESETLOGS Backup taken Full backup

  29. Recovery Through RESETLOGS: Changes LOG_ARCHIVE_FORMAT="LOG%t_%s_%r.arc" SQL> SELECT recid, thread#, sequence#, 2 resetlogs_change#, resetlogs_time 3 FROM v$log_history; SQL> SELECT recid, file#, 2 resetlogs_change#, resetlogs_time 3 FROM v$offline_range; SQL> SELECT incarnation#, resetlogs_id, 2 prior_incarnation#, status 3 FROM v$database_incarnation;

  30. 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

  31. Practice 8 Overview: Incomplete Recovery • This practice covers the following topics: • Point-in-time recovery using RMAN • Database recovery using the EM Database Control Console

More Related