RAC to ADG 出現 ORA-16086
環境
Solaris 11
Oracle 12.2.0.1 EE
Oracle 12.2.0.1 EE
異常
RAC to ADG 出現 ORA-16086細節
建好ADG後, 查看同步狀態, 有一個 ORA-16086 錯誤
sys@HISDB:HISRAC1> @arcdestINST_ID DEST STATUS ERROR
---------- ---- --------- -----------------------------------------------------------------
1 1 VALID
1 2 VALID
1 3 VALID
2 1 VALID
2 2 ERROR ORA-16086: Redo data cannot be written to the standby redo log
2 3 VALID
6 rows selected.
NAME VALUE
-------------------- ----------------------------------------------------------------------------------------------------
log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=HISDB
log_archive_dest_2 service="hisdb_6f", SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1
reopen=300 db_unique_name="hisdb_6f" net_timeout=30, valid_for=(online_logfile,all_roles)
log_archive_dest_3 service="hisdb_9f", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connection
s=1 reopen=300 db_unique_name="hisdb_9f" net_timeout=30, valid_for=(online_logfile,all_roles)
sys@HISDB:HISRAC2> @dgstat
------------------------ ------------- ---- ------ ------ --- -------------- ------------------------------------------------------------
Log Transport Services Control 0 28493 0 YES 05-31 16:19:45 ARC1: Beginning to archive T-2.S-10686 (31430863-31431827)
Log Transport Services Control 0 28494 0 YES 05-31 16:19:45 ARC1: Completed archiving T-2.S-10686 (31430863-31431827)
Log Transport Services Control 0 28495 0 YES 05-31 16:19:46 ASYNC: Completed archiving log 3 thread 2 sequence 10686
Log Transport Services Control 0 28496 0 YES 05-31 16:19:46 ASYNC: Beginning to archive log 4 thread 2 sequence 10687
Log Transport Services Warning 2 28497 0 NO 05-31 16:20:58 LGWR: Standby redo logfile selected to archive thread 2 sequ
ence 10688
Log Transport Services Warning 1 28498 0 NO 05-31 16:20:58 LGWR: Standby redo logfile selected for thread 2 sequence 10
688 for destination LOG_ARCHIVE_DEST_2
Log Transport Services Control 0 28499 0 YES 05-31 16:20:58 LGWR: Beginning to archive log 3 thread 2 sequence 10688
Log Transport Services Control 0 28500 0 YES 05-31 16:20:58 ARC3: Beginning to archive T-2.S-10687 (31431827-31432067)
Log Transport Services Control 0 28501 0 YES 05-31 16:20:58 ARC3: Completed archiving T-2.S-10687 (31431827-31432067)
Log Transport Services Control 0 28502 0 YES 05-31 16:20:58 ASYNC: Completed archiving log 4 thread 2 sequence 10687
Log Transport Services Control 0 28503 0 YES 05-31 16:20:58 ASYNC: Beginning to archive log 3 thread 2 sequence 10688
Log Transport Services Warning 1 28504 0 NO 05-31 16:20:58 ARC1: Standby redo logfile selected for thread 2 sequence 10
687 for destination LOG_ARCHIVE_DEST_2
Log Transport Services Control 0 28505 0 YES 05-31 16:21:04 LGWR: Completed archiving log 3 thread 2 sequence 10688
Log Transport Services Warning 2 28506 0 NO 05-31 16:21:04 LGWR: Standby redo logfile selected to archive thread 2 sequ
ence 10689
Log Transport Services Warning 1 28507 0 NO 05-31 16:21:04 LGWR: Standby redo logfile selected for thread 2 sequence 10
689 for destination LOG_ARCHIVE_DEST_2
在ADG上, 查看有關資訊
sys@HISDB:HISADG> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE
---------- ---------------------------------------- ---------- ---------- ---------- ----------
USED ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE#
---------- --- ---------- ------------- -------------- ------------ -------------- ------------
LAST_TIME
--------------
5 1882445006 1 4057 209715200 512
195584 YES ACTIVE 31429520 05-31 16:08:57 31430710
05-31 16:14:08
6 UNASSIGNED 1 0 209715200 512
0 NO UNASSIGNED
7 UNASSIGNED 2 0 104857600 512
0 NO UNASSIGNED
8 UNASSIGNED 2 0 104857600 512
0 YES UNASSIGNED
重建 standby logfile 容量改為 200m
sys@HISDB:HISADG> alter database recover managed standby database cancel;
Database altered.
sys@HISDB:HISADG> alter database drop logfile group 7;
Database altered.
sys@HISDB:HISADG> alter database drop logfile group 8;
Database altered.
sys@HISDB:HISADG> alter database add standby logfile group 7 size 200m;
Database altered.
sys@HISDB:HISADG> alter database add standby logfile group 8 size 200m;
Database altered.
sys@HISDB:HISADG> alter database recover managed standby database disconnect nodelay ;
Database altered.
sys@HISDB:HISADG> @dbrole
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
-------------------- ------------------------------ ---------------- --------------------
HISDB HISDB_6F PHYSICAL STANDBY READ ONLY WITH APPLY
再從 RAC 上查看, 已回復正常
sys@HISDB:HISRAC1> @arcdest
Session altered.
INST_ID DEST STATUS ERROR
---------- ---- --------- -----------------------------------------------------------------
1 1 VALID
1 2 VALID
1 3 VALID
2 1 VALID
2 2 VALID
2 3 VALID
6 rows selected.
留言
張貼留言