對刪除分割槽的分割槽表執行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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle分割槽表基礎運維-09刪除分割槽Oracle運維
- ORACLE刪除-表分割槽和資料Oracle
- oracle分割槽表和分割槽表exchangeOracle
- SQL Server表分割槽刪除詳情DSCCSQLServer
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- oracle分割槽表和非分割槽表exchangeOracle
- win10 oem分割槽怎麼刪除_win10 oem分割槽可以刪除嗎Win10
- PostgreSQL/LightDB 分割槽表之分割槽裁剪SQL
- PG的非分割槽表線上轉分割槽表
- mysql 進行表分割槽MySql
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- 移動分割槽表和分割槽索引的表空間索引
- 電腦硬碟分割槽要注意什麼，刪除硬碟分割槽的注意事項硬碟
- 非分割槽錶轉換成分割槽表
- Oracle分割槽表基礎運維-07增加分割槽(3列表分割槽)Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(2 HASH分割槽)Oracle運維
- 對oracle分割槽表的理解整理Oracle
- 【MYSQL】 分割槽表MySql
- Oracle分割槽表基礎運維-07增加分割槽(1範圍分割槽)Oracle運維
- HGDB的分割槽表實現SQL Server的分割槽檢視SQLServer
- win10分割槽好了怎麼刪除_win10分割槽完後如何刪除Win10
- 【Linux】MBR磁碟分割槽表只能有四個分割槽？Linux
- Oracle分割槽表基礎運維-04列表分割槽Oracle運維
- SQL Server大分割槽表沒有空分割槽的情況下如何擴充套件分割槽的方法SQLServer套件
- windows10分割槽無法刪除怎麼辦_win10系統磁碟刪除分割槽的方法WindowsWin10
- MySQL 分割槽表探索MySql
- 分割槽表-實戰
- Linux分割槽方案、分割槽建議Linux
- Oracle分割槽表基礎運維-05組合分割槽Oracle運維
- Oracle分割槽表基礎運維-02範圍分割槽Oracle運維
- Oracle分割槽表基礎運維-03HASH分割槽Oracle運維
- 分割槽表之自動增加分割槽（11G）
- SSD固態硬碟要分割槽嗎？SSD固態硬碟分割槽與不分割槽的效能對比硬碟
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- oracle 線上重新定義，普通表改變分割槽表，分割槽表可以更改型別、分割槽欄位等Oracle型別
- win10刪除系統保留分割槽如何操作_win10刪除系統保留分割槽怎麼處理Win10
- 增加表分割槽時，為local分割槽索引指定不同表空間的方法索引
- 非分割槽錶轉換成分割槽表以及注意事項
- Oracle分割槽表基礎運維-01分割槽表分類Oracle運維