Sometimes we may created manual standby database for standard edition databases, by that time it required to apply archivelogs manually to sync-up the standby database with production database.
Here you can find automated script for applying newly generated archivelogs to standby database.
Note: For copying archivelogs from Production to standby server, need to configure passwordless connectivity between servers.
Find below list of scripts need to be copy:
1) main_db_synch_300.sh - This main script will call below list of scripts.
a) db_synch_301.sh
b) db_synch_302.sh
c) db_synch_303.sh
d) getlog.sql
e) getlog_prod.sql
f) getlog_standby.sql
g) getname_prod.sql
Find below detailed sctipts:
1) main_db_synch_300.sh - This is the main script to call.
################### Begin Script main_db_synch_300.sh##################
## Author : Chowdari Mathukumilli
## Description : This automated script will be useful for synch-up
## standby database with Production database even
## database in standard edition.
## Need to put standby database in mount standby database.
## Call Syntax : Schedule below line in standby server crontab file.
## Based on your requirement you can schedule it like every
## 15mins, 30mins or 60mins etc. and also need to add both production
## and standby database tns entries in standby server.
## 00 * * * * /u01/app/oracle/db_scripts/main_db_synch_300.sh
## Script Changes: Below scriptyou need to change
## ORACLE_HOME -- Change it to your DB Home location
## SCR_HOME -- Change it to your sych-up scripts location
## ORACLE_SID -- Change it to your DB SID name
## PASSWORD -- Change it to ur sys password (Primary or Standby)
## hostname -- change it to your primary database server name
## Total files : 8
##
################################################################
#bin ! sh
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export SCR_HOME=/u01/app/oracle/db_scripts
export PATH=$ORACLE_HOME/bin:$PATH:
export DDATE=`date +%Y-%m-%d`
echo "*************** PROD_DB ***************"
export ORACLE_SID=PROD_DB
echo "Performing switch logfile in Production database"
$SCR_HOME/db_synch_301.sh
echo "Finding current archivelog in PROD_DB production DB"
export FIND_CUR_ARCH_PROD=`sqlplus -s sys/PASSWORD@PROD_DB as sysdba @$SCR_HOME/getlog_prod.sql | sed '/^$/d'`
echo $FIND_CUR_ARCH_PROD
echo "Find last applied archive log in stndby side"
export LAST_APPL_ARCH_STAND=`sqlplus -s sys/PASSWORD@STANDBY_DB as sysdba @$SCR_HOME/getlog_standby.sql`
echo $LAST_APPL_ARCH_STAND
export DR_ARCH_LOCATION=/u01/oracle/STANDBY_DB/FRA/ARCH
for (( i = $LAST_APPL_ARCH_STAND; i <= $FIND_CUR_ARCH_PROD; i++ ))
do
if [ $i -le $FIND_CUR_ARCH_PROD ]; then
echo "Below execution will help to find new generated archivelogs with location in primary side"
export NEW_GENERATED_ARCH_PROD=`sqlplus -s sys/PASSWORD@PROD_DB as sysdba @$SCR_HOME/getname_prod.sql $i | tail -2 |head -1`
echo $NEW_GENERATED_ARCH_PROD
echo "Copying achives from prod to standby side $i"
scp -r oracle@hostname:$NEW_GENERATED_ARCH_PROD $DR_ARCH_LOCATION
else
echo " Nothing to DO - Already Standby database is Synch-up with production database - :$i = $FIND_CUR_ARCH_PROD "
exit;
fi
done
echo "Backup archivelogs not backed up in prod"
$SCR_HOME/db_synch_302.sh
echo "Applying archives in backup side"
$SCR_HOME/db_synch_303.sh
sleep 50
#Find latest applied archivelog in Standby Database
export LATEST_APPLIED_ARCH_STAND=`sqlplus -s sys/<PASSWORD>@STANDBY_DB as sysdba @$SCR_HOME/getlog.sql`
echo "Applied arch : $LATEST_APPLIED_ARCH_STAND"
###################### END SCRIPT - main_db_synch_300.sh #############
2) db_synch_301.sh - This script is for performing switch logfile in Production database.
###################Begin Script - db_synch_301.sh #######################
## Author : Chowdari Mathukumilli
## Description : This file is for switch logfile
##
######################################################################
sqlplus /nolog << EOF
connect sys/<PASSWORD>@PROD_DB as sysdba
alter system switch logfile;
disc;
quit;
###################### END SCRIPT - db_synch_301.sh## ################
3) getlog_prod.sql - This sql is used for finding current running archivelog in production side
###################Begin Script - getlog_prod.sql #########################
## Author : Chowdari Mathukumilli
## Description : This sql script is for finding current archivelog in prod
##
######################################################################
set head off;
select lhmax "Current Seq running/applied :"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, trim(max(sequence#)) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;
#disc;
#quit;
###################### END SCRIPT - getlog_prod.sql## #####################
4) getlog_standby.sql - This SQL script is used for find last applied archivelog in stndby side
###################Begin Script - getlog_standby.sql ########################
## Author : Chowdari Mathukumilli
## Description : This sql script is for find last applied archivelog in stndby side
##
#####################################################################
set heading off;
select max(sequence#) from v$archived_log where applied='YES';
exit;
###################### END SCRIPT - getlog_standby.sql## ###############
5) getname_prod.sql - This sql is for find new generated archivelog files in production side
###################Begin Script - getname_prod.sql #######################
## Author : Chowdari Mathukumilli
##
#######################################################################
set heading off;
select name from v$archived_log where sequence# = '&&1';
exit;
###################### END SCRIPT - getname_prod.sql## ##################
6) db_synch_302.sh - Backup the archivelogs in production side
###################Begin Script - db_synch_302.sh ##########################
## Author : Chowdari Mathukumilli
##
########################################################################
rman target sys/<PASSWORD>@PROD_DB << EOF
crosscheck archivelog all;
backup archivelog all not backed up;
quit;
###################### END SCRIPT - db_synch_302.sh ## ####################
7) db_synch_303.sh - Applying archives in backup side
###################Begin Script - db_synch_303.sh ###########################
## Author : Chowdari Mathukumilli
##
#########################################################################
rman target / << EOF
catalog start with '/u01/oracle/STANDBY_DB/FRA/ARCH';
YES
DELETE noprompt COPY OF ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-5';
quit;
###################### END SCRIPT - db_synch_303.sh ## #####################
Note: This (main_db_synch_300.sh) script should schedule in standby server and need to add both production and standby database tns entries in standby server.
Thats it.. If you any problems with this script please contact me :-).
Thanks,
Chowdari
Here you can find automated script for applying newly generated archivelogs to standby database.
Note: For copying archivelogs from Production to standby server, need to configure passwordless connectivity between servers.
Find below list of scripts need to be copy:
1) main_db_synch_300.sh - This main script will call below list of scripts.
a) db_synch_301.sh
b) db_synch_302.sh
c) db_synch_303.sh
d) getlog.sql
e) getlog_prod.sql
f) getlog_standby.sql
g) getname_prod.sql
Find below detailed sctipts:
1) main_db_synch_300.sh - This is the main script to call.
################### Begin Script main_db_synch_300.sh##################
## Author : Chowdari Mathukumilli
## Description : This automated script will be useful for synch-up
## standby database with Production database even
## database in standard edition.
## Need to put standby database in mount standby database.
## Call Syntax : Schedule below line in standby server crontab file.
## Based on your requirement you can schedule it like every
## 15mins, 30mins or 60mins etc. and also need to add both production
## and standby database tns entries in standby server.
## 00 * * * * /u01/app/oracle/db_scripts/main_db_synch_300.sh
## Script Changes: Below scriptyou need to change
## ORACLE_HOME -- Change it to your DB Home location
## SCR_HOME -- Change it to your sych-up scripts location
## ORACLE_SID -- Change it to your DB SID name
## PASSWORD -- Change it to ur sys password (Primary or Standby)
## hostname -- change it to your primary database server name
## Total files : 8
##
################################################################
#bin ! sh
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export SCR_HOME=/u01/app/oracle/db_scripts
export PATH=$ORACLE_HOME/bin:$PATH:
export DDATE=`date +%Y-%m-%d`
echo "*************** PROD_DB ***************"
export ORACLE_SID=PROD_DB
echo "Performing switch logfile in Production database"
$SCR_HOME/db_synch_301.sh
echo "Finding current archivelog in PROD_DB production DB"
export FIND_CUR_ARCH_PROD=`sqlplus -s sys/PASSWORD@PROD_DB as sysdba @$SCR_HOME/getlog_prod.sql | sed '/^$/d'`
echo $FIND_CUR_ARCH_PROD
echo "Find last applied archive log in stndby side"
export LAST_APPL_ARCH_STAND=`sqlplus -s sys/PASSWORD@STANDBY_DB as sysdba @$SCR_HOME/getlog_standby.sql`
echo $LAST_APPL_ARCH_STAND
export DR_ARCH_LOCATION=/u01/oracle/STANDBY_DB/FRA/ARCH
for (( i = $LAST_APPL_ARCH_STAND; i <= $FIND_CUR_ARCH_PROD; i++ ))
do
if [ $i -le $FIND_CUR_ARCH_PROD ]; then
echo "Below execution will help to find new generated archivelogs with location in primary side"
export NEW_GENERATED_ARCH_PROD=`sqlplus -s sys/PASSWORD@PROD_DB as sysdba @$SCR_HOME/getname_prod.sql $i | tail -2 |head -1`
echo $NEW_GENERATED_ARCH_PROD
echo "Copying achives from prod to standby side $i"
scp -r oracle@hostname:$NEW_GENERATED_ARCH_PROD $DR_ARCH_LOCATION
else
echo " Nothing to DO - Already Standby database is Synch-up with production database - :$i = $FIND_CUR_ARCH_PROD "
exit;
fi
done
echo "Backup archivelogs not backed up in prod"
$SCR_HOME/db_synch_302.sh
echo "Applying archives in backup side"
$SCR_HOME/db_synch_303.sh
sleep 50
#Find latest applied archivelog in Standby Database
export LATEST_APPLIED_ARCH_STAND=`sqlplus -s sys/<PASSWORD>@STANDBY_DB as sysdba @$SCR_HOME/getlog.sql`
echo "Applied arch : $LATEST_APPLIED_ARCH_STAND"
###################### END SCRIPT - main_db_synch_300.sh #############
2) db_synch_301.sh - This script is for performing switch logfile in Production database.
###################Begin Script - db_synch_301.sh #######################
## Author : Chowdari Mathukumilli
## Description : This file is for switch logfile
##
######################################################################
sqlplus /nolog << EOF
connect sys/<PASSWORD>@PROD_DB as sysdba
alter system switch logfile;
disc;
quit;
###################### END SCRIPT - db_synch_301.sh## ################
3) getlog_prod.sql - This sql is used for finding current running archivelog in production side
###################Begin Script - getlog_prod.sql #########################
## Author : Chowdari Mathukumilli
## Description : This sql script is for finding current archivelog in prod
##
######################################################################
set head off;
select lhmax "Current Seq running/applied :"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, trim(max(sequence#)) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;
#disc;
#quit;
###################### END SCRIPT - getlog_prod.sql## #####################
4) getlog_standby.sql - This SQL script is used for find last applied archivelog in stndby side
###################Begin Script - getlog_standby.sql ########################
## Author : Chowdari Mathukumilli
## Description : This sql script is for find last applied archivelog in stndby side
##
#####################################################################
set heading off;
select max(sequence#) from v$archived_log where applied='YES';
exit;
###################### END SCRIPT - getlog_standby.sql## ###############
5) getname_prod.sql - This sql is for find new generated archivelog files in production side
###################Begin Script - getname_prod.sql #######################
## Author : Chowdari Mathukumilli
##
#######################################################################
set heading off;
select name from v$archived_log where sequence# = '&&1';
exit;
###################### END SCRIPT - getname_prod.sql## ##################
6) db_synch_302.sh - Backup the archivelogs in production side
###################Begin Script - db_synch_302.sh ##########################
## Author : Chowdari Mathukumilli
##
########################################################################
rman target sys/<PASSWORD>@PROD_DB << EOF
crosscheck archivelog all;
backup archivelog all not backed up;
quit;
###################### END SCRIPT - db_synch_302.sh ## ####################
7) db_synch_303.sh - Applying archives in backup side
###################Begin Script - db_synch_303.sh ###########################
## Author : Chowdari Mathukumilli
##
#########################################################################
rman target / << EOF
catalog start with '/u01/oracle/STANDBY_DB/FRA/ARCH';
YES
DELETE noprompt COPY OF ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-5';
quit;
###################### END SCRIPT - db_synch_303.sh ## #####################
Note: This (main_db_synch_300.sh) script should schedule in standby server and need to add both production and standby database tns entries in standby server.
Thats it.. If you any problems with this script please contact me :-).
Thanks,
Chowdari
Thanks Chowdari for sharing this post,
ReplyDeleteregards,
Foued