近日,同事反馈一套测试测试数据库宕机,进行重启数据库操作时发现数据库无法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