Oracle Database Migration from to 10.2

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 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?

1.) Export from: productive environment: Debian woody + oracle -> test machine: SLES 9 + oracle
2.) Upgrade with dbua: on test machine (SLES 9 +oracle -> 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

LVL 27
Who is Participating?
rbrookerConnect With a Mentor Commented:
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 :)
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..
TolomirAdministratorAuthor Commented:
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?

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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..
TolomirAdministratorAuthor Commented:
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?


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..
TolomirAdministratorAuthor Commented:
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?



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 :)
TolomirAdministratorAuthor Commented:
We used

http:Q_22040794.html#17831461  as solution.

The DBUA failed.

Thank you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.