Dbvisit Replicate and Oracle initialization parameter COMPATIBLE in 11gR2

Owned by Clive Otten (Unlicensed)
Jan 22, 2020
2 min read

Problem Description

Dbvisit Replicate reads (mines) Oracle redo logs in real time at the source database, converts the transactions to SQL commands and executes them (applies) them at the target database. MINE process is performing Oracle version detection while opening redo log. We found a Oracle bug 9549707 (11g with COMPATIBLE=10.2 produces redo which is not 10.2 compatible) causing abort of MINE process.

Let's look at this in more detail.

Solution

COMPATIBILITY parameter

The COMPATIBLE initialization parameter enables or disables the use of features in the database that affect file format on disk. For example, if you create an Oracle Database 11g Release 2 (11.2) database, but specify COMPATIBLE=10.0.0 in the initialization parameter file, then features that requires 11.2 compatibility generate an error if you try to use them. Such a database is said to be at the 10.0.0 compatibility level.

Redo log header

There is a difference between 9i and 10g+ redo log file header. Bug 9549707 creates the redo header in 9i format which is completely wrong. This header is fixed later by ARCH process while creating archive log from redo log. This is most likely not a problem for Oracle database instance but it is for Dbvisit Replicate as MINE is reading transactions directly from redo logs.

A 10g+ header should look like:

oracle@dbvlin216[/home/oracle]: hexdump -C /u01/app/oracle/oradata/SRCDB/redo03.log | head -4 00000000  00 22 00 00 00 00 c0 ff  00 00 00 00 00 00 00 00  |."..............| 00000010  67 c8 00 00 00 02 00 00  00 90 01 00 7d 7c 7b 7a  |g...........}|{z| 00000020  a0 81 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................| 00000030  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|

You can see 10g magic number 0x7a7b7c7d and file type set to 0x22

A 9i header looks like:

oracle@dbvlin216[/home/oracle]: hexdump -C BADREDO01A.LOG | head -4 00000000  00 00 00 00 00 02 00 00  00 c0 00 00 6d 6c 6b 6a  |............mlkj| 00000010  06 c4 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................| 00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|

9i magic number (6a6b6c6d) and the filetype is not set in the header.

Solution

Affected Oracle versions are >= 11.1 but below 12.1

This issue is fixed by Oracle in:

  • 11.2.0.2 patchset

  • 12.1.0.1 patchset

But only for COMPATIBLE parameter set to 10.2.

If you set COMPATIBLE to 10.1 or 10.0, you may still encounter this issue.

We improved version detection to avoid this bug in next release (2.7.18) of Dbvisit Replicate.

References: