Error 1017 received logging on to the standby returning error ORA-16191

ORA-16191: The "Unable to Log On to Standby" Error

 TT00 (PID:184449): Error 1017 received logging on to the standby

TT00 (PID:184449): -------------------------------------------------------------------------

TT00 (PID:184449): Check that the source and target databases are using a password file

TT00 (PID:184449): and remote_login_passwordfile is set to SHARED or EXCLUSIVE,

TT00 (PID:184449): and that the SYS password is same in the password files,

TT00 (PID:184449): returning error ORA-16191

TT00 (PID:184449): -------------------------------------------------------------------------

The ORA-16191 error, "unable to log on to standby," is a specific and critical issue for Oracle Data Guard professionals. This error occurs when a primary database process (specifically a Transport process, or TT00) fails to establish a connection to its designated standby database. The error message you've provided, which includes the underlying ORA-01017 ("invalid username/password") error, points directly to a password file synchronization problem. This guide will provide a detailed explanation of the error, a systematic approach to diagnosis, and the precise steps to resolve the issue and ensure your Data Guard environment is robust.

The Foundation: Data Guard and Password Files

To understand ORA-16191, you must be familiar with the core components of Oracle Data Guard and how they interact.

1. Transport Processes (TT00): These are background processes on the primary database responsible for transporting redo data to the standby. They must be able to log in to the standby database's instance to deliver the redo logs.

2. Password File: This is an external file that stores the encrypted passwords for privileged users like SYS, SYSTEM, and SYSMAN. It allows for privileged connections (e.g., as SYSDBA) without requiring the password to be exposed in a connection string.

3. REMOTE_LOGIN_PASSWORDFILE: This parameter, set to SHARED or EXCLUSIVE, tells the database to use the password file for remote privileged connections. In a Data Guard environment, this is crucial for the primary database to authenticate with the standby.

The ORA-16191 error, as seen in your message, is a direct result of a failure in this authentication process. The primary database process is attempting to connect to the standby as SYS, but the password it's using (from its local password file) does not match the password stored in the standby database's password file. The standby database rejects the connection with ORA-01017.

The Breakdown: Why the Passwords Don't Match

Password file mismatch in a Data Guard setup is a common issue, and it can happen for several reasons:

1. Manual Password Change: A user or script changed the SYS password on the primary database using ALTER USER SYS IDENTIFIED BY new_password; but did not copy the new password file to the standby. The password file is not automatically synchronized by Data Guard.

2. Password File Corruption: The password file on either the primary or standby database could be corrupt, leading to an authentication failure.

3. Standby Creation Issues: The standby database was created without a proper copy of the primary's password file, or the password file was not copied correctly after a password change during the initial setup.

Diagnosis: Pinpointing the Problem

Before you can fix the password file, you need to verify the mismatch.

1. Check REMOTE_LOGIN_PASSWORDFILE: First, ensure the parameter is correctly set on both primary and standby databases.

   SQL

   SHOW PARAMETER remote_login_passwordfile;
   -- It should be SHARED or EXCLUSIVE
   

2. Check V$LOGSTDBY_PROCESS on the Primary: This view can provide information about the TT00 process. Look for the STATUS column, which will confirm the failure to connect.

   SQL

   SELECT process, status, error FROM V$LOGSTDBY_PROCESS;
   

   (Note: This is more for logical standby, but similar views exist for physical standby).

3. Verify Password Files: This is the most crucial step. Use a tool like orapwd to verify the existence and modification times of the password files.

   • Primary: $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapw<sid> force=y (Don't run this, just check the file). ls -l $ORACLE_HOME/dbs/orapw<sid>

   • Standby: ls -l $ORACLE_HOME/dbs/orapw<sid>

     A recent password change on the primary that wasn't mirrored on the standby will be evident in the file's timestamp.

Resolution: A Practical Guide

The resolution for ORA-16191 is straightforward: you must make the password files identical.

1. Reset the SYS Password on the Primary: The safest and most foolproof method is to reset the SYS password on the primary database, which will generate a new password file, and then copy it to the standby.

   • Step A: Connect to the Primary Database as SYSDBA and shut it down.

   SQL

   sqlplus / as sysdba
   SHUTDOWN IMMEDIATE;
   

   • Step B: Use orapwd to create a new password file on the primary.

   Bash

   cd $ORACLE_HOME/dbs
   orapwd file=orapw<sid> password=new_sys_password entries=10 force=y
   

   • Step C: Start the primary database.

   SQL

   STARTUP;
   

   • Step D: Copy the new password file to the standby server.

   Bash

   scp $ORACLE_HOME/dbs/orapw<sid> oracle@standby_server_hostname:$ORACLE_HOME/dbs/
   

2. Restart the Standby Database: The standby database needs to be restarted to load the new password file.

   • Step A: Connect to the Standby Database as SYSDBA and shut it down.

   SQL

   sqlplus / as sysdba
   SHUTDOWN IMMEDIATE;
   

   • Step B: Start the standby database and restart the managed recovery process.

   SQL

   STARTUP NOMOUNT;
   ALTER DATABASE MOUNT STANDBY DATABASE;
   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
   

   After these steps, the TT00 process on the primary should be able to log into the standby without error, and redo transport will resume.

Video Resources

• Oracle Data Guard: Physical Standby Configuration

  • https://www.youtube.com/watch?v=kYx8D9T6Y5c

• Managing Oracle Password Files

  • https://www.youtube.com/watch?v=F07gq-0w4aQ

Conclusion

The ORA-16191 error, when combined with ORA-01017, is a clear indicator that the password files between your primary and standby databases are out of sync. This is a critical issue that halts the transfer of redo data, breaking your Data Guard protection. The resolution is to generate a new password file on the primary database and securely copy it to the standby server. By understanding the role of password files in a Data Guard environment, you can quickly diagnose and resolve this issue, ensuring the high availability and disaster recovery capabilities of your Oracle database.