Oracle数据库基本运维管理

1、Oracle数据库基本运维管理Oracle基本运维01Oracle基本运维数据库物理结构参数文件:$ORACLE_HOME/dbs/spfilesid.ora密码文件:$ORACLE_HOME/dbs/orapwsid控制文件:数据文件同目录/control0 x.ctl数据文件:存放真实数据日志文件:记录对数据所作的修改归档文件:在线日志的归档02Oracle基本运维SGARedo log bufferData buffer cacheShared poolData dict. cacheLibrary cacheOracle InstancePMONSMONDBW0LGWRCKPTOthe

2、rsspfiledb01.ora参数文件Initialization Parameter FilesSQL CONNECT / AS SYSDBA SQL STARTUP文本文件二进制文件03Oracle基本运维密码文件oracle的口令文件的作用是存放所有以sysdba或者sysoper 权限连接数据库的用户的口令，如果想以sysdba权限远程连接 数据库，必须使用口令文件，否则不能连上，由于sys用户在 连接数据库时必，也就是说sys用户要想连须以sysdba or sysoper方式接数据库必须使用口令文件04Oracle基本运维控制文件The control file is a bin

3、ary file that defines the current state of the physical database.Loss of the control file requires recoveryIs read at MOUNT stageIs required to operateIs linked to a single databaseShould be multiplexedMaintains integrity of databaseDatabaseControl files05Oracle基本运维控制文件控制文件中包含了如下的信息:数据库名称和标识符数据库创建时间

4、表空间名称数据文件和联机重做日志的名字和位置当前联机重做日志序号检查点信息回滚段的开始与结束重做日志归档/存档（archive）信息备份信息06Oracle基本运维CONTROL_FILES=$DISK1/ORADATA/control01.ctl, $DISK2/ORADATA/control02.ctlDISK1DISK2CONTROL02.ctlCONTROL01.ctl控制文件多元化控制文件07Oracle基本运维控制文件控制文件做冗余修改 SPFILE:ALTER SYSTEM SET control_files = $DISK1/ORADATA/control01.ctl, $DI

5、SK1/ORADATA/control02.ctl SCOPE=SPFILE;关闭数据库:shutdown immediate创建附加的控制文件:cp$DISK1/ORADATA/control01.ctl$DISK2/ORADATA/control02.ctl启动数据库:startup08Oracle基本运维控制文件获取控制文件信息oraclewekiserver tmp$ sqlplus / as sysdba SQL oradebug setmypidStatement processed. SQL oradebug unlimit Statement processed.SQL alt

6、er database backup controlfile to trace;Database altered.SQL oradebug tracefile_name/server/oracle/app/diag/rdbms/weki/weki/trace/weki_ora_29003.trc09Oracle基本运维控制文件CREATE CONTROLFILE REUSE DATABASE WEKI RESETLOGS FORCE LOGGING ARCHIVELOGMAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXL

7、OGHISTORY 1168 LOGFILEGROUP 1 /server/oracle/oradata/weki/redo01.log SIZE 50M BLOCKSIZE 512, GROUP 2 /server/oracle/oradata/weki/redo02.log SIZE 50M BLOCKSIZE 512, GROUP 3 /server/oracle/oradata/weki/redo03.log SIZE 50M BLOCKSIZE 512- STANDBY LOGFILE DATAFILE/server/oracle/oradata/weki/system01.dbf,

8、 /server/oracle/oradata/weki/sysaux01.dbf, /server/oracle/oradata/weki/undotbs01.dbf, /server/oracle/oradata/weki/users01.dbf, /server/oracle/oradata/weki/confluence_data_01.dbf CHARACTER SET ZHS16GBK;10Oracle基本运维控制文件控制文件损坏的常见处理思路数据库控制文件若有多份冗余，由于控制文件内容完全镜像，只需用完好的控制文件覆盖 即可若所有控制文件损坏，确认能否在操作系统上读取控制文件，获

9、取数据、日志文件名称和 位置若操作系统层面都不能读取，只能人为确认获取数据、日志文件名称和位置，并以固定的 格式重构控制文件11Oracle基本运维控制文件控制文件的状态和位置的信息可以通过查询下列视图来获取V$CONTROLFILE: 列出所有与当前实例相关的控制文件的名和状态V$PARAMETER: 列出所有参数的状态和位置V$CONTROLFILE_RECORD_SECTION: 给出控制文件记录段相关的信息SHOW PARAMETER CONTROL_FILES: 列出控制文件的名，状态，和位置12Oracle基本运维数据文件OS 块数据文件物理表空间逻辑段 区Oracle 块数据库1

10、3Oracle基本运维注意点：业务表索引分离，分布不同表空间建议每个数据文件大小统一数据文件命令规范有序数据文件自动扩展关闭创建表空间步骤：检查当前表空间数据文件位置：SQL select name from v$datafile;+MCDATA/ora11g/system01.dbf创建表空间：create tablespace test datafile +MCDATA/ora11g/app_data01.dbf size 8g extent management localuniform size 1msegment space management auto;添加与创建一致，同样要确保

11、数据文件位置，大小，目前顺序数据文件14Oracle基本运维减少数据字典表的竞争当磁盘空间分配或收回时不会产生回滚（还原数据）不需要合并碎片create tablespace test datafile +MCDATA/ora11g/app_data01.dbf size 8gextent management localuniform size 1msegment space management auto;数据文件区本地管理的优点15Oracle基本运维数据文件手工重置数据文件的大小使用ALTER DATABASE手工增加或减少数据文件大小:改变某个数据文件大小来增加空间而不用增加更多的数

12、据文件手工重置某个数据文件大小，重新收回数据中没用空间ALTER DATABASEDATAFILE +MCDATA/ora11g/app_data01.dbf RESIZE 10g;16Oracle基本运维在线日志文件Using Redo Log FilesRedo log files record all changes made to data and provide a recovery mechanism from a system or media failure.Redo log files are organized into groups.An Oracle database

13、requires at least two groups.Each redo log within a group is called a member.DatabaseRedo log files17Oracle基本运维在线日志文件Structure of Redo Log FilesGroup 2Group 3Group 1MemberDisk 1Disk 2MemberMemberMemberMemberMember18Oracle基本运维在线日志文件Group 1Group 2Group 3Log3b.loglog3a.logLog1a.logLog1b.loglog2a.logLog

14、2b.logALTER DATABASE ADD LOGFILE GROUP 3($HOME/ORADATA/u01/log3a.log, $HOME/ORADATA/u02/log3b.log) SIZE 500M;添加日志组 Group 319Oracle基本运维在线日志文件Group 1Group 2Group 3Log1a.loglog1b.logLog2a.loglog2b.loglog1c.logLog2c.loglog3a.loglog3b.loglog3c.logALTER DATABASE ADD LOGFILE MEMBER$HOME/ORADATA/u03/log1c.l

15、og TOGROUP1size500M,$HOME/ORADATA/u03/log2c.log TOGROUP2size500M,$HOME/ORADATA/u03/log3c.log TOGROUP3size500M;添加日志组成员MEMBER20Oracle基本运维在线日志文件log1a.loglog2a.logLog3a.lohGroup 1Group 2Group 3ALTER DATABASE DROP LOGFILE GROUP 3;删除日志组Group 323Oracle基本运维在线日志文件Group 1Group 2log1b.logolog1c.logLog1a.loglog

16、1b.logLog2c.loglog1a.logALTER DATABASE DROP LOGFILE MEMBER$HOME/ORADATA/u03/log3c.log;删除日志组成员MEMBER24Oracle基本运维在线日志文件强制产生日志切换ALTER SYSTEM SWITCH LOGFILE;可以通过如下的方式来强制产生检查点设置 FAST_START_MTTR_TARGET参数FAST_START_MTTR_TARGET = 900使用 ALTER SYSTEM CHECKPOINT 命令ALTER SYSTEM CHECKPOINT;联机重做日志文件是以一种循环的方式来使用，当

17、一组联机重做日 志文件被写满时，LGWR 将开始写下一组日志文件，这被称为日志切 换，此时还要产生检查点操作，一些信息要被写到控制文件中。25Oracle基本运维可以使用ALTER DATABASE CLEAR LOGFILE 命令重新初始化联机重做日志文件ALTER DATABASE CLEAR LOGFILE GROUP 3;使用UNARCHIVED 关键字来避免归档已崩溃的联机重做日志文件ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;在线日志文件初始化redo日志文件26Oracle基本运维在线日志文件可以通过查询以下视图来获取组和成员的

18、信息:V$LOGV$LOGFILE27Oracle基本运维数据库逻辑结构表空间段区Oracle 块数据文件物理OS 块逻辑数据库SchemaTablespaceSegmentExtentsOracle data block28Oracle基本运维实例内存结构Oracles memory structure consists of two memory areas known as:System Global Area (SGA): Allocated at instance startup, and is a fundamental component of an Oracle Instanc

19、eProgram Global Area (PGA): Allocated when the server process is started29Oracle基本运维SGA组成Buffer Cache：主要缓存数据块Shared Pool： 缓存Cursor相关的信息和数据字典Log Buffer：缓存重做日志条目提示：Log Buffer不能在线动态调整30Oracle基本运维SGA组成（续）31Oracle基础知识SGA的一些重要属性在Unix/Linux系统中，可以通过ipcs命令可以在操作系统级别观察共享 内存段是数据库实例的重要组成部分，正常访问表数据的通道属于计算性内存内存碎片内

20、存交换32Oracle基础知识Buffer Cache的作用数据库（数据文件）存放在磁盘中，buffer cache作为数据库的缓存可以缓存不同大小的数据块，总体使用LRU算法为主淘汰无用的数据块数据块的并发性访问受Latch保护提示：多重缓存不仅带来内存浪费，而且会带来额外的开销，如内核调 用。所以我们建议使用裸设备、ASM存放数据文件。一般可以提高5% 左右的读取性能。33Oracle基础知识Buffer Cache的使用进程前台进程：从磁盘中读数据块至buffer cache中DBWR进程：从buffer cache写数据块至磁盘中提示：前台进程和DBWR进程可以有多个，严格来讲，同一时

21、刻 只能由一个进程更改数据块（可以有多个进程读取数据块），所 以Buffer Cache中需要有latch来保护并发性访问数据块34Oracle基础知识从BUFFER CACHE中读取数据块一般需耗时100ns从磁盘读取数据块一般需耗时10ms左右（假设缓存没有命中）， 所以我们一般建议数据块从buffer cache中读取。谈谈BUFFER CACHE的命中率35Oracle基础知识问题：Buffer Cache的命中率越高越好？BUFFER CACHE的命中率并不是越高越好，避免无效的 数据块读取才是数据库优化之根本36Oracle基础知识BUFFER CACHE的命中率高，并不意味着数据

22、库性能良好。执行计划出错时，逻辑读高，但效率低下“热”块争用时，逻辑读低，但效率低下37Oracle基础知识BUFFER CACHE的种类Default buffer cache：数据块的默认缓冲池，与db_block_size参数有关Keep buffer cache：缓存热块Recycle buffer cache：缓存冷块ALTER SYSTEM SET DB_CACHE_SIZE = 9600M;38Oracle基础知识BUFFER CACHE内存不足的优化思路加大buffer cache内存调整糟糕的SQL语句写法或执行计划DBWR进程数不足或者存储性能缓慢提示：适当加大buffer

23、 cache内存是没有坏处的39Oracle基础知识Shared PoolThe shared pool is used to store the most recently executed SQL statements and the most recently used data definitions.It consists of two key performance-related memory structures:Library cacheData dictionary cacheSized by the parameterSHARED_POOL_SIZEShared pool

24、 Library cacheData dictionary cacheALTER SYSTEM SET SHARED_POOL_SIZE = 64M;40Oracle基础知识分割连续的内存导致的内存碎片（chunk大小不一致），如下图：注意：同一个BUCKET中的chunk不严格排序内存碎片是不可避免的，相邻的碎片会定时合并41Oracle基础知识合并shared pool碎片的方法：SQL alter system flush shared_pool;注意： flush shared pool之后可能会导致：实例hangsequence（cache属性）不连续过量的硬解析导致latch:sh

25、ared pool争用42Oracle基础知识清理缓存的Cursor（延伸）SQL select sql_id,address,hash_value,plan_hash_value from v$sql where sql_text like select * from test where context=:A;SQL exec dbms_shared_pool.purge(000000194F6C2460,2921957255,C,1);43Oracle基础知识Library CacheThe library cache stores information about the most

26、recently used SQL and PL/SQL statements. The library cache:Enables the sharing of commonly used statementsIs managed by a least recently used (LRU) algorithmConsists of two structures:Shared SQL area Shared PL/SQL areaHas its size determined by the shared pool sizing44Oracle基础知识硬解析如果一个新的SQL被发起，但是又没缓

27、存在shared pool里面的 话，它将被完整的解析一次。例如：Oracle必须在shared pool中分配内存，检查句法和语义 等等45Oracle基础知识如果一个session发起一个已经缓存在shared pool中的SQL语句 并且它可以使用一个当前存在的版本，那么这个过程被称为 一个soft parse。对于应用来说，它只需请求解析这个语句。软解析46Oracle基础知识通过设置SESSION_CACHED_CURSORS参数将某个会话中常用的SQL放入UGA的会话缓冲区中，当会话发起相同的SQL时，可以快速地从UGA（User Global Area）取得CURSOR信息，从而

28、减少共享池的争用当一个CURSOR被解析3次以上（包括3次）时就会放入UGA会话缓冲区中当一个语句被parse的时候，Oracle会首先检查session的私有缓存中指向 的语句，如果有可被共享的语句版本的话，它就可以被使用软解析47Oracle基础知识Data Dictionary CacheThe data dictionary cache is a collection of the most recently used definitions in the database.It includes information about database files, tables, indexes, columns, users, privileges, and other database objects.During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access.Caching the data dictionary information into memory improves res