Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Loading...

Monday, December 26, 2011

ORA-16055: FAL request rejected with ORA-00270:

On primary database we were getting the below errors in the alert log continuously:


CONTENTS OF ATTACHED FILE: alert_PRIMARYDB3.monitor.diff

ORA-00270: error creating archive log
ORA-16055: FAL request rejected
ORA-00270: error creating archive log


Run the below sql on standby and primary:

select message from v$dataguard_status;

MESSAGE
--------------------------------------------------------------------------------
ARC3: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (270)
ARC3: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
FAL[server, ARC3]: Error 270 creating remote archivelog file 'PRIMARYDBY'


Now see what is the error for the primary for the archive log dest:



from the above message it is clear that it is not able to create/ship log in the destination.

hmmm so it could be network/listener down on standby?


>/usr/sbin/ping -s 99.6.183.183
PING 99.6.183: 56 data bytes
64 bytes from standby (99.6.183.183): icmp_seq=0. time=24.0 ms
64 bytes from standby (99.6.183.183): icmp_seq=1. time=23.9 ms


Ping & tnsping are under the specified time out periods:


Currently MRP is waiting on the below log:
SQL> select process,status,thread#,sequence# from v$managed_standby;

PROCESS STATUS THREAD# SEQUENCE#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
MRP0 WAIT_FOR_GAP 1 138550

Stopped/started the recovery of the logs on the standby:


SQL> alter database recover managed standby database cancel;

started by again:

SQL>alter database recover managed standby database disconnect from session;


still the MRP is on waiting on the same log:

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

PROCESS STATUS THREAD# SEQUENCE#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
MRP0 WAIT_FOR_GAP 1 138550

confirmed the above log is available on the Primary archivelog destination so we can rule out the "missing logs on the primary" option.

still the standby is not able to receive any logs.

I am missing something here.

2) checking on Space.

On primary everything is under control, like data, FRA and redo disk groups are below thresholds

How about on Standby?

So on standby:
DB instances associated are ..

STANDBY1

ASM Diskgroup information ..


DiskGroup Name %Used TOTAL_MB FREE_MB
-------------------- ----- ------------ ------------
standby_DATA_01 72 2,880,246 795,639
standby_FRA_01 100 392,220 604



There you go FRA on the standby is 100% ahaaa.... that's why Primary is unable to create log on the standby.


So how do we cleanup the FRA on the standby database, By default the ASM instance should have automatically cleaned up the FRA by deleting the unwanted/applied logs from the FRA. In this case looks like it didn't cleanup the FRA this time.


Now get the max sequence number which was applied in the standby.


SQL> select thread#,max(sequence#) from v$archived_log where applied ='YES' and REGISTRAR='RFS' group by thread# ;

THREAD# MAX(SEQUENCE#)
---------- --------------
1 138549
2 35506
3 34795


from the above we got the max sequence numbers which were applied on the standby and no more needed for recovery. So we can delete archive logs from the ASM upto the above sequence numbers for that particular thread#.


so to delete the logs connect to the RMAN on the standby:


RMAN>connect target / ;

*** Here I am deleting the logs for thread#1 upto the below sequence#

RMAN>delete noprompt archivelog until sequence 138540 thread 1;

output:

deleted archive log
archive log filename=+PRIMARYDBY_FRA_01/yPRIMARYDB/archivelog/2011_12_26/thread_1_seq_138391.3384.770889425 recid=32426 stamp=770889932
deleted archive log
archive log filename=+PRIMARYDBY_FRA_01/yPRIMARYDB/archivelog/2011_12_26/thread_1_seq_138392.3429.770889587 recid=32427 stamp=770890095
====
Deleted 335 objects

RMAN> exit


Recovery Manager complete.



This cleared the FRA.


ASM Diskgroup information ..


DiskGroup Name %Used TOTAL_MB FREE_MB
-------------------- ----- ------------ ------------
standby_DATA_01 72 2,880,246 795,639
standby_FRA_01 9 392,220 355,770




Now verify whether logs are being shipped at getting applied on the standby:






confirm by:

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

PROCESS STATUS THREAD# SEQUENCE#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
MRP0 WAIT_FOR_GAP 3 34797


Now the logs are being shipped and appplied since the MRP is moving forward.

confirm again by:

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


PROCESS STATUS THREAD# SEQUENCE#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
MRP0 WAIT_FOR_GAP 3 34800


Make sure you have archive log deletion policy is set on the standby:

RMAN>
CONFIGURE ARCHIVELOG DELETION POLICY TO [CLEAR | NONE | APPLIED ON STANDBY];

1 comment:

Keith Bremer said...

Hi Sameer,

Thanks a million for this - it solved a problem I was having on my testbed setup. It's funny how you can miss some of the most obvious things...

Regards,
Keith Bremer.