Solution for ORA-16191: Primary log shipping client not logged on standby in DataGuard
If you change SYS password with ALTER USER SYS IDENTIFIED BY NEWPASSWORD on the primary database of a dataguard environment, Logs should applied on standby side and sychronization between primary and standby database is fine .But once you restart your standby database, primary side stops to transfer archivelogs to standby and you will see an error on primary database alertlog file like:
------------------------------------------------------------------
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
------------------------------------------------------------------
check whether you are able to log into the standby from the primary using sqlplus or not
sqlplus sys@STANDBY as sysdba --- you can't
This is because "If you issue the ALTER USER statement to change the password for SYS, both the password stored in the data dictionary and the password stored in the password file are updated." So your password file is updated in primary side but not in standby side.
In this situation set your password file in standby server with:
$orapwd file=orapw$ORACLE_SID password=***** entries=5 ignorecase=Y (don't forget to move/delete old one)
or you can copy the primay database password file and rename it at standby side
Conclusion: If you're going to change your sys password in a dataguard environment you must set the password files with new password in both primary and standby servers.
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. In case of RAC defer LOG_ARCHIVE_DEST_STATE_n in all nodes.
Step 2: change the password in primary
Step 3: Recreate or copy the password file in standby. In case of RAC shutdown all instance of standby before recreating the password file.
Step 4: Enable the remote archival destination (log_archive_dest_state_n) parameter in primary
pwd file enhancement in oracle 12c
Until Oracle Database 11g password file of Oracle Database and ASM instance had to be stored in regular filesystem of windows or unix. However, starting from Oracle Database 12c R1 Oracle Database and ASM instance password file can be stored in ASM storage. This gives a great advantage in terms of ease of management of password file in Real Application Clusters (RAC) environment. When using ASM, in earlier versions of RAC, password files had to be stored in individual servers where RAC instances are running. For RAC environments password file can be stored in shared ASM disk group. For this, the compatible.asm disk group attribute must be set to 12.1 or higher for the disk group where the password is to be stored. The SYSASM or SYSDBA privilege is required to manage the Oracle ASM and database password files.
If you change SYS password with ALTER USER SYS IDENTIFIED BY NEWPASSWORD on the primary database of a dataguard environment, Logs should applied on standby side and sychronization between primary and standby database is fine .But once you restart your standby database, primary side stops to transfer archivelogs to standby and you will see an error on primary database alertlog file like:
------------------------------------------------------------------
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
------------------------------------------------------------------
check whether you are able to log into the standby from the primary using sqlplus or not
sqlplus sys@STANDBY as sysdba --- you can't
This is because "If you issue the ALTER USER statement to change the password for SYS, both the password stored in the data dictionary and the password stored in the password file are updated." So your password file is updated in primary side but not in standby side.
In this situation set your password file in standby server with:
$orapwd file=orapw$ORACLE_SID password=***** entries=5 ignorecase=Y (don't forget to move/delete old one)
or you can copy the primay database password file and rename it at standby side
Conclusion: If you're going to change your sys password in a dataguard environment you must set the password files with new password in both primary and standby servers.
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. In case of RAC defer LOG_ARCHIVE_DEST_STATE_n in all nodes.
Step 2: change the password in primary
Step 3: Recreate or copy the password file in standby. In case of RAC shutdown all instance of standby before recreating the password file.
Step 4: Enable the remote archival destination (log_archive_dest_state_n) parameter in primary
pwd file enhancement in oracle 12c
Until Oracle Database 11g password file of Oracle Database and ASM instance had to be stored in regular filesystem of windows or unix. However, starting from Oracle Database 12c R1 Oracle Database and ASM instance password file can be stored in ASM storage. This gives a great advantage in terms of ease of management of password file in Real Application Clusters (RAC) environment. When using ASM, in earlier versions of RAC, password files had to be stored in individual servers where RAC instances are running. For RAC environments password file can be stored in shared ASM disk group. For this, the compatible.asm disk group attribute must be set to 12.1 or higher for the disk group where the password is to be stored. The SYSASM or SYSDBA privilege is required to manage the Oracle ASM and database password files.
