(1)数据库
alter database open ;
SQL> select d.platform_name from
v$db_transportable_platform d ;
Microsoft Windows IA (32-bit)
Linux IA (32-bit)
HP Tru64 UNIX
Linux IA (64-bit)
HP Open VMS
Microsoft Windows IA (64-bit)
Linux x86 64-bit
Microsoft Windows x86 64-bit
Solaris Operating System (x86)
HP IA Open VMS
Solaris Operating System (x86-64)
source |||
SQL>
select d.platform_name from v$database
d;
PLATFORM_NAME
----------------------------------------
Microsoft Windows x86 64-bit
target |||
SQL>
select d.platform_name from v$database
d;
PLATFORM_NAME
----------------------------------------
'AIX-Based Systems (64-bit)'
(2)tablespace read only
省略....
(3) 转换convert
convert可以对不同os平台的表空间文件进行 endian 格式转换,任意big和little编码都可以转换
例如本次源端是 windows X64系统,可以转换为任意系统 'AIX-Based
Systems (64-bit)' 等其他Big endian 格式操作系统
rman target /
---to big
convert tablespace users to platform='HP-UX
(64-bit)'
db_file_name_convert='F:\app\oracle12c\oradata\tx','F:\oracle_convert\tx';
convert tablespace users to
platform='AIX-Based Systems (64-bit)'
db_file_name_convert='F:\app\oracle12c\oradata\tx','F:\oracle_convert\tx';
convert tablespace users to
platform='Solaris[tm] OE (64-bit)'
db_file_name_convert='F:\app\oracle12c\oradata\tx','F:\oracle_convert\tx';
---to little
convert tablespace users to
platform='Solaris Operating System (x86)'
db_file_name_convert='F:\app\oracle12c\oradata\tx','F:\oracle_convert\tx';
convert tablespace users to platform='Linux
x86 64-bit'
db_file_name_convert='F:\app\oracle12c\oradata\tx','F:\oracle_convert\tx';
convert tablespace users to
platform='Microsoft Windows IA (64-bit) '
db_file_name_convert='F:\app\oracle12c\oradata\tx','F:\oracle_convert\tx
RMAN> convert tablespace users to platform='AIX-Based
Systems (64-bit)'
db_file_name_convert='F:\app\oracle12c\oradata\tx','F:\oracle_convert\tx';
启动 conversion at source 于 30-12月-15
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件: 文件号=00004
名称=F:\APP\ORACLE12C\ORADATA\TX\USERS01.DBF
已转换的数据文件 = F:\ORACLE_CONVERT\TX\USERS01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
完成 conversion at source 于 30-12月-15
RMAN>
RMAN>
RMAN> convert tablespace users to
platform='HP-UX (64-bit)'
db_file_name_convert='F:\app\oracle12c\oradata\tx','F:\oracle_convert\tx';
启动 conversion at source 于 30-12月-15
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=13 设备类型=DISK
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件: 文件号=00004
名称=F:\APP\ORACLE12C\ORADATA\TX\USERS01.DBF
已转换的数据文件 = F:\ORACLE_CONVERT\TX\USERS01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
完成 conversion at source 于 30-12月-15
RMAN>
RMAN> convert tablespace users to platform='Solaris[tm] OE
(64-bit)'
db_file_name_convert='F:\app\oracle12c\oradata\tx','F:\oracle_convert\tx';
启动 conversion at source 于 30-12月-15
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件: 文件号=00004
名称=F:\APP\ORACLE12C\ORADATA\TX\USERS01.DBF
已转换的数据文件 = F:\ORACLE_CONVERT\TX\USERS01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
完成 conversion at source 于 30-12月-15
(4) 导出传送导入
..exp tbs metadata
|| imp tba metadata
如果是UTF8编码的,环境变量建议用AL32UTF8,避免转码,更加环境注意选择
set NLS_LANG=AMERICAN_AMERICA.US7ASCII
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
set NLS_LANG=SIMPLIFIED CHINESE_CHINA.zhs16GBK
exp \ '/ as sysdba \' file=exp_users.dmp.12C transport_tablespace=y
tablespaces=users
..传送dmp 和 表空间文件
ftp
scp
..imp 表空间
imp \ '/ as sysdba \' file=exp_users.dmp.12C
transport_tablespace=y tablespaces=users
datafile= file1path ,file2path,。。。。
跨平台传输表空间小结
1) 确定平台的 Endian 格式
2) 确保表空间为自包含并使其只读(如果利用rman操作,可不用将表空间至于只读)
3) 用 exp、expdp等实用程序导出元数据
4) 转换数据文件以匹配 Endian 格式 ,conver
5) 拷贝文件到目标系统
6) 使用 imp导入实用程序导入元数据
=====================================================================================================================================
其他datafile 级别转换:
convert datafile
,文件级别的转换,经常验证不通过,下次再去找资料看看限制和场景,暂时不建议用这个功能。
convert datafile
'F:\APP\ORACLE12C\ORADATA\TX\USERS01.DBF' to platform= 'HP-UX
(64-bit)'
db_file_name_convert='F:\app\oracle12c\oradata\tx','F:\oracle_convert\tx';
convert datafile
'F:\APP\ORACLE12C\ORADATA\TX\USERS01.DBF' to platform= 'Linux x86
64-bit'
db_file_name_convert='F:\app\oracle12c\oradata\tx','F:\oracle_convert\tx';
RMAN>
RMAN> convert datafile
'F:\APP\ORACLE12C\ORADATA\TX\USERS01.DBF' to platform= 'HP-UX
(64-bit)'
db_file_name_convert='F:\app\oracle12c\oradata\tx','F:\oracl
e_convert\tx';
启动 conversion at target 于 30-12月-15
使用通道 ORA_DISK_1
RMAN-00571:
===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571:
===========================================================
RMAN-03002: conversion at target 命令 (在 12/30/2015 10:26:15 上)
失败
RMAN-06595: 平台名 'HP-UX (64-bit)' 与数据库平台名 'Microsoft Windows
x86 64-bit' 不匹配
RMAN>
RMAN> convert datafile
'F:\APP\ORACLE12C\ORADATA\TX\USERS01.DBF' to platform= 'Linux x86
64-bit'
db_file_name_convert='F:\app\oracle12c\oradata\tx','F:\ora
cle_convert\tx';
启动 conversion at target 于 30-12月-15
使用通道 ORA_DISK_1
RMAN-00571:
===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571:
===========================================================
RMAN-03002: conversion at target 命令 (在 12/30/2015 10:26:25 上)
失败
RMAN-06595: 平台名 'Linux x86 64-bit' 与数据库平台名 'Microsoft Windows
x86 64-bit' 不匹配
RMAN>
=============exp=====================================================
set NLS_LANG=SIMPLIFIED CHINESE_CHINA.AL32UTF8
exp \ '/ as sysdba \'
file=exp_andzen1.dmp.12C.UTF8_CHINA transport_tablespace=y tablespaces=andzen1
set NLS_LANG=SIMPLIFIED CHINESE_CHINA.zhs16gbk
exp \ '/ as sysdba \'
file=exp_andzen1.dmp.12C.CHINA transport_tablespace=y tablespaces=andzen1
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
exp \ '/ as sysdba \'
file=exp_andzen1.dmp.12C.utf8_AMER transport_tablespace=y tablespaces=andzen1
convert tablespace andzen1 to platform= 'Microsoft Windows x86
64-bit'
db_file_name_convert='F:\app\oracle12c\oradata\tx','F:\oracle_convert\tx';
convert tablespace andzen1 to platform= 'Linux
x86 64-bit'
db_file_name_convert='F:\app\oracle12c\oradata\tx','F:\oracle_convert\tx2';
=============drop tablespace ===========================================
drop tablespace andzen1 including contents and
datafiles CASCADE constraints ;
=============imp====================================================
imp \ '/ as sysdba \' file=usr_exp_idx.tbs.dmp
transport_tablespace=y tablespaces=example,users,indx
datafiles=/home/oracle/users01.dbf,/home/oracle/example.dbf,/home/oracle/indx01.dbf
imp \ '/ as sysdba \' file=exp_andzen1.dmp.12C.utf8_AMER
transport_tablespace=y tablespaces=andzen1 datafiles='F:\app\oracle12c\oradata\tx\ANDZEN1.DBF'
F:\oracle_convert\tx>imp \ '/ as sysdba \'
file=exp_andzen1.dmp.12C.utf8_AMER transport_tablespace=y
tablespaces=andzen1 datafiles='F:\app\oracle12c\orada
ta\tx\ANDZEN1.DBF'
Import: Release 12.1.0.2.0 - Production on Thu Dec 31 11:36:33
2015
Copyright (c) 1982, 2014, Oracle and/or its
affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release
12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real
Application Testing options
Export file created by EXPORT:V12.01.00 via conventional
path
About to import transportable tablespace(s) metadata...
import done in AL32UTF8 character set and UTF8 NCHAR character
set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing ANDZEN's objects into ANDZEN
. . importing
table "MY_OBJECTS"
. . importing
table "MY_OBJ"
. . importing
table "TX"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
set NLS_LANG=SIMPLIFIED CHINESE_CHINA.AL32UTF8
imp \ '/ as sysdba \'
file=exp_andzen1.dmp.12C.UTF8_CHINA transport_tablespace=y tablespaces=andzen1 datafiles='F:\app\oracle12c\oradata\tx\ANDZ'
Import: Release 12.1.0.2.0 - Production on 鏄熸湡鍥?12鏈?31 11:43:28
2015
Copyright (c) 1982, 2014, Oracle and/or its
affiliates. All rights reserved.
杩炴帴鍒? Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 -
64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real
Application Testing options
缁忕敱甯歌璺緞鐢?EXPORT:V12.01.00 鍒涘缓鐨勫鍑烘枃浠
鍗冲皢瀵煎叆鍙紶杈撶殑琛ㄧ┖闂村厓鏁版嵁...
宸茬粡瀹屾垚 AL32UTF8 瀛楃闆嗗拰 UTF8 NCHAR 瀛楃闆嗕腑鐨勫鍏
. 姝e湪灏?SYS 鐨勫璞″鍏ュ埌 SYS
. 姝e湪灏?SYS 鐨勫璞″鍏ュ埌 SYS
. 姝e湪灏?ANDZEN 鐨勫璞″鍏ュ埌 ANDZEN
. .
姝e湪瀵煎叆琛? "MY_OBJECTS"
. .
姝e湪瀵煎叆琛? "MY_OBJ"
. .
姝e湪瀵煎叆琛? "TX"
. 姝e湪灏?SYS 鐨勫璞″鍏ュ埌 SYS
鎴愬姛缁堟瀵煎叆, 娌℃湁鍑虹幇璀﹀憡銆?
set NLS_LANG=SIMPLIFIED CHINESE_CHINA.zhs16gbk
imp \ '/ as sysdba \'
file=exp_andzen1.dmp.12C.CHINA transport_tablespace=y tablespaces=andzen1 datafiles='F:\app\oracle12c\oradata\tx\ANDZEN1.DBF'
Import: Release 12.1.0.2.0 - Production on 星期四 12月 31 11:46:48
2015
Copyright (c) 1982, 2014, Oracle and/or its
affiliates. All rights reserved.
连接到: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 -
64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real
Application Testing options
经由常规路径由 EXPORT:V12.01.00 创建的导出文件
即将导入可传输的表空间元数据...
已经完成 ZHS16GBK 字符集和 UTF8 NCHAR 字符集中的导入
导入服务器使用 AL32UTF8 字符集 (可能的字符集转换)
. 正在将 SYS 的对象导入到 SYS
. 正在将 SYS 的对象导入到 SYS
. 正在将 ANDZEN 的对象导入到 ANDZEN
. .
正在导入表 "MY_OBJECTS"
. .
正在导入表 "MY_OBJ"
. .
正在导入表 "TX"
. 正在将 SYS 的对象导入到 SYS
成功终止导入, 没有出现警告。
======== result
=======================================================
SQL> select tablespace_name ,table_name from
dba_tables where tableSpace_name='ANDZEN1';
TABLESPACE_N TABLE_NAME
------------ -------------------
ANDZEN1 TY
ANDZEN1 MY_OBJECTS
ANDZEN1 MY_OBJ
ANDZEN1 TX
--------------------------------------------------------------------------
12c create
table 时候不分配空间?
SQL>
SQL> CREATE TABLE ANDZEN.TY (X INT )
TABLESPACE ANDZEN1;
表已创建。
已用时间: 00: 00: 00.03
SQL>
SQL> INSERT INTO ANDZEN.TY SELECT 2 FROM
DUAL;
INSERT INTO ANDZEN.TY SELECT 2 FROM DUAL
*
第 1 行出现错误:
ORA-01647: 表空间 'ANDZEN1' 是只读的, 无法在其中分配空间
已用时间: 00: 00: 00.01
SQL> alter tablespace andzen1 read write;
表空间已更改。
已用时间: 00: 00: 00.17
SQL>
SQL>
SQL> INSERT INTO ANDZEN.TY SELECT 2 FROM
DUAL;
已创建 1 行。
已用时间: 00: 00: 00.05