Oracle_重做OGG的同步

63 阅读 0 评论 42 点赞

我是靠谱客的博主俭朴路灯，最近开发中收集的这篇文章主要介绍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同步一个用户的同步已经完成。