Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 26 August 2012

How to take cold backup of oracle database?‎

Offline or Cold backups are performed when the database is completely shutdown. The disadvantage of an offline backup is that it cannot be done if the database needs to be run 24/7. Additionally, you can only recover the database up to the point when the last backup was made unless the database is running in ARCHIVELOG mode. The following files need to be copied while taking cold backup:
All the datafiles, All the tempfiles, All the controlfiles, Listener, tnsnames and sqlnet files, Pfile, spfile and password files
After shutting down the oracle instance, copy all the above required files to a ColdPath, and then finally will start the instance again. You can query to find the required files to be copied.
SQL>Select name from v$controlfile order by 1;
SQL>Select member from v$logfile order by 1;
SQL>Select name from v$tempfile order by 1;
SQL>Select name from v$datafile order by 1;
An Script for Cold Backup on Windows:
This is automated multi channels RMAN cold backup script. The .cmd file is used to connect the target where .rcv file ia the actual backup script. create the folder as per the mentioned path then finally run the cmd file to prepare the backup.Donot forget to check the log file aftter completion of backup.
Sadhan_Cold_Backup.cmd
D:\oracle\ora92\bin\RMAN TARGET SYS/****@***** NOCATALOG CMDFILE=D:\Cold_Backup\Sadhan_Cold_Backup\Sadhan_Cold_Backup.rcv LOG=D:\Cold_Backup\Sadhan_Cold_Backup\Logs\Sadhan_Cold_Backup.LOG
Sadhan_Cold_Backup.rcv
# SADHAN Full RMAN Cold or consistent Backup Script
run
{
shutdown immediate;
startup mount;
allocate channel d1 type disk FORMAT "H:\oraback\SADHAN_COLD_%T_%d-%s_%p.db";
allocate channel d2 type disk FORMAT "H:\oraback\SADHAN_COLD_%T_%d-%s_%p.db";
allocate channel d3 type disk FORMAT "H:\oraback\SADHAN_COLD_%T_%d-%s_%p.db";
allocate channel d4 type disk FORMAT "H:\oraback\SADHAN_COLD_%T_%d-%s_%p.db";
allocate channel d5 type disk FORMAT "H:\oraback\SADHAN_COLD_%T_%d-%s_%p.db";

backup full format "H:\oraback\SADHAN_COLD_%T_%d-%s_%p.db" TAG= "SADHAN_COLD_BACKUP" (database);
copy current controlfile to 'H:\oraback\SADHAN_COLD_Control.db';

release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;

alter database open;
}
An Script for Cold Backup on Linux
#!/bin/bash
export DT=`date "+%Y%m%d"`
export Instance="PROD"
export ColdPath=" $HOME/Cold.Backups/$Instance.$DT"
export FilesList="$ColdPath/FilesList.txt"
export ColdCP="$ColdPath/ColdCP.sh"
mkdir -p $ColdPath
 
echo "1.Gathering the list of all the important files."
 
#Gathering the list of datafiles, tempfiles, redologs and controlfiles
sqlplus -s "/as sysdba" <<EOF | sed -e 's/[[:space:]]*$//' >$FilesList
        set feedback off heading off verify off
        set pagesize 0 linesize 200
        select name from v\$datafile order by 1;
        select name from v\$tempfile order by 1;
        select member from v\$logfile order by 1;
        select name from v\$controlfile order by 1;
EOF
#Adding the listener.ora, tnsnames.ora and sqlnet.ora to the copy script
echo $TNS_ADMIN/listener.ora >> $FilesList
echo $TNS_ADMIN/tnsnames.ora >> $FilesList
echo $TNS_ADMIN/sqlnet.ora >> $FilesList
 
#Adding the pfile, spfile and password file to the copy script
echo $ORACLE_HOME/dbs/initPROD.ora >> $FilesList
echo $ORACLE_HOME/dbs/spfilePROD.ora >> $FilesList
echo $ORACLE_HOME/dbs/orapwPROD >> $FilesList
#Making the cold copy script from list of files
cat $FilesList | awk '{print "cp "$1 ENVIRON["ColdPath"]}' > $ColdCP
 
#Shutting down the oracle instance
echo "2.Shutting down the oracle instance:"
sqlplus -s "/as sysdba" <<EOF
        shutdown immediate;
EOF
#Running the ColdCP script to copy all the cold files to a safe backup path
echo "3.Coppying the cold files. Please wait..."
. $ColdCP
 
#Starting up the oracle instance
echo "4.Starting up the oracle instance:"
sqlplus -s "/as sysdba" <<EOF
        startup;
EOF
echo "5.The end."

5 comments:

  1. thanks for sharing.

    ReplyDelete
  2. hi sir,what the use of creating script for backup.

    ReplyDelete
    Replies
    1. It helps you in automation of backup or scheduling the backup.

      Delete
  3. Sir, can we take cold backup of standby database instead of live database. And can we restore this(standby) backup on production(live) database, if needed??

    ReplyDelete
    Replies
    1. Yes you can,
      Shutdown standby database
      startup mount
      If RAC
      srvctl stop database -d standbydb
      srvctl start database -d standbydb

      Turn the Log apply off
      $ORACLE_HOME/bin/dgmgrl
      connect /
      edit database 'standbydb' set state='LOG-APPLY-OFF';

      Run the rman backup script.

      turnon log apply
      $ORACLE_HOME/bin/dgmgrl
      connect /
      edit database 'standbydb' set state='LOG-APPLY-ON';

      Delete