oracle 異構平臺遷移之傳輸表空間一例
第一章 傳輸表空間一例(win檔案系統 -> linux asm )
blog 結構圖：
1 場景描述
源平臺為：windows xp 32系統 + oracle 11.2.0.1.0 + ORACLE_SID=orcl
目標平臺：rhel6.5 + oracle 11.2.0.1.0 + asm 64位 + ORACLE_SID=orclasm
目標：要實現將自定義的應用程式表空間app1tbs,app2tbs,idxtbs從源平臺傳遞到目標平臺
注：
① 從linux到windows 下參考： http://blog.itpub.net/26736162/viewspace-1375260/
② source和target database的資料庫版本最好一致，否則會因為db time zone 不一致導致報如下錯誤，但是source如果大於target的話是可以的，向下相容的
ORA-39002: invalid operation
ORA-39322: Cannot use transportabletablespace with timestamp with timezone columns and different timezone version.
2 環境準備
2.1 在源庫上建立3個使用者應用的表空間
C:\Users\Administrator>sqlplus lhr/lhr@orclxp
SQL*Plus: Release 11.2.0.1.0 Production on 星期一 1月 5 17:15:22 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
連線到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
F:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
F:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
F:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
F:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF
F:\APP\ORACLE\ORADATA\ORCL\RMAN.DBF
SQL> create tablespace app1tbs datafile 'F:\APP\ORACLE\ORADATA\ORCL\APP1TBS.DBF' size 10m;
表空間已建立。
SQL> create tablespace app2tbs datafile 'F:\APP\ORACLE\ORADATA\ORCL\APP2TBS.DBF' size 10m;
表空間已建立。
SQL> CREATE TABLESPACE IDXTBS DATAFILE 'F:\APP\ORACLE\ORADATA\ORCL\IDXTBS.DBF' SIZE 10M;
表空間已建立。
SQL> set line 9999 pagesize 9999
SQL> SELECT a.NAME, b.NAME FROM v$tablespace a , v$datafile b WHERE a.TS#=b.TS# ;
NAME NAME
------------------------------ ------------------------------------------------------------
SYSTEM F:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
SYSAUX F:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
UNDOTBS1 F:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
USERS F:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF
RMAN_TS F:\APP\ORACLE\ORADATA\ORCL\RMAN.DBF
APP1TBS F:\APP\ORACLE\ORADATA\ORCL\APP1TBS.DBF
APP2TBS F:\APP\ORACLE\ORADATA\ORCL\APP2TBS.DBF
IDXTBS F:\APP\ORACLE\ORADATA\ORCL\IDXTBS.DBF
已選擇8行。
SQL>
SQL>
2.2 在相應的表空間建立表和索引
SQL> create table scott.app1_tab tablespace app1tbs as select * from scott.emp;
Table created.
SQL> create table scott.app2_tab tablespace app2tbs as select * from scott.dept;
Table created.
SQL> create index scott.idx_emp_ename on scott.app1_tab(ename) tablespace idxtbs;
Index created.
SQL> create index scott.idx_dept_dname on scott.app2_tab(dname) tablespace idxtbs;
Index created.
SQL>
3 判斷平臺支援並確定位元組序
如果傳輸表空間集到不同的平臺，則要確定對於源和目標平臺這種跨平臺表空間被支援，也要確定每個平臺的位元組序，如果平臺具有相同的位元組序，則不需要進行轉化，否則必須做一個表空間集轉化，在源端或目標端。
3.1 在源平臺查詢
SQL> col platform_name for a40
SQL> select d.platform_name,tp.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
結論：當前的系統平臺支援跨平臺表空間傳輸（因為上面的查詢有記錄返回）
3.2 在目標平臺查詢
SQL> col platform_name for a40
SQL> select d.platform_name,tp.endian_format from v$transportable_platform tp,v$database d
2 where tp.platform_name=d.platform_name;
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Linux x86 64-bit Little
SQL>
結論： 當前的平臺支援跨平臺的表空間傳輸源平臺和目標平臺的Endian_format 相同（均為Little），不需要進行表空間集轉換
4 選擇自包含的表空間集（目前要傳輸app1tbs和idxtbs這2個表空間）
4.1 進行檢查
Indicates whether a full or partial dependency check is required. If TRUE, treats all IN and OUT pointers(dependencies) and captures them as violations if they are not self-contained in the transportable set.
SQL> execute sys.dbms_tts.transport_set_check('app1tbs,idxtbs',true);
PL/SQL procedure successfully completed.
4.2 檢視檢查結果
SQL> col violations for a70
SQL> select * from sys.transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
ORA-39907: 索引 SCOTT.IDX_DEPT_DNAME (在表空間 IDXTBS 中) 指向表 SCOTT.APP2_TAB
(在表空間 APP2TBS 中)。
SQL>
結論： 在idxtbs表空間中IDX_DEPT_DNAME索引指向了表空間集外的SYS.APP2_TAB表，所以這裡選擇app1tabs,app2tabs,idxtbs作為新的表空間集再次進行檢查
SQL> execute sys.dbms_tts.transport_set_check('app1tbs,app2tbs,idxtbs',true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
SQL>
結論： 此時這個表空間集已經不在違背自包含的條件，可以確定為一個可傳輸表空間集
5 產生可傳輸表空間集
5.1 使自包含的表空間集中的所有表空間變為只讀狀態
SQL> alter tablespace app1tbs read only;
Tablespace altered.
SQL> alter tablespace app2tbs read only;
Tablespace altered.
SQL> alter tablespace idxtbs read only;
Tablespace altered.
5.2 使用資料泵匯出工具，匯出要傳輸的各個表空間的後設資料
5.2.1 確定匯出目錄
SQL> set line 9999
SQL> col directory_name for a25
SQL> col directory_path for a100
SQL> select directory_name,directory_path from dba_directories;
DIRECTORY_NAME DIRECTORY_PATH
------------------------- --------------------------------------------
XMLDIR c:\ade\aime_dadvfm0254\oracle\rdbms\xml
ASMSRC F:\app\oracle\oradata\orcl
DATA_PUMP_DIR F:\app\oracle\admin\orcl\dpdump\
ORACLE_OCM_CONFIG_DIR F:\app\oracle\product\ccr\state
SQL>
SQL>
5.2.2 開始匯出
C:\Users\Administrator>expdp system/lhr@orclxp dumpfile=expdat.dmp directory=DATA_PUMP_DIR transport_tablespaces=app1tbs,app2tbs,idxtbs logfile=tts_export.log
Export: Release 11.2.0.1.0 - Production on 星期一 1月 5 19:29:29 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
連線到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
啟動 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/********@orclxp dumpfile=expdat.dmp directory=DATA_PUMP_DIR transport_tablespaces=app1tbs,app2tbs,idxtbs logfile=tts_export.log
處理物件型別 TRANSPORTABLE_EXPORT/PLUGTS_BLK
處理物件型別 TRANSPORTABLE_EXPORT/TABLE
處理物件型別 TRANSPORTABLE_EXPORT/INDEX
處理物件型別 TRANSPORTABLE_EXPORT/INDEX_STATISTICS
處理物件型別 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
已成功載入/解除安裝了主表 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 的轉儲檔案集為:
F:\APP\ORACLE\ADMIN\ORCL\DPDUMP\EXPDAT.DMP
******************************************************************************
可傳輸表空間 APP1TBS 所需的資料檔案:
F:\APP\ORACLE\ORADATA\ORCL\APP1TBS.DBF
可傳輸表空間 APP2TBS 所需的資料檔案:
F:\APP\ORACLE\ORADATA\ORCL\APP2TBS.DBF
可傳輸表空間 IDXTBS 所需的資料檔案:
F:\APP\ORACLE\ORADATA\ORCL\IDXTBS.DBF
作業 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已於 19:30:07 成功完成
C:\Users\Administrator>
檢視檔案：
5.3 將資料檔案拷貝到dpdump目錄下
6 還原源庫中的表空間為讀/寫模式
SQL> alter tablespace app1tbs read write;
Tablespace altered.
SQL> alter tablespace app2tbs read write;
Tablespace altered.
SQL> alter tablespace idxtbs read write;
Tablespace altered.
SQL>
至此，已和源庫沒有任何關係。
7 傳輸檔案
這裡需要傳輸轉儲元檔案和資料檔案到目標庫
7.1 利用ftp工具傳輸轉儲元檔案到目標庫
[root@rhel6_lhr share-2]# cd dpdump/
[root@rhel6_lhr dpdump]# ll
total 30850
-rwxrwxrwx 1 root root 10493952 Jan 5 19:27 APP1TBS.DBF
-rwxrwxrwx 1 root root 10493952 Jan 5 19:27 APP2TBS.DBF
-rwxrwxrwx 1 root root 106496 Jan 5 19:30 EXPDAT.DMP
-rwxrwxrwx 1 root root 10493952 Jan 5 19:27 IDXTBS.DBF
-rwxrwxrwx 1 root root 1373 Jan 5 19:30 tts_export.log
[root@rhel6_lhr dpdump]#
7.2 檢視目標庫資料檔案位置和匯入目錄
[oracle@rhel6 ~]$ env | grep ORACLE
ORACLE_UNQNAME=orcl
ORACLE_SID=orcl
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=192.168.59.129
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@rhel6 ~]$ export ORACLE_SID=orclasm
[oracle@rhel6 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 6 09:50:44 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/orclasm/datafile/system.256.868235071
+DATA/orclasm/datafile/sysaux.257.868235073
+DATA/orclasm/datafile/undotbs1.258.868235073
+DATA/orclasm/datafile/users.259.868235073
13 rows selected.
SQL> select directory_name,directory_path from dba_directories;
DIRECTORY_NAME DIRECTORY_PATH
------------------------- ----------------------------------------------------------------------------------------------------
XMLDIR /ade/b/2125410156/oracle/rdbms/xml
DATA_PUMP_DIR /u01/app/oracle/admin/orclasm/dpdump/
ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
3 rows selected.
SQL>
7.3 拷貝檔案到目標庫相應位置並修改檔案許可權
[root@rhel6_lhr dpdump]# rm -rf /u01/app/oracle/admin/orclasm/dpdump/*
[root@rhel6_lhr dpdump]#
[root@rhel6_lhr dpdump]#
[root@rhel6_lhr dpdump]#
[root@rhel6_lhr dpdump]# mv * /u01/app/oracle/admin/orclasm/dpdump/
[root@rhel6_lhr dpdump]# ll /u01/app/oracle/admin/orclasm/dpdump/
total 30852
-rwxrwxrwx 1 root root 10493952 Jan 5 19:27 APP1TBS.DBF
-rwxrwxrwx 1 root root 10493952 Jan 5 19:27 APP2TBS.DBF
-rwxrwxrwx 1 root root 106496 Jan 5 19:30 EXPDAT.DMP
-rwxrwxrwx 1 root root 10493952 Jan 5 19:27 IDXTBS.DBF
-rwxrwxrwx 1 root root 1373 Jan 5 19:30 tts_export.log
[root@rhel6 dpdump]# chown oracle:oinstall APP1TBS.DBF
[root@rhel6 dpdump]# chown oracle:oinstall APP2TBS.DBF
[root@rhel6 dpdump]# chown oracle:oinstall IDXTBS.DBF
[root@rhel6 dpdump]# ll
total 30860
-rwxr-xr-x. 1 oracle oinstall 10493952 Jan 6 00:46 APP1TBS.DBF
-rwxr-xr-x. 1 oracle oinstall 10493952 Jan 6 00:46 APP2TBS.DBF
-rwxr-xr-x. 1 root root 106496 Jan 6 00:46 EXPDAT.DMP
-rwxr-xr-x. 1 oracle oinstall 10493952 Jan 6 00:46 IDXTBS.DBF
-rw-r--r--. 1 oracle oinstall 236 Jan 6 00:52 par.f
-rwxr-xr-x. 1 root root 1373 Jan 6 00:46 tts_export.log
-rw-r--r--. 1 oracle asmadmin 917 Jan 6 00:52 tts_import.log
[root@rhel6 dpdump]#
8 開始匯入
8.1 生成parfile檔案
檔案內容如下：
[root@rhel6_lhr dpdump]# vi par.f
[root@rhel6_lhr dpdump]# more par.f
DUMPFILE=EXPDAT.DMP
DIRECTORY=DATA_PUMP_DIR
TRANSPORT_DATAFILES=
/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.DBF,
/u01/app/oracle/admin/orclasm/dpdump/APP2TBS.DBF,
/u01/app/oracle/admin/orclasm/dpdump/IDXTBS.DBF
LOGFILE=tts_import.log
[root@rhel6_lhr dpdump]#
8.2 開始匯入
[oracle@rhel6 ~]$ impdp system/lhr parfile='/u01/app/oracle/admin/orclasm/dpdump/par.f'
Import: Release 11.2.0.1.0 - Production on Tue Jan 6 00:52:36 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** parfile=/u01/app/oracle/admin/orclasm/dpdump/par.f
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 00:52:57
[oracle@rhel6 ~]$
8.3 檢視目標平臺資訊
[oracle@rhel6 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 6 00:53:46 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
APP1TBS READ ONLY
APP2TBS READ ONLY
IDXTBS READ ONLY
8 rows selected.
SQL>
已選擇9行。
SQL> alter tablespace app1tbs read write;
表空間已更改。
SQL> alter tablespace app2tbs read write;
表空間已更改。
SQL> alter tablespace idxtbs read write;
表空間已更改。
SQL>
SQL> set line 9999 pagesize 9999
SQL> select * from scott.app1_tab;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
9000 lastwiner
9001 lastwiner
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已選擇16行。
SQL> select * from scott.app2_tab;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select D.owner,D.index_name,D.table_name,D.tablespace_name from dba_indexes d WHERE d.table_name in ('APP1_TAB','APP2_TAB');
OWNER INDEX_NAME TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------------------------------------
SCOTT IDX_EMP_ENAME APP1_TAB IDXTBS
SCOTT IDX_DEPT_DNAME APP2_TAB IDXTBS
SQL> SELECT a.NAME, b.NAME FROM v$tablespace a , v$datafile b WHERE a.TS#=b.TS# ;
NAME NAME
----------- --------------------------------------------
SYSTEM +DATA/orclasm/datafile/system.256.868235071
SYSAUX +DATA/orclasm/datafile/sysaux.257.868235073
UNDOTBS1 +DATA/orclasm/datafile/undotbs1.258.868235073
USERS +DATA/orclasm/datafile/users.259.868235073
APP1TBS /u01/app/oracle/admin/orclasm/dpdump/APP1TBS.DBF
APP2TBS /u01/app/oracle/admin/orclasm/dpdump/APP2TBS.DBF
IDXTBS /u01/app/oracle/admin/orclasm/dpdump/IDXTBS.DBF
7 rows selected.
SQL>
9 修改表空間對應的檔名
執行如下指令碼：
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 3
Current log sequence 5
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2215944 bytes
Variable Size 398462968 bytes
Database Buffers 218103808 bytes
Redo Buffers 7544832 bytes
Database mounted.
rman convert修改：
[oracle@rhel6 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jan 6 01:02:30 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLASM (DBID=3442859793, not open)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCLASM
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** +DATA/orclasm/datafile/system.256.868235071
2 480 SYSAUX *** +DATA/orclasm/datafile/sysaux.257.868235073
3 75 UNDOTBS1 *** +DATA/orclasm/datafile/undotbs1.258.868235073
4 5 USERS *** +DATA/orclasm/datafile/users.259.868235073
5 10 APP1TBS *** /u01/app/oracle/admin/orclasm/dpdump/APP1TBS.DBF
6 10 APP2TBS *** /u01/app/oracle/admin/orclasm/dpdump/APP2TBS.DBF
7 10 IDXTBS *** /u01/app/oracle/admin/orclasm/dpdump/IDXTBS.DBF
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 +DATA/orclasm/tempfile/temp.264.868235253
RMAN> backup as copy datafile 5 format '+DATA';
Starting backup at 06-JAN-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=247 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.DBF
output file name=+DATA/orclasm/datafile/app1tbs.266.868237521 tag=TAG20150106T010520 RECID=1 STAMP=868237522
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 06-JAN-15
RMAN> backup as copy datafile 6 format '+DATA';
Starting backup at 06-JAN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/admin/orclasm/dpdump/APP2TBS.DBF
output file name=+DATA/orclasm/datafile/app2tbs.267.868237551 tag=TAG20150106T010550 RECID=2 STAMP=868237551
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 06-JAN-15
RMAN> backup as copy datafile 7 format '+DATA';
Starting backup at 06-JAN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/admin/orclasm/dpdump/IDXTBS.DBF
output file name=+DATA/orclasm/datafile/idxtbs.268.868237557 tag=TAG20150106T010556 RECID=3 STAMP=868237557
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 06-JAN-15
RMAN> SWITCH TABLESPACE APP1TBS TO COPY;
datafile 5 switched to datafile copy "+DATA/orclasm/datafile/app1tbs.266.868237521"
RMAN> SWITCH TABLESPACE APP2TBS TO COPY;
datafile 6 switched to datafile copy "+DATA/orclasm/datafile/app2tbs.267.868237551"
RMAN> SWITCH TABLESPACE IDXTBS TO COPY;
datafile 7 switched to datafile copy "+DATA/orclasm/datafile/idxtbs.268.868237557"
RMAN> ALTER DATABASE OPEN;
database opened
RMAN>
檢視結果：
RMAN> report schema;
Report of database schema for database with db_unique_name ORCLASM
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** +DATA/orclasm/datafile/system.256.868235071
2 480 SYSAUX *** +DATA/orclasm/datafile/sysaux.257.868235073
3 75 UNDOTBS1 *** +DATA/orclasm/datafile/undotbs1.258.868235073
4 5 USERS *** +DATA/orclasm/datafile/users.259.868235073
5 10 APP1TBS *** +DATA/orclasm/datafile/app1tbs.266.868237521
6 10 APP2TBS *** +DATA/orclasm/datafile/app2tbs.267.868237551
7 10 IDXTBS *** +DATA/orclasm/datafile/idxtbs.268.868237557
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 +DATA/orclasm/tempfile/temp.264.868235253
RMAN>
SQL> SELECT a.NAME, b.NAME FROM v$tablespace a , v$datafile b WHERE a.TS#=b.TS#;
NAME NAME
--------------- ------------------------------------------------------
SYSTEM +DATA/orclasm/datafile/system.256.868235071
SYSAUX +DATA/orclasm/datafile/sysaux.257.868235073
UNDOTBS1 +DATA/orclasm/datafile/undotbs1.258.868235073
USERS +DATA/orclasm/datafile/users.259.868235073
APP1TBS +DATA/orclasm/datafile/app1tbs.266.868237521
APP2TBS +DATA/orclasm/datafile/app2tbs.267.868237551
IDXTBS +DATA/orclasm/datafile/idxtbs.268.868237557
7 rows selected.
SQL>
已選擇8行。
查詢資料：
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
RMAN_TS ONLINE
APP1TBS ONLINE
APP2TBS ONLINE
IDXTBS ONLINE
已選擇9行。
SQL> set line 9999 pagesize 9999
SQL> select * from scott.app1_tab;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
9000 lastwiner
9001 lastwiner
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已選擇16行。
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-1391913/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 跨平臺表空間遷移(傳輸表空間)
- [zt]跨平臺表空間傳輸 (DB遷移)
- 用傳輸表空間跨平臺遷移資料
- Oracle 不同平臺間表空間遷移Oracle
- Oracle資料庫同平臺與異構平臺下的表空間傳輸Oracle資料庫
- 利用可傳輸表空間跨平臺遷移 -RMAN CONVERT
- oracle 傳輸表空間一例Oracle
- 【資料遷移】XTTS跨平臺傳輸表空間v4TTS
- 海量資料遷移之傳輸表空間(一)
- 【資料遷移】XTTS跨平臺傳輸表空間（1.傳統方式）TTS
- 基於可傳輸表空間的表空間遷移
- Oracle資料庫遷移 - 異構傳輸表空間TTS HP-UX遷移至Redhat Linux 7.7Oracle資料庫TTSRedhatLinux
- 【DB寶50】Oracle異構平臺遷移之完全可傳輸匯出匯入Oracle
- 【資料遷移】使用傳輸表空間遷移資料
- 【傳輸表空間】使用 EXPDP/IMPDP工具的傳輸表空間完成資料遷移
- 【傳輸表空間】使用 EXPDP/IMPDP工具的傳輸表空間完成資料遷移[轉]
- oracle跨版本與平臺執行傳輸表空間Oracle
- 【資料遷移】XTTS跨平臺傳輸表空間v3（3.DFT方式）TTS
- 【資料遷移】XTTS跨平臺傳輸表空間v3（2.RMAN增量）TTS
- Oracle 表空間傳輸Oracle
- oracle表空間傳輸Oracle
- Oracle傳輸表空間Oracle
- oracle 12c 使用RMAN的傳輸表空間功能在PDB之間遷移資料Oracle
- 用可傳輸表空間實現Oracle跨平臺移植Oracle
- ORACLE表批量遷移表空間Oracle
- 10g新特性之-跨平臺表空間傳輸
- 12c跨平臺傳輸表空間
- 跨平臺表空間傳輸的實現
- 10g跨平臺傳輸表空間
- 傳輸表空間操作-OracleOracle
- Oracle傳輸表空間（TTS）OracleTTS
- Oracle 傳輸表空間-RmanOracle
- mysql之 表空間傳輸MySql
- Ora10G跨平臺傳輸表空間
- RMAN跨平臺傳輸表空間(different Endian)
- RMAN跨平臺傳輸表空間(same endian)
- 利用CONVERT實現跨平臺表空間遷移
- 表空間遷移