Oracle日常維護腳本

1.正常停庫流程

<code> </code><code>ps -ef|grep LOCAL=NO|cut -c 9-15|xargs kill -9 </code>

<code> </code><code>shutdown immediate;</code>

2.備份資料庫

<code> </code><code>backup database format </code><code>'/home/orarch_ccmpdb1/backup2/full_%d_%s_%p_%u.%T'</code><code>; </code>

<code> </code><code>backup current controlfile format </code><code>'/home/orarch_ccmpdb1/backup2/controlfile_%d_%s_%p_%u.%T'</code><code>;</code>

3.建立連接配接

<code> </code><code>ln -s /dev/ccmpvg01/rccmpv01l3111 /home/db/oracle/oradata/cmpdb/rtbs_data2_05_10g </code>

<code> </code><code>ln -s /dev/ccmpvg01/rccmpv01l3112 /home/db/oracle/oradata/cmpdb/rtbs_data2_06_10g</code>

4.增加表空間的空間

<code> </code><code>alter tablespace tbs_data2 add datafile </code>

<code> </code><code>'/home/db/oracle/oradata/cmpdb/rtbs_data2_05_10g'</code> <code>size 10200m, </code>

<code> </code><code>'/home/db/oracle/oradata/cmpdb/rtbs_data2_06_10g'</code> <code>size 10200m;</code>

5.檢視主分區表和子分區表

<code> </code><code>col partition_keyname </code><code>for</code> <code>a20 </code>

<code> </code><code>col sub_partition_keyname </code><code>for</code> <code>a20 </code>

<code> </code><code>select a.*,b.COLUMN_NAME sub_partition_keyname from </code>

<code> </code><code>(select t1.OWNER, </code>

<code> </code><code>t1.TABLE_NAME, </code>

<code> </code><code>t1.PARTITIONING_TYPE, </code>

<code> </code><code>t1.SUBPARTITIONING_TYPE, </code>

<code> </code><code>t1.PARTITION_COUNT, </code>

<code> </code><code>t2.column_name partition_keyname </code>

<code> </code><code>from dba_part_tables t1,dba_part_key_columns t2 </code>

<code> </code><code>where t1.owner = t2.owner </code>

<code> </code><code>and t1.table_name = t2.name </code>

<code> </code><code>and t1.OWNER = </code><code>'BILL'</code>

<code> </code><code>and t1.TABLE_NAME = </code><code>'HA_CREDIT_CTRL'</code>

<code> </code><code>) a,dba_subpart_key_columns b </code>

<code> </code><code>where a.owner = b.owner(+) </code>

<code> </code><code>and a.table_name = b.name(+);</code>

6.檢視子分區資訊

<code> </code><code>select TABLE_OWNER, </code>

<code> </code><code>TABLE_NAME, </code>

<code> </code><code>PARTITION_NAME, </code>

<code> </code><code>SUBPARTITION_NAME, </code>

<code> </code><code>SUBPARTITION_POSITION </code>

<code> </code><code>from DBA_TAB_SUBPARTITIONS </code>

<code> </code><code>where TABLE_OWNER = </code><code>'CASHBILL_TEST'</code>

<code> </code><code>and TABLE_NAME = </code><code>'BALANCE_PAYOUT'</code>

<code> </code><code>order by PARTITION_NAME,SUBPARTITION_POSITION; </code>

<code> </code><code>select dbms_metadata.get_ddl(</code><code>'TABLE'</code><code>,</code><code>'ACCT_ITEM_1080'</code><code>,</code><code>'CASH_BILL'</code><code>) from dual;</code>

7.段級别統計資訊

<code> </code><code>select owner,object_name,statistic_name,value </code>

<code> </code><code>from v$segment_statistics </code>

<code> </code><code>where owner=</code><code>'SYS'</code> <code>and object_name = </code><code>'I_FILE2'</code>

8.看最嚴重的前20個等待事件

<code> </code><code>set lines 200 </code>

<code> </code><code>set pages 200 </code>

<code> </code><code>col parameter1 </code><code>for</code> <code>a30 </code>

<code> </code><code>col parameter2 </code><code>for</code> <code>a30 </code>

<code> </code><code>col parameter3 </code><code>for</code> <code>a30 </code>

<code> </code><code>col value </code><code>for</code> <code>999999999999999999 </code>

<code> </code><code>select * from (select event,wait_time,SECONDS_IN_WAIT from v$session_wait </code>

<code> </code><code>where wait_class not in (</code><code>'Idle'</code><code>,</code><code>'Network'</code><code>) order by SECONDS_IN_WAIT desc) </code>

<code> </code><code>where rownum <= 20;</code>

9.看最嚴重的等待事件

<code> </code><code>select event,count(*) from v$session </code>

<code> </code><code>where wait_class not in (</code><code>'Idle'</code><code>,</code><code>'Network'</code><code>) </code>

<code> </code><code>group by event </code>

<code> </code><code>order by 2 desc;</code>

10.看目前連接配接會話資訊

<code> </code><code>select username,program,status,count(*) from v$session </code>

<code> </code><code>where username is not null group by username,program,status order by 3; </code>

<code> </code><code>select prev_sql_id,count(*) from v$session </code>

<code> </code><code>where program is null group by prev_sql_id order by 2</code>

11.最嚴重的前10個latch

<code> </code><code>select * from (select addr,name,</code><code>gets</code><code>,misses,sleeps,SPIN_GETS,WAIT_TIME </code>

<code> </code><code>from v$latch order by misses desc) where rownum <= 10 </code>

<code> </code><code>--latch: row cache objects </code>

<code> </code><code>select distinct s.kqrstcln latch#, </code>

<code> </code><code>r.cache#, </code>

<code> </code><code>r.parameter name, </code>

<code> </code><code>r.subordinate#, </code>

<code> </code><code>from v$rowcache r,x$kqrst s </code>

<code> </code><code>where r.cache# = s.kqrstcid </code>

<code> </code><code>order by 1,4,5;</code>

<code> </code><code>select * from (select cache#,type,SUBORDINATE#,parameter,count, </code>

<code> </code><code>USAGE,GETS,GETMISSES from v$rowcache order by GETMISSES desc) </code>

<code> </code><code>where rownum <= 10; </code>

<code> </code><code>select addr,latch#,child#,level#,name,</code><code>gets</code> <code>from v$latch_children </code>

<code> </code><code>where name = </code><code>'row cache objects'</code> <code>and </code><code>gets</code> <code><>0 order by </code><code>gets</code><code>;</code>

--latch cbc

--個個查詢

<code> </code><code>select * from (select addr from v$latch_children </code>

<code> </code><code>where name = </code><code>'cache buffers chains'</code> <code>order by misses) </code>

<code> </code><code>select </code><code>/*+ rule */</code> <code>owner,object_name from dba_objects </code>

<code> </code><code>where object_id in </code>

<code> </code><code>(select </code><code>/*+ rule */</code> <code>distinct obj from x$bh </code>

<code> </code><code>where hladdr = </code><code>'C000000BC6813AB8'</code><code>);</code>

12.查詢前十個cbc latch最嚴重對應的對象

<code> </code><code>select </code><code>/*+ rule */</code> <code>owner,object_name from dba_objects where object_id in </code>

<code> </code><code>select </code><code>/*+ rule */</code> <code>distinct obj from x$bh where hladdr in </code>

<code> </code><code>(select </code><code>/*+ rule */</code> <code>* from (select </code><code>/*+ rule */</code> <code>addr </code>

<code> </code><code>from v$latch_children </code>

<code> </code><code>where name = </code><code>'cache buffers chains'</code>

<code> </code><code>order by </code><code>gets</code> <code>desc) where rownum <= 10) </code>

13.檢查分區表的建立與否

<code> </code><code>order by PARTITION_NAME,SUBPARTITION_POSITION;</code>

14.表空間自動擴充性

<code> </code><code>SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS </code>

<code> </code><code>FROM DBA_TABLESPACES T,DBA_DATA_FILES D </code>

<code> </code><code>WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME </code>

<code> </code><code>ORDER BY TABLESPACE_NAME,FILE_NAME;</code>

15.全表掃描 ,注意修改 OBJECT_OWNER

<code> </code><code>select distinct t.sql_text from v$sqlarea t, v$sql_plan p </code>

<code> </code><code>where t.hash_value=p.hash_value </code>

<code> </code><code>and t.PLAN_HASH_VALUE=p.PLAN_HASH_VALUE </code>

<code> </code><code>and p.operation=</code><code>'TABLE ACCESS'</code>

<code> </code><code>and p.options=</code><code>'FULL'</code>

<code> </code><code>and p.OBJECT_OWNER =</code><code>'TBMS'</code><code>;</code>

16.全索引掃描, 注意修改 OBJECT_OWNER

<code> </code><code>and p.operation=</code><code>'INDEX'</code>

<code> </code><code>and p.options=</code><code>'FULL SCAN'</code>

17.檢視歸檔錯誤:

<code> </code><code>select dest_id,error from v$archive_dest where dest_id=1;</code>

18.資料字典命中率

<code> </code><code>column parameter format a21 </code>

<code> </code><code>column pct_succ_gets format 999.9 </code>

<code> </code><code>column updates format 999,999,999 </code>

<code> </code><code>SELECT parameter </code>

<code> </code><code>, sum(getmisses) </code>

<code> </code><code>, 100*sum(</code><code>gets</code> <code>- getmisses) / sum(</code><code>gets</code><code>) pct_succ_gets </code>

<code> </code><code>, sum(modifications) updates </code>

<code> </code><code>FROM V$ROWCACHE WHERE </code><code>gets</code> <code>> 0 </code>

<code> </code><code>GROUP BY parameter;</code>

19.檢視歸檔錯誤

<code> </code><code>select dest_id,error from v$archive_dest where dest_id=1;</code>

20.是否有行遷移

--收集對象的統計資訊:

<code>analyze table t compute statistics;</code>

<code> </code><code>select TABLE_NAME,BLOCKS,EMPTY_BLOCKS,NUM_ROWS,AVG_ROW_LEN,CHAIN_CNT </code>

<code> </code><code>from user_tables where table_name=</code><code>'T'</code><code>;</code>

21.取出全表掃描的表

<code> </code><code>select sql_text from v$sqltext where sql_id in </code>

<code> </code><code>(select sql_id from v$sql_plan </code>

<code> </code><code>where operation like </code><code>'%TABLE ACCESS%'</code>

<code> </code><code>and options like </code><code>'%FULL%'</code><code>) </code>

<code> </code><code>where sql_text like </code><code>'%EMP%'</code>

22.全表掃描統計項

<code> </code><code>select name,value from v$sysstat where name like </code><code>'table scan%'</code><code>;</code>

23.哪個使用者哪個表作了全表掃描

<code> </code><code>select OPERATION,object_owner,OPTIONS,OBJECT_NAME from </code>

<code> </code><code>where options=</code><code>'FULL'</code>

<code> </code><code>and OPERATION=</code><code>'TABLE ACCESS'</code>

<code> </code><code>and object_owner=</code><code>'SCOTT'</code><code>;</code>

24.哪個使用者下有多少張表作了全表掃描

<code> </code><code>select ss.username||</code><code>'('</code><code>||se.sid||</code><code>') '</code> <code>"User Process"</code><code>, </code>

<code> </code><code>sum(decode(name,</code><code>'table scans (short tables)'</code><code>,value)) </code><code>"Short Scans"</code><code>, </code>

<code> </code><code>sum(decode(name,</code><code>'table scans (long tables)'</code><code>, value)) </code><code>"Long Scans"</code><code>, </code>

<code> </code><code>sum(decode(name,</code><code>'table scan rows gotten'</code><code>,value)) </code><code>"Rows Retreived"</code>

<code> </code><code>from v$session ss, </code>

<code> </code><code>v$sesstat se, </code>

<code> </code><code>v$statname sn </code>

<code> </code><code>where se.statistic# = sn.statistic# </code>

<code> </code><code>and (name like </code><code>'%table scans (short tables)%'</code>

<code> </code><code>or name like </code><code>'%table scans (long tables)%'</code>

<code> </code><code>or name like </code><code>'%table scan rows gotten%'</code><code>) </code>

<code> </code><code>and ss.username is not null </code>

<code> </code><code>group by ss.username||</code><code>'('</code><code>||se.sid||</code><code>') '</code>

25.增加全表掃描時I/O的吞吐量

<code> </code><code>db_file_multiblock_read_count=1~128</code>

26.使用并行處理提高全表掃描效率:

<code> </code><code>select </code><code>/*+ parallel (ob ,16)*/</code> <code>count(*) from OB;</code>

27.條帶化堆表資料:

<code> </code><code>alter table ob allocate extent (size 5m datafile </code><code>'/u01/app/oracle/oradata/madrid/users01.dbf'</code><code>); </code>

<code> </code><code>alter table ob allocate extent (size 5m datafile </code><code>'/u01/app/oracle/oradata/madrid/users02.dbf'</code><code>); </code>

<code> </code><code>alter table ob allocate extent (size 5m datafile </code><code>'/u01/app/oracle/oradata/madrid/users03.dbf'</code><code>); </code>

<code> </code><code>alter table ob allocate extent (size 5m datafile </code><code>'/u01/app/oracle/oradata/madrid/users04.dbf'</code><code>);</code>

28.發生cbc latch争用時，如可查是哪個sql語句造成的？

<code> </code><code>select v.SQL_HASH_VALUE, v.SQL_ADDRESS, v.INST_ID, v.EVENT </code>

<code> </code><code>from gv$session v </code>

<code> </code><code>where v.EVENT = </code><code>'cache buffer chains'</code><code>;</code>

<code> </code><code>select a.INST_ID, a.SQL_TEXT from gv$sqltext a </code>

<code> </code><code>where a.HASH_VALUE = </code><code>'&HASH_VALUE'</code>

<code> </code><code>and a.ADDRESS = </code><code>'&ADDRESS'</code>

<code> </code><code>ORDER BY address, hash_value, piece;</code>

29.資料字典命中率

30.估算 db_cache 放大或減小後對 I/o 的影響

<code> </code><code>COLUMN size_for_estimate FORMAT 999,999,999,999 heading </code><code>'Cache Size (MB)'</code>

<code> </code><code>COLUMN buffers_for_estimate FORMAT 999,999,999 heading </code><code>'Buffers'</code>

<code> </code><code>COLUMN estd_physical_read_factor FORMAT 999.90 heading </code><code>'Estd Phys|Read Factor'</code>

<code> </code><code>COLUMN estd_physical_reads FORMAT 999,999,999 heading </code><code>'Estd Phys| Reads'</code>

<code> </code><code>SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, </code>

<code> </code><code>estd_physical_reads </code>

<code> </code><code>FROM V$DB_CACHE_ADVICE </code>

<code> </code><code>WHERE name = </code><code>'DEFAULT'</code>

<code> </code><code>AND block_size = (SELECT value FROM V$PARAMETER WHERE name = </code><code>'db_block_size'</code><code>) </code>

<code> </code><code>AND advice_status = </code><code>'ON'</code><code>;</code>

本文轉自張沖andy 部落格園部落格，原文連結： http://www.cnblogs.com/andy6/p/5840657.html ，如需轉載請自行聯系原作者

Oracle日常維護腳本

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List