COMPATIBLE is an almost mystic parameter. It has a default setting for each release. But if you try to find more information what it really does, you won’t be very happy. And in reply to my previous blog post about whether you need to change COMPATIBLE when you apply an RU, I received the following question: When and how should you change COMPATIBLE? What does COMPATIBLE do? To find an answer to this question, I consulted the documentation at first. And I found this: COMPATIBLE Setting COMPATIBLE ensures that new features do not write data formats or structures to disk…
Category Archives: Best Practice
Should you change COMPATIBLE when you apply an RU?
I have a million ideas for blog posts. But I like it even more when people ask me to explain something on the blog which isn’t there yet but may be interesting for others as well. And Robert Ortel mailed me the other day and asked if he should change COMPATIBLE when he applies an RU for Oracle 18c. That’s a good question. And I doubt that the documentation has a good recommendation. Should you change COMPATIBLE when you apply an RU? No. That’s a short blog post this time, isn’t it? 🙂 But honestly, you shouldn’t touch COMPATIBLE for…
Creating CDBs and non-CDBs with less options
I wanted to write up this blog post a long time ago. When Helen Vanderheide did ask me a few weeks ago if she can omit catoctk.sql during the creation of the database, I realized that I should update my previous blog posts about Creating CDBs and non-CDBs with less options. And of course, the following is usable with non-CDBs as well. You’ll find an example and especially the component dependency matrix below as well as many links to related blog posts at the end. If you don’t want to read all this, just scroll down to the Summary. Why…
New preupgrade.jar and changes in MOS Note:884522.1
We read and listen to your comments. And that’s why my team mate Byron has added a new preupgrade.jar and changes to MOS Note:884522.1. One reader commented that MOS Note:884522.1 (How to Download and Run Oracle’s Database Pre-Upgrade Utility) does not explain how to “run” the preupgrade tool even though it mentions this in the note’s title. New preupgrade.jar and changes in MOS Note:884522.1 With MOS Note:884522.1 you can download always the most recent versions of the Oracle 18c and 12.2 preupgrade.jar tools, of the Oracle 12.1.0.2 preupgrd.sql and utluppkg.sql. And you’ll even find the last utlu112i.sql for upgrades to Oracle…
Oracle Database 18c: A new preupgrade.jar is available
In case you plan to upgrade a database to Oracle Database 18c: A new preupgrade.jar is available for download from MyOracle Support (MOS). Oracle Database 18c: A new preupgrade.jar is available Please download it – as always – from MOS Note:884522.1. The same MOS note will get you also the most recent versions of the preupgrade.jar for Oracle Database 12.2.0.1 upgrades. Of course you can download also all previous incarnations such as preupgrd.sql for 12.1.0.2 upgrades and even utlu112i.sql for 11g upgrades. What’s new in preupgrade.jar for Oracle 18c upgrades? There are a few important and good Improvements and new…
Multiple Hops – Which should be the intermediate release?
This is a topic which doesn’t come up very often. But if it does come up from time to time, and the documentation may give you advice – but as I realized – it does not always give the best advice. When you have to do multiple hops – which should be your intermediate release? I posted something about this topic a while back: Multiple hop upgrades? Execute the matching preupgrade scripts for each hop What are Multiple Hops? We speak of multiple hops in relation to database upgrades when your source release does not allow you to upgrade directly…
preupgrade.jar January 2018 (Build 11) is available now
A new version of the preupgrade.jar January 2018 (Build 11) is available now. Please download it from MOS Note:884522.1.: Please always download the most recent version as this one is much newer with a lot of improvements compared to the version you get with the default installation. Major changes since the first drop are: Added load only capabilities. Improved the interaction with OS Added more obsolete/deprecated parameters Improved IPC calls on Windows Added functionality for aud$unified table partitions. Added preupgrade and postupgrade automatic fixups Check for JAVAVM mitigation patch Added parameters checking when a dynamic parameter has been changed at…
Book Recommendation: Oracle Database Upgrade and Migration Methods
Thanks guys, I received my copy 🙂 And today I’d like to write a book recommendation: Oracle Database Upgrade and Migration Methods. The book got authored by these 3 true database upgrade and migration experts: Y.V. RaviKumar (Oracle ACED and OCM) KM Krishnakumar (OCP) Nassyam Basha (Oracle ACED and OCM) And it’s cool book mostly for techies. It has plenty of examples in all details. A lot of good tips and tricks (for instance: Disable the Broker when you upgrade your database with standby in place). All of it from first hand from real world experience. The book covers topics…
utlrp.sql – How to decrease resource consumption with utlprp.sql
Just being back from Oracle Open World 2017 I’m working on the open topics, the homework, the follow-up activities. And I took several notes during OOW when customers asked me about things I’ve seen before. One topic I wanted to write about a long time ago already: utlrp.sql – How to decrease resource consumption with utlprp.sql. Where it started Actually it’s all Uwe’s fault (Uwe Kirchhoff is one of my best and oldest friends at Oracle and one of the best ACS guys I can think of). Uwe told me this story about a customer case he had years ago….
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…
The MAGIC Questions
Almost every week Roy, Carol and I receive one or more emails in the following style: “Hey, we (or my customer) plan(s) an upgrade to Oracle 11g. We (or the customer) wants zero downtime. Currently we (or they) are on AIX with Oracle 10g (and someold 9i) databases. Can we get an advice please?” or another one here … “Upgrade from 8i to 11g. The customer’s database is 28 TB (quite big!). Downtime is 5-6 hours. It’s on AIX. And it’s an it’s an Oracle EBS database” Well, in both cases we lack a lot of useful information – or…
Potential check for corruptions
Having a corruption somewhere in the database is one of the worst case scenarios I could ever imagine – especially if it “sleeps” somewhere in the data dictionary. Recently I did talk to a customer who encountered a failing upgrade due to a data dictionary corruption gotten introduced in an earlier release. What can you do to check your database(s) prior to an upgrade or generally from time to time? Actually I know now two powerful possibilities: hcheck.sql See MOS Note:136697.1 This script will check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g. You will need to create…
Will gathering fixed object stats reduce recompilation time post upgrade?
Interesting question, isn’t it? Will the time to recompile invalid objects post upgrade decreased once fixed object stats have been gathered? First of all fixed object stats on X$-tables won’t be gathered by default [This will change with Oracle 12c where it is part of the Auto Stats Gathering job]. X$ structures are undocumented. V$ views are build on top of them and should only be used even though it might be sometimes useful to access X$ tables such as X$BH (buffer headers – contains information describing the current contents of a piece of the buffer cache) sometimes directly. Anyway,…
Upgrade Fallback: Offline Backup??
When me an Roy talk about Fallback Scenarios in our Upgrade Workshop we cover first issues happening during the upgrade and second issues happening a few days (hours, weeks) after the upgrade. One of the fallback options for issues encountered during the upgrade is to take an offline backup. Ok, not a complete offline backup of your 24TB database. Take all data tablespaces into read-only mode, then shutdown the database. Take a backup of all datafiles for SYSTEM, SYSAUX and UNDO plus the controlfiles plus the redologs. TEMP files are not a requirement as you could also ALTER TABLESPACE TEMP…
Move to Locally Managed Tablespaces
As I’ve got asked during the workshop in Warsaw how to migrate Dictionary Managed to Locally Managed tablespaces here’s some additional information and an example. To find out if a tablespace is dictionary or locally managed you’d use this query: SQL> select TABLESPACE_NAME, EXTENT_MANAGEMENT from DBA_TABLESPACES; The procedure to migrate to locally managed tablespace is: SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(‘tbs’); And the whole migration procedure would look like this: STARTUP RESTRICT EXCLUSIVE; EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(‘TBS’); — do this for all tablespaces except SYSTEM, TEMP and SYSAUX DROP TABLESPACE TEMP; — necessary if there’s no “real” temp tablespace definded yet – see Note:160426.1 CREATE…
Cross-Platform Migration using Heterogeneous Data Guard
Most people think of Data Guard as a disaster recovery solution, and it certainly excels in that role. However, did you know that you can also use Data Guard for platform migration under some conditions? While you would normally have your primary and standby Data Guard systems running on the same OS and hardware platform, there are some heterogeneous combinations of primary and stanby system that are supported by Data Guard Physical Standby. One example of heterogeneous Data Guard support is the ability to go between Linux and Windows on many processor architectures. Another is the support for environments that…
What an upgrade scenario …
Do you understand this one here 😉 ? No?? Don’t worry 🙂 I’ve taken the picture last week when we’ve run a very special internal training called the “Upgrade Tech Challenge” here in Munich. This was actually the upgrade plan to 11.2 of one of the groups involving a Real Application Testing run with SQL Performance Analyzer (SPA) and taking care about the fallback as well. And as all groups completed their challenge by Friday I’d suppose at least the group itself understood what they wanted to do here 😉
Fix invalid objects and components – BEFORE you upgrade!
We are currently running a Tech Challange Workshop with 25 Oracle consultants and support folks from all over EMEA. We call it Tech Challange because we seperate these experts having between 5 and 20 years of Oracle experience into 5 groups – and each group has to complete their special challange such as moving a database from 10.2 to Exadata V2 or upgrading from single instance 10.2 to Real Application Clusters 11.2 with the new Grid Infrastructure. Actually we start this training with a bit presentation pieces about upgrades, Real Application Testing and Golden Gate. And one topic I always…
Gathering Workload Statistics
We refer in our upgrade talk to gather system (aka workload) statistics to give the optimizer some good knowledge about how powerful your IO-system might be. The optimizer since Oracle 9.2 takes CPU and IO costing into consideration. If you never have taken care on these stats you’ll find default values in AUX_STATS$. These values have been defined a few years back. So it might be a good idea right a few days after upgrading to the new release to create them while a real workload is running. Taking these stats does not generate any overhead or performance degredation on…
Remove “old” parameters and events from your init.ora
It’s cleanup time!! When upgrading to a new database release, especially Oracle Database 11g Release 1 or Release 2, you should always remove old init.ora parameters from the init.ora/spfile when you upgrade. For instance we’ve had a lot of useful (and necessary) tuning parameters in Oracle 9i environments such as _always_anti_join=off, star_transformation=false or several events. Make sure you remove all of them when you upgrade as keeping them will definitely slow down the database performance in the new release. You don’t believe this? Then see this result of a customer case having a packaged OLTP workload for the upgrade testing….
What does an upgrade has in common with mountain hiking?
You might ask yourself what an Oracle database upgrade and mountain hiking have in common? On first sight: really nothing. But if you come a bit closer and look more into the details there are some similarities. Maybe more than you’d thought at the beginning. Being well prepared is of vital importance for your upgrade success! First of all, likewise a hike an upgrade has to be prepared very well. You probably won’t do an 5 hour hike without reading a tour description beforehand, grab a good map, check your equipment the day before. And with an Oracle upgrade it…
How long will your upgrade take?
It seems to be a simple question but to predict the upgrade duration is really hard to do. Due to the fact that an Oracle database upgrade doesn’t touch or change data the duration is totally independent of the size of the database. It won’t make any difference if the database is 2 GB or 28 TB of size. The main factors determine the upgrade duration in descending order are: Number of installed database components and options – the more components/option gotten installed, the more upgrade scripts will have to be run, the longer it’ll take Valid and non-stale dictionary…
Is there a FALLBACK strategy?
Having a valid and complete backup before you start your upgrades is key. But how do you plan your fallback strategy? The first question you’ll have to ask is: Are you allowed to loose any data updates in case of going back – Yes or No? If you say “Yes we can!” because you would be able to redo any changes you’ve done to the upgraded system already once you’ll go back, then restoring your backup from before the upgrade could be your easy fallback strategy. You don’t have the possibility to do a complete online backup (maybe you don’t…
Backup your database!!!
Yes, I appologize for writing this sentence … “Backup your database!!!” … it sounds a bit frumpy, such as something from the past. But still nowadays with those high-class super storage solutions a valid backup can become of vital importance. Especially before you start the upgrade process. RMAN (Oracle Recovery Manager) does a great job in backing up your database completely online. Have I said online? Yes!! Even though some Metalink notes still recommend to do an offline backup you shouldn’t stop your 58 TB data warehouse database for 2 days to do a complete offline backup. Use RMAN for…
Upgrade Companion – Read it first!
Customers and Oracle partners were asking for a long time about a comprehensive note about upgrades. Often questions were raised such as “Why has this default setting changed and where has it been documented?“, “How do I preserve performance information before the upgrade to ease diagnosis just in case something happens?” or “What’s new with the optimizer?“. To answer these and many more questions in late 2007 the first Upgrade Companion for Oracle Database 10gR2 has been published on Metalink. Later the Companion for 11g got published as well – and as soon as Oracle Database 11gR2 gets released the…