對于誤删除表空間的恢複，本文通過基于資料庫的時間點恢複和基于表空間的時間點恢複分别加以讨論
一 通過基于資料庫的時間點恢複被誤删除的表空間
1 需要注意的事項
a 基于資料庫的時間點恢複将會回退整個資料庫。
b 誤删除表空間，當資料庫有之前可用于恢複的全庫備份和相關歸檔，如果對資料庫執行不完全恢複，恢複該資料庫到删除表空間之前的狀态，便可恢複誤删除的表空間。但實際上當我們删除表空間，資料庫備份中将不存在關于該表空間的的資訊,直接進行恢複将會出現問題。如下所示：
RMAN> list backup of database;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
34 Incr 0 2.04G DISK 00:02:22 2014-02-09 19:13:39
BP Key: 34 Status: AVAILABLE Compressed: NO Tag: TAG20140209T191116
Piece Name: /backup/crm/full-20140209-14p0792l_1_1.bak
List of Datafiles in backup set 34
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/CRM/system01.dbf
2 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/CRM/sysaux01.dbf
3 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/CRM/undotbs01.dbf
4 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/CRM/users01.dbf
5 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/jxc.dbf
6 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/pos.dbf
7 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/user01.dbf
8 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/erp.dbf
9 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/undotbs03.dbf
12 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/crm.dbf
RMAN> host;
[oracle@dest bak]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 9 19:16:40 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>drop tablespace pos including contents and datafiles;
Tablespace dropped.
6 0 Incr 3892854 2014-02-09 19:11:17
是以，在恢複前應該先用上一次全備份時刻控制檔案備份恢複目前控制檔案，之後再對整個資料庫執行基于時間點的不完全恢複
2 建立測試表空間及相應的使用者
[oracle@dest bak]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 9 18:18:28 2014
SQL> select group#,archived,sequence#,status from v$log;
GROUP# ARC SEQUENCE# STATUS
---------- --- ---------- ----------------
1 NO 1 CURRENT
2 YES 0 UNUSED
3 YES 0 UNUSED
SQL> alter system switch logfile;
System altered.
SQL> /
SQL> create tablespace jxc datafile '/oracle/CRM/jxc.dbf' size 10m;
Tablespace created.
SQL> create user zx identified by dhhzdhhz default tablespace jxc;
User created.
SQL> grant connect ,resource to zx;
Grant succeeded.
SQL> exit
3 備份資料庫
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
[oracle@dest bak]$ ls
crm201402091452.log crm201402091727.log fullbk.txt rman.sh
[oracle@dest bak]$ cat rman.sh
#!/bin/bash
export today=`date +%Y%m%d%H%M`
export ORACLE_HOME=/oracle/app/db1
export ORACLE_SID=CRM
$ORACLE_HOME/bin/rman target sys/DHHZDHHZ log=/oracle/bak/crm.log cmdfile=/oracle/bak/fullbk.txt
mv /oracle/bak/crm.log "/oracle/bak/crm${today}.log"
[oracle@dest bak]$ cat fullbk.txt
run{
delete noprompt obsolete;
crosscheck backup;
delete noprompt expired backup;
crosscheck archivelog all;
delete noprompt expired archivelog all;
backup incremental level=0 database format '/backup/crm/full-%T-%U.bak';
backup archivelog all format '/backup/crm/arch-%T-%U.bak';
backup current controlfile format '/backup/crm/ctl-%T-%U.bak';
backup spfile format '/backup/crm/spf-%T-%U.bak';
delete noprompt archivelog all completed before 'SYSDATE - 7';
}
[oracle@dest bak]$ ./rman.sh
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> [oracle@dest bak]$
[oracle@dest bak]$
[oracle@dest bak]$ rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Sun Feb 9 18:32:41 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: CRM (DBID=3641885733)
RMAN> list backup of tablespace jxc;
23 Incr 0 2.04G DISK 00:02:29 2014-02-09 18:31:05
BP Key: 23 Status: AVAILABLE Compressed: NO Tag: TAG20140209T182835
Piece Name: /backup/crm/full-20140209-0np076ik_1_1.bak
List of Datafiles in backup set 23
5 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/jxc.dbf
RMAN> exit
4 在表空間上建立測試資料
[oracle@dest bak]$ sqlplus zx/dhhzdhhz
SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 9 18:33:07 2014
SQL> create table test1(i int);
Table created.
SQL> begin
2 for i in 1..20 loop
3 insert into test1 values(i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select * from zx.test1;
I
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
20 rows selected.
SQL> exit
SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 9 18:34:17 2014
SQL> alter system checkpoint;
5 記錄drop掉表空間前目前資料庫的時間點。
SQL> select current_scn,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from v$database;
CURRENT_SCN TO_CHAR(SYSDATE,'YY
----------- -------------------
3892004 2014-02-09 18:35:05
SQL>
6 drop掉表空間和相應的資料檔案
SQL> drop tablespace jxc including contents and datafiles;
7 恢複控制檔案
RMAN> list backup of controlfile;
24 Incr 0 9.67M DISK 00:00:04 2014-02-09 18:31:19
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20140209T182835
Piece Name: /backup/crm/full-20140209-0op076nj_1_1.bak
Control File Included: Ckp SCN: 3891837 Ckp time: 2014-02-09 18:31:15
34 Full 9.64M DISK 00:00:02 2014-02-09 18:31:38
BP Key: 34 Status: AVAILABLE Compressed: NO Tag: TAG20140209T183136
Piece Name: /backup/crm/ctl-20140209-12p076o8_1_1.bak
Control File Included: Ckp SCN: 3891864 Ckp time: 2014-02-09 18:31:36
1 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/CRM/system01.dbf
2 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/CRM/sysaux01.dbf
3 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/CRM/undotbs01.dbf
4 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/CRM/users01.dbf
5 0 Incr 3891775 2014-02-09 18:28:36
6 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/pos.dbf
7 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/user01.dbf
8 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/erp.dbf
9 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/undotbs03.dbf
12 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/crm.dbf
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 952020992 bytes
Fixed Size 2232208 bytes
Variable Size 633340016 bytes
Database Buffers 310378496 bytes
Redo Buffers 6070272 bytes
RMAN> restore controlfile to '/oracle/CRM/CRM/control01.ctl' from '/backup/crm/ctl-20140209-12p076o8_1_1.bak';
Starting restore at 2014-02-09 18:56:10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2014-02-09 18:56:13
Recovery Manager complete.
[oracle@dest bak]$ cd /oracle/CRM/CRM
[oracle@dest CRM]$ ls -lt
total 3325464
-rw-r----- 1 oracle oinstall 10076160 Feb 9 18:56 control01.ctl
-rw-r----- 1 oracle oinstall 10076160 Feb 9 18:53 control02.ctl
-rw-r----- 1 oracle oinstall 555753472 Feb 9 18:42 sysaux01.dbf
-rw-r----- 1 oracle oinstall 828383232 Feb 9 18:42 system01.dbf
-rw-r----- 1 oracle oinstall 115351552 Feb 9 18:42 undotbs01.dbf
-rw-r----- 1 oracle oinstall 1251745792 Feb 9 18:42 users01.dbf
-rw-r----- 1 oracle oinstall 209715712 Feb 9 18:38 redo01.log
-rw-r----- 1 oracle oinstall 209715712 Feb 9 18:35 redo03.log
-rw-r----- 1 oracle oinstall 209715712 Feb 9 18:34 redo02.log
-rw-r----- 1 oracle oinstall 30416896 Feb 9 15:00 temp01.dbf
[oracle@dest CRM]$ cp control01.ctl control02.ctl
[oracle@dest CRM]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
control02.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@dest CRM]$ rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Sun Feb 9 18:57:57 2014
connected to target database: CRM (not mounted)
RMAN> alter database mount;
database mounted
8 執行基于資料庫的時間點恢複回退整個資料庫至2014-02-09 18:35:05
RMAN> run{
2> set until time "to_date('2014-02-09 18:35:05','YYYY-MM-DD HH24:MI:SS')";
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 2014-02-09 18:59:02
skipping datafile 6; already restored to file /oracle/CRM/pos.dbf
skipping datafile 7; already restored to file /oracle/CRM/user01.dbf
skipping datafile 8; already restored to file /oracle/CRM/erp.dbf
skipping datafile 9; already restored to file /oracle/CRM/undotbs03.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 /oracle/CRM/CRM/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oracle/CRM/CRM/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oracle/CRM/CRM/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oracle/CRM/CRM/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /oracle/CRM/jxc.dbf
channel ORA_DISK_1: restoring datafile 00012 to /oracle/CRM/crm.dbf
channel ORA_DISK_1: reading from backup piece /backup/crm/full-20140209-0np076ik_1_1.bak
channel ORA_DISK_1: piece handle=/backup/crm/full-20140209-0np076ik_1_1.bak tag=TAG20140209T182835
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:28
Finished restore at 2014-02-09 19:01:32
Starting recover at 2014-02-09 19:01:33
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /oracle/archivelog/arch_1_4_839094460.arch
archived log for thread 1 with sequence 5 is already on disk as file /oracle/CRM/CRM/redo02.log
archived log for thread 1 with sequence 6 is already on disk as file /oracle/CRM/CRM/redo03.log
archived log file name=/oracle/archivelog/arch_1_4_839094460.arch thread=1 sequence=4
archived log file name=/oracle/CRM/CRM/redo02.log thread=1 sequence=5
archived log file name=/oracle/CRM/CRM/redo03.log thread=1 sequence=6
media recovery complete, elapsed time: 00:00:04
Finished recover at 2014-02-09 19:01:46
RMAN> report schema;
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name CRM
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 790 SYSTEM *** /oracle/CRM/CRM/system01.dbf
2 530 SYSAUX *** /oracle/CRM/CRM/sysaux01.dbf
3 110 UNDOTBS1 *** /oracle/CRM/CRM/undotbs01.dbf
4 1193 USERS *** /oracle/CRM/CRM/users01.dbf
5 10 JXC *** /oracle/CRM/jxc.dbf
6 100 POS *** /oracle/CRM/pos.dbf
7 5 USER01 *** /oracle/CRM/user01.dbf
8 100 ERP *** /oracle/CRM/erp.dbf
9 100 UNDOTBS3 *** /oracle/CRM/undotbs03.dbf
12 10 CRM *** /oracle/CRM/crm.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 29 /oracle/CRM/CRM/temp01.dbf
9 以resetlogs方式打開資料庫
[oracle@dest CRM]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 9 19:02:10 2014
SQL> alter database open resetlogs;
Database altered.
10 驗證資料是否恢複
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
JXC ONLINE
POS ONLINE
USER01 ONLINE
ERP ONLINE
UNDOTBS3 ONLINE
CRM ONLINE
11 rows selected.
SQL> col file_name for a50
SQL> set linesize 400
SQL> select file_name,tablespace_name,status from dba_data_files;
FILE_NAME TABLESPACE_NAME STATUS
---------------------------------- ------------------------------ ---------
/oracle/CRM/crm.dbf CRM AVAILABLE
/oracle/CRM/undotbs03.dbf UNDOTBS3 AVAILABLE
/oracle/CRM/erp.dbf ERP AVAILABLE
/oracle/CRM/user01.dbf USER01 AVAILABLE
/oracle/CRM/pos.dbf POS AVAILABLE
/oracle/CRM/CRM/users01.dbf USERS AVAILABLE
/oracle/CRM/CRM/undotbs01.dbf UNDOTBS1 AVAILABLE
/oracle/CRM/CRM/sysaux01.dbf SYSAUX AVAILABLE
/oracle/CRM/CRM/system01.dbf SYSTEM AVAILABLE
/oracle/CRM/jxc.dbf JXC AVAILABLE
10 rows selected.
二 通過基于表空間的時間點恢複被誤删除的表空間
1 建立測試用的表jxc表空間及使用者
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/CRM/CRM/system01.dbf
/oracle/CRM/CRM/sysaux01.dbf
/oracle/CRM/CRM/undotbs01.dbf
/oracle/CRM/CRM/users01.dbf
/oracle/CRM/user01.dbf
/oracle/CRM/erp.dbf
/oracle/CRM/undotbs03.dbf
/oracle/CRM/crm.dbf
8 rows selected.
SQL> create user zx identified by dhhzdhhz default tablespace jxc;
SQL> grant resource,connect to zx;
[oracle@dest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 10 02:05:25 2014
2 對整個資料庫做一次全備份
[oracle@dest ~]$ rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Mon Feb 10 02:07:05 2014
specification does not match any backup in the repository
2> delete noprompt obsolete;
3> crosscheck backup;
4> delete noprompt expired backup;
5> crosscheck archivelog all;
6> delete noprompt expired archivelog all;
7> backup incremental level=0 database format '/backup/crm/full-%T-%U.bak';
8> backup archivelog all format '/backup/crm/arch-%T-%U.bak';
9> backup current controlfile format '/backup/crm/ctl-%T-%U.bak';
10> backup spfile format '/backup/crm/spf-%T-%U.bak';
11> delete noprompt archivelog all completed before 'SYSDATE - 7';
12> }
.................備份過程省略.........................................
3 在jxc表空間上建立測試資料
[oracle@dest ~]$ sqlplus zx/dhhzdhhz
SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 10 02:12:17 2014
SQL> create table test1(i int);
SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 10 02:13:35 2014
4 查詢目前資料庫的時間
3905661 2014-02-10 02:14:23
5 删除jxc表空間
6 執行全自動rman管理的表空間時間點恢複
Recovery Manager: Release 11.2.0.2.0 - Production on Mon Feb 10 02:17:40 2014
RMAN> recover tablespace jxc until time "to_date('2014-02-10 02:14:23','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/backup';
Starting recover at 2014-02-10 02:20:12
channel ORA_DISK_1: SID=72 device type=DISK
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 UNDOTBS3
Creating automatic instance, with SID='DctA'
initialization parameters used for automatic instance:
db_name=CRM
db_unique_name=DctA_tspitr_CRM
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/backup
log_archive_dest_1='location=/backup'
#No auxiliary parameter file used
starting up automatic instance CRM
Total System Global Area 292278272 bytes
Fixed Size 2225872 bytes
Variable Size 100665648 bytes
Database Buffers 184549376 bytes
Redo Buffers 4837376 bytes
Automatic instance created
List of tablespaces that have been dropped from the target database:
Tablespace jxc
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2014-02-10 02:14:23','yyyy-mm-dd hh24:mi:ss')";
# 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
Starting restore at 2014-02-10 02:20:39
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=81 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 /backup/crm/ctl-20140210-1up081kd_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/backup/crm/ctl-20140210-1up081kd_1_1.bak tag=TAG20140210T021020
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/CRM/controlfile/o1_mf_9hhkqsc7_.ctl
Finished restore at 2014-02-10 02:20:42
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
# 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 9 to new;
set newname for clone datafile 2 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 5 to
"/oracle/CRM/jxc.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 9, 2, 5;
switch clone datafile all;
executing command: SET NEWNAME
renamed tempfile 1 to /backup/CRM/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2014-02-10 02:20:49
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /backup/CRM/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /backup/CRM/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00009 to /backup/CRM/datafile/o1_mf_undotbs3_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /backup/CRM/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /oracle/CRM/jxc.dbf
channel ORA_AUX_DISK_1: reading from backup piece /backup/crm/full-20140210-1ip081fb_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/backup/crm/full-20140210-1ip081fb_1_1.bak tag=TAG20140210T020739
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:16
Finished restore at 2014-02-10 02:22:05
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=839125325 file name=/backup/CRM/datafile/o1_mf_system_9hhkr221_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=839125325 file name=/backup/CRM/datafile/o1_mf_undotbs1_9hhkr231_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=7 STAMP=839125325 file name=/backup/CRM/datafile/o1_mf_undotbs3_9hhkr24l_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=839125325 file name=/backup/CRM/datafile/o1_mf_sysaux_9hhkr22c_.dbf
# 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 9 online";
sql clone "alter database datafile 2 online";
sql clone "alter database datafile 5 online";
# recover and open resetlogs
recover clone database tablespace "JXC", "SYSTEM", "UNDOTBS1", "UNDOTBS3", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
sql statement: alter database datafile 1 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 5 online
Starting recover at 2014-02-10 02:22:07
archived log for thread 1 with sequence 9 is already on disk as file /oracle/archivelog/arch_1_9_839098938.arch
archived log for thread 1 with sequence 10 is already on disk as file /oracle/archivelog/arch_1_10_839098938.arch
archived log for thread 1 with sequence 11 is already on disk as file /oracle/archivelog/arch_1_11_839098938.arch
archived log for thread 1 with sequence 12 is already on disk as file /oracle/archivelog/arch_1_12_839098938.arch
archived log for thread 1 with sequence 13 is already on disk as file /oracle/archivelog/arch_1_13_839098938.arch
archived log file name=/oracle/archivelog/arch_1_9_839098938.arch thread=1 sequence=9
archived log file name=/oracle/archivelog/arch_1_10_839098938.arch thread=1 sequence=10
archived log file name=/oracle/archivelog/arch_1_11_839098938.arch thread=1 sequence=11
archived log file name=/oracle/archivelog/arch_1_12_839098938.arch thread=1 sequence=12
archived log file name=/oracle/archivelog/arch_1_13_839098938.arch thread=1 sequence=13
media recovery complete, elapsed time: 00:00:02
Finished recover at 2014-02-10 02:22:15
database opened
# make read only the tablespace that will be exported
sql clone 'alter tablespace JXC read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/backup''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
sql statement: alter tablespace JXC read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/backup''
Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_DctA":
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_DctA" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_DctA is:
EXPDP> /backup/tspitr_DctA_28726.dmp
EXPDP> Datafiles required for transportable tablespace JXC:
EXPDP> /oracle/CRM/jxc.dbf
EXPDP> Job "SYS"."TSPITR_EXP_DctA" successfully completed at 02:25:39
Export completed
# shutdown clone before import
shutdown clone immediate
database closed
database dismounted
Oracle instance shut down
Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_DctA" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_DctA":
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_DctA" successfully completed at 02:26:50
Import completed
# make read write and offline the imported tablespaces
sql 'alter tablespace JXC read write';
sql 'alter tablespace JXC offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
sql statement: alter tablespace JXC read write
sql statement: alter tablespace JXC offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file /backup/CRM/datafile/o1_mf_temp_9hhkvg7w_.tmp deleted
auxiliary instance file /backup/CRM/onlinelog/o1_mf_3_9hhkv3mp_.log deleted
auxiliary instance file /backup/CRM/onlinelog/o1_mf_2_9hhktyoc_.log deleted
auxiliary instance file /backup/CRM/onlinelog/o1_mf_1_9hhktqyl_.log deleted
auxiliary instance file /backup/CRM/datafile/o1_mf_sysaux_9hhkr22c_.dbf deleted
auxiliary instance file /backup/CRM/datafile/o1_mf_undotbs3_9hhkr24l_.dbf deleted
auxiliary instance file /backup/CRM/datafile/o1_mf_undotbs1_9hhkr231_.dbf deleted
auxiliary instance file /backup/CRM/datafile/o1_mf_system_9hhkr221_.dbf deleted
auxiliary instance file /backup/CRM/controlfile/o1_mf_9hhkqsc7_.ctl deleted
Finished recover at 2014-02-10 02:27:09
7 online jxc測試表空間
SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 10 02:27:55 2014
JXC OFFLINE
SQL> alter tablespace jxc online;
Tablespace altered.
8 驗證測試資料是否恢複
注意：
1 通過執行基于表空間的時間點恢複，僅僅是回退了要需要恢複的表空 間，這和基于資料庫的時間點恢複，回退整個資料庫是有差別的。
2 在10g上通過基于表空間的時間點恢複誤删除的表空間會報如下錯誤：
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-03002: failure of recover command at 02/10/2014 22:25:15
RMAN-20202: tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "JXC"
本文轉自 zhangxuwl 51CTO部落格，原文連結：http://blog.51cto.com/jiujian/1358361，如需轉載請自行聯系原作者
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)