Oracle 数据库实例简介

一：Oracle 数据库实例简介

1：数据库实例的启动顺序：

使用数据库其实就是访问内存。即：数据库实例。数据库的启动是顺序是先 nomount ----> mount -----> open.

2：数据库文件为：数据文件、控制文件、日志文件

数据库就是磁盘上的一些物理文件。可以利用 v$datafile 视图进行查看。如“/u01/app/oracle/oradata/orcl/system01.dbf”该路径下的“system01.dbf”被称作数据文件。数据库的数据信息都是存放在类似“*.dbf”的文件下。

  1 [oracle@localhost ~]$ sqlplus / as sysdba;
  2 
  3 SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 17 22:58:17 2018
  4 
  5 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
  6 
  7 
  8 Connected to:
  9 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
 10 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 11 
 12 SYS@orcl> select name from v$datafile;
 13 
 14 NAME
 15 --------------------------------------------------------------------------------
 16 /u01/app/oracle/oradata/orcl/system01.dbf
 17 /u01/app/oracle/oradata/orcl/sysaux01.dbf
 18 /u01/app/oracle/oradata/orcl/undotbs01.dbf
 19 /u01/app/oracle/oradata/orcl/users01.dbf
 20 /u01/app/oracle/oradata/orcl/example01.dbf
 21 /u01/app/oracle/oradata/orcl/datafile1.dbf
 22 /u01/app/oracle/oradata/orcl/ts1.dbf
 23 /u01/app/oracle/oradata/orcl/datafile3.dbf
 24 /u01/app/oracle/oradata/orcl/datafile4.dbf
 25 /u01/app/oracle/oradata/orcl/datafile5.dbf
 26 /u01/app/oracle/oradata/orcl/datafilebig_1.dfb
 27 
 28 NAME
 29 --------------------------------------------------------------------------------
 30 /u01/app/oracle/oradata/orcl/ts2.dbf
 31 /u01/app/oracle/oradata/orcl/ts3.dbf
 32 /u01/app/oracle/oradata/orcl/tbsp_1.dbf
 33 
 34 14 rows selected.
 35 
 36 SYS@orcl>

再者从视图V$CONTROLFILE查看数据库的控制文件。"/u01/app/oracle/oradata/orcl/control01.ctl " 的“orcl”的目录是数据库实例名。

  1 SYS@orcl> select name from v$controlfile;
  2 
  3 NAME
  4 --------------------------------------------------------------------------------
  5 /u01/app/oracle/oradata/orcl/control01.ctl
  6 /u01/app/oracle/fast_recovery_area/orcl/control02.ctl
  7 /u01/app/oracle/fast_recovery_area/orcl/control03.ctl
  8 
  9 SYS@orcl>

查看日志文件信息

  1 SYS@orcl> select member from v$logfile;
  2 
  3 MEMBER
  4 --------------------------------------------------------------------------------
  5 /u01/app/oracle/oradata/orcl/redo03.log
  6 /u01/app/oracle/oradata/orcl/redo02.log
  7 /u01/app/oracle/oradata/orcl/redo01.log
  8 /home/oracle/oracle_system_files_back/logfile_bak/logfile_bak_20180228/redo20180
  9 228.log
 10 
 11 /home/oracle/oracle_system_files_back/logfile_bak/logfile_bak_20180228/redo20180
 12 228_1.log
 13 
 14 /home/oracle/oracle_system_files_back/logfile_bak/logfile_bak_20180228/redo04.lo
 15 g
 16 
 17 MEMBER
 18 --------------------------------------------------------------------------------
 19 
 20 
 21 6 rows selected.
 22 
 23 SYS@orcl>

1：Oracle 实例结构

用户通过访问数据库实例中的数据块来读取数据文件中的数据信息。用户不能直接读取数据文件中的数据信息。

数据库进程：

客户端进程

Oracle服务器端进程

服务器进程

后台进程

当有客户端进程链接到Oracle数据库的时候，Oracle服务器会相应的给与分配一个服务器进程来与之匹配。然后通过服务器进程来访问获取数据库实例的数据信息。当服务器进程终止的时候，数据库实例还是可以在运行的。数据库实例是有数据库服务器的内存和数据库后台进程组成的。所以，服务器进程终止了，并不影响数据库实例的。

2：数据库实例配置

集群的作用：

保障数据库实例可以正常运行

确保负载均衡

切换数据库实例ID：

当 $ORACLE_SID 的值为那个 oracle实例名；则通过“sqlplus / as sysdba;”进行访问的数据库实例就是显示的那个。

3：Oracle 系统标识符（SID）

  1 [oracle@localhost dbs]$ echo $ORACLE_SID
  2 orcl
  3 [oracle@localhost dbs]$ export ORACLE_SID=orcl
  4 [oracle@localhost dbs]$ echo $ORACLE_SID
  5 orcl
  6 [oracle@localhost dbs]$ cd $ORACLE_HOME/dbs
  7 [oracle@localhost dbs]$ pwd
  8 /u01/app/oracle/product/11.2.0/db_1/dbs
  9 [oracle@localhost dbs]$ ls
 10 dbsorapwPROD1  hc_orcl.dat  hc_PROD1.dat  init.ora  initorcl.ora  initPROD1.ora  lkORCL  lkPROD1  orapworcl  spfileorcl.ora  spfilePROD1.ora
 11 [oracle@localhost dbs]$

二：实例启动和关闭概述

1：实例和数据库启动概述

  1 [oracle@localhost dbs]$ sqlplus / as sysdba;
  2 
  3 SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 18 00:28:18 2018
  4 
  5 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
  6 
  7 
  8 Connected to:
  9 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
 10 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 11 
 12 SYS@orcl> show user
 13 USER is "SYS"
 14 SYS@orcl>
 15

2：实例是如何启动的？

注：创建数据库是指：以命令的方式创建数据库。

3：数据库是如何装载的？

注：数据库恢复的操作有：热备恢复、冷备恢复

4：数据库是如何打开的？

5：数据文件检查

6：只读模式

注：切换数据库为只读模式：

  1 
  2 SYS@orcl> shutdown immediate;
  3 Database closed.
  4 Database dismounted.
  5 ORACLE instance shut down.
  6 SYS@orcl> startup;
  7 ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
  8 ORACLE instance started.
  9 
 10 Total System Global Area 1221992448 bytes
 11 Fixed Size                  1344596 bytes
 12 Variable Size             939527084 bytes
 13 Database Buffers          268435456 bytes
 14 Redo Buffers               12685312 bytes
 15 Database mounted.
 16 Database opened.
 17 SYS@orcl> select open_mode from v$database ;
 18 
 19 OPEN_MODE
 20 --------------------
 21 READ WRITE
 22 
 23 SYS@orcl> shutdown immediate;
 24 Database closed.
 25 Database dismounted.
 26 ORACLE instance shut down.
 27 SYS@orcl> startup open read only;
 28 ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
 29 ORACLE instance started.
 30 
 31 Total System Global Area 1221992448 bytes
 32 Fixed Size                  1344596 bytes
 33 Variable Size             939527084 bytes
 34 Database Buffers          268435456 bytes
 35 Redo Buffers               12685312 bytes
 36 Database mounted.
 37 Database opened.
 38 
 39 SYS@orcl> select open_mode from v$database ;
 40 
 41 OPEN_MODE
 42 --------------------
 43 READ ONLY
 44 
 45 SYS@orcl> shutdown immediate;
 46 Database closed.
 47 Database dismounted.
 48 ORACLE instance shut down.
 49 SYS@orcl> startup mount;
 50 ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
 51 ORACLE instance started.
 52 
 53 Total System Global Area 1221992448 bytes
 54 Fixed Size                  1344596 bytes
 55 Variable Size             939527084 bytes
 56 Database Buffers          268435456 bytes
 57 Redo Buffers               12685312 bytes
 58 Database mounted.
 59 SYS@orcl> alter database open read only;
 60 
 61 Database altered.
 62 
 63 SYS@orcl> select open_mode from v$database ;
 64 
 65 OPEN_MODE
 66 --------------------
 67 READ ONLY
 68 
 69 SYS@orcl>