一、ORACLE结构
在前一篇BLOG中已经说明了ORACLE的基本结构和组成，这一篇来说明ORACLE的启动过程情况。
这一张图很经典。这张图完整的介绍了数据库启动的过程。
Oracle数据库实例的启动，基本分为ORACLE INSTANCE启动+ORACLE DATABASE LOAD.ORACLE的启动可以划分为如上图的几种模式
二、ORACLE的启动过程说明
启动命令：starup [force][restrict] [pfile=...] [nomount] [mount] [open]
启动过程：nomount ---> mount ---> open
2.1、实例启动到nomount阶段
startup nomount;
SQL> show user USER is "SYS" SQL> startup nomount; ORACLE instance started. Total System Global Area 218103808 bytes Fixed Size 1218604 bytes Variable Size 71305172 bytes Database Buffers 142606336 bytes Redo Buffers 2973696 bytes SQL> select status from v$instance; STATUS ------------ STARTED SQL>
启动过程中，告警日志记录的启动信息如下：
[oracle@ocmserver bdump]$ more alert_ocm.log Mon Jul 1 22:26:27 2013 Starting ORACLE instance (normal) -----启动为normal，说明是startup normal;如果是force启动，则会在这条信息之前加入如下信息
Mon Jul 1 22:28:47 2013
Shutting down instance (abort)
License high water mark = 2
Instance terminated by USER, pid = 6058
Mon Jul 1 22:28:49 2013
------------------------------------------------------------------- LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Picked latch-free SCN scheme 2 Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST Autotune of undo retention is turned on. IMODE=BR ILAT =18 LICENSE_MAX_USERS = 0 SYS auditing is disabled ksdpec: called for event 13740 prior to event group initialization Starting up ORACLE RDBMS Version: 10.2.0.1.0. System parameters with non-default values: processes = 150 __shared_pool_size = 62914560 __large_pool_size = 4194304 __java_pool_size = 4194304 __streams_pool_size = 0 sga_target = 218103808 control_files = /opt/oracle/oradata/ocm/control01.ctl, /opt/oracle/oradata/ocm/control02.ctl, /opt/oracle/oradata/ocm/control03.ctl db_block_size = 8192 __db_cache_size = 142606336 compatible = 10.2.0.1.0 db_file_multiblock_read_count= 16 db_recovery_file_dest = /opt/oracle/flash_recovery_area db_recovery_file_dest_size= 2147483648 undo_management = AUTO undo_tablespace = UNDOTBS1 remote_login_passwordfile= EXCLUSIVE db_domain = dispatchers = (PROTOCOL=TCP) (SERVICE=ocmXDB) job_queue_processes = 10 background_dump_dest = /opt/oracle/admin/ocm/bdump user_dump_dest = /opt/oracle/admin/ocm/udump core_dump_dest = /opt/oracle/admin/ocm/cdump audit_file_dest = /opt/oracle/admin/ocm/adump db_name = ocm open_cursors = 300 pga_aggregate_target = 71303168 PMON started with pid=2, OS id=5983 PSP0 started with pid=3, OS id=5985 MMAN started with pid=4, OS id=5987 DBW0 started with pid=5, OS id=5989 LGWR started with pid=6, OS id=5991 CKPT started with pid=7, OS id=5993 SMON started with pid=8, OS id=5995 CJQ0 started with pid=10, OS id=5999 RECO started with pid=9, OS id=5997 MMON started with pid=11, OS id=6001 Mon Jul 1 22:26:28 2013 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... MMNL started with pid=12, OS id=6003 Mon Jul 1 22:26:28 2013 starting up 1 shared server(s) ...
2.2、ORACLE的幕后工作
实例启动的时候，我们发现没有做太多的选择和事情。但实际上ORACLE按照如下逻辑做了很多幕后工作：
1）搜寻参数文件，一般顺序为spfile<sid>.ora -->spfile.ora -->init<sid>.ora
2）分配SGA大小，见上例
3）启动后台日志
4）开启告警追踪日志等信息
小实验：如果init<sid>.ora不存在（手动删除initmydb.ora)
SQL> startup nomount; ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/opt/oracle/product/dbs/initocm.ora' SQL> startup nomount spfile='/opt/oracle/product/dbs/spfilemydb.ora'; SP2-0714: invalid combination of STARTUP options SQL> startup nomount; ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/opt/oracle/product/dbs/initocm.ora' SQL> startup nomount pfile='/opt/oracle/product/dbs/initmydb.ora'; ORACLE instance started. Total System Global Area 218103808 bytes Fixed Size 1218604 bytes Variable Size 71305172 bytes Database Buffers 142606336 bytes Redo Buffers 2973696 bytes SQL> alter database mount; Database altered. SQL> alter database open; Database altered. SQL> SQL> ! more /opt/oracle/product/dbs/initmydb.ora *.spfile='/opt/oracle/product/dbs/spfilemydb.ora' SQL> create spfile from pfile='/opt/oracle/product/dbs/initmydb.ora'; File created. SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /opt/oracle/product/dbs/spfile mydb.ora SQL>
通过上面例子我们发现无法通过startup nomount spfile='/opt/oracle/product/dbs/spfilemydb.ora'启动。
但是却可以通过startup nomount pfile='/opt/oracle/product/dbs/initmydb.ora'启动起来。这是一个必须理解和记住的地方。其中initmydb.ora只相当于做了一个连接而已。
2.3、启动到mount阶段
alter database mount;
由上图可以指导mount过程主要做如下事项：
1）启动实例并打开控制文件，将数据库与实例关联起来
2）利用参数文件中的说明，打开并锁定控制文件
3）读取控制文件以获取数据文件和重做日志文件的名字和状态信息，但不检查数据日志文件是否存在
这一部主要是和控制文件相关，如果控制文件不存在，或者控制文件信息和之前不一致，则需要回复或者取舍（丢失数据，还是启动数据库）。在控制文件实验一节已经说明。
在这一个mount阶段，无法将数据退回到nomount状态
SQL> alter database mount; Database altered. SQL> alter database nomount; alter database nomount * ERROR at line 1: ORA-02231: missing or invalid option to ALTER DATABASE SQL> alter database nomount; alter database nomount * ERROR at line 1: ORA-02231: missing or invalid option to ALTER DATABASE SQL>
可以用dismount参数，但是dismount后却无法再mount数据了
SQL> STARTUP MOUNT; ORACLE instance started. Total System Global Area 218103808 bytes Fixed Size 1218604 bytes Variable Size 71305172 bytes Database Buffers 142606336 bytes Redo Buffers 2973696 bytes Database mounted. SQL> ALTER DATABASE DISMOUNT; Database altered. SQL> ALTER DATABASE MOUNT; ALTER DATABASE MOUNT * ERROR at line 1: ORA-00750: database has been previously mounted and dismounted SQL>
4）可以直接启动到mount状态
SQL> startup mount; ORACLE instance started. Total System Global Area 218103808 bytes Fixed Size 1218604 bytes Variable Size 71305172 bytes Database Buffers 142606336 bytes Redo Buffers 2973696 bytes Database mounted. SQL>
3、open阶段
alter database open;
由上图可以看出来，这个阶段主要是oracle database的工作，而这个工作信息是从controlfile里面读取出来的。打开数据库文件，REDO LOG文件及Archive Log文件等。在这个过程中会做如下信息检查来维持RDBMS所必须遵从的ACID属性：
- Oracle服务器将校验所偶的数据文件和联机日志文件能否打开并对数据库作一致性检查
- 如出现一致性错误，SMON进程将启动实例恢复
- 如任一数据文件或联机日志文件丢失，Oracle服务器将报错
在这个过程也无法回到nomount阶段
SQL> alter database nomount; alter database nomount * ERROR at line 1: ORA-02231: missing or invalid option to ALTER DATABASE SQL> alter database mount; alter database mount * ERROR at line 1: ORA-01100: database already mounted SQL>
但是缺可以close，甚至是dismount;
SQL> alter database close; Database altered. SQL> alter database dismount; Database altered. SQL> 这样的过程无法反复，即： 变成close后，无法再open; 变成dismount后，无法再mount; SQL> alter database open; alter database open * ERROR at line 1: ORA-16196: database has been previously opened and closed SQL>
alter database close的日志信息如下：
alter database close Mon Jul 1 22:47:16 2013 Stopping background process CJQ0 Mon Jul 1 22:47:16 2013 SMON: disabling tx recovery Mon Jul 1 22:47:16 2013 Stopping background process QMNC Mon Jul 1 22:47:21 2013 Stopping Job queue slave processes Mon Jul 1 22:47:24 2013 Waiting for Job queue slaves to complete Mon Jul 1 22:48:03 2013 Job queue slave processes stopped Mon Jul 1 22:48:03 2013 SMON: disabling cache recovery Mon Jul 1 22:48:03 2013 Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active Thread 1 closed at log sequence 3 Successful close of redo thread 1 Mon Jul 1 22:48:03 2013 Completed: alter database close
4、其他方式打开数据库
4.1 Read only模式打开数据库
startup open read only;
or
alter database open read only;
SQL> alter database open read only; Database altered. SQL> select status from v$instance; STATUS ------------ OPEN SQL> SQL> create table t1(name char(20)); create table t1(name char(20)) * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-16000: database open for read-only access SQL>
4.2 restrict模式
SQL> startup restrict; ORACLE instance started. Total System Global Area 218103808 bytes Fixed Size 1218604 bytes Variable Size 71305172 bytes Database Buffers 142606336 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. SQL> select status from v$instance; STATUS ------------ OPEN SQL>
5、oracle正常启动的日志简单分析
SQL> show parameter background_dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /opt/oracle/admin/ocm/bdump
SQL>
[oracle@ocmserver bdump]$ more alert_ocm.log Mon Jul 1 22:23:18 2013 Starting ORACLE instance (normal) -----可以看出来是正常启动，normal一般是没有带参数。 LICENSE_MAX_SESSION = 0 ----如下开始读参数文件，控制文件内的相关信息 LICENSE_SESSIONS_WARNING = 0 Picked latch-free SCN scheme 2 Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST Autotune of undo retention is turned on. IMODE=BR ILAT =18 LICENSE_MAX_USERS = 0 SYS auditing is disabled ksdpec: called for event 13740 prior to event group initialization Starting up ORACLE RDBMS Version: 10.2.0.1.0. System parameters with non-default values: processes = 150 __shared_pool_size = 62914560 __large_pool_size = 4194304 __java_pool_size = 4194304 __streams_pool_size = 0 sga_target = 218103808 control_files = /opt/oracle/oradata/ocm/control01.ctl, /opt/oracle/oradata/ocm/control02.ctl, /opt/oracle/oradata/ocm/control03.ctl db_block_size = 8192 __db_cache_size = 142606336 compatible = 10.2.0.1.0 db_file_multiblock_read_count= 16 db_recovery_file_dest = /opt/oracle/flash_recovery_area db_recovery_file_dest_size= 2147483648 undo_management = AUTO undo_tablespace = UNDOTBS1 remote_login_passwordfile= EXCLUSIVE db_domain = dispatchers = (PROTOCOL=TCP) (SERVICE=ocmXDB) job_queue_processes = 10 background_dump_dest = /opt/oracle/admin/ocm/bdump user_dump_dest = /opt/oracle/admin/ocm/udump core_dump_dest = /opt/oracle/admin/ocm/cdump audit_file_dest = /opt/oracle/admin/ocm/adump db_name = ocm open_cursors = 300 pga_aggregate_target = 71303168 PMON started with pid=2, OS id=5838 -----开始启动后台进程。 PSP0 started with pid=3, OS id=5840 MMAN started with pid=4, OS id=5842 DBW0 started with pid=5, OS id=5844 LGWR started with pid=6, OS id=5846 CKPT started with pid=7, OS id=5848 SMON started with pid=8, OS id=5850 RECO started with pid=9, OS id=5852 CJQ0 started with pid=10, OS id=5854 MMON started with pid=11, OS id=5856 Mon Jul 1 22:23:19 2013 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... MMNL started with pid=12, OS id=5858 Mon Jul 1 22:23:19 2013 starting up 1 shared server(s) ... Mon Jul 1 22:23:19 2013 ALTER DATABASE MOUNT -----启动到mount状态； Mon Jul 1 22:23:23 2013 Setting recovery target incarnation to 2 Mon Jul 1 22:23:23 2013 Successful mount of redo thread 1, with mount id 2202287127 ----挂载redo成功； Mon Jul 1 22:23:23 2013 Database mounted in Exclusive Mode -----数据库挂载为排他模式； Completed: ALTER DATABASE MOUNT Mon Jul 1 22:23:24 2013 ALTER DATABASE OPEN -----打开数据库 Mon Jul 1 22:23:24 2013 Thread 1 opened at log sequence 2 Current log# 1 seq# 2 mem# 0: /opt/oracle/oradata/ocm/redo01.log Successful open of redo thread 1 Mon Jul 1 22:23:24 2013 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Mon Jul 1 22:23:24 2013 SMON: enabling cache recovery Mon Jul 1 22:23:24 2013 Successfully onlined Undo Tablespace 1. -----undo表空间online成功； Mon Jul 1 22:23:24 2013 SMON: enabling tx recovery Mon Jul 1 22:23:24 2013 Database Characterset is AL32UTF8 replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC QMNC started with pid=16, OS id=5866 Mon Jul 1 22:23:25 2013 db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Mon Jul 1 22:23:27 2013 Completed: ALTER DATABASE OPEN -----启动完成； [oracle@ocmserver bdump]$
三、ORACLE的关闭
1、关闭命令
shutdowm abort | immediate | transactional | normal (缺省)
2、关闭命令解释
normal --->不准许新的连接，等待当前的session 结束，等待当前的事务结束，强制检查点并关闭文件
transactional --->不准许新的连接，不等待当前的session结束，等待当前的事务结束，强制检查点并关闭文件。
immediate --->不准许新的连接，不等待当前的session结束，不等待当前的事务结束，强制检查点并关闭文件。
abort --->不准许新的连接，不等待当前的session结束，不等待当前的事务结束，不作强制检查点。
3、ORACLE关闭过程见如下log日志信息
[oracle@ocmserver bdump]$ more alert_ocm.log Mon Jul 1 22:18:00 2013 Starting background process EMN0 EMN0 started with pid=21, OS id=5659 Mon Jul 1 22:18:00 2013 Shutting down instance: further logons disabled ----正常关闭，后续登陆被禁止； Mon Jul 1 22:18:01 2013 Stopping background process CJQ0 ------停止CJQ0进程； Mon Jul 1 22:18:01 2013 Stopping background process QMNC Mon Jul 1 22:18:02 2013 Stopping background process MMNL Mon Jul 1 22:18:02 2013 Stopping background process MMON Mon Jul 1 22:18:02 2013 Shutting down instance (immediate) License high water mark = 6 Mon Jul 1 22:18:02 2013 Stopping Job queue slave processes Mon Jul 1 22:18:02 2013 Job queue slave processes stopped All dispatchers and shared servers shutdown Mon Jul 1 22:18:04 2013 ALTER DATABASE CLOSE NORMAL -------数据库正常关闭； Mon Jul 1 22:18:04 2013 SMON: disabling tx recovery SMON: disabling cache recovery Mon Jul 1 22:18:05 2013 Shutting down archive processes ----关闭归档 Archiving is disabled Archive process shutdown avoided: 0 active Thread 1 closed at log sequence 2 Successful close of redo thread 1 Mon Jul 1 22:18:05 2013 Completed: ALTER DATABASE CLOSE NORMAL -----DATABASE关闭完成； Mon Jul 1 22:18:05 2013 ALTER DATABASE DISMOUNT -----卸载 Completed: ALTER DATABASE DISMOUNT ARCH: Archival disabled due to shutdown: 1089 Shutting down archive processes ----停止归档进程； Archiving is disabled Archive process shutdown avoided: 0 active ARCH: Archival disabled due to shutdown: 1089 Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active [oracle@ocmserver bdump]$
四、小结
以上为正常的启动和关闭ORACLE的一些简单分析，从官网上找了个图片。深入理解这些启动过程有助于对问题的深入分析和理解，在遇到问题的时能迅速定位并解决问题。
我们常用的查看日志，跟踪文件及转储文件等。后续继续努力学习转储文件，跟踪文件的使用。如下为启动、关闭过程中常用视图，留存后查；
v$datafile; v$controlfile; v$logfile; v$log; v$sql v$session v$lock v$insance; show parameter background_dump_dest select FILE#,STATUS,ENABLED,CHECKPOINT_CHANGE#,CHECKPOINT_TIME,ONLINE_TIME,NAME from v$datafile;