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


1,769 views

Recent Posts

See All