Moving Oracle 10g database to a new server

[4gandalf]

Greetings and Happy New Year to all.

I have just completed an upgrade of an Oracle 8i database to Oracle 10g on windows 2000 sp4. Now I need to move this database to a new server. How can I efficiently move this database to a new server. I haven't found any references to this specific issue on Metalink.

Regards,

The Elf with a Wand!

 

[SantaMufasa]

If I were in your shoes, I would use either Oracle Export/Import ("exp"/"imp") or Oracle Data Pump ("expdp"/"impdp"), if the volume of data is manageable. If we are talking terabytes of data, and if your target server is the same o/s flavor as the source server, then you have other options, including (I believe), an RMAN restore (with special settings).

Let us know your specific situation's details, and if you have critical questions about the above methods that you cannot find adequate answers via Metalink or Google.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[4gandalf]

Thanks Mufasa for the reply.

I figured I would have to use exp/imp. I don't have a lot of data to move. I originally thought of using oradim to create the instance on the new server then update the controlfile to point it to where the files are located; since the file locations in 8i are a little different from 10g's default locations. I also would have to deal with things in the spfile for the updated database.

Regards,
The Elf with a Wand.

 

[Turkbear]

Hi,
That idea:

I originally thought of using oradim to create the instance on the new server then update the controlfile to point it to where the files are located.

may have encountered file header issues since I am not sure 8i and 10g use the same format..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[LKBrwnDBA]

I have just completed [red]an upgrade[/red] of an Oracle 8i database to Oracle 10g on windows 2000...

As Dave of Sandy said: "if your target server is the same o/s flavor as the source server, then you have other options," which are: clone the database!, There are several methods to do this, just search this forum or Google "clone oracle database".




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[4gandalf]

Thanks Turkbear and LKBrwnDBA for your responses. I had not thought of actually "cloning" the database. Honestly, I did not know that was an option. I will look into it.

Regards,

The Elf with a Wand!

 

[4gandalf]

Greetings to All,

Again I want to thank you for your help.
I have attempted to use export/import to move the tablespaces from the old server to the new. Unfortunately, I keep getting the following error each time I start the import:

;;;
Import: Release 10.2.0.1.0 - Production on Tuesday, 20 January, 2009 15:29:56

Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
;;; UDI-00011: parameter dumpfile is incompatible with parameter transport_tablespaces

At first I thought it may be a database version compatibility issue. So, I checked the compatible setting on the source database - this is the one that has been upgraded to 10g from 8i. The setting was 9.2.0, so I changed it to 10.2.0.1.0 for full 10g compatibility. Needless to say, that did not work! I'm going to put in a ticket with Oracle and see what they say. I'm confidant that this can't be that difficult to accomplish.

Also, I found quite a few documents on Oracle cloning but they were covering such things as cloning an Oracle 8i HOME, or RAC instance, or Oracle 8 on UNIX; nothing on cloning an Oracle 10g database on the windows platform.

I did see a piece of software called XClone, is anyone familiar with the product? Is it worth a try??

Again thank you,

The Elf with a Wand.

 

[karluk]

If you are able to open a ticket with Oracle, you doubtlessly have Metalink access. I suggest you take a look at articles 444756.1 (for an explanation of your UDI-00011 error - you apparently were using the "TRANSPORT_TABLESPACES" parameter when you should have been using "TRANSPORT_DATAFILES") and 277650.1 (for a step-by-step guide to using expdp and impdp for copying databases to a different server).

 

[4gandalf]

Karluk, thank you. Your suggestion paid off. I am not getting the error I was before.

I've actually moved up to better errors. Now I'm receiving the following import error:

;;;
Import: Release 10.2.0.1.0 - Production on Wednesday, 21 January, 2009 14:04:22

Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "STEWARTG"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "STEWARTG"."SYS_IMPORT_TRANSPORTABLE_01": stewartg/******** dumpfile=nup01.dmp logfile=nup1.log transport_datafiles='c:\oracle\product\10.2.0\oradata\testit\nupmain.ora','c:\oracle\product\10.2.0\oradata\testit\nuplicenses.ora','c:\oracle\product\10.2.0\oradata\testit\nupcontracts.ora'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-01565: error in identifying file 'c:\oracle\product\10.2.0\oradata\testit\nupcontracts.ora'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Job "STEWARTG"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 14:04:24

The error is strange because the "nupcontracts.ora" file is listed last in the import parameters, does impdp read the last file first? Anyway, I'm reading all the relevant documents now. I did discover that my source database and target have different national character sets. I know that Oracle 8i required the character sets and National character sets to be identical in source and target. I'm not sure for 10g to 10g. I checked the "endian-ness" for the source and target and they are identical.
Seriously, I'm considering just creating the tablespaces in the target and importing the tables and data from the source; since my time is growing short.

Much thanks again for everyones help.

The Elf with a Wand.

 

[karluk]

Are you sure you copied the datafiles to the target server? If you didn't, that would explain your "system cannot find the file specified" error.

You should be able to fix a datafile not found error, but the different national character sets on source and target databases is a fatal issue for the "TRANSPORT_DATAFILES" method of transfer. If you read the Metalink article I referenced yesterday, you will see that it says, "The source and target database must use the same character set and national character set".

So, yes, your suggestion of precreating the tablespaces and importing is the way to go.