[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