How to enable log shipping on Standby after failing archival.

The Primary Database was giving error in alert log that it was not able to send archivelogs to Standby. The error looked like the following:

Tue Jun  5 10:31:36 2012

Errors in file /u01/oracle/admin/DB1/bdump/DB1_arcp_6846.trc:

ORA-16055: FAL request rejected

ARCH: FAL archive failed. Archiver continuing

Diagnosis:

To check the status of archiving the following sql was issued:

•  Check what is the destination status

 select ds.dest_id                id,

          ad.status,

         ds.database_mode          db_mode,

         ad.archiver               type,

         ds.recovery_mode,

         ds.protection_mode,

         ds.standby_logfile_count  "SRLs",

         ds.standby_logfile_active active,

         ds.archived_seq#

    from v$archive_dest_status ds, v$archive_dest ad

   where ds.dest_id = ad.dest_id

     and ad.status != 'INACTIVE'

   order by ds.dest_id;

    

ID	STATUS	DB_MODE	TYPE	RECOVERY_MODE	PROTECTION_MODE	SRLs	ACTIVE	ARCHIVED_SEQ#
2	ERROR	MOUNTED-STANDBY	LGWR	MANAGED	MAXIMUM PERFORMANCE	8	7	13317
10	VALID	OPEN	ARCH	IDLE	MAXIMUM PERFORMANCE	0	0	13319

We can see one destination which is STANDBY has status "ERROR".

  select dest_id, dest_name, status

    from v$archive_dest_status

   where status != 'INACTIVE';

DEST_ID	DEST_NAME	STATUS
2	LOG_ARCHIVE_DEST_2	ERROR
10	LOG_ARCHIVE_DEST_10	VALID

• Where the log stuck in standby

select thread#, sequence#, process, status from v$managed_standby;

• Check the errors in dataguard status from primary

  select error_code, message, timestamp

    from v$dataguard_status

   where dest_id = 2;

• To check if the process MRPO exists in secondary, issue the command.

SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

This SQL returned no row which means the dataguard was not working properly.

To find out the root cause of this problem I tried the "df -h" to check space on my STANDBY.

I found that there was no space left on device.

The Solution:

STEP 1: First of all space was made available by deleting BACKUPSET of STANDBY.

STEP 2: Issue the command to delete the archived and old archivelog.

BACKUP ARCHIVELOG ALL DELETE ALL INPUT; 

STEP 3: Issue the command to CROSSCHECK the archivelogs.

CROSSCHECK ARCHIVELOG ALL; 

STEP 4: Shutdown STANDBY.

alter database recover managed standby database cancel;
(N.B : This command is used before shutting down the STANDBY database for regular maintenance of STANDBY)

shutdown immediate;

STEP 5: Startup the database in mount mode.

startup mount;

STEP 6: Start log applying to secondary:

 I.  Log on to STAN SQL prompt and issue the command.

 alter database recover managed standby database disconnect; ---apply cuurent logfile

 II. To check archives are applied to secondary, issue the following command.

 Select sequence#,first_time,applied from v$archived_log order by sequence#;

 III. To check if the process MRPO exists in secondary, issue the command.

SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

Now the MRP process should be there and dataguard should work properly.

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