常用工具_关于RMAN

一、主库重传归档到备库

1.在主库确认归档序号

select NAME,DEST_ID,THREAD#,SEQUENCE#,COMPLETION_TIME,REGISTRAR,APPLIED,DELETED from v$ARCHIVED_LOG where SEQUENCE# between 44561 and 44566 and THREAD#=3 and DEST_ID=1;
select NAME,DEST_ID,THREAD#,SEQUENCE#,COMPLETION_TIME,REGISTRAR,APPLIED,DELETED from v$ARCHIVED_LOG where SEQUENCE# between 44159 and 44160 and THREAD#=4 and DEST_ID=1;

2.在备库执行获取归档命令

rman target sys/password@ip:1521/service_name auxiliary sys/password@ip:1521/service_name
backup as copy archivelog sequence between 44561 and 44566 thread 3 auxiliary format '+DATA';
backup as copy archivelog sequence between 44159 and 44160 thread 4 auxiliary format '+DATA';

3.在备库注册归档，少量使用alter命令；多量使用catalog命令。

alter database register physical logfile '归档路径';
catalog start with '+DATA/dghxa/archivelog/';

二、清理重复日志

1.主库查看重复日志的序号

select t.dest_id,t.thread#,t.sequence#,t.name,t.CREATOR,t.REGISTRAR,t.ARCHIVED,t.APPLIED,t.DELETED,t.status from v$archived_log t,
(select thread#,sequence# from v$archived_log group by thread#,sequence# having count(sequence#)>1) a
where t.thread# = a.thread# and t.sequence# = a.sequence#
order by dest_id,thread#,sequence#;

2.生成清理归档命令，rman执行。

select ' delete noprompt archivelog '''||name||''';' from (
select t.thread#,t.sequence#,t.name,t.CREATOR,t.REGISTRAR,t.ARCHIVED,t.APPLIED,t.DELETED,t.status from v$archived_log t,
(select thread#,sequence# from v$archived_log group by thread#,sequence# having count(sequence#)>1) a
where t.thread# = a.thread# and t.sequence# = a.sequence#
order by thread#,sequence# );

也可以使用rownum=1，待测。
--提取文件 奇数行
awk 'NR%2 != 0' 1.txt > 2.txt

3.清理v$archived_log视图中记录，采用sys.dbms_backup_restore.resetCfileSection(11);清理：

execute sys.dbms_backup_restore.resetCfileSection(11);

4.重新注册

crosscheck archivelog all;
catalog start with '+DATA/dghxa/archivelog/';

三、手动清理归档

rman target /
list archivelog all;
list copy of archivelog until time 'SYSDATE-3';
delete archivelog until time 'SYSDATE-2';

**一般归档满被业务发现，此时的库和rman都已经进不去了，所以需要手工清理一下，然后使用rman；
*在比较极端的情况下可以使用find命令清理较多归档，慎用

手动清理文件0：清空监听的日志文件
$ORACLE_BASE/diag/tnslsnr/oracle/listener/trace/listener.log

手动清理文件1
--30天以前的，find到的文件，并删除

find /u01/app/oracle/diag/rdbms/fkdb/fkdb2/trace -mtime +30 -type f -exec ls -lrt {} \;
find /u01/app/oracle/diag/rdbms/fkdb/fkdb2/trace -mtime +20 -type f -exec rm -rf {} \;

find /u01 -size +1000M
find /u01/app/oracle/diag/rdbms/ods/ods1/trace -mtime +1 -type f -exec rm -f {} \;

cat /dev/null > ods1_ora_118800.trc
cat /dev/null > fkdb1_cjq0_94594.trc

 

四、清理归档脚本的部署
--主库部署

1.定时任务
crontab -e
#delete archive log
00 1 * * * sh /home/oracle/tools/clear_log/clear_arch.sh 1>/tmp/clear_arch.log 2>&1

2.清归档脚本
mkdir -p /home/oracle/tools/clear_log
vi /home/oracle/tools/clear_log/clear_arch.sh
#!/bin/sh
. ~/.bash_profile
cd /home/oracle/tools/clear_log
rman target / log=clear_arch.log < clear_arch.sql

3.调用脚本：设置保留归档时间
vi /home/oracle/tools/clear_log/clear_arch.sql
delete force noprompt archivelog until time 'sysdate-3';

4.测试：
sh /home/oracle/tools/clear_log/clear_arch.sh 1>/tmp/clear_arch.log 2>&1
tail -100f /tmp/clear_arch.log
显示为：
RMAN> RMAN>

 

--备库部署

#delete archive log
30 4 * * * sh /home/oracle/tools/standby/del_stdby_archivelog_rman.sh 1>/tmp/clear_arch.log 2>&1

-rwxr--r-- 1 oracle oinstall 1231 Mar 19 2019 /home/oracle/tools/standby/del_stdby_archivelog_rman.sh

dgsoutha:/home/oracle/tools/standby$ cat /home/oracle/tools/standby/del_stdby_archivelog_rman.sh
#!/bin/sh
# Create_time:2012.01.01
# Author: WonZhao
# Description:Delete the archived logs on Standby which had been applied
# Usage:
# Last modify: 2014.01.03
# Note:

#Initial Parameters
_SCRIPTPATH=/home/oracle/tools/standby/
_DAYBEFOR=3

#OSTYPE
OSTYPE=`uname -s`
if [ $OSTYPE = "AIX" ]
then
. ~/.profile
else
. ~/.bash_profile
fi

#Main
cd ${_SCRIPTPATH}
echo "rman target / log=rman_delete_arch.log << EOF" > tmp_delete_archive_rman.tmp
sqlplus -s "/ as sysdba" >> tmp_delete_archive_rman.tmp << EOF
set head off
set feedback off
SELECT 'delete noprompt archivelog until logseq ' || MAX(D.SEQUENCE#) ||
' thread ' || D.THREAD# || ';'
FROM V\$ARCHIVED_LOG D,(SELECT MAX(A.COMPLETION_TIME) -${_DAYBEFOR} COMPLETION_TIME, A.THREAD#
FROM V\$ARCHIVED_LOG A
WHERE APPLIED = 'YES'
GROUP BY A.THREAD#) TMP
WHERE D.THREAD# = TMP.THREAD#
AND D.COMPLETION_TIME < TMP.COMPLETION_TIME
GROUP BY D.THREAD#;
exit
EOF

echo "exit" >> tmp_delete_archive_rman.tmp
echo "EOF" >> tmp_delete_archive_rman.tmp
sed '/^$/d' tmp_delete_archive_rman.tmp > tmp_delete_archive_rman.sh

#execute delete script
rm tmp_delete_archive_rman.tmp
sh tmp_delete_archive_rman.sh
#rm tmp_delete_archive_rman.sh

 

五、rman的其他命令

RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;