Oracle DBA学习杂记（一）

Oracle DBA Summary

Oracle DB大体包括两个部分：Instance和Database。其中Instance主要是指内存结构，主要由SGA、PMON、SMON、DBWR、CKPT等构成；Database主要是指物理结构，主要由三种文件Control file、Data file和Online Redo log file构成。

Oracle Server is a databse management system that consists of an Oracle instance and an Oracle databse.

Oracle Instance

An Oracle instance is a means to access an Oracle databse.

Always opens one and only one database

Consists of memory and background process structures.

Connection指的是Client端与Oracle Server端建立的一个TCP连接

Background Process专注于数据库核心任务，Server Process专注与服务响应客户端。在建立连接并通过身份验证，安全审计之后才允许建立一个会话Session

An Oracle databse is a collection of data that is treated as a unit

Oracle Database除了三种基础文件之外，还有Password file、 Parameter file和Archived log file等多种辅助文件

Oracle’s memory structure consits of two memory areas: SGA and PGA.

PGA is allocated when the server process is started. SGA is allocated at instance start up.

SGA consists of serveral memory structures:

1、Shared Pool

2、Database Buffer Cache

3、Redo Log Buffer

4、Other (Lock、Latch、Large Pool/、Java Pool等可选项)

通过sqlplus中的show sga命令可以看到SGA的当前分配大小

SGA is sized by the SGA_MAX_SIZE parameter（在9i版本之后可以动态的变化适应增长）

Granules是SGA增长新分配的最小单位，SGA小于128M时Granules默认为4M，超过了128M之后Granules为16M。

查看Granules的SQL语句：select component, granule_size from v$sga_dynamic_components

Shared Pool主要作用是存储最近执行的SQL语句和最近被用到的数据定义。由Library Cache和Data Dictionary Cache两块共享内存，直接影响到数据库性能。更改Shared Pool尺寸的命令是：ALTER SYSTEM SET SHARED_POOL_SIZE=64M

Library Cache stores information about the most recently used SQL and PL/SQL statements (均为经过编译解析之后的，使用LRU算法)

Data Dictionary Cache：A collection of the most recently used definitions in the database. Includes information about database files, tables, indexes, columns, users, privileges, and other database objects.

Database Buffer Cache (最大的一块共享内存)

Stores copies of data blocks that have been retrieved from the data files. DB_BLOCK_SIZE determines primary block size. 针对它的统计信息可以在V$DB_CACHE_ADVICE表中查看

Redo Log Buffer records all changes made to the database data blocks. Its size defined by LOG_BUFFER.

Large Pool用于处理一些额外工作，比如I/O Server processes或者RMAN做Backup的时候

对于同一台机器的两个进程通讯有两种方式：一种是走IPC，包括共享内存、队列和信号量等几种形式；另外一种是方法是走模拟TCP/IP，即走look back环路网卡通信。

DBWn writes when: Checkpoint occurs、Dirty buffers reach threshold、There are no free buffers、Timeout occurs、RAC ping request is made、Tablespace OFFLINE、Tablespace READ ONLY、Table DROP or TRUNCATE等等

LGWR writes at commit、When one-third full、When there is 1MB of redo、Every three seconds、 Before DBWn writes

Block 是Oracle最小的逻辑单位，由若干个OS 文件系统Block组成，重要相关参数是DB_BLOCK_SIZE, 连续的Block组成了Extent。

DB Administration Tools:

1、OUI: used to install, upgrade, or remove software components

2、DBCA: 简单的GUI帮助创建数据库Instance等

3、SQL*Plus

4、OEM: 全面的管理控制界面，但每一代都变化很大

如果要在字符界面安装，需要准备一个responsefile，运行静默安装命令：

./runInstaller –responsefile myrespfile –silent

这个response file在Oracle的解压目录有模板文件，只需要稍加修改就可以直接使用。（在加压Oracle目录下的response目录中）

User SYS is owner of the database data dictionary, 其密码必须在安装的时候指定。

User SYSTEM is owner of additional internal tables and views used by Oracle tools, default password is manager.

The SID is a site identifier. It and ORACLE_HOME are hashed together in UNIX to create a unique key name for attaching an SGA. If your ORACLE_SID or ORACLE_HOME is not set correctly, you’ll get the ORACLE NOT AVAILABLE error, since you cann’t attach to a shared memory segment that is identified by this unique key.

On Windows, shared memory isn’t used in the same fashion as UNIX, but the SID is still important. You can have more that one database on the same ORACLE_HOME, so you need a way to uniquely identify each one, along with their configuration files.

参数文件有PFILE和SPFILE，其中PFILE就是一个文本文件，可以使用编辑器直接编辑，默认路径是ORACLE_HOME/dbs下，可以通过模板文件init.ora来创建，一般命名方式是initSID.ora。SPFILE是一个二进制文件，相对于PFILE一直存在于Server side，SPFILE可以被RMAN备份，PFILE不行，SPFILE可自适应参数调整。可通过PFILE创建出SPFILE:

CREATE SPFILE=’spfile***.ora’ FROM PFILE=’init***.ora’;

如果要修改SPFILE里面的值，可以使用如下命令：alter system set parameter=value <comment=’text’> <deferred> <scope=memory|spfile|both> <sid=’sid|*’>

在数据库startup的时候，优先寻找spfileSID.ora，然后是Default SPFILE，然后是initSID.ora，最后是Default PFILE。也可以显式的指定启用使用的Parameter file，优先使用命令指定的文件。

只有两种用户可以启停数据库：

1、The user’s operating system privileges allow him or her to connect using administrator privileges

2、The user is granted the SYSDBA or SYSOPER privileges and the database uses password files to authenticate database administrators.

数据库的启动过程经历的状态一共有四种：SHUTDOWN->NOMOUNT->MOUNT->OPEN

   在NOMOUNT状态的时候Oracle Instance启动，分配SGA，启动后台进程，但此时没有任何Database与SGA内存关联

   在切换到MOUNT状态的时候，the instance finds the database control file and open them，本质上是把一个database和一个instance关联起来。Oracle Database then reads control files to get the names of data files and redo log files. At this point, the database is still closed and is accessible only to the database administrator and not available for normal operations.

   最后进入OPEN状态，就进入了正常工作的状态。Oracle Database opens the online redo log files and data files. 没有这两种文件的话，数据库就会报错无法启动。

受限模式：RESTRICT MODE，只有特权用户才能操作使用数据库，一般用户无法连接数据库。

只读模式：READ ONLY MODE，使用命令ALTER DATABASE OPEN READ ONLY;

如何杀掉一个连接进来的用户会话：alter system kill session ‘<SID,SERIAL#>’

关闭数据库的过程：

首先会把所有SGA中缓存的数据写入数据文件并关闭联机重做日志文件；接下来Un-mount Database即取消Database和Instance的关联关系；最后才是Shutdown内存中的Instance。

Background trace file命名：SID_processname_PID.trc, location parameter is BACKGROUND_DUMP_DEST

User trace命名规则：SID_ora_PID.trc, location parameter is USER_DUMP_DEST.，打开User Trace，推荐基于Session Level的命令：ALTER SESSION SET SQL_TRACE=TRUE

Alert_SID.log file records the day-to-day operation information, results of major events, must be managed by DBA, location parameter is BACKGROUND_DUMP_DEST.

数据库类型：OLTP(大量并发，小型事务的联机事务处理型) 和 OLAP(少量并发，大型事务的数据仓库型)

OFA involves three major rules：(Optimal Flexible Architecture)

   Establish a directory structure where any database file can be stored on any disk resource

   Separate objects with different behavior into different table-space

   Maximize database reliability and performance by separating database components across different disk resource