[20230510]测试使用tpt ddl脚本是否产生日志.txt

[20230510]测试使用tpt ddl脚本是否产生日志.txt --//发现在19c的tpt ddl脚本无法使用在dg环境下使用,但是在11g的dg下可以,先看看在正常数据库11g下是否产生日志: 1.环境: SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试: --//建立脚本: $ cat dumpredo3.sql column member new_value v_member column member noprint set numw 12 --//pause alter system switch logfile ; --//pause alter system archive log current; --//12c不允许在pluggable database执行以上命令,可以在别的回话执行然后继续。 --//SELECT  member FROM v$log a, v$logfile b WHERE a.group#(+) = b.group# and a.STATUS='CURRENT' and rownum=1; column scn1 new_value v_scn1 column scn2 new_value v_scn2 select current_scn scn1  from v$database; select dbms_metadata.get_ddl('TABLE','DUAL','SYS') from dual; host sleep &&1 select current_scn  scn2 from v$database; --//prompt alter system dump logfile '&&v_member' scn min &&v_curr1 scn max &&v_curr2; --//alter system dump logfile '&&v_member' scn min &&v_curr1 scn max &&v_curr2; prompt alter system dump redo scn min &&v_scn1 scn max &&v_scn2 Objno 339517;; prompt alter system dump redo scn min &&v_scn1 scn max &&v_scn2 ;; alter system dump redo scn min &&v_scn1 scn max &&v_scn2 ; --//执行dumpredo3.sql脚本: SCOTT@book> @ dumpredo3.sql 3         SCN1 ------------  13278159374 DBMS_METADATA.GET_DDL('TABLE','DUAL','SYS') -------------------------------------------------------------------------   CREATE TABLE "SYS"."DUAL"    (    "DUMMY" VARCHAR2(1)    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING   STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "SYSTEM"         SCN2 ------------  13278159380 alter system dump redo scn min  13278159374 scn max  13278159380 Objno 339517; alter system dump redo scn min  13278159374 scn max  13278159380 ; System altered. 3.查看跟踪文件内容: DUMP REDO  Opcodes *.*  SCNs: scn: 0x0003.1770a20e (13278159374) thru scn: 0x0003.1770a214 (13278159380)  Times: creation thru eternity Initial buffer sizes: read 1024K, overflow 832K, change 805K Log read is SYNCHRONOUS though disk_asynch_io is enabled! Log read is SYNCHRONOUS though disk_asynch_io is enabled! Log read is SYNCHRONOUS though disk_asynch_io is enabled!  Thread 1 low checkpoint scn: 0x0003.176ffaf0  SCN Start Scan Point: scn: 0x0003.176ffaf0 (13278116592) Initial buffer sizes: read 1024K, overflow 832K, change 805K  INCARNATION:   START: scn: 0x0000.000e2006 (925702) Timestamp:  11/24/2015 09:11:12   END: scn: 0x0003.1770a214 (13278159380) Log read is SYNCHRONOUS though disk_asynch_io is enabled!  descrip:"Thread 0001, Seq# 0000000733, SCN 0x0003176ffaf0-0xffffffffffff" *** 2023-05-10 11:05:38.838 REDO RECORD - Thread:1 RBA: 0x0002dd.0000fa63.0010 LEN: 0x027c VLD: 0x0d SCN: 0x0003.1770a210 SUBSCN:  1 05/10/2023 11:05:35 (LWN RBA: 0x0002dd.0000fa63.0010 LEN: 0002 NST: 0001 SCN: 0x0003.1770a20d) CHANGE #1 TYP:2 CLS:1 AFN:1 DBA:0x00403391 OBJ:6192 SCN:0x0003.1770a1d9 SEQ:2 OP:11.19 ENC:0 RBL:0 KTB Redo op: 0x11  ver: 0x01 compat bit: 4 (post-11) padding: 1 op: F  xid:  0x000a.01d.00005664    uba: 0x00c002a6.10e8.0b Block cleanout record, scn:  0x0003.1770a20f ver: 0x01 opt: 0x02, entries follow...   itli: 2  flg: 2  scn: 0x0003.1770a1d9 Array Update of 1 rows: tabn: 0 slot: 3(0x3) flag: 0x2c lock: 1 ckix: 0 ncol: 5 nnew: 4 size: 0 KDO Op code:  21 row dependencies Disabled   xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x00403391  hdba: 0x00403390   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ itli: 1  ispac: 0  maxfr: 4863 vect = 28 col  1: [ 3]  c2 02 07 col  2: [ 1]  80 col  3: [ 1]  80 col  4: [11]  78 7b 05 0a 0c 06 24 21 02 64 c8 CHANGE #2 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0003.1770a20b SEQ:1 OP:5.2 ENC:0 RBL:0 ktudh redo: slt: 0x001d sqn: 0x00005664 flg: 0x0012 siz: 204 fbi: 0             uba: 0x00c002a6.10e8.0b    pxid:  0x0000.000.00000000 CHANGE #3 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0003.1770a210 SEQ:1 OP:5.4 ENC:0 RBL:0 ktucm redo: slt: 0x001d sqn: 0x00005664 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c002a6.10e8.0b ext: 3 spc: 6528 fbi: 0 CHANGE #4 TYP:0 CLS:36 AFN:3 DBA:0x00c002a6 OBJ:4294967295 SCN:0x0003.1770a20a SEQ:1 OP:5.1 ENC:0 RBL:0 ktudb redo: siz: 204 spc: 6734 flg: 0x0012 seq: 0x10e8 rec: 0x0b             xid:  0x000a.01d.00005664 ktubl redo: slt: 29 rci: 0 opc: 11.1 [objn: 6192 objd: 6192 tsn: 0] Undo type:  Regular undo        Begin trans    Last buffer split:  No Temp Object:  No Tablespace Undo:  No              0x00000000  prev ctl uba: 0x00c002a6.10e8.0a prev ctl max cmt scn:  0x0003.17709fcb  prev tx cmt scn:  0x0003.17709fd3 txn start scn:  0x0003.1770a20b  logon user: 83  prev brb: 12583587  prev bcl: 0 BuExt idx: 0 flg2: 0 KDO undo record: KTB Redo op: 0x04  ver: 0x01 compat bit: 4 (post-11) padding: 1 op: L  itl: xid:  0x000a.000.000056a5 uba: 0x00c002a0.10e8.1d                       flg: C---    lkc:  0     scn: 0x0003.17709df2 Array Update of 1 rows: tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 0 ncol: 5 nnew: 4 size: 0 KDO Op code:  21 row dependencies Disabled   xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x00403391  hdba: 0x00403390 itli: 1  ispac: 0  maxfr: 4863 vect = 28 col  1: [ 3]  c2 02 06 col  2: [ 1]  80 col  3: [ 1]  80 col  4: [11]  78 7b 05 0a 0c 05 33 30 f9 ac 88 END OF DUMP REDO --//0x00403391 = set dba 1,13201 = alter system dump datafile 1 block 13201 = 4207505 SYS@book> set numw 12 SYS@book> @ dba 00403391       RFILE#       BLOCK# BIGFILE_BLOCK# DUMP_CMD ------------ ------------ -------------- -------------------------------------------            1        13201        4207505 -- alter system dump datafile 1 block 13201 Press enter to find the segment using V$BH (this may take CPU time), CTRL+C to cancel: STATE      BLOCK_CLASS        OBJECT_TYPE         object        TCH    MODE_HELD D T P S D   CR_SCN_BAS   CR_SCN_WRP     FULL_SCN FLG_LRUFLG                    DQ ---------- ------------------ ------------------- ------------- --- ------------ - - - - - ------------ ------------ ------------ ------------------- ------------ cr         data block         TABLE               SYS.KU_UTLUSE   0            0 N N N N N    393257486            3  13278159374 2000000:8                      0 cr         data block         TABLE               SYS.KU_UTLUSE   0            0 N N N N N    393267584            3  13278169472 2000000:8                      0 cr         data block         TABLE               SYS.KU_UTLUSE   1            0 N N N N N    393257431            3  13278159319 80000:6                        0 xcur       data block         TABLE               SYS.KU_UTLUSE   3            0 Y N N N N            0            0            0 2000001:0                      0 Press enter to query what segment resides there using DBA_EXTENTS (this can be IO intensive), CTRL+C to cancel: OWNER                          SEGMENT_NAME                   PARTITION_NAME                 TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ ------------------------------ SYS                            KU_UTLUSE                                                     SYSTEM --//最后使用DBA_EXTENTS的查询对于生产系统有点慢,注意,可以使用Franck Pachot提供的脚本查询. SYS@book>  @ find_obj 1 13201      FILE_ID     BLOCK_ID       BLOCKS SEGMENT_TYPE         OWNER  SEGMENT_NAME    PARTITION_NAME     EXTENT_ID        BYTES TABLESPACE_NAME                RELATIVE_FNO       SEGTSN       SEGRFN       SEGBID ------------ ------------ ------------ -------------------- ------ --------------- --------------- ------------ ------------ ------------------------------ ------------ ------------ ------------ ------------            1        13200            8 TABLE                SYS    KU_UTLUSE                                  0        65536 SYSTEM                                    1            0            1        13200 SYS@book> select * from KU_UTLUSE; UTLNAME                                                USECNT ENCRYPTCNT COMPRESSCNT LAST_USED -------------------------------------------------- ---------- ---------- ----------- -------------------------- Oracle Utility Datapump (Export)                            1          0           0 2017-02-14 01:27:46.056439 Oracle Utility Datapump (Import)                           11          0           0 2017-02-14 02:03:25.528728 Oracle Utility SQL Loader (Direct Path Load)                0          0           0 Oracle Utility Metadata API                               106          0           0 2023-05-10 11:05:35.553805 Oracle Utility External Table                               0          0           0 SYS@book> @ conv_raw_value c20206 C20206=105 PL/SQL procedure successfully completed. --//很明显在使用dbms_metadata.get_ddl时,oracle在正常情况下要做一次登记处理,标识使用次数等信息.简单验证如下: SYS@book> select dbms_metadata.get_ddl('TABLE','DUAL','SYS') from dual; DBMS_METADATA.GET_DDL('TABLE','DUAL','SYS') --------------------------------------------------------------------------   CREATE TABLE "SYS"."DUAL"    (    "DUMMY" VARCHAR2(1)    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING   STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "SYSTEM" SYS@book> select * from KU_UTLUSE where UTLNAME='Oracle Utility Metadata API'; UTLNAME                                                USECNT ENCRYPTCNT COMPRESSCNT LAST_USED -------------------------------------------------- ---------- ---------- ----------- -------------------------- Oracle Utility Metadata API                               107          0           0 2023-05-10 11:14:54.817373 --//这样看来可以使用pdb的情况下,如果数据库只读状态,无法使用dbms_metadata.get_ddl只能讲oracle 的设计缺陷. 4.补充: --//自己的测试与分析走了一个弯路,先入为主的观念认为不会产生日志,实际上简单一点直接使用10046事件跟踪就ok了. @ 10046on 12 select dbms_metadata.get_ddl('TABLE','DUAL','SYS') from dual; @ 10046off --//查看跟踪文件: $ extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_43359.trc | grep -i update UPDATE SYS.KU_UTLUSE   SET USECNT      = USECNT + 1,       ENCRYPTCNT  = ENCRYPTCNT  + :1,       COMPRESSCNT = COMPRESSCNT + :2,       LAST_USED = CURRENT_TIMESTAMP WHERE UTLNAME   = :3 5.附上conv_raw_value.sql $ cat conv_raw_value.sql set serveroutput on set verify off declare n number; begin         dbms_stats.convert_raw_value(upper('&1'),n);         dbms_output.put_line(upper('&1')|| '='|| n); --      dbms_output.put_line(  n); end; / set serveroutput off