RAC(以下稱TARGET端)環境下兩節點HOSTNAME，IP ADDRESS等相關設定如下
# Public
192.168.100.110 racnode01.dba.local racnode01
192.168.100.120 racnode02.dba.local racnode02
# Private
10.0.0.110 racnode01-priv.dba.local racnode01-priv
10.0.0.120 racnode02-priv.dba.local racnode02-priv
# Virtual
192.168.100.111 racnode01-vip.dba.local racnode01-vip
192.168.100.121 racnode02-vip.dba.local racnode02-vip
# SCAN
192.168.100.101 racnode.dba.local racnode
192.168.100.102 racnode.dba.local racnode
192.168.100.103 racnode.dba.local racnode
單主機(以下稱SOURCE端)HOSTNAME，IP ADDRESS等相關設定如下
192.168.100.50 oggs.dba.local
STORAGE(DNS)主機相關設定
192.168.100.10 racstorage.dba.local
設定程序如下：
在SOURCE端
建立GOLDENGATE安裝目錄
[oracle@oggs ~]$ mkdir /u01/app/goldengate
[oracle@oggs ~]$ mkdir /u01/app/goldengate/ogg
將p17952585_1121017_Linux-x86-64.zip解壓縮到/u01/app/goldengate/ogg路徑下
[oracle@oggs ~]$ unzip p17952585_1121017_Linux-x86-64.zip
[oracle@oggs ~]$ tar –xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
在TARGET端
建立GOLDENGATE安裝目錄(此路徑為ACFS，好處是當一個節點有問題時，另一個節點也可以使用，設定方式會有另一篇文章教學)
[oracle@racnode01 ogg]$ mkdir /u01/app/goldengate
[oracle@racnode01 ogg]$ mkdir /u01/app/goldengate/ogg
將p17952585_1121017_Linux-x86-64.zip解壓縮到/u01/app/goldengate/ogg路徑下
[oracle@racnode01 ogg]$ unzip p17952585_1121017_Linux-x86-64.zip
[oracle@racnode01 ogg]$ tar –xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
在SOURCE端
建立goldengate工作目錄
[oracle@oggs ogg]$ ./ggsci
GGSCI (oggs.dba.local) 2> create subdirs
在TARGET端
建立goldengate工作目錄
[oracle@racnode01 ogg]$ ./ggsci
GGSCI (racnode01.dba.local) 1> create subdirs
在SOURCE端
建立GOLDENGATE的tablespace，user及權限
[oracle@oggs ogg]$ sqlplus / as sysdba
SQL> create tablespace ogg_data datafile '/oradata/ogg_data_01.dbf' size 2048M AUTOEXTEND ON;
SQL> CREATE USER OGG
IDENTIFIED BY ogg
DEFAULT TABLESPACE ogg_data
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
SQL> GRANT CONNECT TO OGG;
SQL> GRANT RESOURCE TO OGG;
SQL> GRANT DBA TO OGG;
SQL> ALTER USER OGG DEFAULT ROLE ALL;
SQL> GRANT CREATE ANY SYNONYM TO OGG;
SQL> GRANT UNLIMITED TABLESPACE TO OGG;
SQL> GRANT CREATE SYNONYM TO OGG WITH ADMIN OPTION;
SQL> ALTER USER OGG QUOTA UNLIMITED ON ogg_data;
SQL> grant create any table to ogg;
SQL> grant create any view to ogg;
SQL> grant create any procedure to ogg;
SQL> grant create any sequence to ogg;
SQL> grant create any index to ogg;
SQL> grant create any trigger to ogg;
在SOURCE端
啟用supplemental log
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER SYSTEM SWITCH LOGFILE;
在SOURCE端
在OGG的目錄下登入資料庫，設定並啟用DDL Replication
[oracle@oggs ogg]$ sqlplus / as sysdba
SQL> @marker_setup
--在Enter Oracle GoldenGate schema name輸入[ogg]
SQL> @ddl_setup
--Enter Oracle GoldenGate schema name輸入[ogg]
SQL> @role_setup
--Enter GoldenGate schema name輸入[ogg]
SQL> GRANT GGS_GGSUSER_ROLE TO ogg;
SQL> @ddl_enable
SQL> @ddl_pin ogg
SQL> @SEQUENCE
在TARGET端
建立GOLDENGATE的tablespace，user及權限
SQL> create tablespace ogg_data datafile '+DATA01' size 2048M AUTOEXTEND ON;
SQL> CREATE USER OGG
IDENTIFIED BY ogg
DEFAULT TABLESPACE ogg_data
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
SQL> GRANT CONNECT TO OGG;
SQL> GRANT RESOURCE TO OGG;
SQL> GRANT DBA TO OGG;
SQL> ALTER USER OGG DEFAULT ROLE ALL;
SQL> GRANT CREATE ANY SYNONYM TO OGG;
SQL> GRANT UNLIMITED TABLESPACE TO OGG;
SQL> GRANT CREATE SYNONYM TO OGG WITH ADMIN OPTION;
SQL> ALTER USER OGG QUOTA UNLIMITED ON ogg_data;
SQL> grant create any table to ogg;
SQL> grant create any view to ogg;
SQL> grant create any procedure to ogg;
SQL> grant create any sequence to ogg;
SQL> grant create any index to ogg;
SQL> grant create any trigger to ogg;
SQL> grant create any view to ogg;
在TARGET端
啟用supplemental log
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER SYSTEM SWITCH LOGFILE;
在TARGET端
在OGG的目錄下登入資料庫，設定並啟用DDL Replication
[oracle@racnode01 ogg]$ sqlplus / as sysdba
SQL> @marker_setup
--在Enter Oracle GoldenGate schema name輸入[ogg]
SQL> @ddl_setup
--Enter Oracle GoldenGate schema name輸入[ogg]
SQL> @role_setup
--Enter GoldenGate schema name輸入[ogg]
SQL> GRANT GGS_GGSUSER_ROLE TO ogg;
SQL> @ddl_enable
SQL> @ddl_pin ogg
SQL> @SEQUENCE
在SOURCE端
啟用transaction data change capture
[oracle@oggs ogg]$ ./ggsci
GGSCI (oggs.dba.local) 1> dblogin userid ogg@orcl Password ogg
GGSCI (oggs.dba.local) 2> add checkpointtable ogg.chktab
GGSCI (oggs.dba.local) 3> add trandata OD_CAS.* 代表OD_CAS SCHEMA下都要同步
驗證補充日誌已經在那些TABLE打開
GGSCI (oggs.dba.local) 4> info trandata OD_CAS.*
SQL> select table_name from dba_tables where owner='OD_CAS' minus select table_name from dba_log_groups where owner='OD_CAS';
no rows selected
在TARGET端
啟用transaction data change capture
[oracle@racnode01 ogg]$ ./ggsci
GGSCI (racnode01.dba.local) 5> dblogin userid ogg@orcl Password ogg
GGSCI (racnode01.dba.local) 6> add checkpointtable ogg.chktab
在SOURCE端
配置Goldengate Manager Process
[oracle@oggs ogg]$ ./ggsci
GGSCI (oggs.dba.local) 1> edit params mgr
PORT 7809
DYNAMICPORTLIST 7810-7820, 7830
userid ogg@orcl password ogg
--AUTOSTART ER *
--AUTORESTART ER *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS /u01/app/goldengate/ogg/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3
PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5
PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5
啟動Manager
GGSCI (oggs.dba.local) 2> start mgr
在TARGET端
配置Goldengate Manager Process
[oracle@racnode01 ogg]$ ./ggsci
GGSCI (racnode01.dba.local) 2> edit params mgr
PORT 7809
DYNAMICPORTLIST 7810-7820, 7830
userid ogg@orcl password ogg
--AUTOSTART ER *
--AUTORESTART ER *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS /u01/app/goldengate/ogg/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3
PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5
PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5
啟動Manager
GGSCI (racnode01.dba.local) 3> start mgr
在SOURCE端
配置EXTRACT
GGSCI (oggs.dba.local) 2> edit params e_odcas
extract e_odcas
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
setenv (ORACLE_HOME="/u01/app/oracle/product/11.2.0.4/dbhome_1")
--setenv (ORACLE_SID="orcl")
userid ogg@orcl, password ogg
--tranlogoptions dblogreader asmuser sys@sam2, asmpassword sss433
--tranlogoptions altarchivelogdest threadid 1 F:\oracle\oradata\Archivelog,
--altarchivelogdest threadid 2 F:\oracle\oradata\Archivelog
exttrail /u01/app/goldengate/ogg/dirdat/E1
ddl include mapped;
ddloptions ADDTRANDATA, RETRYOP retrydelay 10 maxretries 10, getreplicates
discardfile /u01/app/goldengate/ogg/dirrpt/e_odcas.dis, purge, megabytes 10
dboptions ALLOWUNUSEDCOLUMN
FETCHOPTIONS FETCHPKUPDATECOLS
table od_cas.*;
SEQUENCE od_cas.*
在SOURCE端
新增一個Extract group
GGSCI (oggs.dba.local) 3> add extract e_odcas, tranlog, begin now
--P.S.threads參數是指RAC節點數 add extract e_odcas, tranlog, threads 2, begin now
在SOURCE端
定義e_odcas trail file路徑
GGSCI (oggs.dba.local) 4> add exttrail /u01/app/goldengate/ogg/dirdat/E1, extract e_odcas, megabytes 100
在SOURCE端
配置 pump process group
GGSCI (oggs.dba.local) 5> edit params p_odcas
extract p_odcas
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
setenv (ORACLE_HOME="/u01/app/oracle/product/11.2.0.4/dbhome_1")
rmthost racnode.dba.local, mgrport 7809
rmttrail /u01/app/goldengate/ogg/dirdat/R1
passthru
table od_cas.*;
SEQUENCE od_cas.*
在SOURCE端
新增一組data pump名稱為p_odcas
GGSCI (oggs.dba.local) 6> add extract p_odcas, exttrailsource /u01/app/goldengate/ogg/dirdat/E1, begin now
在SOURCE端
將p_odcas加入remote trail
GGSCI (oggs.dba.local) 7> add rmttrail /u01/app/goldengate/ogg/dirdat/R1, extract p_odcas, megabytes 200
在SOURCE端
啟動extract及pump
GGSCI (oggs.dba.local) 8> start extract e_odcas
GGSCI (oggs.dba.local) 9> start extract p_odcas
在TARGET端配置replicat
GGSCI (racnode01.dba.local) 5> edit params r_odcas
replicat r_odcas
setenv (ORACLE_HOME= "/u01/app/oracle/product/11.2.0.4/dbhome_1")
setenv (ORACLE_SID=orcl)
setenv (NLS_LANG="AMERICAN_TAIWAN.AL32UTF8")
userid ogg@orcl, password ogg
assumetargetdefs
APPLYNOOPUPDATES
BATCHSQL
--reperror (default,discard)
--reperror 1403, ignore
discardfile /u01/app/goldengate/ogg/dirrpt/r_odcas.dsc,purge,megabytes 100
map od_cas.*, target od_cas.*;
在TARGET端
新增一個Replicat process
GGSCI (racnode01.dba.local) 6> add replicat r_odcas, exttrail /u01/app/goldengate/ogg/dirdat/R1, checkpointtable ogg.chktab
在SOURCE端初始化資料(Initial Load)
取得SOURCE端資料庫SCN
SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1016207
在SOURCE端匯出資料
expdp system/Aa1234567@orcl directory=dumpfile dumpfile=exp_full.dmp logfile=exp_full.log schemas=od_cas FLASHBACK_SCN=1016207
在TARGET端匯入
impdp system/Aa1234567@orcl1 directory=dumpfile dumpfile=exp_full.dmp logfile=imp_full.log
在TARGET端
啟動replicat processes
start replicat R_ODCAS, aftercsn 1016207
檢查GOLDENGATE是否正常
檢查重點
1. 所有服務的STATUS皆須為RUNNING
2. Lag at Chkpt需小於10秒
3. Time Since Chkpt 需小於10秒
在SOURCE端
GGSCI (oggs.dba.local) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_ODCAS 00:00:00 00:00:01
EXTRACT RUNNING P_ODCAS 00:00:00 00:00:03
在TARGET端
GGSCI (racnode01.dba.local) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING R_ODCAS 00:00:00 00:00:02
沒有留言:
張貼留言