您好,登錄后才能下訂單哦!
本篇內(nèi)容介紹了“Oracle數(shù)據(jù)庫執(zhí)行過程的問題怎么修復(fù)”的有關(guān)知識,在實際案例的操作過程中,不少人都會遇到這樣的困境,接下來就讓小編帶領(lǐng)大家學(xué)習一下如何處理這些情況吧!希望大家仔細閱讀,能夠?qū)W有所成!
運行過程中故障
在運行過程中的oracle故障,壞塊和文件異常刪除出現(xiàn)的比較多,特別是初級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
當前數(shù)據(jù)庫處在Open運行狀態(tài),突然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中可以出現(xiàn)上篇中那個“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
差不多兩秒鐘報一個錯誤,發(fā)現(xiàn)文件被刪除無法打開。
此時,我們在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要求將數(shù)據(jù)庫重啟到mount狀態(tài),才能有自動腳本的出現(xiàn)。Manual Actions是那些Oracle覺得需要用戶手工執(zhí)行才能繼續(xù)下去的步驟。
重新啟動一下庫,加載到mount狀態(tài)。
--強制關(guān)閉
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工具,看問題和提示內(nèi)容。
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命令來查看執(zhí)行語句。
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發(fā)現(xiàn)了當前我們有Undo的備份和歸檔日志。所以使用restore之后伴隨recover,可以快速實現(xiàn)恢復(fù)。
如果在preview中沒有發(fā)現(xiàn)什么問題,可以repair failure命令執(zhí)行進行恢復(fù)。
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
--可以選擇打開數(shù)據(jù)庫
Do you want to open the database (enter YES or NO)? yes
database opened
我們在alert log中,可以監(jiān)控到恢復(fù)的步驟。
--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
此時,數(shù)據(jù)庫錯誤消除。
RMAN> list failure;
no failures found that match specification
最后,我們還有一個命令可以使用,就是change failure。Change Failure命令的作用就是顯示的將錯誤的狀態(tài)修改掉。最常用的做法是:當一個錯誤發(fā)生的時候,如果我們沒有在RMAN層面上去解決,比如使用冷備份方法還原。Failure信息是不會變化狀態(tài)的。此時,可以使用change failure命令將狀態(tài)設(shè)置為Closed,命令如:change failure all closed。
“Oracle數(shù)據(jù)庫執(zhí)行過程的問題怎么修復(fù)”的內(nèi)容就介紹到這里了,感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站,小編將為大家輸出更多高質(zhì)量的實用文章!
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。