Solved

Moving Data from One Server to Another

Posted on 2007-04-09
17
1,852 Views
Last Modified: 2013-12-19
I receive new server with better hardware and want to move the production database from the old server to the new server.
basically want to migrate from oracle 9i release 2 to oracle 10G on the new server with minimum down time i have four schemas (40GB data in total).

What is the best way to move this to the new server with minimum downtime.
0
Comment
Question by:imran_fast
  • 6
  • 4
  • 3
  • +3
17 Comments
 
LVL 4

Accepted Solution

by:
ramumorla earned 300 total points
ID: 18880702
do the following

Step 1: On old server export database onto a XXX.dmp file 9i exp command
step2: ftp the xxx.dmp file to new server
step3: import the dmp file using imp command of 10g

this is the easiest
0
 
LVL 8

Assisted Solution

by:gvsbnarayana
gvsbnarayana earned 100 total points
ID: 18880880
Hi,
Backup/restore is the best option with 40GB of database, even though export/import will not require you to have both 9i and 10g on the same system.
Your current scenario is migration from 9i to 10g and also change of hardware. You will need both 9i and 10g on any one of the systems.
1. backup from old system
2. install 9i and 10g on new system
3. restore the backup onto new system
4. upgrade from 9i to 10g on new system
5. uninstall 9i on new system.

You can also do the other way:
1. Install 10g on old system
2. upgrade from 9i to 10g
3. backup 10g database
4. restore the backup onto new system

HTH
Regards,
Badri.
0
 
LVL 4

Expert Comment

by:ramumorla
ID: 18880999
Hi Badri,

What is the need of instaling old version and upgrade it to new version? Its criminal wastage of time and also Oracle export/Import is existing for the same exact reason- to copy the database from a lower version to upper version
0
 
LVL 28

Author Comment

by:imran_fast
ID: 18881122
@ramumorla
Creating dump will take 3 to 4 hours i have 26 GB of images data that alone will take 6 hours so total of 10 hours and import will take around 10 hours as well so you see the downtime will be 24 hours.

@gvsbnarayana
bot version on same machine then upgrade ok.

any other suggestion i want to minimize down time .
0
 
LVL 7

Assisted Solution

by:Fayyaz
Fayyaz earned 100 total points
ID: 18881399
You can do the tasks in pahses
phase 1). Move the 9i database to new server
phase 2). upgrade the 9i database to 10g (on new server)

Phase 1).
1). Take the cold backup of 9i database
2). Restore it on the new server
3). Test that every thing is OK on new server.

-- downtime will only be the time to take cold backup and restore

If phase 1 completes then go for phase 2. Don't mix both tasks. Because if something goes wrong, then it will be very difficult for you to trace what happened.

Run the 9i database on new server for one or two weeks. if everything is OK, then plan the downtime for upgrade. In this way, your users did not feel downtime of 10 hours.
May be for phase 1 your downtime is 6 hours and for phase 2 your downtime is again 6 hours. so your users feel that the downtime is 6 hours only (but off course actuall y it is 12 hours for example)

Phas 2).
1). Use DBUA to upgrade the database. or
2) use manual upgrade method

0
 
LVL 7

Expert Comment

by:gattu007
ID: 18881913
The best option would be

If it is same plarofrm.. then

++ Install 9i on new server
++ use transportable tablespace option to move data  
++ Now upgrade to 10g on new server.

or

++ upgrade to 10g in old server
++ Install 10g on new server.
++ use transportable tablespace option to move data (in 10g you can do it in cross platform also)

with the above you will be saving lot of time
0
 
LVL 8

Expert Comment

by:gvsbnarayana
ID: 18881933
Hi ramumorla,
   export/import is a time consuming process and does have lots of implications.
Can you guess how much time will it take to export and how much more time is needed for an import of a 40GB database?
I accept the fact that export/import is a very good tool for upgrade but this option looses battel when the volume is high.
HTH
Regards,
Badri.
0
 
LVL 4

Expert Comment

by:ramumorla
ID: 18881982
How can you say export import is a time consuming process? Are you doing on the network or on the sever? Please read my steps,  I wanted to run the export on the server itself.
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 7

Expert Comment

by:gattu007
ID: 18882012
Below metalink references may help you

Subject:       How To Use RMAN CONVERT DATABASE for Cross Platform Migration
        Doc ID:       Note:413586.1


0
 
LVL 8

Expert Comment

by:gvsbnarayana
ID: 18882233
Hi ramumorla,
    Yes, I have tried many times, export of a 5 GB of data is completed in less than one hour and then import takes 4-6 hours on our high end database servers on the same host.
Import is always 4-6 times slower than export. You may try on large volumes once.
I repeat, export/import is one good tool for smaller volume upgrades.
Regards,
Badri.
0
 
LVL 28

Author Comment

by:imran_fast
ID: 18882451
@ramumorla
Hi the export is taking 6 hours and the import is taking the same.


Let me look in to the transportable tablespace option.
0
 
LVL 3

Expert Comment

by:kimarti3
ID: 18884387
Use datapump instead of export/import.  It's much more efficient.  It will take a while, but it is much better than export/import is.  Plus, after 10g, export/import will no longer be supported.  We just migrated our database (3 terabytes) from one server to another and it took all day, but datapump was our only solution.
0
 
LVL 4

Expert Comment

by:ramumorla
ID: 18886696
Export data pump is not available for 9i
0
 
LVL 4

Expert Comment

by:ramumorla
ID: 18887152
Export import is the only way to do this in the most cleanest and simplest way, while if you consider time required to install 10g on the old machine and upgrade , backup, restore on a new machine, export is the simplest way... Please go through the process below.

_____________________________________________________________________________________________

To obtain the cleanest possible export, run INVALID.SQL script to check for invalid objects on 9i DB (source server) and run VALIDATE.SQL (UTLRP.SQL?)to recompile any invalid objects

Export the 9i DB

Shutdown the 9i DB to stop users from establishing a session

Copy the Oracle 9i EXPORT.DMP and IMPORT.PAR files to the 10g DB (destination server)

Ensure tablespaces have been created/configured in the 10g DB to coincide with the 9i DB

Take the 10g DB out of ARCHIVELOG mode

Import the 9i DB into the 10G DB

Check import logs for errors

Reset passwords for system (and other) 10g accounts back to the original 9i DB accounts

Make sure users are pointing to proper tablespaces

Run INVALID.SQL script on 10g DB again to check for invalid objects and run VALIDATE.SQL (UTLRP.SQL?) to recompile any invalid objects

Recompile all PL/SQL modules that may be in an INVALID state, including packages, and types on 10G DB using UTLRP.SQL (is this same as above?)

Check the local applications and DB links on the 10g DB

Ensure MS Access apps are able to connect and work

Give the Oracle Devs the OK to change the hardcoded connect string from the old to the new

Give the MS Access Devs the OK to change the ODBC Connect String from the old to the new

Deploy the new Oracle registry key LOCAL default parameter key connect string to all WS

Put the 10g DB back into ARCHIVELOG mode

Verify the nightly backup client is configured properly

Monitor the alert log and dump directories for possible issues
0
 
LVL 28

Author Comment

by:imran_fast
ID: 18888088
Ok i have decided to go with import export the reson why i choose that is

I have 30 GB of images and 10 GB of data the data is taking almost 2.5 hours to export and images are taking 4 hours.
What i have decided is to import the images on the new server and start tracking the new images.
once the images are imported i will stop the user from accessing the old server and export the data.
Import the data
import the new images may be 1 gb
and then get the user back online

After importing it to the new server do i have to rebuild the indexes on the table or not?
0
 
LVL 4

Expert Comment

by:ramumorla
ID: 18888173
Imran, All the constraints/Indexes will be built for you withoiut you doing anything through import, But to make sure, just go through the logs that are generated by exp/imp commands
0
 
LVL 28

Author Comment

by:imran_fast
ID: 18888248
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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

746 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

11 Experts available now in Live!

Get 1:1 Help Now