OGG单向复制(支持DDL)

1.配置环境变量(源端、目标端)

---源端

exportORACLE_SID=test01

exportNLS_LANG=AMERICAN_AMERICA.ZHS16GBK

exportORACLE_HOME=/oracle/app/oracle/product/11.2.4/db_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

 

--目标端

exportORACLE_SID=test02

exportNLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

exportORACLE_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='location=/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;

Databasealtered.

SQL>select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME

--------

YES

对于10G以上版本还需要关闭回收站功能(需重启生效)

sql>altersystem set recyclebin =off scope=spfile;

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

NAME                           VALUE

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

recyclebin                     on

 

---目标端

(同源端)

3.数据库建用户(源端,目标端)

 

---源端 

---首先创建数据库用户

 

create user GOLDENGATE

 identified by GOLDENGATE

 default tablespace USERS

 temporary tablespace TEMP

 profile DEFAULT

 account unlock;

 

alter user GOLDENGATE quota UNLIMITED onUSERS ;

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 toGOLDENGATE ;

grant RESOURCE to GOLDENGATE ;

grant CONNECT to GOLDENGATE ;

GRANTCREATE TABLE,CREATE SEQUENCE TO goldengate;

alter user GOLDENGATE default role all; 

 

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

Successfully logged into database.

GGSCI (test01) 2> exit

 

---目标端

(同源端)

4.配置ddl支持

--源端

SQL> grantexecute 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;

GRANTCREATE TABLE,CREATE SEQUENCE TO goldengate

ERRORat line 1:

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

SYS@testdb>@ddl_disable.sql 

Triggeraltered.

SYS@testdb>  GRANT CREATE TABLE,CREATE SEQUENCE TOgoldengate;

Grantsucceeded.

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 successfullycompleted.

 

5.配置源端

5.1配置全局变量

./ggsci

GGSCI(test01) 1> create subdirs   ---创建软件相关子目录

GGSCI (test01)2> EDIT PARAMS ./GLOBALS

CHECKPOINTTABLEgoldengate.gg_checkpoint_tab

ggschemagoldengate

syslog none

5.2创建检查点表

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

ADDCHECKPOINTTABLE goldengate.gg_checkpoint_tab

5.3配置mgr

edit param mgr，输入如下信息：

 

port7809

DYNAMICPORTLIST7809-7909

AUTOSTARTer *

AUTORESTARTEXTRACT *,RETRIES 20,WAITMINUTES 30 , RESETMINUTES 1440

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

 

LAGREPORTHOURS1

LAGINFOMINUTES30

LAGCRITICALMINUTES45

 

GGSCI (test01) 1>start mgr

5.4源端配置抽取进程

GGSCI(test01) 1> edit param ETEST01

 

extractETEST01

setenv(NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)

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

setenv(ORACLE_SID = test01)

useridgoldengate, password goldengate

REPORTAT 01:59

REPORTROLLOVERat 02:00

GETTRUNCATES

EXTTRAIL./dirdat/exp

NUMFILES2000

DYNAMICRESOLUTION

DDLINCLUDE MAPPED

DDLOPTIONSADDTRANDATA RETRYOP RETRYDELAY 10 MAXRETRIES 10                     

DDLOPTIONSREPORT

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

TABLEDEMO.CALL_RULE_INFO;

 

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

extractPTEST01

setenv(NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)

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

setenv(ORACLE_SID = testdb)

useridgoldengate, password goldengate

PASSTHRU

REPORTAT 01:59

REPORTROLLOVERat 02:00

RMTHOST10.18.4.41, MGRPORT 7809, COMPRESS 

RMTTRAIL./dirdat/rep

DYNAMICRESOLUTION

NUMFILES2000

gettruncates

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

TABLEDEMO.CALL_RULE_INFO;

 

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.CALL_RULE_INFO,TARGETTEST.CALL_RULE_INFO;

 

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