Oracle10g数据库体系结构
体系结构就是数据库的组成。体系结构分为四部分:物理结构、内存结构、进程结构、逻辑结构。
Sharepool:共享池,主要用于处理用户的错误。
进程:用户和服务器端产生会话时产生进程。进程产生在服务器端,进程影响数据库的性能。
数据缓冲区:数据库暂存区域。
DBWR:数据写入进程,将数据由数据缓冲区,写入到数据文件中。
ARCH:归档进程,在日志切换后,将数据写入到磁带中。缺省情况下,ARCH不存在。
一、 物理结构
数据库是由构成数据库的操作系统文件所组成
数据库物理结构文件默认存储目录
D:\oracle\product\10.2.0\oradata\db_name\*.* -----windows
/U01/APP/ORACLE/PRODUCT/10.2.0/oradata/DB_NAME/*.* -------UNIX.
在进行RMAN等备份时,实际是对物理文件的备份。
数据库是由构成数据库的系统文件所组成的。数据库物理结构文件包括三类:*.dbf,*.log,*.log
1、 数据文件(DataFile)
存表或索引,站最大磁盘表空间。
数据链路、触发器这些存哪里呢,一般存在系统表空间,就是数据字典中。
ORACLE中,数据文件大小表示磁盘空间大小,不表示数据大小。数据文件是先规划后使用。
说明,在建立数据库时,要进行数据库规划,为数据文件划分空间。
。查询数据文件信息:
Sql>select * from dba_data_files;
。查询表空间空闲空间
Sql>select tablespace_name,sum(bytes)
from dba_free_space
group by rollup(tablespace_name);
进行数据使用时,要防止空间被用完
2、 数据块(Data Block)
Oracle I/0的最小单位,每次写或读,都是以块为最小单位。目前用的比较多的为8K,块大小在安装后,就不能变更。
Db_block_size = 2k~32k (2K,4K,8K,16K,32K)
操作系统的块一般为512k ,ORACLE的块大小,通常用操作系统的整数倍。
Oracle默认值如下:
Oracle11g 8k 8192
Oracle10g 8k 8192
Oracle9i 9.2.0 8k 8192
Oracle9i 9.2.1 4k 4096
查询数据块大小:
Sql>Show Parameter Db_Block_Size;
查询临时文件信息:
Sql>Select * From Dba_Temp_Files;
A:手工修改数据文件的大小
SQL>alter database datafile ‘数据文件路径及名称’ resize xxM;
该空间大小的两种办法:1:改文件大小。2:增加文件数量 数据文件也可以自动扩展。
B、数据文件自动扩展属性(Autoextend)
。查询数据文件是否允许自动扩展
Sql>select bytes,autoextensible,maxbytes,file_name from dba_data_files;
--BYTES 文件目前大小
。修改数据文件自动扩展功能
Sql>alter database datafile ‘数据文件名称及路径’
Autoextend off;
Sql>alter database datafile’数据文件名称及路径’
Autoextend on;
为了数据库的安全考虑,应竟可能的选择手工,而不要选择自动。
。设置文件最大扩展大小及最大扩展量
数据文件不宜设置太大,否则易备份困难。一般数据文件设置1G,10G ,20G,标准大小。数据文件太大,查询速度会慢。
硬盘的使用率超过%80时,操作系统会发生报警file full。表示某个文件系统太大.
举例:某用户发现操作系统磁盘不够,经查询,系统表空间很大。检查,是由于创建数据库时,设置一个表级参数,创建表时,预分配100M,而系统表空间默认表有很多,造成系统表空间很大。解决办法,逻辑卸出表的内容,重建库导入。但是不能整个数据库卸出,会造成参数仍然错误。
系统表空间,不存储数据,一般比较小。如果发现系统表空间比较大,可能有问题。查询表级参数,例如创建一个表,默认分配多少空间。
Sql>alter database datefile ‘数据文件名及路径’
Autoextend on next xxM maxsize xxM;
查找大小超过500M的大表:
Sql>select segment_name ,sum(bytes) from dba_extents
Where owner !=’SYS’ and owner !=’SYSTEM’
Group by (segment_name)
Having sum(bytes)>500 00000000;
查找用户:
SQL>select owner,table_name from dba_tables where table_name = ‘EMP123’;
2、日志文件:
日志文件不存数据,主要存数据库修改信息,一般大小不变。
例如:插入1万行,然后回退。日志仍然会产生记录。
日志在ORACLE中管理中,用于数据库恢复。日志靠配置来实现安全。对于一个数据库,至少2个日志文件组。每一个数据库实例至少有2个日志文件组,每组有一个或多个日志成员文件。
图1-日志存储原理图
跟踪用户,有用户和审计,审计只知道用户做什么操作,日志还可以记录数据。日志写多少份,和Member有多少没关系。一个循环会保存3份镜像menber日志。日志写满后,处理分为2种:归档和非归档,决定oracle数据时覆盖还是保存。
日志能完整记录数据。
确认日志归档模式:
Sql>select log_mode from v$database;
Sql>archive log list(注意:SYS用户执行)
。查询在线日志信息:
SQL>Select * from v$log;
。变更列名取消科学计数法
Sql>column 列名 format 9999999999 9代表数字个数
SQL>COUMN 列名 from a30 字符型,用a后面加字符长度
查询日志文件信息
Sql>select * from v$logfile;
。查询归档日志信息
Sql>select * from v$archived_log
。查询历史日志信息
Sql>select * from v$log_history
历史日志记录过去的变化,没有变化就没有日志。
日志至少2个组,建议3组,每个组的成员建议放在不同磁盘。归档日志可以删除,一般是和备份同步删除。备份完成,同时清除日志。接收以后的日志。
日志文件应该配置大些,太小,会造成频繁切换,增加CPU的负担和I/0负担。一般配置日志文件几十M以上。100M—700M
DBA强制日志切换:
SQL>alter system switch logfile;
A:增加日志文件组:
Sql>alter database add logfile group n (‘日志成员文件名及路径’,’日志成员文件名及路径’) size xxM;
ORACLE10G最多可加5个成员。成员文件大小最少不得小于4M.
数据文件用的是ORACLE块,日志文件用的是操作系统块,每一块512字节。
B:删除日志文件组
Sql>alter database drop logfile group n;
注意:
。Current,Active组不得删除
。数据库只有两个组是,不得删除。
。先逻辑删除,后物理删除
将日志切换到不活动状态
Sql>alter database check switchpoint
C、增加日志组成员
Sql>alter databse add logfile member
‘日志成员文件名及路径’,’日志成员文件名及路径’.. to group n;
日志文件坏掉,数据库启动不了,只能启动到mount状态,无法open .可以用镜像文件复制坏文件解决问题。
D、删除日志成员文件
Sql>alter database drop logfile member ‘日志成员文件名及路径’;
注意:
。current组中成员不得删除
。组中只有一个成员是,不得删除成员
。先逻辑删除,后物理删除
查询日志成员
Sql>select * from v$logfile;
题目:请 将默认3个日志文件组大小由50M 改为4M;
解决办法,注意同时保持两个日志文件组起作用就可以了,将上面的命令组合使用。
3、 数据库参数文件
关于oracle9i/oracle10g中,数据库参数文件的集中说明:
。在Oracle9i/10g中,数据库可以使用pfile及spfile两种不同类型的参数文件。
。spfile参数文件的默认存数目录
F:\oracle\product\10.2.0\db_1\dbs\spfileSID.ora
修改方法:
Sql>alter system set db_16k_catche_size=8m
Comment =’Changed By Tengyc,2010.08.04’
Scope=?
选项包括:memory:立即生效,重启不生效。
Spfile: 重启生效,永久生效。
Both:永久生效。
链接方法:F:\oracle\product\10.2.0\db_1\database\initSID.ora
Spfile=’ F:\oracle\product\10.2.0\db_1\dbs\spfileSID.ora’
。pfile参数文件的默认存储目录:
F:\oracle\product\10.2.0\admin\db_name\pfile\init.ora
#pfile的链接方法F:\oracle\product\10.2.0\db_1\database\initSID.ora
IFILE='F:\oracle\product\10.2.0\admin\db_name\pfile\init.ora'
查询数据库使用的参数文件
Sql>show parameter spfile
。两个数文件可以相互转换
Sql>create spfile from pfile; 缺省目录是database 目录,不会再原来的dbs目录
如果要更改成链接方式,将生成的pfile文件拷贝到相应目录,然后再编写链接文件
Sql>create pfile from spfile; 缺省目录是database 目录,会默认覆盖掉原来的链接。
Sql>show parameter spfile;
4、 数据库控制文件
控制文件(control file):存储数据库结构的二进制文件。查阅警告文件信息,了解数据库结构的sql操作
警告文件的目录:F:\oracle\product\10.2.0\admin\SID\bdump\alert_SID.log
控制文件存储信息如下:
。数据库创建时间
。数据库名称
。全部数据文件名称及路径
。全部日志文件名称及路径
。数据库最大化参数
。数据库恢复时所需的同步信息
数据库最大化参数文件:
Maxlogfiles 254
Mazlogmember 5
Maxloghistory 65534 //日志最多切换65534.
Maxdatafile 65534
Maxinstances 64
*.dbf
*.log
*.ctl
查询数据文件同步信息:
Sql>select name,checkpoint_change# from v$datafile;
查询控制文件同步信息:
Sql>select checkpoint_change# from v$datafile;
数据库在恢复时,它的基准是控制文件。
参数文件-?控制文件?改数据文件、日志文件
查询控制文件信息:
Sql>select * from v$controlfile;
控制文件的镜像方法:
A、 关闭数据库
B、 复制控制文件
C、 修改参数文件
D、 重新启动数据库
二、 逻辑结构
逻辑结构:从逻辑角度来考虑数据库的构成
。表空间:(Tablespaces)
。段:(Segments)
。区:(Extents)
。数据块:(Data Block)
表空间
段:段也就是表,创建一个表,分一个段。
区:区是块的整数倍 磁盘空间分配的最小单位
。控制数据库磁盘分配
。限制用户可以使用的磁盘大小(quota)
。表空间具有:read only,read write,online,offline 属性
离线offine表空间不允许操作。
Onling +read only :代表表空间只读
查询表空间状态
SQL>Select tablespace_name,status from dba_tablespaces;
变更表空间的状态:
Sql>alter tablespace student read only; ---影响使用表空间的所有用户,只能查询不能操作。
Sql>alter tablespace student offline; ---变更后,查询状态仍然是只读,只显示第一次的状态。
一旦表空间设置为只读或离线后,表空间对应的数据文件将不在同步
表空间为offline后,还是可以drop表,因为drop是在系统表空间的数据字典中,与用户没有关系。Drop是系统表空间删除表的结构,不清理数据。一旦drop结构后,就不能使用。
但是不能创建表,因为创建表需要分配空间,解决办法,先online ,在改为read write
注意:System,Undotb1,Temp,Sysaux表空间不能设置为read only , offline.
如何将系统命令及运行结果拷贝出来
SQL>Spool +路径文件名
创建命令
Sql>spool off
去检查对应路径文件就有内容了。
描述一个表结构:sql>desc +表名称
查询表空间影响哪些表哪些用户
SQL>select owner,table_name from dba_tables where tablespace_name=’STUDENT’
表空间通过数据文件来扩大,表空间的大小等于构成该表空间的所有数据文件大小之和。
查询表空间与数据文件之间的关系:
Sql>select tablespace_name,bytes,file_name from dba_data_files;
。按照表空间中的区的管理方式不同,表空间分为数据字典管理类型及本地化管理类型。
查询表空间中区的管理方式:
SQL>select tablespace_name,extent_management from dba_tablespaces;
A:数据段:存储基表数据的段,在用户使用Create table创建基表时,在用户默认表空间中创建数据段。
。查询用户默认表空间
Sql>select username,default_tablespace from dba_users;
。创建基表时,可以指定表空间
Sql>Create table s_order(
S_name varchar2(20),
Id int) tablespace example;
。查询表与表空间的对应关系
Sql>select table_name,tablespace_name from user_tables;
其中没有显示出来的是分区表。
。将表从一个表空间移动到另一个表空间
Sql>alter table s_order move tablespace users;
B.索引段:存储索引数据的段
。查询索引与存储索引表空间对应的关系。
Sql>select index_name,table_name,tablespace_name from user_indexes;
缺省情况下,索引和表存储在同一个表空间。
创建索引时,允许指定索引表空间。从数据优化上,应该将索引和表存储在不同的表空间。
Sql>create index emp_name on emp(ename) tablespace example;
C、临时段:用于order by 排序
。查询表空间类型
Sql>select tablespace_name,contents,logging from dba_tablespaces;
表空间类型分为三类:
Perment:永久表空间
Temportory:临时表空间,不记录日志
Undo:回滚表空间
表空间创建时,可以选择是否产生日志。
修改表空间,产生日志:
Sql>alter tablespace example loging;
D、回退段:用于事务回退。
参数:
。undo_management = auto ----undo空间自动管理
=manual ---回退段管理
。undo_tablespace:指出undo 表空间的名称
查询回退段信息:
Sql>select * from dba_rollback_segs;
回退段的使用原则:
。每一个事务必须有一个回退段
。每一个事务根据”最小使用原则”自动对应一个回退段。
什么事事务?
..
Create
Delete from ..
Alter table ..
Update..
Insert into
Drop table
…
所有DDL命令都引起事务终结。以上有2个事务,delete引起一个,alter终结。Update一个,drop 终结。
Delete机制:delete后,数据保存到回滚段。如果回滚段不够大,自动扩张,如果扩张还不够,则报错,删除失败。将数据放回表。如果扩张成功,则提示删除成功。如果接着用commit,则回滚段清空。
事务结束后,又按照最小使用原则,继续下一个事务。
3、区(Extents):区时磁盘空间分配的最小单位,当段由于数据增加变大时,通过增加区的个数实现。一个段包含多个区:查询段与区的对应关系:
SQL> select extent_id,bytes,blocks
2 from user_extents
3 where segment_name = 'EMP';
EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
0 65536 8
举例:查看大数据如何存储
SQL>Create table s_data(
S1 varchar2(20),
S2 varchar2(20));
Sql>Insert into s_data(‘aaaaaa’,’bbbbbbbb’);
Sql>Insert into s_data selct * from s_data;
重复执行以上的一条语句,数据就变的大起来。
查询所有表空间所占空间的大小:
Sql>select segment_name,count(*),sum(bytes),sum(blocks) from user_extents group by rollup(segment_name);
举例子:
select extent_id,bytes,blocks from user_extents where segment_name = 'S_DATA';
SQL> column segment_name format a40
SQL> set pagesize 200
SQL> set linesize 1000
SQL> select segment_name,count(*),sum(bytes),sum(blocks) from user_extents group by rollup(segment_name);
已选择12行。
区大小控制参数
。initial 表示第一次创建段时,区的大小。如果未指定,orale9 、10g一般默认是8块。
。next 第一个分配结束后,下一个要分配的是多少。缺省和next相同 8i一般是5块
。pctincrease 区分配百分比比例数,从第三个区分配开始,大小为:next+next*pctincrease
。minextents 最小分配区个数
。maxextent 最大分配区个数
参数使用举例:
Sql>create table s_sales(
S1 varchar2(20),
S2 char(20))
Storage(initial 100k
Next 100k
Minextents 1
Maxextents unlimited
Pctincrease 0);
如上所示,最少分配空间是100k就高折换成8K的整数倍 ,如果minextents 为2,则最小空间是 200k 就高折换成8K的整数倍。(为什么事8K,8K是块的大小)
举例2:
有100M空间,就性能角度考虑,由高到低 ,以下分配方式如何排列。
A.1m 100次
b.5m 20次
c:20m 5次
D:100 1次
正确排序:D?C?B?A
原因:磁盘分配会耗费资源,并且如果区太小,会形成磁盘碎片。
存数优先原则:实体参数优先表空间参数
如何查询表空间参数
Sql>select * from dba_tabespases;
4、数据块
三、 内存结构
1、 SGA(System Global Area)系统全局区
共享的内存占用,全体用户使用
显示SGA
Sql>show parameter SGA
Sql>show parameter sga_max_size
Sql>select * from v$sga
Sql>select * from v$sgastat; ------反映sga内容使用状况。
A:数据缓冲区:存储有磁盘数据文件读入的数据,所有用户共同使用。采用LRU(Least Recently Used)最近最少使用原则,最近的数据保留在内存区域,以减少I/O操作
注意:如果一次性插入上百万数据,都未提交,是存在内存里吗?不是,保留时间根据内存大小来确定。按照LRU原则替换,替换下来的写入数据文件,数据文件里,有标识,一部分是提交,一部分是未提交数据。
数据缓冲区测算方法:
Sql>select size_for_estimate,buffers_for_estimae,estd_physical_read_factor,estd_physical_reads
From v$db_catche_advice
Where name = ‘Default’ and block_size =(select value from v$parameter where name = ‘db_block_size’) and advice_status = ‘ON’;
缓冲区池:
Keep:驻留缓冲区
Recycle:用完后,立即离开
Default:默认,利用LRU,有空间用,没有空间离开。一般的表用default
语句做用:
中间的值是当前内存的情况。第一个是减少%100,最后一个是增加%100,查看减少和增加的一个状态。应该在数据库最忙时,测试,检测出一个系统建议值。
查看用户表的驻留区域:
SQL>
select size_for_estimate,buffers_for_estimate,estd_physical_read_factor,estd_physical_reads
From v$db_cache_advice
Where name = 'DEFAULT' and block_size =(select value from v$parameter where name = 'db_block_size') and advice_status = 'ON' ;
SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR
已选择20行。
因子出现1,说明能存在增加,物理交换是相同的,因为建议去第一个获得因子为1的参数。在这种情况下,在增加内存是无效果的,物理交换不会减少;
举例:试指出一下参数定义是否正确?
。。
Db_block_size = 8192
Db_cache_size=320m
Db_2k_cache_size=64m
Db_4k_cache_size=32m
Db_8k_cache_size=128m
Db_16k_cache_size=64m
……
参数定义错误,非标准块参数不得与 标准块参数相同。
B、共享池:用于对于用户的程序进行语法分析与编译的内存区域。
共享池大小设置参数:shared_pool_size
C、日志缓冲区(Redo cache size)存储数据库修改信息
日志缓冲区大小设置参数:log_buffer
Db_cache_size:如果oracle有很多数据要改,调整db_catche_size
SGA?shared_pool_size: 如果有很多plsql,调整share_pool??_size,例如电价计算,计算机最麻烦的是处理矩阵计算,参数多算法复杂
Log_buffer:
Unix系统运行期间不检测,重新启动时要检测硬件,所以,对于长期不停机的业务,一般没问题,如果1-2年都没停过,突然停,要注意,可能启动不了。
2、 PGA(Program Global Area)程序全局区
非共享内存区域
主要用于变量及数组
PGA设置参数:pga_aggregate_target
PGA大小测算:
Sql>select round(pga_target_for_estimate/1024/1024) MB,estd_pga_cache_hit_percentage HitRate,estd_overalloc_count from v$pga_target_advice;
显示PGA大小:
Sql>show parameter pga
举例:查询数据库的pga
SQL> select round(pga_target_for_estimate/1024/1024) MB,estd_pga_cache_hit_percentage HitRate,estd_overalloc_count from v$pga_target_advice;
MB HITRATE ESTD_OVERALLOC_COUNT
---------- ---------- --------------------
24 100 0
49 100 0
97 100 0
146 100 0
194 100 0
233 100 0
272 100 0
310 100 0
349 100 0
388 100 0
582 100 0
776 100 0
1164 100 0
1552 100 0
已选择14行。
3、 SORT AREA 排序区:主要用于order by 排序,排序区设置参数:sort_area_size
查询排序区大小:
Sql>show parameter sort_area_size
4、 Large pool :大区
主要用于rman 备份工具。
大区设置参数:large_pool_size
5、 java pool:java语言区
设置参数:java_pool_size
所有内存设置都有参数,修改参数不会引起数据库错误。一般:PGA大概是SGA%20
6、 ORACLE10G内存自动设置问题:
在Oracle10g中,设置参数sga_target,用于确定一下参数值:
Db_cache_size
Shared_pool_size
Large_pool_size
Java_pool_size
当设置了sga_target后,其他参数还需要设置。如果设置了sga_target,该4个参数可以不设置。例如:Sga_target=320m
查询某一时间sga的状态:
Sql>select * from v$sgainfo;
举例:
SQL> select * from v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 1249380 No
Redo Buffers 7135232 No
Buffer Cache Size 377487360 Yes
Shared Pool Size 218103808 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No ----SGA 最小分配单位
Maximum SGA Size 612368384 No
Startup overhead in Shared Pool 37748736 No
Free SGA Memory Available 0
已选择11行。
说明:
。如果参数sga_target=0 ,则自动内存管理功能被禁止。
。在自动内存管理方式下,允许为某参数指定最小值:
Sga_target =2000m
Db_cache_size=1000m
Large_pool_size=250m
以上设置的意思是,SGA为2000M,db_cache_size至少1000m,large_pool_size至少250m其他2个参数在分配。
所有内存参数,支持动态修改。
四、 Oracle实例的进程结构
1、 什么是实例?
每一个运行的数据库都有一个实例相联系。数据库启动时,系统分为SGA区,并启动后台。该内存区域与后台进程合成为实例。Instance?SGA+process
查询后台进程:
Sql>select name,description from v$bgprocess;
Instance_name
Oracle_sid
实例是系统参数,而sid是环境变量,需要设置。
$export ORACLE_SID (.PROFILE)---?UNIX系统
Windows:
C:\>set oracle_sid = 实例名 不用每次都设置,可以写进注册表中。
登录时,出现TNS:协议适配器错误,通常都是实例丢失。到windows服务总查询。如果服务正确,要去看看regedit.在UNIX中,实例错误,提示数据库不能登陆,比较难找。
ARCH:归档进程 pmon:进程监控 SMON: CKPT:检查点进程 dbwr:数据写入进程 这些都是系统后台进程,一直常驻内存,USER:用户进程 serverprocess :服务器进程。Serer:服务器进程
在oracle中,所有进程加起来不能超过参数文件中 processes的总数。
举例:Processes=150,则最多允许 150-系统后台进程。最多连140左右。:
SERVER PROCESS:服务器构进程工作过程:
。分析、编译、执行用户的SQL语句
。确定执行计划,执行分析数
。将数据从数据文件里读入SGA缓冲区
。将SQL语句的结果返回给用户进程
。相同的SQL代码只编译一次。
比如:sql>select * from emp
Sql>select * from EMP 这是两个句子,ORACLE要编译两次。
Sql>select * from emp
Sql>select * from emp 这是两个相同的句子,oracle只编译1次,第二个句子直接执行。
从数据库角度来讲,要减少数据库编译的时间。 一条sql运行时间,分析编译大概占%70,执行时间只有30%左右。
A、DBWR:数据写入进程,将数据从数据缓冲区写入磁盘数据文件。
数据写入进程设置参数:
多个CPU服务器使用参数:
Db_writer_processes=1~10(oracle8i)
=1~20(oracle9i,10g)
1个CPU服务器使用参数:
Dbwr_io_slaves 默认是0 ,通过设置I/O通道,来增加cpu的写入速度。
B、LGWR日志进程
在下面情况下将Redo Log缓冲区的信息写入Redo Log文件
每一个实例,只有一个LGWR进程。
C、PMON 进程监控
D、SMON 系统监控:主要用于监控临时表空间。比如一个排序后,是否释放空间。
E、ARCH 归档进程:
用户写日志
只有当日志组切换时才工作。
可将日志写到磁盘或磁带上。
F、CKPT检查点进程
检查点:日志切换,检查点到来,检查点,导致同步号改变。
检查点产生的情况:
1、 日志切换
2、 上一个检查点之后,又经过了指定的时间。
3、 从成一个检验点之后,当预定义数量的redo log块被写入磁盘后。
4、 例程关闭,数据库被shutdown
5、 DBA强制产生。
6、 表空间设置为offline时。
以上6点都会引起检查点变化。
检查点涉及参数:
时间参数:log_checkpoint_timeout =1800 缺省是1800,单位秒,即30分钟
日志量参数:log_checkpoint_interval = 0 表示参数默认没有使用。如果要使用可以指定,比如10000:表示,每产生10000块,oracle写一次同步号。
这些参数是默认参数,在参数文件中看不到,不显示,但是可以改。ORACLE 的参数共有200多个。