dataguard 由于主库参数未配置归档删除策略导致库归档丢失ORA-16016

发布时间：2020-08-14 10:55:25 来源：ITPUB博客阅读：291 作者：shawnloong 栏目：关系型数据库

dataguard 由于主库参数未配置归档删除策略导致库归档丢失
今天巡检库时候发现备库未启动，监控agent也被人关闭了，手动启动
按照日志惯例打开备库(read_only)，但是在open的过程中显示以下错误

点击(此处)折叠或打开

Standby crash recovery failed to bring standby database to a consistent
point because needed redo hasn't arrived yet.
MRP: Wait timeout: thread 1 sequence# 173
Standby Crash Recovery aborted due to error 16016.
Errors in file /u01/app/oracle/diag/rdbms/orcl_st1/orcl/trace/orcl_ora_29639.trc:
ORA-16016: archived log for thread 1 sequence# 173 unavailable
Recovery interrupted!
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Completed Standby Crash Recovery.
Errors in file /u01/app/oracle/diag/rdbms/orcl_st1/orcl/trace/orcl_ora_29639.trc:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-10458 signalled during: alter database open...

但是手动应用归档显示已经应用完成，后台查日志备库应用到sequence 173而主库已经应用到214，查看归档目录下，发现归档已经丢失了，由于备机关机未启动导致归档未传送过来．
我们在主库中恢复归档

点击(此处)折叠或打开

rman target /
restore archivelog from sequence 173

将恢复出来的日志拷贝到备库
手动注册丢失的归档,这里我用脚本批量处理的;

点击(此处)折叠或打开

for i in `seq 173 214`;do echo "ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_"$i"_956999399.dbf;'";done

173-214为丢失归档的范围

点击(此处)折叠或打开

ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_173_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_174_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_175_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_176_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_177_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_178_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_179_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_180_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_181_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_182_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_183_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_184_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_185_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_186_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_187_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_188_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_189_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_190_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_191_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_192_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_193_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_194_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_195_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_196_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_197_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_198_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_199_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_200_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_201_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_202_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_203_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_204_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_205_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_206_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_207_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_208_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_209_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_210_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_211_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_212_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_213_956999399.dbf';
ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_214_956999399.dbf';

点击(此处)折叠或打开

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

注：如果归档量比较多可以指定并行度

点击(此处)折叠或打开

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL 2 DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

数据库可正常open

点击(此处)折叠或打开

SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

最后设置一下主库归档删除策略
主库操作

点击(此处)折叠或打开

rman target /
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

如果出现以下错误

点击(此处)折叠或打开

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters are successfully stored
RMAN-08591: WARNING: invalid archivelog deletion policy

需要修改修改数据库参数，重启库，然后重新设置归档删除策略

点击(此处)折叠或打开

SQL>alter system set "_log_deletion_policy"=ALL scope=spfile sid='*';

向AI问一下细节

dataguard 由于主库参数未配置归档删除策略导致库归档丢失ORA-16016

猜你喜欢

最新资讯

相关推荐

相关标签