Golden Gate on ASM - 11gR2

Golden Gate on ASM -  11gR2

Starting from Oracle Database 10g release 2 introduced Oracle automatic storage management (ASM).  Oracle ASM instance showing the data flow from Oracle GoldenGate extract on an ASM environment.

Oracle GoldenGate change data capture requires ASM authentication for accessing the transaction logs. The extract parameter tranlog options handles authentication to the ASM instance

Create the parameter file for the Primary Extract group

Below configuration is :

GGSCI (dc174.ora.com)> ADD EXTRACT EXTORA, TRANLOG, THREADS 2, BEGIN NOW

GGSCI (dc174.ora.com)> ADD EXTTRAIL /u01/app/gg_home/dirdat/ts, EXTRACT EXTORA

GGSCI (dc174.ora.com)> edit params ./GLOBALS

GGSCHEMA ggtest

CHECKPOINTTABLE GGTEST.CKPT

GGSCI (dc174.ora.com)> DBLOGIN USERID ggtest, PASSWORD oracle

ADD CHECKPOINTTABLE GGTEST.CKPT

[oracle@dc174 gg_home]$ sqlplus / as sysdba

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

ALTER SYSTEM SWITCH LOGFILE;

  

Creating Network Configuration for ASM

To enable Oracle GoldenGate capture from an ASM instance transaction logs, it requires configuring the network for the ASM instance. The steps to prepare Oracle GoldenGate for an ASM instance follows.

STEP 1:

Ensure the database listener is configured to handle connection, and the TNS alias is configured. The network configuration is for the grid infrastructure network.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = SOURCE.ora.com)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = SOURCE)

    )

    (SID_DESC =

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/grid)

      (SID = +ASM)

    )

  )

LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = dc174.ora.com)(PORT = 1521))

  )

ADR_BASE_LISTENER = /u01/app/oracle

ASM =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = dc174.ora.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = +ASM)

    )

  )

STEP 2:

Test connectivity using SQL*Plus as SYSASM users. The test must be successful before proceeding to step 3.

$ . oraenv

ORACLE_SID = [SOURCE] ? +ASM

The Oracle base remains unchanged with value /u01/app/oracle

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 11 10:02:11 2015

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

SQL> conn / as SYSASM

Connected.

GGSCI (dc174.ora.com)> edit params EXTORA

SETENV (ORACLE_SID=SOURCE)

USERID ggtestuser, PASSWORD ggs_tmp1

EXTTRAIL /u01/app/gg_home/dirdat/ts

DISCARDFILE ./dirout/EXTORA.dsc, APPEND, MEGABYTES 10

TRANLOGOPTIONS ASMUSER SYS@+ASM, ASMPASSWORD oracle

--GETTRUNCATES

DYNAMICRESOLUTION

--TRANLOGOPTIONS FETCHCHAINEDUPDATES

ddl include mapped objname ggtest.*;

TABLE ggtest.*;

TABLEEXCLUDE ggtest.USER_ACTIVITY_TRACE;

Note: 

ASM is running and “TRANLOGOPTIONS ASMUSER SYS@+ASM, ASMPASSWORD oracle” is not configure then you will getting below error

Unable to replicate.. because redo’s are in ASM..

2015-01-11 10:20:33  ERROR   OGG-00446  Oracle GoldenGate Capture for Oracle, ext.prm:  No valid log files for current redo sequence 256, thread 1, error retrieving redo file name for sequence 28, archived = 0, use_alternate = 0Not able to establish initial position for begin time 2015-01-11 10:20:33.

2015-01-11 10:20:33  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, ext.prm: PROCESS ABENDING.

Create the parameter file for the Data pump group

GGSCI (dc174.ora.com)> ADD EXTRACT PUMPORA,EXTTRAILSOURCE /u01/app/gg_home/dirdat/ts

GGSCI (dc174.ora.com)> ADD RMTTRAIL /u01/app/gg_home/dirdat/rs, EXTRACT PUMPORA

GGSCI (dc174.ora.com)> edit params PUMPORA

USERID ggtest, password oracle

rmthost dc174.ora.com, mgrport 7809

rmttrail /u01/app/gg_home/dirdat/rs

PASSTHRU

--GETTRUNCATES

DYNAMICRESOLUTION

ddl include mapped objname ggtest.*;

TABLE ggtest.*;

ON TARGET SYSTEM

Create the parameter file for the replicate group

GGSCI (dc174.ora.com)> EDIT PARAMS ./GLOBALS

GGSCHEMA GGS

CHECKPOINTTABLE GGTEST.CKPT

GGSCI (dc174.ora.com)> DBLOGIN USERID ggtest, Password oracle

ADD CHECKPOINTTABLE GGTEST.CKPT

[oracle@dc174 gg_home]$ sqlplus / as sysdba

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

ALTER SYSTEM SWITCH LOGFILE;

GGSCI (dc174.ora.com)> ADD REPLICAT REPORA,EXTTRAIL /u01/app/gg_home/dirdat/rs, CHECKPOINTTABLE GGTEST.CKPT

GGSCI (dc174.ora.com)> EDIT PARAMS REPORA

REPLICAT REPORA

ASSUMETARGETDEFS

USERID ggtest, password oracle

DDLERROR DEFAULT IGNORE

MAP ggtest.*, TARGET ggtest.*;

ON SOURCE

START EXTRACT EXTORA

START EXTRACT PUMPORA

VIEW REPORT EXTORA

VIEW REPORT PUMPORA

GGSCI (dc174.ora.com)> info all

ON TARGET SYSTEM

START REPLICAT REPORA

STATUS REPLICAT REPORA

VIEW REPORT REPORA

make sure every process is running on source and target

GGSCI (dc174.ora.com)> info all

GGSCI (vm212.ora.com)> info all

Check Goldengate alert log file for any error on source and target.

[oracle@dc174 gg_home]$ tail -f ggserr.log

[oracle@vm212 gg_home]$ tail -f ggserr.log