Oracle 数据库实例启动关闭过程
?
转自：http://blog.csdn.net/robinson_0612/article/details/5542983-->Oracle 数据库实例启动关闭过程
--================================
/*
??? Oracle数据库实例的启动，严格来说应该是实例的启动，数据库仅仅是在实例启动后进行装载。Oracle数据启动的过程被划分为
几个不同的步骤，在不同的启动过程中，我们可以对其实现不同的操作，系统修复等等工作，因此了解Oracle数据启动、关闭是非常
有必要的。下面详细了Oracle Database 10g 数据库启动及关闭的过程。
?
一、数据库实例的启动
??? 启动命令：starup [force][restrict] [pfile=...] [nomount] [mount] [open]
??? 启动过程：nomount ---> mount ---> open?????????????? (startup不带参数将逐步一一完成)
??? 1.nomount阶段(即实例的启动)
??? ??? 实例的启动通常包含下列任务：
?????? ??? a.按以下顺序在$ORACLE_HOME/dbs[win平台为database]目录下搜索下列文件,即如果第一个没找到，就找下一个
?????????? ??? spfile<SID>.ora ---> spfile.ora ---> init<SID>.ora
?????????? b.分配SGA
?????????? c.启动后台进程
?????????? d.打开并修改告警<SID>.log文件及跟踪文件
?????? 使用场景：
?????? ??? 多用于数据库创建、控制文件重建、特定的备份恢复等
?????? 注意：
?????? ??? 此阶段不打开任何的控制文件及数据文件。
??????????
??? 2.mount阶段
??? ??? mount阶段完成的任务：
?????????? a.启动实例并打开控制文件，将数据库与实例关联起来
?????????? b.利用参数文件中的说明，打开并锁定控制文件
?????????? c.读取控制文件以获取数据文件和重做日志文件的名字和状态信息，但不检查数据日志文件是否存在
?????? 使用场景：
?????????? a.重命名数据文件，移动数据文件位置等(数据库打开，表空间脱机的情况下也可以重命名数据文件)
?????????? b.启用或关闭重做日志文件的归档及非归档模式
?????????? c.实现数据库的完全恢复
?????? 注意：
?????? ??? 这一步会读控制文件，如果这一步有一个控制文件损坏就无法启动
??????????
??? 3.open阶段
?????? mount阶段完成的任务：
?????????? a.打开数据文件
?????????? b.打开联机日志文件
?????? 注意：
??????????? a.在此期间，Oracle服务器将校验所偶的数据文件和联机日志文件能否打开并对数据库作一致性检查
?????????? b.如果出现一致性错误，SMON进程将启动实例恢复
?????????? c.如果任一数据文件或联机日志文件丢失，Oracle服务器将报错
??? 4.特殊的方式打开数据库
?????? 只读模式：
?????? startup open read only;
??????
?????? 如果现在在mount
?????? alter database open read only;
??????
?????? 受限模式：
?????? 有时进行数据库维护护，希望一般的用户不能登录。可以启动到该模式，这样只有用户具有
?????? restricted session 权限，才可登录到数据库。
??????
?????? 启动方法：
?????????? startup restrict;
??????????
?????? 取消受限：
?????????? ?alter system disable restricted session;
?
二、数据库实例的关闭
??? 关闭命令：shutdowm abort | immediate | transactional | normal (shutdown不带参数将缺省为normal)
??? 关闭选项：
?????? normal???? ??? --->不准许新的连接，等待当前的session 结束，等待当前的事务结束，强制检查点并关闭文件
?????? transactional --->不准许新的连接，不等待当前的session结束，等待当前的事务结束，强制检查点并关闭文件。
?????? immediate? ??? --->不准许新的连接，不等待当前的session结束，不等待当前的事务结束，强制检查点并关闭文件。
?????? abort????? ??? --->不准许新的连接，不等待当前的session结束，不等待当前的事务结束，不作强制检查点。
???
三、几类诊断文件
??? 包含有重要意义时间的具体信息，用于解决问题，用于日常更好的管理数据库
???
??? a.alterSID.log??? 告警文件
??? ??? 记录了数据库启动、关闭时间和一些重要操作及错误警告信息,包含错误号ora-,由dba管理
?????? 位置由background_dump_dest
?????? 维护alertSID.log,定期转移，系统会自动产生新的alert_sid.log
??????
??? b.后台进程跟踪文件：
?????? 记录了后台进程的一些重要信息和警告、错误等
?????? 位置：$ORACLE_BASE/admin/vvorcl/bdump/vvorcl_lgwr_15135.trc
??????
??? c.用户跟踪文件：
?????? 由用户进程产生也能由服务器进程产生，跟踪用户的SQL语句的统计信息，也包括错误信息
?????? 文件位置由user_dump_dest
?????? 用户跟踪：
????????????? 会话级：
????????????????? alter session set sql_trace=true;
?
????????????? 实例级：
????????????????? alter system set set sql_trace=true;
?
?????? 跟踪文件的大小由max_dump_file_size参数决定。跟踪时最好要限制他的大小
??????
四、动态性能视图
??? ??? 记录当前数据库的活动，并且不停的更新，故不能保证读取数据的一致性
??? ??? 监控和调整数据库
??? ??? 所有者sys
??? ??? 以v$开头
??????
?????? DICT和DICT_COLUMNS视图包含了动态性能视图描述及其列的描述
?????? v$FIXED_TALBE包含了所有视图的名字及相关信息
??????
??????
五、演示 */
??? [root@robinson ~]# su - oracle
??? --查看未启动实例前的进程情况
??? [oracle@robinson ~]$ ps -aef |grep oracle
??? root????? 3332? 3300? 0 12:20 pts/1??? 00:00:00 su - oracle
??? oracle??? 3333? 3332? 0 12:20 pts/1??? 00:00:00 -bash
??? oracle??? 3365? 3333? 0 12:20 pts/1??? 00:00:00 ps -aef
??? oracle??? 3366? 3333? 0 12:20 pts/1??? 00:00:00 grep oracle
?
??? --使用ipcs查看当前进程间通讯所用到的共享资源，内存段及信号量，
??? --可以看出当前无任何通信设备及共享资源
??? [oracle@robinson ~]$ ipcs -a
?
??? ------ Shared Memory Segments --------
??? key??????? shmid????? owner????? perms????? bytes????? nattch???? status?????
?
??? ------ Semaphore Arrays --------
??? key??????? semid????? owner????? perms????? nsems????
?
??? ------ Message Queues --------
??? key??????? msqid????? owner????? perms????? used-bytes?? messages???
?
??? --登陆到sqlplus
??? [oracle@robinson ~]$ sqlplus / as sysdba
?
??? SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 29 13:07:29 2010
?
??? Copyright (c) 1982, 2005, Oracle.? All rights reserved.
?
??? Connected to an idle instance.
?
??? --再次查看进程情况，多出了进程
??? SQL> !ps -aef | grep oracle
??? root????? 3332? 3300? 0 12:20 pts/1??? 00:00:00 su - oracle
??? oracle??? 3333? 3332? 0 12:20 pts/1??? 00:00:00 -bash
??? oracle??? 3972? 3333? 0 13:07 pts/1??? 00:00:00 sqlplus?? as sysdba
??? oracle??? 3973? 3972? 0 13:07 ???????? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
??? oracle??? 3974? 3972? 0 13:07 pts/1??? 00:00:00 /bin/bash -c ps -aef | grep oracle
??? oracle??? 3975? 3974? 0 13:07 pts/1??? 00:00:00 ps -aef
?
??? --再次查看进程间通讯资源，同样是没有任何分配
??? SQL> !ipcs -a
?
??? ------ Shared Memory Segments --------
??? key??????? shmid????? owner????? perms????? bytes????? nattch???? status?????
?
??? ------ Semaphore Arrays --------
??? key??????? semid????? owner????? perms????? nsems????
?
??? ------ Message Queues --------
??? key??????? msqid????? owner????? perms????? used-bytes?? messages???
?
??? --启动到nomount
??? SQL> startup nomount
??? ORACLE instance started.
?
??? Total System Global Area? 398458880 bytes
??? Fixed Size????????????????? 1219640 bytes
??? Variable Size???????????? 117441480 bytes
??? Database Buffers????????? 276824064 bytes
??? Redo Buffers??????????????? 2973696 bytes
?
??? --可以看到多出了一些后台进程，即为Oracle的后台进程。从开始。
??? SQL> !ps -aef | grep oracle
??? root????? 3332? 3300? 0 12:20 pts/1??? 00:00:00 su - oracle
??? oracle??? 3333? 3332? 0 12:20 pts/1??? 00:00:00 -bash
??? oracle??? 3972? 3333? 0 13:07 pts/1??? 00:00:00 sqlplus?? as sysdba
??? oracle??? 3981???? 1? 0 13:09 ???????? 00:00:00 ora_pmon_orcl
??? oracle??? 3983???? 1? 0 13:09 ???????? 00:00:00 ora_psp0_orcl
??? oracle??? 3985???? 1? 0 13:09 ???????? 00:00:00 ora_mman_orcl
??? oracle??? 3987???? 1? 0 13:09 ???????? 00:00:00 ora_dbw0_orcl
??? oracle??? 3989???? 1? 0 13:09 ???????? 00:00:00 ora_lgwr_orcl
??? oracle??? 3991???? 1? 0 13:09 ???????? 00:00:00 ora_ckpt_orcl
??? oracle??? 3993???? 1? 0 13:09 ???????? 00:00:00 ora_smon_orcl
??? oracle??? 3995???? 1? 0 13:09 ???????? 00:00:00 ora_reco_orcl
??? oracle??? 3997???? 1? 0 13:09 ???????? 00:00:00 ora_cjq0_orcl
??? oracle??? 3999???? 1? 0 13:09 ???????? 00:00:00 ora_mmon_orcl
??? oracle??? 4001???? 1? 0 13:09 ???????? 00:00:00 ora_mmnl_orcl
??? oracle??? 4003???? 1? 0 13:09 ???????? 00:00:00 ora_d000_orcl
??? oracle??? 4005???? 1? 0 13:09 ???????? 00:00:00 ora_s000_orcl
??? oracle??? 4006? 3972? 0 13:09 ???????? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
??? oracle??? 4009? 3972? 0 13:10 pts/1??? 00:00:00 /bin/bash -c ps -aef | grep oracle
??? oracle??? 4010? 4009? 0 13:10 pts/1??? 00:00:00 ps -aef ??????
?
??? --可以看到已经分配了SGA
??? SQL> !ipcs -a
?
??? ------ Shared Memory Segments --------
??? key??????? shmid????? owner????? perms????? bytes????? nattch???? status?????
??? 0x99eb282c 65537????? oracle??? 640??????? 402653184? 14?????????????????????
?
??? ------ Semaphore Arrays --------
??? key??????? semid????? owner????? perms????? nsems????
??? 0x6df43ca0 98304????? oracle??? 640??????? 127??????
??? 0x6df43ca1 131073???? oracle??? 640??????? 127??????
??? 0x6df43ca2 163842???? oracle??? 640??????? 127??????
?
??? ------ Message Queues --------
??? key??????? msqid????? owner????? perms????? used-bytes?? messages
?
??? --查看控制文件，没有任何数据，因为此阶段控制文件并没有打开
??? SQL> select * from v$controlfile;
?
??? no rows selected
?
??? --查看数据文件和日志文件，提示database not mounted
??? SQL> select * from v$datafile;
??? select * from v$datafile
????????????? ? *
??? ERROR at line 1:
??? ORA-01507: database not mounted
?
??? SQL> select * from v$logfile;
??? select * from v$logfile
????????????? ? *
??? ERROR at line 1:
??? ORA-01507: database not mounted
?
??? --切换到mount阶段
??? SQL> alter database mount;
?
??? Database altered.
?
??? --可以查看控制文件的相关信息
??? SQL> select * from v$controlfile;
?
??? STATUS? NAME?????????????????????????????????????????????? IS_ BLOCK_SIZE FILE_SIZE_BLKS
??? ------- -------------------------------------------------- --- ---------- --------------
?????????? /u01/app/oracle/oradata/orcl/control01.ctl???????? NO?????? 16384??????????? 430
?????????? /u01/app/oracle/oradata/orcl/control02.ctl???????? NO?????? 16384??????????? 430
?????????? /u01/app/oracle/oradata/orcl/control03.ctl???????? NO?????? 16384??????????? 430
??????????
??? --可以查看数据文件的相关信息
??? SQL> select FILE#,STATUS,ENABLED,CHECKPOINT_CHANGE#,CHECKPOINT_TIME,ONLINE_TIME,NAME from v$datafile;
?
?????? ?FILE# STATUS? ENABLED??? CHECKPOINT_CHANGE# CHECKPOIN ONLINE_TI NAME
??? ---------- ------- ---------- ------------------ --------- --------- --------------------------------------------------
?????????? ?1 SYSTEM? READ WRITE??? ????????2027235 28-APR-10 06-APR-10 /u01/app/oracle/oradata/orcl/system01.dbf
?????????? ?2 ONLINE? READ WRITE??????????? 2027235 28-APR-10 06-APR-10 /u01/app/oracle/oradata/orcl/undotbs01.dbf
?????????? ?3 ONLINE? READ WRITE??????????? 2027235 28-APR-10 06-APR-10 /u01/app/oracle/oradata/orcl/sysaux01.dbf
?????????? ?4 ONLINE? READ WRITE??????????? 2027235 28-APR-10 06-APR-10 /u01/app/oracle/oradata/orcl/users01.dbf
?????????? ?5 ONLINE? READ WRITE??????????? 2027235 28-APR-10?????????? /u01/app/oracle/oradata/orcl/example01.dbf
?????????? ?6 ONLINE? READ WRITE??????????? 2027235 28-APR-10?????????? /u01/app/oracle/oradata/orcl/Test.dbf
?????????? ?7 ONLINE? READ WRITE??????????? 2027235 28-APR-10?????????? /u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_s
?????????????????????????????????????????????????????????????? ?_5vrl1t7h_.dbf?????
??? --可以查看日志文件的相关信息
??? SQL> select * from v$logfile;
?
?????? GROUP# STATUS? TYPE??? MEMBER?????????????????????????????????????????????????????? IS_
??? ---------- ------- ------- ------------------------ ---
?????????? ?3???????? ONLINE? /u01/app/oracle/oradata/orcl/redo03.log????????????????????? NO
?????????? ?2???????? ONLINE? /u01/app/oracle/oradata/orcl/redo02.log????????????????????? NO
?????????? ?1???????? ONLINE? /u01/app/oracle/oradata/orcl/redo01.log????????????????????? NO?????? ???
?
??? --数据不可访问，因为在此阶段，数据文件，日志文件并没有打开
??? SQL> select * from scott.emp;
??? select * from scott.emp
???????????????????? *
??? ERROR at line 1:
??? ORA-01219: database not open: queries allowed on fixed tables/views only????
?
??? --切换到open 阶段
??? SQL> alter database open;
?
??? Database altered.
?
??? --再次查看进程信息，多出了之后的相关进程
??? SQL> !ps -aef | grep oracle
??? oracle??? 3981???? 1? 0 13:09 ???????? 00:00:00 ora_pmon_orcl
??? oracle??? 3983???? 1? 0 13:09 ???????? 00:00:00 ora_psp0_orcl
??? oracle??? 3985???? 1? 0 13:09 ???????? 00:00:00 ora_mman_orcl
??? oracle??? 3987???? 1? 0 13:09 ???????? 00:00:00 ora_dbw0_orcl
??? oracle??? 3989???? 1? 0 13:09 ???????? 00:00:00 ora_lgwr_orcl
??? oracle??? 3991???? 1? 0 13:09 ???????? 00:00:00 ora_ckpt_orcl
??? oracle??? 3993???? 1? 0 13:09 ???????? 00:00:00 ora_smon_orcl
??? oracle??? 3995???? 1? 0 13:09 ???????? 00:00:00 ora_reco_orcl
??? oracle??? 3997???? 1? 0 13:09 ???????? 00:00:00 ora_cjq0_orcl
??? oracle??? 3999???? 1? 0 13:09 ???????? 00:00:00 ora_mmon_orcl
??? oracle??? 4001???? 1? 0 13:09 ???????? 00:00:00 ora_mmnl_orcl
??? oracle??? 4003???? 1? 0 13:09 ???????? 00:00:00 ora_d000_orcl
??? oracle??? 4005???? 1? 0 13:09 ???????? 00:00:00 ora_s000_orcl
??? root????? 4068? 4036? 0 13:29 pts/1??? 00:00:00 su - oracle
??? oracle??? 4069? 4068? 0 13:29 pts/1??? 00:00:00 -bash
??? oracle??? 4101? 4069? 0 13:29 pts/1??? 00:00:00 sqlplus
??? oracle??? 4102? 4101? 0 13:29 ???????? 00:00:01 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
??? oracle??? 4137???? 1? 0 13:54 ???????? 00:00:00 ora_qmnc_orcl
??? oracle??? 4139???? 1? 0 13:54 ???????? 00:00:01 ora_j000_orcl
??? oracle??? 4141???? 1? 0 13:54 ???????? 00:00:00 ora_j001_orcl
??? oracle??? 4143???? 1? 0 13:54 ???????? 00:00:00 ora_j002_orcl
??? oracle??? 4145???? 1? 0 13:54 ???????? 00:00:00 ora_j003_orcl
??? oracle??? 4147???? 1? 0 13:54 ???????? 00:00:00 ora_j004_orcl
??? oracle??? 4149???? 1? 0 13:54 ???????? 00:00:00 ora_j005_orcl
??? oracle??? 4159???? 1? 0 13:54 ???????? 00:00:00 ora_q001_orcl
??? oracle??? 4167???? 1? 0 13:56 ???????? 00:00:00 ora_q002_orcl
??? oracle??? 4169???? 1? 0 13:57 ???????? 00:00:00 ora_q003_orcl
??? oracle??? 4172? 4101? 0 13:58 pts/1??? 00:00:00 /bin/bash -c ps -aef | grep oracle
??? oracle??? 4173? 4172? 0 13:58 pts/1??? 00:00:00 ps -aef
?
??? --数据已可正常访问
??? SQL> select * from scott.emp where ename = 'SCOTT';
?
?????? ?EMPNO ENAME????? JOB????????????? MGR HIREDATE???????? SAL?????? COMM
??? ---------- ---------- --------- ---------- --------- ---------- ----------
?????? DEPTNO
??? ----------
?????? ? 7788 SCOTT????? ANALYST???????? 7566 19-APR-87?????? 3000
?????? ?
??? --查看告警日志文件路径
??? SQL> show parameter background_dump_dest
?
??? NAME???????????????????????????????? TYPE??????? VALUE
??? ------------------------------------ ----------- ------------------------------
??? background_dump_dest???????????????? string????? /u01/app/oracle/admin/orcl/bdu
????????????????????????????????????????????? ?mp ?
????????????????????????????????????????????? ?
??? --查看动态性能视图
??? SQL> select sql_text,executions from v$sql where cpu_time >2000000;
?
??? SQL_TEXT
??? --------------------------------------------
??? EXECUTIONS
??? ----------
??? select s.synonym_name object_name, o.object_type from all_synonyms s,????? sys.a
??? ll_objects o where s.owner in ('PUBLIC', :schema) and o.owner = s.table_owner an
??? d o.object_name = s.table_name and o.object_type in ('TABLE', 'VIEW', 'PACKAGE',
??? 'TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')
?????????? ?2
?
??? SQL> select username,status,server,state from v$session where program = 'plsqldev.exe';
?
??? USERNAME?????????????????????? STATUS?? SERVER??? STATE
??? ------------------------------ -------- --------- -------------------
??? SYS??????????????????????????? INACTIVE DEDICATED WAITING
??? SYS??????????????????????????? INACTIVE DEDICATED WAITING
?
??? SQL> select sid,ctime from v$lock where sid = 275;
?
?????? ?? SID????? CTIME
??? ---------- ----------
?????? ?? 275?????? 9607
?????? ?? 275?????? 9270
?????? ?? 275?????? 9243
/*
六、更多*/
?
???
??? SQL/PLSQL 基础
???
??? Oralce 10g 使用DBCA创建数据库
???
??? 使用Uniread实现SQLplus翻页功能
???
??? Linux (RHEL 5.4)下安装Oracle 10g R2
???
??? VmWare6.5.2下安装RHEL 5.4（配置Oracle安装环境）
???
??? Oracle实例和Oracle数据库(Oracle体系结构)
???
??? Oracle相关
