Before continuing further, make sure that you start with clean schema areas for your lab. 4. On the AMER_WEST_SQLPLUS tab, navigate to ~/labs, and then execute the following
SQL script to clean out the source schema: [OS_prompt] cd ~/labs
[OS_prompt labs] rlsqlplus west@amer/oracle_4U
@Section15/sqlscripts/drop_tables.sql
PL/SQL procedure successfully completed.
AMER_SQL> SELECT * FROM user_tables;
no rows selected
5. On the EURO_EAST_SQLPLUS tab, navigate to the same ~/labs, and execute the same SQL script to clean out the target schema:
[OS_prompt] cd ~/labs
[OS_prompt labs] rlsqlplus east@euro/oracle_4U
@Section15/sqlscripts/drop_tables.sql
PL/SQL procedure successfully completed.
EURO_SQL> SELECT * FROM user_tables;
no rows selected
Leave SQL*Plus running.
6. Dropping the tables in the previous steps should have also cleared the TranData (transaction data). Verify this on the AMER_WEST_GGSCI tab:
GGSCI (AMER)> DBLogin UserIDAlias gguamer
Successfully logged into database.
GGSCI (AMER)> Info TranData WEST.*
ERROR: No viable tables matched specification.
GGSCI (AMER)>
Note: The last step of the practice verifies that the TranData comes back automatically.
7. To set up Oracle GoldenGate Change Data Capture on the source server
AMER_WEST_SQLPLUS tab, configure the change data capture extract (eddl.prm) with the following parameters:
GGSCI (AMER)> Edit Param eddl
The text editor of your choice opens the empty eddl.prm file. Enter the following parameters:
Extract eddl
ExtTrail ./dirdat/ed UserIDAlias gguamer
-- DDL Include Mapped ObjName "WEST.*" <-- This won’t work!
DDL Include Mapped ObjName WEST.* DDLOptions AddTranData, Report Table WEST.*;
Save and close the file.
Oracle Internal & Oracle
8. On the AMER_WEST_GGSCI tab, add the Extract group and two local Extract Trails by executing the following commands:
GGSCI (AMER)> Add Extract eddl, TranLog, Begin Now
EXTRACT added.
GGSCI (AMER)> Add ExtTrail ./dirdat/ed, Extract eddl
EXTTRAIL added.
GGSCI (AMER)>
9. On the source AMER_WEST_GGSCI tab, configure the Extract Data Pump (pddl.prm) to read from the dirdat/ed local Extract Trail, transmit the data to the target server, and write it to the dirdat/pd Remote Trail:
GGSCI (AMER)> Edit Param pddl
The text editor of your choice opens the empty pddl.prm file. Add the following runtime parameters to the text file:
Extract pddl
RmtHost easthost, MgrPort 15001, Compress RmtTrail ./dirdat/pd
Passthru Table WEST.*;
Save and close the file.
10. Add the Extract group by executing the following GGSCI commands:
GGSCI (AMER)> Add Extract pddl, ExtTrailSource ./dirdat/ed GGSCI (AMER)> Add RmtTrail ./dirdat/pd, Extract pddl
11. On the target EURO_EAST_GGSCI tab, Replicat checkpoints to a special table created in the database. This checkpoint table ensures that the Replicat checkpoint is part of the transaction and is used to ensure data integrity. If it does not already exist, create the Checkpoint Table.
On the target server EURO_EAST_GGSCI tab, start GGSCI and execute the following commands:
[OS_prompt gg_euro] rlggsci GGSCI (EURO) 1> set editor gedit
GGSCI (EURO) 2> DBLogin UserIDAlias ggueuro GGSCI (EURO) 3> Info CheckpointTable
GGSCI (EURO) 4> Add CheckpointTable
Note: Because this is a frequent-enough sequence, it is useful to create an Obey file with
these commands that could be invoked by entering:
Oracle Internal & Oracle
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 15: DDL Replication
Chapter 15 - Page 10
12. On the target server EURO_EAST_GGSCI tab, configure the Replicat delivery components by executing the following command:
GGSCI (EURO)> Edit Param rddl
The text editor of your choice opens the empty rddl.prm file. Add the following runtime parameters to the text file:
Replicat rddl
DiscardFile ./dirrpt/rddl.dsc, Purge AssumeTargetDefs
UserIDAlias ggueuro DDL Include Mapped
Map WEST.*, Target EAST.*;
Save and close the file.
13. Add the Replicat by executing the following GGSCI command:
GGSCI (EURO)> Add Replicat rddl, ExtTrail ./dirdat/pd 14. Verify that the mgr process is running on both the AMER_WEST_GGSCI tab and the
EURO_EAST_GGSCI tab.
GGSCI (BOTH)> Info mgr
If the manager process is not already running, start it, and then verify that it is running: GGSCI (BOTH)> Start mgr
GGSCI (BOTH)> Info mgr
To start the Extract and Replicat processes, issue the following command on both the AMER and EURO instances.
Oracle Internal & Oracle
On AMER:
GGSCI (AMER)> Start ER *
Sending START request to MANAGER ... EXTRACT EDDL starting
Sending START request to MANAGER ... EXTRACT PDDL starting
GGSCI (AMER)> Info ER *
EXTRACT EDDL Last Started 2013-12-11 17:42 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Process ID 29972
Log Read Checkpoint Oracle Redo Logs
2013-12-11 17:42:51 Seqno 308, RBA 22850048 SCN 0.6319581 (6319581)
EXTRACT PDDL Last Started 2013-12-11 17:42 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Process ID 29973
Log Read Checkpoint File ./dirdat/ed000000 First Record RBA 1411
GGSCI (AMER)> On EURO:
GGSCI (EURO)> Start ER *
Sending START request to MANAGER ... REPLICAT RDDL starting
GGSCI (EURO)> Info ER *
REPLICAT RDDL Last Started 2013-12-11 17:42 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Process ID 29995
Log Read Checkpoint File ./dirdat/pd000000 First Record RBA 0
GGSCI (EURO)>
Oracle Internal & Oracle
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 15: DDL Replication
Chapter 15 - Page 12