Restore and Recovery Flowchart:

┌──────────────────────────────────────────────────────────────┐
│             Failure Detected (missing/corrupt data           │     
│                  file, capture error and file id             │     
└──────────────────────────────────────────────────────────────┘
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐   
│             Access Scope Datafile/Tablespace/Database        │   
│                      Online or mounted recovery              │
└──────────────────────────────────────────────────────────────┘
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐   
│          Validate backups (list/preview/validate)            │ 
│                   confirm acrhive coverage                   │ 
└──────────────────────────────────────────────────────────────┘     
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│    Take file offline or mount DB (Protect consistency,       │
│                  stop access to damaged files                │
└──────────────────────────────────────────────────────────────┘  
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│                     Restore (Copy backup to datafile         │
│                     path file is physically replaced         │
└──────────────────────────────────────────────────────────────┘
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│                     Recover (Apply incremental + redo        │
│                     Advanced file to current SCN             │  (?)
└──────────────────────────────────────────────────────────────┘
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│                     Consistency check (File checkpoint       │
│                         aligned, no missing redo             │
└──────────────────────────────────────────────────────────────┘
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│          Online/Open Bring File online resume service        │ 
└──────────────────────────────────────────────────────────────┘ 
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│       Validate Application objects, sessions, alert log      │ 
└──────────────────────────────────────────────────────────────┘ 
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│     Document recovery Timeline, logs, result, prevention     │ 
└──────────────────────────────────────────────────────────────┘

Test DataFile Complete Recovery Flow:

┌──────────────────────────────────────────────────────────────┐
│          Indentify File (DBA_DATA_FILES / V$DATAFILE         │     
│                    Confrim Test Tablespace                   │     
└──────────────────────────────────────────────────────────────┘
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐   
│                           Offline File                       │   
│                alter database datafile offline;              │
└──────────────────────────────────────────────────────────────┘
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐   
│                Simulate Loss (Move lab file only)            │ 
│                       preserve backup file                   │ 
└──────────────────────────────────────────────────────────────┘     
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│               Restore file RMAN Restore datafile             │
│                        copy from backup                      │
└──────────────────────────────────────────────────────────────┘  
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│              Recover file (Apply incrementals + archive      │
│                     Reach current checkpoint                 │  
└──────────────────────────────────────────────────────────────┘
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐   
│                           Online File                        │   
│                alter database datafile online;               │
└──────────────────────────────────────────────────────────────┘
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│   Validate qyery test table check alert log and file status  │ 
└──────────────────────────────────────────────────────────────┘

Core Recovery Concepts:

Restore: Copies a file from backup to a usable location. Returns a physical file but it maybe older than the current state of the database.

Recover: Applies incremental backup, archived redo and online redo. Advances restored file to a consistent/current SCN.

Complete recovery: Applies all required redo up to the latest available point. Normally opens without RESETLOGS.

Incomplete recovery: Stops at a time, SCN, sequence, or restored point. Required OPEN RESETLOGS.

Media recovery: Recovery from lost/corrupt physical database files. Uses backup plus redo.

Instance recovery: Automatic recovery after abnormal shutdown. Uses online redo, no restore required.

Crash recovery: Another term for automatic instance recovery. SMON/foreground procecces recover instance state.

Datafile checkpoint: SCN recorded in datafile header. Must be advanced to match database consistency requirements.

Restore vs recover:

Feature: Restore is retrieving physical file from backup and recover is changes after backup.

Input: Restores input are backup set and image copy. Recovers input is incrementals, archived logs, online redo log files.

Output: Restores output are older physical file on disk. Recovers output are files advanced to required SCN. (Recovers restore the data files which were deleted or corrupted. Recover database advances the state of data files i,e making the changes done by users DML and DDL after the last backup was taken and make them in that state it was before the files deletion or the SCN or timestamp given by the user. )

Command: restore datafile 1; recover datafile 1;

Can the files be used immediately: Usually no in case of restore. Yes in case of complete recovery the files can be turned online and can be used again.

Complete recovery requirements:

A usable backup of the required file or the database.

All required incremental backups after recovery.

Avalable online redo if the latest changes are not yet archived.

Correct DBID, incarnation, control file metadata and file mapping (?).

Sufficient destination space and correct file permissions.

Database/file in the required recoery state.

Check incarnation:

list incarnatin of database;

Restore preview:

restore datafile 1 preview;

restore datafile 1 preview summary;

Associated views and commands:

V$datafile: Datafile number, name, status, checkpoint scn.

V$datafile_header: datafile header status and recovery requirement.

V$recover_file: Files requiring media recovery.

Dba_data_files: Permanent data file Metadata.

V$tablespace: Tablespace id and name mapping.

V$archived_log: archived log sequence and send coverage.

V$log : Online Redo status and sequence.

V$database: open mode, checkpoint, incarnation context.

V$rman_status: restore/recover operation status.

V$session_longops: Progress of long restore/recovery operation.

V$database_block_corruption: known corrupt blocks.

List backup; : Backup inventory

Restore preview: Show required backup chain.

Restore validate: Checks backup readability and selection.

Pre recovery check:

Database and instance state:

select name, open_mode, log_mode, database_role, current_scn from v$database;

select instance_name, status, database_status, startup_time from v$instance;

Database Mapping:

select df.file_id, df.file_name, df.tablespace_name, df.status, df.online_status, df.bytes/1024/1024 as size_mb from dba_data_files df order by df.file_id;

Files 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, fuzzy, checkpoint_change#, checkpoint_time from v$datafile_header order by file#;

RMAN Inventory:

list backup summary; list backup of database; list backup of datafile 1; list backup of archivelog all;

Validation before failure:

Validate database blocks:

Validate database;

Validate database command checks and defects block corruption on data files, control file, spfile**(a block corruption in simple words is if the contents of the data block are damaged, malformed, or contain data that the database engine cannot recognize or interpret safely. Reading a corrupted block can cause unpredictable errors, query failures or even database crashes)** etc.

Also validate database checks for missing files, checks if all the files are present in their correct location and are accessible or not.

If any corrupt block is found rman automatically record the details in control file accesible via v$database_block_corruption.

This is only for physical checks.

Logical validation:

Validate check logical database;

For checking the logical corruptions.

Restore database validate:

Restore database validate;

Restore one datafile validate:

Restore datafile 5 validate;

Preview required backup chain:

Restore datafile 5 preview summary;

Check corruption:

Select file#, block#, blocks, corruption_change#, corruption_type from v$database_block_corruption order by file#, block#;

Complete test datafile recovery scenario:

Scenario: Restore and completely recover a test datafile:

Lab Assumption: A dedicated test tablespace named LAB_RECOVERY contains datafile 7. A valid backup and required archived logs exist. Do not run this against production files without approval.

Step 1: Create test Tablespace and Data.

Step 2: Identify File number.

Step 3: Take backup.

Step 4: Validate backup.

Step 5: Take Datafile offline.

Step 6: Simulate data file loss in lab. (But never move or delete a production datafile manually)

Step 7: Confirm recovery requirement.

Step 8: Restore Datafile.

Step 9: Recover datafile.

Step 10: Bring datafile online.

Step 11: Validate status.

Step 12: Check alert log.

Online data file recovery:

For datafiles that isn't system, undo can be recovered properly while the rest of the database is open.

Alter database datafile 5 offline:

Rman target / Restore datafile 5; Recover datafile 5;

Alter database datafile 5 online;

Tablespace level recovery:

Alter tablespace name offline immediate;

Restore tablespace name; Recover tablespace name;

Alter tablespace name online;

Whole database complete recovery:

When required:

When multiple critical datafiles lost.

System or active undo data file lost.

Database can't open due to file loss.

Storage failure affect most database files;

Command:

Shutdown abort

Startup mount

Restore database; Recover database,

Alter database open;

Validaton:

select name, open_mode, database_role from v$database;

select file#, name, status, enabled from v$datafile order by file#;

select file#, error, change#, time from v$recover_file;

Control file and spfile considerations:

List backup:

list backup of controlfile; list backup of spfile;

Validate backup:

restore controlfile validate; restore spfile validate (?)

Restore spfile:

startup force nomount restore spfile from autobackup;

Restore control file:

startup nomount restore controlfile from autobackup; alter database mount;

After backup control file restore:

A restored backup control file usually requires recovery and then open resetlogs, because control file metadata is older than current database state.

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:

Preview recovery requirement:

restore datafile 5 preview; restore database preview summary;

Catalog Missing Archived log:

catalog start with '/u05/oracle/archive/' noprompt;

Crosscheck:

crosscheck archivelog all; list expired archivelog all;

Monitor Restore and Recovery:

RMAN Long Operations:

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;