• 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 

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;

Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

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 XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

Perform Manual Switchover on Physical Standby

Connect to proddb database via client: Take a new putty session and connect to proddb database via client machine. Keep querying below sqlplus sys/sys@proddb as sysdba SQL> select name, open_mode, db

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis