[20170204]dg环境修改sys口令.txt

[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.测试还是乱....

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2133049/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2133049/