Oracle_重做OGG的同步

主库查看信息：JRWZ2ZX为一个用户的数据，这里只有一个用户的数据


GGSCI (db2) 5> INFO ALL


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
EXTRACT     ABENDED     EJRWZ2ZX    00:00:02      00:00:01    
EXTRACT     RUNNING     EJRW_OUT    00:00:00      00:00:01    
EXTRACT     RUNNING     EPUB_DAT    00:00:00      00:00:08    
EXTRACT     RUNNING     E_CHARGE    00:00:00      00:00:06    
EXTRACT     RUNNING     PJRWZ2ZX    00:00:03      00:00:01    
EXTRACT     RUNNING     PJRW_OUT    00:00:00      00:00:06    
EXTRACT     RUNNING     PPUB_DAT    00:00:00      00:00:00    
EXTRACT     RUNNING     P_CHARGE    00:00:00      00:00:05 


GGSCI (db2) 11> info EJRWZ2ZX


EXTRACT    EJRWZ2ZX  Last Started 2018-01-03 05:35   Status ABENDED
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2018-01-03 09:20:07  Thread 1, Seqno 10818, RBA 39790080
                     SCN 2.1964901126 (10554835718)
Log Read Checkpoint  Oracle Redo Logs
                     2018-01-03 09:20:04  Thread 2, Seqno 14930, RBA 112192660
                     SCN 2.1964900594 (10554835186)




GGSCI (db2) 12> info PJRWZ2ZX


EXTRACT    PJRWZ2ZX  Last Started 2017-12-29 11:13   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  File ./dirdat/zx015392
                     2018-01-03 09:40:09.000000  RBA 16161953
因为是Rac环境，所有有2个线程
查看主库线程1和线程2的归档文件序列号
发现线程1的归档文件序列号已经大于10818，说明主库的抽取进程还没有应用到，归档文件就被删除了。如果线程2，归档也
不存在，也是一样处理。
导致中断，需要操作OGG同步。
select  * from v$archived_log where THREAD#=1 AND sequence# >10810;
select  * from v$archived_log where THREAD#=2 AND sequence# >14920;




把备库重做Ogg同步：
首先要在主库跳过没有同步的归档文件。是主的抽取进程继续进行。
修改抓取进程，是它跳过被删除的归档文件，然后抽取进程继续读取有已经存在的归档文件
如果线程1和线程2的归档文件序列号不存在，就跳过到有归档的文件
GGSCI (db2) 12> alter extract   EJRWZ2ZX thread 1,extseqno 10819,extrba 0


GGSCI (db2) 12> alter extract   EJRWZ2ZX thread 1,extseqno 14931,extrba 0


然后查看抽取进程和投递进程的状态信息:
GGSCI (db2) 11> info EJRWZ2ZX


EXTRACT    EJRWZ2ZX  Last Started 2018-01-03 05:35   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2018-01-03 09:20:07  Thread 1, Seqno 10819, RBA 2221
                     SCN 2.1964901126 (10554835718)
Log Read Checkpoint  Oracle Redo Logs
                     2018-01-03 09:20:04  Thread 2, Seqno 14931, RBA 423156
                     SCN 2.1964900594 (10554835186)


GGSCI (db2) 11> info EJRWZ2ZX
检查一下RBA是否有曾加，增加说明进程在读取数据。


GGSCI (db2) 12> info PJRWZ2ZX
EXTRACT    PJRWZ2ZX  Last Started 2017-12-29 11:13   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  File ./dirdat/zx015393
                     2018-01-03 09:40:09.000000  RBA 16161953




主库操作，把主库的数据按SCN号到出，然后倒入到备库。
主库：查看当前最新的SCN号
select current_scn  from  v$database;


把用户jrwz2_zx的数据按照最新的SCN号进行备份，然后要排除某些表，用exclude参数
expdp system/ninvoice directory=backup dumpfile=0101.dmp schemas=jrwz2_zx  FLASHBACK_SCN=10564875712  exclude=table:\"IN\(\'ZX_YBNSR\',\'ZX_LRBXX\',\'ZX_ZCFZBXX\',\'ZX_JKLSB\',\'ZX_CWXX\',\'ZX_SBXX\',\'ZX_SBZSXX\',\'ZX_JYDX\',\'JS_SB_ZZS_YBNSR\',\'GD_SB_CWBB_XQYKJZZ_ZCFZB\',\'GD_SB_ZZS_YBNSR\',\'GD_SB_CWBB_XQYKJZZ_LRB\',\'ZX_BGDJXX\',\'ZX_FPKJMX_INFO\',\'JS_SB_CWBB_QYKJZZ_ZCFZB\',\'JS_SB_SBXX\',\'ZX_NSRJCXX\',\'GD_SB_SBXX\',\'JS_ZS_YJSF\',\'GD_ZS_JKS\',\'GD_SB_CWBB_XQYKJZZ_XJLLB\',\'ZX_XJLLBXX\',\'ZX_FP_JYDX_NY\',\'JS_ZX_JYDX_CJB\',\'T_WZPF\',\'GD_SB_CWBB_2007_ZCFZB_CTAIS\'\)\"
或者用RMAN基于SCN号备份，如果数据量太大了的话。




备库操作：先给用户加锁，然后删除备库用户jrwz2_zx的联级所有数据，然后创建该用户。（在TOAD里面操作），请谨慎操作
GGSCI (logdbbk) 26> stop EJRWZ2ZX
GGSCI (logdbbk) 26> stop RJRW_OUT    
GGSCI (logdbbk) 26> stop RPUBDAT     
GGSCI (logdbbk) 26> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
REPLICAT    stop       RJRWZ2ZX    00:00:00      00:00:00    
REPLICAT    stop       RJRW_OUT    00:00:00      00:00:04    
REPLICAT    stop       RPUBDAT     00:00:00      00:00:01  


[root@logdbbk ggtarget]#
SQL>lock user JRWZ2_ZX ;
[root@logdbbk ggtarget]#
SQL>DROP USER JRWZ2_ZX CASCADE;


CREATE USER JRWZ2_ZX
  IDENTIFIED BY <password>
  DEFAULT TABLESPACE JRWZ2_ZX
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 2 Roles for JRWZ2_ZX 
  GRANT CONNECT TO JRWZ2_ZX;
  GRANT RESOURCE TO JRWZ2_ZX;
  ALTER USER JRWZ2_ZX DEFAULT ROLE ALL;
  -- 4 System Privileges for JRWZ2_ZX 
  GRANT CREATE DATABASE LINK TO JRWZ2_ZX;
  GRANT CREATE VIEW TO JRWZ2_ZX;
  GRANT DEBUG CONNECT SESSION TO JRWZ2_ZX;
  GRANT UNLIMITED TABLESPACE TO JRWZ2_ZX;
  -- 2 Tablespace Quotas for JRWZ2_ZX 
  ALTER USER JRWZ2_ZX QUOTA UNLIMITED ON JRWZ2_ZX;
  ALTER USER JRWZ2_ZX QUOTA UNLIMITED ON NINVOICE;


导入数据到备机里面，备库操作,是备库和主库的数据保持一致。
impdp system/ninvoice directory=backup dumpfile=0101.dmp schemas=jrwz2_zx  FLASHBACK_SCN=10564875712；

禁用外键约束，触发器，job：
select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';'
    from dba_constraints
      where constraint_type in ('R') and
      owner in('NINVOICE_NXDS')
    order by status,owner;
   
   
alter table NINVOICE_NXDS.NSR_DYMBXXB disable constraint FK_ID;
alter table NINVOICE_NXDS.PRINTER_PARAM_DETAIL disable constraint FK_PRINTER_ID;
alter table NINVOICE_NXDS.RESOURCE_ROLE_INFO disable constraint FK_RESOURCE_ID;
alter table NINVOICE_NXDS.INVOICE_DETAILED_ACCT_LIST disable constraint FK_SEQ_NO;
alter table NINVOICE_NXDS.INVOICE_BALANCE disable constraint FK_INVOICE_CODE;
alter table NINVOICE_NXDS.RESOURCE_ROLE_INFO disable constraint FK_ROLE_ID;

   
    select owner,table_name,constraint_name,STATUS
    from dba_constraints
      where constraint_type in ('R') and
      owner in('NINVOICE_NXDS')
    order by status,owner;
========================================================================   
   
   
    select 'alter trigger '||owner||'.'||trigger_name||' disable;'
    from dba_triggers
    where owner in('NINVOICE_NXDS')
    order by status,owner;
   
    no rows selected
===========================================================================   
     SQL> show parameter job

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0
SQL>
可以手工在load里面禁用外键约束，触发器，job。


然后开启备库的Ogg抓取进程，从刚刚备份的SCN开始抓取。这样就可以连接起来了。
GGSCI (logdbbk) 26>start replicat EJRWZ2ZX,aftercsn 10564875712
GGSCI (logdbbk) 26> start RJRW_OUT    
GGSCI (logdbbk) 26> start  RPUBDAT     
GGSCI (logdbbk) 27> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
REPLICAT    RUNNING     RJRWZ2ZX    00:00:00      00:00:00    
REPLICAT    RUNNING     RJRW_OUT    00:00:00      00:00:09    
REPLICAT    RUNNING     RPUBDAT     00:00:00      00:00:05  


查看Ogg的log日志文件信息
[root@logdbbk ggtarget]# tail -300 ggserr.log
Ogg同步一个用户的同步已经完成。