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; 
 
 Referance Link: 
 
 http://troubleurheart.blogspot.in/2011/01/monitor-dataguard-status.html
 http://alazydbasblog.blogspot.in/2012/04/steps-to-resolve-error-1031-received.html
 http://bitbach.wordpress.com/2010/08/27/pingarc1-heartbeat-failed-to-connect-to-standby-dgp-error-is-1031/
 www.dbaglobe.com/2011/01/monitor-dataguard-status.html
 http://muthuappsdba.blogspot.in/2012/12/error-16191-pingarc2-heartbeat-failed.html

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: