Solved

Oracle Database Migration from 9.2.0.4 to 10.2

Posted on 2006-10-28
9
1,104 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 10

Expert Comment

by:ravindran_eee
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 27

Author Comment

by:Tolomir
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 10

Expert Comment

by:ravindran_eee
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
We used

http:Q_22040794.html#17831461  as solution.

The DBUA failed.

Thank you.

Tolomir
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now