OGG DDL 双向同步

1. 环境说明。
续单向DDL同步。 再把目标端的DDL也给开启来。

2. 确认源库处于归档模式。

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/archivelog
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5

3. 确认源库为FORCE_LOGGIN。

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR
---
YES

4. 确认源库打开辅助日志。

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

5. 源库关闭回收站。

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on
SQL> alter system set recyclebin=off;

System altered.

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      OFF

6.  保证字符集一致。
7. UNDO设置

SQL> alter system set undo_retention=86400;

System altered.

8. 安装DDL_OBJETS

SQL> @marker_setup

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:goldengate


Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GOLDENGATE

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.

执行DDL_STEP
SQL> @ddl_setup

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:goldengate

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.

 

 

 

Using GOLDENGATE as a Oracle GoldenGate schema name.

Working, please wait ...

RECYCLEBIN must be empty.
This installation will purge RECYCLEBIN for all users.
To proceed, enter yes. To stop installation, enter no.

Enter yes or no:yes


DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GOLDENGATE

CLEAR_TRACE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

CREATE_TRACE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

TRACE_PUT_LINE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

INITIAL_SETUP STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL IGNORE TABLE
-----------------------------------
OK

DDL IGNORE LOG TABLE
-----------------------------------
OK

DDLAUX  PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

SYS.DDLCTXINFO  PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL HISTORY TABLE
-----------------------------------
OK

DDL HISTORY TABLE(1)
-----------------------------------
OK

DDL DUMP TABLES
-----------------------------------
OK

DDL DUMP COLUMNS
-----------------------------------
OK

DDL DUMP LOG GROUPS
-----------------------------------
OK

DDL DUMP PARTITIONS
-----------------------------------
OK

DDL DUMP PRIMARY KEYS
-----------------------------------
OK

DDL SEQUENCE
-----------------------------------
OK

GGS_TEMP_COLS
-----------------------------------
OK

GGS_TEMP_UK
-----------------------------------
OK

DDL TRIGGER CODE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
------------------------------------------------------------------------------------------------------------------------
ENABLED

STAYMETADATA IN TRIGGER
------------------------------------------------------------------------------------------------------------------------
OFF

DDL TRIGGER SQL TRACING
------------------------------------------------------------------------------------------------------------------------
0

DDL TRIGGER TRACE LEVEL
------------------------------------------------------------------------------------------------------------------------
0

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/home/oracle/admin/gg1/udump/ggs_ddl_trace.log

Analyzing installation status...


STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.

执行ROLE_SETUP
SQL> @role_setup

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:goldengate
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.


Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

where <loggedUser> is the user assigned to the GoldenGate processes.

将此角色给GOLDENGATE用户。
SQL> grant ggs_ggsuser_role to goldengate;

Grant succeeded.

开启DDL
SQL> @ddl_enable

Trigger altered.

安装性能优化包。
SQL> @?/rdbms/admin/dbmspool

Package created.


Grant succeeded.


View created.


Package body created.

ddl_pin将触发器用到的plsql包放进内存中
SQL> @ddl_pin goldengate

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

9. MGR配置
源端：
增加一行内容：

PURGEOLDEXTRACTS /home/oracle/gg/trails/w2*, USECHECKPOINTS, MINKEEPFILES 10

目标端：
增加一行内容：

PURGEOLDEXTRACTS /home/oracle/gg/trails/w2*, USECHECKPOINTS, MINKEEPFILES 10

10. ./GLOBALS配置（目标端）

GGSCI (GOLDENGATE1) 2> edit params ./GLOBALS
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.chkpoint

GGSCI (GOLDENGATE1) 3> dblogin userid goldengate, password goldengate
Successfully logged into database.


GGSCI (GOLDENGATE1) 5> add checkpointtable goldengate.chkpoint

Successfully created checkpoint table goldengate.chkpoint.

11. 增加EXTRACT
 

add extract w2ext, tranlog, begin now

修改配置：

GGSCI (GOLDENGATE2) 4> edit params w2ext
EXTRACT w2ext
USERID goldengate, PASSWORD goldengate
TRANLOGOPTIONS EXCLUDEUSER goldengate
IGNOREREPLICATES
EXTTRAIL /home/oracle/gg/trails/w2
DISCARDFILE w1extdsc,APPEND,MEGABYTES 5
TRANLOGOPTIONS ALTARCHIVELOGDEST /home/oracle/archivelog
TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_%r.dbf
DDL INCLUDE MAPPED
DDLOPTIONS ADDTRANDATA
FETCHOPTIONS, USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT
STATOPTIONS REPORTFETCH
WARNLONGTRANS 1H, CHECKINTERVAL 5M
TABLE hr.*;

 

添加该extract对应的trail文件，用来存储抽取的数据。单个文件大小设置为100MB

GGSCI (GOLDENGATE2) 5> add exttrail /home/oracle/gg/trails/w2,extract w2ext, megabytes 100
EXTTRAIL added.

12. 修改原源库EXTRAIL参数，增加以下两行：

TRANLOGOPTIONS EXCLUDEUSER goldengate
IGNOREREPLICATES

13. 增加PUMP

add extract w2extdp, exttrailsource /home/oracle/gg/trails/w2, begin now

修改配置：

GGSCI (GOLDENGATE2) 7> edit params w2extdp
EXTRACT w2extdp
USERID goldengate, PASSWORD goldengate
RMTHOST 192.168.102.156, MGRPORT 5898
RMTTRAIL /home/oracle/gg/trails/w2
DISCARDFILE w1extdpdsc,APPEND,MEGABYTES 5
TABLE hr.*;

 

14. 添加远程TRAIL文件：

GGSCI (GOLDENGATE2) 8> add rmttrail /home/oracle/gg/trails/w2, extract w2extdp, megabytes 100
RMTTRAIL added.

15. 增加目标端REP进程

GGSCI (GOLDENGATE1) 11> add replicat w2rep, exttrail /home/oracle/gg/trails/w2, checkpointtable goldengate.chkpoint
REPLICAT added.

修改配置参数：

GGSCI (GOLDENGATE1) 12> edit params w2rep
REPLICAT w2rep
ASSUMETARGETDEFS
USERID goldengate, PASSWORD goldengate
DISCARDFILE w2repdsc,APPEND,MEGABYTES 5
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
BATCHSQL
DBOPTIONS DEFERREFCONST
DBOPTIONS LOBWRITESIZE 102400
DDLERROR DEFAULT DISCARD RETRYOP MAXRETRIES 5 RETRYDELAY 20
MAP hr.* , TARGET hr.* ;

16. 在源端增加传输表。

GGSCI (GOLDENGATE2) 12> dblogin userid goldengate, password goldengate
Successfully logged into database.

GGSCI (GOLDENGATE2) 13> add trandata hr.*

Logging of supplemental redo data enabled for table HR.COUNTRIES.

Logging of supplemental redo data enabled for table HR.DEPARTMENTS.

Logging of supplemental redo data enabled for table HR.EMPLOYEES.

Logging of supplemental redo data enabled for table HR.JOBS.

Logging of supplemental redo data enabled for table HR.JOB_HISTORY.

Logging of supplemental redo data enabled for table HR.LOCATIONS.

Logging of supplemental redo data enabled for table HR.REGIONS.

2012-09-21 02:55:40  WARNING OGG-00869  No unique key is defined for table 'T'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table HR.T.

2012-09-21 02:55:41  WARNING OGG-00869  No unique key is defined for table 'TT'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table HR.TT.

17. 查源库SCN。

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     525516

18. 启动GG各进程。
注意启动REP进程时：

GGSCI (GOLDENGATE1) 19> start w2rep, aftercsn 525516

Sending START request to MANAGER ...
REPLICAT W2REP starting


GGSCI (GOLDENGATE1) 20> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     W1EXT       00:00:00      00:00:08    
EXTRACT     RUNNING     W1EXTDP     00:00:00      00:00:01    
REPLICAT    RUNNING     W2REP       00:00:00      00:00:00   

19， 测试。
源到目标：

SQL> create table t1(id number(9));

Table created.

SQL> insert into t1(id) values(88);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID
----------
        88

目标到源;

SQL> create table t2(id number(9));

Table created.

SQL> insert into t2(id) values(88);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t2;

        ID
----------
        88