Monday 15 July 2013

Fix-Heartbeat failed to connect to standby 'prm'. Error is 1031 ORA-01031: insufficient privileges

Error 1031 received logging on to the standby
ORA-01031: insufficient privileges

Heartbeat failed to connect to standby 'prm'. Error is 1031


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.Restart 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;