ORA-16191: Primary log shipping client not logged on standby
Reasons:
Primary and standby database SYS password is not same and it creates conflicts to access the standby database and vice versa.
Solution:
Step-1: Check error and remote_login_passwordfile parameter
SQL> SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;
DESTINATION
--------------------------------------------------------------------------------
STATUS ERROR
--------- -----------------------------------------------------------------
dcstdb
ERROR ORA-16191: Primary log shipping client not logged on standby
SQL> show parameter remote_login_passwordfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL>
Step-2: Disable log_archive_dest_state_2
SQL> show parameter log_archive_dest_state_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
SQL>
SQL> alter system set log_archive_dest_state_2=DEFER sid='*' scope=both;
System altered.
SQL> show parameter log_archive_dest_state_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string DEFER
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
SQL>
Step-3: Re-Create password file by following command
oracle@testbk [/u02/app/oracle/product/11.2.0.4/dbhome_1/dbs]$orapwd FILE=orapw$ORACLE_SID ENTRIES=30 ignorecase=Y
Enter password for SYS:
Step-4: Shutdown Standby Server
oracle@dcstdb [/u02/app/oracle/product/11.2.0.4/dbhome_1/dbs]$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue May 3 16:43:01 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Step-5: Transfer Primary DB password file to Standby server
oracle@testbk [/u02/app/oracle/product/11.2.0.4/dbhome_1/dbs]$scp orapwtestbk1 oracle@dcstdb:/u02/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwdcstdb1
Password:
orapwtestbk1 100% 6144 6.0KB/s 6.0KB/s 00:00
Step-6: Startup Standby Server
SQL> startup
ORACLE instance started.
Total System Global Area 5127602176 bytes
Fixed Size 2214432 bytes
Variable Size 4580181472 bytes
Database Buffers 536870912 bytes
Redo Buffers 8335360 bytes
Database mounted.
Database opened.
SQL>
Step-7: recovery process start
SQL> alter database reocver managed standby database using current logfile disconnect from session;
Step-8: Enable log_archive_dest_state_2 of PRIMARY.
SQL> alter system set log_archive_dest_state_2=ENABLE sid='*' scope=both;
SQL> alter system switch all logfile;
SQL> SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;
DESTINATION
--------------------------------------------------------------------------------
STATUS ERROR
--------- -----------------------------------------------------------------
dcstdb
VALID
Primary and standby database SYS password is not same and it creates conflicts to access the standby database and vice versa.
Solution:
Step-1: Check error and remote_login_passwordfile parameter
SQL> SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;
DESTINATION
--------------------------------------------------------------------------------
STATUS ERROR
--------- -----------------------------------------------------------------
dcstdb
ERROR ORA-16191: Primary log shipping client not logged on standby
SQL> show parameter remote_login_passwordfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL>
Step-2: Disable log_archive_dest_state_2
SQL> show parameter log_archive_dest_state_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
SQL>
SQL> alter system set log_archive_dest_state_2=DEFER sid='*' scope=both;
System altered.
SQL> show parameter log_archive_dest_state_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string DEFER
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
SQL>
Step-3: Re-Create password file by following command
oracle@testbk [/u02/app/oracle/product/11.2.0.4/dbhome_1/dbs]$orapwd FILE=orapw$ORACLE_SID ENTRIES=30 ignorecase=Y
Enter password for SYS:
Step-4: Shutdown Standby Server
oracle@dcstdb [/u02/app/oracle/product/11.2.0.4/dbhome_1/dbs]$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue May 3 16:43:01 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Step-5: Transfer Primary DB password file to Standby server
oracle@testbk [/u02/app/oracle/product/11.2.0.4/dbhome_1/dbs]$scp orapwtestbk1 oracle@dcstdb:/u02/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwdcstdb1
Password:
orapwtestbk1 100% 6144 6.0KB/s 6.0KB/s 00:00
Step-6: Startup Standby Server
SQL> startup
ORACLE instance started.
Total System Global Area 5127602176 bytes
Fixed Size 2214432 bytes
Variable Size 4580181472 bytes
Database Buffers 536870912 bytes
Redo Buffers 8335360 bytes
Database mounted.
Database opened.
SQL>
Step-7: recovery process start
SQL> alter database reocver managed standby database using current logfile disconnect from session;
Step-8: Enable log_archive_dest_state_2 of PRIMARY.
SQL> alter system set log_archive_dest_state_2=ENABLE sid='*' scope=both;
SQL> alter system switch all logfile;
SQL> SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;
DESTINATION
--------------------------------------------------------------------------------
STATUS ERROR
--------- -----------------------------------------------------------------
dcstdb
VALID
