３.１ Oracle体系结构之实例启动与关闭
2017-08-06 本文已影响82人
猫猫_tomluo
Oracle 实例分为四种状态，分别为：
关闭（SHUTDOWN）、非装载（NOMOUNT 启动）、装载（MOUNT）、打开（OPEN）。
１、关闭（SHUTDOWN）
如果实例没有打开，以普通用户身份时，会报错。
访问Oracle 数据库首先要启动数据库，管理员连接到空闲实例，然后才能发出启动实例或数据库的命令。
启动Oracle 服务器，每个阶段会打开不同的文件并且会完成不同的管理任务。
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ocm ~]$ sqlplus scott/oracle
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 24 13:27:36 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@ocm ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 24 13:29:19 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 368263168 bytes
Fixed Size 1345016 bytes
Variable Size 247466504 bytes
Database Buffers 113246208 bytes
Redo Buffers 6205440 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ocm ~]$ sqlplus scott/oracle
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 24 13:30:04 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
执行命令STARTUP，要求用户必须具有SYSDBA 或SYSOPER 系统权限，另外，如果要建立数据库，则必须以SYSDBA 身份登录。
２、非装载（NOMOUNT 启动）
shutdown->nomount
分配了内存，启动了进程
SQL> startup nomount
ORACLE instance started.
Total System Global Area 368263168 bytes
Fixed Size 1345016 bytes
Variable Size 239077896 bytes
Database Buffers 121634816 bytes
Redo Buffers 6205440 bytes
分配内存使用参数文件/u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Mon Jul 24 03:56:39 2017
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: ocm.example.com
Release: 2.6.18-164.el5
Version: #1 SMP Thu Sep 3 02:16:47 EDT 2009
Machine: i686
VM name: VMWare Version: 6
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora
System parameters with non-default values:
processes = 150
memory_target = 352M
control_files = "/u01/app/oracle/oradata/orcl/control01.ctl"
control_files = "/u01/app/oracle/oradata/orcl/control02.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
audit_file_dest = "/u01/app/oracle/admin/orcl/adump"
audit_trail = "DB"
db_name = "orcl"
open_cursors = 300
diagnostic_dest = "/u01/app/oracle"
Mon Jul 24 03:56:42 2017
PMON started with pid=2, OS id=18767
Mon Jul 24 03:56:42 2017
PSP0 started with pid=3, OS id=18769
Mon Jul 24 03:56:43 2017
VKTM started with pid=4, OS id=18771 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Mon Jul 24 03:56:44 2017
GEN0 started with pid=5, OS id=18775
Mon Jul 24 03:56:45 2017
DIAG started with pid=6, OS id=18777
Mon Jul 24 03:56:45 2017
DBRM started with pid=7, OS id=18779
Mon Jul 24 03:56:46 2017
DIA0 started with pid=8, OS id=18781
Mon Jul 24 03:56:46 2017
MMAN started with pid=9, OS id=18783
Mon Jul 24 03:56:48 2017
DBW0 started with pid=10, OS id=18785
Mon Jul 24 03:56:48 2017
LGWR started with pid=11, OS id=18787
Mon Jul 24 03:56:48 2017
CKPT started with pid=12, OS id=18789
Mon Jul 24 03:56:49 2017
SMON started with pid=13, OS id=18791
Mon Jul 24 03:56:49 2017
RECO started with pid=14, OS id=18793
Mon Jul 24 03:56:50 2017
MMON started with pid=15, OS id=18795
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Mon Jul 24 03:56:50 2017
MMNL started with pid=16, OS id=18797
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
查看进程
oracle@ocm orcl]$ ps -ef | grep ora_
oracle 21954 1 0 13:42 ? 00:00:00 ora_pmon_orcl
oracle 21956 1 0 13:42 ? 00:00:00 ora_psp0_orcl
oracle 21958 1 0 13:42 ? 00:00:00 ora_vktm_orcl
oracle 21962 1 0 13:42 ? 00:00:00 ora_gen0_orcl
oracle 21964 1 0 13:42 ? 00:00:00 ora_diag_orcl
oracle 21966 1 0 13:42 ? 00:00:00 ora_dbrm_orcl
oracle 21968 1 0 13:42 ? 00:00:00 ora_dia0_orcl
oracle 21970 1 1 13:42 ? 00:00:00 ora_mman_orcl
oracle 21972 1 0 13:42 ? 00:00:00 ora_dbw0_orcl
oracle 21974 1 0 13:42 ? 00:00:00 ora_lgwr_orcl
oracle 21976 1 0 13:42 ? 00:00:00 ora_ckpt_orcl
oracle 21978 1 0 13:42 ? 00:00:00 ora_smon_orcl
oracle 21980 1 0 13:42 ? 00:00:00 ora_reco_orcl
oracle 21982 1 0 13:42 ? 00:00:00 ora_mmon_orcl
oracle 21984 1 0 13:42 ? 00:00:00 ora_mmnl_orcl
oracle 21986 1 0 13:42 ? 00:00:00 ora_d000_orcl
oracle 21988 1 0 13:42 ? 00:00:00 ora_s000_orcl
oracle 22020 20904 0 13:43 pts/4 00:00:00 grep ora_
Oracle 默认打开初始化参数文件的顺序是：spfilesid.ora→spfile.ora→ initsid.ora
如果三个文件都不存在，则报错。
在NOMOUNT 状态下无法备份控制文件，MOUNT 状态下备份成功，说明数据库从NOMOUNT 到MOUNT 状态读取了控制文件。
装载数据库阶段，系统会按照初始化参数文件中control_files 参数的设置查找并打开控制文件。
Oracle 是通过控制文件在实例和数据库之间建立关联的，通过控制文件可以取得数据文件和重做日志的名称和所处状态。但对数据文件和日志文件是否存在不作检查。
SQL> alter database backup controlfile to '/u01/app/oracle/oradata/orcl/control_bak.ctl';
alter database backup controlfile to '/u01/app/oracle/oradata/orcl/control_bak.ctl'
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> alter database mount;
Database altered.
SQL> alter database backup controlfile to '/u01/app/oracle/oradata/orcl/control_bak.ctl';
Database altered.
３、装载（MOUNT）
SQL> select count(*)from scott.emp;
select count(*)from scott.emp
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> alter database open;
Database altered.
SQL> select count(*) from scott.emp;
COUNT(*)
----------
14
在MOUNT 状态下，我们无法读取到数据，说明数据文件没有被读取。当数据库OPEN后，我们可以读取到数据，说明数据库从MOUNT 到OPEN 加载了数据文件。
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open
SQL> alter database open;
Database altered.
SQL> alter system switch logfile;
System altered.
在MOUNT 状态下，我们无法手工切换日志文件组，说明联机重做日志文件没有被读取。当数据库OPEN 后，可以切换，说明数据库从MOUNT 到OPEN 加载了日志文件。
４、打开（OPEN）
在这阶段启动实例恢复SMON，在这一阶段读取了日志文件和数据文件
alter database open
Mon Jul 24 04:15:24 2017
Thread 1 opened at log sequence 14
Current log# 2 seq# 14 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Jul 24 04:15:24 2017
SMON: enabling cache recovery
[19461] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:32216454 end:32216624 diff:170 (1 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Mon Jul 24 04:15:29 2017
QMNC started with pid=20, OS id=19465
Completed: alter database open
Mon Jul 24 04:15:38 2017
Starting background process CJQ0
Mon Jul 24 04:15:39 2017
CJQ0 started with pid=22, OS id=19477
修改scott状态并登陆
SQL> alter user scott identified by oracle account unlock;
User altered.
SQL> conn scott/oracle;
Connected.
