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 220.127.116.11 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