溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“數據庫中3*2*2*3種隨機的特殊恢復方法是什么”，在日常操作中，相信很多人在數據庫中3*2*2*3種隨機的特殊恢復方法是什么問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”數據庫中3*2*2*3種隨機的特殊恢復方法是什么”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！
首先，獲取system文件的字符集，數據庫名，然后創建參數文件，重建控制文件，這里就不過多介紹，話不多說，先嘗試啟動數據庫。
SQL> startup nomount pfile='/gauss/init.ora';
ORACLE instance started.
Total System Global Area 396668928 bytes
Fixed Size 2253624 bytes
Variable Size 125832392 bytes
Database Buffers 264241152 bytes
Redo Buffers 4341760 bytes
SQL> @cf
Control file created.
ORA-00279: change 4936537 generated at 04/21/2020 00:03:57 needed for thread 1
ORA-00289: suggestion :
/guass/app/oracle/product/11.2.0/db_1/dbs/arch2_41_1033397865.dbf
ORA-00280: change 4936537 for thread 1 is in sequence #41
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 338)--壞塊
ORA-01110: data file 1: '/gauss/system.dbf'
Process ID: 32245
Session ID: 1 Serial number: 3
數據啟動報file 1, block 338存在壞塊的錯誤，我們來查一下這個塊對應的對象。
TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME
------------------ --------------- ------------- --------------
SYSTEM INDEX SYS I_OBJ1
這個對象是I_OBJ1，I_OBJ1是什么？
SQL> select * from bootstrap$ where SQL_TEXT like '%I_OBJ1%' order by LINE#;
LINE# OBJ# SQL_TEXT
---------- ---------- --------------------------------------------------------------------------------
36 36 CREATE UNIQUE INDEX I_OBJ1 ON OBJ$(OBJ#,OWNER#,TYPE#) PCTFREE 10 INITRANS 2 MAXT
RANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PC
TINCREASE 0 OBJNO 36 EXTENTS (FILE 1 BLOCK 336))
I_OBJ1是核心基表OBJ$的一個索引，當普通的索引出現壞塊，我們可以通過重建去處理，但是OBJ#<59 的索引出現壞塊，是不能夠通過rebuild的方式處理。數據庫open的情況下可以通過swap的方式處理，不過這里我們數據庫都沒打開。
首先我們先來介紹下數據庫啟動的過程：
1、在system 表空間的第一個數據文件的特定偏移位置，找到root dba變量
2、root dba變量的值就是指向sys.bootstrap$表的物理位置的指針
3、sys.bootstrap$表中記錄了數據庫基礎字典表的物理位置
4、基礎字典表內記錄了用戶段段頭的物理存儲位置
下一步就是要通過某個段的segment header block從數據文件中直接讀出表的數據。
在sys.bootstrap$表中記錄的數據字典表的create語句，還有一部分是沒有直接指定存儲位置的，比如簇成員。
那么怎么去爬過第一個雷？這里有兩個方案：
BBED> copy file 6 block 338 to file 1 block 338
但是如果I_OBJ1存在大量的壞塊情況下，cp的效率比較低。
方法二
刪除I_OBJ1，但是我們這里不是刪除IND$里的I_OBJ1索引，再次重啟的時候又創建了，這里我們需要刪除sys.bootstrap里的索引。
BBED> x /rnnc *kdbr[1]
rowdata[3681] @7322
-------------
flag@7322: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7323: 0x01
cols@7324: 3
col 0[2] @7325: 36
col 1[2] @7328: 36
col 2[208] @7331: CREATE UNIQUE INDEX I_OBJ1 ON OBJ$(OBJ#,OWNER#,TYPE#) P
CTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTE
NTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 36 EXTENTS (FILE 1 BLOCK 33
6))
BBED> assign /x offset 7322 =0x3c --刪除索引
ub1 rowdata[0] @7322 0x3c
BBED> x /rnnc dba 1,523 *kdbr[1]
rowdata[3681] @7322
-------------
flag@7322: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@7323: 0x01
cols@7324: 0 --已經刪除
BBED> sum apply
Check value for File 1, Block 523:
current = 0x7e06, required = 0x7e06
方法三
[ora11@zdata bin]$ strings $ORACLE_HOME/bin/oracle |wc -l1341571
11.2.0.4的執行文件包含1341571個函數，可以通過修改oracle執行文件指定<hint +full>，繞過索引I_OBJ1來處理。
通過上面兩種方式排了第一個雷，我們順利到達第二關。趕緊嘗試打開，看看又會報什么錯。
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 27 with name
"$" too small
Process ID: 17397
Session ID: 1 Serial number: 5
WAIT #140193663907536: nam='db file sequential read' ela= 788 file#=1 block#=241 blocks=1 obj#=18 tim=1587333670688800
=====================
PARSING IN CURSOR #140193661586512 len=142 dep=2 uid=0 oct=3 lid=0 tim=1587333670689091 hv=361892850 ad='775596a0' sqlid='7bd391hat42zk'
select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1
END OF STMT
PARSE #140193661586512:c=242,e=243,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=3,plh=0,tim=1587333670689090
BINDS #140193661586512:
BBED> p ktbbh
.........省略
ub2 kxidusn @44 0x001b
ub2 kxidslt @46 0x000b
ub4 kxidsqn @48 0x00000186
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00c00a97
ub2 kubaseq @56 0x0219
ub1 kubarec @58 0x26
ub2 ktbitflg @60 0x2001 (KTBFUPB)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0xffffffff --這里被改成0xffffffff
這里低位scn都改成最大值了，難怪怎么poke scn都沒效果。。這個雷特么陰險，太壞了。
好了我們知道問題所在了，就是ktbitbas被人工改成0xffffffff，那么恢復方案也有兩種。
方法一
修改scn，我們手動修改ktbitbas的值。
BBED> assign ktbbhitl[0].ktbitbas=0x004b531fub4 ktbitbas @64 0x004b531f
方法二
既然低位已經最大了，再怎么推也不會超過0xffffffff，那么我們就嘗試poke推進高位scn。
SQL> oradebug setmypid
SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
SQL> oradebug poke 0x06001AE74 4 0x50
BEFORE: [06001AE70, 06001AE78) = 00000000 00000000
AFTER: [06001AE70, 06001AE78) = 004B60E0 00000000
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01173: data dictionary indicates missing data file from system tablespace
Process ID: 26639
Session ID: 1 Serial number: 3
看到這個報錯，說明順利進入第三關。
方法一
通過一鍵腳本設置*._corrupted_rollback_segments隱患參數，屏蔽回滾段。
*._corrupted_rollback_segments='_SYSSMU28_79026890$','_SYSSMU24_100127047$','_SYSSMU28_79026890$','_SYSSMU21_1449495591$','_SYSSMU24_100127047$','_SYSSMU21_1449495591$','_SYSSMU30_493042799$','_SYSSMU30_493042799$','_SYSSMU23_1725104698$','_SYSSMU23_1725104698$','_SYSSMU22_3628056578$','_SYSSMU22_3628056578$','_SYSSMU25_3360715651$','_SYSSMU22_3628056578$','_SYSSMU25_3360715651$','_SYSSMU22_36280565.......
方法二
SQL> select OWNER,SEGMENT_NAME,FILE_ID,BLOCK_ID from dba_extents where segment_name='UNDO$';
OWNER SEGMENT_NAME FILE_ID BLOCK_ID
---------- --------------- ---------- ----------
SYS UNDO$ 1 224
BBED> p ktetb
struct ktetb[0], 8 bytes @108
ub4 ktetbdba @108 0x004000e1--extent的首地址
ub4 ktetbnbk @112 0x00000007--連續7個塊
BBED> x /rnc *kdbr[0
rowdata[2337] @8146
-------------
flag@8146: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8147: 0x00
cols@8148: 17
col 0[1] @8149: 0
col 1[6] @8151: SYSTEM
col 2[1] @8158: .
col 3[2] @8160: ..
......省略
BBED> x /rnc *kdbr[1
rowdata[2267] @8076
-------------
flag@8076: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8077: 0x00
cols@8078: 17
col 0[2] @8079: 1
col 1[19] @8082: _SYSSMU1_770609302$ --需要刪除
col 2[2] @8102: ..
col 3[2] @8105: ..
col 4[3] @8108: ...
......省略
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 26280
Session ID: 1 Serial number: 3
SQL> oradebug event 10046 trace name context forever,level 12;
SQL> alter database open resetlogs;
alter database open resetlogs
--后臺alert的報錯
SMON: enabling cache recovery
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xD0C5CD7] [PC:0x97DF62E, kgebse()+776] [flags: 0x2, count: 2]
Fri Apr 24 06:42:13 2020
PMON (ospid: 19501): terminating the instance due to error 397
--file#=1 block#=140需要關注
WAIT #140452738872120: nam='db file sequential read' ela= 670 file#=1 block#=140 blocks=1 obj#=0 tim=1587681730297305
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xD0C5CD7] [PC:0x97E0BBA, kgegpa()+40] [flags: 0x0, count: 1]
DDE previous invocation failed before phase II
DDE was called in a 'No Invocation Mode'
----- Start Diag Diagnostic Dump -----
Diag diagnostic dump is performed due to an error in the diagfw code during error handling.
DDE is switched to protected mode during the diagnostic dump to prevent recursive errors in the error hadnling code.
TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME-------------------- ------------------ ---------- ---------------SYSTEM ROLLBACK SYS SYSTEM
BBED> p dba 1,140 ktubh
struct ktubh, 22 bytes @20
struct ktubhxid, 8 bytes @20
ub2 kxidusn @20 0x0000
ub2 kxidslt @22 0x003b
ub4 kxidsqn @24 0x0000002b
ub2 ktubhseq @28 0x0025 --seq 是0x0025
ub1 ktubhcnt @30 0x03
ub1 ktubhirb @31 0x03
ub1 ktubhicl @32 0x00
ub1 ktubhflg @33 0x00
BBED> p dba 1,128 ktuxc
struct ktuxc, 104 bytes @4148
struct ktuxcscn, 8 bytes @4148
ub4 kscnbas @4148 0x004996f7
ub2 kscnwrp @4152 0x0000
.....省略
struct ktuxcfbp[0], 12 bytes @4192
struct ktufbuba, 8 bytes @4192
ub4 kubadba @4192 0x0040008c
ub2 kubaseq @4196 0x0030 --seq 是0x0030
ub1 kubarec @4198 0x03
sb2 ktufbext @4200 1
sb2 ktufbspc @4202 7340
這里感覺是人為把128塊的offset 4196由0x0025改成了0x0030
assign dba 1,128 4196=0x0025ub2 kubaseq @4196 0x0025
modify /x 00 offset 4168BBED> modify /x 000000 offset 4192
方法三
通過strace跟蹤，得到如下的trace。
01:13:27 write(14, "[32]: ktuiup []", 15) = 15 <0.000012> --ktuiup
01:13:27 write(15, "!gF\n", 4) = 4 <0.000011>
01:13:27 lseek(14, 0, SEEK_CUR) = 3425947 <0.000009>
01:13:27 write(14, "\n", 1) = 1 <0.000011>
01:13:27 write(15, "!A1\n", 4) = 4 <0.000012>
01:13:27 lseek(14, 0, SEEK_CUR) = 3425948 <0.000010>
01:13:27 write(14, "[33]: ktuini []", 15) = 15 <0.000012> ------注意這里ktuini函數
01:13:27 write(15, "!gF\n", 4) = 4 <0.000011>
01:13:27 lseek(14, 0, SEEK_CUR) = 3425963 <0.000009>
01:13:27 write(14, "\n", 1) = 1 <0.000012>
01:13:27 write(15, "!A1\n", 4) = 4 <0.000012>
01:13:27 lseek(14, 0, SEEK_CUR) = 3425964 <0.000010>
01:13:27 write(14, "[34]: adbdrv []", 15) = 15 <0.000011>
01:13:27 write(15, "!gF\n", 4) = 4 <0.000012>
01:13:27 lseek(14, 0, SEEK_CUR) = 3425979 <0.000009>
01:13:27 write(14, "\n", 1) = 1 <0.000012>
01:13:27 write(15, "!A1\n", 4) = 4 <0.000011>
01:13:27 lseek(14, 0, SEEK_CUR) = 3425980 <0.000009>
01:13:27 write(14, "[35]: opiexe []", 15) = 15 <0.000012>
01:13:27 write(15, "!gF\n", 4) = 4 <0.000011>
01:13:27 lseek(14, 0, SEEK_CUR) = 3425995 <0.000009>
01:13:27 write(14, "\n", 1) = 1 <0.000012>
--session 1
(gdb) break ktuini
Breakpoint 1 at 0xf21352
(gdb) c
Continuing.
--session 2
導出數據
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
exp \'/ as sysdba \' file=/home/ora11/meta.dmp ROWS=n buffer=102400000
到此，關于“數據庫中3*2*2*3種隨機的特殊恢復方法是什么”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
