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 statistics – even though creating dictionary statistics in some older releases of Oracle wasn’t a brilliant idea since desupport of the rule based optimizer the data dictionary should be analyzed. Especially right before an upgrade. Otherwise this will happen during the upgrade while the database is started in a restriceted upgrade mode causing addtional downtime.
- Number of rows in AUD$ if audit_trail is set to DB
- Number of synonyms when upgrading from Oracle 9i – synonyms will be touched and get a new dependency in the dictionary in DEPENDENCY$ – if there’s a high number (such as 100,000) this can eat up some time
- Number of objects in XDB
- At a very low rate if COMPATIBLE will be increased: Number of datafiles and the size of the redologs
though listed key factors may impact downtime during upgrade,but we still can give customer a clear answer how long their db upgrade will take.
Hi Weimin,
would be interested how you do this – I can give an estimate, too – but no exact duration without having tested it – so feel free to let us know 🙂
Kind regards
Mike
Hi Mike,
You mention that ,”it would be a good practice to analyze the data dictionary right before an upgrade.”
I am curious to find out if this can adversly impact the performance of some the query in the database, by changing the execution plan etc.
Is that something I should be worried about, or analyzing the data dictionary won’t impact the execution plans of the SQL queries ?
Regards
Ashish
Analyzing the Data Dictionary is not a great idea in Oracle 9i – but doing it right before the upgrade will just save upgrade time as otherwise it will be done during the upgrade and this adds downtime to the upgrade itselves. It won’t effect execution plans.
Kind regards – Mike
I am applying the latest Patch 26635880 on Oracle 12.1.0.2 and so far 5 hours has elapse and the process is still. I did not analyze the dictionary before applying the patch. I assume this is the reason for the long duration. I am learning from this and will share the actual time it took with details of my environment and the approach used.
Eric,
what exactly did take so long? Applying the patch with “opatch” or executing “datapatch -verbose”?
Is this a RAC environment or a single instance env? And does the database reside in ASM?
Cheers
Mike