Data Guard 备库的归档日志 需要手工删除,主库上若有rman备份,可在backup命令中加delete。
$ cat delarch.sh
#!/bin/bash
# Program:
# This script is to delete the arch logs for the standby database after it has applied the logs to the instance.
# History:
# 2016/09/21 sxhua First release
source /home/oracle/.bash_profile
ArgNum=1
retention=$1
script=`basename $0`
dir=/tmp
tmpf=$dir/.$script.tmp
LogFile=/home/oracle/maintain/delete_arch.log
echo "" >> $LogFile
echo "" >> $LogFile
echo "#### `date +'%Y-%m-%d %H:%M:%S'` START ####" >> $LogFile
echo "" >> $LogFile
# basename dirname
usage()
{ #usage
echo " USAGE: `basename $0` $retention" >> $LogFile
exit 2
}
if [ ! $# -eq $ArgNum ];then
echo " "
echo " Incorrect parameter" >> $LogFile
usage
fi
# primary: CURRENT standby: STANDBY
function GetDBRole
{
sqlplus -S /nolog <<EFF
<eof
connect / as sysdba
set head off
set feedback off
set pages 0
select controlfile_type from v\$database;
exit
EFF
return
}
# get archived log list for standby database
function GetLogListForStandby
{
sqlplus -S /nolog <<EFF > $tmpf
connect / as sysdba
set head off
set feedback off
set pages 0
select name from (
select name,sequence#,row_number() over(partition by a.sequence# order by name) rn,
count(decode(applied,'YES',1,null)) over (partition by a.sequence#) cn
from v\$archived_log a
where a.completion_time< sysdate - $retention
and a.resetlogs_id in (
select i.resetlogs_id from v\$database_incarnation i where status = 'CURRENT')
)
where rn=1 and cn=1
order by sequence#;
exit
EFF
return
}
# get archived log list for primary database
function GetLogListForPrimary
{
sqlplus -S /nolog <<EFF > $tmpf
connect / as sysdba
set head off
set feedback off
set pages 0
select name from (
select name,sequence#,row_number() over(partition by a.sequence# order by name) rn,
sum(backup_count) over(partition by a.sequence# ) bk_cnt,
count(decode(applied,'YES',1,null)) over (partition by a.sequence#) cn
from v\$archived_log a
where a.completion_time< sysdate - $retention
and completion_time and a.resetlogs_id in (
select i.resetlogs_id from v\$database_incarnation i where status = 'CURRENT')
)
where rn=1 and cn=1 and bk_cnt>0
order by sequence#;
exit
EFF
return
}
# delete control file recoder
function DelControlFileRecorder
{
rman target / <<eof
crosscheck archivelog all;
delete noprompt expired archivelog all;
exit;
EOF
}
# check database role
DBROLE=`GetDBRole`
NUM=0
if [ $DBROLE = "CURRENT" ];then
echo "It's a primary database ......" >> $LogFile
# get archived log list for primary
GetLogListForPrimary
elif [ $DBROLE = "STANDBY" ];then
echo "It's a standby database ......" >> $LogFile
# get archived log list for standby
GetLogListForStandby
fi
echo "deleting archived log files ......" >> $LogFile
if [ -n $tmpf ]; then
for ARCH in `cat $tmpf`;do
if [ -f $ARCH ];then
NUM=`expr $NUM + 1`
rm -f $ARCH
fi
done
fi
rm -f $tmpf
echo "finished deleting $NUM files" >> $LogFile
# delete control file recoder
echo "deleting control file archivelog recoder ......" >> $LogFile
DelControlFileRecorder
echo "" >> $LogFile
echo "#### `date +'%Y-%m-%d %H:%M:%S'` END ####" >> $LogFile
echo "" >> $LogFile
0 */8 * * * /home/oracle/maintain/delete_arch.sh 1 > /dev/null 2>&1
</eof
</eof
$ cat delarch.sh
#!/bin/bash
# Program:
# This script is to delete the arch logs for the standby database after it has applied the logs to the instance.
# History:
# 2016/09/21 sxhua First release
source /home/oracle/.bash_profile
ArgNum=1
retention=$1
script=`basename $0`
dir=/tmp
tmpf=$dir/.$script.tmp
LogFile=/home/oracle/maintain/delete_arch.log
echo "" >> $LogFile
echo "" >> $LogFile
echo "#### `date +'%Y-%m-%d %H:%M:%S'` START ####" >> $LogFile
echo "" >> $LogFile
# basename dirname
usage()
{ #usage
echo " USAGE: `basename $0` $retention" >> $LogFile
exit 2
}
if [ ! $# -eq $ArgNum ];then
echo " "
echo " Incorrect parameter" >> $LogFile
usage
fi
# primary: CURRENT standby: STANDBY
function GetDBRole
{
sqlplus -S /nolog <<EFF
<eof
connect / as sysdba
set head off
set feedback off
set pages 0
select controlfile_type from v\$database;
exit
EFF
return
}
# get archived log list for standby database
function GetLogListForStandby
{
sqlplus -S /nolog <<EFF > $tmpf
connect / as sysdba
set head off
set feedback off
set pages 0
select name from (
select name,sequence#,row_number() over(partition by a.sequence# order by name) rn,
count(decode(applied,'YES',1,null)) over (partition by a.sequence#) cn
from v\$archived_log a
where a.completion_time< sysdate - $retention
and a.resetlogs_id in (
select i.resetlogs_id from v\$database_incarnation i where status = 'CURRENT')
)
where rn=1 and cn=1
order by sequence#;
exit
EFF
return
}
# get archived log list for primary database
function GetLogListForPrimary
{
sqlplus -S /nolog <<EFF > $tmpf
connect / as sysdba
set head off
set feedback off
set pages 0
select name from (
select name,sequence#,row_number() over(partition by a.sequence# order by name) rn,
sum(backup_count) over(partition by a.sequence# ) bk_cnt,
count(decode(applied,'YES',1,null)) over (partition by a.sequence#) cn
from v\$archived_log a
where a.completion_time< sysdate - $retention
and completion_time and a.resetlogs_id in (
select i.resetlogs_id from v\$database_incarnation i where status = 'CURRENT')
)
where rn=1 and cn=1 and bk_cnt>0
order by sequence#;
exit
EFF
return
}
# delete control file recoder
function DelControlFileRecorder
{
rman target / <<eof
crosscheck archivelog all;
delete noprompt expired archivelog all;
exit;
EOF
}
# check database role
DBROLE=`GetDBRole`
NUM=0
if [ $DBROLE = "CURRENT" ];then
echo "It's a primary database ......" >> $LogFile
# get archived log list for primary
GetLogListForPrimary
elif [ $DBROLE = "STANDBY" ];then
echo "It's a standby database ......" >> $LogFile
# get archived log list for standby
GetLogListForStandby
fi
echo "deleting archived log files ......" >> $LogFile
if [ -n $tmpf ]; then
for ARCH in `cat $tmpf`;do
if [ -f $ARCH ];then
NUM=`expr $NUM + 1`
rm -f $ARCH
fi
done
fi
rm -f $tmpf
echo "finished deleting $NUM files" >> $LogFile
# delete control file recoder
echo "deleting control file archivelog recoder ......" >> $LogFile
DelControlFileRecorder
echo "" >> $LogFile
echo "#### `date +'%Y-%m-%d %H:%M:%S'` END ####" >> $LogFile
echo "" >> $LogFile
0 */8 * * * /home/oracle/maintain/delete_arch.sh 1 > /dev/null 2>&1
</eof
</eof
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/807718/viewspace-2125290/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/807718/viewspace-2125290/