Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 20, 2002

Listen Software's How To: Backup and Recovery Strategy

By David Nishimoto

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


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 password files to the target host.
  3. 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
  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

) 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

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM