[2020-12-31]ogg 判断表是否开启附加日志的方法

--//创建测试表

1. SQL> alter table t3 add supplemental log data (all) columns ;Table altered.SQL>

复制代码

--//方法1 检察表的ddl语句

1. SQL> select dbms_metadata.get_ddl('TABLE','T3','POSTGRES') from dual;DBMS_METADATA.GET_DDL('TABLE','T3','POSTGRES')--------------------------------------------------------------------------------  CREATE TABLE "POSTGRES"."T3"   (    "ID" NUMBER(*,0),        "NAME" VARCHAR2(20),         PRIMARY KEY ("ID")  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "USERS"  ENABLE,         SUPPLEMENTAL LOG DATA (ALL) COLUMNS        info trandata postgres.*ERROR: Not logged into database, use DBLOGIN.GGSCI (QXY.localdomain) 14> dblogin userid goldengate, password goldengate;Successfully logged into database.GGSCI (QXY.localdomain) 15> GGSCI (QXY.localdomain) 15> info trandata postgres.*Logging of supplemental redo log data is disabled for table POSTGRES.T1.Logging of supplemental redo log data is disabled for table POSTGRES.T2.Logging of supplemental redo log data is enabled for table POSTGRES.T3.     dblogin userid goldengate, password goldengate;Successfully logged into database.GGSCI (QXY.localdomain) 19>

复制代码

--//记载goldengate登陆的会话信息

1. SQL> select sid, serial#,username,to_char(logon_time,'yyyy-mm-dd hh24:mi:ss') from  v$session where username = 'GOLDENGATE';       SID    SERIAL# USERNAME                       TO_CHAR(LOGON_TIME,---------- ---------- ------------------------------ -------------------        20        287 GOLDENGATE                     2020-12-31 11:33:47SQL> select p.pid, p.spid from v$process p, v$session s where addr = paddr and sid = 20;       PID SPID---------- ------------------------        28 3670

复制代码

--//对ggsci登陆的会话做10046

1. SQL> oradebug setorapid 28Oracle pid: 28, Unix process pid: 3670, image: oracle@QXY.localdomain (TNS V1-V3)SQL> oradebug event 10046 trace name context forever ,level 1;Statement processed.SQL>

复制代码

--//ggsci下面添加附加日志

1. GGSCI (QXY.localdomain) 20> add trandata POSTGRES.T12020-12-31 12:19:06  WARNING OGG-06439  No unique key is defined for table T1. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.Logging of supplemental redo data enabled for table POSTGRES.T1.TRANDATA for scheduling columns has been added on table 'POSTGRES.T1'.GGSCI (QXY.localdomain) 21>

复制代码

--//检察10046trace内容

1. SQL> oradebug tracefile_name/u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_3670.trcSQL>

复制代码

--//涉及到T1表相关的trace内容如下

1. SQL ID: 9rgyktruazxyc Plan Hash: 0LOCK TABLE "POSTGRES"."T1" IN SHARE MODE  NOWAITcall     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        0      0.00       0.00          0          0          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total        2      0.00       0.00          0          0          0           0Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 91     (recursive depth: 1)********************************************************************************SQL ID: 2mpdbh71vh1dp Plan Hash: 0ALTER TABLE "POSTGRES"."TSQL ID: 20sf93ct37fgg Plan Hash: 0LOCK TABLE "POSTGRES"."T1" IN EXCLUSIVE MODE  NOWAITcall     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          1          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        0      0.00       0.00          0          0          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total        2      0.00       0.00          0          1          0           0Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 91     (recursive depth: 1)************************************************************LOCK TABLE "POSTGRES"."T1" IN SHARE MODE  NOWAITALTER TABLE "POSTGRES"."T                               col sqltext format a60SQL> select /*+parallel(a,16)*/ username,timestamp,sql_text from dba_audit_trail a where owner='&owner' and obj_name='&obj_name';Enter value for owner: POSTGRESEnter value for obj_name: T1old   1: select /*+parallel(a,16)*/ username,timestamp,sql_text from dba_audit_trail a where owner='&owner' and obj_name='&obj_name'new   1: select /*+parallel(a,16)*/ username,timestamp,sql_text from dba_audit_trail a where owner='POSTGRES' and obj_name='T1'USERNAME        TIMESTAMP            SQL_TEXT--------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------GOLDENGATE      31-DEC-20            ALTER TABLE "POSTGRES"."T1" ADD SUPPLEMENTAL LOG GROUP "GGS_89082" ("ID") ALWAYS  /* GOLDENGATE_DDL_REPLICATION */GOLDENGATE      31-DEC-20            ALTER TABLE "POSTGRES"."T1" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS  /* GOLDENGATE_DDL_REPLICATION */SQL>

复制代码

--//包罗多列的情况也是一样

1. SQL> conn postgres/postgresConnected.SQL> create table t6(id int, name varchar2(20));Table created.

复制代码

1. GGSCI (QXY.localdomain) 8> add trandata postgres.T62020-12-31 12:51:39  WARNING OGG-06439  No unique key is defined for table T6. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.Logging of supplemental redo data enabled for table POSTGRES.T6.TRANDATA for scheduling columns has been added on table 'POSTGRES.T6'.GGSCI (QXY.localdomain) 9>

复制代码

--//get_ddl

1. SQL> select dbms_metadata.get_ddl('TABLE','T6','POSTGRES') from dual;DBMS_METADATA.GET_DDL('TABLE','T6','POSTGRES')--------------------------------------------------------------------------------  CREATE TABLE "POSTGRES"."T6"   (    "ID" NUMBER(*,0),        "NAME" VARCHAR2(20),         SUPPLEMENTAL LOG GROUP "GGS_89093" ("ID", "NAME") ALWAYS,         SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS,         SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS,         SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS   ) SEGMENT CREATION DEFERRED  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS255 NOCOMPRESS LOGGING  TABLESPACE "USERS"

复制代码

 

来源：https://blog.csdn.net/m15217321304/article/details/112007854
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！