Backup, Recovery and Administration

Database administration encompasses backup and recovery, space management, performance monitoring, patching, and day-to-day operations. This lesson covers the essential DBA tasks that keep Oracle Database running reliably.

---

ARCHIVELOG vs NOARCHIVELOG Mode

Mode	Description	Recovery Capability
NOARCHIVELOG	Filled redo logs are overwritten	Only full database restore (no point-in-time recovery)
ARCHIVELOG	Filled redo logs are archived before overwriting	Full point-in-time recovery

-- Check current mode
SELECT log_mode FROM v$database;

-- Switch to ARCHIVELOG mode (requires restart)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Tip: Production databases should always run in ARCHIVELOG mode.

---

Recovery Manager (RMAN)

RMAN is Oracle's primary backup and recovery tool:

Full Database Backup

rman target /

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Incremental Backup

# Level 0 (base backup)
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

# Level 1 (changes since last level 0 or level 1)
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Tablespace Backup

RMAN> BACKUP TABLESPACE users;

Backup Validation

RMAN> RESTORE DATABASE VALIDATE;
RMAN> VALIDATE BACKUPSET 42;

RMAN Configuration

RMAN> SHOW ALL;

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
RMAN> CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';

---

Recovery Scenarios

Instance Recovery (Automatic)

When an instance crashes and restarts, SMON automatically:

1. Rolls forward — applies redo logs to restore committed changes
2. Rolls back — undoes uncommitted transactions

Complete Recovery

Restore and recover to the latest point:

RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

Point-in-Time Recovery

Restore to a specific time:

RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE UNTIL TIME "TO_DATE('2024-06-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS')";
RMAN> RECOVER DATABASE UNTIL TIME "TO_DATE('2024-06-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS')";
RMAN> ALTER DATABASE OPEN RESETLOGS;

---

Flashback Technology

Oracle's suite of "undo" features — faster and simpler than traditional recovery:

Flashback Query

See data as it existed at a past time:

SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);

Flashback Table

Restore a table to a previous point:

ALTER TABLE employees ENABLE ROW MOVEMENT;

FLASHBACK TABLE employees
TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' HOUR);

Flashback Drop (Recycle Bin)

Recover a dropped table:

DROP TABLE temp_data;     -- goes to recycle bin
FLASHBACK TABLE temp_data TO BEFORE DROP;

-- View recycled objects
SELECT object_name, original_name FROM recyclebin;

-- Permanently drop
DROP TABLE temp_data PURGE;

Flashback Database

Roll the entire database back in time (requires Flashback Logging):

-- Enable flashback
ALTER DATABASE FLASHBACK ON;

-- Flashback
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '4' HOUR);
ALTER DATABASE OPEN RESETLOGS;

---

Data Guard

Oracle's disaster recovery solution — maintains a standby database that is a synchronised copy of the primary:

Feature	Description
Physical Standby	Block-for-block copy; can be opened read-only (Active Data Guard)
Logical Standby	SQL-based replication; can be open for read-write on non-replicated tables
Far Sync	Zero-data-loss protection across long distances
Automatic Failover	Fast-Start Failover (FSFO) with Data Guard Broker

Protection Modes