Oracle Database Hot Backup and Recovery

This article demonstrates Oracle database hot backup and recovery process. Please note that this method is no longer used in real-time as RMAN does way better job at database backup & recovery.

• Hot Backup Overview

• Taking hot backup

• Important queries

• Parameter file recovery

• Control file recovery

• Redolog recovery

• Tablespace recovery

• Datafile recovery

• System tablespace recovery

• Entire database recovery

• Datafile recovery without backup

This is just good to know activity but do not implement it in real time. Knowing how Oracle hot backup and recovery process works, it helps you understand Oracle RMAN better.

Hot Backup Overview

• Taking the backup while the database is up and running is called hot backup

• During hot backup database will be in fuzzy state and still users can perform transactions which makes backup inconsistent

• Whenever we place a tablespace or database in begin backup mode, following happens

  • The corresponding datafiles header will be freezed i.e CKPT process will not update latest SCN

  • Body of the datafile is still active i.e DBWRn will write the dirty blocks to datafiles

• After end backup, datafile header will be unfreezed and CKPT process will update latest SCN immediately by taking that information from controlfiles

• During hot backup, we will observe much redo generated because oracle will copy entire data block as redo entry into LBC. This is to avoid fractured block

• A block fracture occurs when a block is being read by the backup, and being written to at the same time by DBWR. Because the OS (usually) reads blocks at a different rate than Oracle, your OS copy will pull pieces of an Oracle block at a time. What if the OS copy pulls half a block, and while that is happening, the block is changed by DBWR? When the OS copy pulls the second half of the block it will result in mismatched halves, which Oracle would not know how to reconcile

• This is also why the SCN of the datafile header does not change when a tablespace enters hot backup mode. The current SCNs are recorded in redo, but not in the datafile. This is to ensure that Oracle will always recover over the datafile contents with redo entries. When recovery occurs, the fractured datafile block will be replaced with a complete block from redo, making it whole again

Note: Database should be in archivelog mode to perform hot backup

Taking hot backup

Put the database in begin backup mode

Alter database begin backup;

Copy all database related files to backup location. Create a location on OS where you can copy backup files. Eg /u02/db_backup/hot_bkp_12_jan/

Get the location of datafiles, controlfiles, redolog files

select name from v$datafile;
select name from v$controlfile;
select member from v$logfile;

Copy above files using Linx cp command to backup location

cp <data_files> <backup_location>

Put DB in end backup mode

Alter database end backup;

Take manual controlfile backup

Alter database backup controlfile to '/u02/db_backup/hot_bkp/ctrolfile_DB_level_bkp.ctl';

Alter database backup controlfile to trace as '/u02/db_backup/hot_bkp/ctrolfile_trace_bkp.ctl';

Backup the archive logs generated during the begin backup and end backup mode.

SELECT THREAD#,SEQUENCE#,NAME FROM V$ARCHIVED_LOG;

Copy above archive log files to the backup location using the Linux cp command.

Important queries

Sometimes you want to check which tablespaces inside the database are specifically kept in BACKUP mode. Use below query to get all the tablespaces which are ACTIVE mode (means they are under BEGIN BACKUP mode)

SELECT t.name AS "TB_NAME", d.file# as "DF#", d.name AS "DF_NAME", b.status
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE'
/

The following sample output shows that the example and users tablespaces currently have ACTIVE status

TB_NAME        DF#        DF_NAME                               STATUS
-------------  ---------- ------------------------------------  ------
EXAMPLE        7          /oracle/oradata/proddb/example01.dbf  ACTIVE
USERS          8          /oracle/oradata/proddb/users01.dbf    ACTIVE

Parameter file recovery

There are three scenarios when you loose parameter file and how to recover using hot backups.

Scenario 1: When you have parameter file backup

Let us simulate a failure. We assume that we already have taken the hot backup.

• DB is up and running

• Delete both pfile and spfile under $ORACLE_HOME/dbs location

• Connect to sqlplus and shutdown the database

• Now start the database and it should throw below error

ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initproddb.ora'

Recovering the parameter file

• Exit the sqlplus

• Go to hot backup location

• Copy parameter files (pfile & spfile) to $ORACLE_HOME/dbs location

• Connect to sqlplus and start your database!

Scenario 2: When you do not have parameter file backup

Let us assume we do not have hot backup of the parameter files, then follow below method to recovery your parameter file

• Goto alert log location (Generally /u01/app/oracle/diag/rdbms/testdb/testdb/trace)

• Cat the alert log file (cat alert_testdb.log)

• Find last time database was started

• Copy all the non-default parameters into notepad

• Create a pfile under $ORACLE_HOME/dbs location

• The file name should be init<sid>.ora

• Paste all contents from notepad

• Start the database!

Scenario 3: When you do not have parameter file backup in 11g

From 11g onwards, you can recreate parameter file in case the database is up and running. Even if you loose parameter file but database instance is still running, we can recreate parameter file from memory:

CREATE PFILE FROM MEMORY;

Control file recovery

Simulate failure (we assume that hot backup is already taken). Without shutting down the database, delete database control file at OS level

SQL> select name from v$controlfile;

# rm -rf <controlfile_1> <controlfile_2> ...

Scenario 1: steps for control file incomplete recovery

SQL> shutdown immediate 
SQL> !cp /u03/hotbkp/*.ctl /datafiles/prod/*.ctl 
SQL> startup mount 
SQL> recover database using backup controlfile until cancel; 
SQL> alter database open resetlogs;

Scenario 2: steps for control file complete recovery

SQL> alter database backup controlfile to trace as '/u03/hotbkp/control_bkp.trc'; 
SQL> shutdown immediate

Goto udump location and copy the first create controlfile script to a file called control.sql

SQL> startup nomount 
SQL> @control.sql 
SQL> alter database open;

Note: After creating control files using above procedure, there will be no SCN in that. So server process will write the latest SCN to control files in this situation by taking info from datafile header.

Redolog recovery

Let’s try to recovery our database with loss of redolog files. Simulate a failure and let us delete redologs at OS level

SQL> select member from v$logfile;

rm -rf <redolog_location>

Recover redo log file in archive log mode

SQL> shutdown immediate 
SQL> startup mount 
SQL> recover database until cancel; 
SQL> alter database open resetlogs;

What resetlogs will do?

• Create new redolog files at OS level (location and size will be taken from controlfile) if not already existing

• Resets the log seq number (LSN) to 1, 2, 3 etc for the created files

• Whenever database is opened with resetlogs option, we will say database entered into new incarnation. If database is in new incarnation, the backups which were taken till now are no more useful. So, whenever we perform an incomplete recovery we need to take full backup of database immediately

• We can find the prev incarnation information of a database from below query

select resetlogs_change#,resetlogs_time from v$database;

Tablespace recovery

Delete all the data files related to particular tablespace and follow below recovery steps

SQL> alter tablespace mydata offline; 
SQL> !cp /u03/hotbkp/mydata01.dbf /datafiles/prod 
SQL> recover tablespace mydata; 
SQL> alter tablespace mydata online;

Datafile recovery

Delete only one data file related to a tablespace and follow below steps to recover the data file

SQL> alter database datafile '/datafiles/prod/mydata01.dbf' offline; 
SQL> !cp /u03/hotbkp/mydata01.dbf /datafiles/prod 
SQL> recover 'datafile /datafiles/prod/mydata01.dbf'; 
SQL> alter database datafile '/datafiles/prod/mydata01.dbf' online;

System tablespace recovery

Delete all the data files related to system tablespace and use below steps to recover the system tablespace

SQL> shut immediate 
SQL> !cp /u03/hotbkp/system01.dbf /datafiles/prod 
SQL> startup mount 
SQL> recover tablespace system; 
SQL> alter database open;

Entire database recovery

Let us simulate a failure (make sure you have already taken the database backup). Delete all the data files, control files and redo log files associated to the database

SQL> select name from v$controlfile;
SQL> select name from v$datafile;
SQL> select member from v$logfile;

Follow below steps to perform entire database recovery

SQL> shut immediate / shut abort
SQL> !cp /u03/hotbkp/*.dbf /datafiles/prod 
SQL> startup mount 
SQL> recover database; 
SQL> alter database open;

Note: we can drop a single datafile using below command

SQL> alter database datafile ‘/datafiles/prod/mydata01.dbf’ offline drop;

When we use above command, it will delete the file at OS level, but data dictionary will not be updated and never we can get back that file even if we have backup. So, don’t use this in real time.

Datafile recovery without backup

In case you do not have hot backup of the database or a particular data file still you can recover entire data file but you need to have all the archive logs from the day data file was first created.

To simulate this type of failure, do following

• Create a tablespace

• Create user to store one table inside new tablespace

• Delete data file at OS level

Now use below queries to recover the data file (note we do not have any hot backup)

SQL> alter database datafile 6 offline; 
SQL> alter database create datafile '/u01/app/oracle/oradata/clone1/test01.dbf' as '/u01/app/oracle/oradata/clone1/test01.dbf'; 
SQL> recover tablespace test;
SQL> alter tablespace test online;

Enjoy!