ITPub博客

首页 > 数据库 > Oracle > 对删除分区的分区表执行TSPITR

对删除分区的分区表执行TSPITR

原创 Oracle 作者:eric0435 时间:2015-04-11 14:36:36 0 删除 编辑

在有些情况下将分表区中的一些分区删除后需要恢复,但又不是对整个分区表恢复到某个时间点,这可以通过辅助数据库执行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两个分区的数据恢复回来,对主数据库进行备份这里不再赘述。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26015009/viewspace-1564304/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
系统架构师,ACOUG MEMBER,Oracle ACE,Mail:yongjing.star@gmail.com Mobile:13875984558 QQ:409898894 Blog:www.jydba.net

注册时间:2011-10-12

  • 博文量
    527
  • 访问量
    6148593