September 2017 releases of preupgrade.jar and preupgrd.sql are available

On a regular basis the Database Upgrade Team releases new and improved versions of the preupgrade tools via a MyOracle Support note. And it’s time again: the September 2017 releases of preupgrade.jar and preupgrd.sql are available for download. September 2017 releases of preupgrade.jar and preupgrd.sql are available Please ALWAYS download the most recent versions of preupgrade.jar for upgrades to Oracle Database 12.2 and preupgrd.sql (including utluppkg.sql) for Oracle Database 12.1.0.2 upgrade from MOS Note: 884522.1. The version you’ll download is way newer than the version you’ll get with the base installation. preupgrade.jar – Rel.7 – September 2017 Please find the…

Fallback Strategy: Flashback Pluggable Database

Since Oracle Database 12.2 an important feature works now for pluggable databases as a Fallback Strategy: Flashback Pluggable Database in Oracle 12.2. This was missing in Oracle Database 12.1. Actually I missed this feature a lot as I use “Flashback” almost everyday. And you could only rewind the entire deployment with a FLASHBACK DATABASE command. Fallback Strategy: Flashback Pluggable Database Actually having the possibility to flashback a pluggable database without effecting the other PDBs is really important. Did you ever want to try out what happens if you TRUNCATE TABLE SYS.PROPS$? Give it a try – but set a Guaranteed…

Oracle GoldenGate 12.3 is supported for Oracle Database 12.2.0.1

Finally … it took a little while but now Oracle GoldenGate 12.3 is supported for Oracle Database 12.2.0.1. Never heard of Oracle GoldeGate? Then you may look either into our slide deck and see the customer case of Amadeus. Or you may read further on OTN: http://www.oracle.com/technetwork/middleware/goldengate/overview/index.html Oracle GoldenGate 12.3 is supported for Oracle Database 12.2.0.1 Roy and I received this question quite often in the past months: When will Oracle GoldenGate be certified for use with Oracle Database 12.2.0.1? I just learned about it today via the blog post of my friend YV RaviKumar who wrote about it. And…

Oracle Optimizer Statistics Advisor in Oracle Database 12.2.0.1

The Oracle Optimizer Statistics Advisor in Oracle Database 12.2.0.1 is something I planned to write about for quite a while. Roy and I include this topic in the New Features section in our Upgrade / Migrate / Consolidate to Oracle Database 12.2 slide deck for some time. This blog post (in German only) by my former team mate Ulrike Schwinn inspired me to post something by myself. Oracle Optimizer Statistics Advisor in Oracle Database 12.2.0.1 First of all you’ll find the feature documented in the Oracle Database 12.2 Database SQL Tuning Guide. The advisor task (‘Statistics Advisor‘) runs automatically in…

ORA-44787 – Don’t mess with the Default Oracle Service

At the moment I work with one of our reference customers, Swiss Mobiliar Insurance, on their Multitenant upgrade to Oracle Database 12.2.0.1. And we encountered an “interesting” issue. After upgrade we received an ORA-44787 making it impossible to switch between containers with “alter session set container=pdb1;” commands. Lesson learned now: If you don’t want ORA-44787 – Don’t mess with the Default Oracle Service. What has happened? Very simple case. We approach a database upgrade with a Multitenant deployment of over 90 PDBs. Source database version is Oracle Database 12.1.0.2.BP170117, destination version is Oracle Database 12.2.0.1.RU170718. The upgrade runs fine. But…

Oracle Label Security (OLS) Clean Up in Oracle Database 11.2-12.2

Oracle Label Security (OLS) clean up in Oracle Database 11.2-12.2 is almost as simple and straight forward as removing OWM or APEX. You can do it online without downtime. But in older releases a relink operation did cause downtime. Oracle Label Security is an extra cost option. More information is available on oracle.com. Oracle Label Security (OLS) Clean Up in Oracle Database 11.2-12.2 Oracle Label Security is dependent on Oracle Database Vault (DV). In case DV is present you must remove it first if you plan to remove Oracle Label Security. Before you start removing anything from your database please…

Oracle APEX (Application Express) Clean Up in Oracle Database 11.2-12.2

Oracle APEX (Application Express) clean up in Oracle Database 11.2-12.2 is fairly simple and straight forward. With the upgrade to Oracle Database 12.2.0.1 Oracle APEX does not get upgraded automatically anymore. If necessary you should upgrade Oracle APEX upfront of afterwards. But please do keep an eye on the APEX certification with Oracle Database 12.2.0.1 to avoid any issues: Save Upgrade Downtime – Upgrade Oracle APEX upfront Upgrade downtime credited to APEX Is your APEX version certified with your database release? Oracle APEX (Application Express) Clean Up in Oracle Database 11.2-12.2 Before you start removing anything from your database please…

Remove and Clean Up Components from Oracle Database 11.2 – 12.2

I will start a series of blog posts on how to remove and clean up components from Oracle Database 11.2 – 12.2. Precaution This series of blog posts is not meant to recommend the removal of any options from an Oracle Database. It is not meant to create any sort of negativeness on any of the components or options. It’s is only meant to give you some advice and guideline in the case you’ll need to remove something. The OUI (Oracle Universal Installer) installed everything on disk – and there’s a very good reason to do this. The purpose of…

More Information about RU and RUR patches for Oracle 12.2

The first RU (Release Update) for Oracle Database 12.2.0.1 has been released. And Oracle Support provides some very helpful documents to explain the implication of the new RU (Release Update) and RUR (Release Update Revision) patches. More Information about RU and RUR patches for Oracle 12.2 First of all, these are the most important MOS notes to have a look at: MOS Note:742060.1 – Release Schedule for Current Database Releases (updated Aug 3, 2017 ) MOS Note: 2285040.1 – Release Update and Release Update Revisions for Database Proactive Patch Program MOS Note: 2275525.1 – FAQ for RU and RUR (not…

Alert.log: New timestamp format in Oracle 12.2

Timestamp Format Change There’s an interesting change in the alert.log since Oracle Database 12.2.0.1: The format of the timestamps has changed. If you compare the timestamps in Oracle 12.1 vs Oracle 12.2 you’ll recognize the change immediately: Oracle 12.1 Oracle 12.2 Wed Feb 08 15:39:29 2017 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE   MOUNT Wed Feb 08 15:39:29 2017 ALTER DATABASE OPEN Wed Feb 08 15:39:29 2017 Ping without log force is disabled. Starting background process TMON 2017-05-29T14:09:17.064493+02:00 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE   MOUNT 2017-05-29T14:09:17.252853+02:00 ALTER DATABASE OPEN…

Is “imp” still supported in Oracle Database 12.2?

Good question we receive quite often: Is “imp” still supported in Oracle Database 12.2? For clarification: I don’t talk about “impdp” but about old “imp” (import). And yes, it is still supported in Oracle 12.2. The old “exp” got desupported with Oracle Database 11.1.0.6 – but “imp” is still supported, even in Oracle Database 12.2.0.x. You should be able to import your old dumps into the newest release of the database and migrate even VERY old releases directly to Oracle Database 12.2. Actually, the biggest step upwards we’ve heard about a year ago was an exp from Oracle V5 and…

Issue with PDB Archives in Oracle 12.2.0.1 in ASM

There is a fancy new command to unplug a PDB in Oracle Database 12.2.0.1: ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO ‘pdb1.pdb’; The nice thing with this command differing in the file ending of ‘pdb‘ instead of ‘xml‘ as you used it in Oracle 12.1 (and the ‘xml‘ option is still available of course): Instead of just creating an xml description file it zips everything together into a PDB archive. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ———- —————————— ———- ———- 2 PDB$SEED READ ONLY NO SQL> create pluggable database PDB1 admin user adm identified by adm 2 file_name_convert=(‘/u02/oradata/CDB2/pdbseed’,’/u02/oradata/CDB2/pdb1′); Pluggable…

Unified Auditing – is it ON or OFF in Oracle Database 12.2.0.1?

Just a quick update to my blog post from September 2014: Unified Auditing – is it ON or OFF in Oracle 12c? Any changes in Oracle Database 12.2.0.1? Yes. Significant changes happen to Unified Auditing in Oracle Database 12.2.0.1. Mostly internally as the mechanism used to dump the audit records in Oracle 12.1.0.x when Unified Auditing was on turned out to be very good for write performance, but not so good when you tried to read data. See my blog post from a few weeks ago how to deal with this performance implication and a potential patch: Unified Auditing –…

SGA_MIN_SIZE in Oracle Database 12.2

I did summarize all new init.ora/spfile parameters in Oracle Database 12.2.0.1 a while ago: New SPFILE parameters in Oracle Database 12.2.0.1 And one caught Roy’s and my attention: SGA_MIN_SIZE. When we present this new parameter as part of the Multitenant slides in our workshops, some people ask themselves: “What happens if I oversubscribe such a parameter?” For example, if SGA_TARGET=4G and I have 10 PDBs and define in each of them SGA_MIN_SIZE=500M. Will the 9th and 10th PDB not startup anymore? Or will the 7th PDB already in trouble as the CDB$ROOT will need some SGA portion as well? None…

PGA_AGGREGATE_LIMIT enforces default since Oracle Database 12.2.0.1

The init.ora/spfile parameter PGA_AGGREGATE_LIMIT got introduced in Oracle Database 12.1.0.1. As per documentation in Oracle Database 12.1 it got defined as: “PGA_AGGREGATE_LIMIT specifies a limit on the aggregate PGA memory consumed by the instance.“. Furthermore the algorithm for its setting got described as: “By default, PGA_AGGREGATE_LIMIT is set to the greater of 2 GB, 200% of PGA_AGGREGATE_TARGET, and 3 MB times the PROCESSES parameter. It will be set below 200% of PGA_AGGREGATE_TARGET if it is larger than 90% of the physical memory size minus the total SGA size, but not below 100% of PGA_AGGREGATE_TARGET.” Default Value Change in Oracle Database…

Download Oracle Database 12.1.0.2 or older versions from the Oracle Software Cloud

I did blog a few weeks back about the download of older versions of the Oracle Database as OTN offers you only the most recent ones. In case you miss the downloads of Oracle Database 12.1.0.2 on OTN Looked pretty simply, huh? Well, here’s quick “how to” to access older Oracle Software downloads from eDelivery aka Oracle Software Cloud. Login to https://edelivery.oracle.com/, the Oracle Software Cloud Start typing “Oracle Database Enter” into the Search by field. It will display you the option below – mark it with a click: Then use the Select Platform drop down list to chose your…

Install components in Multitenant ALWAYS with catcon.pl

I did blog several times about how to remove an unwanted component from a database. But yesterday I came across this interesting scenario worth a blog post. How to install a component afterwards in Single-/Multitenant? I haven’t checked all the MOS Notes but I recognized that some MOS Notes explaining how to (re-)create a component such as JVM are not updated to deal with a Single and/or Multitenant environment as they simply call the scripts from SQL*Plus. But the key to script execution in such an environment is catcon.pl, the perl driver meant to execute database scripts not only in…

Release Dates Oracle Database 12.2.0.1 on-prem – Extended Support Waiving for Oracle 11.2.0.4 / 12.1.0.2

Yesterday night the most important MOS Note:742060.1 got updated with the planned release date for Oracle Database 12.2.0.1 on-premises. In addition, the dates for Waived Extended Support for Oracle Database 11.2.0.4 and 12.1.0.2 got extended as well. Please see: MOS Note: 742060.1: Release Schedule of Current Database Releases In summary: Oracle Database 12.2.0.1 for Exadata and SuperCluster is supposed to be released soon. Oracle Database 12.2.0.1 for Intel Linux x86 and Solaris platforms (SPARC and Intel) is supposed to be released soon after the Exadata/SuperCluster releaseFOR EXACT DATES PLEASE SEE MOS Note: 742060.1. The Free (Waived) Extended Support for Oracle Database…

Default Changes SPFILE Parameters – Oracle 12.2

This is the 4th posting in my series about init.ora/SPFILE parameters in Oracle Database 12.2.0.1. New SPFILE Parameters in Oracle Database 12.2.0.1 Obsolete SPFILE Parameters in Oracle Database 12.2.0.1 Deprecated SPFILE Parameters in Oracle Database 12.2.0.1 Default SPFILE Parameter changes between Oracle Database 11.2.0.4, 12.1.0.2 and 12.2.0.1 Roy and I did a comparison between default parameter settings in Oracle Database 11.2.0.4 vs Oracle Database 12.1.0.2 vs Oracle Database 12.2.0.1. And some changes are quite interesting – of course the memory driven parameters are left out in this list. Color RED marks a change between releases. The databases were all on…

Having fun with PDB LOCKDOWN PROFILES

In Oracle Database 12.2 (available in the Oracle DBaaS Cloud) there’s a new functionality called “PDB LOCKDOWN PROFILES“. Actually the parameter came already in undocumented and not functional in Oracle Database 12.1.0.2 (see here). PDB Lockdown Profiles PDB Lockdown Profiles are meant as a granular way to restrict or enable access – mostly under the aspect of using the database in a highly shared environment but requiring security. The idea is to embed restrictions on-top of a grant, i.e. taking away certain things. As an example you could allow a user logged into a certain PDB only to change the…

UKOUG: SIG (RAC, Cloud, Database, …) – 12 Oct 2016

Wow … time flies … I think it was February or March this year when I’d got invited to the UKOUG SIG for RAC, Database, Cloud, Infrastructure in London on Oct 12, 2016. Just a week ago I looked into my calendar and realized: Book a flight. Now! We have to arrange travel at least 7 days before departure as otherwise extra approvals may be required. Time flies … The room number in the picture above was the one of my hotel room in Baku earlier this year in May 😉 And of course on Wednesday I will talk about…

Oracle 12.2: OJVM will no longer support compilation of SQLJ source – and JPub does not get shipped anymore

In Oracle Database 12.2.0.1, OJVM will no longer support the compilation of SQLJ source or executing SQLJ classes. SQLJ on the client side will continue to be developed, shipped and supported.  Only the part inside the DB is affected. Existing SQLJ code that runs inside the DB will need to be re-coded to use generic JDBC. Furthermore JPub (client-side and inside-DB) will not get shipped anymore with Oracle Database 12.2. It got removed from the RDBMS code. There is no replacement. –Mike

OOW 2015 – Upgrade and Migrate to Oracle 12c Talk – Live and Uncensored – Get the Slides

Thank You, Thank You, Thank You!!! Roy and I were extremely happy yesterday as the room was full. Totally sold out. I’ve read on Twitter that some people wanted to go in and weren’t allowed to join as the room capacity was reached. If you want to get the few slides for our first talk: Upgrade and Migrate to Oracle Database 12c: Live and Uncensored! [CON6777] then please find them here: Slides But I’d recommend you the get the full picture as we did mostly a live demo – and the demo is not in the slides: Upgrade, Migrate &…

Why you seriously can’t wait for the second release!

Premier Support for Oracle Database 11.2 has ended 4 weeks ago at 31-January-2015.  I think most Oracle DBAs are aware of it. And I have stressed the topic about the need to upgrade to Oracle Database 12.1.0.2 a lot in the past months via the blog, in workshops and in discussions and customer meetings. But there are still plenty of people out there you would like to wait for Oracle Database 12.2, the so called “second” release. From looking backwards I can understand this thinking. Neither Oracle 9.0 nor Oracle 10.1 were the best and most stable releases ever. If…