Oracle Database Cold Backup & Recovery

Oracle Cold Database backup is rarely used these days. DBAs hardly take cold database backup but sometimes it is important to take one before decommissioning a database.

• Key points

• Take Cold Backup

• Recover Data File with Cold Backup

• Recover Redolog File with Cold Backup

• Recover Control File with Cold Backup

Key Points

• Backup is a copy of original data which will be used to recover databases

• If the data is reproducible and backup not existing, still we can recover the data. But it is a tedious and time consuming task

• Taking backup after shutting down the database is called cold backup and because no transactions exist, the backup will be consistent

• In real time, we will perform cold backup very rarely

Take Cold Backup

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

[oracle@server1 ~]$ mkdir /u03/coldbkp
[oracle@server1 ~]$ cp /datafiles/prod/*.dbf /u03/coldbkp
[oracle@server1 ~]$ cp /datafiles/prod/*.log /u03/coldbkp
[oracle@server1 ~]$ cp /datafiles/prod/*.ctl /u03/coldbkp
[oracle@server1 ~]$ cp $ORACLE_HOME/dbs/*.ora /u03/coldbkp
[oracle@server1 ~]$ sqlplus "/ as sysdba" 

SQL> startup 
SQL> alter database backup controlfile to trace;

Archives are not required to be copied with cold backup

Recover Data File with Cold Backup

Recover data files with cold backup for a no-archivelog database

SQL> shutdown immediate 
SQL> !cp /u03/coldbkp/*.dbf /datafiles/prod 
SQL> !cp /u03/coldbkp/*.ctl /datafiles/prod 
SQL> !cp /u03/coldbkp/*.log /datafiles/prod 
SQL>startup

Recover Redolog File with Cold Backup

Recover redolog files with cold backup for a no-archivelog database

SQL> shutdown immediate 
SQL> !cp /u03/coldbkp/*.dbf /datafiles/prod 
SQL> !cp /u03/coldbkp/*.ctl /datafiles/prod 
SQL> recover database until cancel; 
SQL> alter database open resetlogs;

Recover Control File with Cold Backup

Recover control files with cold backup for a no-archivelog database

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