[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口令,即使跟原来一样,也要注意拷贝口令文件到备库,这次导致数据库日志应用停用好
--几天。同步时明显感觉备库机器响应有点慢。