DBA Hub

📋Steps in this guide1/18

AIM:ONE-WAY DDL replication between an Oracle 10gR2 database and an Oracle 11gR2 database.

AIM: ONE-WAY DDL replication between an Oracle 10gR2 database and an Oracle 11gR2 database. 1. Pre-requisites 2. Stop GoldenGate Process on Source Oracle 10gR2 3. Stop GoldenGate Process on Target Oracle 11gR2 4. Configure DDL setup on Source Oracle 10gR2       A) Turn off the Oracle Recycle Bin       B) Grant GRANT EXECUTE … Continue reading DDL Replication →

oracle replicationintermediate
by OracleDba
12 views
1

Overview

AIM: 1. Pre-requisites 2. Stop GoldenGate Process on Source Oracle 10gR2

Code/Command (click line numbers to comment):

1
2
3
http://www.br8dba.com/unidirectional-dml-replication/
OR
http://www.br8dba.com/zero-downtime-migration/
2

Section 2

3. Stop GoldenGate Process on Target Oracle 11gR2 4. Configure DDL setup on Source Oracle 10gR2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
GGSCI (rac1.rajasekhar.com) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     E10G        57:25:09      00:00:02
EXTRACT     RUNNING     P10G        00:00:00      57:25:04


GGSCI (rac1.rajasekhar.com) 5>
GGSCI (rac1.rajasekhar.com) 9>
stop extract P10G
Sending STOP request to EXTRACT P10G ...
Request processed.


GGSCI (rac1.rajasekhar.com) 10>
stop extract E10G
Sending STOP request to EXTRACT E10G ...
Request processed.


GGSCI (rac1.rajasekhar.com) 11>
stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.


GGSCI (rac1.rajasekhar.com) 12>

GGSCI (rac1.rajasekhar.com) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
JAGENT      STOPPED
REPLICAT    RUNNING     R11G        00:00:00      00:00:01

GGSCI (rac1.rajasekhar.com) 5>

GGSCI (rac1.rajasekhar.com) 6>
stop REPLICAT R11G
Sending STOP request to REPLICAT R11G ...
Request processed.


GGSCI (rac1.rajasekhar.com) 7>
stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.


GGSCI (rac1.rajasekhar.com) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER
STOPPED
JAGENT      STOPPED
REPLICAT
STOPPED
R11G        00:00:00      00:00:09

GGSCI (rac1.rajasekhar.com) 9>
3

Section 3

A) Turn off the Oracle Recycle Bin. *** Recycle Bin can be ON is only for Oracle 11g or above B) Grant GRANT EXECUTE ON UTL_FILE Privilege to goldengate user

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
*** Recycle Bin can be ON is only for Oracle 11g or above
SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
DB10      READ WRITE

SQL>
alter system set recyclebin=off scope=both;
System altered.

SQL>
4

Section 4

C) Run marker_setup script D) Run ddl_setup script

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SQL>
GRANT EXECUTE ON UTL_FILE TO GATE;
Grant succeeded.

SQL>

[oracle@rac1
10gogg
]$ sqlplus / as sysdba

SQL>
@marker_setup.sql;
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:
gate  <---
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GATE

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

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

Script complete.
SQL>
5

Section 5

NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled. E) Run role_setup script This script will drop and recreate the role GGS_GGSUSER_ROLE

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
[oracle@rac1 ~]$
cd /u01/app/oracle/product/10gogg/
[oracle@rac1 10gogg]$ sqlplus / as sysdba

SQL>
@ddl_setup.sql;
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:
gate <----
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 GATE 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 GATE

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
----------------------------------------------------------------
/u01/app/oracle/product/10g/admin/DB10/udump/ggs_ddl_trace.log

Analyzing installation status...


STATUS OF DDL REPLICATION
----------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL>

[oracle@rac1 ~]$ cd /u01/app/oracle/product/10gogg/
[oracle@rac1 10gogg]$ sqlplus / as sysdba

SQL>
@role_setup.sql;
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:
gate <-----
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  is the user assigned to the GoldenGate processes.
SQL>

-- Run below command as advised by above script

SQL>
GRANT GGS_GGSUSER_ROLE TO gate;  <----
Grant succeeded.

SQL>
6

Section 6

F) Run the ddl_enable script G) Run ddl_pin script

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[oracle@rac1 ~]$ cd /u01/app/oracle/product/10gogg/
[oracle@rac1 10gogg]$ sqlplus / as sysdba

SQL>
@ddl_enable.sql;
Trigger altered.

SQL>

[oracle@rac1 ~]$ cd /u01/app/oracle/product/10gogg/
[oracle@rac1 10gogg]$ sqlplus / as sysdba

SQL>
@ddl_pin.sql
gate
<-----
PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL>
7

Section 7

H) Edit Extract (E10G) 5. Configure DDL setup on Target Oracle 11gR2 A) Run role_setup.sql

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
GGSCI (rac1.rajasekhar.com) 3>
edit params R11G
-- Add below parameters
ddl include mapped objname cds.*
DDLOPTIONS ADDTRANDATA REPORT
GGSCI (rac1.rajasekhar.com) 4>
view params E10G
EXTRACT E10G
EXTTRAIL ./dirdat/ea
USERID gate, PASSWORD gate
TABLE CDS.*;
-- Enable DDL
ddl include mapped objname cds.*
DDLOPTIONS ADDTRANDATA REPORT
GGSCI (rac1.rajasekhar.com) 5>
8

Section 8

A) Run role_setup.sql This script will drop and recreate the role GGS_GGSUSER_ROLE B) Grant DBA to GATE

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- conn / as sysdba

SQL>
@role_setup.sql
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:gate
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  is the user assigned to the GoldenGate processes.
SQL>

SQL>
GRANT GGS_GGSUSER_ROLE TO GATE; <---
Grant succeeded.

SQL>

SQL>
grant dba to gate;
Grant succeeded.

SQL>
9

Section 9

C) ENABLE_GOLDENGATE_REPLICATION to TRUE D) Edit Replicat R11G

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SQL>
show parameter ENABLE_GOLDENGATE_REPLICATION
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------                                                                
enable_goldengate_replication        boolean
FALSE <----
SQL>
alter system set enable_goldengate_replication=TRUE;
System altered.

SQL>

-- Add below parameters

-- DDL Report
DDLOPTIONS REPORT

GGSCI (rac1.rajasekhar.com) 52>
view params r11g
REPLICAT R11G
ASSUMETARGETDEFS
userid gate, password gate
DISCARDFILE ./dirout/receiver.dsc, purge
MAP CDS.*, TARGET CDS.*;
-- DDL Report
DDLOPTIONS REPORT
GGSCI (rac1.rajasekhar.com) 53>
10

Section 10

6. Start GoldenGate Process on Source Oracle 10gR2 7. Start GoldenGate Process on Target Oracle 11gR2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
GGSCI (rac1.rajasekhar.com) 5>
start mgr
Manager started.

GGSCI (rac1.rajasekhar.com) 6>
start extract E10G
Sending START request to MANAGER ...
EXTRACT E10G starting

GGSCI (rac1.rajasekhar.com) 7>
start extract P10G
Sending START request to MANAGER ...
EXTRACT P10G starting

GGSCI (rac1.rajasekhar.com) 8>
info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER
RUNNING
EXTRACT
RUNNING
E10G        06:57:03      00:00:07
EXTRACT
RUNNING
P10G        00:00:00      06:57:13

GGSCI (rac1.rajasekhar.com) 9>

GGSCI (rac1.rajasekhar.com) 5>
start mgr
Manager started.

GGSCI (rac1.rajasekhar.com) 6>
start replicat R11G
Sending START request to MANAGER ...
REPLICAT R11G starting

GGSCI (rac1.rajasekhar.com) 7>
info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER
RUNNING
JAGENT      STOPPED
REPLICAT
RUNNING
R11G        00:00:00      00:00:02

GGSCI (rac1.rajasekhar.com) 8>
11

Section 11

8. Verify LIST AVAILABLE TABLES CURRENTLY a1) List tables on Source 10gR2
12

Section 12

a2) List tables on Target 11gR2 CREATE TABLE OPERATION

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL>
select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
TEST                           TABLE

SQL>

SQL>
select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
TEST                           TABLE

SQL>
13

Section 13

b1) Execute CREATE TABLE on the Source Oracle Database 10gR2 b2) Verify CREATE TABLE Changes on Target Oracle Database 11gR2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
SQL>
create table b as select * from a;
Table created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
TEST                           TABLE
B                              TABLE <-----
SQL>

SQL>
select count(*) from b;
COUNT(*)
----------
     50605  <----

SQL>

SQL>
select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
B                              TABLE <-----
TEST                           TABLE

SQL>
select count(*) from b;
COUNT(*)
----------
     50605 <---

SQL>
14

Section 14

ALTER TABLE OPERATION c1) Execute ALTER TABLE on the Source Oracle Database 10gR2 ALTER TABLE OPERATION c1) Execute ALTER TABLE on the Source Oracle Database 10gR2 c2) Verify ALTER TABLE Changes on Target Oracle Database 11gR2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
SQL>
desc test
Name           Null?    Type
 -------------- -------- -------------
 NAME           NOT NULL VARCHAR2(10)
 ROLE                    VARCHAR2(10)

SQL>
ALTER TABLE TEST ADD EMPNO VARCHAR2(30);
Table altered.

SQL>
15

Section 15

DROP TABLE OPERATION d1) Execute DROP TABLE on the Source Oracle Database 10gR2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL>
desc test;
Name           Null?    Type
 -------------- -------- -------------
 NAME           NOT NULL VARCHAR2(10)
 ROLE                    VARCHAR2(10)
EMPNO                   VARCHAR2(30) <---
SQL>

SQL>
drop table b;
Table dropped.

SQL>
select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
TEST                           TABLE

SQL>
16

Section 16

d2) Verify DROP TABLE Changes on Target Oracle Database 11gR2 TRUNCATE TABLE OPERATION e1) Execute TRUNCATE TABLE on the Source Oracle Database 10gR2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL>
select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
BIN$QnU5q0BiTuPgU2UCqMCWyg==$0 TABLE <----
TEST                           TABLE

SQL>

SQL>
show parameter recyclebin
NAME         TYPE        VALUE
------------ ----------- -------
recyclebin   string
on <-----
SQL>
17

Section 17

e1) Execute TRUNCATE TABLE on the Source Oracle Database 10gR2 e2) Verify TRUNCATE TABLE Changes on Target Oracle Database 11gR2 Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL>
truncate table test;
Table truncated.

SQL>
select count(*) from test;
COUNT(*)
----------
         0
<----
SQL>

SQL>
select count(*) from test;
COUNT(*)
----------
         0
<----
SQL>
18

Section 18

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using. Reference: https://blogs.oracle.com/imc/entry/oracle_goldengate_configuring_ddl_replication http://www.vitalsofttech.com/configure-goldengate-ddl-replication/ http://goldengatereplication.blogspot.in/2011/12/steps-for-enabling-ddl-replication-with.html http://www.askmaclean.com/archives/goldengate-best-parameters-test-from-maclean.html http://gavinsoorma.com/2014/06/adding-new-tables-to-a-goldengate-extract-and-using-the-tableexclude-parameter/ https://www.pythian.com/blog/truncates-sequences-replication-oracle-goldengate/ http://anuj-singh.blogspot.in/2014/11/ora-04098-trigger-sysggsddltriggerbefor.html http://www.oracle-scn.com/ddl-replication-configuration-of-oracle-goldengate-11g/ http://www.juliandyke.com/Research/GoldenGate/GoldenGateDDLReplication.php

Comments (0)

Please to add comments

No comments yet. Be the first to comment!