Error 1031 received logging on to the standby

ISSUE

Error 1031 received logging on to the standby
ORA-01031: insufficient privileges
PING[ARC0]: Heartbeat failed to connect to standby 'dgp'. Error is 1031.

PRIMARY
=======

Primary database Alert log error:
--------------------------------

Error 1031 received logging on to the standby

ORA-01031: insufficient privileges

PING[ARC0]: Heartbeat failed to connect to standby 'dgp'. Error is 1031.

Chech DR sync and see the difference.

THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)

------ ----------- ------------- ------------ ------------------------ -------------------------

set pages 999 lines 999

col MESSAGE for a100

select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS')||' '||message||severity from gv$dataguard_status where severity in ('Error','Fatal') order by timestamp;

show parameter log_archive_dest_state_2;

LISTNER VERIFICATION FROM PRIMATY DB

------------------------------------

select dest_id,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';

FIND GAP

--------

select thread#,low_sequence#,high_sequence# from gv$archive_log;

ps -ef |grep tns

lsnrctl status 

DR
==

DR database Alert log error:

----------------------------

Error 1031 received logging on to the standby

set pages 999 lines 999

col MESSAGE for a100

select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS')||' '||message||severity from gv$dataguard_status where severity in ('Error','Fatal') order by timestamp;

select inst_id,process,status,thread#,sequence#,block#,blocks from gv$managed_standby;

PROCESS STATUS

------- ------------

RFS IDLE

RFS IDLE

RFS IDLE

MRP0 WAIT_FOR_LOG

checking log transfer and apply

-------------------------------

SELECT SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED FROM gV$ARCHIVED_LOG ORDER BY SEQUENCE#;

select count(*) from GV$ARCHIVED_LOG where applied='NO';

Redo transfer was not happening. When we checked in the

v$managed_process data dictionary view, we could see that RFS was not starting.

Here,

ora-01031 usually appears when some sysdba session failes to authenticate

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.

1.The RFS process was not started in the standby which indicates that standby is not receiving any redo information from the primary.

2.In the primary alert log file we could see the errors indicating that the primary is not able to communicate with the standby instance. The error observed was "Error 1031 received logging on to the standby".

3.The time stamp of the password file on the primary and the standby was different. This indicated the possibility of having the wrong password file in the standby.

SOLUTION:
=========

For the redo transfer to take place efficiently, the password file on standby should be a copy from the primary and renamed standby.We can use v$pwd_file_users data dictionary view to check if the password file is used

1.copy the password file from the primary to the standby and renamed the password file in the following format ie orapw<sid> . 

2.Restar the media recovery process on the standby. 

Once the above steps are completed we could see that redo shipping and redo apply is taking place.

Copy the latest Password file from available PRIMARY Node to rest of PRIMARY and STANDBY nodes:
---------------------------------------------------------------------------------------------- 

Primary (Node 1)
----------------

cd $ORACLE_HOME/dbs

ls -lrt

scp orapw<sid> oracle@PROD_NODE_2_hostname:/oracle/home/dbs

scp orapw<sid> oracle@DR_NODE_1_hostname:/oracle/home/dbs

scp orapw<sid> oracle@DR_NODE_2_hostname:/oracle/home/dbs

or

DR
==

select * from gv$pwfile_users;

we have to check sec_case_sensitive_logon parameter on primary and standby.

SQL> show parameter sec_case_sensitive_logon;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

sec_case_sensitive_logon boolean FALSE

SQL>

We have to recreate the passwd file or copy Primary server to Standby server.

In cause sec_case_sensitive_logon parameter value is true, we have to use below orapwd command.

orapwd file=$ORACLE_HOME/dbs/orapwPROD password=password123 entries=10 ignorecase=y

In cause sec_case_sensitive_logon parameter value is false, we have to use below orapwd command.

orapwd file=$ORACLE_HOME/dbs/orapwPROD password=password123 entries=10

DR
==

cancelling mrp process:

alter database recover managed standby database cancel;

starting mrp process:

alter database recover managed standby database disconnect from session;

select inst_id,process,status,thread#,sequence#,block#,blocks from gv$managed_standby;.