• Arun Kumar

Oracle Database Cold Backup & Recovery

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

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

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

  4. In real time, we will perform cold backup very rarely


Steps to 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;

Note: archives are not required to take back up with cold backup


Steps to recover datafile (noarchivelog)


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


Steps to recover redologfile (noarchivelog)



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;

Steps to recover controlfile (noarchivelog)


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;



560 views

Recent Posts

See All

Oracle 12c to 19c Database Upgrade

In this article we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c Pre-upgrade tasks Upgrade using DBUA Upgrade using Manual Method You can perform a dir