[20170317]dg出现ora-16009.txt
--//今天例行检查发现一台dg出现ora-16009错误.查询找到如下链接 <del>
--//按照链接介绍默认valid_for引起,这台机器容灾非常奇怪,我不大敢动这台机器.
--//没有设置fal,log_archive_config.连sid,以及db_unique_name都与主库一样.我在测试环境模拟看看.
1.环境:
--//备库:
SYS@bookdg> @ &r/ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@bookdg> create pfile='/tmp/bookdg.ora' from spfile;
File created.
--//修改参数:
*.log_archive_dest_2='SERVICE=book LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=book'
--//如下(仅仅删除VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)):
*.log_archive_dest_2='SERVICE=book LGWR ASYNC AFFIRM DB_UNIQUE_NAME=book'
SYS@bookdg> startup mount pfile='/tmp/bookdg.ora'
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
--//检查alert文件,出现如下错误:
ARC0 started with pid=19, OS id=15489
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC0: Becoming the heartbeat ARCH
ARC0: Thread not mounted
Fri Mar 17 08:47:32 2017
Successful mount of redo thread 1, with mount id 1379601758
Physical Standby Database mounted.
Lost write protection disabled
ARC0: Becoming the active heartbeat ARCH
Completed: ALTER DATABASE MOUNT
Fri Mar 17 08:47:34 2017
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/archivelog/book
Fri Mar 17 08:47:34 2017
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Assigned to RFS process 15494
RFS[1]: Selected log 5 for thread 1 sequence 705 dbid 1337401710 branch 896605872
Fri Mar 17 08:47:35 2017
RFS[2]: Assigned to RFS process 15496
RFS[2]: Selected log 4 for thread 1 sequence 704 dbid 1337401710 branch 896605872
Archived Log entry 12 added for thread 1 sequence 704 ID 0x4fb7d86e dest 1:
FAL[server, ARC0]: Error 16009 creating remote archivelog file 'book'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FAL[server, ARC0]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance bookdg - Archival Error. Archiver continuing.
--//如果继续检查alert文件,不发现时不时出现:
Fri Mar 17 08:53:31 2017
PING[ARC0]: Heartbeat failed to connect to standby 'book'. Error is 16009.
Fri Mar 17 08:54:31 2017
PING[ARC0]: Heartbeat failed to connect to standby 'book'. Error is 16009.
Fri Mar 17 08:55:31 2017
PING[ARC0]: Heartbeat failed to connect to standby 'book'. Error is 16009.
--//视乎是每分钟监测一次.
$ vim bookdg_arc0_15489.trc
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied
'
*** 2017-03-17 08:47:33.677
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied
'
*** 2017-03-17 08:47:35.541
Initial buffer sizes: read 1024K, overflow 832K, change 805K
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
Error 16009 attaching RFS server to standby instance at host 'book'
*** 2017-03-17 08:47:35.839
Error 16009 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'book'
*** 2017-03-17 08:47:35.839 2917 krsi.c
krsi_dst_fail: dest:2 err:16009 force:0 blast:1
kcrrwkx: unknown error:16009
....
*** 2017-03-17 08:55:31.539
Error 16009 attaching RFS server to standby instance at host 'book'
Error 16009 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'book'
*** 2017-03-17 08:55:31.540 4329 krsh.c
PING[ARC0]: Heartbeat failed to connect to standby 'book'. Error is 16009.
*** 2017-03-17 08:55:31.540 2917 krsi.c
krsi_dst_fail: dest:2 err:16009 force:0 blast:1
--//不得不承认,oracle提示非常模糊不清,前面提示是ORA-01017.后面才提示0ra-16009.到底是口令文件的问题还是配置问题.
SYS@bookdg> @ &r/dg/dg_dest
DEST_ID DEST_NAME STATUS TYPE DATABASE_MODE RECOVERY_MODE PROTECTION_MODE DESTINATION ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# ERROR
------- -------------------- --------- ---------- --------------- ----------------------- -------------------- ------------------------------- ---------------- ------------- --------------- ------------ --------------------------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL MOUNTED-STANDBY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE /u01/app/oracle/archivelog/book 1 704 0 0
2 LOG_ARCHIVE_DEST_2 ERROR UNKNOWN UNKNOWN IDLE MAXIMUM PERFORMANCE book 0 0 0 0 ORA-16009: invalid redo transport destination
3 LOG_ARCHIVE_DEST_3 INACTIVE LOCAL UNKNOWN IDLE MAXIMUM PERFORMANCE 0 0 0 0
4 LOG_ARCHIVE_DEST_4 INACTIVE LOCAL UNKNOWN IDLE MAXIMUM PERFORMANCE 0 0 0 0
5 LOG_ARCHIVE_DEST_5 INACTIVE LOCAL UNKNOWN IDLE MAXIMUM PERFORMANCE 0 0 0 0
2.我记忆理许多人做dg都没有设置这个参数.
--//按照链接的介绍如果不设置,缺省就是VALID_FOR=(ALL_LOGFILES, ALL_ROLES).
http://docs.oracle.com/cd/E11882_01/server.112/e41134/log_arch_dest_param.htm#SBYDB01116
Usage Notes
The VALID_FOR attribute is optional. However, Oracle recommends that the VALID_FOR attribute be specified for each
redo transport destination at each database in a Data Guard configuration so that redo transport continues after a
role transition to any standby database in the configuration.
To configure these factors for each LOG_ARCHIVE_DEST_n destination, you specify this attribute with a pair of
keywords: VALID_FOR=(redo_log_type,database_role):
The redo_log_type keyword identifies the destination as valid for archiving one of the following:
ONLINE_LOGFILE—This destination is valid only when archiving online redo log files.
STANDBY_LOGFILE—This destination is valid only when archiving standby redo log files.
ALL_LOGFILES— This destination is valid when archiving either online redo log files or standby redo log files.
The database_role keyword identifies the role in which this destination is valid for archiving:
PRIMARY_ROLE—This destination is valid only when the database is running in the primary role.
STANDBY_ROLE—This destination is valid only when the database is running in the standby role.
ALL_ROLES—This destination is valid when the database is running in either the primary or the standby role.
If you do not specify the VALID_FOR attribute for a destination, by default, archiving online redo log files and
standby redo log files is enabled at the destination, regardless of whether the database is running in the primary
or the standby role. This default behavior is equivalent to setting the (ALL_LOGFILES,ALL_ROLES) keyword pair on the
VALID_FOR attribute.
The VALID_FOR attribute enables you to use the same initialization parameter file for both the primary and standby roles.
Example
The following example shows the default VALID_FOR keyword pair:
LOG_ARCHIVE_DEST_1='LOCATION=/disk1/oracle/oradata VALID_FOR=(ALL_LOGFILES, ALL_ROLES)'
When this database is running in either the primary or standby role, destination 1 archives all log files to the
/disk1/oracle/oradata local directory location.
--//但是我再看生产系统alert,我发现后面不再报这个错误.我检查发现被修改如下:
SYS@fyhis> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------ ------- ---------------------------------------------------------------------------------
log_archive_dest_2 string service=xxxx1 lgwr sync reopen=15 max_failure=10 net_timeout=30 optional noaffirm
--//猜测最大的可能就是加入了optional.
3.继续:
--//做了许多尝试,最终我发现只要不设置DB_UNIQUE_NAME=book参数.就不会报ora-16009错误.
--//修改参数:
*.log_archive_dest_2='SERVICE=book LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=book'
--//如下
*.log_archive_dest_2='SERVICE=book LGWR ASYNC AFFIRM '
--但是arc进程会报:
$ vim bookdg_arc0_15752.trc
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied '
*** 2017-03-17 09:17:19.971
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied '
SYS@bookdg> @ &r/dg/dg_dest
DEST_ID DEST_NAME STATUS TYPE DATABASE_MODE RECOVERY_MODE PROTECTION_MODE DESTINATION ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# ERROR
------- -------------------- --------- ---------- --------------- ------------- -------------------- ------------------------------- ---------------- ------------- --------------- ------------ ------------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL MOUNTED-STANDBY IDLE MAXIMUM PERFORMANCE /u01/app/oracle/archivelog/book 1 708 0 0
2 LOG_ARCHIVE_DEST_2 BAD PARAM LOCAL MOUNTED-STANDBY IDLE MAXIMUM PERFORMANCE book 0 0 0 0
3 LOG_ARCHIVE_DEST_3 INACTIVE LOCAL UNKNOWN IDLE MAXIMUM PERFORMANCE 0 0 0 0
4 LOG_ARCHIVE_DEST_4 INACTIVE LOCAL UNKNOWN IDLE MAXIMUM PERFORMANCE 0 0 0 0
5 LOG_ARCHIVE_DEST_5 INACTIVE LOCAL UNKNOWN IDLE MAXIMUM PERFORMANCE 0 0 0 0
--//status='BAD PARAM'.
--//可以我还是奇怪,对方做的确实没有ORA-01017错误.于是我修改如下:
*.log_archive_dest_2='SERVICE=book lgwr sync reopen=15 max_failure=10 net_timeout=30 optional noaffirm'
--//依旧提示,arc进程:
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied'
--//我甚至还原我原来设置:
*.log_archive_dest_2='SERVICE=book LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=book'
--//还是出现ora-01017错误.
--//难度还是口令文件的问题:
--//主库执行:
SYS@book> ALTER USER SYS IDENTIFIED BY oracle;
User altered.
$ scp orapwbook oracle@192.168.100.40:/tmp
oracle@192.168.100.40's password:
orapwbook 100% 1536 1.5KB/s 00:00
--//备库执行:
$ mv /tmp/orapwbook orapwbookdg
--//问题依旧.好奇怪啊.
--//最终发现不设置log_archive_config参数,就不会报错. ora-16009之类的错误.
SYS@bookdg> @ &r/dg/dg_dest
DEST_ID DEST_NAME STATUS TYPE DATABASE_MODE RECOVERY_MODE PROTECTION_MODE DESTINATION ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# ERROR
------- -------------------- --------- ---------- --------------- ------------- -------------------- ------------------------------- ---------------- ------------- --------------- ------------ ------------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL MOUNTED-STANDBY IDLE MAXIMUM PERFORMANCE /u01/app/oracle/archivelog/book 0 0 0 0
2 LOG_ARCHIVE_DEST_2 DEFERRED UNKNOWN UNKNOWN IDLE MAXIMUM PERFORMANCE book 0 0 0 0
3 LOG_ARCHIVE_DEST_3 INACTIVE LOCAL UNKNOWN IDLE MAXIMUM PERFORMANCE 0 0 0 0
4 LOG_ARCHIVE_DEST_4 INACTIVE LOCAL UNKNOWN IDLE MAXIMUM PERFORMANCE 0 0 0 0
5 LOG_ARCHIVE_DEST_5 INACTIVE LOCAL UNKNOWN IDLE MAXIMUM PERFORMANCE 0 0 0 0
--//不过:.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied'一直存在.