Tackle Archive Log failure in RAC Environment

Tackle Archive Log failure in RAC Environment:-

Your database is in archive log mode and archiving works fine until suddenly connecting via SQLPLUS generates the following error:

 ORA-00257:  archiver is stuck. CONNECT INTERNAL only, until freed

Symptoms:-

1. Frontend does not open up.
2. Unable to connect to database through any user not even apps.
3. Tools like TOAD, SQL-Developer do not connect to database.

Observation:-

The error states that there is an issue with the archive destination occupancy.

Check the archive destination by command:-

archive log list

 

Check if the archive destination is valid or not. With command:-

select * from v$archive_dest;

 

##Note that unlike Single node you won’t find a physical location in RAC instance. The environment I am showing example of is a 3 node RAC. Hence you can not delete or move the archive files manually.

Solution:-

Connect to RMAN:-

$rman target /

Check the archive logs with the command:-

RMAN> list archivelog all;

To delete the archive logs use:-

RMAN> delete noprompt archivelog all;

You may also delete selected archive log files.

 

Connect to catalog RMAN database and do the resync catalog:-

 

Use the command:-

alter system switch logfile;

Check the log destination again if the ERROR status has been removed:-

select * from v$archive_dest;

Perform a clean bounce and you are good to go.. .

:) :)

Author – Akash Pramanik

Database Administrator in IBM

akash007.pramanik@gmail.com