dataguard gap: How to Resolve Error in Remote Archiving

REFERENCE : How to Resolve Error in Remote Archiving [ID 799353.1]

Problem :

ORA-12541: TNS:no listener
LNS: Failed to archive log 5 thread 2 sequence 1101 (12541)
Sun May 22 13:05:55 2011
Error 12541 received logging on to the standby
Check whether the listener is up and running.
Solution :
Step 1. Check the status of remote archive destination on primary database

SQL> SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;

DESTINATION          STATUS     ERROR
-------------------- --------- ----------------------------------------
ERPDR                 ERROR     ORA-12541: TNS:no listener

Step 2. Defer and enable the remote archive destination:

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Step 3. Perform 2 log switches on the Primary/Source database:

SQL>ALTER SYSTEM SWITCH LOGFILE;
SQL>ALTER SYSTEM SWITCH LOGFILE;

Step 4. Check the status of remote archive destination again:

SQL> SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;

DESTINATION          STATUS     ERROR
-------------------- --------- ----------------------------------------
ERPDR                 ERROR     ORA-12541: TNS:no listener

If the status of remote archive destination is error, then check the error code under Error.
Resolve the error in the archiving and perform step 1 to 4 again.

If there is no error in the remote archiving than status of remote archive destination will be "VALID"

SQL> SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;

DESTINATION          STATUS     ERROR
-------------------- --------- ----------------------------------------
ERPDR             VALID

Step 5. Check whether redo logs are getting received on the standby using:

FROM STANDBY

SQL> SELECT THREAD#,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;

THREAD# MAX(SEQUENCE#)
---------- --------------
1         1087
2         1101

Perform 2 log switches on primary database:

SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;

Check whether SEQUENCE# changes

SQL> SELECT THREAD#,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;

THREAD# MAX(SEQUENCE#)
---------- --------------
1         1087
2         1103

Remote archiving is working fine if the sequence# is moving.
Proceed to step 6 if sequence# is not moving:

Step 6. Check the value of log_archive_max_processes on primary:

SQL> SHOW PARAMETER LOG_ARCHIVE_MAX_PROCESSES

NAME                            TYPE        VALUE
------------------------------- ----------- ------------------------------
log_archive_max_processes       integer     2

Increase the value of log_archive_max_processes to 10 (From 10.2 you can increase value up to 30).
It is highly recommended to increase this value to avoid any issues in case of a GAP.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=10;

Repeat step 5 and check whether redo is getting received on the standby.
Proceed to step 7 if issue does not get resolved.

Step 7. Spawn new ARC processes for primary database:  This is required because ARC processes get stuck with old error messages

a. Check the OS PID of the ARC processes on the primary database using:
For Unix

$ ps -ef|grep -i arc| grep $ORACLE_SID

For windows

You can get the os pid from alertlog or from this query output

select p.pid,p.spid,p.serial#,bgp.name from v$process p, v$bgprocess bgp
where p.addr=bgp.paddr and bgp.name like 'ARC%';
for Unix

$kill -9 ...

Either kill the arc processes or restart the instance.