RE: 10g upgrade survey

I don't want to import from the Full Export I do out of the "old" database (in case of conflicts in System objects), so I've developed simple scripts that I run in the "old" database that produce scripts to do the following in the "new" database:
- Create Users and grant them all the Roles and System Privs they have, as
well as tablespace quotas.
- Create Public Database Links and Public Synonyms for non-System objects.

• Create an Import *.par file with identical FromUser and ToUser lists of all non-System (Sys, System, CTXSys, MDSys,etc.) Users. That way I can also exclude Users/Schemas I don't want in the "new" DB.

Then I import into the "new" DB from the Full Export of the "old" DB using that *.par file. That creates all the User objects, private DB Links, private synonyms, etc. Works great.

Jack C. Applewhite - Database Administrator Austin (Texas) Independent School District 512.414.9715 (wk) / 512.935.5929 (pager)

 Same-Day Stump Grinding! Senior Discounts!

• Mike's Tree Service

"Sweetser, Joe" <Joe.Sweetser_at_infocrossing.com> 03/05/2007 01:39 PM

To
<Thomas.Mercadante_at_labor.state.ny.us>, <JApplewhite_at_austinisd.org>, "Oracle Discussion List" <oracle-l_at_freelists.org> cc
<oracledba.williams_at_gmail.com>
Subject
RE: 10g upgrade survey

To all who use #3 (newdb, exp/imp) –  

How do you typically do the imports? Schema by schema? Full? Other?  

Just call me Curious Joe.  

-joe
 

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mercadante, Thomas F (LABOR) Sent: Monday, March 05, 2007 11:59 AM
To: JApplewhite_at_austinisd.org; Oracle Discussion List; oracle-l-bounce_at_freelists.org
Cc: oracledba.williams_at_gmail.com
Subject: RE: 10g upgrade survey  

Jack,  

Just for the problems that you mention, I prefer to create a new database and import the data. I’ve had strange bugs crop up that, to me, just are not worth risking. I’d rather create a new db and import the data.

Tom    

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of JApplewhite_at_austinisd.org
Sent: Monday, March 05, 2007 1:33 PM
To: Oracle Discussion List; oracle-l-bounce_at_freelists.org Cc: oracledba.williams_at_gmail.com
Subject: Re: 10g upgrade survey  

Dennis,

We almost always use option 3, for a variety of reasons, depending on the database in question:
- Have to pre-create the database in advance of a cut-over of a Production
DB to the new version.
- Want to take advantage of a new feature that would be difficult or
impossible to retrofit to an upgraded database.
- Moving a database from, say, 32bit Linux to 64bit Linux, and upgrading
at the same time.

We also had a problem after an upgrade of an HPUX database from 8.1.7.4 to 9.2.0.4. Right after the upgrade, our 3rd Party Financials app, accumulated about 60,000 views. After we dropped those nasty things, that DB's Data Dictionary was somehow damaged - did not shrink. Queries against DBA_Tables, DBA_Indexes, etc. took over a minute. Other 9.2.0.4 DBs, even one where we drop and re-create 125,000 table and 180,000 indexes every week gave sub-second responses to the same queries. After pre-creating a from-scratch 9.2.0.4 DB and importing the contents of the slow one, we got the same sub-second response.

Anyway, we prefer to pre-create and transfer data. Caveat - our largest databases are only around 100GB. With huge databases, moving data around isn't nearly so easy.

Jack C. Applewhite - Database Administrator

"Dennis Williams" <oracledba.williams_at_gmail.com> Sent by: oracle-l-bounce_at_freelists.org 03/05/2007 12:09 PM

Please respond to
oracledba.williams_at_gmail.com

To
"Oracle Discussion List" <oracle-l_at_freelists.org> cc  

Subject
10g upgrade survey      

List,  

If you've upgraded more than one database to 10g, please let me know what method you chose:  

1. dbua
2. Manual upgrade
3. Created a 10g database and migrated the data.

Either reply to the list or to me directly. Also please let me know why you chose the path you selected and if you had to do it again if you'd done it differently.  

Thanks,
Dennis Williams  

--

http://www.freelists.org/webpage/oracle-l Received on Mon Mar 05 2007 - 13:53:49 CST