Database
Recovery For
Newbies
Paper #521Database
Recovery For
Newbies
Paper #521Bonnie Bizzaro, Susan McClain Bonnie Bizzaro, Susan McClain
Objectives
Objectives
Provide basic understanding of recovery processes and terms
Define different types of recovery
Discuss common recovery strategies Demo of Recovery Manager (RMAN)
Provide basic understanding of recovery processes and terms
Define different types of recovery
Discuss common recovery strategies Demo of Recovery Manager (RMAN)
Basic Recovery Types
Basic Recovery Types
Instance recovery Media recovery Data recovery Disaster recovery Instance recovery Media recovery Data recovery Disaster recoveryBasic Recovery Terms
Basic Recovery Terms
Data block Data file
Tablespace
System change number (SCN)
Data block Data file
Tablespace
Basic Recovery Terms, cont’d
Basic Recovery Terms, cont’d
Rollback segment / Undo segment Online redo log Archive redo log
(No)Archivelog mode Offline backup
Online backup
Rollback segment / Undo segment Online redo log
Archive redo log
(No)Archivelog mode Offline backup
What is instance recovery When database crashes
Automatically brings to consistent state Should shutdown/restart database before
resuming normal activity
What is instance recovery When database crashes
Automatically brings to consistent state Should shutdown/restart database before
resuming normal activity
Instance Recovery
Instance Recovery, cont’d
Instance Recovery, cont’d
Entries in the alert logEntries in the alert log Database mounted in Exclusive Mode.
Completed: alter database mount Sun Feb 23 05:50:20 2003
alter database open
Beginning crash recovery of 1 threads Sun Feb 23 05:50:24 2003
Thread recovery: start rolling forward thread 1
Recovery of Online Redo Log: Thread 1 Group 2 Seq 35417 Reading mem 0 Mem# 0 errs 0: u05/oradata/LPROD/redoLPROD2.log
Sun Feb 23 05:50:27 2003
Thread recovery: finish rolling forward thread 1
Thread recovery: 241 data blocks read, 241 data blocks written, 3771 redo blocks read
Crash recovery completed successfully
Database mounted in Exclusive Mode. Completed: alter database mount
Sun Feb 23 05:50:20 2003 alter database open
Beginning crash recovery of 1 threads Sun Feb 23 05:50:24 2003
Thread recovery: start rolling forward thread 1
Recovery of Online Redo Log: Thread 1 Group 2 Seq 35417 Reading mem 0 Mem# 0 errs 0: u05/oradata/LPROD/redoLPROD2.log
Sun Feb 23 05:50:27 2003
Thread recovery: finish rolling forward thread 1
Thread recovery: 241 data blocks read, 241 data blocks written, 3771 redo blocks read
Media Recovery
Media Recovery
What is Media recovery
– physical components of the database
What is Media recovery
Media Recovery, cont’d
Media Recovery, cont’d
Common causes of media loss or failure Lost or corrupted data file strategies
Lost or corrupt control file strategies
Complete or incomplete recovery strategies
Common causes of media loss or failure Lost or corrupted data file strategies
Lost or corrupt control file strategies
Lost or Corrupted Data File
Lost or Corrupted Data File
For non-system tablespace Offline the object
– Alter tablespace xxxx offline;
– Alter database datafile ‘/u01/oradata/xxxxx’ offline;
Restore the file from backup Execute recover command
– Recover tablespace xxxx;
– Recover datafile ‘/u01/oradata/xxxxx’; Offline the object
– Alter tablespace xxxx offline;
– Alter database datafile ‘/u01/oradata/xxxxx’ offline;
Restore the file from backup Execute recover command
– Recover tablespace xxxx;
– Recover datafile ‘/u01/oradata/xxxxx’;
Lost or Corrupted Data File
Lost or Corrupted Data File
For non-system tablespace, cont’d Apply archive logs if prompted Online the object
– Alter tablespace xxxx online;
– Alter database datafile ‘/u01/oradata/xxxxx’ online;
Verify recovery
– Select name,status from v$datafile;
• Status should be ONLINE
Apply archive logs if prompted Online the object
– Alter tablespace xxxx online;
– Alter database datafile ‘/u01/oradata/xxxxx’ online;
Verify recovery
– Select name,status from v$datafile;
• Status should be ONLINE
Lost or Corrupted Data File
Lost or Corrupted Data File
For system tablespace Shutdown database
Restore the file from backup
Start the database in mounted state
– Startup mount;
Execute recover command
– Recover database;
Apply logs if prompted Open database
– Alter database open;
Shutdown database
Restore the file from backup
Start the database in mounted state
– Startup mount;
Execute recover command
– Recover database;
Apply logs if prompted Open database
– Alter database open;
Lost or Corrupted Data File
Lost or Corrupted Data File
No archivelog mode Shutdown database, do a backup
Restore all files from previous offline backup
– Restore control file from the same backup
Startup database
If you don’t have a copy of your control file from the previous offline backup
– Startup mount;
– Alter database open resetlogs; – Immediately do a backup
Shutdown database, do a backup
Restore all files from previous offline backup
– Restore control file from the same backup
Startup database
If you don’t have a copy of your control file from the previous offline backup
– Startup mount;
– Alter database open resetlogs; – Immediately do a backup
Lost or Corrupted Control File
Lost or Corrupted Control File
Shutdown database If control files multiplexed
– Copy a good control file over corrupted one – Restart database
If all copies of control file are gone
– Use backup controlfile script – Restart database
– Backup database Shutdown database
If control files multiplexed
– Copy a good control file over corrupted one – Restart database
If all copies of control file are gone
– Use backup controlfile script – Restart database
Lost or Corrupted Control File
Lost or Corrupted Control File
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "LPROD" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 2 MAXDATAFILES 800 MAXINSTANCES 1 MAXLOGHISTORY 2726 LOGFILE
GROUP 1 '/u06/oradata/LPROD/redoLPROD01.log' SIZE 80M, GROUP 2 '/u05/oradata/LPROD/redoLPROD2.log' SIZE 80M DATAFILE ‘/u06/oradata/LPROD/system01.dbf’, ‘/u06/oradata/LPROD/rbs101.dbf’, ‘/u01/oradata/LPROD/temp01.dbf’, ‘/u01/oradata/LPROD/tools01.dbf’, STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "LPROD" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 2 MAXDATAFILES 800 MAXINSTANCES 1 MAXLOGHISTORY 2726 LOGFILE
GROUP 1 '/u06/oradata/LPROD/redoLPROD01.log' SIZE 80M, GROUP 2 '/u05/oradata/LPROD/redoLPROD2.log' SIZE 80M DATAFILE
‘/u06/oradata/LPROD/system01.dbf’, ‘/u06/oradata/LPROD/rbs101.dbf’, ‘/u01/oradata/LPROD/temp01.dbf’, ‘/u01/oradata/LPROD/tools01.dbf’,
Alter database backup controlfile to trace; Alter database backup controlfile to trace;
Lost or Corrupted Control File
Lost or Corrupted Control File
. .
# Recovery is required if any of the datafiles are restored backups, # or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed. ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally. ALTER DATABASE OPEN;
. .
# Recovery is required if any of the datafiles are restored backups, # or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed. ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally. ALTER DATABASE OPEN;
cont’d cont’d
Alter database backup controlfile to trace;, Alter database backup controlfile to trace;,
Incomplete Recovery
Incomplete Recovery
Common scenarios Database point in time recovery Tablespace point in time recovery Recover until cancel
Recover until change (SCN)
Common scenarios
Database point in time recovery Tablespace point in time recovery Recover until cancel
Database Point In Time
Recovery
Database Point In Time
Recovery
Shutdown database
Restore all datafiles from previous backup
Verify archive logs are available
Startup database in mounted state
– Startup mount ;
Shutdown database
Restore all datafiles from previous backup
Verify archive logs are available
Startup database in mounted state
Database Point In Time
Recovery, cont’d
Database Point In Time
Recovery, cont’d
Execute recovery command
– Recover database until time ‘2002-12-28:10:38:00’;
Or,
Recover database until cancel;
Open database and resetlogs
– Alter database open resetlogs;
Backup database
Execute recovery command
– Recover database until time ‘2002-12-28:10:38:00’;
Or,
Recover database until cancel;
Open database and resetlogs
– Alter database open resetlogs;
Data Recovery
Data Recovery
Using export/import Partial database recovery, then export Transportable tablespace
Logminer
Using export/import
Partial database recovery, then export Transportable tablespace
Disaster Recovery
Disaster Recovery
Loss of – Server – Data center – Building If different OS on DR server, use import
– Verify Oracle version – Create shell database – Import full database
– Restore your initSID.ora Loss of
– Server
– Data center – Building
If different OS on DR server, use import
– Verify Oracle version – Create shell database – Import full database
Disaster Recovery, cont’d
Disaster Recovery, cont’d
If same OS on DR server, restore filesfrom hot/cold backup
– Restore files using original paths – Rename files in mounted state – Restore initSID.ora file
– Startup database, recover
Standby database
Have a plan and test it!
If same OS on DR server, restore files from hot/cold backup
– Restore files using original paths – Rename files in mounted state – Restore initSID.ora file
– Startup database, recover
Standby database
Server Managed Recovery
RMAN Overview
Server Managed Recovery
RMAN Overview
Uses Oracle’s server processes Scripts are found in
$ORACLE_HOME/bin Target database
Repository
Media Manager
Channels / Disk Devices
Uses Oracle’s server processes Scripts are found in
$ORACLE_HOME/bin Target database
Repository
Media Manager
RMAN Overview
Repository Setup
RMAN Overview
Repository Setup
Create repository owner Grant
RECOVERY_CATALOG_OWNER role Connect to the repository and ‘create
catalog’
Connect to target (needs SYSDBA role) and register the database
Create repository owner Grant
RECOVERY_CATALOG_OWNER role Connect to the repository and ‘create
catalog’
Connect to target (needs SYSDBA role) and register the database
RMAN DEMO
RMAN DEMO
Susan McClain, Alliance Data Systems
Final Notes
Final Notes
Keep users out of your database while recovering
– Startup restrict
Backup your database before starting a recovery
– If you think it can’t get any worse, trust me, it can
Analyze all alternatives before you begin Successful recoveries require successful
backups.
– Make sure you have data files, controlfiles,
initSID.ora, archive logs, redo logs (hot backups)
Keep users out of your database while recovering
– Startup restrict
Backup your database before starting a recovery
– If you think it can’t get any worse, trust me, it can
Analyze all alternatives before you begin Successful recoveries require successful
backups.
– Make sure you have data files, controlfiles,
Final Notes
Final Notes
The best recovery is no recovery
– Multiplex control files – Multiplex redo logs
– Have appropriate OS and database security
TEST YOUR RECOVERY PLAN REGULARLY !
If you can’t recover your database
– Select * from v$my_resume;
The best recovery is no recovery
– Multiplex control files – Multiplex redo logs
– Have appropriate OS and database security
TEST YOUR RECOVERY PLAN REGULARLY !
If you can’t recover your database
Q & A
Q & A
Paper #521
Database Recovery for Newbies
Database Recovery for Newbies
Paper #521
Bonnie J. Bizzaro
Lead Database Administrator Alliance Data Systems
[email protected] Susan McClain
Systems Manager, Distributed Database Support Alliance Data Systems
Special thanks to Joe Testa, Data Management Consulting
Paper #521
Bonnie J. BizzaroLead Database Administrator Alliance Data Systems
Susan McClain
Systems Manager, Distributed Database Support Alliance Data Systems
