WARNING archived log not deleted, needed for standby or upstream capture process

I was having a situation, where my FRA was full & database was hung. After investigating found that archivelogs were not getting deleted, so occupying the space in FRA. When I tried deleting them manually I got following warning

RMAN> delete archivelog until sequence <seqno>;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
RMAN-08137: WARNING: archived log not deleted, 
needed for standby or upstream capture process
archived log file name=/u02/test/archivelog/
2011_11_01/o1_mf_1_5895_7bxt9b8z_.arc thread=1 sequence=5895
RMAN-08137: WARNING: archived log not deleted, 
needed for standby or upstream capture process

Temporary solution to remove archive log

1. Removed archive log from filesystem using OS commands
2. Performed RMAN crosscheck

Other alternative was to remove archive log using force option

delete noprompt force archivelog all completed before ‘<condition>’;
But further analysis was required to understand why archive logs were not getting deleted. This environment was not part of standby database. Which leaves other option i.e upstream capture.

1. Checked the DBA_CAPTURE view

SQL> select capture_name, status, captured_scn, applied_scn, 
capture_type from dba_capture;
CAPTURE_NAME STATUS CAPTURED_SCN APPLIED_SCN CAPTURE_TY
------------------------------ -------- ------------ -----
OGG$_EXT154E13570 DISABLED LOCAL

Something related to Golden Gate!! This database use to have GG environment, but it was not in use.

2. Checked GG for extracts

sels-test4:test:/u01/app/gg>ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x64, 64bit (optimized), Oracle 11g on Apr 21 2011 22:42:14
Copyright (C) 1995, 2011, Oracle and/or its affiliates. 
All rights reserved.

GGSCI (sels-test4) 4> info *
EXTRACT EXT1 Last Started 2011-05-31 14:30 Status ABENDED
Checkpoint Lag 00:00:00 (updated 2051:20:22 ago)
Log Read Checkpoint Oracle Redo Logs
 2011-09-12 21:08:33 Seqno 4678, RBA 872960

3. Deleted the extract

GGSCI (sels-test4) 7> delete extract EXT1
2011-12-07 08:30:04 WARNING OGG-01753 Cannot unregister 
EXTRACT EXT1 from database because no database login was
provided. You can manually unregister this group later
with the UNREGISTER EXTRACT <GROUP NAME> LOGRETENTION 
command. Issue DBLOGIN first.
Deleted EXTRACT EXT1.

Deleting the extract was not enough. Need to unregister it as well (or connect to database at the time of delete)

GGSCI (sels-test4) 1> dblogin userid GG_EXTRACT password <pwd>
Successfully logged into database.
GGSCI (sels-test4) 2> Unregister extract ext1 LOGRETENTION
2011-12-07 08:44:42 INFO OGG-01750 Successfully unregistered 
EXTRACT EXT1 from database.

4. Verified the DBA_CAPTURE view

SQL> select capture_name, status, captured_scn, applied_scn, 
capture_type from dba_capture; 
no rows selected

After some reading, found that GG 11 will prevent rman from deleting archivelog that hasn’t been mined. RMAN checks min_required_capture_change# in v$database, which is populated every 6 hrs based on dba_capture. Even after extract is stopped, entry is still in dba_capture & RMAN won’t allow archivelog delete.

Later I could delete the archivelog

RMAN> delete archivelog until sequence <seqid>;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
List of Archived Log Copies for database with db_unique_name test
==================================================================
Key Thrd Seq S Low Time
------- ---- ------- - -------------------
6681 1 6687 A 11/30/2011 23:00:03
 Name: /u02/test/archivelog/2011_12_01/o1_mf_1_6687_7fdxkkcz_.arc
6682 1 6688 A 12/01/2011 00:00:06
 Name: /u02/test/archivelog/2011_12_01/o1_mf_1_6688_7ff120j8_.arc

Do you really want to delete the above objects(enter YES/NO)? yes
deleted archived log
archived log file name=/u02/test/archivelog/2011_12_01/
o1_mf_1_6687_7fdxkkcz_.arc RECID=6681 STAMP=768700812
deleted archived log
archived log file name=/u02/test/archivelog/2011_12_01/
o1_mf_1_6688_7ff120j8_.arc RECID=6682 STAMP=768704426
Deleted 2 objects
Advertisements
This entry was posted in Oracle Golden Gate, Oracle Recovery Manager and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s