DBA Hub

📋Steps in this guide1/6

Enable DDL replication in oracle goldengate DBACLASS

In this below tutorial, we will enable DDL replication for a table, which is already part of  goldengate replication and currently only DML transactions are getting replicated. TABLE_NAME = APPLIANCE.CLASSTAB1 To enable DDL replication, We need to run few SQL scripts(provided under $GG_HOME)  on the source database. and update the extract parameter file. 1. Run role_setup.sql where […]

oracle replicationintermediate
by OracleDba
15 views
1

1. Run role_setup.sql where extract is running [ SOURCE ]

conn / as sysdba

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
cd $GG_HOME

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_USER
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 

where  is the user assigned to the GoldenGate processes.
SQL>
2

2. Grant the role to GATE_USER.[SOURCE]

Code/Command (click line numbers to comment):

1
2
3
SQL>  GRANT GGS_GGSUSER_ROLE TO GATE_USER;

Grant succeeded.
3

3.Run the marker script:[SOURCE]

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
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_USER


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_USER

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

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

Script complete.
4

4. Run ddl_setup.sql script [ SOURCE ]

@ddl_setup.sql

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
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_USER

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_USER as a Oracle GoldenGate schema name.

Working, please wait ...

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_USER

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
------------------------------------------------------------------------------------------------------------------------
NONE

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/log/ggs_ddl_trace.log

Analyzing installation status...


VERSION OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401

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

Script complete.
5

5. Update the extract parameter file to include DDL

We need to add the command DDL INCLUDE MAPPED OBJNAME <SCHEMA_NAME>.<TABLE_NAME> It will look as below No changes required to extract pump and replicat parameter files.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
ggsci> edit params ext1

EXTRACT EXT1
SETENV (ORACLE_SID="SRCDB")
SETENV (ORACLE_HOME = "/oracle/app/oracle/product/12.1.0/dbhome_1")
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
userid ggate, PASSWORD ggate123
FETCHOPTIONS  FETCHPKUPDATECOLS, USESNAPSHOT, USELATESTVERSION
DBOPTIONS ALLOWUNUSEDCOLUMN
DISCARDFILE /ggate/app/oracle/product/12.2.1.0.3/TST/EXT1.dsc, APPEND, MEGABYTES 100
EXTTRAIL /ggate/app/oracle/product/12.2.1.0.3/TST/E5
DDL INCLUDE MAPPED OBJNAME APPLIANCE.CLASSTAB1
TABLE APPLIANCE.CLASSTAB1;
6

6. Restart the extract [ SOURCE]

Now DDL replication is enabled. Do some DDL changes on source. The column has been added in target database also.

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
gggsci> stop ext1

ggsci> start ext1

GGSCI> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:03
EXTRACT     RUNNING     EXT1P       00:00:00      00:00:01

-- DDL change on source


SQL>  alter table appliance.classtab1 add ( SCHOOL varchar2(20));

Table altered.



SQL>  desc appliance.classtab1
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------
 SR                                                 NUMBER
 CLASS                                              VARCHAR2(20)
 SCHOOL                                             VARCHAR2(20)

-- Check the status of extract:

 GGSCI () 18> stats EXT1

 Sending STATS request to EXTRACT EXT1 ...

Start of Statistics at 2017-07-25 10:50:03.

DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                         1.00
        Mapped operations                                  1.00 ---- >>>>>>>>>>>>>>>>>> DDL CHANGE CAPTURES BY EXTRACT.
        Unmapped operations                                0.00
        Other operations                                   0.00
        Excluded operations                                0.00

Output to /GGATE_USER/TRAILFILE/S2:
 

-- Check the table in target db( replicat side)

SQL> desc appliance.classtab1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SR                                                 NUMBER
 CLASS                                              VARCHAR2(20)
 SCHOOL                                             VARCHAR2(20)

Comments (0)

Please to add comments

No comments yet. Be the first to comment!