分区表可以跨多个表空间,Oracle允许对部分分区执行TSPITR而不是所有分区。
1.对每个要执行恢复的分区在主数据库中创建一个表,分区表是sales,有28个分区,要执行TSPITR的分区是sales_1995,sales_1996，所以对这两个分区创建两个单独的表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> create table test.sales_1995 as select * from test.sales where 1=2; Table created. SQL> create table test.sales_1996 as select * from test.sales where 1=2; Table created.
对主数据库进行备份
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.删除要执行TSPITR的分区上的索引(这里是创建的本地索引)
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
----------- -------------------
425540 2015-04-10 17:38:52
在执行前记录当前时间，在对辅助数据库进行恢复时这就是恢复的目标时间点
SQL> alter index TEST.SALES_CUST_BIX modify partition SALES_1995 unusable; Index altered. SQL> alter index TEST.SALES_CUST_BIX modify partition SALES_1996 unusable; Index altered.
3.交换分区表
每一个要执行TSPITR的分区与其相关的表进行分区交换
SQL> alter table test.sales exchange partition sales_1995 with table test.sales_1995;
Table altered.
SQL> alter table test.sales exchange partition sales_1996 with table test.sales_1996;
Table altered.
SQL> select count(*) from test.sales partition(SALES_1995);
COUNT(*)
----------
0
SQL> select count(*) from test.sales partition(SALES_1996);
COUNT(*)
----------
0
SQL> select count(*) from test.sales_1995;
COUNT(*)
----------
999
SQL> select count(*) from test.sales_1996;
COUNT(*)
----------
999
5.创建辅助数据库
将辅助集和恢复集表空间的数据文件与备份的控制文件 还原到/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 15:40:51 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;
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
/u02/auxiliary/control03.ctl
12 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>
Database altered.
SQL> recover database until time '2015-04-10 17:38:52' using backup controlfile;
ORA-00279: change 425356 generated at 04/10/2015 17:31:13 needed for thread 1
ORA-00289: suggestion : /u02/backup/1_6_876665479.dbf
ORA-00280: change 425356 for thread 1 is in sequence #6
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 425511 generated at 04/10/2015 17:37:22 needed for thread 1
ORA-00289: suggestion : /u02/backup/1_7_876665479.dbf
ORA-00280: change 425511 for thread 1 is in sequence #7
ORA-00278: log file '/u02/backup/1_6_876665479.dbf' no longer needed for this recovery
Specify log: { =suggested | filename | AUTO | CANCEL}
/u02/backup/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from test.sales_1995;
COUNT(*)
----------
0
SQL> select count(*) from test.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
从上面的结果可以看到我们将辅助数据库恢复到执行交换分区之前了。
6.在辅助数据库中创建与分区进行交换相关的表,而且交换表只能创建在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.
SQL> select count(*) from test.tts_sales_1995;
COUNT(*)
----------
0
SQL> select count(*) from test.tts_sales_1996;
COUNT(*)
----------
0
7.将辅助数据库中的分区表的sales_1995,sales_1996分区上的索引删除（这里是创建的本地索引)
SQL> alter index SALES_CUST_BIX modify partition SALES_1995 unusable; Index altered. SQL> alter index SALES_CUST_BIX modify partition SALES_1996 unusable; Index altered.
8.在辅助数据库上使用表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
9.导出交换表tts_sales_1995,tts_sales_1996
SQL> create temporary tablespace test_temp tempfile '/u02/auxiliary/test_temp01.dbf' size 50M; Tablespace created. SQL> alter database default temporary tablespace test_temp; Database altered. SQL> alter tablespace test read only; Tablespace altered. [oracle@oracle11g ~]$ 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 19:10:09 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.
10.将交换表 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 19:12:01 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.
11.使用表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两个分区的数据恢复回来,最后需要对对主数据库进行备份这里不再赘述。