本文共 7241 字，大约阅读时间需要 24 分钟。
[20170204]dg环境修改sys口令.txt
--节前花了一个上午安装oracle 11.2.0.4,搭建一个测试环境dg,以前就遇到修改sys口令(修改与原来一样),dg无法接受日志的情况,今天
--探究看看.1.环境:
SYS@book> @ &r/ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production--//执行如下确定日志一直在应用.
SYS@bookdg> select process,pid,status,group#,thread#,sequence#,block#,blocks,delay_mins from v$managed_standby order by thread#,group# ; PROCESS PID STATUS GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS --------- ------- ------------ ---------------------------------------- ---------- ---------- ---------- ---------- ---------- RFS 11741 IDLE N/A 0 0 0 0 0 RFS 11739 IDLE 2 1 246 73219 1 0 ARCH 11723 CLOSING 4 1 245 73728 1843 0 MRP0 11725 APPLYING_LOG N/A 1 246 73219 102400 02.在主库上备份:
$ cp orapwbook orapwbook_20170204SYS@book> column SPARE4 format a62
SYS@book> select name,password,spare4 from sys.user$ where name in ('SYS','SCOTT'); NAME PASSWORD SPARE4 -------------------- ------------------------------ -------------------------------------------------------------- SCOTT 0EDE56329E1D82EA S:6F087B090A929E109C0F4DA49410FB9C8138125C5DE3D05220554F371C73 SYS 8A8F025737A9097A S:D1C72E475ADEC14CC69D55A325D4D7C0B4373D0897DDF692B1467F53438DSYS@book> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS -------------------- ----- ----- ----- SYS TRUE TRUE FALSE SCOTT TRUE TRUE FALSE$ strings orapwbook
]\[Z ORACLE Remote Password file INTERNAL AB27B53EDC5FEF41 8A8F025737A9097A SCOTT 0EDE56329E1D82EA$ strings orapwbook | md5sum
a43f98316db39ac6969f02a02af093ed -$ md5sum orapwbook orapwbook_20170204
06542d516c86b32a4d99dd59b6fa6b90 orapwbook 06542d516c86b32a4d99dd59b6fa6b90 orapwbook_201702043.在备机上备份:
$ cp orapwbookdg orapwbookdg_20170204 $ md5sum orapwbookdg orapwbookdg_20170204 06542d516c86b32a4d99dd59b6fa6b90 orapwbookdg 06542d516c86b32a4d99dd59b6fa6b90 orapwbookdg_20170204--//从主库拷贝过来的,md5sum应该一致.
3.在主库修改口令:
--//注实际上修改还是原来的口令,执行如下: SYS@book> ALTER USER SYS IDENTIFIED BY oracle; User altered.$ strings orapwbook
]\[Z ORACLE Remote Password file INTERNAL AB27B53EDC5FEF41 8A8F025737A9097A .DZ@ SCOTT 0EDE56329E1D82EA$ strings orapwbook | md5sum
0cc07ef4bf34948aaaabccba334bfe86 ---//口令文件已经修改,md5sum的值发生了变化,但是如果仔细看口令没变.
SYS@book> select name,password,spare4 from sys.user$ where name in ('SYS','SCOTT');
NAME PASSWORD SPARE4 -------------------- ------------------------------ -------------------------------------------------------------- SCOTT 0EDE56329E1D82EA S:6F087B090A929E109C0F4DA49410FB9C8138125C5DE3D05220554F371C73 SYS 8A8F025737A9097A S:62EF842E445A40ADBDCEA0DC778ECFB294CD0815421172F74F6D6FC5DA2F--//password还是原来的值8A8F025737A9097A,spare4因为slot变化,加密串也发生了变化.
--检查日志是否应用:
SYS@bookdg> @ &r/dg/dg PROCESS PID STATUS GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS --------- ------- ------------ ---------------------------------------- ---------- ---------- ---------- ---------- ---------- RFS 11741 IDLE N/A 0 0 0 0 0 RFS 11739 IDLE 3 1 247 111 1 0 ARCH 11723 CLOSING 5 1 246 75776 1760 0 MRP0 11725 APPLYING_LOG N/A 1 247 111 102400 0--//可以发现进程MRP0的block#一致在变化.说明日志一直在应用.重启dg的日志应用看看.
SYS@bookdg> alter database recover managed standby database cancel ;
Database altered.SYS@bookdg> shutdown immediate;
ORA-01109: database not open Database dismounted. ORACLE instance shut down.SYS@bookdg> startup nomount
ORACLE instance started. Total System Global Area 634732544 bytes Fixed Size 2255792 bytes Variable Size 197133392 bytes Database Buffers 427819008 bytes Redo Buffers 7524352 bytesSYS@bookdg> alter database mount standby database;
Database altered.SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.--在主库执行:
SYS@book> alter system set log_archive_dest_state_2=defer scope=memory; System altered.SYS@book> alter system set log_archive_dest_state_2=enable scope=memory;
System altered.--//检查日志应用情况:
SYS@bookdg> @ &r/dg/dg PROCESS PID STATUS GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS --------- ------- ------------ ---------------------------------------- ---------- ---------- ---------- ---------- ---------- ARCH 13812 CONNECTED N/A 0 0 0 0 0 MRP0 13814 APPLYING_LOG N/A 1 247 295 102400 0--//可以发现无法传输日志,应用日志停止在不动.....查看alert日志发现:
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. returning error ORA-16191 ------------------------------------------------------------ FAL[client, ARC0]: Error 16191 connecting to book for fetching gap sequence$ oerr ORA 16191
16191, 0000, "Primary log shipping client not logged on standby" // *Cause: An attempt to ship redo to standby without logging on // to standby or with invalid user credentials. // *Action: Check that primary and standby are using password files and that // both primary and standby have the same SYS password. // Restart primary and/or standby after ensuring that // password file is accessible and REMOTE_LOGIN_PASSWORDFILE // initialization parameter is set to SHARED or EXCLUSIVE.$ oerr ora 1017
01017, 00000, "invalid username/password; logon denied" // *Cause: // *Action:--//说明:我虽然修改了sys用户口令,但是实际上我并没有修改.而且口令文件中存在一些变化,但是加密的字符串还是没有变化.
--//正常不应该出现口令不对的情况,为什么呢?4.分析:
-- 首先我远程使用sys用户连接都是ok的.说明口令没有问题. sqlplus sys/oracle@192.168.100.40:1521/bookdg as sysdba sqlplus sys/oracle@192.168.100.78:1521/book as sysdba--主库,备库使用上面的命令都是ok的.
--为什么在执行alter database mount standby database;alert提示: 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. returning error ORA-16191 ------------------------------------------------------------ FAL[client, ARC0]: Error 16191 connecting to book for fetching gap sequence--在主库上拷贝原来的口令文件看看.
$ mv orapwbook orapwbook_good $ cp orapwbook_20170204 orapwbook--备库重新启动数据库.居然这样就ok了.继续测试修改scott口令看看.注scott也记录在口令文件中.
SYS@book> ALTER USER scott IDENTIFIED BY book;
User altered.$ md5sum orapwbook*
387f54823d7325ec204b2ed6dfb255c0 orapwbook 06542d516c86b32a4d99dd59b6fa6b90 orapwbook_20170204 ae3e4530ccfdd1784c4efaaf6225140f orapwbook_good--//口令文件再次修改.重启备库,测试发现日志可以正常传输.也就是说明口令文件改变仅仅修改sys用户才会出现无法传输的情况.
5.继续分析:
--是否是口令文件大小写问题呢?两边执行: $ orapwd file=orapwbook password=oracle force=y ignorecase=y--这样口令文件忽略大小写.测试日志能传输并应用.
SYS@book> ALTER USER SYS IDENTIFIED BY oracle; User altered.--//sys口令"修改".重复测试,可以发现日志能传输并应用.也就是大小写问题导致日志传输问题.
6.对比二者存在什么不同呢:
$ orapwd file=orapway password=oracle force=y ignorecase=y;orapwd file=orapwan password=oracle force=y ignorecase=n
--放弃,难度有点大.
总结:
1.在存在dg的环境下修改sys用户口令注意,口令文件也会变化.因为修改,哪怕你修改与前面的口令一样,也会影响传输.而且是"延后"出现. 2.从测试看视乎是口令的大小写问题. 3.rac环境还要注意的问题是修改口令仅仅修改一个实例下的口令文件.另外一个实例的口令文件不会自动修改.并且应该马上拷贝到dg机器. 避免影响以后日志传输与应用. 4.oracle视乎已经意识到这个问题,12c rac已经放入asm,这样多个实例访问相同的口令文件. --修改口令会自动同步到dg.链接: 5.测试还是乱....转载地址：http://aenqa.baihongyu.com/