Monthly Archives: March 2015

Goldengate initial load checks

These days I’m performing a near zero downtime upgrade of Oracle database from to 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 <= – Check that all tables have log groups:

select owner,table_name from dba_tables where owner in ('<USER1>','<USER2>','<USER3>')
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:


Datapump elapsed time on

Data-Pump-for-OracleToday I notice a small difference when you export/import using Datapump and you connect to a database: there is the elapsed time at the end.

Both times I was using the binary from Oracle

Export: Release - Production on Sun Mar 22 21:07:25 2015

But the output is slight different at the end of the job:

Connected to: Oracle Database 11g Release - 64bit Production
Job "RMAN"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Mar 22 21:10:07 2015 elapsed 0 00:02:21


Connected to: Oracle Database 11g Release - Production
Job "RMAN"."SYS_EXPORT_SCHEMA_03" successfully completed at 21:07:43