Oracle運維詳細手冊

1、 Oracle運維手冊目錄文檔修改記錄21.前言：52.簡單命令使用52.1進入SQL*Plus52.2退出SQL*Plus52.3在sqlplus下得到幫助信息62.4顯示表結(jié)構(gòu)命令DESCRIBE62.5SQL*Plus中的編輯命令62.6調(diào)用外部系統(tǒng)編輯器62.7運行命令文件72.8關(guān)于偵聽73.ORACLE的啟動和關(guān)閉83.1在單機環(huán)境下83.2在雙機環(huán)境下94.數(shù)據(jù)庫管理員日常工作94.1檢查alterSID.log94.2環(huán)境確認105.數(shù)據(jù)庫日常操作SQL115.1查看表空間物理文件的名稱及大小115.2查詢表空間使用情況115.3查詢表空間的碎片程度115.4碎片程度125.5

2、查看回滾段名稱及大小125.6查看控制文件135.7查看日志文件135.8查看表空間的使用情況135.9查看數(shù)據(jù)庫對象135.10查看數(shù)據(jù)庫的版本145.11查看Oracle字符集145.12在某個用戶下找所有的索引145.13表、索引的存儲情況檢查145.14查看數(shù)據(jù)庫的創(chuàng)建日期和歸檔方式155.15顯示所有數(shù)據(jù)庫對象的類別和大小155.16設(shè)置RAC為歸檔模式？156.AWR報告167.Troubleshooting167.1監(jiān)控事務(wù)的等待167.2查看一些等待信息：167.3查看等待(wait)情況177.4回滾段查看177.5回滾段的爭用情況187.6監(jiān)控表空間的 I/O 比例187.

3、7監(jiān)控文件系統(tǒng)的 I/O 比例187.8監(jiān)控 SGA 的命中率187.9監(jiān)控 SGA 中字典緩沖區(qū)的命中率197.10監(jiān)控 SGA 中共享緩存區(qū)的命中率，應(yīng)該小于1%197.11臨控 SGA 中重做日志緩存區(qū)的命中率，應(yīng)該小于1%197.12監(jiān)控內(nèi)存和硬盤的排序比率，最好使它小于 0.10，增加 sort_area_size207.13監(jiān)控當(dāng)前數(shù)據(jù)庫誰在運行什么SQL語句207.14監(jiān)控字典緩沖區(qū)207.15查看Lock207.16捕捉運行很久的SQL227.17查看數(shù)據(jù)表的參數(shù)信息227.18查看還沒提交的事務(wù)237.19查找object為哪些進程所用237.20查看catched obje

4、ct237.21查看V$SQLAREA247.22有關(guān)connection的相關(guān)信息248.備份268.1數(shù)據(jù)邏輯備份268.1.1exp268.1.2imp278.2控制文件備份288.3初始參數(shù)備份288.4其它289.常見問題解決289.1安裝后常用參數(shù)設(shè)置289.2殺死僵死連接299.3AIX內(nèi)存溢出309.4某一功能特別慢329.5統(tǒng)計信息失效導(dǎo)致執(zhí)行計劃走全表掃瞄329.6中銀329.7日志文件太小引起的切換過于頻繁359.8Oracle連接中斷問題379.9查詢委托返回記錄不對389.10Linux + Oracle 10g RAC的平臺上，發(fā)生節(jié)點重啟故障399.11聯(lián)機日志損

5、壞419.12控制文件損壞419.13ORA-01555錯誤419.14HP Proliant DL585G2 機器安裝421. 前言：有一定Linux/Unix操作系統(tǒng)、Oracle數(shù)據(jù)庫基礎(chǔ)的工程人員和維護人員，證券公司信息技術(shù)人員等。本手冊可作為工具，Oralce運維提供幫助。2. 簡單命令使用2.1 進入SQL*Plus$sqlplus用戶名/密碼2.2 退出SQL*PlusSQLexit2.3 在sqlplus下得到幫助信息列出全部SQL命令和SQL*Plus命令SQLhelp列出某個特定的命令的信息SQLhelp命令名2.4 顯示表結(jié)構(gòu)命令DESCRIBESQLDESC表名2.5

6、SQL*Plus中的編輯命令 顯示SQL緩沖區(qū)命令SQLL 修改SQL命令首先要將待改正行變?yōu)楫?dāng)前行SQLn 用CHANGE命令修改內(nèi)容SQLc/舊/新 重新確認是否已正確SQLL 使用INPUT命令可以在SQL緩沖區(qū)中增加一行或多行SQLiSQL輸入內(nèi)容2.6 調(diào)用外部系統(tǒng)編輯器SQLedit文件名可以使用DEFINE命令設(shè)置系統(tǒng)變量EDITOR來改變文本編輯器的類型，在login.sql文件中定義如下一行DEFINE_EDITOR=vi2.7 運行命令文件SQLSTARTtestSQLtest2.8 關(guān)于偵聽1、新建/修改/刪除偵聽以oracle用戶登錄，運行netca，會跳出圖形配置界面

7、。2、打開偵聽lsnrctl start3、查看偵聽Lsnrctl status4、關(guān)閉偵聽lsnrctl stop3. ORACLE的啟動和關(guān)閉3.1 在單機環(huán)境下要想啟動或關(guān)閉ORACLE系統(tǒng)必須首先切換到ORACLE用戶，如下su-oracle啟動oracle 數(shù)據(jù)庫命令：$sqlplus /nologSQL*Plus: Release .0 - Production on 星期一 7月 16 16:09:40 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL conn / as sysdba已連接

8、到空閑例程。SQL startupORACLE 例程已經(jīng)啟動。Total System Global Area 369098752 bytesFixed Size 1249080 bytesVariable Size 201326792 bytesDatabase Buffers 159383552 bytesRedo Buffers 7139328 bytes數(shù)據(jù)庫裝載完畢。數(shù)據(jù)庫已經(jīng)打開。關(guān)閉 oracle 數(shù)據(jù)庫命令：$ sqlplus /nologSQL*Plus: Release .0 - Production on 星期一 7月 16 16:08:10 2007Co

9、pyright (c) 1982, 2005, Oracle. All rights reserved.SQL conn / as sysdba已連接。SQL shutdown immediate數(shù)據(jù)庫已經(jīng)關(guān)閉。已經(jīng)卸載數(shù)據(jù)庫。ORACLE 例程已經(jīng)關(guān)閉。SQL3.2 在雙機環(huán)境下要想啟動或關(guān)閉crs服務(wù)必須首先切換到root用戶，如下su-root啟動crs 服務(wù)： 啟動CRS#$CRS_HOME/crs/bin/crsctl start crs 查看CRS狀態(tài)#$CRS_HOME/crs/bin/crsctl check crs 關(guān)閉CRS#$CRS_HOME/crs/bin/crsctl

10、 stop crs 查看CRS內(nèi)部各資源狀態(tài)#$CRS_HOME/crs/bin/crs_stat t啟動數(shù)據(jù)庫服務(wù)# srvctl startdatabase -d tdb#tdb為數(shù)據(jù)庫名4. 數(shù)據(jù)庫管理員日常工作4.1 檢查alterSID.log這個日志文件位于參數(shù)BACKGROUND_DUMP_DEST指定的目錄，可能通過以下命令來查看。SQL SHOW PARAMETER background_dump_dest 在出現(xiàn)大故障前，數(shù)據(jù)庫有可能會報一些警告或錯誤信息，應(yīng)該充分重視這些信息，未雨綢繆，避免更大錯誤的發(fā)生。檢查alterSID.log 的什么內(nèi)容。 檢查數(shù)據(jù)庫是否出現(xiàn)過宕

11、機（可能在晚間重啟而維護人員不知道） Oracle 出錯信息，通過$grep ORA- alterSID.log查找 產(chǎn)品有關(guān)的問題：ORA-00600/ORA-07445等錯誤 相應(yīng)的TRACE文件4.2 環(huán)境確認數(shù)據(jù)庫實例是否正常工作SQL select status from v$instance;數(shù)據(jù)庫監(jiān)聽器是否正常工作- $ lsnrctl status是否存在故障表空間- SQL select tablespace_name,status from dba_tablespace;控制文件、日志文件是否正常SQL select * from v$controlfile;SQL sel

12、ect * from v$log;SQL select * from v$logfile;性能監(jiān)測 每天按業(yè)務(wù)峰值情況，對數(shù)據(jù)庫性能數(shù)據(jù)進行定時采集 每天檢查數(shù)據(jù)庫的主要性能指標(biāo) 每天檢查最消耗資源的SQL語句變化情況。 每天檢查是否有足夠的資源 檢查所有表空間的剩余情況 識別出一些異常的增長 檢查CPU、內(nèi)存、網(wǎng)絡(luò)等是否異常5. 數(shù)據(jù)庫日常操作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) 使用(

14、M), 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 g

15、roup 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 fro

17、m 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,

19、v.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 ta

20、blespace_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、看數(shù)據(jù)庫對象select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;5.10 查看數(shù)據(jù)庫的版本Select version FROM Product_component_version Where SUBSTR(PRODUCT, 1, 6) = Oracle;5.11 查看Oracle字符集 select * from ps$ where name = NLS_CHARACTERSET;5.12 在某個用戶下找所有的索引 sele

22、ct 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_index

23、es.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 o

24、wner = &owner group by segment_name;5.14 查看數(shù)據(jù)庫的創(chuàng)建日期和歸檔方式Select Created, Log_Mode, Log_Mode From V$Database;5.15 顯示所有數(shù)據(jù)庫對象的類別和大小 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 設(shè)置RAC為歸檔模式？步驟：1. 以SYSDBA身份登陸2個節(jié)點，執(zhí)行alter system set cluster_database=false scope =spfile sid=*;設(shè)置歸檔路徑alter system set log_archive_start=true scope=spfile;2. 2個節(jié)點shutdown immediate3.

26、在一個節(jié)點上執(zhí)行startup mountalter database archivelog;shutdown immediate;alter database open;alter system set cluster_database=true scope =spfile sid=*;shutdown immediate4、分別啟動2個節(jié)點，修改完畢6. AWR報告與9i 中的statspack相似，awr報告也需要兩個快照，才能生成這兩個時間點之間的性能報告。$sqlplus / as sysdba 生成快照一（10g中自動會每個整點都會生成一個快照）SQL exec dbms_work

27、load_repository.create_snapshot(); (間隔一段時間)生成快照二SQL exec dbms_workload_repository.create_snapshot(); 生成報告SQL ?/rdbms/admin/awrrpt.sql7. Troubleshooting常用性能相關(guān)SQL，監(jiān)控數(shù)據(jù)庫性能的SQL語句。 7.1 監(jiān)控事務(wù)的等待 select event, sum(decode(wait_Time, 0, 0, 1) Prev, sum(decode(wait_Time, 0, 1, 0) Curr, count(*) Totol from v$se

28、ssion_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 s

29、ync7.3 查看等待(wait)情況SELECT v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum_value FROM v$waitstat, v$sysstat WHERE v$ 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$rollst

30、at.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$(+) = sys.dba_rollback_segs.segment_name an

31、d 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 監(jiān)控表空間的 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 監(jiān)控文件系統(tǒng)的 I/O 比例 select substr(a.file#, 1, 2) #, substr(, 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 監(jiān)控 SGA 的命中率 select a.value + b.val

33、ue 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# = 4

34、0; - physical write total IO requests7.9 監(jiān)控 SGA 中字典緩沖區(qū)的命中率 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 監(jiān)控 SGA 中

35、共享緩存區(qū)的命中率，應(yīng)該小于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 中重做日志緩存區(qū)的命中率，應(yīng)該小于1% SELECT name, gets, mi

36、sses, 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 監(jiān)控內(nèi)存和硬盤的排序比率，最好使它小于 0.10，增加 sort_a

37、rea_sizeSELECT name, value FROM v$sysstat WHERE name IN (sorts (memory), sorts (disk);7.13 監(jiān)控當(dāng)前數(shù)據(jù)庫誰在運行什么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 監(jiān)控字典緩沖區(qū) SELECT SUM(PINS) EXECUTIONS, SUM(RELOADS) CACHE MISSES WH

38、ILE 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。否則需要調(diào)大SGA的空間。7.15 查看Locks

39、elect 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(, 1, 10) OBJECT, owner, l.id1, l.id2, decode(l.lmode, 1, No Lock, 2, Row Share, 3, Row Exclusive, 4, Share, 5, Sh

40、r 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 捕捉運行很

41、久的SQLselect username, sid, opname, round(sofar * 100 / totalwork, 0) | % as progress, time_remaining, sql_text from v$session_longops, v$sql where time_remaining 0 and sql_address = address and sql_hash_value = hash_value;7.17 查看數(shù)據(jù)表的參數(shù)信息SELECT partition_name, table_name, high_value, high_value_lengt

42、h, tablespace_name, pct_free, pct_used, ini_trans, max_trans, initial_extent, next_extent, min_extent, max_extent, pct_increase, FREELISTS, freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size, last_analyzed FROM sys.dba_tab_partitions

43、 WHERE table_owner = HS_HIS AND table_name = HISBANKTRADE-WHERE table_name = :tname AND table_owner = :towner ORDER BY partition_position,partition_name;7.18 查看還沒提交的事務(wù)select * from v$locked_object;select * from v$transaction;7.19 查找object為哪些進程所用select p.spid, s.sid, s.serial# serial_num, s.username

44、user_name, a.type object_type, s.osuser os_user_name, a.owner, a.object object_name, decode(sign(48 - command), 1, to_char(command), Action Code # | to_char(command) action, gram oracle_process, s.terminal terminal, gram program, s.status session_status from v$session s, v$access a, v$proc

45、ess p where s.paddr = p.addr and s.type = USER and a.sid = s.sid and a.object = FUNDREAL order by s.username, s.osuser;7.20 查看catched objectSELECT owner, name, db_link, namespace, type, sharable_mem, loads, executions, locks, pins, kept FROM v$db_object_cache where owner LIKE HS_%;7.21 查看V$SQLAREASE

46、LECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS, VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS, USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS, BUFFER_GETS, ROWS_PROCESSED FROM V$SQLAREA;7.22 有關(guān)connection的相關(guān)信息l 查看有哪些用戶連接sele

47、ct s.sid, s.serial# serial_num, s.osuser os_user_name, decode(sign(48 - command), 1, to_char(command), Action Code # | to_char(command) action, gram oracle_process, status session_status, s.terminal terminal, gram program, s.username user_name, s.fixed_table_sequence activity_meter from v$

48、session s, v$process p where s.paddr = p.addr and s.type = USER order by s.username, s.osuser;l 2)根據(jù)v.sid查看對應(yīng)連接的資源占用等情況select , v.value, n.class, n.statistic# from v$statname n, v$sesstat v where v.sid = &sid and v.statistic# = n.statistic# order by n.class, n.statistic#;l 3)根據(jù)sid查看對應(yīng)連接正在運行的sqlselect /*+ PUSH_SUBQ */ command_type, sql_text, sharable_mem, persistent_mem, run