[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

    testdg

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,

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 [email protected]:/u01/app/oracle11g/product/11.2.0/db_2/dbs

[email protected]'s password:

orapwtest                                   100% 1536     1.5KB/s                                  

$ mv orapwtest orapwtestdg

/bin/mv: overwrite `orapwtestdg'? y

DGMGRL> show database test

SUCCESS

DGMGRL> show database testdg

  Transport Lag:   0 seconds

  Apply Lag:       0 seconds

  Real Time Query: ON

4，總結：

--在沒有确定這個問題前，以後注意在更新sys密碼，即使跟原來一樣，也要注意拷貝密碼檔案到備庫，這次導緻資料庫日志應用停用好

--幾天。同步時明顯感覺備庫機器響應有點慢。