文章目录
- 摘要
- 常用脚本
摘要
保持 Oracle 数据库的良好健康状况对于系统的可靠性和性能至关重要。本文将介绍一些常用的 Oracle 日常健康脚本，帮助您监控数据库并及时识别潜在的问题，以保证数据库的稳定运行。
常用脚本
1.查询数据库实例和实例级别的信息
通过 gv
d
a
t
a
b
a
s
e
、
g
v
database、 gv
database、gvinstance
查询数据库实例和实例级别的信息。
获取数据库名称、实例名称、平台名称、主机名称、数据库版本和启动时间等信息。
SELECT a.NAME DBNAME,
b.instance_name,
a.PLATFORM_NAME,
b.host_name,
b.version,
b.startup_time
FROM gV$DATABASE a, gv$instance b
where a.inst_id = b.inst_id
order by 2;
2.查询数据库注册表历史记录的详细信息
包括操作时间、动作和注释。通过按照时间降序排序，可以查看最近执行的注册表更改操作和对应的注释。
SELECT TO_CHAR(ACTION_TIME, 'YYYY-MM-DD HH24:MI:SS') ACTION_TIME,
ACTION,
COMMENTS
FROM SYS.DBA_REGISTRY_HISTORY
ORDER BY 1 DESC;
3.查询从V$LOG_HISTORY表中检索最近10天的日志记录
按照日期和小时统计每个小时的日志数量。查询结果按日期和小时排序。
查询使用了SUBSTR函数来提取日期和小时部分，并通过TO_CHAR函数格式化为’MM/DD/RR HH24:MI:SS’的形式。然后使用DECODE函数根据小时值进行条件判断，如果符合条件则返回1，否则返回0。使用SUM函数对每个小时的返回值进行求和，得到每个小时的日志数量。最后使用COUNT函数统计总日志数量。
SELECT SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH:MI:SS'), 1, 5) DAY,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'00',
1,
0)) H00,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'01',
1,
0)) H01,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'02',
1,
0)) H02,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'03',
1,
0)) H03,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'04',
1,
0)) H04,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'05',
1,
0)) H05,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'06',
1,
0)) H06,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'07',
1,
0)) H07,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'08',
1,
0)) H08,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'09',
1,
0)) H09,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'10',
1,
0)) H10,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'11',
1,
0)) H11,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'12',
1,
0)) H12,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'13',
1,
0)) H13,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'14',
1,
0)) H14,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'15',
1,
0)) H15,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'16',
1,
0)) H16,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'17',
1,
0)) H17,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'18',
1,
0)) H18,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'19',
1,
0)) H19,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'20',
1,
0)) H20,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'21',
1,
0)) H21,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'22',
1,
0)) H22,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'), 10, 2),
'23',
1,
0)) H23,
COUNT(*) TOTAL
FROM V$LOG_HISTORY A
WHERE FIRST_TIME > TRUNC(SYSDATE - 10)
GROUP BY SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH:MI:SS'), 1, 5)
ORDER BY SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH:MI:SS'), 1, 5);
4.检索有关数据库存储和结构的统计信息
TOTAL STORAGES 和对应的总存储大小
INDEX STORAGES 和对应的索引存储大小
TABLESPACES 和对应的表空间数量
FILES 和对应的数据文件数量
TOTAL TABLES 和对应的表数量
TOTAL INDEXES 和对应的索引数量
DB_BLOCK_SIZE 和对应的数据块大小
NLS_CHARACTERSET 和对应的字符集
NLS_NCHAR_CHARACTERSET 和对应的NCHAR字符集
set linesize 100
set pages 200
col ITEM format a30
col TOTAL_SIZE format a30
SELECT 'TOTAL STORAGES' AS ITEM,
ROUND(SUM(BYTES) / 1024 / 1024 / 1024, 2) || 'GB' TOTAL_SIZE
FROM DBA_SEGMENTS
UNION ALL
SELECT 'INDEX STORAGES', ROUND(SUM(BYTES) / 1024 / 1024 / 1024, 2) || 'GB'
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'INDEX'
UNION ALL
SELECT 'TABLESPACES', TO_CHAR(COUNT(*))
FROM V$TABLESPACE
UNION ALL
SELECT 'FILES', TO_CHAR(COUNT(*))
FROM V$DATAFILE
UNION ALL
SELECT 'TOTAL TABLES', TO_CHAR(COUNT(*))
FROM DBA_TABLES
UNION ALL
SELECT 'TOTAL INDEXES', TO_CHAR(COUNT(*))
FROM DBA_INDEXES
UNION ALL
SELECT 'DB_BLOCK_SIZE', VALUE
FROM V$PARAMETER
WHERE TRIM(UPPER(NAME)) = 'DB_BLOCK_SIZE'
UNION ALL
SELECT T1.PARAMETER, T1.VALUE
FROM V$NLS_PARAMETERS T1
WHERE UPPER(TRIM(T1.PARAMETER)) IN
('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');
5.检索ASM磁盘的相关信息
从V$ASM_DISK视图检索ASM磁盘的相关信息
GROUP_NUMBER：ASM磁盘所属的磁盘组编号。
DISK_NUMBER：ASM磁盘的编号。
TOTAL_MB：ASM磁盘的总容量（以MB为单位）。
FREE_MB：ASM磁盘的可用容量（以MB为单位）。
NAME：ASM磁盘的名称。
FAILGROUP：ASM磁盘所属的失效组。
PATH：ASM磁盘的路径。
CREATE_DATE：ASM磁盘的创建日期和时间。
set linesize 400
set pages 300
col NAME format a15
col FAILGROUP format a15
col PATH format a30
SELECT GROUP_NUMBER,
DISK_NUMBER,
TOTAL_MB,
FREE_MB,
NAME,
FAILGROUP,
PATH,
TO_CHAR(CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') CREATE_DATE
FROM V$ASM_DISK A
ORDER BY 1;
6.检索ASM磁盘组的相关信息
GROUP_NUMBER：ASM磁盘组的编号。
NAME：ASM磁盘组的名称。
STATE：ASM磁盘组的状态。可能的值包括：MOUNTED（已挂载）、DISMOUNTED（已卸载）、UNKNOWN（未知）等。
TYPE：ASM磁盘组的类型。例如，NORMAL（普通磁盘组）或HIGH_REDUNDANCY（高冗余磁盘组）。
TOTAL_MB：ASM磁盘组的总容量（以MB为单位）。
FREE_MB：ASM磁盘组的可用容量（以MB为单位）。
这条SQL查询用于从V$ASM_DISKGROUP视图检索ASM磁盘组的相关信息，并按照GROUP_NUMBER和NAME进行排序。
SELECT A.GROUP_NUMBER,A.NAME,A.STATE,A.TYPE, A.TOTAL_MB, A.FREE_MB FROM V$ASM_DISKGROUP A ORDER BY 1, 2
7.查看表空间的相关信息
STATUS：表空间的状态。
NAME：表空间的名称。
TYPE：表空间的类型。
EXTENT_MGT：表空间的数据段管理方式。
SEGMENT_MGT：表空间的段空间管理方式。
TS_SIZE：表空间的总大小（以MB为单位）。
FREE：表空间的可用空间（以MB为单位）。
USED：表空间的已使用空间（以MB为单位）。
PCT_USED：表空间的使用率。
第一部分查询从SYS.DBA_TABLESPACES表、DBA_DATA_FILES表和DBA_FREE_SPACE表中检索非临时表空间的相关信息，并计算出表空间的总大小、可用空间、已使用空间以及使用率。
第二部分查询从SYS.DBA_TABLESPACES表、DBA_TEMP_FILES表和V$TEMP_EXTENT_POOL表中检索临时表空间的相关信息，并计算出表空间的总大小、可用空间、已使用空间以及使用率。
SELECT STATUS,
NAME,
TYPE,
EXTENT_MGT,
SEGMENT_MGT,
TS_SIZE,
FREE,
USED,
PCT_USED
FROM (SELECT DECODE(D.STATUS, 'OFFLINE', D.STATUS, D.STATUS) STATUS,
D.TABLESPACE_NAME NAME,
D.CONTENTS TYPE,
D.EXTENT_MANAGEMENT EXTENT_MGT,
D.SEGMENT_SPACE_MANAGEMENT SEGMENT_MGT,
ROUND(NVL(A.BYTES, 0) / 1024 / 1024, 0) TS_SIZE,
ROUND(NVL(F.BYTES, 0) / 1024 / 1024, 0) FREE,
ROUND(NVL(A.BYTES - NVL(F.BYTES, 0), 0) / 1024 / 1024, 0) USED,
DECODE((1 -
SIGN(1 - SIGN(TRUNC(NVL((A.BYTES - NVL(F.BYTES, 0)) /
A.BYTES * 100,
0)) - 90))),
1,
TO_CHAR(TRUNC(NVL((A.BYTES - NVL(F.BYTES, 0)) / A.BYTES * 100,
0))),
TO_CHAR(TRUNC(NVL((A.BYTES - NVL(F.BYTES, 0)) / A.BYTES * 100,
0)))) || '%%' PCT_USED
FROM SYS.DBA_TABLESPACES D,
(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+)
AND D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
AND NOT (D.EXTENT_MANAGEMENT LIKE 'LOCAL' AND
D.CONTENTS LIKE 'TEMPORARY')
ORDER BY 2)
UNION ALL
SELECT DECODE(D.STATUS, 'OFFLINE', D.STATUS, D.STATUS) STATUS,
D.TABLESPACE_NAME,
D.CONTENTS TYPE,
D.EXTENT_MANAGEMENT EXTENT_MGT,
D.SEGMENT_SPACE_MANAGEMENT SEGMENT_MGT,
ROUND(NVL(A.BYTES, 0) / 1024 / 1024, 2) TS_SIZE,
ROUND(NVL(A.BYTES - NVL(T.BYTES, 0), 0) / 1024 / 1024, 2) FREE,
ROUND(NVL(T.BYTES, 0) / 1024 / 1024, 2) USED,
DECODE((1 -
SIGN(1 - SIGN(TRUNC(NVL(T.BYTES / A.BYTES * 100, 0)) - 90))),
1,
TO_CHAR(TRUNC(NVL(T.BYTES / A.BYTES * 100, 0))),
TO_CHAR(TRUNC(NVL(T.BYTES / A.BYTES * 100, 0)))) PCT_USED
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) 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'
ORDER BY 2;
8.查询RMAN备份作业的相关信息
包括备份名称（BACKUP_NAME）
开始时间（START_TIME）
耗时（ELAPSED_TIME）
状态（STATUS）
输入类型（INPUT_TYPE）
输出设备类型（OUTPUT_DEVICE_TYPE）
输入大小（INPUT_SIZE）
输出大小（OUTPUT_SIZE）
输出速率（OUTPUT_RATE_PER_SEC）
SELECT R.COMMAND_ID BACKUP_NAME,
TO_CHAR(R.START_TIME, 'MM/DD/YYYY HH24:MI:SS') START_TIME,
R.TIME_TAKEN_DISPLAY ELAPSED_TIME,
DECODE(R.STATUS,
'COMPLETED',
R.STATUS,
'RUNNING',
R.STATUS,
'FAILED',
R.STATUS,
R.STATUS) STATUS,
R.INPUT_TYPE,
R.OUTPUT_DEVICE_TYPE,
R.INPUT_BYTES_DISPLAY INPUT_SIZE,
R.OUTPUT_BYTES_DISPLAY OUTPUT_SIZE,
R.OUTPUT_BYTES_PER_SEC_DISPLAY OUTPUT_RATE_PER_SEC
FROM (SELECT COMMAND_ID,
START_TIME,
TIME_TAKEN_DISPLAY,
STATUS,
INPUT_TYPE,
OUTPUT_DEVICE_TYPE,
INPUT_BYTES_DISPLAY,
OUTPUT_BYTES_DISPLAY,
OUTPUT_BYTES_PER_SEC_DISPLAY
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY START_TIME DESC) R
WHERE ROWNUM < 30;
9.查看备份集信息(控制文件)
从V$BACKUP_SET，V$BACKUP_PIECE视图查询
BS_KEY：备份集标识(BS.RECID)
PIECE：备份片号（BP.PIECE#）
COPY： 备份片拷贝号（BP.COPY#）
BP_KEY：备份片标识(BP.RECID)
CONTROLFILE_INCLUDED：控制文件是否包含在备份集中的标志列，如果是’NO’则显示为’-'，否则显示原值。
STATUS：备份片的状态，将’A’转换为’AVAILABL’，‘D’转换为’DELETED’，‘X’转换为’EXPIRED’。AVAILAB代表可用状态
HANDLE：备份片的句柄信息。格式化为最大长度为25的字符串。
该查询筛选出满足以下条件的备份集和备份片：
备份片的状态为’A’或’X’。
备份集中包含控制文件。
备份集的完成时间在当前时间的前一天以后。
set linesize 500
set pages 500
column BS_KEY format 999999
column PIECE format 99
column COPY format 99
column BS_KEY format 999999
column CONTROLFILE_INCLUDED format a15
column STATUS format a20
column HANDLE format a25
SELECT BS.RECID BS_KEY,
BP.PIECE# PIECE,
BP.COPY# COPY,
BP.RECID BP_KEY,
DECODE(BS.CONTROLFILE_INCLUDED, 'NO', '-', BS.CONTROLFILE_INCLUDED) CONTROLFILE_INCLUDED,
DECODE(STATUS, 'A', 'AVAILABL', 'D', 'DELETED', 'X', 'EXPIRED') STATUS,
HANDLE HANDLE
FROM V$BACKUP_SET BS, V$BACKUP_PIECE BP
WHERE BS.SET_STAMP = BP.SET_STAMP
AND BS.SET_COUNT = BP.SET_COUNT
AND BP.STATUS <