[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 0
2.在主库上备份:
$ cp orapwbook orapwbook_20170204
SYS@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:D1C72E475ADEC14CC69D55A325D4D7C0B4373D0897DDF692B1467F53438D
SYS@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_20170204
3.在备机上备份:
$ 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 bytes
SYS@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,这样多个实例访问相同的口令文件.
http://www.oraclebuffer.com/general-discussions/oracle-database-12c-12-1-password-file-mystery/
--修改口令会自动同步到dg.链接:
https://uhesse.com/2017/01/10/auto-sync-for-password-files-in-oracle-12c-data-guard/
5.测试还是乱....
