GoldenGate configuration (3) DDL replication Configuration

GoldenGate configuration (iii) DDL replication configuration [description] This article is continued. Previous Article: GoldenGate configuration (ii) bidirectional replication configuration click to open the link environment: itemsourcesystemtargetsystemplatformredhatenterpriselinuxserverrelease5.4redhatenterpriselinuxserver

DDL replication configuration in GoldenGate configuration (3) [description] This article is continued. Previous Article: bidirectional replication configuration in GoldenGate configuration (2) Click to open the link environment: item Source System Target System Platform Red Hat Enterprise Linux Server release 5.4 Red Hat Enterprise Linux Server release

GoldenGate configuration (3) DDL replication Configuration

[Note] This article is continued in the previous article: "GoldenGate configuration (2) bidirectional replication configuration" click to open the link

Environment:

Item	Source System	Target System
Platform	Red Hat Enterprise Linux Server release 5.4	Red Hat Enterprise Linux Server release 5.4
Hostname	Gc1	Gc2
Database	Oracle 10.2.0.1	Oracle 11.2.0.1
Character Set	ZHS16GBK	ZHS16GBK
ORACLE_SID	PROD	EMREP
Listener Name/Port	LISTENER/1521	LISTENER/1521
Goldengate User	Ogg	Ogg

DDL replication configuration gc1: Execute the script (Note: it should be executed in the/u01/app/ogg directory; otherwise it will be hang, oracle bug)

SQL>@ Marker_setup. SQL;-- Log on to the/u01/app/ogg directory and run sqlplus

Marker setup script

You will be prompted for the name of a schema forthe GoldenGate database objects.

NOTE: The schema must be created prior to runningthis script.

NOTE: Stop all DDL replication before startingthis installation.

Enter GoldenGate schema name:Ogg

Marker setup table script complete, runningverification script...

Please enter the name of a schema for theGoldenGate database objects:

Setting schema name to OGG

MARKER TABLE

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

OK

MARKER SEQUENCE

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

OK

Script complete.

SQL>Alter system set recyclebin = off scope = spfile; -- disable the recycle bin

SQL>Startup force; -- restart the database

SQL>@ Ddl_setup

GoldenGateDDL Replication setup script

Verifyingthat current user has privileges to install DDL Replication...

You willbe prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: OnOracle 10g and up, system recycle bin must be disabled.

NOTE: Stop all DDL replication before starting this installation.

EnterGoldenGate schema name:Ogg

You willbe prompted for the mode of installation.

Toinstall or reinstall DDL replication, enter INITIALSETUP

Toupgrade DDL replication, enter NORMAL

Entermode of installation:INITIALSETUP

Working, please wait...

Spoolingto file ddl_setup_spool.txt

UsingOGG as a GoldenGate schema name, INITIALSETUP as a mode of installation.

Working, please wait...

RECYCLEBINmust be empty.

Thisinstallation will purge RECYCLEBIN for all users.

To proceed, enter yes. To stop installation, enter no.

Enteryes or no:Yes

SQL>@ Role_setup

GGS Role setup script

This script will drop and recreate the roleGGS_GGSUSER_ROLE

To use a different role name, quit this scriptand then edit the params. SQL script to change the gg_role parameter to thepreferred name. (Do not run the script .)

You will be prompted for the name of a schema forthe GoldenGate database objects.

NOTE: The schema must be created prior to runningthis script.

NOTE: Stop all DDL replication before startingthis installation.

Enter GoldenGate schema name:Ogg

Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.

Role setup script complete

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

GRANT GGS_GGSUSER_ROLE

Where Is the user assigned tothe GoldenGate processes.

SQL>Grant GGS_GGSUSER_ROLE to ogg;

SQL>@ Ddl_enable

Trigger altered.

Gc2: Execute the script (same as that on gc1)

SQL>Alter system set recyclebin = off scope = spfile;-- Disable the recycle bin

SQL>Startup force; -- restart the database

SQL>@ Marker_setup

SQL>@ Ddl_setup

SQL>@ Role_setup

SQL>Grant GGS_GGSUSER_ROLE to ogg;

SQL>@ Ddl_enable

Gc1: configure the Extract process

GGSCI (gc1) 3>STOP EORA_1

Sending STOP request to MANAGER...

Request Processed.

GGSCI (gc1) 4>Edit params EORA_1

Add the red part:

-- Change Capture parameter file to capture

-- TCUSTMER and TCUSTORD changes

EXTRACT EORA_1

SETENV (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)

USERID ogg, PASSWORD Welcome1

EXTTRAIL./dirdat/aa

Ddl include objname "scott .*"

TABLE scott. TCUSTMER;

TABLE scott. TCUSTORD;

GGSCI (gc1) 5>START EORA_1

Sending START request to MANAGER...

EXTRACT EINI_1 starting

Gc2: Configure extract

GGSCI (gc2) 3>Stop EORA_1 -- disable the EORA_1 Process

Sending STOP request to MANAGER...

Request Processed.

GGSCI (gc2) 4>Edit params EORA_1 -- add the red part

-- Change Capture parameter file to capture

-- TCUSTMER and TCUSTORD changes

EXTRACT EORA_1

SETENV (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)

USERID ogg, PASSWORD Welcome1

EXTTRAIL./dirdat/aa

Ddl include objname "scott .*"

TABLE scott. TCUSTMER;

TABLE scott. TCUSTORD;

GGSCI (gc2) 5>START EORA_1

Sending START request to MANAGER...

EXTRACT EINI_1 starting

Gc1: configure the Replicat Process

GGSCI (gc1) 9>Stop RORA_1

Sending STOP request to REPLICAT RORA_1...

Request processed.

GGSCI (gc1) 10>Info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

Extract running EORA_1 00:00:00 00:00:07

Extract running PORA_1 00:00:00 00:00:02

Replicat stopped RORA_1 00:00:00 00:00:31

GGSCI (gc1) 11>Edit params RORA_1 -- add the red part

DDLERROR DEFAULT IGNORE RETRYOP

--

-- Change Delivery parameter file to apply

-- TCUSTMER and TCUSTORD Changes

--

REPLICAT RORA_1

SETENV (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD Ogg

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE./dirrpt/RORA_aa.DSC, PURGE

DDL INCLUDE ALL

Ddlerror default ignore retryop maxretries 3 RETRYDELAY 5

DDLERROR DEFAULT DISCARD

DDLERROR DEFAULT IGNORE RETRYOP

MAP scott. tcustmer, TARGET scott. tcustmer;

MAP scott. tcustord, TARGET scott. tcustord;

~

"Dirprm/rora_1.prm" 16L, 458C written

GGSCI (gc1) 12>Start RORA_1

Sending START request to MANAGER...

REPLICAT RORA_1 starting

GGSCI (gc1) 13>Info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

Extract running EORA_1 00:00:00 00:00:06

Extract running PORA_1 00:00:00 00:00:02

Replicat running RORA_1 00:00:00 00:00:02

Gc2: configure the Replicate Process

Configuration process, same as gc1

Verify ddl: create operation

Gc1:

SQL>Create table test1 as select * from emp;

Table created.

Gc2:

SQL>Select * from test1;

EMPNOENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 smith clerk 7902 17-DEC-80 800 20

7499 allen salesman 7698 20-FEB-81 1600 300 30

......

7902 ford analyst 7566 03-DEC-81 3000 20

7934 miller clerk 7782 23-JAN-82 1300 10

14 rows selected.

Gc2:

SQL>Create table test2 as select * from emp;

Table created.

Gc1:

SQL>Select * from test2;

EMPNOENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 smith clerk 7902 17-DEC-80 800 20

7499 allen salesman 7698 20-FEB-81 1600 300 30

......

7902 ford analyst 7566 03-DEC-81 3000 20

7934 miller clerk 7782 23-JAN-82 1300 10

14 rows selected.

Verify ddl: alter operation

Gc1:

SQL>Alter table test1 rename column mgr to manager;

Table altered.

Gc2:

SQL>Select * from test1;

EMPNOENAME JOB MANAGER HIREDATE SAL COMM DEPTNO

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

7369 smith clerk 7902 17-DEC-80 800 20

7499 allen salesman 7698 20-FEB-81 1600 300 30

7521 ward salesman 7698 22-FEB-81 1250 500 30

......

Gc2:

SQL>Alter table test1 rename column manager to mgr;

Table altered.

Gc1:

SQL>Select * from test1;

EMPNOENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 smith clerk 7902 17-DEC-80 800 20

7499 allen salesman 7698 20-FEB-81 1600 300 30

7521 ward salesman 7698 22-FEB-81 1250 500 30

7566 jones manager 7839 02-APR-81 2975 20

......

Verify ddl: drop operation

Gc2:

SQL>Select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

TCUSTMER TABLE

TCUSTORD TABLE

TEST1 TABLE

TEST2 TABLE

8 rows selected.

Gc1:

SQL>Drop table test1;

Table dropped.

Gc2:

SQL>Select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

TCUSTMER TABLE

TCUSTORD TABLE

TEST2 TABLE

7 rows selected.

Gc1:

SQL>Select * from tab;

TNAME TABTYPE CLUSTERID

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

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

TCUSTMER TABLE

TCUSTORD TABLE

TEST2 TABLE

7 rows selected.

Gc2:

SQL>Drop table test2;

Table dropped.

SQL>Select * from tab;

TNAME TABTYPE CLUSTERID

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

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

TCUSTMER TABLE

TCUSTORD TABLE

6 rows selected.

Statement:
Original works, from "Deep Blue blog" blog, allow reprint, reprint please be sure to indicate the source (http://blog.csdn.net/huangyanlong ).

The author has the right to pursue legal liability for copyright issues.