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 

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 

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 

Monday May 18, 2015

Create a PDB directly from a stand-alone database?

The documentation offers a well hidden feature for migrating a database into the universe of Oracle Single-/Multitenant:

Remote Cloning with the NON$CDB option.

If you'll read the documentation it doesn't say much about this option, neither the requirements nor the exact syntax or an example:
http://docs.oracle.com/database/121/SQLRF/statements_6010.htm#SQLRF55686

Scroll down to the FROM clause:

... FROM NON$CDB@dblink ... this option will be able to plugin a stand-alone database and make it a pluggable database. Sounds interesting, let's try it.

Test 1 - Try to plugin an Oracle 11.2.0.4 database

Well, the documentation doesn't say anywhere anything about source release limitans. So I tried it simply with an Oracle 11.2.0.4 database. 

  1. Created a database link from my existing CDB pointing into my 11.2.0.4 database
  2. Started my SOURCEDB in read-only mode
  3. Tried to create a pluggable database from my SOURCEDB - and failed ...
    SQL> create pluggable database PDB1 from non$cdb@sourcedb;
    create pluggable database PDB1 from non$cdb@sourcedb
                                                 *
    ERROR at line 1:
    ORA-17627: ORA-28002: the password will expire within 7 days
    ORA-17629: Cannot connect to the remote database server

Test 2 - Try to plugin an Oracle 12.1.0.2 database in file system 

Thanks to Tim Hall - his blog post did the magic trick for me:
http://oracle-base.com/articles/12c/multitenant-clone-remote-pdb-or-non-cdb-12cr1.php#cloning-remote-non-cdb

First of all, the reason why my Test 1 failed is simply that I can't have a user in an Oracle 11.2.0.4 database with the privilege CREATE PLUGGABLE DATABASE - but this is a requirement as I learned later on.

  1. You'll need a user in SOURCEDB with the privilege to CREATE PLUGGABLE DATABSE:
    GRANT CREATE PLUGGABLE DATABASE TO sourcedb_user;
  2. Start SOURCEDB in read-only mode after shutting it down:
    SHUTDOWN IMMEDIATE
    STARTUP OPEN READ ONLY;
  3. Create a database link pointing from the CDB back into the SOURCEDB:
    CREATE DATABASE LINK sourcedblink
    CONNECT TO sourcedb_user IDENTIFIED BY password USING 'upgr12';
  4. Now create the pluggable database from the stand-alone UPGR12 database:
    CREATE PLUGGABLE DATABASE pdb_upgr12 FROM NON$CDB@sourcedblink
    FILE_NAME_CONVERT=('/oradata/UPGR12','/oradata/CDB2/pdb_upgr12');
  5. But when you check the status of the new PDB you'll realize it is OPEN but only in RESTRICTED mode. Therefore noncdb_to_pdb,sql needs to be run. Connect to the new PDB and start the script:
    ALTER SESSION SET CONTAINER=pdb_upgr12;
    @?/rdbms/admin/noncdb_to_pdb.sql

Summary

What will you get from this command? Actually it will allow a simple way to plug in a stand-alone database into a container database but the following restrictions apply:

  • Source database must be at least Oracle 12.1.0.1
  • Source database must be on the same OS platform
  • Source database must be at the same (equal) version as the container database
  • Script noncdb_to_pdb.sql needs to be run

You may have a look at this MOS Note:1928653.1 Example for Cloning PDB from NON-CDB via Dblink as well [Thanks Krishnakumar for pointing me to this note].

Finally the only simplification seems to be to avoid the extra step of creating the XML manifest file with DBMS_PDB.DESCRIBE - but apart from that I can't see many other benefits - except for easing of remote cloning with the above restrictions.

--Mike 

,, 

Monday May 11, 2015

New version of the BIG 12c SLIDE DECK available

I'm not on vacation right now. I'm just very busy traveling between customer onsite visits, customer meetings and workshops, internal and external workshops, testing sessions ... and so on ... 

A lot of stuff has been stacked up in my inbox regarding changes in Oracle 12c, changed behavior - and thanks for your inputs. I will blog on it as soon as I have understood the issue and the solution - so plenty of stuff should come in the next weeks ;-)

Just end of last week Roy and I uploaded a new version of our BIG slide deck - now having even more slides (but still not over 550) *haha* ;-)

Find them here - and as always - the change log at the end of the deck will explain what we've recently adjusted/added/altered.

Upgrade, Migrate and Consolidate to Oracle Database 12c

--Mike 

Thursday Apr 23, 2015

CDBs with less options now supported in Oracle 12.1.0.2

When Oracle Multitenant was launched Roy and I amongst many other people always mentioned that the requirement of having all options in a Container Database (CDB$ROOT), and therefore also for the PDB$SEED with the immediate result that all PDBs provisioned from the PDB$SEED will have all options as well, will hinder customer adoption significantly. 

Almost all customers I have talked to in the past 3-4 years about Oracle Multitenant mentioned immediately that it will be a huge problem for them to install all options as (1) their policy is to install only things they are licensed for to (2) prevent developers, users and DBAs to use things accidentally without even knowing that this or that will require a license.

As it is not allowed to manipulate and change the PDB$SEED the workaround - as PDBs were allowed to have less options - has been to create a stand-alone Oracle 12c database with exactly the options you'd like to have configured as your gold standard - and then plug it in under a remarkable name, for instance PDB$MASTER. Switch it to read only and make sure from now on you'll provision a new PDB always as a clone from PDB$MASTER, and not from PDB$SEED.

All Options in a CDB

That would have even worked in the Single Tenant case, which does not require licensing the Oracle Multitenant option and where you have only one active PDB. For this purpose you would have unplugged your PDB$MASTER after making it a pluggable database and provision new PDBs with just your desired options set as plugging in PDB$MASTER under a new name (e.g. PDB26) using the COPY option of the command.

Now this will become all obsolete as from now you it is allowed to have a CDB installation with less options. This applies to linked kernel modules (e.g. RAT) as well as to configured database components (e.g. JAVA, OWM, SPATIAL etc).

Please see the following new/rephrased MOS Notes:

MOS Note:2001512.1 basically explains the following steps:

  • Do all the click work in DBCA (Database Creation Assistant) to create a container database - but let DBCA only create the scripts
  • Edit the <SID>.sql script and remove the unwanted options according to the dependency table in the MOS Note
  • Edit the CreateDBCatalog.sql in case you want to remove OWM (Oracle Workspace Manager) creation as well 
  • Add the Oracle PERL $ORACLE_HOME/perl/bin in front of your $PATH variable
  • Start the <SID>.sh script on the shell prompt

Here's an example of a CreateDBCatalog.sql and a XXXX.sql creating a CDB with no options except XDB (which is mandatory in Oracle Database 12c):

cat CreateDBCatalog.sql

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/XXXX/scripts/CreateDBCatalog.log append
alter session set "_oracle_script"=true;
alter pluggable database pdb$seed close;
alter pluggable database pdb$seed open;
host perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/XXXX/scripts -b catalog /u01/app/oracle/product/12.1.0.2/rdbms/admin/catalog.sql;
host perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/XXXX/scripts -b catproc /u01/app/oracle/product/12.1.0.2/rdbms/admin/catproc.sql;
host perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/XXXX/scripts -b catoctk /u01/app/oracle/product/12.1.0.2/rdbms/admin/catoctk.sql;
-- host perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/XXXX/scripts -b owminst /u01/app/oracle/product/12.1.0.2/rdbms/admin/owminst.plb;
host perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/XXXX/scripts -b pupbld -u SYSTEM/&&systemPassword /u01/app/oracle/product/12.1.0.2/sqlplus/admin/pupbld.sql;
connect "SYSTEM"/"&&systemPassword"
set echo on
spool /u01/app/oracle/admin/XXXX/scripts/sqlPlusHelp.log append
host perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/XXXX/scripts -b hlpbld -u SYSTEM/&&systemPassword -a 1  /u01/app/oracle/product/12.1.0.2/sqlplus/admin/help/hlpbld.sql 1helpus.sql;
spool off
spool off

.

cat XXXX.sql

set verify off
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
host /u01/app/oracle/product/12.1.0.2/bin/orapwd file=/u01/app/oracle/product/12.1.0.2/dbs/orapwXXXX force=y format=12
@/u01/app/oracle/admin/XXXX/scripts/CreateDB.sql
@/u01/app/oracle/admin/XXXX/scripts/CreateDBFiles.sql
@/u01/app/oracle/admin/XXXX/scripts/CreateDBCatalog.sql
-- @/u01/app/oracle/admin/XXXX/scripts/JServer.sql
-- @/u01/app/oracle/admin/XXXX/scripts/context.sql
-- @/u01/app/oracle/admin/XXXX/scripts/ordinst.sql
-- @/u01/app/oracle/admin/XXXX/scripts/interMedia.sql
-- @/u01/app/oracle/admin/XXXX/scripts/cwmlite.sql
-- @/u01/app/oracle/admin/XXXX/scripts/spatial.sql
-- @/u01/app/oracle/admin/XXXX/scripts/labelSecurity.sql
-- @/u01/app/oracle/admin/XXXX/scripts/apex.sql
-- @/u01/app/oracle/admin/XXXX/scripts/datavault.sql
-- @/u01/app/oracle/admin/XXXX/scripts/CreateClustDBViews.sql

@/u01/app/oracle/admin/XXXX/scripts/lockAccount.sql
@/u01/app/oracle/admin/XXXX/scripts/postDBCreation.sql
@/u01/app/oracle/admin/XXXX/scripts/PDBCreation.sql
@/u01/app/oracle/admin/XXXX/scripts/plug_PDB.sql
@/u01/app/oracle/admin/XXXX/scripts/postPDBCreation_PDB.sql

 .

This results in a database having only these components - the minimal component set in Oracle 12.1.0.2: 

COMP ID  NAME
-------- --------------------------------------

CATALOG  Oracle Database Catalog View

CATPROC  Oracle Database Packages and

XDB      Oracle XML Database

   .

-- Mike 

Tuesday Apr 21, 2015

REPLAY and Slides for Webcast "Why Upgrade to Oracle Database 12c" for ISVs/Partners - Apr 2015

Thanks for attending today's webcast about
"Upgrading to Oracle Database 12c - and why you should upgrade".

Webcast Why Upgrade 12c

Please access the slides via this link:

Webcast Slides - Why Upgrade to Oracle Database 12c

And for those who couldn't participate here's the replay of the 45 minute Webcast:

REPLAY Webcast "Why Upgrade to Oracle 12c"

Thanks :-)

 --Mike 


Thursday Apr 16, 2015

Webcast for ISVs/Partners on Apr 21, 2015, 3pm CET
Why Upgrade to Oracle Database 12c?

Oracle's latest generation of database technology, Oracle Database 12.1.0.2, has some impressive new features. It offers great potential for a fast upgrade, simple migrations and consolidation, making more efficient use of hardware and delivering major improvements in management efficiency.


Join our webcast on Upgrading to Oracle Database 12c for ISVs and you will learn:

  • Why you need to upgrade to Oracle Database 12.1.0.2
  • How to ensure that your applications are ready for Oracle Database 12c
  • How to evaluate and test all enhancements of the upgrade process plus other new features
  • Best practices to upgrade and migrate successfully

At the end of the webcast our speaker Mike Dietrich, Master Product Manager for Oracle's Database Upgrade Development Team, will be available for your questions. The presentation will be for 45 minutes followed by the Q&A session.

Registration Link:
https://event.on24.com/eventRegistration/EventLobbyServlet?target=reg20.jsp&eventid=972198&sessionid=1&key=732CC73F7BED83BF66054033AF7685C3&sourcepage=register

Date/Time:
Tuesday, April 21, 2015 at 3pm CET 

--Mike 

Wednesday Apr 01, 2015

Upgrade Workshop incl Hands-On in Vienna/Austria on April 9./10. - still some seats available :-)

Just in case you'll would like to participate in an Upgrade and Migration Workshop including Hands-On in the area in and around Vienna, Austria :-)

I'll deliver an Upgrade workshop in the Oracle Office (IZD-Tower, 3. Stock - Wagramer Straße 19 - 1223 Wien) next week on April 9 and 10. It will include Hands-On so you'll have to bring your laptop with Oracle Virtual Box installed and roughly 40 GB of free space to copy an image into your environment.

Just in case you'd like to participate ... here's the registration link which has more details about the agenda and the requirements:

 --Mike

SAP is now certified on Oracle Database 12.1.0.2

ORACLE and SAPSAP certified Oracle Database 12.1.0.2 as of March 31, 2015!

Yes, it's true. Our colleagues in the Oracle/SAP/CompetenceCenter in Walldorf worked really hard to complete the certification for SAP with Oracle Database 12.1.0.2 But finally it happened. Actually it was announced long time before as planned. And it happened right in time.

Please find the official documents here:

SAP Service Marketplace ==> Products ==> Installation & Upgrade Guides ==> Database Upgrades (login required) ==> Oracle 

And here's the link to the official announcement:

 

Great job by a excellent Oracle team in Walldorf - and please no excuses anymore from anybody saying that you can't certify your application on Oracle Database 12.1.0.2. If SAP can do it, everybody can do it :-)

If you are looking for more information and useful documents please see this discussion here - scroll down a bit (thanks to Upgrade Expert Andreas Becker from the Oracle-SAP-CC in Walldorf):

See also the ROADMAP for Oracle/SAP regarding future developments, feature support, In-Memory etc:

--Mike

.

PS:
I'm expressing my sincere condolences as one of the 5 founders of SAP, Klaus Tschira, died on March 31, 2015 way too early at the age of 74 years. May he rest in piece!

Friday Mar 13, 2015

EM Cloud Control 12.1.0.4 (or newer) OMS Repository certified with Oracle Database 12.1.0.2 plus patches

The certification for Oracle Database 12.1.0.2 as Repository Database underneath of Oracle Enterprise Manager Cloud Control 12.1.0.4 (OMS) had been revoked for a while. One reason may have been the potential removal of the SYSMAN user during a DBUA upgrade ;-) But this is just a rumor. I don't know the exact reasons.

After checking with the certification matrix earlier today I realized:

Cloud Control Repository Database Certification Oracle 12.1.0.2

Oracle Database 12.1.0.2 is certified as a repository database for Cloud Control resp. OMS.

But ... with a few important restrictions (see MOS Note:1987905.1 - 12c Database has been Certified as a 12cR4 Repository with Certain Patchset Restrictions):

  • Database PSU April 2015 (12.1.0.2.3)

    OR:

  • Database PSU October 2014 (12.1.0.2.1) or newer
  • Database Patch 20243268 on top of the PSU
    • Bug20243268:
      EM QUERY WITH SQL_ID 4RQ83FNXTF39U PERFORMS POORLY ON ORACLE 12C RELATIVE TO 11G

Please be aware that the above mentioned MOS Note has a typo - it refers to PSU 12.1.0.2.3 (correct) as the Oct PSU (incorrect - it's the April 2015 one). 

--Mike 

PS: Thanks for the anonymous comment ... I wasn't aware of the MOS Note and updated the patch requirements

Monday Mar 09, 2015

Applying a PSU or BP to a Single-/Multitenant Environment

I have already explained in broad details a while ago how to:

But one may miss the steps for applying a PSU (Patch Set Update) or BP (Bundled Patch) to a Single-/Multitenant Environment. At first everything will work just the same if you choose the Everything-at-Once strategy as datapatch will adjust all the required things regardless of being executed in a stand-alone or a singe/Multitenant environment.

But what happens if you apply a PSU or a BP to one of your Multitenant environments and want to move PDBs one after another (or a few at the same time) to the new environment?
Or revert a PSU by plugging out from a CDB with the PSU inside - and plug it back into a CDB with a lower version or no PSU at all? 

First step - Check Plug In Compatibility 

Before you can even start your unplug/plug operation you should always perform the plugin check. This is divided in two simple steps:

  1. Create the XML description file for your PDB in CDB_SOURCE
    exec DBMS_PDB.DESCRIBE ('/home/oracle/PDB1_unplug.xml', 'PDB1');
  2. Run the plug in check in CDB_DEST
    begin
      if DBMS_PDB.CHECK_PLUG_COMPATIBILITY('/home/oracle/PDB1_unplug.xml','PDB1') then  
      DBMS_OUTPUT.PUT_LINE('No violations found - you can relax');
    else
      DBMS_OUTPUT.PUT_LINE('Violations found - check PDB_PLUG_IN_VIOLATIONS');
    end if;
    end;
    /

No Plugin Violations?

Then please follow the procedure described in:
http://blogs.oracle.com/UPGRADE/entry/upgrade_pdbs_one_at_a
without the upgrade part as you don't need to upgrade anything in this case of course. 


Higher patch in CDB_DEST than in CDB_SOURCE?

Then run this query:

select TYPE,MESSAGE,ACTION from PDB_PLUG_IN_VIOLATIONS where NAME='PDB1';

It will tell you to execute datapatch:

TYPE    MESSAGE
------  ----------------------------------------------------------------------------
ERROR   PSU bundle patch 1 (PSU Patch 4711): Installed in the CDB but not in the PDB

ACTION
------------------------------------------------
Call datapatch to install in the PDB or the CDB

Lower patch in CDB_DEST than in CDB_SOURCE?

Now this becomes a bit more tricky. See the output of PDB_PLUG_IN_VIOLATIONS:

TYPE  MESSAGE
----- ----------------------------------------------------------------------------
ERROR PSU bundle patch 1 (PSU Patch 4711): Installed in the PDB but not in the CDB

ACTION
------------------------------------------------
Call datapatch to install in the PDB or the CDB

Huh? Install???
What does this mean? Should I install now the current CDB/PDB's PSU into my target environment before being able to step down? 

Actually I think this message is misleading. And when you look into the MyOracle Support Note describing this under scenario 3 (MOS Note:1935365.1 - Multitenant Unplug/Plug Best Practices) you'll see that the author silently assumed as well that is is more likely that you'll remove the patch from the PDB. 

But how do you remove changes which came in with datapatch from within a PDB only?

You will need to run datapatch -rollback on the affected PDBs only:

$> datapatch -rollback <patch id> –force [–bundle_series] -pdbs <pdb1,pdb2,...,pdbn>

For further information see:

--Mike 

Monday Mar 02, 2015

New Behaviour in Oracle Database 12c and 11.2.0.4: SELECT ANY DICTIONARY with reduced privilege set

You've just upgraded to Oracle Database 12c - but your favorite admin tool receives an ORA-1031: Insufficient Privileges after connection?

Then the reason may be the reduced set of privileges for the SELECT ANY DICTIONARY privilege. This privilege does not allow access to tables USER$, ENC$ and DEFAULT_PWD$, LINK$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, and XS$VERIFIERS. Actually such changes are not new. For instance in Oracle 10.1 we removed the access to  LINK$ in SELECT ANY DICTIONARY (well, this may have happened because the dblink's password was stored in clear text in LINK$ - a misbehavior which is fixed since Oracle 10.2).

Please be very careful with granting this privilege. Furthermore, you need to be aware that it can't be granted either through a role, nor is it included in the GRANT ALL PRIVILEGES.

Oracle 11.2:

Oracle 12.1:

Documentation can be found here: 

  1. SELECT ANY DICTIONARY Privilege No Longer Accesses Some SYS Data Dictionary Tables
    For better security, the SELECT ANY DICTIONARY system privilege no longer permits you to query the SYS schema system tables DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, and XS$VERIFIERS. Only user SYShas access to these tables, but user SYS can grant object privileges (such as GRANT SELECT ON USER$ TO sec_admin) to other users.
    .
  2. 2.9.2.5 Increased Security When Using SELECT ANY DICTIONARY

Please be aware that you can't query anywhere inside the database which privileges are included in the SELECT ANY DICTIONARY privilege as this is embedded in our code.

 --Mike

PS: Credits go to Andy Kielhorn for highlighting this to me and thanks to Gopal for providing me with the doc links

Friday Feb 27, 2015

Why you seriously can't wait for the second release!

Premier Support for Oracle 11.2 has ended 4 weeks ago at 31-January-2015. 
Read here if you didn't know or don't believe it.
https://blogs.oracle.com/UPGRADE/entry/premier_support_for_oracle_11

I think most Oracle DBAs are aware of it. And I have stressed the topic about the need to upgrade to Oracle Database 12.1.0.2 a lot in the past months via the blog, in workshops and in discussions and customer meetings.

But there are still plenty of people out there you would like to wait for Oracle Database 12.2, the so called "second" release. From looking backwards I can understand this thinking. Neither Oracle 9.0 nor Oracle 10.1 were the best and most stable releases ever. If you waited a while then you could expect at least the first or sometimes even the 2nd patch set for the SECOND release being available. And then most people started going live. And some did wait for the terminal patch set.

But this has changed. You can't seriously wait anymore for the second release. Why? There are several reasons and it's fairly easy to explain.

  • Reason 1 - Every release is a full release
  • Reason 2 - Every release has a significant number of changes
  • Reason 3 - Every release has a significant number of new features
  • Reason 4 - Oracle 12.1.0.2 is the TERMINAL patch set 
  • Reason 5 - The time span between releases has grown to large
  • Reason 6 - Important application providers will certify Oracle 12.1.0.2

 

Especially the Reason 5 is very important. You can't seriously wait for Oracle Database 12.2 as you will potentially see a period with no bug fixing support for Oracle 11.2. 

So let's be honest:
You don't wait for the second release. You'll wait at least for the first patch set.
Patch Sets in the past got released roughly 12 months after the initial drop has been put out (please don't get this wrong: I'm not saying that 12.2.0.2 will be released 12 months after 12.2.0.1 - I just try to project from the past!).

We announced already the planned availability of Oracle 12.2 for H1CY16 (first half of calender year 2016).
See the Release Schedule MOS Note:742060.1for further details.

And keep in mind that this is a plan and no fixed schedule. So let's project the usual patch set cycle from the past. Then we may be in the 2nd half of 2017. If you start your tests (I hope you'll test :-) ) by then you may be ready to go live in 2018 - and Extended Support for Oracle 11.2.0.4 will end 31-January-2018.

Ouch ...

Look at the release cycles:

Oracle Release Cycle

It has grown from 18 months in the past to 45 months for Oracle Database 12.1.0.1. For Oracle Database 12.2 we may be at 30-36 months based on the currently announced plan.

Any further questions?

Be smart and transform from "We'll go live on the 2nd release only" into "We'll go live with the current release's first or terminal patch set!". This will be Oracle Database 12.1.0.2. There's a reason why large application providers such as SAP will announce support for Oracle Database 12.1.0.2 soon.

--Mike 

Thursday Feb 26, 2015

Oracle In-Memory Advisor with Oracle Multitenant? Issues?

Do you want to use the Oracle In-Memory Advisor in an Oracle Multitenant environment?

This is possible but with the current version it will require a workaround. The next available version of the IM Advisor is designated to support it without any workarounds.

At the moment the IM Advisor does not install into the CDB$ROOT container. But it can be installed into any PDB.

  • Create the user IMADVISOR in the PDB as local user first
  • No objects must be placed into the IMADVISOR schema but the user must have a default and a temporary tablespace
  • Install the IM Advisor locally in this PDB by using the install script imstimadv.sql
    • The script will detect the presences of the IMADVISOR schema. If the IMADVISOR schema is empty, it will proceed. It will then ask you to press ENTER instead of prompting you to set the password and default tablespace. Unless you setup the IMADVISOR user with an automated authentication method, it will still prompt you for the IMADVISOR's password when it connects to the IMADVISOR user

An issue which I learned about in the past days:

ORA-24817 get raised when using the IM Advisor.'s imadvisor_fetch_recommendations script unless you do not have a massive shared pool configured. The script does a SET LONGCHUNKSIZE 2000000000; and this needs to be reduced.
There has been a bug filed for it and I think it's supposed to be fixed in the next release of the IM Advisor.

-Mike 

Tuesday Feb 24, 2015

Oracle In-Memory Advisor now available

Oracle In-Memory is such a great feature - but often the challenge you'll face is the tricky question:
Which of your tables and/or partitions should you mark for In-Memory column store availability? 

And the answer is now easier to find as the new In-.Memory Advisor is available via download from 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

In case you have obtained a pre-release version of the Advisor, please discard it and replace it with the production version 

--Mike 

Friday Feb 20, 2015

Downgrade Oracle Restart 12c - Grid Infrastructure only?

Oracle RestartCan you downgrade your Oracle Restart installation from Oracle 12c back to Oracle 11g?

Actually there's no real direct downgrade supported for Oracle Restart. But of course there's a way to do it.

Basically it is: 

  • Deconfigure Oracle Restart in 12c
  • Configure Oracle Restart in 11g 

But wait a minute. It is very important to know if you have upgraded your database already. If that is the case then first you MUST downgrade your database(s) as you can't manage a higher version Oracle Database with a lower version Clusterware.

So first of all, please downgrade your Oracle database(s) first: 

At the next stage you'll need to "downgrade" the Oracle Clusterware resp Grid Infrastructure for Oracle Restart: 

Before you attempt this you'll need to deconfigure the Restart resources - and please be aware that here's a small difference in commands between Oracle 12.1.0.2 and Oracle 12.1.0.1.

This is from the documentation for Oracle 12.1.0.2

  • Deconfigure Oracle Restart:
    • Log in as root
    • cd /u01/app/12.1.0.2/grid/crs/install
    • roothas.sh -deconfig -force
  • Once this is complete you can now deinstall the Grid Infrastructure with the deinstall tool
  • Then you will need either to reinstall the previous - for instance Oracle 11.2.0.4 - Grid Infrastructure or - if it's still present on the machine - reconfigure it by running root.sh from the previous Clusterware's home
  • And finally reconfigure the database(s) again with Clusterware
    • $ srvctl downgrade database -d db-unique-name -o oraclehome -t to_version

.
In case you'd plan to do this exercise back from Oracle 12.1.0.1 instead then you'll have different steps to follow to deconfigure Oracle Restart 12.1.0.1:

  • Deconfigure Oracle Restart 12.1.0.1
    • # cd /u01/app/12.1.0.1/grid/crs/install
    • # /u01/app/12.1.0.1/grid/perl/bin/perl /u01/app/12.1.0.1/grid/crs/install/roothas.pl -deconfig
      .

--Mike

PS: See bug18160024 or the GI install guide, section A11.4 for the Standalone GI downgrade instructions from 12.1.0.2.0. 

Friday Feb 13, 2015

Hands-On-Lab "Upgrade, Migrate & Consolidate to Oracle Database 12c" available for DOWNLOAD now!

Wow ... that was a hard piece of work. Roy put a lot of effort into getting our Hands-On-Lab on OTN for download. We promised to have it available after OOW - or at least a description how to create it by yourself. And finally it's there. Find it here:

A few important things to mention before you start the download: 

  • It's a Virtual Box image
  • You will need to install Oracle Virtual Box first - and please install also the VBox Extensions
  • Your PC must have a 64-bit host operating system
  • You need to enable Virtualization options in your computer's BIOS
  • You PC should have at least 4GB of RAM - having 8GB is very helpful
  • A fast disk (SSD) will speed up things
  • The instructions are available for download but are included in the download as well
  • The lab will guide you through the following tasks:
    1. Upgrade an 11.2.0.4 database to Oracle 12.1.0.2
    2. Plug in this database into a 12.1.0.2 CDB
    3. Migrate an 11.2.0.4 database with Full Transportable Export into another PDB
    4. Unplug an 12.1.0.1 PDB and plug/upgrade it into an 12.1.0.2 CDB

You'll find a picture as screen background inside the VBox image always giving you guidance about "what to accomplish" and "how to switch environments".

Enjoy :-)

--Mike 

 

 

Thursday Feb 05, 2015

Oracle Multitenant: New SQL Container Clause

Tiny little enhancement in Oracle Database 12.1.0.2
The new CONTAINER clause to access data from different containers within one SQL statement. This may be very helpful, especially in case of schema consolidation. Similar things could have be done in Oracle 12.1.0.1 already by using database links - but resulting in way more complicated SQLs.

This is the new clause: 

SELECT ename FROM CONTAINERS(scott.emp) WHERE CON_ID IN (45, 49); 

See the documentation for more info about it.

--Mike 

Tuesday Feb 03, 2015

How to migrate to Unified Auditing?

Lock

What is Unified Auditing and is it on by default?

Unified Auditing is the new auditing facility since Oracle Database 12c. But the "old" auditing is still working. And there are a few things to mention if you'd like to make the right choice. I have written some things about it a while ago but as I discovered yesterday my previous blog post (https://blogs.oracle.com/UPGRADE/entry/unified_auditing_is_it_on)  doesn't satisfy all my needs.

The initial motivation to move towards the new Unified Audit trail is audit performance. The audit records will be written into the read-only table AUDSYS in SYSAUX tablespace. But there are other benefits such as no dependency on init.ora parameters, one location - one format, and close interaction with Oracle Audit Vault and Database Firewall. And of course tiny things such as the immediate write, which avoids losing any audit records during an instance crash.

Audit records are coming from those sources:

  • Audit records (including SYS audit records) from unified audit policies and AUDIT settings
  • Fine-grained audit records from the DBMS_FGA PL/SQL package
  • Oracle Database Real Application Security audit records
  • Oracle Recovery Manager audit records
  • Oracle Database Vault audit records
  • Oracle Label Security audit records
  • Oracle Data Mining records
  • Oracle Data Pump
  • Oracle SQL*Loader Direct Load 

In addition to user SYS all users having the roles AUDIT_ADMIN and AUDIT_VIEWER can query the AUDSYS table.

After upgrade to Oracle Database 12c Unified Auditing is not enabled by default in order to prevent customers having "old" auditing on already from enabling both auditing facilities at the same time. This is something you need to be aware of: Unified Auditing can be on together with the "old" auditing at the same time

Check if Unified Auditing is linked into the oracle kernel;

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

PARAMETER         VALUE
----------------  ---------- 
Unified Auditing  FALSE

To link it into the kernel or enable it use the following commands/actions - and the documentation states that you'll have to shut down the listener and restart it again afterwards:

  • UNIX
    • cd $ORACLE_HOME/rdbms/lib
      make -f ins_rdbms.mk uniaud_on ioracle
  • Windows
    • Rename the file %ORACLE_HOME%/bin/orauniaud12.dll.option to %ORACLE_HOME%/bin/orauniaud12.dll

The tricky part is now that - even though Unified Auditing is not enabled by default - Unified Auditing is enabled in a Mixed Mode, i.e. there are two auditing policies enabled - but the option is not linked into the kernel.

To disable these policies you'll execute:

  • SQL> noaudit policy ORA_SECURECONFIG;
  • SQL> noaudit policy ORA_LOGON_FAILURES;

Don't get me wrong: This is not a recommendation to disable Unified Auditing. I just would like to explain what's on and the possibilities to turn things into the desired direction. The documentation says about Mixed Mode:

Mixed mode is intended to introduce unified auditing, so that you can have a feel of how it works and what its nuances and benefits are. Mixed mode enables you to migrate your existing applications and scripts to use unified auditing. Once you have decided to use pure unified auditing, you can relink the oracle binary with the unified audit option turned on and thereby enable it as the one and only audit facility the Oracle database runs. 

How do you enable a Unified Auditing Policy?

The documentation offers a straight forward tutorial (which is a bit EM driven):
https://docs.oracle.com/database/121/TDPSG/tdpsg_auditing.htm#TDPSG50528

How to change between IMMEDIATE and QUEUED WRITE mode?

For a performance evaluation please see Szymon's blog post at the CERN blogs. To switch between the different modes please see the Oracle Documentation:

  • To use immediate write mode use this procedure:
  • BEGIN
     DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
      DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
      DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE, 
      DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE);
    END;
    /
    
  • To use queued write mode run this procedure:
  • BEGIN
     DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
      DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, 
      DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE, 
      DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE);
    END;
    /

The size of the queue by default is 1MB. If you'd like to change it (maximum: 30MB) the initialization parameter UNIFIED_AUDIT_SGA_QUEUE_SIZE has to be changed.

What happens now to the traditional AUDIT_TRAIL parameter and what effect does it have?

AUDIT_TAIL will still trigger and direct the "old" auditing facilitiy (SYS.AUD$ for the database audit trail, SYS.FGA_LOG$ for fine-grained auditing, DVSYS.AUDIT_TRAIL$ for Oracle Database Vault, Oracle Label Security, and so on). So be aware to have both auditing facilities on at the same time as this won't make much sense. Our recommendation since Oracle Database 11g is generally to set AUDIT_TRAIL in every 11g/12c database explicitly to the value you want. Otherwise it could always happen (and happens many times) that your database accidentally writes audit records into AUD$. Reason why this happens so often: the default setting for AUDIT_TRAIL since Oracle Database 11g is "DB" unless you change this via the non-standard parameter listening in the DBCA (Database Configuration Assistant).

Therefore always set AUDIT_TRAIL explicitly to the value you want to prevent the database from accidental auditing.

Summary - Steps to migrate to Unified Auditing?

  1. Turn off traditional auditing with AUDIT_TRAIL=NONE
  2. Link Unified Auditing into the kernel or enable it on Windows
  3. Define your auditing policies 
  4. Monitor it with the views UNIFIED_AUDIT_TRAIL and in multitenant environments with CDB_UNIFIED_AUDIT_TRAIL
A final question remains unanswered:
What happens to your existing audit records in AUD$ and the other auditing tables?

Actually I can't answer this question but to me there seems to be no way to migrate existing audit records into the new Unified Auditing facility. But I don't think that this will cause any issues as you can keep and safely store the contents of the traditional auditing. They don't get overwritten or deleted during an upgrade.

Further information required?

-Mike 

Thursday Jan 29, 2015

Upgrade to 12c and Plugin - one fast way to move into the world of Oracle Multitenant

What is the best way to move into Oracle Multitenant (which includes Single Tenant with just one PDB) as well?

This question came up on the blog, it's part of the Multitenant chapter in our big slide deck but let me elaborate this a bit more in detail. Of course there are several ways to move a database into a Single/Multitenant environment such as Data Pump but one fact is for sure: You can't convert a stand-alone database to become a container database (CDB$ROOT). A container database has to be built up from scratch as first step. Please use the DBCA to create it as this is the easiest path.

Upgrade and Plugin

But how can your stand-alone database be moved now?

  1. Upgrade your stand-alone database to Oracle Database 12c, ideally to Oracle 12.1.0.2 with the newest PSU included already. Upgrade it to exactly the same version including the same PSU as your container database (CDB) got created from.
    --
  2. Then start your stand-alone database in read-only mode - of course at this step you'll encounter downtime:
    startup open read only;
    --
  3.  Generate the XML description file – this file will contain the information describing the database structure. To create it the database has to be in read only mode:
    exec DBMS_PDB.DESCRIBE('/tmp/pdb1.xml');
    --
  4. Shutdown the database
    shutdown immediate
    --
  5. Change into your new CDB and do a compatibility check:
    SET SERVEROUTPUT ON
    DECLARE
    compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/tmp/pdb1.xml', pdb_name => 'PDB1') WHEN TRUE THEN 'YES' ELSE 'NO'
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE(compatible);
    END;
    /
    --
  6. Plugin your stand-alone database (in my case with a new name PDB1):
    create pluggable database PDB1 using '/tmp/pdb1.xml' nocopy tempfile reuse;
    --
    Be aware that using the NOCOPY option will require that you have a valid backup of your database. If you'll use the COPY option instead you will need additional disk space. When using NOCOPY files will remain at their location. When using COPY you'll combine it with the FILE_NAME_CONVERT option, converting source to destination paths.
    It's always a recommendation to keep the TEMP tablespaces. In case you'd like to remove then you'll have to drop the tablespace and the tempfile before. Otherwise there will be a reference in the XML manifest file making it impossible to plug in your new PDB.

    --
  7. Connect to this new PDB1 and perform sanity operations:
    alter session set container=PDB1;
    @?/rdbms/admin/noncdb_to_pdb.sql

    This script will do required sanity operations to connect the PDB with the CDB. It will do things such as change object id's, mark common objects etc. Its runtime can vary between 5 minutes and several hours depending on the complexity of the former stand-alone's database data dictionary. It will do recompilations of many invalid objects as well. Please test it carefully. 
    If this step gets skipped the PDB can be open in read-only mode only.
  8. ----

  9. Now the database is plugged in – but not open yet. It will need to be started.
    startup
    show pdbs

    exit
    --
  10. To connect to the consolidated PDB1 from the command prompt the following command syntax needs to be used:
    sqlplus "sys/oracle@pdb1 as sysdba"
    As an alternative you could use the EZconnect syntax:
    sqlplus "sys/oracle@//localhost:1521/pdb1 as sysdba"

1.
-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
« July 2015
SunMonTueWedThuFriSat
   
4
5
7
8
10
11
12
15
16
18
19
21
22
23
25
26
27
29
30
31
 
       
Today
Oracle related Tech Blogs
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers