oracle 傳輸表空間一例
文件結構圖：
1 場景描述
源平臺為：rhel6.5 + oracle 11.2.0.3.0 + asm 64位
目標平臺：windows xp 32系統 + oracle 11.2.0.1.0
要實現將自定義的應用程式表空間app1tbs,app2tbs,idxtbs從源平臺傳遞到目標平臺
2 環境準備
2.1 在源庫上建立3個使用者應用的表空間
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 21 17:00:38 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create tablespace app1tbs datafile '+DATA' size 50m;
Tablespace created.
SQL> create tablespace app2tbs datafile '+DATA' size 50m;
Tablespace created.
SQL> create tablespace idxtbs datafile '+DATA' size 50m;
Tablespace created.
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 +DATA/orclasm/datafile/system.256.850260145
SYSAUX +DATA/orclasm/datafile/sysaux.257.850260145
UNDOTBS1 +DATA/orclasm/datafile/undotbs1.258.851526539
USERS +DATA/orclasm/datafile/users.259.850260147
EXAMPLE +DATA/orclasm/datafile/example.265.850260295
UNDOTBS2 +DATA/orclasm/datafile/undotbs2.267.851204361
TBS_RC +DATA/orclasm/datafile/tbs_rc.268.852116523
TS_LHR +DATA/orclasm/datafile/ts_lhr.269.852632495
ENCRYPTED_TS +DATA/orclasm/datafile/encrypted_ts.272.854650889
GOLDENGATE +DATA/orclasm/datafile/goldengate.273.862829891
APP1TBS +DATA/orclasm/datafile/app1tbs.274.866911939
APP2TBS +DATA/orclasm/datafile/app2tbs.275.866912075
IDXTBS +DATA/orclasm/datafile/idxtbs.276.866912133
13 rows selected.
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
---------------------------------------- --------------
Linux x86 64-bit Little
SQL>
結論：當前的系統平臺支援跨平臺表空間傳輸（因為上面的查詢有記錄返回）
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
---------------------------------------- --------------
Microsoft Windows IA (32-bit) Little
結論： 當前的windows 平臺支援跨平臺的表空間傳輸
源平臺和目標平臺的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 dbms_tts.transport_set_check('app1tbs,idxtbs',true);
PL/SQL procedure successfully completed.
4.2 檢視檢查結果
SQL> col violations for a70
SQL> select * from transport_set_violations;
VIOLATIONS
------------------------------------------------------------------------------------------------------------
ORA-39907: Index scott.IDX_DEPT_DNAME in tablespace IDXTBS points to table scott.APP2_TAB in tablespace APP2TBS.
SQL>
結論： 在idxtbs表空間中IDX_DEPT_DNAME索引指向了表空間集外的SYS.APP2_TAB表，所以這裡選擇app1tabs,app2tabs,idxtbs作為新的表空間集再次進行檢查
SQL> execute 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
------------------------- ----------------------------------------------------------------------------------------------------
REPDIR /oradata06/repdir
DIR_ALERT_LHR /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace
SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep
SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/
DIR_ALERT /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace
DIR_ALERT_XML_LHR /u01/app/oracle/diag/rdbms/orclasm/orclasm/alert
LOG_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/
MEDIA_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/
XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml
DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/
DATA_PUMP_DIR /u01/app/oracle/admin/orclasm/dpdump/
DIRECTORY_NAME DIRECTORY_PATH
------------------------- ----------------------------------------------------------------------------------------------------
ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
12 rows selected.
5.2.2 開始匯出
[oracle@rhel6_lhr ~]$ env | grep ORACLE
ORACLE_UNQNAME=orclasm
ORACLE_SID=orclasm
ORACLE_HOSTNAME=192.168.59.130
ORACLE_BASE=/u01/app/oracle
ORACLE_ALERT=/u01/app/oracle/diag/rdbms/orclasm/orclasm/trace
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@rhel6_lhr ~]$ expdp system dumpfile=expdat.dmp directory=DATA_PUMP_DIR transport_tablespaces=app1tbs,app2tbs,idxtbs logfile=tts_export.log
Export: Release 11.2.0.3.0 - Production on Sun Dec 21 17:48:20 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=expdat.dmp directory=DATA_PUMP_DIR transport_tablespaces=app1tbs,app2tbs,idxtbs logfile=tts_export.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/admin/orclasm/dpdump/expdat.dmp
******************************************************************************
Datafiles required for transportable tablespace APP1TBS:
+DATA/orclasm/datafile/app1tbs.274.866911939
Datafiles required for transportable tablespace APP2TBS:
+DATA/orclasm/datafile/app2tbs.275.866912075
Datafiles required for transportable tablespace IDXTBS:
+DATA/orclasm/datafile/idxtbs.276.866912133
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:49:47
[oracle@rhel6_lhr ~]$
檢視檔案：
[root@rhel6_lhr ~]# cd /u01/app/oracle/admin/orclasm/dpdump/
[root@rhel6_lhr dpdump]# ll
total 108
-rw-r----- 1 oracle asmadmin 106496 Dec 21 17:49 expdat.dmp
-rw-r--r-- 1 oracle asmadmin 1499 Dec 21 17:49 tts_export.log
[root@rhel6_lhr dpdump]#
告警日誌可以看到：
Sun Dec 21 17:48:50 2014
DM00 started with pid=45, OS id=13188, job SYSTEM.SYS_EXPORT_TRANSPORTABLE_01
Sun Dec 21 17:48:56 2014
DW00 started with pid=46, OS id=13190, wid=1, job SYSTEM.SYS_EXPORT_TRANSPORTABLE_01
Sun Dec 21 17:49:15 2014
XDB installed.
XDB initialized.
5.3 生成資料檔案
[root@rhel6_lhr ~]# su - grid
[grid@rhel6_lhr ~]$ asmcmd
ASMCMD> cd +DATA/orclasm/datafile/
ASMCMD> ls
APP1TBS.274.866911939
APP2TBS.275.866912075
ENCRYPTED_TS.272.854650889
EXAMPLE.265.850260295
GOLDENGATE.273.862829891
IDXTBS.276.866912133
SYSAUX.257.850260145
SYSTEM.256.850260145
TBS_RC.268.852116523
TS_LHR.269.852632495
UNDOTBS1.258.851526539
UNDOTBS2.267.851204361
USERS.259.850260147
example.265.850260295_bk
ASMCMD> cp APP1TBS.274.866911939 /u01/app/oracle/admin/orclasm/dpdump
copying +DATA/orclasm/datafile/APP1TBS.274.866911939 -> /u01/app/oracle/admin/orclasm/dpdump/APP1TBS.274.866911939
ASMCMD-8016: copy source->'+DATA/orclasm/datafile/APP1TBS.274.866911939' and target->'/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.274.866911939' failed
ORA-19505: failed to identify file "/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.274.866911939"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 13: Permission denied
Additional information: 1
ORA-15120: ASM file name '/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.274.866911939' does not begin with the ASM prefix character
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 413
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)
無許可權，暫時拷貝到grid目錄下：
ASMCMD> cp APP1TBS.274.866911939 /home/grid
copying +DATA/orclasm/datafile/APP1TBS.274.866911939 -> /home/grid/APP1TBS.274.866911939
ASMCMD> cp APP2TBS.275.866912075 /home/grid
copying +DATA/orclasm/datafile/APP2TBS.275.866912075 -> /home/grid/APP2TBS.275.866912075
ASMCMD> cp IDXTBS.276.866912133 /home/grid
copying +DATA/orclasm/datafile/IDXTBS.276.866912133 -> /home/grid/IDXTBS.276.866912133
ASMCMD>
然後再拷貝到一個目錄下：
[root@rhel6_lhr dpdump]# ll
total 108
-rw-r----- 1 oracle asmadmin 106496 Dec 21 17:49 expdat.dmp
-rw-r--r-- 1 oracle asmadmin 1499 Dec 21 17:49 tts_export.log
[root@rhel6_lhr dpdump]# cp /home/grid/APP1TBS.274.866911939 /u01/app/oracle/admin/orclasm/dpdump/
[root@rhel6_lhr dpdump]# cp /home/grid/APP2TBS.275.866912075 /u01/app/oracle/admin/orclasm/dpdump/
[root@rhel6_lhr dpdump]# cp /home/grid/IDXTBS.276.866912133 /u01/app/oracle/admin/orclasm/dpdump/
[root@rhel6_lhr dpdump]# ll
total 153732
-rw-r----- 1 root root 52436992 Dec 21 18:05 APP1TBS.274.866911939
-rw-r----- 1 root root 52436992 Dec 21 18:06 APP2TBS.275.866912075
-rw-r----- 1 oracle asmadmin 106496 Dec 21 17:49 expdat.dmp
-rw-r----- 1 root root 52436992 Dec 21 18:06 IDXTBS.276.866912133
-rw-r--r-- 1 oracle asmadmin 1499 Dec 21 17:49 tts_export.log
[root@rhel6_lhr 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 傳輸轉儲元檔案到目標庫
7.2 檢視目標庫資料檔案位置和目錄
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> 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
DATA_PUMP_DIR F:\app\oracle\admin\orcl\dpdump\
ORACLE_OCM_CONFIG_DIR F:\app\oracle\product\ccr\state
SQL>
7.3 拷貝檔案到目標庫相應位置
將表空間檔案拷貝到F:\APP\ORACLE\ORADATA\ORCL\下，將expdat.dmp 檔案拷貝到F:\app\oracle\admin\orcl\dpdump\ 下，如下：
8 開始匯入
8.1 生成parfile檔案
檔案內容如下：
DUMPFILE=expdat.dmp
DIRECTORY=DATA_PUMP_DIR
TRANSPORT_DATAFILES=
F:\app\oracle\oradata\orcl\APP1TBS.274.866911939,
F:\app\oracle\oradata\orcl\APP2TBS.275.866912075,
F:\app\oracle\oradata\orcl\IDXTBS.276.866912133
LOGFILE=tts_import.log
8.2 開始匯入
C:\Documents and Settings\Administrator>set oracle_sid=orcl
C:\Documents and Settings\Administrator>impdp sys parfile='F:\app\oracle\admin\orcl\dpdump\par.f'
Import: Release 11.2.0.1.0 - Production on 星期日 12月 21 18:39:02 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
口令:
UDI-28009: 操作產生了 ORACLE 錯誤 28009
ORA-28009: 應當以 SYSDBA 身份或 SYSOPER 身份建立 SYS 連線
使用者名稱: sys as sysdba
口令:
連線到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功載入/解除安裝了主表 "SYS"."SYS_IMPORT_TRANSPORTABLE_01"
啟動 "SYS"."SYS_IMPORT_TRANSPORTABLE_01": sys/******** AS SYSDBA parfile='F:\app\oracle\admin\orcl\dpdump\par.f'
處理物件型別 TRANSPORTABLE_EXPORT/PLUGTS_BLK
處理物件型別 TRANSPORTABLE_EXPORT/TABLE
處理物件型別 TRANSPORTABLE_EXPORT/INDEX/INDEX
處理物件型別 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作業 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 已於 18:39:15 成功完成
8.3 檢視目標平臺資訊
C:\Documents and Settings\Administrator>
檢視目標庫表空間狀態
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 READ ONLY
APP2TBS READ ONLY
IDXTBS READ ONLY
已選擇9行。
SQL> alter tablespace app1tbs read write;
表空間已更改。
SQL> alter tablespace app2tbs read write;
表空間已更改。
SQL> alter tablespace idxtbs read write;
表空間已更改。
SQL>
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 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.274.866911939
APP2TBS F:\APP\ORACLE\ORADATA\ORCL\APP2TBS.275.866912075
IDXTBS F:\APP\ORACLE\ORADATA\ORCL\IDXTBS.276.866912133
已選擇8行。
SQL>
9 修改表空間對應的檔名
執行如下指令碼：
create directory asmsrc as 'F:\app\oracle\oradata\orcl';
alter tablespace app1tbs offline ;
alter tablespace app2tbs offline ;
alter tablespace idxtbs offline ;
begin
dbms_file_transfer.copy_file('ASMSRC','APP1TBS.274.866911939','ASMSRC','APP1TBS.DBF');
dbms_file_transfer.copy_file('ASMSRC','APP2TBS.275.866912075','ASMSRC','APP2TBS.DBF');
dbms_file_transfer.copy_file('ASMSRC','IDXTBS.276.866912133','ASMSRC','IDXTBS.DBF');
END;
/
alter database rename file'F:\APP\ORACLE\ORADATA\ORCL\APP1TBS.274.866911939' to'F:\APP\ORACLE\ORADATA\ORCL\APP1TBS.DBF';
alter database rename file'F:\APP\ORACLE\ORADATA\ORCL\APP2TBS.275.866912075' to'F:\APP\ORACLE\ORADATA\ORCL\APP2TBS.DBF';
alter database rename file'F:\APP\ORACLE\ORADATA\ORCL\IDXTBS.276.866912133' to'F:\APP\ORACLE\ORADATA\ORCL\IDXTBS.DBF';
alter tablespace app1tbs online ;
alter tablespace app2tbs online ;
alter tablespace idxtbs online ;
檢視結果：
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> 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>
10 結束語
本例項是源庫為asm，目標庫為os檔案，另外，如果源庫為os檔案，而目標庫為asm檔案的話，以上步驟不變，在最後修改表空間對應的資料檔名稱的時候使用rman映象拷貝來重新命名檔案即可。
① 啟動目標庫到mount狀態
② rman 執行：
backup as copy datafile 6 format ‘+DATA/ORCLASM/XXX.DBF’;
SWITCH TABLESPACE APP1TBS TO COPY;
ALTER DATABSE OPEN;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-1375260/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【XTTS】Oracle傳輸表空間xtts增量方式TTSOracle
- MySQL 傳輸表空間MySql
- mysql之 表空間傳輸MySql
- Oracle表空間Oracle
- oracle 表空間Oracle
- 【XTTS】使用XTTS傳輸表空間將Oracle11.2.0.4資料遷移至Oracle19CTTSOracle
- 增加oracle表空間Oracle
- oracle temp 表空間Oracle
- oracle 表移動表空間Oracle
- Oracle表移動表空間Oracle
- MySQL傳輸表空間的簡單使用方法MySql
- oracle表空間的整理Oracle
- Oracle 批量建表空間Oracle
- Oracle清理SYSAUX表空間OracleUX
- 用傳輸表空間跨平臺遷移資料
- Oracle Temp 表空間切換Oracle
- Oracle 表空間增加檔案Oracle
- Oracle OCP(49)：表空間管理Oracle
- Oracle表空間收縮方案Oracle
- Oracle RMAN 表空間恢復Oracle
- Oracle新建使用者、表空間、表Oracle
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- Oracle資料庫遷移 - 異構傳輸表空間TTS HP-UX遷移至Redhat Linux 7.7Oracle資料庫TTSRedhatLinux
- Oracle的表空間quota詳解Oracle
- oracle臨時表空間相關Oracle
- oracle sql 表空間利用率OracleSQL
- Oracle OCP(47)：表空間的建立Oracle
- 【Oracle 恢復表空間】 實驗Oracle
- 【TABLESPACE】Oracle表空間最佳實踐Oracle
- 【資料遷移】XTTS跨平臺傳輸表空間（1.傳統方式）TTS
- 【資料遷移】XTTS跨平臺傳輸表空間v4TTS
- oracle 建立表空間和使用者Oracle
- Oracle建立表空間和使用者Oracle
- Oracle中新建表空間、使用者Oracle
- Oracle中表空間、表、索引的遷移Oracle索引
- ORACLE線上切換undo表空間Oracle
- oracle表空間增長趨勢分析Oracle
- Oracle OCP(46)：表空間、段、區、塊Oracle
- 【TABLESPACE】Oracle 表空間結構說明Oracle