💾 Module 17: Backup & Recovery (RMAN)

-- Connect to RMAN
RMAN TARGET / CATALOG recovery_catalog_user@catalog_db

-- Configure RMAN defaults
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET;
CONFIGURE ENCRYPTION FOR ALL TABLESPACES ON;

-- View RMAN configuration
SHOW ALL;

-- Configure backup location
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/rman/%d_%T_%s_%p';

-- Full backup of entire database
BACKUP
  DATABASE
  PLUS ARCHIVELOG
  DELETE INPUT;

-- Full backup with tag
BACKUP
  TAG 'full_backup_daily'
  DATABASE
  PLUS ARCHIVELOG DELETE INPUT;

-- Full backup with multiple channels (parallel)
BACKUP
  DEVICE TYPE DISK
  PARALLELISM 4
  DATABASE
  PLUS ARCHIVELOG DELETE INPUT;

-- Backup specific tablespaces
BACKUP
  TABLESPACE users, temp
  PLUS ARCHIVELOG;

-- Backup with compression
BACKUP
  COMPRESSION ALGORITHM 'MEDIUM'
  DATABASE
  PLUS ARCHIVELOG;

-- Level 0 incremental (full backup that allows incrementals)
BACKUP
  INCREMENTAL LEVEL 0
  DATABASE
  TAG 'level0_backup';

-- Level 1 incremental (changes since last level 0 or 1)
BACKUP
  INCREMENTAL LEVEL 1
  CUMULATIVE
  DATABASE
  TAG 'level1_cumulative';

-- Level 1 differential (changes since last backup)
BACKUP
  INCREMENTAL LEVEL 1
  DIFFERENTIAL
  DATABASE
  TAG 'level1_differential';

-- Incremental backup with archivelog
BACKUP
  INCREMENTAL LEVEL 1
  DATABASE
  PLUS ARCHIVELOG DELETE INPUT;

-- Recover database to specific timestamp
STARTUP MOUNT;
RECOVER DATABASE
  UNTIL TIME \"'2025-12-29 10:00:00'\";
ALTER DATABASE OPEN RESETLOGS;

-- Recover to specific SCN (System Change Number)
RECOVER DATABASE UNTIL SCN 12345678;

-- Recover to specific log sequence
RECOVER DATABASE UNTIL SEQUENCE 150 THREAD 1;

-- Preview recovery (show what will be recovered)
RECOVER DATABASE
  UNTIL TIME \"'2025-12-29 10:00:00'\"
  PREVIEW;

-- Restore from backup first, then recover
RESTORE DATABASE;
RECOVER DATABASE
  UNTIL TIME \"'2025-12-29 10:00:00'\";

-- List all backups
LIST BACKUP;

-- List recent backups with details
LIST BACKUP SUMMARY;

-- Validate backup integrity
BACKUP
  VALIDATE CHECK LOGICAL
  DATABASE;

-- List backup pieces
LIST BACKUPPIECE;

-- List datafile copies
LIST DATAFILECOPY ALL;

-- Show archivelog backup status
LIST ARCHIVELOG ALL;

-- Report backup age (older than 30 days)
REPORT NEED BACKUP RECOVERY WINDOW OF 30 DAYS;

-- Crosscheck backups against disk
CROSSCHECK BACKUP;
DELETE EXPIRED BACKUP;

-- Complete database restore and recovery
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;

-- Restore specific tablespace
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;

-- Restore specific datafile
RESTORE DATAFILE '/oracle/data/users01.dbf';
RECOVER DATAFILE '/oracle/data/users01.dbf';

-- Restore from specific backup tag
RESTORE DATABASE FROM TAG 'full_backup_daily';

-- Restore to different location
SET NEWNAME FOR DATAFILE 1 TO '/new/location/system01.dbf';
RESTORE DATABASE;
SWITCH DATAFILE ALL;