转 OGG add trandata 到底做了什么

有的时候我们做OGG的时候add trandata会出现异常。
 这里就剖析一下add trandata到底做了什么
GGSCI (yjfora81 as ggs_admin@testdb) 2> add trandata ppzhu1.test3
2016-03-08 11:47:36  WARNING OGG-00706  Failed to add supplemental log group on table PPZHU1.TEST3 due to ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL ALTER TABLE “PPZHU1″.”TEST3″ ADD SUPPLEMENTAL LOG GROUP “GGS_87926″ (“USER_ID”) ALWAYS  /* GOLDENGATE_DDL_REPLICATION */.
然后一直hang住

使用OGG用户登陆我的用户GGS_ADMIN

GGSCI (yjfora81) 1> dblogin userid ggs_admin,password test;
 Successfully logged into database.

SQL> select SID , SERIAL# ,PADDR  from v$session where USERNAME=’GGS_ADMIN';
        SID    SERIAL# PADDR
———- ———- —————-
       145         15 00000001BE185360
 SQL> select pid,spid from v$process where addr=’00000001BE185360′;
        PID SPID
———- ————————
        37 40986
         
找到SPID和pid  OS PID是40986数据库的pid是37
我们用37 进行ORADEBUG

SQL> oradebug SETORAPID 37
Oracle pid: 37, Unix process pid: 40986, image: oracle@yjfora81 (TNS V1-V3)
 SQL> oradebug tracefile_name
 /home/oracle/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_40986.trc

10046事件可以设置以下四个级别:
1 – 启用标准的SQL_TRACE功能,等价于sql_trace
 4 – Level 1 加上绑定值(bind values)
 8 – Level 1 + 等待事件跟踪
12 – Level 1 + Level 4 + Level 8

找到语句使用LEVEL 1即可

SQL> oradebug event 10046 trace name context forever ,level 1;
 Statement processed.

在ggsci中

GGSCI (yjfora81 as ggs_admin@testdb) 3> add trandata ppzhu1.test4

Logging of supplemental redo data enabled for table PPZHU1.TEST4.
 TRANDATA for scheduling columns has been added on table ‘PPZHU1.TEST4′.
 TRANDATA for instantiation CSN has been added on table ‘PPZHU1.TEST4′.

SQL> Oradebug event 10046 trace name context off;
 Statement processed.

然后我们查看trace文件

LOCK TABLE “PPZHU1″.”TEST6″ IN SHARE MODE  NOWAIT 
 LOCK TABLE “PPZHU1″.”TEST6″ IN EXCLUSIVE MODE  NOWAIT 
 ALTER TABLE “PPZHU1″.”TES

可以看到是要加锁的所以add trandata可能造成堵塞
 由于ALTER TABLE 看不全，我做了审计发现如下：
 ALTER TABLE “PPZHU1″.”TEST8″ ADD SUPPLEMENTAL LOG GROUP “GGS_87937″ (“USER_ID”) ALWAYS  /* GOLDENGATE_DDL_REPLICATION */
  ALTER TABLE “PPZHU1″.”TEST8″ ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS  /* GOLDENGATE_DDL_REPLICATION */
在结合我们的报错信息

GGSCI (yjfora81 as ggs_admin@testdb) 2> add trandata ppzhu1.test3
 2016-03-08 11:47:36  WARNING OGG-00706  Failed to add supplemental log group on table PPZHU1.TEST3 due to ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL ALTER TABLE “PPZHU1″.”TEST3″ ADD SUPPLEMENTAL LOG GROUP “GGS_87926″ (“USER_ID”) ALWAYS  /* GOLDENGATE_DDL_REPLICATION */.
 2016-03-08 11:53:00  WARNING OGG-00706  Failed to add supplemental log group on table PPZHU1.TEST3 due to ORA-03113: end-of-file on communication channel
 Process ID: 23575
 Session ID: 80 Serial number: 2709 SQL BEGIN DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name => ‘”PPZHU1″.”TEST3″‘, supplemental_logging => ‘none’); END;.

可以确定 add trandata 至少做了如下操作

1、ALTER TABLE “PPZHU1″.”TEST8″ ADD SUPPLEMENTAL LOG GROUP “GGS_87937″ (“USER_ID”) ALWAYS
 2、ALTER TABLE “PPZHU1″.”TEST8″ ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS 
 3、DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name => ‘”PPZHU1″.”TEST3″‘, supplemental_logging => ‘none’

其中这几部中第三步会加锁并且不是NOWAIT的方式，如果表上有事物正在运行，那么这个语句会等待，因为他需要一个MODE 4的锁在表级S锁
 如下：
SQL> select * from v$lock;
 ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
—————- —————- ———- —- ———- ———- ———- ———- ———- ———-
00000001BD52BC80 00000001BD52BCF8         80 TX       655363       1685          6          0        122          0
 00007F716C65A908 00007F716C65A968         80 TM        87926          0          0          4        122          0

而DML会在表级别上一个SX级别的3级锁，所以不兼容一直卡着，并且它还会影响随后的DML因为DML需要表级别的SX锁，这样对生产系统的影响
 很大。解决方式就是KILL掉OGG登陆的会话。
而1,2两部都是NOWAIT方式，可以及时报错出来及ORA-00054: resource busy，不会堵塞

实际上1,2两部做完后，我们可以再ORACLE数据库中查看视图
dba_log_group_columns
 dba_log_groups
其中的含义不在给出，可以看看官方手册。

Q: SQL>alter database add supplemental log data; 将数据库附加日志打开后还需要对每张表执行 add trandata 吗？
谁知道啊？

A:需要！如果不执行add trandata，insert同步没有问题（ORACLE数据库），但是在同步update或delete操作时，就会因为丢失主键报同步错误。不开启表级的最小附加日志，update的redo信息不记录没有进行更新的字段信息，如主键不更新的话主键不记录在redo中，所以会导致同步失败。
亲自实践过，不信你也可以试试。

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> /

Session altered.

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL>select * from v$diag_info;

案例可以参考 http://blog.mchz.com.cn/?p=4047

alter session set statistics_level=all;

select /*+ gathe_plan_statistics */ * from ts.ts_record t where system_name='人员小车闸口' order by pass_datetime desc;

select sql_text,sql_id from v$sql where sql_text like 'select%*%人员小车闸口%';

spool D:dba mpsql_dev.log

select * from table(dbms_xplan.display_cursor('36tvbv2uth9j9',0,'runstats_last'));

spool off

set pages 100 heading off pause on

select * from table(dbms_xplan.display_cursor(null,0,'allstats last'));

select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

-->

SET LONG 1000000 SET FEEDBACK OFF

spool monitor_sql.html

SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>'20wfgydukawbw',type=> 'HTML') AS report FROM dual;

spool off