[20210224]控制檔案序列號滿的分析.txt
[20210224]控制檔案序列號滿的分析.txt
--//上午看了連結:https://blog.csdn.net/enmotech/article/details/113855641,出現控制檔案序列號滿的情況,我從來沒有遇到.
--//下午沒事,看看是否能在測試環境演示出來重複故障.
--//注意不能在生產系統做這樣的測試!!!很久沒有做這類恢復工作,寫的有點亂.
1.環境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.測試:
--//首先我仔細看了原始連結,我發現作者透過設定小的閃回區,不斷切換歸檔,看檢視控制檔案順序號.
--//我自己也嘗試了一下,導致歸檔滿了,掛起,我認為跟這個沒關係,實際上只要切換歸檔或者發出檢查點,
--//控制檔案順序號就會增加.我先驗證這種情況.
sqlplus -s -l scott/book << EOF | grep "[0-9]"
set head off
set feedback off
$(seq 5 | xargs -I{} echo -e 'alter system checkpoint;\nselect CONTROLFILE_SEQUENCE# from v$database;\nhost sleep 1')
quit
EOF
36500
36501
36502
36503
36504
--//你可以發現發出檢查點,CONTROLFILE_SEQUENCE#增加1.
sqlplus -s -l scott/book << EOF | grep "[0-9]"
set head off
set feedback off
$(seq 5 | xargs -I{} echo -e 'alter system switch logfile;\nselect CONTROLFILE_SEQUENCE# from v$database;\nhost sleep 1')
quit
EOF
36506
36509
36513
36517
36520
--//執行alter system switch logfile也是一樣.
--//我有一種預感可能對方日誌可能切換過於頻繁,可能導致控制檔案序列號增加太快,消耗枯竭.
3.首先定位它在控制檔案的什麼位置.
SYS@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
------------ ------------ ------------------------ --------- ------ ------- ------------ --------------------------------------------------
30 261 57742 DEDICATED 57743 26 101 alter system kill session '30,261' immediate;
SCOTT@book> select CONTROLFILE_SEQUENCE# from v$database;
CONTROLFILE_SEQUENCE#
---------------------
36571
$ strace -f -p 57743 -e pread
Process 57743 attached - interrupt to quit
pread(256, "\25\302\0\0\1\0\0\0\0\0\0\0\0\0\1\4\327?\0\0\0\0\0\0\0\4 \vn!\267O"..., 16384, 16384) = 16384
pread(256, "\25\302\0\0\17\0\0\0j\275\0\0\377\377\1\4 C\0\0\0\6\0\0\0\0\0\0\0\0\0\4"..., 16384, 245760) = 16384
pread(256, "\25\302\0\0\21\0\0\0j\275\0\0\377\377\1\4\225Z\0\0\0\0\0\0\0\0\0\0\256\36q5"..., 16384, 278528) = 16384
pread(256, "\25\302\0\0\1\0\0\0\0\0\0\0\0\0\1\4\327?\0\0\0\0\0\0\0\4 \vn!\267O"..., 16384, 16384) = 16384
pread(256, "\25\302\0\0\17\0\0\0j\275\0\0\377\377\1\4 C\0\0\0\6\0\0\0\0\0\0\0\0\0\4"..., 16384, 245760) = 16384
pread(256, "\25\302\0\0\21\0\0\0j\275\0\0\377\377\1\4\225Z\0\0\0\0\0\0\0\0\0\0\256\36q5"..., 16384, 278528) = 16384
pread(256, "\25\302\0\0\32\1\0\0b\275\0\0\377\377\1\4\3270\0\0\27\2\2\0\345\7\0\0\0\0\0\0"..., 16384, 4620288) = 16384
--//應該讀的位置在控制檔案偏移16384,245760,278528,4620288.
$ echo 16384,245760,278528,4620288 | tr ',' '\n' | xargs -IQ bash -c "echo Q/16384| bc "| paste -sd','
1,15,17,282
--//關閉資料庫,重新啟動資料庫到mount狀態.這樣啟動後應該不變.
SYS@book> startup mount
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
SYS@book> select CONTROLFILE_SEQUENCE# from v$database;
CONTROLFILE_SEQUENCE#
---------------------
36580
--//36580 = 0x8ee4,顛倒過來就是0xe48e.
$ echo 1,15,17,282 | tr ',' '\n' | xargs -IQ echo -e 'set dba 101,Q\nfind /x e48e top' | rlbbed
BBED: Release 2.0.0.0.0 - Limited Production on Wed Feb 24 15:59:39 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set count 64
COUNT 64
BBED> set width 160
WIDTH 160
BBED> DBA 0x19400001 (423624705 101,1)
BBED> File: /mnt/ramdisk/book/control01.ctl (101)
Block: 1 Offsets: 40 to 103 Dba:0x19400001
------------------------------------------------------------------------------------------------------------------------------------------------
e48e0000 8c020000 00400000 00000100 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 5e89c659 ae1e7135
<64 bytes per line>
BBED> DBA 0x1940000f (423624719 101,15)
BBED> BBED-00212: search string not found
BBED> DBA 0x19400011 (423624721 101,17)
BBED> BBED-00212: search string not found
BBED> DBA 0x1940011a (423624986 101,282)
BBED> BBED-00212: search string not found
--//注我已經定義101對應控制檔案,並且實際上指定101,自動設定blocksize=16384,不需要設定.
$ ls -l /mnt/ramdisk/book/control01.ctl
-rw-r----- 1 oracle oinstall 10698752 2021-02-24 16:03:22 /mnt/ramdisk/book/control01.ctl
--//10698752/16384 = 653,從0開始到652,控制檔案的0塊也是OS頭,你可以使用xxd -c 16 /mnt/ramdisk/book/control01.ctl檢視.
$ xxd -c 16 /mnt/ramdisk/book/control01.ctl | head -6
0000000: 00c2 0000 0000 c0ff 0000 0000 0000 0000 .?...?........
0000010: eaf8 0000 0040 0000 8c02 0000 7d7c 7b7a 犋...@......}||z
~~~~~~~~~=>奇幻數,資料檔案以及日誌檔案都有類似標識.
0000020: a081 0000 0000 0000 0000 0000 0000 0000 ................
0000030: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000040: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000050: 0000 0000 0000 0000 0000 0000 0000 0000 ................
$ seq 652 | tr ',' '\n' | xargs -IQ echo -e 'set dba 101,Q\nfind /x e48e top' | rlbbed | grep -C2 "\-\-\--"
BBED> File: /mnt/ramdisk/book/control01.ctl (101)
Block: 1 Offsets: 40 to 103 Dba:0x19400001
------------------------------------------------------------------------------------------------------------------------------------------------
e48e0000 8c020000 00400000 00000100 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 5e89c659 ae1e7135
--//很明顯位置在控制檔案塊1,偏移40的位置,應該佔32位,4個位元組.最大0xffffffff = 4294967295.
4.先做一些功課,看看資料檔案以及日誌是否存在這個資訊.
--//我看一些檔案頭資料,在資料檔案頭kcvfh.kcvfhhdr.kccfhcsq也是記錄控制檔案的seq.
$ seq 6 | xargs -IQ echo "p dba Q,1 kcvfh.kcvfhhdr.kccfhcsq" | rlbbed| grep kccfhcsq
BBED> ub4 kccfhcsq @40 0x00008edc
BBED> ub4 kccfhcsq @40 0x00008edc
BBED> ub4 kccfhcsq @40 0x00008edc
BBED> ub4 kccfhcsq @40 0x00008edc
BBED> ub4 kccfhcsq @40 0x00008edc
BBED> ub4 kccfhcsq @40 0x00008edc
--//也是在檔案頭偏移40的位置,但是數值0x8edc = 36572.感覺是我關閉資料庫記錄的值寫入的,比前面+1(對比前面).
--//看看日誌檔案的情況:
$ seq 501 503 | xargs -IQ echo "dump /v dba Q,1 offset 36 count 8" | rlbbed | grep 00900100
d68e0000 00900100 l ........
dc8e0000 00900100 l ........
d28e0000 00900100 l ........
--//注我已經定義501,502,503對應日誌檔案redo01.log,redo02.log,redo03.log.
--//我估計在切換日誌時寫入當時的控制檔案seq.
SYS@book> @ log
Show redo log layout from V$LOG and V$LOGFILE...
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
--------------- --------------- --------------- --------------- --------------- --------------- --- ---------------- --------------- ------------------- --------------- -------------------
1 1 716 52428800 512 1 YES INACTIVE 13276955579 2021-02-24 15:48:54 13276955587 2021-02-24 15:48:57
2 1 717 52428800 512 1 NO CURRENT 13276955587 2021-02-24 15:48:57 281474976710655
3 1 715 52428800 512 1 YES INACTIVE 13276955571 2021-02-24 15:48:51 13276955579 2021-02-24 15:48:54
--//可以確定日誌檔案也有對應資訊.位於塊1的偏移36位元組處.當前日誌是第2組.
5.嘗試修改控制檔案順序號:
--//注意修改前備份控制檔案.關閉資料庫
$ cp /mnt/ramdisk/book/control0* /home/oracle/tmp/
BBED> set dba 101,1
DBA 0x19400001 (423624705 101,1)
BBED> dump /v dba 101,1 offset 40 count 8
File: /mnt/ramdisk/book/control01.ctl (101)
Block: 1 Offsets: 40 to 47 Dba:0x19400001
-----------------------------------------------------------------------------------------------------------
e68e0000 8c020000 l ........
<32 bytes per line>
--//關閉資料庫後增加+2.
$ seq 652 | tr ',' '\n' | xargs -IQ echo -e 'set dba 101,Q\nfind /x e68e top' | rlbbed | grep -C2 "\-\-\--"
BBED> File: /mnt/ramdisk/book/control01.ctl (101)
Block: 1 Offsets: 40 to 103 Dba:0x19400001
------------------------------------------------------------------------------------------------------------------------------------------------
e68e0000 8c020000 00400000 00000100 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 5e89c659 ae1e7135
$ seq 652 | tr ',' '\n' | xargs -IQ echo -e 'set dba 101,Q\nfind /x e48e top' | rlbbed | grep -C2 "\-\-\--"
BBED> File: /mnt/ramdisk/book/control01.ctl (101)
Block: 15 Offsets: 8 to 71 Dba:0x1940000f
------------------------------------------------------------------------------------------------------------------------------------------------
e48e0000 ffff0104 2ce80000 00040000 00000000 00000004 06440008 000400e3 00000000 00000002 00000000 00000000 008ddae0 15000000 00000000 00000000
--
BBED> File: /mnt/ramdisk/book/control01.ctl (101)
Block: 17 Offsets: 8 to 71 Dba:0x19400011
------------------------------------------------------------------------------------------------------------------------------------------------
e48e0000 ffff0104 10de0000 00000000 00000000 ae1e7135 424f4f4b 00000000 00000000 08024000 01404010 00000000 00000000 06200e00 00000000 b01e7135
--
BBED> File: /mnt/ramdisk/book/control01.ctl (101)
Block: 281 Offsets: 8 to 71 Dba:0x19400119
------------------------------------------------------------------------------------------------------------------------------------------------
e48e0000 ffff0104 9adc0000 0dff0200 e1070000 00000000 00000000 02000000 02000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
--//視乎在別的位置備份原來的控制檔案seq資訊.僅僅是猜測.因為前面查詢沒找到.
--//我嘗試再次mount查詢該位置.
SYS@book> select CONTROLFILE_SEQUENCE# from v$database;
CONTROLFILE_SEQUENCE#
---------------------
36587
--//36587 = 0x8eeb
$ seq 652 | tr ',' '\n' | xargs -IQ echo -e 'set dba 101,Q\nfind /x eb8e top' | rlbbed | grep -C2 "\-\-\--"
BBED> File: /mnt/ramdisk/book/control01.ctl (101)
Block: 1 Offsets: 40 to 103 Dba:0x19400001
------------------------------------------------------------------------------------------------------------------------------------------------
eb8e0000 8c020000 00400000 00000100 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 12d1c659 ae1e7135
$ seq 652 | tr ',' '\n' | xargs -IQ echo -e 'set dba 101,Q\nfind /x ea8e top' | rlbbed | grep -C2 "\-\-\--"
BBED> File: /mnt/ramdisk/book/control01.ctl (101)
Block: 15 Offsets: 8 to 71 Dba:0x1940000f
------------------------------------------------------------------------------------------------------------------------------------------------
ea8e0000 ffff0104 2ca00000 00040000 00000000 00000004 06440008 000c00a3 04000000 00000002 00000000 04000000 008ddae0 15000000 00000000 00000000
--
BBED> File: /mnt/ramdisk/book/control01.ctl (101)
Block: 17 Offsets: 8 to 71 Dba:0x19400011
------------------------------------------------------------------------------------------------------------------------------------------------
ea8e0000 ffff0104 10de0000 00000000 00000000 ae1e7135 424f4f4b 00000000 00000000 08024000 01404010 00000000 00000000 06200e00 00000000 b01e7135
--
BBED> File: /mnt/ramdisk/book/control01.ctl (101)
Block: 319 Offsets: 8 to 71 Dba:0x1940013f
------------------------------------------------------------------------------------------------------------------------------------------------
ea8e0000 ffff0104 e60b0000 626f6f6b 000060ff ffffffff 03b66b2a ff7f0000 38aeef7e 00000000 48000000 00000000 48000000 fd7f0000 10000000 00000000
--//應該可以肯定我的分析大致正確.開始修改看看.注意最好在關閉資料庫下進行.
BBED> dump /v dba 101,1 Offset 40 count 16
File: /mnt/ramdisk/book/control01.ctl (101)
Block: 1 Offsets: 40 to 55 Dba:0x19400001
-----------------------------------------------------------------------------------------------------------
ed8e0000 8c020000 00400000 00000100 l .........@......
<32 bytes per line>
--//修改命令如下:
modify /x ff8e dba 101,1 offset 40
sum apply dba 101,1
modify /x ff8e dba 102,1 offset 40
sum apply dba 102,1
--//注意另外的控制檔案也要修改:
BBED> modify /x ff8e dba 101,1 offset 40
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /mnt/ramdisk/book/control01.ctl (101)
Block: 1 Offsets: 40 to 55 Dba:0x19400001
------------------------------------------------------------------------------------------------------------------------------------------------
ff8e0000 8c020000 00400000 00000100
<64 bytes per line>
BBED> sum apply dba 101,1
Check value for File 101, Block 1:
current = 0x8883, required = 0x8883
BBED> modify /x ff8e dba 102,1 offset 40
File: /mnt/ramdisk/book/control02.ctl (102)
Block: 1 Offsets: 40 to 55 Dba:0x19800001
------------------------------------------------------------------------------------------------------------------------------------------------
ff8e0000 8c020000 00400000 00000100
<64 bytes per line>
BBED> sum apply dba 102,1
Check value for File 102, Block 1:
current = 0x8883, required = 0x8883
--//0x8eff = 36607,驗證看看:
SYS@book> startup mount
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
SYS@book> select CONTROLFILE_SEQUENCE# from v$database;
CONTROLFILE_SEQUENCE#
---------------------
36612
--//36612 = 0x8f04,即使在mount狀態也增加一點點.
BBED> dump /v dba 101,1 Offset 40 count 16
File: /mnt/ramdisk/book/control01.ctl (101)
Block: 1 Offsets: 40 to 55 Dba:0x19400001
-----------------------------------------------------------------------------------------------------------
048f0000 8c020000 00400000 00000100 l .........@......
<32 bytes per line>
--//說明修改位置正確.
6.繼續增大步幅:
--//注意修改前最好關閉資料庫進行.
BBED> modify /x 8affffff dba 101,1 offset 40
BBED-00209: invalid number (8affffff)
--//有一個小技巧就是如果修改4個位元組的話,第一個字元要小於0x8,,不然報錯.
--//修改命令如下:
modify /x 7fffffff dba 101,1 offset 40
sum apply dba 101,1
modify /x 7fffffff dba 102,1 offset 40
sum apply dba 102,1
--//相當於控制檔案seq = 0xffffff7f = 4294967167.
--//0xff-0x7f = 128
SYS@book> startup mount
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
SYS@book> select CONTROLFILE_SEQUENCE# from v$database;
CONTROLFILE_SEQUENCE#
---------------------
4294967172
--//4294967172= 0xffffff84,^_^現在已經逼近消耗枯竭狀態了.看看是否可以開啟,該是見證奇蹟的時刻...
SYS@book> alter database open ;
Database altered.
SYS@book> alter system checkpoint ;
System altered.
SYS@book> select CONTROLFILE_SEQUENCE# from v$database;
CONTROLFILE_SEQUENCE#
---------------------
4294967181
SYS@book> alter system checkpoint ;
System altered.
SYS@book> select CONTROLFILE_SEQUENCE# from v$database;
CONTROLFILE_SEQUENCE#
---------------------
4294967182
--//每次checkpoint僅僅增加1. 0xffffffff = 4294967295
SYS@book> alter system switch logfile;
System altered.
--//執行多次,加快增加....省略....
SYS@book> alter system switch logfile;
System altered.
..
SYS@book> alter system checkpoint ;
System altered.
SYS@book> select CONTROLFILE_SEQUENCE# from v$database;
CONTROLFILE_SEQUENCE#
---------------------
4294967295
--//4294967295= 0xffffffff,在往前呢?
SYS@book> alter system checkpoint ;
System altered.
SYS@book> select CONTROLFILE_SEQUENCE# from v$database;
--//掛起..
--//alert.log報如下錯誤.
Wed Feb 24 17:14:01 2021
Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_arc1_29847.trc:
ORA-00202: control file: '/mnt/ramdisk/book/control01.ctl'
Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_arc1_29847.trc (incident=3792203):
ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-00202: control file: '/mnt/ramdisk/book/control01.ctl'
Incident details in: /u01/app/oracle/diag/rdbms/book/book/incident/incdir_3792203/book_arc1_29847_i3792203.trc
Wed Feb 24 17:14:02 2021
Sweep [inc][3792203]: completed
Sweep [inc][3792195]: completed
Sweep [inc2][3792203]: completed
Sweep [inc2][3792195]: completed
Dumping diagnostic data in directory=[cdmp_20210224171402], requested by (instance=1, osid=29847 (ARC1)), summary=[incident=3792203].
BBED> dump /v dba 101,1 Offset 40 count 16
File: /mnt/ramdisk/book/control01.ctl (101)
Block: 1 Offsets: 40 to 55 Dba:0x19400001
-----------------------------------------------------------------------------------------------------------
00000000 8c020000 00400000 00000100 l .........@......
<32 bytes per line>
--//全部為0.補充說明我做了多次,如果seq跳躍很快,資料庫直接crash,我上面的測試到4294967295時換成了alter system checkpoint ;.
$ echo 15,17,281,319 | tr ',' '\n' | xargs -IQ echo -e 'set dba 101,Q\ndump /v offset 8 count 8' | rlbbed | grep -C2 "\-\-"
BBED> File: /mnt/ramdisk/book/control01.ctl (101)
Block: 15 Offsets: 8 to 15 Dba:0x1940000f
-----------------------------------------------------------------------------------------------------------
ffffffff ffff0104 l ........
--
BBED> File: /mnt/ramdisk/book/control01.ctl (101)
Block: 17 Offsets: 8 to 15 Dba:0x19400011
-----------------------------------------------------------------------------------------------------------
ffffffff ffff0104 l ........
--
BBED> File: /mnt/ramdisk/book/control01.ctl (101)
Block: 281 Offsets: 8 to 15 Dba:0x19400119
-----------------------------------------------------------------------------------------------------------
85ffffff ffff0104 l ........
--
BBED> File: /mnt/ramdisk/book/control01.ctl (101)
Block: 319 Offsets: 8 to 15 Dba:0x1940013f
-----------------------------------------------------------------------------------------------------------
83ffffff ffff0104 l ........
--//驗證我的推測.這裡也記錄seq資訊.
$ seq 501 503 | xargs -IQ echo "dump /v dba Q,1 offset 36 count 8" | rlbbed | grep 00900100
f8ffffff 00900100 l ........
fcffffff 00900100 l ........
fcffffff 00900100 l ........
--//現在資料庫依舊可以登入,但是一些查詢會hang住,比如查詢v$database檢視.
SYS@book> shutdown immediate;
ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
ORA-00202: control file: '/mnt/ramdisk/book/control01.ctl'
SYS@book> alter system checkpoint ;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 30040
Session ID: 114 Serial number: 3
--//alert.log記錄如下:
Wed Feb 24 17:27:02 2021
Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ckpt_29816.trc:
ORA-00202: control file: '/mnt/ramdisk/book/control01.ctl'
Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ckpt_29816.trc (incident=3792131):
ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
ORA-00202: control file: '/mnt/ramdisk/book/control01.ctl'
Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ckpt_29816.trc:
ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
ORA-00202: control file: '/mnt/ramdisk/book/control01.ctl'
CKPT (ospid: 29816): terminating the instance due to error 227
Wed Feb 24 17:27:03 2021
System state dump requested by (instance=1, osid=29816 (CKPT)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/book/book/trace/book_diag_29800_20210224172703.trc
Dumping diagnostic data in directory=[cdmp_20210224172703], requested by (instance=1, osid=29816 (CKPT)), summary=[abnormal instance termination].
Instance terminated by CKPT, pid = 29816
7.嘗試恢復:
SYS@book> startup mount
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-00202: control file: '/mnt/ramdisk/book/control01.ctl'
SYS@book> select CONTROLFILE_SEQUENCE# from v$database;
select CONTROLFILE_SEQUENCE# from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
--//alert.log
ALTER DATABASE MOUNT
Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_30094.trc:
ORA-00202: control file: '/mnt/ramdisk/book/control01.ctl'
Wed Feb 24 17:29:02 2021
Sweep [inc][3792131]: completed
Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_30094.trc (incident=3793788):
ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-// 我沒有遇到原連結的提示,這個提示有點昏.
ORA-00202: control file: '/mnt/ramdisk/book/control01.ctl'
Incident details in: /u01/app/oracle/diag/rdbms/book/book/incident/incdir_3793788/book_ora_30094_i3793788.trc
Dumping diagnostic data in directory=[cdmp_20210224172902], requested by (instance=1, osid=30094), summary=[incident=3793788].
ORA-227 signalled during: ALTER DATABASE MOUNT...
$ dbv file=/mnt/ramdisk/book/control01.ctl BLOCKSIZE=16384
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Feb 24 17:29:52 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/control01.ctl
DBVERIFY - Verification complete
Total Pages Examined : 652
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 147
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 505
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 4294967295 (65535.4294967295)
--//ok,但是注意後面那行.Highest block SCN : 4294967295 (65535.4294967295)
--//感覺這個可以稱為控制檔案的scn號. 4294967295 = 0xffffffff ,65535 = 0xffff
$ seq 6 | xargs -IQ echo "p dba Q,1 kcvfh.kcvfhhdr.kccfhcsq" | rlbbed| grep kccfhcsq
BBED> ub4 kccfhcsq @40 0xffffffff
BBED> ub4 kccfhcsq @40 0xffffffff
BBED> ub4 kccfhcsq @40 0xffffffff
BBED> ub4 kccfhcsq @40 0xffffffff
BBED> ub4 kccfhcsq @40 0xffffffff
BBED> ub4 kccfhcsq @40 0xffffffff
--//資料檔案的kccfhcsq全部是0xffffffff.
BBED> dump /v dba 101,1 offset 40 count 4
File: /mnt/ramdisk/book/control01.ctl (101)
Block: 1 Offsets: 40 to 43 Dba:0x19400001
-----------------------------------------------------------------------------------------------------------
00000000 l ....
<32 bytes per line>
--//執行如下:
modify /x 7fffffff dba 101,1 offset 40
modify /x 7fffffff dba 102,1 offset 40
modify /x ff dba 101,1 offset 40
modify /x ff dba 102,1 offset 40
sum apply dba 101,1
sum apply dba 102,1
--//重啟不行.
modify /x 7fffffff dba 101,1 offset 40
modify /x 7fffffff dba 102,1 offset 40
sum apply dba 101,1
sum apply dba 102,1
SYS@book> startup mount
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
ORA-00600: internal error code, arguments: [2131], [9], [8], [], [], [], [], [], [], [], [], []
--//不行.
7.如何恢復呢?
--//連結介紹一種方式就是重建控制檔案,使用resetlogs重建.
--//原始連結的測試使用resetlogs重建的控制檔案,我做了OK,不再貼出,方法比較簡單.
--//我想給自己增加一點點難度,就是使用noresetlogs開啟,因為這樣重建的控制檔案要讀取redo,資料檔案重新
--//回填一些資訊,實際上resetlogs也類似,但是noresetlogs回填的控制檔案seq很大,一樣打不開資料庫.
--//也就是必須提到我前面要修改的資料檔案以及redo檔案的幾個偏移位置.太長了,另外寫一篇blog.
8.補充說明:
--//前面我提到的rlbbed我建立的bash shell函式,你可以使用別名代替,效果一樣的.
$ export RLWRAP=$(which rlwrap)
$ type rlbbed
rlbbed is a function
rlbbed ()
{
cd /home/oracle/bbed;
$RLWRAP -s 9999 -c -r -i -f /usr/local/share/rlwrap/bbed $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=cmd.par
}
--//關於bbed配置看相關文件.
$ cat cmd.par
set count 64
set width 160
$ cat bbed.par
blocksize=8192
listfile=$HOME/bbed/filelist.txt
mode=edit
PASSWORD=blockedit
SPOOL=Y
--//filelist.txt檔案透過select file#||' '||name c100 from v$dbfile order by file#;生成.我增加了控制檔案以及redo檔案.
--//還有臨時檔案.
$ cat filelist.txt | grep -v "#"
4 /mnt/ramdisk/book/users01.dbf
1 /mnt/ramdisk/book/system01.dbf
2 /mnt/ramdisk/book/sysaux01.dbf
3 /mnt/ramdisk/book/undotbs01.dbf
5 /mnt/ramdisk/book/example01.dbf
6 /mnt/ramdisk/book/tea01.dbf
101 /mnt/ramdisk/book/control01.ctl
102 /mnt/ramdisk/book/control02.ctl
201 /mnt/ramdisk/book/temp01.dbf
501 /mnt/ramdisk/book/redo01.log
502 /mnt/ramdisk/book/redo02.log
503 /mnt/ramdisk/book/redo03.log
504 /mnt/ramdisk/book/redostb01.log
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2759213/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210225]控制檔案序列號滿的恢復.txt
- [20210819]給檔案內容編行號.txt
- [20201126]檔案相對號與絕對號問題.txt
- [20210224]fetch r=0算邏輯讀嗎.txt
- XLOG段檔案跳號現象分析
- php如何上傳txt檔案，並且讀取txt檔案PHP
- [20200318]crontab檔案格式中的%.txt
- Oracle 控制檔案Oracle
- SQLSERVER匯出TXT文字檔案，ORACLE SQL LOADER匯入TXT文字檔案SQLServerOracle
- chm檔案怎麼轉換成TXT格式？chm檔案快速轉化成TXT格式的方法
- python處理txt檔案Python
- pandas操作txt檔案的方便之處
- delphi清理txt檔案多餘的空格
- 2.6.4 指定控制檔案
- txt是什麼格式的檔案 txt格式怎麼弄出來
- 【DBA】Oracle 中DBA地址的檔案號使用的是相對檔案號(RELATIVE_FNO)Oracle
- [20190329]grep與管道檔案.txt
- [20191009]檔案頭fuzzy.txt
- [20201112]tmpwatch 刪除檔案.txt
- [20190530]oracle Audit檔案管理.txtOracle
- [20220427]ls 忽略特定檔案.txt
- LIUNUX如何擷取txt檔案中的內容，並建立新檔案UX
- Python提取文字檔案（.txt）資料的方法Python
- [20201103]lsof顯示link＝0的檔案.txt
- [20190410]dg建立臨時表檔案資料檔案.txt
- ubunt下boot檔案空間滿boot
- 得到txt空白行的行數、將txt檔案的空行刪除和刪除csv檔案中指定的行
- Mac怎麼建立txt檔案？如何設定新建txt的快捷鍵？Mac
- 【RMAN】Oracle中如何備份控制檔案？備份控制檔案的方式有哪幾種？Oracle
- [20230508]crack oracle執行檔案.txtOracle
- [20191104]sqlplus 管道檔案 過濾.txtSQL
- requirements.txt 檔案宣告依賴UIREM
- java如何追加寫入txt檔案Java
- 與控制檔案有關的恢復
- APK 檔案分析APK
- hex檔案分析
- 電腦TXT檔案怎麼顯示行數? 電腦TXT檔案行數檢視方法
- 讀取txt檔案的簡易演算法演算法