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

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

Tuesday Jan 20, 2015

Some Tokyo Impressions - January 2015

Just some impressions from our week in Tokyo, Japan in January 2015.

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

-Mike 

Tuesday Jan 13, 2015

New MOS Notes on Database Upgrades for 12c with or without Oracle Multitenant

Please find some new MyOracle Support (MOS) Notes about Database Upgrades to Oracle Database 12c, with or without Oracle Multitenant:

Credits for the CDB/PDB Notes go to my colleague, Krishnakumar Kunka  :-)

-Mike 

Monday Jan 12, 2015

Upcoming Upgrade Workshops Jan/Feb 2015

The new year will start with a good bunch of Upgrade, Migrate & Consolidate to Oracle Database 12c workshops in Japan, USA, Canada and Europe: 

January 20, 2015  
Tokyo,
Japan
Oracle Database 12c Upgrade Seminar
including Hands-On for Partners
by invitation only



February 04, 2015  
Irving (Dallas), TX
USA
Oracle Database 12c Upgrade Seminar - Dallas
Register
.

February 05, 2015  
Houston, TX
USA
Oracle Database 12c Upgrade Seminar - Houston
Register
.

February 09, 2015  
Bratislava,
Slovakia
Oracle 12c Database Upgrade Seminar - Bratislava
Register
.

February 10, 2015  
Budapest,
Hungary
Oracle 12c Upgrade Seminar Budapest
Register
.

February 10, 2015  
Reston, VA
USA
Oracle 12c Upgrade Seminar Reston
Register
.

February 11, 2015  
Atlanta, GA
USA
Oracle Database 12c Upgrade Seminar - Atlanta
Register
.
February 19, 2015  
Nürnberg,
Germany
Franconian User Group:
Oracle Database 12c Upgrade
17:30-21:00 Uhr - TU Nürnberg GSO - 
Raum WE.012 Wasertorstraße 10
Anmeldung
.

Friday Jan 09, 2015

Oracle 12.1.0.2 is now certified with Oracle EBS 12.2

Just copying this information from Steven Chang's blog: 

Please note that Oracle Database 12.1.0.2 is already certified with EBS 12.1. 

-Mike 

Friday Dec 19, 2014

Upgrade Workshops 2014 - Summary

2014 is almost over. Time for a quick review :-)

Roy and I would like to thank you for attending at our workshops throughout the globe. We've delivered more workshops than ever before. Most of them in Europe and Asia, but we did visit also South and Middle America, Australia and - well - the US of course. 

See here the full workshop map:

We delivered in 2014:

  • 62 workshops and conferences
  • for almost 4500 attendees
    • 13x in Asia/Australia
    •  5x in South/Mid America
    • 15x in the US and
    • 29x in EMEA
  • Many dedicated customer workshops
  • Several webcasts

Thanks again to all of you - it was a pleasure to meet with you all.

Have wonderful holidays and a successful start into 2015. Hope to see you all soon again and don't forget: Upgrade Now!!!

-Mike & Roy 

Thursday Nov 27, 2014

New Slide Decks Uploaded:
Upgrade/Migrate/Consolidate to Oracle 12c and
Parallel Multitenant Upgrade Internals

It's time for a few updates and slide uploads :-) And thanks again to all the great people in Madrid earlier this week. It was a fantastic day - I enjoyed it a lot and wish you all successful upgrades and migrations :-)

What's new? 

  • A refreshed version of our huge Oracle Database 12c "Upgrade, Migrate & Consilidate" slide deck. Now with 530 slides - kudos to Tom Kyte who gave me a lot of food for thought after his excellent talk at the DB TECH SHOWCASE 2014 TOKYO. I had to add a few slides later on making it now even harder to fit the content into a 1 day workshop :-)
    As usual see the Change Log at the end of the deck about what has been added/changed.
  • A new and extended version of Joe Errede's OOW talk about how an Oracle Multitenant Upgrade works. All details about the parallel upgrade, options and tweaks of catctl.pl. So kudos to Joe as he has build the basis for the slides - I reused many of them and added some stuff - and thanks to the (always) great audience at DOAG Conference in Nuernberg :-)
  •  A refreshed version of the Hitchhiker's Guide especially prepared for the (amazingly) huge audience at the DOAG Conference in Nuernberg (I was really impressed!!)
-Mike 

Monday Nov 17, 2014

Oracle Database 12.1.0.2 EE for HP, AIX and zLinux available

Last week the remaining ports for Oracle Database 12c 12.1.0.2 became available at the usual sources:

Thursday Nov 13, 2014

Incremental Statistics Collection improved in Oracle 12c

Traveling right now through Asia. It was Beijing for 32 hours, Toyko for 24 hours - and now we are running an internal 2-day workshop with colleagues from Korea, New Zealand, India and some other countries in Seoul. And yesterday I had the pleasure to listen to Tom Kyte to his optimizer talk at the OTN Conference in Tokyo. And I learned a lot - as always when having the chance to listen to Tom, Graham Wood and the other great experts.

Oracle Database 11.1 offered a great new feature: Incremental Statistics Collection which helped a lot to make stats collection on partitioned tables way more efficient. But it had a few flaws and it took a while to work as expected. And it had one side effect when you used it heavily: It stored tons of data in WRI$_OPSTAT_SYNOPSIS. We saw it on some databases at almost 300GB. 

Now the thing with such a huge WRI$_OPSTAT_SYNOPSIS can be: It gets a new partitioning layout during upgrades twice:

  • Upgrade from Oracle 11.1.0.x/11.2.0.1 to Oracle 11.2.0.2/3/4:
    • Change to Range.Hash Partitioning for WRI$_OPSTAT_SYNOPSIS
    • This can cause a lot of data movement.
  • Upgrade from Oracle 11.2.0.2/3/4 to Oracle 12.1.0.x:
    • Change to List-Hash Partitioning
    • This will cause not as much data movement as in the previous change

Tom explained yesterday that in Oracle Database 12c Incremental Statistics Collection has gotten a few excellent extensions making it more efficient: 

  • Smaller footprint on disk for synopses compared to previous releases
  • Incremental stats with partition exchange operations
  • Ability to define a stale percentage for existing partitions

The latter one is very interesting as it meant: Before Oracle Database 12c whenever you did change a single row within an existing partition during a recalculation of the Global Stats this particular partition need to be examined again - even though just one record has been changed - instead of still using the stored synopsis.

In Oracle Database 12c you can now define a stale percentage. First you'll have to enable it, second you can set a stale percentage by yourself - otherwise the default of 10% would apply - but only if it has been enabled. Otherwise the pre-12c default will be kept (and this is the behavior in Oracle Database 12c out of the box):

  • Switch incremental statistics on for a specific partitioned table:
    • SQL> exec DBMS_STATS.SET_TABLE_PREFS('SH','SALES','INCREMENTAL','TRUE'); 
  • Switch on the new 12c stale percentage feature globally:
    • SQL> exec DBMS_STATS.SET_DATABASE_PREFS('INCREMENTAL_STALENESS',
      'USE_STALE_PERCENT');
  • Change (only if desired) the stale percentage of default of 10%:
    • SQL> exec DBMS_STATS.SET_DATABASE_PREFS('STALE_PERCENT','12');
-Mike

Thursday Oct 23, 2014

ORA-20000 Unable to gather statistics concurrently: Resource Manager is not enabled
ORA-06512: at "SYS.DBMS_STATS"

We are running a great training this week in Munich, called TECH CHALLENGE for Oracle people only. And one group hit the below issue so I thought I should share this as it is very likely it will happen with the upgrade to Oracle Database 12c in your environment as well.

During the run of catctl.pl or DBUA you may get an error saying:

ORA-20000: Unable to gather statistics concurrently:
Resource Manager is not
enabled.

ORA-06512: at "SYS.DBMS_STATS"

Even worse, the upgrade will fail and exit in phase 68 of the parallel upgrade.
This issue is new with Oracle Database 12.1.0.2 and is logged as bug19664340 

Reason why this happens:
It seems to be "somebody" has switched statistics gathering with Oracle Database 12.1.0.2 to CONCURRENT.

Workaround:

  1. SQL> exec dbms_stats.set_global_prefs('CONCURRENT', 'FALSE');
  2. Reset it after upgrade with
    SQL> exec dbms_stats.set_global_prefs('CONCURRENT', 'TRUE');
  3. Enter SQL*Plus in your 12c environment [even if you had used the DBUA]
  4. Restart your database in upgrade mode
    SQL> startup upgrade
    SQL> exit
  5. cd to $ORACLE_HOME/rdbms/admin
  6. $ORACLE_HOME/perl/bin/perl catctl.pl -l /home/oracle/upgrade -p 68 catupgrd.sql
    The "-p" option will restart the parallel upgrade now at phase 68. 

I also would like to thank Jason Arneil, who's Blog Post helped me a lot! 


Since Feb 23, 2015, there's a generic one off patch available on top of Oracle 12.1.0.2:

Patch 19664340: ORA-20000: UNABLE TO GATHER STATISTICS CONCURRENTLY: RESOURCE MANAGER IS NOT ENA

And please see also the COMMENT section with the comments by Arun Gupta - thanks a lot, Arun!

-Mike 

Tuesday Oct 07, 2014

Upcoming UPGRADE Workshops in:
Netherlands, Ireland, Northern Ireland, Spain and Belgium

Wow ... another OOW is over ... I have returned well but a bit jetlaged on Saturday from San Fransciso. It was a pleasure to meet with so many great customers, partners, Oracle ACEs, geeks and of course colleagues from all over the world.

And I'm still surprised how little DBAs and managers are aware of the upcoming end of Premier Support for Oracle Database 11.2 and still believe in "The 2nd Release Only". But I'll write something about this later this or next week.

And as soon as my time allows (most likely in the 2nd half of October) I will publish also the details about how to setup the HOL by yourself as we are not allowed to distribute it because of the patch sets included into it. 

Anyhow, back in reality (still on vacation today) let me offer you the final seats for the upcoming UPGRADE, MIGRATE & CONSOLIDATE TO ORACLE DATABASE 12c workshops in the following weeks: 

In case you'll miss links I'll update the blog post as soon as I get them.

-Mike 

Monday Sep 22, 2014

New AQ Background Architecture in Oracle 12c

Advanced Queing

I know a some really high profile customers making heavy use of Oracle Advanced Queuing. Now in Oracle Database 12c there's a change in the background architecture of AQ. The doc describes it more clearly than I ever could:

As far as I can see there shouldn't be any backwards compatibility issues. But (as always) I'd be happy to hear, read and learn about your experiences :-) 

And see a list of changes, additions, enhancements and new features of AQ here:


-Mike 

Thursday Sep 04, 2014

OOW 2014 - Upgrade and Data Pump Talks

Oracle Open World (OOW) 2014 in San Francisco is coming ... just a few weeks to go ... everybody is in the prep phase for demos, presentations, labs etc. 

If you'd like to get in touch with us to discuss your upgrade and migration strategies please feel free to contact either Roy Swonger or myself directly. We'll be happy to assist you. And of course you are welcome to stop by at our combined Upgrade/DataPump booth at the demo grounds and visit one of our talks.

Our group is happy to deliver the following talks and labs:

How to Upgrade, Migrate, and Consolidate to Oracle Database 12c [CON7647]
Monday, Sep 29, 5:15 PM - 6:00 PM - Moscone South - 102

The most widely anticipated feature of Oracle Database 12c is now available, and you may be wondering just how you can move your current databases to pluggable databases in a multitenant architecture. Whether you are just starting to explore the world of pluggable databases or are planning a production upgrade in the near future to Oracle Database 12c, this presentation by Oracle Database upgrade and migration experts gives you all the details: what methods are available; how they work; and which is the best for your particular upgrade, migration, or consolidation scenario.

How an Oracle Database 12c Upgrade Works in a Multitenant Environment [CON7648]
Tuesday, Sep 30, 12:00 PM - 12:45 PM - Moscone South - 306

With the first patch set of Oracle Database 12c, you will be able to choose between various methods of upgrading a multitenant container database and its pluggable databases. In this session, you will hear from Oracle upgrade experts about all the details of how a database upgrade works in a multitenant environment. You will learn what your options are, how parallelism works for database upgrades, and what is new for database upgrades in the first patch set of Oracle Database 12c.

How and Why to Migrate from Schema Consolidation to Pluggable Databases [CON7649]
Wednesday, Oct 1, 11:30 AM - 12:15 PM - Moscone South - 306

One important use case for pluggable databases is to enable you to move from schema consolidation with multiple applications in the same database to a more secure environment with Oracle Multitenant and pluggable databases. In this technical session, you will hear from Oracle development experts about the methods available for migrating from schema consolidation to a multitenant database environment with Oracle Data Pump, transportable tablespaces, or new features in Oracle Multitenant.

Oracle Database 12c Upgrade: Tools and Best Practices from Oracle Support [CON8236]
This talk is not done by us but by our Global Tech Lead for Upgrades in Support, Agrim Pandit
Tuesday, Sep 30, 5:00 PM - 5:45 PM - Moscone South - 310

You’ve heard about Oracle Database 12c and its new capabilities. Now come hear from Oracle experts about all the great tools and resources Oracle offers to help you upgrade to Oracle Database 12c efficiently and effectively. This session’s presenters, from Oracle Support, bring years of database experience and recent lessons learned from Oracle Database 12c upgrades at companies of all sizes all around the world. You are sure to leave with valuable information that will help you plan and execute your upgrade. What's more, most, if not all, of the tools and resources they discuss are available to current customers at no additional cost through their standard product support coverage.

.

I will publish the schedules for the Hands-On-Lab (4x) and the location of the demo ground's booth as soon as I'll get it.

-Mike

Tuesday Sep 02, 2014

Unified Auditing - is it ON or OFF in Oracle 12c?

Don't trust our slides - only believe what you've verified by yourself ;-)

Actually one of our slides gives a parameter recommendation to set AUDIT_TRAIL since Oracle 11g explicitly to the value you want as otherwise it may switch to "DB" and you may not be aware of it. In conjunction with this setting we explain the new Oracle Database 12c feature Unified Auditing - which is not linked into the kernel and therefore should be off.

Should be ... well ... thanks to Marco Patzwahl who asked me why he still has over 100 audit records in V$UNIFIED_AUDIT_TRAIL? Good question - and I've had no answer. But Carol, my manager, knew the right person to ask. And Naveen replied within minutes (thanks!!!).

Here are the facts: 

  • Unified Auditing is not linked into the Oracle 12c kernel by default to offer people the choice to use it and to avoid conflicts in case somebody has auditing ON already - so neither during an upgrade nor with a fresh 12c database you'll see it included into the kernel. It will have to be linked in manually (see our slides)
    • Check if Unified Auditing is present in your environment:
      SQL> select VALUE from V$OPTION where PARAMETER='Unified Auditing';
    • In case you'll link it into the kernel
      cd $ORACLE_HOME/rdbms/lib
      make -f ins_rdbms.mk
      uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

      make sure you set AUDIT_TRAIL=NONE afterwards as otherwise both auditing mechanisms will run concurrently
  • But even though it is not linked into the kernel a bit of Unified Auditing is ON by default in MIXED MODE when you create a fresh Oracle 12c database.
    • MIXED MODE auditing?
      • See the documentation for further information
      • Just two policies are enabled by default: ORA_SECURECONFIG and ORA_LOGON_FAILURES
  • Turn Unfiied Auditing OFF?
    • If is has been linked in into the kernel, unlink it:
      cd $ORACLE_HOME/rdbms/lib
      make -f ins_rdbms.mk 
      uniaud_off ioracle ORACLE_HOME=$ORACLE_HOME
    • Disable the two default policies - this will turn off any Unified Auditing features:
      SQL> noaudit policy ORA_SECURECONFIG;
      Noaudit succeeded.
      SQL> noaudit policy ORA_LOGON_FAILURES;
      Noaudit succeeded.


-Mike

Friday Aug 22, 2014

Grid Infrastructure Management Repository (GIMR)
database now mandatory in Oracle GI 12.1.0.2

During the installation of Oracle Grid Infrastructure 12.1.0.1 you've had the following option to choose YES/NO to install the Grid Infrastructure Management Repository (GIMR) database MGMTDB:

With Oracle Grid Infrastructure 12.1.0.2 this choice has become obsolete and the above screen does not appear anymore. The GIMR database has become mandatory

What gets stored in the GIMR?

See the changes in Oracle Clusterware 12.1.0.2 here:

  • Automatic Installation of Grid Infrastructure Management Repository

    The Grid Infrastructure Management Repository is automatically installed with Oracle Grid Infrastructure 12crelease 1 (12.1.0.2). The Grid Infrastructure Management Repository enables such features as Cluster Health Monitor, Oracle Database QoS Management, and Rapid Home Provisioning, and provides a historical metric repository that simplifies viewing of past performance and diagnosis of issues. This capability is fully integrated into Oracle Enterprise Manager Cloud Control for seamless management.

Furthermore what the doc doesn't say explicitly:

  • The -MGMTDB has now become a single-tenant deployment having a CDB with one PDB
    • This will allow the use of a Utility Cluster that can hold the CDB for a collection of GIMR PDBs
  • When you've had already an Oracle 12.1.0.1 GIMR this database will be destroyed and recreated
    • Preserving the CHM/OS data can be acchieved with OCULMON to dump it out into node view
  • The data files associated with it will be created within the same disk group as OCR or VOTING
    •  The OUI will get the disk groups for OCR and Voting and chooses the first one - which usually is the first OCR. This may lead to serious space issues. It is tracked internally as Bug:19661882  In a future release there may be an option offered to put in into a separate disk group.
      Workaround would be to move the affected OCR to another disk group (use ocrconfig command for it) - see MOS Note:1589394.1
  • Some important MOS Notes:
    • MOS Note 1568402.1
      FAQ: 12c Grid Infrastructure Management Repository, states there's no supported procedure to enable Management Database once the GI stack is configured
    • MOS Note 1589394.1
      How to Move GI Management Repository to Different Shared Storage
      (shows how to delete and recreate the MGMTDB)
    • MOS Note 1631336.1
      Cannot delete Management Database (MGMTDB) in 12.1
  • Average growth size per day per node is roughly 650-750 MB. E.g. a 4 node cluster would lead at the default retention of 3 days to an approximate size of  5.9-6.8 GB
  • Change the retention:
    $CRS_HOME/bin/oclumon manage -repos checkretentiontime 260000

-Mike

PS: Kudos to Sebastian Solbach who updated me on the things to add (retention, average growth, OUI choosing the first disk group displayed for the MGMTDB) - cheers!

Thursday Aug 14, 2014

RMAN Catalog requires Enterprise Edition (EE)
since Oracle Database 12.1.0.2

Credits go to Cameron Hodge, Malcom and Martin Mäs who all highlighted issues to me following my previous entry about RMAN - and sorry for any disappointment but I wasn't aware of all these nice little things. 

Ok, you'd upgrade your RMAN Catalog to be ready to backup/recover Oracle Database 12.1.0.2 databases and you see this error:

RMAN> upgrade catalog;

error creating create_deleted_object_seq
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-06004: ORACLE error from recovery catalog database: ORA-00439: feature not enabled: Partitioning

Now you start to wonder as your Catalog Database had been an Oracle Standard Edition (SE) database for quite a while - and of course an SE database does not have Partitioning on. And on the side the Oracle Partitioning Option is licensable option.

First of all this new behavior gets introduced with Oracle Database 12.1.0.2. And as far as I know it is not documented in relation to the "upgrade catalog". 

The valid workaround leads to a new feature called Infrastructure Repository Database - which is always an EE database without the need for extra licensening as all feature will be used only by Oracle internal mechanisms. 

Licensing doc:
http://docs.oracle.com/database/121/DBLIC/editions.htm#DBLIC119

Infrastructure Repository Databases

A separate single instance Oracle Database can be installed and used as an infrastructure repository for RMAN, Oracle Enterprise Manager Cloud Control, Automatic Workload Repository (AWR) Warehouse, Global Data Services Catalog, and Grid Infrastructure Management Repository without additional license requirements, provided that all the targets are correctly licensed. It may not be used or deployed for other uses.

The infrastructure repositories for RMAN, Oracle Enterprise Manager Cloud Control, AWR Warehouse, and Global Data Services Catalog can be shared in one database, or deployed in separate databases. Enterprise Edition must be used for the infrastructure repository database(s).

Furthermore the Recovery Manager documentation mentions this:

Creating and Managing Virtual Private Catalogs with Oracle Database 12c Release 1 (12.1.0.2)

Note:

Starting with Oracle Database 12c Release 1 (12.1.0.2), virtual private catalogs can be used only with the Enterprise Edition of the Oracle Database. This functionality is not supported for the Standard Edition.

Now this is a bit misleading as most of you won't use the Virtual Private Catalogs - but even though you may not use it still some of the functionality is in your catalog already. And therefore every RMAN catalog beginning with Oracle Database 12.1.0.2 must be hosted in an Enterprise Edition database which does not require an EE license.

The next question is:
How do I migrate my catalog into an EE database?

There are two options whereas I'd prefer the one mentioned in the RMAN documentation:

And there are more things to mention:

  • DBMS_LOB package must be present
  • UTL_HTTP must be present

.

To summarize to successfully upgrade your RMAN catalog:

  • You need to execute this script before upgrading the catalog:
    SQL> @$ORACLE_HOME/rdbms/admin/dbmsrmansys.sql
  • Your database hosting the RMAN Catalog beginning with Oracle 12.1.0.2:
    • Must be an Enterprise Edition database - no EE license required
    • Oracle Partitioning Option must be installed - no Partitioning lincese required
    • DBMS_LOB and UTL_HTTP packages must be present

-Mike                  

PS:  Credits for this addition go to Malcom!!! In addition to RMAN Catalog Database now requiring to be in an EE database this database will require the Partitioning Option linked in as well. See MOS Note:1927265.1

Tuesday May 13, 2014

More than one PDB in the same directory?

Can you create more than one pluggable database (PDB) within the same directory?
And how does the file naming work? Considering the fact each PDB's SYSTEM tablespace will be named system01.dbf by default the question is not trivial. 

This question got asked by a customer during one of the workshops in Switzerland last week. And the solution is straight forward. Thanks to Roy for trying it out yesterday at 170 km/h on our way back from Stuttgart :-)

Thanks :-)

-Mike 

Additional information:

Within ASM with OMF the file structure looks like this:

 1  select con_id, substr(file_name,1,90),tablespace_name from cdb_data_files
  2* order by 1

    CON_ID SUBSTR(FILE_NAME,1,90)                                                           TABLESPACE_NAME
---------- -------------------------------------------------------------------------------- ---------------
         1 +DA1/CDBUPGR/DATAFILE/system.394.845632641                                       SYSTEM
         1 +DA1/CDBUPGR/DATAFILE/users.475.845632685                                        USERS
         1 +DA1/CDBUPGR/DATAFILE/undotbs4.448.845632683                                     UNDOTBS4
         1 +DA1/CDBUPGR/DATAFILE/sysaux.392.845632651                                       SYSAUX
         1 +DA1/CDBUPGR/DATAFILE/undotbs2.393.845632679                                     UNDOTBS2
         1 +DA1/CDBUPGR/DATAFILE/undotbs1.471.845632657                                     UNDOTBS1
         1 +DA1/CDBUPGR/DATAFILE/undotbs3.478.845632681                                     UNDOTBS3
         2 +DA1/CDBUPGR/F7B70DCBF2D4ECEAE0437A28890AE4D8/DATAFILE/sysaux.472.845632655      SYSAUX
         2 +DA1/CDBUPGR/F7B70DCBF2D4ECEAE0437A28890AE4D8/DATAFILE/system.398.845632647      SYSTEM
         3 +DA1/CDBUPGR/F6A142792168D540E0437A28890A4707/DATAFILE/system.493.845643325      SYSTEM
         3 +DA1/CDBUPGR/F6A142792168D540E0437A28890A4707/DATAFILE/sysaux.468.845643325      SYSAUX
         3 +DA1/CDBUPGR/F6A142792168D540E0437A28890A4707/DATAFILE/soets.452.845643325       SOETS
         4 +DA1/CDBUPGR/F7B9BDC2AEC4411EE0437A28890A2B81/DATAFILE/system.491.845643937      SYSTEM
         4 +DA1/CDBUPGR/F7B9BDC2AEC4411EE0437A28890A2B81/DATAFILE/sysaux.488.845643937      SYSAUX
         4 +DA1/CDBUPGR/F7B9BDC2AEC4411EE0437A28890A2B81/DATAFILE/soets.484.845643937       SOETS
         5 +DA1/CDBUPGR/F7B9CA6B92804A56E0437A28890A2721/DATAFILE/system.485.845644149      SYSTEM
         5 +DA1/CDBUPGR/F7B9CA6B92804A56E0437A28890A2721/DATAFILE/sysaux.490.845644149      SYSAUX
         5 +DA1/CDBUPGR/F7B9CA6B92804A56E0437A28890A2721/DATAFILE/soets.487.845644149       SOETS
         6 +DA1/CDBUPGR/F7B9D727715B5B4AE0437A28890AB3D9/DATAFILE/system.486.845644363      SYSTEM
         6 +DA1/CDBUPGR/F7B9D727715B5B4AE0437A28890AB3D9/DATAFILE/sysaux.483.845644363      SYSAUX
         6 +DA1/CDBUPGR/F7B9D727715B5B4AE0437A28890AB3D9/DATAFILE/soets.481.845644363       SOETS
         7 +DA1/CDBUPGR/F7B9E3D23CFC67F1E0437A28890A5A68/DATAFILE/system.453.845644575      SYSTEM
         7 +DA1/CDBUPGR/F7B9E3D23CFC67F1E0437A28890A5A68/DATAFILE/sysaux.482.845644575      SYSAUX
         7 +DA1/CDBUPGR/F7B9E3D23CFC67F1E0437A28890A5A68/DATAFILE/soets.467.845644575       SOETS
         8 +DA1/CDBUPGR/F7B9F051E81B7892E0437A28890AD3A3/DATAFILE/system.465.845644785      SYSTEM
         8 +DA1/CDBUPGR/F7B9F051E81B7892E0437A28890AD3A3/DATAFILE/sysaux.455.845644785      SYSAUX
         8 +DA1/CDBUPGR/F7B9F051E81B7892E0437A28890AD3A3/DATAFILE/soets.479.845644785       SOETS
         9 +DA1/CDBUPGR/F7BA2D0F2F17A755E0437A28890A72C6/DATAFILE/system.464.845645805      SYSTEM
         9 +DA1/CDBUPGR/F7BA2D0F2F17A755E0437A28890A72C6/DATAFILE/sysaux.500.845645805      SYSAUX
         9 +DA1/CDBUPGR/F7BA2D0F2F17A755E0437A28890A72C6/DATAFILE/soets.498.845645805       SOETS
        10 +DA1/CDBUPGR/F7BA3A179DAFB12FE0437A28890ABBF3/DATAFILE/system.499.845646023      SYSTEM
        10 +DA1/CDBUPGR/F7BA3A179DAFB12FE0437A28890ABBF3/DATAFILE/sysaux.504.845646023      SYSAUX
        10 +DA1/CDBUPGR/F7BA3A179DAFB12FE0437A28890ABBF3/DATAFILE/soets.502.845646023       SOETS
        11 +DA1/CDBUPGR/F7BA46A1A6B7B9C2E0437A28890AE021/DATAFILE/system.503.845646233      SYSTEM
        11 +DA1/CDBUPGR/F7BA46A1A6B7B9C2E0437A28890AE021/DATAFILE/sysaux.508.845646233      SYSAUX
        11 +DA1/CDBUPGR/F7BA46A1A6B7B9C2E0437A28890AE021/DATAFILE/soets.506.845646233       SOETS
...


About

Mike Dietrich - Oracle Mike Dietrich
Senior Principal Technologist - Database Upgrade Development Group - Oracle Corporation

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

Contact me either via XING or LinkedIn

Search

Archives
« March 2015
SunMonTueWedThuFriSat
1
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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