表空间迁移

异构平台的表空间迁移。在平台不一致时，特别是endian_format不一致的情况下如何进行数据文件的迁移 

原平台：Solaris[tm] OE (64-bit) endian_format 为big ，现有平台：Linux IA (32-bit)  endian_format 为litter

1、创建用户，赋权，创建目录，赋权

SQL> create user sst identified by sst;

User created.

SQL> grant connect,resource,imp_full_database to sst;

Grant succeeded.

SQL> create directory tempfile as '/home/oracle/scripts';

Directory created.

SQL> grant all on directory tempfile to sst;

Grant succeeded.

2、查看当前平台版本

SQL> select PLATFORM_NAME from v$database;

PLATFORM_NAME
--------------------------------------------------------------------------------
Linux IA (32-bit)

3、查看支持转换的平台

SQL> select PLATFORM_NAME,ENDIAN_FORMAT from v$transportable_platform;

PLATFORM_NAME                            ENDIAN_FORMAT
---------------------------------------- --------------
Solaris[tm] OE (32-bit)                  Big
Solaris[tm] OE (64-bit)                  Big
Microsoft Windows IA (32-bit)            Little
Linux IA (32-bit)                        Little
AIX-Based Systems (64-bit)               Big
HP-UX (64-bit)                           Big
HP Tru64 UNIX                            Little
HP-UX IA (64-bit)                        Big
Linux IA (64-bit)                        Little
HP Open VMS                              Little
Microsoft Windows IA (64-bit)            Little
IBM zSeries Based Linux                  Big
Linux x86 64-bit                         Little
Apple Mac OS                             Big
Microsoft Windows x86 64-bit             Little
Solaris Operating System (x86)           Little
IBM Power Based Linux                    Big
HP IA Open VMS                           Little
Solaris Operating System (x86-64)        Little
Apple Mac OS (x86-64)                    Little

20 rows selected.

可以看到支持Solaris的平台数据文件转换

3、现在开始进行转换

[oracle@oddpc ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Sep 1 16:09:52 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD5 (DBID=1633016061)

RMAN>  convert from platform 'Solaris[tm] OE (64-bit)' datafile '/home/oracle/scripts/TRPDATA_6' format '/u01/app/oracle/oradata/PROD5/trpdata01.dbf';

Starting conversion at target at 01-SEP-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/scripts/TRPDATA_6
converted datafile=/u01/app/oracle/oradata/PROD5/trpdata01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 01-SEP-16

4、导入数据

[oracle@oddpc ~]$ impdp sst/sst directory=tempfile dumpfile=trans3_2.dmp transport_datafiles=/u01/app/oracle/oradata/PROD5/trpdata01.dbf

Import: Release 11.2.0.3.0 - Production on Thu Sep 1 16:31:20 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SST"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SST"."SYS_IMPORT_TRANSPORTABLE_01":  sst/******** directory=tempfile dumpfile=trans3_2.dmp transport_datafiles=/u01/app/oracle/oradata/PROD5/trpdata01.dbf 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SST"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 16:31:23

5、查看表空间的状态

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TRPDATA                        READ ONLY

6、可以看到当前的表空间trpdata是只读的，修改成正常状态

SQL> alter tablespace TRPDATA read write;

Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TRPDATA                        ONLINE

6 rows selected.

表空间正常，数据迁移完毕。