OGG表级同步

OGG可以做全库的同步，也可用来做部分表的同步。下面介绍的案例就是一个基于表的同步。

需要注意的事项：
1，表级别同步也需要开启 FORCE LOGGING 和 ADD SUPPLEMENTAL LOG DATA
2，expdp导出前确源端认字符集
3，expdp导出前记录源端当前的SCN
4，impdp导入前确认目标端字符集
5，启动复制进程时采用源端记录的SCN

实验开始：

source ogg 目录：  /u02/orcl/ogg

target ogg 目录：  /u01/orcl/ogg

一、确认字符集

1. 确认源端字符集

[ora102@cdbsym135]%[/u02]export ORACLE_SID= ORASYM

[ora102@cdbsym135]%[/u02]echo $NLS_LANG

AMERICAN_AMERICA.ZHS16GBK

SQL> select * from nls_database_parameters;

PARAMETER                                                    VALUE

------------------------------------------------------------ ------------------------------

NLS_LANGUAGE                                                 SIMPLIFIED CHINESE

NLS_TERRITORY                                                CHINA

NLS_CURRENCY                                                 ?

NLS_ISO_CURRENCY                                             CHINA

NLS_NUMERIC_CHARACTERS                                       .,

NLS_CHARACTERSET                                             AL32UTF8

NLS_CALENDAR                                                 GREGORIAN

NLS_DATE_FORMAT                                              DD-MON-RR

NLS_DATE_LANGUAGE                                            SIMPLIFIED CHINESE

NLS_SORT                                                     BINARY

NLS_TIME_FORMAT                                              HH.MI.SSXFF AM

2. 确认目标端字符集（目标端的instance name与源端不同）

[ora102@cdbsym3]%[/u02/mydump]export ORACLE_SID= data

[ora102@cdbsym3]%[/u02/mydump]echo $NLS_LANG

AMERICAN_AMERICA.ZHS16GBK

SQL> select * from nls_database_parameters;

PARAMETER                                                    VALUE

------------------------------------------------------------ ----------

NLS_LANGUAGE                                                 AMERICAN

NLS_TERRITORY                                                AMERICA

NLS_CURRENCY                                                 $

NLS_ISO_CURRENCY                                             AMERICA

NLS_NUMERIC_CHARACTERS                                       .,

NLS_CHARACTERSET                                             AL32UTF8

NLS_CALENDAR                                                 GREGORIAN

NLS_DATE_FORMAT                                              DD-MON-RR

NLS_DATE_LANGUAGE                                            AMERICAN

NLS_SORT                                                     BINARY

NLS_TIME_FORMAT                                              HH.MI.SSXF

二，修改OGG参数文件中的字符集设置

1. 源端停止ogg抽取进程、投递进程

GGSCI (cdbsym135) 2> stop extsym

GGSCI (cdbsym135) 4> stop dpesym

GGSCI (cdbsym135) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

EXTRACT     STOPPED     DPESYM      00:00:00      00:00:06    

EXTRACT     STOPPED     EXTSYM      00:00:00      00:00:14    

2. 删除源端队列文件

[ora102@cdbsym135]%[/u02/orcl/ogg/dirdat]ls

ym000001

[ora102@cdbsym135]%[/u02/orcl/ogg/dirdat] rm -rf ym*

3. 源端EXTRACT修改字符集为AMERICAN_AMERICA.AL32UTF8

   之前的字符集为AMERICAN_AMERICA.ZHS16GBK

EXTRACT extsym

--setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")

setenv (NLS_LANG=" AMERICAN_AMERICA.AL32UTF8 ")

userid ogg,password ogg_123

GETTRUNCATES

REPORTCOUNT EVERY 1 MINUTES, RATE

numfiles 5000

DISCARDFILE ./dirrpt/extsym.dsc,APPEND,MEGABYTES 1000

DISCARDROLLOVER AT 3:00

WARNLONGTRANS 2h,CHECKINTERVAL 3m

EXTTRAIL ./dirdat/ym,MEGABYTES 200

DYNAMICRESOLUTION

TRANLOGOPTIONS EXCLUDEUSER ogg

TRANLOGOPTIONS  RAWDEVICEOFFSET  0

TRANLOGOPTIONS  CONVERTUCS2CLOBS

--TRANLOGOPTIONS altarchivelogdest primary instance amlstms1 /u11/arch/amlstms, altarc

hivelogdest  instance  amlstms2 /u10/arch/anlstms

table SYMBOLS.RB_AIO_ACCT_STATS;

table SYMBOLS.RB_MEMO_TRAN;

table SYMBOLS.RB_RESTRAINTS;

table SYMBOLS.DP2_RECV_MSG;

table SYMBOLS.DP2_SEND_MSG;

table SYMBOLS.RB_ACCT_STATS;

table SYMBOLS.RB_INT_DETAIL;

table SYMBOLS.RB_TRAN_HIST;

table SYMBOLS.RB_TRAN_DEF;

table SYMBOLS.RB_ACCT_BAL_TYPE;

table SYMBOLS.RB_ACCT;

table SYMBOLS.RB_CASH_RELATION;

table SYMBOLS.FM_BRANCH_TBL;

4. 目标端停止ogg抽取进程、投递进程

GGSCI (cdbsym3) 4>  stop repsym

5. 目标端删除队列文件

[ora102@cdbsym3]%[/u01/orcl/ogg/dirdat]ls

ym000001

[ora102@cdbsym3]%[/u01/orcl/ogg/dirdat] rm -rf ym*

6. 目标端REPLICAT修改字符集为AMERICAN_AMERICA.AL32UTF8

   之前的字符集为AMERICAN_AMERICA.ZHS16GBK

REPLICAT repsym

USERID ogg,PASSWORD  ogg_123

--SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")

setenv (NLS_LANG=" AMERICAN_AMERICA.AL32UTF8 ")

SQLEXEC "ALTER SESSION SET CONSTRAINTS=DEFERRED"

REPORT AT 01:59

REPORTCOUNT EVERY 1 MINUTES, RATE

REPORTROLLOVER AT 02:00

REPERROR DEFAULT, ABEND

--numfiles 5000

--GROUPTRANSOPS 10000

--HANDLECOLLISIONS

assumetargetdefs

DISCARDFILE ./dirrpt/repsym.dsc, APPEND, MEGABYTES 1000

DISCARDROLLOVER AT 02:00

GETTRUNCATES

ALLOWNOOPUPDATES

map SYMBOLS.RB_AIO_ACCT_STATS, target dbp.RB_AIO_ACCT_STATS;

map SYMBOLS.RB_MEMO_TRAN, target dbp.RB_MEMO_TRAN;

map SYMBOLS.RB_RESTRAINTS, target dbp.RB_RESTRAINTS;

map SYMBOLS.DP2_RECV_MSG, target dbp.DP2_RECV_MSG;

map SYMBOLS.DP2_SEND_MSG, target dbp.DP2_SEND_MSG;

map SYMBOLS.RB_ACCT_STATS, target dbp.RB_ACCT_STATS;

map SYMBOLS.RB_INT_DETAIL, target dbp.RB_INT_DETAIL;

map SYMBOLS.RB_TRAN_HIST, target dbp.RB_TRAN_HIST;

map SYMBOLS.RB_TRAN_DEF, target dbp.RB_TRAN_DEF;

map SYMBOLS.RB_ACCT_BAL_TYPE, target dbp.RB_ACCT_BAL_TYPE;

map SYMBOLS.RB_ACCT, target dbp.RB_ACCT;

map SYMBOLS.RB_CASH_RELATION, target dbp.RB_CASH_RELATION;

三，重新创建源端EXTRACT进程、DATA PUMP进程和目标端的REPLICAT进程

源端删除抽取进程、删除投递进程

GGSCI (cdbsym135) > dblogin userid ogg, password ogg_123

GGSCI (cdbsym135) >  delete extsym

GGSCI (cdbsym135) > delete dpesym

源端创建抽取进程 extsym

GGSCI (cdbsym135) > add extract extsym,tranlog,begin now

GGSCI (cdbsym135) > add exttrail ./dirdat/ym,extract extsym,megabytes 200

源端创建投递进程 dpesym

GGSCI (cdbsym135) > add extract dpesym,exttrailsource ./dirdat/ym

GGSCI (cdbsym135) > add rmttrail ./dirdat/ym,extract dpesym ,megabytes 200

源端启动EXTRACT,DATA PUMP进程

start extsym

start dpesym

目标端删除复制进程repsym

GGSCI (cdbsym3) 4> dblogin userid ogg,password ogg_123

GGSCI (cdbsym3) 5> delete repsym

目标端创建复制进程repsym

GGSCI (cdbsym3) 6>  add replicat repsym exttrail ./dirdat/ym, checkpointtable ogg.checktable

四、禁用目标端的trigger、外键级联删除约束、job

1. 目标端查看DBP是否有开启的trigger

SQL> select owner,trigger_name,status from dba_triggers where owner=' DBP ' and status='ENABLED';

禁用DBP下所有trigger

declare

v_sql varchar2(2000);

CURSOR c_trigger IS SELECT 'alter trigger '||owner||'.'||trigger_name||' disable' from dba_triggers where owner in ('DBP');

BEGIN

OPEN c_trigger;

LOOP

FETCH c_trigger INTO v_sql;

EXIT WHEN c_trigger%NOTFOUND;

execute immediate v_sql;

end loop;

close c_trigger;

end;

/

2. 查看DBP下是否存在外键级联删除约束

select distinct constraint_type from dba_constraints where owner='DBP'；

禁用DBP下所有外键级联删除约束

declare

v_sql varchar2(2000);

CURSOR c_trigger IS SELECT 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name from dba_constraints where constraint_type=' R ' and owner in (' DBP ');

BEGIN

OPEN c_trigger;

LOOP

FETCH c_trigger INTO v_sql;

EXIT WHEN c_trigger%NOTFOUND;

execute immediate v_sql;

end loop;

close c_trigger;

end;

/

五，源端准备expdp

1. 查看当前scn

SQL> col GET_SYSTEM_CHANGE_NUMBER for 999999999999999999

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

           12509239693613

2. 确认expdp的DIRECTORY_NAME

SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME

------------------------------ ------------------------------

DIRECTORY_PATH

----------------------------------------

SYS                            MYDUMP

/u04/dump_dir

3. 编辑expdp脚本，修改当前scn

[ora102@cdbsym135]%[/home/ora102]cd /u04/dump_dir

[ora102@cdbsym135]%[/u04/dump_dir]ll

total 6318072

-rwxrwxrwx    1 ora102   dba             606 Feb 05 15:38 expdp.sh

-rw-r--r--    1 ora102   dba              28 Feb 05 15:30 include.par

-rw-r--r--    1 ora102   dba            3548 Feb 05 15:41 symbols_20150205.log

-rw-r-----    1 ora102   dba      3234836480 Feb 05 15:41 symbols_20150205_01.dump

mv expdp_orasym_20150204. expdp_orasym_20150205.sh

vi expdp_orasym_20150205.sh

expdp userid=\'/ as sysdba\' directory=mydump logfile=symbols_20150205.log dumpfile=sy

mbols_20150205_%U.dump tables=SYMBOLS.RB_AIO_ACCT_STATS,SYMBOLS.RB_MEMO_TRAN,SYMBOLS.R

B_RESTRAINTS,SYMBOLS.DP2_RECV_MSG,SYMBOLS.DP2_SEND_MSG,SYMBOLS.RB_ACCT_STATS,SYMBOLS.R

B_INT_DETAIL,SYMBOLS.RB_TRAN_HIST,SYMBOLS.RB_TRAN_DEF,SYMBOLS.RB_ACCT_BAL_TYPE,SYMBOLS

.RB_ACCT,SYMBOLS.RB_CASH_RELATION,SYMBOLS.FM_BRANCH_TBL,SYMBOLS.FM_SYSTEM,SYMBOLS.FM_T

AX_RATE,SYMBOLS.RB_BASE_ACCT_STATS,SYMBOLS.RB_ACCT_ATTACH,SYMBOLS.DP2_CNAPS_BANK,SYMBOLS.FM_REF_CODE,SYMBOLS.RB_RESTRAINT_TYPE flashback_scn= 12509239693613

4. 指定字符集为AMERICAN_AMERICA.AL32UTF8

[ora102@cdbsym135]%[/home/ora102]export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

[ora102@cdbsym135]%[/home/ora102]nohup expdp_orasym_20150205.sh &

5. 将源端导出的dmp文件scp到目标端

[ora102@cdbsym135]%[/u04/dump_dir]scp sym* 10.66.204.19:/u02/mydump

六，目标端准备impdp

1. 目标端修改impdp脚本

注意：REMAP_SCHEMA=SYMBOLS:DBP

schema为DBP

export ORACLE_SID=data

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

impdp userid=\'/ as sysdba\' directory=mydump logfile=symbols_20150205_impdp.log dumpfile=symbols_20150205_%U.dump REMAP_SCHEMA=SYMBOLS:DBP table_exists_action=replace parfile=include.par

parfile内容如下：

[ora102@cdbsym3]%[/u02/mydump]cat include.par

exclude=trigger

exclude=CONSTRAINT

exclude=COMMENT

exclude=STATISTICS

exclude=grant

2. 目标端执行impdp导入

[ora102@cdbsym3]%[/u02/mydump]impdp userid=\'/ as sysdba\' directory=mydump logfile=symbols_20150205_impdp.log dumpfile=symbols_20150205_%U.dump REMAP_SCHEMA=SYMBOLS:DBP table_exists_action=replace parfile=include.par

3. 目标端启动REPLICAT进程

GSCI (cdbsym3) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

REPLICAT    STOPPED     REPSYM      00:00:00      06:06:32 

 

sGSCI (cdbsym3) 2> tart repsym aftercsn 12509239693613

整个实验结束

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29047826/viewspace-1429439/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29047826/viewspace-1429439/