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