Quantitative Researcher OTC Fixed Income (IL) Next Step Systems US-IL-Chicago Post A Job | Post A Resume

Types of Media Recovery

Complete Media Recovery: All necessary redo or incremental backup ever generated for the particular incarnation of the database being recovered.

Incomplete Media Recovery: a point-in-time recovery (PITR), produces a version of the database as it was at some time in the past.

Time-based Recovery: Recovers data up to a specified point in time.

Cancel-based Recovery: Recovers until you issue the CANCEL command.

Change-based Recovery: Recovers up to a specified SCN.

Log Sequence Recovery: Recovers up to a specified log sequence number.

Three Backup PL/SQLs for Media Recovery

• RECOVER DATABASE
• RECOVER TABLESPACE
• RECOVER DATAFILE

Archiving Using Hot Backup

Backup archive files to tape
Backup the datafiles
Backup the redo logs
Execute an 'alter system switch logfiles'
Backup the Password and Control files
Perform a full database export of the system.

Restoring From a Hot Backup

1. Complete rebuild from a hot backup
2. Copy the datafiles, archive files, logfiles, control files, and
3. Password files to the target host.
4. Start the database

startup pfile=d:\orant\database\init.ora
recover database; (Applies changes made
while executing the hot backup)

alter database open;

Cold Backup

shutdown immediate

backup all datafiles, redo logs,
control files, parameter files,
and password file to backup media

startup pfile=c:\orant\database\init{sid}.ora

Hot Backup

Alter tablespace user_data
begin backup;

backup all datafiles to backup media

alter tablespace user_data
end backup;

Alter System switch logfile

* The begin backup command prevents the SCN in the datafile header from changing, so in the case of recovery, logs are applied from backup start time.

* Repeat process for all tablespaces

Backup and Recovery Techniques

Recovery: Noarchivelog Mode

1. Shutdown the Server
    Shutdown Abort
2. Restore data, control, and redo log from the last full backup
3. connect / as sysdba
    startup pfile=initDB00.ora

Recovery with Archiving (Complete Recovery)

Enabling Archiving
1. Shutdown immediate
2. Startup mount pfile=initDB00.ora
3. Alter database archivelog
4. Change initDB00.ora setting for (LOG_ARCHIVE_START=TRUE, LOG_ARCHIVE_DEST, LOG_ARCHIVE_FORMAT)
5. Alter database open
6. Shutdown immediate
7. Full Database Backup
8. Startup pfile=initDB00.ora

Method 1: Recovering a Closed Database

Media or hardware failure
1. Shutdown immediate
2. Copy e:\backup\datafile1.dbf c:\data\datafile1.dbf
3. Startup mount pfile=initDB00.ora
4. Recover datafile 'c:\data\datafile1.dbf'
5. Alter Database Open

Method 2. Complete Recovery (Open Database, Initially Open)

Datafile has been removed while the database was open
1. copy e:\backup\datafile1.dbf c:\data\datafile1.dbf
2. recover datafile 'c:\data\datafile1.dbf'
3. alter database datafile 'c:\data\datafile1.dbf' online
4. alter tablespace USER_DATA online;

Method 3. Complete Recovery (Open Database, Initially Closed)

You know there has been a media failure
1. startup mount pfile=initDB00.ora
2. Select * from v$datafile indicates that datafile1.dbf needs recovery
3. alter database datafile 'c:\data\datafile1.dbf' offline
4. alter database open
5. copy e:\backup\datafile1.dbf d:\newmedia\datafile1.dbf
6. alter database rename 'c:\data\datafile1.dbf' to 'd:\newmedia\datafile1.dbf
7. recover datafile 'd:\newmedia\datafile1.dbf'
8. alter tablespace USER_DATA online

Method 4. Complete Recovery (Loss of Datafile with No Backup)

Loss of Datafile with no backup of the datafile but archive logs are available
1. startup mount pfile=initDB00.ora
2. alter database drop datafile 'c:\data\datafile1.dbf'
3. alter tablespace USER_DATA offline immediate;
4. alter database create datafile 'd:\newlocation\datafile1.dbf' as 'c:\data\datafile1.dbf'
5. recover tablespace USER_DATA;
6. alter tablespace USER_DATA online;

Adding Online Redo Log Groups

You have at least two log groups functioning to prevent database failure

      ALTER DATABASE ADD LOGFILE
	(
		'c:\data\log3a.rdo',
		'e:\data\log3b.rdo' 
	) size 250k;

Standby Database Features

A copy of your primary database is kept on a separate machine
Always kept in recovery mode
Activated if the primary database fails and recovery will take too long

Creating the Standby Database

Backup datafiles for primary database
Create the standby database control file
Archive the primary database's current online redo logs
Transfer archived logs, datafiles, and control file to the standby Machine
Mount the standby database
Recover standby database

Back to the LSS "How To" Series Main Page