表空间传输
源数据库操作
----检查平台版本
col platform_name for a40
SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM. tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Linux IA (32-bit) Little
----检查可传输表空间类型
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
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
17 rows selected.
--创建用户TRANS，并插入一些数据，这里不在贴出具体过程
---利用数据泵将TRANS表空间导出
[oracle@test2 backup]$ expdp sys/system directory=pump dumpfile=trans.dmp transport_tablespaces=trans
Export: Release 10.2.0.3.0 - Production on Thursday, 18 October, 2007 14:24:20
Copyright (c) 2003, 2005, Oracle. All rights reserved.
UDE-00008: operation generated ORACLE error 1017
ORA-01017: invalid username/password; logon denied
Username: / as sysdba ----这里需要注意的是要用SYSDBA的权限
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA directory=pump dumpfile=trans.dmp transport_tablespaces=trans
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/trans.dmp
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 14:24:54
---利用RMAN做表空间转换
RMAN> convert tablespace trans to platform. 'Microsoft Windows IA (32-bit)'
2> format '/home/oracle/%U';
Starting backup at 18-OCT-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00007 name=/u01/app/oracle/oradata/rep2/trans.dbf
converted datafile=/home/oracle/data_D-REP2_I-4143594773_TS-TRANS_FNO-7_02iuqca7
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
Finished backup at 18-OCT-07
RMAN> exit
目标数据库操作
----检查平台版本
SQL> col platform_name for a40
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM. tp, V$DATABASE d
2 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Microsoft Windows IA (32-bit) Little
----检查可传输表空间类型
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
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
已选择17行。
---将TRANS转换到WIN2003下
RMAN>CONVERT DATAFILE 'c:\expdp\data_D-REP2_I-4143594773_TS-TRANS_FNO-7_02iuqca7'
DB_FILE_NAME_CONVERT
'c:\expdp\data_D-REP2_I-4143594773_TS-TRANS_FNO-7_02iuqca7','c:\oracle\product\10.2.0\oradata\test\trans.dbf';
----利用数据泵导回库中
impdp sys/system dumpfile=trans.dmp directory=test_dir transport_datafiles='c:\oracle\product\10.2.0\oradata\test\trans.dbf'
---测试
SQL> connect trans/trans
已连接。
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
TEST
SQL> select count(*) from test;
COUNT(*)
----------
2
这里需要注意几个问题
1、在注意IMPDP/EXPDP的语法
2、注意用户要事先建立好
3、注意将表空间改回READ WITRE
此实验过程参考：http://www.eygle.com/archives/2004/06/10g_transport_tbs.html
===================================================================================
非标准块的表空间传输
WIN上的操作和以前步骤基本一致只有在建立16K表空间的时候稍有却别
1、
create tablespace bl16k xxxx blocksize 16k;
2、
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS ADMIN_DIR
C:\ADE\aime_10.2_nt_push\oracle/md/admin
SYS DATA_PUMP_DIR
E:\oracle\product\10.2.0\admin\test\dpdump\
SYS TEST_DIR
c:\archive
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS WORK_DIR
C:\ADE\aime_10.2_nt_push\oracle/work
SYS OUT_DIR
c:\out_dir
3、
SQL> select name,block_size from v$datafile;
NAME
--------------------------------------------------------------------------------
BLOCK_SIZE
----------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF
8192
E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF
8192
E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF
8192
NAME
--------------------------------------------------------------------------------
BLOCK_SIZE
----------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF
8192
E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS02.DBF
8192
E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TB16K.DBF
8192
NAME
--------------------------------------------------------------------------------
BLOCK_SIZE
----------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\PAR1.DBF
8192
E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\PAR2.DBF
8192
E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\PAR3.DBF
8192
NAME
--------------------------------------------------------------------------------
BLOCK_SIZE
----------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\PAR4.DBF
8192
E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\BL16K.DBF
16384
已选择11行。
4、
expdp system/system directory=OUT_DIR dumpfile=trans.dmp transport_tablespaces=bl16k
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
启动 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=OUT_DIR
dumpfile=trans.dmp transport_tablespaces=bl16k
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
已成功加载/卸载了主表 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 的转储文件集为:
C:\OUT_DIR\TRANS.DMP
作业 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已于 09:52:42 成功完成
首先在AIX上建立
SQL> create or replace directory testdir as '/home/oracle/testdir';
Directory created.
SQL> SELECT * FROM DBA_DIRECTORIES;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS ADMIN_DIR
/install/ksuriana/050630.0008/withoutseed/ohome/md/admin
SYS DATA_PUMP_DIR
/home/oracle/product/10.2/rdbms/log/
SYS WORK_DIR
/install/ksuriana/050630.0008/withoutseed/ohome/work
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS TESTDIR
/home/oracle/testdir
2、
SQL> col platform_name for a40
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM. tp, V$DATABASE d
2 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
AIX-Based Systems (64-bit) Big
3、文件传输
4、用rman进行转换
RMAN> CONVERT DATAFILE '/home/oracle/DTDATA_D-TEST_I-1951370425_TS-BL16K_FNO-11_0AJSHNUR' db_file_name_convert '/home/oracle/DTDATA_D-TEST_I-1951370425_TS-BL16K_FNO-11_0AJSHNUR','/oradata/bl16k';
Starting backup at 07-OCT-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=/home/oracle/DTDATA_D-TEST_I-1951370425_TS-BL16K_FNO-11_0AJSHNUR
converted datafile=/oradata/bl16k
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02
Finished backup at 07-OCT-08
5、
$ impdp sys/system dumpfile=TRANS.DMP directory=testdir transport_datafiles='/oradata/bl16k';
Import: Release 10.2.0.1.0 - 64bit Production on Tuesday, 07 October, 2008 10:53:27
Copyright (c) 2003, 2005, Oracle. All rights reserved.
UDI-00008: operation generated ORACLE error 28009
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
Username: system
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=TRANS.DMP directory=testdir transport_datafiles=/oradata/bl16k
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 10:53:34
验证是否传输转换成功
select name,block_size from v$datafile;
NAME BLOCK_SIZE
------------------------ ----------
/oradata/system01.dbf 8192
/oradata/undotbs01.dbf 8192
/oradata/sysaux01.dbf 8192
/oradata/users01.dbf 8192
/oradata/bigfile.dbf 8192
/oradata/bl16k 16384
6 rows selected.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata/system01.dbf
/oradata/undotbs01.dbf
/oradata/sysaux01.dbf
/oradata/users01.dbf
/oradata/bigfile.dbf
/oradata/bl16k
6 rows selected.
问题处理：
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=TRANS.DMP directory=testdir transport_datafiles=/oradata/bl16k.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29339: tablespace block size 16384 does not match configured block sizes
解决办法：
因为没有给16K块分配DB_CACHE。
SQL> show parameter db_16k
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
SQL> alter system set db_16k_cache_size=20m;
System altered.
SQL> show parameter db_16k
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 32M
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7882490/viewspace-468166/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- Installing GoldenGate For Oracle RAC
- Installing GoldenGate For Oracle RAC
- create table 出现Ora-00604 And Ora-18008 OUTLN用户不存在
- 判断一个字符串中的数字，并将其挑选出来
- 10G RAC升级到11G的方法（官方）
- GOLDENGATE之RAC下的复制(1)
- Oracle数据库静默安装实践
- gcs log flush sync --wait event
- GG初始化同步方法 -2
- ORA-00494: enqueue [CF] held for too long (more than 900 seconds)
