Oracle數據庫執行過程的問題怎么修復

161次閱讀

沒有評論

共計 7100 個字符，預計需要花費 18 分鐘才能閱讀完成。

本篇內容介紹了“Oracle 數據庫執行過程的問題怎么修復”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

運行過程中故障

在運行過程中的 oracle 故障，壞塊和文件異常刪除出現的比較多，特別是初級 DBA 剛剛上手的時候。我們先來模擬一下這個場景。

Undo 表空間是 Oracle 核心表空間之一，刪除之后會引起比較嚴重的問題故障。

SQL select file_name from dba_data_files where tablespace_name= UNDOTBS1

FILE_NAME

——————————————————————————–

/u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf

當前數據庫處在 Open 運行狀態，突然 Undo 文件被后 OS 層面刪除。

[oracle@bspdev datafile]$ ls -l | grep undo

-rw-r—– 1 oracle oinstall 346038272 Sep 6 07:21 o1_mf_undotbs1_7xt3yzl5_.dbf

[oracle@bspdev datafile]$ mv o1_mf_undotbs1_7xt3yzl5_.dbf o1_mf_undotbs1_7xt3yzl5_.dbf.bak

[oracle@bspdev datafile]$ ls -l | grep undo

-rw-r—– 1 oracle oinstall 346038272 Sep 6 07:21 o1_mf_undotbs1_7xt3yzl5_.dbf.bak

此時，alert log 中可以出現上篇中那個“checker”的工作過程。

Fri Sep 06 07:25:47 2013

Checker run found 1 new persistent data failures

Fri Sep 06 07:26:34 2013

Starting background process SMCO

Fri Sep 06 07:26:34 2013

SMCO started with pid=19, OS id=4819

Fri Sep 06 07:26:46 2013

Errors in file /u01/diag/rdbms/wilson/wilson/trace/wilson_mmnl_4418.trc:

ORA-01116: error in opening database file 3

ORA-01110: data file 3: /u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Fri Sep 06 07:26:48 2013

Errors in file /u01/diag/rdbms/wilson/wilson/trace/wilson_m000_4835.trc:

ORA-01116: error in opening database file 3

ORA-01110: data file 3: /u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

差不多兩秒鐘報一個錯誤，發現文件被刪除無法打開。

此時，我們在 rman 上使用 list failure 命令，查看生成的錯誤信息。

RMAN list failure all;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

———- ——– ——— ————- ——-

242 HIGH OPEN 06-SEP-13 One or more non-system datafiles are missing

我們使用 advisor failure，查看一個 Oracle 的建議。

RMAN advise failure ;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

———- ——– ——— ————- ——-

242 HIGH OPEN 06-SEP-13 One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=30 device type=DISK

analyzing automatic repair options complete

Mandatory Manual Actions

========================

1. If file /u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf was unintentionally renamed or moved, restore it

2. Automatic repairs may be available if you shutdown the database and restart it in mount mode

3. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair

Optional Manual Actions

=======================

no manual actions available

Automated Repair Options

========================

no automatic repair options available

注意，在 automated repair options 中，我們沒有看到腳本信息。說明 Oracle 好像在目前也沒有太好的方法。在 Manual Actions 中，Oracle DRA 要求將數據庫重啟到 mount 狀態，才能有自動腳本的出現。Manual Actions 是那些 Oracle 覺得需要用戶手工執行才能繼續下去的步驟。

重新啟動一下庫，加載到 mount 狀態。

– 強制關閉

RMAN shutdown abort;

Oracle instance shut down

RMAN startup mount;

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 616566352 bytes

Database Buffers 226492416 bytes

Redo Buffers 5132288 bytes

此時再次使用 DRA 工具，看問題和提示內容。

RMAN advise failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

———- ——– ——— ————- ——-

242 HIGH OPEN 06-SEP-13 One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If file /u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf was unintentionally renamed or moved, restore it

Automated Repair Options

========================

Option Repair Description

—— ——————

1 Restore and recover datafile 3

Strategy: The repair includes complete media recovery with no data loss

Repair script. /u01/diag/rdbms/wilson/wilson/hm/reco_1850469943.hm

使用 repair failure review 命令來查看執行語句。

RMAN repair failure preview;

Strategy: The repair includes complete media recovery with no data loss

Repair script. /u01/diag/rdbms/wilson/wilson/hm/reco_1850469943.hm

contents of repair script.:

# restore and recover datafile

restore datafile 3;

recover datafile 3;

注意：此時 Oracle DRA 發現了當前我們有 Undo 的備份和歸檔日志。所以使用 restore 之后伴隨 recover，可以快速實現恢復。

如果在 preview 中沒有發現什么問題，可以 repair failure 命令執行進行恢復。

RMAN repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script. /u01/diag/rdbms/wilson/wilson/hm/reco_1850469943.hm

contents of repair script.:

# restore and recover datafile

restore datafile 3;

recover datafile 3;

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

Starting restore at 06-SEP-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf

channel ORA_DISK_1: reading from backup piece /u01/flash_recovery_area/WILSON/backupset/2013_09_06/o1_mf_nnndf_TAG20130906T061608_92l0od6w_.bkp

channel ORA_DISK_1: piece handle=/u01/flash_recovery_area/WILSON/backupset/2013_09_06/o1_mf_nnndf_TAG20130906T061608_92l0od6w_.bkp tag=TAG20130906T061608

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

Finished restore at 06-SEP-13

Starting recover at 06-SEP-13

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:02

Finished recover at 06-SEP-13

repair failure complete

– 可以選擇打開數據庫

Do you want to open the database (enter YES or NO)? yes

database opened

我們在 alert log 中，可以監控到恢復的步驟。

–Restore 過程

Fri Sep 06 07:35:49 2013

Full restore complete of datafile 3 /u01/oradata/WILSON/datafile/o1_mf_undotbs1_92l5b0v4_.dbf. Elapsed time: 0:00:15

checkpoint is 3838694

last deallocation scn is 3817636

Undo Optimization current scn is 3815429

Fri Sep 06 07:35:54 2013

alter database recover datafile list clear

Completed: alter database recover datafile list clear

–recovery 過程

alter database recover if needed

datafile 3

Media Recovery Start

Serial Media Recovery started

Recovery of Online Redo Log: Thread 1 Group 2 Seq 176 Reading mem 0

Mem# 0: /u01/oradata/WILSON/onlinelog/o1_mf_2_870n48hc_.log

Mem# 1: /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_870n4dtl_.log

Recovery of Online Redo Log: Thread 1 Group 3 Seq 177 Reading mem 0

Mem# 0: /u01/oradata/WILSON/onlinelog/o1_mf_3_870n4lsg_.log

Mem# 1: /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_3_870n4o31_.log

Recovery of Online Redo Log: Thread 1 Group 1 Seq 178 Reading mem 0

Mem# 0: /u01/oradata/WILSON/onlinelog/o1_mf_1_870n42n1_.log

Mem# 1: /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_1_870n44z3_.log

Media Recovery Complete (wilson)

Completed: alter database recover if needed

datafile 3

Fri Sep 06 07:36:04 2013

alter database open

此時，數據庫錯誤消除。

RMAN list failure;

no failures found that match specification

最后，我們還有一個命令可以使用，就是 change failure。Change Failure 命令的作用就是顯示的將錯誤的狀態修改掉。最常用的做法是：當一個錯誤發生的時候，如果我們沒有在 RMAN 層面上去解決，比如使用冷備份方法還原。Failure 信息是不會變化狀態的。此時，可以使用 change failure 命令將狀態設置為 Closed，命令如：change failure all closed。

“Oracle 數據庫執行過程的問題怎么修復”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！

正文完