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