Archive log files which are used by SQL apply process at Logical Standby database is said to be Foreign Archive Logs or Remote Archived Logs. Foreign Archive log files are managed by SQL Apply process.
To know more about it Oracle Dataguard
After all the redo entries of Archive log files are applied using SQL apply process, these files are kept for a specific amount of time, a time set for LOG_AUTO_DEL_RETENTION_TARGET or for 1440 minutes if LOG_AUTO_DEL_RETENTION_TARGET is not specified. Once the timer exceeds this limit, Archive log files are automatically deleted.
Note: If you are using a flash recovery area, auto-deletion is based on the flash recovery area’s space pressure.
You can enable and disable the auto-delete feature for archived redo logs by using the DBMS_LOGSTDBY.APPLY_SET procedure. By default, the auto-delete feature is enabled.
To disable automatic deletion of archive logs we can use the following command.
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE','FALSE');
PL/SQL procedure successfully completed.
If you are overriding the default automatic log deletion capability, periodically perform the following steps to identify and delete archived redo log files that are no longer needed by SQL Apply:
To purge the logical standby session of metadata that is no longer needed, enter the following PL/SQL statement:
SQL> EXECUTE DBMS_LOGSTDBY.PURGE_SESSION;
This statement also updates the DBA_LOGMNR_PURGED_LOG view that displays the archived redo log files that are no longer needed.
Query the DBA_LOGMNR_PURGED_LOG view to list the archived redo log files that can be removed:
SQL> SELECT * FROM DBA_LOGMNR_PURGED_LOG; FILE_NAME -------------------------------------------------------------------------------- /u01/arc/mgr/stdby/1_132_972570193.dbf /u01/arc/mgr/stdby/1_133_972570193.dbf /u01/arc/mgr/stdby/1_134_972570193.dbf /u01/arc/mgr/stdby/1_135_972570193.dbf /u01/arc/mgr/stdby/1_136_972570193.dbf /u01/arc/mgr/stdby/1_137_972570193.dbf /u01/arc/mgr/stdby/1_138_972570193.dbf /u01/arc/mgr/stdby/1_139_972570193.dbf /u01/arc/mgr/stdby/1_140_972570193.dbf /u01/arc/mgr/stdby/1_141_972570193.dbf /u01/arc/mgr/stdby/1_142_972570193.dbf /u01/arc/mgr/stdby/1_143_972570193.dbf /u01/arc/mgr/stdby/1_144_972570193.dbf /u01/arc/mgr/stdby/1_145_972570193.dbf
Stay tuned for More articles on Oracle DataGuard
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel: https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
