转 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
其中的含义不在给出，可以看看官方手册。
2.
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\tmp\sql_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
->
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;
