However, an odd behaviour was observed in a data guard configuration where even when OMF is used there database was looking for LOG_FILE_NAME_CONVERT and if not set stopping the automatic logfile clearing.
The databases (both primary and standby) had following OMF related parameters
NAME TYPE VALUE ------------------------------------ ----------- ------- db_create_file_dest string +DATA db_create_online_log_dest_1 string db_create_online_log_dest_2 string db_create_online_log_dest_3 string db_create_online_log_dest_4 string db_create_online_log_dest_5 string db_recovery_file_dest string +FRAThe *convert* parameters were not set.
NAME TYPE VALUE ------------------------------------ ----------- ------------ db_file_name_convert string log_file_name_convert string pdb_file_name_convert stringDuring the active database duplication the duplication processed detected use of OMF as seen from below output.
...
sql statement: alter system set local_listener = ''LISTENER_DGTEST2'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 9965665616 bytes
Fixed Size 12684624 bytes
Variable Size 1543503872 bytes
Database Buffers 8388608000 bytes
Redo Buffers 20869120 bytes
duplicating Online logs to Oracle Managed File (OMF) location
duplicating Datafiles to Oracle Managed File (OMF) location
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/DGTEST2/CONTROLFILE/current.264.1067949389'', ''+FRA/DGTEST2/CONTROLFILE/current.418.1067949389'' comment=
...Data guard consists of two databases.DGMGRL>Configuration - test_dg
Protection Mode: MaxAvailability
Members:
dgtest - Primary database
dgtest2 - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESSWith these setting on, after a switchover following was seen on the alert log of the old primary (new standby)2021-03-23T12:55:20.314959+00:00 TT02 (PID:8514): LOG_FILE_NAME_CONVERT is not defined, stop clearing ORLsValidate of the old primary (new standby) showed that online redo logs are not cleared
DGMGRL> validate database dgtest
Database Role: Physical standby database
Primary Database: dgtest2
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Managed by Clusterware:
dgtest2: YES
dgtest : YES
Log Files Cleared:
dgtest2 Standby Redo Log Files: Cleared
dgtest Online Redo Log Files: Not Cleared
dgtest Standby Redo Log Files: AvailableIt was the same case when the new primary (old standby) did a switchover as well.2021-03-23T13:02:25.800263+00:00
TT02 (PID:9657): LOG_FILE_NAME_CONVERT is not defined, stop clearing ORLs
DGMGRL> validate database dgtest2
Database Role: Physical standby database
Primary Database: dgtest
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Managed by Clusterware:
dgtest : YES
dgtest2: YES
Log Files Cleared:
dgtest Standby Redo Log Files: Cleared
dgtest2 Online Redo Log Files: Not Cleared
dgtest2 Standby Redo Log Files: Available
This behaviour was observed even after applying release updates (RU) 19.3, 19.4, 19.5, 19.6 and 19.7. If the databases are in one of those RUs then only way to achieve the automatic ORL clearing is to explicitly set the log file name convert parameter. For example in this case for the primary DB dgtest
alter system set log_file_name_convert='/dgtest2/','/dgtest/' scope=spfile;Once log_file_name_convert is set the automatic ORL clearence was observed even though MOS doc states log_file_name_convert cannot be used with OMF.
The issue is fixed on RU 19.8 and up (tested on 19.9 , 19.10). Once these RU are applied automatic ORL clearing happens as expected. If previously (in one of the affected RUs) log_file_name_convert was set then it could be removed once RU 19.8 or up is applied.
When automatic ORL clearing happens following could be seen on the alert log of the old primary
2021-03-23T13:20:23.135283+00:00 TT02 (PID:10641): Waiting for all non-current ORLs to be archived 2021-03-23T13:20:23.135414+00:00 TT02 (PID:10641): All non-current ORLs have been archived 2021-03-23T13:20:23.273380+00:00 TT02 (PID:10641): Clearing ORL LNO:1 +DATA/DGTEST/ONLINELOG/group_1.264.1067878075 2021-03-23T13:20:24.512745+00:00 TT02 (PID:10641): Clearing ORL LNO:1 complete TT02 (PID:10641): Clearing ORL LNO:2 +DATA/DGTEST/ONLINELOG/group_2.262.1067878077 Clearing online log 2 of thread 1 sequence number 34 2021-03-23T13:20:26.525504+00:00 TT02 (PID:10641): Clearing ORL LNO:2 complete TT02 (PID:10641): Clearing ORL LNO:3 +DATA/DGTEST/ONLINELOG/group_3.261.1067878079 Clearing online log 3 of thread 1 sequence number 35 2021-03-23T13:20:28.163683+00:00 TT02 (PID:10641): Clearing ORL LNO:3 complete 2021-03-23T13:20:33.174192+00:00 TT02 (PID:10641): Waiting for all non-current ORLs to be archived 2021-03-23T13:20:33.174386+00:00 TT02 (PID:10641): All non-current ORLs have been archivedValidating the standby shows no log files to be cleared.
DGMGRL> validate database dgtest2
Database Role: Physical standby database
Primary Database: dgtest
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Managed by Clusterware:
dgtest : YES
dgtest2: YESUseful Metalink notes
Usage and Limitation of db_file_name_convert and log_file_name_convert [ID 1367014.1]
