Saturday, August 23, 2014

ORA-16191: Primary log shipping client not logged on standby

After Dataguard (Physical Standby) Configuration (RAC/Non-RAC), Many time I've faced issue that, initially archives are not getting transferred from Primary to Standby Database through RFS even though everything is set properly. So I thought, I should write about it this time.

One of the Cause is : "ORA-16191: Primary log shipping client not logged on standby"

You can check the same in with the below query as well as in the alert log,

SQL> select error from v$archive_dest_Status where dest_id=2;

ERROR

-----------------------------------------------------------------
ORA-16191: Primary log shipping client not logged on standby

================ Alert Log Portion (Primary) ================

Suppressing further error logging of LOG_ARCHIVE_DEST_2.

Sat Aug 23 17:43:09 2014
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

Reason: Check for Password File and verify the same using "sqlplus sys/xxxx@TNS as sysdba" from all Database Instances (in case of RAC) of Primary & Standby and it should get connected.

In my case though, password file was exist on all locations and even it was able to connect with sqlplus from primary to standby and vice versa, but was still getting "ORA-16191: Primary log shipping client not logged on standby"

Workaround/Solution:

1) Disable log_archive_dest_state_2 for which log_archive_dest_2 is Standby Location.

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
----------------------------------- ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=EDQPRDBLR LGWR ASYNC 
                                                                       valid_for=(all_logfiles,primary_role)             db_unique_name=EBSPRD

SQL> alter system set log_archive_dest_state_2=DEFER sid='*' scope=both;

2) Recreate password file on 1st RAC Instance with exact Syntax as below,

$ orapwd file=/u01/app/oracle/product/11.2.0.3/EDQPRD/dbs/orapwEDQPRD1 password=oracle entries=10

[oracle@inmumdcdbadm01 dbs]$ ls -l orapwEDQPRD1
-rw-r----- 1 oracle oinstall 2560 Aug 23 17:48 orapwEDQPRD1

The permission should be as it is shown.

3) Replicate (scp) / Recreate on other RAC Instances/Servers with given appropriate Syntax and check for the size.

4) Enable log_archive_dest_state_2 again,

SQL> alter system set log_archive_dest_state_2=ENABLE sid='*' scope=both;

5) Try to make log switches,

SQL> alter system switch all logfile;

================Check for the Alert Log (Primary) ======================

You should see this message,

Sat Aug 23 17:51:37 2014

******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Sat Aug 23 17:52:11 2014

Even Error get disappear from below query,

SQL> select inst_id,error from gv$archive_Dest_status where dest_id=2;

   INST_ID ERROR
---------- -----------------------------------------------------------------
         1
         2

Now, your archives which are getting generated at (RAC) Primary Servers would transfer through RFS to (RAC) Standby Servers and will fetch the gap too, provided FAL_CLIENT & FAL_SERVER have been properly mentioned.

Thanks, Your Comments / Suggestions are welcome - Manish

3 comments:

  1. Thanks for your post. This was really useful.

    ReplyDelete
  2. After taking the class, you will have the ability to make and manage your company from any place in the world. Visit here for more interesting information on Contact - Niche Tyrant.

    ReplyDelete