Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite
4

How long would take to upgrade an Oracle DB which has 1T data from 10g to 11g usually/roughly? I need to estimate the down time for it since it is a prod db. Thanks much!

share|improve this question

migrated from stackoverflow.com Sep 6 '11 at 22:09

2 Answers

up vote 10 down vote accepted

30-90 minutes according to Oracle's Best Practices for Upgrading. This is about the closest estimate you will get given all the unknowns in this situation.

The size of the database really matters very little in determining how long the upgrade will take. Here are the main factors effecting the duration (from the Oracle.com upgrade blog):

  • 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 restricted upgrade mode causing additional 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

Here are some additional factors you may want to consider that aren't related to the core of the upgrade itself:

  • Whether the pre-upgrade script has been run and issues resolved.
  • How many invalid objects there are.
  • If the upgrade is being done in place (vs. Import/Export, Streams, Data Guard, etc.)
  • Whether the DBUA or scripts are used to do the upgrade.
  • If new Oracle home pre-installed.
  • Disk speed and throughput.
  • Other CPU/Disk activity occurring at the same time.
  • Archive Log Mode.
  • Other changes being made with the upgrade.
  • If a cold backup is necessary before and/or after the upgrade.
  • Any patch sets or one off patches that will also be applied.
  • How much verification of the upgrade needs to be done before it can be made available.

Probably the biggest factor effecting the upgrade is the unknown factor. Even when the upgrade is practiced in advance on similar hardware with similar data sets, etc. things can still occur that were unanticipated and can drastically effect the duration. With that in mind you should mimic the production environment as closely as you can for the test upgrades. That is, as close as your budget will allow.

If space is the issue preventing you from testing the upgrade, consider restoring the database to a test box excluding some of the larger user tablespaces. This won't give you an exact feel for the time, but it should give you a closer ballpark and allow you to work through more of the unknowns.

share|improve this answer
Very thoughtful Leigh, you are right on. Thanks much! – magqq Sep 7 '11 at 21:33
up vote 5 down vote

Presumably, you have a development and test instance of this database running on similar hardware with a similar data volume and the same database components installed, correct? And, presumably, you will be upgrading these lower environments (and testing that whatever applications use this database still function correctly), correct?

Assuming that is the case, I'd time how long it took to upgrade the development database and use that as your estimate of the time required to upgrade the other instances. There are, obviously, a number of factors that determine how long the actual upgrade will take. My guess is that the downtime would likely only need to be an hour or two but you're much better off using the actual time required to upgrade dev.

share|improve this answer
1  
let's presume he's at a cheap shop, without a similar environment in the testing/dev facility as in production. If that is the case... what's a good enough advice? :-) – Marian Sep 6 '11 at 22:36
1  
@Marian - As I said, my guess without any information is an hour or two. But if you're running at a cheap shop that doesn't even purchase one adequate lower environment, I'd massively pad my estimate because I'd assume that something would go wrong during the upgrade. If you're not going to spend on building appropriate lower environments, you can't expect great levels of uptime. – Justin Cave Sep 6 '11 at 22:45
1  
Even if you have a crap dev platform that'll give you a decent ball park number to work off of. Take that number and if it feels a little low, double or triple that and give that number to management. – mrdenny♦ Sep 6 '11 at 23:01
2  
@Marian - The wise folks know, though, that it's far better to overestimate a downtime window than to underestimate it. If an organization doesn't want to provide an appropriate environments for a DBA to get reasonable metrics, they have to accept a much larger downtime window than they would if the lower environments were appropriately provisioned. – Justin Cave Sep 7 '11 at 1:22
1  
@magqq - The upgrade itself is not data dependent normally. But since you generally want a complete backup prior to an upgrade, the step of taking the backup is highly dependent on the size of the database. – Justin Cave Sep 7 '11 at 1:28
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.