rman的删除语句如下:
run {
delete noprompt obsolete;
crosscheck backup;
delete noprompt expired backup;
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog all completed before 'SYSDATE - 7';
}
之前正常,一段时间之后发现过期的备份和归档日志没有自动删除。
查看过期发现报错:
RMAN> report obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of report command at 01/25/2010 10:01:02
ORA-01775: looping chain of synonyms
(在之前发生过删除表空间,导致部分挪到该表空间上的数据字典表等被删除的事情。)
debug该语句:
rman target / log rman.log trace rman.trc debug
RMAN> run {
2> debug on;
3> report obsolete;
4> debug off;
5> }
RMAN> exit
view rman.trc
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of report command at 01/26/2010 13:49:40
RMAN-06003: ORACLE error from target database:
ORA-01775: looping chain of synonyms
RMAN-06097: text of failing SQL statement: select min(required_checkpoint_scn) ,min(source_resetlogs_scn) into :b1,:b2 from dba_capture where capture_type='LOCAL'
RMAN-06099: error occurred in source file: krmk.pc, line: 19284
DBGMISC: ENTERED krmkursr [13:49:40.492]
发现dba_capture 同义词确实存在问题。
找到该同义词创建脚本:
cd $ORACLE_HOME/rdbms/admin
grep DBA_CAPTURE *
确认为catcap.sql脚本
在测时间上安装该脚本,发现不会对系统产生影响,于是重新执行该脚本。
sqlplus / as sysdba
@catcap.sql
dba_capture 同义词正常。
重新执行删除过期语句,删除成功。
注:通过debug方式可以解决很多rman的问题。