• Arun Kumar

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

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



793 views

Recent Posts

See All

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With Citrix XenServer, you must create your own ISO repository to upload ISO files Create ISO director