Oracle … as usual

Oracle by Laurent Leturgez

Slides of my presentation at UKOUG TECH15 conference

The conference was last December, but I was a little bit busy this month …

So here are the slides of my presentation about SIMD extensions inside and outside Oracle 12c during the Super Sunday at UKOUG TECH15 conference.

It was my pleasure to have some great attendees in the audience: Frits Hoogland (ACE Director and Oak Table Member), Ludovico Caldara (Oracle Ace), Luca Canali (Oak Table Member), Mark Fielding (Oracle ACE), Bertrand Drouvot (Oracle Ace), Franck Pachot (Oracle Ace) and all the French guys who were at the conference!

Active Average Session trending in Statspack

Active Average Session (AAS) is one of the first statistic you have to look when you analyze a performance issue.

In Oracle Standard Edition (or Enterprise Edition without Diagnostic pack), you cannot query V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY views, nor execute ashrpt script.

If you have installed statspack and if you take snapshots, you can find Active Average Session between 2 snapshots.

AAS is calculated with the formula : DB Time / elapsed time. So, if you have recorded a db time of 300 seconds in 900 seconds (elapsed time between two snapshots), AAS is 0,33.

So it’s easy to calculate AAS between two snapshot, but the most important thing is to have AAS trending during a period.

To do that, I wrote a small PL/SQL function based on SYS_TIME_MODEL_DIF function available in STATSPACK package definition (in spcpkg.sql script).

create or replace function SYS_TIME_MODEL_DIF(i_name varchar2, bid number, eid number, db_ident number, inst_num number) RETURN number is

/* Returns the difference between statistics for the Time Model statistic
   name specified for the interval between the begin and end
   snapshot id's (bid, eid)

   In the case the data being queried is from a prior release
   which did not have the statistic requested, this function
   returns 0.

beg_val_missing   boolean := false;
end_val_missing   boolean := false;
bval           number;
eval           number;

cursor SY (i_snap_id number) is
select value
  from stats$sys_time_model      stm
     , stats$time_model_statname tms
where stm.snap_id         = i_snap_id
   and stm.dbid            = db_ident
   and stm.instance_number = inst_num
   and stm.stat_id         = tms.stat_id
   and tms.stat_name       = i_name;

   /* Get start value */
   open SY (bid); fetch SY into bval;
   if SY%notfound then
      beg_val_missing := true;
   end if; close SY;

   /* Get end value */
   open SY (eid); fetch SY into eval;
   if SY%notfound then
      end_val_missing := true;
   end if; close SY;

   if     beg_val_missing = true
      and end_val_missing = true      then

         /* this is likely a newer statitic which did not exist for this database version    */
        return 0;

   elsif     beg_val_missing = true
         and end_val_missing = false  then
         return -1;
   elsif     beg_val_missing = false
         and end_val_missing = true   then
         return -1;

        /* Return difference */
        return eval - bval;

   end if;


When this function has been created, you can call it to get Db time (or any value in the time model) between two snapshots (identified by their snapshot_id : bid (begin id) and eid (end id)) , for example :

SQL> select SYS_TIME_MODEL_DIF('DB time',791,792,1386309985,1) from dual;


SQL> select SYS_TIME_MODEL_DIF('DB CPU',791,792,1386309985,1) from dual;


SQL> select SYS_TIME_MODEL_DIF('parse time elapsed',791,792,1386309985,1) from dual;


Note that time is given in microseconds.

Next step, I wrote an anonymous PL/SQL script that executes this function accross all the snapshots taken between two snap_id and prints DB Time, DB CPU, and AAS in CSV format (you can adapt this script to adapt your output format):

 -- Constantes
 usec_to_sec number := 1000000;
 day_to_sec number := 86400;
 db_id number := &dbid
 dbtim number;
 dbcpu number;
 cursor SN_tim(sn_id number) is select snap_time from stats$snapshot where snap_id=sn_id;
 cursor snaps(sn1 number, sn2 number) is select snap_id from stats$snapshot where snap_id between sn1 and sn2 order by snap_id;
 b_time date;
 e_time date;
 bid number;
 eid number;
 cur_nf number;
open snaps(&start,&end);
fetch snaps into bid;
exit when snaps%notfound;
 cur_nf := 0;
 fetch snaps into eid;

 open SN_tim(bid); fetch SN_tim into b_time;
 if SN_tim%notfound then
 cur_nf := 1;
 end if;
 close SN_tim;

 open SN_tim(eid); fetch SN_tim into e_time;
 if SN_tim%notfound then
 cur_nf := 1;
 end if;
 close SN_tim;

 dbtim := SYS_TIME_MODEL_DIF('DB time',bid,eid,db_id,1);
 dbcpu := SYS_TIME_MODEL_DIF('DB CPU',bid,eid,db_id,1);
 if ((dbtim>0 or dbcpu>0) and cur_nf = 0) then
 dbms_output.put_line(b_time||';'||e_time||';'||round(dbtim/usec_to_sec,2) ||';'||round(dbcpu/usec_to_sec,2) ||';'|| round((dbtim/usec_to_sec) / ((e_time-b_time)*day_to_sec),2) );
 dbms_output.put_line(b_time||';'||e_time||';N/A;N/A' );
 end if;
end loop;

Then you can graph it in Excel or Tableau (for example) and you will get the AAS trend:


Keep in touch with My Oracle Support updates

This post is related to My Oracle Support (MOS) and one of the features every DBA has to configure is the Hot Topic email.

This feature will send you an email every day ou week (depending on how you configure it) with all the new stuff published or updated on MOS (Knowledge base article, bugs, alerts …).

Of course, you can filter on the products you want to subscribe and select the number of items you want to receive by category.

To configure this fonction, log in to MOS with your account and click on the “settings” tab, ,the select the “Hot Topic Email” link on the left … see in the screenshot below (green box):


Then, you will see the “hot topics” part and here you can configure it:

  • On the first part (1), you can configure the frequency of the mail and its format. You can also configure the content you want to include in this mail: favorites and Service requests
  • Next, on the second part (2), which is for me the most important part, you will select the products you want to subscribe and what kind of information you want to receive (knowledge articles, alerts, bugs etc.)
  • Finally, on the third part (3), you will choose the number of items the mail will includes. In the previous screenshot, I will receive 25 bugs (for all the products I selected), 25 knowledge articles etc. (Usually the bug and knowledge articles are the most important parts… yes, there are a lot of bugs updated every day ;) )

As a result, I receive this kind of email every day:


Alert Product Area Last Updated
Bug 20830449 – Disk corruption possible when a disk media error occurs while synchronous I/O is performed Oracle Exadata Storage Server Software Fri, 26 Jun 2015 18:44 GMT+01:00



Bug Product Area Bug ID Last Updated
ORACLE PROCESS RUNNING OUT OF OS KERNEL I/O RESOURCES EVEN AFTER APPLY 6343215 Oracle Database – Enterprise Edition 7523755 Sat, 27 Jun 2015 13:10 GMT+01:00
MESSAGES IN AQ$_ALERT_QT_E ARE NOT BEING PURGED IN A RAC CLUSTER Oracle Database – Enterprise Edition 19904389 Sat, 27 Jun 2015 13:10 GMT+01:00
ORA-01792 OCCURED WHEN USING RIGHT OUTER JOIN Oracle Database – Enterprise Edition 9256994 Sat, 27 Jun 2015 12:55 GMT+01:00
ORA-600 [KLAPRS_11]/[KLAPRS_12] BEING HIT DURING IMPDP Oracle Database – Enterprise Edition 6944948 Sat, 27 Jun 2015 12:54 GMT+01:00
ORA-00600 [KDTIGETROW-2] AFTER UPGRADE TO BUNDLE 22 Oracle Database – Enterprise Edition 17343797 Sat, 27 Jun 2015 12:52 GMT+01:00
MERGE SQL WITH BOTH AN INSERT AND UPDATE AND LOGGING ENABLED CAN CAUSE INT. ERR Oracle Database – Enterprise Edition 17397545 Sat, 27 Jun 2015 12:52 GMT+01:00
WRONG PRECISION RETURNED FOR QUERY AFTER DB UPGRADE TO Oracle Database – Enterprise Edition 21241579 Sat, 27 Jun 2015 12:49 GMT+01:00
NUMA MESSAGES IN THE ALERT LOG AFTER UPGRADE Oracle Database – Enterprise Edition 17475024 Sat, 27 Jun 2015 12:49 GMT+01:00
ORA-600 [QKSVCREPLACEVC0] USING SQL TUNING ADVISOR Oracle Database – Enterprise Edition 17401718 Sat, 27 Jun 2015 12:45 GMT+01:00
ORA-600 [QKSVCREPLACEVC0] WHEN AUTOMATIC SQL TUNING ADVISOR EXECUTED Oracle Database – Enterprise Edition 16491690 Sat, 27 Jun 2015 12:45 GMT+01:00
ORA-00600 [QKSVCREPLACEVC0] Oracle Database – Enterprise Edition 13959984 Sat, 27 Jun 2015 12:45 GMT+01:00
INSTANCE CRASHES WITH ORA-600 [KJBRWRDONE:SC2] ON LMS Oracle Database – Enterprise Edition 17027916 Sat, 27 Jun 2015 12:35 GMT+01:00
WITH, THE CAPTURE MECHANISM STARTS UP TOO MANY PARALLEL QUERY SERVERS Oracle Database – Enterprise Edition 19587324 Sat, 27 Jun 2015 12:28 GMT+01:00
WAIT DEPENDENCY ON APPLY INSERT AFTER UPGRADE TO Oracle Database – Enterprise Edition 19442102 Sat, 27 Jun 2015 12:28 GMT+01:00
FAILED TO RAISE ORA-1 FOR PK UPDATE WHEN CONSTRAINT=IMMEDIATE Oracle Database – Enterprise Edition 19440386 Sat, 27 Jun 2015 12:28 GMT+01:00
INTEGRATED REPLICAT INVALIDATES DEPENDENT PACKAGES RESULTING IN AN ORA-4068 Oracle Database – Enterprise Edition 19277336 Sat, 27 Jun 2015 12:28 GMT+01:00
PGA MEMORY LEAK ON APPLY SERVER ON KOH-KGHU CALL – KNGLXRCOL Oracle Database – Enterprise Edition 18973548 Sat, 27 Jun 2015 12:28 GMT+01:00
LOGMINER DDL TRACKING NOT SCALABLE, SESSIONS BLOCK EACHOTHER DURING DDL APPLY Oracle Database – Enterprise Edition 16674686 Sat, 27 Jun 2015 12:28 GMT+01:00
MALFORMED KTU-KRVMISC TRANSACTION FINALIZATION MARKER Oracle Database – Enterprise Edition 14705949 Sat, 27 Jun 2015 12:28 GMT+01:00
ORA 7445 [__INTEL_SSSE3_REP_MEMCPY] WITH CLUSTER TABLE CREATION Oracle Database – Enterprise Edition 21041573 Sat, 27 Jun 2015 12:27 GMT+01:00
ORA-600 [17285] RUNNING DIFFERENT APPLICATION PACKAGES Oracle Database – Enterprise Edition 19475971 Sat, 27 Jun 2015 12:09 GMT+01:00
WRONG RESULTS USING FUNCTION BASED INDEX WITH LOWER FUNCTION Oracle Database – Enterprise Edition 18550648 Sat, 27 Jun 2015 12:06 GMT+01:00
PGA MEMORY SPIKE WHEN CONSISTENTLY RUNNING A JAVA STORED PROCEDURE Oracle Database – Enterprise Edition 20806625 Sat, 27 Jun 2015 12:06 GMT+01:00
LOADJAVA FAILS WITH ORA-12154 WHEN USING NAME-VALUE PAIR IN CONNECTION STRING. Oracle Database – Enterprise Edition 21072270 Sat, 27 Jun 2015 12:05 GMT+01:00
ORA-600 [KSSADD: NULL PARENT] DURING HIGH DATABASE ACTIVITY Oracle Database – Enterprise Edition 16590736 Sat, 27 Jun 2015 11:50 GMT+01:00


Knowledge Articles

Knowledge Article Product Area Last Updated
Does Weblogic Server support SAML2.0 Single Logout Protocol? Oracle WebLogic Server Sat, 27 Jun 2015 02:52 GMT+01:00
How to use the DBMS_SQLPA (SQL Performance Analyzer) API to test database upgrade using CONVERT SQLSET Oracle Database – Enterprise Edition Sat, 27 Jun 2015 02:37 GMT+01:00
12c ACFS Configuration on ASM Flex Architecture (White Paper) Oracle Database – Enterprise Edition Fri, 26 Jun 2015 23:37 GMT+01:00
Best Practices and Recommendations for RAC databases using very large SGA (e.g. 100 GB) Oracle Database – Enterprise Edition Fri, 26 Jun 2015 23:24 GMT+01:00
Receive “There are Issues with the Configuration of The Static Files…” After Upgrading to APEX 5.0 Oracle Application Express (formerly HTML DB) Oracle REST Data Services Oracle HTTP Server Oracle Database – Enterprise Edition Fri, 26 Jun 2015 23:19 GMT+01:00
Cluster Health Monitor (CHM) FAQ Oracle Database – Enterprise Edition Fri, 26 Jun 2015 23:16 GMT+01:00
ODA: “oakcli show repo” Does Not Show Any Repository After Restart Oracle Database Appliance Software Oracle Database Appliance X3-2 Fri, 26 Jun 2015 22:34 GMT+01:00
NTP leap second event causing Oracle Clusterware node reboot Oracle Database – Enterprise Edition Fri, 26 Jun 2015 22:29 GMT+01:00
ODA (Oracle Database Appliance): Leap Second adjustment impact Oracle Database Appliance Oracle Database Appliance Software Fri, 26 Jun 2015 22:01 GMT+01:00
Bug 20830449 – Disk corruption possible when a disk media error occurs while synchronous I/O is performed Oracle Exadata Storage Server Software Fri, 26 Jun 2015 18:44 GMT+01:00
Exadata release and patch (20748218) Oracle Exadata Storage Server Software Fri, 26 Jun 2015 18:40 GMT+01:00
11gR2 Grid Infrastructure Does not Use ulimit Setting Appropriately Oracle Database – Standard Edition Oracle Database – Enterprise Edition Fri, 26 Jun 2015 18:33 GMT+01:00
Exadata Database Machine and Exadata Storage Server Supported Versions Oracle Exadata Hardware Exadata Database Machine X2-2 Hardware Oracle Platinum Services Oracle Exadata Storage Server Software Oracle Database – Enterprise Edition Fri, 26 Jun 2015 18:32 GMT+01:00
Disable SSLv3 And Enable TLSv2 / TLSv1.1/ TLSv1.2 Oracle WebLogic Server Fri, 26 Jun 2015 18:30 GMT+01:00
ORA-7445 Troubleshooting Tool Oracle Database – Enterprise Edition Fri, 26 Jun 2015 17:13 GMT+01:00
ORA-600 Troubleshooting Tool Oracle Database – Enterprise Edition Fri, 26 Jun 2015 16:58 GMT+01:00
hp-ux: Database Instance Startup is Slow in 11gR2 as DBW0 Has High Wait “Disk file operations I/O” Oracle Database – Enterprise Edition Fri, 26 Jun 2015 16:49 GMT+01:00
ODA (Oracle Database Appliance): Unable to Delete VM Oracle Database Appliance Software Fri, 26 Jun 2015 16:49 GMT+01:00
WLS Admin Server Deadlock Detected Waiting To Acquire Lock Java.util.concurrent.CopyOnWriteArrayList Oracle WebLogic Server Fri, 26 Jun 2015 16:43 GMT+01:00
[WLS-10.3.6] -JAXB Marshaller Returns The Class Name Instead Of The Objects In The List Oracle WebLogic Server Fri, 26 Jun 2015 16:14 GMT+01:00
Oracle Advance Security Licence Components Oracle Database – Enterprise Edition Fri, 26 Jun 2015 16:11 GMT+01:00
Steps to Implement Address Windowing Extensions (AWE) / VLM on 32-bit Windows Platforms Oracle Database – Enterprise Edition Fri, 26 Jun 2015 15:50 GMT+01:00
DBMS_AUDIT_MGMT does not release the space occupied by LOB segment Oracle Database – Standard Edition Fri, 26 Jun 2015 15:47 GMT+01:00
WLS Wtc-l10n.jar Missing Oracle WebLogic Server Fri, 26 Jun 2015 15:26 GMT+01:00
Egypt cancels DST in 2015 – Impact on Oracle RDBMS Oracle Database – Standard Edition Oracle Database – Enterprise Edition Fri, 26 Jun 2015 15:21 GMT+01:00



Oracle 12c Application Continuity and its resources usage

During a recent Oracle Meetup in Paris, I met Martin Bach and Bertrand Drouvot. Martin was here to present on Application Continuity, and we had an interesting discussion about this technology. (Bertrand had recently published an interesting post about Application Continuity in a Dataguard configuration … here).

In the post, I will explain quickly how it works based on an sample code I wrote few months ago. I will explained too application continuity resources allocation, where they are consumed, and how much is the resource overhead.

12c Application Continuity … What is it and how does it work?

Application Continuity is a technology that will improve application availability. Before Oracle 12c, Transparent Application Failover (TAF) or Fast Connection Failover (FCF) coud help you to improve application availability without downtime, but developers usually have to implement a TAF or FCF aware code. And usually, it’s wasn’t the case for many reasons (testing and UAT platform which weren’t RAC platform, specifications weren’t written in this aim etc.).

In 12c, Oracle releases application continuity. This technology will do the same thing with more simplicity and has been developed to handle planned and unplanned outages.

Application Continuity works on Oracle 12c database and with application that use:

  • Oracle Weblogic Server
  • Oracle UCP (Unified Connection Pool)
  • Third party JAVA application server which used the PooledConnection Interface
  • JDBC Thin-driver

The lab …

To test application continuity, I wrote a simple test case based on :

  • a 12c ( RAC Cluster with a container database (CDB), a Pluggable Database (PDB), and a Database Schema (LAURENT) with on 2 tables and a “spy” trigger.
  • a small Java Code sample.

On the database side, I created a TEST table add fill it with 500000 rows, and another one (SPY_TABLE) which will track the id of the TEST table that have been modified :

SQL> create table test as select rownum id, dbms_random.string('U',20) v from dual connect by level <= 500000;

Table created.

SQL> create table spy_table(d date, inst_name varchar2(16), id number);

Table created.

Row updates tracking is made by a trigger on update on the TEST table:

SQL> create or replace trigger spy_test
 2  before update on test for each row
 3  declare
 4    pragma autonomous_transaction;
 5  begin
 6    insert into spy_table values(sysdate,sys_context('USERENV', 'INSTANCE_NAME'),;
 7  commit;
 8  end;
 9 /

Trigger created.

To make the test, we have to create a RAC service for our database which have these features:

  • FAILOVER_TYPE has to be set on TRANSACTION to enable Application Continuity
  • COMMIT_FAILOVER has to be set to TRUE (Even if it’s a parameter related to Transaction Guard, you have to set it)
  • REPLAY_INITIATION_TIMEOUT, FAILOVER_RETRIES, FAILOVER_DELAY should be reviewed to configure the delays for the replay (see this link for more information)
[oracle@rac1 JAVA]$ srvctl add service -d cdb -s app -preferred cdb1 -available cdb2 -pdb pdb -j SHORT -B SERVICE_TIME -z 30 -w 10 -commit_outcome TRUE -e TRANSACTION -replay_init_time 1800 -retention 86400 -notification TRUE
[oracle@rac1 JAVA]$ srvctl start service -s app -d cdb

[oracle@rac1 JAVA]$ srvctl status service -d cdb
Service app is running on instance(s) cdb1

[oracle@rac1 JAVA]$ /u01/app/12.1.0/grid/bin/crsctl stat res -t
Name           Target  State        Server                   State details
Cluster Resources
      1        ONLINE  ONLINE       rac1                     STABLE

Note: I voluntarily configured the service with a preferred node to be sure It will be always the same I will use for my tests (related to resources). But I recommend you to configure it with all instances as “preferred”.

The Java source code is available at this link.

Procedures and functions are:

  • pressAnyKeyToContinue: it’s a private method that make a pause during the program. This pause can be useful to attach a JConsole or VisualVM to track JVM memory and CPU.
  • getStatValue: this function returns the value of an oracle statistics for the current session.
  • doTx: this is the most important function in this program. It will perform the transaction and it’s in this one you will find the most important thing when we code Java with Application Continuity: the callback. This callback is delimited by two calls: beginRequest and endRequest. In this callback, you will put all of the operation you want to protect against failures. If a planned or unplanned outage occurs during the callback, the transaction will be rolled back and then replayed. Please note that in the callback area, you have to:
    • call the prepareStatement function to assign the SQL statement, otherwise you will throw a SQLRecoverableException during the connection failover.
    • disable autoCommit for the connection.
    • put a commit statement to say where the transaction ends.
    private void doTx(Connection c, int numValue) throws SQLException {
        String updsql = "UPDATE test " +
                        "SET v=UPPER(v) " +
                        "WHERE id=?";
        PreparedStatement pstmt = null;

        /* Beginning of the callback
         * AutoCommit has to be disabled in the callback part.
        for (int i=0;i<numValue;i++) {
        // End of the Callback.

  • In the main function, the most important thing is to use the new OracleDataSourceImpl class instead of OracleDataSource.

Then, I compile the code and run it with the number of updates I want to execute in my transaction.

First run

During the run, I execute a “kill -9” against the PMON process of the instance where my java program is connected to:

[oracle@rac1 JAVA]$ ./ SimpleAC 50000
Instance Name = cdb1
Press any key to continue...
Performing transactions


Instance Name = cdb2
CPU used by this session = 38643
session pga memory max = 4140696

We can see that, even with an instance failure which was running a transaction (of 50000 updates), the failover occurs without any error message.

The Spy table shows the results above:

SQL> select inst_name,count(*) from spy_table group by inst_name;

---------------- ----------
cdb2		      50000
cdb1		       1270

This proves that the transaction has been rolled-back and completely replayed after the connection failover.

Mutable values

When a transaction becomes replayable, a problem comes with what we call a mutable value. If you are interested by the definition you can read this on wikipedia: But I prefer to explain this in a different way.

Imagine we do a close test to the previous one, but instead of updating data, we insert lines into a table and in this table, we have a timestamp column which takes the value of systimestamp oracle function when we insert a line. Now an outage occurs, the connection if failed over and the transaction is rolled back and replayed on the other node. If we don’t say to Oracle that this value is immutable, the value will be inserted at the timestamp recorded on the second node and not the original one and this could cause some problems in your data integrity.

This problem occurs with sysdate, systimestamp, sequence values and another function that depends on your process id (and other parameters) : SYS_GUID.

If you want to make your variables immutables, you need to have the KEEP grant:

SQL> show user
SQL> grant keep date time to laurent;

Grant succeeded.

SQL> grant keep sysguid to laurent;

Grant succeeded.

SQL> connect laurent/laurent@rac-scan:1521/app

SQL> grant keep sequence on seq1 to laurent;

Grant succeeded.

No pain, no gain … what about resources consumption?

Application continuity is a good thing because, with less code modification your java app will become completely fault tolerant. As the documentation mentions, there’s an overhead on CPU and memory … then, let’s check this !

To test this, I used the same environment than previously but I didn’t kill any process, I just ran it with and without callback definition.

Then I measured different things:

  • CPU used by the database session (statistic: CPU used by this session)
  • Memory consumed by the database session (statistic: session pga memory max)
  • CPU used by the java program
  • Memory consumed by the java program (Java heap memory usage)

CPU and memory consumed by the java program has been measured with this good old Jconsole.

To get the java heap memory usage, I set up the program with a heap size of 512Mb as it was the good value to avoid garbage collection during execution.

Here are the results:

  • With callback defined

[oracle@rac1 JAVA]$ egrep 'beginRequest|endRequest'

[oracle@rac1 JAVA]$ ./

[oracle@rac1 JAVA]$ ./ -Xms512M -Xmx512M SimpleAC 50000
Instance Name = cdb1
Press any key to continue...
Performing transactions
Instance Name = cdb1
CPU used by this session = 34859
session pga memory max = 3961144


Note the heap memory usage high water mark is 110Mb and the average CPU is 0,4%

  • With no callback defined
[oracle@rac1 JAVA]$ egrep 'beginRequest|endRequest'

[oracle@rac1 JAVA]$ ./

[oracle@rac1 JAVA]$ ./ -Xms512M -Xmx512M SimpleAC 50000
Instance Name = cdb1
Press any key to continue...
Performing transactions
Instance Name = cdb1
CPU used by this session = 35138
session pga memory max = 3747576


The Heap usage high water mark is 91Mb and the average CPU is 0,2%.

To sum up, we can tell that there is a memory overhead on the jdbc layer (for my example which was based on a transaction of 50000 updates). The CPU overhead announced by Oracle seems to be trivial. On the database side, no overhead has been found on CPU nor on the memory.

If you search more information about Application Continuity, you can read this paper by Oracle :

Oracle 12c, VMWare Fusion and the perl binary’s segmentation fault

Recently I have installed a new Virtual Machine in VM Ware Fusion 7 Pro under Oracle Enterprise Linux 6.6 for running Oracle 12c Grid Infrastructure and database ( Mac OS X is a 10.9.5 (Mavericks). As usual for my tests environment, I deploy the oracle-rdbms-server-12cR1-preinstall rpm package to be sure everything will be correctly configured on my system. During the installation process, there was a first error during link edition of rman (“error invoking target irman ioracle of makefile /u01/app/oracle/product/12.1.0/grid/rdbms/lib/”). This problem can be fixed by copying the libjavavm12.a library located in $ORACLE_HOME/javavm/jdk/jdk7/lib to $ORACLE_HOME/lib directory, and then retry the link edition by clicking on “Retry” button. So, if you don’t use Grid Infrastructure here or if you install only the database kernel … you won’t have any problem. Maybe, you will encountered the problems detailed above if you create a mutitenant (or single tenant) database (because it needs to run script … but we will see why later). In my case, I was deploying Grid Infrastructure and at the end of the installation process, you have to run script to configure the CRS stack. At this step, the script fails with an error on script and a cute “Segmentation fault (core dumped)” error:

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
/u01/app/oracle/product/12.1.0/grid/crs/config/ line 131: 4562 Segmentation fault (core dumped) $ROOTSCRIPT $ROOTSCRIPT_ARGS
The command '/u01/app/oracle/product/12.1.0/grid/perl/bin/perl -I/u01/app/oracle/product/12.1.0/grid/perl/lib -I/u01/app/oracle/product/12.1.0/grid/crs/install /u01/app/oracle/product/12.1.0/grid/crs/install/ ' execution failed

After analyzing the error, I get the cause of my problem which is located on the perl binary delivered with Oracle. If I run a simple “perl -v”, I got the error.

[oracle@oel6 bin]$ pwd
[oracle@oel6 bin]$ ./perl -v
Segmentation fault (core dumped)

If we go deeper, gdb shows us an error located on PerlIO functions and specifically on PerlIO_default_layers function:

[oracle@oel6 bin]$ gdb perl
GNU gdb (GDB) Red Hat Enterprise Linux (7.2-75.el6)
Copyright (C) 2010 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
Reading symbols from /u01/app/oracle/product/12.1.0/grid/perl/bin/perl...(no debugging symbols found)...done.
(gdb) r
Starting program: /u01/app/oracle/product/12.1.0/grid/perl/bin/perl
warning: no loadable sections found in added symbol-file system-supplied DSO at 0x7ffff7ffa000
[Thread debugging using libthread_db enabled]

Program received signal SIGSEGV, Segmentation fault.
0x0000000000531399 in PerlIO_default_layers ()
Missing separate debuginfos, use: debuginfo-install glibc-2.12-1.149.el6_6.7.x86_64 libgcc-4.4.7-11.el6.x86_64 nss-softokn-freebl-3.14.3-22.el6_6.x86_64
(gdb) bt
#0 0x0000000000531399 in PerlIO_default_layers ()
#1 0x000000000053070e in PerlIO_resolve_layers ()
#2 0x000000000053050f in PerlIO_openn ()
#3 0x0000000000530206 in PerlIO_fdopen ()
#4 0x0000000000530198 in PerlIO_stdstreams ()
#5 0x0000000000530150 in Perl_PerlIO_stdin ()
#6 0x0000000000435f09 in S_parse_body ()
#7 0x00000000004343bb in perl_parse ()
#8 0x000000000041cf13 in main ()

IMHO, it’s a specific problem on WMWare fusion because on a VM hosted on Virtualbox there’s no problem, and more :

  • If I copy the working perl binary from the virtualbox VM to the VMWare VM … execution failed on VMWare
  • If I copy the failing perl binary from the VMWare VM to the virtualbox VM … it works fine

At the time I write this post, I have asked to a friend of mine if he can reproduced the problem on a VM hosted on an ESX server, I will update this post as soon as I will get his results. I made a lot of different tests (Downgrading VMWare fusion to Fusion 6 Pro, Installing OEL 6.4, OEL 7.0, Redhat 6.5 and 7, creating my vmdk’s on another disk, changing scsi driver etc … always the same problem). I read on many blogs, people have faced the same problem and have symlinked the failing perl to the system perl binary which runs fine. Doing this can produce different issues because of the INC directory which is not the same, you can encountered some errors due to version compatibility between modules in $OH/perl/lib directory and the perl binary. Indeed Oracle 12cR1 uses a perl v5.14.1 and system perl binary is 5.10 on OEL/RHEL 6, and 5.16 on OEL/RHEL7. I workarounded the problem by recreating the perl binary from sources, but you need to be careful of the $OH/perl/lib directory, because it contains all the perl modules needed by Oracle. See above, the different steps to compile perl binary and replace it in $ORACLE_HOME:

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/grid
cd ~
rm -rf perl
mkdir perl
cd perl/
curl -O
tar -xvzf perl-5.14.1.tar.gz

mv perl/ perl.OLD
mkdir perl
cd /home/oracle/perl/perl-5.14.1
./Configure -des -Dprefix=$ORACLE_HOME/perl -Doptimize=-O3 -Dusethreads -Duseithreads -Duserelocatableinc ; make clean ; make ; make install
cd $ORACLE_HOME/perl
rm -rf lib/ man/
cp -r ../perl.OLD/lib/ .
cp -r ../perl.OLD/man/ .
cp ../perl.OLD/bin/dbilogstrip bin/
cp ../perl.OLD/bin/dbiprof bin/
cp ../perl.OLD/bin/dbiproxy bin/
cp ../perl.OLD/bin/ora_explain bin/

Once these steps are done and you have a running perl binary, you can safely run your script, create multitenant databases, and all the stuff that needs $ORACLE_HOME/bin/perl.

UPDATE: I have just downloaded the last release of Oracle Virtualbox (5.0) on my Macbook and the same problems occurs. They can be resolved by using the same method described in this post.

UPDATE2 : I would like to thank Deiby Gomez for his trick. Indeed, in my original post I forgot to keep some perl binaries that could be useful ;) : dbilogstrip, dbiprof, dbiproxy and ora_explain. The script above has been modified.

UPDATE3:  Danny Bryant from Enkitec has worked closely with Oracle VM and virtual box team and they discovered the origin of the bug. This one should be fixed in a next release. But, if you use Virtualbox 5.0, Danny published a workaround, you can read it following this link:



Get every new post delivered to your Inbox.

Join 352 other followers