Redo log is crucial for recovery. An Oracle database consists of two or more redo log files. If a database is in archive mode, Oracle database writes changes (change vectors, a.k.a redo entries) to one of redo log files while others are being archived.
Redo records are buffered in a circular fashion in the redo log buffer of the SGA and are written to one of the redo log files by the Log Writer (LGWR) database background process. Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file, and assigns a system change number (SCN) to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.
When configuring redo logs for a database, one consideration is to multiplex redo log files in each redo log group and put them on different disks to prevent single point failures. Other considerations could be the size of redo log files, block size of redo log files, number of redo log file groups, archive lag. And one last thing is the performance of disks where redo log files reside. You want fast disks with good write performance. For this reason, usually you will have dedicated locations (disks) for redo log files.
Here you may see a problem when creating a Phsycial Standby database on a different server which has identical disk layouts with RMAN duplicate command — after the duplication, all other files seem to be fine regarding their names and locations except for redo log files and standby redo log files. They are created under the fast recovery area with OMF file names!
How come? Well here is the reason — duplicate will always re-create redo log files and standby redo log files. And because DB_RECOVERY_FILE_DEST is defined on the Primary server, redo and standby redo log files are created as OMF despite NOFILENAMECHECK is used. And they are not multiplexed as you have on the Primary database!
SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ CON_ID ---------- ------- ------- ------------------------------------------------------------ --- ---------- 1 ONLINE /backup/JLIPROD2/onlinelog/o1_mf_1_d44q3bf4_.log YES 0 2 ONLINE /backup/JLIPROD2/onlinelog/o1_mf_2_d44q3dbs_.log YES 0 3 ONLINE /backup/JLIPROD2/onlinelog/o1_mf_3_d44q3fyw_.log YES 0 4 ONLINE /backup/JLIPROD2/onlinelog/o1_mf_4_d44q3hs4_.log YES 0 5 STANDBY /backup/JLIPROD2/onlinelog/o1_mf_5_d44q3knr_.log YES 0 6 STANDBY /backup/JLIPROD2/onlinelog/o1_mf_6_d44q3m7p_.log YES 0 7 STANDBY /backup/JLIPROD2/onlinelog/o1_mf_7_d44q3o21_.log YES 0 8 STANDBY /backup/JLIPROD2/onlinelog/o1_mf_8_d44q3pst_.log YES 0 9 STANDBY /backup/JLIPROD2/onlinelog/o1_mf_9_d44q3rd1_.log YES 0 9 rows selected.
So far I haven’t found duplicate options that can prevent this. No matter using SET, or LOG_FILE_NAME_CONVERT of SPFILE. There is an option — LOGFILE, but it’s for creating a duplicate database that is not a standby database
So to correct this, you need to recreate redo log and standby redo log files on the newly created standby server — basically dropping the OMF ones and creating them under the correct locations with correct file names.
- cancel recovery on standby
- change standby_file_management from “AUTO” to “MANUAL”
- drop existing groups
- create new ones.
One thing to note, with Oracle 11g, you may see a redo log file group showing as CURRENT which is weird because redo log files should not be used. In that case, you will have trouble dropping it because Oracle gives the ORA-01624 error and the workaround is to recreate standby control files. Oracle 12c & 18c have no such issue though. Please see following Oracle Support articles for this issue:
Online Redo Logs on Physical Standby (Doc ID 740675.1)
BUG:17286325 – UNABLE TO DROP ONLINE REDO LOGFILE ON PHYSICAL STANDBY