实验记录：TSPITR 表空间定点时间恢复

TSPITR是英文Tablespace Point In Time Recovery的缩写。也就是表空间定点时间恢复。
TSPITR是一种相对细粒度的不完全恢复技术。我们通常见到的还原操作，都是将所有的表空间和数据还原到相同的一个时间点上。
而TSPITR则是以表空间为粒度单元，单独将某个表空间内容还原到一个特定可恢复时间点上。
举一个例子：一个Oracle数据库运行在归档模式下，在夜间零时保留一份完全备份。早上七点时候，某个特定表空间上数据表（单个表独占表空间）发生一个误操作，数据损坏。要求在不伤害其他数据表数据的情况下，将表空间数据恢复到早上六点。这样部分数据恢复的场景，就是TSPITR的典型应用。
切换到pams用户开搞：
1. 建立一个表空间
SQL> create tablespace tspitr datafile '/home/oracle/app/oradata/pamsdb/tspitr01.dbf' size 10M;
Tablespace created
2. 在这个表空间上创建一个表，并插入数据
SQL> create table tspitr_test tablespace tspitr as select * from sys.t2;
Table created
SQL> select count(*) from tspitr_test;
COUNT(*)
----------
   8
3. 对数据库做一个备份


--由于安装目录空间有限，需要将之前的备份集删除，腾出空间
RMAN> delete backupset;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status - -  Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
22 - - -22 - - -1 - 1 - AVAILABLE - DISK - - -  /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/0sshto2j_1_1
23 - - -23 - - -1 - 1 - AVAILABLE - DISK - - -  /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/0tshto2l_1_1
24 - - -24 - - -1 - 1 - AVAILABLE - DISK - - -  /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/0ushto51_1_1
25 - - -25 - - -1 - 1 - AVAILABLE - DISK - - -  /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/0vshto56_1_1
Do you really want to delete the above objects (enter YES or NO)- yes
deleted backup piece
backup piece handle=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/0sshto2j_1_1 RECID=22 STAMP=958324819
deleted backup piece
backup piece handle=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/0tshto2l_1_1 RECID=23 STAMP=958324821
deleted backup piece
backup piece handle=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/0ushto51_1_1 RECID=24 STAMP=958324898
deleted backup piece
backup piece handle=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/0vshto56_1_1 RECID=25 STAMP=958324902
Deleted 4 objects
RMAN> delete backup;
using channel ORA_DISK_1
specification does not match any backup in the repository
RMAN> backup database plus archivelog delete all input;
Starting backup at 23-OCT-17
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 10/23/2017 17:39:29
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_3_957807611.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
---------------------------
原因：之前自己手动用rm命令删掉了归档日志。但是controlfile中还记录着归档日志信息，oracle还会去找这些归档日志文件，因此就会报错。
解决方法：使控制文件中的归档日志信息和实际物理文件信息保持一致；
1. corsscheck archivelog all；
此命令用来检查控制文件和实际物理文件信息的差异。
2.delete expired archivelog all；
删除无效的归档日志信息，使检查控制文件和实际物理文件信息同步。
---------------------------
--处理过程
RMAN> crosscheck archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
validation failed for archived log
archived log file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_3_957807611.dbf RECID=9 STAMP=957869815
validation failed for archived log
archived log file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_4_957807611.dbf RECID=7 STAMP=957869815
validation failed for archived log
archived log file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_5_957807611.dbf RECID=8 STAMP=957869815
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_1_957869815.dbf RECID=10 STAMP=958144259
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_1_958144259.dbf RECID=11 STAMP=958153168
Crosschecked 5 objects
RMAN> report obsoleted;
--显示哪些备份已经废弃。
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
List of Archived Log Copies for database with db_unique_name PAMSDB
=====================================================================
Key - - Thrd Seq - - S Low Time 
------- ---- ------- - ---------
9 - - - 1 - -3 - - - X 20-OCT-17
 - - -  Name: /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_3_957807611.dbf
7 - - - 1 - -4 - - - X 20-OCT-17
 - - -  Name: /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_4_957807611.dbf
8 - - - 1 - -5 - - - X 20-OCT-17
 - - -  Name: /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_5_957807611.dbf
Do you really want to delete the above objects (enter YES or NO)- yes
deleted archived log
archived log file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_3_957807611.dbf RECID=9 STAMP=957869815
deleted archived log
archived log file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_4_957807611.dbf RECID=7 STAMP=957869815
deleted archived log
archived log file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_5_957807611.dbf RECID=8 STAMP=957869815
Deleted 3 EXPIRED objects
--删除过期备份集，重新备份
RMAN> backup database plus archivelog delete all input;
Starting backup at 26-OCT-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=41 STAMP=958325901
input archived log thread=1 sequence=6 RECID=42 STAMP=958326023
input archived log thread=1 sequence=7 RECID=43 STAMP=958326221
input archived log thread=1 sequence=8 RECID=44 STAMP=958356625
input archived log thread=1 sequence=9 RECID=45 STAMP=958382812
channel ORA_DISK_1: starting piece 1 at 26-OCT-17
channel ORA_DISK_1: finished piece 1 at 26-OCT-17
piece handle=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/10shvgms_1_1 tag=TAG20171026T092652 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_5_958322553.dbf RECID=41 STAMP=958325901
archived log file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_6_958322553.dbf RECID=42 STAMP=958326023
archived log file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_7_958322553.dbf RECID=43 STAMP=958326221
archived log file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_8_958322553.dbf RECID=44 STAMP=958356625
archived log file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_9_958322553.dbf RECID=45 STAMP=958382812
Finished backup at 26-OCT-17
Starting backup at 26-OCT-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/home/oracle/app/oradata/pamsdb/system01.dbf
input datafile file number=00002 name=/home/oracle/app/oradata/pamsdb/sysaux01.dbf
input datafile file number=00005 name=/home/oracle/app/oradata/pamsdb/DATA_PAMS_01.dbf
input datafile file number=00006 name=/home/oracle/app/oradata/pamsdb/DATA_PAMS_02.dbf
input datafile file number=00003 name=/home/oracle/app/oradata/pamsdb/undotbs01.dbf
input datafile file number=00007 name=/home/oracle/app/oradata/pamsdb/INDX_PAMS_01.dbf
input datafile file number=00009 name=/home/oracle/app/oradata/pamsdb/test_chen.dbf
input datafile file number=00013 name=/home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_test_dyjwd77b_.dbf
input datafile file number=00014 name=/home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_ts_uni_dyjwdn4f_.dbf
input datafile file number=00015 name=/home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_ts_auto_dyjwdn5l_.dbf
input datafile file number=00016 name=/home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_test1_dyjwf0vx_.dbf
input datafile file number=00017 name=/home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_test3_ms_dyjwf0z1_.dbf
input datafile file number=00018 name=/home/oracle/app/oradata/pamsdb/test4_assm.dbf
input datafile file number=00008 name=/home/oracle/app/oradata/pamsdb/undotbs02.dbf
input datafile file number=00010 name=/home/oracle/app/oradata/pamsdb/test_chen1.dbf
input datafile file number=00011 name=/home/oracle/app/oradata/pamsdb/ts_mssm.dbf
input datafile file number=00012 name=/home/oracle/app/oradata/pamsdb/big.dbf
input datafile file number=00004 name=/home/oracle/app/oradata/pamsdb/users01.dbf
input datafile file number=00020 name=/home/oracle/app/oradata/pamsdb/tspitr01.dbf
input datafile file number=00019 name=/home/oracle/app/oradata/pamsdb/blkerr01.dbf
channel ORA_DISK_1: starting piece 1 at 26-OCT-17
channel ORA_DISK_1: finished piece 1 at 26-OCT-17
piece handle=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/11shvgn7_1_1 tag=TAG20171026T092702 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:27
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 26-OCT-17
channel ORA_DISK_1: finished piece 1 at 26-OCT-17
piece handle=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/12shvgtn_1_1 tag=TAG20171026T092702 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 26-OCT-17
Starting backup at 26-OCT-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=46 STAMP=958383034
channel ORA_DISK_1: starting piece 1 at 26-OCT-17
channel ORA_DISK_1: finished piece 1 at 26-OCT-17
piece handle=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/13shvgtr_1_1 tag=TAG20171026T093035 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_10_958322553.dbf RECID=46 STAMP=958383034
Finished backup at 26-OCT-17
4. 我们做如下操作
SQL> select sequence#,status,sysdate from v$log;
 SEQUENCE# STATUS - - - - - SYSDATE
---------- ---------------- ---------
 - - - -10 INACTIVE - - - - 26-OCT-17
 - - - -11 CURRENT - - - - -26-OCT-17
 - - - - 9 INACTIVE - - - - 26-OCT-17
SQL> alter system switch logfile;
System altered.
SQL> select sequence#,status,sysdate from v$log;
 SEQUENCE# STATUS - - - - - SYSDATE
---------- ---------------- ---------
 - - - -10 INACTIVE - - - - 26-OCT-17
 - - - -11 ACTIVE - - - - - 26-OCT-17
 - - - -12 CURRENT - - - - -26-OCT-17
SQL> select count(*) from tspitr_test;
 -COUNT(*)
----------
 - - - - 8
SQL> insert into tspitr_test select * from tspitr_test;
8 rows created.
SQL> select count(*) from tspitr_test;
 -COUNT(*)
----------
 - - - -16
SQL> alter system switch logfile;
System altered.
SQL> select sequence#,status,sysdate from v$log;
 SEQUENCE# STATUS - - - - - SYSDATE
---------- ---------------- ---------
 - - - -13 CURRENT - - - - -26-OCT-17
 - - - -11 INACTIVE - - - - 26-OCT-17
 - - - -12 ACTIVE - - - - - 26-OCT-17
 - - - -
这里需要看一下，第一个操作时11为当前日志，此时记录数为8条，切换日志后，11变成active(活跃，用作crash recover，但不是当前日志)，12变成当前日志，此时往表中插入数据，记录数为16条，再次切换日志，12变为灾难恢复日志，13为当前日志，那么我们要恢复到表中有16条数据时，应该是until seq为12的，看看运行结果。
4. 建立辅助恢复目录
--之前创建在home目录下，这个目录只剩下400M空间，直接空间不够，因为rman会复制数据文件过来，所以重新创建辅助恢复目录
在backup目录下
chown oracle:oinstall aux
根目录下 -/backup/aux
6.执行恢复
--表空间名得是大写，否则报错：RMAN-06019: could not translate tablespace name "tspitr_test"
recover tablespace 'TSPITR' until logseq 12 auxiliary destination '/backup/aux';
==================
第一次实验报错，已经将表和表空间删除。
报错了：原因是表空间是sys用户创建的，不能搞：这里先drop 掉该表空间下的所有表，然后drop表空间，准备用pams用户重新创建，因为之前该表空间已经挂了数据文件，所以问题又来了：
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/25/2017 17:49:01
RMAN-05072: TRANSPORT_SET_CHECK failed
SQL> drop tablespace tspitr_test;
Tablespace dropped.
SQL> conn pams/pams@pamsdb
Connected.
SQL> create tablespace tspitr_test datafile '/home/oracle/app/oradata/pamsdb/tspitr_test01.dbf' size 10M;
create tablespace tspitr_test datafile '/home/oracle/app/oradata/pamsdb/tspitr_test01.dbf' size 10M
*
ERROR at line 1:
ORA-01119: error in creating database file
'/home/oracle/app/oradata/pamsdb/tspitr_test01.dbf'
ORA-27038: created file already exists
Additional information: 1


ORA-01119: 创建数据库文件出错--
我已经删除了表空间。为什么还说文件存在。事实证明drop tablespace-tspitr_test;这句命令只是删除了一些逻辑关联，但在指定目录下dbf文件还是存在的。
执行这句命令，则会连文件一便删除的。
drop tablespace sonar including contents and datafiles;
这样，重新执行建立表空间的语句就不会有错误了。
--之前失败的表空间也都删除成功，重新用pams用户登录，重建表空间和表
=====================================================


第二次实验，已经在pams下创建表空间和表：


执行：recover tablespace 'TSPITR' until logseq 12 auxiliary destination '/backup/aux';
rman在起辅助实例，执行drop tablespace- "TSPITR" including contents keep datafiles;报错
因为什么删除表空间内容而保留数据文件时失败呢，由于当前日志为seq13，而非12，是不是因为这里的until 12 是<12 而非<=12呢，我用seq13试试。


在重新实验之前，来看下部分日志：
留意一下这句日志内容：
archived log for thread 1 with sequence 11 is already on disk as file 
然后开始做seq13的实验。
starting media recovery
archived log for thread 1 with sequence 11 is already on disk as file /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_11_958322553.dbf
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/13shvgtr_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/13shvgtr_1_1 tag=TAG20171026T093035
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/backup/aux/1_10_958322553.dbf thread=1 sequence=10
channel clone_default: deleting archived log(s)
archived log file name=/backup/aux/1_10_958322553.dbf RECID=46 STAMP=958385719
archived log file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_11_958322553.dbf thread=1 sequence=11
media recovery complete, elapsed time: 00:00:00
Finished recover at 26-OCT-17
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  "TSPITR" read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/backup/aux''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/backup/aux''";
}
executing Memory Script
sql statement: alter tablespace -"TSPITR" read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/backup/aux''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/backup/aux''
Performing export of metadata...
 - EXPDP> Starting "SYS"."TSPITR_EXP_lrqx": -
 - EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
 - EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
 - EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
 - EXPDP> Master table "SYS"."TSPITR_EXP_lrqx" successfully loaded/unloaded
 - EXPDP> ******************************************************************************
 - EXPDP> Dump file set for SYS.TSPITR_EXP_lrqx is:
 - EXPDP> - /backup/aux/tspitr_lrqx_27703.dmp
 - EXPDP> ******************************************************************************
 - EXPDP> Datafiles required for transportable tablespace TSPITR:
 - EXPDP> - /home/oracle/app/oradata/pamsdb/tspitr01.dbf
 - EXPDP> Job "SYS"."TSPITR_EXP_lrqx" successfully completed at 10:17:55
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql 'drop tablespace  "TSPITR" including contents keep datafiles';
}
executing Memory Script
database closed
database dismounted
Oracle instance shut down
sql statement: drop tablespace -"TSPITR" including contents keep datafiles
Removing automatic instance
shutting down automatic instance 
target database instance not started
Automatic instance removed
auxiliary instance file /backup/aux/PAMSDB/datafile/o1_mf_temp_dz2kb2tg_.tmp deleted
auxiliary instance file /backup/aux/PAMSDB/onlinelog/o1_mf_3_dz2k9xgv_.log deleted
auxiliary instance file /backup/aux/PAMSDB/onlinelog/o1_mf_2_dz2k9voz_.log deleted
auxiliary instance file /backup/aux/PAMSDB/onlinelog/o1_mf_1_dz2k9tcs_.log deleted
auxiliary instance file /backup/aux/PAMSDB/datafile/o1_mf_sysaux_dz2k6w2h_.dbf deleted
auxiliary instance file /backup/aux/PAMSDB/datafile/o1_mf_undo2_dz2k7g1m_.dbf deleted
auxiliary instance file /backup/aux/PAMSDB/datafile/o1_mf_undotbs1_dz2k6w3j_.dbf deleted
auxiliary instance file /backup/aux/PAMSDB/datafile/o1_mf_system_dz2k6w29_.dbf deleted
auxiliary instance file /backup/aux/PAMSDB/controlfile/o1_mf_dz2k6l2n_.ctl deleted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/26/2017 10:18:19
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on default channel at 10/26/2017 10:18:18
RMAN-11003: failure during parse/execution of SQL statement: drop tablespace -"TSPITR" including contents keep datafiles
ORA-00604: error occurred at recursive SQL level 1
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
开始觉得ORA-00604,可能是表空间不足，但是查询后不是这个问题。
SQL> select
 -2  b.file_name as "物理文件名",
 -3  b.tablespace_name as "表空间",
 -4  b.bytes/1024/1024 as "大小M",
 -5  (b.bytes-sum(nvl(a.bytes,0)))/1024/1024 as "已使用M",
 -6  substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) as "利用率"
 -7 -from dba_free_space a,dba_data_files b
 -8 -where a.file_id=b.file_id
 -9 -group by b.tablespace_name,b.file_name,b.bytes
 10 -order by b.tablespace_name;
物理文件名 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 表空间 - - - - - - - - - - - - - - -  大小M - - - 已使用M 利用率
-------------------------------------------------------------------------------- ------------------------------ ---------- ---------- ----------
/home/oracle/app/oradata/pamsdb/big.dbf - - - - - - - - - - - - - - - - - - - -  BIG - - - - - - - - - - - - - - - - - -50 - - - - -9 18
/home/oracle/app/oradata/pamsdb/DATA_PAMS_01.dbf - - - - - - - - - - - - - - - - DATA_PAMS_01 - - - - - - - - - - - - -500 - -21.6875 4.337
/home/oracle/app/oradata/pamsdb/DATA_PAMS_02.dbf - - - - - - - - - - - - - - - - DATA_PAMS_02 - - - - - - - - - - - - -500 - - -85.75 17.15
/home/oracle/app/oradata/pamsdb/INDX_PAMS_01.dbf - - - - - - - - - - - - - - - - INDX_PAMS_01 - - - - - - - - - - - - -100 - -13.0625 13.06
/home/oracle/app/oradata/pamsdb/sysaux01.dbf - - - - - - - - - - - - - - - - - - SYSAUX - - - - - - - - - - - - - - - -600 - 429.1875 71.53
/home/oracle/app/oradata/pamsdb/system01.dbf - - - - - - - - - - - - - - - - - - SYSTEM - - - - - - - - - - - - - - - -700 - 578.1875 82.59
/home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_test_dyjwd77b_.dbf - - - - TEST - - - - - - - - - - - - - - - - -100 - -17.0625 17.06
/home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_test1_dyjwf0vx_.dbf - - -  TEST1 - - - - - - - - - - - - - - - - 100 - - 1.0625 1.062
/home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_test3_ms_dyjwf0z1_.dbf - - TEST3_MSSM - - - - - - - - - - - - - -100 - - - - -1 1
/home/oracle/app/oradata/pamsdb/test4_assm.dbf - - - - - - - - - - - - - - - - - TEST4_ASSM - - - - - - - - - - - - - -100 - - 1.0625 1.062
/home/oracle/app/oradata/pamsdb/test_chen.dbf - - - - - - - - - - - - - - - - -  TEST_CHEN - - - - - - - - - - - - - - 100 - -13.1875 13.18
/home/oracle/app/oradata/pamsdb/test_chen1.dbf - - - - - - - - - - - - - - - - - TEST_CHEN1 - - - - - - - - - - - - - - 50 - - 5.4375 10.87
/home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_ts_auto_dyjwdn5l_.dbf - -  TS_AUTO - - - - - - - - - - - - - - - 100 - - - - -1 1
/home/oracle/app/oradata/pamsdb/ts_mssm.dbf - - - - - - - - - - - - - - - - - -  TS_MSSM - - - - - - - - - - - - - - - -50 - - 1.0625 2.125
/home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_ts_uni_dyjwdn4f_.dbf - - - TS_UNI - - - - - - - - - - - - - - - -100 - - - - -2 2
/home/oracle/app/oradata/pamsdb/undotbs02.dbf - - - - - - - - - - - - - - - - -  UNDO2 - - - - - - - - - - - - - - - - -50 - - - 2.25 4.5
/home/oracle/app/oradata/pamsdb/undotbs01.dbf - - - - - - - - - - - - - - - - -  UNDOTBS1 - - - - - - - - - - - - - - -395 - - -15.25 3.860
/home/oracle/app/oradata/pamsdb/users01.dbf - - - - - - - - - - - - - - - - - -  USERS - - - - - - - - - - - - - - - - -30 - - 28.125 93.75
18 rows selected
--执行seq13恢复指令：
RMAN> recover tablespace 'TSPITR' until logseq 13 auxiliary destination '/backup/aux';
Starting recover at 26-OCT-17
using channel ORA_DISK_1
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Tablespace UNDO2
Creating automatic instance, with SID='mfuj'
initialization parameters used for automatic instance:
db_name=PAMSDB
db_unique_name=mfuj_tspitr_PAMSDB
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/backup/aux
log_archive_dest_1='location=/backup/aux'
#No auxiliary parameter file used
starting up automatic instance PAMSDB
Oracle instance started
Total System Global Area - -292278272 bytes
Fixed Size - - - - - - - - - -2212736 bytes
Variable Size - - - - - - - -100666496 bytes
Database Buffers - - - - - -184549376 bytes
Redo Buffers - - - - - - - - -4849664 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully
contents of Memory Script:
{
# set requested point in time
set until  logseq 13 thread 1;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log 
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 26-OCT-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=17 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/12shvgtn_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/12shvgtn_1_1 tag=TAG20171026T092702
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/backup/aux/PAMSDB/controlfile/o1_mf_dz2lo0x6_.ctl
Finished restore at 26-OCT-17
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
contents of Memory Script:
{
# set requested point in time
set until  logseq 13 thread 1;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile -1 to new;
set newname for clone datafile -3 to new;
set newname for clone datafile -8 to new;
set newname for clone datafile -2 to new;
set newname for clone tempfile -1 to new;
set newname for datafile -20 to 
"/home/oracle/app/oradata/pamsdb/tspitr01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile -1, 3, 8, 2, 20;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /backup/aux/PAMSDB/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 26-OCT-17
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /backup/aux/PAMSDB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /backup/aux/PAMSDB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /backup/aux/PAMSDB/datafile/o1_mf_undo2_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /backup/aux/PAMSDB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00020 to /home/oracle/app/oradata/pamsdb/tspitr01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/11shvgn7_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/11shvgn7_1_1 tag=TAG20171026T092702
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:26
Finished restore at 26-OCT-17
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=958387198 file name=/backup/aux/PAMSDB/datafile/o1_mf_system_dz2lo91b_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=958387198 file name=/backup/aux/PAMSDB/datafile/o1_mf_undotbs1_dz2lo930_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=8 STAMP=958387199 file name=/backup/aux/PAMSDB/datafile/o1_mf_undo2_dz2loqkb_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=958387199 file name=/backup/aux/PAMSDB/datafile/o1_mf_sysaux_dz2lo91h_.dbf
contents of Memory Script:
{
# set requested point in time
set until  logseq 13 thread 1;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  8 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  20 online";
# recover and open resetlogs
recover clone database tablespace -"TSPITR", "SYSTEM", "UNDOTBS1", "UNDO2", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile -1 online
sql statement: alter database datafile -3 online
sql statement: alter database datafile -8 online
sql statement: alter database datafile -2 online
sql statement: alter database datafile -20 online
Starting recover at 26-OCT-17
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 11 is already on disk as file /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_11_958322553.dbf
archived log for thread 1 with sequence 12 is already on disk as file /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_12_958322553.dbf
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/13shvgtr_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/13shvgtr_1_1 tag=TAG20171026T093035
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:00
archived log file name=/backup/aux/1_10_958322553.dbf thread=1 sequence=10
channel clone_default: deleting archived log(s)
archived log file name=/backup/aux/1_10_958322553.dbf RECID=46 STAMP=958387202
archived log file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_11_958322553.dbf thread=1 sequence=11
archived log file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_12_958322553.dbf thread=1 sequence=12
media recovery complete, elapsed time: 00:00:01
Finished recover at 26-OCT-17
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  "TSPITR" read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/backup/aux''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/backup/aux''";
}
executing Memory Script
sql statement: alter tablespace -"TSPITR" read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/backup/aux''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/backup/aux''
Performing export of metadata...
  EXPDP> Starting "SYS"."TSPITR_EXP_mfuj": -
  EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
  EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
  EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
  EXPDP> Master table "SYS"."TSPITR_EXP_mfuj" successfully loaded/unloaded
  EXPDP> ******************************************************************************
  EXPDP> Dump file set for SYS.TSPITR_EXP_mfuj is:
  EXPDP>  /backup/aux/tspitr_mfuj_54700.dmp
  EXPDP> ******************************************************************************
  EXPDP> Datafiles required for transportable tablespace TSPITR:
  EXPDP>  /home/oracle/app/oradata/pamsdb/tspitr01.dbf
  EXPDP> Job "SYS"."TSPITR_EXP_mfuj" successfully completed at 10:42:14
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql 'drop tablespace  "TSPITR" including contents keep datafiles';
}
executing Memory Script
database closed
database dismounted
Oracle instance shut down
sql statement: drop tablespace -"TSPITR" including contents keep datafiles
Performing import of metadata...
  IMPDP> Master table "SYS"."TSPITR_IMP_mfuj" successfully loaded/unloaded
  IMPDP> Starting "SYS"."TSPITR_IMP_mfuj": -
  IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
  IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
  IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
  IMPDP> Job "SYS"."TSPITR_IMP_mfuj" successfully completed at 10:43:41
Import completed
contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  "TSPITR" read write';
sql 'alter tablespace  "TSPITR" offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
sql statement: alter tablespace -"TSPITR" read write
sql statement: alter tablespace -"TSPITR" offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file /backup/aux/PAMSDB/datafile/o1_mf_temp_dz2lrc6m_.tmp deleted
auxiliary instance file /backup/aux/PAMSDB/onlinelog/o1_mf_3_dz2lr744_.log deleted
auxiliary instance file /backup/aux/PAMSDB/onlinelog/o1_mf_2_dz2lr5rm_.log deleted
auxiliary instance file /backup/aux/PAMSDB/onlinelog/o1_mf_1_dz2lr3y0_.log deleted
auxiliary instance file /backup/aux/PAMSDB/datafile/o1_mf_sysaux_dz2lo91h_.dbf deleted
auxiliary instance file /backup/aux/PAMSDB/datafile/o1_mf_undo2_dz2loqkb_.dbf deleted
auxiliary instance file /backup/aux/PAMSDB/datafile/o1_mf_undotbs1_dz2lo930_.dbf deleted
auxiliary instance file /backup/aux/PAMSDB/datafile/o1_mf_system_dz2lo91b_.dbf deleted
auxiliary instance file /backup/aux/PAMSDB/controlfile/o1_mf_dz2lo0x6_.ctl deleted
Finished recover at 26-OCT-17
这次成功了。
我留意到跟seq12时日志不同的地方：
archived log for thread 1 with sequence 11 is already on disk as file /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_11_958322553.dbf
archived log for thread 1 with sequence 12 is already on disk as file /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_12_958322553.dbf
下面这条是这次多的，说明第一次执行logseq 12时，是不包含seq12本身的。


回头来看恢复表数据：
SQL> conn pams/pams@pamsdb- - 
Connected.
SQL> select count(*) from tspitr_test;
select count(*) from tspitr_test
- - - - - - - - - - *
ERROR at line 1:
ORA-00376: file 20 cannot be read at this time
ORA-01110: data file 20: '/home/oracle/app/oradata/pamsdb/tspitr01.dbf'


这里报错，因为表空间和数据文件都是offline的状态，在rman的日志中能看到最后是执行了下线指令的，因为我的表空间和数据文件已经恢复，这里可以大胆的执行online操作。


SQL> conn / as sysdba
Connected.
SQL> alter tablespace TSPITR online;
Tablespace altered.


SQL> select tablespace_name,status from dba_tablespaces where tablespace_name = 'TSPITR';- - - - - - - 


TABLESPACE_NAME- - - - - - - - STATUS
------------------------------ ---------
TSPITR- - - - - - - - - - - - ONLINE


SQL> select name,status from v$datafile where name = '/home/oracle/app/oradata/pamsdb/tspitr01.dbf';


NAME
--------------------------------------------------------------------------------
STATUS
-------
/home/oracle/app/oradata/pamsdb/tspitr01.dbf
ONLINE


表空间和数据文件都已经online，再来查询恢复的数据。


SQL> conn pams/pams@pamsdb
Connected.
SQL> select count (*) from tspitr_test;


- COUNT(*)
----------
- - - - 8
数据是8条？ 之前在插入数据的时候没有commit，所以确实是8条。


--或者直接指定时间，没试验过
SQL> select to_char(sysdate,'yyyymmdd hh34:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYYMMDDHH24:')
------------------------------
20171025 17:42:01
recover tablespace 'tspitr_test' until time "to_date('2017-10-24 16:47:12','yyyy-mm-dd hh34:mi:ss')" auxiliary destination '/backup/aux'; 
*实验原始内容来自：http://lqding.blog.51cto.com/9123978/1680903


*这篇文章逻辑比较乱，主要是记录之前踩过的坑，也是由于自己对RMAN了解不多，只能摸石头过河，有篇比较清晰的问题推荐：http://blog.itpub.net/17203031/viewspace-1082080/


=============之前踩的坑


--以下为之前找不到办法把原来实验的表空间删掉，干脆不用，由于手动删除的数据文件，发现数据库open不了了。
之后恢复时，报错，不用01数据文件，重建了个02，还是不行，直接restore database；
RMAN> restore database;
Starting restore at 25-OCT-17
using channel ORA_DISK_1
creating datafile file number=21 name=/home/oracle/app/oradata/pamsdb/tspitr02.dbf
skipping datafile 20; already restored to file /home/oracle/app/oradata/pamsdb/tspitr01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/app/oradata/pamsdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oradata/pamsdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oradata/pamsdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oradata/pamsdb/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/app/oradata/pamsdb/DATA_PAMS_01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/app/oradata/pamsdb/DATA_PAMS_02.dbf
channel ORA_DISK_1: restoring datafile 00007 to /home/oracle/app/oradata/pamsdb/INDX_PAMS_01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /home/oracle/app/oradata/pamsdb/undotbs02.dbf
channel ORA_DISK_1: restoring datafile 00009 to /home/oracle/app/oradata/pamsdb/test_chen.dbf
channel ORA_DISK_1: restoring datafile 00010 to /home/oracle/app/oradata/pamsdb/test_chen1.dbf
channel ORA_DISK_1: restoring datafile 00011 to /home/oracle/app/oradata/pamsdb/ts_mssm.dbf
channel ORA_DISK_1: restoring datafile 00012 to /home/oracle/app/oradata/pamsdb/big.dbf
channel ORA_DISK_1: restoring datafile 00013 to /home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_test_dyjwd77b_.dbf
channel ORA_DISK_1: restoring datafile 00014 to /home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_ts_uni_dyjwdn4f_.dbf
channel ORA_DISK_1: restoring datafile 00015 to /home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_ts_auto_dyjwdn5l_.dbf
channel ORA_DISK_1: restoring datafile 00016 to /home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_test1_dyjwf0vx_.dbf
channel ORA_DISK_1: restoring datafile 00017 to /home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_test3_ms_dyjwf0z1_.dbf
channel ORA_DISK_1: restoring datafile 00018 to /home/oracle/app/oradata/pamsdb/test4_assm.dbf
channel ORA_DISK_1: restoring datafile 00019 to /home/oracle/app/oradata/pamsdb/blkerr01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/0jshr1lm_1_1
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/0jshr1lm_1_1 tag=TAG20171024T164542
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:36
Finished restore at 25-OCT-17
RMAN> recover database;
Starting recover at 25-OCT-17
using channel ORA_DISK_1
starting media recovery
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/25/2017 16:37:06
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 3 and starting SCN of 1766660 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2 and starting SCN of 1766284 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1 and starting SCN of 1760640 found to restore
=======================================================================
这个时候由于归档文件问题，没有recover成功，但是restore成功，之前删除了tspitr01.dbf回来了，试着将数据库从mount切换到open。
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01248: file 21 was created in the future of incomplete recovery
ORA-01110: data file 21: '/home/oracle/app/oradata/pamsdb/tspitr02.dbf'
SQL> alter database datafile '/home/oracle/app/oradata/pamsdb/tspitr02.dbf' offline drop;
Database altered.
SQL> alter database open resetlogs;
Database altered.
======================================
检查数据文件状态
SQL> select name,status from v$datafile;
NAME - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - STATUS
-------------------------------------------------------------------------------- -------
/home/oracle/app/oradata/pamsdb/system01.dbf - - - - - - - - - - - - - - - - - - SYSTEM
/home/oracle/app/oradata/pamsdb/sysaux01.dbf - - - - - - - - - - - - - - - - - - ONLINE
/home/oracle/app/oradata/pamsdb/undotbs01.dbf - - - - - - - - - - - - - - - - -  ONLINE
/home/oracle/app/oradata/pamsdb/users01.dbf - - - - - - - - - - - - - - - - - -  ONLINE
/home/oracle/app/oradata/pamsdb/DATA_PAMS_01.dbf - - - - - - - - - - - - - - - - ONLINE
/home/oracle/app/oradata/pamsdb/DATA_PAMS_02.dbf - - - - - - - - - - - - - - - - ONLINE
/home/oracle/app/oradata/pamsdb/INDX_PAMS_01.dbf - - - - - - - - - - - - - - - - ONLINE
/home/oracle/app/oradata/pamsdb/undotbs02.dbf - - - - - - - - - - - - - - - - -  ONLINE
/home/oracle/app/oradata/pamsdb/test_chen.dbf - - - - - - - - - - - - - - - - -  ONLINE
/home/oracle/app/oradata/pamsdb/test_chen1.dbf - - - - - - - - - - - - - - - - - ONLINE
/home/oracle/app/oradata/pamsdb/ts_mssm.dbf - - - - - - - - - - - - - - - - - -  ONLINE
/home/oracle/app/oradata/pamsdb/big.dbf - - - - - - - - - - - - - - - - - - - -  ONLINE
/home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_test_dyjwd77b_.dbf - - - - ONLINE
/home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_ts_uni_dyjwdn4f_.dbf - - - ONLINE
/home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_ts_auto_dyjwdn5l_.dbf - -  ONLINE
/home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_test1_dyjwf0vx_.dbf - - -  ONLINE
/home/oracle/app/oradata/pamsdb/PAMSDB/datafile/o1_mf_test3_ms_dyjwf0z1_.dbf - - ONLINE
/home/oracle/app/oradata/pamsdb/test4_assm.dbf - - - - - - - - - - - - - - - - - ONLINE
/home/oracle/app/oradata/pamsdb/blkerr01.dbf - - - - - - - - - - - - - - - - - - ONLINE
/home/oracle/app/oradata/pamsdb/tspitr01.dbf - - - - - - - - - - - - - - - - - - OFFLINE
20 rows selected
表空间也下线了
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name = 'TSPITR';
TABLESPACE_NAME - - - - - - -  STATUS
------------------------------ ---------
TSPITR - - - - - - - - - - - - OFFLINE
--以下为数据文件offline相关资料
引用一片说明表空间和数据文件offline的文章
数据文件、表空间offline用法及区别
对数据库的脱机包括数据文件的脱机和对表空间的脱机，表空间脱机实际就是表空间对应的所有数据文件脱机。
1.---------数据文件OFFLINE
数据文件添加到表空间之后不能够被删除的，没有语法支持这么做，如果想不使用该数据文件，唯一是将数据文件设置为OFFLINE状态。执行以下步骤将数据文件设置为OFFLINE状态：
1)---------如果是归档模式可以执行如下SQL设置数据文件的状态为OFFLINE：
ALTER DATABASE DATAFILE 'XXXX.DBF' OFFLINE;
2)---------如果是非归档模式执行以下SQL将数据文件状态设置为OFFLINE：
ALTER DATABASE DATAFILE 'XXXX.DBF' OFFLINE DROP;
数据文件脱机，数据文件相关的数据字典信息、元数据信息都依然存在，当表空间被删除后，相关数据文件的信息才会被清除。DROP TABLESPACE只是清空Oracle数据字典信息，即使数据文件不存在都可以正常的DROP表空间。对于数据文件的脱机，在设置该数据文件ONLINE的时候都需要对该数据文件执行介质恢复。
如果在非归档模式下使用OFFLINE DROP使数据文件脱机，这就意味着该数据文件可能无法再恢复到ONLINE状态，原因就在于在非归档模式可能没有足够的日志执行ONLINE的介质恢复。如果日志未发生切换，还依然存在的话，依然可以执行介质恢复后使数据文件ONLINE。
2.---------表空间OFFLINE
表空间脱机分为正常脱机、临时脱机和立即脱机，下面讨论这三种脱机方式。
1)---------OFFLINE NORMAL
这是默认的选项，正常情况表空间的脱机，当重新执行ONLINE时，Oracle会用相应的SCN来更新表空间数据文件头SCN即可正常的ONLINE表空间，不需要执行介质恢复。
ALTER TABLESPACE XXX OFFLINE [NORMAL];
2)---------OFFLINE TEMPORARY
如果指定TEMPORARY，Oracle数据库为表空间中所有在线数据文件执行一个检查点，但是不能确保所有文件能被同步。当执行这个语句数据文件已经脱机，那么在使表空间重新ONLINE之前需要执行介质恢复。
ALTER TABLESPACE XXX OFFLINE TEMPORARY;
3)---------OFFLINE IMMEDIATE
执行这个操作表示立即使表空间脱机，在下次使表空间ONLINE的时候必须执行介质恢复，介质恢复成功才能使表空间ONLINE：
ALTER TABLESPACE XXX OFFLINE IMMEDIATE;
对于数据文件的脱机来说，在下次ONLINE的时候一定要执行介质恢复过程，如果介质恢复成功，那么就可以成功ONLINE。对于正常的表空间脱机，下次使表空间ONLINE的时候不需要执行介质恢复。对于OFFLINE TEMPORARY的表空间，如果脱机前已经有数据文件是脱机的，那么在表空间上线前也需要执行部分数据文件的介质恢复。对于IMMEDIATE OFFLINE的表空间，在表空间上线前需要对表空间的所有数据文件执行介质恢复。脱机后的数据文件和表空间，在实例重启的时候都不会对数据文件的SCN号进行验证。


来源：-http://blog.itpub.net/23135684/viewspace-705015/