oracle数据库热启动流程
1、oracle默认找system表空间file# 1文件的@96字节处，
该位置记录的rdba地址指向bootstrap$表
2、bootstrap$表记录着oracle必须的数据字典ddl语句
3、bootstrap$表按照line 列、sql_text ddl内容进行数据字典内存表的创建
4、line 列再根据obj# 列找到该内存对象在磁盘的数据存储并加载
当bootstrap$表内某个核心表的index损坏时，我们可以通过复制创建
新的bootstrap$img,损坏对象表及表索引的img对象，将img对象的obj#替换到
bootstra$img中，这样相当于在创建损坏对象的img时，数据创建了一份，
索引变相的重建了一次，让表和索引数据对应，将来新的bootstrap$img加载
新的磁盘img对象数据；同时为了数据字典的数据一致性，还必须将obj$表中
损坏对象的obj#指向新的img对象的obj#
重建bootstrap$记录的前60对象步骤
1、创建bootstrap$ 及损坏对象的镜像对象
--创建 bootstrap$ 的镜像表 BOOTSTRAP$IMG
CREATE TABLE "SYS"."BOOTSTRAP$IMG"
( "LINE#" NUMBER,
"OBJ#" NUMBER,
"SQL_TEXT" VARCHAR2(4000)
);
--创建 bootstrap$ 的第二张镜像表 BOOTSTRAP$TMPSTR
CREATE TABLE "SYS"."BOOTSTRAP$TMPSTR"
( "LINE#" NUMBER,
"OBJ#" NUMBER,
"SQL_TEXT" VARCHAR2(4000)
);
--创建 CON$ 的镜像表及索引 CON$IMG I_CON1_IMG I_CON2_IMG
CREATE TABLE "SYS"."CON$IMG"
( "OWNER#" NUMBER,
"NAME" VARCHAR2(128),
"CON#" NUMBER,
"SPARE1" NUMBER,
"SPARE2" NUMBER,
"SPARE3" NUMBER,
"SPARE4" VARCHAR2(1000),
"SPARE5" VARCHAR2(1000),
"SPARE6" DATE
);
CREATE UNIQUE INDEX "SYS"."I_CON1_IMG" ON "SYS"."CON$IMG" ("OWNER#", "NAME");
CREATE UNIQUE INDEX "SYS"."I_CON2_IMG" ON "SYS"."CON$IMG" ("CON#");
2、生成新的bootstrap中的sql_text(obj#、file#、block#)
原bootstrap$内，con$ i_con1 i_con2 bootstrap$定义(建议查询出来备份)
CREATE TABLE CON$("OWNER#" NUMBER NOT NULL,"NAME" VARCHAR2(128) NOT NULL,"CON#" NUMBER NOT NULL,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 28 EXTENTS (FILE 1 BLOCK 288))
CREATE UNIQUE INDEX I_CON1 ON CON$(OWNER#,NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 51 EXTENTS (FILE 1 BLOCK 456))
CREATE UNIQUE INDEX I_CON2 ON CON$(CON#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 52 EXTENTS (FILE 1 BLOCK 464))
CREATE TABLE BOOTSTRAP$("LINE#" NUMBER NOT NULL,"OBJ#" NUMBER NOT NULL,"SQL_TEXT" VARCHAR2(4000) NOT NULL) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 56K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 59 EXTENTS (FILE 1 BLOCK 520))
--生成需要替换对象的新的sql_text
declare
pl_objtxt varchar2(4000); /* bootstrap$.sql_text for the new obj */
pl_obj_num number; /* obj# of the new obj */
pl_line_num number; /* line# in bootstrap$ for the new obj */
/* Get Obj Number in OBJ$
Given the obj name and namespace, return the obj# in obj$.
*/
function get_obj_num(pl_objname varchar2, pl_nmspc number) return number
is
pl_obn number;
begin
select obj# into pl_obn from sys.obj$
where owner#=0 and name=pl_objname and namespace=pl_nmspc;
return pl_obn;
end;
/* Get Line Number in bootstrap$
Given the obj name and namespace, returns the line# in boostrap$. If the
obj doesn't exists, then return null.
*/
function get_line_num(pl_objname varchar2, pl_nmspc number) return number
is
pl_bln number;
begin
select b.line# into pl_bln
from sys.bootstrap$ b, sys.obj$ o
where o.owner# = 0
and o.name = pl_objname
and o.obj# = b.obj#
and o.namespace = pl_nmspc;
return pl_bln;
exception
when NO_DATA_FOUND then
return NULL;
end;
/* Storage text generation
The bootstrap$ sql_text requires the DDL to provide the storage
parameters. The following function will generate the storage
parameter for table creation and index creation, given the obj# as input.
*/
-- generate storage parameter
-- it requires some info from tab$/ind$, seg$, ts$
function gen_storage(pl_objnum number, pl_objtype varchar2) return varchar2
is
pl_text varchar2(4000);
pl_pctf number;
pl_pctused number;
pl_initrans number;
pl_maxtrans number;
pl_file_num number;
pl_block_num number;
pl_ts_num number;
pl_tab_num number;
pl_initial number;
pl_next number;
pl_minext number;
pl_maxext number;
pl_pctinc number;
pl_block_size number;
begin
if (pl_objtype = 'TABLE') then
-- info from tab$
select pctfree$, pctused$, initrans, maxtrans, file#, block#, ts#
into pl_pctf, pl_pctused, pl_initrans, pl_maxtrans,
pl_file_num, pl_block_num, pl_ts_num
from sys.tab$
where obj# = pl_objnum;
elsif (pl_objtype = 'CLUSTER TABLE') then
select tab#
into pl_tab_num
from sys.tab$
where obj# = pl_objnum;
elsif (pl_objtype = 'INDEX') then
-- info from ind$
select pctfree$, initrans, maxtrans, file#, block#, ts#
into pl_pctf, pl_initrans, pl_maxtrans,
pl_file_num, pl_block_num, pl_ts_num
from ind$ where obj# = pl_objnum;
end if;
if (pl_objtype != 'CLUSTER TABLE') then
-- info from seg$
select iniexts, minexts, maxexts, extsize, extpct
into pl_initial, pl_minext, pl_maxext, pl_next, pl_pctinc
from sys.seg$
where file# = pl_file_num
and block# = pl_block_num
and ts# = pl_ts_num;
-- info from ts$
select blocksize into pl_block_size from sys.ts$ where ts# = pl_ts_num;
pl_initial := pl_initial * pl_block_size;
pl_next := pl_next * pl_block_size;
end if;
if (pl_objtype = 'TABLE') then
-- generate the table storage text
pl_text := ' PCTFREE ' || pl_pctf || ' PCTUSED ' || pl_pctused ||
' INITRANS ' || pl_initrans || ' MAXTRANS '|| pl_maxtrans ||
' STORAGE ( INITIAL ' || pl_initial ||
' NEXT ' || pl_next ||
' MINEXTENTS ' || pl_minext ||
' MAXEXTENTS ' || pl_maxext ||
' PCTINCREASE ' || pl_pctinc ||
' OBJNO ' || pl_obj_num ||
' EXTENTS (FILE ' || pl_file_num ||
' BLOCK ' || pl_block_num ||'))';
elsif (pl_objtype = 'CLUSTER TABLE') then
pl_text := ' STORAGE ( OBJNO '|| pl_obj_num ||
' TABNO '|| pl_tab_num ||
') CLUSTER C_USER#(USER#)';
elsif (pl_objtype = 'INDEX') then
-- generate the index storage text
pl_text := ' PCTFREE ' || pl_pctf ||
' INITRANS ' || pl_initrans ||
' MAXTRANS ' || pl_maxtrans ||
' STORAGE ( INITIAL ' || pl_initial ||
' NEXT ' || pl_next ||
' MINEXTENTS ' || pl_minext ||
' MAXEXTENTS ' || pl_maxext ||
' PCTINCREASE ' || pl_pctinc ||
' OBJNO ' || pl_obj_num ||
' EXTENTS (FILE ' || pl_file_num ||
' BLOCK ' || pl_block_num ||'))';
end if;
return pl_text;
end;
begin
/* Create the bootstrap sql text for CON$ */
pl_obj_num := get_obj_num('CON$IMG', 1);
pl_line_num := get_line_num('CON$', 1);
pl_objtxt := 'CREATE TABLE CON$("OWNER#" NUMBER NOT NULL,"NAME" VARCHAR2(128) NOT NULL,"CON#" NUMBER NOT NULL,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE)';
pl_objtxt := pl_objtxt || gen_storage(pl_obj_num, 'TABLE');
insert into bootstrap$tmpstr values(pl_line_num, pl_obj_num, pl_objtxt);
commit;
/* Create the bootstrap sql text for I_CON1_IMG (replace i_con1) */
pl_obj_num := get_obj_num('I_CON1_IMG', 4);
pl_line_num := get_line_num('I_CON1', 4);
pl_objtxt :='CREATE UNIQUE INDEX I_CON1 ON CON$(OWNER#,NAME)';
pl_objtxt := pl_objtxt || gen_storage(pl_obj_num, 'INDEX');
insert into bootstrap$tmpstr values(pl_line_num, pl_obj_num, pl_objtxt);
commit;
/* Create the bootstrap sql text for I_CON2_IMG (replace i_con2) */
pl_obj_num := get_obj_num('I_CON2_IMG', 4);
pl_line_num := get_line_num('I_CON2', 4);
pl_objtxt := 'CREATE UNIQUE INDEX I_CON2 ON CON$(CON#)';
pl_objtxt := pl_objtxt || gen_storage(pl_obj_num, 'INDEX');
insert into bootstrap$tmpstr values(pl_line_num, pl_obj_num, pl_objtxt);
commit;
/* Create the bootstrap sql text for BOOTSTRAT$IMG (replace BOOTSTRAP$) */
pl_obj_num := get_obj_num('BOOTSTRAP$IMG', 1);
pl_line_num := get_line_num('BOOTSTRAP$', 1);
pl_objtxt := 'CREATE TABLE BOOTSTRAP$("LINE#" NUMBER NOT NULL,"OBJ#" NUMBER NOT NULL,"SQL_TEXT" VARCHAR2(4000) NOT NULL)';
pl_objtxt := pl_objtxt || gen_storage(pl_obj_num, 'TABLE');
insert into bootstrap$tmpstr values(pl_line_num, pl_obj_num, pl_objtxt);
commit;
end;
/
3、旧表数据导入新表
insert into sys.con$img select * from sys.con$;
insert into bootstrap$img select * from bootstrap$;
commit;
4、更新统计信息
begin
dbms_stats.delete_table_stats('SYS', 'CON$IMG');
dbms_Stats.gather_table_stats('SYS', 'CON$IMG', estimate_percent => 100,
method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
end;
/
5、新旧表 swap index(即更新line# obj#以及obj$表记录的obj#的对应关系)
原对应关系：
line# obj# sql_text(obj#,file#,block#)
28 28 con$(28,1,288)
51 51 i_con1(51,1,456)
52 52 i_con2(52,1,464)
59 59 bootstrap$(59,1,520)
--null 7395244 bootstrap$tmpstr(null)
null 7354534 bootstrap$img(7354534,8,752)
null 7354642 con$img(7354642,8,1280)
null 7354643 i_con1_img(7354643,8,1288)
null 7354644 i_con2_img(7354644,8,1296)
执行以下sql后结果：
line# obj# sql_text(obj#,file#,block#)
28 7354642 con$(28,1,288)
51 7354643 i_con1(51,1,456)
52 7354644 i_con2(52,1,464)
59 7354534 bootstrap$(59,1,520)
null 59 bootstrap$img(7354534,8,752)
null 28 con$img(7354642,8,1280)
null 51 i_con1_img(7354643,8,1288)
null 52 i_con2_img(7354644,8,1296)
bootstrap$tmpstr 对应关系：
line# obj# sql_text(obj#,file#,block#)
28 7354642 con$(7354642,8,1280)
51 7354643 i_con1(7354643,8,1288)
52 7354644 i_con2(7354644,8,1296)
59 7354534 bootstrap$(7354534,8,752)
declare
type vc_nst_type is table of varchar2(30);
type nb_nst_type is table of number;
old_name_array vc_nst_type; /* old object name array */
new_name_array vc_nst_type; /* new object name array */
ns_array nb_nst_type; /* namespace of the object */
begin
old_name_array := vc_nst_type('CON$','I_CON1', 'I_CON2',
'BOOTSTRAP$');
new_name_array := vc_nst_type('CON$IMG', 'I_CON1_IMG', 'I_CON2_IMG',
'BOOTSTRAP$IMG');
ns_array := nb_nst_type(1,4,4,1);
/* Swap the name in old_name_array with new_name_array in ONJ$ */
for i in old_name_array.FIRST .. old_name_array.LAST
loop
update obj$ set name = 'ORA$IMG_TMP'
where name = old_name_array(i) and owner# = 0 and namespace=ns_array(i);
update obj$ set name = old_name_array(i)
where name = new_name_array(i) and owner# = 0 and namespace=ns_array(i);
update obj$ set name = new_name_array(i)
where name = 'ORA$IMG_TMP' and owner# = 0 and namespace=ns_array(i);
end loop;
/* Commit when we're done with the swap */
commit;
end;
/
6、删除bootstrap$img 的旧数据(注意:这里的旧数据指的是老的con$等对象，由于上一步进行了swap，旧的con$已经变成了con$img,因此删除的是con$img)
当前bootstrap$img 对应关系：
line# obj# sql_text(obj#,file#,block#)
28 28 con$img(28,1,288)
51 51 i_con1_img(51,1,456)
52 52 i_con2_img(52,1,464)
59 59 bootstrap$img(59,1,520)
delete from bootstrap$img
where obj# in
(select obj#
from obj$
where name in ('CON$IMG', 'I_CON1_IMG', 'I_CON2_IMG', 'BOOTSTRAP$IMG'));
commit;
7、插入bootstrap$img 新的sql_text内容(核心启动原理,bootstrap$tmpstr中的内容一定是将来数据库的启动顺序路径)
insert into bootstrap$img select * from bootstrap$tmpstr;
commit;
插入后bootstrap$img对应关系
line# obj# sql_text(obj#,file#,block#)
28 7354642 con$(7354642,8,1280)
51 7354643 i_con1(7354643,8,1288)
52 7354644 i_con2(7354644,8,1296)
59 7354534 bootstrap$(7354534,8,752)
8、更新底层对象依赖关系
declare
type vc_nst_type is table of varchar2(30);
old_obj_num number;
new_obj_num number;
new_ts timestamp;
old_name vc_nst_type;
new_name vc_nst_type;
begin
old_name := vc_nst_type('CON$', 'BOOTSTRAP$');
new_name := vc_nst_type('CON$IMG', 'BOOTSTRAP$IMG');
for i in old_name.FIRST .. old_name.LAST
loop
select obj# into old_obj_num from obj$
where owner#=0 and name=old_name(i) and namespace=1;
select obj#, stime into new_obj_num, new_ts
from obj$ where owner#=0 and name=new_name(i) and namespace=1;
-- Step 7
update dependency$
set p_obj# = new_obj_num,
p_timestamp = new_ts
where p_obj# = old_obj_num;
-- Step 8
update objauth$ set obj# = new_obj_num where obj# = old_obj_num;
end loop;
commit;
end;
/
9、swap rdba(核心,即更换bootstrap$的地址指向)
--该方法在12c未生效，不推荐使用
startup upgrade
exec dbms_ddl_internal.swap_bootstrap('BOOTSTRAP$IMG');
delete from props$ where name = 'BOOTSTRAP_UPGRADE_ERROR';
delete from props$ where name = 'LOGMNR_BOOTSTRAP_UPGRADE_ERROR';
--dba和文件号、块号互相转换
CREATE OR REPLACE FUNCTION getbfno (p_dba IN VARCHAR2)
RETURN VARCHAR2
IS
l_str VARCHAR2 (255) DEFAULT NULL;
l_fno VARCHAR2 (15);
l_bno VARCHAR2 (15);
BEGIN
l_fno :=
DBMS_UTILITY.data_block_address_file (TO_NUMBER(LTRIM (p_dba, '0x'), 'xxxxxxxx'));
l_bno :=
DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x'), 'xxxxxxxx'));
l_str :=
'datafile# is:'
|| l_fno
|| CHR (10)
|| 'datablock is:'
|| l_bno
|| CHR (10)
|| 'dump command:alter system dump datafile '
|| l_fno
|| ' block '
|| l_bno
|| ';';
RETURN l_str;
END;
/--根据dba查询文件号、块号
select getbfno('0x00400179') bfno from dual;--根据文件号、块号查dba
select dbms_utility.make_data_block_address(8,752) from dual;
更新前：
1,520 --> 0x00400208 --> 4194824(十进制) --> 400208(十六进制) --> 旧的bootstrap$启动地址
8,752 --> 0x020002F0 --> 33555184(十进制) --> 20002F0(十六进制)--> 新的bootstrap$启动地址
--bbed修改启动地址,该地址的记录位置默认在1号文件@96偏移量处
set filename '/u01/oradata/HECPROD/system.258.893786245'
set count 32
set offset 96
p kcvfhrdb
set mode edit
assign kcvfhrdb=0x020002F0 --bootstrap$ 0x020002F0
sum apply
verify