如何修改oracle11g的sys口令,[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口令，即使跟原来一样，也要注意拷贝口令文件到备库，这次导致数据库日志应用停用好

--几天。同步时明显感觉备库机器响应有点慢。