RMAN CURSOR_SHARING=EXACT脚本
CREATE OR REPLACE TRIGGER RMAN_CURSOR_SHARING
AFTER LOGON ON DATABASE
BEGIN
if (upper(sys_context('USERENV', 'MODULE')) like '%RMAN%')
THEN
execute immediate ' alter session set cursor_sharing=EXACT';
END IF;
END;
虽然我们也可以通过dbv(db file verify)工具做到对单个数据文件的坏块检测，但是直接使用RMAN的”backup validate check logical database;”结合V$DATABASE_BLOCK_CORRUPTION视图要方便地多。
如果自己搞不定可以找ASKMACLEAN专业ORACLE数据库修复团队成员帮您恢复!
Script:
1) $ rman target / nocatalog
2) RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup validate check logical database;
}
3) select * from V$DATABASE_BLOCK_CORRUPTION ;
REM www.askmac.cn & www.askmac.cn
4) If V$DATABASE_BLOCK_CORRUPTION contains rows please run this query to
find the objects that contains the corrupted blocks:
SELECT e.owner,
e.segment_type,
e.segment_name,
e.partition_name,
c.file#,
greatest(e.block_id, c.block#) corr_start_block#,
least(e.block_id + e.blocks - 1, c.block# + c.blocks - 1) corr_end_block#,
least(e.block_id + e.blocks - 1, c.block# + c.blocks - 1) -
greatest(e.block_id, c.block#) + 1 blocks_corrupted,
null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner,
s.segment_type,
s.segment_name,
s.partition_name,
c.file#,
header_block corr_start_block#,
header_block corr_end_block#,
1 blocks_corrupted,
'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner,
null segment_type,
null segment_name,
null partition_name,
c.file#,
greatest(f.block_id, c.block#) corr_start_block#,
least(f.block_id + f.blocks - 1, c.block# + c.blocks - 1) corr_end_block#,
least(f.block_id + f.blocks - 1, c.block# + c.blocks - 1) -
greatest(f.block_id, c.block#) + 1 blocks_corrupted,
'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;
SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = &fileid
and &blockid between block_id AND block_id + blocks - 1;
以下脚本可以用于收集 11gR2中 RAC DRM 动态资源管理 特性的诊断信息:
REM for 11.2 only
REM written by Maclean.Liu
set linesize 140 pagesize 1400
select DRMS, AVG_DRM_TIME, QUIESCE_T,FRZ_T,CLEANUP_T,REPLAY_T,FIXWRITE_T,SYNC_T from X$KJDRMAFNSTATS
/
select * from GV$DYNAMIC_REMASTER_STATS
/
select object, node, sopens, xopens, xfers
from x$object_policy_statistics
-- where object=&object_id
/
select data_object_id, current_master, previous_master, remaster_cnt from gv$gcspfmaster_info
/
select * from gv$policy_history
-- where object=&object_id
order by EVENT_DATE
/
select name, value
from v$sysstat
where name in ('gc local grants',
'gc remote grants')
/
以下SQL脚本可以用于列出数据库最近1分钟的性能度量信息(performance metric):
set linesize 80 pagesize 1400
SELECT /*+ ORDERED USE_MERGE(m) */
TO_CHAR(FROM_TZ(CAST(m.end_time AS TIMESTAMP),
TO_CHAR(systimestamp, 'tzr')) AT TIME ZONE sessiontimezone,
'YYYY-MM-DD HH24:MI:SS'),
SUM(CASE
WHEN a.internal_metric_name = 'transactions_ps' THEN
m.value
ELSE
0
END) transactions_ps,
SUM(CASE
WHEN a.internal_metric_name = 'physreads_ps' THEN
m.value
ELSE
0
END) physreads_ps,
SUM(CASE
WHEN a.internal_metric_name = 'physreads_pt' THEN
m.value
ELSE
0
END) physreads_pt,
SUM(CASE
WHEN a.internal_metric_name = 'physwrites_ps' THEN
m.value
ELSE
0
END) physwrites_ps,
SUM(CASE
WHEN a.internal_metric_name = 'physwrites_pt' THEN
m.value
ELSE
0
END) physwrites_pt,
SUM(CASE
WHEN a.internal_metric_name = 'physreadsdir_ps' THEN
m.value
ELSE
0
END) physreadsdir_ps,
SUM(CASE
WHEN a.internal_metric_name = 'physreadsdir_pt' THEN
m.value
ELSE
0
END) physreadsdir_pt,
SUM(CASE
WHEN a.internal_metric_name = 'physwritesdir_ps' THEN
m.value
ELSE
0
END) physwritesdir_ps,
SUM(CASE
WHEN a.internal_metric_name = 'physwritesdir_pt' THEN
m.value
ELSE
0
END) physwritesdir_pt,
SUM(CASE
WHEN a.internal_metric_name = 'physreadslob_ps' THEN
m.value
ELSE
0
END) physreadslob_ps,
SUM(CASE
WHEN a.internal_metric_name = 'physreadslob_pt' THEN
m.value
ELSE
0
END) physreadslob_pt,
SUM(CASE
WHEN a.internal_metric_name = 'physwriteslob_ps' THEN
m.value
ELSE
0
END) physwriteslob_ps,
SUM(CASE
WHEN a.internal_metric_name = 'physwriteslob_pt' THEN
m.value
ELSE
0
END) physwriteslob_pt,
SUM(CASE
WHEN a.internal_metric_name = 'redosize_ps' THEN
m.value
ELSE
0
END) redosize_ps,
SUM(CASE
WHEN a.internal_metric_name = 'logons_ps' THEN
m.value
ELSE
0
END) logons_ps,
SUM(CASE
WHEN a.internal_metric_name = 'logons_pt' THEN
m.value
ELSE
0
END) logons_pt,
SUM(CASE
WHEN a.internal_metric_name = 'opncurs_ps' THEN
m.value
ELSE
0
END) opncurs_ps,
SUM(CASE
WHEN a.internal_metric_name = 'opncurs_pt' THEN
m.value
ELSE
0
END) opncurs_pt,
SUM(CASE
WHEN a.internal_metric_name = 'commits_ps' THEN
m.value
ELSE
0
END) commits_ps,
SUM(CASE
WHEN a.internal_metric_name = 'commits_pt' THEN
m.value
ELSE
0
END) commits_pt,
SUM(CASE
WHEN a.internal_metric_name = 'rollbacks_ps' THEN
m.value
ELSE
0
END) rollbacks_ps,
SUM(CASE
WHEN a.internal_metric_name = 'rollbacks_pt' THEN
m.value
ELSE
0
END) rollbacks_pt,
SUM(CASE
WHEN a.internal_metric_name = 'usercalls_ps' THEN
m.value
ELSE
0
END) usercalls_ps,
SUM(CASE
WHEN a.internal_metric_name = 'usercalls_pt' THEN
m.value
ELSE
0
END) usercalls_pt,
SUM(CASE
WHEN a.internal_metric_name = 'recurscalls_ps' THEN
m.value
ELSE
0
END) recurscalls_ps,
SUM(CASE
WHEN a.internal_metric_name = 'recurscalls_pt' THEN
m.value
ELSE
0
END) recurscalls_pt,
SUM(CASE
WHEN a.internal_metric_name = 'logreads_ps' THEN
m.value
ELSE
0
END) logreads_ps,
SUM(CASE
WHEN a.internal_metric_name = 'logreads_pt' THEN
m.value
ELSE
0
END) logreads_pt,
SUM(CASE
WHEN a.internal_metric_name = 'dbwrcheckpoints_ps' THEN
m.value
ELSE
0
END) dbwrcheckpoints_ps,
SUM(CASE
WHEN a.internal_metric_name = 'bgcheckpoints_ps' THEN
m.value
ELSE
0
END) bgcheckpoints_ps,
SUM(CASE
WHEN a.internal_metric_name = 'redowrites_ps' THEN
m.value
ELSE
0
END) redowrites_ps,
SUM(CASE
WHEN a.internal_metric_name = 'redowrites_pt' THEN
m.value
ELSE
0
END) redowrites_pt,
SUM(CASE
WHEN a.internal_metric_name = 'tabscanslong_ps' THEN
m.value
ELSE
0
END) tabscanslong_ps,
SUM(CASE
WHEN a.internal_metric_name = 'tabscanslong_pt' THEN
m.value
ELSE
0
END) tabscanslong_pt,
SUM(CASE
WHEN a.internal_metric_name = 'tabscanstotal_ps' THEN
m.value
ELSE
0
END) tabscanstotal_ps,
SUM(CASE
WHEN a.internal_metric_name = 'tabscanstotal_pt' THEN
m.value
ELSE
0
END) tabscanstotal_pt,
SUM(CASE
WHEN a.internal_metric_name = 'indxscansfull_pt' THEN
m.value
ELSE
0
END) indxscansfull_pt,
SUM(CASE
WHEN a.internal_metric_name = 'indxscansfull_ps' THEN
m.value
ELSE
0
END) indxscansfull_ps,
SUM(CASE
WHEN a.internal_metric_name = 'indxscanstotal_ps' THEN
m.value
ELSE
0
END) indxscanstotal_ps,
SUM(CASE
WHEN a.internal_metric_name = 'indxscanstotal_pt' THEN
m.value
ELSE
0
END) indxscanstotal_pt,
SUM(CASE
WHEN a.internal_metric_name = 'parses_ps' THEN
m.value
ELSE
0
END) parses_ps,
SUM(CASE
WHEN a.internal_metric_name = 'parses_pt' THEN
m.value
ELSE
0
END) parses_pt,
SUM(CASE
WHEN a.internal_metric_name = 'hardparses_ps' THEN
m.value
ELSE
0
END) hardparses_ps,
SUM(CASE
WHEN a.internal_metric_name = 'hardparses_pt' THEN
m.value
ELSE
0
END) hardparses_pt,
SUM(CASE
WHEN a.internal_metric_name = 'failedparses_ps' THEN
m.value
ELSE
0
END) failedparses_ps,
SUM(CASE
WHEN a.internal_metric_name = 'failedparses_pt' THEN
m.value
ELSE
0
END) failedparses_pt,
SUM(CASE
WHEN a.internal_metric_name = 'executions_ps' THEN
m.value
ELSE
0
END) executions_ps,
SUM(CASE
WHEN a.internal_metric_name = 'sortsdisk_ps' THEN
m.value
ELSE
0
END) sortsdisk_ps,
SUM(CASE
WHEN a.internal_metric_name = 'sortsdisk_pt' THEN
m.value
ELSE
0
END) sortsdisk_pt,
SUM(CASE
WHEN a.internal_metric_name = 'rows_psort' THEN
m.value
ELSE
0
END) rows_psort,
SUM(CASE
WHEN a.internal_metric_name = 'executeswoparse_pct' THEN
m.value
ELSE
0
END) executeswoparse_pct,
SUM(CASE
WHEN a.internal_metric_name = 'softparse_pct' THEN
m.value
ELSE
0
END) softparse_pct,
SUM(CASE
WHEN a.internal_metric_name = 'usercall_pct' THEN
m.value
ELSE
0
END) usercall_pct,
SUM(CASE
WHEN a.internal_metric_name = 'networkbytes_ps' THEN
m.value
ELSE
0
END) networkbytes_ps,
SUM(CASE
WHEN a.internal_metric_name = 'enqtimeouts_ps' THEN
m.value
ELSE
0
END) enqtimeouts_ps,
SUM(CASE
WHEN a.internal_metric_name = 'enqtimeouts_pt' THEN
m.value
ELSE
0
END) enqtimeouts_pt,
SUM(CASE
WHEN a.internal_metric_name = 'enqwaits_ps' THEN
m.value
ELSE
0
END) enqwaits_ps,
SUM(CASE
WHEN a.internal_metric_name = 'enqwaits_pt' THEN
m.value
ELSE
0
END) enqwaits_pt,
SUM(CASE
WHEN a.internal_metric_name = 'enqdeadlocks_ps' THEN
m.value
ELSE
0
END) enqdeadlocks_ps,
SUM(CASE
WHEN a.internal_metric_name = 'enqdeadlocks_pt' THEN
m.value
ELSE
0
END) enqdeadlocks_pt,
SUM(CASE
WHEN a.internal_metric_name = 'enqreqs_ps' THEN
m.value
ELSE
0
END) enqreqs_ps,
SUM(CASE
WHEN a.internal_metric_name = 'enqreqs_pt' THEN
m.value
ELSE
0
END) enqreqs_pt,
SUM(CASE
WHEN a.internal_metric_name = 'dbblkgets_ps' THEN
m.value
ELSE
0
END) dbblkgets_ps,
SUM(CASE
WHEN a.internal_metric_name = 'dbblkgets_pt' THEN
m.value
ELSE
0
END) dbblkgets_pt,
SUM(CASE
WHEN a.internal_metric_name = 'consistentreadgets_ps' THEN
m.value
ELSE
0
END) consistentreadgets_ps,
SUM(CASE
WHEN a.internal_metric_name = 'consistentreadgets_pt' THEN
m.value
ELSE
0
END) consistentreadgets_pt,
SUM(CASE
WHEN a.internal_metric_name = 'dbblkchanges_ps' THEN
m.value
ELSE
0
END) dbblkchanges_ps,
SUM(CASE
WHEN a.internal_metric_name = 'dbblkchanges_pt' THEN
m.value
ELSE
0
END) dbblkchanges_pt,
SUM(CASE
WHEN a.internal_metric_name = 'consistentreadchanges_ps' THEN
m.value
ELSE
0
END) consistentreadchanges_ps,
SUM(CASE
WHEN a.internal_metric_name = 'consistentreadchanges_pt' THEN
m.value
ELSE
0
END) consistentreadchanges_pt,
SUM(CASE
WHEN a.internal_metric_name = 'crblks_ps' THEN
m.value
ELSE
0
END) crblks_ps,
SUM(CASE
WHEN a.internal_metric_name = 'crblks_pt' THEN
m.value
ELSE
0
END) crblks_pt,
SUM(CASE
WHEN a.internal_metric_name = 'crundorecs_pt' THEN
m.value
ELSE
0
END) crundorecs_pt,
SUM(CASE
WHEN a.internal_metric_name = 'userrollbackundorec_ps' THEN
m.value
ELSE
0
END) userrollbackundorec_ps,
SUM(CASE
WHEN a.internal_metric_name = 'userrollbackundorec_pt' THEN
m.value
ELSE
0
END) userrollbackundorec_pt,
SUM(CASE
WHEN a.internal_metric_name = 'leafnodesplits_ps' THEN
m.value
ELSE
0
END) leafnodesplits_ps,
SUM(CASE
WHEN a.internal_metric_name = 'leafnodesplits_pt' THEN
m.value
ELSE
0
END) leafnodesplits_pt,
SUM(CASE
WHEN a.internal_metric_name = 'branchnodesplits_ps' THEN
m.value
ELSE
0
END) branchnodesplits_ps,
SUM(CASE
WHEN a.internal_metric_name = 'branchnodesplits_pt' THEN
m.value
ELSE
0
END) branchnodesplits_pt,
SUM(CASE
WHEN a.internal_metric_name = 'redosize_pt' THEN
m.value
ELSE
0
END) redosize_pt,
SUM(CASE
WHEN a.internal_metric_name = 'crundorecs_ps' THEN
m.value
ELSE
0
END) crundorecs_ps,
SUM(CASE
WHEN a.internal_metric_name = 'dbtime_ps' THEN
m.value
ELSE
0
END) dbtime_ps,
SUM(CASE
WHEN a.internal_metric_name = 'avg_active_sessions' THEN
m.value
ELSE
0
END) avg_active_sessions,
SUM(CASE
WHEN a.internal_metric_name = 'avg_sync_singleblk_read_latency' THEN
m.value
ELSE
0
END) avg_block_read_latency,
SUM(CASE
WHEN a.internal_metric_name = 'iombs_ps' THEN
m.value
ELSE
0
END) iombs_ps,
SUM(CASE
WHEN a.internal_metric_name = 'iorequests_ps' THEN
m.value
ELSE
0
END) iorequests_ps
FROM v$alert_types a, v$threshold_types t, v$sysmetric m
WHERE a.internal_metric_category = 'instance_throughput'
AND a.reason_id = t.alert_reason_id
AND t.metrics_id = m.metric_id
AND m.group_id = 2
AND m.end_time <= SYSDATE
GROUP BY m.end_time
ORDER BY m.end_time ASC
/
使用方法:
SQL> @metric
TO_CHAR(FROM_TZ(CAS TRANSACTIONS_PS PHYSREADS_PS PHYSREADS_PT PHYSWRITES_PS
------------------- --------------- ------------ ------------ -------------
PHYSWRITES_PT PHYSREADSDIR_PS PHYSREADSDIR_PT PHYSWRITESDIR_PS PHYSWRITESDIR_PT
------------- --------------- --------------- ---------------- ----------------
PHYSREADSLOB_PS PHYSREADSLOB_PT PHYSWRITESLOB_PS PHYSWRITESLOB_PT REDOSIZE_PS
--------------- --------------- ---------------- ---------------- -----------
LOGONS_PS LOGONS_PT OPNCURS_PS OPNCURS_PT COMMITS_PS COMMITS_PT ROLLBACKS_PS
---------- ---------- ---------- ---------- ---------- ---------- ------------
ROLLBACKS_PT USERCALLS_PS USERCALLS_PT RECURSCALLS_PS RECURSCALLS_PT LOGREADS_PS
------------ ------------ ------------ -------------- -------------- -----------
LOGREADS_PT DBWRCHECKPOINTS_PS BGCHECKPOINTS_PS REDOWRITES_PS REDOWRITES_PT
----------- ------------------ ---------------- ------------- -------------
TABSCANSLONG_PS TABSCANSLONG_PT TABSCANSTOTAL_PS TABSCANSTOTAL_PT
--------------- --------------- ---------------- ----------------
INDXSCANSFULL_PT INDXSCANSFULL_PS INDXSCANSTOTAL_PS INDXSCANSTOTAL_PT PARSES_PS
---------------- ---------------- ----------------- ----------------- ----------
PARSES_PT HARDPARSES_PS HARDPARSES_PT FAILEDPARSES_PS FAILEDPARSES_PT
---------- ------------- ------------- --------------- ---------------
EXECUTIONS_PS SORTSDISK_PS SORTSDISK_PT ROWS_PSORT EXECUTESWOPARSE_PCT
------------- ------------ ------------ ---------- -------------------
SOFTPARSE_PCT USERCALL_PCT NETWORKBYTES_PS ENQTIMEOUTS_PS ENQTIMEOUTS_PT
------------- ------------ --------------- -------------- --------------
ENQWAITS_PS ENQWAITS_PT ENQDEADLOCKS_PS ENQDEADLOCKS_PT ENQREQS_PS ENQREQS_PT
----------- ----------- --------------- --------------- ---------- ----------
DBBLKGETS_PS DBBLKGETS_PT CONSISTENTREADGETS_PS CONSISTENTREADGETS_PT
------------ ------------ --------------------- ---------------------
DBBLKCHANGES_PS DBBLKCHANGES_PT CONSISTENTREADCHANGES_PS
--------------- --------------- ------------------------
CONSISTENTREADCHANGES_PT CRBLKS_PS CRBLKS_PT CRUNDORECS_PT
------------------------ ---------- ---------- -------------
USERROLLBACKUNDOREC_PS USERROLLBACKUNDOREC_PT LEAFNODESPLITS_PS
---------------------- ---------------------- -----------------
LEAFNODESPLITS_PT BRANCHNODESPLITS_PS BRANCHNODESPLITS_PT REDOSIZE_PT
----------------- ------------------- ------------------- -----------
CRUNDORECS_PS DBTIME_PS AVG_ACTIVE_SESSIONS AVG_BLOCK_READ_LATENCY IOMBS_PS
------------- ---------- ------------------- ---------------------- ----------
IOREQUESTS_PS
-------------
2011-10-27 20:02:23 .349533955 2.69640479 7.71428571 .116511318
.333333333 .199733688 .571428571 .116511318 .333333333
.116511318 .333333333 .116511318 .333333333 16212.0506
.016644474 .047619048 19.0745672 54.5714286 .349533955 100 0
0 .349533955 1 81.1917443 232.285714 130.54261
373.47619 0 0 .515978695 1.47619048
0 0 .216378162 .619047619
0 0 19.4573901 55.6666667 4.92676431
14.0952381 1.89747004 5.42857143 0 0
20.4560586 0 0 22.026087 75.9153784
61.4864865 .42865891 995.838881 0 0
0 0 0 0 8.9713715 25.6666667
44.9400799 128.571429 85.60253 244.904762
45.1398136 129.142857 .249667111
.714285714 0 0 0
0 0 .199733688
.571428571 0 0 46381.9048
0 .023586884 .000235869 .032960413 3.09587217
195.489348
PS Per Second
PT Per Transaction
以下脚本可以用于收集数据库中用户的角色和表空间等信息(user_role_tbs.sql):
SET pagesize 50 linesize 115
REM
COLUMN username format a10 heading User
COLUMN default_tablespace format a12 heading Default
COLUMN temporary_tablespace format a12 heading Temporary
COLUMN granted_role format a25 heading Roles
COLUMN default_role format a10 heading Default?
COLUMN admin_option format a7 heading Admin?
COLUMN profile format a12 heading Profile
REM
BREAK on username skip 1 on account_status on default_tablespace on temporary_tablespace on profile
REM
SELECT username,
default_tablespace,
temporary_tablespace,
profile,
granted_role,
admin_option,
default_role
FROM sys.dba_users a, sys.dba_role_privs b
WHERE a.username = b.grantee
ORDER BY username,
default_tablespace,
temporary_tablespace,
profile,
granted_role;
REM
SET termout on flush on feedback on verify on
CLEAR columns
CLEAR breaks
Sample Output:
User Default Temporary Profile Roles Admin? Default?
---------- ------------ ------------ ------------ ------------------------- ------- ----------
SCOTT USERS TEMP DEFAULT CONNECT NO YES
RESOURCE NO YES
SYS SYSTEM TEMP DEFAULT AQ_ADMINISTRATOR_ROLE YES YES
AQ_USER_ROLE YES YES
CONNECT YES YES
DBA YES YES
DELETE_CATALOG_ROLE YES YES
EXECUTE_CATALOG_ROLE YES YES
EXP_FULL_DATABASE YES YES
HS_ADMIN_ROLE YES YES
IMP_FULL_DATABASE YES YES
OEM_MONITOR YES YES
RECOVERY_CATALOG_OWNER YES YES
RESOURCE YES YES
SELECT_CATALOG_ROLE YES YES
SYSTEM SYSTEM TEMP DEFAULT AQ_ADMINISTRATOR_ROLE YES YES
DBA YES YES
以下脚本可以用于收集介质恢复诊断信息(recovery_info.sql)：
--- begin [recovery_info.sql] --- define spoolfile = &1 spool &spoolfile alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'; alter session set timed_statistics = true; alter session set max_dump_file_size = UNLIMITED; set feedback on set term on set wrap on set trimspool on set pagesize 1000 set linesize 100 set numwidth 10 select to_char(sysdate) start_time from dual; column host_name format a20 tru select instance_name, host_name, version, status, startup_time from v$instance; set echo on select * from v$database; select * from v$controlfile; select * from v$tablespace; select * from v$datafile; select * from v$datafile_header; select * from v$tempfile; select * from v$thread; select * from v$log; select * from v$logfile; select * from v$archived_log; alter session set events 'immediate trace name file_hdrs level 3'; alter session set events 'immediate trace name redohdr level 3'; set echo off Prompt; Prompt Output file name is:; define spoolfile Prompt; Prompt ALERT.LOG and TRACE FILES are located in:; column host_name format a12 tru column name format a20 tru column value format a60 tru select distinct i.host_name, p.name, p.value from v$instance i, v$parameter p where p.name like '%_dump_dest' and p.name != 'core_dump_dest'; select to_char(sysdate) end_time from dual; spool off exit --- end [recovery_info.sql] ---
Script:以下脚本可以用于诊断segement space usage问题：
set serveroutput on;
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('&OWNER', '&TABNAME', 'TABLE', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/
SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
"Data lower than HWM in MB" FROM DBA_TABLES WHERE UPPER(owner) =UPPER('&OWNER') order by 2 desc;
select tablespace_name, allocated_space, reclaimable_space from table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'ALL'))
以下脚本可以用于收集10g以后的闪回数据库日志Flashback Database Log的诊断信息:
WITH flashback_database_log AS
(SELECT ROUND((SYSDATE - oldest_flashback_time) * 24 * 60, 2) oldest_log_minutes,
retention_target retention_target_minutes,
flashback_size / 1048576 flashback_size_mb,
estimated_flashback_size / 1048576 estimated_flashback_size_mb
FROM v$flashback_database_log),
flashback_database_logfile AS
(SELECT COUNT(*) logs,
SUM(BYTES / 1048576) size_mb,
MIN(first_time) oldest_log,
MAX(first_time) latest_log
FROM v$flashback_database_logfile),
flashback_usage AS
(SELECT file_type,
ROUND(mb_used, 2) mb_used,
ROUND(mb_reclaimable, 2) mb_reclaimable,
DECODE(total_mb, 0, 0, ROUND(mb_used * 100 / total_mb, 2)) percent_space_used,
DECODE(total_mb, 0, 0, ROUND(mb_reclaimable * 100 / total_mb, 2)) percent_space_reclaimable,
number_of_files,
total_mb db_recovery_file_dest_mb,
flashback_retention_target,
oldest_record,
ROUND((sysdate - oldest_record) * 24 * 60, 2) oldest_record_age_sec
FROM (SELECT SUM(DECODE(NAME,
'db_recovery_file_dest_size',
VALUE / 1048576,
0)) total_mb,
SUM(DECODE(NAME, 'db_flashback_retention_target', VALUE, 0)) flashback_retention_target
FROM v$parameter
WHERE NAME IN ('db_recovery_file_dest_size',
'db_flashback_retention_target')),
(SELECT 'FLASHBACKLOG' file_type,
NVL(SUM(BYTES) / 1048576, 0) mb_used,
sum(CASE
WHEN last_time <= (sysdate - (tgt.value / 1440)) THEN
bytes / 1048576
ELSE
0
END) mb_reclaimable,
COUNT(*) number_of_files,
MIN(first_time) oldest_record
FROM (select bytes,
lead(first_time) over(order by first_time asc) last_time,
first_time
from v$flashback_database_logfile) fla_log,
(SELECT value value
FROM v$parameter
WHERE name = 'db_flashback_retention_target') tgt
UNION
SELECT 'BACKUPPIECE' file_type,
NVL(SUM(BYTES / 1048576), 0) mb,
SUM(CASE
WHEN dl.rectype = 13 THEN
(BYTES / 1048576)
ELSE
0
END) reclaimable_mb,
COUNT(*) no_of_files,
MIN(start_time) oldest_record
FROM v$backup_piece bp, x$kccagf dl
WHERE is_recovery_dest_file = 'YES'
AND deleted = 'NO'
AND bp.recid = dl.recid(+)
AND dl.rectype(+) = 13
UNION
SELECT 'ARCHIVELOG' file_type,
NVL(SUM(blocks * block_size) / 1048576, 0) mb,
SUM(CASE
WHEN dl.rectype = 11 THEN
(LOG.blocks * LOG.block_size / 1048576)
ELSE
0
END) reclaimable_mb,
COUNT(*) no_of_files,
MIN(first_time) oldest_record
FROM v$archived_log log, x$kccagf dl
WHERE deleted = 'NO'
AND is_recovery_dest_file = 'YES'
AND dl.recid(+) = log.recid
AND dl.rectype(+) = 11
UNION
SELECT 'ONLINELOG' file_type,
SUM(BYTES / 1048576) mb,
0 reclaimable,
COUNT(*) no_of_files,
MIN(first_time) oldest_record
FROM v$logfile lf,
(SELECT group#, BYTES, first_time
FROM v$standby_log
UNION
SELECT group#, BYTES, first_time FROM v$log) l
WHERE l.group# = lf.group#
AND lf.is_recovery_dest_file = 'YES'
UNION
SELECT 'IMAGECOPY',
NVL(SUM(blocks * (block_size / 1048576)), 0) mb,
0 reclaimable_mb,
COUNT(*) no_of_files,
MIN(creation_time) oldest_record
FROM v$datafile_copy
WHERE deleted = 'NO'
AND is_recovery_dest_file = 'YES'
UNION
SELECT 'CONTROLFILE',
NVL(SUM(block_size * file_size_blks) / 1048576, 0) mb,
0 reclaimable,
COUNT(*) no_of_files,
NULL oldest_record
FROM v$controlfile
WHERE is_recovery_dest_file = 'YES'))
SELECT order_, NAME, VALUE
FROM(
SELECT 0 order_, NAME, VALUE
FROM v$parameter
WHERE NAME LIKE 'db_recovery_file%'
UNION
SELECT 3, 'oldest flashback log (minutes)', TO_CHAR(ROUND(oldest_log_minutes, 2))
FROM flashback_database_log
UNION
SELECT 1, 'retention target (minutes)', TO_CHAR(ROUND(retention_target_minutes, 2))
FROM flashback_database_log
UNION
SELECT 2, 'estimated size for flashback logs (MB)', TO_CHAR(ROUND(estimated_flashback_size_mb, 2))
FROM flashback_database_log
UNION
SELECT 2, 'Current flashback log count', TO_CHAR(logs)
FROM flashback_database_logfile
UNION
SELECT 3, 'Most recent flashback log (minutes)', TO_CHAR(ROUND((SYSDATE - latest_log) * 24 * 60, 2))
FROM flashback_database_logfile
UNION
SELECT 4, 'Total size of all files in MB', TO_CHAR(ROUND(SUM(mb_used), 2))
FROM flashback_usage
UNION
SELECT 5, 'Total size of reclaimable files in MB', TO_CHAR(ROUND(SUM(mb_reclaimable), 2))
FROM flashback_usage
UNION
SELECT 6, 'unused space in MB', TO_CHAR(ROUND(MIN(db_recovery_file_dest_mb) - SUM(mb_used)))
FROM flashback_usage)
ORDER BY order_, NAME
/
Sample Output:
ORDER_ NAME VALUE
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
0 db_recovery_file_dest +SYSTEMDG
0 db_recovery_file_dest_size 5218762752
1 retention target (minutes) 1440
2 Current flashback log count 33
2 estimated size for flashback logs (MB) 142.15
3 Most recent flashback log (minutes) 164.03
3 oldest flashback log (minutes) 5846.35
4 Total size of all files in MB 1963.11
5 Total size of reclaimable files in MB 534.47
6 unused space in MB 3014
以下脚本可以用于列出最近Oracle数据库每小时估算的redo重做日志产生量，因为估算数据来源于archivelog的产生量和大小，所以数据是近似值，可供参考:
WITH times AS
(SELECT /*+ MATERIALIZE */
hour_end_time
FROM (SELECT (TRUNC(SYSDATE, 'HH') + (2 / 24)) - (ROWNUM / 24) hour_end_time
FROM DUAL
CONNECT BY ROWNUM <= (1 * 24) + 3),
v$database
WHERE log_mode = 'ARCHIVELOG')
SELECT hour_end_time, NVL(ROUND(SUM(size_mb), 3), 0) size_mb, i.instance_name
FROM(
SELECT hour_end_time, CASE WHEN(hour_end_time - (1 / 24)) > lag_next_time THEN(next_time + (1 / 24) - hour_end_time) * (size_mb / (next_time - lag_next_time)) ELSE 0 END + CASE WHEN hour_end_time < lead_next_time THEN(hour_end_time - next_time) * (lead_size_mb / (lead_next_time - next_time)) ELSE 0 END + CASE WHEN lag_next_time > (hour_end_time - (1 / 24)) THEN size_mb ELSE 0 END + CASE WHEN next_time IS NULL THEN(1 / 24) * LAST_VALUE(CASE WHEN next_time IS NOT NULL AND lag_next_time IS NULL THEN 0 ELSE(size_mb / (next_time - lag_next_time)) END IGNORE NULLS) OVER(
ORDER BY hour_end_time DESC, next_time DESC) ELSE 0 END size_mb
FROM(
SELECT t.hour_end_time, arc.next_time, arc.lag_next_time, LEAD(arc.next_time) OVER(
ORDER BY arc.next_time ASC) lead_next_time, arc.size_mb, LEAD(arc.size_mb) OVER(
ORDER BY arc.next_time ASC) lead_size_mb
FROM times t,(
SELECT next_time, size_mb, LAG(next_time) OVER(
ORDER BY next_time) lag_next_time
FROM(
SELECT next_time, SUM(size_mb) size_mb
FROM(
SELECT DISTINCT a.sequence#, a.next_time, ROUND(a.blocks * a.block_size / 1024 / 1024) size_mb
FROM v$archived_log a,(
SELECT /*+ no_merge */
CASE WHEN TO_NUMBER(pt.VALUE) = 0 THEN 1 ELSE TO_NUMBER(pt.VALUE) END VALUE
FROM v$parameter pt
WHERE pt.name = 'thread') pt
WHERE a.next_time > SYSDATE - 3 AND a.thread# = pt.VALUE AND ROUND(a.blocks * a.block_size / 1024 / 1024) > 0)
GROUP BY next_time)) arc
WHERE t.hour_end_time = (TRUNC(arc.next_time(+), 'HH') + (1 / 24)))
WHERE hour_end_time > TRUNC(SYSDATE, 'HH') - 1 - (1 / 24)), v$instance i
WHERE hour_end_time <= TRUNC(SYSDATE, 'HH')
GROUP BY hour_end_time, i.instance_name
ORDER BY hour_end_time
/
Sample Output:
HOUR_END_TIME SIZE_MB INSTANCE_NAME ------------- ---------- ---------------- 2011/9/29 1:0 2.92 VPROD1 2011/9/29 2:0 2.92 VPROD1 2011/9/29 3:0 2.92 VPROD1 2011/9/29 4:0 2.92 VPROD1 2011/9/29 5:0 2.92 VPROD1 2011/9/29 6:0 2.92 VPROD1 2011/9/29 7:0 2.92 VPROD1 2011/9/29 8:0 2.92 VPROD1 2011/9/29 9:0 2.92 VPROD1 2011/9/29 10: 2.92 VPROD1 2011/9/29 11: 2.92 VPROD1 2011/9/29 12: 3.537 VPROD1 2011/9/29 13: 3.55 VPROD1 2011/9/29 14: 3.55 VPROD1 2011/9/29 15: 3.55 VPROD1 2011/9/29 16: 3.55 VPROD1 2011/9/29 17: 3.55 VPROD1 2011/9/29 18: 3.55 VPROD1 2011/9/29 19: 3.55 VPROD1 2011/9/29 20: 3.55 VPROD1
以下脚本可以用于收集Oracle Instance I/O 性能信息：
set linesize 80 pagesize 1400;
SELECT ios.filetype_name,
df.file_name,
df.tablespace_name,
ios.small_read_megabytes + ios.large_read_megabytes read_megabytes,
ios.small_write_megabytes + ios.large_write_megabytes write_megabytes,
ios.small_read_reqs + ios.large_read_reqs read_reqs,
ios.small_write_reqs + ios.large_write_reqs write_reqs,
ios.small_read_servicetime + ios.large_read_servicetime read_servicetime,
ios.small_write_servicetime + ios.large_write_servicetime write_servicetime,
ios.small_read_reqs,
ios.small_read_servicetime
FROM v$iostat_file ios, dba_data_files df
WHERE ios.filetype_name = 'Data File'
AND df.file_id = ios.file_no
UNION ALL
SELECT ios.filetype_name,
tf.file_name,
tf.tablespace_name,
ios.small_read_megabytes + ios.large_read_megabytes read_megabytes,
ios.small_write_megabytes + ios.large_write_megabytes write_megabytes,
ios.small_read_reqs + ios.large_read_reqs read_reqs,
ios.small_write_reqs + ios.large_write_reqs write_reqs,
ios.small_read_servicetime + ios.large_read_servicetime read_servicetime,
ios.small_write_servicetime + ios.large_write_servicetime write_servicetime,
ios.small_read_reqs,
ios.small_read_servicetime
FROM v$iostat_file ios, dba_temp_files tf
WHERE ios.filetype_name = 'Temp File'
AND tf.file_id = ios.file_no
UNION ALL
SELECT ios.filetype_name,
NULL file_name,
NULL tablespace_name,
ios.small_read_megabytes + ios.large_read_megabytes read_megabytes,
ios.small_write_megabytes + ios.large_write_megabytes write_megabytes,
ios.small_read_reqs + ios.large_read_reqs read_reqs,
ios.small_write_reqs + ios.large_write_reqs write_reqs,
ios.small_read_servicetime + ios.large_read_servicetime read_servicetime,
ios.small_write_servicetime + ios.large_write_servicetime write_servicetime,
ios.small_read_reqs,
ios.small_read_servicetime
FROM v$iostat_file ios
WHERE ios.filetype_name NOT IN ('Temp File', 'Data File')
/
SELECT function_name,
small_read_megabytes + small_write_megabytes + large_read_megabytes +
large_write_megabytes throughput_megabytes,
small_read_reqs + small_write_reqs + large_read_reqs +
large_write_reqs throughput_reqs,
number_of_waits,
wait_time
FROM v$iostat_function
/
SELECT * FROM v$iostat_file
/
col filetype_name format a14 heading "File Type"
col reads format 9,999,999 heading "Reads"
col writes format 9,999,999 heading "Writes"
col read_time_sec format 99,999 heading "Read Time|sec"
col write_time_sec format 99,999 heading "Write Time|sec"
col avg_sync_read_ms format 999.99 heading "Avg Sync|Read ms"
col total_io_seconds format 9\99,999,999 heading "Total IO|sec"
set lines 80
set pages 10000
set echo on
WITH iostat_file AS
(SELECT filetype_name,SUM(large_read_reqs) large_read_reqs,
SUM(large_read_servicetime) large_read_servicetime,
SUM(large_write_reqs) large_write_reqs,
SUM(large_write_servicetime) large_write_servicetime,
SUM(small_read_reqs) small_read_reqs,
SUM(small_read_servicetime) small_read_servicetime,
SUM(small_sync_read_latency) small_sync_read_latency,
SUM(small_sync_read_reqs) small_sync_read_reqs,
SUM(small_write_reqs) small_write_reqs,
SUM(small_write_servicetime) small_write_servicetime
FROM sys.v_$iostat_file
GROUP BY filetype_name)
SELECT filetype_name, small_read_reqs + large_read_reqs reads,
large_write_reqs + small_write_reqs writes,
ROUND((small_read_servicetime + large_read_servicetime)/1000)
read_time_sec,
ROUND((small_write_servicetime + large_write_servicetime)/1000)
write_time_sec,
CASE WHEN small_sync_read_reqs > 0 THEN
ROUND(small_sync_read_latency / small_sync_read_reqs, 2)
END avg_sync_read_ms,
ROUND(( small_read_servicetime+large_read_servicetime
+ small_write_servicetime + large_write_servicetime)
/ 1000, 2) total_io_seconds
FROM iostat_file
ORDER BY 7 DESC
/
Copyright © 2024 · Genesis Framework · WordPress · Log in
