详解Oracle数据库的启动

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

其过程可参见下图：
在这里插入图片描述
当数据库从关闭状态进入打开数据库状态时，它会经历以下阶段。

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

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

基本概念

启动的第一步是寻找spfile，SPFILE 文件名为 spfileSID.ora，位于ORACLE_HOME/database 目录中。

我们知道spfile是包含了control file的位置的，因此启动的顺序也是先读spfile，然后会读control file。

spfile和control file都是二进制文件。

获取文本形式的spfile，可以：

create pfile='xxx' from spfile;

获取文本形式的control file，可以：

alter database backup controlfile to trace;

数据库启动涉及的文件

SQL> show parameter spfile
NAME   TYPE   VALUE
------ ------ ------------------------------------------------------
spfile string /opt/oracle/product/19c/dbhome_1/dbs/spfileORCLCDB.oraSQL> show parameter control_files
NAME          TYPE   VALUE
------------- ------ ------------------------------------------------------------------------------------
control_files string /opt/oracle/oradata/ORCLCDB/control01.ctl, /opt/oracle/oradata/ORCLCDB/control02.ctlSQL> 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

为了简单，我们只采用以下4个文件的状态：

spfile
control file：/opt/oracle/oradata/ORCLCDB/control01.ctl
data file：/opt/oracle/oradata/ORCLCDB/system01.dbf
redo log file：/opt/oracle/oradata/ORCLCDB/redo01.log

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

记录一下几个文件的时间戳。包括atime, mtime和ctime。

$ stat /opt/oracle/product/19c/dbhome_1/dbs/spfileORCLCDB.oraFile: ‘/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 +0800Birth: -$ stat /opt/oracle/oradata/ORCLCDB/control01.ctlFile: ‘/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 +0800Birth: -$ stat /opt/oracle/oradata/ORCLCDB/system01.dbfFile: ‘/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 +0800Birth: -

在以上时间中，atime是文件最后被访问的时间，如读取。mtime和ctime容易混淆，前者指文件内容被修改，后者指文件元数据被修改。
在ls命令中，-t显示mtime，-u显示atime，-c显示ctime，不过stat命令更清晰些。

然后目前并没有共享内存被分配。

$ 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，启动实例的过程如下：

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

数据库已挂载（阶段2）

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

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

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

数据库已打开（阶段3）

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

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

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

查询实例启动时间

当前启动时间：

SQL> alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss";Session altered.SQL> select startup_time from v$instance;STARTUP_TIME
-------------------
2024-01-09 11:34:52

历史启动时间，1月8日晚上那几个是做本次实验的时间：

SQL> SELECT STARTUP_TIME FROM dba_hist_database_instance ORDER BY startup_time DESC;STARTUP_TIME
---------------------------------------------------------------------------
09-JAN-24 11.35.06.000 AM
08-JAN-24 11.24.32.000 PM
08-JAN-24 10.27.23.000 PM
15-DEC-23 09.50.18.000 AM
22-NOV-23 09.27.57.000 AM

详细的启动日志可以从alert log中找到。

来看一下从close到nomount 数据库的这一段日志，也就是启动实例的日志：

2024-01-08T22:26:51.216756+08:00
Starting ORACLE instance (normal) (OS id: 18596)
2024-01-08T22:26:51.415274+08:00
****************************************************Sys-V shared memory will be used for creating SGA****************************************************
2024-01-08T22:26:51.421581+08:00
**********************************************************************
2024-01-08T22:26:51.421777+08:00
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)2024-01-08T22:26:51.422091+08:00Per process system memlock (soft) limit = 128G
2024-01-08T22:26:51.422197+08:00Expected per process system memlock (soft) limit to lockinstance MAX SHARED GLOBAL AREA (SGA) into memory: 1154M
...
2024-01-08T22:26:54.321786+08:00
Initial number of CPU is 2
Number of processor cores in the system is 2
Number of processor sockets in the system is 1
...
2024-01-08T22:26:56.201230+08:00
NOTE: remote asm mode is local (mode 0x1; from cluster type)
2024-01-08T22:26:57.060942+08:00
NOTE: Using default ASM root directory ASM
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Cluster configuration type = NONE [2]
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0.
ORACLE_HOME:    /opt/oracle/product/19c/dbhome_1
System name:    Linux
Node name:      oracle-19c-vagrant
Release:        5.4.17-2136.323.8.2.el7uek.x86_64
Version:        #2 SMP Wed Sep 20 00:06:39 PDT 2023
Machine:        x86_64
Using parameter settings in server-side spfile /opt/oracle/product/19c/dbhome_1/dbs/spfileORCLCDB.ora
System parameters with non-default values:processes                = 300nls_language             = "AMERICAN"nls_territory            = "AMERICA"filesystemio_options     = "setall"sga_target               = 1152Mcontrol_files            = "/opt/oracle/oradata/ORCLCDB/control01.ctl"control_files            = "/opt/oracle/oradata/ORCLCDB/control02.ctl"db_block_size            = 8192compatible               = "19.0.0"db_create_file_dest      = "/u02/oradata"undo_tablespace          = "UNDOTBS1"remote_login_passwordfile= "EXCLUSIVE"audit_sys_operations     = FALSEwallet_root              = "/opt/oracle/wallet"tde_configuration        = "keystore_configuration=file"dispatchers              = "(PROTOCOL=TCP) (SERVICE=ORCLCDBXDB)"local_listener           = "(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))"audit_file_dest          = "/opt/oracle/admin/ORCLCDB/adump"commit_wait              = "nowait"commit_logging           = "batch"audit_trail              = "NONE"db_name                  = "ORCLCDB"open_cursors             = 300pga_aggregate_target     = 384Mmax_string_size          = "EXTENDED"diagnostic_dest          = "/opt/oracle"enable_pluggable_database= TRUE
2024-01-08T22:26:57.272471+08:00
============================================================
NOTE: PatchLevel of this instance 0
============================================================
2024-01-08T22:27:00.845366+08:00
Starting background process PMON
2024-01-08T22:27:00.921901+08:00
PMON started with pid=2, OS id=18611
Starting background process CLMN
...

mount 数据库的这一段日志：

2024-01-08T22:27:15.577994+08:00
ALTER DATABASE   MOUNT
2024-01-08T22:27:19.960506+08:00
.... (PID:18684): Redo network throttle feature is disabled at mount time
2024-01-08T22:27:20.151186+08:00
Successful mount of redo thread 1, with mount id 2928771267
2024-01-08T22:27:20.170803+08:00
Database mounted in Exclusive Mode
Lost write protection disabled
.... (PID:18684): Using STANDBY_ARCHIVE_DEST parameter default value as /opt/oracle/product/19c/dbhome_1/dbs/arch [krsd.c:18141]
Completed: ALTER DATABASE   MOUNT
2024-01-08T22:27:20.604215+08:00

open 数据库的这一段日志：

2024-01-08T22:27:20.604215+08:00
ALTER DATABASE OPEN
Ping without log force is disabled:instance mounted in exclusive mode.
2024-01-08T22:27:20.970579+08:00
Crash Recovery excluding pdb 2 which was cleanly closed.
2024-01-08T22:27:21.009403+08:00
Crash Recovery excluding pdb 4 which was cleanly closed.
2024-01-08T22:27:21.044930+08:00
Beginning crash recovery of 1 threadsparallel recovery started with 2 processes
2024-01-08T22:27:21.862998+08:00Thread 1: Recovery starting at checkpoint rba (logseq 28 block 121441), scn 0
2024-01-08T22:27:21.904360+08:00
Started redo scan
2024-01-08T22:27:22.131319+08:00
Completed redo scanread 403 KB redo, 75 data blocks need recovery
2024-01-08T22:27:22.231830+08:00
Started redo application atThread 1: logseq 28, block 121441, offset 0
2024-01-08T22:27:22.250156+08:00
Recovery of Online Redo Log: Thread 1 Group 1 Seq 28 Reading mem 0Mem# 0: /opt/oracle/oradata/ORCLCDB/redo01.log
2024-01-08T22:27:22.267918+08:00
Completed redo application of 0.24MB
2024-01-08T22:27:22.500676+08:00
Completed crash recovery atThread 1: RBA 28.122247.16, nab 122247, scn 0x00000000004127ec75 data blocks read, 75 data blocks written, 403 redo k-bytes read
2024-01-08T22:27:23.079959+08:00
Endian type of dictionary set to little
2024-01-08T22:27:23.298785+08:00
Thread 1 advanced to log sequence 29 (thread open)
Redo log for group 2, sequence 29 is not located on DAX storage
2024-01-08T22:27:23.359758+08:00
TT00 (PID:18716): Gap Manager starting
2024-01-08T22:27:23.553486+08:00
Thread 1 opened at log sequence 29Current log# 2 seq# 29 mem# 0: /opt/oracle/oradata/ORCLCDB/redo02.log
Successful open of redo thread 1
2024-01-08T22:27:23.554324+08:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
stopping change tracking
2024-01-08T22:27:24.346943+08:00
Undo initialization recovery: err:0 start: 34362829 end: 34362990 diff: 161 ms (0.2 seconds)
2024-01-08T22:27:24.656931+08:00
[18698] Successfully onlined Undo Tablespace 2.
Undo initialization online undo segments: err:0 start: 34362990 end: 34363300 diff: 310 ms (0.3 seconds)
Undo initialization finished serial:0 start:34362829 end:34363317 diff:488 ms (0.5 seconds)
Verifying minimum file header compatibility for tablespace encryption for pdb 1..
Verifying file header compatibility for tablespace encryption completed for pdb 1
Database Characterset is AL32UTF8
No Resource Manager plan active
2024-01-08T22:27:26.737750+08:00
joxcsys_required_dirobj_exists: directory object exists with required path /opt/oracle/product/19c/dbhome_1/javavm/admin/, pid 18698 cid 1
replication_dependency_tracking turned off (no async multimaster replication found)
2024-01-08T22:27:28.530215+08:00
Starting background process AQPC
2024-01-08T22:27:28.651850+08:00
AQPC started with pid=43, OS id=18728
2024-01-08T22:27:29.867380+08:00
PDB$SEED(2):Autotune of undo retention is turned on.
2024-01-08T22:27:30.272563+08:00
PDB$SEED(2):Endian type of dictionary set to little
PDB$SEED(2):Undo initialization finished serial:0 start:34369350 end:34369350 diff:0 ms (0.0 seconds)
PDB$SEED(2):Database Characterset for PDB$SEED is AL32UTF8
2024-01-08T22:27:32.582739+08:00
PDB$SEED(2):Opening pdb with no Resource Manager plan active
2024-01-08T22:27:33.849456+08:00
ORCLPDB1(3):Autotune of undo retention is turned on.
2024-01-08T22:27:34.037019+08:00
ORCLPDB1(3):Endian type of dictionary set to little
ORCLPDB1(3):Undo initialization recovery: err:0 start: 34373141 end: 34373314 diff: 173 ms (0.2 seconds)
2024-01-08T22:27:34.923730+08:00
QPI: opatch file present, opatch
QPI: qopiprep.bat file present
2024-01-08T22:27:35.231303+08:00
ORCLPDB1(3):[18698] Successfully onlined Undo Tablespace 2.
ORCLPDB1(3):Undo initialization online undo segments: err:0 start: 34373314 end: 34373875 diff: 561 ms (0.6 seconds)
ORCLPDB1(3):Undo initialization finished serial:0 start:34373141 end:34373908 diff:767 ms (0.8 seconds)
ORCLPDB1(3):Database Characterset for ORCLPDB1 is AL32UTF8
2024-01-08T22:27:39.604926+08:00
ORCLPDB1(3):Opening pdb with no Resource Manager plan active
ORCLPDB1(3):joxcsys_required_dirobj_exists: directory object exists with required path /opt/oracle/product/19c/dbhome_1/javavm/admin/, pid 18698 cid 3
2024-01-08T22:27:41.057725+08:00
Pluggable database ORCLPDB1 opened read write
2024-01-08T22:27:42.744511+08:00
Starting background process CJQ0
2024-01-08T22:27:43.150317+08:00
CJQ0 started with pid=56, OS id=18916
Completed: ALTER DATABASE OPEN