Oracle运维详细手册簿.doc

《Oracle运维详细手册簿.doc》由会员分享，可在线阅读，更多相关《Oracle运维详细手册簿.doc（39页珍藏版）》请在三一办公上搜索。

1、Oracle运维手册目录文档修改记录21.前言：52.简单命令使用52.1进入SQL*Plus52.2退出SQL*Plus52.3在sqlplus下得到帮助信息62.4显示表结构命令DESCRIBE62.5SQL*Plus中的编辑命令62.6调用外部系统编辑器62.7运行命令文件72.8关于侦听73.ORACLE的启动和关闭83.1在单机环境下83.2在双机环境下94.数据库管理员日常工作94.1检查alterSID.log94.2环境确认105.数据库日常操作SQL115.1查看表空间物理文件的名称及大小115.2查询表空间使用情况115.3查询表空间的碎片程度115.4碎片程度125.5查

2、看回滚段名称及大小125.6查看控制文件135.7查看日志文件135.8查看表空间的使用情况135.9查看数据库对象135.10查看数据库的版本145.11查看Oracle字符集145.12在某个用户下找所有的索引145.13表、索引的存储情况检查145.14查看数据库的创建日期和归档方式155.15显示所有数据库对象的类别和大小155.16设置RAC为归档模式？156.AWR报告167.Troubleshooting167.1监控事务的等待167.2查看一些等待信息：167.3查看等待(wait)情况177.4回滚段查看177.5回滚段的争用情况187.6监控表空间的 I/O 比例187.7

3、监控文件系统的 I/O 比例187.8监控 SGA 的命中率187.9监控 SGA 中字典缓冲区的命中率197.10监控 SGA 中共享缓存区的命中率，应该小于1%197.11临控 SGA 中重做日志缓存区的命中率，应该小于1%197.12监控内存和硬盘的排序比率，最好使它小于 0.10，增加 sort_area_size207.13监控当前数据库谁在运行什么SQL语句207.14监控字典缓冲区207.15查看Lock207.16捕捉运行很久的SQL227.17查看数据表的参数信息227.18查看还没提交的事务237.19查找object为哪些进程所用237.20查看catched objec

4、t237.21查看V$SQLAREA247.22有关connection的相关信息248.备份268.1数据逻辑备份268.1.1exp268.1.2imp278.2控制文件备份288.3初始参数备份288.4其它289.常见问题解决289.1安装后常用参数设置289.2杀死僵死连接299.3AIX内存溢出309.4某一功能特别慢329.5统计信息失效导致执行计划走全表扫瞄329.6中银329.7日志文件太小引起的切换过于频繁359.8Oracle连接中断问题379.9查询委托返回记录不对389.10Linux + Oracle 10g RAC的平台上，发生节点重启故障399.11联机日志损坏

5、419.12控制文件损坏419.13ORA-01555错误419.14HP Proliant DL585G2 机器安装421. 前言：有一定Linux/Unix操作系统、Oracle数据库基础的工程人员和维护人员，证券公司信息技术人员等。本手册可作为工具，Oralce运维提供帮助。2. 简单命令使用2.1 进入SQL*Plus$sqlplus用户名/密码2.2 退出SQL*PlusSQLexit2.3 在sqlplus下得到帮助信息列出全部SQL命令和SQL*Plus命令SQLhelp列出某个特定的命令的信息SQLhelp命令名2.4 显示表结构命令DESCRIBESQLDESC表名2.5 S

6、QL*Plus中的编辑命令 显示SQL缓冲区命令SQLL 修改SQL命令首先要将待改正行变为当前行SQLn 用CHANGE命令修改内容SQLc/旧/新 重新确认是否已正确SQLL 使用INPUT命令可以在SQL缓冲区中增加一行或多行SQLiSQL输入内容2.6 调用外部系统编辑器SQLedit文件名可以使用DEFINE命令设置系统变量EDITOR来改变文本编辑器的类型，在login.sql文件中定义如下一行DEFINE_EDITOR=vi2.7 运行命令文件SQLSTARTtestSQLtest2.8 关于侦听1、新建/修改/删除侦听以oracle用户登录，运行netca，会跳出图形配置界面。

7、2、打开侦听lsnrctl start3、查看侦听Lsnrctl status4、关闭侦听lsnrctl stop3. ORACLE的启动和关闭3.1 在单机环境下要想启动或关闭ORACLE系统必须首先切换到ORACLE用户，如下su-oracle启动oracle 数据库命令：$sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on 星期一 7月 16 16:09:40 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL conn / as sysdba已连接到

8、空闲例程。SQL startupORACLE 例程已经启动。Total System Global Area 369098752 bytesFixed Size 1249080 bytesVariable Size 201326792 bytesDatabase Buffers 159383552 bytesRedo Buffers 7139328 bytes数据库装载完毕。数据库已经打开。关闭 oracle 数据库命令：$ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on 星期一 7月 16 16:08:10 2007Cop

9、yright (c) 1982, 2005, Oracle. All rights reserved.SQL conn / as sysdba已连接。SQL shutdown immediate数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。SQL3.2 在双机环境下要想启动或关闭crs服务必须首先切换到root用户，如下su-root启动crs 服务： 启动CRS#$CRS_HOME/crs/bin/crsctl start crs 查看CRS状态#$CRS_HOME/crs/bin/crsctl check crs 关闭CRS#$CRS_HOME/crs/bin/crsctl

10、stop crs 查看CRS内部各资源状态#$CRS_HOME/crs/bin/crs_stat t启动数据库服务# srvctl startdatabase -d tdb#tdb为数据库名4. 数据库管理员日常工作4.1 检查alterSID.log这个日志文件位于参数BACKGROUND_DUMP_DEST指定的目录，可能通过以下命令来查看。SQL SHOW PARAMETER background_dump_dest 在出现大故障前，数据库有可能会报一些警告或错误信息，应该充分重视这些信息，未雨绸缪，避免更大错误的发生。检查alterSID.log 的什么内容。 检查数据库是否出现过宕机

11、（可能在晚间重启而维护人员不知道） Oracle 出错信息，通过$grep ORA- alterSID.log查找 产品有关的问题：ORA-00600/ORA-07445等错误 相应的TRACE文件4.2 环境确认数据库实例是否正常工作SQL select status from v$instance;数据库监听器是否正常工作- $ lsnrctl status是否存在故障表空间- SQL select tablespace_name,status from dba_tablespace;控制文件、日志文件是否正常SQL select * from v$controlfile;SQL sele

12、ct * from v$log;SQL select * from v$logfile;性能监测 每天按业务峰值情况，对数据库性能数据进行定时采集 每天检查数据库的主要性能指标 每天检查最消耗资源的SQL语句变化情况。 每天检查是否有足够的资源 检查所有表空间的剩余情况 识别出一些异常的增长 检查CPU、内存、网络等是否异常5. 数据库日常操作SQL5.1 查看表空间物理文件的名称及大小select tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) filesize from dba_data_files

13、order by tablespace_name;5.2 查询表空间使用情况select a.tablespace_name 表空间名称, 100 - round(nvl(b.bytes_free, 0) / a.bytes_alloc) * 100, 2) 占用率(%), round(a.bytes_alloc / 1024 / 1024, 2) 容量(M), round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) 空闲(M), round(a.bytes_alloc - nvl(b.bytes_free, 0) / 1024 / 1024, 2) 使用(M

14、), to_char(sysdate, yyyy-mm-dd hh24:mi:ss) 采样时间 from (select f.tablespace_name, sum(f.bytes) bytes_alloc, sum(decode(f.autoextensible, YES, f.maxbytes, NO, f.bytes) maxbytes from dba_data_files f group by tablespace_name) a, (select f.tablespace_name, sum(f.bytes) bytes_free from dba_free_space f gr

15、oup by tablespace_name) b where a.tablespace_name = b.tablespace_name order by 2 desc;5.3 查询表空间的碎片程度select tablespace_name, count(tablespace_name) from dba_free_space group by tablespace_namehaving count(tablespace_name) 10;alter tablespace HS_USER_DATA coalesce;alter table name deallocate unused;5.

16、4 碎片程度select tablespace_name, count(tablespace_name) from dba_free_space group by tablespace_namehaving count(tablespace_name) 10;alter tablespace name coalesce;alter table name deallocate unused;create or replace view ts_blocks_v as select tablespace_name, block_id, bytes, blocks, segment_name from

17、 dba_free_space union all select tablespace_name, block_id, bytes, blocks, segment_name from dba_extents;select * from ts_blocks_v;select tablespace_name, sum(bytes), max(bytes), count(block_id) from dba_free_space group by tablespace_name; 查看碎片程度高的表 SELECT segment_name table_name, COUNT(*) extents

18、FROM dba_segments WHERE owner NOT IN (SYS, SYSTEM) GROUP BY segment_nameHAVING COUNT(*) = (SELECT MAX(COUNT(*) FROM dba_segments GROUP BY segment_name);5.5 查看回滚段名称及大小select segment_name, tablespace_name, r.status, (initial_extent / 1024) InitialExtent, (next_extent / 1024) NextExtent, max_extents, v

19、.curext CurExtent From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+) order by segment_name;5.6 查看控制文件select name from v$controlfile;5.7 查看日志文件select member from v$logfile;5.8 查看表空间的使用情况select sum(bytes) / (1024 * 1024) as free_space, tablespace_name from dba_free_space group by tab

20、lespace_name;SELECT A.TABLESPACE_NAME, A.BYTES TOTAL, B.BYTES USED, C.BYTES FREE, (B.BYTES * 100) / A.BYTES % USED, (C.BYTES * 100) / A.BYTES % FREE FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;5.9 查看

21、数据库对象select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;5.10 查看数据库的版本Select version FROM Product_component_version Where SUBSTR(PRODUCT, 1, 6) = Oracle;5.11 查看Oracle字符集 select * from sys.props$ where name = NLS_CHARACTERSET;5.12 在某个用户下找所有的索引 selec

22、t user_indexes.table_name, user_indexes.index_name, uniqueness, column_name from user_ind_columns, user_indexes where user_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name order by user_indexes.table_type, user_indexes.table_name, user_indexe

23、s.index_name, column_position;5.13 表、索引的存储情况检查 select segment_name, sum(bytes), count(*) ext_quan from dba_extents where tablespace_name = &tablespace_name and segment_type = TABLE group by tablespace_name, segment_name;select segment_name, count(*) from dba_extents where segment_type = INDEX and ow

24、ner = &owner group by segment_name;5.14 查看数据库的创建日期和归档方式Select Created, Log_Mode, Log_Mode From V$Database;5.15 显示所有数据库对象的类别和大小 select type, count(name) num_instances, sum(source_size) source_size, sum(parsed_size) parsed_size, sum(code_size) code_size, sum(error_size) error_size, sum(source_size) +

25、sum(parsed_size) + sum(code_size) + sum(error_size) size_required from dba_object_size group by type order by 1;5.16 设置RAC为归档模式？步骤：1. 以SYSDBA身份登陆2个节点，执行alter system set cluster_database=false scope =spfile sid=*;设置归档路径alter system set log_archive_start=true scope=spfile;2. 2个节点shutdown immediate3. 在

26、一个节点上执行startup mountalter database archivelog;shutdown immediate;alter database open;alter system set cluster_database=true scope =spfile sid=*;shutdown immediate4、分别启动2个节点，修改完毕6. AWR报告与9i 中的statspack相似，awr报告也需要两个快照，才能生成这两个时间点之间的性能报告。$sqlplus / as sysdba 生成快照一（10g中自动会每个整点都会生成一个快照）SQL exec dbms_workl

27、oad_repository.create_snapshot(); (间隔一段时间)生成快照二SQL exec dbms_workload_repository.create_snapshot(); 生成报告SQL ?/rdbms/admin/awrrpt.sql7. Troubleshooting常用性能相关SQL，监控数据库性能的SQL语句。 7.1 监控事务的等待 select event, sum(decode(wait_Time, 0, 0, 1) Prev, sum(decode(wait_Time, 0, 1, 0) Curr, count(*) Totol from v$ses

28、sion_Wait group by event order by 4; 7.2 查看一些等待信息：select sid, event from v$session_wait where event not like SQL% and event not like %ipc%;查看是否存在下面等常见的等待事件： buffer busy waits， free buffer waits， db file sequential read, db file scattered read, enqueue,latch free, log file parallel write, log file sy

29、nc7.3 查看等待(wait)情况SELECT v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum_value FROM v$waitstat, v$sysstat WHERE v$sysstat.name IN (db block gets, consistent gets) group by v$waitstat.class, v$waitstat.count;7.4 回滚段查看select rownum, sys.dba_rollback_segs.segment_name Name, v$rollsta

30、t.extents Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs, v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes, sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs, v$rollname where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and

31、 v$rollstat.usn(+) = v$rollname.usn order by rownum;7.5 回滚段的争用情况 select name, waits, gets, waits / gets Ratio from v$rollstat a, v$rollname b where a.usn = b.usn; 7.6 监控表空间的 I/O 比例 select df.tablespace_name name, df.file_name file, f.phyrds pyr, f.phyblkrd pbr, f.phywrts pyw, f.phyblkwrt pbw from v$

32、filestat f, dba_data_files df where f.file# = df.file_id order by df.tablespace_name; 7.7 监控文件系统的 I/O 比例 select substr(a.file#, 1, 2) #, substr(a.name, 1, 30) Name, a.status, a.bytes, b.phyrds, b.phywrts from v$datafile a, v$filestat b where a.file# = b.file#; 7.8 监控 SGA 的命中率 select a.value + b.valu

33、e logical_reads, c.value phys_reads, round(100 * (a.value + b.value) - c.value) / (a.value + b.value) BUFFER HIT RATIO from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 38 - physical read total multi block requests and b.statistic# = 39 - physical read total bytes and c.statistic# = 40

34、; - physical write total IO requests7.9 监控 SGA 中字典缓冲区的命中率 select parameter, gets, Getmisses, getmisses / (gets + getmisses) * 100 miss ratio, (1 - (sum(getmisses) / (sum(gets) + sum(getmisses) * 100 Hit ratio from v$rowcache where gets + getmisses 0 group by parameter, gets, getmisses;7.10 监控 SGA 中共

35、享缓存区的命中率，应该小于1% select sum(pins) Total Pins, sum(reloads) Total Reloads, sum(reloads) / sum(pins) libcache from v$librarycache;select sum(pinhits - reloads) / sum(pins) * 100 hit radio, sum(reloads) / sum(pins) reload percent from v$librarycache;7.11 临控 SGA 中重做日志缓存区的命中率，应该小于1% SELECT name, gets, mis

36、ses, immediate_gets, immediate_misses, Decode(gets, 0, 0, misses / gets * 100) ratio1, Decode(immediate_gets + immediate_misses, 0, 0, immediate_misses / (immediate_gets + immediate_misses) * 100) ratio2 FROM v$latch WHERE name IN (redo allocation, redo copy);7.12 监控内存和硬盘的排序比率，最好使它小于 0.10，增加 sort_ar

37、ea_sizeSELECT name, value FROM v$sysstat WHERE name IN (sorts (memory), sorts (disk);7.13 监控当前数据库谁在运行什么SQL语句 SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address = b.address order by address, piece;7.14 监控字典缓冲区 SELECT SUM(PINS) EXECUTIONS, SUM(RELOADS) CACHE MISSES WHI

38、LE EXECUTING, (SUM(PINS - RELOADS) / SUM(PINS) LIB CACHE FROM V$LIBRARYCACHE;SELECT SUM(GETS) DICTIONARY GETS, SUM(GETMISSES) DICTIONARY CACHE GET MISSES, (SUM(GETS - GETMISSES - USAGE - FIXED) / SUM(GETS) ROW CACHE FROM V$ROWCACHE;“LIB CACHE“与“ROW CACHE”越接近1.00超好，不要低于0.90。否则需要调大SGA的空间。7.15 查看Lockse

39、lect s.osuser, l.sid, s.serial#, s.username, s.terminal, decode(l.type, TM, TM - DML Enqueue, TX, TX - Trans Enqueue, UL, UL - User, l.type | - Other Type) LOCKTYPE, substr(t.name, 1, 10) OBJECT, u.name owner, l.id1, l.id2, decode(l.lmode, 1, No Lock, 2, Row Share, 3, Row Exclusive, 4, Share, 5, Shr

40、 Row Excl, 6, Exclusive, null) lmode, decode(l.request, 1, No Lock, 2, Row Share, 3, Row Excl, 4, Share, 5, Shr Row Excl, 6, Exclusive, null) request from v$lock l, v$session s, sys.user$ u, sys.obj$ t where l.sid = s.sid and s.type != BACKGROUND and t.obj# = l.id1 and u.user# = t.owner#;7.16 捕捉运行很久的SQLselect username, sid, opname, round(sofar * 100 / totalwork, 0) | % as progress,