--(南宋)陆游 《冬夜读书示子聿》
前 言
01
Oracle数据库启动过程会读取bootstrap$表中创建数据字典的DDL语句,然后成功打开数据库。那么当出现bootstrap$表损坏或丢失的情况,我们又该如何做好应急处置。今天我想跟大家先分享下如何运用bbed工具恢复bootstrap$表。想深入学习bbed工具的朋友,一定要动用多做实验!
实验环境介绍
02
数据库版本:Oracle 12.1.0.2.0 单机版
BBED版本:BBED 2.0.0.0.0
操作系统:Red Hat Enterprise Linux Server 6.5
BBED软件下载方式:发送“bbed软件包”关键字获取软件包
备份重于一切
03
实验前,一定要保证数据库至少有一份全量备份!
1、使用backup as copy命令对数据文件1进行拷贝备份
RMAN> backup as copy datafile 1 format '/home/oracle/files/system_rman_%U';
2、实验过程会出现各种异常情况,特殊情况下,还是需要通过全备恢复的
RMAN> backup database format '/home/oracle/files/full_%U';
查看表数据分布情况
04
查看表数据分布情况
1、数据分布在1号文件的521、522、523三个数据块
SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) rfno,
dbms_rowid.rowid_block_number(rowid) blocknum
from bootstrap$ order by 2;
RFNO BLOCKNUM
---------- ----------
1 521
1 522
1 523
2、表存储在1号文件的第1个区,从520号数据块开始,占用8个块,共65536字节(8k*8个)
SQL> select EXTENT_ID,FILE_ID,BLOCK_ID,BYTES,BLOCKS
from dba_extents where segment_name='BOOTSTRAP$';
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ---------- ----------
0 1 520 65536 8
模拟数据丢失
05
模拟数据丢失
1、使用delete语句删除数据(非法操作)
SQL> create table bootstrap_bak as select * from bootstrap$;
Table created.
2、查看备份表的数据分布情况
SQL> select EXTENT_ID,FILE_ID,BLOCK_ID,BYTES,BLOCKS from dba_extents where segment_name='BOOTSTRAP_BAK';
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ---------- ----------
0 1 99656 65536 8
3、删除数据
SQL> delete from bootstrap$;
SQL> commit;
启动过程异常分析
06
1、启动数据库出现ORA-03113错误,接着实例出现崩溃。
1、启动报错
SYS@PROD4> startup
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 2926320 bytes
Variable Size 440404240 bytes
Database Buffers 75497472 bytes
Redo Buffers 5459968 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 58876
Session ID: 237 Serial number: 53232
2、alert_sid.log日志出现ORA-07445错误
Thu Jun 06 06:55:53 2024
ARC1 started with pid=27, OS id=68790
Thu Jun 06 06:55:53 2024
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0xCD2175A, lmebucp()+26] [flags: 0x0, count: 1]
Starting background process ARC2
Starting background process ARC3
Thu Jun 06 06:55:53 2024
ARC2 started with pid=29, OS id=68794
Errors in file u01/app/oracle/diag/rdbms/prod4/PROD4/trace/PROD4_ora_68784.trc (incident=129671):
ORA-07445: exception encountered: core dump [lmebucp()+26] [SIGSEGV] [ADDR:0x0] [PC:0xCD2175A] [Address not mapped to object] []
Incident details in: u01/app/oracle/diag/rdbms/prod4/PROD4/incident/incdir_129671/PROD4_ora_68784_i129671.trc
2、通过10046查看启动过程,从日志可以看出无法获取FETCH数据字典DDL语句后出现异常(因为数据被人为删除掉)。
1、开启10046事件
SQL> STARTUP MOUNT;
SQL> ORADEBUG SETMYPID
SQL> ORADEBUG TRACEFILE_NAME
SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
SQL> ALTER DATABASE OPEN;
。。。数据库直接abort,后面步骤不用执行
SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
SQL> SHOW PARAMETER USER_DUMP_DEST
2、正常情况下,打印的trace日志会出现60条FETCH记录。但此处获取第1条就出现实例异常。=====================
PARSING IN CURSOR #140187415311440 len=65 dep=1 uid=0 oct=3 lid=0 tim=376351389604 hv=1762642493 ad='6ec42e68' sqlid='aps3qh1nhzkjx'
select line#, sql_text from bootstrap$ where obj# not in (:1, :2)
【开始读取bootstrap$】
END OF STMT
PARSE #140187415311440:c=0,e=810,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=376351389603
。。。省略部分内容
EXEC #140187415311440:c=2999,e=2431,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=867914364,tim=376351392199
WAIT #140187415311440: nam='db file sequential read' ela= 20 file#=1 block#=520 blocks=1 obj#=59 tim=376351392355
WAIT #140187415311440: nam='db file scattered read' ela= 36 file#=1 block#=521 blocks=3 obj#=59 tim=376351393102
FETCH #140187415311440:c=2000,e=1567,p=4,cr=5,cu=0,mis=0,r=0,dep=1,og=4,plh=867914364,tim=376351393823
【正常情况下,会出现60条FETCH记录。但此处获取第1条就出现异常】
STAT #140187415311440 id=1 cnt=0 pid=0 pos=1 obj=59 op='TABLE ACCESS FULL BOOTSTRAP$ (cr=5 pr=4 pw=0 time=1577 us)'
WAIT #140187415311440: nam='latch: shared pool' ela= 628 address=1611722448 number=453 tries=0 obj#=59 tim=376351394520
*** 2024-06-06 06:00:57.834
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0xCD2175A, lmebucp()+26] [flags: 0x0, count: 1]
Incident 122471 created, dump file: /u01/app/oracle/diag/rdbms/prod4/PROD4/incident/incdir_122471/PROD4_ora_62412_i122471.trc
ORA-07445: exception encountered: core dump [lmebucp()+26] [SIGSEGV] [ADDR:0x0] [PC:0xCD2175A] [Address not mapped to object] []
ssexhd: crashing the process...【实例崩溃】
Shadow_Core_Dump = partial
ksdbgcra: writing core file to directory '/u01/app/oracle/diag/rdbms/prod4/PROD4/cdump'
bbed恢复表数据
07
bbed恢复表数据
1、如果没有bbed环境的朋友,可以将依赖文件拷贝到指定目录(发送“bbed软件包”关键字获取软件包)
$ cp ssbbded.o $ORACLE_HOME/rdbms/lib
$ cp sbbdpt.o $ORACLE_HOME/rdbms/lib
$ cp bbedus.msb $ORACLE_HOME/rdbms/mesg
$ cp bbedus.msg $ORACLE_HOME/rdbms/mesg
2、编译程序
当执行make命令时,make命令就在指定目录下找Makefile文件,根据文件里面的执行规则,编译程序。
$ make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed
3、使用BBED
根据《DBA实验手册第1讲 运用bbed工具和SQL语句学习bootstrap$表存储信息》,bootstrap$数据存储在521、522、523三个数据块当中,因此我们直接从备份文件(或者同版本的system.dbf文件)拷贝对应的数据块过来。该场景对应的备份文件为“/home/oracle/files/system_data_D-PROD4_I-1644960615_TS-SYS”
$ bbed parfile=bbed.par
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /u01/app/oracle/oradata/PROD4/system01.dbf 103680
2 /home/oracle/files/system_data_D-PROD4_I-1644960615_TS-SYS 0
BBED> copy file 2 block 521 to file 1 block 521
BBED> copy file 2 block 522 to file 1 block 522
BBED> copy file 2 block 523 to file 1 block 523
BBED> sum apply
4、通过上述恢复操作,就可以成功打开数据库。
SQL> alter database open;
Database altered.
SQL> select count(*) from bootstrap$;
COUNT(*)
----------
60