oracle切换实例启动,３.１　Oracle体系结构之实例启动与关闭

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.