Upgrade to 10g from 9.2.0.6 2

[KenCunningham]

Hi folks. Hopefully just a quick one. Is it possible/advisable to upgrade a database directly from 9.2.0.6 to 10.2.0.1 without applying the 9.2.0.7 patch first? Thanks.

I want to be good, is that not enough?

 

[SantaMufasa]

Ken,

I don't mean to spook you about a 9.2.x to 10.2.x upgrade, but whenever I have done so, I have found much greater success by installing a fresh 10.2.x install with a new, empty database, then I did an exp-imp or data-pump move of the data from 9.2.x to the new 10.2.x database. Not only have I never had errors that way, the data gets defragmented and reorganised, as well.

I have had unsavory results when I depend upon Oracle to do an upgrade-in-place of the data.

Perhaps others have success stories of in-place upgrades, but I am simply unlucky in that endeavor.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[KenCunningham]

Thanks Dave, I'm suitably spooked (no bad thing).

In fact, I was thinking of one of these options (probably exp/imp) myself and will try it over the weekend. That said, I successfully upgraded a test environment from 9.2.0.7 to 10.2.0.1 earlier this week using the 10g dbua and experienced no problems. I might have the time (if not the patience) to try both methods for comparison.

We'll see - and I'll report back.

I want to be good, is that not enough?

 

[SantaMufasa]

Cool...I'd be interested.

Also, if I'm not punctual today with responses to any threads, it's because I'm teaching a "Precision Writing and Speaking" class from 8:30 a.m. to 5:00 p.m. (MDT).

Have a good day, all.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[hoinz]

Dave and Ken,

what database sizes are you talking about?

Currently we are upgrading some databases, too. During the last few weeks I upgraded a few test and development systems from 9.2.0.7 to 10.2.0.2, using dbua, with some minor problems. Our most important production system still has do be done, and I think I will use the same method. The size is around 2.2 TB. And I doubt that exp/imp would be feasible at all.
My two concerns are downtime and disk space.
The upgrade method would need one or two hours at most; tested with another system of about the same size; size shouldn't be decisive with this method.
exp/imp would need 16 hours or more. This number comes from a hardware and OS migration we did half a year ago.
And we would need three times the disk space of 2.2 TB (old - dmp - new). Ok, maybe just two times, if we delete the old data files before importing, or if we replace dmp by some clever Unix pipe trick, or maybe Oracle data pump (never tried that so far).
The defragmentation argument isn't that important for us, because that was done during the migration only half a year ago.

What do you think?

 

[KenCunningham]

Hello hoinz, as always nice to have your input. Our databases are nowhere near the size of yours (< 100 Gb in each case), so it is difficult for me to visualise the extent of your operation.

However, I would say that if the dbua method working for you, albeit with a few minor issues (it would be good to know what these were if you can recall), that's the way I would go too. Having said that, like you I have no experience with the data-pump method either.

Did you move to 9.2.0.7 deliberately for the upgrade or were you there by natural progression as it were?



I want to be good, is that not enough?

 

[hoinz]

Ken,

Thank you for your feedback.

So far I upgraded three databases from 9.2.0.7 to 10.2.0.2.
I needed several tries with dbua for two databases.
There were error messages because of missing directories or missing permissions; I don't recall what it was exactly. When the problem was solved, and I clicked the retry-button, this didn't help in one case; I had to restart dbua.
Then I found out that dbua obviously had updated the oratab file with the new Oracle 10g home directory, and for a restart I had to reset this.
In one case dbua hung at 8 or 9 percent, nothing happend, I had to kill all the Unix processes and restart. Maybe in this situation dbua was waiting for input in a window that had disappeared, or that I somehow had managed to hide by clicking around a lot, changing windows all the time.
And worst of all, once it aborted with ORA-3113 end-of-file on communication channel.
In this case I did a restore from backup, but not sure if this really had been necessary. (It was later on I found out about oratab file!). But, well, it was a good exercise.
My guess is that this Oracle error was caused by some resource bottleneck at either the database server or the workstation where dbua GUI was running. I remember having clicked around a lot, trying to do serveral things at the same time.

In short, what I have learned from my tests is this:
1) While running dbua, do not unnecessarily switch between windows. Don't do other tasks at the same time.
2) You may have to restart dbua. In this case check oratab file, and reset the old Oracle 9i home directory.
3) If everything else fails, have a good backup.

And regarding versions:
Somwhere I read that dbua will check if your database is at 9.2.0.4 or later, and won't run otherwise. But this is no answer to your question if it is good idea to upgrade from 9.2.0.6 directly.
We had upgraded most of our Oracle databases from 9.2.0.x to 9.2.0.7 during our migration from Tru64 Unix to HP-UX/IA64 last year. (Digital Unix aka Tru64 Unix is bound to die; what a pity!) And I think it was a wise decision not to upgrade to 10g at that time.

What's most alarming imho is the large number of patches (one-off patches, merge patches) that are needed for Oracle 10g. I doubt it is a stable product so far...

regards

 

[SantaMufasa]

Hoinz, Very helpful, valuable post (especially for those anticipating such an update). Hava

.

Ken, what Hoinz describes is roughly the (painful) scenario that I recall...And that's why I use the method(s) that I mention, above. Luckily, the largest db against which I have had to conduct such an upgrade was only about 125GB...it took the better part of a day to import the data, but we had the luxury having people off the application during the weekend that this occurred.

The big problem here (IMHO) is that Oracle has not provided a high-enough-quality migration tool...At best, a shame.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[KenCunningham]

Thanks guys, your help is really appreciated. Well, here goes....see you on the other side.

Fingers crossed and all that (though I do tend to find it difficult to type like that!).

I want to be good, is that not enough?

 

[hoinz]

Dave,

thank you very much for the star.

And let's hope my post will be useful for others...

 

[KenCunningham]

Not finished yet - will make a full report when I am, but Dave's dbua spooks were well and truly out in force at the weekend.

I want to be good, is that not enough?

 

[KenCunningham]

I am finished. Literally!!

We eventually got the databases upgraded, but not without a significant extra amount of work occasioned by the the dbua. Our first problem arose when the dbua wouldn't proceed past an error message to the effect that:

For input string: ""
Upgrade configuration file
/path/to/file/upgrade.xml
is not a valid xml file

As the dbua created the upgrade.xml file itself one has to wonder how it came to create it as an invalid file. A search on the web indicated that the problem might occur if the user sys' temporary tablespace in the 9.2 database is dictionary rather than locally managed. Creating a locally managed tablespace and assigning it as sys' temporary tablespace. Subsequently, and after further problems, I had to alter system's temporary tablespace similarly to get past the dreaded Red Alarm Bell. For completeness, the following website gave me the answer:

http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1251223,00.html

The dbua then carried on and completed within a couple of hours. Blithely having thought the problem had been solved, I set off the next upgrade, waited 'till it had reached 54% then decided to leave for home on the assumption that I could then come in the following morning to another complete upgrade. Wrong! According to the upgrade logs, the dbua ran into an ORA-4031 error indicating a lack of shared pool available. I find this particularly odd when one considers that the dbua is supposed (as far as I have been able to ascertain) to do the necessary tuning for the upgrade itself to avaoid this kind of occurrence.

What I also find rather galling is that rather than report the ORA-4031 state of affairs through the GUI, it continues to increment the progress bar as if nothing is amiss. Very strange behaviour in my view, and liable to waste a lot of time if one is not monitoring the upgrade logs.

Dave - sorry that due to having to carry out restores at several stages, I was unable to try the other method. However, I am hopeful that the lessons learned here will be valuable when we come to do the live upgrade in July.


I want to be good, is that not enough?

 

[hoinz]

Ken,

That's not very encouraging ...


I still think I will have to go the dbua way for our production system. But I will wait a few days longer, and look for more info, before I will dare.
I will have to rely on production behaving like QA did. QA was copied from Prod a few months ago.

My current concern is performance degradation in the upgraded systems. But now it seems that once again checking and adjusting Oracle parameters, especially those regarding optimizer, should have solved it.

regards

 

[KenCunningham]

Hoinz, I wouldn't be too disheartened. Unfortunately I only had another (entirely different) database on which to practice, so it's maybe the case that if your prod. system is set up exactly like your QA, you should be OK. Here's hoping anyway, and another star for your support in this.

I want to be good, is that not enough?

 

[hoinz]

Ken,

thank you for your encouraging words,
and thank you for the star!

Fyi, I dared to do an upgrade in our prod system last weekend, and dbua ran without any problems; it was what one could have expected after the test on QA.
However users were not happy with response times afterwards; we found out that a mix of old and new Oracle parameters had resulted in an SGA way too small. Now all looks good again.
Several other databases have to be upgraded in the next months, but the most important system is done.

And good luck for your upgrade, I'll keep my fingers crossed!

 

[elsimo]

Hi all,

I am about to upgrade a 9.2.0.7 database to 10.2.0.1 on another unix server, both machine have same configurations and same file sturcture, everything is the same.
I am thinkig to take a full backup of the 9i db and restore on 10g then upgrade it.
do you guys think this aprrocah will work?
I am using RMAN for backup and restore.
Thanks,
Simo.