search
HomeDatabaseMysql TutorialOracle 10g新特性之-跨平台表空间传输

1.准备工作: 查询源数据库平台信息 SQLgt; col platform_name for a40SQLgt; SELECT d.PLATFORM_NAME, ENDIAN_FORMAT 2 FROM

1.准备工作:
 查询源数据库平台信息

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

查询目标数据库平台信息

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

查询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.创建一个独立的自包含表空间

用于测试

$ sqlplus "/ as sysdba"

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

Copyright (c) 1982, 2004, 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

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/wwl/datafile/o1_mf_test_03xv34ny_.dbf
/opt/oracle/oradata/wwl/wwl/datafile/o1_mf_itpub_03xv5g66_.dbf

7 rows selected.

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

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 N 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.导出要传输的表空间

$ 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 April, 2004 14:07

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
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 at 14:08

注意:传输表空间必须置为只读状态

$ sqlplus "/ as sysdba"

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

Copyright (c) 1982, 2004, 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

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 April, 2004 14:08

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 at 14:09

4.使用rman转换文件格式


 $ rman target /

Recovery Manager: Release 10.1.0.2.0 - 64bit Production

Copyright (c) 1995, 2004, 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.确认导出文件已生成

$ 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 14:12 /tmp/data_D-wwl_I-1337390772_TS-TRANS_FNO-8_01fk92hg
 $ chmod 777 /tmp/data*
 $ chmod 777 /opt/oracle/dpdata/*

6.通过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 received in 15.90Seconds 659.87Kbytes/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 received in 0.01Seconds 2.30Kbytes/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 received in 0.03Seconds 2457.60Kbytes/sec.
 ftp> bye
 221 Goodbye.


 7.使用rman在目标数据库转换文件

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

恢复管理器: 版本10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

连接到目标数据库: 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\wwl\DATAFILE\trans01.dbf';

启动 backup 于 27-4月 -04
 使用通道 ORA_DISK_1
 通道 ORA_DISK_1: 启动数据文件转换
 输出文件名=E:\ORACLE\ORADATA\wwl\DPDATA\DATA_D-wwl_I-1337390772_TS-TRANS_FNO-8_01FK92HG
 已转换的数据文件 = E:\ORACLE\ORADATA\wwl\wwl\DATAFILE\TRANS01.DBF
 通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:04
 完成 backup 于 27-4月 -04

RMAN>


8.在目标数据库plugin数据文件

注意目标数据库中的目标用户必须存在,否则会报错.

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

Import: Release 10.1.0.2.0 - Production on 星期二, 27 4月, 2004 15:02

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

连接到: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
 With the Partitioning, OLAP and Data Mining options
 已成功加载/卸载了主表 "wwl"."SYS_IMPORT_TRANSPORTABLE_01"
 启动 "wwl"."SYS_IMPORT_TRANSPORTABLE_01": wwl/******** dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\
 Oracle\oradata\wwl\wwl\DATAFILE\TRANS01.DBF'
 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
 ORA-39123: 数据泵可传输的表空间作业中止
 ORA-29342: 数据库中不存在用户 TRANS

作业 "wwl"."SYS_IMPORT_TRANSPORTABLE_01" 因致命错误于 15:02 停止


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

SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 4月 27 15:03:03 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.


 连接到:
 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;

用户已创建。

SQL> grant connect,resource to trans;

授权成功。

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

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

Import: Release 10.1.0.2.0 - Production on 星期二, 27 4月, 2004 15:03

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

连接到: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
 With the Partitioning, OLAP and Data Mining options
 已成功加载/卸载了主表 "wwl"."SYS_IMPORT_TRANSPORTABLE_01"
 启动 "wwl"."SYS_IMPORT_TRANSPORTABLE_01": wwl/******** dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\
 Oracle\oradata\wwl\wwl\DATAFILE\TRANS01.DBF'
 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
 处理对象类型 TRANSPORTABLE_EXPORT/TABLE
 处理对象类型 TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
 作业 "wwl"."SYS_IMPORT_TRANSPORTABLE_01" 已于 15:03 成功完成

9.检查数据

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

SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 4月 27 15:03:50 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.


 连接到:
 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
 从 Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
 With the Partitioning, OLAP and Data Mining options 断开

E:\Oracle\oradata\wwl\dpdata>

可以选择把表空间更改为读写

SQL> alter tablespace trans read write;

表空间已更改。


10.总结

10g的表空间跨平台迁移,,较9i就是增加了一个使用Rman进行的文件格式转换的过程.
 实际上也就是转换了数据文件头的格式信息而已.


-The End-

更多Oracle相关信息见Oracle 专题页面 ?tid=12

linux

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Explain the InnoDB Buffer Pool and its importance for performance.Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)