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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.