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

整個實驗結束

OGG表級同步

相關文章