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