Chapter 15 Backup and Recovery Concepts

Identify the Types of Failure That Can Occur in an Oracle Database
Statement Failure
invalid data errors - e.g. format violation, constraint violation.
logic errors - e.g. deadlock
privilege permission errors - e.g. not granting system privileges or object privileges
space problem errors - e.g. unable to extend segment because tablespace is full, running out of undo space, insufficient temporary space, user hitting quota limit.

To prevent space problem error:
set datafiles to autoextend: ALTER DATABASE DATAFILE 'path_to_dbf' AUTOEXTEND ON NEXT n[MGT] MAXSIZE m[MGT];
enable resumable space allocation: ALTER SESSION ENABLE RESUMABLE;
RESUMABLE_TIMEOUT parameter for whole instance

User Process Failure: PMON poll server process to check the state of session to tidy up if server process lost contact with it user process. If a session terminates abnormally, an active transaction will be rolled back automatically.

Network Failure
listeners - high volume of concurrent connection requests, configure more listeners on different addresses/ports
network interface cards - use two cards
routes - use different subnet

User Error - cannot control user hit commit, need to help to reverse
flashback query (limited by undo tablespace):SELECT * FROM table_name as of timestamp(sysdate-n/1440);
flashback drop: FLASHBACK TABLE table_name TO BEFORE DROP;
Log Miner (limited by log files): use online and archived redo log files
incomplete recovery: whole database
flashback database: whole database

Media Failure: disk damage
controlfiles, online redo log files, archived redo log files are protected through multiplexing
datafiles are protected through hardware level multiplexing, RAID

Backup Concept: controlfiles,archived redo log files, and datafiles.

restore it from a backup: extract it, put back
recover it from the restored backup by extract changes from archived and online redo log files generated since the backup was taken to bring forward.

Instance Failure:
after instance failure, corrupted database: missing committed transactions and storing uncommitted transactions

pg552:
In principle, instance recovery is nothing more than using the contents of the online redo log files to rebuild the database buffer cache to the state it was in before the crash.

instance recovery: using online redo log files, there will always be enough information in the redo log stream on disk to correct the damage. reinstate any committed transactions that had not been saved to the datafiles at the time of crash, roll back any uncommitted transactions that had been written to datafiles.

Instance Recovery:
Roll Forward Phase: Redo -> Disk (can open database)
Roll Back Phase: undo segment by roll forward redo protecting uncommitted transaction (after open database)

SMON check before open the file headers of all the datafiles and online redo log files. If out of sync, then once roll forward recovery completed then open database.

Uncommitted work must never be saved.

Media Recovery --> Instance Recovery

COMMIT -> LGWR
CHECKPOINT -> DBWn

Describe Ways to Tune Instance Recovery
controlled by checkpoints
all changes written to datafile by DBWn when checkpoint occurs.
SMON replay redo generated from the last checkpoint position.

FAST_START_MTTR_TARGET=0 (default) in seconds:maximize performance but long instance recovery

if set to zon-zero value, checkpoint auto-tuning will be enabled. (incrementing checkpoint)

MTTR advisor can advise the estimated time for instance recovery (how long it takes)
V$INSTANCE_RECOVERY


Identify the Importance of Checkpoints, Redo Log Files, and Archived Log Files
Checkpointing is the process of forcing the DBWn to write dirty buffers from the database buffer cache to the datafiles.

Checkpoint position in redo stream is pushed forward or advanced by the DBWn. CKPT update it to controlfiles.

Full checkpoint: all dirty buffers are written to disk
orderly shutdown
on demand request manually by DBA: ALTER SYSTEM CHECKPOINT;

Partial checkpoint:
tablespace offline
datafile offline
dropping a segment
truncating a table
tablespace backup mode

archivelog mode: clean shutdown first

never corrupted - shutdown abort - instance recovery - online redo log files
no loss of data - datafile damage - media recovery - all archived log files since last backup are available (contain complete history) - recovering a backup.

ARCn 4-30


Overview of Flash Recovery Area
db_recovery_file_dest
db_recovery_file_dest_size

used by Recovery Manager, RMAN

Configure ARCHIVELOG Mode
Shutdown the database cleanly
Startup in mount mode
ALTER DATABASE ARCHIVELOG;
Open the database: ALTER DATABASE OPEN;
Perform a full backup