如何做到SYS密码修改不影响DG同步

郑全

How to make log shipping to continue work without copying password file from primary to physical standby when changing sys password on primary? (Doc ID 1416595.1)        To BottomTo Bottom       

In this Document
Goal
Solution

APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 21-Oct-2016***
The solution applies to 11g and above Data Guard environment or log transport from source database to target database.


GOAL

This is 11.2.0.x or above Data Guard environment.
Whenever SYS password is changed in Primary DB, you need to copy password file from primary to Standby databases . Otherwise, the log shipping will fail with ora-16191.
Considering the number of Primary databases and Standby databases, it is tedious to copy password files from primary databases to the standby databases.

Is there any workaround to avoid copying of password file from primary to Standby ?

The same question applies to t he log transport  from source database to target database.

SOLUTION

The password for the sys user needs to be the same on the standby as the primary's when a remote login password file is used for redo transport authentication.

That's why you need to copy the password file from the primary to the standby after changing the sys password on the primary database.

However, the 11g and above database introduces a new init parameter REDO_TRANSPORT_USER which specifies the name of the user whose password verifier is used when a remote login password file is used for redo transport authentication. This user must have SYSOPER privileges and must have the same password in the database that initiates the redo transport session and in the database that is the target of the redo transport session.

If this parameter is not specified, then the password verifier of the SYS user will be used when a remote login password file is used for redo transport authentication.

Conclusion: As long as you specify REDO_TRANSPORT_USER to a user who has SYSOPER privileges on both the primary and the standby databases, then you don't need to copy the password file from the primary database to the standby database whenever you change the sys password on the primary database.

SQL> GRANT SYSOPER to &USER;

SQL> ALTER SYSTEM SET REDO_TRANSPORT_USER = <user_name> SID='*';
set the same user REDO_TRANSPORT_USER in both primary and standby.

The user you specified for REDO_TRANSPORT_USER must be an existing user in the database which exactly matches the value of the USERNAME column of a row in the V$PWFILE_USERS view and the value of the SYSDBA or SYSOPER column of the row must also be TRUE.

If you need to change the password for the user specified in REDO_TRANSPORT_USER, then you would need to copy the password file from the primary to the standby site.

Reference: http://docs.oracle.com/cd/E11882 ... 25608/create_ps.htm
Oracle® Data Guard Concepts and Administration
11g Release 2 (11.2)
Part Number E25608-03

3.1.2 Configure Redo Transport Authentication

Note: As the redo transport authentication with a remote login password file worked before you changed the sys password, assume
the following are true before and after you changed the sys password on the primary database.

- The Initialization Parameter 'REMOTE_LOGIN_PASSWORDFILE' is set to 'SHARED' or 'EXCLUSIVE' on the Primary and Standby Database.
- The password file on the Primary and Standby Database is located under $ORACLE_HOME/dbs for unix platform or $ORACLE_HOME\database
for Microsoft Windows platform.
- The password file name is called orapw<primary instance_name> for the primary database and orapw<standby instance_name> for the
standby database. The password file name format for the Microsoft Windows platform is pwd<instance_name>.ora.

You could find the instance_name by login as sysdba :

sqlplus / as sysdba
SQL> show parameter instance_name;

- The Initialization Parameter 'SEC_CASE_SENSITIVE_LOGON' is set to false to disable the password case sensitivity in the database
for 11g primary and the standby databases. If you couldn't set 'SEC_CASE_SENSITIVE_LOGON' to false for security reason, you could

recreate the primary password file with ignorecase=y option and then copy the new password file to the standby site.

UNIX platform
orapwd file=$ORACLE_HOME/dbs/orapw<ORACLE_SID> password=<sys password> entries=5 ignorecase=y

Windows platform
orapwd file=$ORACLE_HOME/database/PWD<ORACLE_SID>.ora password=<sys password> entries=5 ignorecase=y