LogMiner supports redo generated for
XMLType data stored as
CLOB is supported when redo is generated at a compatibility setting of 18.104.22.168 or higher.
XMLType data stored as object-relational and binary XML is supported for redo generated at a compatibility setting of 22.214.171.124 and higher.
LogMiner presents the
V$LOGMNR_CONTENTS in different ways depending on the
XMLType storage. In all cases, the contents of the
SQL_REDO column, in combination with the
STATUS column, require careful scrutiny, and usually require reassembly before a SQL or PL/SQL statement can be generated to redo the change. There may be cases when it is not possible to use the
SQL_REDO data to construct such a change. The examples in the following subsections are based on
XMLType stored as
CLOB which is generally the simplest to use for reconstruction of the complete row change.
XMLType data stored as
CLOB is deprecated as of Oracle Database 12c Release 1 (12.1).
Querying V$LOGMNR_CONTENTS For Changes to Tables With XMLType Columns
The example in this section is for a table named
XML_CLOB_COL_TAB that has the following columns:
Assume that a LogMiner session has been started with the logs and with the
COMMITED_DATA_ONLY option. The following query is executed against
V$LOGMNR_CONTENTS for changes to the
SELECT OPERATION, STATUS, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = 'SCOTT' AND TABLE_NAME = 'XML_CLOB_COL_TAB';
The query output looks similar to the following:
OPERATION STATUS SQL_REDO INSERT 0 insert into "SCOTT"."XML_CLOB_COL_TAB"("F1","F2","F5") values ('5010','Aho40431','PETER') XML DOC BEGIN 5 update "SCOTT"."XML_CLOB_COL_TAB" a set a."F3" = XMLType(:1) where a."F1" = '5010' and a."F2" = 'Aho40431' and a."F5" = 'PETER' XML DOC WRITE 5 XML Data XML DOC WRITE 5 XML Data XML DOC WRITE 5 XML Data XML DOC END 5
SQL_REDO columns for the
XML DOC WRITE operations there will be actual data for the XML document. It will not be the string 'XML Data'.
This output shows that the general model for an insert into a table with an
XMLType column is the following:
An initial insert with all of the scalar columns.
XML DOC BEGIN operation with an update statement that sets the value for one
XMLType column using a bind variable.
One or more
XML DOC WRITE operations with the data for the XML document.
XML DOC END operation to indicate that all of the data for that XML document has been seen.
If there is more than one
XMLType column in the table, then steps 2 through 4 will be repeated for each
XMLType column that is modified by the original DML.
If the XML document is not stored as an out-of-line column, then there will be no
XML DOC BEGIN,
XML DOC WRITE, or
XML DOC END operations for that column. The document will be included in an update statement similar to the following:
OPERATION STATUS SQL_REDO UPDATE 0 update "SCOTT"."XML_CLOB_COL_TAB" a set a."F3" = XMLType('<?xml version="1.0"?> <PO pono="1"> <PNAME>Po_99</PNAME> <CUSTNAME>Dave Davids</CUSTNAME> </PO>') where a."F1" = '5006' and a."F2" = 'Janosik' and a."F5" = 'MMM'
Querying V$LOGMNR_CONTENTS For Changes to XMLType Tables
XMLType tables are slightly different from DMLs to
XMLType columns. The XML document represents the value for the row in the
XMLType table. Unlike the
XMLType column case, an initial insert cannot be done which is then followed by an update containing the XML document. Rather, the whole document must be assembled before anything can be inserted into the table.
Another difference for
XMLType tables is the presence of the
OBJECT_ID column. An object identifier is used to uniquely identify every object in an object table. For
XMLType tables, this value is generated by Oracle Database when the row is inserted into the table. The
OBJECT_ID value cannot be directly inserted into the table using SQL. Therefore, LogMiner cannot generate
SQL_REDO which is executable that includes this value.
V$LOGMNR_CONTENTS view has a new
OBJECT_ID column which is populated for changes to
XMLType tables. This value is the object identifier from the original table. However, even if this same XML document is inserted into the same
XMLType table, a new object identifier will be generated. The
SQL_REDO for subsequent DMLs, such as updates and deletes, on the
XMLType table will include the object identifier in the
WHERE clause to uniquely identify the row from the original table.