Add tables to an Existing GoldenGate Configuration - Oracle Golden Gate

 1.     Log into the source database via GGSCI


[oracle@blue ggate]$ ggsci


Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.13 17435036 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131002.1206_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Oct  3 2013 02:39:46


Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.




GGSCI (blue.color) 2> dblogin userid ggate, password ggate

Successfully logged into database.


2.     Add trandata for the new table(s).


GGSCI (blue.color) 3> add trandata song.stone


2013-12-05 15:17:07  WARNING OGG-00869  No unique key is defined for table 'STONE'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.


Logging of supplemental redo data enabled for table SONG.STONE.


GGSCI (blue.color) 4> add trandata song.greystreet


2013-12-05 15:17:15  WARNING OGG-00869  No unique key is defined for table 'GREYSTREET'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.


Logging of supplemental redo data enabled for table SONG.GREYSTREET.


3.     Add the two tables in the Extract Parameter file


GGSCI (blue.color) 5> edit params e_gg

...

TABLE song.stone;

TABLE song.greystreet;

...


4.     Restart the Extract e_gg


GGSCI (blue.color) 2> stop e_gg


Sending STOP request to EXTRACT E_GG ...

Request processed.



GGSCI (blue.color) 3> start e_gg


Sending START request to MANAGER ...

EXTRACT E_GG starting


5.     At this point, capture the Current SCN from the database. Do not lose this number, we need it later!


[oracle@blue ggate]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 5 15:28:31 2013


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



Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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


SQL> select current_scn from v$database;


CURRENT_SCN

-----------

    1113396


SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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


6.     Take a Data Pump export of the two tables. Please note the use of FLASHBACK_SCN.


[oracle@blue ggate]$ expdp system/oracle directory=data_pump_dir dumpfile=additional_tables.dmp flashback_scn=1113396 tables=song.stone,song.greystreet


Export: Release 11.2.0.3.0 - Production on Thu Dec 5 15:32:04 2013


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** directory=data_pump_dir dumpfile=additional_tables.dmp flashback_scn=1113396 tables=song.stone,song.greystreet

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

. . exported "SONG"."GREYSTREET"                         5.882 KB     100 rows

. . exported "SONG"."STONE"                              5.679 KB     100 rows

Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/admin/sky/dpdump/additional_tables.dmp

Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 15:32:14


7.     Run the import on the Target database.


[oracle@blue ggate]$ impdp system/oracle directory=data_pump_dir dumpfile=additional_tables.dmp remap_schema=song:river


Import: Release 11.2.0.3.0 - Production on Thu Dec 5 15:45:29 2013


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=data_pump_dir dumpfile=additional_tables.dmp remap_schema=song:river

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "RIVER"."GREYSTREET"                        5.882 KB     100 rows

. . imported "RIVER"."STONE"                             5.679 KB     100 rows

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 15:45:31


8.     Here’s the important step, you need to edit the replicat’s parameter file and include the following string in the map statement.


GGSCI (blue.color) 2> edit params r_gg

...

MAP song.stone, TARGET river.stone , FILTER ( @GETENV("TRANSACTION", "CSN") > 1113396);

MAP song.greystreet, TARGET river.greystreet, FILTER ( @GETENV("TRANSACTION", "CSN") > 1113396);

...


9.     Recycle the replicat.


GGSCI (blue.color) 4>  stop r_gg


Sending STOP request to REPLICAT R_GG ...

start Request processed.



GGSCI (blue.color) 5> start r_gg


Sending START request to MANAGER ...

REPLICAT R_GG starting


GGSCI (blue.color) 6> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           

EXTRACT     RUNNING     E_GG        00:00:00      00:00:09   

REPLICAT    RUNNING     R_GG        00:00:00      00:00:03   


10. Check the counts on both systems.


10.1. On the Source


SQL> select name from v$database;


NAME

---------

SKY


SQL> select count(1) from song.stone;


  COUNT(1)

----------

       100


10.2. On the Target


SQL> select name from v$database;


NAME

---------

WATER


SQL> select count(1) from river.stone;


  COUNT(1)

----------

       100


10.3. Insert data on the source.


SQL> INSERT INTO song.stone

SELECT dbms_random.string('A', 2) RNDMSTR

  FROM all_objects

 WHERE ROWNUM < 101;  2    3    4 


100 rows created.


SQL> commit;


Commit complete.


SQL> select count(1) from song.stone;


  COUNT(1)

----------

       200


10.4. Check the counts on the target table.


SQL> select count(1) from river.stone;


  COUNT(1)

----------

       200


10.5.  Check lag on the replicat.


GGSCI (blue.color) 1> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                          

EXTRACT     RUNNING     E_GG        00:00:00      00:00:02   

REPLICAT    RUNNING     R_GG        00:00:00      00:00:08   


11.     Once the table is caught up, remove the FILTER parameters from the replicat.


GGSCI (blue.color) 2> edit params r_gg

...

MAP song.stone, TARGET river.stone;

MAP song.greystreet, TARGET river.greystreet;

...


12.     Restart the replicat and we’re done!


GGSCI (blue.color) 4>  stop r_gg


Sending STOP request to REPLICAT R_GG ...

start Request processed.



GGSCI (blue.color) 5> start r_gg


Sending START request to MANAGER ...

REPLICAT R_GG starting


GGSCI (blue.color) 6> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                          

EXTRACT     RUNNING     E_GG        00:00:00      00:00:06   

REPLICAT    RUNNING     R_GG        00:00:00      00:00:02

Comments

Post a Comment

Popular posts from this blog

How to Solve - "WAIT FOR EMON PROCESS NTFNS"

Event Monitor Process in short called as EMON is generally used by oracle to send notification. Often applications uses Database Change Notification process also called as DBCN to be aware of changes in the database, based on these notifications they take further actions. Often clients subscribe to the database and notifications are send as callback to them. In some cases you will notice database sessions are showing wait event "WAIT FOR EMON PROCESS NTFNS" and it can even make your database un-responsive. One observation can be these processes are taking more CPU (may not in be in all cases). As stated EMON process deals with database notification and it's spawn and gets down itself and Administrators have no control on theses processes. Anyone can go through oracle documents to read more EMON functioning process.  EMON uses oracle advanced queues and database queue notifications into them and EMON process these notifications to clients based on their subscriptions. In s
Read more

Query Regression - "OR" Transformation Oracle 19c

Recently while upgrading Oracle Database from version 12.1 to 19.10 found there were couple of queries which was running slow post upgrade. Usually these type of behavior is seen while upgrading to newer version and oracle provide various ways to fix them. The difference between good and bad plan was an introduction of  "MERGE JOIN CARTESIAN" (aka MJC) and that's in where regressed query in 19c taking most of the time. MJC sometimes works for queries but in this case it was clearly not helping.  We had following options here:     1. Copy the profile from 12c to 19c database and it worked, but the problem was not just to specific query but with many similar query. This means profile option is not very attractive in this case.     2. We also tried running query with OFE 12.1 to use the same optimizer features as previous version and query behaves well. Though this option can't be placed as we need to use 19c OFE and can be placed only for testing purpose.           alte
Read more

ORA - 12537: TNS: connection closed

Often when active clone to be done to create a new environment, we land up into couple of issues. I will discuss about one issues during this process. Action : Create a new database on different machine from a source. Error : ORA - 12537: TNS: connection closed In order to duplicate the environment was using below rman log /tmp/duplicate.log connect target sys/<password>@<source tns alias> connect auxiliary sys/<password>@<target tns alias> run{ allocate channel ch1 device type disk; allocate channel ch2 device type disk; allocate channel ch3 device type disk; allocate channel ch4 device type disk; allocate auxiliary channel ax1 device type disk; allocate auxiliary channel ax2 device type disk; allocate auxiliary channel ax3 device type disk; allocate auxiliary channel ax4 device type disk; DUPLICATE TARGET DATABASE TO <TARGET_DBNAME> FROM ACTIVE DATABASE NOFILENAMECHECK; } While making connection to Auxiliary database received mentioned error ORA - 12537:
Read more