Miguel Anjo


Oracle 12.1.0.2 – ORA-600 [QERHJDISABLEHJCBK: NOT A HASH JOIN] on UPDATE

Surprises happen when you go to the latest version of Oracle without deep testing.
My client just hit the bug:
Bug 20513930 : UPDATE FAILS WITH [QERHJDISABLEHJCBK: NOT A HASH JOIN]

Basically the client was getting a ORA-600 [QERHJDISABLEHJCBK: NOT A HASH JOIN] when doing a certain query. Looking at it, it was an UPDATE of a view. This view had a “instead of update” trigger, which was doing several actions. After enabling trace on the session, the bug does not happen!

Fortunately someone had already open the bug at Metalink. The workaround is to disable the new “groundbreaking feature” (dixit Oracle) called Adaptive Plans.

SQL> alter system set "_optimizer_adaptive_plans"=false;
System altered.

Hopefully there will be soon a patch for this problem.


Oracle automatic maintenance, expdp and ORA-01466

At a client request I’ve implemented a daily consistent export of few schemas, using something like:

expdp "/ as sysdba" SCHEMAS=user1 dumpfile=export_%1_%datetime%.dmp logfile=export_%1_%datetime%.log flashback_time=systimestamp

I was set to run at 22:00 every night.

Next day the log showed some errors like:

ORA-31693: Table data object "USER1"."TABLE_TEST" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01466: unable to read data - table definition has changed

Looking closer, it was due to the automatic statistics collection, done within the daily maintenance window of Oracle. The LAST_ANALYZED column of DBA_TABLES changed, as well as LAST_DDL_TIME on DBA_OBJECTS. I did not expect neither I see nowhere else complaining about this interference between the maintenance windows and a export data pump. Strange!

To avoid any problem in the future we decided to change the maintenance window time and lenght.
On Oracle 11g and 12c there is a window per day. So to change lets say to 19:00 and having a duration of 2 hours you do:

EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW','repeat_interval','freq=daily;byday=MON;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW','repeat_interval','freq=daily;byday=TUE;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW','repeat_interval','freq=daily;byday=THU;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW','repeat_interval','freq=daily;byday=FRI;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));

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 *