對分割槽表的部分分割槽執行TSPITR
分割槽表可以跨多個表空間,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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 對刪除分割槽的分割槽表執行TSPITR
- 使用split對分割槽表再分割槽
- 範圍分割槽表和INTERVAL分割槽表對於SPLIT分割槽的區別
- 分割槽表、分割槽索引和全域性索引部分總結索引
- 如何查詢分割槽表的分割槽及子分割槽
- 使用expdp匯出分割槽表中的部分分割槽資料
- PLSQL根據分割槽表的分割槽名批次truncate分割槽SQL
- oracle分割槽表和分割槽表exchangeOracle
- rebuild分割槽表分割槽索引的方法Rebuild索引
- 【學習筆記】分割槽表和分割槽索引——概念部分（一）筆記索引
- oracle分割槽表執行計劃Oracle
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引
- Oracle分割槽表及分割槽索引Oracle索引
- INTERVAL分割槽表鎖分割槽操作
- Oracle分割槽表增加分割槽報錯“ORA-14760:不允許對間隔分割槽物件執行 ADD PARTITION”Oracle物件
- oracle分割槽表和非分割槽表exchangeOracle
- 全面學習分割槽表及分割槽索引(9)--刪除表分割槽索引
- 全面學習分割槽表及分割槽索引(11)--合併表分割槽索引
- 全面學習分割槽表及分割槽索引(12)--修改list表分割槽索引
- 學習筆記】分割槽表和分割槽索引——新增表分割槽（二）筆記索引
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- 全面學習分割槽表及分割槽索引(10)--交換分割槽索引
- Oracle帶區域性分割槽索引的分割槽表刪除舊分割槽新增新分割槽Oracle索引
- 【學習筆記】分割槽表和分割槽索引——分割槽表的其他管理（三）筆記索引
- 簡單ORACLE分割槽表、分割槽索引Oracle索引
- 分割槽表及分割槽索引建立示例索引
- 分割槽表入無分割槽的資料庫資料庫
- mysql 進行表分割槽MySql
- 分割槽 執行計劃
- 全面學習分割槽表及分割槽索引(8)--增加和收縮表分割槽索引
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- PostgreSQL/LightDB 分割槽表之分割槽裁剪SQL
- 非分割槽錶轉換成分割槽表
- 分割槽表分割槽索引查詢效率探究索引
- 【學習筆記】分割槽表和分割槽索引——管理索引分割槽（四）筆記索引
- 對oracle分割槽表的理解整理Oracle
- Oracle分割槽表基礎運維-07增加分割槽(3列表分割槽)Oracle運維
- 全面學習分割槽表及分割槽索引(15)--修改表分割槽屬性和模板索引