News

  1. Home
  2. All posts
All posts, Backup, Oracle by Team Ajara

Oracle database restore and recovery scripts

Oracle database restore and recovery scripts.

Media management software is net backup.
If the Media management software is different ,for example Tivoli , neworker etc then replace the “allocate channel” steps given in the below link Oracle Database backup configurations.

prod DB : orclprd
Test DB : orcltst
Catalog: RMANPRD

Restore controlfile

# script name : restore_controlfile.ksh

#!/usr/bin/ksh
export LOGDATE=`date +”%m_%d_%y_%H_%M”`
export ORACLE_SID=orclprd
export ORACLE_HOME=/opt/local/orcltdbs/oracle/12c
export PATH=$PATH:$ORACLE_HOME/bin
export CAT_USER=RMAN_ORCLPRD/RMAN_ORCLPRD@RMANPPRD
export NLS_DATE_FORMAT=”MM-DD-YYYY HH24:MI:SS”
LOGFILE=$ORACLE_HOME/scripts/rman/logs/orcltst_control_$LOGDATE.log
exec > $LOGFILE 2>&1
$ORACLE_HOME/bin/rman << EOF
connect target /
connect CATALOG $CAT_USER
run
{
allocate channel t1 device type sbt
PARMS=’ENV=(SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64.1,NB_ORA_CLIENT=orclpdbs01-bvip,NB_ORA_POLICY=TCP_ORA_ORCLPRD_FULL)’;
set until time “to_date(’09-10-2019 07:16:20′,’MM-DD-YYYY HH24:MI:SS’)”;
restore controlfile;
release channel t1;
}

EOF

controlfile restore will not take much time ,we can run as below

$sh restore_controlfile.ksh

 

export ORACLE_SID=orclprd
export ORACLE_HOME=/opt/local/orcltdbs/oracle/12c
export PATH=$PATH:$ORACLE_HOME/bin$sqlplus “/as sysdba”SQL>Startup mount;

Restore Database files :

#script name : restore_datafiles.ksh
#!/usr/bin/ksh
export LOGDATE=`date +”%m_%d_%y_%H_%M”`
export ORACLE_SID=orclprd
export ORACLE_HOME=/opt/local/orcltdbs/oracle/12c
export PATH=$PATH:$ORACLE_HOME/bin
export NLS_DATE_FORMAT=”MM-DD-YYYY HH24:MI:SS”
LOGFILE=$ORACLE_HOME/scripts/rman/logs/orcltst_restore_$LOGDATE.log
exec > $LOGFILE 2>&1
$ORACLE_HOME/bin/rman << EOF
connect target /
run
{
allocate channel t1 device type sbt
PARMS=’ENV=(SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64.1,NB_ORA_CLIENT=orclpdbs01-bvip,NB_ORA_POLICY=TCP_ORA_ORCLPRD_FULL)’;
allocate channel t2 device type sbt
PARMS=’ENV=(SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64.1,NB_ORA_CLIENT=orclpdbs01-bvip,NB_ORA_POLICY=TCP_ORA_ORCLPRD_FULL)’;
allocate channel t3 device type sbt
PARMS=’ENV=(SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64.1,NB_ORA_CLIENT=orclpdbs01-bvip,NB_ORA_POLICY=TCP_ORA_ORCLPRD_FULL)’;
allocate channel t4 device type sbt
PARMS=’ENV=(SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64.1,NB_ORA_CLIENT=orclpdbs01-bvip,NB_ORA_POLICY=TCP_ORA_ORCLPRD_FULL)’;set until time “to_date(’09-10-2019 07:09:45′,’MM-DD-YYYY HH24:MI:SS’)”;
set newname for datafile ‘/opt/local/orclpdbs/oradata/system001/system001.dbf’ to ‘/opt/local/orcltdbs/oradata/system001/system001.dbf’;
set newname for datafile ‘/opt/local/orclpdbs/oradata/undo001/undotbs001.dbf’ to ‘/opt/local/orcltdbs/oradata/undo001/undotbs001.dbf’;
set newname for datafile ‘/opt/local/orclpdbs/oradata/system002/sysaux001.dbf’ to ‘/opt/local/orcltdbs/oradata/system002/sysaux001.dbf’;
set newname for datafile ‘/opt/local/orclpdbs/oradata/system002/users001.dbf’ to ‘/opt/local/orcltdbs/oradata/system002/users001.dbf’;/* so many database files based on the size of the database*/

/* so many database files based on the size of the database*/

 

set newname for datafile ‘/opt/local/orclpdbs/oradata/data008/EFPRTD_1207_01.dbf’ to ‘/opt/local/orcltdbs/oradata/data008/EFPRTD_1207_01.dbf’;
set newname for tempfile ‘/opt/local/orclpdbs/oradata/system002/temp001.dbf’ to ‘/opt/local/orcltdbs/oradata/system002/temp001.dbf’;
set newname for tempfile ‘/opt/local/orclpdbs/oradata/temp001/dw_temp.dbf’ to ‘/opt/local/orcltdbs/oradata/temp001/dw_temp.dbf’;
set newname for tempfile ‘/opt/local/orclpdbs/oradata/temp002/dw_temp1.dbf’ to ‘/opt/local/orcltdbs/oradata/temp002/dw_temp1.dbf’;
set newname for tempfile ‘/opt/local/orclpdbs/oradata/temp001/dw_temp2.dbf’ to ‘/opt/local/orcltdbs/oradata/temp001/dw_temp2.dbf’;
set newname for tempfile ‘/opt/local/orclpdbs/oradata/temp001/dw_temp3.dbf’ to ‘/opt/local/orcltdbs/oradata/temp001/dw_temp3.dbf’;
set newname for tempfile ‘/opt/local/orclpdbs/oradata/temp002/dw_temp4.dbf’ to ‘/opt/local/orcltdbs/oradata/temp002/dw_temp4.dbf’;
sql “alter database rename file ”/opt/local/orclpdbs/oradata/redo001/redo001a.log” to ”/opt/local/orcltdbs/oradata/redo001/redo001a.log” “;
sql “alter database rename file ”/opt/local/orclpdbs/oradata/redo002/redo001b.log” to ”/opt/local/orcltdbs/oradata/redo002/redo001b.log” “;
sql “alter database rename file ”/opt/local/orclpdbs/oradata/redo003/redo002a.log” to ”/opt/local/orcltdbs/oradata/redo003/redo002a.log” “;
sql “alter database rename file ”/opt/local/orclpdbs/oradata/redo004/redo002b.log” to ”/opt/local/orcltdbs/oradata/redo004/redo002b.log” “;
sql “alter database rename file ”/opt/local/orclpdbs/oradata/redo005/redo003a.log” to ”/opt/local/orcltdbs/oradata/redo005/redo003a.log” “;
sql “alter database rename file ”/opt/local/orclpdbs/oradata/redo001/redo003b.log” to ”/opt/local/orcltdbs/oradata/redo001/redo003b.log” “;
sql “alter database rename file ”/opt/local/orclpdbs/oradata/redo002/redo004a.log” to ”/opt/local/orcltdbs/oradata/redo002/redo004a.log” “;
sql “alter database rename file ”/opt/local/orclpdbs/oradata/redo003/redo004b.log” to ”/opt/local/orcltdbs/oradata/redo003/redo004b.log” “;
sql “alter database rename file ”/opt/local/orclpdbs/oradata/redo004/redo005a.log” to ”/opt/local/orcltdbs/oradata/redo004/redo005a.log” “;
sql “alter database rename file ”/opt/local/orclpdbs/oradata/redo005/redo005b.log” to ”/opt/local/orcltdbs/oradata/redo005/redo005b.log” “;
restore database;
switch datafile all;
switch tempfile all;
release channel t4;
release channel t3;
release channel t2;
release channel t1;
}

EOF

restoring database files will take more time hence run the script in the background as below

$nohup sh restore_datafiles.ksh  > restore_DB_files.log &

 

Restore Archivelog files

#Script name : restore_archive.ksh
#!/usr/bin/ksh
export LOGDATE=`date +”%m_%d_%y_%H_%M”`
export ORACLE_SID=orclprd
export ORACLE_HOME=/opt/local/orcltdbs/oracle/12c
export PATH=$PATH:$ORACLE_HOME/bin
export CAT_USER=RMAN_ORCLPRD/RMAN_ORCLPRD@RMANPPRD
export NLS_DATE_FORMAT=”MM-DD-YYYY HH24:MI:SS”
LOGFILE=$ORACLE_HOME/scripts/rman/logs/orcltst_archive_$LOGDATE.log
exec > $LOGFILE 2>&1
$ORACLE_HOME/bin/rman << EOF
connect target /
connect CATALOG $CAT_USER
run
{
allocate channel t1 device type sbt
PARMS=’ENV=(SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64.1,NB_ORA_CLIENT=orclpdbs01-bvip,NB_ORA_POLICY=TCP_ORA_ORCLPRD_FULL)’;restore archivelog logseq 12304;
release channel t1;
}EOF

$sh restore_archive.ksh

 

Recover database :

#Script name : recover_database.ksh

#!/usr/bin/ksh
export LOGDATE=`date +”%m_%d_%y_%H_%M”`
export ORACLE_SID=orclprd
export ORACLE_HOME=/opt/local/orcltdbs/oracle/12c
export PATH=$PATH:$ORACLE_HOME/bin
export NLS_DATE_FORMAT=”MM-DD-YYYY HH24:MI:SS”
LOGFILE=$ORACLE_HOME/scripts/rman/logs/orcltst_recover_$LOGDATE.log
exec > $LOGFILE 2>&1
$ORACLE_HOME/bin/rman << EOF
connect target /
run
{
allocate channel t1 device type sbt
PARMS=’ENV=(SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64.1,NB_ORA_CLIENT=orclpdbs01-bvip,NB_ORA_POLICY=TCP_ORA_ORCLPRD_FULL)’;

set until time “to_date(’09-10-2019 07:09:45′,’MM-DD-YYYY HH24:MI:SS’)”;
recover database;
release channel t1;
}

EOF

$sh recover_database.ksh

 

See also: