{"id":106,"date":"2015-03-25T19:45:40","date_gmt":"2015-03-25T18:45:40","guid":{"rendered":"http:\/\/anjo.pt\/wp\/keyword-oracle\/?p=106"},"modified":"2015-11-17T20:27:22","modified_gmt":"2015-11-17T19:27:22","slug":"goldengate-initial-load-checks","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2015\/03\/25\/goldengate-initial-load-checks\/","title":{"rendered":"Goldengate initial load checks"},"content":{"rendered":"

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.<\/p>\n

More information can be seen at the presentation here<\/a>\u00a0(not yet posted).<\/p>\n

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!<\/p>\n

I will not repeat all steps described at the Goldengate Documentation<\/a>. These are the\u00a0just the main reminders for a successful initial load.<\/p>\n

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

\r\nselect owner,table_name from dba_tables where owner in ('<USER1>','<USER2>','<USER3>')\r\nminus\r\nselect owner,table_name from dba_log_groups where owner in ('<USER1>','<USER2>','<USER3>');\r\n<\/pre>\n
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).<\/p>\n
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.<\/p>\n
After starting the extract
\n2. Goldengate only captures transactions that begin\u00a0after the extract starts:<\/p>\n
\"2015-03-31<\/a>
Extract of Goldengate 12c documentation.<\/p><\/div>\n
So you\u00a0need to find the time Extract started:<\/p>\n
\r\nGGSCI> info extract E_*\r\n<\/pre>\n
And make sure that all transactions started after that time:<\/p>\n
\r\nselect 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,\r\na.start_time,to_char(b.logon_time,'MM\/DD\/YY HH24:MI:SS') logon_time\r\nfrom    gv$transaction a, gv$session b , gv$sql c, v$locked_object d, all_objects e\r\nwhere a.inst_id = b.inst_id\r\nand a.ses_addr = b.SADDR\r\nand b.prev_sql_addr = c.address(+)\r\nand b.prev_hash_value = c.hash_value(+)\r\nand b.prev_child_number = c.child_number(+)\r\nand b.inst_id = c.inst_id(+)\r\nand b.prev_sql_id=c.sql_id\r\nand d.object_id=e.object_id\r\nand d.session_id=b.sid(+);\r\n<\/pre>\n
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. \ud83d\ude42<\/p>\n
3. Get the current SCN and performing the consistent export of the database.<\/p>\n
\r\nalter session set nls_date_format='DD.MM.YYYY HH24:MI:SS';\r\ncol current_scn for 99999999999999999\r\nselect sysdate, current_scn from v$database;\r\n\r\nexpdp DIRECTORY=migration DUMPFILE=expdp_MIGRATION_%U.dmp LOGFILE=expdp_MIGRATION.log parallel=8 filesize=5G flashback_scn=<previous-scn>\u00a0SCHEMAS='<USER1>','<USER2>','<USER3>'\r\n<\/pre>\n
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:<\/p>\n
\r\nAUTOSTART ER *\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
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\u00a0(not yet posted). Now I want to call your attention to the initial load. There are a small but important checklist to […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,6],"tags":[],"class_list":{"0":"post-106","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-goldengate","7":"category-oracle","8":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/106","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=106"}],"version-history":[{"count":5,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/106\/revisions"}],"predecessor-version":[{"id":115,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/106\/revisions\/115"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=106"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=106"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=106"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}