Listen Software’s How To: Backup and Recovery Strategy

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 password files to the target host.
  3. Start the database
startup pfile=d:orantdatabaseinit.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:orantdatabaseinit{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:backupdatafile1.dbf c:datadatafile1.dbf
  3. Startup mount pfile=initDB00.ora
  4. Recover datafile ‘c:datadatafile1.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:backupdatafile1.dbf c:datadatafile1.dbf
  2. recover datafile ‘c:datadatafile1.dbf’
  3. alter database datafile ‘c:datadatafile1.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:datadatafile1.dbf’ offline
  4. alter database open
  5. copy e:backupdatafile1.dbf d:newmediadatafile1.dbf
  6. alter database rename ‘c:datadatafile1.dbf’ to ‘d:newmediadatafile1.dbf
  7. recover datafile ‘d:newmediadatafile1.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:datadatafile1.dbf’
  3. alter tablespace USER_DATA offline immediate;
  4. alter database create datafile ‘d:newlocationdatafile1.dbf’ as ‘c:datadatafile1.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:datalog3a.rdo',
   'e:datalog3b.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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles