在有些情况下将分表区中的一些分区删除后需要恢复，但又不是对整个分区表恢复到某个时间点，这可以通过辅助数据库执行TSPITR来实现，下面的例子介绍了使用这种方法的具体操作过程。
1.对每个要执行恢复的分区在主数据库中创建一个表,分区表是sales,有28个分区,删除分区sales_1995,sales_1996之后对分区表sales执行TSPITR来恢复分区sales_1995,sales_1996
SQL> select a.owner,a.table_name,a.partitioning_type,a.subpartitioning_type,a.partition_count,a.def_tablespace_name from dba_part_tables a where a.owner='TEST' and a.table_name='SALES'; OWNER TABLE_NAME PARTITIONING_TYPE SUBPARTITIONING_TYPE PARTITION_COUNT DEF_TABLESPACE_NAME ------------------------------ ------------------------------ ----------------- -------------------- --------------- ------------------------------ TEST SALES RANGE NONE 28 TEST SQL> select a.table_owner,a.table_name,a.partition_name from dba_tab_partitions a where a.table_owner='TEST' and a.table_name='SALES'; TABLE_OWNER TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ ------------------------------ TEST SALES SALES_1995 TEST SALES SALES_1996 TEST SALES SALES_H1_1997 TEST SALES SALES_H2_1997 TEST SALES SALES_Q1_1998 TEST SALES SALES_Q2_1998 TEST SALES SALES_Q3_1998 TEST SALES SALES_Q4_1998 TEST SALES SALES_Q1_1999 TEST SALES SALES_Q2_1999 TEST SALES SALES_Q3_1999 TEST SALES SALES_Q4_1999 TEST SALES SALES_Q1_2000 TEST SALES SALES_Q2_2000 TEST SALES SALES_Q3_2000 TEST SALES SALES_Q4_2000 TEST SALES SALES_Q1_2001 TEST SALES SALES_Q2_2001 TEST SALES SALES_Q3_2001 TEST SALES SALES_Q4_2001 TEST SALES SALES_Q1_2002 TEST SALES SALES_Q2_2002 TEST SALES SALES_Q3_2002 TEST SALES SALES_Q4_2002 TEST SALES SALES_Q1_2003 TEST SALES SALES_Q2_2003 TEST SALES SALES_Q3_2003 TEST SALES SALES_Q4_2003
对主数据库进行备份
SQL> alter database begin backup; Database altered. [oracle@oracle11g backup]$ cp /u01/app/oracle/oradata/test/*.dbf /u02/backup/ SQL> alter database end backup; Database altered. SQL> alter database backup controlfile to '/u02/backup/control.ctl'; Database altered. SQL> alter system switch logfile; System altered.
2.删除分区表sales上的sales_1995,sales_1996
SQL> select current_scn,to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss') from v$database;
CURRENT_SCN TO_CHAR(SCN_TO_TIME
----------- -------------------
430881 2015-04-10 20:56:59
在执行前记录当前时间，在对辅助数据库进行恢复时这就是恢复的目标时间点
SQL> alter table test.sales drop partition sales_1995;
Table altered.
SQL> alter table test.sales drop partition sales_1996;
Table altered.
SQL> select count(*) from test.sales partition(SALES_1995);
select count(*) from test.sales partition(SALES_1995)
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
SQL> select count(*) from test.sales partition(SALES_1996);
select count(*) from test.sales partition(SALES_1996)
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
SQL> select count(*) from test.sales where time_id
3.创建辅助数据库 将辅助集和恢复集表空间的数据文件与备份的控制文件 还原到/u02/auxiliary目录中
[oracle@oracle11g backup]$ cp system01.dbf /u02/auxiliary/ [oracle@oracle11g backup]$ cp sysaux01.dbf /u02/auxiliary/ [oracle@oracle11g backup]$ cp undotbs01.dbf /u02/auxiliary/ [oracle@oracle11g backup]$ cp test01.dbf /u02/auxiliary/ [oracle@oracle11g backup]$ cp temp01.dbf /u02/auxiliary/ [oracle@oracle11g backup]$ cp control.ctl /u02/auxiliary/control01.ctl [oracle@oracle11g backup]$ cp control.ctl /u02/auxiliary/control02.ctl [oracle@oracle11g backup]$ cp control.ctl /u02/auxiliary/control03.ctl [oracle@oracle11g backup]$ cp /u02/archive/* /u02/backup/ [oracle@oracle11g backup]$ cp /u01/app/oracle/oradata/test/*.log /u02/backup/
创建初始化参数文件
[oracle@oracle11g auxiliary]$ vi initauxiliary.ora
db_name=test
db_unique_name=auxiliary
sga_max_size=160M
sga_target=160M
pga_aggregate_target=16M
db_file_name_convert=('/u01/app/oracle/oradata/test/','/u02/auxiliary/')
log_file_name_convert=('/u01/app/oracle/oradata/test/','/u02/auxiliary/')
control_files=('/u02/auxiliary/control01.ctl','/u02/auxiliary/control02.ctl','/u02/auxiliary/control03.ctl')
log_archive_dest_1='location=/u02/backup'
log_archive_format='%t_%s_%r.dbf'
compatible=10.2.0.5.0
还原和恢复辅助实例
[oracle@oracle11g ~]$ export ORACLE_SID=auxiliary
[oracle@oracle11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Apr 10 21:05:42 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u02/auxiliary/initauxiliary.ora'
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1272624 bytes
Variable Size 58721488 bytes
Database Buffers 104857600 bytes
Redo Buffers 2920448 bytes
SQL> alter database mount clone database;
Database altered.
SQL> SELECT NAME FROM V$DATAFILE
2 UNION ALL
3 SELECT MEMBER FROM V$LOGFILE
4 UNION ALL
5 SELECT NAME FROM V$CONTROLFILE
6 union all
7 select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u02/auxiliary/system01.dbf
/u02/auxiliary/undotbs01.dbf
/u02/auxiliary/sysaux01.dbf
/u02/auxiliary/users01.dbf
/u02/auxiliary/example01.dbf
/u02/auxiliary/test01.dbf
/u02/auxiliary/redo03.log
/u02/auxiliary/redo02.log
/u02/auxiliary/redo01.log
/u02/auxiliary/control01.ctl
/u02/auxiliary/control02.ctl
NAME
--------------------------------------------------------------------------------
/u02/auxiliary/control03.ctl
/u02/auxiliary/temp01.dbf
13 rows selected.
SQL> alter database datafile '/u02/auxiliary/system01.dbf' online;
Database altered.
SQL> alter database datafile '/u02/auxiliary/undotbs01.dbf' online;
Database altered.
SQL> alter database datafile '/u02/auxiliary/test01.dbf' online;
Database altered.
SQL> alter database datafile '/u02/auxiliary/sysaux01.dbf' online;
Database altered.
SQL> alter database tempfile '/u02/auxiliary/temp01.dbf' online;
Database altered.
SQL> recover database until time '2015-04-10 20:56:59' using backup controlfile;
ORA-00279: change 430764 generated at 04/10/2015 20:52:21 needed for thread 1
ORA-00289: suggestion : /u02/backup/1_7_876665479.dbf
ORA-00280: change 430764 for thread 1 is in sequence #7
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 430825 generated at 04/10/2015 20:54:32 needed for thread 1
ORA-00289: suggestion : /u02/backup/1_8_876665479.dbf
ORA-00280: change 430825 for thread 1 is in sequence #8
ORA-00278: log file '/u02/backup/1_7_876665479.dbf' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/backup/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from test.sales partition(SALES_1995);
COUNT(*)
----------
999
SQL> select count(*) from test.sales partition(SALES_1996);
COUNT(*)
----------
999
从上面的结果可以看到我们将辅助数据库恢复到删除分区之前的时间点了。
4.在辅助数据库中创建与分区进行交换相关的表,而且交换表只能创建在SYSTEM表空间中，并且与分区表有完全相同的列名和数据类型。如果交换表没有创建在SYSTEM表空间中会出现ORA-01552错误。
SQL> create table test.tts_sales_1995 tablespace system as select * from test.sales partition(SALES_1995) where 1=2; Table created. SQL> create table test.tts_sales_1996 tablespace system as select * from test.sales partition(SALES_1996) where 1=2; Table created.
5.在辅助数据库上使用表tts_sales_1995,tts_sales_1996与分区sales_1995,sales_1996进行交换
SQL> alter table test.sales exchange partition sales_1995 with table test.tts_sales_1995;
Table altered.
SQL> alter table test.sales exchange partition sales_1996 with table test.tts_sales_1996;
Table altered.
SQL> select count(*) from test.tts_sales_1995;
COUNT(*)
----------
999
SQL> select count(*) from test.tts_sales_1996;
COUNT(*)
----------
999
SQL> select count(*) from test.sales partition(SALES_1995);
COUNT(*)
----------
0
SQL> select count(*) from test.sales partition(SALES_1996);
COUNT(*)
----------
0
6.导出交换表tts_sales_1995,tts_sales_1996
[oracle@oracle11g auxiliary]$ export ORACLE_SID=auxiliary [oracle@oracle11g auxiliary]$ exp \'test/test\' file=/u02/sales.dmp log=/u02/sales.log tables=\(tts_sales_1995,tts_sales_1996\) Export: Release 10.2.0.5.0 - Production on Fri Apr 10 21:16:32 2015 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set About to export specified tables via Conventional Path ... . . exporting table TTS_SALES_1995 999 rows exported . . exporting table TTS_SALES_1996 999 rows exported Export terminated successfully without warnings.
7.将交换表 tts_sales_1995,tts_sales_1996导入到主数据库中
[oracle@oracle11g auxiliary]$ export ORACLE_SID=test [oracle@oracle11g auxiliary]$ imp \'test/test\' file=/u02/sales.dmp log=/u02/sales_dr.log fromuser=test touser=test Import: Release 10.2.0.5.0 - Production on Fri Apr 10 21:17:20 2015 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing TEST's objects into TEST . . importing table "TTS_SALES_1995" 999 rows imported . . importing table "TTS_SALES_1996" 999 rows imported Import terminated successfully without warnings.
8.对主数据库中的分区表sales增加两个分区sales_1995,sales_1996
SQL> alter table test.sales add partition sales_1995 values less than(TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
alter table test.sales add partition sales_1995 values less than(TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
SQL> alter table test.sales split partition sales_h1_1997 at(TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
2 into (partition sales_1995,partition sales_h1_1997);
Table altered.
SQL> alter table test.sales split partition sales_h1_1997 at(TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
2 into (partition sales_1996,partition sales_h1_1997);
Table altered.
9.使用表tts_sales_1995,tts_sales_1996与主数据库中的表sales的分区sales_1995,sales_1996进行交换
SQL> alter table test.sales exchange partition sales_1995 with table test.tts_sales_1995;
Table altered.
SQL> alter table test.sales exchange partition sales_1996 with table test.tts_sales_1996;
Table altered.
SQL> select count(*) from test.tts_sales_1995;
COUNT(*)
----------
0
SQL> select count(*) from test.tts_sales_1996;
COUNT(*)
----------
0
SQL> select count(*) from test.sales partition(SALES_1995);
COUNT(*)
----------
999
SQL> select count(*) from test.sales partition(SALES_1996);
COUNT(*)
----------
999
从上面的结果可以看到，表sales的sales_1995,sales_1996两个分区的数据恢复回来,对主数据库进行备份这里不再赘述。