Oracle exp/imp question 1

[nyck]

Hello,

I have been asked to take a full dump of 4 oracle 9.2.0.7 databases and then load the dumps into 4 oracle 11G R2 databases. Will this work?

If so what would be the best way of doing this?

 

[Thargy]

Yes, it should.

Backwards compatibility with IMP (as opposed to IMPDP) is maintained by Oracle for just such situations. Obviously you have to be IMP'ing into a db on the same OS. To go between different OS's, you have to use transportable tablespaces.

Regards

T

 

[SantaMufasa]

Obviously you have to be IMP'ing into a db on the same OS.

John, I have exported from one operating-system file system into a different operating system or very regular occasions, without ever using transportable tablespaces. Therefore, your assertion puzzles me.

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.”

 

[Thargy]

Santa,

the files from a big-endian OS are incompatible with a little-endian OS. You may succeed going from one to another, perhaps windows 32 to 64 bit, but success is not guaranteed.

The only sure-fire method is to make a transportable tablespace.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tspaces013.htm

provides details, with an excerpt below.

About Transporting Tablespaces Across Platforms

Starting with Oracle Database 11g, you can transport tablespaces across platforms. This functionality can be used to:

*

Allow a database to be migrated from one platform to another
*

Provide an easier and more efficient means for content providers to publish structured data and distribute it to customers running Oracle Database on different platforms
*

Simplify the distribution of data from a data warehouse environment to data marts, which are often running on smaller platforms
*

Enable the sharing of read-only tablespaces between Oracle Database installations on different operating systems or platforms, assuming that your storage system is accessible from those platforms and the platforms all have the same endianness, as described in the sections that follow

Many, but not all, platforms are supported for cross-platform tablespace transport. You can query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported, and to determine each platform's endian format (byte ordering). The following query displays the platforms that support cross-platform tablespace transport:

SQL> COLUMN PLATFORM_NAME FORMAT A32
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux 64-bit for AMD Little
16 Apple Mac OS Big
12 Microsoft Windows 64-bit for AMD Little
17 Solaris Operating System (x86) Little

16 rows selected.

If the source platform and the target platform are of different endianness, then an additional step must be done on either the source or target platform to convert the tablespace being transported to the target format. If they are of the same endianness, then no conversion is necessary and tablespaces can be transported as if they were on the same platform.

Before a tablespace can be transported to a different platform, the datafile header must identify the platform to which it belongs. In an Oracle Database with compatibility set to 10.0.0 or later, you can accomplish this by making the datafile read/write at least once.


Regards

T

 

[SantaMufasa]

My wife points out to me that I have a big-endian. I need to use diet and exercise (not transportable tablespaces) to achieve a little-endian. <grin>

Of course, I cannot argue with your elegant and eloquent explanation, John. Thanks for sharing...Hava star.

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.”

 

[karluk]

SantaMufasa is right - there's no need to resort to transportable tablespaces when doing cross platform migrations. The old style export/import utilities work just fine. About a year ago we migrated a 200 GB database from windows (little endian) to HP-UX 64 bit (big endian). Because of the relatively large size of the database and slow network transmission speeds, we were facing a lengthy outage on a critical production system, so we looked at a wide variety of different migration strategies. It turned out that export/import was considerably faster than transportable tablespaces and the other strategies we tried. That's primarily because - by doing the export on the unix side - we could start the import while the export was still running. Unlike windows, unix doesn't lock a file from being read while another process is writing to it. The near twofold increase in throughput more than outweighed the benefits of any other strategy.

So I would say that transportable tablespaces are an additional tool that can be used for cross platform migration, but certainly they are not the only method that will work. Oracle has supported cross platform migrations long before transportable tablespaces became available.

 

[nyck]

This is to be perfromed on a V890 running Solaris9 so no issues there

So a bog standard full exp should do the job?

Thanks for all the excellent responses by the way!

 

[Thargy]

guys,

I was under the impression that the endian-ness of the OS prevented exp and imp files being valid on different OS's. This is sort of new to me. Can anyone give me a reference to the oracle documentation?

Regards

T