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/Xmake -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/Xmake -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/Xmake -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/Xmake -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.

In case you wonder why I'm writing always  Xmake in this blog post (which is not correct - it is "make" of course):
Our blog software does not allow me to publish an article saying /usr/bin/m... :-( Sorry for the inconvenience.

--Mike 

Tuesday May 26, 2015

Oracle 12.1.0.2 - Security Behavior Change with non-SYSDBA Triggers

Oracle Database SecuritySometimes things get revealed at unexpected occasions. This one happened during a recent customer upgrade to Oracle Database 12c with a 3rd party geospatioanl application installed (ESRI).

At the very end of the upgrade the customer saw many ORA-1031 (insufficient privileges) errors and it seemed to be that nothing was working correctly anymore. 

This happened during the run of catupend.sql. The following code path in  catupend.sql causes the error.

cursor ddl_triggers is                                       
   select o.object_id from dba_triggers t, dba_objects o     
    where t.owner = o.owner and t.trigger_name = o.object_name
      and o.object_type = 'TRIGGER'                          
      and (t.triggering_event like '%ALTER%' or              
    t.triggering_event like '%DDL%');     


ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
SDE.DB_EV_ALTER_ST_METADATA
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_UTILITY", line 1279
ORA-06512: at line 20

Apparently there's no access to an application trigger anymore - which got deployed as a system trigger (for more information about ESRI's system trigger please click this link). Even though this is strange it doesn't seem like a big issue. But in fact it is as this procedure failed and caused other stuff not getting validated correctly. So subsequent actions (for instance the run of utlu121s.sql, the post upgrade script) failed with ORA-1031 as well pointing to DBMS_UTILITY.

The customer [Danke Andy!!!] itself found the workaround by pattern matching similar issues in MOS and trying some grants - the 3rd one did the trick:

  • GRANT ADMINISTER DATABASE TRIGGER to SDE;


So it was obvious that something in the security architecture in Oracle Database 12.1.0.2 had been changed - and somebody forgot to document it. Later on I've learned that this change got introduced with the July 2013 PSU/CPU as well. I don't blame the customer for not applying PSUs since almost two years - I knew that upfront and we are implementing a 2-PSUs-per-year strategy now with the upgrade to Oracle Database 12c. 

The system trigger ESRI had created couldn't be validated anymore under the context of the SDE (ESRI's application) user. Therefore it failed but caused other actions to fail as well.  

This behavior change is related to "SYSDBA privilege should not be available in non-SYS owned DR procedure / trigger execution" which is first fixed into 12.1.0.2, and then backported as part of CPU July-2013.
When SYS executes a non-SYS owned DR procedure or a Trigger, the SYS privileges would not available during the procedure/trigger execution. The procedure/trigger owner privileges prevail.

--Mike

Patching Best Practices - 38min Video

Patch Ever asked yourself about Database Patching Best Practices?

I know that not always everything works that simple and easy. See the blog post by Pieter Van Puymbroeck from Exitas in Belgium about some findings when applying the April 2015 PSU to an Exadata or by myself about my experiences when applying the GI PSU from Jan 2015.

But this 38 min video may give you a good overview about the best and recommended techniques at first hand by Eleanor Meritt and David Price, both from the Sustaining Engineering organization that issues bug fixes, patches and patchset updates for the Oracle Database, Enterprise Manager and Fusion Middleware product area.

Building on a highly popular session from Oracle OpenWorld 2013, this 2014 OpenWorld session further explores ways to help you maintain and patch your database systems most efficiently. Learn about patch testing best practices, techniques for minimizing downtimes, how to best rollout patches in cloud environments, and more. The presentation also shares the latest Oracle Database 12c features and tooling to help ease patching processes.

-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 


Monday Apr 20, 2015

Oracle PSU and BP April 2015 is available

As of April 14, 2015:

The April 2015 PSU/BP is available!

Here's the most important information:

Please find below the links to the Recommended Patches and Patch Numbers for each of your database releases:

12.1.0.2
 Availability and Known issues for 12.1.0.2      Note:1683799.1
12.1.0.1
 Availability and Known issues for 12.1.0.1      Note:1565082.1 
11.2.0.4
 Availability and Known issues for 11.2.0.4      Note:1562139.1
 
11.2.0.3
 Availability and Known issues for 11.2.0.3      Note:1348336.1

11.1.0.7
 List of fixes included in 11.1.0.7              Note:601739.1

-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 

Tuesday Apr 14, 2015

COLLABORATE15: Hands On Lab: Bring Your Laptop!!!


Wednesday, Apr  15, 2015, Roy and I will deliver two Hands-On Labs at COLLABORATE15 in the "Southseas Room A" in the morning. If you are signed up and read this please don't forget to bring your laptop with a VNC installed - and power charged for at least 1 hour.

You will access the lab via a VNC session - there are no laptops in the room - and the hotel doesn't provide power strips for labs shorter than 1 hour :-(

Unfortunately we don't have access to the registration data. Therefore we are unable to inform you upfront directly. Just in case you arrive without meeting these requirements you can always afterwards download the entire lab from the blog:

Hands On Lab -  Upgrade, Migrate, Consolidate to 12c

Hands On Lab Instructions 

--Mike

Friday Apr 10, 2015

Parallel Index Creation with Data Pump Import

Here is a new capability that might be interesting to anybody who is performing a migration using Data Pump. Previously, Data Pump would create indexes one at a time, specifying the PARALLEL keyword for the CREATE INDEX statement to invoke parallel query for index creation. We used to recommend a workaround to create indexes in parallel, which involved a three-step process of importing without indexes, then creating a SQLFILE of the CREATE INDEX statements, and breaking that file into multiple windows.

Through extensive performance testing we found that it is faster to create multiple indexes in parallel (using a parallel degree of 1) instead of creating a single index using parallel query processes. This is enabled by the patch for bug 18793090, which is available as a backport for 11.2.0.4 Exadata BP 9, 12.1.0.1.3, or 12.1.0.2. If you need it for another platform, that can of course be requested. The number of indexes created will be based on the PARALLEL parameter.

Here is an example of the effects of this patch on a toy example that I created using our hands-on lab VM environment. I created a table in the SYSTEM schema with 4 columns and 14 indexes, and then inserted a couple of dozen rows into the table. Then I exported the SYSTEM schema from 11.2.0.4 and imported into a 12.1.0.2 PDB with PARALLEL=4, both with and without the patch. 

Normal (unpatched) behavior:

;;; 
Import: Release 12.1.0.2.0 - Production on Thu Apr 9 14:38:50 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
09-APR-15 14:39:08.602: Startup took 2 seconds
09-APR-15 14:39:12.841: Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
09-APR-15 14:39:13.417: Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@pdb1 directory=temp dumpfile=exp_test.dmp logfile=imp_test1.log logtime=all metrics=yes parallel=4 
09-APR-15 14:39:13.605: Processing object type SCHEMA_EXPORT/USER
09-APR-15 14:39:14.454:      Completed 1 USER objects in 1 seconds
09-APR-15 14:39:14.470: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
09-APR-15 14:39:14.541:      Completed 5 SYSTEM_GRANT objects in 0 seconds
09-APR-15 14:39:14.596: Processing object type SCHEMA_EXPORT/ROLE_GRANT
09-APR-15 14:39:14.655:      Completed 2 ROLE_GRANT objects in 0 seconds
09-APR-15 14:39:14.690: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
09-APR-15 14:39:14.728:      Completed 1 DEFAULT_ROLE objects in 0 seconds
09-APR-15 14:39:14.746: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
09-APR-15 14:39:15.275:      Completed 1 PROCACT_SCHEMA objects in 1 seconds
09-APR-15 14:39:15.377: Processing object type SCHEMA_EXPORT/TABLE/TABLE
09-APR-15 14:39:15.626:      Completed 1 TABLE objects in 0 seconds
09-APR-15 14:39:15.673: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
09-APR-15 14:39:16.031: . . imported "SYSTEM"."TAB1"                             6.375 KB      12 rows in 1 seconds
09-APR-15 14:39:16.096: Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
09-APR-15 14:39:20.740:      Completed 14 INDEX objects in 4 seconds

With Patch:

;;; 
Import: Release 12.1.0.2.0 - Production on Thu Apr 9 15:05:19 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
09-APR-15 15:05:22.590: Startup took 0 seconds
09-APR-15 15:05:23.175: Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
09-APR-15 15:05:23.613: Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@pdb1 directory=temp dumpfile=exp_test.dmp logfile=imp_test3.log logtime=all metrics=yes parallel=4 
09-APR-15 15:05:23.699: Processing object type SCHEMA_EXPORT/USER
09-APR-15 15:05:23.862:      Completed 1 USER objects in 0 seconds
09-APR-15 15:05:23.882: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
09-APR-15 15:05:23.937:      Completed 5 SYSTEM_GRANT objects in 0 seconds
09-APR-15 15:05:23.993: Processing object type SCHEMA_EXPORT/ROLE_GRANT
09-APR-15 15:05:24.071:      Completed 2 ROLE_GRANT objects in 1 seconds
09-APR-15 15:05:24.096: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
09-APR-15 15:05:24.180:      Completed 1 DEFAULT_ROLE objects in 0 seconds
09-APR-15 15:05:24.216: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
09-APR-15 15:05:24.378:      Completed 1 PROCACT_SCHEMA objects in 0 seconds
09-APR-15 15:05:24.460: Processing object type SCHEMA_EXPORT/TABLE/TABLE
09-APR-15 15:05:24.665:      Completed 1 TABLE objects in 0 seconds
09-APR-15 15:05:24.782: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
09-APR-15 15:05:25.096: . . imported "SYSTEM"."TAB1"                             6.375 KB      12 rows in 1 seconds
09-APR-15 15:05:26.291: Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
09-APR-15 15:05:26.809: Startup took 4 seconds
09-APR-15 15:05:26.896: Startup took 5 seconds
09-APR-15 15:05:27.138: Startup took 4 seconds
09-APR-15 15:05:28.398:      Completed 14 INDEX objects in 3 seconds

A few of things are noteworthy here:

  1. The indexes took 4.644 seconds without the patch and 3.302 seconds with the patch. So, the effect is significant even on a toy example.
  2. Those messages about startup taking X seconds are because we did not need the parallel workers for the table data. The startup time probably isn't correct; that's just the time between the start of the job and the first use of the worker
  3. If you apply this patch, we will no longer use PX processes (formerly known as PQ slaves) to create indexes
If you take advantage of this patch, let me know the results in a reply here! We are excited to be able to add a bit more parallelism into Data Pump, and plan on more for the future.

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!

Thursday Mar 19, 2015

Migration of an EM Repository cross-platform?

Can you migrate your EM Cloud Control Repository to another OS platform? Cross-platform and cross-Endianness?

This question sounds so incredibly simple that you won't even start thinking I guess. Same for ourselves. Use Data Pump. Or Transportable Tablespaces. Or Full Transportable Export/Import if your source is at least 11.2.0.3 or newer.

But sometimes in life things seem to be simple, but as soon as you unmask them you'll find a full bunch of issues. It's a fact that the repository of EM Cloud Control is quite a bit complicated. And uses plenty of databases technologies. 

Actually all credits go to Roy here as he has worked with the EM group for the past 6 months on this topic.

You can migrate a EM Cloud Control Repository cross-platform but not cross-Endianness (e.g. HP-UX to OL, big to little Endianness). The latter is scheduled to be supported in EM 13.2.

 

Summary:

As EM Cloud Control Repository migrations is possible right now only within the same Endianness group you should decide carefully where you store your EM Cloud Control Repository.

 --Mike

 

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 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

--Mike 

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

Tuesday Mar 10, 2015

Another Upgrade Workshop in Reston on March 31!

For those who were unable to attend the previous Database Upgrade workshop in Reston, VA (just outside DC), we have added another event in three weeks on Tuesday, March 31. This time we will have the nice auditorium with a larger capacity, so we should be able to accommodate everybody. If you would like to sign up, please see the evite at

http://www.oracle.com/us/dm/seo100396221-na-us-ip-ipi1-ev-2436170.html 

You will notice that this time the registration is being handled via telephone, to avoid the problems we had last time with the email system.  If you have any problems registering, please let me know.

So, apologies to anybody who was shut out last month, and I hope we get a nice big audience in three weeks! 

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 

Tuesday Mar 03, 2015

There is still time to register for next week's Upgrade Workshop in Omaha!

Upgrade Workshop Banner

While the event in San Francisco is a sell-out (metaphorically speaking -- Upgrade Workshops are of course free of charge), we do still have room at next week's seminar in Omaha on Wednesday, March 11:

Oracle Database 12c Upgrade Seminar

This full-day event at the Embassy Suites in La Vista will include discussions of many ways to upgrade and migrate to Oracle Database 12c. I will also talk about Oracle Multitenant and other new features. We also include a guide for how to ensure you will have good performance after the upgrade.

I have visited Omaha as a tourist before, but this is my first upgrade workshop in the area. I hope to see you there! 

ORAchk 12.1.0.2.3 with new checks + Restart support

ORAchk

Never used or heard about ORAchk? Then it's time to give it a try! 

ORAchk 12.1.0.2.3 has now be released. ORAchk 12.1.0.2.3 has so many great features, especially more than 50 new health checks, OL7 Support - and is now aware of Oracle Restart environments.

New features include:

  • Linux on System Z (RHEL 6, RHEL 7, SuSE 12)
  • Oracle Enterprise Linux 7
  • Single Instance ASM Checks
  • Upgrade Validation checks for 12.1.0.2
  • Enhanced Golden Gate Checks 
  • Enterprise Manager Agent Checks
  • Enhanced Reporting to highlight checks that ORAchk cannot gain full visibility for
    (checks that require manual validation)
  • Improved health Score Calculation (you can now hit 100%)
  • Over 50 new health checks
  • Bug Fixes

Download the most recent version via MOS Note:1268927.1.

Learn more about the Upgrade Readiness Assessment with ORAckh in MOS Note: 1457357.1

--Mike 

PS: Updated the post on Mar 7, 2015, as ORAchk 12.1.0.2.3 became production!

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 

Monday Feb 23, 2015

Upgrade the Operating System in a RAC Environment

Last week at the upgrade workshop in Budapest a customer had a interesting and - I believe - not uncommon question.

How can I upgrade my Linux Operating System in my RAC environment without taking the entire cluster down?
In this specific case the customer wanted to upgrade from RHEL 5.10 to RHEL6 or RHEL7.

Let's assume it's the typical 2-node-RAC where one wants to upgrade in a rolling fashion. And the data is stored within ASM.

  1. Drain Node 1 (i.e. take the workload off) - this will be the node getting upgraded first
  2. Remove Node 1 from the  cluster (deleteNode procedure)
  3. Upgrade the OS (which is most likely a reimaging of the node). If the OS upgrade does not wipe out the entire server you can follow MOS Note:1559762.1 as it shows an OS upgrade from OL 6.2 to 6.4 which leave the Oracle installations intact) 
  4. Add Node 1 back to the cluster (addNode procedure)
  5. Extend the Database Home to Node 1 using either cloning or addNode
  6. Make the database available on the newly added Node 1 and drain Node 2
  7. Repeat steps 2-6 for Node 2 
  8. Ideally now you'll perform a rolling upgrade of Oracle GI to Oracle Database 12c. Please apply the most recent PSU as well.
  9. Furthermore you may now look into upgrading your databases to Oracle Database 12.1.0.2 as well.
See the following documentation: 
 --Mike  

Friday Feb 20, 2015

Upgrade Workshops coming in March in North America

The days are getting longer, the snow is still getting deeper, and the upgrade workshops keep coming in North America! Remember that you can always go to http://events.oracle.com and plug in a keyword of "upgrade" to find upcoming live events. Roy will be the speaker at some of these events, while others will be delivered by some of our top presales technical folks like Dan Wittry:

March 5: Oracle 12c Database Upgrade Seminar-Detroit (Troy, MI)

March 11: Oracle Database 12c Upgrade Seminar - Omaha (La Vista, NE)

March 12: Oracle Database 12c Upgrade Seminar - Redwood Shores, CA Sold out!

March 17: Oracle 12c Database Upgrade Seminar-Calgary

March 19: Oracle Database 12c Upgrade Seminar - Seattle (Bellevue, WA)

March 19: Oracle Database 12c Upgrade Seminar Tampa

As you can see, we do sometimes reach capacity and have to shut down registrations. If you are interested in any of these events, sign up now and don't be left out in the cold (literally in the north, or metaphorically in other regions).

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
« May 2015
SunMonTueWedThuFriSat
     
1
2
3
4
5
6
7
8
9
10
12
13
14
15
16
17
19
20
21
22
23
24
25
28
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