|
DG备库无法接受主库归档日志之密码文件 实验目的：还原某个客户案例，欧博注册客户审计需要，对主库sys用户进行锁定，一小时后对sys用户进行解锁后，发现备库无法接受主库的归档日志 本篇文章，测试sys用户与DG备库接受归档有什么关系？ 1. 实验环境:类别 主库 备库 软件版本 11.2.0.4.0 11.2.0.4.0 是否RAC 否 否 Db_name ENMO Db_unique_name ENMO dage Service_name ENMO dage Listener.ora 192.168.20.128:1521/ENMO 192.168.20.66:1521/dage Tnsnames.ora ENMO DAGE log_archive_config ENMO,dage Log_Archive_dest_ x=service SERVICE=dage lgwr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dage Log_desc_x ENABLE Log_Archive_dest_ x=local LOCATION=/picclife/app/oracle/oradata/arch VALID_FOR=(ALL_LO GFILES,ALL_ROLES) DB_UNIQUE_NAME =dage Log_desc_x enable 1.1实验环境检测 --主库操作 SYS > alter system switch logfile; SYS@dage >select max(sequence#),thread# from v$archived_log group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 1009 1 --主库查询 SELECT THREAD#,NAME,SEQUENCE#,APPLIED,REGISTRAR,CREATOR FROM V$ARCHIVED_LOG WHERE SEQUENCE#=1009 THREAD# NAME SEQUENCE# APPLIED REGISTR CREATOR ---------- ------------------------------------------------- --------- --------- ------- ------- 1 /u01/app/oracle/oradata/arch/1_1009_960955299.log 1009 NO ARCH ARCH 1 dage 1009 NO LGWR LGWR --备库查询 SYS@dage >SELECT THREAD#,NAME,SEQUENCE#,NAME,APPLIED,REGISTRAR,CREATOR FROM V$ARCHIVED_LOG WHERE SEQUENCE#=1009; THREAD# NAME SEQUENCE# APPLIED REGISTR CREATOR ---------- ------------------------------------------------- --------- --------- ------- ------- 1/picclife/app/oracle/oradata/arch1_1009_960955299.log 1009 NO RFS LGWR 解释说明：备库，当registrar=RFS且 applied=NO 代表远程归档已接受，欧博代理但日志未应用。 Memory 代表内存已应用但数据文件未更新，Yes代表接受且应用完毕 2. 测试 2.1测试对主库修改sys密码，对备库的影响--主库操作 SYS > select sysdate from dual; SYSDATE ------------------- 2018-05-03 08:57:55 SYS > alter user sys identified by sys; SYS > alter system switch logfile; SYS > select max(sequence#),thread# from v$archived_log group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 1010 1 --备库查询 SYS@dage >SELECT THREAD#,NAME,SEQUENCE#,NAME,APPLIED,REGISTRAR,CREATOR FROM V$ARCHIVED_LOG WHERE SEQUENCE#=1010; THREAD# NAME SEQUENCE# APPLIED REGISTR CREATOR ---------- ------------------------------------------------- --------- --------- ------- ------- 1 /picclife/app/oracle/oradata/arch1_1010_960955299.log 1010 YES RFS LGWR --主库修改sys用户密码，主备状态都不变的情况下，无异常，备库可正常接受主库的归档文件 --查询主备之间的操作系统的密码文件 [oracle@enmo dbs]$ strings orapwENMO ]\[Z ORACLE Remote Password file INTERNAL 6A75B1BBE50E66AB [oracle@dage ~]$ sqlplus sys/sys@enmo as sysdba [oracle@dage dbs]$ strings orapwdage ]\[Z ORACLE Remote Password file INTERNAL AB27B53EDC5FEF41 8A8F025737A9097A [oracle@dage ~]$ sqlplus sys/oracle@dage as sysdba --发现主库修改sys密码后，欧博官网对于操作系统口令文件也会改变，但是备库即使应用了主库修改sys日志的归档日志，也并未主动修改sys密码口令，未改变 #重启主库，主库切换日志，查询备库是否存在无法接受日志的情况 --备库查询 SYS@dage >SELECT THREAD#,NAME,SEQUENCE#,NAME,APPLIED,REGISTRAR,CREATOR FROM V$ARCHIVED_LOG WHERE SEQUENCE#=1016; no rows selected --主库查询 SYS > select dest_id,error from v$archive_dest; DEST_ID ERROR ---------- ----------------------------------------------------------------- 1 2 ORA-16191: Primary log shipping client not logged on standby --由于本次实验操作，欧博娱乐知道由于密码文件修改主备不一致造成的情况 --还原主库操作，恢复主备之间的连通性 --主库操作 SYS > alter user sys identified by oracle; SYS > alter system switch logfile; select dest_id,error from v$archive_dest; DEST_ID ERROR ---------- ----------------------------------------------------------------- 1 2 ORA-16191: Primary log shipping client not logged on standby alter system set log_archive_dest_state_2=defer; alter system set log_archive_dest_state_2=enable; System altered. SYS > select dest_id,error from v$archive_dest; DEST_ID ERROR ---------- ----------------------------------------------------------------- 1 2 ORA-16191: Primary log shipping client not logged on standby alter system switch logfile; [oracle@enmo dbs]$ strings orapwENMO ]\[Z ORACLE Remote Password file INTERNAL AB27B53EDC5FEF41 >#.Y 8A8F025737A9097A [oracle@dage dbs]$ strings orapwdage ]\[Z ORACLE Remote Password file INTERNAL AB27B53EDC5FEF41 8A8F025737A9097A --手工将sys用户密码口令文件传输至备库 [oracle@enmo dbs]$ scp orapwENMO 192.168.20.66:/picclife/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdage --重置后，v$archive_dest dest_id=2无异常 --主库操作 alter system set log_archive_dest_state_2=defer; alter system set log_archive_dest_state_2=enable; SYS > select dest_id,error from v$archive_dest; DEST_ID ERROR ---------- ----------------------------------------------------------------- 1 2 --再次修改SYS用户密码，测试 SYS > alter user sys identified by sys; --备库操作 #重启备库监听，主库切换日志，查询备库是否存在无法接受日志的情况 [oracle@dage ~]$ lsnrctl stop [oracle@dage ~]$ lsnrctl start --测试结果，欧博allbet无影响 --备库操作 #对于备库而言，外部的连接只有4个 [oracle@dage dbs]$ ps -ef|grep LOCAL=NO|grep -v grep oracle 8682 1 0 15:03 ? 00:00:00 oracledage (LOCAL=NO) oracle 8684 1 0 15:03 ? 00:00:00 oracledage (LOCAL=NO) oracle 8696 1 0 15:03 ? 00:00:00 oracledage (LOCAL=NO) oracle 8702 1 0 15:03 ? 00:00:00 oracledage (LOCAL=NO) #kill 备库所有的外部连接 [oracle@dage dbs]$ ps -ef|grep LOCAL=NO|grep -v grep|cut -c 9-15|xargs kill -9 [oracle@dage dbs]$ ps -ef|grep LOCAL=NO|grep -v grep --主库切换归档--日志无法传输过去，报错不同 alter system switch logfile SYS > select dest_id,error from v$archive_dest; DEST_ID ERROR ---------- ----------------------------------------------------------------- 2 ORA-03135: connection lost contact --重置归档线程后，发现报错与之前的报错相同 --主库操作 alter system set log_archive_dest_state_2=defer; alter system set log_archive_dest_state_2=enable; SYS > select dest_id,error from v$archive_dest; DEST_ID ERROR ---------- ----------------------------------------------------------------- 2 ORA-16191: Primary log shipping client not logged on standby --还原上述操作 SYS > alter user sys identified by oracle; [oracle@enmo dbs]$ scp orapwENMO 192.168.20.66:/picclife/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdage 小结测试2.1 结论一：对于修改SYS用户密码而言，在主备之间数据库实例状态未改变的情况下，备库能正常接受主库的日志，但是当主备任何实例重启或者主库的归档远程进程重置，新建立的连接都会导致备库无法接受主库的归档 结论二：在明确知道sys用户密码修改后，通过alter user sys identified by 重置原密码，但是操作系统层面口令文件OrapwSID.ora文件不一致，也是白瞎，只能通过scp主库操作系统层面sys密码达到想要的效果 添加小测试： 测试备库修改操作系统口令文件与主库sys密码口令文件不一致情况会如何 --备库修改操作系统口令文件，kill主库的远程连接 --备库操作 [oracle@dage dbs]$ orapwd file=orapwdage password=abc entries=10 ignorecase=y force=y [oracle@dage dbs]$ sqlplus sys/abc@dage as sysdba [oracle@dage dbs]$ ps -ef|grep LOCAL=NO|grep -v grep|cut -c 9-15|xargs kill -9 --主库重置远程归档 SYS > select dest_id,error from v$archive_dest; DEST_ID ERROR ---------- ----------------------------------------------------------------- 2 ORA-03135: connection lost contact alter system set log_archive_dest_state_2=defer; alter system set log_archive_dest_state_2=enable; SYS > select dest_id,error from v$archive_dest; 2 ORA-16191: Primary log shipping client not logged on standby 结论三、对于主备而言，只要主备操作系统口令文件不一致，且主库远程归档参数需要重新建立连接，都会得到dest_id 无法连接备库，解决方法，cp操作系统口令文件 2.2测试对主库sys用户进行锁定，切换归档后测试--备库开启MRP进程 SYS@dage >recover managed standby database disconnect; SYS@dage >select process,client_process,sequence#,status,BLOCK#,BLOCKS from v$managed_standby; PROCESS CLIENT_P SEQUENCE# STATUS BLOCK# BLOCKS --------- -------- ---------- ------------ ---------- ---------- MRP0 N/A 1007 APPLYING_LOG 14740 82074 --主库对sys用户进行锁定 SYS > alter user sys account lock; SYS > select username,ACCOUNT_STATUS from dba_users where username='SYS'; USERNAME ACCOUNT_STATUS ------------------------------ -------------------------------- SYS LOCKED SYS > alter system switch logfile; --备库重启库后--查询 SYS@dage >startup force; SYS@dage >select username,ACCOUNT_STATUS from dba_users where username='SYS'; USERNAME ACCOUNT_STATUS ------------------------------ -------------------------------- SYS LOCKED --主库操作，重置线程查询状态 alter system set log_archive_dest_state_2=defer; alter system set log_archive_dest_state_2=enable; SYS > select dest_id,error from v$archive_dest; 2 ORA-16191: Primary log shipping client not logged on standby SYS > select severity,error_code,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp" , message from v$dataguard_status where dest_id=2; SEVERITY ERROR_CODE timestamp MESSAGE --------------- ---------- -------------------- ---------------------------------------------------------------------- Error 16191 03-MAY-2018 10:10:50 PING[ARC2]: Heartbeat failed to connect to standby 'dage'. Error is 16191. --主库查询归档参数 SYS > show parameter archive log_archive_dest_2 SERVICE=dage lgwr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dage SYS > host tnsping dage OK (40 msec) --主库查询alert日志 [oracle@enmo dbs]$ tail -200f /u01/app/oracle/diag/rdbms/enmo/ENMO/trace/alert_ENMO.log Thu May 03 10:10:50 2018 Error 1017 received logging on to the standby ------------------------------------------------------------ Check that the primary and standby are using a password file and remote_login_passwordfile is set to SHARED or EXCLUSIVE, and that the SYS password is same in the password files. -密码文件sys密码相同！！！ returning error ORA-16191 --只能作为参考 --正常途径以及无法自动主库归档传输至备库了 --一般情况下，scp操作系统口令文件就能解决问题，但是本次主备密码文件一致 [oracle@enmo dbs]$ strings orapwENMO ]\[Z ORACLE Remote Password file INTERNAL AB27B53EDC5FEF41 8A8F025737A9097A #D}r [oracle@dage dbs]$ strings orapwdage ]\[Z ORACLE Remote Password file INTERNAL AB27B53EDC5FEF41 8A8F025737A9097A #D}r --通过处理gap归档的方法实现上述功能，原理理论分析：主库修改了sys用户密码 --对主库sys用户进行解锁 SYS > alter user sys account unlock; SYS > alter system switch logfile; SYS@dage >r 1* select username,ACCOUNT_STATUS from dba_users where username='SYS' USERNAME ACCOUNT_STATUS ------------------------------ -------------------------------- SYS LOCKED --备库sys用户状态不变，因为主库的归档文件无法传输至备库 --原理分析：主库锁定的日志传输至备库，备库sys用户被锁定，在备库重启后，主库的远程归档参数无法连接备库，无法传输归档日志，主库对sys用户的解锁操作，无法在备库应用 -- --查询主库最新的归档日志 SYS > select max(sequence#),thread# from v$archived_log group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 1032 1 select thread#,sequence#,name,applied from v$archived_log THREAD# SEQUENCE# NAME APPLIED ----------------------------------------------------------------------------------------------------------------------------------------- 1 1029 dage YES 1 1030 /u01/app/oracle/oradata/arch/1_1030_960955299.log NO 1 1030 dage YES 1 1031 /u01/app/oracle/oradata/arch/1_1031_960955299.log NO 1 1032 /u01/app/oracle/oradata/arch/1_1032_960955299.log NO --查询备库最后接受应用的归档日志 select thread#,sequence#,name,applied from v$archived_log THREAD# SEQUENCE# NAME APPLIED ----------------------------------------------------------------------------------------------------------------------------------------- 1 1030 /picclife/app/oracle/oradata/arch1_1030_960955299.log YES --为稳妥起见：对主库1031/1032两个日志文件手工拷贝 [oracle@enmo ~]$ scp /u01/app/oracle/oradata/arch/1_1031_960955299.log /u01/app/oracle/oradata/arch/1_1032_960955299.log 192.168.20.66:/picclife/app/oracle/oradata/. --备库注册归档日志 RMAN> catalog start with '/picclife/app/oracle/oradata/'; --备库重启后，需手工启动Mrp进程 SYS@dage >recover managed standby database disconnect from session; --备库查询sys用户状态 SYS@dage >select username,ACCOUNT_STATUS from dba_users where username='SYS'; USERNAME ACCOUNT_STATUS ------------------------------ -------------------------------- SYS OPEN 结论当出现备库无法接受主库归档日志现象时： 主库查询视图：select dest_id,error from v$archive_dest; select severity,error_code,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp" , message from v$dataguard_status where dest_id=2; 主备库alert日志文件 主库归档远程参数log_archive_dest_xxx log_archive_dest_state_2状态 如果以上均OK，增加一个可能性，sys用户状态，及操作系统密码文件是否一致 --本篇文档主要考虑sys密码文件造成备库无法接受日志的情况，不详细介绍log_archive_dest_xx参数 (责任编辑：) |