第一章 传输表空间一例(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>