Oracle system recreate bootstrap

Overview

Bootstrap is one of the most critical phases during Oracle database startup. When the database transitions from MOUNT to OPEN state, Oracle must first load the core metadata of the data dictionary—this process is called Bootstrap. Understanding the Bootstrap mechanism is essential for diagnosing database startup failures, handling ORA-00704 errors, and performing database recovery.

This article provides an in-depth analysis of Oracle Bootstrap's working principles, core objects, startup process, and common troubleshooting methods.

---

1. Oracle Data Dictionary Fundamentals

1.1 Data Dictionary Overview

The Oracle Data Dictionary is the database's "metadata repository," storing all information about database structure and objects:

• Storage Location: SYSTEM tablespace
• Owner: SYS user
• Caching Mechanism: Some data dictionary information is cached in the SGA's Dictionary Cache (Row Cache)

1.2 Core Data Dictionary Base Tables

The following are the most important base tables in the Oracle Data Dictionary:

1.3 BOOTSTRAP$ Table Structure

The BOOTSTRAP$ table is the core of the Bootstrap process, storing the DDL statements required to create other Bootstrap objects:

SQL> DESC SYS.BOOTSTRAP$

 Name                           Null?    Type
 ------------------------------ -------- -------------------
 LINE#                          NOT NULL NUMBER
 OBJ#                           NOT NULL NUMBER
 SQL_TEXT                       NOT NULL VARCHAR2(4000)

Column descriptions:

• LINE#: Line number, used to order DDL execution sequence
• OBJ#: Object number, corresponding to OBJECT_ID in the data dictionary
• SQL_TEXT: DDL statement to create the object

Important Note: Objects in the BOOTSTRAP$ table cannot be modified with standard DDL statements. Any attempt will trigger ORA-00701 error.

---

2. Bootstrap Objects Explained

2.1 Definition of Bootstrap Objects

Bootstrap objects are the core objects that Oracle must load first during database startup. These objects have the following characteristics:

• OBJECT_ID Range: Objects with IDs less than 56 are classified as core Bootstrap objects
• Storage Location: Fixed location in the SYSTEM tablespace
• Loading Time: Loaded during the MOUNT to OPEN phase

2.2 Bootstrap Object Relationship Diagram

Bootstrap objects have complex interdependencies:

                    ┌─────────────┐
                    │ BOOTSTRAP$  │
                    │  (OBJ# 56)  │
                    └──────┬──────┘
                           │
           ┌───────────────┼───────────────┐
           │               │               │
           ▼               ▼               ▼
    ┌──────────┐    ┌──────────┐    ┌──────────┐
    │   OBJ$   │    │  USER$   │    │   TS$    │
    │ (OBJ# 18)│    │ (OBJ# 22)│    │ (OBJ# 6) │
    └────┬─────┘    └──────────┘    └──────────┘
         │
    ┌────┴────┬─────────┬─────────┐
    │         │         │         │
    ▼         ▼         ▼         ▼
┌───────┐ ┌───────┐ ┌───────┐ ┌───────┐
│ TAB$  │ │ IND$  │ │ COL$  │ │ SEG$  │
└───────┘ └───────┘ └───────┘ └───────┘

2.3 Core Bootstrap Objects List

Query Bootstrap objects in your database:

-- Query all objects in BOOTSTRAP$
SELECT LINE#, OBJ#, 
       SUBSTR(SQL_TEXT, 1, 80) AS SQL_PREVIEW
FROM SYS.BOOTSTRAP$
ORDER BY LINE#;

-- Query objects with Object ID less than 56
SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE, CREATED
FROM DBA_OBJECTS
WHERE OBJECT_ID < 56
ORDER BY OBJECT_ID;

2.4 Bootstrap Objects Added in Version Evolution

Added in Oracle 10g R1:

• FIXED_OBJ$ - Fixed object information
• TAB_STATS$ - Table statistics
• IND_STATS$ - Index statistics
• I_FIXED_OBJ$_OBJ# - Fixed object index
• I_TAB_STATS$_OBJ# - Table statistics index
• I_IND_STATS$_OBJ# - Index statistics index
• OBJECT_USAGE - Object usage tracking

Histogram-related objects:

• HIST_HEAD$ - Histogram header information
• HISTGRM$ - Histogram data
• I_HH_OBJ#_COL# - Histogram index
• I_HH_OBJ#_INTCOL# - Internal column histogram index
• C_OBJ#_INTCOL# - Column cluster

---

3. Database Startup and Bootstrap Process

3.1 Three Stages of Database Startup

SHUTDOWN ──▶ NOMOUNT ──▶ MOUNT ──▶ OPEN
               │           │         │
               │           │         └── Database fully available
               │           │
               │           └── Control file read
               │               Datafiles/logfiles located
               │               Bootstrap begins
               │
               └── Instance started
                   SGA allocated
                   Background processes started

3.2 Detailed Bootstrap Process

Bootstrap operations occur between the MOUNT and OPEN stages. You can capture the detailed process using SQL_TRACE:

-- Enable SQL trace to observe Bootstrap process
STARTUP MOUNT;

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

ALTER DATABASE OPEN;

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

-- Get trace file location
SHOW PARAMETER USER_DUMP_DEST;
-- Or
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';

3.3 Bootstrap Execution Steps Explained

Step 1: Create BOOTSTRAP$ Table

Oracle first creates the BOOTSTRAP$ table structure in memory:

CREATE TABLE BOOTSTRAP$ (
    LINE#    NUMBER NOT NULL,
    OBJ#     NUMBER NOT NULL,
    SQL_TEXT VARCHAR2(4000) NOT NULL
) STORAGE (
    INITIAL 50K 
    OBJNO 56 
    EXTENTS (FILE 1 BLOCK 377)  -- Location varies by version and platform
);

Note: This is not a standard CREATE TABLE statement but a special operation executed through internal driver routines. The BOOTSTRAP$ table location information is hardcoded in the datafile header.

Step 2: Read and Execute DDL

-- Query executed internally by Oracle
SELECT LINE#, SQL_TEXT 
FROM BOOTSTRAP$ 
WHERE OBJ# != :1  -- :1 = 56 (excluding BOOTSTRAP$ itself)
ORDER BY LINE#;

Step 3: Create Objects in Order

• Object# 0: System Rollback Segment
• Object# 2-55: Other core base tables and indexes
• Object# 1: Reserved, not used

Step 4: Verify Consistency

After Bootstrap completes, Oracle performs multiple consistency checks to ensure all core objects are in correct state.

---

4. ORA-00704 Error Explained

4.1 Error Description

ORA-00704: bootstrap process failure

This is one of the most serious errors during Oracle database startup, indicating that the Bootstrap process has failed and the database cannot be opened.

4.2 Common Causes

4.3 Diagnostic Methods

Check Alert Log:

# Find alert log location
SHOW PARAMETER BACKGROUND_DUMP_DEST;
# Or
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Alert';

# Check error messages during startup
tail -500 alert_<SID>.log | grep -A 20 "ORA-00704"

Check SYSTEM Datafile:

-- Check in MOUNT state
SELECT FILE#, STATUS, NAME 
FROM V$DATAFILE 
WHERE FILE# = 1;

-- Use DBVERIFY to check for physical corruption
$ dbv file=/path/to/system01.dbf blocksize=8192

4.4 Recovery Solutions

Solution 1: Complete Recovery (Recommended)

-- Restore SYSTEM datafile from backup
STARTUP MOUNT;
RESTORE DATAFILE 1;
RECOVER DATAFILE 1;
ALTER DATABASE OPEN;

Solution 2: Point-in-Time Recovery (PITR)

If the problem is not caused by physical corruption:

STARTUP MOUNT;
RUN {
    SET UNTIL TIME "TO_DATE('2024-01-15 10:00:00','YYYY-MM-DD HH24:MI:SS')";
    RESTORE DATABASE;
    RECOVER DATABASE;
}
ALTER DATABASE OPEN RESETLOGS;

Solution 3: Using Event Workaround (Specific Scenarios Only)

Event 38003 can affect the loading of fixed cache during Bootstrap:

-- Add to init.ora
EVENT="38003 TRACE NAME CONTEXT FOREVER"

-- Or specify at startup
STARTUP PFILE='init_with_event.ora';

Warning: This method is only applicable to specific situations and may cause other issues. Use only under Oracle Support guidance.

Solution 4: Open in Migrate Mode

STARTUP MIGRATE;

Migrate mode can bypass certain Bootstrap object checks, but is only suitable for upgrade scenarios.

---

5. Data Dictionary Corruption Detection and Repair

5.1 Common Data Dictionary Corruption Types

Row-level Inconsistencies:

• Records exist in TAB$/IND$ but no corresponding entry in OBJ$
• Records exist in UNDO$/TAB$/IND$ but no corresponding entry in SEG$
• OWNER# in OBJ$ does not exist in USER$
• Records exist in OBJ$ but no corresponding entry in TAB$/IND$/SEQ$ etc.

Object-level Corruption:

• Missing data dictionary objects
• Corrupted data dictionary tables or indexes

5.2 Diagnostic Scripts

Using Oracle Official Diagnostic Scripts:

-- HCHECK script (Reference MOS Note:136697.1)
@?/rdbms/admin/hcheck.sql

-- Or download the latest version
-- MOS Doc ID: 136697.1 - HCHECK - Health Check Script

Manual Key Consistency Checks:

-- Check OBJ$ and TAB$ consistency
SELECT o.OBJ#, o.NAME 
FROM SYS.OBJ$ o
WHERE o.TYPE# = 2  -- TABLE
AND NOT EXISTS (
    SELECT 1 FROM SYS.TAB$ t WHERE t.OBJ# = o.OBJ#
);

-- Check OBJ$ and USER$ consistency
SELECT o.OBJ#, o.NAME, o.OWNER#
FROM SYS.OBJ$ o
WHERE NOT EXISTS (
    SELECT 1 FROM SYS.USER$ u WHERE u.USER# = o.OWNER#
);

-- Check SEG$ and OBJ$ consistency
SELECT s.FILE#, s.BLOCK#, s.TS#
FROM SYS.SEG$ s
WHERE s.TYPE# IN (5,6)  -- TABLE/INDEX segments
AND NOT EXISTS (
    SELECT 1 FROM SYS.OBJ$ o 
    WHERE o.DATAOBJ# = s.OBJ# OR o.OBJ# = s.OBJ#
);

-- Validate table/index cross-reference
ANALYZE TABLE <table_name> VALIDATE STRUCTURE CASCADE;
-- ORA-01499 indicates cross-reference failure

5.3 BOOTSTRAP$ Table Validation

-- Check BOOTSTRAP$ table integrity
SELECT COUNT(*) AS total_entries,
       MIN(OBJ#) AS min_obj,
       MAX(OBJ#) AS max_obj
FROM SYS.BOOTSTRAP$;

-- Verify all Bootstrap objects exist
SELECT b.OBJ#, b.LINE#,
       CASE WHEN o.OBJ# IS NULL THEN 'MISSING' ELSE 'OK' END AS status
FROM SYS.BOOTSTRAP$ b
LEFT JOIN SYS.OBJ$ o ON b.OBJ# = o.OBJ#
WHERE b.OBJ# != 56
ORDER BY b.LINE#;

-- Check Bootstrap DDL validity
SELECT OBJ#, LINE#,
       SUBSTR(SQL_TEXT, 1, 100) AS ddl_preview
FROM SYS.BOOTSTRAP$
WHERE SQL_TEXT IS NULL OR LENGTH(SQL_TEXT) = 0;

---

6. Best Practices and Prevention

6.1 Backup Strategy

• Regular SYSTEM Tablespace Backups: The SYSTEM tablespace contains all Bootstrap objects and is critical for recovery
• Maintain Multiple Backup Copies: Recommend keeping at least 7 days of backups
• Test Recovery Procedures: Regularly verify backup recoverability

-- RMAN backup of SYSTEM tablespace
BACKUP TABLESPACE SYSTEM;

-- Validate backup
RESTORE TABLESPACE SYSTEM VALIDATE;

6.2 Operational Guidelines

• Avoid Direct Modification of Data Dictionary Base Tables: Any direct UPDATE/DELETE operations can cause catastrophic consequences
• Do Not Use Unofficial Recovery Methods: Such as forced open, skipping recovery, etc.
• Prepare Before Upgrades: Upgrades may modify Bootstrap objects; ensure complete backup

6.3 Monitoring Recommendations

-- Monitor SYSTEM tablespace usage
SELECT TABLESPACE_NAME,
       ROUND(USED_SPACE * 8192 / 1024 / 1024, 2) AS USED_MB,
       ROUND(TABLESPACE_SIZE * 8192 / 1024 / 1024, 2) AS TOTAL_MB,
       ROUND(USED_PERCENT, 2) AS USED_PCT
FROM DBA_TABLESPACE_USAGE_METRICS
WHERE TABLESPACE_NAME = 'SYSTEM';

-- Check SYSTEM datafile status
SELECT FILE#, STATUS, ENABLED, NAME,
       BYTES/1024/1024 AS SIZE_MB
FROM V$DATAFILE
WHERE TS# = (SELECT TS# FROM V$TABLESPACE WHERE NAME = 'SYSTEM');

---

7. Deep Dive: Bootstrap Internal Mechanisms

7.1 Source Code Reference

The core Bootstrap logic is implemented in the Oracle kernel code file kqlb.c (Kernel Query Language Bootstrap). Key functions include:

• kqlblfc(): Load fixed cache
• kqlbld(): Load data dictionary
• kqlbcre(): Create Bootstrap objects

7.2 Physical Location of BOOTSTRAP$

The BOOTSTRAP$ table location information is stored in the SYSTEM datafile header, which is why Oracle can locate it without querying the data dictionary.

-- View BOOTSTRAP$ segment information
SELECT SEGMENT_NAME, FILE_ID, BLOCK_ID, BLOCKS
FROM DBA_EXTENTS
WHERE SEGMENT_NAME = 'BOOTSTRAP$'
AND OWNER = 'SYS';

7.3 Warm Startup vs. Cold Startup

Warm Startup: Normal database startup where Bootstrap objects are loaded into the shared pool.

Cold Startup: Startup after instance crash, requiring instance recovery in addition to Bootstrap.

---

8. Related Error Code Reference

---

9. Reference Resources

• MOS Note 136697.1: HCHECK - Health Check Script
• MOS Note 472231.1: How to Diagnose ORA-704 During Startup
• MOS Note 1526094.1: ORA-704 Bootstrap Process Failure
• Oracle Database Concepts: Data Dictionary and Dynamic Performance Views

---

Summary

Bootstrap is the foundation of Oracle database startup, and understanding its working principles is essential for database administrators. Key takeaways:

1. Bootstrap objects (OBJ# < 56) are prerequisites for database startup
2. BOOTSTRAP$ table stores the DDL statements for creating core objects
3. ORA-00704 is a serious error that typically requires recovery from backup
4. Prevention is better than cure: Regular backups, proper operations, and continuous monitoring are the best strategies

When encountering Bootstrap-related issues, first check the Alert Log and trace files to determine the root cause before choosing the appropriate recovery strategy. For complex scenarios, it is recommended to contact Oracle Support for professional guidance.