change primary DB time and physical standby DB timezone step(二)-问题追踪-CSDN博客

一、 Chang sys password neet attention

在生产环境中SYS用户的密码最好不要随意的修改，因为他会涉及带库在rman备份中，隐含sys密码的变更，另外如果生产环境中进行了DG的配置，那么修改primary db sys password 会导致与从库之间传递redo的错误，所以如果要修改主库的sys密码，必须在主库和从库2个节点中重新生成ORAPWD密码文件。

Its advisable to follow the steps below when you are planning to change the SYS password in PRIMARY

Step 1: Defer the remote archival destination (log_archive_dest_state_n) parameter in primary
Step 2: change the password in primary
Step 3: Recreate or copy the password file in standby
Step 4: Enable the remote archival destination (log_archive_dest_state_n) parameter in primary
For 10g database

a) Every database in a Data Guard configuration must use a password file, and the password for the SYS user must be identical on every system for redo data transmission to succeed.

b) REMOTE_LOGIN_PASSWORDFILE parameter setting should be either EXCLUSIVE or SHARED.

1. primary alert日志info:
Fri Apr 23 23:53:24 2010
Thread 1 advanced to log sequence 9420 (LGWR switch)
Current log# 1 seq# 9420 mem# 0: /dev/vgleidata/rlvleiredo1_1
Current log# 1 seq# 9420 mem# 1: /dev/vgleidata/rlvleiredo1_2
Fri Apr 23 23:53:24 2010
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
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
------------------------------------------------------------
LGWR: Error 16191 creating archivelog file 'STCPF'
Fri Apr 23 23:53:24 2010
Errors in file /oracle/admin/bosdb/bdump/leiz1_lns1_6050.trc:
ORA-16191: Primary log shipping client not logged on standby
LNS: Failed to archive log 1 thread 1 sequence 9420 (16191)
Fri Apr 23 23:53:29 2010
Error 1017 received logging on to the standby

2.Reproduction orapwd file in parimary db and standby db.

leizdb:

Primary db:

orapwd file=/oracle/product/10.2/db/dbs/orapwleizdb1 password=new password force=y

standby db(attention orapwd file path)

orapwd file=/oracle/product/10.2.0/db/dbs/orapwleizdb1 password=new password force=y

参考文献：Changing SYS password of PRIMARY database when STANDBY in place to avoid ORA-16191 [ID 806703.1]

二、修改完standby db os timezone参数，为什么查询没有生效？

1. 修改完standby db /etc/TIMEZONE参数，发现主库和从库的dbtomezone参数仍然不一致。

select dbtimezone from dual;

主库：+08：00

从库：+00：00

咨询metalink工程师，原因如下：

standby shows dbtimezone as +00:00 when it is in mount state.
You can get a correct value if you cancel the recovery & open in read only mode.

=== ODM Test Case ===
********* Primary *************

SQL> select database_role,db_unique_name,open_mode from v$database;

DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE
---------------- ------------------------------ ----------
PRIMARY chicago READ WRITE

SQL> select dbtimezone from dual;

DBTIME
------
+05:30

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

********** Physical Standby ********************

SQL> select database_role,db_unique_name,open_mode from v$database;

DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE
---------------- ------------------------------ ----------
PHYSICAL STANDBY boston MOUNTED

SQL> select dbtimezone from dual;

DBTIME
------
+00:00

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\>dgmgrl /
DGMGRL for 32-bit Windows: Version 10.2.0.4.0 - Production

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration;

Configuration
Name: DRSolution
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
chicago - Primary database
boston - Physical standby database

Current status for "DRSolution":
SUCCESS

DGMGRL> edit database 'boston' set state='READ-ONLY';
Succeeded.
DGMGRL> exit

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Apr 26 15:06:35 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select database_role,db_unique_name,open_mode from v$database;

DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE
---------------- ------------------------------ ----------
PHYSICAL STANDBY boston READ ONLY

SQL> select dbtimezone from dual;

DBTIME
------
+05:30

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

转载于:http://blog.itpub.net/9252210/viewspace-660897/