Friday Sep 04, 2015

Oracle non-CDB architecture may be desupported after Oracle Database 12.2

You may wonder about the headline of this blog post.
Haven't I blogged about this a long time ago?

Yes, I did. On January 22, 2015 - over 7 months ago:
https://blogs.oracle.com/UPGRADE/entry/non_cdb_architecture_of_oracle

But whenever in the past weeks this message has been refreshed and got a bit more precise (which I'd guess everybody appreciates). Now we are clearly saying that such a change won't happen with Oracle 12.2. Before we've said only "in a later release".

See the Oracle Database 12c Upgrade Guide

Deprecation Note non-CDB architecture

In case you'd like to explore the world of (at least) Oracle Single Tenant (no extra license required, available even with SE2) which I'd highly recommend you'll find some useful advice in this slide deck:

  • How Oracle Single Tenant will change a DBA's life 
  •  

    --Mike 

    Thursday Sep 03, 2015

    Can you have Oracle Multitenant in Oracle 12.1.0.2 SE2?

    Can you have Oracle Multitenant in Oracle Standard Edition 12.1.0.2 SE2?

    No, you can't as Oracle Multitenant is a licensable option for Enterprise Edition (EE) databases only.

    But wait a second ...

    You can do Oracle Single Tenant with SE2 of course meaning you can have one active pluggable database within one container database at a time. That is possible and does not require any additional licenses.

    See the DBCA (Database Configuration Assistant) screen when you'll try to create a container database:

    DBCA - Single Tenant

    More information? 

    You'll find more information about the differences in handling and such between Oracle non-CDB, Oracle Single Tenant and Oracle Multitenant databases in this presentation:

    --Mike 

    Wednesday Sep 02, 2015

    No patches anymore for Oracle Database 11.2.0.3

    Just in case you've missed the date please be aware:

    No bug fixes, no patches, no CPU/SPUs, no PSUs, no BPs will be produced for Oracle Database 11.2.0.3 since Augugst 28, 2015

    I know from the many emails I'm receiving that people are a bit disturbed since the Oracle Lifetime Support Policy brochure (Lifetime Support Policy and Brochure for Technology Products ) doesn't talk explicitly about patch sets but offers support for Oracle 11.2 until January 2018.

    Oracle 11.2 Lifetime Support Policy

    But this - as always - applies to the terminal patch set only - i.e. Oracle Database 11.2.0.4.

    For a detailed description please see MOS Note:742060.1 and my previous blog posts from:

    I have visited customers with hundreds of Oracle 11.2.0.3 databases in the past months. If you are now sitting there with one or many or many many Oracle 11.2.0.3 instances don't even think of moving to Oracle 11.2.0.4. The amount of work you'll spend in evaluating Oracle 11.2.0.4 is exactly the same as testing Oracle 12.1.0.2. Move to Oracle Database 12.1.0.2 - NOW!

    Waived Extended Support for Oracle Database 11.2.0.4 will end in less than 5 months.

    And keep in mind: there's no such thing as a 2nd release anymore. Every patch set release is a full release - no matter if we talk about 11.2.0.4 or 12.1.0.2. Both have new features, new behavior etc etc.

    --Mike

    Tuesday Sep 01, 2015

    Oracle Database 12.1.0.2 Standard Edition (SE2) available for download

    Oracle SE2Finally ...

    Oracle Database 12.1.0.2 Standard Edition (SE2) is available for download.

    Some information is already available in the Oracle Database Licensing Information. And you'll find more here: https://www.oracle.com/database/standard-edition-two/index.html

    Quoting from the pricing document:
    http://www.oracle.com/us/corporate/pricing/databaselicensing-070584.pdf

    "Oracle Database Standard Edition 2 may only be licensed on servers that have a maximum capacity of 2 sockets. When used with Oracle Real Application Clusters, Oracle Database Standard Edition 2 may only be licensed on a maximum of 2 one-socket servers. In addition, notwithstanding any provision in Your Oracle license agreement to the contrary, each Oracle Database Standard Edition 2 database may use a maximum of 16 CPU threads at any time. When used with Oracle Real Application Clusters, each Oracle Database Standard Edition 2 database may use a maximum of 8 CPU threads per instance at any time. The minimums when licensing by Named User Plus (NUP) metric are 10 NUP licenses per server."

    .

    Download Oracle Database 12.1.0.2 Standard Edition (SE2) on OTN

    (12.1.0.2.0) - Standard Edition (SE2)

    Download Microsoft Windows x64 (64-bit) File 1, File 2  (2.6 GB)
    Download Linux x86-64 File 1, File 2  (2.5 GB)
    Download Oracle Solaris (SPARC systems, 64-bit) File 1, File 2  (2.7 GB)
    Download Oracle Solaris (x86 systems, 64-bit) File 1, File 2  (2.5 GB)
    Download HP-UX Itanium File 1, File 2  (3.3 GB)
    Download AIX (PPC64) File 1, File 2  (2.9 GB)
    Download zLinux64 File 1, File 2  (2.5 GB)

    And on the Oracle Software Cloud (edelivery.oracle.com) which is the recommended download location:

    Edelivery.oracle.com SE2


    This location has been a corrected download link pointing to OTN as well:

    https://www.oracle.com/database/standard-edition-two/index.html


    .

    For a good overview about first experiences with Oracle Database Standard Edition 12.1.0.2 (SE2) please see Franck Pachot's excellent blog post here:

     

     

    --Mike 

    Upgrade downtime credited to APEX

    What do you think when you see this post-upgrade result?

    Oracle Database 12.1 Post-Upgrade Status Tool           08-07-2015 15:08:26

    Component                               Current         Version  Elapsed Time
    Name                                    Status          Number   HH:MM:SS

    Oracle Server                          UPGRADED      12.1.0.2.0  00:19:26
    JServer JAVA Virtual Machine              VALID      12.1.0.2.0  00:10:52
    Oracle Workspace Manager                  VALID      12.1.0.2.0  00:01:52
    OLAP Analytic Workspace                   VALID      12.1.0.2.0  00:00:34
    OLAP Catalog                         OPTION OFF      11.2.0.3.0  00:00:00
    Oracle OLAP API                           VALID      12.1.0.2.0  00:00:42
    Oracle XDK                                VALID      12.1.0.2.0  00:01:07
    Oracle Text                               VALID      12.1.0.2.0  00:01:36
    Oracle XML Database                       VALID      12.1.0.2.0  00:03:55
    Oracle Database Java Packages             VALID      12.1.0.2.0  00:00:22
    Oracle Multimedia                         VALID      12.1.0.2.0  00:03:57
    Spatial                                UPGRADED      12.1.0.2.0  00:08:56
    Oracle Application Express                VALID     4.2.5.00.08  00:46:19
    Final Actions                                                    00:03:48

    Total Upgrade Time: 01:44:16

    I've got a bit worried as the time to upgrade APEX took 44% of the complete database upgrade downtime. APEX (Oracle Application Express) is a fantastic piece of software which is still completely underrated - potentially because it is for free for everybody who has an Oracle Database license. And things not costing anything are just worth nothing, ey? 

    Simply be aware when you have APEX in your databases installed - and especially if you ACTIVELY use APEX - it may be a very good idea to upgrade APEX upfront without causing downtime for your entire database.

    See this blog post here about how to upgrade APEX upfront:

    https://blogs.oracle.com/UPGRADE/entry/save_upgrade_downtime_upgrade_apex 

    --Mike

    Friday Aug 21, 2015

    DBMS_STATS. GATHER_DICTIONARY_STATS fails with ORA-20001 ORA-6502 ORA-6512 - Concurrent Stats

    I really have to say "Thank you very much" to the people out there alerting me about issues I haven't seen before. Just in the past week I've got to learn about three issues which are related to the upgrade - and I haven't seen before. Please don't expect me always to follow up with the progress of your SRs or the related bugs. But your experiences are such an important source for me - so thanks again!

    The most recent issue (thanks to Bernd Tuba from MM Warburg) ...

    Symptom

    execute dbms_stats.gather_dictionary_stats

    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at "SYS.DBMS_STATS", line 24268
    ORA-20001: Unexpected configuration: sofar: 41 objList.count: 41 sofar_part: 0 partObjList.count: 0 sofar_ind: 0 indObjList.count: 21 concurrent: TRUE runPartTable: TRUE jstats.Running: -19
    ORA-06512: at "SYS.DBMS_STATS", line 27383
    ORA-06512: at "SYS.DBMS_STATS", line 27402
    ORA-06512: at line 1

    I see the word "CONCURRENT" - and it immediately rings a bell. Wasn't there an issue with the change of setting for default stats gathering in Oracle Database 12.1.0.2 being now CONCURRENT=TRUE?

    Yes ... the reason for this blog's existence is not only to give you some interesting useful information to read about but also to dump of details from my limited brain capacity :-) I have written something about a similar issue in October 2014:

    https://blogs.oracle.com/UPGRADE/entry/ora_20000_unable_to_gather
    .

    Analysis

    This is interesting. Whereas in my other blog post the concurrent stats gathering conflicted with the resource manager settings and is fixed by a patch (Patch 19664340: ORA-20000: UNABLE TO GATHER STATISTICS CONCURRENTLY: RESOURCE MANAGER IS NOT ENABLED) this one is a different topic.

    Bug:18406728  DBMS_STATS.GATHER_DICTIONARY_STATS GOES INTO SPIN CONSUMING 100% CPU 
    (unpublished bug - therefore no link included here)

    Solution

    Very simple to solve - switch CONCURRENT stats gathering to FALSE

    SQL> exec dbms_stats.set_global_prefs('CONCURRENT', 'FALSE');

    In the Exadata Upgrade Note (MOS Note: 1681467.1) you'll find the same recommendation. 

    More Information? 

    --Mike 

    Thursday Aug 20, 2015

    Upgrade nach Oracle Database 12c - alles ganz einfach!

    Sorry - German only - if you seek for a very similar presentation in English please find it here:
    https://blogs.oracle.com/UPGRADE/entry/webcast_why_upgrade_to_oracle
    .


    Danke noch mal an meinen Kollegen Frank Schneede, der das erste Webinar rund um 12c eingeführt hat. 

    Das Replay meines Seminar-Teils ist hier in Deutsch verfügbar:


    Viel Spass :-)

    --Mike 

    Tuesday Aug 18, 2015

    Ouch, this hurts: bug 17325413 - patch BEFORE upgrade!

    PatchI really don't want to turn this blog into something making our database look bad. But in this case it is really necessary as it is VERY UNUSUAL that we recommend to patch the database BEFORE upgrade


    Just for clarification:

    The following topic will affect databases 11.2.0.3.9+, 11.2.0.4.0 and 11.2.0.4.1 only - those (and only those) need to be patched BEFORE upgrade. The topic is fixed in 12.1.0.2 but as it gets introduced with the BEFORE upgrade database version you'll have to apply the fix before upgrade. The inclusion of the fix in 12.1.0.2 means only that the misbehavior won't happen there again. But as it is a meta data dictionary corruption you'll have to apply the fix before as otherwise it will break during or after the upgrade.


    First of all, thanks to Ehtiram Hasanov (cleverbridge AG) and Oliver Pyka (http://www.pyka.de/) for highlighting this to me. And sorry for hitting this issue ...

    Symptoms:

    After upgrading to Oracle Database 12.1.0.2 you'll get one of the below errors when trying to read data: 

    • ORA-07445: exception encountered: core dump [qcsIsColInFro()+358] [SIGSEGV] [ADDR:0x4] [PC:0xCDB4A26] [Address not mapped to object] []
    • ORA-12899 / ORA-607
    • ORA-600 [kdmv_check_row_2:IMCU row has wrong contents]
    • ORA-600 [kddummy_blkchk]
    • ORA-600 [kdBlkCheckError]
    • ORA-600 [klaprs_12]
    • ORA-600 [13013]
    • ORA-600 [17182] 

    Analysis:

    Basically this happens when you try to drop a column with a DEFAULT value and a NOT NULL definition - it ends up with dropped column data being written to disk leading to block corruptions. This causes problems for generating undo which cannot be applied; a ROLLBACK fails.

    If you need more information please look up this MOS Note about
    Bug 17325413 - Drop column with DEFAULT value and NOT NULL definition ends up with dropped column data hitting disk leading to corruption

    Versions being affected:

    • These versions require to be patched BEFORE upgrade:
      • Oracle Database 11.2.0.3.9 and above (may happen with earlier PSUs as well)
        Solution: Apply the fix 17325413  on top - see below
      • Oracle Database 11.2.0.4.0 and 11.2.0.4.1 
        Solution: Apply the most recent PSU
    • These versions can get you the issue if you haven't patched BEFORE upgrade:
      • Oracle Database 12.1.0.1
      • Oracle Database 12.1.0.2

    Workaround and/or Fix:

    The MOS Note about Bug 17325413 - Drop column with DEFAULT value and NOT NULL definition ends up with dropped column data hitting disk leading to corruption explains the workaround WHEN you hit this issues. 

    As a precaution you will have to make sure that you applied one of those fixes BEFORE upgrading to Oracle Database 12.1.0.2. as the fix for Bug 17325413 is included in all those mentioned below (list is taken from above MOS Note as well).

    The best way to avoid this is really to apply the patch (or the PSU/BP including the patch) before upgrading.

    The issue has been mentioned in "Oracle 11.2.0.4 - Known Issues and Alerts" (MOS Note:1562139.1)  under "Issues Introduced":

    Issues introduced

    But that does jump into your eye as a thing you need to fix before upgrade.
    We'll see if we can get the issue added to the 12c MOS Notes as "Upgrade Issues".

    --Mike 

    Monday Aug 17, 2015

    DBCA 12c and "datapatch.pl" - things to know

    A few weeks ago I did blog about the DBUA (Database Upgrade Assistant) not executing 'datapatch' (i.e. not applying the SQL changes involved with a SPU/PSU/BP) automatically:

    Again, please note that this behavior DOES NOT APPLY to command line upgrades done with catctl.pl - as you can see from this somewhat disturbing messages during the upgrade in phase 65 and phase 69 (which are not errors but just informational messages for datapatch's execution):

    Datapatch phases command line upgrade catctl.pl

    But afterwards I have learned that things are worse.
    The same behavior is true when you create a database.

    Not a typo.
    You create a fresh database with DBCA (Database Configuration Assistant), you are a honest customer, you have followed our advice and applied the most recent PSU (or SPU or BP) into your Oracle Home. You don't even deploy one of the preconfigured databases but use the CREATE CUSTOM DATABASE option of DBCA. And the database will run from the patched home - but the SQL changes haven't been applied to it

    Reason?

    DBCA does not call 'datapatch' for database changes.

     

    I consider this even worse than the DBUA behavior as the person who upgrades a database in most cases is aware of the future home. But the person who either deploys a new database or asks for one to be deployed is often not identical with the person who did patch the homes.

    And there's no warning displayed yet nor (afaik) is there a MOS note available talking about it.

    How do you fix the issue?

    After creating a new database make sure to run:

     

    cd $ORACLE_HOME/OPatch
    ./datapatch -verbose

    and double check with DBA_REGISTRY_SQLPATCH view:

    select  PATCH_ID, PATCH_UID, VERSION, STATUS, DESCRIPTION
    from DBA_REGISTRY_SQLPATCH
    order by BUNDLE_SERIES;

    Actually to be 100% you may find some patch information in DBA_REGISTRY_SQLPATCH showing that the JAVAVM patch has been applied in case you've installed the Combo version of the PSU. But you'll miss the database changes.

    --Mike

    Friday Aug 14, 2015

    Oracle Databases and Microsoft Windows 10

    MS Windows 10If you have upgraded already or plan to upgrade to Microsoft Windows 10 in the near (or later) future, you may find this Statement Of Direction by Oracle regarding Microsoft Windows 10 certification quite useful:

    http://www.oracle.com/technetwork/database/windows/whatsnew/oracle-db-win10-sod-2626195.pdf

    In summary:

    • Oracle plans to certify Oracle Database 12.1.0.2 by October 2015
    • Oracle plans to certify the next major version of the database as well on MS Win 10 - 64bit
    --Mike 

    Tuesday Aug 04, 2015

    Other people's thoughts: "Should you upgrade to 12c?"

    You still don't believe me yet when I say: You can't seriously wait for the so called 2nd release of Oracle Database 12c as it will be an entirely new release again? And you haven't upgraded yet to Oracle Database 12.1.0.2 for various reasons? 

    Then sometimes it's good to listen to other people's opinions - and I'm happy to share this 6 minute video by Tim Hall (very well known for his great page oracle-base). Listen to Tim and his Thoughts about Upgrading to Oracle Database 12c. And don't get disturbed that he's driving on the wrong side of the road ;-)


    And I promise I didn't bribe Tim (cocktails don't count) ;-)

    Thanks Tim!!! 

    --Mike 

    Thursday Jul 30, 2015

    SQL Monitoring - Limitation at 300 lines per statement

    One of the best parts of my job at Oracle:
    I still learn something new every day.

    Yesterday I've learned from my colleague from Oracle Switzerland, Thomas Teske, that SQL Real Time Monitoring has an embedded default limitation on the number of lines in the statement. If the limit (default: 300 lines) is exceeded the statement won't be monitored. We both work with a leading Swiss company and we wanted to monitor a complex plan. 

    Now you may think: Who the heck has statements longer than 300 lines?
    Well ... sometimes that is beyond your influence as in this particular case this is of course done by the application.

    Solution:

    SQL> alter system set "_sqlmon_max_planlines"=800 scope=both;

    or set in your spfile:

    _sqlmon_max_planlines=800

    This limitation is described in:

    MOS Note:1613163.1
    How to Monitor SQL Statements with Large Plans Using Real-Time SQL Monitoring?

    If you'd like to read a bit more about SQL Real Time Monitoring please follow one of these links - and be aware that it's part of the Tuning Pack license and VERY helpful in many everyday situations. You'll have to have STATISTICS_LEVEL either TYPICAL (the default) or ALL and CONTROL_MANAGEMENT_PACK_ACCESS='DIAGNOSTIC+TUNING' (the default as well).

     

    --Mike 

    Things to consider BEFORE upgrading to Oracle 12.1.0.2 to AVOID poor performance and wrong results

    Finally it got published on MyOracle Support (MOS) portal - one of the most helpful notes when we prep for an upgrade or a migration - and potentially the one with the longest title in MOS:

    MOS Note: 2034610.1
    Things to consider BEFORE upgrading to Oracle Database 12.1.0.2
    to AVOID poor performance and wrong results 

    Avoid Poor Performance and Wrong Results when upgrading to 12.1.0.2

    Just in case you are NOT upgrading/migrating to Oracle Database 12.1.0.2 there are three other helpful notes for earlier releases available as well:

    Document 1320966.1
    Things to Consider Before Upgrading to 11.2.0.2
    to Avoid Poor Performance or Wrong Results

    Document 1392633.1
    Things to Consider Before Upgrading to 11.2.0.3
    to Avoid Poor Performance or Wrong Results
    Document 1645862.1
    Things to Consider Before Upgrading to 11.2.0.4
    to Avoid Poor Performance or Wrong Results

    --Mike 

    Tuesday Jul 28, 2015

    Optimizer Issue in Oracle 12.0.1.2: "Reduce Group By"

    Wrong Query Results BugDBAs biggest fears I'd guess are Optimizer Wrong Query Results bugs as usually the optimizer does not write a message into the alert.log saying "Sorry, I was in a bad mood today ..."

    The Oracle Database Optimizer is a complex piece - and in Oracle 12c it delivers great performance results. Plus (my personal experience when you know what to do) it is more predictable which I like a lot when changing databases from one to another release. But due to its complexity sometimes we see issues - and sometimes it is necessary to switch off tiny little pieces until a fix is available.

    Roy just came across this one - and we believe it's worth to tell you about it. Again, our intention is only to prevent issues when upgrading or migrating to Oracle Database 12.1.0.2.

    Symptom:

    An outer join query with a bind variable and a group by clause can produce wrong results in some cases.

    Analysis:

     If all of the following match, you may be hitting this bug:
     - two or more subquery views are outer-joined on column C1
     - column C1 is specified on select list of top-most query block
     - column C1 is filtered on a bind value

    Example:

     create table test1(c1 number(5),c2 varchar2(16));
     insert into test1 values(1,'3');
     commit;

     set NULL NULL
     variable num1 number
     execute :num1 :=1;

     -- Following query retuns wrong result(NULL), this should return 1.

     select V.c1 from
      (SELECT c1 FROM test1 GROUP BY c1) V,
      (SELECT c1 FROM test1 WHERE c2 = '1' GROUP BY c1) V2
     where  V.c1 = :num1
        and V.c1 = V2.c1(+);

    Workaround:

    alter session set "_optimizer_reduce_groupby_key" = false;

    Please don't use the workaround:
    alter session set optimizer_features_enable='12.1.0.1';
    as this will switch off other good 12.1.0.2 optimizer features working very well.

    More information:

    See MOS Note:20634449.8 describing:
    Bug 20634449 - Wrong results from OUTER JOIN with a bind variable and a GROUP BY clause in 12.1.0.2

    As far as I can see there are no interim (one-off/single) patches available right now. 

    --Mike

    Friday Jul 24, 2015

    Why does a PDB require an upgrade?

    Why do I need to upgrade (or downgrade) a Pluggable Database (PDB) once I unplug it from version A and replug into version B?

    This question is one of the most often asked questions in our workshops when Roy and I present Upgrades and Oracle Multitenant.

    If you take a look into the documentation you'll find:

    But unfortunately this is not 100% correct for every object.

    When you do a simple query for basic dictionary structures such as OBJ$ you'll easily find out that a PDB has its own Data Dictionary.  Some things are really linked into the PDB only - check the SHARING column of DBA_OBJECTS for instance. If it says "METADATA LINK" it will give you an indication that it exchanges information with its parent in the CDB$ROOT. But that doesn't mean necessarily that it's just an empty meta object. Some tables do exist in the PDB as well and allocate space. Check it by yourself and you'll find out that OBJ$ inside a PDB has in fact the same attribute - but still owns segments in the PDB and in the CDB$ROOT - as other objects do allocate space as well. And of course the object's definition exists in the PDB as well. 

    • CDB$ROOT

      OBJ$ in CDB$ROOT

    • PDB 

      OBJ$ in PDB
      .

    So the answer is simple:
    A Pluggable Database (PDB) must be upgraded (or downgraded) when it get moved between versions simply because it has its own data dictionary. And this doesn't get upgraded automatically.
    ,

     

    --Mike 

    Monday Jul 20, 2015

    DBUA 12c and "datapatch.pl" - things to know

    For clarification:
    The following blog post applies to upgrades to Oracle 12.1 done by DBUA only whenever a SPU/PSU/BP is installed into the 12.1 home prior to the upgrade (which I'd highly recommend as patching before upgrade saves you headache after upgrade!).



    Two customers independently reported last week that they have doubts on DBUA's ability to apply the required SQL changes associated with CPU/SPU or PSU

    First of all, let me tell you that this is not an issue when you do a command line upgrade to Oracle Database 12c with catctl.pl - you'll only need to take care when using the DBUA

    One claimed that this feature alongside with datapatch.pl had been announced a while back:

    Oracle Premier Support - Oracle Database Support News
    Issue November, 2014 Volume 46
    (Doc ID 1954478.1)

    Which Patching Tools uses Datapatch ?

    • Opatchauto   
      • OPatchAuto calls datapatch automatically to complete post patch actions upon installation of the binary patch and restart of the database.
    • Enterprise Manager Cloud Control   
      • Starting version 12.1 EMCC now calls datapatch to complete post patch actions upon any 12c or later database restart
    • Upgrade   
      • Catctl.pl and DBUA now call Datapatch during the upgrade process
    • OPatch  
      • Datapatch integration with OPatch is not possible as OPatch is executed when the database is down and datapatch requires the database to be open to complete its activity.

    The other customer provided all the logfiles - and I print the important logs only with the interesting part marked in RED:

    ==========================================
    Contents of catupgrd_datapatch_upgrade.log
    ==========================================
    SQL Patching tool version 12.2.0.0.0 on Tue Jul 14 13:10:39 2015
    Copyright (c) 2014, Oracle.  All rights reserved.
    Connecting to database...OK
    Bootstrapping registry and package to current versions...done
    Determining current state...done
    Current state of SQL patches:
    Patch 19282028 (Database PSU 12.1.0.2.1, Oracle JavaVM Component ():
      Installed in the binary registry only
    Bundle series PSU:
      ID 1 in the binary registry and not installed in the SQL registry
    Adding patches to installation queue and performing prereq checks...
    Installation queue:
      Nothing to roll back
    Nothing to apply
    SQL Patching tool complete on Tue Jul 14 13:10:57 2015

    =============================================
    Contents of sqlpatch_catcon__catcon_22773.lst
    =============================================
    catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts
    catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any
    !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
            catconInit: start logging catcon output at 2015-07-14 13:10:39

    !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    Ok, so it seems to be true that DBUA did not apply the post upgrade SQL changes associated with the most recent PSU.

    DBUA 12c

    Now digging a bit deeper we could solve the puzzle.

    The DBUA uses the "catctl.pl -x" option executing catuppst.sql (the post upgrade script) in a separate step whereas on the command line catctl.pl will execute catuppst.sql by default (tracked with bug19990037). The DBUA uses catcon.pl instead to execute catuppst.sql. In previous releases this was not an issue as catbundle.sql got automatically executed as part of catuppst.sql. But as datapatch.pl is a PERL script, and a PERL script cannot be run from within a SQL script, catuppst.sql can no longer call the post-patching activities. The DBUA in 12.1.0.2 misses this action as a separate task.

    Summary and Solution

    DBUA misses the post-upgrade datapatch execution in Oracle 12.1.0.2. The solution is to apply the SQL changes manually after DBUA has completed the database upgrade to Oracle Database 12c:

    cd $ORACLE_HOME/OPatch
    ./datapatch -verbose

    And again, this is only necessary when you used the DBUA for a database upgrade. This step is not required for the command line upgrade. This will be fixed in an upcoming release of the DBUA.

    If you are in doubt whether the DBUA or the command line upgrade had been used, unfortunately you won't find any indication inside the database. But look into $ORACLE_BASE/cfgtoollogs/dbua/logs - if the "dbua" directory exists, the DBUA had been used. If not than the command line upgrade had been processed.

    --Mike 

    Monday Jul 13, 2015

    Updated version of the In-Memory Advisor is available

    Which of your tables and/or partitions should you mark for In-Memory column store availability? 

    You'll get the answer with the help of the Oracle In-.Memory Advisor which just got updated and is available via MyOracle Support:

    The Advisor produces a report identifying the objects that should be placed In-Memory for maximum benefit, along with a SQL*PLUS script which implements those recommendations. It can be run on Oracle Database 11.2.0.3 and above. And of course the recommendations can be implemented on Oracle Database 12.1.0.2. (and newer).

    Important to know:
    The In-Memory Advisor is licensed as part of the Database Tuning Pack.

    Further information can be found at: OTN

    The New Features include:

    • Oracle Multitenant support
    • Enhanced analysis
    • and bug fixes

     

    See the RELEASE NOTES for further details on version 1.0.0.1. 

    --Mike 

    Thursday Jul 02, 2015

    SAP on Oracle Database 12c now with Oracle In-Memory

    Oracle SAPOn March 31, 2015 SAP has been certified to run on Oracle Database 12.1.0.2:
    https://blogs.oracle.com/UPGRADE/entry/sap_is_now_certified_on

    As of June 30, 2015, Oracle Database In-Memory is supported and certified for SAP environments for all SAP products based on SAP NetWeaver 7.x. on Unix/Linux, Windows and Oracle Engineered Systems platforms running Oracle Database 12c - in single instance and Oracle Oracle Real Application Clusters deployments.

    Oracle Database 12c is the database of choice for SAP customers based on In-Memory Technology which is fully supported for SAP BW and SAP OLTP applications.

    For requirements, restrictions, and implementation details see the documents below.

    --Mike

    PS: Fidel, thanks for the correct links - highly appreciated!!! 

    Wednesday Jul 01, 2015

    Log Writer Slave Issues in 12.1.0.2 - mainly on IBM AIX

    Currently we see a lot of issues with the Multiple Logwriter feature in Oracle Database 12.1.0.2, especially on the IBM AIX platform.

    What are Multiple LGWRs?

    You will see multiple LGnn (Log Writer Worker) processes on multiprocessor systems, The LGWR (Log Writer) creates worker processes (slaves) to improve the performance of writing to the redo log. LGnn (Log Writer Worker) processes are not used when there is a SYNC standby destination. Possible processes include LG00-LG99.

    Here is a list of new background processes in Oracle Database 12.1:
    MOS Note 1625912.1 - New Background Processes In 12c 

    For deep dive information about how to trace the LGWR (and DBWR) and the differences 11.2/12.1 and wait events and much more, please see this Deep Dive PDF from an UKOUG talk from Frits Hoogland.

    Known Symptoms:


    Solution:

    Turn off multiple logwriters on IBM AIX at the moment - in some cases this may solve issues on other platforms as well but please don't take this as a generic recommendation for all platforms. We are seeing the issues mainly on IBM AIX.

    Set in your spfile: 

    _use_single_log_writer=true

    .

    --Mike 

    Tuesday Jun 30, 2015

    Some Data Pump issues:
    + DBMS_DATAPUMP Import via NETWORK_LINK fails
    + STATUS parameter giving bad performance

    One of my dear Oracle ACS colleagues (Danke Thomas!) highlighted this issue to me as one of his lead customers hit this pitfall a week ago. . 

    DBMS_DATAPUMP Import Over NETWORK_LINK fails with ORA-39126 / ORA-31600

    Symptoms are: 

    KUPW$WORKER.CONFIGURE_METADATA_UNLOAD [ESTIMATE_PHASE]
    ORA-31600: invalid input value IN ('VIEWS_AS_TABLES/TABLE_DATA') for parameter VALUE in function SET_FILTER

    This can be cured with the patch for bug19501000 -  but this patch can conflict with:Bug 18793246  EXPDP slow showing base object lookup during datapump export causes full table scan per object and therefore may require a merge patch - patch 21253883 is the one to go with in this case.

     .

    Another issue Roy just came across:

    Data Pump is giving bad performance in Oracle 12.1.0.2 when the STATUS parameter option is used on command line

    Symptoms are: 

    It looks like the routines we are using to get status are significantly slower in 12c than in 11g. On 11.2.0.4 a STATUS call of expdp/impdp runs in 0.2-0.3 seconds, but in 12.1.0.2 it takes 0.8-1.6 seconds. As a result the client falls behind on 12.1.0.2; it is taking about 0.5-0.8 seconds to put out each line in the logfile because it is getting the status each time. With over 9000 tables in a test that half a second really adds up. The result in this test case was that the data pump job completed in 35 minutes, but it took another 30-35 minutes to finish putting out messages on the client (the log file was already complete) and return control to the command line. This happens only when you use STATUS on the command line.

    Recommendation is:  

    Don't use the STATUS parameter on the expdp/impdp command line in Oracle 12.1.0.2 until the issue is fixed. This will be tracked under Bug 21123545.

    --Mike 

    Monday Jun 29, 2015

    Premier Support for Oracle 11.2 has ended months ago

    I have blogged many times about the End of Premier Support for Oracle Database 11.2 and the different stages and regulations for Extended Support for this release. But still I'm getting inquiries almost every day, internally and externally.

    Let me first point out that I'm not a rep of Oracle Support. I'm just a Product Manager with some Oracle Support background. So you'll always have to consult our officially available documents and sources as regulations may change after I published this blog post. I'm just trying to summarize what I know at the moment to help you avoid some of the usual misunderstandings.

    Premier Support for Oracle 11.2 has already ended

    Fact. Premier Support for Oracle 11.2 has ended on Jan 31, 2015. Period. Regardless of the patch level you may be on. No further discussion here please. Just for your notes: this has happened a while ago in case you've missed the date:

    Extended Support for Oracle 11.2 has started on Feb 1, 2015

    • For Oracle Database 11.2.0.1 there's no such Extended Support offering available.
      This information can be found in MOS Note:742060.1.
      .
    • For Oracle Database 11.2.0.2 there's no such Extended Support offering available.
      This information can be found in MOS Note:742060.1.
      .
    • For Oracle Database 11.2.0.3 Oracle will waive Extended Support.
      But any patching for Oracle Database 11.2.0.3 will end on 27-AUG-2015 meaning no further bug fixes or patches for Oracle Database 11.2.0.3 (including PSUs, SPUs and BPs) after 27-AUG-2015 (please see the table below). Any bug fixing support for Oracle 11.2.0.3 (of course including the waived Extended Support offering) will end at this date. No extension possible.
      This information can be found in MOS Note:742060.1.
      .
    • For Oracle Database 11.2.0.4 - the Terminal (final) Patch Set for Oracle Database 11.2 - Oracle will waive (no extra cost, no action required) the entire first year of Extended Support to every customer with a valid support contract. After 31-JAN-2016 you can purchase an additional maximum of two years of Extended Support for Oracle Database 11.2.0.4 only. 
      This information can be found in MOS Note:742060.1.
      .

    Finally please look into MOS Note:742060.1 and MOS Note:1067455.1:

    End of Patching

    Quoting from MOS Note:1067455.1:

    Support for Patch Set 11.2.0.3
    Under the new policy for overlapping Patch Set support, 11.2.0.3 will continue to be supported through 27 August 2015. The last Critical Patch Updates (Security Patch Update and Patch Set Update) for 11.2.0.3 will be released in July 2015. After then you will need to be running on 11.2.0.4 or a supported 12.1 Patch Set to get Critical Patch Updates.

    I have learned in the past weeks that some information in the official Support documents (Lifetime Support Policy)such as the Brochure for Technology Products (including of course the database but also a clear description about the different levels of support) is not clear and obvious to everybody. This docs don't differentiate between the several patch releases.  

    Premier Extended Support Oracle Database

    Therefore you will have to consult MOS Note:742060.1 for further clarification and specific dates per patch level. 

    --Mike 

    Tuesday Jun 23, 2015

    Package Differences between Oracle 11.2.0.4 and 12.1.0.2?

    The question sounds pretty trivial:

    Which packages did exist in Oracle 11.2.0.4 but don't exist in Oracle 12.1.0.2 anymore?

    And going a bit deeper into the topic,

    Which procedures and functions calls of SYS packages have been changed since then?

    .

    A colleague of mine (thanks to Thomas Kempkens from ACS Support for writing up a fancy procedure - all credits go to him) had to dig this out for a customer,

    See the result.
    These packages don't exist in Oracle 12.1.0.2 anymore but did exist in Oracle 11.2.0.4 (without PSUs):

    • CWM2_OLAP_INSTALLER
    • DBMS_AMD
    • DBMS_APPCTX
    • DBMS_DBLINK
    • DBMS_DM_IMP_INTERNAL
    • DBMS_DM_UTIL_INTERNAL
    • DBMS_DUMA_INTERNAL
    • DBMS_JDM_INTERNAL
    • DBMS_RULE_COMPATIBLE_90
    • DBMS_SQL2
    • DBMS_XDSUTL
    • DBMS_XS_SECCLASS_INT
    • DBMS_XS_SECCLASS_INT_FFI
    • EXF$DBMS_EXPFIL_SYSPACK
    • HTMLDB_SYSTEM
    • ODM_ABN_MODEL
    • SQLJUTL2

    17 packages don't exist anymore.

    And this is the list of
    Procedures and Functions Calls inside of Oracle SYS packages which have changed in Oracle 12.1.0.2:

    • PACKAGE_NAME               SUBTYPE    SUBTYPE_NAME
    • -------------------------- ---------- ------------------------------------------------
    • CWM2_OLAP_INSTALLER        PROCEDURE  VALIDATE_CWM2_INSTALL
    • DBMS_AMD                   PROCEDURE  MOVE_OLAP_CATALOG
    • DBMS_APPCTX                PROCEDURE  CLEAR_CONTEXT
    • DBMS_APPCTX                PROCEDURE  SET_CONTEXT
    • DBMS_AQADM_SYS             PROCEDURE  GET_CORR_MSG
    • DBMS_AQADM_SYS             PROCEDURE  RESET_CORR_MSG
    • DBMS_AQADM_SYSCALLS        PROCEDURE  KWQA_3GL_UPDPERSTATS
    • DBMS_DATA_MINING           FUNCTION   GET_MODEL_DETAILS_ABN RETURNS DM_ABN_DETAILS
    • DBMS_DATA_MINING_INTERNAL  PROCEDURE  CREATE_TEMP_META_TABLE
    • DBMS_DBLINK                PROCEDURE  UPGRADE
    • DBMS_DDL_INTERNAL          FUNCTION   GEN_NEW_OBJECT_ID RETURNS BINARY_INTEGER
    • DBMS_DM_EXP_INTERNAL       FUNCTION   GET_TRACE RETURNS BOOLEAN
    • DBMS_DM_EXP_INTERNAL       PROCEDURE  DELETE_FROM_TEMP
    • DBMS_DM_EXP_INTERNAL       PROCEDURE  DO_TEMP_TABLE
    • DBMS_DM_EXP_INTERNAL       PROCEDURE  DUMP_STATUS
    • DBMS_DM_EXP_INTERNAL       PROCEDURE  GET_LIST_MODELS
    • DBMS_DM_EXP_INTERNAL       PROCEDURE  INSERT_LIST_MODELS
    • DBMS_DM_EXP_INTERNAL       PROCEDURE  SET_TRACE
    • DBMS_DM_IMP_INTERNAL       PROCEDURE  REGISTER_TEMP_TABLE
    • DBMS_DM_IMP_INTERNAL       PROCEDURE  UNREGIST_TEMP_TABLE
    • DBMS_DM_MODEL_EXP          FUNCTION   INSTANCE_INFO_EXP RETURNS VARCHAR2
    • DBMS_DM_MODEL_EXP          FUNCTION   SCHEMA_INFO_EXP RETURNS VARCHAR2
    • DBMS_DM_MODEL_EXP          PROCEDURE  SCHEMA_CALLOUT
    • DBMS_DM_UTIL               PROCEDURE  DISPLAY_MSG
    • DBMS_DM_UTIL               PROCEDURE  DUMP_MSG
    • DBMS_DM_UTIL_INTERNAL      PROCEDURE  DUMP_DM_TRACE
    • DBMS_DUMA_INTERNAL         FUNCTION   GET_PROPERTY RETURNS NUMBER
    • DBMS_DUMA_INTERNAL         FUNCTION   READ_LONG_TO_BLOB RETURNS BLOB
    • DBMS_DUMA_INTERNAL         PROCEDURE  CONVERT_LONG
    • DBMS_DUMA_INTERNAL         PROCEDURE  SCAN_TABLE
    • DBMS_DUMA_INTERNAL         PROCEDURE  WRITE_BLOB_TO_LONG
    • DBMS_INTERNAL_LOGSTDBY     PROCEDURE  EDS_EVOLVE_TABLE_1_FINISH
    • DBMS_INTERNAL_LOGSTDBY     PROCEDURE  END_INSTANTIATION
    • DBMS_INTERNAL_LOGSTDBY     PROCEDURE  INSTANTIATE_FEATURE
    • DBMS_INTERNAL_LOGSTDBY     PROCEDURE  LOCK_TABLES
    • DBMS_INTERNAL_LOGSTDBY     PROCEDURE  PREPARE_INSTANTIATION
    • DBMS_INTERNAL_LOGSTDBY     PROCEDURE  REGISTER_SCHEMA
    • DBMS_INTERNAL_LOGSTDBY     PROCEDURE  UNSUPPORTED_DML
    • DBMS_JAVA                  FUNCTION   DEPLOY_INVOKE RETURNS VARCHAR2
    • DBMS_JAVA                  FUNCTION   DROP_JAR RETURNS NUMBER
    • DBMS_JAVA                  FUNCTION   FINISH_LOADING_JAR RETURNS NUMBER
    • DBMS_JAVA                  FUNCTION   GETSOURCECHUNK RETURNS VARCHAR2
    • DBMS_JAVA                  FUNCTION   HANDLEMD5 RETURNS RAW
    • DBMS_JAVA                  FUNCTION   INITGETSOURCECHUNKS RETURNS NUMBER
    • DBMS_JAVA                  FUNCTION   JAR_STATUS RETURNS NUMBER
    • DBMS_JAVA                  FUNCTION   RJBC_INIT RETURNS VARCHAR2
    • DBMS_JAVA                  FUNCTION   RJBC_NORMALIZE RETURNS NUMBER
    • DBMS_JAVA                  FUNCTION   RJBC_REQUEST RETURNS NUMBER
    • DBMS_JAVA                  FUNCTION   RJBC_RESPOND RETURNS NUMBER
    • DBMS_JAVA                  FUNCTION   START_LOADING_JAR RETURNS NUMBER
    • DBMS_JAVA                  PROCEDURE  DEPLOY_COPY
    • DBMS_JAVA                  PROCEDURE  DEPLOY_OPEN
    • DBMS_JAVA                  PROCEDURE  RJBC_DONE
    • DBMS_JAVA                  PROCEDURE  RJBC_OUTPUT
    • DBMS_JDM_INTERNAL          FUNCTION   GET_JDM_ATTR_NAMES RETURNS JDM_ATTR_NAMES
    • DBMS_JDM_INTERNAL          FUNCTION   GET_JDM_NUM_VALS RETURNS JDM_NUM_VALS
    • DBMS_JDM_INTERNAL          FUNCTION   GET_JDM_STR_VALS RETURNS JDM_STR_VALS
    • DBMS_JDM_INTERNAL          FUNCTION   TO_CHAR_VARRAY RETURNS VARCHAR2
    • DBMS_JDM_INTERNAL          FUNCTION   UNIQUE_OBJECT_NAME RETURNS VARCHAR2
    • DBMS_JDM_INTERNAL          PROCEDURE  APPLY_TASK
    • DBMS_JDM_INTERNAL          PROCEDURE  BUILD_TASK
    • DBMS_JDM_INTERNAL          PROCEDURE  EXPLAIN_TASK
    • DBMS_JDM_INTERNAL          PROCEDURE  EXPORT_TASK
    • DBMS_JDM_INTERNAL          PROCEDURE  IMPORT_TASK
    • DBMS_JDM_INTERNAL          PROCEDURE  PREDICT_TASK
    • DBMS_JDM_INTERNAL          PROCEDURE  PROFILE_TASK
    • DBMS_JDM_INTERNAL          PROCEDURE  SQL_APPLY_TASK
    • DBMS_JDM_INTERNAL          PROCEDURE  TEST_TASK
    • DBMS_JDM_INTERNAL          PROCEDURE  XFORM_SEQ_TASK
    • DBMS_JDM_INTERNAL          PROCEDURE  XFORM_TASK
    • DBMS_LOGMNR_INTERNAL       PROCEDURE  ADDXTENDEDPKLOGGROUP
    • DBMS_LOGMNR_INTERNAL       PROCEDURE  DROPXTENDEDPKLOGGROUP
    • DBMS_LOGMNR_INTERNAL       PROCEDURE  KRVGDRM2
    • DBMS_LOGMNR_INTERNAL       PROCEDURE  LOGMNR_KRVILD
    • DBMS_LOGMNR_INTERNAL       PROCEDURE  LOGMNR_KRVIULD
    • DBMS_LOGMNR_INTERNAL       PROCEDURE  SET_PARAMETER
    • DBMS_LOGMNR_INTERNAL       PROCEDURE  UNSET_PARAMETER
    • DBMS_LOGSTDBY              FUNCTION   VALIDATE_AUTH RETURNS BOOLEAN
    • DBMS_LOGSTDBY              PROCEDURE  EDS_EVOLVE_TABLE
    • DBMS_METADATA_INT          PROCEDURE  CLEAR_CACHE
    • DBMS_METADATA_INT          PROCEDURE  CLEAR_XSL_CACHE
    • DBMS_OBJECTS_UTILS         FUNCTION   SPLIT_SOURCE RETURNS NUMBER
    • DBMS_OBJECTS_UTILS         PROCEDURE  RECOMPILE_TYPES
    • DBMS_OBJECTS_UTILS         PROCEDURE  UPDATE_TYPES
    • DBMS_REPORT                FUNCTION   STORE_FILE RETURNS NUMBER
    • DBMS_REPORT                PROCEDURE  CREATE_SHARED_DIRECTORY
    • DBMS_REPORT                PROCEDURE  DROP_SHARED_DIRECTORY
    • DBMS_RMIN                  PROCEDURE  INSTALL
    • DBMS_RMIN                  PROCEDURE  RM$_CALIBRATE_IO
    • DBMS_RMIN                  PROCEDURE  RM$_CLRPAREA
    • DBMS_RMIN                  PROCEDURE  RM$_CONSUMER_GROUP_MAPPING_PRI
    • DBMS_RMIN                  PROCEDURE  RM$_CRTPAREA
    • DBMS_RMIN                  PROCEDURE  RM$_CUP_CATEGORY
    • DBMS_RMIN                  PROCEDURE  RM$_CUP_GROUP
    • DBMS_RMIN                  PROCEDURE  RM$_CUP_PLAN
    • DBMS_RMIN                  PROCEDURE  RM$_CUP_PLAN_DIRECTIVE
    • DBMS_RMIN                  PROCEDURE  RM$_CUP_STORAGE_POOL_MAPPING
    • DBMS_RMIN                  PROCEDURE  RM$_DRP_CATEGORY
    • DBMS_RMIN                  PROCEDURE  RM$_DRP_GROUP
    • DBMS_RMIN                  PROCEDURE  RM$_DRP_PLAN
    • DBMS_RMIN                  PROCEDURE  RM$_DRP_PLAN_CSD
    • DBMS_RMIN                  PROCEDURE  RM$_DRP_PLAN_DIRECTIVE
    • DBMS_RMIN                  PROCEDURE  RM$_SET_CAPABILITY
    • DBMS_RMIN                  PROCEDURE  RM$_SET_CONSUMER_GROUP_MAPPING
    • DBMS_RMIN                  PROCEDURE  RM$_SET_INSTANCE_CAPABILITY
    • DBMS_RMIN                  PROCEDURE  RM$_SUBPAREA
    • DBMS_RMIN                  PROCEDURE  RM$_SWTCH_SESSION_GROUP
    • DBMS_RMIN                  PROCEDURE  RM$_SWTCH_USR_GROUP
    • DBMS_RMIN                  PROCEDURE  RM$_VLDPAREA
    • DBMS_RMIN                  PROCEDURE  UNINSTALL
    • DBMS_RULE_COMPATIBLE_90    FUNCTION   INCOMPATIBLE_RULES_ENGINE_OBJ RETURNS NUMBER
    • DBMS_RULE_COMPATIBLE_90    PROCEDURE  DOWNGRADE_RULE_OBJECTS
    • DBMS_RULE_COMPATIBLE_90    PROCEDURE  INCOMPATIBLE_EVCTXS
    • DBMS_RULE_COMPATIBLE_90    PROCEDURE  INCOMPATIBLE_RULES
    • DBMS_RULE_COMPATIBLE_90    PROCEDURE  INCOMPATIBLE_RULESETS
    • DBMS_RULE_COMPATIBLE_90    PROCEDURE  UPGRADE_RULE_OBJECTS
    • DBMS_SPM_INTERNAL          FUNCTION   FETCH_PLAN_INFO RETURNS REF CURSOR
    • DBMS_SPM_INTERNAL          PROCEDURE  GET_SPM_HINTSET
    • DBMS_SQL2                  PROCEDURE  EXPAND_SQL_TEXT
    • DBMS_SQL2                  PROCEDURE  PARSE
    • DBMS_STATS                 FUNCTION   TO_CASCADE_TYPE RETURNS BOOLEAN
    • DBMS_STATS                 FUNCTION   TO_DEGREE_TYPE RETURNS NUMBER
    • DBMS_STATS                 FUNCTION   TO_ESTIMATE_PERCENT_TYPE RETURNS NUMBER
    • DBMS_STATS                 FUNCTION   TO_PUBLISH_TYPE RETURNS BOOLEAN
    • DBMS_STATS_INTERNAL        FUNCTION   COMPOSE_HASHVAL_CLOB RETURNS CLOBTAB
    • DBMS_STATS_INTERNAL        FUNCTION   GET_CHTAB RETURNS CHTAB
    • DBMS_STATS_INTERNAL        FUNCTION   GET_SYNOPSIS_BLKCNT RETURNS NUMBER(38)
    • DBMS_STATS_INTERNAL        PROCEDURE  DELETE_TABLE_SYNOPSIS
    • DBMS_STATS_INTERNAL        PROCEDURE  EXPORT_SYNOPSIS_HEAD
    • DBMS_SWRF_REPORT_INTERNAL  FUNCTION   TO_1000S RETURNS VARCHAR2
    • DBMS_SWRF_REPORT_INTERNAL  FUNCTION   TO_1024S RETURNS VARCHAR2
    • DBMS_WORKLOAD_REPLAY       FUNCTION   SYNCPOINT_WAIT_TO_POST RETURNS NUMBER
    • DBMS_WRR_INTERNAL          FUNCTION   DATE_TO_EPOCH_SECS RETURNS BINARY_INTEGER
    • DBMS_WRR_INTERNAL          FUNCTION   IS_CONSOLIDATED_DIR RETURNS BOOLEAN
    • DBMS_XDS                   PROCEDURE  REFRESH_DSD
    • DBMS_XDSUTL                PROCEDURE  INVALIDATE_DSD_CACHE
    • DBMS_XDSUTL                PROCEDURE  INVALIDATE_DSD_CACHE_BY_ACLID
    • DBMS_XS_SECCLASS_INT       PROCEDURE  DELETE_SEC_CLASS
    • DBMS_XS_SECCLASS_INT       PROCEDURE  GET_LEAF_PRIVILEGES
    • DBMS_XS_SECCLASS_INT_FFI   PROCEDURE  DELETE_SEC_CLASS
    • DBMS_XS_SECCLASS_INT_FFI   PROCEDURE  GET_LEAF_PRIVILEGES
    • DBMS_XS_SESSIONS           FUNCTION   CHECK_ROLE RETURNS BOOLEAN
    • DBMS_XS_SESSIONS           FUNCTION   CHECK_SESSION RETURNS BOOLEAN
    • DBMS_XS_SESSIONS           FUNCTION   GET_SESSIONID_FROM_COOKIE RETURNS RAW
    • DBMS_XS_SESSIONS           PROCEDURE  ABORTATTACH_SESSION
    • DBMS_XS_SESSIONS           PROCEDURE  GET_SESSION_COOKIE
    • DBMS_XS_SESSIONS           PROCEDURE  SET_NAMESPACE_ACL
    • DBMS_XS_SESSIONS           PROCEDURE  SET_NAMESPACE_HANDLER
    • DBMS_XS_SESSIONS_FFI       PROCEDURE  ABORTATTACH_SESSION
    • DBMS_XS_SESSIONS_FFI       PROCEDURE  CHECK_ROLE
    • DBMS_XS_SESSIONS_FFI       PROCEDURE  CHECK_SESSION
    • DBMS_XS_SESSIONS_FFI       PROCEDURE  GET_SESSION_COOKIE
    • DBMS_XS_SESSIONS_FFI       PROCEDURE  SET_NAMESPACE_ACL
    • DBMS_XS_SESSIONS_FFI       PROCEDURE  SET_NAMESPACE_HANDLER
    • DMP_SEC                    FUNCTION   GET_MODEL_DETAILS_ABN RETURNS VARCHAR2(32767)
    • DMP_SYS                    PROCEDURE  DUMP_MODEL_REC
    • DMP_SYS                    PROCEDURE  DUMP_TRACE
    • DMP_SYS                    PROCEDURE  DUMP_VARCHAR2A
    • DMP_SYS                    PROCEDURE  DUMP_VARCHAR2S
    • DM_QGEN                    PROCEDURE  LS_DUMP
    • EXF$DBMS_EXPFIL_SYSPACK    FUNCTION   GET_CMTSCN_FOR_XID RETURNS NUMBER
    • EXF$DBMS_EXPFIL_SYSPACK    FUNCTION   GET_EXPRID_4M_TEXTID RETURNS VARCHAR2
    • EXF$DBMS_EXPFIL_SYSPACK    FUNCTION   GET_SPATIAL_MDATAREP RETURNS VARCHAR2
    • EXF$DBMS_EXPFIL_SYSPACK    FUNCTION   GET_TAB_NAME RETURNS VARCHAR2
    • EXF$DBMS_EXPFIL_SYSPACK    FUNCTION   GET_TAB_OWNER RETURNS VARCHAR2
    • EXF$DBMS_EXPFIL_SYSPACK    FUNCTION   GET_TYPELV_STATUS RETURNS VARCHAR2
    • EXF$DBMS_EXPFIL_SYSPACK    FUNCTION   GET_TYPE_OID RETURNS RAW
    • EXF$DBMS_EXPFIL_SYSPACK    FUNCTION   PROC_IS_DEFINERS RETURNS NUMBER
    • EXF$DBMS_EXPFIL_SYSPACK    FUNCTION   TAB_COLUMN_EXISTS RETURNS NUMBER
    • EXF$DBMS_EXPFIL_SYSPACK    PROCEDURE  CLR_EXP_DEPACTION
    • EXF$DBMS_EXPFIL_SYSPACK    PROCEDURE  CLR_OBJECT_NOEXP
    • EXF$DBMS_EXPFIL_SYSPACK    PROCEDURE  CLR_USER_NOEXP
    • EXF$DBMS_EXPFIL_SYSPACK    PROCEDURE  DOWNGRADE_COMPILED_SPARSE
    • EXF$DBMS_EXPFIL_SYSPACK    PROCEDURE  PARSE_PROC_AS_OWNER
    • EXF$DBMS_EXPFIL_SYSPACK    PROCEDURE  PARSE_PROC_AS_OWNER2
    • EXF$DBMS_EXPFIL_SYSPACK    PROCEDURE  SET_EXP_DEPACTION
    • EXF$DBMS_EXPFIL_SYSPACK    PROCEDURE  SET_OBJECT_NOEXP
    • HTMLDB_SYSTEM              FUNCTION   VERIFY_USER RETURNS BINARY_INTEGER
    • INITJVMAUX                 FUNCTION   DO_JIS_DROP RETURNS BOOLEAN
    • KUPD$DATA                  FUNCTION   CONVENTIONAL_LOAD_CALLOUT RETURNS BINARY_INTEGER
    • KUPF$FILE_INT              FUNCTION   GET_ENCPWD RETURNS RAW
    • KUPF$FILE_INT              FUNCTION   GET_OBFPWD RETURNS RAW
    • KUPF$FILE_INT              PROCEDURE  ENCRYPT_PWD
    • KUPP$PROC                  PROCEDURE  INTALGCONV
    • LOGMNR_DICT_CACHE          PROCEDURE  PURGE_SCN
    • LOGSTDBY_INTERNAL          PROCEDURE  EDS_EVOLVE_TABLE_1_I
    • ODM_ABN_MODEL              PROCEDURE  BUILD
    • ODM_ABN_MODEL              PROCEDURE  ESTIMATE_FEATUREBLD_TIME
    • ODM_ABN_MODEL              PROCEDURE  GET_MODEL_STATE
    • ODM_CLUSTERING_UTIL        FUNCTION   IS_DEBUG_ENABLED RETURNS BOOLEAN
    • ODM_MODEL_UTIL             FUNCTION   UNIQUE_SEQUENCE_NAME RETURNS VARCHAR2(30)
    • ODM_MODEL_UTIL             PROCEDURE  CREATE_SEQUENCE
    • ODM_MODEL_UTIL             PROCEDURE  DEBUG_DUMP
    • ODM_MODEL_UTIL             PROCEDURE  DROP_SEQUENCE
    • ODM_MODEL_UTIL             PROCEDURE  GET_TIME_MILLISECONDS
    • ODM_UTIL                   FUNCTION   COMPUTE_LOG_COMBINATIONS RETURNS NUMBER
    • PRVT_RTADDM                FUNCTION   EXTRACTOSSTAT RETURNS NUMBER
    • PRVT_RTADDM                FUNCTION   GETDATA RETURNS CLOB_T
    • PRVT_RTADDM                FUNCTION   GETVERSION RETURNS VARCHAR2
    • SQLJUTL2                   FUNCTION   EVALUATE RETURNS LONG RAW
    • SQLJUTL2                   FUNCTION   INVOKE RETURNS LONG RAW
    • SQLJUTL2                   FUNCTION   REFLECT RETURNS LONG
    • SQLJUTL2                   FUNCTION   REFLECT2 RETURNS CLOB
    • STANDARD                   FUNCTION   ASCII RETURNS BINARY_INTEGER

    204 procedure and function calls have been changed.

    -- Mike

    PS: The above listening is without PSUs applied - I learned that the list my vary slightly based on the PSUs or BPs you have applied. Credits to my colleague Katsumi-san, who pointed my to the DBMS_OPTIM_BUNDLE package which seems to be introduced with the October 2014 PSU. Philippe Fierens has blogged about the package: http://pfierens.blogspot.co.uk/2014_10_01_archive.html

    Monday Jun 22, 2015

    Java in the database - OJVM non-rolling patches

    Question:

    How can I find out if Oracle's JVM is used in my database?

    Answer:

    This is unfortunately not as trivial as I thought initially ...
    Let's start with:



    Until Oracle version 11.2 or later, there was no way to confirm if Oracle JVM is not actively used in the database

    However, what can be said is:
    1) If there are non-Oracle schemas that contain java objects, then 3rd party products or user defined java programs could be actively using the Oracle JVM.
    2) If there are Oracle schemas, other than SYS, that contain java objects, then other Oracle products or Oracle Applications could be actively using the Oracle JVM.  (For example, ORDSYS schema for Oracle Intermedia and APPS schema for Oracle Applications).
    3) Even if all java objects are owned by SYS schema, there might still be user defined java objects in the SYS schema. 

    If the total number of java objects owned by SYS is much greater than the totals shown above, then this is likely.  However, the totals shown above are for a fully installed Oracle JVM.  If the JVM is not fully installed, then the existence of user defined java objects in the SYS schema could still make the total number of java objects exceed the above totals. Therefore, there is no way to guarantee that the Oracle JVM is not in use.

    For Oracle version 11.2 or later query the DBA_FEATURE_USAGE_STATISTICS view to confirm if the Java features are being used.

    I'm not a JAVA/OJVM expert but I'd do the following:

    1. Check how many JAVA objects exist in your database:
      select owner, status, count(*) from all_objects 
              where object_type like '%JAVA%' group by owner, status;
      .
    2. lf the results is equal to 29211 in Oracle 12c (see MOS Note: 397770.1 for numbers in different releases) then I'd silently assume that JAVA is not in use inside the database as there are no additional user defined objects. 
      .
    3. In addition you may run this script from MOS Note:456949.1 (Script to Check the Status or State of the JVM within the Database) to check for any user defined objects JAVA objects in your database
      .
    4. Anyhow, before doing anything to your JAVA installation now keep in mind that there are dependencies. The following components require the existence of a valid JAVA installation in your database:
      Oracle Multimedia (formerly known as Intermedia)
      Oracle Spatial
      Oracle OLAP
      And even more important, as there are dependencies between components there may be also dependent objects belonging to these components in your database. So it's not as simple as it looked initially - you'll have to check if any of the dependent components is in use as well - and the numbers 4.-6. will apply to 11.2. databases only, not to Oracle 12c:
      1. How to Determine if Spatial is Being Used in the Database? (Doc ID 726929.1)
        Please be aware that having a user defined SPATIAL SDO Geometry object will NOT increase the number of existing Java objects compared to a default installation. Roy verified this (THANKS!). So you'll have to make sure that you checked also the dependent components for being in use. 
      2. How To Find Out If OLAP Is Being Used (Doc ID 739032.1)
      3. How To Check If Oracle Multimedia Is Being Used In Oracle Version 11.2 (Doc ID 1088032.1)
      4. How to Determine if Ultra Search is Being Used? (Doc ID 738126.1)
      5. Warehouse builder has a note about how to uninstall it, but that (very badly written) note does not tell you how to determine whether OWB is in use
      6. Rules Manager and Expression Filter document installation and deinstallation in their developers guide
      7. .
    5. And even more important, before doing anything to your JAVA installation please take a backup - even though you may believe that backups are just for wimpies you'll better take one before :-)
      .
    6. Now the question is:
      Should you remove only JAVAVM component - or CATJAVA as well? Please see the section further below on this blog posting for more information.
      To remove only JAVAVM this script could do the job - but it will leave two INVALID Package Bodies (JAVAVM_SYS, JVMRJBCINV):
      SQL> @?/xdk/admin/rmxml.sql 
      SQL> delete from registry$ where status='99' and cid = 'JAVAVM';
      SQL> commit;

    7. The execution of the removal scripts won't de-register the component from DBA_REGISTRY - that's why the manual de-registration is necessary. 
      .
    8. Even if I'd remove the entire JAVA stack including the XDK it will leave those two invalid objects  (JAVAVM_SYS, JVMRJBCINV).
      1. SQL> @?/rdbms/admin/catnojav.sql
      2. SQL> @?/xdk/admin/rmxml.sql
      3. SQL> @?/javavm/install/rmjvm.sql
      4. SQL> @?/rdbms/admin/utlrp.sql
      5. SQL> delete from registry$ where status='99' and cid in ('XML','JAVAVM','CATJAVA');
    So honestly the best choice is always not to install things you clearly don't need instead of trying to remove those things afterwards. In this case would now need to double check with Oracle Support if we'd safely can drop the two remaining  package bodies JAVAVM_SYand JVMRJBCINV. In my environment that worked well - but obviously I can't give any official statement here.

    Again, please don't get me wrong:
    I don't say that you should remove JAVA from your databases And please check back with Oracle Support before doing this. But the question came up so often in the past months because of the OJVM patch which does not allow a rolling PSU upgrade anymore. For further information please see the following MOS Note: 

    • Document 1929745.1 Oracle Recommended Patches -- "Oracle JavaVM Component Database PSU" (OJVM PSU) Patches

    So let's do a quick experiment.

    Check the installed components in a standard Oracle 12.1.0.2 database first:

    SQL> select substr(comp_id,1,8) COMP_ID, substr(COMP_NAME,1,36) COMP_NAME from dba_registry;

    COMP_ID    COMP_NAME
    ---------- ------------------------------------
    DV         Oracle Database Vault
    APEX       Oracle Application Express
    OLS        Oracle Label Security
    SDO        Spatial
    ORDIM      Oracle Multimedia
    CONTEXT    Oracle Text
    OWM        Oracle Workspace Manager
    XDB        Oracle XML Database
    CATALOG    Oracle Database Catalog Views
    CATPROC    Oracle Database Packages and Types
    JAVAVM     JServer JAVA Virtual Machine
    XML        Oracle XDK
    CATJAVA    Oracle Database Java Packages
    APS        OLAP Analytic Workspace
    XOQ        Oracle OLAP API
    RAC        Oracle Real Application Clusters

    Before we'd be able to safely remove JAVAVM we will need to take out Spatial, Multimedia and OLAP (exactly in this order) as well.

    Spatial removal: 

    SQL> drop user MDSYS cascade;
    SQL> drop user MDDATA cascade;
    SQL> drop user spatial_csw_admin_usr cascade;
    SQL> drop user spatial_wfs_admin_usr cascade;

    After this action you'll end up with 5 invalid objects in APEX in case APEX is installed. I think you can safely ignore them as those are spatial objects in the FLOWS-APEX schema:

    PACKAGE: WWV_FLOW_SPATIAL_INT, WWV_FLOW_SPATIAL_API
    PACKAGE BODY: WWV_FLOW_SPATIAL_INT, WWV_FLOW_SPATIAL_API
    SYNONYM: APEX_SPATIAL

    Multimendia removal:

    SQL> @?/rdbms/admin/catcmprm.sql ORDIM

    OLAP removal:

    SQL> @?/olap/admin/olapidrp.plb
    SQL> @?/olap/admin/catnoxoq.sql
    SQL> @?/olap/admin/catnoaps.sql
    SQL> @?/rdbms/admin/utlrp.sql

    Let's do the check again:

    SQL> select substr(comp_id,1,8) COMP_ID, substr(COMP_NAME,1,36) COMP_NAME from dba_registry;

    COMP_ID    COMP_NAME
    ---------- ------------------------------------
    DV         Oracle Database Vault
    APEX       Oracle Application Express
    OLS        Oracle Label Security
    CONTEXT    Oracle Text
    OWM        Oracle Workspace Manager
    XDB        Oracle XML Database
    CATALOG    Oracle Database Catalog Views
    CATPROC    Oracle Database Packages and Types
    JAVAVM     JServer JAVA Virtual Machine
    XML        Oracle XDK
    CATJAVA    Oracle Database Java Packages
    RAC        Oracle Real Application Clusters 

    12 components still there - SDO, ORDIM, XQO and APS are gone as expected.

    JAVAVM removal: 

    Question would be now to remove only the JAVAVM - or CATJAVA as well?
    As of MOS Note:397770.1 it seems to be that removing the JAVAVM is (a) trivial and (b) will avoid to apply the OJVM patch. So removing JAVAVM only seems to be the best way in this case. As shown above this will lead to two additional leftover package bodies JAVAVM_SYS and JVMRJBCINV

    4) Oracle JVM is not installed in the database

    Do not apply the DST JVM patch.

    If for some reason the patch is applied, then apply the patch to the ORACLE_HOME but DO NOT run the post install steps in the database.  This will leave unwanted java objects in the database and create an incomplete non-working Oracle JVM.  See Note 414248.1 for details.

    SQL> @?/xdk/admin/rmxml.sql 
    SQL> delete from registry$ where status='99' and cid = 'JAVAVM';
    SQL> commit;

    -- Mike 

    Wednesday Jun 17, 2015

    Webcast "Why Upgrade to Oracle 12c" available

    In April I've done a webcast mainly for Oracle partners to explain and elaborate
    Why Upgrade to Oracle Database 12c?

    This webcast is now available without registration etc. Watch it here (click on the picture below):

    Webcast Why Upgrade To Oracle Database 12c - Mike Dietrich, April 2015

    --Mike


    Wednesday May 27, 2015

    Removing Options from the Oracle Database kernel in 12c

    Remove Options from the Oracle Database Kernel - chopt

    Sometimes people have the desire to remove options from the database kernel (i.e. from the oracle executable).

    It's a matter of fact that by default you'll get plenty of things linked into your kernel in Oracle Database 12c.

    In case you'd like to remove things the chopt utility does still exist in Oracle Database 12c - but you may recognize a difference between Oracle 11.2 and Oracle 12.1. Anyhow, ideally you'll do these changes before you create a database directly after the installation has been completed. See the documentation for Post Installation Tasks first:

    Now let's call chopt and see what it tells us on the command prompt:

    $ chopt

    usage:
    chopt <enable|disable> <option>

    options:
                      dm = Oracle Data Mining RDBMS Files
                    olap = Oracle OLAP
            partitioning = Oracle Partitioning
                     rat = Oracle Real Application Testing
    e.g. chopt enable rat 

    For a first try I'm unlinking Data Mining:

    $ chopt disable dm

    Writing to /u01/app/oracle/product/12.1.0.2/install/disable_dm.log...
    /usr/bin/make -f /u01/app/oracle/product/12.1.0.2/rdbms/lib/ins_rdbms.mk dm_off ORACLE_HOME=/u01/app/oracle/product/12.1.0.2
    /usr/bin/make -f /u01/app/oracle/product/12.1.0.2/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/12.1.0.2

    I tested all the 4 available chopt options and this is the result when you exit SQL*Plus afterwards:

    SQL> exit
    Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

    versus before with all options still linked into the kernel:

    SQL> exit
    Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

    But how about all the other options being available in releases before Oracle Database 12c, such as: lbac_on|off (Label Security), dv_on|off (Database Vault)? If you'd refer to the list of options to link on/off published by Ghokan Atil years back in his blog you may find more things to try out.

    Lets give it a try with Label Security:

    $ /usr/bin/make -f /u01/app/oracle/product/12.1.0.2/rdbms/lib/ins_rdbms.mk lbac_off ORACLE_HOME=/u01/app/oracle/product/12.1.0.2
    lbac_off has been deprecated

    Ah, very smart ;-) It signals that you can't link those things off anymore. The same would happen with dv_off.

    And how about things which are from older sources, such as Spatial Data (sdo_on|off)?

    $ /usr/bin/make -f /u01/app/oracle/product/12.1.0.2/rdbms/lib/ins_rdbms.mk sdo_off ORACLE_HOME=/u01/app/oracle/product/12.1.0.2
    Warning: sdo is always turned on. sdo_off is disabled.

    In this case it would be simple: You'd take out SDO from the components inside the database - and there's no need to unlink anything.

    --Mike 

    About

    Mike Dietrich - Oracle Mike Dietrich
    Master Product Manager - Database Upgrade & Migrations - Oracle Corp

    Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite or remotely with reference customers. Acting as interlink between customers/partners and the Upgrade Development.

    Follow me on TWITTER

    Contact me via LinkedIn or XING

    Search

    Archives
    « September 2015
    SunMonTueWedThuFriSat
      4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
       
           
    Today
    Oracle related Tech Blogs
    Slides Download Center
    Visitors since 17-OCT-2011
    White Paper and Docs
    Workshops
    Viewlets and Videos
    Workshop Map
    This week on my Rega & Pono
    Upgrade Reference Papers