ORA-00704: bootstrap process failure故障修复

近日,同事反馈一套测试测试数据库宕机,进行重启数据库操作时发现数据库无法open,抛出ORA-00704: bootstrap process failure错误,版本为11.2.0.4

SQL> startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size		    2260088 bytes
Variable Size		  671089544 bytes
Database Buffers	  390070272 bytes
Redo Buffers		    5517312 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
Process ID: 57338
Session ID: 96 Serial number: 3

错误一并抛出了ORA-00942: table or view does not exist错误,应该是系统表丢失导致的bootstrap失败,并且alert日志中没有任何其他日志输出。

将数据库启动到nomount与mount状态,利用10046 event跟踪bootstrap过程:
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/opt/app/ora11g/albert/diag/rdbms/prod3/PROD3/trace/PROD3_ora_57585.trc
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> alter database mount;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
Process ID: 57585
Session ID: 96 Serial number: 3

访问/opt/app/ora11g/albert/diag/rdbms/prod3/PROD3/trace/PROD3_ora_57585.trc发现访问access$表时抛出异常:


  value=1
EXEC #140534360327848:c=0,e=96,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2853959010,tim=1605062466327561
WAIT #140534360327848: nam='db file sequential read' ela= 11 file#=1 block#=11666 blocks=1 obj#=37 tim=1605062466327595
FETCH #140534360327848:c=0,e=39,p=1,cr=2,cu=0,mis=0,r=0,dep=2,og=4,plh=2853959010,tim=1605062466327610
CLOSE #140534360327848:c=0,e=1,dep=2,type=3,tim=1605062466327641
=====================
PARSE ERROR #140534355186424:len=56 dep=1 uid=0 oct=3 lid=0 tim=1605062466327703 err=942
select order#,columns,types from access$ where d_obj#=:1
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

*** 2020-11-11 10:41:06.328
USER (ospid: 57585): terminating the instance due to error 704
启动到MOUNT状态确认,access$表确实不存在:
SQL> desc access$;
ERROR:
ORA-04043: object access$ does not exist

修复过程,重建ACCESS$表。

找寻另外的测试库中,获取一下相关ACCESS$的对象元数据信息:
SQL> select dbms_metadata.get_ddl('TABLE','ACCESS$','SYS') from dual
  2  ;

DBMS_METADATA.GET_DDL('TABLE','ACCESS$','SYS')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."ACCESS$"
   (	"D_OBJ#" NUMBER NOT NULL ENABLE,
	"ORDER#" NUMBER NOT NULL ENABLE,
	"COLUMNS" RAW(126),
	"TYPES" NUMBER NOT NULL ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"

SQL> select index_name,table_name from dba_indexes where table_name='ACCESS$';

INDEX_NAME		       TABLE_NAME
------------------------------ ------------------------------
I_ACCESS1		       ACCESS$

SQL> select dbms_metadata.get_ddl('INDEX','I_ACCESS1','SYS') from dual;

DBMS_METADATA.GET_DDL('INDEX','I_ACCESS1','SYS')
--------------------------------------------------------------------------------

  CREATE INDEX "SYS"."I_ACCESS1" ON "SYS"."ACCESS$" ("D_OBJ#", "ORDER#")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"

其他涉及关联对象并没有确认,为安全起见,计划将数据库启动后将数据导出导入重新建立数据库。

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size		    2260088 bytes
Variable Size		  671089544 bytes
Database Buffers	  390070272 bytes
Redo Buffers		    5517312 bytes
Database mounted.
Database opened.

SQL>   CREATE TABLE "SYS"."ACCESS$"
   (    "D_OBJ#" NUMBER NOT NULL ENABLE,
    "ORDER#" NUMBER NOT NULL ENABLE,
    "COLUMNS" RAW(126),
    "TYPES" NUMBER NOT NULL ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  2    3    4    5    6    7    8    9   10   11  ;

Table created.

SQL>
SQL> CREATE INDEX "SYS"."I_ACCESS1" ON "SYS"."ACCESS$" ("D_OBJ#", "ORDER#")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  2    3    4    5    6  ;

Index created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size		    2260088 bytes
Variable Size		  671089544 bytes
Database Buffers	  390070272 bytes
Redo Buffers		    5517312 bytes
Database mounted.
Database opened.

--数据库成功OPEN,建议马上将数据导入导出,并将数据库重建。

至于为何ACCESS$表丢失的原因为应用人员登录SYS用户进行操作测试时误删除操作。但是值得注意的是,此表在12C CDB架构中,是无法在PDB中删除的(公共元数据存储在CDB管理,公共对象均为metadata link方式呈现给PDB),体现出了更好的隔离性-应用元数据与数据库字典元数据分离。

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB001A			  READ WRITE NO
	 4 PDB1 			  READ WRITE NO
	 5 PDB2 			  READ WRITE NO
SQL> alter session set container=pdb1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> show user
USER is "SYS"
SQL> drop table access$;
drop table access$
           *
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database