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:

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 *