RMAN-08137
Saw error RMAN-08137 when we tried to delete some aged archive logs.
RMAN> delete noprompt archivelog until time 'sysdate-7';
...
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
RMAN-08137 means that some processes are registered to capture archived logs at this moment, you need to unregister them from the database in order to release the hook.
Usually, it happens during the initial phase of an Oracle GoldenGate (OGG) building to prevent archive logs from being deleted.
Let's see what processes are registered with the capture of archived logs. They're usually extract groups and sometimes replicat groups.
SQL> column capture_name format a20;
SQL> select capture_name, capture_type, status from dba_capture;
CAPTURE_NAME CAPTURE_TY STATUS
-------------------- ---------- --------
OGG$CAP_ERPAPP_E LOCAL ENABLED
OGG$CAP_CRMAPP_E LOCAL DISABLED
OK, there're 2 extracts groups in the capture, one is running and the other is not in use.
GGSCI (dbs) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ERPAPP_E 00:00:02 00:00:04
EXTRACT RUNNING ERPAPP_P 00:00:00 00:00:04
Solution
To solve RMAN-08137, you have 2 options.
Use FORCE Keyword
We use FORCE Keyword to delete archived logs no matter what condition it is.
RMAN> delete noprompt force archivelog until time 'sysdate-7';
You have to make sure that no one needs those archived logs.
Unregister Captures
We can also unregistering all registered extract groups from the database.
Let's see the steps.
Stop Extracts
We need to stop the running extract first.
GGSCI (dbs) 2> stop ERPAPP_E
Sending STOP request to EXTRACT ERPAPP_E ...
STOP request pending end-of-transaction (1 records so far).
Since the other extract CRMAPP_E is not in use, so we don't have to stop it.
Let's make sure its status.
GGSCI (dbs) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED ERPAPP_E 00:00:02 00:00:11
EXTRACT RUNNING ERPAPP_P 00:00:00 00:00:10
DBLOGIN
We make a connection to the database by DBLOGIN.
GGSCI (dbs) 4> dblogin userid ogg, password <ogg_password>
Successfully logged into database.
To prevent any confusion, we can also use a TNSNAME.
GGSCI (dbs) 4> dblogin userid ogg@orcl, password <ogg_password>
UNREGISTER EXTRACT
We perform an UNREGISTER EXTRACT command.
GGSCI (dbs as ogg@orcl) 5> unregister extract ERPAPP_E database;
2023-12-17 09:01:44 INFO OGG-01750 Successfully unregistered EXTRACT ERPAPP_E from database.
GGSCI (dbs as ogg@orcl) 6> unregister extract CRMAPP_E database;
2023-12-17 09:03:14 INFO OGG-01750 Successfully unregistered EXTRACT CRMAPP_E from database.
Both of them should be unregistered, even though the second one is not in use.
Start Extracts
GGSCI (dbs as ogg@orcl) 7> start ERPAPP_E
Sending START request to MANAGER ...
EXTRACT ERPAPP_E starting
GGSCI (dbs as ogg@orcl) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ERPAPP_E 00:00:02 00:00:01
EXTRACT RUNNING ERPAPP_P 00:00:00 00:00:08
Check Capture
The last step, check the capture list.
SQL> select capture_name, capture_type, status from dba_capture;
no rows selected
Good! There's none. We can now deleted aged archived logs.