生产环境Oracle dataguard不同步处理过程分享

一、问题描述

dg环境扩容表空间新增的数据文件部分没有传到备库上去，报ORA-16136: Managed Standby Recovery not active

二、分析及处理过程：

2.1 备库上查看v$managed_standby

SQL>selectprocess,status,sequence#fromv$managed_standby;

PROCESSSTATUSSEQUENCE#

ARCHCLOSING114516

ARCHCLOSING114517

ARCHCONNECTED0

ARCHCLOSING114518

RFSIDLE0

RFSWRITING114519

RFSIDLE0

7rowsselected.

从上面的输出，可以看出，缺少MRP0进程。

2.2 确认报错信息

SQL>alterdatabaserecovermanagedstandbydatabase;

alterdatabaserecovermanagedstandbydatabase

*

ERRORatline1:

ORA-00283:recoverysessioncanceledduetoerrors

ORA-01111:namefordatafile14isunknown-renametocorrectfile

ORA-01110:datafile14:

'/data/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00014'

ORA-01157:cannotidentify/lockdatafile14-seeDBWRtracefile

ORA-01111:namefordatafile14isunknown-renametocorrectfile

ORA-01110:datafile14:

'/data/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00014'

从上面的输出可以看出是14号文件的问题

2.3 备库重新创建14号文件

14号文件本次表空间扩容新增的数据文件。

altersystemsetstandby_file_management=manual;

alterdatabasecreatedatafile'/data/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00014'as'/data/app/oracle/newdata/dataqiye_data9.dbf'

altersystemsetstandby_file_management=auto;

2.4 再次查看警告日志文件

startedlogmergerprocess

TueJan3013:31:352018

ManagedStandbyRecoverynotusingRealTimeApply

ParallelMediaRecoverystartedwith8slaves

Waitingforallnon-currentORLstobearchived...

Allnon-currentORLshavebeenarchived.

MediaRecoveryWaitingforthread1sequence114456

Fetchinggapsequenceinthread1,gapsequence114456-114459

Completed:alterdatabaserecovermanagedstandbydatabasedisconnectfromsession

TueJan3013:33:262018

FAL[client]:Failedtorequestgapsequence

GAP-thread1sequence114456-114459

DBID1477707379branch949967087

FAL[client]:AlldefinedFALservershavebeenattempted.

CheckthattheCONTROL_FILE_RECORD_KEEP_TIMEinitialization

parameterisdefinedtoavaluethat'ssufficientlylarge

enoughtomaintainadequatelogswitchinformationtoresolve

archiveloggaps.

日志出现断档，从主库的归档备份中恢复序列号是sequence114456-114459的归档日志。

2.5 主库上操作

从主库归档备份中恢复所需的归档日志序列(114456-114459)

rmantarget/

restorearchivelogfromsequence114456untilsequence114459;

2.6 备库上观察警告日志输出

RFS[3]:Openedlogforthread1sequence114456dbid1477707379branch949967087

TueJan3013:42:442018

ArchivedLogentry114633addedforthread1sequence114457rlc949967087ID0x0dest2:

TueJan3013:42:442018

ArchivedLogentry114634addedforthread1sequence114456rlc949967087ID0x0dest2:

TueJan3013:42:442018

RFS[4]:AssignedtoRFSprocess5977

RFS[4]:Openedlogforthread1sequence114504dbid1477707379branch949967087

RFS[2]:Openedlogforthread1sequence114503dbid1477707379branch949967087

RFS[3]:Openedlogforthread1sequence114502dbid1477707379branch949967087

TueJan3013:42:472018

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114456.arc

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114457.arc

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114458.arc

TueJan3013:43:082018

ArchivedLogentry114635addedforthread1sequence114502rlc949967087ID0x58146bd8dest2:

TueJan3013:43:082018

ArchivedLogentry114636addedforthread1sequence114503rlc949967087ID0x58146bd8dest2:

TueJan3013:43:082018

ArchivedLogentry114637addedforthread1sequence114504rlc949967087ID0x58146bd8dest2:

TueJan3013:43:172018

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114459.arc

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114460.arc

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114461.arc

[oracle@iZwz9h66josrg1nhghd32dZtrace]$tail-falert_orcl.log

TueJan3013:43:082018

ArchivedLogentry114636addedforthread1sequence114503rlc949967087ID0x58146bd8dest2:

TueJan3013:43:082018

ArchivedLogentry114637addedforthread1sequence114504rlc949967087ID0x58146bd8dest2:

TueJan3013:43:172018

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114459.arc

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114460.arc

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114461.arc

TueJan3013:43:432018

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114462.arc

TueJan3013:43:552018

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114463.arc

TueJan3013:44:112018

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114464.arc

TueJan3013:44:232018

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114465.arc

TueJan3013:44:372018

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114466.arc

TueJan3013:44:512018

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114467.arc

TueJan3013:45:052018

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114468.arc

TueJan3013:45:172018

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114469.arc

TueJan3013:45:312018

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114470.arc

TueJan3013:45:452018

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114471.arc

TueJan3013:46:012018

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114472.arc

TueJan3013:46:142018

MediaRecoveryLog/data/app/oracle/arch/arch_949967087_1_114473.arc

....................
从日志的输出中我们可以看出，备库正在应用归档。

三、验证

3.1 备库验证

SQL>selectprocess,status,sequence#fromv$managed_standby;

PROCESSSTATUSSEQUENCE#

ARCHCLOSING114534

ARCHCLOSING114535

ARCHCONNECTED0

ARCHCLOSING114533

RFSIDLE0

RFSIDLE114536

RFSIDLE0

MRP0WAIT_FOR_LOG114536

RFSIDLE0

9rowsselected.

SQL>selectdatabase_role,switchover_status,open_modefromv$database;

DATABASE_ROLESWITCHOVER_STATUSOPEN_MODE

PHYSICALSTANDBYNOTALLOWEDMOUNTED

SQL>selectdistinctthread#,max(sequence#)over(partitionbythread#)afromv$archived_log;

THREAD#A

1114537

SQL>

3.2 主库验证

SQL>selectdistinctthread#,max(sequence#)over(partitionbythread#)afromv$archived_log;

THREAD#A

1114537

SQL>

四、dg重启关闭命令总结

4.1 关闭备用数据库

SQL>alterdatabaserecovermanagedstandbydatabasecancel;
SQL>shutdownimmediate;

4.2 启动备库

SQL>startupnomount;
SQL>alterdatabasemountstandbydatabase;
SQL>alterdatabaserecovermanagedstandbydatabasedisconnectfromsession;

4.3 启用备库的实时应用

SQL>startupnomount;
SQL>alterdatabasemountstandbydatabase;
SQL>ALTERDATABASERECOVERMANAGEDSTANDBYDATABASEUSINGCURRENTLOGFILEDISCONNECTFROMSESSION;

4.4 启用备库的实时应用+只读模式

SQL>startupnomount;
SQL>alterdatabasemountstandbydatabase;
SQL>alterdatabaseopenreadonly;
SQL>ALTERDATABASERECOVERMANAGEDSTANDBYDATABASEUSINGCURRENTLOGFILEDISCONNECTFROMSESSION;