GoldenGate


Goldengate 12.2 – ERROR OGG-00041 Data source not specified. 1

While testing the new Goldengate 12.2 with a training material I’ve been doing, I got the error:

2016-03-29 15:36:00 ERROR OGG-00041 Data source not specified. 
2016-03-29 15:36:00 ERROR OGG-01668 PROCESS ABENDING.

This when configuring the most simple integrated extract.

Looking at the ggserr.log did not gave any other idea.

After opening a SR with Oracle, I decided to try the same configuration on Goldengate 12.1. And to my surprise the error was “clearer”:

2016-03-29 16:15:51 ERROR OGG-00303 Unrecognized parameter (EXTRACT e_hr).

Immediatly it come to my mind that the problem was related to my copy/paste from powerpoint (training material I’m doing) to the putty window. Doing ‘:set list’ in vi did not show any strange character, but after recreating the extract parameter file from scratch with exactly the same parameters, it worked like a charm.

Go back test on Goldengate 12.2 and no error either.

So, if you have OGG-00041 error, it can be just that Goldengate is not interpreting well the parameter file.

Meanwhile, another problem on Goldengate 12.2 is that it crashes when you delete an extract while connected to a database.


Install latest version of Oracle Goldengate 12

With Oracle Goldengate 12 there is now a big change on the way to install: there is an installer!

Up to Oracle Goldengate 11 every new patch was a new complete version. With Oracle Goldengate 12 you need to install a base version and then the latest patch.

Here is a quick sum up of the instructions (done on AIX, but is similar on Linux):

  1. From Oracle website download the Goldengate 12.1.2.1.0 for your operating system
  2. From Oracle support note 1645495.1 download the latest patchset for Goldengate. Check the availability for your OS.
  3. Unzip both downloaded files
  4. Call the <download_dir>/ggs_AIX_ppc_shiphome/Disk1/runInstaller
  5. Follow the instructions by choosing the target DB version and install directory (I recommend $ORACLE_BASE/product/gg)
  6. Oracle GoldenGate 12.1.2.1.0 - Install Wizard - Step 1 of 5

    Oracle GoldenGate 12.1.2.1.0 - Install Wizard - Step 2 of 5

    Oracle GoldenGate 12.1.2.1.0 - Install Wizard - Step 3 of 5

    Oracle GoldenGate 12.1.2.1.0 - Install Wizard - Step 4 of 5

    Oracle GoldenGate 12.1.2.1.0 - Install Wizard - Step 5 of 5

  7. Check it works
  8. # set ORACLE_HOME to an existing Oracle database installation:
    export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4
    
    # set LD_LIBRARY_PATH to use libraries from $ORACLE_HOME:
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_BASE/product/gg
    
    # Start Goldengate:
    cd $ORACLE_BASE/product/gg
    ./ggsci
    
    Oracle GoldenGate Command Interpreter for Oracle
    Version 12.1.2.1.1 19797716 OGGCORE_12.1.2.1.1_PLATFORMS_141026.2215
    AIX 6, ppc, 64bit (optimized), Oracle 11g on Oct 27 2014 00:19:39
    Operating system character set identified as ISO-8859-1.
    
    Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
    
    GGSCI (anjovm1) 1> info all
    
    Program Status Group Lag at Chkpt Time Since Chkpt
    
    MANAGER STOPPED
    GGSCI (anjovm1) 2> exit
    
  9. Install the latest patchset with OPatch
  10. cd <download_dir>/21785294
    $ORACLE_BASE/product/gg/OPatch/opatch apply -oh $ORACLE_BASE/product/gg
    
    Invoking OPatch 11.2.0.1.7
    
    Oracle Interim Patch Installer version 11.2.0.1.7
    Copyright (c) 2011, Oracle Corporation.  All rights reserved.
    
    Oracle Home       : /oracle/app/product/gg121
    Central Inventory : /oracle/oraInventory
       from           : /etc/oraInst.loc
    OPatch version    : 11.2.0.1.7
    OUI version       : 11.2.0.3.0
    Log file location : /oracle/app/product/gg121/cfgtoollogs/opatch/opatch2015-11-11_08-32-55AM.log
    
    Applying interim patch '21785294' to OH '/oracle/app/product/gg121'
    Verifying environment and performing prerequisite checks...
    
    Do you want to proceed? [y|n] y
    User Responded with: Y
    All checks passed.
    
    Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
    (Oracle Home = '/oracle/app/product/gg121')
    
    Is the local system ready for patching? [y|n] y
    User Responded with: Y
    Backing up files...
    
    Patching component oracle.oggcore.ora11g, 12.1.2.1.0...
    Patch 21785294 successfully applied
    Log file location: /oracle/app/product/gg121/cfgtoollogs/opatch/opatch2015-11-11_08-32-55AM.log
    
    OPatch succeeded.
    
  11. Check it is updated
# Start Goldengate:
cd $ORACLE_BASE/product/gg
./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.10 21604177 OGGCORE_12.1.2.1.0OGGBP_PLATFORMS_150902.1337
AIX 6, ppc, 64bit (optimized), Oracle 11g on Sep  2 2015 16:29:21
Operating system character set identified as ISO-8859-1.

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

GGSCI (anjovm1) 1> 

We have now Oracle Goldengate 12.1.2.1.10 installed.

Interesting enough is that Oracle Goldengate 12.1 still uses the OPatch version 11.2.0.1.7, taken probably from Oracle 11.2.0.3, as we can see from the line “OUI version : 11.2.0.3.0”.


Goldengate initial load checks

These days I’m performing a near zero downtime upgrade of Oracle database from 10.2.0.4-32bit to 12.1.0.2-64bit. Both on Windows, source is 2003 and target 2012.

More information can be seen at the presentation here (not yet posted).

Now I want to call your attention to the initial load. There are a small but important checklist to follow to avoid bad surprises. On a proof-of-concept, using a source database without activity you are likely not to find any problem. If the source database is actively used by dozens of users, then attention!

I will not repeat all steps described at the Goldengate Documentation. These are the just the main reminders for a successful initial load.

Before starting the extract:
1. Supplemental log groups. In case using table level loggroups (source Oracle DB <= 11.2.0.3) – Check that all tables have log groups:

select owner,table_name from dba_tables where owner in ('<USER1>','<USER2>','<USER3>')
minus
select owner,table_name from dba_log_groups where owner in ('<USER1>','<USER2>','<USER3>');

Reason: on a active database, if the table is locked by some user, Goldengate will simply skip to add the log group (in fact, it gets “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired” but in the middle of adding the ADD TRANDATA command you will likely skip the error).

Note: if there is DDL replication, make sure that a log group is always created when a new table is created. The schema level log group should solve this problem.

After starting the extract
2. Goldengate only captures transactions that begin after the extract starts:

2015-03-31 08_27_16-16 Instantiating Oracle GoldenGate with an Initial Load (12c (12.1.2))

Extract of Goldengate 12c documentation.

So you need to find the time Extract started:

GGSCI> info extract E_*

And make sure that all transactions started after that time:

select b.inst_id, b.sid, b.serial#,b.username,b.machine ,b.status,b.prev_sql_id,c.sql_text,d.object_id,e.object_name,
a.start_time,to_char(b.logon_time,'MM/DD/YY HH24:MI:SS') logon_time
from    gv$transaction a, gv$session b , gv$sql c, v$locked_object d, all_objects e
where a.inst_id = b.inst_id
and a.ses_addr = b.SADDR
and b.prev_sql_addr = c.address(+)
and b.prev_hash_value = c.hash_value(+)
and b.prev_child_number = c.child_number(+)
and b.inst_id = c.inst_id(+)
and b.prev_sql_id=c.sql_id
and d.object_id=e.object_id
and d.session_id=b.sid(+);

The previous piece of SQL not only shows the starting time of the transaction, but also shows the user and machine holding that transaction open. It is useful to identify the person and contact her instead of just killing the session. 🙂

3. Get the current SCN and performing the consistent export of the database.

alter session set nls_date_format='DD.MM.YYYY HH24:MI:SS';
col current_scn for 99999999999999999
select sysdate, current_scn from v$database;

expdp DIRECTORY=migration DUMPFILE=expdp_MIGRATION_%U.dmp LOGFILE=expdp_MIGRATION.log parallel=8 filesize=5G flashback_scn=<previous-scn> SCHEMAS='<USER1>','<USER2>','<USER3>'

4. On the replicat side, just to assure it does not start at the wrong SCN, it is better you do not have on the manager configuration the parameter:

AUTOSTART ER *

Primary key not unique

Today I discovered that primary key indexes do not need to be unique. This makes a mess for checking supplemental logging for GoldenGate.

Here is the example:

SQL> create table t(c1 number, c2 number);
Table created.


SQL> create index i on t(c1,c2);
Index created.


SQL> alter table t add primary key (c1,c2) using index i;
Table altered.


SQL> select uniqueness from dba_indexes where table_name=’T';
UNIQUENES
———
NONUNIQUE


SQL>  select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where table_name=’T';
CONSTRAINT_NAME                C
—————————— -
SYS_C001632047                 P


SQL> insert into t values (1,2);
1 row created.


SQL> commit;
Commit complete.


SQL> insert into t values (1,2);
insert into t values (1,2)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C001632047) violated


SQL> alter table t drop constraint SYS_C001632047;
Table altered.


SQL> insert into t values (1,2);
1 row created.


SQL> commit;
Commit complete.


SQL> alter table t add primary key (c1,c2) using index i;
alter table t add primary key (c1,c2) using index i
*
ERROR at line 1:
ORA-02437: cannot validate (SYS.SYS_C001632048) – primary key violated


SQL> alter table t add primary key (c1,c2) using index i novalidate;
Table altered.


SQL>  select uniqueness from dba_indexes where table_name=’T';
UNIQUENES
———
NONUNIQUE


SQL> select * from t;
        C1         C2
———- ———-
         1          2
         1          2


SQL> select VALIDATED from dba_constraints where table_name=’T';
VALIDATED
————-
NOT VALIDATED


SQL> insert into t values (1,2);
insert into t values (1,2)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C001632049) violated


SQL> drop table t;
Table dropped.

Goldengate, RAC One node and PROCESSTHREADS parameter

I use Goldengate on classic capture mode to replicate several RAC One Node databases to an external system.
Since the first time there was a relocation of the RAC One Node database, there are two redo log threads (because the DB was temporarily a 2-node RAC).

Now the current thread# has changed and GoldenGate complaints that he cannot access anymore to the redologs.

What was necessary to do was:

1. Check which is the current thread:

SQL> select thread# from v$log where status=’CURRENT’; –> current=1

2. Add to the extract parameter file the parameter:
THREADOPTIONS PROCESSTHREADS EXCEPT 1

3. Recreate the extract with:

delete extract e_xx
add extract e_xx tranlog threads 2 begin now
add exttrail /oraacfs/goldengate/gg/dirdat/e_xx/xx extract e_xx

4. Check with
info e_xx
that the RBA for one of the threads increases with the time.

All this should not be needed when running in integrated capture mode.


GoldenGate – SCN translation

On GoldenGate when you do “SEND <extract> status” you get a SCN field which does not correspond to the database SCN:
GGSCI> send e_test status
Sending STATUS request to EXTRACT E_TEST …
EXTRACT E_TEST (PID 9372244)
Current status: Recovery complete: Processing data
Current read position:
Redo thread #: 1
Sequence #: 198315
RBA: 335376
Timestamp: 2013-11-11 16:11:57.000000
SCN: 78.924145222
Current write position:
Sequence #: 1548
RBA: 401683
Timestamp: 2013-11-11 16:12:31.811533
Extract Trail: /acfs/goldengate/dirdat/test/tt


SQL> select current_scn from v$database;
CURRENT_SCN
———————-
335931612762


To translate that SCN into the database SCN there is the calculation to make:


78 * (2^32) + 924145222 = 335931594310or with a hexadecimal translation:
78 to hexa        ==> 4E
924145222 to hexa ==> 37155646

Concatenate both and then
4E37155646 to decimal ==> 335931594310

Well, with the most recent versions of GoldenGate you can simply to “INFO <extract>” and you get the database SCN between parentheses.

GGSCI> info e_test
EXTRACT  E_TEST  Last Started 2013-11-13 08:08   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  Oracle Redo Logs
                  2013-11-13 09:57:40  Seqno 200447, RBA 16256592
                  SCN 78.1163096680 (336170545768)

Goldengate – Refresh single table

With the recent versions of GoldenGate >11.2 you can see the current SCN on with the extract process is reading, even when it is stopped:

GGSCI> INFO E_TEST
EXTRACT  E_TEST  Last Started 2013-11-13 03:50   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint  Oracle Redo Logs
                   2013-11-13 09:32:34  Seqno 508717, RBA 4366336
                   SCN 78.1156360444 (336163809532)

This makes it easiear to refresh a single table on a GoldenGate replication, if you can allow a small delay. There can be this need if you discover that it does not match anymore, or if you need to add a new table to the configuration. For that you do, in case of a Oracle to Oracle replication:
1 – Stop extract process and do INFO <extract> (see above). Note down the SCN between parentheses.
2 – Add the table to the parameter file (or remove an existing TABLEEXCLUDE on extract process or MAPEXCLUDE on replicat process) 
3 – Export the table from the source with FLASHBACK_SCN parameter pointing to the SCN taken from parameter 1.
4 – Make sure the replicat process has no delay with the command LAG <replicat>. You should get “At EOF, no more records to process.”
GGSCI> LAG R_TEST
Sending GETLAG request to REPLICAT R_TEST …
Last record lag: 4 seconds.
At EOF, no more records to process.
5 – Import the table at the destination (with TABLE_EXISTS=REPLACE, if needed)
6 – Re-start the extract process.
You can make the operation simpler if you have a database link between destination and source. In that case you skip the step 3 and use directly the FLASHBACK_SCN on step 5:
impdp NETWORK_LINK=source_db TABLES=test.table1 EXCLUDE=TRIGGER 

GoldenGate and Oracle 11.2

Some weeks ago a customer upgraded its databases from 10.2 to 11.2.0.3.

Some time after this change there were GoldenGate errors:

2013-02-03 10:11:55  ERROR   OGG-00519  Oracle GoldenGate Delivery for Oracle, r_xxxx.prm:  Fatal error executing DDL replication: error [Error code [922], ORA-00922: missing or invalid option, SQL create table “XXXXXX”.”DBMS_TABCOMP_TEMP_CMP” organization heap  tablespace “TBS_MAGIC” compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from “XXXXXX”.DBMS_TABCOMP_TEMP_U], no error handler present.
2013-02-03 10:14:55  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_xxxx.prm:  DDL found, operation [create table “XXXXXX”.DBMS_TABCOMP_TEMP_CMP organization heap  tablespace “TBS_MAGIC” compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from “XXXXXX”.DBMS_TABCOMP_TEMP_UNCMP mytab  (size 209)].
2013-02-03 10:14:55  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_xxxx.prm:  DDL is of mapped scope, after mapping new operation [create table “XXXXXX”.”DBMS_TABCOMP_TEMP_CMP” organization heap  tablespace “TBS_MAGIC” compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from “XXXXXX”.DBMS_TABCOMP_TEMP_UNCMP mytab  (size 211)].


After some investigation we found out this is due to the new “feature” Compression Advisor, from Oracle 11.2 (How Does Compression Advisor Work? [1284972.1]).


As it does “create table as select” (CTAS) which GoldenGate does not yet support well, the replicat abended.


There is already one Metalink note on this issue: DDL / Remap Schema : ORA-942 Connected With DBMS_TABCOMP_TEMP_CMP & DBMS_TABCOMP_TEMP_UNCMP [ID 1505178.1].


So we added to the extract parameter files:
TABLEEXCLUDE xxxxx.DBMS_TABCOMP_TEMP*



GoldenGate replicat performance

Today at a client I had a old abended replicat process that I decided to give life to. So I solved the problem and start it.

The environment is using GoldenGate 11.1.1.1.2 against Oracle 10.2.0.4 database with ASM, on AIX. The replicat process replicates all operations of a 700+ tables schema. There were other replicats running at the same time writing to other schemas.

Then I decided to see the performance of GoldenGate. With the query:

GGSCI> stats r_xxxx TOTALSONLY * REPORTRATE sec

You get the detailed statistics of operations per second since the start of the process, since the beginning of the day and last hour for all tables replicated by that process. For instance:

*** Hourly statistics since 2012-04-16 13:00:00 ***
        Total inserts/second:                     1397.76
        Total updates/second:                     1307.46
        Total deletes/second:                      991.50
        Total discards/second:                       0.00
        Total operations/second:                  3696.71

So here we can see it is doing a bit more than 3500 operations per second, divided quite evenly between inserts, updates and deletes.

As usually the GoldenGate is used for realtime replication and there are no big operations, the client does not use performance related parameters. But this time I decided to play with them.

After adding both: BATCHSQL and INSERTAPPEND to the parameter file of the replicat process, the results were the following (after more than 10 minutes running) and performance is still increasing:

*** Hourly statistics since 2012-04-16 13:43:17 ***
        Total inserts/second:                     2174.92
        Total updates/second:                     2540.20
        Total deletes/second:                     1636.16
        Total discards/second:                       0.00
        Total operations/second:                  6351.28

We see the performance increased by 90% !

I got interested to see if the BATCHSQL parameter only by itself could make the difference. So I removed the INSERTAPPEND parameter (which only influences the inserts anyway). Here are the results after more than 10 minutes.

*** Hourly statistics since 2012-04-16 14:00:00 ***
        Total inserts/second:                     2402.21
        Total updates/second:                     2185.24
        Total deletes/second:                     1742.43
        Total discards/second:                       0.00
        Total operations/second:                  6329.88

Yep, seems the system of my client in certain situations benefits mostly of the BATCHSQL parameter.

For those who don’t know, “in BATCHSQL mode, Replicat organizes similar SQL statements into batches within a memory queue, and then it applies each batch in one database operation. A batch contains SQL statements that affect the same table, operation type (insert, update, or delete), and column list.” (in GoldenGate Reference Guide).


New Oracle GoldenGate 11.2

Oracle released few weeks ago the new version of GoldenGate 11.2.1.0.0. The biggest novelty is the “integrated capture mode”, meaning that it starts working together with the internal log miner functionality.

This means no extra configuration is needed when extracting from an Oracle RAC or using ASM.

Soon I’ll be testing this new feature for a client.

Meanwhile, I leave you with a snippet of the “Release Notes”, page 6.