Oracle GoldenGate 11gR2 Configuration On Oracle Database 11gR2 RAC ACFS

source

Oracle 11gR2 RAC

#public IP
192.168.1.51     vzwc1
192.168.1.52     vzwc2
#private IP
172.168.1.51     vzwc1-priv
172.168.1.52     vzwc2-priv
#VIP
192.168.1.151    vzwc1-vip
192.168.1.152    vzwc2-vip
#scanip
192.168.1.159    vzwc-cluster vzwc-cluster-scan

Target

Oracle 11gR2 single instance

192.168.1.233 ggos

on source

Create ASM volumes 

[grid@vzwc1 ~]$ asmcmd lsdgState    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  NameMOUNTED  HIGH    N         512   4096  1048576      5120     4024             2048             658              0             N  CRSDG/MOUNTED  NORMAL  N         512   4096  1048576     30720    26374                0           13187              0             N  DATADG/MOUNTED  NORMAL  N         512   4096  1048576     30720    29724                0           14862              0             N  FRADG/MOUNTED  NORMAL  N         512   4096  1048576     20480    20290                0           10145              0             N  OGGDG/MOUNTED  HIGH    N         512   4096  1048576      5120     3867             2048             606              0             Y  SYSDG/[grid@vzwc1 ~]$ sqlplus / as sysasmSQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 16 15:48:20 2013Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Real Application Clusters and Automatic Storage Management optionsSQL> alter diskgroup oggdg add volume ggs size 8G;Diskgroup altered.

Create the ACFS filesystem

[grid@vzwc1 ~]$ /sbin/mkfs -t acfs -b 4k /dev/asm/ggs-11 mkfs.acfs: version                   = 11.2.0.4.0mkfs.acfs: on-disk version           = 39.0mkfs.acfs: volume                    = /dev/asm/ggs-11mkfs.acfs: volume size               = 8589934592mkfs.acfs: Format complete.

Register the ASM Volume (ADVM) & ACFS filesystem in the Oracle Registry

[grid@vzwc1 ~]$ /sbin/acfsutil registry -f -a /dev/asm/ggs-11 /ggsacfsutil registry: mount point /ggs successfully added to Oracle Registry

 As root user, please mount the ACFS filesystem on the ASM Volume (ADVM)

[root@vzwc1 ~]# mount -t acfs /dev/asm/ggs-11 /ggs

[root@vzwc1 ~]# chown -R oracle.oinstall /ggs[root@vzwc1 ~]# df -THFilesystem    Type     Size   Used  Avail Use% Mounted on/dev/mapper/VolGroup00-LogVol00              ext3      47G    18G    27G  40% //dev/sda1     ext3     104M    25M    75M  25% /boottmpfs        tmpfs     1.6G   964M   617M  61% /dev/shm/dev/asm/ggs-11              acfs     8.6G    98M   8.5G   2% /ggs

configuration GoldenGate

add supplemental log

[oracle@vzwc1 ora]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 16 17:05:11 2013Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;Database altered.SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;Database altered.SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;Database altered.SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;Database altered.SQL> ALTER SYSTEM SWITCH LOGFILE;System altered.

Create DDL objects

SQL> GRANT EXECUTE ON UTL_FILE TO goldengate;Grant succeeded.SQL> GRANT CONNECT,RESOURCE,DBA TO goldengate;Grant succeeded.

SQL> @marker_setupMarker setup scriptYou 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:goldengateMarker setup table script complete, running verification script...Please enter the name of a schema for the GoldenGate database objects:Setting schema name to GOLDENGATEMARKER TABLE-------------------------------OKMARKER SEQUENCE-------------------------------OKScript complete.SQL> @ddl_setupOracle GoldenGate DDL Replication setup scriptVerifying 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:goldengateWorking, please wait ...Spooling to file ddl_setup_spool.txtChecking for sessions that are holding locks on Oracle Golden Gate metadata tables ...Check complete.WARNING: Tablespace GOLDENGATE does not have AUTOEXTEND enabled.Using GOLDENGATE 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 GOLDENGATECLEAR_TRACE STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsCREATE_TRACE STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsTRACE_PUT_LINE STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsINITIAL_SETUP STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsDDLVERSIONSPECIFIC PACKAGE STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsDDLREPLICATION PACKAGE STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsDDLREPLICATION PACKAGE BODY STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsDDL IGNORE TABLE-----------------------------------OKDDL IGNORE LOG TABLE-----------------------------------OKDDLAUX  PACKAGE STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsDDLAUX PACKAGE BODY STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsSYS.DDLCTXINFO  PACKAGE STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsSYS.DDLCTXINFO  PACKAGE BODY STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsDDL HISTORY TABLE-----------------------------------OKDDL HISTORY TABLE(1)-----------------------------------OKDDL DUMP TABLES-----------------------------------OKDDL DUMP COLUMNS-----------------------------------OKDDL DUMP LOG GROUPS-----------------------------------OKDDL DUMP PARTITIONS-----------------------------------OKDDL DUMP PRIMARY KEYS-----------------------------------OKDDL SEQUENCE-----------------------------------OKGGS_TEMP_COLS-----------------------------------OKGGS_TEMP_UK-----------------------------------OKDDL TRIGGER CODE STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsDDL TRIGGER INSTALL STATUS-----------------------------------OKDDL TRIGGER RUNNING STATUS----------------------------------------------------------------------ENABLEDSTAYMETADATA IN TRIGGER----------------------------------------------------------------------OFFDDL TRIGGER SQL TRACING----------------------------------------------------------------------0DDL TRIGGER TRACE LEVEL----------------------------------------------------------------------0LOCATION OF DDL TRACE FILE------------------------------------------------------------------------------------------------------------------------/u01/app/oracle/diag/rdbms/zwc/zwc1/trace/ggs_ddl_trace.logAnalyzing installation status...STATUS OF DDL REPLICATION------------------------------------------------------------------------------------------------------------------------SUCCESSFUL installation of DDL Replication software componentsScript complete.SQL> @role_setupGGS Role setup scriptThis script will drop and recreate the role GGS_GGSUSER_ROLETo 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:goldengateWrote file role_setup_set.txtPL/SQL procedure successfully completed.Role setup script completeGrant 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 goldengate;Grant succeeded.

SQL> @ddl_enableTrigger altered.SQL> @ddl_pin goldengatePL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.SQL> @sequencePlease enter the name of a schema for the GoldenGate database objects:Setting schema name to GOLDENGATEUPDATE_SEQUENCE STATUS:Line/pos--------------------Error-----------------------------------------------------------------No errorsNo errorsGETSEQFLUSHLine/pos--------------------Error-----------------------------------------------------------------No errorsNo errorsSEQTRACELine/pos--------------------Error-----------------------------------------------------------------No errorsNo errorsREPLICATE_SEQUENCE STATUS:Line/pos--------------------Error-----------------------------------------------------------------No errorsNo errorsSTATUS OF SEQUENCE SUPPORT--------------------------------------------------------------SUCCESSFUL installation of Oracle Sequence Replication support

[oracle@vzwc1 ora]$ ./ggsciOracle GoldenGate Command Interpreter for OracleVersion 11.2.1.0.8 17044551 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_130718.0526_FBOLinux, x64, 64bit (optimized), Oracle 11g on Jul 18 2013 10:34:27Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.GGSCI (vzwc1) 1> view params mgrPORT 7839  DYNAMICPORTLIST 7840-7914  USERID goldengate, PASSWORD goldengate  --AUTOSTART EXTRACT *  AUTORESTART EXTRACT *,RETRIES 3,WAITMINUTES 5,RESETMINUTES 10   PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 1   PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5   PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5   LAGREPORTHOURS 1  LAGINFOMINUTES 30  LAGCRITICALMINUTES 45

config source extract group

GGSCI (vzwc1) 2> dblogin userid goldengate, password goldengateSuccessfully logged into database.GGSCI (vzwc1) 3> info trandata zwc.*Logging of supplemental redo log data is disabled for table ZWC.TAB1.GGSCI (vzwc1) 4> add trandata zwc.tab1Logging of supplemental redo data enabled for table ZWC.TAB1.GGSCI (vzwc1) 5> info trandata zwc.tab1Logging of supplemental redo log data is enabled for table ZWC.TAB1.Columns supplementally logged for table ZWC.TAB1: OBJECT_ID.GGSCI (vzwc1) 6> info trandata zwc.*Logging of supplemental redo log data is enabled for table ZWC.TAB1.Columns supplementally logged for table ZWC.TAB1: OBJECT_ID.GGSCI (vzwc1) 7> list tables *GOLDENGATE.GGS_DDL_COLUMNSGOLDENGATE.GGS_DDL_HISTGOLDENGATE.GGS_DDL_HIST_ALTGOLDENGATE.GGS_DDL_LOG_GROUPSGOLDENGATE.GGS_DDL_OBJECTSGOLDENGATE.GGS_DDL_PARTITIONSGOLDENGATE.GGS_DDL_PRIMARY_KEYSGOLDENGATE.GGS_DDL_RULESGOLDENGATE.GGS_DDL_RULES_LOGGOLDENGATE.GGS_MARKERGOLDENGATE.GGS_SETUPGOLDENGATE.GGS_STICKGOLDENGATE.GGS_TEMP_COLSGOLDENGATE.GGS_TEMP_UKFound 14 tables matching list criteria.GGSCI (vzwc1) 8> capture tabledef zwc.tab1Table definitions for ZWC.TAB1:OWNER                          VARCHAR (30)OBJECT_NAME                    VARCHAR (128)SUBOBJECT_NAME                 VARCHAR (30)OBJECT_ID                      NUMBER NOT NULL PKDATA_OBJECT_ID                 NUMBEROBJECT_TYPE                    VARCHAR (19)CREATED                        DATETIMELAST_DDL_TIME                  DATETIMETIMESTAMP                      VARCHAR (19)STATUS                         VARCHAR (7)TEMPORARY                      VARCHAR (1)GENERATED                      VARCHAR (1)SECONDARY                      VARCHAR (1)NAMESPACE                      NUMBEREDITION_NAME                   VARCHAR (30)GGSCI (vzwc1) 9> 

create a primary Extract group exta,and local trail file

[oracle@vzwc1 ora]$ ./ggsci Oracle GoldenGate Command Interpreter for OracleVersion 11.2.1.0.8 17044551 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_130718.0526_FBOLinux, x64, 64bit (optimized), Oracle 11g on Jul 18 2013 10:34:27Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.GGSCI (vzwc1) 1> dblogin userid goldengate, password goldengateSuccessfully logged into database.GGSCI (vzwc1) 2> add extract exta, tranlog, begin now, threads 2EXTRACT added.GGSCI (vzwc1) 3> add exttrail ./dirdat/ea, extract extaEXTTRAIL added.GGSCI (vzwc1) 4> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           EXTRACT     STOPPED     EXTA        00:00:00      00:03:31    

GGSCI (vzwc1) 13> view params extaEXTRACT exta  setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)setenv (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")--setenv (ORACLE_SID="VRAC1")  USERID goldengate@zwc, PASSWORD goldengate  --cachemgr cachedirectory ./dirtmp 100MB, CacheDirectory /ggs  --DDL Include Mapped  DDL INCLUDE OBJNAME "ZWC.*"  EXCLUDE INSTR 'CREATE TRIGGER'   DDLOPTIONS ADDTRANDATA NOCROSSRENAME  REPORT  TRANLOGOPTIONS RAWDEVICEOFFSET 0  REPORTCOUNT EVERY 1 MINUTES, RATE  DISCARDFILE ./dirrpt/exta.dsc,APPEND,MEGABYTES 1024  DBOPTIONS  ALLOWUNUSEDCOLUMN  WARNLONGTRANS 5h,CHECKINTERVAL 30m  EXTTRAIL ./dirdat/ea  FETCHOPTIONS NOUSESNAPSHOT  --tranlogoptions altarchivelogdest instance zwc1 /ggs  TranLogOptions ExcludeUser goldengate  TRANLOGOPTIONS DBLOGREADER ThreadOptions MaxCommitPropagationDelay 10000 IOLatency 3000 TRANLOGOPTIONS _INFINITYTOZERO  TranlogOptions ASMUser sys@asm , asmpassword oracle  TRANLOGOPTIONS  CONVERTUCS2CLOBS    --exclude report-- TABLEEXCLUDE ZWC.TAB2;                 TABLE ZWC.*;

Create  data pump group

GGSCI (vzwc1) 14> add extract pumpa, exttrailsource ./dirdat/ea, begin nowEXTRACT added.

add remote trail file

GGSCI (vzwc1) 15> add rmttrail ./dirdat/ra, extract pumpaRMTTRAIL added.

GGSCI (vzwc1) 4> view params pumpaEXTRACT pumpaRMTHOST 192.168.1.233, MGRPORT 7839RMTTRAIL /ggs/ora/dirdat/raPASSTHRUTABLEEXCLUDE ZWC.TAB2;TABLE ZWC.*;

"oracle" user tnsnames.ora

ASM =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = vzwc1-vip)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = +ASM)      (INSTANCE_NAME = +ASM1)      (UR=A)    )  )--on node2 tnsnames.oraASM =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = vzwc2-vip)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = +ASM)      (INSTANCE_NAME = +ASM2)      (UR=A)    )  )

on target

SQL> @sequence.sqlPlease enter the name of a schema for the GoldenGate database objects:goldengateSetting schema name to GOLDENGATEUPDATE_SEQUENCE STATUS:Line/pos--------------------Error-----------------------------------------------------------------No errorsNo errorsGETSEQFLUSHLine/pos--------------------Error-----------------------------------------------------------------No errorsNo errorsSEQTRACELine/pos--------------------Error-----------------------------------------------------------------No errorsNo errorsREPLICATE_SEQUENCE STATUS:Line/pos--------------------Error-----------------------------------------------------------------No errorsNo errorsSTATUS OF SEQUENCE SUPPORT--------------------------------------------------------------SUCCESSFUL installation of Oracle Sequence Replication support

create mgr

GGSCI (ggos) 6> view params mgrPORT 7839  DYNAMICPORTLIST 7840-7914  USERID goldengate, PASSWORD goldengate  --AUTOSTART REPLICAT *  AUTORESTART REPLICAT *, RETRIES 5, WAITMINUTES 3  PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS, MINKEEPDAYS 10   LAGREPORTHOURS 1  LAGINFOMINUTES 30  LAGCRITICALMINUTES 45

create replicat group

GGSCI (ggos) 8> add replicat repa, exttrail ./dirdat/ra, nodbcheckpointREPLICAT added.

GGSCI (ggos) 12> view params repaREPLICAT repa  setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)  setenv (ORACLE_SID="oraprod")  setenv (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")USERID goldengate, PASSWORD goldengate  --DDLOPTIONS MAPSESSIONSCHEMA ZWC TARGET ZHONGWC  DDL include mapped  ddloptions report  ddlerror 30568, ignore   ddlerror 942, ignore  ddlerror 955, ignore  ddlerror 1430, ignore  ddlerror 54,ignore  ddlerror 904,ignore  ddlerror 4081,ignore  ddlerror 1418,ignore  REPORT AT 01:59  REPORTCOUNT EVERY 30 MINUTES, RATE  REPERROR DEFAULT, ABEND  HANDLECOLLISIONS  assumetargetdefs  DISCARDFILE ./dirrpt/repa.dsc,  PURGE, MEGABYTES 1024  DISCARDROLLOVER AT 02:30  ALLOWNOOPUPDATES  GROUPTRANSOPS 2000  BATCHSQL    MAPEXCLUDE ZWC.TAB2;    MAP ZWC.*, TARGET ZWC.*;  --MAP ZWC.*, TARGET ZHONGWC.* ,filter (@range (1, 6));

check source/target status

GGSCI (vzwc1) 1> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           EXTRACT     RUNNING     EXTA        00:00:00      00:00:00    EXTRACT     RUNNING     PUMPA       00:00:00      00:00:06    

GGSCI (ggos) 1> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           REPLICAT    RUNNING     REPA        00:00:00      00:00:01