Aside
0

I got some pretty useful data from the net in pdf format. It was 101 pages long. The content has a few columns – some categories and some numbers. I wanted to do a bit of analysis of the data, using Jasper or a similar tool. So the first question was – how can I convert this into some format that is more ‘tabular’. There are quite a few pdf to excel conversion tools and web-sites. But everything I tried failed – the number of pages or format was too much. In the end. it was http://www.zamzar.com which did a perfect job. Now I have an excel with 101 worksheets. I wanted to load it into a PostgreSQL table and analyze. Pentaho, my favourite (because it is free) ETL tool had no way of walking through many sheets in the same excel. I would have had to create 101 components to load this (may be I don’t know the tool well enough). Enter the saviour, as always – Python. The following script (taken from stackoverflow and modified) did the trick.
Each sheet had a header row. I had to get rid of that in all sheets except the first one. Also, the sheets ended with “Page 1 of 101″, “Page 2 of…”. That had to be removed for all sheets. So you find a few if conditions. Also, conversion for unicode had to be done.
You will have to import the necessary python modules (xlrd, csv).

#!/usr/bin/env python
import xlrd
import csv

workbook = xlrd.open_workbook('my_big_excel.xls')
your_csv_file = open('your_csv_file.csv', 'wb')
wr = csv.writer(your_csv_file, quoting=csv.QUOTE_ALL)
all_worksheets = workbook.sheet_names()
iter = 1
for worksheet_name in all_worksheets:
  print ("Worsksheet is %s ",worksheet_name)
  worksheet = workbook.sheet_by_name(worksheet_name)
  for rownum in xrange(worksheet.nrows):
    firstc = str(worksheet.cell(rownum, 0).value)
    print ("value is %s", firstc)
    print ("type is %s", type(firstc))
    sfirtsc = firstc[0:1]
    print ("first char is  is %s", sfirtsc)
    if iter == 1:
      wr.writerow(worksheet.row_values(rownum))
      print ("Let me write")
      iter=2
    elif iter == 2 and sfirtsc !='S' and sfirtsc !='P':
      print ("Let me write")
      wr.writerow(worksheet.row_values(rownum))
your_csv_file.close()

Later my friend sent me this VB script to do the same – though it does not get rid of the Page No, Header etc. It merges all the sheets into one.

Sub mergedata()
Sheets(1).Activate
lastrow = ActiveSheet.UsedRange.Rows.Count
For Each Sheet In Sheets
If Sheet.Index <> 1 Then
RowCount = Sheet.UsedRange.Rows.Count
Sheet.UsedRange.Copy Destination:=Sheets(1).Cells(lastrow + 1, 1)
lastrow = lastrow + RowCount
Sheet.UsedRange.Clear
End If
Next Sheet
End Sub
0

iptables on centos – issue

iptables  provide one layer/type of security for linux systems. For our CentOS 6.4 system, I made changes to the script  (“add salt to taste”) available at https://www.linode.com/wiki/index.php/CentOS_IPTables_sh
and restarted the service. But it did not really work.
/etc/init.d/iptables restart
iptables: Flushing firewall rules: [ OK ]
iptables: Setting chains to policy ACCEPT: security raw nat[FAILED]filter
iptables: Unloading modules: [ OK ]
iptables: Applying firewall rules: [ OK ]

A bit of googling gave a couple of patches. But for some reason, the patch command kept erroring, probably because the line numbers were not matching. So I ended up changing the iptables script rather than applying the patch.Here is the process
1) Go to the iptables directory
cd /etc/init.d
2) Take a backup of the existing script
cp iptables ~/iptables.backup
3) Open the script (not backup), and add the following lines

security)
$IPTABLES -t filter -P INPUT $policy \
&& $IPTABLES -t filter -P OUTPUT $policy \
&& $IPTABLES -t filter -P FORWARD $policy \
|| let ret+=1
;;

These need to be added below the lines which say

set_policy() {
# Set policy for configured tables.
policy=$1

# Check if iptable module is loaded
[ ! -e "$PROC_IPTABLES_NAMES" ] && return 0

# Check if firewall is configured (has tables)
tables=$(cat "$PROC_IPTABLES_NAMES" 2>/dev/null)
[ -z "$tables" ] && return 1

echo -n $"${IPTABLES}: Setting chains to policy $policy: "
ret=0
for i in $tables; do
echo -n "$i "
case "$i" in

and above

raw)
$IPTABLES -t raw -P PREROUTING $policy \
&& $IPTABLES -t raw -P OUTPUT $policy \
|| let ret+=1
;;

Then restart the service
/etc/init.d/iptables restart
iptables: Flushing firewall rules: [ OK ]
iptables: Setting chains to policy ACCEPT: security raw nat[ OK ]filter
iptables: Unloading modules: [ OK ]
iptables: Applying firewall rules: [ OK ]

1

MySQL – load balancing and failover using HAProxy

We have a requirement to set up MySQL for significant read TPs and limited write TPs. So we set up a MySQL master for writes, and two slaves fed by the master. We wanted to load balance the reads in a round-robin fashion between the slaves. In case one slave failed, we wanted to redirects the requests to the master rathern than the other slave. The reasoning was that in case one slave goes down, it may be because of some replication issue and the other slave may also go down soon. Also, the frequency of writes was insignificant and the master would be able to handle the reads also til the slave was recovered whereas the slaves are usually under significant load.

With HAProxy if we set up 2 nodes in load balance mode, the requests will go to the backup only if both of them go down. It is not possible to say that if one slave goes down, erquests meant for that node should go to the backup server. So we tried the following configuration and it worked. Here the requests coming to HAProxy from app server are sent two 2 HA Proxy ports on the same instance in a roundrobin fashion. In each one, we configured one MySQL slave and set the master as the backup server.

Master MySQL runs on port 3308. The slaves run on 3306 and 3307. App servers will connect to the port 8090 of HAProxy. Those get redirected to the ports 3340 and 3341.

defaults

log global

mode tcp

retries 3

option redispatch

maxconn 4096

contimeout 60000

clitimeout 60000

srvtimeout 60000



listen  stats

mode http

bind :8091

stats enable

stats uri     /admin?stats



listen mysql_proxy 192.168.x.x:8090

mode tcp

balance roundrobin

server  ha01 localhost:3340 weight 1

server  ha02 localhost:3341 weight 1





frontend db_read1

bind localhost:3340

default_backend myisam_1



frontend db_read2

bind localhost:3341

default_backend myisam_2







backend myisam_1

mode    tcp

option httpchk

server  db01 192.168.x.x:3306 weight 1 check port 48620 inter 12000 rise 1 fall 3

server  db03 192.168.x.x:3308 weight 1 backup



backend myisam_2

mode    tcp

option httpchk

balance roundrobin

server  db02 192.168.x.x:3307 weight 1 check port 48621 inter 12000 rise 1 fall 3

server  db03 192.168.x.x:3308 weight 1 backup
0

MySQL – Optimizer and index issues

Trying to optimize a query in MySQL led to discovery of a few issues with MySQL.

1)Dropping an index (yes – dropping, not creating) can take a long, long time on MyISAM as well as InnoDB tables. The reason is that storage structures in MySQL are not optimized for table alterations. Here are a few links which explain why it takes such a long time and the activities happening behind the scenes when we drop an index. If we have a table with 6 indexes and try to drop one of them, MySQL internally creates copies of the table as well as existing indexes to drop that index.

http://lists.mysql.com/mysql/202489
http://bugs.mysql.com/bug.php?id=2364
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-create-index-overview.html
The issue was logged way back in 2006 and still exists.

2) MySQL optimizer is not as optimized (read – intelligent) as Oracle’s or PostgreSQL’s is. Minor changes to the way the query is written results in huge differences to the time taken to execute.

Here is a comparison for such operations on PostgrSQL and MySQL. Both are running on the same machine and comparable parameters – such as shared buffers, sort size etc have been kept the same.

The table has 15664343 records.
The table size is about 3 GB on both databases.

test=# SELECT pg_size_pretty(pg_total_relation_size(‘my_big_table’));

pg_size_pretty
—————-
3238 MB
(1 row)

MySQL -

my_big_table InnoDB 15601781 3,391.00 Mb

Let us look at how long these dataabse take to create/drop an index and execute the original as well as modified queries

PostgreSQL

create unique index nfe_acs_seg_uq on my_big_table(SLICE_ID,DEPARTURE_DATE,MARKETING_AIRLINE,FLIGHT_NO,ORIGIN,DESTINATION,BOOKING_CLASS);

Time: 176961.535 ms

That is about 3 minutes

Now let us see how much time mySQL takes to create that index
mysql>

create unique index nfe_acs_seg_uq on my_big_table(SLICE_ID,DEPARTURE_DATE,MARKETING_AIRLINE,FLIGHT_NO,ORIGIN,DESTINATION,BOOKING_CLASS);

Query OK, 15664343 rows affected (13 min 0.29 sec)
Records: 15664343 Duplicates: 0 Warnings: 0

PostgreSQL

drop index nfe_acs_seg_uq;

DROP INDEX
Time: 730.736 ms

That is less than one second

MySQL
mysql>

alter table my_big_table drop index nfe_acs_seg_uq ;

Query OK, 15664343 rows affected (32 min 5.73 sec)
Records: 15664343 Duplicates: 0 Warnings: 0

Dropping an index takes 32 minutes (ouch)

Next Index on PostgreSQL -

create index tst_idx on my_big_table(DEPARTURE_DATE,MARKETING_AIRLINE,ORIGIN,DESTINATION,SLICE_ID);

CREATE INDEX
Time: 214488.928 ms

About 3-4 minutes

mysql>

create index tst_idx on my_big_table(DEPARTURE_DATE,MARKETING_AIRLINE,ORIGIN,DESTINATION,SLICE_ID);

Query OK, 15664343 rows affected (39 min 11.50 sec)
Records: 15664343 Duplicates: 0 Warnings: 0

Dropping the index in PostgreSQL
test=#drop index tst_idx;

DROP INDEX
Time: 255.092 ms
Less than a second

In MySQL (This took less time because now the table does not have any other index)

mysql> alter table my_big_table drop index tst_idx;

Query OK, 15664343 rows affected (7 min 38.15 sec)
Records: 15664343 Duplicates: 0 Warnings: 0

And what happens in MySQL when we drop an index?

mysql> show processlist;

+—–+——-+————————+——-+———+——+——————-+—————————————————-+
| Id | User | Host | db | Command | Time | State | Info |
+—–+——-+————————+——-+———+——+——————-+—————————————————-+
| 414 | root | localhost | his | Query | 139 | copy to tmp table | alter table my_big_table drop index tst_idx |
| 491 | sonar | n3105.mydomain.com:64673 | sonar | Sleep | 528 | | NULL |
| 492 | sonar | n3105.mydomain.com:64678 | sonar | Sleep | 47 | | NULL |
| 493 | root | localhost | his | Query | 0 | NULL | show processlist |
+—–+——-+————————+——-+———+——+——————-+—————————————————-+
4 rows in set (0.00 sec)

MySQL is creating temp tables for storing the table/index data

Now for query execution -
In PostgrSQL
Original Query

select count(*) from(
SELECT
ACS.ORIGIN,ACS.DESTINATION,
ACS.MARKETING_AIRLINE,ACS.FLIGHT_NO
FROM my_big_table ACS WHERE ACS.SLICE_ID IN ( SELECT ACS2.SLICE_ID FROM my_big_table ACS2 WHERE (ACS2.ORIGIN IN (‘SEA’)
OR ACS2.DESTINATION IN (‘NRT’) )
AND ACS2.MARKETING_AIRLINE = ‘DL’ AND ACS2.DEPARTURE_DATE = ‘2012-07-10′ ) ORDER BY ACS.SLICE_ID,ACS.LAST_UPDATED
) x;
count
——-
7873
(1 row)

Time: 90.038 ms

After modification -

test=# select count(*) from
test-# (
test(# select * from
test(# (
test(# SELECT
test(# ACS.ORIGIN,ACS.DESTINATION,
test(# ACS.MARKETING_AIRLINE,ACS.FLIGHT_NO, acs.slice_id,acs.last_updated
test(# FROM my_big_table ACS , my_big_table acs2 WHERE ACS.SLICE_ID = acs2.slice_id and ACS2.ORIGIN IN (‘SEA’)
test(# AND ACS2.MARKETING_AIRLINE = ‘DL’ AND ACS2.DEPARTURE_DATE = ‘2012-07-10′
test(# union
test(# SELECT
test(# ACS.ORIGIN,ACS.DESTINATION,
test(# ACS.MARKETING_AIRLINE,ACS.FLIGHT_NO, acs.slice_id,acs.last_updated
test(# FROM my_big_table ACS , my_big_table acs2 WHERE ACS.SLICE_ID = acs2.slice_id and ACS2.DESTINATION IN (‘NRT’)
test(# AND ACS2.MARKETING_AIRLINE = ‘DL’ AND ACS2.DEPARTURE_DATE = ‘2012-07-10′
test(# ) x order by slice_id,last_updated) y
test-# ;
count
——-
989
(1 row)

Time: 2943.702 ms

The changed query gets rid of duplicatse (the UNION does that). Both data sets are acceptable in this specific case. The UNION adds an extra sort operation, increasing the time for execution.

And MySQL?
mysql> select count(*) from(
SELECT
ACS.ORIGIN,ACS.DESTINATION,
ACS.MARKETING_AIRLINE,ACS.FLIGHT_NO
FROM my_big_table ACS WHERE ACS.SLICE_ID IN ( SELECT ACS2.SLICE_ID FROM my_big_table ACS2 WHERE (ACS2.ORIGIN IN (‘SEA’)
OR ACS2.DESTINATION IN (‘NRT’) )
AND ACS2.MARKETING_AIRLINE = ‘DL’ AND ACS2.DEPARTURE_DATE = ‘2012-07-10′ ) ORDER BY ACS.SLICE_ID,ACS.LAST_UPDATED
) x;
-> SELECT
-> ACS.ORIGIN,ACS.DESTINATION,
-> ACS.MARKETING_AIRLINE,ACS.FLIGHT_NO
-> FROM my_big_table ACS WHERE ACS.SLICE_ID IN ( SELECT ACS2.SLICE_ID FROM my_big_table ACS2 WHERE (ACS2.ORIGIN IN (‘SEA’)
-> OR ACS2.DESTINATION IN (‘NRT’) )
-> AND ACS2.MARKETING_AIRLINE = ‘DL’ AND ACS2.DEPARTURE_DATE = ‘2012-07-10′ ) ORDER BY ACS.SLICE_ID,ACS.LAST_UPDATED
-> ) x;
+———-+
| count(*) |
+———-+
| 7873 |
+———-+
1 row in set (5 min 31.20 sec)

Changed query -
mysql> select count(*) from
-> (
-> select * from
-> (
-> SELECT
-> ACS.ORIGIN,ACS.DESTINATION,
-> ACS.MARKETING_AIRLINE,ACS.FLIGHT_NO, acs.slice_id,acs.last_updated
-> FROM my_big_table ACS , my_big_table acs2 WHERE ACS.SLICE_ID = acs2.slice_id and ACS2.ORIGIN IN (‘SEA’)
-> AND ACS2.MARKETING_AIRLINE = ‘DL’ AND ACS2.DEPARTURE_DATE = ‘2012-07-10′
-> union
-> SELECT
-> ACS.ORIGIN,ACS.DESTINATION,
-> ACS.MARKETING_AIRLINE,ACS.FLIGHT_NO, acs.slice_id,acs.last_updated
-> FROM my_big_table ACS , my_big_table acs2 WHERE ACS.SLICE_ID = acs2.slice_id and ACS2.DESTINATION IN (‘NRT’)
-> AND ACS2.MARKETING_AIRLINE = ‘DL’ AND ACS2.DEPARTURE_DATE = ‘2012-07-10′
-> ) x order by slice_id,last_updated) y
-> ;
+———-+
| count(*) |
+———-+
| 989 |
+———-+
1 row in set (0.34 sec)

The intersting thing is that rewriting the query reduces time from more than 5 minutes to less than one second for MySQL. In PostgreSQL, the execution time is comparable.

7

GoldenGate replication from Oracle to PostgreSQL

Oracle added support for PostgreSQL as a target database recently for GoldenGate. While Oracle does have quick how-tos for various replication setups (Oracle to MySQL, Oracle to Teradata etc), I couldn’t find anything for setting up replication from Oracle to PostgreSQL. So here is how to.
Versions used -
GoldenGate for PostgreSQL –
Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205Linux, x64, 64bit

GoldenGate for Oracle –
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBOLinux, x64, 64bit

I tried this on a 64 bit linux box (CentOS).

Downloading the software -
1. Navigate to http://edelivery.oracle.com.
2. On the Welcome page:
Select your language.
Click Continue.
3. On the Export Validation page:
Enter your identification information.
Accept the Trial License Agreement (even if you have a permanent license).
Accept the Export Restrictions.
Click Continue.
4. On the Media Pack Search page:
Select the Oracle Fusion Middleware Product Pack.
Select the platform on which you will be installing the software.
Click Go.
5. In the Results List:
Select the Media Pack that you want to download.
Click Continue.
6. On the Download page:
Click Download for each component that you want.

Add a user under which the software is to be installed

useradd -g dba gguser-d /home/gguser

Later, it turned out that this user is unable to read the redo log files. So the user had to be modified with

useradd -G oinstall gguser

Login using the user and create two directories – one for GoldenGate for Oracle and one for GoldenGate for PostgreSQL.

cd /opt/
mkdir postgresgg
mkdir oraclegg
[gguser@myhost oraclegg]$ pwd
/opt/oraclegg
unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
rm ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
rm fbo_ggs_Linux_x64_ora11g_64bit.tar

For GG for PostgreSQL

cd ../postgresgg/
pwd
/opt/postgresgg
unzip V34006-01.zip
tar -xvf ggs_Linux_x64_PostgreSQL_64bit.tar

Edit the profile for gguser

Edit the .bash_profile to set environment variables

After editing, bash profile looks like this

[gguser@~]$ more .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=.:$PATH:$HOME/bin

export PATH
export ODBCINI=/etc/odbc.ini
alias cdogg='cd /opt/oraclegg'
alias cdpgg='cd /opt/postgresgg'
export PS1="[\u@\w]\\$ "
export LD_LIBRARY_PATH=/opt/oracle/product/11.2.0.3/dbhome_1/lib:/usr/local/pgsql92/lib:/opt/postgresgg/lib
export ORACLE_SID=orcl11g
export ORACLE_HOME=/opt/oracle/product/11.2.0.3/dbhome_1

The environment variables are necessary for GoldenGate to function while the aliases were added to move easily between the installation directories for Oracle and PostgreSQL PATH was prefixed with “.” So that there is no need to type “./” when invoking goldengate executable.

Now complete installation -

cdpgg
ggsci

Oracle GoldenGate Command Interpreter
Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205
Linux, x64, 64bit (optimized), PostgreSQL on Jul 25 2012 00:32:24
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights re-served.
GGSCI (myhost.mydom.com) 1>;

GGSCI (myhost.mydom.com) 1>; CREATE SUBDIRS

Creating subdirectories under current directory /opt/postgresgg

Parameter files /opt/postgresgg/dirprm: created
Report files /opt/postgresgg/dirrpt: created
Checkpoint files /opt/postgresgg/dirchk: created
Process status files /opt/postgresgg/dirpcs: created
SQL script files /opt/postgresgg/dirsql: created
Database definitions files /opt/postgresgg/dirdef: created
Extract data files /opt/postgresgg/dirdat: created
Temporary files /opt/postgresgg/dirtmp: created
Stdout files /opt/postgresgg/dirout: created

GGSCI (myhost.mydom.com) 2>;

Now Installation of GoldenGate for PostgrSQL is complete

Let us repeat for Oracle

[gguser@/opt/postgresgg]$ cdogg
[gguser@/opt/oraclegg]$
[gguser@/opt/oraclegg]$ 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 re-served.

GGSCI (myhost.mydom.com) 1&gt; CREATE SUBDIRS

Creating subdirectories under current directory /opt/oraclegg

Parameter files /opt/oraclegg/dirprm: already exists
Report files /opt/oraclegg/dirrpt: created
Checkpoint files /opt/oraclegg/dirchk: created
Process status files /opt/oraclegg/dirpcs: created
SQL script files /opt/oraclegg/dirsql: created
Database definitions files /opt/oraclegg/dirdef: created
Extract data files /opt/oraclegg/dirdat: created
Temporary files /opt/oraclegg/dirtmp: created
Stdout files /opt/oraclegg/dirout: created

Now create subdirs has been done for both Oracle and PostgreSQL. Please note that it was necessary to add lib under postgresql installation alone to LD_LIBRARY_PATH. This was necessary to pick up the PostgreSQL driver from Data Direct shipped with Goldengate.

Next step is the initial load.
The initial load involves creating the database and users, creating an extract job with details of tables, creating a replication job, configuring the odbc connection for PostgreSQL, creating a source definition file to be used by the replicat job and then starting the extract job which will in turn execute the replicat job to complete the one time data load.

Let us set up users and tables for trying this out -
On Oracle,

SQL> create user GG IDENTIFIED BY GG;

User created.

SQL> GRANT RESOURCE, CONNECT,DBA TO GG;

Grant succeeded.

SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE (grantee =>>'GG',privilege_type>>'capture',grant_select_privileges>>true, do_grants=>>TRUE);

PL/SQL procedure successfully completed.

[oracle@myhost ~]$

sqlplus GG/GG

SQL> CREATE TABLE MYTABLE(ID INTEGER, NAME VARCHAR2(10) ,PRIMARY KEY(ID));

Table created.

Insert a few records by

insert into mytable select rownum,substr(object_name,1,10) from all_objects where rownum < 50;
commit;

On PostgreSQL

create user gg PASSWORD 'gg';
grantpostgres to gg;

ALTER USER ggcreatedb;

Login using gg

create database targetpostgres;
CREATE TABLE mytable
(
ID integer,
NAME VARCHAR(10 ),
PRIMARY KEY (ID)
);

Configure and start the manager on both nodes. This can be done later too, before starting any of the other processes.

Oracle – go to gg installation directory by typing

cdogg
ggsci
GGSCI (myhost.mydom.com) 1> EDIT PARAMS MGR 

Type in values so that the file looks like below

GGSCI (myhost.mydom.com) 2>; VIEW PARAMS MGR

PORT 1522
DYNAMICPORTLIST 7810-7830, 7840

Ensure that the dynamicportlist option is used. Without that, TCP error “Connection reset by peer” kept appearing in the log file and the processes kept abending and restating.

GGSCI (myhost.mydom.com) 1>;

START MGR

Manager started.

GGSCI (myhost.mydom.com) 3>; VIEW REPORT MGR
***********************************************************************
Oracle GoldenGate Manager for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 04:27:29

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

Starting at 2012-10-23 14:59:47
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Sat Nov 12 15:11:58 CST 2011, Release 2.6.32-131.0.15.el6.x86_64
Node: myhost.mydom.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: 5040

Parameters…

PORT 1522
DYNAMICPORTLIST 7810-7830, 7840

***********************************************************************
** Run Time Messages **
***********************************************************************

2012-10-23 14:59:47 INFO OGG-00983 Manager started (port 1522).

For PostgreSQL, edit the file in a similar fashion. For editing the manager for PostgreSQL, it is necessary to go to the PostgreSQL GG installation directory and execute ggsci there like this –

[gguser@~]$ cdpgg
[gguser@/opt/postgresgg]$ pwd
/opt/postgresgg
[gguser@/opt/postgresgg]$ ggsci

Oracle GoldenGate Command Interpreter
Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205
Linux, x64, 64bit (optimized), PostgreSQL on Jul 25 2012 00:32:24

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

GGSCI (myhost.mydom.com) 7>; VIEW PARAMS MGR

PORT 5433
DYNAMICPORTLIST 8810-8830, 8840

GGSCI (myhost.mydom.com) 2>; START MGR

Manager started.

GGSCI (myhost.mydom.com) 3>; STATUS MGR

Manager is running (IP port myhost.mydom.com.5433).

GGSCI (myhost.mydom.com) 4>; VIEW REPORT MGR

***********************************************************************
Oracle GoldenGate Manager
Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205
Linux, x64, 64bit (optimized), PostgreSQL on Jul 25 2012 00:26:53

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

Starting at 2012-10-23 14:58:35
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Sat Nov 12 15:11:58 CST 2011, Release 2.6.32-131.0.15.el6.x86_64
Node: myhost.mydom.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: 5018

Parameters…

PORT 5433
DYNAMICPORTLIST 8810-8830, 8840

***********************************************************************
** Run Time Messages **
***********************************************************************

2012-10-23 14:58:35 INFO OGG-00983 Manager started (port 5433).

[root@myhost dirprm]#

ss -anp | grep -P "5433|1522"

LISTEN 0 50 :::5433 :::* users:((“mgr”,31581,3))
LISTEN 0 50 :::1522 :::* users:((“mgr”,31768,3))

Configure ODBC

[gguser@/opt/postgresgg/lib]$ more /etc/odbc.ini
[ODBC Data Sources]
gg=DataDirect 6.1 PostgreSQL Wire Protocol
[ODBC]
IANAAppCodePage=4
InstallDir=/opt/postgresgg
[ggtarget]
Driver=/opt/postgresgg/lib/GGpsql25.so
Description=DataDirect 6.1 PostgreSQL Wire Protocol
Database=targetpostgres
HostName=192.168.2.72
PortNumber=5432

We can test connectivity from ggsci prompt –

GGSCI (myhost.mydom.com) 2>; DBLOGIN SOURCEDB ggtarget,USERIDgg, PASSWORD gg

2012-10-23 10:06:08 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.

2012-10-23 10:06:08 INFO OGG-03037 Session character set identified as UTF-8.
Successfully logged into database.

Configure logging on source

SQL> SELECT log_mode FROM v$database;

LOG_MODE
————
ARCHIVELOG

SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;

FOR SUPPLEME
— ——–
NO NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL>; SELECT force_logging, supplemental_log_data_min FROM v$database;

FOR SUPPLEME
— ——–
YES YES

Supplemental logging for the table
On oracle gg

GGSCI (myhost.mydom.com) 1&gt; DBLOGIN USERID GG, PASSWORD GG
Successfully logged into database.

GGSCI (myhost.mydom.com) 2>

GGSCI (myhost.mydom.com) 2> list tables *
GG.MYTABLE

Found 1 tables matching list criteria.

Then add logging for the schema from ggsci

GGSCI (myhost.mydom.com) 2&gt; ADD SCHEMATRANDATA GG

2012-10-23 13:37:15 INFO OGG-01788 SCHEMATRANDATA has been added on schema GG.

GGSCI (myhost.mydom.com) 4> ADD TRANDATA MYTABLE

Logging of supplemental redo log data is already enabled for table GG.MYTABLE.

GGSCI (myhost.mydom.com) 3> INFO TRANDATA GG.MYTABLE

Logging of supplemental redo log data is enabled for table GG.MYTABLE.

Columns supplementally logged for table GG.MYTABLE: ID.

Create a source definition file -
Oracle gg -

GGSCI (myhost.mydom.com) 1&gt; EDIT PARAM DEFGEN
GGSCI (myhost.mydom.com) 2&gt; VIEW PARAM DEFGEN

DEFSFILE ./dirdef/source.def, PURGE
USERID GG, PASSWORD GG
TABLE GG.MYTABLE;

Go back to shell prompt and execute

[gguser@/opt/oraclegg]$ defgenparamfile ./dirprm/defgen.prm

***********************************************************************
Oracle GoldenGate Table Definition Generator for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 05:08:19[/sourcecode]

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

Starting at 2012-10-23 16:23:22
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Sat Nov 12 15:11:58 CST 2011, Release 2.6.32-131.0.15.el6.x86_64
Node: myhost.mydom.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: 6035

***********************************************************************
** Running with the following parameters **
***********************************************************************
DEFSFILE ./dirdef/source.def, PURGE
USERID GG, PASSWORD **
TABLE GG.MYTABLE;
Retrieving definition for GG.MYTABLE
Definitions generated for 1 table in ./dirdef/source.def

Copy the file over to Postgres def directory

cpdirdef/source.def /opt/postgresgg/dirdef/.
[gguser@/opt/oraclegg]$ more /opt/postgresgg/dirdef/source.def

*+- Defgen version 2.0, Encoding UTF-8
*
* Definitions created/modified 2012-10-23 16:23
*
* Field descriptions for each column entry:
*
* 1 Name
* 2 Data Type
* 3 External Length
* 4 Fetch Offset
* 5 Scale
* 6 Level
* 7 Null
* 8 Bump if Odd
* 9 Internal Length
* 10 Binary Length
* 11 Table Length
* 12 Most Significant DT
* 13 Least Significant DT
* 14 High Precision
* 15 Low Precision
* 16 Elementary Item
* 17 Occurs
* 18 Key Column
* 19 Sub Data Type
*
Database type: ORACLE
Character set ID: windows-1252
National character set ID: UTF-16
Locale: neutral
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
*
Definition for table GG.MYTABLE
Record length: 72
Syskey: 0
Columns: 2
ID 64 50 0 0 0 1 0 50 50 50 0 0 0 0 1 0 1 2
NAME 64 10 56 0 0 1 0 10 10 0 0 0 0 0 1 0 0 0
End of definition

On PostgreSQLgg

GGSCI (myhost.mydom.com) 1&gt; ADD REPLICAT RINI1, SPECIALRUN
REPLICAT added.

GGSCI (myhost.mydom.com) 2> INFO REPLICAT *, TASKS

REPLICAT RINI1 Initialized 2012-10-23 15:57 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint Not Available
Task SPECIALRUN

GGSCI (myhost.mydom.com) 4> EDIT PARAMS RINI1

GGSCI (myhost.mydom.com) 4>

VIEW PARAMS RINI1

REPLICAT RINI1
SETENV (ODBCINI="/etc/odbc.ini")
TARGETDB ggtarget, USERID gg, PASSWORD gg
SOURCEDEFS ./dirdef/source.def
DISCARDFILE ./dirrpt/RINI1.dsc, PURGE
MAP GG.MYTABLE, TARGET public.mytable;

On Oracle ggsci, add extract

GGSCI (myhost.mydom.com) 4&gt; ADD EXTRACT EINI1, SOURCEISTABLE
EXTRACT added.
GGSCI (myhost.mydom.com) 5&gt; INFO EXTRACT *, TASKS

EXTRACT EINI1 Initialized 2012-10-23 15:47 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Not Available
First Record Record 0
Task SOURCEISTABLE

A source-is-table Extract process extracts a current set of static data directly from the source objects in preparation for an initial load to another database. This process type does not use checkpoints. The SOURCEISTABLE parameter applies to this process type

EDIT PARAMS EINI1
GGSCI (myhost.mydom.com) 10&gt; VIEW PARAMS EINI1
EXTRACT EINI1
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
SETENV (ORACLE_HOME="/opt/oracle/product/11.2.0.3/dbhome_1")
SETENV (ORACLE_SID="orcl11g")
USERID GG, PASSWORD GG
RMTHOST myhost.mydom.com, MGRPORT 5433
RMTTASK REPLICAT, GROUP RINI1
TABLE gg.mytable;

On Oraclgg

GGSCI (myhost.mydom.com) 2&gt; START EXTRACT EINI1

Sending START request to MANAGER …
EXTRACT EINI1 starting

GGSCI (myhost.mydom.com) 3> VIEW REPORT EINI1
2012-10-23 17:09:19 INFO OGG-01017 Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.

***********************************************************************
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 re-served.

Starting at 2012-10-23 17:09:19
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Sat Nov 12 15:11:58 CST 2011, Release 2.6.32-131.0.15.el6.x86_64
Node: myhost.mydom.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: 6734

Description:

***********************************************************************
** Running with the following parameters **
***********************************************************************

2012-10-23 17:09:19 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
EXTRACT EINI1
SETENV (NLS_LANG=”AMERICAN_AMERICA.WE8MSWIN1252″)
Set environment variable (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
SETENV (ORACLE_HOME=”/opt/oracle/product/11.2.0.3/dbhome_1″)
Set environment variable (ORACLE_HOME=/opt/oracle/product/11.2.0.3/dbhome_1)
SETENV (ORACLE_SID=”orcl11g”)
Set environment variable (ORACLE_SID=orcl11g)
USERID GG, PASSWORD **
RMTHOST myhost.mydom.com, MGRPORT 5433
RMTTASK REPLICAT, GROUP RINI1
TABLE gg.mytable;
Using the following key columns for source table GG.MYTABLE: ID.

2012-10-23 17:09:19 INFO OGG-01815 Virtual Memory Facilities for: COM
anonalloc: mmap(MAP_ANON) anon free: munmap
filealloc: mmap(MAP_SHARED) file free: munmap
target directories:
/opt/oraclegg/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 Produc-tion
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 = “AMERICAN_AMERICA.WE8MSWIN1252″
NLS_LANGUAGE = “AMERICAN”
NLS_TERRITORY = “AMERICA”
NLS_CHARACTERSET = “WE8MSWIN1252″

Processing table GG.MYTABLE

***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************

Report at 2012-10-23 17:09:24 (activity since 2012-10-23 17:09:19)

Output to RINI1:

From Table GG.MYTABLE:
# inserts: 49
# updates: 0
# deletes: 0
# discards: 0

REDO Log Statistics
Bytes parsed 0
Bytes output 4169

On PostgreSQL ggsci

VIEW REPORT RINI1

***********************************************************************
Oracle GoldenGate Delivery
Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205
Linux, x64, 64bit (optimized), PostgreSQL on Jul 25 2012 01:37:05

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

Starting at 2012-10-23 17:09:19
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Sat Nov 12 15:11:58 CST 2011, Release 2.6.32-131.0.15.el6.x86_64
Node: myhost.mydom.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: 6740

Description:

***********************************************************************
** Running with the following parameters **
***********************************************************************

2012-10-23 17:09:24 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
REPLICAT RINI1
SETENV (ODBCINI=”/etc/odbc.ini”)
Set environment variable (ODBCINI=/etc/odbc.ini)
TARGETDB ggtarget, USERID gg, PASSWORD **

2012-10-23 17:09:24 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.

2012-10-23 17:09:24 INFO OGG-03037 Session character set identified as UTF-8.
SOURCEDEFS ./dirdef/source.def
DISCARDFILE ./dirrpt/RINI1.dsc, PURGE
MAP GG.MYTABLE, TARGET public.mytable;

2012-10-23 17:09:24 INFO OGG-01815 Virtual Memory Facilities for: COM
anonalloc: mmap(MAP_ANON) anon free: munmap
filealloc: mmap(MAP_SHARED) file free: munmap
target directories:
/opt/postgresgg/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:
PostgreSQL
Version 09.02.0001
ODBC Version 03.52.0000

Driver Information:
GGpsql25.so
Version 06.10.0068 (B0059, U0030)
ODBC Version 03.52

***********************************************************************
** Run Time Messages **
***********************************************************************

MAP resolved (entry GG.MYTABLE):
MAP “GG”.”MYTABLE”, TARGET public.mytable;
Using following columns in default map by name:
id, name
Using the following key columns for target table public.mytable: id.

2012-10-23 17:09:24 INFO OGG-03010 Performing implicit conversion of column data from character set windows-1252 to UTF-8.

***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************

Report at 2012-10-23 17:09:29 (activity since 2012-10-23 17:09:24)

From Table GG.MYTABLE to public.mytable:
# inserts: 49
# updates: 0
# deletes: 0
# discards: 0

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
forcedunmaps = 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
forcedunmaps = 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
curlen = 0 max len = 0
q vm current = 0 vm max = 0
q hits = 0 q misses = 0

queue size q hits curlenmaxlen 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: rini1 id: p6740_BLOB
trans active = 0 trans concurrent (max) = 0
trans total = 0 (0 )
flag = 0x00000030
last error = (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

================================================================================
CUMULATIVE STATS FOR CACHE POOL #0
POOL INFO group: rini1 id: p6740_BLOB
trans active = 0 trans concurrent (max) = 0
trans total = 0 (0 )
flag = 0x00000030
last error = (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

QUEUE Statistics:
num queues = 15 default index = 0
curlen = 0 max len = 0
q vm current = 0 vm max = 0
q hits = 0 q misses = 0

queue size q hits curlenmaxlen 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: rini1 id: p6740_BLOB
trans active = 0 trans concurrent (max) = 0
trans total = 0 (0 )
flag = 0x00000030
last error = (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

================================================================================
CUMULATIVE STATS FOR CACHE POOL #0
POOL INFO group: rini1 id: p6740_BLOB
trans active = 0 trans concurrent (max) = 0
trans total = 0 (0 )
flag = 0x00000030
last error = (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

If we run EINI1 again, it abends. The error on extract job is

“2012-10-24 09:26:05 WARNING OGG-01194 EXTRACT task RINI1 abended : There is no trail to reposition to when doing direct load task.”

On the REPLICAT side the error says

2012-10-24 09:26:05 WARNING OGG-01004 Aborted grouped transaction on ‘public.mytable’, Database error 3505685 ([SQL error 3505685 (0x357e15)][Oracle][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: duplicate key value violates unique constraint “mytable_pkey”(Detail Key (id)=(1) already exists.;Filenbtinsert.c;Line 396;Routine _bt_check_unique;)
[Oracle][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]Failed transac-tion. The current transaction rolled back.

).

Source Context :
SourceModule : [er.errors]
SourceID : [/scratch/aime1/adestore/views/aime1_adc4150256/oggcore/OpenSys/src/app/er/errors.cpp]
SourceFunction : [take_rep_err_action]
SourceLine : [490]
ThreadBacktrace : [9] elements
: [/opt/postgresgg/libgglog.so(CMessageContext::AddThreadContext()+0x26) [0x7f373009f946]]
: [/opt/postgresgg/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, …)+0x366) [0x7f3730099636]]
:[/opt/postgresgg/libgglog.so(_MSG_ERR_IDLX_REPOSITION_UNSUPPORTED(CSourceContext*, CMessageFactory::MessageDisposition)+0x36) [0x7f3730065416]]
: [./replicat(take_rep_err_action(short, int, char const*, extr_ptr_def*, __std_rec_hdr*, char*, file_def*, bool)+0x8c6) [0x521586]]
: [./replicat(replicate_io(file_def*, __std_rec_hdr*, char*, extr_ptr_def*, int*, int)+0x344e) [0x54b4be]]
: [./replicat(process_extract_loop()+0x4b6c) [0x546f0c]]
: [./replicat(main+0xe54) [0x5614e4]]
: [/lib64/libc.so.6(__libc_start_main+0xfd) [0x32bb21ec9d]]
: [./replicat(__gxx_personality_v0+0x272) [0x48f2da]]

2012-10-24 09:26:05 ERROR OGG-01200 There is no trail to reposition to when doing direct load task.

If you enable logging on PostgreSQL, you can see the inserts that were executed –

“duration: 0.160 ms bind ST2319560/ST2319560:
INSERT INTO “public”.”mytable” (“id”,”name”) VALUES ($1,$2)”
“parameters: $1 = ‘1’, $2 = ‘ICOL$'”

The error message was

“duplicate key value violates unique constraint “mytable_pkey””
“Key (id)=(1) already exists.”

The next line says that the transaction was rolled back.

“duration: 0.037 ms statement: ROLLBACK”

Now let us implement change data capture -
On Postgresqlggsci

GGSCI (myhost.mydom.com) 1&gt; EDIT PARAMS ./GLOBALS
GGSCI (myhost.mydom.com) 8&gt; VIEW PARAMS ./GLOBALS

CHECKPOINTTABLE public.ggschkpt

Note that we have used the schema – i.e public, and not the owner (gg).
It is necessary to exit and login after this step (for globals to take effect)

GGSCI (myhost.mydom.com) 1&gt; DBLOGIN SOURCEDB ggtarget,USERIDgg, PASSWORD gg
2012-10-24 11:47:51 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.
2012-10-24 11:47:51 INFO OGG-03037 Session character set identified as UTF-8.
Successfully logged into database.

GGSCI (myhost.mydom.com) 2> ADD CHECKPOINTTABLE
No checkpoint table specified, using GLOBALS specification (pub-lic.ggschkpt)…
Successfully created checkpoint table public.ggschkpt.

In psql, if you do

targetpostgres=&gt; \d

List of relations
Schema | Name | Type | Owner
——–+———-+——-+——-
public | ggschkpt | table | gg
public | myt | table | gg
public | myt1 | table | gg
public | mytable | table | gg
(4 rows)
t

argetpostgres=&gt; \d ggschkpt

Table “public.ggschkpt”
Column | Type | Modifiers
—————-+—————————–+———–
group_name | character varying(8) | not null
group_key | bigint | not null
seqno | integer |
rba | bigint | not null
audit_ts | character varying(29) |
create_ts | timestamp without time zone | not null
last_update_ts | timestamp without time zone | not null
current_dir | character varying(255) | not null
Indexes:
“ggschkpt_pkey” PRIMARY KEY, btree (group_name, group_key)

Since we need to capture all details of changes, we will use a new table on PostgreSQL. So we will create a table with extra columns and repeat the initial load process -

In PostgreSQL, create a table to capture the changes.

postgres=# create table auditt ( TS timestamp, BEFORE_AFTER varchar(10), OP_TYPE var-char(10), id integer, name varchar(20) );
CREATE TABLE

Then create an extract and replicat programs for an initlal load and execute as we did for mytable.
On Oracle ggsci,

GGSCI (myhost.mydom.com) 3&gt; ADD EXTRACT EINIAUD1, SOURCEISTABLE
EXTRACT added.

GGSCI (myhost.mydom.com) 4> EDIT PARAMS EINIAUD1

GGSCI (myhost.mydom.com) 5> VIEW PARAMS EINIAUD1

EXTRACT EINIAUD1
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
SETENV (ORACLE_HOME="/opt/oracle/product/11.2.0.3/dbhome_1")
SETENV (ORACLE_SID="orcl11g")
USERID GG, PASSWORD GG
GETUPDATEBEFORES
RMTHOST myhost.mydom.com, MGRPORT 5433
RMTTASK REPLICAT, GROUP RINIAUD1
TABLE gg.mytable;

Note the GETUPDATEBEFORES clause
On PostgreSQL ggsci,

ADD REPLICAT RINIAUD1, SPECIALRUN
GGSCI (myhost.mydom.com) 3&gt; EDIT PARAMS RINIAUD1

GGSCI (myhost.mydom.com) 4> VIEW PARAMS RINIAUD1

REPLICAT RINIAUD1
SETENV (ODBCINI="/etc/odbc.ini")
TARGETDB ggtarget, USERID gg, PASSWORD gg
INSERTALLRECORDS
SOURCEDEFS ./dirdef/source.def
DISCARDFILE ./dirrpt/RINIAUD1.dsc, PURGE
MAP GG.MYTABLE, TARGET public.auditt,
COLMAP (
TS = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),
BEFORE_AFTER = @GETENV ("GGHEADER", "BEFOREAFTERINDICATOR"),
OP_TYPE = @GETENV ("GGHEADER", "OPTYPE"),
ID = ID,
name = name
);

Note the INSERTALLRECORDS clause
On Oracle ggsci

GGSCI (myhost.mydom.com) 1&gt; START EXTRACT EINIAUD1

Sending START request to MANAGER …
EXTRACT EINIAUD1 starting

GGSCI (myhost.mydom.com) 2> VIEW REPORT EINIAUD1

2012-10-24 17:13:05 INFO OGG-01017 Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.

***********************************************************************
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 re-served.

Starting at 2012-10-24 17:13:05
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Sat Nov 12 15:11:58 CST 2011, Release 2.6.32-131.0.15.el6.x86_64
Node: myhost.mydom.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: 26607

Description:

***********************************************************************
** Running with the following parameters **
***********************************************************************

2012-10-24 17:13:05 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
EXTRACT EINIAUD1
SETENV (NLS_LANG=”AMERICAN_AMERICA.WE8MSWIN1252″)
Set environment variable (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
SETENV (ORACLE_HOME=”/opt/oracle/product/11.2.0.3/dbhome_1″)
Set environment variable (ORACLE_HOME=/opt/oracle/product/11.2.0.3/dbhome_1)
SETENV (ORACLE_SID=”orcl11g”)
Set environment variable (ORACLE_SID=orcl11g)
USERID GG, PASSWORD **
GETUPDATEBEFORES
RMTHOST myhost.mydom.com, MGRPORT 5433
RMTTASK REPLICAT, GROUP RINIAUD1
TABLE gg.mytable;
Using the following key columns for source table GG.MYTABLE: ID.

2012-10-24 17:13:05 INFO OGG-01815 Virtual Memory Facilities for: COM
anonalloc: mmap(MAP_ANON) anon free: munmap
filealloc: mmap(MAP_SHARED) file free: munmap
target directories:
/opt/oraclegg/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 Produc-tion
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 = “AMERICAN_AMERICA.WE8MSWIN1252″
NLS_LANGUAGE = “AMERICAN”
NLS_TERRITORY = “AMERICA”
NLS_CHARACTERSET = “WE8MSWIN1252″

Processing table GG.MYTABLE

***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************

Report at 2012-10-24 17:13:11 (activity since 2012-10-24 17:13:05)

Output to RINIAUD1:

From Table GG.MYTABLE:
# inserts: 598
# updates: 0
# deletes: 0
# discards: 0

REDO Log Statistics
Bytes parsed 0
Bytes output 51983

VIEW REPORT RINIAUD1


***********************************************************************
Oracle GoldenGate Delivery
Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205
Linux, x64, 64bit (optimized), PostgreSQL on Jul 25 2012 01:37:05

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

Starting at 2012-10-24 17:13:05
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Sat Nov 12 15:11:58 CST 2011, Release 2.6.32-131.0.15.el6.x86_64
Node: myhost.mydom.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: 26613

Description:

***********************************************************************
** Running with the following parameters **
***********************************************************************

2012-10-24 17:13:10 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
REPLICAT RINIAUD1
SETENV (ODBCINI=”/etc/odbc.ini”)
Set environment variable (ODBCINI=/etc/odbc.ini)
TARGETDB ggtarget, USERID gg, PASSWORD **

2012-10-24 17:13:10 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.

2012-10-24 17:13:10 INFO OGG-03037 Session character set identified as UTF-8.
INSERTALLRECORDS
SOURCEDEFS ./dirdef/source.def
DISCARDFILE ./dirrpt/RINIAUD1.dsc, PURGE
MAP GG.MYTABLE, TARGET public.auditt,
COLMAP (
TS = @GETENV (“GGHEADER”, “COMMITTIMESTAMP”),
BEFORE_AFTER = @GETENV (“GGHEADER”, “BEFOREAFTERINDICATOR”),
OP_TYPE = @GETENV (“GGHEADER”, “OPTYPE”),
ID = ID,
name = name
);

2012-10-24 17:13:10 INFO OGG-01815 Virtual Memory Facilities for: COM
anonalloc: mmap(MAP_ANON) anon free: munmap
filealloc: mmap(MAP_SHARED) file free: munmap
target directories:

/opt/postgresgg/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:
PostgreSQL
Version 09.02.0001
ODBC Version 03.52.0000

Driver Information:
GGpsql25.so
Version 06.10.0068 (B0059, U0030)
ODBC Version 03.52

***********************************************************************
** Run Time Messages **
***********************************************************************

MAP resolved (entry GG.MYTABLE):
MAP “GG”.”MYTABLE”, TARGET public.auditt, COLMAP ( TS = @GETENV (“GGHEADER”, “COMMITTIMESTAMP”), BEFORE_AFTER = @GETENV (“GGHEADER”, “BEFOREAFTERINDICATOR”
), OP_TYPE = @GETENV (“GGHEADER”, “OPTYPE”), ID = ID, name = name );
Using the following key columns for target table public.auditt: ts, be-fore_after, op_type, id, name.

2012-10-24 17:13:11 INFO OGG-03010 Performing implicit conversion of column data from character set windows-1252 to UTF-8.

***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************

Report at 2012-10-24 17:13:16 (activity since 2012-10-24 17:13:11)

From Table GG.MYTABLE to public.auditt:
# inserts: 598
# updates: 0
# deletes: 0
# discards: 0

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
forcedunmaps = 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
forcedunmaps = 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
curlen = 0 max len = 0
q vm current = 0 vm max = 0
q hits = 0 q misses = 0

queue size q hits curlenmaxlen 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: riniaud1 id: p26613_BLOB
trans active = 0 trans concurrent (max) = 0

trans total = 0 (0 )
flag = 0x00000030
last error = (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

================================================================================
CUMULATIVE STATS FOR CACHE POOL #0
POOL INFO group: riniaud1 id: p26613_BLOB
trans active = 0 trans concurrent (max) = 0
trans total = 0 (0 )
flag = 0x00000030
last error = (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

QUEUE Statistics:
num queues = 15 default index = 0
curlen = 0 max len = 0
q vm current = 0 vm max = 0
q hits = 0 q misses = 0

queue size q hits curlenmaxlen 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: riniaud1 id: p26613_BLOB
trans active = 0 trans concurrent (max) = 0
trans total = 0 (0 )
flag = 0x00000030
last error = (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

================================================================================
CUMULATIVE STATS FOR CACHE POOL #0
POOL INFO group: riniaud1 id: p26613_BLOB
trans active = 0 trans concurrent (max) = 0
trans total = 0 (0 )
flag = 0x00000030
last error = (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

Sample data looks like this in the PostgreSQL table –

SELECT * FROM auditt limit 3 

“2012-10-24 17:13:10.98261″;”AFTER”;”INSERT”;1;”ICOL$”
“2012-10-24 17:13:10.98261″;”AFTER”;”INSERT”;2;”I_USER1″
“2012-10-24 17:13:10.98261″;”AFTER”;”INSERT”;3;”CON$”

The ‘After’ tags along with INSERT clearly say these are newly inserted records. So initial load table with track of changes is done. Now let us do on-going/live change data capture.

Add extract

GGSCI (myhost.mydom.com) 1>

ADD EXTRACT EORAAUD1, TRANLOG, BEGIN NOW, THREADS 1 

EXTRACT added.

GGSCI (myhost.mydom.com) 3> VIEW PARAMS EORAAUD1

SETENV (NLS_LANG = "AMERICAN_AMERICA.WE8MSWIN1252")
SETENV (ORACLE_HOME = "/opt/oracle/product/11.2.0.3/dbhome_1")
SETENV (ORACLE_SID = "orcl11g")
EXTRACT EORAAUD1
USERID GG, PASSWORD GG
GETUPDATEBEFORES
RMTHOST myhost.mydom.com, MGRPORT 5433
RMTTRAIL ./dirdat/au
TABLE GG.MYTABLE;

GGSCI (myhost.mydom.com) 5>

START EXTRACT EORAAUD1

Sending START request to MANAGER …
EXTRACT EORAAUD1 starting

2012-10-24 17:55:06 ERROR OGG-01044 The trail ‘./dirdat/au’ is not assigned to extract ‘EORAAUD1′. Assign the trail to the extract with the command “ADD
EXTTRAIL/RMTTRAIL ./dirdat/au, EXTRACT EORAAUD1″.

2012-10-24 17:55:06 INFO OGG-01517 Position of first record processed for Thread 1, Sequence 1391, RBA 15836176, SCN 0.23566287, Oct 24, 2012 5:37:21 PM
.

2012-10-24 17:55:06 ERROR OGG-01668 PROCESS ABENDING.

GGSCI (myhost.mydom.com) 29>

ADD EXTTRAIL  ./dirdat/au, EXTRACT EORAAUD1

EXTTRAIL added.

GGSCI (myhost.mydom.com) 32> INFO EXTRACT EORAAUD1

EXTRACT EORAAUD1 Initialized 2012-10-24 17:55 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:19:44 ago)
Log Read Checkpoint Oracle Redo Logs
2012-10-24 17:36:58 Thread 1, Seqno 1391, RBA 15836176
SCN 0.0 (0)

GGSCI (myhost.mydom.com) 33> INFO RMTTRAIL *

Extract Trail: ./dirdat/jd
Extract: EORA1
Seqno: 0
RBA: 64302
File Size: 1M

Extract Trail: ./dirdat/au
Extract: EORAAUD1
Seqno: 0
RBA: 0
File Size: 100M

GGSCI (myhost.mydom.com) 34> START EXTRACT EORAAUD1

Sending START request to MANAGER …
EXTRACT EORAAUD1 starting

GGSCI (myhost.mydom.com) 35> view report EORAAUD1

***********************************************************************
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-10-24 17:57:39
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Sat Nov 12 15:11:58 CST 2011, Release 2.6.32-131.0.15.el6.x86_64
Node: myhost.mydom.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: 27259

Description:

***********************************************************************
** Running with the following parameters **
***********************************************************************

2012-10-24 17:57:39 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
SETENV (NLS_LANG = “AMERICAN_AMERICA.WE8MSWIN1252″)
Set environment variable (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
SETENV (ORACLE_HOME = “/opt/oracle/product/11.2.0.3/dbhome_1″)
Set environment variable (ORACLE_HOME=/opt/oracle/product/11.2.0.3/dbhome_1)
SETENV (ORACLE_SID = “orcl11g”)
Set environment variable (ORACLE_SID=orcl11g)
EXTRACT EORAAUD1
USERID GG, PASSWORD **
RMTHOST myhost.mydom.com, MGRPORT 5433
RMTTRAIL ./dirdat/au
GETUPDATEBEFORES
TABLE GG.MYTABLE;

2012-10-24 17:57:39 INFO OGG-01635 BOUNDED RECOVERY: reset to initial or altered checkpoint.

2012-10-24 17:57:39 INFO OGG-01815 Virtual Memory Facilities for: BR
anonalloc: mmap(MAP_ANON) anon free: munmap
filealloc: mmap(MAP_SHARED) file free: munmap
target directories:
/opt/oraclegg/BR/EORAAUD1.

Bounded Recovery Parameter:
Options = BRRESET
BRINTERVAL = 4HOURS
BRDIR = /opt/oraclegg

2012-10-24 17:57:39 INFO OGG-01815 Virtual Memory Facilities for: COM
anonalloc: mmap(MAP_ANON) anon free: munmap
filealloc: mmap(MAP_SHARED) file free: munmap
target directories:
/opt/oraclegg/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 = “AMERICAN_AMERICA.WE8MSWIN1252″
NLS_LANGUAGE = “AMERICAN”
NLS_TERRITORY = “AMERICA”
NLS_CHARACTERSET = “WE8MSWIN1252″

2012-10-24 17:57:40 WARNING OGG-01423 No valid default archive log destination directory found for thread 1.

2012-10-24 17:57:40 INFO OGG-00546 Default thread stack size: 10485760.

2012-10-24 17:57:40 INFO OGG-01513 Positioning to (Thread 1) Sequence 1391, RBA 15836176, SCN 0.0.

2012-10-24 17:57:40 INFO OGG-01516 Positioned to (Thread 1) Sequence 1391, RBA 15836176, SCN 0.0, Oct 24, 2012 5:36:58 PM.

2012-10-24 17:57:40 INFO OGG-01517 Position of first record processed for Thread 1, Sequence 1391, RBA 15836176, SCN 0.23566287, Oct 24, 2012 5:37:21 PM

Add replicat

GGSCI (myhost.mydom.com) 5> ADD REPLICAT RCDCAUD1, EXTTRAIL./dirdat/au,CHECKPOINTTABLEpublic.ggschkpt
REPLICAT added.

GGSCI (myhost.mydom.com) 8> VIEW PARAMS RCDCAUD1

REPLICAT RCDCAUD1
SETENV (ODBCINI=”/etc/odbc.ini”)
TARGETDB ggtarget, USERID gg, PASSWORD gg
INSERTALLRECORDS
SOURCEDEFS ./dirdef/source.def
DISCARDFILE ./dirrpt/RCDCAUD1.dsc, PURGE
MAP GG.MYTABLE, TARGET public.auditt,
COLMAP (
TS = @GETENV (“GGHEADER”, “COMMITTIMESTAMP”),
BEFORE_AFTER = @GETENV (“GGHEADER”, “BEFOREAFTERINDICATOR”),
OP_TYPE = @GETENV (“GGHEADER”, “OPTYPE”),
ID = ID,
name = name
);

GGSCI (myhost.mydom.com) 9> START REPLICAT RCDCAUD1

Sending START request to MANAGER …
REPLICAT RCDCAUD1 starting

1.6.5 Test change data capture

Let us try deleting a record
On Oracle,

DELETE FROM mytable WHERE ID = 100;
COMMIT;

On PostgreSQL,

SELECT * FROM AUDITT WHERE ID = 100
“2012-10-24 17:13:10.98261″;”AFTER”;”INSERT”;100;”NEWDATA”
“2012-10-25 09:36:50.078464″;”BEFORE”;”DELETE”;100;””

Try updates
On Oracle,

updatemytable set name=’UPDDTED’ where id = 200;
commit;

On PostgreSQL,

SELECT * FROM AUDITT WHERE ID = 200

“2012-10-24 17:13:10.98261″;”AFTER”;”INSERT”;200;”NEWDATA”
“2012-10-25 09:38:29.086581″;”BEFORE”;”SQL COMPUP”;200;”NEWDATA”
“2012-10-25 09:38:29.086581″;”AFTER”;”SQL COMPUP”;200;”UPDDTED”

Try Inserts
On Oracle,

insert into mytable values (10000,’SINGLEIN’);
commit;

PostgreSQL,

SELECT * FROM AUDITT WHERE ID = 10000

“2012-10-25 09:40:24.064644″;”AFTER”;”INSERT”;10000;”SINGLEIN”