Oracle正常启动过程顺序

jiawang · 发表于 2022-9-22 09:30:09

本帖最后由 jiawang 于 2022-9-22 11:29 编辑

Oracle数据库有四种状态：SHUTDOWN、NOMOUNT、MOUNT、OPEN.
Oracle数据库启动主要包括三个过程：

（1）启动数据库到 NOMOUNT 状态；（只是启动实例，尚未打开控制文件）
（2）启动数据库到 MOUNT 状态；（打开控制文件）
（3）启动数据库到 OPEN 状态。（执行控制文件）

shutdown→nomount过程：

SQL> startup nomount;

ORA-32004: obsolete or deprecatedparameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 790941696 bytes

Fixed Size 1367536 bytes

Variable Size 490734096 bytes

Database Buffers 293601280 bytes

Redo Buffers 5238784 bytes　　

nomount过程可以访问的动态性能视图：v$parameter，v$sga，v$option，v$process，v$version，v$instance

1、读初始参数文件

SQL> select * from v$ parameter;

SQL> desc v$parameter;

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

NUM NUMBER

NAME VARCHAR2(80)

TYPE NUMBER

VALUE VARCHAR2(4000)

DISPLAY_VALUE VARCHAR2(4000)

ISDEFAULT VARCHAR2(9)

ISSES_MODIFIABLE VARCHAR2(5)

ISSYS_MODIFIABLE VARCHAR2(9)

ISINSTANCE_MODIFIABLE VARCHAR2(5)

ISMODIFIED VARCHAR2(10)

ISADJUSTED VARCHAR2(5)

ISDEPRECATED VARCHAR2(5)

ISBASIC VARCHAR2(5)

DESCRIPTION VARCHAR2(255)

UPDATE_COMMENT VARCHAR2(255)

HASH NUMBER

2、分配物理内存SGA情况

SQL> select * from v$sga;

NAME VALUE

------------------------------ ---------------

Fixed Size 1367536

Variable Size 490734096

Database Buffers 293601280

Redo Buffers 5238784

3、查看后台进程

SQL> desc v$process;

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

ADDR RAW(4)

PID NUMBER

SPID VARCHAR2(24)

PNAME VARCHAR2(5)

USERNAME VARCHAR2(15)

SERIAL# NUMBER

TERMINAL VARCHAR2(30)

PROGRAM VARCHAR2(48)

TRACEID VARCHAR2(255)

TRACEFILE VARCHAR2(513)

BACKGROUND VARCHAR2(1)

LATCHWAIT VARCHAR2(8)

LATCHSPIN VARCHAR2(8)

PGA_USED_MEM NUMBER

PGA_ALLOC_MEM NUMBER

PGA_FREEABLE_MEM NUMBER

PGA_MAX_MEM NUMBER

4、数据库安装情况

SQL> select * from v$option;

PARAMETER VALUE

---------------------------------------- --------------------

Partitioning TRUE

Objects TRUE

Real Application Clusters FALSE

Advanced replication TRUE

Bit-mapped indexes TRUE

Connection multiplexing TRUE

Connection pooling TRUE

Database queuing TRUE

Incremental backup and recovery TRUE

Instead-of triggers TRUE

Parallel backup and recovery TRUE

PARAMETER VALUE

---------------------------------------- --------------------

Parallel execution TRUE

Parallel load TRUE

Point-in-time tablespace recovery TRUE

Fine-grained access control TRUE

Proxy authentication/authorization TRUE

Change Data Capture TRUE

Plan Stability TRUE

Online Index Build TRUE

Coalesce Index TRUE

Managed Standby TRUE

Materialized view rewrite TRUE

PARAMETER VALUE

---------------------------------------- --------------------

Database resource manager TRUE

Spatial TRUE

Automatic Storage Management FALSE

Export transportable tablespaces TRUE

Transparent Application Failover TRUE

Fast-Start Fault Recovery TRUE

Sample Scan TRUE

Duplexed backups TRUE

Java TRUE

OLAP Window Functions TRUE

Block Media Recovery TRUE

PARAMETER VALUE

---------------------------------------- --------------------

Fine-grained Auditing TRUE

Application Role TRUE

Enterprise User Security TRUE

Oracle Data Guard TRUE

Oracle Label Security FALSE

OLAP TRUE

Basic Compression TRUE

Join index TRUE

Trial Recovery TRUE

Data Mining TRUE

Online Redefinition TRUE

PARAMETER VALUE

---------------------------------------- --------------------

Streams Capture TRUE

File Mapping TRUE

Block Change Tracking TRUE

Flashback Table TRUE

Flashback Database TRUE

Transparent Data Encryption TRUE

Backup Encryption TRUE

Unused Block Compression TRUE

Oracle Database Vault FALSE

Result Cache TRUE

SQL Plan Management TRUE

PARAMETER VALUE

---------------------------------------- --------------------

SecureFiles Encryption TRUE

Real Application Testing TRUE

Flashback Data Archive TRUE

DICOM TRUE

Active Data Guard TRUE

Server Flash Cache TRUE

Advanced Compression TRUE

XStream TRUE

Deferred Segment Creation TRUE

Data Redaction TRUE

65 rows selected.

5、查看版本信息

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

6、安装实例情况

SQL> desc v$instance;

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

INSTANCE_NUMBER NUMBER

INSTANCE_NAME VARCHAR2(16)

HOST_NAME VARCHAR2(64)

VERSION VARCHAR2(17)

STARTUP_TIME DATE

STATUS VARCHAR2(12)

PARALLEL VARCHAR2(3)

THREAD# NUMBER

ARCHIVER VARCHAR2(7)

LOG_SWITCH_WAIT VARCHAR2(15)

LOGINS VARCHAR2(10)

SHUTDOWN_PENDING VARCHAR2(3)

DATABASE_STATUS VARCHAR2(17)

INSTANCE_ROLE VARCHAR2(18)

ACTIVE_STATE VARCHAR2(9)

BLOCKED VARCHAR2(3)

SQL> select INSTANCE_NAME,HOST_NAME,INSTANCE_NUMBER,ARCHIVER from v$instance;

INSTANCE_NAME HOST_NAME INSTANCE_NUMBER ARCHIVE

---------------- -------------------- --------------- -------

oracle strong33 1 STOPPED

7、查看alert文件

[oracle@strong33 trace]$ cd /u01/app/oracle/diag/rdbms/oracle/oracle/trace

oracle@strong33 trace]$ ls alert*.log

alert_oracle.log

[oracle@strong33 trace]$ tail -500 alert_oracle.log |more

ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1

System name: Linux

Node name: strong33

Release: 2.6.32-358.el6.i686

Version: #1 SMP Tue Jan 29 11:48:01 EST 2013

Machine: i686

VM name: VMWare Version: 6

Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfileoracle.ora

System parameters with non-default values:

processes = 150

memory_target = 756M

control_files = "/u01/app/oracle/oradata/oracle/control01.ctl"

control_files = "/u01/app/oracle/fast_recovery_area/oracle/control02.ctl"

db_block_size = 8192

compatible = "11.2.0.4.0"

log_archive_start = TRUE

db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"

db_recovery_file_dest_size= 4152M

undo_tablespace = "UNDOTBS1"

remote_login_passwordfile= "EXCLUSIVE"

db_domain = ""

dispatchers = "(PROTOCOL=TCP) (SERVICE=oracleXDB)"

audit_file_dest = "/u01/app/oracle/admin/oracle/adump"

audit_trail = "DB"

db_name = "oracle"

open_cursors = 300

diagnostic_dest = "/u01/app/oracle"

Deprecated system parameters with specified values:

log_archive_start

End of deprecated system parameter listing

Wed Jun 21 14:52:55 2017

PMON started with pid=2, OS id=19825

Wed Jun 21 14:52:55 2017

PSP0 started with pid=3, OS id=19827

Wed Jun 21 14:52:56 2017

VKTM started with pid=4, OS id=19829

VKTM running at (100ms) precision

Wed Jun 21 14:52:56 2017

GEN0 started with pid=5, OS id=19833

Wed Jun 21 14:52:56 2017

DIAG started with pid=6, OS id=19835

Wed Jun 21 14:52:56 2017

DBRM started with pid=7, OS id=19837

Wed Jun 21 14:52:56 2017

DIA0 started with pid=8, OS id=19839

Wed Jun 21 14:52:56 2017

MMAN started with pid=9, OS id=19841

Wed Jun 21 14:52:56 2017

DBW0 started with pid=10, OS id=19843

Wed Jun 21 14:52:56 2017

LGWR started with pid=11, OS id=19845

Wed Jun 21 14:52:56 2017

CKPT started with pid=12, OS id=19847

Wed Jun 21 14:52:56 2017

SMON started with pid=13, OS id=19849

Wed Jun 21 14:52:56 2017

RECO started with pid=14, OS id=19851

Wed Jun 21 14:52:56 2017

MMON started with pid=15, OS id=19853

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

Wed Jun 21 14:52:56 2017

MMNL started with pid=16, OS id=19855

starting up 1 shared server(s) ...

Nomount状态下，不打开任何的控制文件及数据文件

SQL> select * from v$database;

select * from v$database

*

ERROR at line 1:

ORA-01507: database not mounted

mount：读并打开控制文件，并确认到database的结构信息

SQL> desc v$database；

SP2-0565: Illegal identifier.

nomount→mount过程：
1、打开控制文件

SQL> select * from v$controlfile;

STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS

------- ------------------------------------------------------------ --- ---------- --------------

/u01/app/oracle/oradata/oracle/control01.ctl NO 16384 594

/u01/app/oracle/fast_recovery_area/oracle/control02.ctl NO 16384 594

2、确认 database数据库的结构信息

SQL> desc v$database;

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

DBID NUMBER

NAME VARCHAR2(9)

CREATED DATE

RESETLOGS_CHANGE# NUMBER

RESETLOGS_TIME DATE

PRIOR_RESETLOGS_CHANGE# NUMBER

PRIOR_RESETLOGS_TIME DATE

LOG_MODE VARCHAR2(12)

CHECKPOINT_CHANGE# NUMBER

ARCHIVE_CHANGE# NUMBER

CONTROLFILE_TYPE VARCHAR2(7)

CONTROLFILE_CREATED DATE

CONTROLFILE_SEQUENCE# NUMBER

CONTROLFILE_CHANGE# NUMBER

CONTROLFILE_TIME DATE

OPEN_RESETLOGS VARCHAR2(11)

VERSION_TIME DATE

OPEN_MODE VARCHAR2(20)

PROTECTION_MODE VARCHAR2(20)

PROTECTION_LEVEL VARCHAR2(20)

REMOTE_ARCHIVE VARCHAR2(8)

ACTIVATION# NUMBER

SWITCHOVER# NUMBER

DATABASE_ROLE VARCHAR2(16)

ARCHIVELOG_CHANGE# NUMBER

ARCHIVELOG_COMPRESSION VARCHAR2(8)

SWITCHOVER_STATUS VARCHAR2(20)

DATAGUARD_BROKER VARCHAR2(8)

GUARD_STATUS VARCHAR2(7)

SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8)

SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(3)

SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(3)

FORCE_LOGGING VARCHAR2(3)

PLATFORM_ID NUMBER

PLATFORM_NAME VARCHAR2(101)

RECOVERY_TARGET_INCARNATION# NUMBER

LAST_OPEN_INCARNATION# NUMBER

CURRENT_SCN NUMBER

FLASHBACK_ON VARCHAR2(18)

SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(3)

SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(3)

DB_UNIQUE_NAME VARCHAR2(30)

STANDBY_BECAME_PRIMARY_SCN NUMBER

FS_FAILOVER_STATUS VARCHAR2(22)

FS_FAILOVER_CURRENT_TARGET VARCHAR2(30)

FS_FAILOVER_THRESHOLD NUMBER

FS_FAILOVER_OBSERVER_PRESENT VARCHAR2(7)

FS_FAILOVER_OBSERVER_HOST VARCHAR2(512)

CONTROLFILE_CONVERTED VARCHAR2(3)

PRIMARY_DB_UNIQUE_NAME VARCHAR2(30)

SUPPLEMENTAL_LOG_DATA_PL VARCHAR2(3)

MIN_REQUIRED_CAPTURE_CHANGE# NUMBER

　mount过程可以访问的动态性能视图：v$thread，v$controlfile，v$database，v$datafile，v$datafile_header，v$logfile

3、查看线程结构

SQL> desc v$thread;

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

THREAD# NUMBER

STATUS VARCHAR2(6)

ENABLED VARCHAR2(8)

GROUPS NUMBER

INSTANCE VARCHAR2(80)

OPEN_TIME DATE

CURRENT_GROUP# NUMBER

SEQUENCE# NUMBER

CHECKPOINT_CHANGE# NUMBER

CHECKPOINT_TIME DATE

ENABLE_CHANGE# NUMBER

ENABLE_TIME DATE

DISABLE_CHANGE# NUMBER

DISABLE_TIME DATE

LAST_REDO_SEQUENCE# NUMBER

LAST_REDO_BLOCK NUMBER

LAST_REDO_CHANGE# NUMBER

LAST_REDO_TIME DATE

4、查看数据文件结构

SQL> desc v$datafile;

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

FILE# NUMBER

CREATION_CHANGE# NUMBER

CREATION_TIME DATE

TS# NUMBER

RFILE# NUMBER

STATUS VARCHAR2(7)

ENABLED VARCHAR2(10)

CHECKPOINT_CHANGE# NUMBER

CHECKPOINT_TIME DATE

UNRECOVERABLE_CHANGE# NUMBER

UNRECOVERABLE_TIME DATE

LAST_CHANGE# NUMBER

LAST_TIME DATE

OFFLINE_CHANGE# NUMBER

ONLINE_CHANGE# NUMBER

ONLINE_TIME DATE

BYTES NUMBER

BLOCKS NUMBER

CREATE_BYTES NUMBER

BLOCK_SIZE NUMBER

NAME VARCHAR2(513)

PLUGGED_IN NUMBER

BLOCK1_OFFSET NUMBER

AUX_NAME VARCHAR2(513)

FIRST_NONLOGGED_SCN NUMBER

FIRST_NONLOGGED_TIME DATE

FOREIGN_DBID NUMBER

FOREIGN_CREATION_CHANGE# NUMBER

FOREIGN_CREATION_TIME DATE

PLUGGED_READONLY VARCHAR2(3)

PLUGIN_CHANGE# NUMBER

PLUGIN_RESETLOGS_CHANGE# NUMBER

PLUGIN_RESETLOGS_TIME DATE

5、日志文件

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

---------- ------- ------- -------------------------------------------------- ---

3 ONLINE /u01/app/oracle/oradata/oracle/redo03.log NO

2 ONLINE /u01/app/oracle/oradata/oracle/redo02.log NO

1 ONLINE /u01/app/oracle/oradata/oracle/redo01.log NO

mount→open过程：一致性校验
SQL> select INSTANCE_NAME,STATUS from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
Oracle OPEN
当数据库 Open 之后，所有的动态性能视图和数据字典都可以被查询。

帐号		自动登录	找回密码
密码			注册

[Oracle] Oracle正常启动过程顺序