数据库中3*2*2*3种随机的特殊恢复方法是什么

这篇文章主要介绍“数据库中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: {=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#
首先我们先来介绍下数据库启动的过程：
1、在system 表空间的第一个数据文件的特定偏移位置，找到root dba变量
2、root dba变量的值就是指向sys.bootstrap$表的物理位置的指针
3、sys.bootstrap$表中记录了数据库基础字典表的物理位置
4、基础字典表内记录了用户段段头的物理存储位置
下一步就是要通过某个段的segment header block从数据文件中直接读出表的数据。
在sys.bootstrap$表中记录的数据字典表的create语句，还有一部分是没有直接指定存储位置的，比如簇成员。
那么怎么去爬过第一个雷？这里有两个方案：

但是如果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

方法三

11.2.0.4的执行文件包含1341571个函数，可以通过修改oracle执行文件指定，绕过索引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的值。

方法二

既然低位已经最大了，再怎么推也不会超过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隐患参数，屏蔽回滚段。

方法二
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@807免费主机域名6: 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.

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

01:13:27 write(14, “[32]: ktuiup []”, 15) = 15 –ktuiup
01:13:27 write(15, “!gFn”, 4) = 4
01:13:27 lseek(14, 0, SEEK_CUR) = 3425947
01:13:27 write(14, “n”, 1) = 1
01:13:27 write(15, “!A1n”, 4) = 4
01:13:27 lseek(14, 0, SEEK_CUR) = 3425948
01:13:27 write(14, “[33]: ktuini []”, 15) = 15 ——注意这里ktuini函数
01:13:27 write(15, “!gFn”, 4) = 4
01:13:27 lseek(14, 0, SEEK_CUR) = 3425963
01:13:27 write(14, “n”, 1) = 1
01:13:27 write(15, “!A1n”, 4) = 4
01:13:27 lseek(14, 0, SEEK_CUR) = 3425964
01:13:27 write(14, “[34]: adbdrv []”, 15) = 15
01:13:27 write(15, “!gFn”, 4) = 4
01:13:27 lseek(14, 0, SEEK_CUR) = 3425979
01:13:27 write(14, “n”, 1) = 1
01:13:27 write(15, “!A1n”, 4) = 4
01:13:27 lseek(14, 0, SEEK_CUR) = 3425980
01:13:27 write(14, “[35]: opiexe []”, 15) = 15
01:13:27 write(15, “!gFn”, 4) = 4
01:13:27 lseek(14, 0, SEEK_CUR) = 3425995
01:13:27 write(14, “n”, 1) = 1

到此，关于“数据库中3*2*2*3种随机的特殊恢复方法是什么”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注云技术网站，小编会继续努力为大家带来更多实用的文章！

相关推荐: MySQL5.7.18主从复制搭建一主一从的示例分析

这篇文章主要介绍了MySQL5.7.18主从复制搭建一主一从的示例分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。一、复制原理主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循…