详解Oracle数据库的启动

Oracle数据库的启动，其概念可参考Overview of Instance and Database Startup。

其过程可参见下图：

当数据库从关闭状态进入打开数据库状态时，它会经历以下阶段。

阶段	Mount状态	描述
1	实例在没有挂载数据库的情况下启动	实例已启动，但尚未与数据库关联。
2	数据库挂载	实例启动并通过读取其控制文件与数据库相关联。 数据库对用户关闭。
3	数据库打开	实例已启动并与打开的数据库相关联。 数据文件中包含的数据可供授权用户访问。

下面我们将通过示例来详细了解数据库的启动过程。

数据库启动涉及的文件

SQL> show parameter spfile
NAME   TYPE   VALUE
------ ------ ------------------------------------------------------
spfile string /opt/oracle/product/19c/dbhome_1/dbs/spfileORCLCDB.ora

SQL> show parameter control_files
NAME          TYPE   VALUE
------------- ------ ------------------------------------------------------------------------------------
control_files string /opt/oracle/oradata/ORCLCDB/control01.ctl, /opt/oracle/oradata/ORCLCDB/control02.ctl

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/system01.dbf
/opt/oracle/oradata/ORCLCDB/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/users01.dbf
...

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/redo03.log
/opt/oracle/oradata/ORCLCDB/redo02.log
/opt/oracle/oradata/ORCLCDB/redo01.log

初始状态，数据库处于关闭状态

$ stat /opt/oracle/product/19c/dbhome_1/dbs/spfileORCLCDB.ora
  File: ‘/opt/oracle/product/19c/dbhome_1/dbs/spfileORCLCDB.ora’
  Size: 3584            Blocks: 8          IO Block: 4096   regular file
Device: fc00h/64512d    Inode: 147838129   Links: 1
Access: (0640/-rw-r-----)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Context: unconfined_u:object_r:usr_t:s0
Access: 2024-01-08 22:39:40.758879960 +0800
Modify: 2024-01-08 22:27:21.718465002 +0800
Change: 2024-01-08 22:27:21.718465002 +0800
 Birth: -

$ stat /opt/oracle/oradata/ORCLCDB/control01.ctl
  File: ‘/opt/oracle/oradata/ORCLCDB/control01.ctl’
  Size: 18726912        Blocks: 36576      IO Block: 4096   regular file
Device: fc00h/64512d    Inode: 139684770   Links: 1
Access: (0640/-rw-r-----)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Context: unconfined_u:object_r:usr_t:s0
Access: 2024-01-08 22:41:25.153574355 +0800
Modify: 2024-01-08 22:41:25.179574414 +0800
Change: 2024-01-08 22:41:25.179574414 +0800
 Birth: -

$ stat /opt/oracle/oradata/ORCLCDB/system01.dbf
  File: ‘/opt/oracle/oradata/ORCLCDB/system01.dbf’
  Size: 954212352       Blocks: 1863704    IO Block: 4096   regular file
Device: fc00h/64512d    Inode: 139684766   Links: 1
Access: (0640/-rw-r-----)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Context: unconfined_u:object_r:usr_t:s0
Access: 2024-01-08 22:41:24.812573601 +0800
Modify: 2024-01-08 22:41:24.816573610 +0800
Change: 2024-01-08 22:41:24.816573610 +0800
 Birth: -

$ ipcs -a

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status

------ Semaphore Arrays --------
key        semid      owner      perms      nsems

$ ps -ef|grep smon|grep -v grep

数据库实例已启动 （阶段1）

根据文档How an Instance Is Started，启动实例的过程如下：

1. 搜索服务器参数文件（SPFILE）
2. 读取服务器参数文件以确定初始化参数的值
3. 根据初始化参数设置分配SGA
4. 启动 Oracle 后台进程
5. 打开警报日志和跟踪文件，并以有效的参数语法将所有显式参数设置写入警报日志

相关命令为：

-- 数据库从关闭状态进入实例启动状态
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size                  9134176 bytes
Variable Size             603979776 bytes
Database Buffers          587202560 bytes
Redo Buffers                7639040 bytes

来看一下相关变化。

首先spfile被读取了，虽然其没有被改动。

$ stat /opt/oracle/product/19c/dbhome_1/dbs/spfileORCLCDB.ora
  File: ‘/opt/oracle/product/19c/dbhome_1/dbs/spfileORCLCDB.ora’
  Size: 3584            Blocks: 8          IO Block: 4096   regular file
Device: fc00h/64512d    Inode: 147838129   Links: 1
Access: (0640/-rw-r-----)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Context: unconfined_u:object_r:usr_t:s0
Access: 2024-01-08 22:39:40.758879960 +0800
Modify: 2024-01-08 22:27:21.718465002 +0800
Change: 2024-01-08 22:27:21.718465002 +0800
 Birth: -

控制文件和数据文件都没有被读取，毕竟数据库还没有被mount。

$ stat /opt/oracle/oradata/ORCLCDB/control01.ctl
  File: ‘/opt/oracle/oradata/ORCLCDB/control01.ctl’
  Size: 18726912        Blocks: 36576      IO Block: 4096   regular file
Device: fc00h/64512d    Inode: 139684770   Links: 1
Access: (0640/-rw-r-----)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Context: unconfined_u:object_r:usr_t:s0
Access: 2024-01-08 22:41:25.153574355 +0800
Modify: 2024-01-08 22:41:25.179574414 +0800
Change: 2024-01-08 22:41:25.179574414 +0800
 Birth: -

$ stat /opt/oracle/oradata/ORCLCDB/system01.dbf
  File: ‘/opt/oracle/oradata/ORCLCDB/system01.dbf’
  Size: 954212352       Blocks: 1863704    IO Block: 4096   regular file
Device: fc00h/64512d    Inode: 139684766   Links: 1
Access: (0640/-rw-r-----)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Context: unconfined_u:object_r:usr_t:s0
Access: 2024-01-08 22:41:24.812573601 +0800
Modify: 2024-01-08 22:41:24.816573610 +0800
Change: 2024-01-08 22:41:24.816573610 +0800
 Birth: -

尽管数据库没有启动，但内存确实分配了，这也说明SGA等内存组件实际是实例的特性：

$ ipcs -a

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00000000 4          oracle     600        9138176    64
0x00000000 5          oracle     600        1191182336 32
0x00000000 6          oracle     600        7639040    32
0x43b375cc 7          oracle     600        12288      32

------ Semaphore Arrays --------
key        semid      owner      perms      nsems
0x8d2a3534 10         oracle     600        250
0x8d2a3535 11         oracle     600        250

对照以下SGA的信息：

SQL> select * from v$sga;

NAME                      VALUE     CON_ID
-------------------- ---------- ----------
Fixed Size              9134176          0
Variable Size         603979776          0
Database Buffers      587202560          0
Redo Buffers            7639040          0

Fixed Size对应ipcs输出中的shmid 4；Variable Size+Database Buffers等于1191182336，对应ipcs输出中的shmid 5；Redo Buffers对应ipcs输出中的shmid 6。

关于Fixed Size和Variable Size，参见What are Fixed Size and Variable Size of SGA indicative of and how do we calculate them?

后台进程启动了：

$ ps -ef|grep smon|grep -v grep
oracle   20677     1  0 22:51 ?        00:00:00 ora_smon_ORCLCDB

这个阶段，管理员还不能对数据库进行操作：

SQL> select open_mode from v$database;
select open_mode from v$database
                      *
ERROR at line 1:
ORA-01507: database not mounted

数据库已挂载（阶段2）

根据文档How a Database Is Mounted，在这一阶段，Oracle挂载一个数据库并与数据库实例关联。如果是RAC配置，一个数据库可以与多个数据库实例关联。

实例通过CONTROL_FILES初始化参数获取控制文件的位置并将其打开。在控制文件中，可以获取数据文件和在线重做日志文件的位置。

在这一阶段，数据库仍然是关闭的，管理员可以做一些特定的数据库维护操作。

相关命令为：

-- 数据库从关闭状态直接进入数据库挂载状态
SQL> STARTUP MOUNT
-- 数据库从NOMOUNT状态进入数据库挂载状态
SQL> ALTER DATABASE MOUNT;
Database altered.

来看一下和上一阶段想比，发生了什么变化。

首先，管理员已经可以访问数据库进行管理操作，但访问用户数据还是不行的：

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> select * from hr.employees;
select * from hr.employees
                 *
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only

spfile被修改了，而在上一阶段仅仅被读取。control file被修改了，而在上一阶段没有被访问。

$ stat /opt/oracle/product/19c/dbhome_1/dbs/spfileORCLCDB.ora
  File: ‘/opt/oracle/product/19c/dbhome_1/dbs/spfileORCLCDB.ora’
  Size: 3584            Blocks: 8          IO Block: 4096   regular file
Device: fc00h/64512d    Inode: 147838129   Links: 1
Access: (0640/-rw-r-----)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Context: unconfined_u:object_r:usr_t:s0
Access: 2024-01-08 23:04:55.733604697 +0800
Modify: 2024-01-08 23:04:55.744604708 +0800
Change: 2024-01-08 23:04:55.744604708 +0800
 Birth: -

$ stat /opt/oracle/oradata/ORCLCDB/control01.ctl
  File: ‘/opt/oracle/oradata/ORCLCDB/control01.ctl’
  Size: 18726912        Blocks: 36576      IO Block: 4096   regular file
Device: fc00h/64512d    Inode: 139684770   Links: 1
Access: (0640/-rw-r-----)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Context: unconfined_u:object_r:usr_t:s0
Access: 2024-01-08 23:14:05.202142083 +0800
Modify: 2024-01-08 23:14:05.189142070 +0800
Change: 2024-01-08 23:14:05.189142070 +0800
 Birth: -

数据文件仍没有被读取：

$ stat /opt/oracle/oradata/ORCLCDB/system01.dbf
  File: ‘/opt/oracle/oradata/ORCLCDB/system01.dbf’
  Size: 954212352       Blocks: 1863704    IO Block: 4096   regular file
Device: fc00h/64512d    Inode: 139684766   Links: 1
Access: (0640/-rw-r-----)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Context: unconfined_u:object_r:usr_t:s0
Access: 2024-01-08 22:41:24.812573601 +0800
Modify: 2024-01-08 22:41:24.816573610 +0800
Change: 2024-01-08 22:41:24.816573610 +0800
 Birth: -

数据库仍然是关闭的，但可以做一些维护工作：

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 ORCLPDB1                       MOUNTED
         4 ORCLPDB2                       MOUNTED

目前为止，redo log也没有被读取：

$ stat /opt/oracle/oradata/ORCLCDB/redo01.log
  File: ‘/opt/oracle/oradata/ORCLCDB/redo01.log’
  Size: 209715712       Blocks: 409616     IO Block: 4096   regular file
Device: fc00h/64512d    Inode: 139684773   Links: 1
Access: (0640/-rw-r-----)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Context: unconfined_u:object_r:usr_t:s0
Access: 2024-01-08 22:27:23.244467670 +0800
Modify: 2024-01-08 22:27:23.252467684 +0800
Change: 2024-01-08 22:27:23.252467684 +0800
 Birth: -

数据库已打开（阶段3）

根据文档How a Database Is Opened，在这一阶段，普通的用户就可以访问数据库进行增删改查了。

打开数据库时，Oracle 数据库执行以下操作：

1. 打开除撤销表空间以外的表空间中的在线数据文件
   如果之前关闭数据库时表空间处于脱机状态，那么当数据库重新打开时，该表空间及其相应的数据文件将处于脱机状态。
2. 获取一个撤销表空间
3. 打开在线重做日志文件

相关命令为：

-- 数据库从关闭状态直接进入数据库打开状态
SQL> STARTUP OPEN
-- 数据库从NOMOUNT状态进入数据库挂载状态
SQL> ALTER DATABASE OPEN;

Database altered.

这一阶段，数据文件和redo log都被读取和修改了：

$ stat /opt/oracle/oradata/ORCLCDB/system01.dbf
  File: ‘/opt/oracle/oradata/ORCLCDB/system01.dbf’
  Size: 954212352       Blocks: 1863704    IO Block: 4096   regular file
Device: fc00h/64512d    Inode: 139684766   Links: 1
Access: (0640/-rw-r-----)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Context: unconfined_u:object_r:usr_t:s0
Access: 2024-01-08 23:24:32.182755204 +0800
Modify: 2024-01-08 23:24:32.077755101 +0800
Change: 2024-01-08 23:24:32.077755101 +0800
 Birth: -

$ stat /opt/oracle/oradata/ORCLCDB/redo01.log
  File: ‘/opt/oracle/oradata/ORCLCDB/redo01.log’
  Size: 209715712       Blocks: 409616     IO Block: 4096   regular file
Device: fc00h/64512d    Inode: 139684773   Links: 1
Access: (0640/-rw-r-----)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Context: unconfined_u:object_r:usr_t:s0
Access: 2024-01-08 23:24:32.020755046 +0800
Modify: 2024-01-08 23:24:32.026755051 +0800
Change: 2024-01-08 23:24:32.026755051 +0800
 Birth: -

此时数据库处于可读写状态，业务用户可以访问数据库了：

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

参考

• Database Concepts: Overview of Database Instance Startup and Shutdown
• User’s Guide and Reference: STARTUP
• Database Administrator’s Guide: 3.1 Starting Up a Database