[20151118]11g dg修改sys口令.txt

[20151118]11g dg修改sys口令.txt

--今天在例行检查中再次发现问题，我在测试环境做了1个模拟，主要问题是修改sys口令后，即使你修改的口令一样。
--我一般在数据库中修改，不使用 orapwd命令。

--修改口令在dg观察，日志传输与应用一切正常。但是如果备用机器重启后问题就出现了。

1. 测试环境：
SYS@test> @ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

2.问题分析：

DGMGRL> show database  test
Database - test

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    test
      Error: ORA-16737: the redo transport service for standby database "testdg" has an error

Database Status:
ERROR

DGMGRL> show database  testdg
Database - testdg

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   (unknown)
  Apply Lag:       (unknown)
  Real Time Query: OFF
  Instance(s):
    testdg

Database Status:
DGM-17016: failed to retrieve status for database "testdg"
ORA-01031: insufficient privileges
ORA-16625: cannot reach database "testdg"

$ oerr ora 16737
16737, 00000, "the redo transport service for standby database \"%s\" has an error"
// *Cause:  A communication problem with the standby database caused the redo
//          transport to fail.
// *Action: Query the LogXptStatus property to see the error message.
//          Check the Data Guard broker log and Oracle alert log for
//          more details.

DGMGRL> show database test LogXptStatus
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME               STATUS
                test               testdg ORA-03135: connection lost contact

DGMGRL> show database testdg LogXptStatus
Error: ORA-01031: insufficient privileges
Error: ORA-16625: cannot reach database "testdg"

$ oerr ora 03135
03135, 00000, "connection lost contact"
// *Cause:  1) Server unexpectedly terminated or was forced to terminate.
//          2) Server timed out the connection.
// *Action: 1) Check if the server session was terminated.
//          2) Check if the timeout parameters are set properly in sqlnet.ora.

$ oerr ora 1031
01031, 00000, "insufficient privileges"
// *Cause: An attempt was made to change the current username or password
//         without the appropriate privilege. This error also occurs if
//         attempting to install a database without the necessary operating
//         system privileges.
//         When Trusted Oracle is configure in DBMS MAC, this error may occur
//         if the user was granted the necessary privilege at a higher label
//         than the current login.
// *Action: Ask the database administrator to perform the operation or grant
//          the required privileges.
//          For Trusted Oracle users getting this error although granted the
//          the appropriate privilege at a higher label, ask the database
//          administrator to regrant the privilege at the appropriate label.

--出现这个错误ora－1031，多数是口令文件出了问题。为什么我修改的口令一样的，日志会无法传输呢？感觉很奇怪，实际上前一阵子
--我已经遇到过，不过当时主库是rac的情况。

--检查主库的alert：
Wed Nov 18 15:29:39 2015
Error 1031 received logging on to the standby
PING[ARC2]: Heartbeat failed to connect to standby 'testdg'. Error is 1031.

--dg broker的日志文件drctest.log：
11/18/2015 15:30:33
Redo transport problem detected: redo transport for database testdg has the following error:
  ORA-03135: connection lost contact
Connection to database testdg returns ORA-01031.
Please check database testdg is using a remote password file,
its remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and the SYS password is the same as this database.
Failed to connect to remote database testdg. Error is ORA-01031
Failed to send message to site testdg. Error code is ORA-01031.
Data Guard Broker Status Summary:
  Type                        Name                             Severity  Status
  Configuration               study                             Warning  ORA-16607
  Primary Database            test                                Error  ORA-16778
  Physical Standby Database   testdg                              Error  ORA-01031

--检查备库的dg broker的日志文件drctestdg.log：

11/18/2015 15:14:15
DMON Registering service testdg_DGB with listener(s)
Broker Configuration:       "study"
      Protection Mode:            Maximum Performance
      Fast-Start Failover (FSFO): Disabled, flags=0x0, version=0
      Primary Database:           test (0x01010000)
      Standby Database:           testdg, Enabled Physical Standby (0x02010000)
11/18/2015 15:14:19
Connection to database test returns ORA-01017.
Please check database test is using a remote password file,
its remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and the SYS password is the same as this database.
Failed to connect to remote database test. Error is ORA-01017
Failed to send message to site test. Error code is ORA-01017.
database testdg unable to contact primary database for version check; status ORA-01017
      completing bootstrap of this database
Creating process RSM0

$ oerr ora 1017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:

--使用sqlplus sys用户测试连通性一些正常！难道和11g的口令大小写机制有关。

3.问题解决：

--实际上口令是一样的，不知道为什么会出现这个问题。最简单的解决方法就是拷贝口令文件，在改名问题解决。

$ scp orapwtest oracle11g@192.168.101.115:/u01/app/oracle11g/product/11.2.0/db_2/dbs
oracle11g@192.168.101.115's password:
orapwtest                                   100% 1536     1.5KB/s                                  
$ mv orapwtest orapwtestdg
/bin/mv: overwrite `orapwtestdg'? y

DGMGRL> show database test
Database - test

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    test

Database Status:
SUCCESS

DGMGRL> show database testdg

Database - testdg

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    testdg

Database Status:
SUCCESS

4，总结：
--在没有确定这个问题前，以后注意在更新sys口令，即使跟原来一样，也要注意拷贝口令文件到备库，这次导致数据库日志应用停用好
--几天。同步时明显感觉备库机器响应有点慢。

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