Thursday, August 2, 2012

Goldengate on ASM using DBLOGREADER

This is demostration of CLASSIC CAPTURE on ASM using New ASM API ,
Which reading redo log on ASM Using GoldenGate user provided by USERID in Extract

Source Side : 3 Node RAC / Grid Infrastructure 11.2.0.2
Target Side : Stand-alone 11.2.0.1

DBLOGREADER

(Oracle) Valid for Extract in classic capture mode.Causes Extract to use a newer ASM API that is available as
of Oracle 10.2.0.5 and later 10g R2 versions, and Oracle 11.2.0.2 and later 11g R2 versions (but not in Oracle 11g R1
versions). This API uses the database server to access the redo and archive logs, instead of connecting directly to the
Oracle ASM instance. The database must contain the libraries that contain the API modules and must be
running. To use this feature, the Extract database user must have SELECT ANY TRANSACTION privilege.
When used, DBLOGREADER enables Extract to use a read size of up to 4 MB in size. This is controlled with the
DBLOGREADERBUFSIZE option The maximum read size when using the default OCI buffer is 28672 bytes.
This is controlled by the ASMBUFSIZE option. A larger buffer may improve the performance of Extract
when redo rate is high.When using DBLOGREADER, do not use the ASMUSER and ASMPASSWORD options of TRANLOGOPTIONS.
The API uses the user and password specified with the USERID parameter.

DBLOGREADERBUFSIZE

(Oracle) Valid for Extract in classic capture mode.Controls the maximum size, in bytes, of a read operation
into the internal buffer that holds the results of each read of the transaction log in ASM. Higher values increase
extraction speed but cause Extract to consume more memory. Low values reduce memory usage but increase I/O
because Extract must store data that exceeds the cache size to disk.
Use DBLOGREADERBUFSIZE together with the DBLOGREADER option if the source ASM instance is Oracle 10.2.0.5 or
later10g R2 versions, or Oracle 11.2.0.2 and later 11g R2 versions (but not Oracle 11g R1 versions). The newer ASM
API in those versions provides better performance than the older one. If the Oracle version is not one of those versions,
then ASMBUFSIZE must be used.



--SOURCE SIDE

Add supplement log at database level.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Database altered.

--ON SOURCE Add checkpoint Table if it is bi-directional replication,and if you dont have one.

GGSCI (PNETN1.localdomain.com) 19>DBLOGIN, USERID GGADMIN, PASSWORD Summer2011
GGSCI (PNETN1.localdomain.com) 19>INFO CHECKPOINTTABLE

--if you dont have one , create it. 

GGSCI (PNETN1.localdomain.com) 19>ADD CHECKPOINTTABLE GGADMIN.CKPT_TABLE

GGSCI (PNETN1.localdomain.com) 19>EDIT PARAMS ./GLOBALS
GGSCHEMA GGADMIN
CHECKPOINTTABLE GGADMIN.CKPT_TABLE


--Add Extract as Below , REGISTER extract using LOGRETENTION

Use DBLOGIN to REGISTER extract

GGSCI (PNETN1.localdomain.com)>DBLOGIN USERID GGADMIN,PASSWORD Summer2011
GGSCI (PNETN1.localdomain.com)>REGISTER EXTRACT DW_EX LOGRETENTION

--Add extract 

GGSCI (PNETN1.localdomain.com)>ADD EXTRACT DW_EX TRANLOG,BEGIN NOW,THREADS 3
GGSCI (PNETN1.localdomain.com)>ADD EXTTRAIL ./dirdat/EX, EXTRACT DW_EX

--Create parameter file for Extract 
GGSCI (PNETN1.localdomain.com) 19>EDIT PARAMS DW_EX
EXTRACT DW_EX
---ORACLE ENVIRONMET
SETENV (ORACLE_HOME = "/u01/app/oracle/11.2.0/db_1")
SETENV (ORACLE_SID = "EDWP1")
SETENV (NLS_LANG = "AMERICAN_AMERICA.WE8MSWIN1252")
USERID GGADMIN, PASSWORD Summer2011

--TRANLOGOPTIONS ASMUSER sys@+ASM, ASMPASSWORD Summer69
--This is ASM API that is available as of
--Oracle 10.2.0.5 and later 10g R2 versions AND
--Oracle 11.2.0.2 and later 11g R2 versions
--BUT NOT in Oracle 11g R1 versions

TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE  2597152,ASMBUFSIZE 28000
DYNAMICRESOLUTION
DISCARDFILE ./dirrpt/edwp.dsc,PURGE, MEGABYTES 100
EXTTRAIL ./dirdat/EX

--DDL REPLICATION
DDL INCLUDE MAPPED OBJNAME TEST.*

--DML replication for SCHEMA level.
TABLE TEST.*;
--end


--Add DATAPUMP

GGSCI (PNETN1.localdomain.com) 19>ADD EXTRACT DW_EP, EXTTRAILSOURCE ./dirdat/EX,begin now
GGSCI (PNETN1.localdomain.com) 19>ADD RMTTRAIL ./dirdat/EP, EXTRACT DW_EP, MEGABYTES 100
GGSCI (PNETN1.localdomain.com) 19>edit params DW_EP
EXTRACT DW_EP
SETENV (ORACLE_HOME = "/u01/app/oracle/11.2.0/db_1")
SETENV (ORACLE_SID = "EDWP1")
SETENV (NLS_LANG = "AMERICAN_AMERICA.WE8MSWIN1252")
USERID GGADMIN, PASSWORD Summer2011
PASSTHRU
RMTHOST 192.168.100.101, MGRPORT 7809
RMTTRAIL ./dirdat/EP
TABLE TEST.*;
--end

--START extract / Pump.

GGSCI (PNETN1.localdomain.com) 17> start DW_EX

Sending START request to MANAGER ...
EXTRACT DW_EX starting


GGSCI (PNETN1.localdomain.com) 18> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     DW_EP       00:00:00      00:20:02
EXTRACT     RUNNING     DW_EX       00:33:56      00:00:06


GGSCI (PNETN1.localdomain.com) 19> start DW_EP

Sending START request to MANAGER ...
EXTRACT DW_EP starting


GGSCI (PNETN1.localdomain.com) 20> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DW_EP       00:00:00      00:00:08
EXTRACT     RUNNING     DW_EX       00:00:02      00:00:07


--Output from ggserr.log

 
[[A2012-07-31 12:34:15  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start DW_EX.
2012-07-31 12:34:15  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host PNETN1.localdomain.com (START EXTRACT DW_EX ).
2012-07-31 12:34:15  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT DW_EX starting.
2012-07-31 12:34:15  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  EXTRACT DW_EX starting.
2012-07-31 12:34:15  INFO    OGG-03035  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
2012-07-31 12:34:15  INFO    OGG-01635  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  BOUNDED RECOVERY: reset to initial or altered checkpoint.
2012-07-31 12:34:15  INFO    OGG-01815  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Virtual Memory Facilities for: BR
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /u02/gghome/BR/DW_EX.
2012-07-31 12:34:15  INFO    OGG-01815  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Virtual Memory Facilities for: COM
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /u02/gghome/dirtmp.
2012-07-31 12:34:17  INFO    OGG-00546  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Default thread stack size: 33554432.
2012-07-31 12:34:17  INFO    OGG-01515  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Positioning to begin time Jul 31, 2012 12:00:23 PM.
2012-07-31 12:34:18  INFO    OGG-01516  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Positioned to (Thread 1) Sequence 4, RBA 5810192, SCN 0.0, Jul 31, 2012 12:00:23 PM.
2012-07-31 12:34:18  INFO    OGG-01515  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Positioning to begin time Jul 31, 2012 12:00:23 PM.
2012-07-31 12:34:18  INFO    OGG-01516  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Positioned to (Thread 2) Sequence 2, RBA 5019152, SCN 0.0, Jul 31, 2012 12:00:23 PM.
2012-07-31 12:34:18  INFO    OGG-01515  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Positioning to begin time Jul 31, 2012 12:00:23 PM.
2012-07-31 12:34:19  INFO    OGG-01516  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Positioned to (Thread 3) Sequence 2, RBA 4712464, SCN 0.0, Jul 31, 2012 12:00:23 PM.
2012-07-31 12:34:19  INFO    OGG-01517  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Position of first record processed for Thread 2, Sequence 2, RBA 5019152, SCN 0.1098626, Jul 31, 2012 12:00:23 PM.
2012-07-31 12:34:19  INFO    OGG-01517  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Position of first record processed for Thread 3, Sequence 2, RBA 4712464, SCN 0.1098627, Jul 31, 2012 12:00:23 PM.
2012-07-31 12:34:19  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  EXTRACT DW_EX started.
2012-07-31 12:34:19  INFO    OGG-01052  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  No recovery is required for target file ./dirdat/EX000000, at RBA 0 (file not opened).
2012-07-31 12:34:19  INFO    OGG-01478  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Output file ./dirdat/EX is using format RELEASE 11.2.
2012-07-31 12:34:19  INFO    OGG-01517  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Position of first record processed for Thread 1, Sequence 4, RBA 5810192, SCN 0.1098585, Jul 31, 2012 12:00:23 PM.
2012-07-31 12:34:32  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start DW_EP.
2012-07-31 12:34:32  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host PNETN1.localdomain.com (START EXTRACT DW_EP ).
2012-07-31 12:34:32  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT DW_EP starting.
2012-07-31 12:34:33  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, dw_ep.prm:  EXTRACT DW_EP starting.
2012-07-31 12:34:33  INFO    OGG-03035  Oracle GoldenGate Capture for Oracle, dw_ep.prm:  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
2012-07-31 12:34:33  INFO    OGG-01815  Oracle GoldenGate Capture for Oracle, dw_ep.prm:  Virtual Memory Facilities for: COM
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /u02/gghome/dirtmp.
2012-07-31 12:34:33  WARNING OGG-01015  Oracle GoldenGate Capture for Oracle, dw_ep.prm:  Positioning with begin time: Jul 31, 2012 12:02:01 PM, waiting for data: at extseqno 0, extrba 0.
2012-07-31 12:34:33  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, dw_ep.prm:  EXTRACT DW_EP started.
2012-07-31 12:34:38  INFO    OGG-01226  Oracle GoldenGate Capture for Oracle, dw_ep.prm:  Socket buffer size set to 27985 (flush size 27985).
2012-07-31 12:34:38  INFO    OGG-01052  Oracle GoldenGate Capture for Oracle, dw_ep.prm:  No recovery is required for target file ./dirdat/EP000000, at RBA 0 (file not opened).
2012-07-31 12:34:38  INFO    OGG-01478  Oracle GoldenGate Capture for Oracle, dw_ep.prm:  Output file ./dirdat/EP is using format RELEASE 11.2.


---Also you will see below output on Target ggserr.log

2012-07-31 09:32:36  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from EXTRACT on host 192.168.100.126 (START SERVER CPU -1 PRI -1  TIMEOUT 300 PARAMS ).
2012-07-31 09:32:36  INFO    OGG-00974  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager started collector process (Port 7840).
2012-07-31 09:32:36  INFO    OGG-01677  Oracle GoldenGate Collector:  Waiting for connection (started dynamically).
2012-07-31 09:32:36  INFO    OGG-01228  Oracle GoldenGate Collector:  Timeout in 300 seconds.
2012-07-31 09:32:41  INFO    OGG-01229  Oracle GoldenGate Collector:  Connected to 192.168.100.126:23270.
2012-07-31 09:32:41  WARNING OGG-01223  Oracle GoldenGate Collector:  did not recognize command (n).
2012-07-31 09:32:41  INFO    OGG-01669  Oracle GoldenGate Collector:  Opening ./dirdat/EP000000 (byte -1, current EOF 0).
2012-07-31 09:32:41  INFO    OGG-01670  Oracle GoldenGate Collector:  Closing ./dirdat/EP000000.
2012-07-31 09:32:41  INFO    OGG-01669  Oracle GoldenGate Collector:  Opening ./dirdat/EP000000 (byte -1, current EOF 0).


=============================================================
=============================================================

--TARGET SIDE

--Add checkpoint Table into GLOBALS, if you dont have any.

GGSCI (TEST.localdomain.com) 4> view PARAMS ./GLOBALS
CHECKPOINTTABLE GGSUSER.CKPT
GGSCHEMA GGSUSER

--Add checkpoint Table into Database. 

GGSCI (TEST.localdomain.com) 6>DBLOGIN USERID GGSUSER,PASSWORD Summer2011
Successfully logged into database.

--confirm if any checkpoint Table is already exist. 
GGSCI (TEST.localdomain.com) 8>INFO CHECKPOINTTABLE

No checkpoint table specified, using GLOBALS specification (GGSUSER.CKPT)...
Checkpoint table GGSUSER.CKPT created 2012-05-31 13:32:57.


--Add Replicat on Target SIDE.

GGSCI (TEST.localdomain.com) 8>ADD REPLICAT DW_ER, EXTTRAIL ./dirdat/EP,checkpointtable GGSUSER.CKPT

--Create parameter file for Replicat

GGSCI (TEST.localdomain.com) 8>edit params DW_ER
REPLICAT DW_ER
SETENV (ORACLE_HOME = "/u00/app/oracle/product/11.2.0/db_1")
SETENV (ORACLE_SID = "TEST")
--Assume DDL of Source.
ASSUMETARGETDEFS
USERID ggsuser, PASSWORD Summer2011
DISCARDFILE ./dirrpt/EDWP.dsc, append, megabytes 100
--DLL replication. 
DDL INCLUDE ALL
--DML replication from TEST schema to TEST schema. 
MAP TEST.*, TARGET TEST.*;
--end



--start Replicat

GGSCI (TEST.localdomain.com) 6> start DW_ER

Sending START request to MANAGER ...
REPLICAT DW_ER starting


--Some Output from Replicat ggserr.log

2012-07-31 09:33:08  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start DW_ER.
2012-07-31 09:33:08  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host 192.168.100.101 (START REPLICAT DW_ER ).
2012-07-31 09:33:08  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  REPLICAT DW_ER starting.
2012-07-31 09:33:09  INFO    OGG-00995  Oracle GoldenGate Delivery for Oracle, dw_er.prm:  REPLICAT DW_ER starting.
2012-07-31 09:33:09  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, dw_er.prm:  REPLICAT DW_ER started.

5 comments:

  1. Thank you very much, very good notes.

    ReplyDelete
  2. Dear Jignesh,

    Very useful information and thanks a lot for sharing.

    Regards,
    Mohan.D

    ReplyDelete
  3. Nice Post Jignesh,

    based on following line in your Post i have a question.
    ========================================================================
    GGSCI (PNETN1.localdomain.com)>ADD EXTRACT DW_EX TRANLOG,BEGIN NOW,THREADS 3
    =========================================================================

    you'd performed replication from 3 node RAC to Stand alone, for this you configured 3 threads (on RAC ) for extract process,
    my question is that if we replicate from Stand alone Databse to RAC (2 or 3 node) where our Extract process will run on Stand alone and Replicat Process will on RAC
    so in this situation do we need to setup Thread parameter or not, if yes then where we would we define. In extract definition or in Replicate definition.

    ReplyDelete
    Replies
    1. We dont Need to define THREADS parameter for single instance. This is only for RAC

      Delete
  4. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Oracle Golden Gate.kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on Oracle Golden Gate. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.

    For Free Demo Contact us:
    Name : Arunkumar U
    Email : arun@maxmunus.com
    Skype id: training_maxmunus
    Contact No.-+91-9738507310
    Company Website –http://www.maxmunus.com


    ReplyDelete