Oracle学习笔记-体系结构

Oracle8I体系结构
一个运行着的Oracle数据库称为一个实例（Instance）。实例是数据库系统的唯一标识，在操作系统中用Oracle_SID表示，在Oracle中用参数INSTANCE_NAME表示。数据库启动时首先在服务器中分配系统全局区（SGA），构成内存结构。然后启动若干个常驻内存的OS进程，组成ORACLE进程结构。体系结构包括物理结构、逻辑结构、内存结构和进程结构。

物理结构：数据库是由数据库使用的操作系统文件所构成的。一个ORACLE数据库物理文件分为数据文件、控制文件和日志文件及参数文件和配置文件等

数据库文件：用于存储数据库数据的文件，如基表，索引数据等。存取数据时，系统首先从数据文件中读取数据，并存储在内存的数据缓冲区内。为减少IO操作总数，提高性能，数据被存储在内存，由后台进程DBWR决定如何将其写入到相应的数据文件中。

数据文件的存储信息：全部数据文件的名称、大小、对应的表空间信息、是否自动扩展。
select tablespace_name,bytes,blocks,autoextensible,file_name from dba_data_files;
数据文件的大小通常可以用bytes字节数或blocks表示数据块个数。

数据块:8i中BLOCKS=8192bytes,即数据块大小为8k。在Oracle数据库中大小以数据块表示，作为数据库中最小的一个单位，读取数据时也以数据块为单位读取。数据块的大小由参数DB_BLOCK_SIZE确定，在参数文件中说明。一般是操作系统物理块的倍数，即512字节的倍数。9i中系统及默认表空间用DB_BLOCK_SIZE确定数据块大小，其他非默认表空间则使用参数BLOCKSIZE表示。一般数据块为4K、8K、16k、32K大小
查询数据快大小：select * from v$parameter where name='db_block_size';
                show parameter db_block_size;
                看参数文件
数据文件信息：一旦添加到数据库就不能删除，除非删除表空间。通常一个数据库包含多个表空间，一个表空间包含一个或多个数据文件。且数据文件大小可以动态改变，修改数据文件大小：alter database datafile 'd:\oracle\oradata\oradbsp\gsm01.dbf' resize 1000M
查询数据字典DBA_FREE_SPACE可了解表空间空闲量。自动扩展，当数据文件的空间使用完时，可以自动扩展空间，Autoextend属性决定是否扩展，next表示每次扩展的大小，自动扩展的最大尺寸由maxsize决定或unlimited,系统中默认设置为数据文件全部可自动扩展。以上参数可以使用Alter Database Datafile命令进行修改。使用Alter TAbleSPACE ADD命令增加新的数据文件时，增加autoextend属性。通常在创建时需要将数据文件自动扩展的属性关闭或增加maxsize限定.

表空间信息:表空间由一个或多个数据文件组成，可通过DBA_TABLESPACES查询。表空间的存储参数有：initial_extent（初始区大小）\next_extent（下次分配区大小）\min_extents（最小区数目）\maxtents（最大区数目）和Pct_increase（下次增长百分比）,这些参数用于规定存储在表空间里的实体的存储方法及空间大小。查询方法： select TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from dba_tablespaces;
表存储参数信息：
select initial_extent,next_extent,min_extents,max_extents,pct_increase from user_tables;

日志文件：用于记录对数据库的修改信息，对数据库所做的全部修改信息被记录在日志中。系统默认创建的数据库为三个日志组，每组一个成员。那个组用于日志的切换。为保证数据库的安全可以对日志组的成员做镜像，这样当其中一个破坏时不影响数据库的正常运行。
Alter database add logfile ‘path\filename’ group 1//为第一日志组增加一个成员。
日志成员的个数受参数maxlogmembers的限制。

日志切换：当一个日志文件组中所有的日志成员被同时写满数据时，系统自动切换到下一个日志文件组中。手动切换：alter system switch logfile;

查询日志：select * from v$log;//看日志状态
select * from v$logfile;//看组和成员文件名

日志检查点（CheckPoint）：简称CKPT,是Oracle的一个后台进程，叫检验点或检查点。检查点保证所有修改过的数据库缓冲区都写入数据库文件。参数log_checkpoint_timeout决定一个检查点发生的时间间隔，参数log_checkpoint_inteval决定执行一个检查点需要填充的日志文件快的数目。通过数据字典查v$log，得到系统当前的改变号SCN（System Change Number）,也叫检查点号，在系统中用checkpoint_change#表示，是数据库数据文件、控制文件、日志文件的同步依据。Checkpoint_change#同时存储在日志文件、数据库文件及控制文件中。在切换日志文件时，检查点同时改变。SCN号在数据库运行时会实时改变，同时将改变结果写入数据文件、控制文件和数据库头，以保证全部数据库文件同步.

增加和删除日志组:
alter database add logfile group 4 (‘d:\oracle\oradata\oradbsp\redo04.log’, ‘e:\oradata\oradbsp\redo04.log’) size 1m
alter database dorp logfile gorup 4;
增加与删除日志成员
alter database add logfile member ‘filename’ reuse to group 4;
alter database drop logfile member ‘filename’;

控制文件:控制文件是个二进制文件,用于描述数据物理结构.一个数据库只需要一个控制文件.控制文件的内容包括:
1.        数据库名DB_NAME及数据库惟一标识.数据库创建后该参数被以二进制方式写入控制文件.数据库唯一标识DBID也被写入控制文件
   select dbid,name from v$database;
2.        数据加创建时间
select name,created,checkpoint_change# from v$database;
3.        数据文件和日志文件标识:全部的数据文件名及路径,日志文件名及路径.
4.        数据库恢复所需的同步信息也存储在控制文件中,同步信息即checkpont_change#号
当数据库的结构发生变化时,系统自动修改控制文件中的信息,数据库创建时默认为三个控制文件,文件名及路径在参数文件中,三个文件之间是镜像的关系,一般存储在不同物理磁盘上
select * from v$controlfile;
手工镜像控制文件的步骤:
1).关闭数据库 2).复制控制文件到指定路径 3).将复制的控制文件加入到参数文件中 4)重启库
重建控制文件:alter database backup controlfile to trace   然后到 udump目录下的重建控制文件的脚本.即可重建
所有修改数据库结构的命令都会引起控制文件的改变.修改数据库结构的信息同时被记录在ORACLE跟踪文件中,跟踪信息由ORACLE后台进自动存储,跟踪文件名为alert_SID.log

跟踪文件的存储目录可以在参数文件中用参数background_dest确定.默认在$oraclHome/admin/DB_NAME/bdump目录下.用户跟踪文件由USER_DUMP_DEST参数确定

逻辑结构: 包括表空间(Tablespaces)\段(segments)\区(Extents)\数据快(Data Blocks)及模式对象.表空间是最大逻辑单位,数据快是取小的逻辑单位.数据库在逻辑上由多个表空间构成.

表空间在物理上由多个数据文件构成.逻辑上存储的对象是段,一个实体占用一个段,包括数据段,索引段\临时段\回退段.段的增大是由增加区的个数来实现的,分配空间时,最少分配一个区,区的单位是数据快的整数倍,数据快是数据库中最小的IO单位,通常是OS数据快的整数倍.
查询表空间类型及个数:
select TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from dba_tablespaces;
//修改数据文件大小
alter database datafile 'd:\oracle\oradata\oradbsp\gsm01.dbf' resize 1000M
//表空间脱机
alter tablespace employee OFFLINE;
//查看表空间状态
select tablespace_name,status from dba_tablespaces;
段的分类:按Oracle存储的数据性能区分,段分为四类:数据段和索引段\临时段和回退段.数据库用于存储基表的数据,索引段用于存储索引数据,临时段用于排序;回退段用于回退.

回退表空间:回退段有一个专属的表空间.如RBS.RBS表空间物理上由多个数据文件组成,逻辑上体现为多个回退段
//查看回退段
select SEGMENT_NAME.TABLESPACE_NAME from dba_rollback_segs;
//设置表空间RBS为OFFLINE或READ ONLY状态
alter rollback segment rbs1 offline;//设置回退段脱机
alter tablespace rbs offline;、//再设置RBS表空间脱机
临时表空间:主要提供用户排序或会总时所需的临时工作空间,用户的临时表空间可以由DBA创建用户时指定,创建临时表空间增加temporary选项
//添加临时表空间，增加TempOraRY选项
create tablespace userTemp datafile 'd:\oracle\oradata\oradbsp\userTemp01.dbf' size 200M autoextend on next 50M maxSize 1000M Temporary;
//设置用户临时表空间
alter user test temporary tablespace userTemp;
//创建“临时”表空间
create temporary tablespace TempTableSpace tempfile 'd:\oracle\oradata\oradbsp\tempfile.dbf' size 200M autoextend on next 50M maxSize 1000M;
select * from v$tempfile;//查看“临时”表空间信息
段:段存储于表空间中,是指定类型的逻辑存储结构.段由区(extents)组成.分为数据段\索引段\回退段和临时段.
据据段:用于存储数据的段,数据段存储在数据表空间中
//设置用户默认表空间
alter user test default tablespace testtablespace; //查看用户默认表空间
基表的数据段随着数据的增加而逐渐变大,段的增大是通过增加区的个数实现的
索引段:用于存储索引数据,如果创建了索引段,在索引表空间中自动创建一个索引段
//查询索引名、索引的表名以及表中作为索引列的列名
select a.table_name,a.index_name,a.uniqueness,b.column_name
from user_indexes a,user_ind_columns b
where a.index_name=b.index_name and a.table_name=b.table_name and a.table_name='YHDA_ONE'
order by a.table_type,a.table_name,a.index_name;
回退段:用于存储用户数据修改之前的值,每个用户在执行insert\update\delete时,系统必须为该用户提供一个回退段
//查询回退段信息
select SEGMENT_NAME,OWNER,TABLESPACE_NAME from dba_rollback_segs; 或
select SEGMENT_NAME,OWNER,TABLESPACE_NAME from dba_segments where segment_type='ROLLBACK';
临时段:使用order by 排序时,在用户的临时表空间自动创建一个临时段,排序结束后,临时段自动清除,系统使用默认临时表空间供用户排序时使用
//查看用户专属临时段
select username,temporary_tablespace from dba_users;
区:磁盘按区划分,每次至少分配一个区,段的增大是通过区来实现的,它由连续数据快组成.,每个区的大小是数据快的整数倍。
//查看数据段中区的存储参数
select * from all_tables where owner='TEST'; 或
select a.initial_extent,
       a.next_extent,
       a.min_extents,
       a.max_extents,
       a.pct_increase
from user_tables a;
数据快：由参数DB_BLOCK_SIZE确定，9i中对于系统默认表空间使用参数DB_BLOCK_SIZE确定数据快大小，非默认使用参数BLOCKSIZE确定数据快大小
模式对象：包括基表、视图、聚集、索引、序列、同义词、链路等

内存结构：Oracle数据库的内存分为系统全局区SGA\程序全局区PGA\排序区Sort Area、大池Large Pool及JAVA池。

系统全局区：包括数据缓冲区、日志缓冲区、共享池。系统全局区是数据库中占用服务器内存最大的一个区域。
1).数据缓冲区：修改、插入的数据均被存储于数据缓冲区中，当修改完成时，数据被写入文件.使用LRU最近最少使用原则。大小由参数文件中的参数db_block_buffers确定
select * from v$parameter where name='db_block_buffers'
命中率：hit Ratio=1-(数据文件中读的量－从缓冲区中读的总量－缓冲区中包含LOB的量)/读取总量
2).日志缓冲区：用于存储数据库的修改信息。当日志缓冲区的日志数据达到一定数量时，由日志写入进程LGWR将日志数据写入日志文件组。日志缓冲区大小由参数LOG_BUFFER确定
  show parameter log_buffer或查看参数文件log_buffe
3).共享池：是对SQL\pl/sql程序进行语法分析、编译和执行的内存区域。共享池包括库缓冲区、数据字典缓冲区和用户全局区3个部分.库缓冲区含有SQL语句的执行码，执行计划;数据字典缓冲区含有从数据字典中得到的表、列定义和权限
共享池主要用于对SQL、PL/SQL程序进行语法分析、编译。如果要运行大量的存储过程或包，则要增加共享池的尺寸
select * from v$parameter where name='shared_pool_size'
系统全局区的大小可使用命令查看: show sga;
select * from v$sga;
Oracle9i使用动态内存管理，可在线动态改变数据缓冲区及共享池大小。9i使用了动态的数据快大小DB_CACHE_SIZE

程序全局区：在用户进程连接到ORACLE数据库并创建一个会话时，由Oracle自动分配.PGA主要供用户存储编程时变量与数组。
排序区：为有排序要求的SQL语句提供内存空间，系统使用专门的内存区域进行数据排序，这部分空间称为排序区。分为内存排序区，和磁盘排序区。如果内存不够，ORACLE使用磁盘临时表空间进行排序。Sort_Area_Size用于设置排序区大小。
Show parameter sort_area_size;

大池:shared pool，用于数据库备份工具恢复管理器RMAN。由参数large_pool_size
  show parameter large_pool_size;

java池，由参数java_pool_size确定
进程结构：WIN下是种综合性的服务，UNIX下则为多个进程。
数据库启动时，首先启动实例，系统自动分配全局区SGA，并启动多个后台进程。内存区域和后台进程称为Oracle实例。

多进程实例：多进程实例中，系统的进程被分为用户进程，服务器进程与后台进程。每个连接的用户都有一个进程。常驻内存的后台进程有：USER(用户进程)\Dnnn\Listener（监听进程）\DBWR（读写进程）\LGWR（日志进程）\ARCH（归档进程）\pmon（进程监视）\SMON（系统监视）,CHPT(检查点)\RECO(恢复进程),数据库中占用OS的进程总数受参数process的限制。select * from v$parameter where name like '%process%';  
所以要增大参数文件process的数量，还要修改LINUX的核心参数SEMMNS，表示系统总共可以分配的进程。
查看用户会话信息：
select * from v$process p,v$session s where p.ADDR=s.PADDR;
select p.SPID,p.USERNAME,p.BACKGROUND,s.SCHEMANAME,s.MACHINE,s.OSUSER
from v$process p,v$session s
where p.PID=s.SID;

v$session : sid表示会话ID号，SER表示会话serial#号。它们都是惟一表不一个会话。
         SCHEMANAME数据库用户名，OSUSER，MACHINE主机名
V$process: SPID 表示操作系统进程号，OS process ID。 BACKGROUND表示进程类型，use表示用户进程，BAC为后台进程。

后台进程结构：主要包括：数据写入进程DBWR，日志写入进程LGWR，检查点进程CKPT，系统监控SMON,进程监控SMON、归档进程ARCH、恢复进程RECO、封锁进程LCKn，调度进程Dnnn，服务器进程Snnn，作业进程SNP等。后台进程可查询V$bgprocess

1.        DBWR：出现检查点时，LGWR进程能知DBWR进行写操作。最多可以启动10个数据写入进程DBWR，进程名称分别为DBW0,DBW1,DBW2…..DBW9。方法是修改db_writer_processes的取值，范围为1-10
2.        LGWR：日志写入进程,将日志数据从日志缓冲区写入磁盘日志文件组。系统将对数据库的所有修改信息存储在日志缓冲区，由LGWR将日志缓冲区中的信息写入磁盘。用户发出commit命令时提交的数据即被写入日志缓冲区。事务被提交时，被赋于一个系统改变号SCN，即检查点。
3.        SMON：系统监控，该进程在实例启动时执行实例恢复，并负责不再使用的临时段。
4.        PMON：进程监控，该进程在用户进程出现故障时执行进程恢复，负责清理内存存储区和释放该进程所使用的资源。PMON有规律的被唤醒
5.        ARCH：归档进程，该进程在日志存满时将日志信息写入磁盘或磁带，用于介质失败时的数据库恢复。数据库运行在归档模式下，在日志切换之前，由ARCH进程将日志信息写入磁盘或磁带。最多可启动10个归档进程ARCH，名称分别为ARC0-ARC9，方法是修改参数文件，增加参数LOG_ARCHIVE_MAX_PROCESSES
6.        CKPT：检查点进程，该进程负责对系统全部数据文件及控制文件的文件头中的同步号进行修改。以保证数据库的同步。Log_check_point_timeout用于决定产生一个检验点的时间间隔，每到一定时间，产生一个新的检验点和SCN号。Log_checkpoint_internal该参数决定执行一个检验点需要填充的日志文件快的数目。决定检查点的几个条件：
1).每个日志切换时
2).经过了指定的时间，即Log_check_point_timeout的时间
3).预定义数量的日志快被写入磁盘后，自动产生一个检查点。由参数Log_checkpoint_internal决定。
4)数据库关闭。
5).表空间OFFLINE时
7.        RECO：恢复进程，该进程用于分布式数据库系统，RECO自动维持在分布式环境中的数据一致性。
8.        LCKn：锁进程，用于ORACLE并行服务器下
9.        Dnnn：调度进程，该进程存在于多线程服务器体系结构中，负责将用户进程连接到服务器进程。
10.        SNP：作业进程，通过DBMS_JOB程序包自动运行预定的数据库作业，并用于分布式数据库中，进行数据库之间快照的自动刷新。参数JOB_QUEUE_PROCESS用于设置作业进程个数，JOB_QUEUE_INTERNAL用于设置作业进程时间间隔。
后台跟踪文件信息：系统对于后台进程进行自动跟踪，所有修改数据库结构的命令被后台进程跟踪记录，如ALTER Database、Create TableSpace等。默认跟踪文件名称为alert_SID.log默认路径：$ORACE_HOME\admin\DB_NAME\bdump\SIDALERT.log。跟踪文件的路径由参数Background_demp_dest确定。而用户跟踪文件由User_dump_dest参数确定，用户跟踪文件为：SID_*.trc。可用命令TKPROF对TRACE文件进行整理，格式为：
  C:\ >tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
可以用命令Alter Session Set SQL_TRACE=TRUE在线设置，设置结束后，系统自动产生用户的跟踪信息文件。

锁等待问题：排它DDL锁（Exclusive DDL）主要由语句DROP TABLE、ALTER TABLE引起。共享（Shared DDL）锁主要由语句Create Procedure引起。行组排它锁，表示被锁住行之外，表中其他行均可正常修改。只要事实被提交或回退之后，锁才会被解开
查询锁住其他用户的用户进程
select a.USERNAME,a.SID,a.SERIAL#,b.ID1
from v$session a,v$lock b
where b.ID1 in (select distinct e.id21
                from v$session d,v$lock e
                where d.LOCKWAIT=e.KADDR)
      and a.SID=b.SID
      and b.REQUEST=0;
强制解锁： Alter system kill session ‘SID,SERIVAL#’;