DDL Replication

Add DDL support to the extract process

GGSCI (sourcesrv) 17> edit params xhr

GGSCI (sourcesrv) 18> view params xhr

EXTRACT xhr

USERID gg, PASSWORD oracle

EXTTRAIL ./dirdat/hr

DDL INCLUDE MAPPED OBJNAME HR.*

TABLE hr.*;

GGSCI (sourcesrv) 19> stop xhr

Sending STOP request to EXTRACT XHR ...

Request processed.

GGSCI (sourcesrv) 20> start xhr

Sending START request to MANAGER ...

EXTRACT XHR starting

2011-12-22 04:10:21 INFO OGG-00733 Oracle GoldenGate Capture for Oracle, xhr.prm: Marker table GG.GGS_MARKER not found.

2011-12-22 04:10:21 ERROR OGG-00529 Oracle GoldenGate Capture for Oracle, xhr.prm: DDL Replication is enabled but table GG.GGS_DDL_HIST is not found. Please check DDL installation in the database.

 [oracle@sourcesrv gg]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 22 04:11:56 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @marker_setup.sql

Marker setup 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:gg

Marker setup table script complete, running verification script...

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

Setting schema name to GG

MARKER TABLE

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

OK

MARKER SEQUENCE

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

OK

Script complete.

SQL> @ddl_setup.sql

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 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 GoldenGate schema name:gg

You will be prompted for the mode of installation.

To install or reinstall DDL replication, enter INITIALSETUP

To upgrade DDL replication, enter NORMAL

Enter mode of installation:INITIALSETUP

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.

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:gg

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.

SQL> GRANT GGS_GGSUSER_ROLE TO gg;

Grant succeeded.

SQL> @ddl_enable

Trigger altered.

Now Extract process can be started.

GGSCI (sourcesrv) 21> start xhr

Sending START request to MANAGER ...

EXTRACT XHR starting

GGSCI (sourcesrv) 22> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING PHR 00:00:00 00:00:03

EXTRACT RUNNING XHR 00:00:00 00:00:02

GGSCI (sourcesrv) 26> stats xhr

Sending STATS request to EXTRACT XHR ...

Start of Statistics at 2011-12-22 04:24:21.

DDL replication statistics (for all trails):

*** Total statistics since extract started ***

Operations 10.00

Mapped operations 1.00

Unmapped operations 7.00

Other operations 2.00

Excluded operations 10.00

[oracle@sourcesrv gg]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 22 04:22:17 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table hr.lookup purge;

Table dropped.

GGSCI (sourcesrv) 27> stats xhr

Sending STATS request to EXTRACT XHR ...

Start of Statistics at 2011-12-22 04:24:47.

Check Replicat Status.

2011-12-22 04:24:44 ERROR OGG-00519 Oracle GoldenGate Delivery for Oracle, rhr.prm: Fatal error executing DDL replication: error [Error code [942], ORA-00942: table or view does not exist, SQL drop table "HR"."LOOKUP" purge /* GOLDENGATE_DDL_REPLICATION */], no error handler present.

GGSCI (targetsrv) 7> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

REPLICAT ABENDED RHR 00:04:41 00:00:50

GGSCI (targetsrv) 9> edit params rhr

GGSCI (targetsrv) 10> view params rhr

REPLICAT rhr

ASSUMETARGETDEFS

USERID gg, PASSWORD oracle

DDLERROR DEFAULT IGNORE

MAP HR.*, TARGET HR.*;

GGSCI (targetsrv) 12> stats rhr