DataGuard脚本2则

一、《下午部署DG，顺手写了几个dg维护脚本》

http://www.easyora.net/blog/scripts_list.html

 

下午给项目搭建了套DG，没啥好说的，顺手写了几个脚本，后面还需完善完善.
1.主备库开机自动启动(自动判断库运行模式，如果为Primary ，则启动到open状态，如果为Standby，则启动到recover managed standby database状态）

#!/bin/sh
#Author:Kevin.yuan
#Create_Time:2008-12-11
#Description:
#1.The script. just put /etc/init.d/rc.local ==> su - oracle -c “/home/oracle/dbstart.sh
#2.The script. is used to startup the datbase automatically when the OS is up.If the database is primary role,then “alter database open”, if the role is standby,then “recover managed standby database…”

#This function is used to get the database’s current role (Primary: 1 /Standby 2 Other(error) 3)
get_role()
{
sqlplus -s “/as sysdba” << !
startup mount;
spool /home/oracle/jurge_role.log
set head off
set feedback off
select database_role from v\$database;
spool off
exit;
!
role=`cat /home/oracle/jurge_role.log|tail -1`
if [ $role = "PRIMARY" ]
then
return 1
elif [ $role = "STANDBY" ]
then
return 2
else
return 3
fi
}
#—–main begin——
source /home/oracle/.bash_profile
lsnrctl start
get_role
role_stat=$?
if [ $role_stat -eq 1 ]
then
sqlplus -s “/as sysdba”<< !
alter database open;
exit
!
elif [ $role_stat -eq 2 ]
then
sqlplus -s “/as sysdba”<< !
alter database recover managed standby database disconnect from session;
exit
!
fi

2.定期自动删除主库/备库端已经在备库上apply过的归档日志，我不太喜欢用OS命令下的rm删除归档，容易出毛病，而且不灵便，还是喜欢调用Rman的delete archivelog命令.

#!/bin/sh
#Create_time:2008.12.11
#Author:Kevin.yuan
#Description:Delete the archived logs which had applied on Standby database using Rman.
source /home/oracle/.bash_profile
Dir=/opt/oracle/script/standby
cd $Dir
echo “rman target / log rman_delete_arch.log append << !">rman_delete_arch.sh
echo “crosscheck archivelog all;”>>rman_delete_arch.sh
sqlplus -s “sys/sys@paybilldg as sysdba”>>rman_delete_arch.sh << !
set head off
set feedback off
select ‘delete noprompt archivelog until logseq ‘||max(sequence#)||’;’ from v\$log_history;
exit
!
echo “exit;”>>rman_delete_arch.sh
echo “!”>>rman_delete_arch.sh
sed -i ‘/^$/d’ rman_delete_arch.sh
##delete the archived logs
sh rman_delete_arch.sh

不过还是写了一个利用os的rm命令来删除的脚本.

#!/bin/sh
#Create_time:2008.12.11
#Author:Kevin.yuan
#Description:Delete the archived logs which had applied on Standby database Using os command.
source /home/oracle/.bash_profile
##Here is the primay/standby archive dest
Arch_dest=/archive/test
Sc_dir=/opt/oracle/script/standby
cd $Sc_dir
sqlplus -s “sys/sys@paybilldg as sysdba”>standby.log << !
set head off
set feedback off
select ‘1_’||a.SEQUENCE#||’_'||a.RESETLOGS_ID||’.dbf’ from v$archived_log a where a.APPLIED=’YES’ and first_time>sysdate-8;
exit
!
##
cat /dev/null>rm_arch.sh
for i in `ls -l $Arch_dest|grep ‘1_’|awk ‘{print $NF}’` ; do
for j in `cat standby.log` ; do
if [ $i = $j ]
then
if [ `grep $i rm_arch.sh|wc -l` -eq 0 ]
then
echo “rm -f $Arch_dest/”$i>>rm_arch.sh
fi
fi
done
done
sh rm_arch.sh

 

二、定期删除DG归档日志的脚本

· 作者 小荷||

· 永久链接: http://www.oracleblog.cn/useful-script/shell-for-delete-standby-arch/

Dataguard的维护稍微麻烦点，不能删除尚未applied的归档日志，但是每次手工去核对就比较麻烦了，今天在pub上看到这样一个要求：“哪位有 standby数据库 定期删除已经apply的archive的shell脚本？”于是就写了个脚本，基本可以满足题目的要求：

1、已经在standby库apply的；

2、2天以上的

脚本如下（具体的脚本和初始化路径可见文章最后的下载tar包，在这里对这个脚本的内容进行下说明）：
OS:

[oracle@standby1 etc]$ cat redhat-release
Enterprise Linux Enterprise Linux AS release 4 (October Update 4)

DB:

BANNER
-------------------------------------------------------------------------------------------------- --
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL / SQL Release 9.2.0.4.0 - Production
CORE     9.2.0.3.0         Production
TNS for Linux : Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

脚本部署路径为：/oracle/del_appl_arc/bin
脚本生成的日志路径：/oracle/del_appl_arc/log

#!/bin/sh
#########################################################################
#         This shell is for primary and standby database                #
#         to rm applied archivelog that before some day ago.            #
#                                                                       #
#       You can define "some day" in variables ${day_before}            #
#            This shell can be put in crontab for auto run              #
#                                                                       #
#            2008-01-18   writen by www.oracleblog.cn                   #
#########################################################################
 
## load profile file
. /oracle/.bash_profile
 
## Path Define
main_path=/oracle/del_appl_arc      < ---------------- 部署的主路径
bin_path =${ main_path }/ bin       < -------------------- 脚本所在路径
log_path =${ main_path }/ log       < --------------------- 脚本日志所在路径
arc_path =/ oracle / arch       < ------------------------- 归档日志所在路径
 
cd ${ bin_path }
 
## Initial script
touch app_arc_name . sh
chmod + x app_arc_name . sh
 
## rm applied archivelog that before ${ day_before } day ago
day_before = 1       < ------------------------------------- 假设删除 1 天前已经规定的日志，这个变量设置为 1 ，你可以设置成其他。 
 
## Db info
dbuser = test
dbpwd = test
dbsid = primary
 
########## Main shell start here ##########
## load exisit archlog list to db       < ------------------------ 从此处开始利用 sqlldr 将 arch 文件列表 load 到数据库中。
sqlplus ${ dbuser }/${ dbpwd }@${ dbsid }<< EOF > /dev/null
drop table ${dbuser}.arc_log_list;
CREATE TABLE ${dbuser}.arc_log_list (arc_name VARCHAR2(2000));
exit;
EOF
 
ls -l ${arc_path}|awk '{print $9}' |grep arc >arc_log_list.tmp
 
echo "load data">>arc_log.ctl
echo "infile 'arc_log_list.tmp'">>arc_log.ctl
echo "replace into table arc_log_list">>arc_log.ctl
echo "fields terminated by X'09'">>arc_log.ctl
echo "(arc_name)">>arc_log.ctl
 
sqlldr ${dbuser}/${dbpwd}@${dbsid} control=arc_log.ctl log=sqlldr_run.log bad=sqlldr_badfile.bad
 
### Create shell for rm applied archive that before some day ago
sqlplus -s ${dbuser}/${dbpwd}@${dbsid} < < EOF > /dev/null      < ------------- 利用 load 数据库中的 arch 列表和
set feedback off                                      < ------------- 数据库中 v $ archived_log ，找出符合条件可以删除的 arch ，同时生成删除脚本。
set pages 0
set head off
set timing off
set echo off
spool app_arc_name . tmp
select ' rm -f '||'${ arc_path }/'|| arc_name from test . arc_log_list
intersect
select ' rm -f '|| name from v \$ archived_log
where DEST_ID = 1 and name like '%. arc '
and SEQUENCE #select max(SEQUENCE#) from v\$archived_log where applied='YES')
and COMPLETION_TIME<=sysdate-${day_before};
spool
exit
EOF
 
## Exec the shell in background mode
cat app_arc_name.tmp |grep -v spooling>app_arc_name.sh
./app_arc_name.sh
mv app_arc_name.sh rm_appl_arc_`date +"%Y%m%d%H%M"`.log
 
mv rm_appl_arc*.log ${log_path}
rm app_arc_name.tmp arc_log.ctl sqlldr_run.log arc_log_list.tmp

完成脚本后，你可以把脚本放入crontab中定期运行，以达到自动删除n天以前且已经applied的归档日志。注意crontab的部署把primary和standby的时间错开，如果同时进行，会对arc_log_list表有争用。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14293419/viewspace-609285/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14293419/viewspace-609285/