• Home
  • About
  •  

    Oracle Redo Operation 24.1

    Oracle Redo Operation 24.1

    The following post describes Oracle redo operation 24.1

    The post is based on research in single instance Oracle 11.2.0.3 running on Linux OEL5U6.

    This post is a by-product of research into GoldenGate DDL Support. I originally thought that the GoldenGate extract process would capture DDL changes from the 24.1 operation in the online redo logs. However, it appears that DDL is captured by a database trigger which fires every time a DDL statement is executed in the database.

    The information in this post has been used to enhance the RedoAnalyzer which is a tool I have developed for low-level performance investigation of the online redo logs.

    Logically redo consists of a set of redo records. Each redo record consists of one or more changes. A change generally affects a single block. A change consists of one or more elements (the latter is my terminology)

    A change consists of:

    • a change header
    • an array of element lengths
    • an array of elements

    There are two types of change:

    • Block changes
    • Media recovery record

    Block changes usually consist of undo or redo to be applied to a specific block. The change header consists of fixed data such as the block number, object number, SCN and operation code.

    Media recovery records contain additional information required when performing recovery. This information is used by various tools. The change header includes the SCN and operation code. DDL changes are recorded in a media recovery record.

    The change header is followed by an array of element lengths. The first slot in the array is a two byte value reporting the number of slots in the array. The remaining slots are each two bytes reporting the size of each element in the change.

    The contents of redo log can be dumped using the ALTER SYSTEM command. For example:

    ALTER SYSTEM DUMP LOGFILE '/u01/app/oradata/NORTH/redo03.log';
    

    DDL statements are recorded in changes with operation code 24.1.

    REDO RECORD - Thread:1 RBA: 0x000060.0000001f.0098 LEN: 0x0194 VLD: 0x01
    SCN: 0x0000.001e0c5f SUBSCN:  1 04/13/2013 00:09:57
    CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:24.1 ENC:0
    

    Unfortunately this is the only information available in this dump. The dump does not report the DDL statement or any other information. However, this information IS stored in the redo log.

    The example in this post based on the following DDL statement:

    CREATE TABLE t200
    (
      c1 NUMBER,
      c2 VARCHAR2(30),
      c3 DATE,
      c4 NUMBER
    );
    

    In Oracle 11.2, this DDL statement is represented in the redo log by a change of type 24.1 which has 25 elements (0 to 24). I have managed to identify the contents of the majority of these changes.

    For each element the following description includes a block dump. It should be noted that this research was performed on Linux which is little endian. In the block dumps, byte data is read from left to right; 16-bit and 32-bit numbers are read byte-wise right to left. Until the context is understood it is not possible to determine whether a field will consist of bytes or numbers. The reason I dump byte data left to right is that it is usually simpler to start decoding a block dump by starting with the ASCII values (strings). When the ASCII values have been eliminated it is easier to identify the remaining numeric values.

    Element 0

    This fixed length element contains 24 bytes

    10270000 06001700 27050000 01000000 00000100 01000000
    
    Bytes # Bytes Description Value
    0-3 4 Purpose unknown – always 10270000 0×10270000
    4-5 2 XID Undo Segment Number (USN) 0×0006
    6-7 2 XID Slot Number 0×0017
    8-11 4 XID Sequence Number 0×00000527
    12-13 2 Command 0×0001
    14-15 2 Unknown 0×0000
    16-17 2 Unknown 0×0000
    18-19 2 Unknown 0×0001
    20-23 4 Unknown 0×00000001

    The XID undo segment number, XID slot number and XID sequence number correspond to the XIDUSN, XIDSLOT and XIDSQN columns in V$TRANSACTION for the recursive transaction created for the DDL statement.

    The command is the internal action number of the DDL command. An almost complete list of commands can be found in the AUDIT_ACTIONS table.

    SQL> SELECT name FROM audit_actions WHERE action = 1;
    
    NAME
    ----------------------------
    CREATE TABLE
    

    Element 1

    This is a variable length element. It contains the login user name

    55533031
    

    In the above example the login user name is US01.

    Element 2

    This is a variable length element. It contains the current user name.

    55533033
    

    In the above example the current user name is US03.

    The current user name can be set using

    ALTER SESSION SET CURRENT_SCHEMA = US03;
    

    Element 3

    This fixed length element contains 12 bytes

    54000000 392D0100 0100470A
    
    Bytes # Bytes Description Value
    0-3 4 Login user id 0×00000054
    4-7 4 Object id 0x00012D39 (77113)
    8-9 2 Unknown 0×0001
    10-11 2 Unknown 0x0A47

    The user id can be determined from DBA_USERS. For example:

    SQL> SELECT user_id FROM dba_users WHERE username = 'US01';
    
       USER_ID
    ----------
            84
    

    The object id can be determined from DBA_OBJECTS. For example:

    SQL> SELECT object_id FROM dba_objects
      2  WHERE owner = 'US03'
      3  AND object_name = 'T200'
      4  AND object_type = 'TABLE';
    
     OBJECT_ID
    ----------
         77113
    

    Testing (using TRUNCATE) confirms that this value is not the data object id.

    Bytes 8-9 appear to be dependent on the command type. Examples observed so far include:

    Bytes 8-9 Command
    0001 CREATE TABLE
    0002 ALTER TABLE
    0001 DROP TABLE
    FFFF CREATE INDEX
    0000 DROP INDEX
    FFFF CREATE VIEW
    FFFF DROP VIEW
    0000 CREATE TRIGGER
    0000 ALTER TRIGGER
    0000 DROP TRIGGER

    Bytes 10-11 are always 0xA47 in examples observed so far.

    Element 4

    In this example this element is empty

    Element 5

    This fixed length element contains 2 bytes

    0000
    

    This element is the recursive depth. In this example the DDL statement is a top level call.

    Consider the following:

    DECLARE
      l_str VARCHAR2(100) := 'CREATE TABLE t201 (c1 NUMBER)';
    BEGIN
      EXECUTE IMMEDIATE l_str;
    END;
    /
    

    The recursive depth for the CREATE TABLE statement will be 1 as it is called from within a PL/SQL block.

    The depth is also reported in level 10046 trace as the dep value. For example:

    PARSING IN CURSOR #139724080042120 len=29 dep=1 uid=84 oct=1 lid=84 tim=1365819945533638 hv=1251380688 ad='919340f0' sqlid='b8rk1at59d3fh'
    CREATE TABLE t201 (c1 NUMBER)
    END OF STMT
    ...
    PARSE #139724080042120:c=0,e=356,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,
    tim=1365819945533637
    EXEC #139724080042120:c=65990,e=72822,p=0,cr=87,cu=51,mis=0,r=0,dep=1,og=1,
    plh=0,tim=1365819945606539
    CLOSE #139724080042120:c=0,e=2,dep=1,type=0,tim=1365819945606644
    

    In the above example the depth is 1 (dep=1)

    Element 6

    This fixed length element contains 2 bytes

    0200
    

    In this example the value of this element is 2 for a recursive depth of 0. If the recursive depth is increased to 1, then the value of this element will be 3.

    Note that this may be an oversimplification. Further testing is required.

    Element 7

    This is a variable length element

    43524541 54452054 41424C45 20743230 300A280A 20206331 204E554D 4245522C
    0A202063 32205641 52434841 52322833 30292C0A 20206333 20444154 452C0A20
    20633420 4E554D42 45520A29 00
    

    This element consists of a null-terminated string containing the DDL command. In this example:

    CREATE TABLE t200
    (
      c1 NUMBER,
      c2 VARCHAR2(30),
      c3 DATE,
      c4 NUMBER
    )
    

    Element 8

    This is a variable-length element

    55533033
    

    This element contains the owner (schema name) of the object that is being modified. In this example the schema name is US03.

    Element 9

    This is a variable-length element

    54323030
    

    This element contains the object name of the object that is being modified. In this example the object name is T200.

    Element 10

    This fixed length element contains 20 bytes

    00000000 01000000 00000000 01000100 00000200
    

    The contents of this element are not currently understood. They appear to be constant for a specific command type.

    Bytes # Bytes Description Value
    0-3 4 Unknown flags 0×00000000
    4-7 4 Command Type 0×00000001
    8-11 4 Unknown flags 0×00000000
    12-13 2 Unknown 0×0001
    14-15 2 Unknown 0×0001
    16-17 2 Unknown 0×0000
    18-19 2 Unknown 0×0002

    Bytes 0-3 – usually 0×00000000 – can be 0×00000003 if the DDL statement is executed within a PL/SQL block.

    Bytes 4-7 – The command is the internal action number of the DDL command. An almost complete list of commands can be found in the AUDIT_ACTIONS table:

    SQL> SELECT name FROM audit_actions WHERE action = 1;
    
    NAME
    ----------------------------
    CREATE TABLE
    

    Bytes 8-11 appear to be dependent on the command type. Examples observed so far include:

    Bytes 8-11 Command
    00000000 CREATE TABLE
    00000000 ALTER TABLE
    00000020 DROP TABLE
    00000000 CREATE INDEX
    00000000 DROP INDEX
    00008000 CREATE VIEW
    00008000 DROP VIEW
    00008000 CREATE TRIGGER
    00008000 ALTER TRIGGER
    00008000 DROP TRIGGER

    Bytes 12-15 appear to be dependent on the command type. Examples observed so far include:

    Bytes 12-15 Command
    00010001 CREATE TABLE
    00010001 ALTER TABLE
    00000001 DROP TABLE
    00000001 CREATE INDEX
    00000001 DROP INDEX
    00000001 CREATE VIEW
    00000000 DROP VIEW
    00000001 CREATE TRIGGER
    00000001 ALTER TRIGGER
    00000001 DROP TRIGGER

    Bytes 16-17 are also dependent on the command type. Examples observed so far:

    Bytes 16-17 Command
    0000 CREATE TABLE
    0000 ALTER TABLE
    0000 DROP TABLE
    0000 CREATE INDEX
    0000 DROP INDEX
    FFFF CREATE VIEW
    FFFF DROP VIEW
    0000 CREATE TRIGGER
    0000 ALTER TRIGGER
    0000 DROP TRIGGER

    Bytes 18-19 are also dependent on the command type. Examples observed so far:

    Bytes 18-19 Command
    0002 CREATE TABLE
    0002 ALTER TABLE
    0002 DROP TABLE
    0001 CREATE INDEX
    0001 DROP INDEX
    B404 CREATE VIEW
    B404 DROP VIEW
    850C CREATE TRIGGER
    850C ALTER TRIGGER
    850C DROP TRIGGER

    Element 11

    This fixed length element contains 4 bytes

    392D0100
    

    0x00012d39 = 77113

    The object id can be determined from DBA_OBJECTS. For example:

    SQL> SELECT object_id FROM dba_objects
      2  WHERE owner = 'US03'
      3  AND object_name = 'T200'
      4  AND object_type = 'TABLE';
    
     OBJECT_ID
    ----------
         77113
    

    Testing (using TRUNCATE) confirms that this value is not the data object id.

    The element can be empty for some command types.

    Element 12

    This fixed length element contains 2 bytes

    0100
    

    The significance of this column is not known. So far the only observed value is 0×01 (1)

    The element can be empty for some command types.

    Element 13

    This fixed length element contains 2 bytes

    0000
    

    The significance of this column is not known. So far the only observed value is 0×00 (0)

    For synonyms this element is empty.

    Element 14

    This is a variable length element. In this example this element is empty

    For some command types this element is the edition, for example ORA$BASE which is the default edition.

    Bytes 16-17 Command
    NULL CREATE TABLE
    NULL ALTER TABLE
    NULL DROP TABLE
    NULL CREATE INDEX
    NULL DROP INDEX
    ORA$BASE CREATE VIEW
    ORA$BASE CREATE SYNONYM

    Element 15

    This is a variable-length element

    2E2C
    

    This is the value for the NLS_NUMERIC_CHARACTERS parameter. In this example:

    .,
    

    Element 16

    This is a variable-length element

    44442D4D 4F4E2D52 52
    

    This is the value for the NLS_DATE_FORMAT parameter. In this example:

    DD-MON-RR
    

    Element 17

    This is a variable-length element

    44442D4D 4F4E2D52 52204848 2E4D492E 53535846 4620414D
    

    This is the value for the NLS_TIMESTAMP_FORMAT parameter. In this example:

    DD-MON-RR HH.MI.SSXFF AM
    

    Element 18

    This is a variable-length element

    48482E4D 492E5353 58464620 414D
    

    This is the value for the NLS_TIME_FORMAT parameter. In this example:

    HH.MI.SSXFF AM
    

    Element 19

    This is a variable-length element

    48482E4D 492E5353 58464620 414D2054 5A52
    

    This is the value for the NLS_TIME_TZ_FORMAT parameter. In this example:

    HH.MI.SSXFF AM TZR
    

    Element 20

    This is a variable-length element

    44442D4D 4F4E2D52 52204848 2E4D492E 53535846 4620414D 20545A52
    

    This is the value for the NLS_TIMESTAMP_TZ_FORMAT parameter. In this example:

    DD-MON-RR HH.MI.SSXFF AM TZR
    

    Element 21

    This is a variable-length element

    454E474C 495348
    

    This is the value for the NLS _DATE_LANGUAGE parameter. In this example:

    ENGLISH
    

    Element 22

    This is a variable-length element

    414D4552 4943414E
    

    This is the value for the NLS_LANGUAGE parameter. In this example:

    AMERICAN
    

    Element 23

    This is a variable-length element

    47524547 4F524941 4E
    

    This is the value for the NLS_CALENDAR parameter. In this example:

    GREGORIAN
    

    Element 24

    In this example this element is empty

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    *

    HTML tags are not allowed.