Oracle数据库日常管理方案精样本.docx

1. Oracle数据库寻常管理方案
Oracle数据库运维解决，更重要是做好数据库管理工作，做好数据库检查与备份工作，然后需要熟悉有关逻辑构造故障解决办法、物理构造故障解决办法，应对数据库损坏状况，在数据库发生损坏而无法修复时候，能运用数据库管理工作中所做数据备份及时恢复系统、数据，恢复正常生产工作。
1.1. 数据库管理
Oracle数据库管理重要是要做好寻常检查管理工作，并检查好数据库备份状况，在发生紧急状况时能及时不但切换到备用系统上进行工作，并要能在数据库数据浮现异常时候，能及时恢复数据。因而，做好寻常检查与管理工作特别重要，重要工作涉及：
l 数据库性能监控检查
l 数据库状态巡检检查
l 数据库备份状况与可恢复性检查
1.1.1. 数据库性能监控管理
通过对数据库性能监控管理工作，可以有效防范风险事件发生，并能从长时间运营数据中，总结出数据库性能状态，为系统性能优化、隐患事件排查提供更好根据。
1.1.1.1. 查找前十条性能差sql
SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE，DISK_READS,sql_text FROM v$sqlarea order BY disk_reads DESC )where ROWNUM<10 ;

1.1.1.2. 查询有enqueue等待事件
SELECT b.SID，b.serial#，b.username，machine，event，wait_time,CHR (BITAND (p1，-16777216) / 16777215) || CHR (BITAND (p1，16711680) / 65535) "Enqueue Type" FROM v$session_wait a，v$session b WHERE a.event NOT LIKE 'SQL*N%' AND a.event NOT LIKE 'rdbms%' AND a.SID = b.SID AND b.SID > 8 AND a.event = 'enqueue' ORDER BY username;

1.1.1.3. 如何拟定哪个表空间读写频繁
select name,phyrds,phywrts,readtim,writetim from v$filestat a,v$dbfile b where a.file#=b.file# order by readtim desc;

SELECT s.sid，kglpnmod “Mode”，kglpnreq “Req”，SPID “OS Process” FROM v$session_wait w，x$kglpn p，v$session s ,v$process o WHERE p.kglpnuse=s.saddr AND kglpnhdl=w.p1raw and w.event like ‘%library cache pin%’ and s.paddr=o.addr;
1.1.1.4. 查询全表扫描表
SQL> col name for a30
SQL> select name,value from v$sysstat where name in ('table scans (short tables)','table scans (long tables)');

NAME VALUE
------------------------------ ----------
table scans (short tables) 18602
table scans (long tables) 111

SQL> select count(target),target from v$session_longops where opname = 'Table Scan' group by target;

COUNT(TARGET) TARGET
------------- ------------------------------
84 ECM_APPL.RPTLOG
159 ECM_DCTM_OTHR.DMR_CONTENT_S
9 ECM_DCTM_OTHR.DM_SYSOBJECT_R
2 ECM_DCTM_OTHR.DM_SYSOBJECT_S

1.1.1.5. 查出全表扫描表
Select sql_text from v$sqltext t，v$sql_plan p Where t.hash_value=p.hash_value And p.operation=’TABLE ACCESS’ And p.option=’FULL’ Order by p.hash-value，t.piece;
1.1.1.6. 查找Fast Full index 扫描Sql语句可以这样：
Select sql_text from v$sqltext t，v$sql_plan p Where t.hash_value=p.hash_value And p.operation=’INDEX’ And p.option=’FULL SCAN’ Order by p.hash-value，t.piece;
1.1.1.7. 查询硬语法分析次数
SQL> select name,value from v$sysstat where name like 'parse count%';

NAME VALUE
------------------------------ ----------
parse count (total) 16103
parse count (hard) 343
parse count (failures) 5
该项显示buffer cache大小与否适当。
公式：1-((physical reads-physical reads direct-physical reads direct (lob)) / session logical reads)
执行：
select 1-((a.value-b.value-c.value)/d.value) from v$sysstat a,v$sysstat b,v$sysstat c,v$sysstat d where a.name=’physical reads’ and b.name=’physical reads direct’ and c.name=’physical reads direct (lob)’ and d.name=’session logical reads’;

1.1.1.8. 显示buffer命中率
公式：1-(physical reads/ (db block gets+consistent gets))
执行：
select 1-(sum(decode(name,'physical reads',value,0)))/(sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0))) "buffer hit ratio" from v$sysstat;

Soft parse ratio：
这项将显示系统与否有太多硬解析。该值将会与原始记录数据对比以保证精准。例如，软解析率仅为0.2则表达硬解析率太高。但是，如果总解析量(parse count total)偏低，这项值可以被忽视。
公式：1 - ( parse count (hard) / parse count (total) )
执行：
select 1-(a.value/b.value) from v$sysstat a,v$sysstat b Where a.name='parse count (hard)' and b.name='parse count (total)';

In-memory sort ratio：该项显示内存中完毕排序所占比例。最抱负状态下，在OLTP系统中，大某些排序不但小并且可以完全在内存里完毕排序。
公式：sorts (memory) / ( sorts (memory) + sorts (disk) )
执行：
select a.value/(b.value+c.value) from v$sysstat a,v$sysstat b,v$sysstat c where a.name=’sorts (memory)’ and b.name=’sorts (memory)’ and c.name=’sorts (disk)’;

Parse to execute ratio：在生产环境，最抱负状态是一条sql语句一次解析多数运营。
公式：1 - (parse count/execute count)
执行：
select 1-(a.value/b.value) from v$sysstat a,v$sysstat b where a.name=’parse count (total)’ and b.name=’execute count’;

Parse CPU to total CPU ratio：该项显示总CPU耗费在执行及解析上比率。如果这项比率较低，阐明系统执行了太多解析。
公式：1 - (parse time cpu / CPU used by this session)
执行：
select 1-(a.value/b.value) from v$sysstat a,v$sysstat b where a.name=’parse time cpu’ and b.name=’CPU used by this session’;

Parse time CPU to parse time elapsed：普通，该项显示锁竞争比率。这项比率计算与否时间耗费在解析分派给CPU进行周期运算(即生产工作)。解析时间耗费不在CPU周期运算普通表达由于锁竞争导致了时间耗费。
公式：parse time cpu / parse time elapsed
执行：
select a.value/b.value from v$sysstat a,v$sysstat b where a.name=’parse time cpu’ and b.name=’parse time elapsed’;

从V$SYSSTAT获取负载间档(Load Profile)数据负载间档是监控系统吞吐量和负载变化重要某些，该某些提供如下每秒和每个事务记录信息：logons cumulative，parse count (total)，parse count (hard)，executes，physical reads，physical writes，block changes，and redo size.
被格式化数据可检查’rates’与否过高，或用于对比其他基线数据设立为辨认system profile在期间如何变化。例如，计算每个事务中block changes可用如下公式：
db block changes / ( user commits + user rollbacks )
执行：
select a.value/(b.value+c.value) from v$sysstat a,v$sysstat b,v$sysstat c where a.name=’db block changes’ and b.name=’user commits’ and c.name=’user rollbacks’;

其他计算记录以衡量负载方式，如下：
Blocks changed for each read：这项显示出block changes在block reads中比例。它将指出与否系统重要用于只读访问或是重要进行诸多数据操作(如：inserts/updates/deletes)
公式：db block changes / session logical reads
执行：
select a.value/b.value from v$sysstat a,v$sysstat b where a.name=’db block changes’ and b.name=’session logical reads’ ;

Rows for each sort：数据排序状况
公式：sorts (rows) / ( sorts (memory) + sorts (disk) )
执行：
select a.value/(b.value+c.value) from v$sysstat a,v$sysstat b,v$sysstat c where a.name=’sorts (rows)’ and b.name=’sorts (memory)’ and c.name=’sorts (disk)’;

1.1.1.9. 查看某表约束条件
select constraint_name，constraint_type,search_condition，r_constraint_name from user_constraints where table_name = upper('&table_name');

select c.constraint_name,c.constraint_type,cc.column_name from user_constraints c,user_cons_columns cc where c.owner = upper('&table_owner') and c.table_name = upper('&table_name') and c.owner = cc.owner and c.constraint_name = cc.constraint_name order by cc.position;

1.1.1.10. 查看表信息
Select * from user_tables;
1.1.1.11. 查看表空间名称及大小
select t.tablespace_name，round(sum(bytes/(1024*1024)),0) ts_size from dba_tablespaces t，dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;

select t.tablespace_name，round(sum(bytes/(1024*1024)),0) ts_size from dba_tablespaces t，dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;

TABLESPACE_NAME TS_SIZE
------------------------------ ----------
CWMLITE 20
DRSYS 20
ODM 20
PERFSTAT 99
SYSTEM 400

1.1.1.12. 查看回滚段名称及大小
select segment_name，tablespace_name，r.status， (initial_extent/1024) InitialExtent，(next_extent/1024) NextExtent， max_extents，v.curext CurExtent From dba_rollback_segs r，v$rollstat v Where r.segment_id = v.usn(+) order by segment_name ;

1.1.1.13. 移动一种表各种分区
BEGIN
FOR x IN (SELECT partition_name
FROM user_tab_partitions
WHERE table_name = ‘BIG_TABLE2′)
LOOP
EXECUTE IMMEDIATE ‘alter table big_table2 move partition ‘|| x.partition_name;
END LOOP;
END;
/
1.1.1.14. 查看LOCK
SELECT /*+ ORDERED USE_HASH(H,R) */
H.SID HOLD_SID,
R.SID WAIT_SID,
decode(H.type,
”MR”，”Media Recovery”,
”RT”，”Redo Thread”,
”UN”，”User Name”,
”TX”，”Transaction”,
”TM”，”DML”,
”UL”，”PL/SQL User Lock”,
”DX”，”Distributed Xaction”,
”CF”，”Control File”,
”IS”，”Instance State”,
”FS”，”File Set”,
”IR”，”Instance Recovery”,
”ST”，”Disk Space Transaction”,
”TS”，”Temp Segment”,
”IV”，”Library Cache Invalidation”,
”LS”，”Log Start or Switch”,
”RW”，”Row Wait”,
”SQ”，”Sequence Number”,
”TE”，”Extend Table”,
”TT”，”Temp Table”,
H.type) type,
decode(H.lmode,
0，”None”， 1，”Null”,
2，”Row-S (SS)”， 3，”Row-X (SX)”,
4，”Share”， 5，”S/Row-X (SSX)”,
6，”Exclusive”， to_char(H.lmode)) hold,
decode(r.request， 0，”None”,
1，”Null”， 2，”Row-S (SS)”,
3，”Row-X (SX)”， 4，”Share”,
5，”S/Row-X (SSX)”,6，”Exclusive”,
to_char(R.request)) request,
R.ID1,R.ID2,R.CTIME
FROM VLOCK H,VLOCK R WHERE H.BLOCK = 1 AND R.BLOCK=0 and H.TYPE <> ”MR” AND R.TYPE <> ”MR” AND H.ID1 = R.ID1 AND H.ID2 = R.ID2

1.1.1.15. 找ORACLE字符集
select * from sys.props$ where name='NLS_CHARACTERSET'；
1.1.1.16. 查看ORACLE运营OS平台
SQL> run
begin
dbms_output.put_line(dbms_utility.port_string);
end;
/
1.1.1.17. 查看空间详细使用状况
CREATE OR REPLACE PROCEDURE show_space (
p_segname IN VARCHAR2,
p_owner IN VARCHAR2 DEFAULT USER,
p_type IN VARCHAR2 DEFAULT 'TABLE',
p_partition IN VARCHAR2 DEFAULT NULL )
AS
l_total_blocks NUMBER;
l_total_bytes NUMBER;
l_unused_blocks NUMBER;
l_unused_bytes NUMBER;
l_lastusedextfileid NUMBER;
l_lastusedextblockid NUMBER;
l_last_used_block NUMBER;
PROCEDURE p (p_label IN VARCHAR2，p_num IN NUMBER)
IS
BEGIN
DBMS_OUTPUT.put_line (RPAD (p_label，40，'.') || p_num);
END;
BEGIN
DBMS_SPACE.unused_space
(segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
last_used_extent_file_id => l_lastusedextfileid,
last_used_extent_block_id => l_lastusedextblocki
last_used_block => l_last_used_block
);
p ('Total Blocks'，l_total_blocks);
p ('Total Bytes'，l_total_bytes);
p ('Unused Blocks'，l_unused_blocks);
p ('Unused Bytes'，l_unused_bytes);
p ('Last Used Ext FileId'，l_lastusedextfileid);
p ('Last Used Ext BlockId'，l_lastusedextblockid);
p ('Last Used Block'，l_last_used_block);
END;
/
1.1.1.18. 显示缓冲区有关SQL
SELECT tch，file#，dbablk,
CASE
WHEN obj =
THEN 'rbs/compat segment'
ELSE (SELECT MAX ( '('|| object_type|| ') '|| owner|| '.'|| object_name) || DECODE (COUNT (*)，1，''，' maybe!') FROM dba_objects WHERE data_object_id = x.obj)
END what
FROM (SELECT tch,file#,dbablk,obj FROM x$bh WHERE state <> 0 ORDER BY tch DESC) x
WHERE ROWNUM <= 5;

1.1.1.19. 获取生成依照文献名
select c.value ||'/' || d.instance_name || '_ora_' ||a.spid || '.trc' trace from v$process a,v$session b,v$parameter c,v$instance d where a.addr=b.paddr and b.audsid=userenv('sessionid') and c.name='user_dump_dest' ;

在v$session_longops视图中，sofar字段表达已经扫描块数，totalwork表达总得需要扫描块数，因此咱们即可以对正在运营长查询进行监控，例如在索引创立时，查看索引创立进度，也可以查看系统中以往长查询。
col opname format a32
col target_desc format a32
col perwork format a12
set lines 131
select sid,OPNAME,TARGET_DESC,sofar,TOTALWORK,trunc(sofar/totalwork*100,2)||'%' as perwork from v$session_longops where sofar!=totalwork;

set lines 121
set pages 999
col opname format a29
col target format a29
col target_desc format a12
col perwork format a12
col remain format 99
col start_time format a21
col sofar format 99999999
col totalwork format 99999999
col sql_text format a101
col bufgets format 99999999

select opname,target,to_char(start_time,'yy-mm-dd:hh24:mi:ss') start_time,elapsed_seconds elapsed,executions execs,buffer_gets/decode(executions,0,1,executions) bufgets,module,sql_text from v$session_longops sl,v$sqlarea sa where sl.sql_hash_value = sa.hash_value and upper(substr(module,1,4)) <> 'RMAN' and substr(opname,1,4) <> 'RMAN' and module <> 'SQL*Plus' and sl.start_time>trunc(sysdate) order by start_time;
1.1.1.20. IO事件监控
1. 监控事例等待
select event,sum(decode(wait_Time,0,0,1)) "Prev"， sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot" from v$session_Wait group by event order by 4;
2. 回滚段争用状况
select name，waits，gets，waits/gets "Ratio" from v$rollstat a，v$rollname b where a.usn = b.usn;
3. 监控表空间 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;
4. 监控文献系统 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#;
1.1.1.21. 在某个顾客下找所有索引
select 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_indexes.index_name，column_position;
1.1.1.22. 监控 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$sysstat a，v$sysstat b，v$sysstat c where a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40;
1.1.1.23. 监控 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；

监控 SGA 中共享缓存区命中率，应当不大于1%
select sum(pins) "Total Pins"，sum(reloads) "Total Reloads"， sum(reloads)/sum(pins) *100 libcache from v$librarycache； select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent" from v$librarycache;

显示所有数据库对象类别和大小
select count(name) num_instances ,type ,sum(source_size) source_size ，sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size，sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required from dba_object_size group by type order by 2;

监控 SGA 中重做日记缓存区命中率，应当不大于1%
SELECT name,gets,misses,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');

监控内存和硬盘排序比率，最佳使它不大于 .10，增长 sort_area_size:
SELECT name