• 转 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 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    

                       

    ->

     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;

  • 相关阅读:
    339. Nested List Weight Sum
    41. First Missing Positive
    366. Find Leaves of Binary Tree
    287. Find the Duplicate Number
    130. Surrounded Regions
    ubuntu18.04安装mongodb4.4
    阿里dataX配置使用
    MySQL主从同步简单介绍&配置
    阿里yugong配置使用
    ubuntu编译安装mysql
  • 原文地址:https://www.cnblogs.com/feiyun8616/p/6019768.html
Copyright © 2020-2023  润新知