日誌檔案的修復小結
SYS@ENMOEDU> select GROUP#, MEMBERS,STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 CURRENT
3 1 INACTIVE
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 CURRENT
3 1 INACTIVE
INACTIVE :表時例項恢復不再需要此組日誌檔案,可以覆蓋
active :表示日誌組中仍有例項恢復所需的重做資料
CURRENT:表示 正在被LGWR使用的日誌組
一: 資料庫聯機的情況下,丟失 INACTIVE 日誌檔案的
1.SYS@ENMOEDU> select GROUP#, MEMBERS,STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 CURRENT
3 1 INACTIVE
SYS@ENMOEDU> select GROUP# ,STATUS,MEMBER from v$logfile; GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 CURRENT
3 1 INACTIVE
GROUP# STATUS
---------- -------
MEMBER
--------------------------------------------------------------------------------
1
/u01/app/oracle/oradata/ENMOEDU/redo01.log
2
/u01/app/oracle/oradata/ENMOEDU/redo02.log
3
/u01/app/oracle/oradata/ENMOEDU/redo03.log
2.刪除第一組
SYS@ENMOEDU> ! rm /u01/app/oracle/oradata/ENMOEDU/redo01.log
3.發現丟失後,沒等資料庫反應過來,就立即恢復。 刪掉重建
SYS@ENMOEDU> alter database add logfile group 1 ('/u01/app/oracle/oradata/ENMOEDU/redo01.log') SIZE 50m;
Database altered.
Database altered.
4.恢復完成
SYS@ENMOEDU> select GROUP#, MEMBERS,STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 ACTIVE
3 1 INACTIVE
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 ACTIVE
3 1 INACTIVE
二:資料庫聯機下,刪除 INACTIVE 日誌組,重啟資料庫後恢復
1,製造環境,先看日誌狀態
SYS@ENMOEDU> select GROUP#, MEMBERS,STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 INACTIVE
3 1 INACTIVE
SYS@ENMOEDU> select GROUP# ,STATUS, MEMBER from v$logfile;
GROUP# STATUS
---------- -------
MEMBER
--------------------------------------------------------------------------------
1
/u01/app/oracle/oradata/ENMOEDU/redo01.log
2
/u01/app/oracle/oradata/ENMOEDU/redo02.log
3
/u01/app/oracle/oradata/ENMOEDU/redo03.log
SYS@ENMOEDU> ! rm /u01/app/oracle/oradata/ENMOEDU/redo02.log
SYS@ENMOEDU> ! ls /u01/app/oracle/oradata/ENMOEDU/redo02.log
ls: /u01/app/oracle/oradata/ENMOEDU/redo02.log: No such file or directory
2.重啟資料庫,讓資料庫發現已經丟失了日誌檔案
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 INACTIVE
3 1 INACTIVE
SYS@ENMOEDU> select GROUP# ,STATUS, MEMBER from v$logfile;
GROUP# STATUS
---------- -------
MEMBER
--------------------------------------------------------------------------------
1
/u01/app/oracle/oradata/ENMOEDU/redo01.log
2
/u01/app/oracle/oradata/ENMOEDU/redo02.log
3
/u01/app/oracle/oradata/ENMOEDU/redo03.log
SYS@ENMOEDU> ! rm /u01/app/oracle/oradata/ENMOEDU/redo02.log
SYS@ENMOEDU> ! ls /u01/app/oracle/oradata/ENMOEDU/redo02.log
ls: /u01/app/oracle/oradata/ENMOEDU/redo02.log: No such file or directory
2.重啟資料庫,讓資料庫發現已經丟失了日誌檔案
SYS@ENMOEDU> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ENMOEDU> startup 啟動時只能到mount (只是看著應該是mount 狀態,其實資料庫目前的狀態是shutdown狀態,)
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 343935132 bytes
Database Buffers 71303168 bytes
Redo Buffers 6086656 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 10779
Session ID: 125 Serial number: 5
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ENMOEDU> startup 啟動時只能到mount (只是看著應該是mount 狀態,其實資料庫目前的狀態是shutdown狀態,)
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 343935132 bytes
Database Buffers 71303168 bytes
Redo Buffers 6086656 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 10779
Session ID: 125 Serial number: 5
3.因為沒有明確不能open的原因,所以你應先看一下alert日誌,我們會發現問題原因。
[oracle@ENMOEDU trace]$ tail -100 alert_ENMOEDU.log
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo02.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
4.對2號日誌組執行clear 操作,
對於這類非當前日誌可以直接clear,系統會重新自動生成一個redo檔案。
SYS@ENMOEDU> alter database clear logfile group 1; #驗證了前面的說法,此時確實是在shutdown狀態
alter database clear logfile group 1
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SYS@ENMOEDU> startup mount
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 343935132 bytes
Database Buffers 71303168 bytes
Redo Buffers 6086656 bytes
Database mounted.
alter database clear logfile group 1
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SYS@ENMOEDU> startup mount
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 343935132 bytes
Database Buffers 71303168 bytes
Redo Buffers 6086656 bytes
Database mounted.
5.檢視結果 發現已經有了物理檔案,恢復成功
SYS@ENMOEDU> ! ls /u01/app/oracle/oradata/ENMOEDU/*.log
/u01/app/oracle/oradata/ENMOEDU/redo01.log
/u01/app/oracle/oradata/ENMOEDU/redo01.log
/u01/app/oracle/oradata/ENMOEDU/redo02.log
/u01/app/oracle/oradata/ENMOEDU/redo03.log
三:丟失current狀態的日誌檔案。
1.先查狀態
SYS@ENMOEDU> select GROUP# ,STATUS, MEMBER from v$logfile;
GROUP# STATUS
---------- -------
MEMBER
--------------------------------------------------------------------------------
1
/u01/app/oracle/oradata/ENMOEDU/redo01.log
2
/u01/app/oracle/oradata/ENMOEDU/redo02.log
3
/u01/app/oracle/oradata/ENMOEDU/redo03.log
SYS@ENMOEDU> select GROUP#, MEMBERS,STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
3 1 INACTIVE
2 1 UNUSED
GROUP# STATUS
---------- -------
MEMBER
--------------------------------------------------------------------------------
1
/u01/app/oracle/oradata/ENMOEDU/redo01.log
2
/u01/app/oracle/oradata/ENMOEDU/redo02.log
3
/u01/app/oracle/oradata/ENMOEDU/redo03.log
SYS@ENMOEDU> select GROUP#, MEMBERS,STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
3 1 INACTIVE
2 1 UNUSED
2,1號日誌組是當前日誌組,刪掉
SYS@ENMOEDU> ! rm /u01/app/oracle/oradata/ENMOEDU/redo01.log
3.做DML操作,生成日誌
SYS@ENMOEDU> create table hr.t tablespace users as select * from dba_objects where 1=2;
SYS@ENMOEDU> insert into hr.t select * from dba_objects;
4.嘗試去刪掉該日誌組,然後再重新建立日誌組,發現不成功,因為還沒有歸檔,
YS@ENMOEDU> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance ENMOEDU (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo01.log'
解決辦法1 既然它是因為歸檔而造成的,那自然想到可以將資料庫改成非歸檔模式,然後再刪掉重建,
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance ENMOEDU (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo01.log'
解決辦法1 既然它是因為歸檔而造成的,那自然想到可以將資料庫改成非歸檔模式,然後再刪掉重建,
SYS@ENMOEDU> alter database noarchivelog;
SYS@ENMOEDU> alter database drop logfile group 1;
SYS@ENMOEDU> alter database add logfile group 1 ('/u01/app/oracle/oradata/ENMOEDU/redo01.log') size 50m;
解決辦法2:採用recover 的方式完成重做日誌恢復,
SYS@ENMOEDU> alter database open resetlogs; #resetlogs 會把日誌序號清空,從頭開始,是讓控制檔案,日誌檔案的SCN號向資料檔案靠 ,也就是資料檔案的scn號 更新一點
Database altered.
Database altered.
SYS@ENMOEDU> ! ls /u01/app/oracle/oradata/ENMOEDU/redo01.log #發現刪掉的日誌組1 已經回來。
/u01/app/oracle/oradata/ENMOEDU/redo01.log
/u01/app/oracle/oradata/ENMOEDU/redo01.log
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29654823/viewspace-1252691/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 修復損害的事件日誌檔案(轉)事件
- oracle 控制檔案的修復小結Oracle
- 日誌檔案使用小結(轉)
- 恢復歸檔日誌檔案的常用方法
- 減小SQL SERVER的日誌檔案SQLServer
- win10日誌檔案缺了怎麼辦_win10日誌缺失如何修復Win10
- 【恢復】Redo日誌檔案丟失的恢復
- 聯機重做日誌檔案的恢復
- 非歸檔丟失日誌檔案的恢復
- 丟失所有重做日誌檔案的恢復例子丟失所有重做日誌檔案的恢復例子如下:
- 引數檔案丟失無法啟動_ 利用alert日誌修復
- 修復檔案終結者病毒破壞的檔案
- oracle丟失日誌檔案的恢復( 轉)Oracle
- SQL Server日誌檔案總結及日誌滿的處理SQLServer
- 日誌檔案和歸檔日誌檔案的關係以及如何切換日誌
- INACTIVE日誌組損壞的修復
- 丟失聯機重做日誌檔案的恢復
- 在歸檔模式下丟失日誌檔案的恢復模式
- [zt] SQL Server日誌檔案總結及日誌滿的處理SQLServer
- Oracle歸檔日誌比聯機重做日誌小很多的情況總結Oracle
- 【Oracle日誌】- 日誌檔案重建Oracle
- 日誌檔案
- 恢復案例:無歸檔,丟失全部控制檔案、日誌檔案恢復案例
- 【備份恢復】恢復inactive狀態的日誌檔案
- 日誌檔案的管理
- 日誌檔案的大小
- 控制檔案修復
- Oracle使用備份檔案集恢復歸檔日誌Oracle
- sql server日誌檔案總結及日誌滿的處理辦法SQLServer
- SQL Anywhere db檔案損壞修復 DB檔案修復 DB資料庫修復SQL資料庫
- 【備份恢復】恢復 丟失已歸檔重做日誌檔案
- 對日誌檔案組和日誌檔案組成員的管理
- 一次日誌檔案損壞的恢復
- 刪除日誌檔案組與日誌檔案成員
- RMAN恢復案例:無恢復目錄,丟失全部資料檔案、控制檔案、日誌檔案恢復
- 管理日誌檔案
- dump日誌檔案
- APACHE日誌檔案Apache