Tag Archives: Oracle GoldenGate 12c

To resync one or more tables/schemas on different SCN's using a single or minimum number of replicats without using handlecollisions.

This can be done with Oracle goldenGate (OGG) 11.1.1.0.x or higher.
We are going to add new table SOURCE.DASANI to existing extract,pump and replicat


Environment

Source

Server Name		: RAC1
OS Version		: Red Hat Enterprise Linux Server release 6.5
RAC			: No
Database Version	: 12.2.0.1.0
GoldenGate Version	: Version 12.3.0.1.4 for oracle db 12.2.0.1.0
Database Name		: PSG01DAS
Golden Gate User	: OGGADMIN
Golden Gate Extract	: ERAJ
Golden Gate Pump	: PRAJ
Source Schema		: SOURCE
Oracle Home		: /u01/app/oracle/product/12.2.0/dbhome_1
GoldenGate Home		: /u01/app/oracle/product/12.3/ogg

Target

Server Name		: RAC1
OS Version		: Red Hat Enterprise Linux Server release 6.5
RAC			: No
Database Version	: 12.2.0.1.0
GoldenGate Version	: Version 12.3.0.1.4 for oracle db 12.2.0.1.0
Database Name		: USG01DAS
Golden Gate User	: OGGADMIN
Golden Gate Replicat    : RRAJ
Target Schema		: TARGET
Oracle Home		: /u01/app/oracle/product/12.2.0/dbhome_1
GoldenGate Home		: /u01/app/oracle/product/12.3/ogg

Source:

1. Login to GGSCI

GGSCI (rac1.rajasekhar.com) 1> dblogin UserIdAlias OGGADMIN
Successfully logged into database.

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 2>

2. Add trandata for new table

SQL> SELECT COUNT (*) FROM SOURCE.DASANI;

  COUNT(*)
----------
        15  <-----

SQL> ALTER TABLE SOURCE.DASANI ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Table altered.

SQL>

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 2> ADD TRANDATA SOURCE.DASANI

2019-04-24 00:39:01  WARNING OGG-06439  No unique key is defined for table DASANI. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2019-04-24 00:39:01  INFO    OGG-15130  No key found for table SOURCE.DASANI.  All viable columns will be logged.

2019-04-24 00:39:03  INFO    OGG-15132  Logging of supplemental redo data enabled for table SOURCE.DASANI.

2019-04-24 00:39:03  INFO    OGG-15133  TRANDATA for scheduling columns has been added on table SOURCE.DASANI.

2019-04-24 00:39:03  INFO    OGG-15135  TRANDATA for instantiation CSN has been added on table SOURCE.DASANI.

2019-04-24 00:39:04  INFO    OGG-10471  ***** Oracle Goldengate support information on table SOURCE.DASANI *****
Oracle Goldengate support native capture on table SOURCE.DASANI.
Oracle Goldengate marked following column as key columns on table SOURCE.DASANI: NAME, ROLE
No unique key is defined for table SOURCE.DASANI.

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 3>

3. Add table to Extract

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 4> edit params eraj

-- Add below
TABLE SOURCE.DASANI;

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 5> view params eraj

Extract ERAJ
SETENV (ORACLE_SID='PSG01DAS')
UserIdAlias OGGADMIN
TranlogOptions IntegratedParams (max_sga_size 256)
Exttrail ./dirdat/ea
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TABLE SOURCE.EMP;
TABLE SOURCE.EMPLOYEE;
TABLE SOURCE.X;
TABLE SOURCE.X1;
TABLE SOURCE.DASANI;  <---

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 6>

4. Add table to Pump

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 7> edit params praj

-- Add below
TABLE SOURCE.DASANI;


GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 8> view params praj

Extract  PRAJ
SETENV (ORACLE_SID='PSG01DAS')
UserIdAlias OGGADMIN
rmthost rac2, mgrport 7809
rmttrail ./dirdat/pa
TABLE SOURCE.EMP;
TABLE SOURCE.EMPLOYEE;
TABLE SOURCE.X;
TABLE SOURCE.X1;
TABLE SOURCE.DASANI; <----


GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 9>

5. Restart Extract

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 11> stop eraj

Sending STOP request to EXTRACT ERAJ ...
Request processed.


GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 12> start eraj

Sending START request to MANAGER ...
EXTRACT ERAJ starting


GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 13>

6. Restart Pump

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 13> stop praj

Sending STOP request to EXTRACT PRAJ ...
Request processed.


GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 14> start praj

Sending START request to MANAGER ...
EXTRACT PRAJ starting


GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 15>

7. Verify Status

GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 15> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     ERAJ        00:00:03      00:00:00
EXTRACT     RUNNING     PRAJ        00:00:00      00:00:10


GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 16>

8. Capture current SCN

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2298283 <----

SQL>

9. Export Table using FLASHBACK_SCN

SQL> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';

OWNER      DIRECTORY_NAME            DIRECTORY_PATH                           ORIGIN_CON_ID
---------- ------------------------- ---------------------------------------- -------------
SYS        DATA_PUMP_DIR             /u01/app/oracle/admin/PSG01DAS/dpdump/               0

SQL>


[oracle@rac1 ~]$ expdp \'/ as sysdba\' directory=data_pump_dir dumpfile=source_dasani.dmp logfile=source_dasani.log tables=source.dasani flashback_scn=2298283

Export: Release 12.2.0.1.0 - Production on Wed Apr 24 00:52:11 2019

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=data_pump_dir dumpfile=source_dasani.dmp logfile=source_dasani.log tables=source.dasani flashback_scn=2298283
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "SOURCE"."DASANI"                           5.687 KB      15 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/PSG01DAS/dpdump/source_dasani.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Apr 24 00:52:48 2019 elapsed 0 00:00:28

[oracle@rac1 ~]$

10. Transfer dump to target server

[oracle@rac1 ~]$ scp /u01/app/oracle/admin/PSG01DAS/dpdump/source_dasani.dmp oracle@rac2:/u01/app/oracle/admin/USG01DAS/dpdump/
oracle@rac2's password:
source_dasani.dmp                 100%  200KB 200.0KB/s   00:00
[oracle@rac1 ~]$

Target:

11. Import new table

SQL> col OWNER for a10
SQL> col DIRECTORY_NAME for a25
SQL> col DIRECTORY_PATH for a40
SQL> set lines 180
SQL> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';

OWNER      DIRECTORY_NAME            DIRECTORY_PATH                           ORIGIN_CON_ID
---------- ------------------------- ---------------------------------------- -------------
SYS        DATA_PUMP_DIR             /u01/app/oracle/admin/USG01DAS/dpdump/               0

SQL>

[oracle@rac2 ~]$ impdp \'/ as sysdba\' directory=data_pump_dir dumpfile=source_dasani.dmp logfile=source_dasani_imp.log remap_schema=source:target transform=segment_attributes:n transform=oid:n

Import: Release 12.2.0.1.0 - Production on Wed Apr 24 00:58:28 2019

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=data_pump_dir dumpfile=source_dasani.dmp logfile=source_dasani_imp.log remap_schema=source:target transform=segment_attributes:n transform=oid:n
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TARGET"."DASANI"                           5.687 KB      15 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Wed Apr 24 00:58:54 2019 elapsed 0 00:00:23

[oracle@rac2 ~]$

12. Add table to Replicat

Note : 

Please use single quotes since OGG v12 uses ANSI SQL parameter by default, if you use double quotes then you may receive this error.
You can use double quotes in pre OGG v12
    

2019-04-24T01:10:34.244+0800  ERROR   OGG-00375  Oracle GoldenGate Delivery for Oracle, rraj.prm:  Error in FILTER clause.
2019-04-24T01:10:39.386+0800  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rraj.prm:  PROCESS ABENDING.

GGSCI (rac2.rajasekhar.com) 14> edit params rraj

-- Add below line

Map SOURCE.DASANI, target TARGET.DASANI, FILTER ( @GETENV('TRANSACTION', 'CSN') > 2298283);


GGSCI (rac2.rajasekhar.com) 15> view params rraj

Replicat RRAJ
SETENV(ORACLE_SID='USG01DAS')
UserIdAlias OGGADMIN
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, Purge
UserIdAlias OGGADMIN
Map SOURCE.EMP, target TARGET.EMP;
Map SOURCE.EMPLOYEE, target TARGET.EMPLOYEE;
Map SOURCE.X, target TARGET.X;
Map SOURCE.X1, target TARGET.X1;
Map SOURCE.DASANI, target TARGET.DASANI, FILTER ( @GETENV('TRANSACTION', 'CSN') > 2298283);  <-----


GGSCI (rac2.rajasekhar.com) 16>

13. Restart Replicat

GGSCI (rac2.rajasekhar.com) 16> stop rraj

Sending STOP request to REPLICAT RRAJ ...
Request processed.


GGSCI (rac2.rajasekhar.com) 17>
GGSCI (rac2.rajasekhar.com) 19> start rraj

Sending START request to MANAGER ...
REPLICAT RRAJ starting


GGSCI (rac2.rajasekhar.com) 20>

14. Verify Status

GGSCI (rac2.rajasekhar.com) 20> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     RRAJ        00:00:00      00:00:11


GGSCI (rac2.rajasekhar.com) 21>

15. Data sync verify

Source:

SQL> SELECT COUNT(*) FROM SOURCE.DASANI;

  COUNT(*)
----------
        15 <---

SQL> 
SQL> INSERT INTO SOURCE.DASANI VALUES ('CHITTI','MANAGER');

1 row created.

SQL> INSERT INTO SOURCE.DASANI VALUES ('SRINIVAS','LEAD');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT COUNT(*) FROM SOURCE.DASANI;

  COUNT(*)
----------
        17 <-----

SQL>

Target:

SQL> SELECT COUNT(*) FROM TARGET.DASANI;

  COUNT(*)
----------
        15

SQL>

SQL> SELECT COUNT(*) FROM TARGET.DASANI;

  COUNT(*)
----------
        17 <----

SQL>

It's sync now, next step we need to FILTER PARAMETER from Relicat

16. Remove the FILTER parameters replicat

GGSCI (rac2.rajasekhar.com) 31> dblogin UserIdAlias OGGADMIN
Successfully logged into database.

GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 32> lag rraj

Sending GETLAG request to REPLICAT RRAJ ...
Last record lag 246 seconds.
Low watermark lag: 1.
High watermark lag: 258.
Low watermark position: 2211928.
High watermark position: 2316576.
At EOF, no more records to process


GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 33> edit params rraj

-- From 
Map SOURCE.DASANI, target TARGET.DASANI, FILTER ( @GETENV('TRANSACTION', 'CSN') > 2298283);

-- To
Map SOURCE.DASANI, target TARGET.DASANI;


GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 34> view params rraj

Replicat RRAJ
SETENV(ORACLE_SID='USG01DAS')
UserIdAlias OGGADMIN
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, Purge
UserIdAlias OGGADMIN
Map SOURCE.EMP, target TARGET.EMP;
Map SOURCE.EMPLOYEE, target TARGET.EMPLOYEE;
Map SOURCE.X, target TARGET.X;
Map SOURCE.X1, target TARGET.X1;
Map SOURCE.DASANI, target TARGET.DASANI; <-----


GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 35>

17. Restart the replicat

GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 35> stop rraj

Sending STOP request to REPLICAT RRAJ ...
Request processed.


GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 36> 

GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 37> start rraj

Sending START request to MANAGER ...
REPLICAT RRAJ starting


GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 38>

18. Verify Status

GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 38> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     RRAJ        00:00:00      00:00:05


GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 39>

19. Re-verify data sync

Source:

SQL> SELECT COUNT(*) FROM SOURCE.DASANI;

  COUNT(*)
----------
        17 <----

SQL>    INSERT INTO SOURCE.DASANI VALUES ('SOMU','DBA');

1 row created.

SQL> INSERT INTO SOURCE.DASANI VALUES ('ZABI','DBA');

1 row created.

SQL> INSERT INTO SOURCE.DASANI VALUES ('GIRI','BIGDATA');

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT COUNT(*) FROM SOURCE.DASANI;

  COUNT(*)
----------
        20 <-----

SQL>

Target:

SQL> SELECT COUNT(*) FROM TARGET.DASANI;

  COUNT(*)
----------
        17

SQL> 

SQL> SELECT COUNT(*) FROM TARGET.DASANI;

  COUNT(*)
----------
        20 <------

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Reference:

OGG How to Resync Tables / Schemas on Different SCN s in a Single Replicat (Doc ID 1339317.1)

Integrated Capture – Local deployment

GoldenGateIntegrated Capture, Integrated Capture local deployment, Oracle GoldenGate 12c

AIM: Configure GoldenGate 12^C Integrated Capture (All extract/pump and replicat on same database/server).
_________________________________________________________________________________

1. Overview
2. Environment
3. Install GoldenGate 12C for Oracle 11.2.0.3.10
4. Create test schemas for replication
5. Grant DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE
6. Set streams_pool_size
7. Verify compatible

8. Setup DDL scripts at Database level

a) Run marker_setup script
b) Run ddl_setup script
c) Run ddl_enable script
d) Run ddl_pin script

9. OGG Configuration

a) Set the environment
b) Login to Database
c) Configure Manager
d) Create Extract Group
e) Create Pump Group
f) Add supplemental logging
g) Set up the Checkpoint table
h) Activate the GLOBALS parameters
i) Add a Replicat checkpoint table
j) Create the Replicat Group
k) Start Manager, Extract, Pump and Replicat

10. Verify

LIST AVAILABLE TABLES CURRENTLY

a1) Source
a2) Target

CREATE TABLE OPERATION

b1) Source
b2) Target

INSERT OPERATION

c1) Execute INSERT on the Source
c2) Verify Insert Changes on Target

UPDATE OPERATION

d1) Execute update on the Source
d2) Verify Update Changes on Target

DELETE OPERATION

e1) Execute Delete on the Source
e2) Verify Delete Changes on Target

ALTER TABLE OPERATION

f1) Execute ALTER TABLE on the Source
f2) Verify ALTER TABLE Changes on Target

DROP TABLE OPERATION

g1) Execute DROP TABLE on the Source
g2) Verify DROP TABLE Changes on Target

TRUNCATE TABLE OPERATION

h1) Execute TRUNCATE TABLE on the Source
h2) Verify TRUNCATE TABLE Changes on Target

11. Verify alertlog

_________________________________________________________________________________________________________________

1. Overview

GoldenGate 12c: 
Integrated Extract is available from OGG 11g
Integrated Replicat is available from OGG 12c

What is Integrated Capture?

Instead of reading from the redo/archive logs, the Oracle GoldenGate Extract process interacts directly with a database logmining server to receive data changes in the form of logical change records (LCR).

In Pre-Goldengate 12c classic capture mode, the Oracle GoldenGate Extract process captures data changes from the Oracle redo or archive log files on the source system.

Integrated capture modes (2 Types)

a) Local deployment
The source database and the log mining server are the same database

b) Downstream deployment <--- We will cover this soon in upcoming article
The source and log mining databases are different databases.

2. Environment

Server Name			: RAC1,RAC2
OS Version			: Red Hat Enterprise Linux 5.7
RAC				: 2 Node RAC
Database Version		: 11.2.0.3.10
GoldenGate Version		: Version 12.1.2.0.0 for oracle db 11.2.0.3.10
Database Name			: BR8DBA (br8dba1, br8dba2)
File System			: ASM 
Golden Gate User		: GATE
Golden Gate Extract		: E11G
Golden Gate Pump		: P11G
Golden Gate Replicat	        : R11G
Source Schema			: SENDER
Target Schema			: RECEIVER
Oracle Home			: /u01/app/oracle/product/11.2.0.3/db_1
GoldenGate Home			: /u01/app/oracle/product/gg/ogg11 (shared home)

3. Install GoldenGate 12C for Oracle 11.2.0.3.10

http://www.br8dba.com/goldengate-12c-installation/

4. Create test schemas for replication

SQL> create user sender identified by sender default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,unlimited tablespace to sender;

Grant succeeded.

SQL> create user receiver identified by receiver default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,unlimited tablespace to receiver;

Grant succeeded.

SQL>

5. Grant DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE

SQL> exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('GATE', 'capture');

PL/SQL procedure successfully completed.

SQL>

6. Set streams_pool_size

On Instance 1 (br8dba1):

SQL> alter system set streams_pool_size=150M sid='br8dba1';

System altered.

SQL> 

On Instance 2 (br8dba2):

SQL> alter system set streams_pool_size=150M sid='br8dba2';

System altered.

SQL>

7. Verify compatible

SQL> select ISSYS_MODIFIABLE from V$PARAMETER where NAME='compatible';

ISSYS_MOD
---------
FALSE

SQL>

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.3.0 <----
SQL>

*** Database version should be atleast 11.2.0.3.0 OR above
*** Also using below MOS note we have to apply patch based on your current PSU

11.2.0.3 Database Specific Bundle Patches for Integrated Extract 11.2.x (Doc ID 1411356.1)

[oracle@rac1 ~]$ opatch lsinventory | grep -i 18466925
Patch  18466925     : applied on Mon Dec 05 21:56:31 IST 2016
[oracle@rac1 ~]$

8. Setup DDL scripts at Database level

a) Run marker_setup script

SQL> @marker_setup.sql

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:gate <---


Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GATE

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.
SQL>

b) Run ddl_setup script

SQL> @ddl_setup.sql;

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

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.

Using GATE as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GATE

CLEAR_TRACE STATUS:

Line/pos   Error
---------- --------------------------
No errors  No errors

CREATE_TRACE STATUS:

Line/pos   Error
---------- --------------------------
No errors  No errors

TRACE_PUT_LINE STATUS:

Line/pos   Error
---------- --------------------------
No errors  No errors

INITIAL_SETUP STATUS:

Line/pos   Error
---------- --------------------------
No errors  No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos   Error
---------- --------------------------
No errors  No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos   Error
---------- ----------------------------
No errors  No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos   Error
---------- ----------------------------
No errors  No errors

DDL IGNORE TABLE
-----------------------------------
OK

DDL IGNORE LOG TABLE
-----------------------------------
OK

DDLAUX  PACKAGE STATUS:

Line/pos   Error
---------- ----------------------------
No errors  No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos   Error
---------- ----------------------------
No errors  No errors

SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos   Error
---------- ----------------------------
No errors  No errors

SYS.DDLCTXINFO  PACKAGE BODY STATUS:

Line/pos   Error
---------- ----------------------------
No errors  No errors

DDL HISTORY TABLE
-----------------------------------
OK

DDL HISTORY TABLE(1)
-----------------------------------
OK

DDL DUMP TABLES
-----------------------------------
OK

DDL DUMP COLUMNS
-----------------------------------
OK

DDL DUMP LOG GROUPS
-----------------------------------
OK

DDL DUMP PARTITIONS
-----------------------------------
OK

DDL DUMP PRIMARY KEYS
-----------------------------------
OK

DDL SEQUENCE
-----------------------------------
OK

GGS_TEMP_COLS
-----------------------------------
OK

GGS_TEMP_UK
-----------------------------------
OK

DDL TRIGGER CODE STATUS:

Line/pos   Error
---------- ------------------------------
No errors  No errors

DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
-----------------------------------
ENABLED

STAYMETADATA IN TRIGGER
-----------------------------------
OFF

DDL TRIGGER SQL TRACING
-----------------------------------
0

DDL TRIGGER TRACE LEVEL
-----------------------------------
0

LOCATION OF DDL TRACE FILE
----------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/br8dba/br8dba1/trace/ggs_ddl_trace.log

Analyzing installation status...


VERSION OF DDL REPLICATION
----------------------------------------------------------------------
OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316

STATUS OF DDL REPLICATION
----------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.
SQL>

c) Run ddl_enable script

SQL> @ddl_enable.sql;

Trigger altered.

SQL>

d) Run ddl_pin script

SQL> @ddl_pin.sql gate

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL>

9. OGG Configuration

a) Set the environment

[oracle@rac1 ~]$ cat 11.env
GG11=/u01/app/oracle/product/gg/ogg11; export GG11
ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1;  export ORACLE_HOME
ORACLE_SID=br8dba1; export ORACLE_SID
PATH=$PATH:/u01/app/oracle/product/gg/ogg11; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/product/gg/ogg11; export LD_LIBRARY_PATH
alias ggsci='cd $GG11; ./ggsci'
[oracle@rac1 ~]$

[oracle@rac1 ~]$ . 11.env
[oracle@rac1 ~]$

b) Login to Database

[oracle@rac1 ~]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:31:13
Operating system character set identified as UTF-8.

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

GGSCI (rac1.rajasekhar.com) 1> dblogin userid gate, password gate
Successfully logged into database.

GGSCI (rac1.rajasekhar.com) 2>

c) Configure Manager

GGSCI (rac1.rajasekhar.com) 2> edit param mgr

-- Add below entries 
PORT 15500
DYNAMICPORTLIST 15510-15520
PURGEOLDEXTRACTS /u01/app/oracle/product/gg/ogg11/dirdat/*, USECHECKPOINTS

GGSCI (rac1.rajasekhar.com) 3> view params mgr

PORT 15500
DYNAMICPORTLIST 15510-15520
PURGEOLDEXTRACTS /u01/app/oracle/product/gg/ogg11/dirdat/*, USECHECKPOINTS

GGSCI (rac1.rajasekhar.com) 4>

d) Create Extract Group

GGSCI (rac1.rajasekhar.com) 4> ADD EXTRACT E11G INTEGRATED TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (rac1.rajasekhar.com) 5> ADD EXTTRAIL ./dirdat/ea, EXTRACT E11G, MEGABYTES 50
EXTTRAIL added.

GGSCI (rac1.rajasekhar.com) 6> edit params E11G

-- Add below entries
EXTRACT E11G
EXTTRAIL ./dirdat/ea
USERID gate, PASSWORD gate
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 100)
DDL INCLUDE ALL
DDLOPTIONS REPORT
TABLE sender.*;

GGSCI (rac1.rajasekhar.com) 7> view params E11G

EXTRACT E11G
EXTTRAIL ./dirdat/ea
USERID gate, PASSWORD gate
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 100)
DDL INCLUDE ALL
DDLOPTIONS REPORT
TABLE sender.*;

GGSCI (rac1.rajasekhar.com) 8>

GGSCI (rac1.rajasekhar.com) 14> REGISTER EXTRACT E11G DATABASE

Extract E11G successfully registered with database at SCN 2152703.

GGSCI (rac1.rajasekhar.com) 15>

e) Create Pump Group

GGSCI (rac1.rajasekhar.com) 8> ADD EXTRACT P11G, EXTTRAILSOURCE ./dirdat/ea
EXTRACT added.

GGSCI (rac1.rajasekhar.com) 9> ADD RMTTRAIL ./dirdat/pa, EXTRACT P11G, MEGABYTES 50
RMTTRAIL added.

GGSCI (rac1.rajasekhar.com) 10> edit params P11G

GGSCI (rac1.rajasekhar.com) 11> view params P11G

EXTRACT P11G
RMTHOST 192.168.2.101, MGRPORT 15500
USERID gate, password gate
RMTTRAIL ./dirdat/pa
PASSTHRU
TABLE sender.*;

GGSCI (rac1.rajasekhar.com) 12>

f) Add supplemental logging

GGSCI (rac1.rajasekhar.com) 16> ADD SCHEMATRANDATA sender

2016-12-05 20:36:13  INFO    OGG-01788  SCHEMATRANDATA has been added on schema sender.

2016-12-05 20:36:13  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema sender.

GGSCI (rac1.rajasekhar.com) 17>

--
The ADD SCHEMATRANDATA command adds supplemental logging to all tables both current and future for sender schema.

g) Set up the Checkpoint table

GGSCI (rac1.rajasekhar.com) 17> EDIT PARAMS ./GLOBALS

-- Add below entries
GGSCHEMA GATE
ENABLEMONITORING
CHECKPOINTTABLE GATE.CHECKPOINT

GGSCI (rac1.rajasekhar.com) 18> sh cat ./GLOBALS

GGSCHEMA GATE
ENABLEMONITORING
CHECKPOINTTABLE GATE.CHECKPOINT

GGSCI (rac1.rajasekhar.com) 19>

h) Activate the GLOBALS parameters

For the GLOBALS configuration to take effect, you must exit the session in which the changes were made. Execute the following command to exit GGSCI.

GGSCI (rac1.rajasekhar.com) 19> exit
[oracle@rac1 ogg11]$

i) Add a Replicat checkpoint table

[oracle@rac1 ~]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:31:13
Operating system character set identified as UTF-8.

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

GGSCI (rac1.rajasekhar.com) 1> dblogin userid gate, password gate
Successfully logged into database.

GGSCI (rac1.rajasekhar.com) 2> ADD CHECKPOINTTABLE GATE.CHECKPOINT

Successfully created checkpoint table GATE.CHECKPOINT.

GGSCI (rac1.rajasekhar.com) 3>

j) Create the Replicat Group

GGSCI (rac1.rajasekhar.com) 3> ADD REPLICAT R11G, EXTTRAIL ./dirdat/pa,checkpointtable gate.checkpoint
REPLICAT added.


GGSCI (rac1.rajasekhar.com) 4> edit param r11g

-- Add below 
REPLICAT R11G
ASSUMETARGETDEFS
userid gate, password gate
DISCARDFILE ./dirout/receiver.dsc, purge
ASSUMETARGETDEFS
DDLOPTIONS REPORT
MAP sender.*, TARGET receiver.*;

-- The ASSUMETARGETDEFS statement means that we are assuming both source and target table structure is same.

GGSCI (rac1.rajasekhar.com) 8> view params r11g

REPLICAT R11G
ASSUMETARGETDEFS
userid gate, password gate
DISCARDFILE ./dirout/receiver.dsc, purge
ASSUMETARGETDEFS
DDLOPTIONS REPORT
MAP sender.*, TARGET receiver.*;

GGSCI (rac1.rajasekhar.com) 9>

k) Start Manager, Extract, Pump and Replicat

GGSCI (rac1.rajasekhar.com) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
JAGENT      STOPPED
EXTRACT     STOPPED     E11G        00:00:00      01:18:04
EXTRACT     STOPPED     P11G        00:00:00      01:08:06
REPLICAT    STOPPED     R11G        00:00:00      00:12:02


GGSCI (rac1.rajasekhar.com) 10> start mgr
Manager started.


GGSCI (rac1.rajasekhar.com) 11> start extract E11G

Sending START request to MANAGER ...
EXTRACT E11G starting


GGSCI (rac1.rajasekhar.com) 12> start extract P11G

Sending START request to MANAGER ...
EXTRACT P11G starting


GGSCI (rac1.rajasekhar.com) 13> start replicat R11G

Sending START request to MANAGER ...
REPLICAT R11G starting


GGSCI (rac1.rajasekhar.com) 14> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
JAGENT      STOPPED
EXTRACT     RUNNING     E11G        00:00:09      00:00:00
EXTRACT     RUNNING     P11G        00:00:00      02:29:55
REPLICAT    RUNNING     R11G        00:00:00      00:00:04

GGSCI (rac1.rajasekhar.com) 5>

10. Verify

LIST AVAILABLE TABLES CURRENTLY

a1) Source:

SQL> conn sender/sender;
Connected.
SQL> select * from tab;

no rows selected  <----

SQL>

a2) Target:

SQL> conn receiver/receiver
Connected.
SQL> select * from tab;

no rows selected <---

SQL>

CREATE TABLE OPERATION

b1) Source:

SQL> create table sugi(Name varchar2(10) PRIMARY KEY, Role varchar2(10));

Table created.

SQL>

b2) Target:

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
SUGI                           TABLE <-----

SQL>

INSERT OPERATION

c1) Execute INSERT on the Source

SQL> insert into SUGI values ('&a','&b');
Enter value for a: RAJ
Enter value for b: DBA
old   1: insert into SUGI values ('&a','&b')
new   1: insert into SUGI values ('RAJ','DBA')

1 row created.

SQL> /
Enter value for a: SUGI
Enter value for b: DBA
old   1: insert into SUGI values ('&a','&b')
new   1: insert into SUGI values ('SUGI','DBA')

1 row created.

SQL> /
Enter value for a: TEJU
Enter value for b: DBA
old   1: insert into SUGI values ('&a','&b')
new   1: insert into SUGI values ('TEJU','DBA')

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM SUGI;

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA

SQL>

c2) Verify Insert Changes on Target

SQL> SELECT * FROM SUGI;

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA

SQL>

UPDATE OPERATION

d1) Execute update on the Source

SQL> update SUGI TEJU SET role='MANAGER' where name='RAJ';

1 row updated.

SQL> update SUGI SET role='MANAGER' where name='TEJU';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from sugi;

NAME       ROLE
---------- ----------
RAJ        MANAGER <----
SUGI       DBA
TEJU       MANAGER <----

SQL>

d2) Verify Update Changes on Target

SQL> select * from sugi;

NAME       ROLE
---------- ----------
RAJ        MANAGER <----
SUGI       DBA
TEJU       MANAGER <----

SQL>

DELETE OPERATION

e1) Execute Delete on the Source

SQL> delete from SUGI where name='RAJ';

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM SUGI;

NAME       ROLE
---------- ----------
SUGI       DBA
TEJU       MANAGER

SQL>

e2) Verify Delete Changes on Target

SQL> SELECT * FROM SUGI;

NAME       ROLE
---------- ----------
SUGI       DBA
TEJU       MANAGER

SQL>

ALTER TABLE OPERATION

f1) Execute ALTER TABLE on the Source

SQL> CREATE TABLE TEST AS SELECT * FROM SUGI;

Table created.

SQL> SELECT * FROM TAB;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
SUGI                           TABLE
TEST                           TABLE <----

SQL> ALTER TABLE TEST ADD EMPNO VARCHAR2(30);

Table altered.

SQL> desc test
 Name        Null?    Type
 ---------   -------- ----------------------------
 NAME                 VARCHAR2(10)
 ROLE                 VARCHAR2(10)
 EMPNO                VARCHAR2(30)
             
SQL>

f2) Verify ALTER TABLE Changes on Target

SQL> desc test
 Name      Null?    Type
 -------   -------- ----------------------------
 NAME               VARCHAR2(10)
 ROLE               VARCHAR2(10)
 EMPNO              VARCHAR2(30) <-----
          
SQL>

DROP TABLE OPERATION

g1) Execute DROP TABLE on the Source

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
SUGI                           TABLE
TEST                           TABLE

SQL> drop table sugi purge;

Table dropped.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE <----

SQL>

g2) Verify DROP TABLE Changes on Target

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE <----

SQL>

TRUNCATE TABLE OPERATION

h1) Execute TRUNCATE TABLE on the Source

SQL> truncate table test;

Table truncated.

SQL> select count(*) from test;

  COUNT(*)
----------
         0 <----

SQL>

h2) Verify TRUNCATE TABLE Changes on Target

SQL> select count(*) from test;

  COUNT(*)
----------
         0 <----

SQL>

11. Verify alertlog (alert_br8dba1.log)

CAPTURE OGG$CAP_E11G: Source Database: BR8DBA
CAPTURE OGG$CAP_E11G: Parameter Set by User: CAPTURE_IDKEY_OBJECTS Value: Y
CAPTURE OGG$CAP_E11G: Parameter Set by User: CAPTURE_SEQUENCE_NEXTVAL Value: N
CAPTURE OGG$CAP_E11G: Parameter Set by User: GETAPPLOPS Value: Y
CAPTURE OGG$CAP_E11G: Parameter Set by User: GETREPLICATES Value: N
CAPTURE OGG$CAP_E11G: Parameter Set by User: INCLUDE_OBJECTS Value: SYS.SMON_SCN_TIME
CAPTURE OGG$CAP_E11G: Parameter Set by User: MAX_SGA_SIZE Value: 100 <---
CAPTURE OGG$CAP_E11G: Parameter Set by User: PARALLELISM Value: 2 <---
GoldenGate CAPTURE CP01 for OGG$CAP_E11G with pid=54, OS id=32549 is in combined capture and apply mode.
Capture OGG$CAP_E11G is handling 1 applies.
  -- capture is running in apply-state checkpoint mode.
Starting persistent Logminer Session with sid = 1 for GoldenGate Capture OGG$CAP_E11G
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 4, Transaction Chunk Size = 1
LOGMINER: Memory Size = 66M, Checkpoint interval = 1000M
LOGMINER: SpillScn 0, ResetLogScn 1974745
LOGMINER: summary for session# = 1
LOGMINER: StartScn: 2279365 (0x0000.0022c7c5)
LOGMINER: EndScn: 0
LOGMINER: HighConsumedScn: 2279365 (0x0000.0022c7c5)
LOGMINER: session_flag: 0xf0
LOGMINER: Read buffers: 64
LOGMINER: Memory LWM limit: 10M, 79%
LOGMINER: Memory Release Limit: 15000
LOGMINER: LowCkptScn: 2275760 (0x0000.0022b9b0)
LOGMINER: HighCkptScn: 2275762 (0x0000.0022b9b2)
LOGMINER: SkipScn: 2275760 (0x0000.0022b9b0)
Tue Dec 06 00:56:22 2016
LOGMINER: session#=1 (OGG$CAP_E11G), reader MS00 pid=55 OS id=32557 sid=164 started
Tue Dec 06 00:56:22 2016
LOGMINER: session#=1 (OGG$CAP_E11G), builder MS01 pid=57 OS id=32561 sid=162 started
Tue Dec 06 00:56:22 2016
LOGMINER: session#=1 (OGG$CAP_E11G), preparer MS02 pid=58 OS id=32565 sid=33 started
Tue Dec 06 00:56:22 2016
LOGMINER: session#=1 (OGG$CAP_E11G), preparer MS03 pid=60 OS id=32569 sid=22 started
CAPTURE OGG$CAP_E11G: Session Restart SCN: 2275760
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 20, +TEST/br8dba/onlinelog/group_2.272.924477045
LOGMINER: Begin mining logfile for session 1 thread 2 sequence 17, +TEST/br8dba/onlinelog/group_3.271.924477045
Tue Dec 06 00:56:23 2016
XStream Out client for OGG$E11G with pid=61, OS id=32572 is waiting for GoldenGate Capture OGG$CAP_E11G to complete initializ
ation.
XStream Out client for OGG$E11G successfully attached to GoldenGate Capture OGG$CAP_E11G to receive uncommitted changes with
pid=61, OS id=32572.
[oracle@rac1 trace]$

Thank you,
Rajasekhar Amudala

Reference:

11.2.0.3 Database Specific Bundle Patches for Integrated Extract 11.2.x (Doc ID 1411356.1)

Bright DBA

ORACLE DBA – Tips and Techniques

Tag Archives: Oracle GoldenGate 12c

ADD NEW TABLE TO AN EXISTING GOLDENGATE CONFIGURATION

ADD NEW TABLE TO AN EXISTING EXTRACT, PUMP AND REPLICAT

Integrated Capture – Local deployment

AIM: Configure GoldenGate 12^C Integrated Capture (All extract/pump and replicat on same database/server).
_________________________________________________________________________________

ADD NEW TABLE TO AN EXISTING EXTRACT, PUMP AND REPLICAT

AIM: Configure GoldenGate 12C Integrated Capture (All extract/pump and replicat on same database/server). _________________________________________________________________________________

AIM: Configure GoldenGate 12^C Integrated Capture (All extract/pump and replicat on same database/server).
_________________________________________________________________________________