Oracle GoldenGate: Configuring DDL Replication

Oracle Goldengate(GG) is one of the key products of Oracle’s Data Integration product portfolio.

In this entry of our Goldengate series we will be configuring the Oracle Goldengate DDL replication between the existing source and target Oracle Databases 11.2.0.2 environment.

Let's first check our current status:

Check Extract & Pump - Source system.

GGSCI (sourcesrv) 15> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING PHR 00:00:00 00:00:04

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

No lag. Manager - Extract and Pump are running. OK.

Check Replicat - Target system.

GGSCI (targetsrv) 5> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING RHR 00:00:00 00:00:03

No lag. Manager and Replicat are running. OK.

Check Data Replication Status!

Compare Records from both databases.

First create a database link from target to source system to run some queries.

[oracle@targetsrv admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /oracle/product/11.2.0/db/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

TARGET =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = targetsrv)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = target)

)

)

[oracle@targetsrv admin]$ vi tnsnames.ora

[oracle@targetsrv admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /oracle/product/11.2.0/db/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

TARGET =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = targetsrv)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = target)

)

)

SOURCEDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = sourcesrv)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = source)

)

)

Add a TNS entry and create a database link.

[oracle@targetsrv admin]$ tnsping sourcedb

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 22-DEC-2011 03:40:12

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = sourcesrv)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = source)))

OK (10 msec)

Create a database link.

CREATE PUBLIC DATABASE LINK testlink CONNECT TO system identified by oracle USING 'SOURCEDB';

[oracle@targetsrv admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 22 03:48:22 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> CREATE PUBLIC DATABASE LINK testlink CONNECT TO system identified by oracle USING 'SOURCEDB';

Database link created.

SQL> select salary sal_target from hr.employees where employee_id=200;

SAL_TARGET

----------

5000

SQL> select salary sal_source from hr.employees@testlink where employee_id=200;

SAL_SOURCE

----------

5000

Update a row in source database:

[oracle@sourcesrv ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 22 03:54:10 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> update hr.employees set salary=4999 where employee_id=200;

1 row updated.

SQL> commit;

Commit complete.

Check the record in both databases:

SQL> select salary sal_source from hr.employees@testlink where employee_id=200;

SAL_SOURCE

----------

4999

SQL> select salary sal_target from hr.employees where employee_id=200;

SAL_TARGET

----------

4999

Replication is running for HR schema’s objects.

GGSCI (sourcesrv) 16> view params XHR

EXTRACT xhr

USERID gg, PASSWORD oracle

EXTTRAIL ./dirdat/hr

TABLE hr.*;

Let’s try to create a new table.

[oracle@sourcesrv ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 22 04:00:02 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> CREATE TABLE hr.lookup (

id NUMBER(10),

description VARCHAR2(50)

);

2 3 4

Table created.

Table creation didn’t replicated

[oracle@targetsrv admin]$ sqlplus / as sysdba

SQL> select * from hr.lookup;

select * from hr.lookup

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> select * from hr.lookup@testlink;

no rows selected

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:14 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): stop xhr.

2011-12-22 04:10:16 INFO OGG-01021 Oracle GoldenGate Capture for Oracle, xhr.prm: Command received from GGSCI: STOP.

2011-12-22 04:10:16 INFO OGG-00991 Oracle GoldenGate Capture for Oracle, xhr.prm: EXTRACT XHR stopped normally.

2011-12-22 04:10:20 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start xhr.

2011-12-22 04:10:20 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.157.129 (START EXTRACT XHR ).

2011-12-22 04:10:20 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT XHR starting.

2011-12-22 04:10:20 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, xhr.prm: EXTRACT XHR starting.

2011-12-22 04:10:21 WARNING OGG-01423 Oracle GoldenGate Capture for Oracle, xhr.prm: No valid default archive log destination directory found for thread 1.

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.

2011-12-22 04:10:21 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, xhr.prm: PROCESS ABENDING.

DDL support scripts should be run in source 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

2011-12-22 04:20:00 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, xhr.prm: EXTRACT XHR starting.

2011-12-22 04:20:01 WARNING OGG-01423 Oracle GoldenGate Capture for Oracle, xhr.prm: No valid default archive log destination directory found for thread 1.

2011-12-22 04:20:01 INFO OGG-01513 Oracle GoldenGate Capture for Oracle, xhr.prm: Positioning to Sequence 7, RBA 6973456.

2011-12-22 04:20:01 INFO OGG-01516 Oracle GoldenGate Capture for Oracle, xhr.prm: Positioned to Sequence 7, RBA 6973456, Dec 22, 2011 4:09:42 AM.

2011-12-22 04:20:01 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, xhr.prm: EXTRACT XHR started.

2011-12-22 04:20:01 INFO OGG-01055 Oracle GoldenGate Capture for Oracle, xhr.prm: Recovery initialization completed for target file ./dirdat/hr000001, at RBA 1138.

2011-12-22 04:20:01 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, xhr.prm: Output file ./dirdat/hr is using format RELEASE 10.4/11.1.

2011-12-22 04:20:01 INFO OGG-01026 Oracle GoldenGate Capture for Oracle, xhr.prm: Rolling over remote file ./dirdat/hr000001.

2011-12-22 04:20:01 INFO OGG-01053 Oracle GoldenGate Capture for Oracle, xhr.prm: Recovery completed for target file ./dirdat/hr000002, at RBA 997.

2011-12-22 04:20:01 INFO OGG-01057 Oracle GoldenGate Capture for Oracle, xhr.prm: Recovery completed for all targets.

2011-12-22 04:20:01 INFO OGG-01517 Oracle GoldenGate Capture for Oracle, xhr.prm: Position of first record processed Sequence 7, RBA 6973456, SCN 0.889104, Dec 22, 2011 4:09:42 AM.

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.

DDL replication statistics (for all trails):

*** Total statistics since extract started ***

Operations 11.00

Mapped operations 2.00

Unmapped operations 7.00

Other operations 2.00

Excluded operations 10.00

Check Replicat Status.

2011-12-22 03:30:04 INFO OGG-01735 Oracle GoldenGate Collector: Synchronizing ./dirdat/hr000000 to disk.

2011-12-22 03:30:04 INFO OGG-01670 Oracle GoldenGate Collector: Closing ./dirdat/hr000000.

2011-12-22 03:30:04 INFO OGG-01669 Oracle GoldenGate Collector: Opening ./dirdat/hr000001 (byte -1, current EOF 0).

2011-12-22 03:30:04 INFO OGG-01020 Oracle GoldenGate Delivery for Oracle, rhr.prm: Processed extract process RESTART_ABEND record at seq 1, rba 989 (aborted 0 records).

2011-12-22 04:24:43 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, rhr.prm: Setting current schema for DDL operation to [SYS].

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.

2011-12-22 04:24:44 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rhr.prm: PROCESS ABENDING.

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.*;

2011-12-22 04:29:46 INFO OGG-00995 Oracle GoldenGate Delivery for Oracle, rhr.prm: REPLICAT RHR starting.

2011-12-22 04:29:47 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, rhr.prm: REPLICAT RHR started.

2011-12-22 04:29:47 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, rhr.prm: Setting current schema for DDL operation to [SYS].

2011-12-22 04:29:47 INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, rhr.prm: Restoring current schema for DDL operation to [GG].

2011-12-22 04:30:05 INFO OGG-01021 Oracle GoldenGate Delivery for Oracle, rhr.prm: Command received from GGSCI: STATS.

GGSCI (targetsrv) 12> stats rhr

Sending STATS request to REPLICAT RHR ...

No active replication maps

DDL replication statistics:

*** Total statistics since replicat started ***

Operations 1.00

Mapped operations 1.00

Unmapped operations 0.00

Other operations 0.00

Excluded operations 0.00

Errors 1.00

Retried errors 0.00

Discarded errors 0.00

Ignored errors 1.00

In source database create table again.

SQL> CREATE TABLE hr.lookup (

id NUMBER(10),

description VARCHAR2(50)

);

2 3 4

Table created.

GGSCI (targetsrv) 13> stats rhr

Sending STATS request to REPLICAT RHR ...

No active replication maps

DDL replication statistics:

*** Total statistics since replicat started ***

Operations 2.00

Mapped operations 2.00

Unmapped operations 0.00

Other operations 0.00

Excluded operations 0.00

Errors 1.00

Retried errors 0.00

Discarded errors 0.00

Ignored errors 1.00

.

[oracle@targetsrv ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 22 04:34:40 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> select * from hr.lookup@testlink;

no rows selected

SQL> select * from hr.lookup;

no rows selected

SQL>

Insert data into new table

SQL> INSERT INTO hr.lookup (id, description) VALUES (3, 'THREE');

COMMIT;

1 row created.

SQL>

Commit complete.

SQL> select * from hr.lookup@testlink;

ID DESCRIPTION

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

3 THREE

SQL> select * from hr.lookup;

ID DESCRIPTION

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

3 THREE

Check trandata option of the new table

GGSCI (sourcesrv) 29> dblogin userid gg, password oracle

Successfully logged into database.

GGSCI (sourcesrv) 30> info trandata hr.*

Logging of supplemental redo log data is enabled for table HR.COUNTRIES

Logging of supplemental redo log data is enabled for table HR.DEPARTMENTS

Logging of supplemental redo log data is enabled for table HR.EMPLOYEES

Logging of supplemental redo log data is enabled for table HR.JOBS

Logging of supplemental redo log data is enabled for table HR.JOB_HISTORY

Logging of supplemental redo log data is enabled for table HR.LOCATIONS

Logging of supplemental redo log data is disabled for table HR.LOOKUP.

Logging of supplemental redo log data is enabled for table HR.REGIONS

Execute an update on new table:

SQL> UPDATE hr.lookup SET DESCRIPTION = 'FOUR';

1 row updated.

SQL> commit;

Commit complete.

2011-12-22 04:40:07 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rhr.prm: Aborted grouped transaction on 'HR.LOOKUP', Database error 1403 ().

2011-12-22 04:40:07 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rhr.prm: Repositioning to rba 4099 in seqno 1.

2011-12-22 04:40:07 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rhr.prm: SQL error 1403 mapping HR.LOOKUP to HR.LOOKUP.

2011-12-22 04:40:07 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rhr.prm: Repositioning to rba 4099 in seqno 1.

2011-12-22 04:40:07 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rhr.prm: Error mapping from HR.LOOKUP to HR.LOOKUP.

2011-12-22 04:40:07 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rhr.prm: PROCESS ABENDING.

Replicat ABENDED!!!

GGSCI (targetsrv) 14> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

REPLICAT ABENDED RHR 00:00:22 00:02:33

GGSCI (targetsrv) 15> edit params rhr

GGSCI (targetsrv) 16> view params rhr

REPLICAT rhr

ASSUMETARGETDEFS

USERID gg, PASSWORD oracle

DISCARDFILE ./dirrpt/rhr.dsc, append, megabytes 100

DDLERROR DEFAULT IGNORE

MAP HR.*, TARGET HR.*;

[oracle@targetsrv ~]$ cd /gg/dirrpt

[oracle@targetsrv dirrpt]$ ls -ltr

total 76

-rw-rw-rw- 1 oracle oinstall 1586 Dec 20 10:19 MGR0.rpt

-rw-rw-rw- 1 oracle oinstall 12892 Dec 20 10:22 RHR1.rpt

-rw-rw-rw- 1 oracle oinstall 15414 Dec 22 04:24 RHR0.rpt

-rw-rw-rw- 1 oracle oinstall 1766 Dec 22 04:29 MGR.rpt

-rw-rw-rw- 1 oracle oinstall 15341 Dec 22 04:40 RHR.rpt

GGSCI (targetsrv) 17> start r*

Sending START request to MANAGER ...

REPLICAT RHR starting

[oracle@targetsrv dirrpt]$ cat rhr.dsc

Oracle GoldenGate Delivery for Oracle process started, group RHR discard file opened: 2011-12-22 04:49:12

Current time: 2011-12-22 04:49:12

Discarded record from action ABEND on error 1403

Aborting transaction on ./dirdat/hr beginning at seqno 1 rba 4099

error at seqno 1 rba 4099

Problem replicating HR.LOOKUP to HR.LOOKUP

Record not found

Mapping problem with compressed key update record (target format)...

*

ID = NULL

DESCRIPTION = THREE

ID = NULL

DESCRIPTION = FOUR

*

Process Abending : 2011-12-22 04:49:12

/// SINCE SUPPLEMENTAL LOGGING IS NOT ENABLED FOR LOOKUP TABLE. ID COLUMN IS NULL..

Add ADDTRANDATA option to the extract process

GGSCI (sourcesrv) 32> edit params xhr

GGSCI (sourcesrv) 33> view params xhr

EXTRACT xhr

USERID gg, PASSWORD oracle

EXTTRAIL ./dirdat/hr

DDLOPTIONS ADDTRANDATA

DDL INCLUDE MAPPED OBJNAME HR.*

TABLE hr.*;

GGSCI (sourcesrv) 35> stop xhr

Sending STOP request to EXTRACT XHR ...

Request processed.

GGSCI (sourcesrv) 36> start xhr

EXTRACT XHR starting.

Check with another table creation

SQL> CREATE TABLE hr.lookup2 (

id NUMBER(10),

description VARCHAR2(50)

); 2 3 4

Table created.

2011-12-22 04:54:17 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT XHR starting.

2011-12-22 04:54:17 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, xhr.prm: EXTRACT XHR starting.

2011-12-22 04:54:17 ERROR OGG-00401 Oracle GoldenGate Capture for Oracle, xhr.prm: DDL Replication must be enabled in order to use DDLOPTIONS (use DDL statement earlier).

2011-12-22 04:54:17 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, xhr.prm: PROCESS ABENDING.

GGSCI (sourcesrv) 41> info trandata hr.*

Logging of supplemental redo log data is enabled for table HR.COUNTRIES

Logging of supplemental redo log data is enabled for table HR.DEPARTMENTS

Logging of supplemental redo log data is enabled for table HR.EMPLOYEES

Logging of supplemental redo log data is enabled for table HR.JOBS

Logging of supplemental redo log data is enabled for table HR.JOB_HISTORY

Logging of supplemental redo log data is enabled for table HR.LOCATIONS

Logging of supplemental redo log data is disabled for table HR.LOOKUP.

Logging of supplemental redo log data is disabled for table HR.LOOKUP2.

Logging of supplemental redo log data is enabled for table HR.REGIONS

SQL> drop table hr.lookup2 purge;

Table dropped.

GGSCI (sourcesrv) 51> view params xhr

EXTRACT xhr

USERID gg, PASSWORD oracle

EXTTRAIL ./dirdat/hr

DDL INCLUDE MAPPED OBJNAME HR.*

DDLOPTIONS ADDTRANDATA

TABLE hr.*;

GGSCI (sourcesrv) 52> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING PHR 00:00:00 00:00:01

EXTRACT RUNNING XHR 00:00:00 00:00:05

SQL> CREATE TABLE hr.lookup3 (

id NUMBER(10),

description VARCHAR2(50)

); 2 3 4

Table created.

GGSCI (sourcesrv) 53> info trandata hr.*

Logging of supplemental redo log data is enabled for table HR.COUNTRIES

Logging of supplemental redo log data is enabled for table HR.DEPARTMENTS

Logging of supplemental redo log data is enabled for table HR.EMPLOYEES

Logging of supplemental redo log data is enabled for table HR.JOBS

Logging of supplemental redo log data is enabled for table HR.JOB_HISTORY

Logging of supplemental redo log data is enabled for table HR.LOCATIONS

Logging of supplemental redo log data is disabled for table HR.LOOKUP.

Logging of supplemental redo log data is enabled for table HR.LOOKUP3

Logging of supplemental redo log data is enabled for table HR.REGIONS

To re-run replicat process

GGSCI (targetsrv) 19> edit params rhr

GGSCI (targetsrv) 20> view params rhr

REPLICAT rhr

ASSUMETARGETDEFS

USERID gg, PASSWORD oracle

DISCARDFILE ./dirrpt/rhr.dsc, append, megabytes 100

DDLERROR DEFAULT IGNORE

MAP HR.*, TARGET HR.*;

MAPEXCLUDE HR.LOOKUP;

GGSCI (targetsrv) 24> stats RHR

Sending STATS request to REPLICAT RHR ...

No active replication maps

DDL replication statistics:

*** Total statistics since replicat started ***

Operations 3.00

Mapped operations 3.00

Unmapped operations 0.00

Other operations 0.00

Excluded operations 0.00

Errors 0.00

Retried errors 0.00

Discarded errors 0.00

Ignored errors 0.00

2011-12-22 05:03:54 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, rhr.prm: REPLICAT RHR started.

2011-12-22 05:03:54 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, rhr.prm: Setting current schema for DDL operation to [SYS].

2011-12-22 05:03:54 INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, rhr.prm: Restoring current schema for DDL operation to [GG].

2011-12-22 05:03:54 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, rhr.prm: Setting current schema for DDL operation to [SYS].

2011-12-22 05:03:55 INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, rhr.prm: Restoring current schema for DDL operation to [GG].

2011-12-22 05:03:55 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, rhr.prm: Setting current schema for DDL operation to [SYS].

2011-12-22 05:03:55 INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, rhr.prm: Restoring current schema for DDL operation to [GG].

[oracle@targetsrv dirrpt]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 22 05:05:15 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> select * from hr.lookup;

ID DESCRIPTION

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

3 THREE

SQL> select * from hr.lookup@testlink;

ID DESCRIPTION

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

3 FOUR

In Source database:

SQL> truncate table hr.lookup;

Table truncated.

SQL> select * from hr.lookup;

no rows selected

SQL> select * from hr.lookup@testlink;

no rows selected

SQL>

SQL> INSERT INTO hr.lookup3 (id, description) VALUES (3, 'THREE');

COMMIT;

1 row created.

SQL>

Commit complete.

SQL> select * from hr.lookup3;

ID DESCRIPTION

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

3 THREE

SQL> select * from hr.lookup3@testlink;

ID DESCRIPTION

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

3 THREE

SQL> update hr.lookup3 set description='FOUR';

1 row updated.

SQL> commit;

Commit complete.

2011-12-22 05:04:16 INFO OGG-01021 Oracle GoldenGate Delivery for Oracle, rhr.prm: Command received from GGSCI: STATS.

2011-12-22 05:06:29 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, rhr.prm: Setting current schema for DDL operation to [SYS].

2011-12-22 05:06:29 INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, rhr.prm: Restoring current schema for DDL operation to [GG].

2011-12-22 05:07:40 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, rhr.prm: No unique key is defined for table LOOKUP3. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

SQL> select * from hr.lookup3;

ID DESCRIPTION

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

3 FOUR

SQL> select * from hr.lookup3@testlink;

ID DESCRIPTION

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

3 FOUR

SQL>

GGSCI (targetsrv) 25> stats RHR

Sending STATS request to REPLICAT RHR ...

Start of Statistics at 2011-12-22 05:10:22.

DDL replication statistics:

*** Total statistics since replicat started ***

Operations 4.00

Mapped operations 3.00

Unmapped operations 1.00

Other operations 0.00

Excluded operations 0.00

Errors 0.00

Retried errors 0.00

Discarded errors 0.00

Ignored errors 0.00

Replicating from HR.LOOKUP3 to HR.LOOKUP3:

*** Total statistics since 2011-12-22 05:07:41 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

*** Daily statistics since 2011-12-22 05:07:41 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

*** Hourly statistics since 2011-12-22 05:07:41 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

*** Latest statistics since 2011-12-22 05:07:41 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

End of Statistics.

To support ALL DLLs

GGSCI (sourcesrv) 64> view params XHR

EXTRACT xhr

USERID gg, PASSWORD oracle

EXTTRAIL ./dirdat/hr

DDL INCLUDE ALL

--DDL INCLUDE MAPPED OBJNAME HR.*

DDLOPTIONS ADDTRANDATA

TABLE hr.*;

@SOURCE DATABASE

SQL> create user sezgi identified by oracle;

User created.

SQL> grant connect, resource to sezgi;

Grant succeeded.

SQL> conn sezgi/oracle

Connected.

SQL>

CREATE TABLE sezgi.big_table (

id NUMBER(10),

created_date DATE,

lookup_id NUMBER(10),

data VARCHAR2(50)

);SQL> 2 3 4 5 6

Table created.

@TARGET DATABASE

[oracle@targetsrv dirrpt]$ sqlplus sezgi/oracle

SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 22 05:21:16 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> select * from big_table;

no rows selected

----------

That's all for now, I hope it would be helpful for you...

Comments:

Good Information which help me a lot

Posted by Prasad on July 20, 2012 at 01:18 PM CEST #

Very good informative article.
Just want to ask why we need to disable recyclebin only for 10g, why not for 11g.

Posted by Sirish on February 20, 2014 at 04:20 AM CET #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Oracle ECEMEA Partner Hubs Migration Center Team

We share our skills to maximize your revenue!
Our dedicated team of consultants can rapidly and successfully assist you to adopt and implement the latest of Oracle Technology in your solutions.

Stay Connected
partner.imc
@
beehiveonline.oracle-DOT-com
Google+

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
4
5
6
9
10
11
12
13
14
16
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today