[20170302]正常關閉資料庫日誌丟失3.txt
[20170302]正常關閉資料庫日誌丟失3.txt
--//上午寫了一篇[20170302]什麼是fuzzy.txt ,連結 http://blog.itpub.net/267265/viewspace-2134551/
--//到最後恢復結束時fuzzy=NO,這時scn=13276966782.也就是將只有恢復到結束,oracle才會認為資料檔案一致的.
--//我本來想刪除日誌檔案看看是否能open resetlogs的,結果報錯.
--//下午重新探究看看:
1.環境:
SYS@book> @ &r/ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//正常關閉資料庫,做一個冷備份,然後刪除日誌檔案.
$ rm /mnt/ramdisk/book/r*.log
2..測試:
SYS@book> startup mount
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------- ------------------------------
1 13276932032 2017-03-02 14:57:02 7 925702 ONLINE 842 NO /mnt/ramdisk/book/system01.dbf SYSTEM
2 13276932032 2017-03-02 14:57:02 1834 925702 ONLINE 831 NO /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13276932032 2017-03-02 14:57:02 923328 925702 ONLINE 752 NO /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13276932032 2017-03-02 14:57:02 16143 925702 ONLINE 837 NO /mnt/ramdisk/book/users01.dbf USERS
5 13276932032 2017-03-02 14:57:02 952916 925702 ONLINE 748 NO /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13276932032 2017-03-02 14:57:02 13276257767 925702 ONLINE 216 NO /mnt/ramdisk/book/tea01.dbf TEA
6 rows selected.
--//FUZZY=NO.
SYS@book> @ &r/logfile
GROUP# STATUS TYPE MEMBER IS_ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ------ ---------- -------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
1 ONLINE /mnt/ramdisk/book/redo01.log NO 1 1 695 52428800 512 1 YES INACTIVE 13276910949 2017-02-28 14:40:12 13276931102 2017-03-02 14:56:08
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 696 52428800 512 1 NO CURRENT 13276931102 2017-03-02 14:56:08 2.814750E+14
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 694 52428800 512 1 YES INACTIVE 13276910486 2017-02-28 14:40:06 13276910949 2017-02-28 14:40:12
4 STANDBY /mnt/ramdisk/book/redostb01.log NO
5 STANDBY /mnt/ramdisk/book/redostb02.log NO
6 STANDBY /mnt/ramdisk/book/redostb03.log NO
7 STANDBY /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
SYS@book> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
--//嗯,^_^不能透過open resetlogs;,才想起來要採用alter database clear logfile group N的方式,沒有歸檔執行:
--//alter database clear unarchived logfile group N ;這樣不用open resetlogs.
3.恢復:
--//我記憶裡使用上面的方法我以前是失敗的,我採用建立新的控制檔案方式(resetlogs)方式建立.
--//那個時候剛剛學oracle,什麼都不懂,完全是依葫蘆畫瓢.再次使用alter database clear logfile group N方式.
--//說明:當前的情況 status=CURRENT,是group#=2.
SYS@book> alter database clear logfile group 1 ;
Database altered.
SYS@book> alter database clear logfile group 2 ;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of instance book (thread 1) needs to be archived
ORA-00312: online log 2 thread 1: '/mnt/ramdisk/book/redo02.log'
--//這個必須歸檔,不能這樣執行.
SYS@book> alter database clear unarchived logfile group 2 ;
Database altered.
SYS@book> alter database clear logfile group 3 ;
Database altered.
--//ok,以前為什麼不行呢?我仔細看了我以前做的文件,才明白我的錯誤.
4.重複測試:
--//從冷備份恢復,刪除redo.
SYS@book> alter database clear logfile group 2 ;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of instance book (thread 1) needs to be archived
ORA-00312: online log 2 thread 1: '/mnt/ramdisk/book/redo02.log'
SYS@book> alter database clear unarchived logfile group 2 ;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/mnt/ramdisk/book/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--//注意看提示,當時就是沒有看提示,提示無法獲得/mnt/ramdisk/book/redo03.log的狀態.實際上如果你執行很快,一樣報錯.
$ cat /tmp/a.txt
alter database clear logfile group 1 ;
alter database clear unarchived logfile group 2 ;
--//注:沒有包括clear logfile group 3 ;
SYS@book> @ /tmp/a.txt
Database altered.
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/mnt/ramdisk/book/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Database altered.
--//依舊是報無法獲得/mnt/ramdisk/book/redo03.log狀態.
--//而這個時候你在手工執行:
SYS@book> alter database clear unarchived logfile group 2 ;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/mnt/ramdisk/book/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--//一樣報錯.
SYS@book> @ &r/logfile
GROUP# STATUS TYPE MEMBER IS_ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- -------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
1 ONLINE /mnt/ramdisk/book/redo01.log NO 1 1 0 52428800 512 1 YES UNUSED 1.3277E+10 2017-02-28 14:40:12 1.3277E+10 2017-03-02 14:56:08
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 0 52428800 512 1 NO CLEARING_C 1.3277E+10 2017-03-02 14:56:08 2.8147E+14
URRENT
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 0 52428800 512 1 YES UNUSED 0 2017-02-28 14:40:06 0 2017-02-28 14:40:12
4 STANDBY /mnt/ramdisk/book/redostb01.log NO
5 STANDBY /mnt/ramdisk/book/redostb02.log NO
6 STANDBY /mnt/ramdisk/book/redostb03.log NO
7 STANDBY /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
SYS@book> alter database clear logfile group 3 ;
Database altered.
SYS@book> alter database clear unarchived logfile group 2 ;
Database altered.
SYS@book> @ &r/logfile
GROUP# STATUS TYPE MEMBER IS_ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ------ ---------- ------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
1 ONLINE /mnt/ramdisk/book/redo01.log NO 1 1 697 52428800 512 1 NO CURRENT 13276931103 2017-03-02 15:26:41 2.814750E+14
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 0 52428800 512 1 YES UNUSED 13276931102 2017-03-02 14:56:08 13276931103 2017-03-02 15:26:41
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 0 52428800 512 1 YES UNUSED 0 2017-02-28 14:40:06 0 2017-02-28 14:40:12
4 STANDBY /mnt/ramdisk/book/redostb01.log NO
5 STANDBY /mnt/ramdisk/book/redostb02.log NO
6 STANDBY /mnt/ramdisk/book/redostb03.log NO
7 STANDBY /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
--//SEQUENCE#=697使用group#=1.實際上執行alter database clear unarchived logfile group 2 ;要找一個新的redo分配seq,而不知
--//道為什麼oracle一定要先/mnt/ramdisk/book/redo03.log,當然這個是開始SEQUENCE#=694最小的.
--//當時的錯誤就是沒有認真看提示.
5.再重複測試:
--//從冷備份恢復,刪除redo.
$ cat /tmp/a.txt
alter database clear logfile group 1 ;
alter database clear unarchived logfile group 2 ;
alter database clear logfile group 3 ;
SYS@book> @ /tmp/a.txt
Database altered.
Database altered.
Database altered.
SYS@book> @ &r/logfile
GROUP# STATUS TYPE MEMBER IS_ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- ------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
1 ONLINE /mnt/ramdisk/book/redo01.log NO 1 1 697 52428800 512 1 NO CURRENT 13276931103 2017-03-02 15:33:48 2.814750E+14
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 0 52428800 512 1 YES UNUSED 13276931102 2017-03-02 14:56:08 13276931103 2017-03-02 15:33:48
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 0 52428800 512 1 YES UNUSED 13276910486 2017-02-28 14:40:06 13276910949 2017-02-28 14:40:12
4 STANDBY /mnt/ramdisk/book/redostb01.log NO
5 STANDBY /mnt/ramdisk/book/redostb02.log NO
6 STANDBY /mnt/ramdisk/book/redostb03.log NO
7 STANDBY /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
--//OK!!
--//我反覆測試多次,只要執行時沒有報如下錯誤.什麼執行都是ok的.一旦報了這個錯誤必須先清除group 3,再執行alter database
--//clear unarchived logfile group 2.才會ok,當時太不注意看提示了.^_^.
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/mnt/ramdisk/book/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Database altered.
5.再再重複測試:
--//從冷備份恢復,刪除redo.
$ cat /tmp/a.txt
alter database clear logfile group 3 ;
alter database clear unarchived logfile group 2 ;
alter database clear logfile group 1 ;
SYS@book> @ /tmp/a.txt
Database altered.
Database altered.
Database altered.
SYS@book> @ &r/logfile
GROUP# STATUS TYPE MEMBER IS_ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- ------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
1 ONLINE /mnt/ramdisk/book/redo01.log NO 1 1 0 52428800 512 1 YES UNUSED 13276910949 2017-02-28 14:40:12 13276931102 2017-03-02 14:56:08
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 0 52428800 512 1 YES UNUSED 13276931102 2017-03-02 14:56:08 13276931103 2017-03-02 15:44:59
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 697 52428800 512 1 NO CURRENT 13276931103 2017-03-02 15:44:59 2.814750E+14
4 STANDBY /mnt/ramdisk/book/redostb01.log NO
5 STANDBY /mnt/ramdisk/book/redostb02.log NO
6 STANDBY /mnt/ramdisk/book/redostb03.log NO
7 STANDBY /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
--//這時的SEQUENCE#=697,是GROUP#=3.
--//也就是清除順序最好按照SEQUENCE#的順序,從小到大(694,695,696),最後清除沒有歸檔的線上日誌.當然你也可以重新組織順序.
--//再次說明提示要認真看.教訓啊.
SYS@book> alter database open ;
Database altered.
--//ok,正常開啟.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2134585/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- UNDO表空間資料檔案丟失處理（二）正常關閉資料庫資料庫
- oracle資料庫正常關閉狀態下丟失undo檔案的恢復Oracle資料庫
- redo log 丟失（非歸檔模式,資料庫正常關閉，redo log 被誤刪除！）模式資料庫
- oracle歸檔日誌丟失後的資料庫恢復Oracle資料庫
- 開啟關閉oracle資料庫附加日誌Oracle資料庫
- 丟失已歸檔日誌檔案下恢復資料庫資料庫
- SQL Server 2005資料庫日誌丟失的恢復SQLServer資料庫
- 關閉資料庫會導致cache的sequence值丟失嗎？資料庫
- 丟失當前current重做日誌檔案下恢復資料庫資料庫
- 解決Oracle資料庫日誌檔案丟失恢復問題Oracle資料庫
- Oracle資料庫聯機日誌檔案丟失處理方法(1)Oracle資料庫
- Oracle資料庫聯機日誌檔案丟失處理方法(3)Oracle資料庫
- Oracle資料庫聯機日誌檔案丟失處理方法(2)Oracle資料庫
- Oracle資料庫聯機日誌檔案丟失處理方法(4)Oracle資料庫
- Oracle資料庫聯機日誌檔案丟失處理方法(5)Oracle資料庫
- Oralce資料庫關閉歸檔日誌並且刪除歸檔日誌資料庫
- Oracle資料庫聯機日誌檔案丟失處理方法（總結）！Oracle資料庫
- Sqlserver系統資料庫和使用者資料庫日誌檔案全部丟失的恢復SQLServer資料庫
- Oracle資料庫聯機日誌檔案丟失處理方法（總結）(轉)Oracle資料庫
- [原創] Oracle資料庫聯機日誌檔案丟失處理方法（總結）！Oracle資料庫
- Centos系統關閉Mysql資料庫查詢操作日誌的方法CentOSMySql資料庫
- 根據Alert日誌查詢資料庫啟動、關閉時間資料庫
- rman恢復:資料檔案丟失,控制檔案丟失,聯機日誌檔案丟失(非當前使用與當前使用)
- oracle 正常關閉shutdown immediate與開啟open資料庫alertOracle資料庫
- 從丟失日誌組中恢復流程
- 聯機重做日誌丟失的恢復
- 丟失非活動日誌組的恢復
- 沒備份，歸檔日誌存在，丟失資料檔案的恢復
- 資料庫關閉資料庫
- ORACLE DSG資料同步軟體程式導致資料庫無法正常關閉Oracle資料庫
- 資料庫OPEN下，丟失部分或所有control file 是否會導致 例項關閉。資料庫
- 關閉監聽的日誌。
- oracle丟失的是所有的redo日誌組Oracle
- Oracle DataGuard歸檔日誌丟失處理方法Oracle
- 聯機日誌檔案丟失解決方法
- 【redo】日誌檔案的丟失解決方法
- 利用incremental backup恢復丟失日誌的standbyREM
- oracle丟失日誌檔案的恢復( 轉)Oracle