Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle Database Migration from 9.2.0.4 to 10.2

Posted on 2006-10-28
9
Medium Priority
?
1,115 Views
Last Modified: 2012-06-27
Hello we would like to migrate a "complex" database with 50 different users to the up to date oracle 10r2  version (+ fixpack + CPU)

The source platform is debian woody and target would be either debian sarge or SLES 9/10

With exp/imp I was able to dump and reimport the complete database onto another computer with SLES 9 (test machine) with the same oracle 9.2.0.4 version.

I was already able to solve some ora-xxxx's but the main problem persists: Is is wise to replace the system tables from the target database or is there a better way to migrate all these user accounts. (Since we don't know each single account's password at once it would be cool if we could extract somehow the password for each account, even a hash would do the trick that could be entered into the target database.)

Later I'd like to use "dbua" from oracle 10r2 to upgrade, but until that I'd prefer to have a rather 1-to-1 copy on the test machine to get valid results from the upgrade.

Any ideas?

Procedure:  
1.) Export from: productive environment: Debian woody + oracle 9.2.0.4 -> test machine: SLES 9 + oracle 9.2.0.4
2.) Upgrade with dbua: on test machine (SLES 9 +oracle 9.2.0.4) -> test machine: (SLES 9 + oracle 10r2)
3.) If successful: Export of database to new productive machine with SLES 9/10 or debian sarge + oracle 10r2

Tolomir
0
Comment
Question by:Tolomir
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 10

Expert Comment

by:ravindran_eee
ID: 17825821
First let me be clear with ur question.. U are doing a full export of ur database.. In this case the user schema will get exported automatically.. The passwords of the users will also be retained..
Now u would like to set ur own passwords for all the users.. Is this what is needed? If that is the case then the only option is to import each users data seperately.. if u have 50 users then 50 dumps needs to be exported and imported back into 50 user schemas.. probably these can be done by automated scripts..
0
 
LVL 27

Author Comment

by:Tolomir
ID: 17825909
Hello ravindran_eee, thanks for your answer,

I've done the full export and copied and imported that dump to a test database on another computer.
During the import I got several errors for the system tables, synonymes etc... , so I'm not sure if the test database is in a healthy state after the import. The user "tables & stuff" were imported without problems though.

So since I cannot guarantee a successful import, I'm thinking about plan B: exporting each user and importing him to the target DB. But in that case I would loose the password and also had to make sure that the needed tablespaces do exist.

Plan C would be to convert the physical machine into a vmware session and make all the upgrade tests, but since we are mostlikely exchanging the old productive platform with some new hardware, I would have the problem of migrating all accounts again.

Basically I'm looking for the best way to upgrade the oracle database to 10g R2, involving upgrade to a new server and  a compatible OS.

Any ideas?

Tolomir
0
 
LVL 10

Expert Comment

by:ravindran_eee
ID: 17827796
Plan A:
A full export dump should not contain any SYS objects.. However it will have the objects owned by SYSTEM user. When performing a full import, most of those objects will already exist and you will get an error, so those can be ignored.. still this is what I can tell without seeing the error messagess..

Plan B:
This will require lots of manual work.. U can log in as SYSTEM user and query the dba_tablespaces view for getting information about the tablespaces.. in case u have the scripts for creating them then just apply them on the target DB as well.. If not u will have to write them again with the information from the view that i have stated..
next step is to create the user schema.. for this get the info from "select * from all_users".. the catch is that u cannot get the password from DB! so u will have to provide the password for each user manually..
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 27

Author Comment

by:Tolomir
ID: 17828076
Hmm plan A sounds promissing.

Yes indeed it's the system user. So I will check these errors then on monday. All I want to prevent is an unstable database and I just don't know if I don't mess it up by mixing the system data fro the target DB with the imported one...

Is there maybe some kind of sanity check I could use?

Tolomir





 
0
 
LVL 10

Expert Comment

by:ravindran_eee
ID: 17829198
Ok.. Please check out the error messages..
In the mean while I would check out whether there are any utilities which would get u the differene between 2 databases..
0
 
LVL 18

Accepted Solution

by:
rbrooker earned 2000 total points
ID: 17831461
you can get everyones passwords with :

select 'alter user ' || username || ' identified by values ''' || password || ''' ;'
from dba_users

you can :
- run the query, spooling out to a file
- reset everyones password to blah
- do what you have to do
- reset everyones password by running the spooled output from the above query.

or to reset the users passwords in the target db, simply run the spooled output.
this will reset everyones password to what it was before you reset them.

good luck :)
0
 
LVL 27

Author Comment

by:Tolomir
ID: 17831895
Cool, my problem is: we are using oracle just as database.

As frontend we got in most cases webapplications from different developers, some of them are even gone. Maintainance is done by us the IT and an editor to keep the content up-to-date. These applications got the needed password buildin, so we cannot simply change these passwords in the DB.

So even though we are not running any mission critical programs I'd like to keep the update time as low as possible. But I also have to be sure the database is safe and sound after the upgrade.

May I ask you, rbrooker how you plan such an upgrade? Is oracle's dbua reliable?

Tolomir



0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17835705
Hi,

i have used dbua to upgrade 350 - 400 GB of datawarehouse 5 times, 3 dev instances, a test and then production.  i had trouble with one, there was an obsolete initialisation parameter that i had to remove, other than thet, i have had no trouble.  that was 9206 -> 10105.  it took about 2.5 hours to do and was really straight forward.  there was no resetting of passwords required.

i would get a test instance running first, point some test web apps at it and then upgrade it, make sure all the web apps continue to function.

also, straight after the upgrade, refresh your statistics.  all your explain plans will have changed - we were caught by that one.

good luck :)
0
 
LVL 27

Author Comment

by:Tolomir
ID: 17887223
We used

http:Q_22040794.html#17831461  as solution.

The DBUA failed.

Thank you.

Tolomir
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question