業(yè)內(nèi)新聞

UNDO表空間存儲(chǔ)著DML操作數(shù)據(jù)塊的前鏡像數(shù)據(jù)，在數(shù)據(jù)回滾，一致性讀，閃回操作，實(shí)例恢復(fù)的時(shí)候都可能用到UNDO表空間中的數(shù)據(jù)。如果在生產(chǎn)過程中丟失或破壞了UNDO表空間，可能導(dǎo)致某些事務(wù)無法回滾，數(shù)據(jù)庫(kù)無法恢復(fù)到一致性的狀態(tài)，Oracle實(shí)例可能宕機(jī)，之后實(shí)例無法正常啟動(dòng)；如果有多個(gè)UNDO表空間數(shù)據(jù)文件，丟失其中一個(gè)數(shù)據(jù)文件數(shù)據(jù)庫(kù)實(shí)例可能不會(huì)導(dǎo)致實(shí)例宕機(jī)，數(shù)據(jù)庫(kù)無法干凈的關(guān)閉（只能SHUTDOWN ABORT），數(shù)據(jù)庫(kù)實(shí)例能正常的重啟，但所有未回滾的數(shù)據(jù)塊依然無法處理，嘗試新建UNDO表空間、exp、expdp等操作都會(huì)收到ORA-604, ORA-376, and ORA-1110的報(bào)錯(cuò)，下面通過一個(gè)實(shí)際的案例討論如何處理UNDO損壞后的恢復(fù)。

客戶的某系統(tǒng)數(shù)據(jù)庫(kù)運(yùn)行在HP-UX服務(wù)器上，數(shù)據(jù)庫(kù)版本10.2.0.5，單機(jī)數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)運(yùn)行在非歸檔模式，數(shù)據(jù)庫(kù)文件存放在裸設(shè)備上。由于操作系統(tǒng)某文件系統(tǒng)空間不夠，維護(hù)工程師準(zhǔn)備擴(kuò)展該文件系統(tǒng)的大小，由于在生產(chǎn)時(shí)段的一個(gè)誤操作（誤以為不會(huì)影響系統(tǒng)運(yùn)行的一個(gè)操作）導(dǎo)致文件系統(tǒng)不能正常工作，卸載之后無法掛載，不巧的是不知道是誰之前為數(shù)據(jù)庫(kù)的UNDO表空間和TEMP表空間都添加了一個(gè)數(shù)據(jù)文件到該文件系統(tǒng)下。這個(gè)操作導(dǎo)致某些事務(wù)掛起無法回滾，數(shù)據(jù)庫(kù)不能正常關(guān)閉，但能成功重啟，重啟后事務(wù)依然存在，回滾段顯示狀態(tài)依然ONLINE，嘗試新建UNDO表空間收到ORA-604, ORA-376, and ORA-1110，也無法執(zhí)行exp、expdp操作，告警日志一直在報(bào)ORA-604, ORA-376, and ORA-1110的錯(cuò)誤。

出現(xiàn)這種情況首先我們要確定是否有事務(wù)受到影響，確定影響范圍，之后嘗試恢復(fù)丟失的數(shù)據(jù)文件（如果在歸檔模式利用備份和歸檔、在線日志恢復(fù)丟失的文件），但該客戶的數(shù)據(jù)庫(kù)運(yùn)行在非歸檔模式，無法對(duì)數(shù)據(jù)文件進(jìn)行恢復(fù)操作。備份重于一切，一切工作都要圍繞數(shù)據(jù)安全來開展！

下面是處理過程：

我們的基本思維應(yīng)該是新建一個(gè)UNDO表空間替換現(xiàn)有的UNDO表空間，UNDO表空間數(shù)據(jù)文件的丟失導(dǎo)致某些事務(wù)無法回滾，數(shù)據(jù)庫(kù)的某些數(shù)據(jù)塊不一致（可以認(rèn)為被邏輯損壞），但我們可以接受部分塊的損壞，恢復(fù)過程需要多次重啟數(shù)據(jù)庫(kù)實(shí)例。

如果你的數(shù)據(jù)庫(kù)還能干凈的關(guān)閉，但在正常情況下無法新建UNDO表空間，那么執(zhí)行以下的步驟：

I.A. THE DATABASE WAS CLEANLY SHUT DOWN
---------------------------------------

If you are ABSOLUTELY POSITIVE that the database was cleanly shutdown,
i.e., it was closed with either shutdown NORMAL or IMMEDIATE, then
the simplest solution is to offline drop the missing datafile, open the
database in restricted mode, and then drop and recreate the undo
tablespace to which the file belonged.  DO NOT follow this procedure
if the database was shut down ABORT or if it crashed.

The steps are:

1. Make sure the database was last cleanly shut down.

Check the alert.log file for this instance.  Go to the bottom of
the file and make sure the last time you shut the database down
you got the messages:

      "Shutting down instance (immediate)"

OR

"alter database close normal
      Completed: alter database close normal"

This also includes the case of a clean shutdown followed by a
failed attempt to startup the database.  In that case, Oracle will
issue error messages and shut itself down abort.  For the purposes
of this solution, though, this counts as a clean shutdown.

If that is not the case, i.e., if the last time YOU shut the database
down it was in abort mode, or the database crashed itself, it is
NOT safe to proceed.  You should follow the instructions for
case I.B below.

2. If using automatic UNDO_MANAGEMENT, comment out this entry from the parameter
file, or set it to MANUAL.

將UNDO_MANAGEMENT修改為MANUAL是因?yàn)閁NDO表空間在自動(dòng)管理模式下，如果不能成功新建回滾段（后面會(huì)DROP現(xiàn)有表空間）將導(dǎo)致數(shù)據(jù)庫(kù)實(shí)例宕機(jī)。

If using rollback segments, remove all the rollback segments in the
tablespace to which the lost datafile belongs from the ROLLBACK_SEGMENTS
parameter in the init.ora file for this instance.  If you are not sure about which rollbacks are
in that tablespace, simply comment out the whole ROLLBACK_SEGMENTS entry.

3. Mount the database in restricted mode.

SQL> STARTUP RESTRICT MOUNT
以RESTRICT模式啟動(dòng)實(shí)例是避免在處理過程中有其他客戶端連接。

4. Offline drop the lost datafile.

SQL> ALTER DATABASE DATAFILE '' OFFLINE DROP;

5. Open the database.

SQL> ALTER DATABASE OPEN

You should receive the message "Statement processed,".

If instead you get ORA-604, ORA-376, and ORA-1110, it is likely the shutdown
was not normal/immediate.  Review the rest of the options available and/or
contact Oracle Support Services.

6. Drop the undo tablespace or tablespace which contains rollback segments
to which the datafile belonged.

SQL> DROP TABLESPACE INCLUDING CONTENTS;

7. Recreate the undo tablespace.  If using rollback segments, recreate the
rollback segment tablespace and all it's rollback segments.  Remember to
bring the rollbacks online after you create them.

  SQL> CREATE TABLESPACE UNDOTBS2 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 100M;

8. Edit the parameter file setting:
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=

If using rollback segments, reinclude the rollbacks you just recreated in
the ROLLBACK_SEGMENTS parameter in the init.ora file for this instance.
As rollback segments were brought online in step #7, no need to proceed
with shutdown/startup as needed for undo tablespace.  All that is required
is:

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

如果你的數(shù)據(jù)庫(kù)不能正常關(guān)閉，只需要在重啟數(shù)據(jù)庫(kù)實(shí)例之前將下面的參數(shù)加到參數(shù)文件：
_allow_resetlogs_corruption=TRUE
_offline_rollback_segments="_SYSSMU1$"
_offline_rollback_segments="_SYSSMU2$"
_offline_rollback_segments="_SYSSMU3$"
_offline_rollback_segments="_SYSSMU4$"
_offline_rollback_segments="_SYSSMU5$"
_offline_rollback_segments="_SYSSMU6$"
_offline_rollback_segments="_SYSSMU7$"
_offline_rollback_segments="_SYSSMU8$"
_offline_rollback_segments="_SYSSMU9$"
_offline_rollback_segments="_SYSSMU10$"
_corrupted_rollback_segments="_SYSSMU1$"
_corrupted_rollback_segments="_SYSSMU2$"
_corrupted_rollback_segments="_SYSSMU3$"
_corrupted_rollback_segments="_SYSSMU4$"
_corrupted_rollback_segments="_SYSSMU5$"
_corrupted_rollback_segments="_SYSSMU6$"
_corrupted_rollback_segments="_SYSSMU7$"
_corrupted_rollback_segments="_SYSSMU8$"
_corrupted_rollback_segments="_SYSSMU9$"
_corrupted_rollback_segments="_SYSSMU10$"

rollback_segments的具體值可以從v$rollname中獲得。

處理完成后停止數(shù)據(jù)庫(kù)實(shí)例，去掉以上參數(shù)，修改好UNDO相關(guān)參數(shù)即可正常啟動(dòng)數(shù)據(jù)庫(kù)實(shí)例，之后再手動(dòng)處理TEMP表空間丟失的TEMP數(shù)據(jù)文件。

雖然數(shù)據(jù)庫(kù)實(shí)例能夠正常啟動(dòng)，也恢復(fù)了UNDO表空間的使用，但這并不代表不一致的塊已經(jīng)恢復(fù)，執(zhí)行某些查詢的時(shí)候可能會(huì)收到報(bào)錯(cuò)，數(shù)據(jù)庫(kù)完全恢復(fù)正常后應(yīng)該立即執(zhí)行一次邏輯備份+物理備份，確保數(shù)據(jù)庫(kù)的安全。