Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Installing Oracle GoldenGate (OGG) 11.2.1.0.1 in an Oracle cluster 11.2.0.3 (ACFS) and configuring a direct load and classic Change Data Capture (CDC) and Data Apply

Installing Oracle GoldenGate (OGG) 11.2.1.0.1 in an Oracle cluster 11.2.0.3 (ACFS) and configuring a direct load and classic Change Data Capture (CDC) and Data Apply

In the article you will have a look at the OGG 11.2.1.0.1 installation and an example of a classic CDC configuration, initial data load and Data apply. The environment is a two node Oracle RAC cluster running Oracle GI 11.2.0.3 and Oracle RAC RDBMS 11.2.0.3 and Oracle RAC RDBMS 10.2.0.5 described here and here. An ACFS mount point /u02 is created to host the OGG installation directory. The source schema and target schemas are as described in the following table. Both RACD and RACDB are two node databases using ASM. This article applies only to OGG using Oracle database as a target and source. For different databases the same OGG concept applies but setup and parameters are different.

Source Target
Schema Scott/tiger Scott_1/tiger
Scott_2/tiger
Scott_3/tiger
Database RACD RACDB
DB users ddl_ogg
ogg_extract
ogg_replicat
ddl_ogg
ogg_extract
ogg_replicat

Setting a replication between sites requires

  • Install OGG on both sites and configure managers
  • Set CDC – start CDC extract
  • Perform an initial dataload
  • Start data apply – start the replicat to apply changes captured by CDC
  • Set NOHANDLECOLLISION on the target replicats

For simplicity and illustration purpose I will configure only a single instance OGG, i.e. only one OGG manager is running on the first node raclinux1 and both databases has running instances RACD1 and RACDB1 on node raclinux1 and all data is transferred within the node raclinux1. In real life deployments you will install OGG and configure managers on every source and target node. Thus you will have a running OGG instance (OGG instance refers to running manager not a running Oracle RDBMS instance)on every source and target. OGG in a cluster requires a shared location for installing OGG binaries, for more information refer to the documentation here.

Install OGG in a cluster

  1. Download OGG 11.2.1.0.1 from Oracle Software Delivery Cloud (E-delivery) here. Make sure that you get the proper version for OS (Linux x86-64) and database version 10g and 11g.
  1. Make shared directories for OGG for oracle 10g and OGG for oracle 11g. In my case I have /u02 ACFS mount point and directories /u02/stage_ogg_ora10 and /u02/stage_ogg112_ora11. Login as the user that performed the Oracle RDBMS installation and unzip the downloaded zips into the respective directories.

[oracle@raclinux1 u02]$ df -k

Filesystem 1K-blocks Used Available Use% Mounted on

/dev/sda10 9920592 773924 8634600 9% /

/dev/sda11 317002932 91111176 209529156 31% /u01

……………………………………………………………………………………………………………………………………………………………………………………………………………………….

software 1953382396 1644783944 308598452 85% /media/sf_software

/dev/asm/data_vol-115

41943040 2014636 39928404 5% /u02

[oracle@raclinux1 u02]$

[oracle@raclinux1 u02]$ ls -l

total 853564

drwxrwxrwx 2 oracle oinstall 65536 Jul 5 15:19 lost+found

……………………………………………………………………………………………………………………………………………………………………………………………………………………………..

-rwxrwxrwx 1 oracle oinstall 220546 May 1 22:37 OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf

-rwxrwxrwx 1 oracle oinstall 93696 May 1 23:28 Oracle GoldenGate 11.2.1.0.1 README.doc

-rwxrwxrwx 1 oracle oinstall 24390 May 1 23:28 Oracle GoldenGate 11.2.1.0.1 README.txt

drwxr-xr-x 6 oracle oinstall 12288 Jul 5 15:57 stage_ogg112_ora10

drwxr-xr-x 6 oracle oinstall 12288 Jul 5 15:54 stage_ogg112_ora11

[oracle@raclinux1 u02]$

Extract the file fbo_ggs_Linux_x64_ora11g_64bit.tar for Oracle 11g release into the OGG home stage_ogg112_ora11. Extract the file fbo_ggs_Linux_x64_ora11g_64bit.tar for Oracle 10g release into the OGG home stage_ogg112_ora10. At the end you have a separate home for OGG for each database version.

  1. Set paths and shared library paths.

    For OGG on Oracle 11 add /u02/stage_ogg112_ora11 to both PATH and LD_LIBRARY_PATH

    LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u02/stage_ogg112_ora11

    PATH=$PATH:$ORACLE_HOME/bin:/u02/stage_ogg112_ora11

    For OGG on Oracle 10 add /u02/stage_ogg112_ora10 to both PATH and LD_LIBRARY_PATH

    LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u02/stage_ogg112_ora10

    PATH=$PATH:$ORACLE_HOME/bin:/u02/stage_ogg112_ora10

  2. Create working directories

    Invoke ggsci from the installation directory

    [oracle@raclinux1 stage_ogg112_ora11]$ ggsci

    Oracle GoldenGate Command Interpreter for Oracle

    Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

    Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

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

    GGSCI (raclinux1.gj.com) 1> create subdirs

    Creating subdirectories under current directory /u02/stage_ogg112_ora11

    Parameter files /u02/stage_ogg112_ora11/dirprm: already exists

    Report files /u02/stage_ogg112_ora11/dirrpt: created

    Checkpoint files /u02/stage_ogg112_ora11/dirchk: created

    Process status files /u02/stage_ogg112_ora11/dirpcs: created

    SQL script files /u02/stage_ogg112_ora11/dirsql: created

    Database definitions files /u02/stage_ogg112_ora11/dirdef: created

    Extract data files /u02/stage_ogg112_ora11/dirdat: created

    Temporary files /u02/stage_ogg112_ora11/dirtmp: created

    Stdout files /u02/stage_ogg112_ora11/dirout: created

    GGSCI (raclinux1.gj.com) 2>

  3. Prepare the databases for OGG extract, replicat or DDL handling. If you are using an Oracle database as source and target and intend to use DDL capture and sequences than you need to install support for DDL and support for sequences. Also you may opt for using dedicated database users for extract and replicat processes. DDL and sequence support should be installed on BOTH source and target database. Extract user should be created on the source database. Replicat user should be created on the target database.

    Create a user for DDL support and sequence support.

    create user ddl_ogg identified by ddl_ogg;

    grant connect,resource,dba to ddl_ogg;

    Create separate database users for extract and replicat. Refer to the documentation for the fine grained privileges. Here are some sample set up.

    create user ogg_extract identified by ogg_extract;

    grant CREATE SESSION, ALTER SESSION to ogg_extract;

    grant resource, connect to ogg_EXTRACT;

    grant SELECT ANY DICTIONARY to ogg_extract;

    grant flashback any table to ogg_extract;

    grant SELECT ANY TABLE to ogg_extract;

    grant GGS_GGSUSER_ROLE to ogg_extract;

    grant SELECT on dba_clusters to ogg_extract;

    grant EXECUTE on DBMS_FLASHBACK to ogg_extract;

    grant SELECT ANY TRANSACTION to ogg_extract;

    create user ogg_replicat identified by ogg_replicat;

    grand dba to ogg_replicat;

    In order to have sequence support run the following command as sysdba and when prompted specify the ddl_ogg schema. The output is in the Annex.

    SQL> @sequence.sql

    Please enter the name of a schema for the GoldenGate database objects:

    ddl_ogg

    Setting schema name to DDL_OGG

    ………………………………………………………………………………..

    SUCCESSFUL installation of Oracle Sequence Replication support

    SQL>

    Execute the following

    alter table sys.seq$ add supplemental log data (primary key) columns;

    GRANT EXECUTE on DDl_ogg.updateSequence TO ogg_extract;

    GRANT EXECUTE on DDL_ogg.replicateSequence TO ogg_replicat;

    In order to have DDL support run the following command as sysdba and when prompted specify the ddl_ogg schema.

    SQL> connect / as sysdba

    Connected.

    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:ddl_ogg

    Marker setup table script complete, running verification script…

    Please enter the name of a schema for the GoldenGate database objects:

    Setting schema name to DDL_OGG

    MARKER TABLE

    ——————————-

    OK

    MARKER SEQUENCE

    ——————————-

    OK

    Script complete.

    SQL>

    Run as sysdba @ddl_setup.sql and enter the ddl_ogg when prompted

    @ddl_setup.sqlSQL>

    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:ddl_ogg

    Working, please wait …

    Spooling to file ddl_setup_spool.txt

    …………………………………………………………………………………………………………………………

    STATUS OF DDL REPLICATION

    ————————————————————————————————————————

    SUCCESSFUL installation of DDL Replication software components

    Script complete.

    SQL>

    Run role setup as sysdba and enter ddl_ogg when prompted.

    SQL> @role_setup.sql

    GGS Role setup script

    This script will drop and recreate the role GGS_GGSUSER_ROLE

    To 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:ddl_ogg

    Wrote file role_setup_set.txt

    PL/SQL procedure successfully completed.

    Role setup script complete

    Grant 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 role GGS_GGSUSER_ROLE to ddl_ogg, ogg_extract, ogg_replicat

    SQL> GRANT GGS_GGSUSER_ROLE to ddl_ogg;

    Grant succeeded.

    SQL>

    Enable the triggers by running the following sql script.

    SQL> @ddl_enable.sql

    Trigger altered.

    SQL>

    Create the ./GLOBALS file for global parameters shared by all extract and replicats in the OGG instance and specify the GGSSCHEMA parameter. The CHECKPOINTTABLE parameter will be added later when data apply replicat is created.

    edit params ./CLOBALS

    GGSCI (raclinux1.gj.com) 95> view params ./GLOBALS

    ggschema ddl_ogg

    checkpointtable ddl_ogg.oggchkpt

    GGSCI (raclinux1.gj.com) 96>

  4. Create the manager parameter file and start the manager.

    GGSCI (raclinux1.gj.com) 1> edit params mgr

    GGSCI (raclinux1.gj.com) 2> view params mgr

    PORT 7809

    DYNAMICPORTLIST 8001,8002, 9500-9550

    AUTOSTART ER *

    AUTORESTART ER *, RETRIES 6, WAITMINUTES 4

    STARTUPVALIDATIONDELAY 5

    PURGEOLDEXTRACTS /u02/stage_ogg112_ora11/dirdat/aa*, USECHECKPOINTS, MINKEEPDAYS 5

    PURGEOLDEXTRACTS /u02/stage_ogg112_ora11/dirdat/aa*, USECHECKPOINTS, MINKEEPDAYS 5

    LAGREPORTHOURS 1

    LAGINFOMINUTES 3

    LAGCRITICALMINUTES 5

    GGSCI (raclinux1.gj.com) 1> start mgr

    Manager started.

    GGSCI (raclinux1.gj.com) 2>

    GGSCI (raclinux1.gj.com) 2> info mgr

    Manager is running (IP port raclinux1.gj.com.7809).

    GGSCI (raclinux1.gj.com) 3>

  5. Plan for an CDC and initial dataload

    The source is scott schema in RACD database. The targets are scott_1, scott_2 and scott_3 schemas in RACDB database. Create the target schemas and table structure within each schema.

    drop table dept;

    create table dept as select * from scott.dept where 1=2;

    drop table emp;

    create table emp as select * from scott.emp where 1=2;

    drop table bonus;

    create table bonus as select * from scott.bonus where 1=2;

    drop table salgrade;

    create table salgrade as select * from scott.salgrade where 1=2;

    alter table dept add constraint PK_dept primary key (deptno);

    alter table emp add constraint PK_emp primary key (empno);

    It is essential to understand that following activities should be done in order.

    1. Set CDC and run extract to capture the data from source to trail
    2. Set and run initial data load
    3. Set and run replicat to apply changes from the CDC extract trail to the target schemas.

CDC extract extcdc will write to three remote trails files, each trail file will correspond to a target schema, which will be read by a separate replicat. There will be three replicats repcdc1, repcdc2 and repcdc3 for each target schema.

Configure and run extract extcdc

  1. Prepare and run CDC to remote trails

    Add supplemental logging to the source database at database level

    SQL> alter database add supplemental log data;

    Database altered.

    SQL>

    SQL> alter system switch logfile;

    System altered.

    SQL>

    Turn on supplemental logging at table level. For each table that is to be replicated add trandata to the table. Pay attention that for table without the PK, KEYCOLS parameter will be used.

    SQL> connect scott/tiger

    Connected.

    SQL> select table_name from user_tables;

    TABLE_NAME

    ——————————

    DEPT

    EMP

    BONUS

    SALGRADE

    SQL>

    GGSCI (raclinux1.gj.com) 59> dblogin userid ddl_ogg

    Password:

    Successfully logged into database.

    GGSCI (raclinux1.gj.com) 60> add trandata scott.DEPT

    Logging of supplemental redo data enabled for table SCOTT.DEPT.

    GGSCI (raclinux1.gj.com) 61> add trandata scott.EMP

    Logging of supplemental redo data enabled for table SCOTT.EMP.

    GGSCI (raclinux1.gj.com) 62> add trandata scott.BONUS

    2012-07-06 23:25:07 WARNING OGG-00869 No unique key is defined for table ‘BONUS’. 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 SCOTT.BONUS.

    GGSCI (raclinux1.gj.com) 63> add trandata scott.SALGRADE

    2012-07-06 23:25:32 WARNING OGG-00869 No unique key is defined for table ‘SALGRADE’. 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 SCOTT.SALGRADE.

    GGSCI (raclinux1.gj.com) 64>

    GGSCI (raclinux1.gj.com) 7> dblogin userid ddl_ogg

    Password:

    Successfully logged into database.

    GGSCI (raclinux1.gj.com) 8> info trandata scott.*

    Logging of supplemental redo log data is enabled for table SCOTT.BONUS.

    Columns supplementally logged for table SCOTT.BONUS: ENAME, JOB, SAL, COMM.

    Logging of supplemental redo log data is enabled for table SCOTT.DEPT.

    Columns supplementally logged for table SCOTT.DEPT: DEPTNO.

    Logging of supplemental redo log data is enabled for table SCOTT.EMP.

    Columns supplementally logged for table SCOTT.EMP: EMPNO.

    Logging of supplemental redo log data is enabled for table SCOTT.SALGRADE.

    Columns supplementally logged for table SCOTT.SALGRADE: GRADE, LOSAL, HISAL.

    GGSCI (raclinux1.gj.com) 9>

    Make sure that you can use a service to access the ASM instance.

    SQL> show parameter service

    NAME TYPE VALUE

    ———————————— ———– ——————————

    service_names string +ASM

    SQL> alter system set service_names=’+ASM’,’ASM’ scope=both sid=’*’;

    System altered.

    SQL> show parameter service

    NAME TYPE VALUE

    ———————————— ———– ——————————

    service_names string +ASM, ASM

    SQL> alter system register;

    System altered.

    SQL>

    ASM =

    (DESCRIPTION =

    (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip)(PORT = 1521))

    )

    (CONNECT_DATA =

    (SERVICE_NAME = ASM)

    )

    )

    Create parameter file for the extcdc extract

    GGSCI (raclinux1.gj.com) 25> view params extcdc

    extract extcdc

    tranlogoptions asmuser sys@ASM, asmpassword sys1

    userid ogg_extract, password ogg_extract

    rmthost raclinux1, mgrport 7809

    rmttrail ./dirdat/aa

    table scott.dept, keycols(deptno ,dname ,loc);

    table scott.emp, keycols(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO);

    table scott.bonus, keycols(ENAME, JOB, SAL, COMM) ;

    table scott.salgrade, keycols(GRADE, LOSAL, HISAL);

    rmthost raclinux1, mgrport 7809

    rmttrail ./dirdat/bb

    table scott.dept, keycols(deptno ,dname ,loc);

    table scott.emp, keycols(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO);

    table scott.bonus, keycols(ENAME, JOB, SAL, COMM) ;

    table scott.salgrade, keycols(GRADE, LOSAL, HISAL);

    rmthost raclinux1, mgrport 7809

    rmttrail ./dirdat/cc

    table scott.dept, keycols(deptno ,dname ,loc);

    table scott.emp, keycols(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO);

    table scott.bonus, keycols(ENAME, JOB, SAL, COMM) ;

    table scott.salgrade, keycols(GRADE, LOSAL, HISAL);

GGSCI (raclinux1.gj.com) 26>

Add the extcdc extract and the remote trails

GGSCI (raclinux1.gj.com) 30> add extract extcdc, TRANLOG, THREADS 2, BEGIN now

EXTRACT added.

GGSCI (raclinux1.gj.com) 31>

GGSCI (raclinux1.gj.com) 6>

Define trails

GGSCI (raclinux1.gj.com) 6> add rmttrail ./dirdat/aa, extract extcdc

RMTTRAIL added.

GGSCI (raclinux1.gj.com) 7> add rmttrail ./dirdat/bb, extract extcdc

RMTTRAIL added.

GGSCI (raclinux1.gj.com) 8> add rmttrail ./dirdat/cc, extract extcdc

RMTTRAIL added.

GGSCI (raclinux1.gj.com) 9>

Start the extract

GGSCI (raclinux1.gj.com) 31> start extract extcdc

Sending START request to MANAGER …

EXTRACT EXTCDC starting

GGSCI (raclinux1.gj.com) 32>

Verify that extract is running

GGSCI (raclinux1.gj.com) 26> start extract extcdc

Sending START request to MANAGER …

EXTRACT EXTCDC starting

GGSCI (raclinux1.gj.com) 27> info extract extcdc, detail

EXTRACT EXTCDC Last Started 2012-07-07 00:30 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:02:06 ago)

Log Read Checkpoint Oracle Redo Logs

2012-07-07 00:28:33 Thread 1, Seqno 145, RBA 7086608

SCN 0.2787002 (2787002)

Log Read Checkpoint Oracle Redo Logs

2012-07-07 00:28:14 Thread 2, Seqno 49, RBA 39260672

SCN 0.2786972 (2786972)

Target Extract Trails:

Remote Trail Name Seqno RBA Max MB

./dirdat/aa 2 1097 100

./dirdat/bb 2 53151 100

./dirdat/cc 2 53151 100

Extract Source Begin End

+DATADG/racd/onlinelog/group_4.1214.787361735 2012-07-07 00:28 2012-07-07 00:28

+DATADG/racd/onlinelog/group_4.1214.787361735 2012-07-07 00:23 2012-07-07 00:28

+DATADG/racd/onlinelog/group_4.1214.787361735 2012-07-06 21:50 2012-07-07 00:23

+DATADG/racd/onlinelog/group_4.1214.787361735 2012-07-06 19:12 2012-07-06 21:50

+DATADG/racd/onlinelog/group_4.1214.787361735 * Initialized * 2012-07-06 19:12

Not Available * Initialized * 2012-07-06 19:12

Current directory /u02/stage_ogg112_ora11

Report file /u02/stage_ogg112_ora11/dirrpt/EXTCDC.rpt

Parameter file /u02/stage_ogg112_ora11/dirprm/extcdc.prm

Checkpoint file /u02/stage_ogg112_ora11/dirchk/EXTCDC.cpe

Process file /u02/stage_ogg112_ora11/dirpcs/EXTCDC.pce

Stdout file /u02/stage_ogg112_ora11/dirout/EXTCDC.out

Error log /u02/stage_ogg112_ora11/ggserr.log

GGSCI (raclinux1.gj.com) 28>

  1. Set and run initial data load

    There will be three initial data loads

  • From scott schema on RACD to scott_1 schema on RACDB done by extract initext1 and replicat initrep1 using a queue trail file
  • From scott schema on RACD to scott_2 schema on RACDB done by extract initext2 and replicat initrep2 using a direct TCP/IP load
  • From scott schema on RACD to scott_3 schema on RACDB done by extract initext3 and replicat initrep3 using a direct TCL/IP load with sqlldr

Transfer from scott on RACD to scott_1 on RACDB.

Create parameter files for each group extract and replicat. Create the extract parameter file on the source amd create the replicat on the target system. Pay attention to the rmtfile /u01/stage_ogg112_ora11/scott.dat used as a queue file.

GGSCI (raclinux1.gj.com) 75> view param initext1

sourceistable

userid ogg_extract, password ogg_extract

rmthost raclinux1, mgrport 7809

rmtfile /u02/stage_ogg112_ora11/scott.dat, purge

table scott.dept, keycols(deptno ,dname ,loc);

table scott.emp, keycols(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO);

table scott.bonus, keycols(ENAME, JOB, SAL, COMM) ;

table scott.salgrade, keycols(GRADE, LOSAL, HISAL);

GGSCI (raclinux1.gj.com) 76> view param initrep1

specialrun

end runtime

userid scott_1, password tiger

assumetargetdefs

extfile /u02/stage_ogg112_ora11/scott.dat

map scott.dept, target scott_1.dept;

map scott.emp, target scott_1.emp;

map scott.bonus, target scott_1.bonus;

map scott.salgrade, target scott_1.salgrade;

GGSCI (raclinux1.gj.com) 77>

Set ORACLE_SID=RACD1 and execute the command for the source extract. Look at the output in the Annex.

[oracle@raclinux1 stage_ogg112_ora11]$ extract paramfile /u02/stage_ogg112_ora11/dirprm/initext1.prm

Set ORACLE_SID=RACDB1 and execute the command for the replicat to load the data queued in scott.dat. Look at the output in the Annex.

[oracle@raclinux1 stage_ogg112_ora11]$ replicat paramfile /u02/stage_ogg112_ora11/dirprm/initrep1.prm

Transfer from scott on RACD to scott_2 on RACDB.

Create parameter files for each group extract and replicat. This uses a direct TCP/IP load.

GGSCI (raclinux1.gj.com) 77> view params initext2

extract initext2

SETENV (ORACLE_SID = “RACD1”)

userid ogg_extract, password ogg_extract

rmthost raclinux1, mgrport 7809

rmttask replicat, group initrep2

table scott.dept, keycols(deptno ,dname ,loc);

table scott.emp, keycols(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO);

table scott.bonus, keycols(ENAME, JOB, SAL, COMM) ;

table scott.salgrade, keycols(GRADE, LOSAL, HISAL);

GGSCI (raclinux1.gj.com) 78> view params initrep2

replicat initrep2

SETENV (ORACLE_SID = “RACDB1”)

userid ogg_replicat, password ogg_replicat

assumetargetdefs

map scott.dept, target scott_2.dept;

map scott.emp, target scott_2.emp;

map scott.bonus, target scott_2.bonus;

map scott.salgrade, target scott_2.salgrade;

GGSCI (raclinux1.gj.com) 79>

Add an extract on the source and replicat on the target.

GGSCI (raclinux1.gj.com) 3> add extract initext2, sourceistable

EXTRACT added.

GGSCI (raclinux1.gj.com) 4> add replicat initrep2, specialrun

REPLICAT added.

Start the extract. Do not start the replicat as it get started automatically by the extract.

GGSCI (raclinux1.gj.com) 1> start extract initext2

Sending START request to MANAGER …

EXTRACT INITEXT2 starting

GGSCI (raclinux1.gj.com) 2> info extract initext2

EXTRACT INITEXT2 Initialized 2012-07-06 00:16 Status RUNNING

Checkpoint Lag Not Available

Log Read Checkpoint Not Available

First Record Record 0

Task SOURCEISTABLE

GGSCI (raclinux1.gj.com) 3>

GGSCI (raclinux1.gj.com) 33> info extract initext2, detail

EXTRACT INITEXT2 Last Started 2012-07-06 01:34 Status STOPPED

Checkpoint Lag Not Available

Log Read Checkpoint Table SCOTT.SALGRADE

2012-07-06 01:35:05 Record 5

Task SOURCEISTABLE

Extract Source Begin End

Database 2012-07-06 01:34 2012-07-06 01:35

Database 2012-07-06 01:31 2012-07-06 01:31

Database 2012-07-06 01:27 2012-07-06 01:27

Database 2012-07-06 01:22 2012-07-06 01:22

Database 2012-07-06 01:16 2012-07-06 01:16

Database 2012-07-06 01:11 2012-07-06 01:11

Database 2012-07-06 01:07 2012-07-06 01:07

Database 2012-07-06 01:06 2012-07-06 01:06

Database * Initialized * First Record

Current directory /u02/stage_ogg112_ora11

Report file /u02/stage_ogg112_ora11/dirrpt/INITEXT2.rpt

Parameter file /u02/stage_ogg112_ora11/dirprm/initext2.prm

Checkpoint file /u02/stage_ogg112_ora11/dirchk/INITEXT2.cpe

Process file /u02/stage_ogg112_ora11/dirpcs/INITEXT2.pce

Stdout file /u02/stage_ogg112_ora11/dirout/INITEXT2.out

Error log /u02/stage_ogg112_ora11/ggserr.log

GGSCI (raclinux1.gj.com) 34>

GGSCI (raclinux1.gj.com) 33> INFO REPLICAT *, DETAIL, ALLPROCESSES

REPLICAT INITREP2 Initialized 2012-07-06 01:21 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:17:26 ago)

Log Read Checkpoint Not Available

Task SPECIALRUN

Extract Source Begin End

Not Available * Initialized * First Record

Current directory /u02/stage_ogg112_ora11

Report file /u02/stage_ogg112_ora11/dirrpt/INITREP2.rpt

Parameter file /u02/stage_ogg112_ora11/dirprm/initrep2.prm

Checkpoint file /u02/stage_ogg112_ora11/dirchk/INITREP2.cpr

Process file /u02/stage_ogg112_ora11/dirpcs/INITREP2.pcr

Stdout file /u02/stage_ogg112_ora11/dirout/INITREP2.out

Error log /u02/stage_ogg112_ora11/ggserr.log

GGSCI (raclinux1.gj.com) 34>

Report at 2012-07-06 01:35:05 (activity since 2012-07-06 01:34:44)

Output to initrep2:

From Table SCOTT.DEPT:

# inserts: 4

# updates: 0

# deletes: 0

# discards: 0

From Table SCOTT.EMP:

# inserts: 14

# updates: 0

# deletes: 0

# discards: 0

From Table SCOTT.SALGRADE:

# inserts: 5

# updates: 0

# deletes: 0

# discards: 0

REDO Log Statistics

Bytes parsed 0

Bytes output 3473

Report at 2012-07-06 01:35:10 (activity since 2012-07-06 01:35:04)

From Table SCOTT.DEPT to SCOTT_2.DEPT:

# inserts: 4

# updates: 0

# deletes: 0

# discards: 0

From Table SCOTT.EMP to SCOTT_2.EMP:

# inserts: 14

# updates: 0

# deletes: 0

# discards: 0

From Table SCOTT.SALGRADE to SCOTT_2.SALGRADE:

# inserts: 5

# updates: 0

# deletes: 0

# discards: 0

Transfer from scott on RACD to scott_3 on RACDB.

Create parameter files for each group extract and replicat. This uses a direct TCP/IP load wit sql loader.

Create a parameter file for the extract on the source system. Create a parameter file for the replicat on the target system

GGSCI (raclinux1.gj.com) 54> view params initext3

extract initext3

userid ogg_extract, password ogg_extract

rmthost raclinux1, mgrport 7809

rmttask replicat, group initrep3

table scott.dept, keycols(deptno ,dname ,loc);

table scott.emp, keycols(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO);

table scott.bonus, keycols(ENAME, JOB, SAL, COMM) ;

table scott.salgrade, keycols(GRADE, LOSAL, HISAL);

GGSCI (raclinux1.gj.com) 55>

GGSCI (raclinux1.gj.com) 38> view params initrep3

replicat initrep3

SETENV (ORACLE_SID = “RACDB1”)

userid ogg_replicat, password ogg_replicat

bulkload

assumetargetdefs

map scott.dept, target scott_3.dept;

map scott.emp, target scott_3.emp;

map scott.bonus, target scott_3.bonus;

map scott.salgrade, target scott_3.salgrade;

GGSCI (raclinux1.gj.com) 39>

Add an extract on the source system and add a replicat on the target system

GGSCI (raclinux1.gj.com) 55> add extract initext3, sourceistable

EXTRACT added.

GGSCI (raclinux1.gj.com) 56> add replicat initrep3, specialrun

REPLICAT added.

GGSCI (raclinux1.gj.com) 57>

Start the extract but do not start the replicat as it will be started automatically. Verify that target schema gets populated.

GGSCI (raclinux1.gj.com) 65> start extract initext3

Sending START request to MANAGER …

EXTRACT INITEXT3 starting

GGSCI (raclinux1.gj.com) 66>

GGSCI (raclinux1.gj.com) 68> info extract initext3, detail

EXTRACT INITEXT3 Last Started 2012-07-06 02:38 Status STOPPED

Checkpoint Lag Not Available

Log Read Checkpoint Table SCOTT.SALGRADE

2012-07-06 02:38:38 Record 5

Task SOURCEISTABLE

Extract Source Begin End

Database 2012-07-06 02:38 2012-07-06 02:38

Database 2012-07-06 02:36 2012-07-06 02:36

Database 2012-07-06 02:32 2012-07-06 02:32

Database * Initialized * First Record

Current directory /u02/stage_ogg112_ora11

Report file /u02/stage_ogg112_ora11/dirrpt/INITEXT3.rpt

Parameter file /u02/stage_ogg112_ora11/dirprm/initext3.prm

Checkpoint file /u02/stage_ogg112_ora11/dirchk/INITEXT3.cpe

Process file /u02/stage_ogg112_ora11/dirpcs/INITEXT3.pce

Stdout file /u02/stage_ogg112_ora11/dirout/INITEXT3.out

Error log /u02/stage_ogg112_ora11/ggserr.log

GGSCI (raclinux1.gj.com) 69>

Report at 2012-07-06 02:38:38 (activity since 2012-07-06 02:38:14)

Output to initrep3:

From Table SCOTT.DEPT:

# inserts: 4

# updates: 0

# deletes: 0

# discards: 0

From Table SCOTT.EMP:

# inserts: 14

# updates: 0

# deletes: 0

# discards: 0

From Table SCOTT.SALGRADE:

# inserts: 5

# updates: 0

# deletes: 0

# discards: 0

REDO Log Statistics

Bytes parsed 0

Bytes output 3473

GGSCI (raclinux1.gj.com) 56> info replicat initrep3, detail

REPLICAT INITREP3 Initialized 2012-07-06 02:31 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:10:48 ago)

Log Read Checkpoint Not Available

Task SPECIALRUN

Extract Source Begin End

Not Available * Initialized * First Record

Current directory /u02/stage_ogg112_ora11

Report file /u02/stage_ogg112_ora11/dirrpt/INITREP3.rpt

Parameter file /u02/stage_ogg112_ora11/dirprm/initrep3.prm

Checkpoint file /u02/stage_ogg112_ora11/dirchk/INITREP3.cpr

Process file /u02/stage_ogg112_ora11/dirpcs/INITREP3.pcr

Stdout file /u02/stage_ogg112_ora11/dirout/INITREP3.out

Error log /u02/stage_ogg112_ora11/ggserr.log

GGSCI (raclinux1.gj.com) 57>

Report at 2012-07-06 02:38:43 (activity since 2012-07-06 02:38:37)

From Table SCOTT.DEPT to SCOTT_3.DEPT:

# inserts: 4

# updates: 0

# deletes: 0

# discards: 0

From Table SCOTT.EMP to SCOTT_3.EMP:

# inserts: 14

# updates: 0

# deletes: 0

# discards: 0

From Table SCOTT.SALGRADE to SCOTT_3.SALGRADE:

# inserts: 5

# updates: 0

# deletes: 0

# discards: 0

In all three cases the initial data load was successful.

  1. Create and start CDC replicats to apply the data from the trail file captured by extract extcdc

Create checkpoint table on each target database. In my case it will be one checkpoint table on RACDB. Make sure that you modify the ./GLOBALS file with the name of the table assigned to the parameter checkpointtable.

GGSCI (raclinux1.gj.com) 95> view params ./GLOBALS

ggschema ddl_ogg

checkpointtable ddl_ogg.oggchkpt

GGSCI (raclinux1.gj.com) 96>

GGSCI (raclinux1.gj.com) 2> dblogin userid ddl_ogg

Password:

Successfully logged into database.

GGSCI (raclinux1.gj.com) 3>

GGSCI (raclinux1.gj.com) 4> ADD CHECKPOINTTABLE

No checkpoint table specified, using GLOBALS specification (ddl_ogg.oggchkpt)…

Successfully created checkpoint table ddl_ogg.oggchkpt.

GGSCI (raclinux1.gj.com) 5>

Create and start on the target systems the following replicats repcdc1, repcdc2 and repcdc3.

Create and start replicat repcdc1

GGSCI (raclinux1.gj.com) 7> view params repcdc1

replicat repcdc1

SETENV (ORACLE_SID = “RACDB1”)

userid ogg_replicat, password ogg_replicat

handlecollisions

sourcedefs ./dirdef/source.def

discardfile ./dirrpt/repcdc1.dsc, purge

map scott.dept, target scott_1.dept;

map scott.emp, target scott_1.emp;

map scott.bonus, target scott_1.bonus;

map scott.salgrade, target scott_1.salgrade;

GGSCI (raclinux1.gj.com) 8>

GGSCI (raclinux1.gj.com) 2> add replicat repcdc1, exttrail ./dirdat/aa

REPLICAT added.

GGSCI (raclinux1.gj.com) 3>

Start the replicat repcdc1 and check status and stats.

GGSCI (raclinux1.gj.com) 3>
start replicat repcdc1

Sending START request to MANAGER …

REPLICAT REPCDC1 starting

GGSCI (raclinux1.gj.com) 30>

GGSCI (raclinux1.gj.com) 21> info replicat repcdc1, detail

REPLICAT REPCDC1 Last Started 2012-07-07 00:11 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:08 ago)

Log Read Checkpoint File ./dirdat/aa000003

2012-07-07 00:32:10.000389 RBA 79173

Extract Source Begin End

./dirdat/aa000003 * Initialized * 2012-07-07 00:32

./dirdat/aa000000 * Initialized * First Record

Current directory /u02/stage_ogg112_ora11

Report file /u02/stage_ogg112_ora11/dirrpt/REPCDC1.rpt

Parameter file /u02/stage_ogg112_ora11/dirprm/repcdc1.prm

Checkpoint file /u02/stage_ogg112_ora11/dirchk/REPCDC1.cpr

Checkpoint table ddl_ogg.oggchkpt

Process file /u02/stage_ogg112_ora11/dirpcs/REPCDC1.pcr

Stdout file /u02/stage_ogg112_ora11/dirout/REPCDC1.out

Error log /u02/stage_ogg112_ora11/ggserr.log

GGSCI (raclinux1.gj.com) 22> info replicat repcdc1, detail

REPLICAT REPCDC1 Last Started 2012-07-07 00:11 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:03 ago)

Log Read Checkpoint File ./dirdat/aa000003

2012-07-07 00:32:10.000389 RBA 79173

Extract Source Begin End

./dirdat/aa000003 * Initialized * 2012-07-07 00:32

./dirdat/aa000000 * Initialized * First Record

Current directory /u02/stage_ogg112_ora11

Report file /u02/stage_ogg112_ora11/dirrpt/REPCDC1.rpt

Parameter file /u02/stage_ogg112_ora11/dirprm/repcdc1.prm

Checkpoint file /u02/stage_ogg112_ora11/dirchk/REPCDC1.cpr

Checkpoint table ddl_ogg.oggchkpt

Process file /u02/stage_ogg112_ora11/dirpcs/REPCDC1.pcr

Stdout file /u02/stage_ogg112_ora11/dirout/REPCDC1.out

Error log /u02/stage_ogg112_ora11/ggserr.log

GGSCI (raclinux1.gj.com) 23>

GGSCI (raclinux1.gj.com) 24> stats replicat repcdc1, total

Sending STATS request to REPLICAT REPCDC1 …

Start of Statistics at 2012-07-07 00:34:48.

Replicating from SCOTT.DEPT to SCOTT_1.DEPT:

*** Total statistics since 2012-07-07 00:32:19 ***

Total inserts 150.00

Total updates 150.00

Total deletes 150.00

Total discards 0.00

Total operations 450.00

Total update collisions 150.00

Total delete collisions 150.00

End of Statistics.

GGSCI (raclinux1.gj.com) 25>

Create and start replicat repcdc2

Create a parameter file for replicat repcdc2. Create the replicat. Start the replicat and check status and stats.

GGSCI (raclinux1.gj.com) 27> view params repcdc2

replicat repcdc2

SETENV (ORACLE_SID = “RACDB1”)

userid ogg_replicat, password ogg_replicat

handlecollisions

sourcedefs ./dirdef/source.def

discardfile ./dirrpt/repcdc2.dsc, purge

map scott.dept, target scott_2.dept;

map scott.emp, target scott_2.emp;

map scott.bonus, target scott_2.bonus;

map scott.salgrade, target scott_2.salgrade;

GGSCI (raclinux1.gj.com) 28>

GGSCI (raclinux1.gj.com) 28> add replicat repcdc2, exttrail ./dirdat/bb

REPLICAT added.

GGSCI (raclinux1.gj.com) 29>

GGSCI (raclinux1.gj.com) 29> start replicat repcdc2

Sending START request to MANAGER …

REPLICAT REPCDC2 starting

GGSCI (raclinux1.gj.com) 30> info replicat repcdc2, detail

REPLICAT REPCDC2 Last Started 2012-07-07 00:51 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:06 ago)

Log Read Checkpoint File ./dirdat/bb000003

2012-07-07 00:32:10.001274 RBA 79173

Extract Source Begin End

./dirdat/bb000003 * Initialized * 2012-07-07 00:32

./dirdat/bb000000 * Initialized * First Record

Current directory /u02/stage_ogg112_ora11

Report file /u02/stage_ogg112_ora11/dirrpt/REPCDC2.rpt

Parameter file /u02/stage_ogg112_ora11/dirprm/repcdc2.prm

Checkpoint file /u02/stage_ogg112_ora11/dirchk/REPCDC2.cpr

Checkpoint table ddl_ogg.oggchkpt

Process file /u02/stage_ogg112_ora11/dirpcs/REPCDC2.pcr

Stdout file /u02/stage_ogg112_ora11/dirout/REPCDC2.out

Error log /u02/stage_ogg112_ora11/ggserr.log

GGSCI (raclinux1.gj.com) 31> stats replicat repcdc2, total

Sending STATS request to REPLICAT REPCDC2 …

Start of Statistics at 2012-07-07 00:52:00.

Replicating from SCOTT.DEPT to SCOTT_2.DEPT:

*** Total statistics since 2012-07-07 00:51:17 ***

Total inserts 250.00

Total updates 250.00

Total deletes 250.00

Total discards 0.00

Total operations 750.00

Total update collisions 250.00

Total delete collisions 250.00

End of Statistics.

GGSCI (raclinux1.gj.com) 32>

Create and start replicat repcdc3

Create a parameter file for replicat repcdc3. Create the replicat. Start the replicat and check status and stats.

GGSCI (raclinux1.gj.com) 39> view params repcdc3

replicat repcdc3

SETENV (ORACLE_SID = “RACDB1”)

userid ogg_replicat, password ogg_replicat

handlecollisions

sourcedefs ./dirdef/source.def

discardfile ./dirrpt/repcdc3.dsc, purge

map scott.dept, target scott_3.dept;

map scott.emp, target scott_3.emp;

map scott.bonus, target scott_3.bonus;

map scott.salgrade, target scott_3.salgrade;

GGSCI (raclinux1.gj.com) 40>

GGSCI (raclinux1.gj.com) 41> add replicat repcdc3, exttrail ./dirdat/cc

REPLICAT added.

GGSCI (raclinux1.gj.com) 42>

GGSCI (raclinux1.gj.com) 42> start replicat repcdc3

Sending START request to MANAGER …

REPLICAT REPCDC3 starting

GGSCI (raclinux1.gj.com) 43> info replicat repcdc3, detail

REPLICAT REPCDC3 Last Started 2012-07-07 01:00 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:06 ago)

Log Read Checkpoint File ./dirdat/cc000003

2012-07-07 00:32:10.001334 RBA 79173

Extract Source Begin End

./dirdat/cc000003 * Initialized * 2012-07-07 00:32

./dirdat/cc000000 * Initialized * First Record

Current directory /u02/stage_ogg112_ora11

Report file /u02/stage_ogg112_ora11/dirrpt/REPCDC3.rpt

Parameter file /u02/stage_ogg112_ora11/dirprm/repcdc3.prm

Checkpoint file /u02/stage_ogg112_ora11/dirchk/REPCDC3.cpr

Checkpoint table ddl_ogg.oggchkpt

Process file /u02/stage_ogg112_ora11/dirpcs/REPCDC3.pcr

Stdout file /u02/stage_ogg112_ora11/dirout/REPCDC3.out

Error log /u02/stage_ogg112_ora11/ggserr.log

GGSCI (raclinux1.gj.com) 44>

GGSCI (raclinux1.gj.com) 44> stats replicat repcdc3, total

Sending STATS request to REPLICAT REPCDC3 …

Start of Statistics at 2012-07-07 01:01:18.

Replicating from SCOTT.DEPT to SCOTT_3.DEPT:

*** Total statistics since 2012-07-07 01:00:01 ***

Total inserts 600.00

Total updates 550.00

Total deletes 550.00

Total discards 0.00

Total operations 1700.00

Total update collisions 550.00

Total delete collisions 500.00

End of Statistics.

GGSCI (raclinux1.gj.com) 45>

Repeat the same procedure for repcdc3. Create and start replicat repcdc3. Create a parameter file for replicat repcdc3. Create the replicat. Start the replicat and check status and stats.

GGSCI (raclinux1.gj.com) 39> view params repcdc3

replicat repcdc3

SETENV (ORACLE_SID = “RACDB1”)

userid ogg_replicat, password ogg_replicat

handlecollisions

sourcedefs ./dirdef/source.def

discardfile ./dirrpt/repcdc3.dsc, purge

map scott.dept, target scott_3.dept;

map scott.emp, target scott_3.emp;

map scott.bonus, target scott_3.bonus;

map scott.salgrade, target scott_3.salgrade;

GGSCI (raclinux1.gj.com) 40>

GGSCI (raclinux1.gj.com) 41> add replicat repcdc3, exttrail ./dirdat/cc

REPLICAT added.

GGSCI (raclinux1.gj.com) 42>

GGSCI (raclinux1.gj.com) 42> start replicat repcdc3

Sending START request to MANAGER …

REPLICAT REPCDC3 starting

GGSCI (raclinux1.gj.com) 43> info replicat repcdc3, detail

REPLICAT REPCDC3 Last Started 2012-07-07 01:00 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:06 ago)

Log Read Checkpoint File ./dirdat/cc000003

2012-07-07 00:32:10.001334 RBA 79173

Extract Source Begin End

./dirdat/cc000003 * Initialized * 2012-07-07 00:32

./dirdat/cc000000 * Initialized * First Record

Current directory /u02/stage_ogg112_ora11

Report file /u02/stage_ogg112_ora11/dirrpt/REPCDC3.rpt

Parameter file /u02/stage_ogg112_ora11/dirprm/repcdc3.prm

Checkpoint file /u02/stage_ogg112_ora11/dirchk/REPCDC3.cpr

Checkpoint table ddl_ogg.oggchkpt

Process file /u02/stage_ogg112_ora11/dirpcs/REPCDC3.pcr

Stdout file /u02/stage_ogg112_ora11/dirout/REPCDC3.out

Error log /u02/stage_ogg112_ora11/ggserr.log

GGSCI (raclinux1.gj.com) 44>

GGSCI (raclinux1.gj.com) 44> stats replicat repcdc3, total

Sending STATS request to REPLICAT REPCDC3 …

Start of Statistics at 2012-07-07 01:01:18.

Replicating from SCOTT.DEPT to SCOTT_3.DEPT:

*** Total statistics since 2012-07-07 01:00:01 ***

Total inserts 600.00

Total updates 550.00

Total deletes 550.00

Total discards 0.00

Total operations 1700.00

Total update collisions 550.00

Total delete collisions 500.00

End of Statistics.

GGSCI (raclinux1.gj.com) 45>

  1. Verify that CDC extract and respective replicat apply works

    After all groups are running I will simulate some transactions on the source system using the following script.

    SQL> !cat /tmp/s.sql

    DECLARE

    x NUMBER := 100;

    BEGIN

    FOR i IN 50..99 LOOP

    delete from dept where deptno=i;

    commit;

    x := x + 100;

    END LOOP;

    FOR i IN 50..99 LOOP

    IF MOD(i,2) = 0 THEN — i is even

    INSERT INTO dept VALUES (i, ‘Dep even ‘||X, ‘Loc ‘||X);

    ELSE

    INSERT INTO dept VALUES (i, ‘Dep odd ‘||X, ‘Loc ‘||X);

    END IF;

    commit;

    x := x + 100;

    END LOOP;

    COMMIT;

    FOR i IN 50..99 LOOP

    update dept set dname=’Dep ‘||x where deptno=i;

    commit;

    x := x + 100;

    END LOOP;

    COMMIT;

    END;

    SQL>

    GGSCI (raclinux1.gj.com) 85> info all

    Program Status Group Lag at Chkpt Time Since Chkpt

    MANAGER RUNNING

    EXTRACT RUNNING EXTCDC 00:00:00 00:00:04

    EXTRACT RUNNING EXTSALE 00:00:00 00:00:01

    EXTRACT RUNNING PUMPSALE 00:00:00 00:00:00

    REPLICAT RUNNING REPCDC1 00:00:00 00:00:03

    REPLICAT RUNNING REPCDC2 00:00:00 00:00:02

    REPLICAT RUNNING REPCDC3 00:00:00 00:00:05

    REPLICAT RUNNING REPCUST 00:00:00 00:00:10

    REPLICAT RUNNING REPSALE 00:00:00 00:00:02

    GGSCI (raclinux1.gj.com) 86>

    Initially before execution I have

    GGSCI (raclinux1.gj.com) 84> stats extract extcdc

    Sending STATS request to EXTRACT EXTCDC …

    No active extraction maps.

    GGSCI (raclinux1.gj.com) 85>

    GGSCI (raclinux1.gj.com) 7> stats replicat repcdc1

    Sending STATS request to REPLICAT REPCDC1 …

    No active replication maps.

    GGSCI (raclinux1.gj.com) 8> stats replicat repcdc2

    Sending STATS request to REPLICAT REPCDC2 …

    No active replication maps.

    GGSCI (raclinux1.gj.com) 9> stats replicat repcdc3

    Sending STATS request to REPLICAT REPCDC3 …

    No active replication maps.

    GGSCI (raclinux1.gj.com) 10>

    After the script execution on the source I have

    GGSCI (raclinux1.gj.com) 86> stats extract extcdc

    Sending STATS request to EXTRACT EXTCDC …

    Start of Statistics at 2012-07-08 21:32:52.

    Output to ./dirdat/aa:

    Extracting from SCOTT.DEPT to SCOTT.DEPT:

    *** Total statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    *** Daily statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    *** Hourly statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    *** Latest statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    Output to ./dirdat/bb:

    Extracting from SCOTT.DEPT to SCOTT.DEPT:

    *** Total statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    *** Daily statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    *** Hourly statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    *** Latest statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    Output to ./dirdat/cc:

    Extracting from SCOTT.DEPT to SCOTT.DEPT:

    *** Total statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    *** Daily statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    *** Hourly statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    *** Latest statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    End of Statistics.

    GGSCI (raclinux1.gj.com) 87>

    GGSCI (raclinux1.gj.com) 10> stats replicat repcdc1

    Sending STATS request to REPLICAT REPCDC1 …

    Start of Statistics at 2012-07-08 21:33:26.

    Replicating from SCOTT.DEPT to SCOTT_1.DEPT:

    *** Total statistics since 2012-07-08 21:32:57 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    *** Daily statistics since 2012-07-08 21:32:57 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    *** Hourly statistics since 2012-07-08 21:32:57 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    *** Latest statistics since 2012-07-08 21:32:57 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    End of Statistics.

    GGSCI (raclinux1.gj.com) 11>

    GGSCI (raclinux1.gj.com) 11> stats replicat repcdc2

    Sending STATS request to REPLICAT REPCDC2 …

    Start of Statistics at 2012-07-08 21:33:56.

    Replicating from SCOTT.DEPT to SCOTT_2.DEPT:

    *** Total statistics since 2012-07-08 21:32:56 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    *** Daily statistics since 2012-07-08 21:32:56 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    *** Hourly statistics since 2012-07-08 21:32:56 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    *** Latest statistics since 2012-07-08 21:32:56 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    End of Statistics.

    GGSCI (raclinux1.gj.com) 12>

    GGSCI (raclinux1.gj.com) 22> stats replicat repcdc3

    Sending STATS request to REPLICAT REPCDC3 …

    Start of Statistics at 2012-07-08 21:53:11.

    Replicating from SCOTT.DEPT to SCOTT_3.DEPT:

    *** Total statistics since 2012-07-08 21:52:42 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    *** Daily statistics since 2012-07-08 21:52:42 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    *** Hourly statistics since 2012-07-08 21:52:42 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    *** Latest statistics since 2012-07-08 21:52:42 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 50.00

    Total discards 0.00

    Total operations 150.00

    End of Statistics.

    GGSCI (raclinux1.gj.com) 23>

    On the source RACD issue the following delete.

    SQL> delete from dept where deptno>40;

    commit;

    50 rows deleted.

    SQL> commit;

    Commit complete.

    SQL>

    GGSCI (raclinux1.gj.com) 92> stats extract extcdc

    Sending STATS request to EXTRACT EXTCDC …

    Start of Statistics at 2012-07-08 21:56:15.

    Output to ./dirdat/aa:

    Extracting from SCOTT.DEPT to SCOTT.DEPT:

    *** Total statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    *** Daily statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    *** Hourly statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    *** Latest statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    Output to ./dirdat/bb:

    Extracting from SCOTT.DEPT to SCOTT.DEPT:

    *** Total statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    *** Daily statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    *** Hourly statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    *** Latest statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    Output to ./dirdat/cc:

    Extracting from SCOTT.DEPT to SCOTT.DEPT:

    *** Total statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    *** Daily statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    *** Hourly statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    *** Latest statistics since 2012-07-08 21:32:46 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    End of Statistics.

    GGSCI (raclinux1.gj.com) 93>

    GGSCI (raclinux1.gj.com) 23> stats replicat repcdc1

    Sending STATS request to REPLICAT REPCDC1 …

    Start of Statistics at 2012-07-08 21:57:12.

    Replicating from SCOTT.DEPT to SCOTT_1.DEPT:

    *** Total statistics since 2012-07-08 21:32:57 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    *** Daily statistics since 2012-07-08 21:32:57 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    *** Hourly statistics since 2012-07-08 21:32:57 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    *** Latest statistics since 2012-07-08 21:32:57 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    End of Statistics.

    GGSCI (raclinux1.gj.com) 24>

    GGSCI (raclinux1.gj.com) 24> stats replicat repcdc2

    Sending STATS request to REPLICAT REPCDC2 …

    Start of Statistics at 2012-07-08 21:57:36.

    Replicating from SCOTT.DEPT to SCOTT_2.DEPT:

    *** Total statistics since 2012-07-08 21:32:56 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    *** Daily statistics since 2012-07-08 21:32:56 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    *** Hourly statistics since 2012-07-08 21:32:56 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    *** Latest statistics since 2012-07-08 21:32:56 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    End of Statistics.

    GGSCI (raclinux1.gj.com) 25>

    GGSCI (raclinux1.gj.com) 25> stats replicat repcdc3

    Sending STATS request to REPLICAT REPCDC3 …

    Start of Statistics at 2012-07-08 21:58:07.

    Replicating from SCOTT.DEPT to SCOTT_3.DEPT:

    *** Total statistics since 2012-07-08 21:52:42 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    *** Daily statistics since 2012-07-08 21:52:42 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    *** Hourly statistics since 2012-07-08 21:52:42 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    *** Latest statistics since 2012-07-08 21:52:42 ***

    Total inserts 50.00

    Total updates 50.00

    Total deletes 100.00

    Total discards 0.00

    Total operations 200.00

    End of Statistics.

    GGSCI (raclinux1.gj.com) 26>

  2. Modify the replicats to operate in NOHANDLECOLLISION

    Issue the following commands

    send replicat repcdc1, nohandlecollisions

    send replicat repcdc2, nohandlecollisions

    send replicat repcdc3, nohandlecollisions

    mask off the HANDLECOLLISIONS in the parameter file of the replicats repcdc1, repcdc2, repcdc3

    GGSCI (raclinux1.gj.com) 39> view params repcdc3

    replicat repcdc3

    SETENV (ORACLE_SID = “RACDB1”)

    userid ogg_replicat, password ogg_replicat

    –handlecollisions

    sourcedefs ./dirdef/source.def

    discardfile ./dirrpt/repcdc3.dsc, purge

    map scott.dept, target scott_3.dept;

    map scott.emp, target scott_3.emp;

    map scott.bonus, target scott_3.bonus;

    map scott.salgrade, target scott_3.salgrade;

    GGSCI (raclinux1.gj.com) 40>

    GGSCI (raclinux1.gj.com) 7> view params repcdc1

    replicat repcdc1

    SETENV (ORACLE_SID = “RACDB1”)

    userid ogg_replicat, password ogg_replicat

    –handlecollisions

    sourcedefs ./dirdef/source.def

    discardfile ./dirrpt/repcdc1.dsc, purge

    map scott.dept, target scott_1.dept;

    map scott.emp, target scott_1.emp;

    map scott.bonus, target scott_1.bonus;

    map scott.salgrade, target scott_1.salgrade;

    GGSCI (raclinux1.gj.com) 8>

    GGSCI (raclinux1.gj.com) 27> view params repcdc2

    replicat repcdc2

    SETENV (ORACLE_SID = “RACDB1”)

    userid ogg_replicat, password ogg_replicat

    –handlecollisions

    sourcedefs ./dirdef/source.def

    discardfile ./dirrpt/repcdc2.dsc, purge

    map scott.dept, target scott_2.dept;

    map scott.emp, target scott_2.emp;

    map scott.bonus, target scott_2.bonus;

    map scott.salgrade, target scott_2.salgrade;

    GGSCI (raclinux1.gj.com) 28>

  1. This concludes the setup and test.

Summary

In the article you have a look at how to install OGG on two node Oracle 11.2.0.3 cluster running Oracle 11.2.0.3 database. You looked at an example of configuring a classic extract CDC, performed a initial data load and applied changes accumulated into a trail to catch-up with the source. You looked at how to conduct a test and verify that OCC replicates properly the changes on the source to the targets.

Annex

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

[oracle@raclinux1 stage_ogg112_ora11]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 5 17:05:07 2012

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, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> @sequence.sql

Please enter the name of a schema for the GoldenGate database objects:

ddl_ogg

Setting schema name to DDL_OGG

UPDATE_SEQUENCE STATUS:

Line/pos

—————————————-

Error

—————————————————————–

No errors

No errors

GETSEQFLUSH

Line/pos

—————————————-

Error

—————————————————————–

No errors

No errors

SEQTRACE

Line/pos

—————————————-

Error

—————————————————————–

No errors

No errors

REPLICATE_SEQUENCE STATUS:

Line/pos

—————————————-

Error

—————————————————————–

No errors

No errors

STATUS OF SEQUENCE SUPPORT

————————————————————–

SUCCESSFUL installation of Oracle Sequence Replication support

SQL>

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SQL> connect / as sysdba

Connected.

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:ddl_ogg

Marker setup table script complete, running verification script…

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to DDL_OGG

MARKER TABLE

——————————-

OK

MARKER SEQUENCE

——————————-

OK

Script complete.

SQL>

++++++++++++++++

+++++++++++++++++++++++++++++++

SQL>

@ddl_setup.sqlSQL>

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:ddl_ogg

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 DDL_OGG 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 DDL_OGG

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/racd/RACD1/trace/ggs_ddl_trace.log

Analyzing installation status…

STATUS OF DDL REPLICATION

————————————————————————————————————————

SUCCESSFUL installation of DDL Replication software components

Script complete.

SQL>

++++++++++++++++++++++++++++++++

++++++++++++++++++++++++++++

SQL> @role_setup.sql

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE

To 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:ddl_ogg

Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.

Role setup script complete

Grant 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>

++++++++++++++++++++++++++++

[oracle@raclinux1 stage_ogg112_ora11]$ extract paramfile /u02/stage_ogg112_ora11/dirprm/initext1.prm

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

Oracle GoldenGate Capture for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:42:16

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

Starting at 2012-07-05 22:50:50

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

Operating System Version:

Linux

Version #1 SMP Thu Sep 3 04:15:13 EDT 2009, Release 2.6.18-164.el5

Node: raclinux1.gj.com

Machine: x86_64

soft limit hard limit

Address Space Size : unlimited unlimited

Heap Size : unlimited unlimited

File Size : unlimited unlimited

CPU Time : unlimited unlimited

Process id: 17637

Description:

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

** Running with the following parameters **

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

2012-07-05 22:50:50 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

sourceistable

2012-07-05 22:50:50 INFO OGG-01017 Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.

userid ogg_extract, password ***********

2012-07-05 22:50:51 INFO OGG-03500 WARNING: NLS_LANG environment variable does not match database character set, or not set. Using database character set value of AL32UTF8.

rmthost raclinux1, mgrport 7809

rmtfile /u02/stage_ogg112_ora11/scott.dat, purge

table scott.dept, keycols(deptno ,dname ,loc);

Using the following key columns for source table SCOTT.DEPT: DEPTNO, DNAME, LOC.

table scott.emp, keycols(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO);

Using the following key columns for source table SCOTT.EMP: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO.

table scott.bonus, keycols(ENAME, JOB, SAL, COMM) ;

Using the following key columns for source table SCOTT.BONUS: ENAME, JOB, SAL, COMM.

table scott.salgrade, keycols(GRADE, LOSAL, HISAL);

Using the following key columns for source table SCOTT.SALGRADE: GRADE, LOSAL, HISAL.

2012-07-05 22:51:18 INFO OGG-01815 Virtual Memory Facilities for: COM

anon alloc: mmap(MAP_ANON) anon free: munmap

file alloc: mmap(MAP_SHARED) file free: munmap

target directories:

/u02/stage_ogg112_ora11/dirtmp.

CACHEMGR virtual memory values (may have been adjusted)

CACHESIZE: 64G

CACHEPAGEOUTSIZE (normal): 8M

PROCESS VM AVAIL FROM OS (min): 128G

CACHESIZEMAX (strict force to disk): 96G

Database Version:

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

PL/SQL Release 11.2.0.3.0 – Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

Database Language and Character Set:

NLS_LANG = “.AL32UTF8”

NLS_LANGUAGE = “AMERICAN”

NLS_TERRITORY = “AMERICA”

NLS_CHARACTERSET = “AL32UTF8”

2012-07-05 22:51:22 INFO OGG-01478 Output file /u02/stage_ogg112_ora11/scott.dat is using format RELEASE 11.2.

2012-07-05 22:51:33 INFO OGG-01226 Socket buffer size set to 27985 (flush size 27985).

Processing table SCOTT.DEPT

Processing table SCOTT.EMP

Processing table SCOTT.BONUS

Processing table SCOTT.SALGRADE

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

* ** Run Time Statistics ** *

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

Report at 2012-07-05 22:51:38 (activity since 2012-07-05 22:50:57)

Output to /u02/stage_ogg112_ora11/scott.dat:

From Table SCOTT.DEPT:

# inserts: 4

# updates: 0

# deletes: 0

# discards: 0

From Table SCOTT.EMP:

# inserts: 14

# updates: 0

# deletes: 0

# discards: 0

From Table SCOTT.SALGRADE:

# inserts: 5

# updates: 0

# deletes: 0

# discards: 0

REDO Log Statistics

Bytes parsed 0

Bytes output 3473

[oracle@raclinux1 stage_ogg112_ora11]$

[oracle@raclinux1 stage_ogg112_ora11]$ replicat paramfile /u02/stage_ogg112_ora11/dirprm/initrep1.prm

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

Oracle GoldenGate Delivery for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:48:07

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

Starting at 2012-07-05 23:40:10

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

Operating System Version:

Linux

Version #1 SMP Thu Sep 3 04:15:13 EDT 2009, Release 2.6.18-164.el5

Node: raclinux1.gj.com

Machine: x86_64

soft limit hard limit

Address Space Size : unlimited unlimited

Heap Size : unlimited unlimited

File Size : unlimited unlimited

CPU Time : unlimited unlimited

Process id: 351

Description:

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

** Running with the following parameters **

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

2012-07-05 23:40:10 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

specialrun

end runtime

userid scott_1, password *****

2012-07-05 23:40:11 INFO OGG-03501 WARNING: NLS_LANG environment variable is invalid or not set. Using operating system character set value of AL32UTF8.

assumetargetdefs

extfile /u02/stage_ogg112_ora11/scott.dat

map scott.dept, target scott_1.dept;

map scott.emp, target scott_1.emp;

map scott.bonus, target scott_1.bonus;

map scott.salgrade, target scott_1.salgrade;

2012-07-05 23:40:12 INFO OGG-01815 Virtual Memory Facilities for: COM

anon alloc: mmap(MAP_ANON) anon free: munmap

file alloc: mmap(MAP_SHARED) file free: munmap

target directories:

/u02/stage_ogg112_ora11/dirtmp.

CACHEMGR virtual memory values (may have been adjusted)

CACHESIZE: 2G

CACHEPAGEOUTSIZE (normal): 8M

PROCESS VM AVAIL FROM OS (min): 4G

CACHESIZEMAX (strict force to disk): 3.41G

Database Version:

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

PL/SQL Release 11.2.0.3.0 – Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

Database Language and Character Set:

NLS_LANG = “.AL32UTF8”

NLS_LANGUAGE = “AMERICAN”

NLS_TERRITORY = “AMERICA”

NLS_CHARACTERSET = “AL32UTF8”

Opened trail file /u02/stage_ogg112_ora11/scott.dat at 2012-07-05 23:40:13

2012-07-05 23:40:15 INFO OGG-01014 Positioning with begin time: Jan 1, 1970 12:00:00 AM, starting record time: Jul 5, 2012 10:51:35 PM at extrba 1016.

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

** Run Time Messages **

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

Opened trail file /u02/stage_ogg112_ora11/scott.dat at 2012-07-05 23:40:15

MAP resolved (entry scott.dept):

map “SCOTT”.”DEPT”, target scott_1.dept;

2012-07-05 23:40:24 WARNING OGG-00869 No unique key is defined for table ‘DEPT’. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Using following columns in default map by name:

DEPTNO, DNAME, LOC

Using the following key columns for target table SCOTT_1.DEPT: DEPTNO, DNAME, LOC.

MAP resolved (entry scott.emp):

map “SCOTT”.”EMP”, target scott_1.emp;

2012-07-05 23:40:25 WARNING OGG-00869 No unique key is defined for table ‘EMP’. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Using following columns in default map by name:

EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO

Using the following key columns for target table SCOTT_1.EMP: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO.

MAP resolved (entry scott.salgrade):

map “SCOTT”.”SALGRADE”, target scott_1.salgrade;

2012-07-05 23:40:26 WARNING OGG-00869 No unique key is defined for table ‘SALGRADE’. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Using following columns in default map by name:

GRADE, LOSAL, HISAL

Using the following key columns for target table SCOTT_1.SALGRADE: GRADE, LOSAL, HISAL.

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

* ** Run Time Statistics ** *

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

Last record for the last committed transaction is the following:

___________________________________________________________________

Trail name : /u02/stage_ogg112_ora11/scott.dat

Hdr-Ind : E (x45) Partition : . (x04)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 33 (x0021) IO Time : 2012-07-05 22:51:37.755766

IOType : 5 (x05) OrigNode : 255 (xff)

TransInd : . (x03) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 0 AuditPos : 0

Continued : N (x00) RecCount : 1 (x01)

2012-07-05 22:51:37.755766 Insert Len 33 RBA 4391

Name: SCOTT.SALGRADE

___________________________________________________________________

Reading /u02/stage_ogg112_ora11/scott.dat, current RBA 4489, 23 records

Report at 2012-07-05 23:40:26 (activity since 2012-07-05 23:40:24)

From Table SCOTT.DEPT to SCOTT_1.DEPT:

# inserts: 4

# updates: 0

# deletes: 0

# discards: 0

From Table SCOTT.EMP to SCOTT_1.EMP:

# inserts: 14

# updates: 0

# deletes: 0

# discards: 0

From Table SCOTT.SALGRADE to SCOTT_1.SALGRADE:

# inserts: 5

# updates: 0

# deletes: 0

# discards: 0

Last log location read:

FILE: /u02/stage_ogg112_ora11/scott.dat

RBA: 4489

TIMESTAMP: 2012-07-05 22:51:37.755766

EOF: NO

READERR: 400

CACHE OBJECT MANAGER statistics

CACHE MANAGER VM USAGE

vm current = 0 vm anon queues = 0

vm anon in use = 0 vm file = 0

vm used max = 0 ==> CACHE BALANCED

CACHE CONFIGURATION

cache size = 2G cache force paging = 3.41G

buffer min = 64K buffer highwater = 8M

pageout eligible size = 8M

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

RUNTIME STATS FOR SUPERPOOL

CACHE Transaction Stats

trans active = 0 max concurrent = 0

non-zero total = 0 trans total = 0

CACHE File Caching

disk current = 0 disk total = 0

disk caching = 0 file cached = 0

file retrieves = 0

CACHE MANAGEMENT

buffer links = 0 anon gets = 0

forced unmaps = 0 cnnbl try = 0

cached out = 0 force out = 0

Allocation Request Distribution

< 128B: 0

128B: 0 0 | 512B: 0 0

2K: 0 0 | 8K: 0 0

32K: 0 0 | 128K: 0 0

512K: 0 0 | 2M: 0 0

8M: 0 0 | 32M: 0 0

128M: 0 0 | 512M: 0 0

2G: 0 0 | 8G: 0

Cached Transaction Size Distribution

0: 0

< 4K: 0

4K: 0 0 | 16K: 0 0

64K: 0 0 | 256K: 0 0

1M: 0 0 | 4M: 0 0

16M: 0 0 | 64M: 0 0

256M: 0 0 | 1G: 0 0

4G: 0 0 | 16G: 0 0

64G: 0 0 | 256G: 0 0

1T: 0 0 | 4T: 0 0

16T: 0 0 | 64T: 0 0

256T: 0 0 |1024T: 0 0

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

CUMULATIVE STATS FOR SUPERPOOL

CACHE Transaction Stats

trans active = 0 max concurrent = 0

non-zero total = 0 trans total = 0

CACHE File Caching

disk current = 0 disk total = 0

disk caching = 0 file cached = 0

file retrieves = 0

CACHE MANAGEMENT

buffer links = 0 anon gets = 0

forced unmaps = 0 cnnbl try = 0

cached out = 0 force out = 0

Allocation Request Distribution

< 128B: 0

128B: 0 0 | 512B: 0 0

2K: 0 0 | 8K: 0 0

32K: 0 0 | 128K: 0 0

512K: 0 0 | 2M: 0 0

8M: 0 0 | 32M: 0 0

128M: 0 0 | 512M: 0 0

2G: 0 0 | 8G: 0

Cached Transaction Size Distribution

0: 0

< 4K: 0

4K: 0 0 | 16K: 0 0

64K: 0 0 | 256K: 0 0

1M: 0 0 | 4M: 0 0

16M: 0 0 | 64M: 0 0

256M: 0 0 | 1G: 0 0

4G: 0 0 | 16G: 0 0

64G: 0 0 | 256G: 0 0

1T: 0 0 | 4T: 0 0

16T: 0 0 | 64T: 0 0

256T: 0 0 |1024T: 0 0

QUEUE Statistics:

num queues = 15 default index = 0

cur len = 0 max len = 0

q vm current = 0 vm max = 0

q hits = 0 q misses = 0

queue size q hits curlen maxlen cannibalized

0 64K 0 0 0 0

1 128K 0 0 0 0

2 256K 0 0 0 0

3 512K 0 0 0 0

4 1M 0 0 0 0

5 2M 0 0 0 0

6 4M 0 0 0 0

7 8M 0 0 0 0

8 16M 0 0 0 0

9 32M 0 0 0 0

10 64M 0 0 0 0

11 128M 0 0 0 0

12 256M 0 0 0 0

13 512M 0 0 0 0

14 1G 0 0 0 0

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

RUNTIME STATS FOR CACHE POOL #0

POOL INFO group: initrep1 id: p351_BLOB

trans active = 0 trans concurrent (max) = 0

trans total = 0 (0 )

flag = 0x00000030

last error = (0=<none>)

Allocation Request Distribution

< 128B: 0

128B: 0 0 | 512B: 0 0

2K: 0 0 | 8K: 0 0

32K: 0 0 | 128K: 0 0

512K: 0 0 | 2M: 0 0

8M: 0 0 | 32M: 0 0

128M: 0 0 | 512M: 0 0

2G: 0 0 | 8G: 0

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

CUMULATIVE STATS FOR CACHE POOL #0

POOL INFO group: initrep1 id: p351_BLOB

trans active = 0 trans concurrent (max) = 0

trans total = 0 (0 )

flag = 0x00000030

last error = (0=<none>)

Allocation Request Distribution

< 128B: 0

128B: 0 0 | 512B: 0 0

2K: 0 0 | 8K: 0 0

32K: 0 0 | 128K: 0 0

512K: 0 0 | 2M: 0 0

8M: 0 0 | 32M: 0 0

128M: 0 0 | 512M: 0 0

2G: 0 0 | 8G: 0

QUEUE Statistics:

num queues = 15 default index = 0

cur len = 0 max len = 0

q vm current = 0 vm max = 0

q hits = 0 q misses = 0

queue size q hits curlen maxlen cannibalized

0 64K 0 0 0 0

1 128K 0 0 0 0

2 256K 0 0 0 0

3 512K 0 0 0 0

4 1M 0 0 0 0

5 2M 0 0 0 0

6 4M 0 0 0 0

7 8M 0 0 0 0

8 16M 0 0 0 0

9 32M 0 0 0 0

10 64M 0 0 0 0

11 128M 0 0 0 0

12 256M 0 0 0 0

13 512M 0 0 0 0

14 1G 0 0 0 0

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

RUNTIME STATS FOR CACHE POOL #0

POOL INFO group: initrep1 id: p351_BLOB

trans active = 0 trans concurrent (max) = 0

trans total = 0 (0 )

flag = 0x00000030

last error = (0=<none>)

Allocation Request Distribution

< 128B: 0

128B: 0 0 | 512B: 0 0

2K: 0 0 | 8K: 0 0

32K: 0 0 | 128K: 0 0

512K: 0 0 | 2M: 0 0

8M: 0 0 | 32M: 0 0

128M: 0 0 | 512M: 0 0

2G: 0 0 | 8G: 0

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

CUMULATIVE STATS FOR CACHE POOL #0

POOL INFO group: initrep1 id: p351_BLOB

trans active = 0 trans concurrent (max) = 0

trans total = 0 (0 )

flag = 0x00000030

last error = (0=<none>)

Allocation Request Distribution

< 128B: 0

128B: 0 0 | 512B: 0 0

2K: 0 0 | 8K: 0 0

32K: 0 0 | 128K: 0 0

512K: 0 0 | 2M: 0 0

8M: 0 0 | 32M: 0 0

128M: 0 0 | 512M: 0 0

2G: 0 0 | 8G: 0

[oracle@raclinux1 stage_ogg112_ora11]$


July 8, 2012 - Posted by | oracle

3 Comments »

  1. […] could read online from here or download Installing Oracle GoldenGate (OGG) 11.2.1.0.1 in a Oracle cluster 11.2.0.3 (ACFS) and […]

    Pingback by Installing Oracle GoldenGate (OGG) 11.2.1.0.1 in an Oracle cluster 11.2.0.3 (ACFS) and configuring a direct load and classic Change Data Capture (CDC) and Data Apply – Download « Guenadi N Jilevski's Oracle BLOG | July 8, 2012 | Reply

  2. my linux version is 2.6.18-8.el5 i686.any one tell me where can i download asmlibrarys rpm

    Comment by Venkat Kuncham | August 22, 2012 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: