24. Restore and Recovery - sayanbhattacharya1234/Oracle_DBA GitHub Wiki
Restore and Recovery
01 Learing objective
1. Explain Restore, Recover, Media Recovery , and Complete Recovery
a. Restore : The physical action of copying database files (datafiles,control_files,parameter files) from backup storage location back to the database server.
b. Recover : The process of updating restored database files by applying changes that occurred after the backup was taken.
c. Media recovery : A broad category of manual recovery triggered by a human administrator to fix a physical "media failure".
d. Complete Recovery : A specific type of media recovery that restores and updates the database up to the most recent transaction before the failure occurred.
2.Use RMAN Review and validate the backup commands.
3.Restore and recover an offline datafile
4. Fix the missing backups,archive logs, recovery errors.
SAFETY : restore and recover operations can overwrite the files, change database state,effect the application availability. Verify the database name,number,file,backup availability,archive log coverage.
02. Restore and recovery flowchart
03. Test DATAfile complete recovery flowchart :
04. Recovery
05. Restore vs recovery
06. Complete recovery
Check incarnation
Check restore preview
07. Views
V$database
dba_data_files
v$recover_file
v$datafile
v$datafilel_header
v$tablespaces
v$rman_status
v$archivelogs
v$logs
V$session_longops
list backup
restore_preview
restore_validate
08. Pre recovery checks
database and instance status
database mapping
file requiring recovery
select file#,online_status,error,change#,time from v$recover_file order by file#;
datafile header status :
select file#,name,status,error,recover,checkpoint_change#,checkpoint_time from v$datafile_header order by file#;
RMAN inventory
09. Validation before faliure
validate database blocks
logical validation
restore database validate
restore a datafile validate
preview backups
10. Test file
STEP 1 : Create a tablespace , an user and give the session ,table permission
STEP 2: Identify the file number
here datafile 7 i choose
STEP 3 and 4 : already backup is taken and validated datafile 7.
STEP 5 : simulate file loss ( here i delete datafile 7 after taking backup of datafile 7 )
STEP 6 : take file offline
STEP 7: Restore file
STEP 8: Recover file
STEP 9: take file online STEP 10: Validate status
STEP 11: check
11. online datafile recovery
12. Whole database complete recovery
13. Control file and sp file consideration
List backup of control file and sp file
14. Archved log and redo requirements:
Check archive log coverage:
select thread#, sequence#, first_change#, next_change#, first_time, next_time, archived, deleted, status, name from v$archived_log order by thread#, sequence#;
Check current online redo:
15.
RMAN long operation
select sid, serial#, opname, target, sofar, totalwork, units, round(sofar * 100 / nullif(totalwork, 0), 2) as pct_done, elapsed_seconds, time_remaining from v$session_longops where opname like '%RMAN%' and totalwork > 0 and sofar <> totalwork order by start_time
RMAN status
select session_recid, session_stamp, operation, status, start_time, end_time, object_type, output_bytes from v$rman_status where start_time > sysdate-1 order by start_time desc;
Recovery process
select item, sofar, total, units, timestamp from v$recovery_progress order by item;
Media Recovery Processes:
select process, status, thread#, sequence#, block# from v$managed_standby order by process;
16. Troubleshooting scenarios :
RMAN-06023: No backup or copy found:
list backup of datafile 1; restore datafile 1 preview; list incarnation of database;
RMAN-06054: Unknown Archived Log:
crosscheck archivelog all; catalog start with '/u05/oracle/archive' noprompt; list archivelog all;
RMAN-06025: No backup of Archived Log Found:
One of more archived log required for recovery are unavailable.
list backup of archivelog from sequence &start_seq until sequence &end_seq thread 1;
ORA-01113: File needs media recovery
select file#, error, change#, time from v$recover_file;
recover datafile $file_id;
ORA-01110: Datafile number and name:
Use the accompanning ORA-01110 message to identify the affected file, then map it with v$datafile and dba_data_files.
ORA-01245 / File offline during recovery:
Confirm the file is offline in the correct mode and not part of system or active undo.
Datafile restored to different wrong path:
Original location
Restored to new location
Backup piece is expired:
Recover requests missing sequence:
Restore the archived log from backup or copy it from another valid destination/standby then catalog it.
Note: Donot force OPEN RESETLOGS simply to bypass missing redo. That changes the recovery objective and may cause data loss. Escalate and obtain approval for incomplete recovery.