Skip to Main Content
  • Questions

  • Create table statement and create index sequence in redo logs / archive logs

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, Bobby.

Asked: December 04, 2024 - 8:09 am UTC

Last updated: December 11, 2024 - 6:10 am UTC

Version: 11.1.7

Viewed 100+ times

You Asked

Hi Tom,

First of thank for your great work about helping so many people with brilliant explanations example.

My question is, when I am creating a table with primary keys, and then regenerating the redo data/statements information using log miner ordered by SCN, I see that oracle is creating unique index on table “test1” first and then creates a table “test1”.

Why and how oracle is doing that? If I would play these statement on some other database in same the sequence ordered by SCN, it has failed as it is trying to create an unique index on a table which does not exist.

Any help would be highly appreciated.

Environment is Oracle 11.1.7 on AIX.
Many thanks in advance.
Bobby




and Connor said...

Dude ... 11.1.7 ? Cmon.... :-)


Anyway, You can think of the DDL you see in the log miner trail as the "conclusion" of the DDL. For example:

SQL> create table zzz ( x int primary key, y int );

Table created.

SQL> select * from user_objects where object_name = 'ZZZ'
  2  @pr
==============================
OBJECT_NAME                   : ZZZ
SUBOBJECT_NAME                :
OBJECT_ID                     : 1437544
DATA_OBJECT_ID                : 1437544
OBJECT_TYPE                   : TABLE
CREATED                       : 09-DEC-2024 11:48:50
LAST_DDL_TIME                 : 09-DEC-2024 11:48:50
TIMESTAMP                     : 2024-12-09:11:48:50
STATUS                        : VALID
[snip]
PL/SQL procedure successfully completed.


SQL> select * from user_objects where object_name = 'SYS_C0012819'
  2  @pr
==============================
OBJECT_NAME                   : SYS_C0012819
SUBOBJECT_NAME                :
OBJECT_ID                     : 1437545
DATA_OBJECT_ID                : 1437545
OBJECT_TYPE                   : INDEX
CREATED                       : 09-DEC-2024 11:48:50
LAST_DDL_TIME                 : 09-DEC-2024 11:48:50
[snip]

PL/SQL procedure successfully completed.


Lets look at what was in LogMiner for this table

SQL> SELECT scn, operation, sql_redo
  2  FROM   v$logmnr_contents
  
       SCN OPERATION                        SQL_REDO
---------- -------------------------------- ------------------------------------------------------------  
1.6331E+13 COMMIT                           commit;
1.6331E+13 START                            set transaction read write;
1.6331E+13
1.6331E+13 INSERT                           insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","
                                            NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS
                                            ","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2",
                                            "SPARE3","SPARE4","SPARE5","SPARE6","SIGNATURE","SPARE7","SP
                                            ARE8","SPARE9","DFLCOLLID","CREAPPID","CREVERID","CREPATCHID
                                            ","MODAPPID","MODVERID","MODPATCHID","SPARE10","SPARE11","SP
                                            ARE12","SPARE13","SPARE14") values ('1437544','1437544','108
                                            ','ZZZ','1',NULL,'2',TO_DATE('09-DEC-24', 'DD-MON-RR'),TO_DA
                                            TE('09-DEC-24', 'DD-MON-RR'),TO_DATE('09-DEC-24', 'DD-MON-RR
                                            '),'1',NULL,NULL,'0',NULL,'6','1','108',NULL,NULL,NULL,NULL,
                                            '0','0','0','16382',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
                                            NULL,NULL,NULL);

1.6331E+13 UPDATE                           update "SYS"."CON$" set "CON#" = '12824', "SPARE1" = '0' whe
                                            re "CON#" = '12819' and "SPARE1" = '0' and ROWID = 'AAAAAcAA
                                            BAAAAEhAAz';

1.6331E+13 START                            set transaction read write;
1.6331E+13 INSERT                           insert into "SYS"."DEFERRED_STG$"("OBJ#","PCTFREE_STG","PCTU
                                            SED_STG","SIZE_STG","INITIAL_STG","NEXT_STG","MINEXT_STG","M
                                            AXEXT_STG","MAXSIZ_STG","LOBRET_STG","MINTIM_STG","PCTINC_ST
                                            G","INITRA_STG","MAXTRA_STG","OPTIMAL_STG","MAXINS_STG","FRL
                                            INS_STG","FLAGS_STG","BFP_STG","ENC_STG","CMPFLAG_STG","CMPL
                                            VL_STG","IMCFLAG_STG","CCFLAG_STG","FLAGS2_STG") values ('14
                                            37544',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL
                                            L,NULL,NULL,NULL,NULL,NULL,'8',NULL,NULL,'0','0',NULL,NULL,'
                                            0');

1.6331E+13 COMMIT                           commit;
1.6331E+13 INSERT                           insert into "SYS"."CON$"("OWNER#","NAME","CON#","SPARE1","SP
                                            ARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('108','SY
                                            S_C0012819','12819','0',NULL,NULL,NULL,NULL,NULL);

1.6331E+13 INSERT                           insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","
                                            NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS
                                            ","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2",
                                            "SPARE3","SPARE4","SPARE5","SPARE6","SIGNATURE","SPARE7","SP
                                            ARE8","SPARE9","DFLCOLLID","CREAPPID","CREVERID","CREPATCHID
                                            ","MODAPPID","MODVERID","MODPATCHID","SPARE10","SPARE11","SP
                                            ARE12","SPARE13","SPARE14") values ('1437545','1437545','108
                                            ','SYS_C0012819','4',NULL,'1',TO_DATE('09-DEC-24', 'DD-MON-R
                                            R'),TO_DATE('09-DEC-24', 'DD-MON-RR'),TO_DATE('09-DEC-24', '
                                            DD-MON-RR'),'1',NULL,NULL,'4',NULL,'0','65535','108',NULL,NU
                                            LL,NULL,NULL,'0','0','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,
                                            NULL,NULL,NULL,NULL,NULL);

1.6331E+13 INSERT                           insert into "SYS"."DEFERRED_STG$"("OBJ#","PCTFREE_STG","PCTU
                                            SED_STG","SIZE_STG","INITIAL_STG","NEXT_STG","MINEXT_STG","M
                                            AXEXT_STG","MAXSIZ_STG","LOBRET_STG","MINTIM_STG","PCTINC_ST
                                            G","INITRA_STG","MAXTRA_STG","OPTIMAL_STG","MAXINS_STG","FRL
                                            INS_STG","FLAGS_STG","BFP_STG","ENC_STG","CMPFLAG_STG","CMPL
                                            VL_STG","IMCFLAG_STG","CCFLAG_STG","FLAGS2_STG") values ('14
                                            37545','10',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL
                                            L,'2','255',NULL,NULL,NULL,'0',NULL,NULL,'0','0',NULL,NULL,'
                                            0');

1.6331E+13 DDL                              CREATE UNIQUE INDEX "MCDONAC"."SYS_C0012819" on "MCDONAC"."Z
                                            ZZ"("X") NOPARALLEL;

1.6331E+13 START                            set transaction read write;
1.6331E+13 INTERNAL
1.6331E+13 COMMIT                           commit;
1.6331E+13 START                            set transaction read write;
1.6331E+13 COMMIT                           commit;
1.6331E+13 INSERT                           insert into "SYS"."ICOL$"("OBJ#","BO#","COL#","POS#","SEGCOL
                                            #","SEGCOLLENGTH","OFFSET","INTCOL#","SPARE1","SPARE2","SPAR
                                            E3","SPARE4","SPARE5","SPARE6") values ('1437545','1437544',
                                            '1','1','0','0','0','1','0','0','0',NULL,NULL,NULL);

1.6331E+13 INSERT                           insert into "SYS"."IND$"("OBJ#","DATAOBJ#","TS#","FILE#","BL
                                            OCK#","BO#","INDMETHOD#","COLS","PCTFREE$","INITRANS","MAXTR
                                            ANS","PCTTHRES$","TYPE#","FLAGS","PROPERTY","BLEVEL","LEAFCN
                                            T","DISTKEY","LBLKKEY","DBLKKEY","CLUFAC","ANALYZETIME","SAM
                                            PLESIZE","ROWCNT","INTCOLS","DEGREE","INSTANCES","TRUNCCNT",
                                            "EVALEDITION#","UNUSABLEBEFORE#","UNUSABLEBEGINNING#","SPARE
                                            1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('14
                                            37545','1437545','4','0','0','1437544','0','1','10','2','255
                                            ',NULL,'1','67108864','4097',NULL,NULL,NULL,NULL,NULL,NULL,N
                                            ULL,NULL,NULL,'1',NULL,NULL,NULL,'0',NULL,NULL,'1',NULL,NULL
                                            ,NULL,NULL,TO_DATE('09-DEC-24', 'DD-MON-RR'));

1.6331E+13 INSERT                           insert into "SYS"."TAB$"("OBJ#","DATAOBJ#","TS#","FILE#","BL
                                            OCK#","BOBJ#","TAB#","COLS","CLUCOLS","PCTFREE$","PCTUSED$",
                                            "INITRANS","MAXTRANS","FLAGS","AUDIT$","ROWCNT","BLKCNT","EM
                                            PCNT","AVGSPC","CHNCNT","AVGRLN","AVGSPC_FLB","FLBCNT","ANAL
                                            YZETIME","SAMPLESIZE","DEGREE","INSTANCES","INTCOLS","KERNEL
                                            COLS","PROPERTY","TRIGFLAG","SPARE1","SPARE2","SPARE3","SPAR
                                            E4","SPARE5","SPARE6","SPARE7","SPARE8","SPARE9","SPARE10","
                                            ACDRFLAGS","ACDRTSOBJ#","ACDRDEFAULTTIME","ACDRROWTSINTCOL#"
                                            ) values ('1437544','1437544','4','0','0',NULL,NULL,'2',NULL
                                            ,'10','40','1','255','1073741825','-------------------------
                                            -------------',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
                                            NULL,NULL,NULL,'2','2','17716740096','0','736',NULL,NULL,NUL
                                            L,NULL,TO_DATE('09-DEC-24', 'DD-MON-RR'),NULL,NULL,NULL,NULL
                                            ,'0','0',NULL,'0');

1.6331E+13 INTERNAL
1.6331E+13 START                            set transaction read write;
1.6331E+13 INSERT                           insert into "SYS"."COL$"("OBJ#","COL#","SEGCOL#","SEGCOLLENG
                                            TH","OFFSET","NAME","TYPE#","LENGTH","FIXEDSTORAGE","PRECISI
                                            ON#","SCALE","NULL$","DEFLENGTH","DEFAULT$","INTCOL#","PROPE
                                            RTY","CHARSETID","CHARSETFORM","EVALEDITION#","UNUSABLEBEFOR
                                            E#","UNUSABLEBEGINNING#","SPARE1","SPARE2","SPARE3","SPARE4"
                                            ,"SPARE5","SPARE6","SPARE7","SPARE8","COLLID","COLLINTCOL#",
                                            "SPARE9","SPARE10","SPARE11","SPARE12","SPARE13","ACDRRESCOL
                                            #") values ('1437544','2','2','22','0','Y','2','22','0',NULL
                                            ,'0','0',NULL,NULL,'2','0','0','0','0',NULL,NULL,'0','0','0'
                                            ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0','0',NULL,NULL,NULL,N
                                            ULL);

1.6331E+13 INSERT                           insert into "SYS"."COL$"("OBJ#","COL#","SEGCOL#","SEGCOLLENG
                                            TH","OFFSET","NAME","TYPE#","LENGTH","FIXEDSTORAGE","PRECISI
                                            ON#","SCALE","NULL$","DEFLENGTH","DEFAULT$","INTCOL#","PROPE
                                            RTY","CHARSETID","CHARSETFORM","EVALEDITION#","UNUSABLEBEFOR
                                            E#","UNUSABLEBEGINNING#","SPARE1","SPARE2","SPARE3","SPARE4"
                                            ,"SPARE5","SPARE6","SPARE7","SPARE8","COLLID","COLLINTCOL#",
                                            "SPARE9","SPARE10","SPARE11","SPARE12","SPARE13","ACDRRESCOL
                                            #") values ('1437544','1','1','22','0','X','2','22','0',NULL
                                            ,'0','1',NULL,NULL,'1','0','0','0','0',NULL,NULL,'0','0','0'
                                            ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0','0',NULL,NULL,NULL,N
                                            ULL);

1.6331E+13 COMMIT                           commit;
1.6331E+13 START                            set transaction read write;
1.6331E+13 COMMIT                           commit;
1.6331E+13 INSERT                           insert into "SYS"."CCOL$"("CON#","OBJ#","COL#","POS#","INTCO
                                            L#","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") v
                                            alues ('12819','1437544','1','1','1','0',NULL,NULL,NULL,NULL
                                            ,NULL);

1.6331E+13 INSERT                           insert into "SYS"."CDEF$"("CON#","OBJ#","COLS","TYPE#","ROBJ
                                            #","RCON#","RRULES","MATCH#","REFACT","ENABLED","CONDLENGTH"
                                            ,"CONDITION","INTCOLS","MTIME","DEFER","SPARE1","SPARE2","SP
                                            ARE3","SPARE4","SPARE5","SPARE6") values ('12819','1437544',
                                            '1','2',NULL,NULL,NULL,NULL,NULL,'1437545',NULL,NULL,'1',TO_
                                            DATE('09-DEC-24', 'DD-MON-RR'),'12','6','3802','1686276887',
                                            NULL,NULL,NULL);

1.6331E+13 DDL                              create table zzz ( x int primary key, y int );
1.6331E+13 UPDATE                           update "SYS"."OBJ$" set "OBJ#" = '1437544', "DATAOBJ#" = '14
                                            37544', "TYPE#" = '2', "CTIME" = TO_DATE('09-DEC-24', 'DD-MO
                                            N-RR'), "MTIME" = TO_DATE('09-DEC-24', 'DD-MON-RR'), "STIME"
                                             = TO_DATE('09-DEC-24', 'DD-MON-RR'), "STATUS" = '1', "FLAGS
                                            " = '0', "OID$" = NULL, "SPARE1" = '6', "SPARE2" = '1', "SPA
                                            RE3" = '108', "SIGNATURE" = HEXTORAW('74c53bcd3dcb2bb79993b2
                                            fec37d362a'), "SPARE7" = '0', "SPARE8" = '0', "SPARE9" = '0'
                                            , "DFLCOLLID" = '16382' where "OBJ#" = '1437544' and "DATAOB
                                            J#" = '1437544' and "TYPE#" = '2' and "CTIME" = TO_DATE('09-
                                            DEC-24', 'DD-MON-RR') and "MTIME" = TO_DATE('09-DEC-24', 'DD
                                            -MON-RR') and "STIME" = TO_DATE('09-DEC-24', 'DD-MON-RR') an
                                            d "STATUS" = '1' and "FLAGS" = '0' and "OID$" IS NULL and "S
                                            PARE1" = '6' and "SPARE2" = '1' and "SPARE3" = '108' and "SI
                                            GNATURE" IS NULL and "SPARE7" = '0' and "SPARE8" = '0' and "
                                            SPARE9" = '0' and "DFLCOLLID" = '16382' and ROWID = 'AAAAASA
                                            ABAAA01tAAS';

1.6331E+13 COMMIT                           commit;


You can see first insert into OBJ$ for the table definition. So the table now "exists" but the DDL has not finished yet. Now we can do inserts to create the index definition.

At this point, the index DDL is completed, and thus appears in our trial. Once this (and any other subordinate things are completed - check constraints etc etc) then the table DDL is completed, and the "create table" can appear because it was successful.

Rating

  (3 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

V$LOGMNR_CONTENTS.INFO=USER DDL%

Mikhail Velikikh, December 09, 2024 - 11:36 am UTC

> If I would play these statement on some other database in same the sequence ordered by SCN, it has failed as it is trying to create an unique index on a table which does not exist.

Bobby, there are USER DDL and INTERNAL DDL statements. If you want to replay DDL statements, you are interested in the former, which are top-level DDLs.

SQL> select scn, sql_redo, info from v$logmnr_contents where operation='DDL';

       SCN SQL_REDO                                                                         INFO
---------- -------------------------------------------------------------------------------- --------------------------------
   1614967 CREATE UNIQUE INDEX "TC"."SYS_C007715" on "TC"."ZZZ"("X") NOPARALLEL;            INTERNAL DDL (PlSql=0 RecDep=1)
   1614981 create table zzz ( x int primary key, y int );                                   USER DDL (PlSql=0 RecDep=0)


Thanks,
Mikhail
Connor McDonald
December 09, 2024 - 1:44 pm UTC

Agreed.

But if you want to reinvent Goldengate...that's a slippery slope down a deep deep rabbit hole

Bobby, December 10, 2024 - 6:50 am UTC

Dear Connor,

Thank you for taking time and your efforts for showing a working example, really appreciate.

Oracle 11.1.7 :).. lol lol exactly was my reaction when I joined the client and totally with you on that!!!

Now the client is migrating to 19c. As a part of the project data need to migrating but the challenge is that new 19c database needs to be kept in sync with no direct connectivity between source and target, hence no other technology like Goldengate was an option.

Coming back to the question, thus opted for log mining and need to replay those transactions on target. Replaying them on target in sequence as were generated on source ordered by SCN.

I can see from your example, the table is being created and being added into dictionary and then index but while I am extracting user transactions (ignoring SYS owned), I see index creation first and then table as per SCN order.

Any suggestion to deal with this scenario so that target receives create table first and then index. (As a work around, tested with table creation without primary key and add primary keys as an alter table and that resolves the challenge so far).

Thank you again.
Connor McDonald
December 11, 2024 - 6:10 am UTC

as per Mikhail, you just need to top level DDL... but I stress

But if you want to reinvent Goldengate...that's a slippery slope down a deep deep rabbit hole

Bobby, December 10, 2024 - 6:53 am UTC

Mikhail ,

Yes that's exactly I am experiencing and became a challenge.

Thanks

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library