installing oracle goldengate (ogg) 11.2.1.0.1 in an … · installing oracle goldengate (ogg) ......

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

http://gjilevski.wordpress.com/2011/11/13/upgrade-oracle-rac-to-11-2-0-3-from-11-2-0-2-on-linux/#more-4117

http://gjilevski.wordpress.com/2011/09/29/virtualization-using-oracle-vm-virtualbox-for-building-two-node-oracle-rac-11gr2-11-2-0-2-cluster-where-oracle-11-2-0-2-rdbms-coexists-with-10gr2-10-2-0-5-rdbms-based-on-linux-oel-5-4-2/

http://docs.oracle.com/cd/E35209_01/doc.1121/e29797.pdf

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.

2. 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.

3. 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

https://edelivery.oracle.com/

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

4. 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>

5. 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.

http://docs.oracle.com/cd/E35209_01/doc.1121/e29797.pdf

Enter Oracle GoldenGate schema name:ddl_ogg

Marker setup table script complete, running verification script...



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.




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.



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


6. 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> info mgr

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


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

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



Password:


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.


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);


rmttrail ./dirdat/bb






rmttrail ./dirdat/cc






Add the extcdc extract and the remote trails

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

EXTRACT added.



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.


Start the extract

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

Sending START request to MANAGER ...

EXTRACT EXTCDC starting


Verify that extract is running

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


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


00:28


00:23


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


7.2 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



direct TCP/IP load



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



rmtfile /u02/stage_ogg112_ora11/scott.dat, purge





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;


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



This uses a direct TCP/IP load.

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

extract initext2

SETENV (ORACLE_SID = "RACD1")



rmttask replicat, group initrep2





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

replicat initrep2

SETENV (ORACLE_SID = "RACDB1")

userid ogg_replicat, password ogg_replicat

assumetargetdefs






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


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) 33> info extract initext2, detail

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


Log Read Checkpoint Table SCOTT.SALGRADE

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

Task SOURCEISTABLE


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


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



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

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



Task SPECIALRUN


Not Available * Initialized * First Record


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



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


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



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



rmttask replicat, group initrep3






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

replicat initrep3



bulkload

assumetargetdefs






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.


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


EXTRACT INITEXT3 starting


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

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


Log Read Checkpoint Table SCOTT.SALGRADE

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

Task SOURCEISTABLE


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


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




Output to initrep3:


# inserts: 4

# updates: 0

# deletes: 0

# discards: 0


# inserts: 14

# updates: 0

# deletes: 0

# discards: 0


# 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



Task SPECIALRUN


Not Available * Initialized * First Record


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





# inserts: 4

# updates: 0

# deletes: 0

# discards: 0


# inserts: 14

# updates: 0

# deletes: 0

# discards: 0


# inserts: 5

# updates: 0

# deletes: 0

# discards: 0

In all three cases the initial data load was successful.

7.3 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



Password:



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

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

Successfully created checkpoint table ddl_ogg.oggchkpt.


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



handlecollisions

sourcedefs ./dirdef/source.def

discardfile ./dirrpt/repcdc1.dsc, purge






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

REPLICAT added.


Start the replicat repcdc1 and check status and stats.

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


REPLICAT REPCDC1 starting


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

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


Log Read Checkpoint File ./dirdat/aa000003

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


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

./dirdat/aa000000 * Initialized * First Record


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





Log Read Checkpoint File ./dirdat/aa000003

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


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

./dirdat/aa000000 * Initialized * First Record










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.



Create a parameter file for replicat repcdc2. Create the

replicat. Start the replicat and check status and stats.


replicat repcdc2



handlecollisions








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

REPLICAT added.








Log Read Checkpoint File ./dirdat/bb000003

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


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

./dirdat/bb000000 * Initialized * First Record

















Total discards 0.00




End of Statistics.



Create a parameter file for replicat repcdc3. Create the



replicat repcdc3



handlecollisions








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

REPLICAT added.








Log Read Checkpoint File ./dirdat/cc000003

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


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

./dirdat/cc000000 * Initialized * First Record


















Total discards 0.00




End of Statistics.


Repeat the same procedure for repcdc3. Create and start replicat

repcdc3. Create a parameter file for replicat repcdc3. Create the



replicat repcdc3



handlecollisions








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

REPLICAT added.








Log Read Checkpoint File ./dirdat/cc000003

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


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

./dirdat/cc000000 * Initialized * First Record


















Total discards 0.00




End of Statistics.


8. 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;


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;


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 REPCUST 00:00:00 00:00:10

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


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) 7> stats replicat repcdc1


No active replication maps.








After the script execution on the source I have




Output to ./dirdat/aa:

Extracting from SCOTT.DEPT to SCOTT.DEPT:


Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.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


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


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


Output to ./dirdat/bb:



Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00



Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00



Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00



Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00


Output to ./dirdat/cc:



Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00



Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00



Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00



Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00


End of Statistics.







Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00



Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00



Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00



Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00


End of Statistics.







Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00



Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00



Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00



Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00


End of Statistics.







Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00



Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00



Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00



Total inserts 50.00

Total updates 50.00

Total deletes 50.00

Total discards 0.00


End of Statistics.


On the source RACD issue the following delete.

SQL> delete from dept where deptno>40;

commit;

50 rows deleted.

SQL> commit;

Commit complete.

SQL>




Output to ./dirdat/aa:



Total inserts 50.00

Total updates 50.00


Total discards 0.00



Total inserts 50.00

Total updates 50.00


Total discards 0.00



Total inserts 50.00

Total updates 50.00


Total discards 0.00



Total inserts 50.00

Total updates 50.00


Total discards 0.00


Output to ./dirdat/bb:



Total inserts 50.00

Total updates 50.00


Total discards 0.00



Total inserts 50.00

Total updates 50.00


Total discards 0.00



Total inserts 50.00

Total updates 50.00


Total discards 0.00



Total inserts 50.00

Total updates 50.00


Total discards 0.00


Output to ./dirdat/cc:



Total inserts 50.00

Total updates 50.00


Total discards 0.00



Total inserts 50.00

Total updates 50.00


Total discards 0.00



Total inserts 50.00

Total updates 50.00


Total discards 0.00



Total inserts 50.00

Total updates 50.00


Total discards 0.00


End of Statistics.







Total inserts 50.00

Total updates 50.00


Total discards 0.00



Total inserts 50.00

Total updates 50.00


Total discards 0.00



Total inserts 50.00

Total updates 50.00


Total discards 0.00



Total inserts 50.00

Total updates 50.00


Total discards 0.00


End of Statistics.







Total inserts 50.00

Total updates 50.00


Total discards 0.00



Total inserts 50.00

Total updates 50.00


Total discards 0.00



Total inserts 50.00

Total updates 50.00


Total discards 0.00



Total inserts 50.00

Total updates 50.00


Total discards 0.00


End of Statistics.







Total inserts 50.00

Total updates 50.00


Total discards 0.00



Total inserts 50.00

Total updates 50.00


Total discards 0.00



Total inserts 50.00

Total updates 50.00


Total discards 0.00



Total inserts 50.00

Total updates 50.00


Total discards 0.00


End of Statistics.


9. Modify the replicats to operate in NOHANDLECOLLISION

Issue the following commands

send replicat repcdc1, nohandlecollisions



mask off the HANDLECOLLISIONS in the parameter file of the

replicats repcdc1, repcdc2, repcdc3


replicat repcdc3



--handlecollisions









replicat repcdc1



--handlecollisions









replicat repcdc2



--handlecollisions








10. 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


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.




Marker setup table script complete, running verification script...



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.





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.


DDL replication setup script complete, running verification script...



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.



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




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.


rmtfile /u02/stage_ogg112_ora11/scott.dat, purge


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


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

SAL, COMM, DEPTNO.


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


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:


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

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


Output to /u02/stage_ogg112_ora11/scott.dat:


# inserts: 4

# updates: 0

# deletes: 0

# discards: 0


# inserts: 14

# updates: 0

# deletes: 0

# discards: 0


# 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




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





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:


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


to define the key.


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


to define the key.


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



# inserts: 4

# updates: 0

# deletes: 0

# discards: 0


# inserts: 14

# updates: 0

# deletes: 0

# discards: 0


# 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


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


< 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




flag = 0x00000030



< 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




flag = 0x00000030



< 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




flag = 0x00000030



< 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]$

installing oracle goldengate (ogg) 11.2.1.0.1 in an … · installing oracle goldengate (ogg) ......

Documents