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! 

Wednesday Sep 24, 2014

OOW 2014: Focus on Upgrades/Migrations

For our presentations and others around the topic of database upgrades and migrations please see our "Focus On ..." listening - but there are also many others for different topics collected by the responsible Product Managers:

The whole set of "Focus On" documents that will help you find sessions about any given topic?  For example, you could take a look at

Focus on Database Upgrade

or

Focus on Database Utilities: Data Pump, SQL*Loader, Transportable Tablespaces

I hope you will find these useful as you plan what is sure to be a very, VERY busy week!  

-Roy 

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 

Friday Sep 19, 2014

Highly Recommended OOW Sessions

Here's a list of highly recommended OOW sessions apart from our own talks related to upgrades and migrations: 

-Mike 

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

Tuesday Sep 09, 2014

OOW 2014 - Upgrade Hands-On-Lab at NIKKO HOTEL

This year's Oracle Open World will be a real challenge logistically for us. Non-privileged employees were assigned to airport hotels only. So Roy, Joe, Dean and I stay >16 miles (>25km) away from Moscone Center. And to make things even more complicated due to some reconstruction at the Moscone Center (I hope they'll get rid of their 1990s audio equipment and such) our Hands On Labs for instance are at the NIKKO Hotel, not at the Marriott Marquee anymore.

These are the dates for our HOL about How to upgrade to Oracle Database 12c and plug into Oracle Multitenant:

  • Monday , Sep 29 - 11:45 AM in Hotel Nikko - Bay View (0/32)
  • Tuesday , Sep 30 - 5:15 PM in Hotel Nikko - Bay View (0/32)
  • Wednesday, Oct 01 - 4:15 PM in Hotel Nikko - Bay View (0/32)
  • Thursday , Oct 02 - 8:30 AM in Hotel Nikko - Bay View (0/32)
Follow this link to the OOW Session Agenda - ID of the Labs is [HOL9127] - and usually you'll have to hurry as the 50 seats per lab sell out quickly.

Especially Thursday will be a tough call to be there at 8:30am in the morning. At least traffic on 101 won't be that terrible at such a night time ;-)

-Mike

Monday Sep 08, 2014

OOW Demo Booth SD-138 in Moscone South Exhib Hall

Just in case you'd like to meet with the Upgrade and/or Data Pump Team at OOW in San Francisco please come by during the Exhibition hours in Moscone South on the LEFT side of thehall - SLD-138 is the number of our booth (see the BLUE arrow in the chart below):

-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

Automatic Maintenance Jobs in every PDB?
New SPM Evolve Advisor Task in Oracle 12.1.0.2

A customer checking out our slides from the OTN Tour in August 2014 asked me a finicky question the other day:

"According to the documentation the Automatic SQL Tuning Advisor maintenance task gets executed only within the CDB$ROOT, but not within each PDB - but the slides are not clear here. So what is the truth?"

Ok, that's good question. In my understanding all tasks will get executed within each PDB - that's why we recommend (based on experience) to break up the default maintenance windows when using Oracle Multitenant. Otherwise all PDBs will have the same maintenance windows, and guess what will happen when 25 PDBs start gathering object statistics at the same time ...

The documentation indeed says:

Automatic SQL Tuning Advisor data is stored in the root. It might have results about SQL statements executed in a PDB that were analyzed by the advisor, but these results are not included if the PDB is unplugged. A common user whose current container is the root can run SQL Tuning Advisor manually for SQL statements from any PDB. When a statement is tuned, it is tuned in any container that runs the statement.

This sounds reasonable. But when we have a look into our PDBs or into the CDB_AUTOTASK_CLIENT view the result is different from what the doc says. In my environment I did create just two fresh empty PDBs (CON_ID 3 and 4):

SQL> select client_name, status, con_id from cdb_autotask_client;

CLIENT_NAME                           STATUS         CON_ID
------------------------------------- ---------- ----------
auto optimizer stats collection       ENABLED             1
sql tuning advisor                    ENABLED             1
auto space advisor                    ENABLED             1
auto optimizer stats collection       ENABLED             4
sql tuning advisor                    ENABLED             4
auto space advisor                    ENABLED             4
auto optimizer stats collection       ENABLED             3
sql tuning advisor                    ENABLED             3
auto space advisor                    ENABLED             3

9 rows selected.

I haven't verified the reason why this is different from the docs but it may have been related to one change in Oracle Database 12.1.0.2: The new SPM Evolve Advisor Task ( SYS_AUTO_SPM_EVOLVE_TASK) for automatic plan evolution for SQL Plan Management. This new task doesn't appear as a stand-alone job (client) in the maintenance window but runs as a sub-entity of the Automatic SQL Tuning Advisor task. And (I'm just guessing) this may be one of the reasons why every PDB will have to have its own Automatic SQL Tuning Advisor task 

Here you'll find more information about how to enable, disable and configure the new Oracle 12.1.0.2 SPM Evolve Advisor Task:

-Mike

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: 1921105.1
      Managing the Cluster Health Monitor Repository (incl how to resize)  
    • 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
    • MOS Note 1945558.1
      _mgmtdb Service Registered with All Local Listeners in a Grid Infrastructure Environment
  • 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 12.1.0.1:
    $CRS_HOME/bin/oclumon manage -repos changeretentiontime 260000
  • Change the retention 12.1.0.2:
    $CRS_HOME/bin/oclumon manage -repos checkretentiontime 260000


Update:

Markus Michalewicz, our Director of Product Management, Oracle Real Application Clusters (RAC), has published a very interesting and helpful insight article about GIMR on July 30, 2015. Read it here:
https://www.linkedin.com/pulse/how-handle-oracle-gimr-markus-michalewicz


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

Monday Aug 11, 2014

RMAN Catalog Upgrade to Oracle 12.1.0.2

It sounds so simple - but in this specific case a bit of doc reading is required as upgrading the RMAN catalog to handle Oracle 12.1.0.2 databases is not as trivial as in the past. 

Thanks to a German customer and a friendly colleague from Sales Consulting in Stuttgart I have learned my RMAN lesson for Oracle Database 12.1.0.2 this week. A simple "upgrade catalog" is not the correct step once you'd like to handle backups of Oracle 12.1.0.2 databases in your current catalog schema. 

Even though you may not have ever heard before about Virtual Private Catalogs you need to follow this guideline:

http://docs.oracle.com/database/121/BRADV/rcmcatdb.htm#BRADV848

The doc tells you to run this step first:

SQL> @$ORACLE_HOME/rdbms/admin/dbmsrmansys.sql

  • Please ignore the "-all" option printed in the doc - this is a known docu bug
  • If you miss this step the upgrade of the catalog will fail with a warning that your user lacks privileges 

$ rman CATALOG my_catalog_owner@catdb
recovery catalog database Password: 
RMAN> UPGRADE CATALOG; 
RMAN> EXIT; 

It should work now :-)

-Mike 

Friday Aug 08, 2014

Slides for the OTN LAOUC 2014 Tour

Roy has done the OTN Tour 2014 in Brazil, Peru and Chile this week - and I will fly out on the weekend towards Argentina to present at the OTN Tour in Buenos Aires, and afterwards in Montevideo in Uruguay.

In case you would like to download the slide decks (even though we would recommend to download the big slide deck) please find them in the Slides Download Center to the right.

CU next week (if I can unfold my legs after an almost 14 hour flight with my dear friends from Lufthansa)!

-Mike 

Wednesday Aug 06, 2014

Upgrade PDBs - Everything At Once (Full CDB Upgrade)

As referred to it before there are two techniques to upgrade an Oracle Multitenant environment:

In this post I will explain the method of "Everything At Once" and describe all the steps. The benefit of this approach is simplicity and ease of maintenance. In an upgrade workshop in Melbourne earlier this year a DBA from Germany came by in one of the breaks explaining that he takes care on over 100 developer databases - and it would ease his life a lot if he could "hit" all of the databases at the same time with one patch set or PSU, or even upgrade them to a higher release. This is the use case where it will make a lot of sense to leverage from this approach. But be aware and don't over-consolidate as the pain point is common downtime. If you plan to use this approach you need to check before if your application owners can agree on common downtime windows - otherwise you may end in trouble. Big trouble!

The technique is easy to describe:

  • CDB$ROOT will always get upgraded first - I call this CYCLE 1
    • The "-n" parameter of catctl.pl will define how many parallel workers run the upgrade - 8 is the current maximum
    • The "-M" option will decide whether the CDB$ROOT stays in UPGRADE mode throughout the entire process of upgrade or becomes available for access after being upgraded leading to the fact that PDBs become available for access as well once they got upgraded - set "-M" and the CDB$ROOT will stay in UPGRADE mode throughout the entire process
  • Afterwards we can upgrade multiple PDBs (including the PDB$SEED) in parallel at the same time - I call this CYCLE 2,3, ...
    • The "-n" parameter (divided by 2) of catctl.pl will determine how many PDBs will be upgraded in parallel
    • Your limiting factor is CPU power and cores
    • The "-N" parameter can alter the number of parallel workers per PDB - default is 2
      .

A few simple examples will demonstrate how the parameters work hand-in-hand: 

  • CDB has (always) one PDB$SEED and 25 PDBs numbered PDB1 .. PDB25
  • That means we'll have CYCLE 1 (for the CDB$ROOT) and between one additional CYCLE 2 (for all remaining 26 PDBs in parallel) up to 27 CYCLES once you decide to have no PDBs upgraded at the same time together with another PDB
    .
  • catctl.pl -M -n 16 would lead to:
    • CYCLE 1: CDB$ROOT
    • CYCLE 2: PDB$SEED, PDB1-PDB7 (-n 16 divided by 2 = 8 PDBs to upgrade in parallel)
    • CYCLE 3: PDB8-PDB15
    • CYCLE 4: PDB16-PDB23
    • CYCLE 5: PDB24 and PDB25
    • Each PDB will be upgraded with 2 workers in parallel as -N default is 2
    • The CDB$ROOT will remain in UPGRADE mode until the last PDB is upgraded due to -M setting

  • catctl.pl -M -n 26 would lead to:
    • CYCLE 1: CDB$ROOT
    • CYCLE 2: PDB$SEED, PDB1-PDB12 (-n 26 divided by 2 = 13 PDBs to upgrade in parallel)
    • CYCLE 3: PDB13-PDB25
    • Each PDB will be upgraded with 2 workers in parallel as -N default is 2
    • The CDB$ROOT will remain in UPGRADE mode until the last PDB is upgraded due to -M setting

  • catctl.pl -n 52 -N 1 would lead to:
    • CYCLE 1: CDB$ROOT
    • CYCLE 2: PDB$SEED, PDB1-PDB25 (-n 52 divided by 2 = 26 PDBs to upgrade in parallel)
    • Each PDB will be upgraded with 1 workerl as -N is 1
    • The CDB$ROOT will be available once upgraded - same applies for PDBs once the upgrade is finished

Step-by-step instructions:

Preupgrade Steps

  • Create a guaranteed restore point in order to flashback in case of failure
    CREATE RESTORE POINT UPGRADE GUARANTEE FLASHBACK DATABASE;
  • Copy preupgrd.sql and utluppkg.sql from the Oracle 12.1.0.2 home's ?/rdbms/admin into the source Oracle 12.1.0.1 ?/rdbms/admin directoryTHIS STEP IS EXTREMLY IMPORTANT as otherwise the preupgrd.sql and the utluppkg.sql from 12.1.0.1 will be loaded into the 12.1.0.1 database - but the concept requires always to use the preupgrd.sql/utluppkg.sql from the higher version. If you fail you see it (a) hanging and (b) getting plenty of errors.
  • Execute preuprd.sql within the source database - database needs to be up and running:
    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -d $ORACLE_HOME/rdbms/admin -l /home/oracle/mike -b preupgrd preupgrd.sql
    • It will create 3 files which combine all information for the root, the seed and all pdbs together into one preupgrade.log, one preupgrade_fixups.sql and one postupgrade_fixups.sql. Default location for those files is $ORACLE_HOME/cfgtoollogs/<SID>/preupgrade
    • Verify the preupgrad.log and follow all advices
  • Execute the preupgrade_fixups.sql while all PDBs are open:
    ALTER PLUGGABLE DATABASE ALL OPEN;
    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -d $ORACLE_HOME/cfgtoollogs/cdbupgr/preupgrade -l /home/oracle/mike -b preupgrade_fixups preupgrade_fixups.sql
  • Copy the init<sid>.ora into the new $ORACLE_HOME/dbs
  • Specific stepts for RAC environments:
    • Set cluster_database=false
      ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;
    • Stop all instances
      srvctl stop database -d <db_name>

Upgrade

  • Switch to the new $ORACLE_HOME including all necessary env variables
  • Connect with sqlplus:
    sqlplus / as sysdba
  • Bring the CDB$ROOT instance into upgrade mode:
    STARTUP UPGRADE
  • Bring all PDBs into upgrade mode:
    ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;
  • Control it with:
    SHOW PDBS
    Status should be MIGRATE for all PDBs
  • Exit from SQL*Plus and cd to $ORACLE_HOME/rdbms/admin :
    EXIT
    cd $ORACLE_HOME/rdbms/admin
  • Perform the upgrade in parallel:
    $ORACLE_HOME/perl/bin/perl catctl.pl -d $ORACLE_HOME/rdbms/admin -n 16 -M -l /home/oracle/mike catupgrd.sql
  • The important file with timings per PDB and to for a quick check is called upg_summary.log and can be found in:
    $ORACLE_HOME/cfgtoollogs/<SID>/upgrade/upg_summary.log

Postupgrade Steps


  • Only in case -M hasn't been used then the CDB remains open during the upgrade of the PDBs and will need to be shutdown manually post upgrade:
    SHUTDOWN IMMEDIATE
  • Followed by a startup all PDBs must be opened now for recompilation
    STARTUP
    ALTER PLUGGABLE DATABASE ALL OPEN;
  • Execute the postupgrade_fixups.sql:
    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -d $ORACLE_HOME/cfgtoollogs/cdbupgr/preupgrade -l /home/oracle/mike -b postupgrade_fixups postupgrade_fixups.sql
  • Exit from SQL*Plus and cd into $ORACLE_HOME/rdbms/admin :
    EXIT
    cd $ORACLE_HOME/rdbms/admin
  • The recompilation is done via catcon.pl using the utlrp.sql script from within ?/rdbms/admin:
    $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
    1. utl.rp does not parallelize between PDBs
    2. Default parallel degree is: cpu_cores x 2
    3. It takes roughly 45 sec per PDB PLUS another 30 seconds to initialize XDB - as this happens serially it takes approx a while to complete recompilation past upgrade 
  • Drop the guaranteed restore point
    DROP RESTORE POINT UPGRADE;
  • In RAC environments only:
    • Set cluster_database=true again
      ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;
    • Start all instances
      srvctl start database -d <db_name>

Seems to be a lot of work, too? But in fact most of the steps are applicable to any upgrade such as the preupgrd.sql etc. Only remarkable change is the need to start scripts with catcon.pl - and not directly within SQL*Plus. Please remember that this approach will upgrade as many PDBs as you have depending on your CPU power in parallel.

In my test environment (a very outdated Exadata V1 without Flash, 6 year old disks, 2 physical CPU sockets) I upgrade 25 PDBs each roughly 25 GB in size with user data in it and all options present in less than 3 hours including recompilation. Please repeat this exercise with 25 independent databases consolidated on the same node within the same time ;-) Try it :-)

-Mike 

Tuesday Aug 05, 2014

New version of the UPGRADE 12c SLIDE DECK available

Oh ... it took a while ... but we've tried to include a bit of the new Oracle Database 12.1.0.2 stuff into the slides. And we had to refresh them to the new template which looks nice but is a bit strange to handle ;-)

So please find the new revised slide deck about:
Upgrade, Migrate & Consolidate to Oracle Database 12c

ready for download.

-Roy & Mike

Upgrade PDBs - One at a Time (unplug/plug)

*** I have added an important change on May 26, 2015 ***
***      Please see below marked in YELLOW           *** 
********************************************************

Basically there are two techniques to upgrade an Oracle Multitenant environment:

In this post I will refer to the "One at a Time" approach and describe the steps. During some presentations, discussions etc people were left with the impression that it will be a very simple approach to unplug one or many PDBs from a CDB in lets say Oracle 12.1.0.1 and plug it into an Oracle 12.1.0.2 Container Database. Bingo, upgraded!

Well, unfortunately this is not true. In fact it is completely wrong.


If you want to upgrade via unplug/plug the following steps will have to be followed:

  • In CDB1 environment - e.g. Oracle 12.1.0.1 with an PDB1
    • In SQL*Plus: 
      • alter session set container=PDB1;
      • @$ORACLE_HOME_12102/rdbms/admin/preupgrd.sql
        (The output of the preupgrade.log will show you the location of the fixups)
      • @/u01/app/oracle/cfgtoollogs/CDB1/preupgrade/preupgrade_fixups.sql
        (If ORACLE_BASE is not set the files will be created under $ORACLE_HOME/cfgtoollogs instead of $ORACLE_BASE/cfgtoollogs)
      • exec dbms_stats.gather_dictionary_stats;
        (plus include all additional treatments recommended by the preupgrade.log)
      • alter session set container=CDB$ROOT; 
      • alter pluggable database PDB1 close;
      • alter pluggable database PDB1 unplug into '/stage/pdb1.xml';
      • drop pluggable database PDB1 keep datafiles;
        The reason why you will need to DROP the PDB afterwards is simply to cleanup leftovers in the CDB views. It is under observation if this is a bug or not. The information does not get removed to allow quick plugin again but the leftovers may cause plenty of trouble once you'll try to upgrade this CDB1 later on.
      • exit
        .
  • In CDB2 environment - e.g. Oracle 12.1.0.2
    • In SQL*Plus:
      • alter session set container=CDB$ROOT;
      • At this point we "could" do a Plug In Check but as the COMPATIBLE of the new CDB2 created as per recommendation with DBCA defaults to "12.1.0.2" the Plug In Check will result in "NO" - but obviously the plugin operation will work. Just for the records here's the procedure to check plugin compatibility
        • SET SERVEROUTPUT ON
          DECLARE
            compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
            pdb_descr_file => '/stage/pdb1.xml',
            pdb_name => 'PDB1')
            WHEN TRUE THEN 'YES' ELSE 'NO'
          END;
          BEGIN
          DBMS_OUTPUT.PUT_LINE(compatible);
          END;
          /

          .
          select message, status from pdb_plug_in_violations where type like '%ERR%';
          .
      • create pluggable database pdb1 using '/stage/pdb1.xml' file_name_convert=('/oradata/CDB1/pdb1', '/oradata/CDB2/pdb1');
      • alter pluggable database PDB1 open upgrade;
      • exit
    • On the command prompt:
      • cd $ORACLE_HOME/rdbms/admin 
      • $ORACLE_HOME/perl/bin/perl catctl.pl -c "PDB1" -l /home/oracle/upgrade catupgrd.sql
    • Back into SQL*Plus:
      • alter session set container=pdb1;
      • startup
      • @?/rdbms/admin/utlrp.sql
      • @/u01/app/oracle/cfgtoollogs/CDB1/preupgrade/postupgrade_fixups.sql
        (If ORACLE_BASE is not set the files will be created under $ORACLE_HOME/cfgtoollogs instead of $ORACLE_BASE/cfgtoollogs)
Of course this technique will work also with more than one PDB at a given time. You'll have to repeat the steps, and your upgrade call on the command line will look like this:

      • $ORACLE_HOME/perl/bin/perl catctl.pl -c "PDB1, PDB2" -l /home/oracle/upgrade catupgrd.sql

Well, not really unplug+plug=upgraded ;-)

-Mike 

PS: I did add a few pieces of information based on the excellent feedback given to me by Frank Kobylanski from the MAA Team - cheers, Frank!!! 

Friday Aug 01, 2014

New (some undocumented) Parameters in Oracle 12.1.0.2

Every release offers some surprises - even to myself ;-)

Right now Roy and I are in the final steps to refresh our big slide deck to the new layout, but more important, to have Oracle 12.1.0.2 information included as well (were necessary). So I did my usual "compare parameters" query between releases - getting unusual surprises this time.

This is the list of new parameters introduced with the patch set Oracle Database 12.1.0.2. Where applicable I have added the link to the doc.

But as you may recognize not all of them are explained in the doc ;-)

  • DBFIPS_140
    • Default: FALSE
    • DBFIPS_140 enables Transparent Data Encryption (TDE) and DBMS_CRYPTO PL/SQL package program units to run in a mode compliant to the Federal Information Processing Standard (subsequently known as "FIPS mode";)
  • COMMON_USER_PREFIX
    • Default: c##
    • Specifies a prefix that the names of common users, roles, and profiles in a multitenant container database (CDB) must start with. If COMMON_USER_PREFIX is set to an empty string, Oracle will not enforce any restrictions on the names of common or local users, roles, and profiles.
  • DB_PERFORMANCE_PROFILE <<updated Dec 16>>
    • Undocumented
    • See bug17861171, bug18406144 and bug19817284 - IORM feature on Exadata only
  • ENABLE_GOLDENGATE_REPLICATION
    • Default: FALSE
    • Controls services provided by the RDBMS for Oracle GoldenGate (both capture and apply services). Set this to true to enable RDBMS services used by Oracle GoldenGate
    • Introduced with Oracle 11.2.0.4 and Oracle 12.1.0.2
  • EXCLUDE_SEED_CDB_VIEW
    • Undocumented
    • Per feedback by the Multitenant team:
      • Default: TRUE
      • Setting this parameter to FALSE would return results for the seed database when querying against the CDB views
  • INMEMORY_CLAUSE_DEFAULT
    • Default: an empty string
    • Enables you to specify a default In-Memory Column Store (IM column store) clause for new tables and materialized views. If the INMEMORY_CLAUSE_DEFAULT parameter is unset or set to an empty string (the default), only tables and materialized views explicitly specified asINMEMORY will be populated into the IM column store. Setting the value of the INMEMORY_CLAUSE_DEFAULT parameter to NO INMEMORY has the same effect as setting it to the default value.
  • INMEMORY_FORCE
    • Default: DEFAULT
    • Allows you to specify whether tables and materialized view that are specified as INMEMORY are populated into the In-Memory Column Store (IM column store) or not. The default value is DEFAULT. When this value is in effect, the IM column store is populated only with tables and materialized views specified as INMEMORY. If OFF is specified, then even if the IM column store is configured on this instance, no tables or materialized are populated in memory.
  • INMEMORY_MAX_POPULATE_SERVERS
    • DefaultHalf the effective CPU thread count or the PGA_AGGREGATE_TARGET value divided by 512M, whichever is less.
    • Specifies the maximum number of background populate servers to use for In-Memory Column Store (IM column store) population, so that these servers do not overload the rest of the system
  • INMEMORY_QUERY
    • Default: ENABLE
    • Used to enable or disable in-memory queries for the entire database at the session or system level. This parameter is helpful when you want to test workloads with and without the use of the In-Memory Column Store (IM column store)
  • INMEMORY_SIZE
    • Default: 0
    • Sets the size of the In-Memory Column Store (IM column store) on a database instance. If a database does not have automatic memory management enabled, this parameter must be set to a nonzero value that reserves the amount of memory to use for the database's IM column store. The default value is 0, which means that the IM column store is not used. The database must be restarted after setting this parameter to enable the IM column store. The minimum size to which this parameter can be set is 100 MB.
  • INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT
    • Default: 1
    • Limits the maximum number of background populate servers used for In-Memory Column Store (IM column store) repopulation, as trickle repopulation is designed to use only a small percentage of the populate servers. The value for this parameter is a percentage of the INMEMORY_MAX_POPULATE_SERVERS initialization parameter value
  • OPTIMIZER_INMEMORY_AWARE
    • Default: TRUE
    • Enables or disables all of the optimizer cost model enhancements for in-memory. Setting the parameter to false causes the optimizer to ignore the in-memory property of tables during the optimization of SQL statements. This behavior can also be achieved by setting theOPTIMIZER_FEATURES_ENABLE initialization parameter to values lower than 12.1.0.2
  • PDB_LOCKDOWN
    • Undocumented
    • Per feedback by the Multitenant team:
      • Not functional in Oracle 12.1.0.2
  • PDB_OS_CREDENTIAL
    • Undocumented
    • Per feedback by the Multitenant team:
      • Not functional in Oracle 12.1.0.2
      • May be functional with a future PSU allwoing then OS user verfication/validation for PDBs

-Mike

PS: Forgot to mention this one as a parameter which had been disappeared in Oracle 12.1.0.2:

  • PARALLEL_FAULT_TOLERANCE_ENABLED
    • Undocumented in Oracle 12.1.0.2
    • Disappeared in Oracle 12.1.0.2 but did exist in Oracle 12.1.0.1 

Tuesday Jul 29, 2014

Today the Upgrade Blog has its Fifth Anniversary :-)

Not sure if 5 is a lucky number :-)

But in fact today the Upgrade Blog "Upgrade Your Database Now!" has its fifth anniversary.

Well, there's not an update every day - and there are spans of silence for weeks when I'm either too busy (traveling most likely, writing etc) or when I feel there's not much important to say.  

So it's time to say THANK YOU to all our visitors and readers. I can't say exactly how many people have visited the blog so far as I have put up the Flag Counter only less than 3 years ago. And it does not count people logged into the Oracle network as our IT folks have blacklisted the Flag Counter for whatever reason (opened tickets never get a response). Guessing that we've had over 500,000 hits within the past 5 years. And almost 40,000 downloads of our "Upgrade, Migrate and Consolidate to Oracle 12c" deck. That it impressive. And the new version with new layout, new content and some corrections and enhancements will follow soon. 

So thanks to all of you for stoping by, coming back, commenting, sending mails, discussing and downloading our stuff - and we'll do our best to keep you updated on Database Upgrades and Migrations in the future, and serve you well with useful tech slide decks :-)

-Mike

Thursday Jul 24, 2014

Why "We'll wait for the 2nd release!" is a misconception ...

Oh, how often have I heard this phrase:

"We'll wait for the second release!"

And sometimes it makes me really anxious and angry at the same time when I hear that.

Anxious because it means that somebody has no strategy for the database upgrades/migrations and is just postponing necessary tasks to sometime in the future. Easy deal but not very clever

Angry because this is a way of thinking from the 90s/00s when Oracle had this "10.1", then later "10.2" strategy in database releases. But we are in 2014 now. And things have changed. Changed a lot in fact.

I'm probably not the only person who would love to see if we'd remove this "first" and "second" release tags. This has become obsolete with Oracle 11.1. We were telling customers officially that this is the brand new fantastic Oracle Database 11g release. But in fact it was - from the coding perspective - more or less a very stable 10g. In my workshops I did call it Oracle 10.3 with a marketing sticker on it. And as far as I can see the customer's I've had helped with going live on Oracle Database 11.1.0.7 were quite happy. Some really large shops still work with this release today with hundreds of databases in production.

But honestly most of the changes got introduced with Oracle Database 11.2. And not for a small number of customers this meant waiting for the first patch set (which since then has become a full release). Plenty of people went live with Oracle Database 11.2.0.2. But a lot of the remarkable changes got introduced not in Oracle 11.1 but in Oracle 11.2. Just remember things such as the move from Clusterware to Grid Infrastructure. But also minor things such as DEFERRED_SEGMENT_CREATION and plenty of optimizer news.

Now with Oracle Database 12c and the first patch set (full release) 12.1.0.2 I have heard this again - and I see it on the mailing list as well once a week:

"When will Oracle 12.2 be available?"

Well, that's the misconception. It's true, Oracle Database 12.1.0.2 has new features and extensions. But it has also many fixes over the already very stable Oracle 12.1.0.1. Why should anybody wait for Oracle 12.2 now? Because it's supposed to be THE SECOND release? Forget this - this is thinking from the old days.
We are in year 2014 now.

And then spend a minute to look closer to the Support Policy.

  • Oracle Database 11.2 will go out of Premier Support in 6 months. Yes!!! 6 months
  • And correct, we'll give everybody on Oracle 11.2.0.4 one full year of Extended Support for free
  • For Oracle 11.2.0.3 Extended Support will end 28-AUG-2015

That means if you plan to stay on Oracle 11.2.0.3/4 for a longer period you'll either have to calculate 20% extra of your support fee for the 2nd year of Extended Support. Or you prefer to "hope".

I can't tell you when Oracle 12.2 will be available - and I don't care. Usually people wait for the first patch set anyways which gets releases based on experience from the past 3 databases releases roughly a year and a bit after the initial release. Just do the math and you'll see where you end up with this strategy.

My recommendations are:

  • Stop thinking about THE SECOND release
  • Evaluate Oracle Database 12.1.0.2 now - not tomorrow
  • Look at the Support Policy - you need to start your upgrades as soon as possible
-Mike 

 

 

Tuesday Jul 22, 2014

Oracle Database 12.1.0.2 is available!!!

Oracle Database 12.1.0.2 is available for download now. It is the most comprehensive patch set we've ever built. Look up the list of inclusions - and it includes the new marquee feature OracleDatabase In-Memory.

Three things important to mention:

  • It is a full release - no need to get Oracle Database 12.1.0.1
  • Even though it is a patch set it will be available on OTN and eDelivery - and Oracle Database 12.1.0.1 will be removed instantly
  • For now it is an Enterprise Edition install only - SE and SE One may follow later.
    Please see MOS Note:1905806.1 for further details.

And it contains a bunch of new things and improvements:

  • Oracle Database In-Memory
  • Oracle Big Data SQL
  • Oracle JSON Document Store
  • Oracle REST Data Services
  • Improvements to Oracle Multitenant
  • Advanced Index Compression
  • Zone Maps
  • Approximate Count Distinct
  • Attribute Clustering
  • Full Database Caching
  • Rapid Home Provisioning
Availability is for the following platforms right now:

Time to upgrade!!! 

-Mike

PS: Kudos to Morohashi-san for alerting me about the link to the internal MOS - exchanged the link address!
PPS: Updated entry on Sept 30 with links for MS Windows

Monday Jul 14, 2014

Deutschland ist Weltmeister!!!

No tech stuff this morning :-)

I'm still so exited. It was a true thriller yesterday with 127 minutes of suspense, fights and even blood - and chances on both sides. But overall I think Germany was the better team not only yesterday but throughout the entire tournament. Last time when Germany became Weltmeister (World Champion) in Italy in 1990 I was in Turkey on after-school-vacation celebrating a win over ... well, Argentina :-)

And this time I watched the match again with friends in an Italian restaurant in Munich - and the best team of the World Cup 2014 won over ... well, Argentina again :-) I'm still so exited :-)

And obrigado also to all the wonderful people in Brazil. In previous world cups often matches such as Honduras vs Switzerland had just a small crowd of people in the stadium. This time it seemed like a huge party :-) Obrigado, Brazil!

-Mike 

Monday Jul 07, 2014

Remote Cloning of Pluggable Databases
in Oracle Database 12.1.0.1

Cloning of Pluggable Database in Oracle Multitenant is a great feature and very useful. It had just a tiny flaw in Oracle Database 12.1.0.1: Remote cloning from one to another CDB fails

This will work flawless beginning with the first patch set Oracle 12.1.0.2 - but if you want to use that feature still in Oracle 12.1.0.1 you need to apply at least PSU3 or a single patch 18898403.

See the documentation

-Mike 

Back on Track :-)

No updates for almost 3 weeks? Well ... sometimes I need a bit of vacation as well ... and for sure I didn't board an airplane. Thanks for staying tuned and you'll see the next update within a few hours :-)

 -Mike

Monday Jun 16, 2014

DBUA can ignore Underscores with "keepHiddenParams"

The Database Upgrade Assistant (DBUA) by default will remove all underscores and events before actually doing an upgrade. This is expected behaviour as we believe that underscores or events were meant to treat misbehaviour of the database just in a specific release only. And in our experience a nice collection of underscores will at least make your upgrades run slower.

Now with Oracle Database 11.2.0.4 and Oracle Database 12.1.0.1 the DBUA has a new parameter which can be used during startup:

$> dbua -keepHiddenParams

Then it will keep the hidden/underscore parameters during and after the upgrade.

-Mike 

PS: Many thanks to Mr. Frank Becker for highlighting this to myself - I wasn't aware of it - screenshot is courtesy from Mr Becker as well 

About

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

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

Follow me on TWITTER

Contact me via LinkedIn or XING

Search

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