  UNDO表空間儲存著DML運算元據塊的前映象資料，在資料回滾，一致性讀，閃回操作，例項恢復的時候都可能用到UNDO表空間中的資料。如果在生產過程中丟失或破壞了UNDO表空間，可能導致某些事務無法回滾，資料庫無法恢復到一致性的狀態，Oracle例項可能當機，之後例項無法正常啟動；如果有多個UNDO表空間資料檔案，丟失其中一個資料檔案資料庫例項可能不會導致例項當機，資料庫無法乾淨的關閉（只能SHUTDOWN ABORT），資料庫例項能正常的重啟，但所有未回滾的資料塊依然無法處理，嘗試新建UNDO表空間、exp、expdp等操作都會收到ORA-604, ORA-376, and ORA-1110的報錯，下面透過一個實際的案例討論如何處理UNDO損壞後的恢復。

  客戶的某系統資料庫執行在HP-UX伺服器上，資料庫版本10.2.0.5，單機資料庫，資料庫執行在非歸檔模式，資料庫檔案存放在裸裝置上。由於作業系統某檔案系統空間不夠，維護工程師準備擴充套件該檔案系統的大小，由於在生產時段的一個誤操作（誤以為不會影響系統執行的一個操作）導致檔案系統不能正常工作，解除安裝之後無法掛載，不巧的是不知道是誰之前為資料庫的UNDO表空間和TEMP表空間都新增了一個資料檔案到該檔案系統下。這個操作導致某些事務掛起無法回滾，資料庫不能正常關閉，但能成功重啟，重啟後事務依然存在，回滾段顯示狀態依然ONLINE，嘗試新建UNDO表空間收到ORA-604, ORA-376, and ORA-1110，也無法執行exp、expdp操作，告警日誌一直在報ORA-604, ORA-376, and ORA-1110的錯誤。

  出現這種情況首先我們要確定是否有事務受到影響，確定影響範圍，之後嘗試恢復丟失的資料檔案（如果在歸檔模式利用備份和歸檔、線上日誌恢復丟失的檔案），但該客戶的資料庫執行在非歸檔模式，無法對資料檔案進行恢復操作。備份重於一切，一切工作都要圍繞資料安全來開展！

下面是處理過程：

  我們的基本思維應該是新建一個UNDO表空間替換現有的UNDO表空間，UNDO表空間資料檔案的丟失導致某些事務無法回滾，資料庫的某些資料塊不一致（可以認為被邏輯損壞），但我們可以接受部分塊的損壞，恢復過程需要多次重啟資料庫例項。

如果你的資料庫還能幹淨的關閉，但在正常情況下無法新建UNDO表空間，那麼執行以下的步驟：

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是因為UNDO表空間在自動管理模式下，如果不能成功新建回滾段（後面會DROP現有表空間）將導致資料庫例項當機。

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模式啟動例項是避免在處理過程中有其他客戶端連線。

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;

如果你的資料庫不能正常關閉，只需要在重啟資料庫例項之前將下面的引數加到引數檔案：
_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中獲得。

  處理完成後停止資料庫例項，去掉以上引數，修改好UNDO相關引數即可正常啟動資料庫例項，之後再手動處理TEMP表空間丟失的TEMP資料檔案。

  雖然資料庫例項能夠正常啟動，也恢復了UNDO表空間的使用，但這並不代表不一致的塊已經恢復，執行某些查詢的時候可能會收到報錯，資料庫完全恢復正常後應該立即執行一次邏輯備份+物理備份，確保資料庫的安全。

  另外操作前請仔細閱讀《RECOVERING FROM A LOST DATAFILE IN A UNDO TABLESPACE (文件 ID 1013221.6)》文章。

--end--

Oracle資料庫UNDO損壞後的恢復

相關文章