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

x
?
Solved

Moving Data from One Server to Another

Posted on 2007-04-09
17
Medium Priority
?
1,861 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
[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
  • 6
  • 4
  • 3
  • +3
17 Comments
 
LVL 4

Accepted Solution

by:
ramumorla earned 1200 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 400 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

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