New Features of Oracle 10 GB-cross-platform tablespace Transfer

1. preparations:
Query source database platform information

SQL> col platform_name for a40
SQL> SELECT d. PLATFORM_NAME, ENDIAN_FORMAT
2 from v $ TRANSPORTABLE_PLATFORM tp, V $ DATABASE d
3 WHERE tp. PLATFORM_NAME = d. PLATFORM_NAME;

PLATFORM_NAME ENDIAN_FORMAT
------------------------------------------------------
Solaris [tm] OE (64-bit) Big

Query target database platform information

SQL> col platform_name for a40
SQL> SELECT d. PLATFORM_NAME, ENDIAN_FORMAT
2 from v $ TRANSPORTABLE_PLATFORM tp, V $ DATABASE d
3 WHERE tp. PLATFORM_NAME = d. PLATFORM_NAME;

PLATFORM_NAME ENDIAN_FORMAT
------------------------------------------------------
Microsoft Windows IA (32-bit) Little

Query Platform conversions supported by Oracle10g

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

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
-----------------------------------------------------------------
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

2. Create an independent self-contained tablespace

Used for testing

$ Sqlplus "/as sysdba"

SQL * Plus: Release 10.1.0.2.0-Production on Tue Apr 27 14:04:08 2004

Copyright (c) 1982,200 4, Oracle .? All rights reserved.

Connected:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select name from v $ datafile;

NAME
--------------------------------------------------------------------------------
/Opt/oracle/oradata/wwl/system01.dbf
/Opt/oracle/oradata/wwl/undotbs01.dbf
/Opt/oracle/oradata/wwl/sysaux01.dbf
/Opt/oracle/oradata/wwl/users01.dbf
/Data1/oradata/systemfile/wwl01.dbf
/Opt/oracle/oradata/wwl/datafile/o1_mf_test_03xv34ny _. dbf
/Opt/oracle/oradata/wwl/datafile/ow.mf_itpub_03xv5g66 _. dbf

7 rows selected.

SQL> create tablespace trans
2? Datafile '/data1/oradata/systemfile/trans01.dbf'
3? Size 10 M;

Tablespace created.

SQL> create user trans identified by trans
2? Default tablespace trans;

User created.

SQL> grant connect, resource to trans;

Grant succeeded.

SQL> connect trans/trans
Connected.

SQL> create table test as select * from user_objects;

Table created.

SQL> select count (*) from test;

COUNT (*)
----------
1

SQL> select * from test;

OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME? OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
-------------------------------------------------------------------------
CREATED? LAST_DDL_TIM TIMESTAMP? STATUS? T G S
-----------------------------------------------------
TEST
15604? 15604 TABLE
27-APR-04? 27-APR-04? 2004-04-27: 14: 05: 42 VALID? N

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-64bit Production
With the Partitioning, OLAP and Data

3. Export the tablespace to be transmitted

$ Pwd
/Opt/oracle
$ Cd dpdata
$ Ls
$ Expdp wwl/wwl dumpfile = trans. dmp directory = dpdata transport_tablespace = trans
LRM-00101: unknown parameter name 'Transport _ tablespace'

$ Expdp wwl/wwl dumpfile = trans. dmp directory = dpdata TRANSPORT_TABLESPACES = trans

Export: Release 10.1.0.2.0-64bit Production on Tuesday, 27 then l, 2004

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "wwl". "SYS_EXPORT_TRANSPORTABLE_01": wwl/******** dumpfile = trans. dmp directory = dpdata TRANSPORT_TABLESPACES = trans
The ORA-39123: Data Pump transportable tablespace job aborted
ORA-29335: tablespace 'Trans 'is not read only

Job "wwl". "SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error

Note: The tablespace to be transferred must be read-only.

$ Sqlplus "/as sysdba"

SQL * Plus: Release 10.1.0.2.0-Production on Tue Apr 27 14:08:13 2004

Copyright (c) 1982,200 4, Oracle. All rights reserved.

Connected:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> alter tablespace trans read only;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-64bit Production
With the Partitioning, OLAP and Data Mining options

$ Expdp wwl/wwl dumpfile = trans. dmp directory = dpdata TRANSPORT_TABLESPACES = trans

Export: Release 10.1.0.2.0-64bit Production on Tuesday, 27 then l, 2004

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "wwl". "SYS_EXPORT_TRANSPORTABLE_01": wwl/******** dumpfile = trans. dmp directory = dpdata TRANSPORT_TABLESPACES = trans
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
Master table "wwl". "SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
**************************************** **************************************
Dump file set for wwl. SYS_EXPORT_TRANSPORTABLE_01 is:
/Opt/oracle/dpdata/trans. dmp
Job "wwl". "SYS_EXPORT_TRANSPORTABLE_01" successfully completed

4. Use rman to convert the file format

$ Rman target/

Recovery Manager: Release 10.1.0.2.0-64bit Production

Copyright (c) 1995,200 4, Oracle. All rights reserved.

Connected to target database: wwl (DBID = 1337390772)

RMAN> convert tablespace trans
2> to platform 'Microsoft Windows IA (32-bit )'
3> Format '/tmp/% U ';

Starting backup at 27-APR-04
Using target database controlfile instead of recovery catalog
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 148 devtype = DISK
Channel ORA_DISK_1: starting datafile conversion
Input datafile fno = 00008 name =/data1/oradata/systemfile/trans01.dbf
Converted datafile =/tmp/data_D-wwl_I-1337390772_TS-TRANS_FNO-8_01fk92hg
Channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 27-APR-04

RMAN> exit

Recovery Manager complete.

5. confirm that the exported file has been generated.

$ Ls/tmp/data *
// Tmp/data_D-wwl_I-1337390772_TS-TRANS_FNO-8_01fk92hg
$ Ls-l/tmp/data *
-Rw-r ----- 1 oracle dba 10493952 Apr 27/tmp/data_D-wwl_I-1337390772_TS-TRANS_FNO-8_01fk92hg
$ Chmod 777/tmp/data *
$ Chmod 777/opt/oracle/dpdata /*

6. transfer files to the target host through ftp

220 billing-center.hurray.com.cn FTP server (SunOS 5.8) ready.
User (192.168.96.10none): gqgai
331 Password required for gqgai.
Password:
230 User gqgai logged in.
Ftp> bin
200 Type set to I.
Ftp> cd/tmp
250 CWD command successful.
Ftp> mget data *
200 Type set to I.
Mget data_D-wwl_I-1337390772_TS-TRANS_FNO-8_01fk92hg? Y
200 PORT command successful.
150 Binary data connection for data_D-wwl_I-1337390772_TS-TRANS_FNO-8_01fk92hg (192.168.96.5, 2885) (10493952 bytes ).
226 Binary Transfer complete.
Ftp: 10493952 bytes encoded ed in 15.90 Seconds 659.87 Kbytes/sec.
Ftp> cd/opt/oracle/dpdata
250 CWD command successful.
Ftp> ls
200 PORT command successful.
150 ASCII data connection for/bin/ls (192.168.96.5, 2889) (0 bytes ).
Export. log
Trans. dmp
226 ASCII Transfer complete.
Ftp: 23 bytes encoded ed in 0.01 Seconds 2.30 Kbytes/sec.
Ftp> bin
200 Type set to I.
Ftp> mget trans. dmp
200 Type set to I.
Mget trans. dmp? Y
200 PORT command successful.
150 Binary data connection for trans. dmp (192.168.96.5, 2893) (73728 bytes ).
226 Binary Transfer complete.
Ftp: 73728 bytes encoded ed in 0.03 Seconds 2457.60 Kbytes/sec.
Ftp> bye
221 Goodbye.

7. Use rman to convert files in the target database

E: \ Oracle \ oradata \ wwl \ dpdata> rman target/

Recovery MANAGER: Version 10.1.0.2.0-Production

Copyright (c) 1995,200 4, Oracle. All rights reserved.

Connect to the target database: wwl (DBID = 1587222708)

RMAN> convert datafile 'e: \ Oracle \ oradata \ wwl \ dpdata \ data_D-wwl_I-1337390772_TS-TRANS_FNO-8_01fk92hg'
2> DB_FILE_NAME_CONVERT
3> 'e: \ Oracle \ oradata \ wwl \ dpdata \ data_D-wwl_I-1337390772_TS-TRANS_FNO-8_01fk92hg ', 'e: \ Oracle \ oradata \ wwl \ DATAFILE \ trans01.dbf ';

Start backup in September 4-04
Use channel ORA_DISK_1
Channel ORA_DISK_1: starts data file conversion
Output file name = E: \ ORACLE \ ORADATA \ wwl \ DPDATA \ DATA_D-wwl_I-1337390772_TS-TRANS_FNO-8_01FK92HG
Converted data files = E: \ ORACLE \ ORADATA \ wwl \ DATAFILE \ TRANS01.DBF
Channel ORA_DISK_1: the data file has been converted, after 00:00:04
Backup completed in September 4-04

RMAN>

8. Plug-in data files in the target database

Note that the target user in the target database must exist; otherwise, an error is returned.

E: \ Oracle \ oradata \ wwl \ dpdata> impdp wwl/wwl dumpfile = trans. dmp directory = dpdata transport_datafiles = 'e: \ Oracle \ orad
Ata \ wwl \ DATAFILE \ TRANS01.DBF'

Import: Release 10.1.0.2.0-Production on Tuesday, 27 July 22, 2004

Copyright (c) 2003, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-Production
With the Partitioning, OLAP and Data Mining options
The master table "wwl". "SYS_IMPORT_TRANSPORTABLE_01" is successfully loaded/uninstalled"
Start "wwl". "SYS_IMPORT_TRANSPORTABLE_01": wwl/******** dumpfile = trans. dmp directory = dpdata transport_datafiles = 'e :\
Oracle \ oradata \ wwl \ DATAFILE \ TRANS01.DBF'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transfer-able tablespace job abort
ORA-29342: User TRANS does not exist in the Database

Job "wwl". "SYS_IMPORT_TRANSPORTABLE_01" stopped at due to a fatal error

E: \ Oracle \ oradata \ wwl \ dpdata> sqlplus "/as sysdba"

SQL * Plus: Release 10.1.0.2.0-Production on Tuesday April 27 15:03:03 2004

Copyright (c) 1982,200 4, Oracle. All rights reserved.

Connect:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-Production
With the Partitioning, OLAP and Data Mining options

SQL> create user trans identified by trans;

The user has been created.

SQL> grant connect, resource to trans;

Authorization successful.

SQL> exit
From Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-Production
With the Partitioning, OLAP and Data Mining options disconnected

E: \ Oracle \ oradata \ wwl \ dpdata> impdp wwl/wwl dumpfile = trans. dmp directory = dpdata transport_datafiles = 'e: \ Oracle \ orad
Ata \ wwl \ DATAFILE \ TRANS01.DBF'

Import: Release 10.1.0.2.0-Production on Tuesday, 27 July 22, 2004

Copyright (c) 2003, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-Production
With the Partitioning, OLAP and Data Mining options
The master table "wwl". "SYS_IMPORT_TRANSPORTABLE_01" is successfully loaded/uninstalled"
Start "wwl". "SYS_IMPORT_TRANSPORTABLE_01": wwl/******** dumpfile = trans. dmp directory = dpdata transport_datafiles = 'e :\
Oracle \ oradata \ wwl \ DATAFILE \ TRANS01.DBF'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
The job "wwl". "SYS_IMPORT_TRANSPORTABLE_01" was successfully completed.

9. Check Data

E: \ Oracle \ oradata \ wwl \ dpdata> sqlplus trans/trans

SQL * Plus: Release 10.1.0.2.0-Production on Tuesday April 27 15:03:50 2004

Copyright (c) 1982,200 4, Oracle. All rights reserved.

Connect:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-Production
With the Partitioning, OLAP and Data Mining options

SQL> select count (*) from test;

COUNT (*)
----------
1

SQL> exit
From Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-Production
With the Partitioning, OLAP and Data Mining options disconnected

E: \ Oracle \ oradata \ wwl \ dpdata>

You can change the tablespace to read/write.

SQL> alter tablespace trans read write;

The tablespace has been changed.

10. Summary

10 Gb tablespace is migrated across platforms. Compared with 9i, a file format conversion process using Rman is added.
In fact, the format information of the data file header is converted.

-The End-

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12