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
