Oracle运维手册.doc

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

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语句。 什么时侯需要重建索引？答：（1）表上频繁发生update,delete操作在分析(analyze)指定索引之后，查询index_stats的height字段的值，如果这个值=4 ,则最好重建（rebuild）这个索引。虽然这个规则不是总是正确，但如果

28、这个值一直都是不变的，则这个索引也就不需重建。（2）表上发生了alter table .move操作（move操作导致了rowid变化）。怎样查询某进程正在运行的SQL？答：可以执行以下的查询语句:select sq.sql_textfrom v$session se,v$sql sq,v$process prwhere se.sql_address=sq.address(+)and se.sql_hash_value=sq.hash_value(+)and se.paddr=pr.addrand pr.spid=怎样终止某用户的会话？答：执行以下的查询语句查出用户的会话id：Select s

29、id,serial#,username,machine,programFrom v$session再利用查出的会话sid和serial号终于用户会话：Alter system kill session ,怎样查询表空间使用率？答：执行以下语句进行查询：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

30、 B, SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND A.TABLESPACE_NAME = C.TABLESPACE_NAME order by % USED DESC;怎样查询正在使用临时表空间的语句？答：执行以下的查询语句：SELECT se.username, se.sid,su.extents,su.blocks*to_number(rtrim(p.value) as Space,tablespace,segtype,sql_textFROM v$sort_usage su,v$parameter p

31、,v$session se,v$sql sWHERE p.name=db_block_sizeAND su.session_addr=se.saddrAND s.hash_value=su.sqlhashAND s.address=su.sqladdrORDER BY se.username,se.sid;怎样查询当前的系统等待事件？答：执行以下的查询语句：SELECT s.sid, s.username, w.seq#, w.event, w.p1text, w.p1, w.p2text, w.p2, w.p3text, w.p3, w.seconds_in_wait, w.state, s

32、.logon_time, s.osuser, s.programFROM v$session s, v$session_wait wWHERE s.sid = w.sidAND w.event NOT LIKE %SQL*Net%AND w.event NOT LIKE %rdbms%AND w.event NOT LIKE %timer%AND w.event NOT LIKE %jobq%ORDER BY w.event, w.seconds_in_wait;怎样迁移lob字段表空间？答：ALTER TABLE MOVE LOB() STORE AS(TABLESPACE );怎样迁移分区

33、表表空间？答：alter table move PARTITION tablespace ;7.1 监控事务的等待 select event, sum(decode(wait_Time, 0, 0, 1) Prev, sum(decode(wait_Time, 0, 1, 0) Curr, count(*) Totol from v$session_Wait group by event order by 4; 7.2 查看一些等待信息：select sid, event from v$session_wait where event not like SQL% and event not l

34、ike %ipc%;查看是否存在下面等常见的等待事件： buffer busy waits， free buffer waits， db file sequential read, db file scattered read, enqueue,latch free, log file parallel write, log file sync7.3 查看等待(wait)情况SELECT v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum_value FROM v$waitstat, v$sysstat WHER

35、E 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$rollstat.extents Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs, v$rollstat.gets Gets, v$rollstat.waits Waits, v$rolls

36、tat.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 v$rollstat.usn(+) = v$rollname.usn order by rownum;7.5 回滚段的争用情况 select name, waits, gets, waits / gets Ratio from v$rollstat a,

37、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$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

38、, 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.value logical_reads, c.value phys_reads, round(100 * (a.value + b.value) - c.value) / (a.value + b.value) BUFFER HIT RATIO from v$sys

39、stat 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; -physical write total IO requests7.9 监控 SGA 中字典缓冲区的命中率 select parameter, gets, Getmisses, getmisses / (gets + getmisses) * 100

40、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 中共享缓存区的命中率，应该小于1% select sum(pins) Total Pins, sum(reloads) Total Reloads, sum(reloads) / sum(pins) libcache from v$librarycache;sel

41、ect sum(pinhits - reloads) / sum(pins) * 100 hit radio, sum(reloads) / sum(pins) reload percent from v$librarycache;7.11 临控 SGA 中重做日志缓存区的命中率，应该小于1% SELECT name, gets, misses, immediate_gets, immediate_misses, Decode(gets, 0, 0, misses / gets * 100) ratio1, Decode(immediate_gets + immediate_misses, 0

42、, 0, immediate_misses / (immediate_gets + immediate_misses) * 100) ratio2 FROM v$latch WHERE name IN (redo allocation, redo copy);7.12 监控内存和硬盘的排序比率，最好使它小于 0.10，增加 sort_area_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(R