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

Downgrade Oracle Restart 12c - Grid Infrastructure only?

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

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

Basically it is: 

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

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

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

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

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

This is from the documentation for Oracle 12.1.0.2

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

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

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

--Mike

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

Monday Feb 16, 2015

Oracle Fail Safe 4.1.1 released - supports Multitenant

Oracle Fail SafeOracle Fail Safe 4.1.1 is now released. It will be included in future Oracle Database 12c media packs.  Customers can download the kit and documentation from the Oracle Technology Network (OTN) now by starting at the main Fail Safe page.  

The main changes in this release are:

  • Basic multi-tenant (container database, or CDB) support
  • This release of Oracle Fail Safe adds support for the container database feature that was introduced in Oracle Database 12c. Fail Safe will recognize that a database is a root container and will start and stop individual pluggable databases owned by the container database. When a database is failed over or moved to another node in the cluster, Oracle Fail Safe will start each pluggable database using the state that was saved by the last SQL ALTER PLUGGABLE DATABASE ALL SAVE STATE command. Oracle database 12c patch set 1 (12.1.0.2) is required to have the ability to save the state of the pluggable databases. 

  • Improved net (TNS) configuration
  • Cluster Shared Volume (CSV) support

 All documentation for this release, including the release notes, can be found here.

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

 

 

Monday Feb 09, 2015

Virtual Technology Summit with Hands-On Labs!

VTS Virtual Technology Summit

If you are at all interested in upgrade to Oracle Database 12c, you really should sign up for the Virtual Technology Summit for February. This is a "pseudo-live" event with recorded webcasts and live experts online to answer questions. Even better, it includes a hands-on lab that will take you through the process of:

  1. Upgrading from 11.2.0.4 to 12.1.0.2
  2. Plugging that upgraded database in as a PDB
  3. Migrating a second 11.2.0.4 database to a second PDB using the new Full Transportable Export/Import feature

The Virtual Technology Summit will run three times: 

and it has content for developers and sysadmins as well as DBAs.

So, sign up now and get a head start by downloading the hands-on lab VM image here:

We think you will find the VM environment really useful to keep around for those times when you want to try something out, without having to install binaries or create databases. 

-Roy 

Friday Feb 06, 2015

New MOS Note:1962125.1 - Overview of Database Patch Delivery Methods

Usually I don't announce MOS Notes but this one may be very helpful to sort out between all the different patches available these days for the database only. From Patch Sets to PSUs to SPUs to Interim Patches to Bundle Patches and so on.

Plus it includes also recommendations for testing and if you should apply them on a regular basis.

A very important MOS Note for most DBAs:

MOS Note:1962125.1
Overview of Database Patch Delivery Methods

MOS Note - Patch Delivery Methods

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

Wednesday Jan 21, 2015

Oracle Supplied Objects Listing in upobjxt.lst ... ... Oracle Supplied Users Listing in upuserxt.lst

Maybe not important to many people but as I didn't find much about it via a search I'd thought I post something about the new upobjxt.lst and upuserxt.lst files. Since Oracle Database 12.1.0.1 we deliver these files in ?/rdbms/admin .

All Oracle supplied objects are kept in an external table format.
The file's name is: upobjxt.lst

If you'd like to access it you may do the following:

  1. Create a directory object:
    CREATE OR REPLACE DIRECTORY ext_tab_data AS '/u01/app/oracle/product/12.1.0.2/rdbms/admin/';
    --
  2. Define the external table to query it:
    CREATE TABLE oracle_supplied_ext (
      owner       VARCHAR2(31),
      name        VARCHAR2(127),
      placeholder VARCHAR2(20),
      object_type number
    )
    ORGANIZATION EXTERNAL (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY ext_tab_data
      ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL
        (
          owner             CHAR(31),
          name              CHAR(127),
          placeholder       CHAR(20),
          object_type       CHAR(2)
        )
      )
      LOCATION ('upobjxt.lst')
    )
    PARALLEL 5
    REJECT LIMIT UNLIMITED;

    --
  3. Now you can query from the table oracle_supplied_ext
    For example:
    select count(*), object_type
    from oracle_supplied_ext
    group by object_type
    order by 2;
    --
  4. Would you like to match object types to object codes? Try this one  ...:
    select count(o.type#), o.type#,d.object_type
     from dba_objects d, obj$ o
     where d.object_id=o.obj#
      group by o.type#,d.object_type
      order by 2;
    Object mapping can be found also in the definition of OBJ$ in the dcore.bsq file

All Oracle supplied users are kept in an external table format in another file named: upuserxt.lst

-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 

Tuesday Dec 30, 2014

Happy New Year, Complete with New Upgrade Workshops!

Oracle Database 12c Upgrade Workshop banner

With the New Year arriving in a couple of days, it is a good time to flip the page on the calendar and make plans to attend an Oracle Database Upgrade workshop! The first two workshops will be:

Denver on Wednesday, January 7 (register here)

Chicago on Thursday, January 8 (register here)

I hope to see you (and you...and you too!) at one of these events next week!

Tuesday Dec 23, 2014

Upgrading ORACLE RESTART from 11.2 to 12.1

First of all, Oracle Restart is deprecated in Oracle Database 12c. Deprecation means the product is still supported, you can open SRs, you'll get bug fixes - but we won't do further development of the product or feature.

But a customer (Thanks to Jaco de Graaf for bringing this to my attention!) had a question via the blog about the best approach to upgrade his Oracle Restart configuration from Oracle 11.2.0.3 to Oracle 12.1.0.2 - and apply the October PSU as well.

What is Oracle Restart?
Please find an overview here in the doc. 
.

How to upgrade your Oracle Restart configuration?
The standard way is simple: Choose "Upgrade" in the starting page of the Grid Infrastructure's OUI screen.
Grid Infrastructure OUI Upgrade Oracle Restart
You won't see anything saying "Upgrade Stand Alone Installation" or "Upgrade Oracle Restart" - but the general "Upgrade ..." option will cover this topic as well.
.

Upgrade Oracle Restart and include a PSU (Patch Set Update)?
This is a bit more tricky and as far as I see not documented entirely. But Jaco did open an SR and Support could help.

  1. Install 12.1 GI ‘software only’
  2. Patch that software set to the latest PSU (using: opatch apply ….-local)
  3. Remove the Oracle Restart configuration (using the ‘old’ 11.2 GRID_HOME)
    Please find a description on the excellent blog of my support colleague, Helmut Hutzler:
    http://www.hhutzler.de/blog/reconfigure-12-1-0-2-has-stack-running-11-2-0-4-database/
    or in MOS Note:986740.1
    It is important to mention that you need to use the 'old' GI Home to remove the Restart configuration - the MOS Note doesn't say this explicitlely.
  4. Reconfigure the Oracle Restart configuration (using the ‘new’ 12.1 GRID_HOME)
  5. Add all required items back to the Oracle Restart stack (ASM, Listener, databases).

Steps 3, 4, and 5 will require a bit of downtime. 
.

-Mike 

---

Added MOS Note 1584742.1Support Impact of the Deprecation Announcement of Oracle Restart with Oracle Database 12c on Feb 22, 2015

Wednesday Dec 03, 2014

Upgrade Webcast on Thursday, December 4!

CORRECTED LINK:
http://w.on24.com/r.htm?e=899022&s=1&k=04C11FDCF30A6B7255FA6EF05BEBD5BC 

Upgrade NOW! Banner

At this time of year the saying goes that it is better to give than to receive, and I am very excited to be GIVING a webcast about upgrade to Oracle Database 12c on Thursday, December 4 at 8:00 AM Eastern / 14:00 CET. Yes, that is TOMORROW. The registration link is right here (or click on the image above).

I will be talking about why to upgrade, how to upgrade, and also about some of the new features about which you may not have heard. There is still time to register and participate, so I hope to see you online!

Sunday Nov 30, 2014

VBOX Hands-on-Lab image - build your own :-)

Oh ... I know ... I promised to post all the details how I've build up our pretty straight forward Hands-On-Lab Roy, Carol, Cindy, Joe and I used at OOW and some other occasions to let you upgrade, migrate and consolidate databases to Oracle Database 12c and into Oracle Multitenant.

And well, some have emailed me already ... and I had this feeling that my schedule will be very tight after OOW. Even right now (Sunday evening) I'm already back at my second home, Lufthansa Senator Lounge at Munich Airport. Waiting for my flight to Rome in an hour or so. Honestly speaking I had really no time in the past weeks to sit down for 2 hours to write down all the steps to guide you through the rebuild. And I didn't want to throw just a few nuggets - my intention is always to get you detailed steps which really work and don't miss anything.

But I have very good news for all who are waiting for the HOL Image :-)
Roy is working hard (and I'm confident that he'll succeed) to get the image published on OTN within the next weeks. So please stay tuned. Even with the Christmas holidays coming up I'm tied into a schedule to visit Rome, Torino, Milan, Brussels, assist some customers in their final go-live-phase for Oracle Database 12c - and I'm really looking forward to that vacation.

Stay tuned - and thanks again for your patience :-)

-Mike 

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

Wednesday Nov 05, 2014

Premier Support for Oracle 11.2 will end soon ...
Upgrade to Oracle Database 12c now!

Most customer and partners know it already - but still these days I get emails about upgrading from Oracle 10.2 to Oracle Database 11.2.0.4. Well, I have stopped arguing with people as there may be a reason to invest money and resources into a release which will go out of Premier Support in less than 3 months.

Some clarifications:

  • End of Premier Support means that Oracle will not offer fixes for new issues unless an extra-cost offer called Extended Support has been purchased. But you'll still get Sustaining Support until you'll stop working on the release. For a description what terms such as Premier Support, Extended Support and Sustaining Support mean please refer to the Lifetime Support Policy.
    .
  • For Oracle Database 11.2.0.4 - the Terminal (final) Patch Set for Oracle Database 11.2 - Oracle will waive (no extra cost, no action required) the first year of Extended Support to every customer with a valid support contract. Premier Support for Oracle Database 11.2 will still end on Jan-31, 2015, but you'll get Extended Support for Oracle Database 11.2.0.4 for one additional year at no extra cost. You can extend the Extended Support for Oracle Database 11.2.0.4 by a maximum of two additional years until 31-JAN-2018 - but then at the usual extra cost. 
    This information can be found in MOS Note:742060.1.
    .
  • For Oracle Database 11.2.0.3 Oracle will waive also Extended Support, but as Extended Support for Oracle Database 11.2.0.3 will end on 27-AUG-2015 no bug fixes will be available (including PSUs, SPUs and BPs) after that date. There's no offer to extend Extended Support for Oracle Database 11.2.0.3 upon that date.
    This information can be found in MOS Note:742060.1.
    .
  • For Oracle Database 11.2.0.2 there's no such Extended Support offering available.
    This information can be found in MOS Note:742060.1.
    ..
  • For Oracle Database 11.2.0.1 there's no such Extended Support offering available.
    This information can be found in MOS Note:742060.1.
    .

If you are interested in more details about Upgrade, Migrate and Consolidate to Oracle Database 12c please see the refreshed version (31-OCT-2014) of our slide deck available via the Slides Download Center to your right. 

-Mike 

Thursday Oct 23, 2014

Maintenance Windows is too small? Autotask Jobs fail

Since Oracle Database 10g we have automatic maintenance jobs in the database running. Since Oracle Database 11g (and the same applies to 12c) we have:

  • Weekday Jobs being able to get executed from 10pm until 2am on MON-FRI
  • Weekend Jobs being able to get executed from 6am until 2am on SAT-SUN

A support colleague contacted me a while ago asking why at his customer the jobs don't start anymore as they have set the duration for weekdays to 15 minutes. You may adjust the window generally by yourself with:

  • exec DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW','DURATION',numtodsinterval(2,'hour'));

But in this particular case only changing the default setting of _autotask_min_window=14 helped. That ensured that - once the job window was set to as little as 15 minutes that Automatic Maintenance Jobs still get executed. Once you adjust your Maintenance Windows making them really small ensure that you'll adjust this little underscore as well.

-Mike 

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 

Wednesday Oct 15, 2014

PSU October 2014

October 14, 2014 Oracle released a new Patch Set Update. And as usual we strongly recommend that you'll apply it as soon as possible to your databases.  

There's one fix for JAVA in it which requires to either take the database down or do some other actions. Please find a detailed description here: 

  • Eric Maurice’s security blog:
    https://blogs.oracle.com/security/entry/october_2014_critical_patch_update
    "Due to the nature of the fixes required, Oracle development was not able to produce a normal RAC-rolling fix for these issues. To help protect customers until they can apply the Oracle JavaVM component Database PSU, which requires downtime, Oracle produced a script that introduces new controls to prevent new Java classes from being deployed or new calls from being made to existing Java classes, while preserving the ability of the database to execute the existing Java stored procedures that customers may rely on."
  • MOS:1929745.1 - "Oracle JavaVM Component Database PSU"
-Mike 

Friday Sep 26, 2014

Best Practices for Patching -- learn more at OOW!

Although upgrade and patching are truly two very different processes, they both fall into the same general category of planned maintenance and are often associated with each other. So, it seems relevant to point out yet another important talk at OpenWorld in San Francisco:

Database Patching Best Practices II [CON7748]

  • Tuesday, Sep 30, 5:00 PM - 5:45 PM - Moscone South - 104

Building on a highly popular session from Oracle OpenWorld 2013, this 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 roll out patches in cloud environments, and more. The presentation also shares the latest Oracle Database 12c features and tooling to help ease patching processes. 

This is a great time and place to hear from the patching gurus who can tell you how to maintain your systems with minimal downtime and how to take advantage of some tools, techniques, and information about which you might not be aware. Hope to see you there! 

Tuesday Sep 16, 2014

Oracle Database Documentation - From Past to Present

Yesterday one of my colleagues, well known Data Guard Guru Larry "Murphy" Carpenter, sent out an internal email with links to older and recent Oracle Database Documentation. Reason why you should use the below links: Some sites in the www have stored copies of the doc - but some may represent not the most recent state or may not be complete. So stay with the official Oracle docs only:

-Mike

PS: For those who haven't upgraded for the past 17 years or for those with nostalgic feelings this is the link to the Oracle 7.3.4 documentation :-)

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

About

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

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

Follow me on TWITTER

Contact me via LinkedIn or XING

Search

Archives
« July 2015
SunMonTueWedThuFriSat
   
4
5
7
8
10
11
12
15
16
18
19
21
22
23
25
26
27
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