Possible Bug in Logical Standby Auto-Deletion of Logs


Logical Standby databases has an annoying problem—the foriegn archived redo log files were not being cleaned up automatically.

Manual Procedure

The manual procedure is to use the DBMS_LOGSTDBY.PURGE_SESSION to identify …all archived redo log files that have been applied to the logical standby database and are no longer needed by SQL Apply.

Then, you query from the DBA_LOGMNR_PURGED_LOG view.

The following code should work for ASM 11.2:

EXEC DBMS_LOGSTDBY.PURGE_SESSION
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 1024
SET TRIMSPOOL ON
SET ECHO OFF
SPOOL remove_old_logs.sh
SELECT 'asmcmd rm -f ' || FILE_NAME as cmd
  FROM DBA_LOGMNR_PURGED_LOG;
SPOOL OFF

Automatic Procedure

As I was researching this article, I came across Automatic Deletion of Log Files.

It would appear that I should use the DBMS_LOGSTDBY.APPLY_SET procedure as follows:

EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'TRUE');

This value should be reflected in the following query on the DBA_LOGSTDBY_PARAMETERS view:

SELECT name, value FROM DBA_LOGSTDBY_PARAMETERS WHERE name = 'LOG_AUTO_DELETE';

Further Investigation

I am sure that this parameter is set correctly. Looks I might be striking an Oracle bug. I will have to investigate further.

Advertisements

6 thoughts on “Possible Bug in Logical Standby Auto-Deletion of Logs

  1. Solution to Possible Bug in Logical Standby Auto-Deletion of Logs – Yet Another OCM

    • Fred,

      Today, I got bitten by having this parameter too small because of the change in the workload.

      This is a management headache I do not need.

      Douglas

      • The logs should really delete itself based on the retention parameter really. I couldn’t find many references about this or many people who encountered a similar problem…

      • Fred,

        The retention parameter determines which logs are reclaimable once there is space pressure. In other words, the logs are not deleted until the space is needed, not when the retention period expires.

        This confused me as well.

        Douglas

  2. In my case (11.2.0.3) – deletion is occurring even though there is no space pressure.

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