ITPub博客

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

对分区表的部分分区执行TSPITR

原创 Oracle 作者:eric0435 时间:2015-04-10 19:26:07 0 删除 编辑

分区表可以跨多个表空间,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两个分区的数据恢复回来,最后需要对对主数据库进行备份这里不再赘述。

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

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

注册时间:2011-10-12

  • 博文量
    527
  • 访问量
    6149234