實驗記錄：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/