對刪除分割槽的分割槽表執行TSPITR
在有些情況下將分表區中的一些分割槽刪除後需要恢復，但又不是對整個分割槽表恢復到某個時間點，這可以透過輔助資料庫執行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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 對分割槽表的部分分割槽執行TSPITR
- 全面學習分割槽表及分割槽索引(9)--刪除表分割槽索引
- 【實驗】【PARTITION】RANGE分割槽表刪除分割槽
- Oracle帶區域性分割槽索引的分割槽表刪除舊分割槽新增新分割槽Oracle索引
- Oracle分割槽表基礎運維-09刪除分割槽Oracle運維
- 全面學習分割槽表及分割槽索引(16)--增加和刪除索引分割槽索引
- 使用split對分割槽表再分割槽
- 範圍分割槽表和INTERVAL分割槽表對於SPLIT分割槽的區別
- 刪除LINUX分割槽Linux
- Linux 分割槽刪除Linux
- swap分割槽新增刪除
- ORACLE刪除-表分割槽和資料Oracle
- 如何查詢分割槽表的分割槽及子分割槽
- 資料表分割槽分割與刪除歷史資料
- PLSQL根據分割槽表的分割槽名批次truncate分割槽SQL
- oracle分割槽表和分割槽表exchangeOracle
- rebuild分割槽表分割槽索引的方法Rebuild索引
- SQL Server表分割槽刪除詳情DSCCSQLServer
- mysql 動態新建以及刪除分割槽表MySql
- ORACLE 刪除無用分割槽Oracle
- oracle分割槽表執行計劃Oracle
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引
- Oracle分割槽表及分割槽索引Oracle索引
- INTERVAL分割槽表鎖分割槽操作
- Oracle分割槽表增加分割槽報錯“ORA-14760:不允許對間隔分割槽物件執行 ADD PARTITION”Oracle物件
- oracle分割槽表和非分割槽表exchangeOracle
- 全面學習分割槽表及分割槽索引(11)--合併表分割槽索引
- 全面學習分割槽表及分割槽索引(12)--修改list表分割槽索引
- 學習筆記】分割槽表和分割槽索引——新增表分割槽（二）筆記索引
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- 全面學習分割槽表及分割槽索引(10)--交換分割槽索引
- 【學習筆記】分割槽表和分割槽索引——分割槽表的其他管理（三）筆記索引
- 簡單ORACLE分割槽表、分割槽索引Oracle索引
- 分割槽表及分割槽索引建立示例索引
- win10 oem分割槽怎麼刪除_win10 oem分割槽可以刪除嗎Win10
- win10分割槽好了怎麼刪除_win10分割槽完後如何刪除Win10
- delete_partition.pl 刪除分割槽delete
- 分割槽表入無分割槽的資料庫資料庫