Oracle数据库的启动(进阶篇)

首先读环境ORACLE_SID

到此目录下$ORACLE_HOME/dbs按照以下顺序查找参数文件。 spfileorcl.ora spfile.ora initorcl.ora

连接并启动数据库到nomount，这个过程启动ORACLE实例，读取相关参数，并分配相应的内存区域。

SQL> startup nomount ORACLE instance started.

Total System Global Area 238530560 bytes Fixed Size 1335724 bytes Variable Size 163581524 bytes Database Buffers 71303168 bytes Redo Buffers 2310144 bytes

查看alter日志，了解详细的启动过程： Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0 Picked latch-free SCN scheme 2

Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST Autotune of undo retention is turned on. IMODE=BR ILAT =51

LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options.

Using parameter settings in server-side spfile /u01/oracle/product/11.1/dbs/spfileorcl.ora System parameters with non-default values: processes = 300 shared_pool_size = 100M sga_target = 228M

control_files = \"/u01/oracle/product/oradata/orcl/control01.ctl\"

control_files = \"/u01/oracle/product/flash_recovery_area/orcl/control02.ctl\" db_block_size = 8192

compatible = \"11.2.0.0.0\"

db_recovery_file_dest = \"/u01/oracle/product/flash_recovery_area\" db_recovery_file_dest_size= 3852M

undo_tablespace = \"UNDOTBS1\" remote_login_passwordfile= \"EXCLUSIVE\" db_domain = \"\"

dispatchers = \"(PROTOCOL=TCP) (SERVICE=orclXDB)\" shared_servers = 3 max_shared_servers = 10 max_dispatchers = 19 shared_server_sessions = 300 job_queue_processes = 5

audit_file_dest = \"/u01/oracle/product/admin/orcl/adump\" audit_trail = \"DB\" db_name = \"orcl\" open_cursors = 300 pga_aggregate_target = 75M

diagnostic_dest = \"/u01/oracle/product\" Fri May 16 11:09:32 2014

PMON started with pid=2, OS id=94 Fri May 16 11:09:32 2014

VKTM started with pid=3, OS id=96 at elevated priority

VKTM running at (10)millisec precision with DBRM quantum (100)ms Fri May 16 11:09:32 2014

GEN0 started with pid=4, OS id=9000 Fri May 16 11:09:32 2014

DIAG started with pid=5, OS id=9002 Fri May 16 11:09:32 2014

DBRM started with pid=6, OS id=9004 Fri May 16 11:09:32 2014

PSP0 started with pid=7, OS id=9006 Fri May 16 11:09:32 2014

DIA0 started with pid=8, OS id=9008 Fri May 16 11:09:32 2014

MMAN started with pid=9, OS id=9010 Fri May 16 11:09:32 2014

DBW0 started with pid=10, OS id=9012 Fri May 16 11:09:32 2014

LGWR started with pid=11, OS id=9014 Fri May 16 11:09:32 2014

CKPT started with pid=12, OS id=9016 Fri May 16 11:09:32 2014

SMON started with pid=13, OS id=9018 Fri May 16 11:09:32 2014

RECO started with pid=14, OS id=9020

Fri May 16 11:09:32 2014

MMON started with pid=15, OS id=9022 Fri May 16 11:09:32 2014

MMNL started with pid=16, OS id=9024 starting up 1 dispatcher(s) for network address

'(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... starting up 3 shared server(s) ...

ORACLE_BASE from environment = /u01/oracle/product

不难看出，在启动数据库到nomount状态时，参数文件被调用，数据库根据参数文件中的相关设置确定控制文件，闪回恢复区，dump跟踪文件的路径，并启动实例，分配相关的内存，从alter日志中可以查看到在此过程中相应的进程信息，其中pid代表的是数据库内的标识符编号，OS id代表是的操作系统上的进程编号（oracle11g再罗列出进程信息的同时，把进程启动的时间也呈现出来，方便查看进程启动时间，帮助数据诊断）

利用v$process视图查看进程，其中spid指的是操作系统中的进程号： SQL> select pid,spid,pname from v$process;

PID SPID PNAME ---------- ------------------------ ----- 1

2 94 PMON 3 96 VKTM 4 9000 GEN0 5 9002 DIAG 6 9004 DBRM 7 9006 PSP0 8 9008 DIA0 9 9010 MMAN 10 9012 DBW0 11 9014 LGWR

PID SPID PNAME ---------- ------------------------ -----

12 9016 CKPT 13 9018 SMON 14 9020 RECO 15 9022 MMON 16 9024 MMNL 17 9026 D000 18 9028 S000 19 9030 S001 20 9032 S002 21 9033

与操作系统中的进程进行对比：

[oracle@orcl11g trace]$ ps aux | grep ora_

oracle 94 0.0 3.3 3984 17256 ? Ss 11:09 0:00 ora_pmon_orcl oracle 96 0.0 2.5 3324 13272 ? Ss 11:09 0:00 ora_vktm_orcl oracle 9000 0.0 2.5 3324 13336 ? Ss 11:09 0:00 ora_gen0_orcl oracle 9002 0.0 2.5 3324 13212 ? Ss 11:09 0:00 ora_diag_orcl oracle 9004 0.0 2.5 3324 13344 ? Ss 11:09 0:00 ora_dbrm_orcl oracle 9006 0.0 2.5 3324 13356 ? Ss 11:09 0:00 ora_psp0_orcl oracle 9008 0.0 3.8 390860 19904 ? Ss 11:09 0:00 ora_dia0_orcl oracle 9010 0.0 3.0 3324 15692 ? Ss 11:09 0:00 ora_mman_orcl oracle 9012 0.0 3.3 393224 17356 ? Ss 11:09 0:00 ora_dbw0_orcl oracle 9014 0.0 2.5 3324 13380 ? Ss 11:09 0:00 ora_lgwr_orcl oracle 9016 0.0 3.1 3324 162 ? Ss 11:09 0:00 ora_ckpt_orcl oracle 9018 0.0 2.7 3324 14108 ? Ss 11:09 0:00 ora_smon_orcl oracle 9020 0.0 2.5 3324 13352 ? Ss 11:09 0:00 ora_reco_orcl oracle 9022 0.0 2.8 3324 14584 ? Ss 11:09 0:00 ora_mmon_orcl oracle 9024 0.0 3.3 3324 17160 ? Ss 11:09 0:00 ora_mmnl_orcl oracle 9026 0.0 2.6 390108 13420 ? Ss 11:09 0:00 ora_d000_orcl oracle 9028 0.0 2.4 3488 12712 ? Ss 11:09 0:00 ora_s000_orcl oracle 9030 0.0 2.4 3488 12708 ? Ss 11:09 0:00 ora_s001_orcl oracle 9032 0.0 2.4 3488 12712 ? Ss 11:09 0:00 ora_s002_orcl oracle 9350 0.0 0.1 3920 6 pts/3 R+ 11:31 0:00 grep ora_

注意PID=1的进程在操作系统中及告警日志中均没有体现，该进程被认为是初始化数据库的进程，启动其他进程之前即被占用。

二）：mount

根据参数文件里记录的控制文件的位置，找到控制文件，校验控制文件的完整性和一致性，如果完整性和一致性都满足，数据库进入mount状态。 mount的本质是实例和数据库进行关联。

SQL> alter database mount; Database altered.

SQL> select name from v$controlfile; NAME

----------------------------------------------------------- /u01/oracle/product/oradata/orcl/control01.ctl

/u01/oracle/product/flash_recovery_area/orcl/control02.ctl

此时数据文件的信息已经加载

SQL> select name from v$datafile; NAME

----------------------------------------------------------- /u01/oracle/product/oradata/orcl/system01.dbf /u01/oracle/product/oradata/orcl/sysaux01.dbf /u01/oracle/product/oradata/orcl/undotbs01.dbf /u01/oracle/product/oradata/orcl/users01.dbf /u01/oracle/product/oradata/orcl/example01.dbf /u01/oracle/product/oradata/orcl/test001.dbf 6 rows selected.

此时如果数据文件丢失，可以在v$recover_file视图查看相关信息： SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ---------- ------- ------- -------------------- ---------- ---------

6 ONLINE ONLINE FILE NOT FOUND 0

在启动数据到mount状态时可以在告警日志中看到如下信息：

ALTER DATABASE MOUNT

Successful mount of redo thread 1, with mount id 1375768858 Database mounted in Exclusive Mode Lost write protection disabled

Completed: ALTER DATABASE MOUNT

数据库计算mount id并将其记录在控制文件中,并开始启动心跳（heartbeat），没三秒更新一次控制文件。

HEARTBEAT可以通过X$KCCCP视图查询出： SQL> select CPHBT from X$KCCCP;

CPHBT ---------- 847995711

该心跳是通过等待时间实现：

SQL> select event#,name from v$event_name where name like '%heart%'; EVENT# NAME

---------- ---------------------------------------------------------------- 75 heartbeat monitor sleep

380 ASM mount : wait for heartbeat 563 control file heartbeat 三）：open

根据控制文件里记录的数据文件和日志文件的位置，找到数据文件和日志文件 校验数据文件，日志文件的完整性，并且校验数据文件，日志文件，控制文件 的一致性，如果完整性和一致性都满足，数据库可以打开。

在数据库OPEN时，ORACLE会先检查数据文件头中的检查点计数（CNT）是否与控制文件中的检查点计数一致。

做个测试,看一下数据文件6在不同状态下CNT的变化情况：

SQL> alter tablespace test begin backup; Tablespace altered.

SQL> alter session set events 'immediate trace name controlf level 8'; Session altered.

SQL> alter system checkpoint; System altered.

SQL> alter session set events 'immediate trace name controlf level 8'; Session altered.

SQL> alter tablespace test end backup; Tablespace altered.

SQL> alter session set events 'immediate trace name controlf level 8'; Session altered.

获取dump路径：

SQL> show parameter background_dump_dest

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------

background_dump_dest string /u01/oracle/product/diag/rdbms /orcl/orcl/trace 查看dump出的trace文件，可以找到如下信息： [oracle@orcl11g orcl]$ cat orcl_ora_5786.trc

------------------------正常情况下------------------------ DATA FILE #6:

Checkpoint cnt:26 scn: 0x0000.001195e8 05/19/2014 18:05:43

------------------------begin backup cnt+1 scn+1------------------------

DATA FILE #6:

Checkpoint cnt:27 scn: 0x0000.001196c0 05/19/2014 18:10:13 注：对表空间的begin backup会出发一次检查点操作

------------------------执行检查点 cnt+1 scn无变化------------------------ DATA FILE #6:

Checkpoint cnt:28 scn: 0x0000.001196c0 05/19/2014 18:10:13 注：表空间处于热备模式，数据文件检查点被冻结

------------------------end backup cnt+1 scn+1------------------------ DATA FILE #6:

Checkpoint cnt:29 scn: 0x0000.001196dc 05/19/2014 18:10:48 注：结束备份状态，数据文件检查点开始变化

在数据库OPEN过程中如果数据文件头中的检查点计数与控制文件中的检查点计数一直，则会检查数据文件的开始SCN是否与控制文件中的结束SCN一直，若不一致则需对该数据文件进行恢复。一旦数据库处于OPEN状态，控制文件中记录的数据文件的stop scn将置为无穷大。