Oracle Database Migration from to 10.2

Posted on 2006-10-28
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 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

Question by:Tolomir
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
LVL 10

Expert Comment

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..
LVL 27

Author Comment

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?

LVL 10

Expert Comment

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..
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

LVL 27

Author Comment

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?


LVL 10

Expert Comment

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..
LVL 18

Accepted Solution

rbrooker earned 500 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 :)
LVL 27

Author Comment

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?


LVL 18

Expert Comment

ID: 17835705

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 :)
LVL 27

Author Comment

ID: 17887223
We used

http:Q_22040794.html#17831461  as solution.

The DBUA failed.

Thank you.


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

Suggested Solutions

Title # Comments Views Activity
form builder not starting 3 73
Oracle Insert not working 10 48
SQL query for highest sequence 4 64
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

730 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