OGG單向複製(支援DDL)

1. 配置環境變數(源端、目標端)

---源端

export ORACLE_SID=test01

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export ORACLE_HOME=/oracle/app/oracle/product/11.2.4/db_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

--目標端

export ORACLE_SID=test02

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export ORACLE_HOME=/oracle/app/oracle/product/11.2.4/db_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

 

注意:標紅部分是跟ogg的環境變數有關

2.開啟歸檔和附加日誌(源端,目標端)

2.1開啟歸檔

---源端

sql> alter system set log_archive_dest='/oracle/arch' scope=spfile;

sql> shutdown immediate;

sql> startup mount;

sql> alter database archivelog;

sql> alter database open;

sql> archive log list;

---目標端

(同源端)

2.2開啟附加日誌

---源端

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME

--------

NO

SQL> alter database add supplemental log data;

Database altered.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME

--------

YES

對於10G版本還需要關閉回收站功能(需重啟生效)

sql>alter system set recyclebin =off scope=spfile;

SQL> select NAME,VALUE from  v$parameter where name like '%recyclebin%';

NAME                           VALUE

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

recyclebin                     off

 

---目標端

(同源端)

3.資料庫建使用者(源端,目標端)

---源端 

---首先建立資料庫使用者

 

create user GOLDENGATE

 identified by GOLDENGATE

 default tablespace USERS

 temporary tablespace TEMP

 profile DEFAULT

 account unlock;

 

alter user GOLDENGATE quota UNLIMITED on USERS ;

grant SELECT ANY TABLE to GOLDENGATE;

grant FLASHBACK ANY TABLE to GOLDENGATE;

grant SELECT ANY DICTIONARY to GOLDENGATE;

grant UPDATE ANY TABLE to GOLDENGATE;

grant DELETE ANY TABLE to GOLDENGATE;

grant INSERT ANY TABLE to GOLDENGATE;

grant UNLIMITED TABLESPACE to GOLDENGATE;

grant ALTER SESSION to GOLDENGATE;

grant ALTER ANY TABLE to GOLDENGATE;

grant CREATE SESSION to GOLDENGATE;

grant EXECUTE on SYS.DBMS_FLASHBACK to GOLDENGATE ;

grant RESOURCE to GOLDENGATE ;

grant CONNECT to GOLDENGATE ;

GRANT CREATE TABLE,CREATE SEQUENCE TO goldengate;

alter user GOLDENGATE default role all; 

 

GGSCI (test01) 1> dblogin userid goldengate@test01,password goldengate

Successfully logged into database.

GGSCI (test01) 2> exit

 

---目標端

(同源端)

4.配置ddl支援(源端,目標端)

--源端

SQL> grant execute on utl_file to goldengate;

SQL> @marker_setup.sql

SQL> @ddl_setup.sql

SQL> @role_setup.sql

SQL> GRANT GGS_GGSUSER_ROLE TO goldengate;

SQL> @?/rdbms/admin/dbmspool

SQL >@ddl_pin goldengate;

 

注意:

1.@ddl_setup.sql

這一步可能報錯,跟goldengate使用者的許可權有關,然後重新授權會報ORA-04098系統錯誤,需要執行

@ddl_disable.sql後再授權.

 

SYS@testdb> GRANT CREATE TABLE,CREATE SEQUENCE TO goldengate;

GRANT CREATE TABLE,CREATE SEQUENCE TO goldengate

ERROR at line 1:

ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation

SYS@testdb> @ddl_disable.sql 

Trigger altered.

SYS@testdb>  GRANT CREATE TABLE,CREATE SEQUENCE TO goldengate;

Grant succeeded.

2.效能提升工具

--為了提供OGG的DDL複製的效能，可以將ddl_pin指令碼加入到資料庫啟動的指令碼後面，該指令碼需要帶一個OGG的DDL使用者（即安裝DDL物件的使用者，本例中是goldengate）的引數，指令碼如果不能正常執行，需要事先建立DBMS_SHARED_POOL包（可以通過執行$ORACLE_HOME/rdbms/admin/dbmspoll.sql建立）。

--執行以下操作建立效能提升工具

SQL> @ddl_pin goldengate

--如果因為業務問題，開啟DDL以後，對效能的影響比較大的話，需要臨時禁用DDL觸發器的話，可以執行以下語句：

SQL> @ddl_disable.sql

PL/SQL procedure successfully completed.

---目標端(同源端)

5.配置源端

5.1配置全域性變數

./ggsci

GGSCI (test01) 1> create subdirs   ---建立軟體相關子目錄

GGSCI (test01) 2> EDIT PARAMS ./GLOBALS

CHECKPOINTTABLE goldengate.gg_checkpoint_tab

ggschema goldengate

syslog none

5.2建立檢查點表

GGSCI (test01) 3> dblogin userid goldengate , password  goldengate

ADD CHECKPOINTTABLE goldengate.gg_checkpoint_tab

5.3配置mgr

edit param mgr，輸入如下資訊：

 

port 7809

DYNAMICPORTLIST 7809-7909

AUTOSTART er *

AUTORESTART EXTRACT *,RETRIES 20,WAITMINUTES 30 , RESETMINUTES 1440

PURGEOLDEXTRACTS ./dirdat/*/*,USECHECKPOINTS, MINKEEPDAYS 16

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

GGSCI (test01) 1>start mgr

5.4源端配置抽取程式

GGSCI (test01) 1> edit param ETEST01

 

extract ETEST01

setenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)

setenv (ORACLE_HOME = /oracle/app/oracle/product/11.2.4/db_1)

setenv (ORACLE_SID = test01)

userid goldengate, password goldengate

REPORT AT 01:59

REPORTROLLOVER at 02:00

GETTRUNCATES

EXTTRAIL ./dirdat/exp

NUMFILES 2000

DYNAMICRESOLUTION

DDL INCLUDE MAPPED

DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 10 MAXRETRIES 10                     

DDLOPTIONS REPORT

------lizhen-----

TABLE DEMO.TEST_TABLE_FOR_OGG;

 

GGSCI > ADD EXTRACT ETEST01 , TRANLOG, BEGIN NOW, threads 1  ---RAC 注意

GGSCI > ADD EXTTRAIL ./dirdat/exp, EXTRACT ETEST01 , MEGABYTES 200

 

5.5源端配置對應的Data Pump程式

GGSCI (test01) 1> edit param PTEST01

extract PTEST01

setenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)

setenv (ORACLE_HOME = /oracle/app/oracle/product/11.2.4/db_1)

setenv (ORACLE_SID = testdb)

userid goldengate, password goldengate

PASSTHRU

REPORT AT 01:59

REPORTROLLOVER at 02:00

RMTHOST 10.18.4.41, MGRPORT 7809, COMPRESS 

RMTTRAIL ./dirdat/rep

DYNAMICRESOLUTION

NUMFILES 2000

gettruncates

------lizhen-------

TABLE DEMO.TEST_TABLE_FOR_OGG;

 

GGSCI > ADD EXTRACT PTEST01, EXTTRAILSOURCE ./dirdat/exp

GGSCI > ADD RMTTRAIL ./dirdat/rep, EXTRACT PTEST01, MEGABYTES 200

 

 

6. 配置目標端

6.1配置全域性變數

同源端

6.2建立檢查點表

同源端

6.3 配置mgr

同源端

6.4 配置目標端複製程式

GGSCI (test01) 23> view param RTEST01

replicat RTEST01

setenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)

userid goldengate, password goldengate

REPORTCOUNT EVERY 30 MINUTES,RATE

REPORT AT 01:59

REPORTROLLOVER at 02:00

DISCARDROLLOVER on friday

HANDLECOLLISIONS

REPERROR DEFAULT,ABEND

DISCARDFILE ./DIRRPT/RTEST01.DSC,APPEND, MEGABYTES 1000

ASSUMETARGETDEFS

CHECKSEQUENCEVALUE

ALLOWNOOPUPDATES

DYNAMICRESOLUTION

NUMFILES 2000

DDL INCLUDE MAPPED

DDLOPTIONS REPORT

GROUPTRANSOPS 500

MAXTRANSOPS 500

------lizhen--------

MAP DEMO.TEST_TABLE_FOR_OGG,TARGET TEST.TEST_TABLE_FOR_OGG;

 

ggsci >add replicat RTEST01 ,exttrail ./dirdat/rep

 

7. 啟動源端和目標端的ogg程式

---源端

GGSCI (test01) 50> start ETEST01

GGSCI (test01) 51> start PTEST01

 

GGSCI (test01) 53> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

EXTRACT     RUNNING     ETEST01        00:00:00      00:00:01   

EXTRACT     RUNNING     PTEST01        00:00:00      00:00:03   

 

---目標端

GGSCI (test02) 24> start RTEST01

GGSCI (test02) 24> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

REPLICAT    RUNNING     RTEST01        00:00:00      00:00:05   

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29392174/viewspace-2147498/，如需轉載，請註明出處，否則將追究法律責任。