Oracle 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.

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.

Note: You cannot take database hot backup if the DB is running in archive log mode.

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!

Was this article helpful?

Related Articles

Leave a Comment