Monday Aug 24, 2015

Migration IBM AIX ==> SPARC Solaris with Data Guard

Can we migrate our database with Oracle Data Guard?

We are getting this question asked quite often during our workshops or via email. And if you are staying within the same operating system family (such as Red Hat 5.8 to OL 7) all is fine, and this is one of the best and most simple approaches to jump between servers. Even when you add a subsequent database upgrade all is very straight forward.

But what if you mix operating systems?

The Support Note for Heterogeneous Data Guard Configurations explains which combinations are allowed beginning with specific releases:

MOS Note: 413484.1
Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration

The most popular combinations are Windows to Linux or Intel Solaris to Linux beginning with Oracle 11.1. One combination often requested was IBM AIX to Oracle SPARC Solaris. And we did support this for a few weeks - a white paper got published - and taken away shortly after, removing the support as it turned out that the control file had 5 or 6 pieces which were highly OS dependent. There were rumors circling around you still could do it with a bit of manual extra work.

Migration Oracle on AIX to SPARC SOLARIS

Now (ok, since end of March 2015), surprise-surprise, there's an official MOS Note available explaining how to migrate away from IBM AIX to SPARC Solaris by using a Physical Standby Database beginning with Oracle 11.2.0.1:

MOS Note: 1982638.1
One Time Migration Steps from IBM AIX Power to Solaris SPARC using Data Guard

It is based on the MOS Note:469493.1 (Step By Step Guide to create a Physical Standby Database using RMAN Backup/Restore).

The key action is to recreate the controlfile:

To recreate a new primary control file, use the following procedure:
a) On the open production, connect as SYSDBA, and issue "alter database backup controlfile to trace;"
b) In the trace file for this session you will see two sets of DDLs to create a new backup control file. Use the set of DDLs that create the control file with the NORESETLOGS option (Set # 1)
c) Shutdown the production database

SQL> shutdown;

d) Run the commands in the trace file starting with 'startup NOMOUNT', 'CREATE CONTROLFILE REUSE .. NORESETLOGS ..', 'RECOVER DATABASE', etc..

And how about Big to Little Endian platforms?

As the redo stream is highly OS dependent I don't think that we'll see combinations such as HP-UX to OL in the near future - just my feeling. But with the offering of Full Transportable Export/Import and the help of RMAN Incremental Backups (see this presentation in our Slides Download Center about it: ) we have very strong alternative in place.

--Mike 


Friday Aug 21, 2015

DBMS_STATS. GATHER_DICTIONARY_STATS fails with ORA-20001 ORA-6502 ORA-6512 - Concurrent Stats

I really have to say "Thank you very much" to the people out there alerting me about issues I haven't seen before. Just in the past week I've got to learn about three issues which are related to the upgrade - and I haven't seen before. Please don't expect me always to follow up with the progress of your SRs or the related bugs. But your experiences are such an important source for me - so thanks again!

The most recent issue (thanks to Bernd Tuba from MM Warburg) ...

Symptom

execute dbms_stats.gather_dictionary_stats

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_STATS", line 24268
ORA-20001: Unexpected configuration: sofar: 41 objList.count: 41 sofar_part: 0 partObjList.count: 0 sofar_ind: 0 indObjList.count: 21 concurrent: TRUE runPartTable: TRUE jstats.Running: -19
ORA-06512: at "SYS.DBMS_STATS", line 27383
ORA-06512: at "SYS.DBMS_STATS", line 27402
ORA-06512: at line 1

I see the word "CONCURRENT" - and it immediately rings a bell. Wasn't there an issue with the change of setting for default stats gathering in Oracle Database 12.1.0.2 being now CONCURRENT=TRUE?

Yes ... the reason for this blog's existence is not only to give you some interesting useful information to read about but also to dump of details from my limited brain capacity :-) I have written something about a similar issue in October 2014:

https://blogs.oracle.com/UPGRADE/entry/ora_20000_unable_to_gather
.

Analysis

This is interesting. Whereas in my other blog post the concurrent stats gathering conflicted with the resource manager settings and is fixed by a patch (Patch 19664340: ORA-20000: UNABLE TO GATHER STATISTICS CONCURRENTLY: RESOURCE MANAGER IS NOT ENABLED) this one is a different topic.

Bug:18406728  DBMS_STATS.GATHER_DICTIONARY_STATS GOES INTO SPIN CONSUMING 100% CPU 
(unpublished bug - therefore no link included here)

Solution

Very simple to solve - switch CONCURRENT stats gathering to FALSE

SQL> exec dbms_stats.set_global_prefs('CONCURRENT', 'FALSE');

In the Exadata Upgrade Note (MOS Note: 1681467.1) you'll find the same recommendation. 

More Information? 

--Mike 

Thursday Aug 20, 2015

Upgrade nach Oracle Database 12c - alles ganz einfach!

Sorry - German only - if you seek for a very similar presentation in English please find it here:
https://blogs.oracle.com/UPGRADE/entry/webcast_why_upgrade_to_oracle
.


Danke noch mal an meinen Kollegen Frank Schneede, der das erste Webinar rund um 12c eingeführt hat. 

Das Replay meines Seminar-Teils ist hier in Deutsch verfügbar:


Viel Spass :-)

--Mike 

Wednesday Aug 19, 2015

Invalid Table Data before Upgrade to 12.1.0.2?

Oracle Database Upgrade 12c

You plan to upgrade your database(s) to Oracle Database 12.1.0.2,?

You did run the preupgrd.sql including the preupgrade package in your current database already?

But in the preupgrade.log you'll see the following ERROR:
.
.

ERROR: --> Invalid Oracle supplied table data found in your database.

     Invalid data can be seen prior to the database upgrade
     or during PDB plug in.  This table data must be made
     valid BEFORE upgrade or plug in.

   - To fix the data, load the Preupgrade package and execute
     the fixup routine.
     For plug in, execute the fix up routine in the PDB.

    @?/rdbms/admin/utluppkg.sql
    SET SERVEROUTPUT ON;
    exec dbms_preup.run_fixup_and_report('INVALID_SYS_TABLEDATA')
    SET SERVEROUTPUT OFF;

Where does it come from?

First of all it's important to know that you can't upgrade your database without resolving this error condition before. If you'd ignore it you'll see the magic universal ORA-1722: invalid number error indicating that one of the mandatory checks in the upgrade scripts had failed

For more information on type evolution check the Oracle Database 12c Object-Relational Developer's Guide

What does this warning mean? 

The error results from a failed check for table data in columns of evolved types. Those must be upgraded before the database can be upgraded, otherwise they will be considered as "invalid" data. The same thing can happen when you try to plugin a stand-alone (non-CDB) database making it a pluggable database. The sanity script noncdb_to_pdb.sql will also check for this condition.  

And how do you solve the ERROR

  • For oracle-supplied data, the conversion would be done by:
    SET SERVEROUTPUT ON;
    exec dbms_preup.run_fixup_and_report('INVALID_SYS_TABLEDATA');
    SET SERVEROUTPUT OFF;

    .
  • For user data, the conversion would be done by:
    SET SERVEROUTPUT ON;
    exec dbms_preup.run_fixup_and_report('INVALID_USR_TABLEDATA');
    SET SERVEROUTPUT OFF;

In both cases the preupgrade package @?/rdbms/admin/utluppkg.sql from either your 12.1.0.2 $ORACLE_HOME or from the download via MOS Note: 884522.1 must be loaded into your database - it has been loaded already if you'd execute the preupgrd.sql

You shouldn't run the fixups on a live production system during normal operation but only right before upgrade as the fixups run ALTER TABLE DDL commands. Column metadata and data related to the object columns/tables that contains data of older version types will be updated. 

The below query will check how many of the tables/columns (oracle-supplied and user data) are affected

SQL> SELECT COUNT(*) FROM SYS.OBJ$ o, SYS.COL$ c, SYS.COLTYPE$ t WHERE o.OBJ# = t.OBJ# AND c.OBJ# = t.OBJ# AND c.COL# = t.COL# AND t.INTCOL# = c.INTCOL# AND BITAND(t.FLAGS, 256) = 256; 

Further Information?

MOS Note: 2009405.1
ORA-01722: invalid number SELECT TO_NUMBER(‘INVALID_TABLEDATA’) while upgrading to 12c

--Mike 

PS: Credits to my colleagues Cindy and Agrim for providing tech background and the MOS Note 

Tuesday Aug 18, 2015

Ouch, this hurts: bug 17325413 - patch BEFORE upgrade!

PatchI really don't want to turn this blog into something making our database look bad. But in this case it is really necessary as it is VERY UNUSUAL that we recommend to patch the database BEFORE upgrade


Just for clarification:

The following topic will affect databases 11.2.0.3.9+, 11.2.0.4.0 and 11.2.0.4.1 only - those (and only those) need to be patched BEFORE upgrade. The topic is fixed in 12.1.0.2 but as it gets introduced with the BEFORE upgrade database version you'll have to apply the fix before upgrade. The inclusion of the fix in 12.1.0.2 means only that the misbehavior won't happen there again. But as it is a meta data dictionary corruption you'll have to apply the fix before as otherwise it will break during or after the upgrade.


First of all, thanks to Ehtiram Hasanov (cleverbridge AG) and Oliver Pyka (http://www.pyka.de/) for highlighting this to me. And sorry for hitting this issue ...

Symptoms:

After upgrading to Oracle Database 12.1.0.2 you'll get one of the below errors when trying to read data: 

  • ORA-07445: exception encountered: core dump [qcsIsColInFro()+358] [SIGSEGV] [ADDR:0x4] [PC:0xCDB4A26] [Address not mapped to object] []
  • ORA-12899 / ORA-607
  • ORA-600 [kdmv_check_row_2:IMCU row has wrong contents]
  • ORA-600 [kddummy_blkchk]
  • ORA-600 [kdBlkCheckError]
  • ORA-600 [klaprs_12]
  • ORA-600 [13013]
  • ORA-600 [17182] 

Analysis:

Basically this happens when you try to drop a column with a DEFAULT value and a NOT NULL definition - it ends up with dropped column data being written to disk leading to block corruptions. This causes problems for generating undo which cannot be applied; a ROLLBACK fails.

If you need more information please look up this MOS Note about
Bug 17325413 - Drop column with DEFAULT value and NOT NULL definition ends up with dropped column data hitting disk leading to corruption

Versions being affected:

  • These versions require to be patched BEFORE upgrade:
    • Oracle Database 11.2.0.3.9 and above (may happen with earlier PSUs as well)
      Solution: Apply the fix 17325413  on top - see below
    • Oracle Database 11.2.0.4.0 and 11.2.0.4.1 
      Solution: Apply the most recent PSU
  • These versions can get you the issue if you haven't patched BEFORE upgrade:
    • Oracle Database 12.1.0.1
    • Oracle Database 12.1.0.2

Workaround and/or Fix:

The MOS Note about Bug 17325413 - Drop column with DEFAULT value and NOT NULL definition ends up with dropped column data hitting disk leading to corruption explains the workaround WHEN you hit this issues. 

As a precaution you will have to make sure that you applied one of those fixes BEFORE upgrading to Oracle Database 12.1.0.2. as the fix for Bug 17325413 is included in all those mentioned below (list is taken from above MOS Note as well).

The best way to avoid this is really to apply the patch (or the PSU/BP including the patch) before upgrading.

The issue has been mentioned in "Oracle 11.2.0.4 - Known Issues and Alerts" (MOS Note:1562139.1)  under "Issues Introduced":

Issues introduced

But that does jump into your eye as a thing you need to fix before upgrade.
We'll see if we can get the issue added to the 12c MOS Notes as "Upgrade Issues".

--Mike 

Monday Aug 17, 2015

DBCA 12c and "datapatch.pl" - things to know

A few weeks ago I did blog about the DBUA (Database Upgrade Assistant) not executing 'datapatch' (i.e. not applying the SQL changes involved with a SPU/PSU/BP) automatically:

Again, please note that this behavior DOES NOT APPLY to command line upgrades done with catctl.pl - as you can see from this somewhat disturbing messages during the upgrade in phase 65 and phase 69 (which are not errors but just informational messages for datapatch's execution):

Datapatch phases command line upgrade catctl.pl

But afterwards I have learned that things are worse.
The same behavior is true when you create a database.

Not a typo.
You create a fresh database with DBCA (Database Configuration Assistant), you are a honest customer, you have followed our advice and applied the most recent PSU (or SPU or BP) into your Oracle Home. You don't even deploy one of the preconfigured databases but use the CREATE CUSTOM DATABASE option of DBCA. And the database will run from the patched home - but the SQL changes haven't been applied to it

Reason?

DBCA does not call 'datapatch' for database changes.

 

I consider this even worse than the DBUA behavior as the person who upgrades a database in most cases is aware of the future home. But the person who either deploys a new database or asks for one to be deployed is often not identical with the person who did patch the homes.

And there's no warning displayed yet nor (afaik) is there a MOS note available talking about it.

How do you fix the issue?

After creating a new database make sure to run:

 

cd $ORACLE_HOME/OPatch
./datapatch -verbose

and double check with DBA_REGISTRY_SQLPATCH view:

select  PATCH_ID, PATCH_UID, VERSION, STATUS, DESCRIPTION
from DBA_REGISTRY_SQLPATCH
order by BUNDLE_SERIES;

Actually to be 100% you may find some patch information in DBA_REGISTRY_SQLPATCH showing that the JAVAVM patch has been applied in case you've installed the Combo version of the PSU. But you'll miss the database changes.

--Mike

Friday Aug 14, 2015

Oracle Databases and Microsoft Windows 10

MS Windows 10If you have upgraded already or plan to upgrade to Microsoft Windows 10 in the near (or later) future, you may find this Statement Of Direction by Oracle regarding Microsoft Windows 10 certification quite useful:

http://www.oracle.com/technetwork/database/windows/whatsnew/oracle-db-win10-sod-2626195.pdf

In summary:

  • Oracle plans to certify Oracle Database 12.1.0.2 by October 2015
  • Oracle plans to certify the next major version of the database as well on MS Win 10 - 64bit
--Mike 

Wednesday Aug 05, 2015

Grid Infrastructure Management Repository (GIMR) - Datapatch?

I have blogged about the Grid Infrastructure Management Repository (GIMR) a while back:
https://blogs.oracle.com/UPGRADE/entry/grid_infrastructure_management_repository_gimr

And 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


Since Oracle Database 12.1.0.2 the GIMR database will be created by default - and it is a single tenant database having a CDB$ROOT and one active PDB. 

Recently the question came up if - in the likely event of applying a PSU or BP to the GI Home - you'll have to run datapatch manually to adopt the SQL changes for the PSU/BP into the GIMR database as well?

Simple answer: No.

SQL changes will be automatically applied to the GIMR database by default. This got introduced in Oracle 12.1.0.1 with the PSU1 already and is tracked by ER BUG 14830129 - MGMT DATABASE PATCH ACTIONS NEED TO RUN DURING GI POST PATCH PHASE

You can verify this by looking at your logs (Thanks Santosh!) - you should see something similar as:

<grid_home>/cfgtoollogs/crsconfig/crspatch_xxxx file

-------------------
2015-07-15 15:36:51: Mgmtdb is running on node: racnode1; local node: racnode1
2015-07-15 15:36:51: Mgmtdb is running on the local node
2015-07-15 15:36:51: Starting to patch Mgmt DB ...
2015-07-15 15:36:51: Invoking "/opt/oracle/app/12.1.0/grid/sqlpatch/sqlpatch -db -MGMTDB"
2015-07-15 15:36:51: Running as user oracle: /opt/oracle/app/12.1.0/grid/sqlpatch/sqlpatch -db -MGMTDB
2015-07-15 15:36:51:   Invoking "/opt/oracle/app/12.1.0/grid/sqlpatch/sqlpatch -db -MGMTDB" as user "oracle"
2015-07-15 15:36:51: Executing /bin/su oracle -c "/opt/oracle/app/12.1.0/grid/sqlpatch/sqlpatch -db -MGMTDB"
2015-07-15 15:36:51: Executing cmd: /bin/su oracle -c "/opt/oracle/app/12.1.0/grid/sqlpatch/sqlpatch -db -MGMTDB"
2015-07-15 15:37:50: Command output:
>  SQL Patching tool version 12.1.0.2.0 on Tue Jul 15 15:36:51 2015
>  Copyright (c) 2015, Oracle.  All rights reserved.

>  Connecting to database...OK
>  Note:  Datapatch will only apply or rollback SQL fixes for PDBs
>         that are in an open state, no patches will be applied to closed PDBs.
>         Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
>         (Doc ID 1585822.1)
>  Determining current state...done
>  Adding patches to installation queue and performing prereq checks...done
>  Installation queue:
>    For the following PDBs: CDB$ROOT PDB$SEED CRS
>      Nothing to roll back
>      The following patches will be applied:
>        20831110 (Database Patch Set Update : 12.1.0.2.4 (20831110))

>  Installing patches...
>  Patch installation complete.  Total patches installed: 3

>  Validating logfiles...done
>  SQL Patching tool complete on Tue Jul 21 15:37:50 2015

---------------------- 

For all the skeptical people (Germans especially) let me add that in Oracle Database 12.1.0.2 the Grid Infrastructure Management Repository (GIMR) is not mandatory - but its existence will be mandatory for a future upgrade to Grid Infrastructure 12.2.

--Mike 

Tuesday Aug 04, 2015

Other people's thoughts: "Should you upgrade to 12c?"

You still don't believe me yet when I say: You can't seriously wait for the so called 2nd release of Oracle Database 12c as it will be an entirely new release again? And you haven't upgraded yet to Oracle Database 12.1.0.2 for various reasons? 

Then sometimes it's good to listen to other people's opinions - and I'm happy to share this 6 minute video by Tim Hall (very well known for his great page oracle-base). Listen to Tim and his Thoughts about Upgrading to Oracle Database 12c. And don't get disturbed that he's driving on the wrong side of the road ;-)


And I promise I didn't bribe Tim (cocktails don't count) ;-)

Thanks Tim!!! 

--Mike 

Saturday Aug 01, 2015

Upgrade workshop this week in Denver

Denver workshop

I am looking forward to my trip to the Denver area this week, including an upgrade workshop at the Oracle office in the tech center area. We have a good registration list so far but there is still room! If you would like to attend, just register at events.oracle.com

Thursday Jul 30, 2015

SQL Monitoring - Limitation at 300 lines per statement

One of the best parts of my job at Oracle:
I still learn something new every day.

Yesterday I've learned from my colleague from Oracle Switzerland, Thomas Teske, that SQL Real Time Monitoring has an embedded default limitation on the number of lines in the statement. If the limit (default: 300 lines) is exceeded the statement won't be monitored. We both work with a leading Swiss company and we wanted to monitor a complex plan. 

Now you may think: Who the heck has statements longer than 300 lines?
Well ... sometimes that is beyond your influence as in this particular case this is of course done by the application.

Solution:

SQL> alter system set "_sqlmon_max_planlines"=800 scope=both;

or set in your spfile:

_sqlmon_max_planlines=800

This limitation is described in:

MOS Note:1613163.1
How to Monitor SQL Statements with Large Plans Using Real-Time SQL Monitoring?

If you'd like to read a bit more about SQL Real Time Monitoring please follow one of these links - and be aware that it's part of the Tuning Pack license and VERY helpful in many everyday situations. You'll have to have STATISTICS_LEVEL either TYPICAL (the default) or ALL and CONTROL_MANAGEMENT_PACK_ACCESS='DIAGNOSTIC+TUNING' (the default as well).

 

--Mike 

Things to consider BEFORE upgrading to Oracle 12.1.0.2 to AVOID poor performance and wrong results

Finally it got published on MyOracle Support (MOS) portal - one of the most helpful notes when we prep for an upgrade or a migration - and potentially the one with the longest title in MOS:

MOS Note: 2034610.1
Things to consider BEFORE upgrading to Oracle Database 12.1.0.2
to AVOID poor performance and wrong results 

Avoid Poor Performance and Wrong Results when upgrading to 12.1.0.2

Just in case you are NOT upgrading/migrating to Oracle Database 12.1.0.2 there are three other helpful notes for earlier releases available as well:

Document 1320966.1
Things to Consider Before Upgrading to 11.2.0.2
to Avoid Poor Performance or Wrong Results

Document 1392633.1
Things to Consider Before Upgrading to 11.2.0.3
to Avoid Poor Performance or Wrong Results
Document 1645862.1
Things to Consider Before Upgrading to 11.2.0.4
to Avoid Poor Performance or Wrong Results

--Mike 

Wednesday Jul 29, 2015

ORAchk - How to log SRs and ERs for ORAchk

ORAchkI have recently recommended to use ORAchk in its new version 12.1.0.4 - and I can just emphasize on this.
https://blogs.oracle.com/UPGRADE/entry/orachk_12_1_0_4

During a conf call with a lead customer from the UK Roy and I learned about the uncertainness on how to log a Service Request (SR) or enhancement Request (ER) against ORAchk in case something is not working correctly or missing.

Especially as the documentation of ORAchk states:

Appendix B - How to Obtain Support

If problems are encountered either at runtime or if there are questions about the content of the findings of the tool, please post your issues/questions/concerns to the ORAchk thread within the ORAchk Thread of the Scalability RAC My Oracle Support Community.

Roy explored this topic in the past days and that is the outcome

  • Of course you can file SRs via MOS (and ERs as well).
    If a Support Engineer told you that the product is only supported via the Community Forum this is not correct. Just insist :-)
  • There's an official product ID (10655), component (ORACHK) and sub component (FRAMEWORK) for filing ORAchk bugs and enhancements internally in our bug database. But of course a customer will need to file an SR first via MOS.

We hope this helps :-)

--Mike 

Tuesday Jul 28, 2015

Optimizer Issue in Oracle 12.0.1.2: "Reduce Group By"

Wrong Query Results BugDBAs biggest fears I'd guess are Optimizer Wrong Query Results bugs as usually the optimizer does not write a message into the alert.log saying "Sorry, I was in a bad mood today ..."

The Oracle Database Optimizer is a complex piece - and in Oracle 12c it delivers great performance results. Plus (my personal experience when you know what to do) it is more predictable which I like a lot when changing databases from one to another release. But due to its complexity sometimes we see issues - and sometimes it is necessary to switch off tiny little pieces until a fix is available.

Roy just came across this one - and we believe it's worth to tell you about it. Again, our intention is only to prevent issues when upgrading or migrating to Oracle Database 12.1.0.2.

Symptom:

An outer join query with a bind variable and a group by clause can produce wrong results in some cases.

Analysis:

 If all of the following match, you may be hitting this bug:
 - two or more subquery views are outer-joined on column C1
 - column C1 is specified on select list of top-most query block
 - column C1 is filtered on a bind value

Example:

 create table test1(c1 number(5),c2 varchar2(16));
 insert into test1 values(1,'3');
 commit;

 set NULL NULL
 variable num1 number
 execute :num1 :=1;

 -- Following query retuns wrong result(NULL), this should return 1.

 select V.c1 from
  (SELECT c1 FROM test1 GROUP BY c1) V,
  (SELECT c1 FROM test1 WHERE c2 = '1' GROUP BY c1) V2
 where  V.c1 = :num1
    and V.c1 = V2.c1(+);

Workaround:

alter session set "_optimizer_reduce_groupby_key" = false;

Please don't use the workaround:
alter session set optimizer_features_enable='12.1.0.1';
as this will switch off other good 12.1.0.2 optimizer features working very well.

More information:

See MOS Note:20634449.8 describing:
Bug 20634449 - Wrong results from OUTER JOIN with a bind variable and a GROUP BY clause in 12.1.0.2

As far as I can see there are no interim (one-off/single) patches available right now. 

--Mike

Friday Jul 24, 2015

Why does a PDB require an upgrade?

Why do I need to upgrade (or downgrade) a Pluggable Database (PDB) once I unplug it from version A and replug into version B?

This question is one of the most often asked questions in our workshops when Roy and I present Upgrades and Oracle Multitenant.

If you take a look into the documentation you'll find:

But unfortunately this is not 100% correct for every object.

When you do a simple query for basic dictionary structures such as OBJ$ you'll easily find out that a PDB has its own Data Dictionary.  Some things are really linked into the PDB only - check the SHARING column of DBA_OBJECTS for instance. If it says "METADATA LINK" it will give you an indication that it exchanges information with its parent in the CDB$ROOT. But that doesn't mean necessarily that it's just an empty meta object. Some tables do exist in the PDB as well and allocate space. Check it by yourself and you'll find out that OBJ$ inside a PDB has in fact the same attribute - but still owns segments in the PDB and in the CDB$ROOT - as other objects do allocate space as well. And of course the object's definition exists in the PDB as well. 

  • CDB$ROOT

    OBJ$ in CDB$ROOT

  • PDB 

    OBJ$ in PDB
    .

So the answer is simple:
A Pluggable Database (PDB) must be upgraded (or downgraded) when it get moved between versions simply because it has its own data dictionary. And this doesn't get upgraded automatically.
,

 

--Mike 

Monday Jul 20, 2015

DBUA 12c and "datapatch.pl" - things to know

For clarification:
The following blog post applies to upgrades to Oracle 12.1 done by DBUA only whenever a SPU/PSU/BP is installed into the 12.1 home prior to the upgrade (which I'd highly recommend as patching before upgrade saves you headache after upgrade!).



Two customers independently reported last week that they have doubts on DBUA's ability to apply the required SQL changes associated with CPU/SPU or PSU

First of all, let me tell you that this is not an issue when you do a command line upgrade to Oracle Database 12c with catctl.pl - you'll only need to take care when using the DBUA

One claimed that this feature alongside with datapatch.pl had been announced a while back:

Oracle Premier Support - Oracle Database Support News
Issue November, 2014 Volume 46
(Doc ID 1954478.1)

Which Patching Tools uses Datapatch ?

  • Opatchauto   
    • OPatchAuto calls datapatch automatically to complete post patch actions upon installation of the binary patch and restart of the database.
  • Enterprise Manager Cloud Control   
    • Starting version 12.1 EMCC now calls datapatch to complete post patch actions upon any 12c or later database restart
  • Upgrade   
    • Catctl.pl and DBUA now call Datapatch during the upgrade process
  • OPatch  
    • Datapatch integration with OPatch is not possible as OPatch is executed when the database is down and datapatch requires the database to be open to complete its activity.

The other customer provided all the logfiles - and I print the important logs only with the interesting part marked in RED:

==========================================
Contents of catupgrd_datapatch_upgrade.log
==========================================
SQL Patching tool version 12.2.0.0.0 on Tue Jul 14 13:10:39 2015
Copyright (c) 2014, Oracle.  All rights reserved.
Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of SQL patches:
Patch 19282028 (Database PSU 12.1.0.2.1, Oracle JavaVM Component ():
  Installed in the binary registry only
Bundle series PSU:
  ID 1 in the binary registry and not installed in the SQL registry
Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
Nothing to apply
SQL Patching tool complete on Tue Jul 14 13:10:57 2015

=============================================
Contents of sqlpatch_catcon__catcon_22773.lst
=============================================
catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts
catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        catconInit: start logging catcon output at 2015-07-14 13:10:39

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Ok, so it seems to be true that DBUA did not apply the post upgrade SQL changes associated with the most recent PSU.

DBUA 12c

Now digging a bit deeper we could solve the puzzle.

The DBUA uses the "catctl.pl -x" option executing catuppst.sql (the post upgrade script) in a separate step whereas on the command line catctl.pl will execute catuppst.sql by default (tracked with bug19990037). The DBUA uses catcon.pl instead to execute catuppst.sql. In previous releases this was not an issue as catbundle.sql got automatically executed as part of catuppst.sql. But as datapatch.pl is a PERL script, and a PERL script cannot be run from within a SQL script, catuppst.sql can no longer call the post-patching activities. The DBUA in 12.1.0.2 misses this action as a separate task.

Summary and Solution

DBUA misses the post-upgrade datapatch execution in Oracle 12.1.0.2. The solution is to apply the SQL changes manually after DBUA has completed the database upgrade to Oracle Database 12c:

cd $ORACLE_HOME/OPatch
./datapatch -verbose

And again, this is only necessary when you used the DBUA for a database upgrade. This step is not required for the command line upgrade. This will be fixed in an upcoming release of the DBUA.

If you are in doubt whether the DBUA or the command line upgrade had been used, unfortunately you won't find any indication inside the database. But look into $ORACLE_BASE/cfgtoollogs/dbua/logs - if the "dbua" directory exists, the DBUA had been used. If not than the command line upgrade had been processed.

--Mike 

Friday Jul 17, 2015

Webinars about Oracle Database 12c - GERMAN only

7 Webinare rund um Oracle Database 12c - mit prominenten und erfahrenen Gästen wie Johannes Ahrends, Christian Trieb und Markus Geis (siehe auch Sprecher-Übersicht).

Ab dem 11. August 2015 geht es gleich mit einer Einführung und dem Thema "Upgrade" los.

Eine Übersicht zu allen 7 Webinaren gibt es hier:
Oracle 12c Webinare - Überblick

Und auf dieser Seite findet sich auch oben mittig der Anmeldelink (grauer Balken).

 Bis dahin! 

--Mike 

Update:

Hier sind die Links zum Replay - der Einführungsteil meines Kollegen Frank Schneede - und die 34 Minuten zum Thema "Upgrade nach 12c ist einfach" von mir. Viel Spass damit :-)

Tuesday Jul 14, 2015

Just to remember what happened on July 13, 2014

Just one year ago on July 13, 2014 in  Estádio do Maracanã in Rio De Janeiro... :-)

Germany World Cup 2014 Goal Argentina Mario Götze

:-)

--Mike 

Monday Jul 13, 2015

Updated version of the In-Memory Advisor is available

Which of your tables and/or partitions should you mark for In-Memory column store availability? 

You'll get the answer with the help of the Oracle In-.Memory Advisor which just got updated and is available via MyOracle Support:

The Advisor produces a report identifying the objects that should be placed In-Memory for maximum benefit, along with a SQL*PLUS script which implements those recommendations. It can be run on Oracle Database 11.2.0.3 and above. And of course the recommendations can be implemented on Oracle Database 12.1.0.2. (and newer).

Important to know:
The In-Memory Advisor is licensed as part of the Database Tuning Pack.

Further information can be found at: OTN

The New Features include:

  • Oracle Multitenant support
  • Enhanced analysis
  • and bug fixes

 

See the RELEASE NOTES for further details on version 1.0.0.1. 

--Mike 

Thursday Jul 09, 2015

Bug Fixing Support for Oracle 11.2.0.3 will end soon

The bug fixing support for Oracle Database 11.2.0.3 will end soon.

From my visits in the past months seeing some really large customer environments with easily over 1000 production databases each I can tell you that there may be many Oracle 11.2.0.3 installations out there. So maybe not everybody is aware of this fact.

It's hard to believe but the Error Correction Support for Oracle Database 11.2.0.3 will end shortly on August 27,2015.

See MOS Note:742060.1  :

End of error Correction Support 11.2 .

And does it need to be said?
Yes, you can - of course - upgrade directly to Oracle Database 12.1.0.2.

If you'd like to read more details please find them in my previous blog post:
https://blogs.oracle.com/UPGRADE/entry/premier_support_for_oracle_111 

--Mike 

Monday Jul 06, 2015

Monitor SGA/PGA while Database Upgrade is running

This blog is always a reminder to myself as well - just in case you wonder about that simple example and all steps here ;-)

Currently I work with an important customer from Switzerland - and they upgrade their database landscape to Oracle Database 12.1.0.2. But in some rare cases we see the upgrade failing at different stages with dying PQ slaves or running out of memory. The reason may be that the customer is using only MEMORY_TARGET=1G - but actually our preupgrd.sql has nothing too complain about. And from reading the docs all seems to be fine as well (MEMORY_TARGET>152M).

The temporary workaround used by the customer is to increase MEMORY_TARGET prior to the upgrade to 4G - and decrease it later on to the old value. This works but it does not answer the question yet if something is wrong with MEMORY_TARGET or if our script does not recommend the correct value. And I can't answer this at the moment - but in order to collect some information I've did the following in my environment:

  1. Simple SQL script check_sga.sh to monitor the change in pool shares while the upgrade is running:

    #!/bin/sh
    ORACLE_BASE=/u01/app/oracle
    ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2

    if [ "$1" = "" ] ; then
      ORACLE_SID=UPGR
    else
      ORACLE_SID=$1
    fi

    TNS_ADMIN=/u01/app/oracle/product/12.1.0.2/network/admin
    NLS_LANG=AMERICAN_AMERICA.AL32UTF8
    PATH=$ORACLE_HOME/bin:$PATH
    LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
    CLASSPATH=.:$ORACLE_HOME/jdbc/lib/classes12.jar:$ORACLE_HOME/jdbc/lib/nls_charset12.jar:$ORACLE_HOME/rdbms/jlib/xdb.jar:$ORACLE_HOME/lib/xmlparserv2.jar:$ORACLE_HOME/sqlj/lib/utl_dbws.jar
    export ORACLE_BASE ORACLE_HOME ORACLE_SID NLS_LANG CLASSPATH PATH LD_LIBRARY_PATH TNS_ADMIN

    sqlplus -s "sys/oracle@upgr as sysdba" @check_sga.sql >> check_sga.txt
    .
  2. Simple shell script to call the SQL script check_sga.sql periodically - and there would be other views to monitor the SGA behavior especially for resize operations than the V$MEMORY_DYNAMIC_COMPONENTS I used below: Shared Memory Management Views in Oracle 12c.

    SET PAGESIZE 50000
    SET LINESIZE 25000
    SET NUMWIDTH 5
    SET FEEDBACK OFF
    set echo off
    set heading on
    set headsep off
    set wrap off
    column MB format 999999
    column OPERATION format a22

    SELECT TO_CHAR(SYSDATE, 'dd-mm-yy hh24:mi:ss') "TIMESTAMP" from dual;

    select component, current_size/power(1024,2) "MB", last_oper_type "OPERATION" from v$memory_dynamic_components where current_size != 0;

    exit
    .
  3. Enable the listener to accept remote connections via TPC while the database is in RESTRICTED SESSION more.
    This is from my tnsnames.ora - I had to add the (UR=A) setting:
    .
    UPGR = 
    (DESCRIPTION =
    (ADDRESS_LIST = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        )
        (CONNECT_DATA =
    (UR = A)
          (SERVICE_NAME = UPGR)
        )
      )
    .
  4. Change the script properties to make it executable without any issues from cron
    $ chmod 777 check_sqg.sh
    $ chmod +x checg_sga.sh

    .
  5. Add the shell script to crontab for periodic execution every minute ==> * * * * *
    $ export EDITOR=vi
    $ crontab -e

    * * * * * /home/oracle/check_sga.sh


    .
  6. Start the database in UPGRADE mode with a very tiny memory_target (700MB in my example) and no other memory values specified:

    *.audit_file_dest='/u01/app/oracle/admin/UPGR/adump'
    *.audit_trail='NONE'
    *.compatible='12.1.0'
    *.control_files='/oradata/UPGR/control01.ctl','/oradata/UPGR/control02.ctl'
    *.db_block_size=8192
    *.db_domain=''
    *.db_name='UPGR'
    *.db_securefile='ALWAYS'
    *.diagnostic_dest='/oradata'
    *.memory_target=734003200
    *.open_cursors=300
    *.processes=300
    *.remote_login_passwordfile='EXCLUSIVE'
    *.sessions=100
    *.undo_tablespace='UNDOTBS1'

    .
  7. Monitor the output and see what's happening
    .
    PGA SGA Upgrade Usage Shift 12c
    What do we see here:
    1. The Upgrade took roughly 34 minutes - it's one my laptop, 2 years old, 5400rpm spinning disk, a true high end system ;-) Inside an Oracle VirtualBox image.
    2. SGA_TARGET and PGA_TARGET stay constant throughout the upgrade - no shift from one to another part is happening
    3. When the Java component upgrade happens at 13:13h there's a huge allocation of JAVA_POOL_SIZE taken away from the Default Buffer Cache.
    4. The Shared Pool is slightly increasing during the upgrade - mem shifted also from the Default Buffer Cache.
      .
  8. I did the same exercise now while recompiling the database objects afterwards - this has nothing to do with the original question but I was simply curious:

    Of course we see plenty of shared pool getting allocated - that is expected. But see also how heavily the buffer cache shrinks with such a small SGA. That's why we always recommend to have DB_CACHE_SIZE set in addition as a minimum barrier to prevent the database (regardless if you use SGA_TARGET or MEMORY_TARGET) from getting too low during regular operation).
    And just on the side: The recompilation took only 4 minutes from 13:53h-13:57h - but as there was no load on the database the pools stayed constant afterwards.
    .

I'm pretty sure this all can be done way more sophisticated - but I wanted to have a simple example working quickly in my environment. And it does what I want.

Now the next step will be to verify and compare this with the customer's database upgrades.

--Mike 

Friday Jul 03, 2015

News on Oracle Database STANDARD EDITION 12.1.0.2

Standard EditionJust fyi in case you had bookmarked this blog post or found a link to it:

There will be an update on Oracle Database 12c SE soon.

Thanks for your patience and sorry for the inconvenience!

--Mike


ORAchk 12.1.0.4 released - a MUST USE Tool

ORAchk I have blogged a while back in March 2015 about ORAchk 12.1.0.3  as it had added the support for Oracle Restart amongst other great features.

ORAchk (short for: ORA check) is such a great tool to check your environment before any upgrade, but also on a regular basis for almost everything. I'd call it a MUST USE Tool not only for Oracle Grid Infrastructure or Oracle Restart upgrades or patches or changes. But also for EM Cloud Control 12c, EBS, Siebel, and of course your hardware.

The newest version of ORAchk 12.1.0.4 has been released.

The new ORAchk release 12.1.0.2.4 is now available to download.

General ORAchk features

  • Proactively scans for the most impactful problems across the various layers of your stack
  • Simplifies and streamlines how to investigate and analyze which known issues present a risk to you
  • Lightweight tool that runs within your environment without requiring config data to be sent to Oracle
  • High level reports show your system health risks with the ability to drill down into specific problems and understand their resolutions
  • Can be configured to send email notifications when it detects problems
  • Collection Manager, a companion Application Express web app, provides a single dashboard view of collections across your entire enterprise 
  • It does not cost anything!!!

 

Details of new features in ORAchk 12.1.0.2.4

Auto update ORAchk when newer version is available

New in this release, if ORAchk is older than 120 days and a newer version is not available locally it will check to see if a newer version is available on My Oracle Support and automatically download and upgrade.

Download of latest version directly from My Oracle Support can also be specifically triggered with “./orachk –download”.

If ORAchk is running in automated mode the daemon will automatically upgrade from local location defined by RAT_UPGRADE_LOC just before the next scheduled run. Email notification will be sent about the upgrade then ORAchk will continue with the scheduled run using the upgraded version, all without requiring you to restart the ORAchk daemon.

Expanded Oracle Product Support

ORAchk 12.1.0.2.4 now brings wider and deeper support throughout the Oracle product stack, with newly added support for the following product areas:

  • Enterprise Manager OMS
  • E-Business Suite Oracle Fixed Assets
  • E-Business Suite Oracle Human Resources
  • E-Business Suite Oracle Receivables
  • Siebel CRM Application

See Document 1268927.2 for further details of the new product support.

Over 60 New Health Checks

This release of ORAchk adds new checks for some of the most impactful problems seen to Oracle Customer Support specifically in the areas of:

  • Systems hardware settings to optimize encryption performance for the Database and E-Business Suite.
  • Solaris & Siebel CRM Object Manager to ensure page sizes are set appropriately for Siebel CRM to handle large numbers of users.
  • Database optimization of memory and resource related configurations and Application Continuity checks.
  • Enterprise Manager OMS High impact problems that cause functional failure or difficulty with patching or upgrade.
  • E-Business Suite Receivables detection of non-validated Receivables Accounting Definitions, which might prevent the Create Accounting process from functioning.
  • E-Business Suite Fixed Assets checks for any books with an errored or incomplete depreciation run, to allow for resolution prior to month end close.
  • E-Business Suite Human Resources verification of Setup Business Group configuration.
  • Siebel Applications verification of the database configuration for stability, best practices and performance optimization.

--Mike

Thursday Jul 02, 2015

SAP on Oracle Database 12c now with Oracle In-Memory

Oracle SAPOn March 31, 2015 SAP has been certified to run on Oracle Database 12.1.0.2:
https://blogs.oracle.com/UPGRADE/entry/sap_is_now_certified_on

As of June 30, 2015, Oracle Database In-Memory is supported and certified for SAP environments for all SAP products based on SAP NetWeaver 7.x. on Unix/Linux, Windows and Oracle Engineered Systems platforms running Oracle Database 12c - in single instance and Oracle Oracle Real Application Clusters deployments.

Oracle Database 12c is the database of choice for SAP customers based on In-Memory Technology which is fully supported for SAP BW and SAP OLTP applications.

For requirements, restrictions, and implementation details see the documents below.

--Mike

PS: Fidel, thanks for the correct links - highly appreciated!!! 

Wednesday Jul 01, 2015

Log Writer Slave Issues in 12.1.0.2 - mainly on IBM AIX

Currently we see a lot of issues with the Multiple Logwriter feature in Oracle Database 12.1.0.2, especially on the IBM AIX platform.

What are Multiple LGWRs?

You will see multiple LGnn (Log Writer Worker) processes on multiprocessor systems, The LGWR (Log Writer) creates worker processes (slaves) to improve the performance of writing to the redo log. LGnn (Log Writer Worker) processes are not used when there is a SYNC standby destination. Possible processes include LG00-LG99.

Here is a list of new background processes in Oracle Database 12.1:
MOS Note 1625912.1 - New Background Processes In 12c 

For deep dive information about how to trace the LGWR (and DBWR) and the differences 11.2/12.1 and wait events and much more, please see this Deep Dive PDF from an UKOUG talk from Frits Hoogland.

Known Symptoms:


Solution:

Turn off multiple logwriters on IBM AIX at the moment - in some cases this may solve issues on other platforms as well but please don't take this as a generic recommendation for all platforms. We are seeing the issues mainly on IBM AIX.

Set in your spfile: 

_use_single_log_writer=true

.

--Mike 

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
« August 2015
SunMonTueWedThuFriSat
      
2
3
6
7
8
9
10
11
12
13
15
16
22
23
25
26
27
28
29
30
31
     
Today
Oracle related Tech Blogs
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
Workshop Map
This week on my Rega & Pono
Upgrade Reference Papers