一個簡單資料庫健康檢查指令碼
NLS_DATE_FORMAT="DD-MON-RRRR HH24:MI:SS"
export NLS_DATE_FORMAT
rman target /
spool log to rman_report.log
set echo on
show all;
report schema;
list incarnation;
list backup summary;
list backup;
list copy;
report need backup;
report obsolete;
restore database preview;
spool log off
sqlplus / as sysdba
spool results01.txt
set echo on feedback on time on timing on pagesize 100 linesize 200 numwidth 13
show user
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select * from v$version;
select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') as current_date from dual;
column name format a30
column value format a49
select name, value from v$parameter where isdefault='FALSE' order by 1;
column parameter format a30
column value format a49
select * from v$nls_parameters order by parameter;
column name format a10
select dbid, name,
to_char(created, 'DD-MON-YYYY HH24:MI:SS') created,
open_mode, log_mode,
to_char(checkpoint_change#, '999999999999999') as checkpoint_change#,
controlfile_type,
to_char(controlfile_change#, '999999999999999') as controlfile_change#,
to_char(controlfile_time, 'DD-MON-YYYY HH24:MI:SS') controlfile_time,
to_char(resetlogs_change#, '999999999999999') as resetlogs_change#,
to_char(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') resetlogs_time
from v$database;
select * from v$instance;
archive log list;
select * from v$thread order by thread#;
select * from v$log order by first_change#;
column member format a45
select * from v$logfile;
column name format a79
select '#' || ts.name || '#' as tablespace_name, ts.ts#,
'#' || df.name || '#' as filename, df.file#, df.status, df.enabled, df.creation_change#,
to_char(df.creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
to_char(df.checkpoint_change#, '999999999999999') as checkpoint_change#,
to_char(df.checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
to_char(df.offline_change#, '999999999999999') as offline_change#,
to_char(df.online_change#, '999999999999999') as online_change#,
to_char(df.online_time, 'DD-MON-YYYY HH24:MI:SS') as online_time,
to_char(df.unrecoverable_change#, '999999999999999') as online_change#,
to_char(df.unrecoverable_time, 'DD-MON-YYYY HH24:MI:SS') as online_time,
to_char(df.bytes, '9,999,999,999,990') as bytes, block_size
from v$datafile df, v$tablespace ts
where ts.ts# = df.ts#
and ( df.status <> 'ONLINE'
or df.checkpoint_change# <> (select checkpoint_change# from v$database) );
select '#' || ts.name || '#' as tablespace_name, ts.ts#,
'#' || dh.name || '#' as filename, dh.file#, dh.status, dh.error, dh.
fuzzy, dh.creation_change#,
to_char(dh.creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
to_char(dh.checkpoint_change#, '999999999999999') as checkpoint_change#,
to_char(dh.checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
to_char(dh.resetlogs_change#, '999999999999999') as resetlogs_change#,
to_char(dh.resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') as resetlogs_time,
to_char(dh.bytes, '9,999,999,999,990') as bytes
from v$datafile_header dh, v$tablespace ts
where ts.ts# = dh.ts#
and ( dh.status <> 'ONLINE'
or dh.checkpoint_change# <> (select checkpoint_change# from v$database) );
select * from v$tempfile;
select HXFIL File_num,substr(HXFNM,1,60) file_name, FHTNM tablespace_name,
FHTYP type, HXERR validity,
FHSCN SCN, FHTIM SCN_Time, FHSTA status,
FHTHR Thread, FHRBA_SEQ Sequence
from X$KCVFH
--where HXERR > 0
order by HXERR, FHSTA, FHSCN, HXFIL;
column error format a15
select error, fuzzy, status, checkpoint_change#,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
from v$datafile_header
group by error, fuzzy, status, checkpoint_change#, checkpoint_time
order by checkpoint_change#, checkpoint_time;
select * from V$INSTANCE_RECOVERY;
select * from v$recover_file order by change#;
select * from dba_tablespaces where status <> 'ONLINE';
SELECT * FROM database_properties order by property_name;
select *
from X$KCCLH, (select min(checkpoint_change#) df_min_scn,
min(checkpoint_change#) df_max_scn
from v$datafile_header
where status='ONLINE') df
where LHLOS in (select first_change# from v$log)
or df.df_min_scn between LHLOS and LHNXS
or df.df_max_scn between LHLOS and LHNXS;
select * from v$backup where status <> 'NOT ACTIVE';
select ADDR, XIDUSN, XIDSLOT, XIDSQN,
UBAFIL, UBABLK, UBASQN,
START_UBAFIL, START_UBABLK, START_UBASQN,
USED_UBLK, STATUS
from v$transaction;
select * from v$archive_gap;
select * from v$archive_dest_status where recovery_mode <> 'IDLE';
column USED_GB format 999,990.999
column USED% format 990.99
column RECLAIM_GB format 999,990.999
column RECLAIMABLE% format 990.99
column LIMIT_GB format 999,990.999
select frau.file_type as type,
frau.percent_space_used/100 * rfd.space_limit /1024/1024/1024 "USED_GB",
frau.percent_space_used "USED%",
frau.percent_space_reclaimable "RECLAIMABLE%",
frau.percent_space_reclaimable/100 * rfd.space_limit /1024/1024/1024 "RECLAIM_GB",
frau.number_of_files "FILES#"
from v$flash_recovery_area_usage frau,
v$recovery_file_dest rfd
order by file_type;
select name,
space_limit/1024/1024/1024 "LIMIT_GB",
space_used/1024/1024/1024 "USED_GB",
space_used/space_limit*100 "USED%",
space_reclaimable/1024/1024/1024 "RECLAIM_GB",
number_of_files "FILE#"
from v$recovery_file_dest;
select * from v$backup_corruption;
select * from v$copy_corruption order by file#, block#;
select * from v$database_block_corruption order by file#, block#;
SELECT f.file#, f.name,
e.tablespace_name, e.segment_type, e.owner, e.segment_name,
c.file#, c.block#, c.blocks, c.corruption_change#, c.corruption_type
FROM dba_extents e, V$database_block_corruption c, v$datafile f
WHERE c.file# = f.file#
and e.file_id = c.file#
and c.block# between e.block_id AND e.block_id + e.blocks - 1;
select * from v$database_incarnation;
select * from v$rman_configuration;
select s.recid as bs_key, p.recid as bp_key, p.status, p.tag, p.device_type,
p.handle, p.media, p.completion_time, p.bytes
from v$backup_piece p, v$backup_set s
where p.set_stamp = s.set_stamp
and s.controlfile_included='YES'
order by p.completion_time;
select s.recid as bs_key, p.recid as bp_key, p.status, p.tag, p.device_type,
p.handle, p.media, p.completion_time, f.absolute_fuzzy_change#, p.bytes
from v$backup_datafile f, v$backup_piece p, v$backup_set s
where p.set_stamp = s.set_stamp
and f.set_stamp = s.set_stamp
and p.handle is not null
and f.file# = 1
order by p.completion_time;
SELECT
session_recid,
input_bytes_per_sec_display,
output_bytes_per_sec_display,
time_taken_display,
end_time
FROM v$rman_backup_job_details
ORDER BY end_time;
select * from v$filestat;
column EBS_MB format 9,990.99
column TOTAL_MB format 999,990.99
select SID, SERIAL, FILENAME, EFFECTIVE_BYTES_PER_SECOND/1024/1024 as EBS_MB,
OPEN_TIME, CLOSE_TIME, ELAPSED_TIME, TOTAL_BYTES/1024/1024 as TOTAL_MB,
STATUS, MAXOPENFILES, buffer_size, buffer_count
from v$backup_async_io
where close_time >= sysdate-3
order by close_time;
select SID, SERIAL, FILENAME, EFFECTIVE_BYTES_PER_SECOND/1024/1024 as EBS_MB,
OPEN_TIME, CLOSE_TIME, ELAPSED_TIME, TOTAL_BYTES/1024/1024 as TOTAL_MB,
STATUS, MAXOPENFILES, buffer_size, buffer_count
from v$backup_sync_io
where close_time >= sysdate-3;
select * from v$controlfile_record_section order by type;
select to_char(rownum) || '. ' || output rman_output from v$rman_output;
select * from v$rman_status where trunc(end_time) > trunc(sysdate)-3;
select protection_mode, protection_level from v$database;
select * from v$recovery_progress;
select s.client_info,
sl.message,
sl.sid, sl.serial#, p.spid,
round(sl.sofar/sl.totalwork*100,2) "% Complete"
from v$session_longops sl, v$session s, v$process p
where p.addr = s.paddr
and sl.sid=s.sid
and sl.serial#=s.serial#
and opname LIKE 'RMAN%'
and opname NOT LIKE '%aggregate%'
and totalwork != 0
and sofar <> totalwork;
select AL.*,
DF.min_checkpoint_change#, DF.min_checkpoint_time
from v$archived_log AL,
(select min(checkpoint_change#) min_checkpoint_change#,
min(checkpoint_time) min_checkpoint_time
from v$datafile_header
where status='ONLINE') DF
where DF.min_checkpoint_change# between AL.first_change# and AL.next_change#
order by AL.first_change#;
select * from v$asm_diskgroup;
select * from v$asm_disk;
select * from v$flashback_database_log;
select * from v$flashback_database_logfile order by first_change# desc;
select * from v$flashback_database_stat order by begin_time desc;
select * from v$restore_point;
select * from v$rollname;
select * from v$undostat;
select * from dba_rollback_segs;
select a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
round(maxbytes / 1048576) Max
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
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
union all
select h.tablespace_name,
round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
1048576) megs_free,
round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
100 -
round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
round(sum(f.maxbytes) / 1048576) max
from sys.v_$TEMP_SPACE_HEADER h,
sys.v_$Temp_extent_pool p,
dba_temp_files f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
group by h.tablespace_name
ORDER BY 1
/
SELECT d.tablespace_name "Name",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999') "HWM (M)",
TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,
TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY'
/
spool off
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/12798004/viewspace-1390521/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle 資料庫效能健康檢查指令碼Oracle資料庫指令碼
- (轉)oracle 資料庫效能健康檢查指令碼Oracle資料庫指令碼
- 資料庫健康檢查 sqlplus 指令碼資料庫SQL指令碼
- oracle 資料庫效能健康檢查指令碼[轉帖]Oracle資料庫指令碼
- check_postgres指令碼集檢查資料庫健康情況指令碼資料庫
- 資料庫健康檢查(轉)資料庫
- 分享一個簡單的 laravel 應用健康檢查命令Laravel
- ORAchk-資料庫健康檢查資料庫
- MySQL資料庫健康檢查--MySQL巡檢MySql資料庫
- 資料庫的常規檢查指令碼資料庫指令碼
- 巧用shell生成資料庫檢查指令碼資料庫指令碼
- 資料庫的檢查步驟指令碼資料庫指令碼
- 漫談Oracle資料庫健康檢查Oracle資料庫
- Oracle資料庫健康檢查常用SQLOracle資料庫SQL
- MSSQL資料庫健康檢查--SQL Server巡檢SQL資料庫Server
- oracle資料庫健康檢查報告模板Oracle資料庫
- 資料庫日常健康檢查方式方法資料庫
- 簡單的備份資料庫指令碼資料庫指令碼
- 簡單資料庫及表建立shell指令碼資料庫指令碼
- Mysql資料庫的簡單備份指令碼MySql資料庫指令碼
- [20170515]檢查資料庫scn指令碼.txt資料庫指令碼
- oracle 資料庫中壞塊概念和檢查指令碼Oracle資料庫指令碼
- 每天檢查正式Server的一個指令碼Server指令碼
- 醬油DBA奉獻ORACLE資料庫監控健康指令碼(一)Oracle資料庫指令碼
- DB健康檢查使用RDA收集資料
- 一個命令,生成資料庫物件的指令碼資料庫物件指令碼
- 最簡單的一個powershell的指令碼指令碼
- 檢查資料庫資料欄位命名規範和合法性的指令碼資料庫指令碼
- 作資料庫健康檢查常用到的一些unix相關的命令!資料庫
- 自動檢查RAID 資訊的一個指令碼AI指令碼
- Oracle 資料庫巡檢指令碼 單例項 RAC 輸出HTML格式Oracle資料庫指令碼單例HTML
- 資料庫監控指令碼(一)資料庫指令碼
- 一個簡單的Gradle指令碼檔案Gradle指令碼
- 自己實現一個最簡單的資料庫資料庫
- RAC指令碼檢查指令碼
- Linux 系統健康巡檢指令碼Linux指令碼
- ORACLE提供檢驗RAC是否健康指令碼Oracle指令碼
- 一個檢視Access資料庫密碼的工具資料庫密碼