Oracle数据库维护手册8517.docx

《Oracle数据库维护手册8517.docx》由会员分享，可在线阅读，更多相关《Oracle数据库维护手册8517.docx（17页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、 数据库维护手册目 录1前言42ORACLE维护方法42.1数据库启动42.2数据库停止52.3监听器的启停52.4用户管理62.5数据库参数文件62.6数据库概况的查询72.7常用对象的创建和使用82.8失效数据库对象的检测和编译92.9数据备份的技巧102.10数据库命中率的监测112.11最消耗资源进程的检测112.12锁的监测及处理122.13SQL语句执行技巧132.14表空间的管理142.15数据文件I/O的统计优化152.16错误号的跟踪出理153日常问题处理流程163.1ORACLE数据库日常检查163.2会话连接日志的清理163.3数据库hang住时的停止和方法163.4扩表

2、空间方法173.5死锁会话的杀死173.6未决两阶段事务的回滚184相关通用工具介绍184.1EM工具181 前言本文主要是简单介绍和总结日常Oracle数据在运行维护方面的一些方法和技巧。通过该手册以便于相关技术人员的日常运维工作。2 ORACLE DB维护方法2.1 数据库启动1、以oracle用户用户登录，sqlplus /as sysdbasqlplus startup2、以oracle用户或ora816用户登录sqlplus /as sysdbasqlplus startup nomunt sqlplus alter database mount；sqlplus alter data

3、base open；3、第一种启动方式是最常用的手工启动方式，第二种启动方式是数据库有故障时常用的逐步启动方式，可以观察故障点。因为startup nomount 语句只读取spfile（server parameter file 数据库参数文件），启动instance，启动SGA和后台进程；alter database mount语句打开控制文件，确认数据文件和联机日志文件的位置，但此时不对数据文件和日志文件进行校验检查；alter database open打开包括Redo log文件在内的所有数据库文件，这种方式下可访问数据库中的数据。Startup完成功能是上述三条语句的之和。2.2

4、数据库停止1、sqlplus /as sysdbasqlplus shutdown normal正常方式关闭数据库。2、shutdown immediate立即方式关闭数据库,在 SQLPLUS中执行shutdown immediate，数据库并不立即关闭，而是在所有事务执行完毕并提交工作后才关闭，所以可能会等待，因此在关闭数据前要停掉连接到数据库的所有应用程序。建议日常维护工作中的关闭数据库，采用此方式。3、shutdown abort直接关闭数据库，正在访问数据库的会话会被突然终止。如果数据库中有大量操作正在执行，这时执行shutdown abort后，因日志回滚、前滚（Roll Back

5、/Roll Forward），下次重新启动数据库需要教长时间。当用shutdown immediate不能关闭数据库时，shutdown abort可以立即完成数据库的关闭操作。2.3 监听器的启停1、监听器的启动，以oracle用户用户登录lsnrctl start $ORACLE_SID2、监听器的停止，以oracle用户用户登录lsnrctl stop $ORACLE_SID3、监听器状态的查看，以oracle用户用户登录lsnrctl status $ORACLE_SID4、检测服务名是否有效，在操作系统下运行tnsping SERVICE_NAME NUMBERSSERVICE_NA

6、ME为你建立的服务名，NUMBERS为你要试PING数据库服务的次数2.4 用户管理1、用SQL语句查看系统中已有用户情况 select * from dba_users;2、增加新的用户，并授予连接和资源权限，只有授予连接权限才可登录，授予资源权限才可建表和修改create user USERNAME profile default identified by PASSWORDdefault tablespace DEFALUT_TABLESPACE_NAME temporary tablespace TEMP_TABLESPACE_NAME ;grant connect to USERNA

7、ME;grant resource to USERNAME;3、修改用户口令alter user USERNAME identified by NEWPASSWORD;4、删除用户drop user USERNAME;5、限制某个已有用户会话的连接数(通过创建profile的方式)create profile PROFILENAME limit sessions_per_user CONNECT_NUMBERS;alter user USERNAME profile PROFILENAME;2.5 数据库参数文件1、参数文件位置$ORACLE_HOME/dbs/spfileINSTANCE_N

8、AME.ora,参数主要分为动态参数和非动态参数，动态参数修改够直接生效，非动态参数需要重新启动数据库才能生效。 2、 修改参数的方法 alter system set PARAMETER_NAME = VAlUE scope=both(system/spfile)3、几个重要的参数A、db_block_size 数据库块大小，数据库创建时决定，创建后不能修改。B、db_block_buffers数据高速缓冲区大小为此值与db_block_buffes的乘积，该区越大越好。 C、share_pool_size 程序高速缓冲区和数据字典缓冲区的大小，主要用于存储执行过的sql语句，减少重复分析，

9、提高运行速度。该区也越大越好，但建议数据高速缓冲区加上程序高速缓冲区和数据字典缓冲区的大小（即SGA）一般不超过系统物理内存的50%。D、sort_area_size每个会话用于排序操作的内存大小，建议为默认值的两倍到M之间。E、process能同时访问数据库的最大进程数，根据连接数的多少，来设定，一般为300以上。F、db_io_slaves 后台写进程数 对I/O比较繁忙的数据库，可以将其设为多于1个。（以上参数以9i为参考，在10g里个别参数名有所变化）2.6 数据库概况的查询1、表空间的情况select tablespace_name,stauts from dba_tablespac

10、es;2、数据文件的情况SELECT FILE_NAME,FILE_ID,TABLESPACE_NAME FROM DBA_DATA_FILES;3、段的情况SELECT SEGMENT_NAME,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;4、表、存储过程的拥有者名字和创建日期select owner,object_name,object_type,created from all_objectswhere object_type=TABLEOR OBJECT_TYPE=PROCEDURE5、其他常用的视图

11、和表v$lock 锁的情况v$session 会话的情况v$process 进程的情况v$parameter 数据库参数v$rollname 回滚段的情况v$sqlarea 共享池中的SQL语句v$waitstat 等待的会话dba_jobs 数据里定义的作业dba_jobs_running 数据库里正在运行的作业dba_views 数据库中所有视图的文本dba_indexs 数据库中所有索引的描述dba_free_space 数据库中所有表空间自由分区dba_db_links 数据库中所有数据库链接2.7 常用对象的创建和使用1、表的创建(在表空间zy上创建表dhhm为例)create ta

12、ble dhhm (hm varchar2(11) ,yhmc varchar2(60) tablespace zy;2、索引的创建(将DHHM表上hm字段建立索引hmsy存储在表空间zy上为例)create index hmsy on dhhm(hm) tablespace zy;3、已有存储过程、函数、包源代码的查找select name,text from user_source where name=NAME;4、 数据库链接的创建和使用create public database linkDBLINKNAME.world connect to USERNAME identdied b

13、y PASWORD using SERVICENAME;select * from TABLE_NAMEDBLINKNAME;5、同义词的创建和使用(以dyh用户可以查询并修改yyxt用户的dhhm表为例)crate synonyn dyh.dhhm for yyxt.dhhm;grant select on yyxt.dhhm to dyh;grant update on yyxt.dhhm to dyh;2.8 失效数据库对象的检测和编译1、失效存储过程的查找和编译select alter procedure|object_name| compile; from user_objects

14、where status = INVALID and object_type=PROCEDURE 然后复制粘贴并执行第一条语句的输出结果2、失效的包的查找和编译select alter package|object_name| compile; from user_objects where status = INVALID and object_type= PACKAGE然后复制粘贴并执行第一条语句的输出结果2.9 数据备份的技巧一、EXP/IMP1、利用管道将数据库备份压缩到磁带中去,以备份dhhm,yhzl 表为例（磁带机名IBM为/dev/rmt0，HP为/dev/rtm/m0）/us

15、r/sbin/mknod pipe_of p/usr/sbin/mknod pipe_if pdd of=/dev/rmt0 if=pipe_if bs=1024 &compress pipe_if &exp USERNAME/PASSWORD tables=(dhhm,yhzl) file=pipe_ofrm pipe_ofrm pipe_if2、利用管道将备份磁带解压后倒入到数据库中去/usr/sbin/mknod pipe_of p/usr/sbin/mknod pipe_if pdd if=/dev/rmt0 of=pipe_of bs=1024 &uncompress pipe_if

16、 &imp USERNAME/PASSWORD tables=(dhhm,yhzl) file=pipe_ifrm pipe_ofrm pipe_if3、数据库备份的分割,解决有时文件系统限制文件大小不能超过2G的问题（此处只分割为三个文件，当然可以根据实际情况继续增加）exp USERNAME/PASSWORD file=(FILE1.dmp,FILE2.dmp,FILE3.dmp) log=dump.log filesize=1g tables=(dhhm,yhzl)1、 为了提高备份速度,可将DIRECT参数设为Y（但当该参数设置为Y时，备份时不做一些有效性检查，应慎用）,并将buffe

17、r开大，如exp USERNAME/PASSWORD direct=y buffer=8192000 tables=(dhhm,yhzl) file=1.dmp2、 只备份建表和建索引语句exp USERNAME/PASSWORD rows=n tables=(dhhm,yhzl) file=1.dmp5、 有时为了提高倒入速度，应该将数据和索引的倒入倒出分开，而不是一起进行二、RMAN备份恢复1、非归档模式下数据库必须在mount状态下进行备份。$rman target/ 进入rman全库备份RMANshutdown immediate；关闭数据库RMANstartup mount；RMAN

18、backup database format /路径/DAT_%d_%T_%s；RMANalter database open；恢复数据库为OPEN状态RMANlist backupset of database; 备份记录RMANlist backup;恢复RMANshutdown immediate；RMANstartup nomount；RMANrestore controlfile from 控制文件备份;RMANalter database mount;RMANrestore database;RMANrecover database;RMANalter database open

19、resetlogs; 打开数据库并重做日志2、 归档模式切换归档模式SQL alter system set log_archive_dest_1=location=归档路径 scope=both; 首先需要指定归档路径SQL startup mount; SQL alter database archivelog; 非归档模式noarchivelogSQL alter database open;SQL alter system switch logfile; /*切换日志*/RMANbackup database format /数据备份路径/full_%d_%T_%s plus arch

20、ivelog format /日志备份路径/arch_%d_%T_%s delete all input;2.10 数据库命中率的监测1、 监控 SGA中数据高速缓冲区的命中率 select a.value + b.value logical_reads, c.value phys_reads,(100 * (a.value+b.value)-c.value) / (a.value+b.value) SGA DATA BUFFER HIT RATIO from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 38 and b

21、.statistic# = 39 and c.statistic# = 40; 2、监控 SGA 中字典缓冲区的命中率select 100*(sum(pins-reloads)/sum(pins) DICTIONARY BUFFER HIT RATIO from v$librarycache;3、监控 SGA 中共享缓存区的命中率，select 100*(sum(pins-reloads)/sum(pins) SQL SHARE AREA HIT RATIO from v$librarycache;4、整个SGA使用率的检查select name,sgasize/1024/1024 total

22、 sga M,bytes/1024/1024 free sga M,round(bytes/sgasize*100, 2) free ratio% from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f where f.name = free memory;2.11 最消耗资源进程的检测1、查看某个会话正在执行的SQL语句及该会话是哪台台机器发出select b.username,mand,b.termainal,b.osuser b.machine a.sql_text from v$sqltext a

23、,v$session b where a.address=b.sql_address and b.sid=SESSION_ID2、 SQLPLUS下找出最消耗资源的10个进程的SQL指令集!ps -eaf | grep ora | grep -v /sh | grep -v ora_ | awk print $4,$1,$2 | sort -r | head 10 （HP平台）!ps -eaf -o pcpu,pid,user,tty,comm | grep ora | grep -v /sh | grep -v ora_ | sort -r | head 10（IBM平台）prompt En

24、ter The UNIX PID :accept PIDSELECT a.username, a.terminal, a.program, b.sql_textFROM v$session a, v$sqlarea b, v$process cWHERE ( c.spid = &PID OR a.process = &PID )AND a.paddr = c.addrAND a.sql_address = b.address；3、利用数据自带统计工具检测其性能和瓶颈A、启动统计程序：svrmgrlsvrmgrl$ORACLE_HOME/admin/rdbms/utlbstat.sqlB、停止统

25、计程序svrmgrlsvrmgrl$ORACLE_HOME/admin/rdbms/utlestat.sqlC、一般在系统忙时开始统计程序，半小时后停止统计程序，不能执行过长，否则会影响系统性能；停止统计程序后，会在当前目录产生名为report.txt的文本文件，应对其做分析。2.12 锁的监测及处理1、锁的监测及锁住哪些表（注意绝大部分锁都不是死锁）select id1,count(*) from v$lock group by id2 having count(*)1;select object_name,object_type from dba_objects where object_

26、id=id1;这里v$lock中的id1和all_objects中的object_id 是相对应的。2、查看哪些会话被锁并杀死会话select id1,count(*) from v$lock group by id2 having count(*)1;select sid,serial# from v$session where id1=被锁的表ID；alter system kill session sid,serial#;(注意sid,serial#都为数字)3、 死数据库僵死进程（假设此进程的数据库会话号为X）select spid from v$process a ,v$sessio

27、n bwhere a.addr=b.paddr and b.sid=x;找出此数据库会话的操作系统进程spid ,然后在操作系统提示符号下杀死此进程 kill spid 2.13 SQL语句执行技巧1、打开和关闭显示SQL语句的执行方案（如使用哪个索引等）set autotrace on;set autotroace off;2、打开和关闭显示SQL语句的执行时间set time on;set time off;set timing on;set timing off;3、尽量使用 union all 而不使用union ,因为union操作时要排序并移走重复记录，而union all不执行上

28、述操作，所以速度要块很多倍。4、避免在SQL里使用PL/SQL功能调用，以提高速度5、查询时避免使用 like %string,以免全表扫描，而like string%则使用了对应字段的索引。6、定期重建索引，提高查询速度alter index INDEX_NAME rebuild ;6、 保存SQL输出到文本文件(以输出到ls.txt 为例)set serverout onspool ls.txtSQL 语句Spool off。2.14 表空间的管理1、查看所有表空间大小和使用率select a.tablespace_name,sum(a.bytes)/1024/1024 total siz

29、e(M),round(10000*(sum(a.bytes)/1024/1024-sum(b.bytes)/1024/1024)/1000 used size (M),round(10000*(sum(a.bytes)/1024/1024-sum(b.bytes)/1024/1024)/(sum(a.bytes)/1024/1024)/100 percent %from dba_data_files a,(select tablespace_name,file_id,sum(bytes) bytes from dba_free_space group by tablespace_name,fi

30、le_id ) b where a.tablespace_name=b.tablespace_nameand a.file_id=b.file_id group by a.tablespace_name;2、使用文件系统的表空间扩展 alter database TABLESPACE_NAME add datafile FILE_NAME size SIZE;3、用裸设备表空间的扩展alter database TABLESPACE_NAME add datafile RAW_DEVICE_NAME size SIZE;这里需要注意的是裸设备要提前建立后，且这里使用的是裸设备的字符文件（类型为

31、C）,SIZE值要比其实际大小要稍微小一些。4、集群中，在某一台主机上共享卷组上增加文件系统和裸设备后，一定要同步到集群中另外的主机，否则当数据库在另外的主机中启动时，会找不着相应的设备号，导致数据库不能够被打开。5、查找表空间的剩余空间块数以及最大空闲块的大小，如果碎片过多或最大空闲块过小，需要手工合并表空间碎片select tablespace_name,count(*)free block number,trunc(sum(bytes)/1024/1024)total free size(M),trunc(max(bytes)/1024/1024) max block(M) from d

32、ba_free_space group by tablespace_name;6、手工合并某表空间碎片alter tablespace TABLESPACE_NAME coalesce;2.15 数据文件I/O的统计优化 col 文件名 format a35；select df.name 文件名,fs.phyrds 读次数,fs.phywrts 写次数,(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds) 读时间,(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts) 写时间from v$datafile df,v$f

33、ilestat fswhere df.file#=fs.file# order by df.name；2.16 错误号的跟踪出理1、在操作系统级显示其错误详细信息和常用解决办法以报ora_4031错误为例）$oerr ora 40312、有时为了进一步跟踪其错误，让其产生更详细的告警日志在用户报警文件里，需要在数据库的初始化文件里加下面一句配置，并重新启动数据库event = 4031 trace name errorstack level 3这里注意的是，跟踪完毕后，应将此句话屏蔽掉，以免影响数据库性能。3 日常问题处理流程3.1 ORACLE数据库日常检查1、登录到主机上利用sqlplus

34、 检查是否可用； 2、数据库告警日志的检查$ORACLE_HOME/admin/INSTANCE_NAME/bdump/alert_INSTANCE_NAME.log此文件中记载数据库的启动、停止、系统级修改、告警信息、日志切换，是日常维护中的重要依据。3.2 会话连接日志的清理会话连接日志$ORACLE_HOME/network/log/listener.log,此文件中记载着每个连接登录时的地址和时间等情况，可作为监测的依据，因为此文件不断增大，需要定期清空，清空方式为在$ORACLE_HOME/network/log目录下运行操作系统命令listener.log。 3.3 数据库hang

35、住时的停止和方法在某个帐务数据库发生故障时，在将其对应的服务和后台帐务应用停完后，需要数据库重新启动，具体停止方法如下： $lsnrctl stop$kill 9 ps ef|grep LOCAL=NO|grep v grep|awk print $2$sqlplus /as sydbasqlplus alter system switch logfile;sqlplus shutdown immediate启动方法为：$sqlplus /as sydbasqlplus startup$lsnrctl start3.4 扩表空间方法方法一：利用图形化得OEM工具来扩，选择存储管理的表空间；方法

36、二：利用脚本：alter database TABLESPACE_NAME add datafile RAW_DEVICE_NAME size SIZE;(注：三个大写字符串是需要变化的具体值)这里需要注意的是裸设备要提前建立，且这里使用的是裸设备的字符文件（类型为c）,SIZE值要比其实际大小要稍微小一些，如设备大小为15M，语句里就不能使用15M，只要比15M小10K左右就可以。3.5 死锁会话的杀死 如果发现一会话SID为100，具体操作如下：(1)、select sid,serial#,program from v$session where sid=100；查到其 serial#号，

37、如这里为1199；（2）、SELECT a.sid,a.serial#,a.username, a.terminal, a.program,b.spid FROM v$s ession a, v$process bWHERE a.sid = 100 AND a.paddr = b.addr;b.spid为操作系统进程号，如这里为25555（3） alter system kill session 100,1199;kill 掉ORACLE回话（4） kill 9 25555kill掉操作系统进程4 相关通用工具介绍4.1 Enterprise Manager （EM）EM是用于Oracle数据库管理的一个功能强大的工具，并且可以通过IE访问连接，使用EM可以完成大部分的数据库管理工作。1. 作为 oracle 用户登录到操作系统上。执行以下命令来启动 dbconsole 进程：emctl start dbconsoleEM启动后可以通过登录以下URL来连接：https:/localhosts:5500/em2. 通过执行以下命令来检查 dbconsole 进程的状态： emctl status dbconsole3. 停止 dbconsole 进程：emctl stop dbconsole 第 17 页