這篇“Oracle redo文件損壞怎么恢復”文章的知識點大部分人都不太理解,所以小編給大家總結了以下內(nèi)容,內(nèi)容詳細,步驟清晰,具有一定的借鑒價值,希望大家閱讀完這篇文章能有所收獲,下面我們一起來看看這篇“Oracle redo文件損壞怎么恢復”文章吧。
成都創(chuàng)新互聯(lián)主要業(yè)務有網(wǎng)站營銷策劃、網(wǎng)站建設、網(wǎng)站制作、微信公眾號開發(fā)、小程序設計、H5開發(fā)、程序開發(fā)等業(yè)務。一次合作終身朋友,是我們奉行的宗旨;我們不僅僅把客戶當客戶,還把客戶視為我們的合作伙伴,在開展業(yè)務的過程中,公司還積累了豐富的行業(yè)經(jīng)驗、全網(wǎng)營銷推廣資源和合作伙伴關系資源,并逐漸建立起規(guī)范的客戶服務和保障體系。
redo文件損壞涉及到多種多樣場景,具體場景可以分四大部分:
1、按照redo的狀態(tài)可以分為current、active和inactive;
2、按照數(shù)據(jù)庫歸檔模式可以分為歸檔和非歸檔;
3、按照臟塊有沒寫入數(shù)據(jù)文件可以分為有和無;
4、按照損壞時數(shù)據(jù)庫的狀態(tài)可以分為在線和關閉;
現(xiàn)在主要通過兩部分來介紹redo文件恢復相關的內(nèi)容:
1、按照redo狀態(tài)維度來介紹各種場景的恢復方法;
2、模擬幾種恢復方法的操作;
一、按照redo狀態(tài)維度來介紹各種場景的恢復方法。
1.1、current redo文件恢復介紹:
二、模擬幾種恢復方法的操作;
下面主要選取 “current的redo文件在歸檔模式下采用不正常關閉數(shù)據(jù)庫時還有臟塊沒有寫入數(shù)據(jù)文件時的損壞” 和 “current的redo文件在非歸檔模式下數(shù)據(jù)庫在線時還有臟塊沒有寫入數(shù)據(jù)文件時的損壞” 這兩個場景來模擬和恢復,其他場景的恢復請參考上面的恢復操作。
2.1、current的redo文件在歸檔模式下采用不正常關閉數(shù)據(jù)庫時還有臟塊沒有寫入數(shù)據(jù)文件時的損壞模擬恢復:
1、數(shù)據(jù)庫基本信息和redo情況 SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string leonliao SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /home/oracle/oradata/leonliao/arch Oldest online log sequence 7 Next log sequence to archive 9 Current log sequence 9 SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 9 52428800 512 1 NO CURRENT 1250771 07-MAR-16 2.8147E+14 2 1 8 52428800 512 1 YES INACTIVE 1250768 07-MAR-16 1250771 07-MAR-16 3 1 7 52428800 512 1 YES INACTIVE 1250765 07-MAR-16 1250768 07-MAR-16 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- --------------------------------------------- --- 3 ONLINE /home/oracle/oradata/leonliao/redo03.log NO 2 ONLINE /home/oracle/oradata/leonliao/redo02.log NO 1 ONLINE /home/oracle/oradata/leonliao/redo01.log NO 2、在t_redo表插入一條記錄2,并shutdown abort關閉數(shù)據(jù)庫 SQL> select * from t_redo; ID ---------- 1 SQL> insert into t_redo values(2); 1 row created. SQL> commit; Commit complete. SQL> shutdown abort ORACLE instance shut down. 3、刪掉current 的redo文件 [oracle@leon1 leonliao]$ pwd /home/oracle/oradata/leonliao [oracle@leon1 leonliao]$ rm -rf redo01.log 4、啟動數(shù)據(jù)庫到mount狀態(tài)并嘗試打開數(shù)據(jù)庫 SQL> startup mount ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 184550744 bytes Database Buffers 432013312 bytes Redo Buffers 7532544 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/home/oracle/oradata/leonliao/redo01.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> alter database clear logfile group 1; alter database clear logfile group 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance leonliao (thread 1) ORA-00312: online log 1 thread 1: '/home/oracle/oradata/leonliao/redo01.log' SQL> alter database clear unarchived logfile group 1; alter database clear unarchived logfile group 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance leonliao (thread 1) ORA-00312: online log 1 thread 1: '/home/oracle/oradata/leonliao/redo01.log' SQL> recover database until cancel; ORA-00279: change 1250771 generated at 03/07/2016 07:35:44 needed for thread 1 ORA-00289: suggestion : /home/oracle/oradata/leonliao/arch/1_9_905840705.dbf ORA-00280: change 1250771 for thread 1 is in sequence #9 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: cannot open archived log '/home/oracle/oradata/leonliao/arch/1_9_905840705.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-00308: cannot open archived log '/home/oracle/oradata/leonliao/arch/1_9_905840705.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/home/oracle/oradata/leonliao/system01.dbf' SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/home/oracle/oradata/leonliao/system01.dbf' 5、設置隱含參數(shù)_allow_resetlogs_corruption為true SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile; System altered. SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 184550744 bytes Database Buffers 432013312 bytes Redo Buffers 7532544 bytes Database mounted. SQL> alter database open resetlogs; Database altered. 6、驗證數(shù)據(jù)是否丟失,數(shù)據(jù)為2的記錄已經(jīng)丟失 SQL> select * from t_redo; ID ---------- 1 |
2.2、current的redo文件在非歸檔模式下數(shù)據(jù)庫在線時還有臟塊沒有寫入數(shù)據(jù)文件時的損壞模擬恢復:
1、數(shù)據(jù)庫基本信息和redo情況 SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string leonliao SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /home/oracle/oradata/leonliao/arch Oldest online log sequence 2 Current log sequence 4 SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 4 52428800 512 1 NO CURRENT 1250086 07-MAR-16 2.8147E+14 2 1 2 52428800 512 1 NO INACTIVE 1250080 07-MAR-16 1250083 07-MAR-16 3 1 3 52428800 512 1 NO INACTIVE 1250083 07-MAR-16 1250086 07-MAR-16 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- --------------------------------------------- --- 3 ONLINE /home/oracle/oradata/leonliao/redo03.log NO 2 ONLINE /home/oracle/oradata/leonliao/redo02.log NO 1 ONLINE /home/oracle/oradata/leonliao/redo01.log NO 2、創(chuàng)建t_redo表并插入一條數(shù)據(jù) SQL> create table t_redo (id number); Table created. SQL> insert into t_redo values(1); 1 row created. SQL> commit; Commit complete. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 4 52428800 512 1 NO CURRENT 1250086 07-MAR-16 2.8147E+14 2 1 2 52428800 512 1 NO INACTIVE 1250080 07-MAR-16 1250083 07-MAR-16 3 1 3 52428800 512 1 NO INACTIVE 1250083 07-MAR-16 1250086 07-MAR-16 3、刪除current的redo01.log文件 [oracle@leon1 leonliao]$ pwd /home/oracle/oradata/leonliao [oracle@leon1 leonliao]$ rm -rf redo01.log 4、嘗試直接通過不歸檔等方式初始化redo01.log文件,無法初始化current的redo文件 SQL> alter database clear unarchived logfile group 1; alter database clear unarchived logfile group 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance leonliao (thread 1) ORA-00312: online log 1 thread 1: '/home/oracle/oradata/leonliao/redo01.log' SQL> alter database clear logfile group 1; alter database clear logfile group 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance leonliao (thread 1) ORA-00312: online log 1 thread 1: '/home/oracle/oradata/leonliao/redo01.log' 5、通過將current狀態(tài)切換到active狀態(tài),并初始化redo01.log文件 SQL> alter system switch logfile; System altered. SQL> alter database clear logfile group 1; Database altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 0 52428800 512 1 NO UNUSED 1250086 07-MAR-16 1250147 07-MAR-16 2 1 5 52428800 512 1 NO CURRENT 1250147 07-MAR-16 2.8147E+14 3 1 3 52428800 512 1 NO INACTIVE 1250083 07-MAR-16 1250086 07-MAR-16 6、驗證數(shù)據(jù)是否丟失,數(shù)據(jù)沒有丟失 SQL> select * from t_redo; ID ---------- 1 |
以上就是關于“Oracle redo文件損壞怎么恢復”這篇文章的內(nèi)容,相信大家都有了一定的了解,希望小編分享的內(nèi)容對大家有幫助,若想了解更多相關的知識內(nèi)容,請關注創(chuàng)新互聯(lián)行業(yè)資訊頻道。
標題名稱:Oracleredo文件損壞怎么恢復
URL分享:http://muchs.cn/article10/jpesdo.html
成都網(wǎng)站建設公司_創(chuàng)新互聯(lián),為您提供網(wǎng)站策劃、手機網(wǎng)站建設、定制網(wǎng)站、用戶體驗、網(wǎng)站設計公司、
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉載內(nèi)容為主,如果涉及侵權請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載,或轉載時需注明來源: 創(chuàng)新互聯(lián)