1 / 30

Database Recovery

Database Recovery. User man. RMAN CLI EM Complete Incomplete Control file RO TBS. Objectives. After completing this lesson, you should be able to: Perform complete or incomplete user-managed recovery Identify situations where incomplete recovery is necessary

jjaynes
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. User man. RMAN CLI EM Complete Incomplete Control file RO TBS Objectives • After completing this lesson, you should be able to: • Perform complete or incomplete user-managed recovery • Identify situations where incomplete recovery is necessary • Perform complete or incomplete recovery by using RMAN • Perform incomplete recovery based on time, SCN, log sequence, restore points, or the cancel method • Recover an automatically backed up control file • Use Enterprise Manage to perform recovery • Recover read-only tablespaces

  3. Recovery Methods • There are two methods for performing recovery: • User-managed recovery • Files must be maintained and moved into place manually. • Use SQL*Plus commands. • RMAN recovery • Files are managed automatically. • Use RMAN functionality including all repository maintenance and reporting capabilities. • This can be done by using Enterprise Manager. • Oracle Corporation recommends using this method.

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

  5. User man. > RMAN CLI EM Complete Incomplete Control file RO TBS RMAN Recovery: RESTOREand RECOVER Commands run{ sql "ALTER TABLESPACE inv_tbs OFFLINE IMMEDIATE"; RESTORE TABLESPACE inv_tbs; RECOVER TABLESPACE inv_tbs DELETE ARCHIVELOG; sql "ALTER TABLESPACE inv_tbs ONLINE"; }

  6. User man. RMAN CLI > EM Complete Incomplete Control file RO TBS Recovery UsingEnterprise Manager • With the Enterprise Manager Recovery Wizard, you can create and run an RMAN script to perform the recovery. RMAN> RECOVER DATABASE …

  7. Complete Versus Incomplete Recovery • There are two types of recovery: • Complete recovery brings the database up to the present, including all data changes made to the point in time when the recovery was requested. Completerecovery Incompleterecovery Recoverytask startedat this time Restore fromthis backup Missing transactionsafter incomplete recovery • Incomplete recovery brings the database up to a specified point in time in the past, before the recovery operation was requested.

  8. Archivedlog Archivedlog OnlineRedo log 2 6 3 4 User man. RMAN CLI EM > Complete Incomplete Control file RO TBS Complete Recovery Changes applied Databaseopened Undo applied 5 1 Restoreddata files Data files containing committed anduncommitted transactions Recovereddata files

  9. Archivedlog Archivedlog OnlineRedo log 2 6 4 3 User man. RMAN CLI EM Complete > Incomplete Control file RO TBS Incomplete Recovery X X Changes applied to point in time (PIT) Databaseopened Undo applied 5 1 Restored data files from as farback as necessary Data files containing committed anduncommitted Transactions up to PIT PIT-recovereddata files

  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. Performing User-ManagedIncomplete Recovery • Recover a database until time: • Recover a database until cancel: • Recover using the backup control file: SQL> RECOVER DATABASE UNTIL 2 TIME '2005-12-14:12:10:03'; SQL> RECOVER DATABASE UNTIL CANCEL; SQL> RECOVER DATABASE 2 UNTIL TIME '2005-12-14:12:10:03' 3 USING BACKUP CONTROLFILE;

  13. Performing User-Managed Incomplete Recovery • To perform user-managed incomplete recovery, follow these steps: • 1. Shut down the database. • 2. Restore data files. • 3. Mount the database. • 4. Recover the database. • 5. Open the database with the RESETLOGS option.

  14. User-Managed Time-Based Recovery: Example • This is the scenario: • A job ran in error, and its effects have to be undone. • This happened 15 minutes ago, and there has been little database activity since then. • You decide to perform incomplete recovery to restore the database back to its state as of 15 minutes ago. SQL> SHUTDOWN IMMEDIATE $ cp /BACKUP/* /u01/db01/ORADATA SQL> STARTUP MOUNT SQL> RECOVERDATABASEUNTILTIME'2005-11-28:11:44:00'; SQL> ALTER DATABASE OPEN RESETLOGS;

  15. User-Managed Cancel-Based Recovery: Example • The scenario is the same as the one for the time-based example, except for these 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 are lost. • Users can reenter their data manually.

  16. User-Managed Cancel-Based Recovery: Example • Recover the database as follows: • Shut down the database. • Restore all data files from the most recent backup. • You already have a valid backup, so mount the database. • Execute RECOVERDATABASEUNTILCANCEL. • Execute ALTERDATABASEOPENRESETLOGS to open the database.

  17. Performing Incomplete Recoveryby Using 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.

  18. Time-Based RecoveryUsing RMAN: Example RMAN> RUN { 2> SET UNTIL TIME = '2005-11-28:11:44:00'; 3> RESTORE DATABASE; 4> RECOVER DATABASE; 5> ALTER DATABASE OPEN RESETLOGS; }

  19. Log Sequence RecoveryUsing RMAN: 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 RESETLOGS; 7> }

  20. Incomplete Recovery UsingEnterprise Manager

  21. Incomplete Recovery and the Alert Log • The following are some best practices regarding the alert log in incomplete recovery scenarios: • Check the alert log before and after recovery. • Look for error information, hints, and SCNs. • Confirm that steps in the recovery process were successful.

  22. 2 Restore Points • A restore point: • Serves as an alias for anSCN or a specific point in time • Is stored in the control file • Can be used with: • RECOVER DATABASE • FLASHBACK DATABASE • FLASHBACK TABLE before_load 1 SQL> CREATE RESTORE POINT before_load; RMAN> RECOVER DATABASE UNTIL RESTORE POINT before_load;

  23. Incomplete Recovery: Best Practices • Plan for and practice scenarios ahead of time. • Investigate and verify that incomplete recovery is necessary. • Follow all steps carefully. • Take whole database backups before and after recovery. • Always verify that the recovery was successful. • Take advantage of restore points.

  24. User man. RMAN CLI EM Complete Incomplete > Control file RO TBS Recovering a Control FileAutobackup RMAN> RESTORE CONTROLFILE TO 2> '/oradata/ctlfile.bak' FROM AUTOBACKUP; RecoveryManager(RMAN) Flash recovery area RECOVER DATABASE; ALTER DATABASE OPEN RESETLOGS; ALTER TABLESPACE temp ADD TEMPFILE...; Controlfile Database

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

  26. Creating a New Control File

  27. User man. RMAN CLI EM Complete Incomplete Control file > RO TBS Recovering Read-OnlyTablespaces * Case 1 Read-only * Read/write Case 2 Read-only * Case 3 Read-only Read/write * Control filebackup to beused for recoveryif current one lost Change TablespaceState Backup Crashand perform recovery

  28. Read-Only Tablespace Recovery Issues • Special considerations must be taken for read-only • tablespaces when: • Re-creating a control file • Renaming data files • Using a backup control file

  29. Summary • In this lesson, you should have learned how to: • Perform complete or incomplete user-managed recovery • Perform complete or incomplete recovery by using RMAN • Identify situations where incomplete recovery is necessary • Perform incomplete recovery based on time, SCN, log sequence, restore points, or the cancel method • Recover a control file autobackup • Use Enterprise Manage to perform recovery • Recover read-only tablespaces

  30. Practice Overview: Performing Incomplete Recovery • This practice covers the following topics: • Point-in-time recovery using RMAN • Recovery from the loss of control files

More Related