- Arun Kumar
Oracle Database Cold Backup & Recovery
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
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;