数据库日常巡检文档.docx
《数据库日常巡检文档.docx》由会员分享,可在线阅读,更多相关《数据库日常巡检文档.docx(25页珍藏版)》请在冰豆网上搜索。
![数据库日常巡检文档.docx](https://file1.bdocx.com/fileroot1/2023-1/30/a716db83-5191-46d4-bf67-7ba9958b4fea/a716db83-5191-46d4-bf67-7ba9958b4fea1.gif)
数据库日常巡检文档
日常巡检
OS操作系统
1.查看文件系统使用率df–g(主要看存放数据文件的文件系统和归档的文件系统)
2.查看系统负载情况topas
数据库DB
查看警告日志里面是否包含ORA-600
Bcsp147
sudocat/oracle/admin/cdc/bdump/alert_cdc.log|grepora-600
查看alert日志里面有没有错误信息
sudocat/oracle/admin/cdc/bdump/alert_cdc.log
数据库版本信息
select*fromv$version;
数据库初始化参数
Sql>showparametersspfile
Select*fromv$parameters
查询控制文件
select*fromv$controlfile;
STATUSNAMEBLOCK_SIZE
-------------------------------------------------------
/datafs/cdc/control01.ctl16384
/datafs/cdc/control02.ctl16384
/datafs/cdc/control03.ctl16384
查询redo
select*fromv$log;
GROUP#THREAD#SEQUENCE#BYTESMEMBERSARCHIVEDSTATUSFIRST_CHANGE#
11579893524288001YESINACTIVE51703918649
21579895524288001NOINACTIVE51704352642
31579891524288001YESINACTIVE51703698062
41579894524288001YESCURRENT51704180740
51579889524288001YESCURRENT51703566031
61579890524288001YESCURRENT51703633441
71579892524288001YESCURRENT51703761803
select*fromv$logfile;
GROUP#STATUSTYPEMEMBERIS_RECOVERY_DEST_FILE
3ONLINE/datafs/cdc/redo03.logNO
2ONLINE/datafs/cdc/redo02.logNO
1ONLINE/datafs/cdc/redo01.logNO
4ONLINE/datafs/cdc/redo04.logNO
5ONLINE/datafs/cdc/redo05.logNO
6ONLINE/datafs/cdc/redo06.logNO
7ONLINE/datafs/cdc/redo07.logNO
Undo管理
SQL>showparametersundo
NAMETYPEVALUE
undo_managementstringAUTO
undo_tablespacestringundotbs1
selectstatus,file_id,file_name,tablespace_namets_name,autoextensible,
blocks/128cur_mb,maxblocks/128max_mb
fromdba_data_fileswheretablespace_name=’UNDOTBS1’orderbyfile_name;
查看redo切换频率和归档的切换频率
//redo切换频率
SELECTMAX(first_time)max_first_time,
TO_CHAR(first_time,'yyyy-mm-dd')DAY,
COUNT(recid)count_number,
COUNT(recid)*50size_mb
FROMv$log_history
WHEREthread#=3
GROUPBYTO_CHAR(first_time,'yyyy-mm-dd')
ORDERBY1;
//归档切换频率
SELECTTRUNC(first_time)"Date",TO_CHAR(first_time,'Dy')"Day",
COUNT
(1)"Total",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'00',1,0))"h0",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'01',1,0))"h1",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'02',1,0))"h2",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'03',1,0))"h3",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'04',1,0))"h4",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'05',1,0))"h5",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'06',1,0))"h6",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'07',1,0))"h7",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'08',1,0))"h8",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'09',1,0))"h9",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'10',1,0))"h10",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'11',1,0))"h11",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'12',1,0))"h12",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'13',1,0))"h13",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'14',1,0))"h14",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'15',1,0))"h15",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'16',1,0))"h16",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'17',1,0))"h17",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'18',1,0))"h18",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'19',1,0))"h19",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'20',1,0))"h20",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'21',1,0))"h21",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'22',1,0))"h22",
SUM(DECODE(TO_CHAR(first_time,'hh24'),'23',1,0))"h23",
ROUND(COUNT
(1)/24,2)"Avg"
FROMgv$log_history
WHEREthread#=inst_id
ANDinst_id=3
ANDfirst_time>=sysdate-7
GROUPBYTRUNC(first_time),TO_CHAR(first_time,'Dy')
ORDERBY1;
表空间使用率(包括临时表空间使用率)
--查询表空间使用率包括临时表空间
SELECTD.TABLESPACE_NAME,SPACE"SUM_SPACE(M)",BLOCKSSUM_BLOCKS,SPACE-NVL(FREE_SPACE,0)"USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2)"USED_RATE(%)",FREE_SPACE"FREE_SPACE(M)"
FROM
(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)SPACE,SUM(BLOCKS)BLOCKS
FROMDBA_DATA_FILES
GROUPBYTABLESPACE_NAME)D,
(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)FREE_SPACE
FROMDBA_FREE_SPACE
GROUPBYTABLESPACE_NAME)F
WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME(+)
UNIONALL--ifhavetempfile
SELECTD.TABLESPACE_NAME,SPACE"SUM_SPACE(M)",BLOCKSSUM_BLOCKS,
USED_SPACE"USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2)"USED_RATE(%)",
NVL(FREE_SPACE,0)"FREE_SPACE(M)"
FROM
(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)SPACE,SUM(BLOCKS)BLOCKS
FROMDBA_TEMP_FILES
GROUPBYTABLESPACE_NAME)D,
(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2)USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2)FREE_SPACE
FROMV$TEMP_SPACE_HEADER
GROUPBYTABLESPACE_NAME)F
WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME(+)
数据文件管理
selectdistinctblock_sizefromdba_tablespaces;
8192
setlinesize110pagesize50
colfile_idfor9999999
colfile_namefora48
colts_namefora18
colcur_mbfor99999
colmax_mbfor99999
selectstatus,file_id,file_name,tablespace_namets_name,autoextensible,
blocks/128cur_mb,maxblocks/128max_mb
fromdba_data_filesorderbyfile_name;
STATUSFILE_IDFILE_NAMETS_NAMEAUTOEXTENSIBLECUR_MBMAX_MB
AVAILABLE5/datafs/cdc/csk_base01.dbfCSK_BASENO307200
AVAILABLE68/datafs/cdc/csk_bill_data01.dbfCSK_BILL_DATANO225280
AVAILABLE69/datafs/cdc/csk_bill_data02.dbfCSK_BILL_DATANO225280
临时文件管理
selectstatus,file_id,file_name,tablespace_namets_name,autoextensible,
blocks/128cur_mb,maxblocks/128max_mb
fromdba_temp_filesorderbyfile_name;
查询数据库无效对象
无效对象统计
selectcount(*)fromdba_objectswherestatus='INVALID';
COUNT(*)
168
selectowner,count(*)fromdba_objectswherestatus='INVALID'groupbyowner;
OWNERCOUNT(*)
PUBLIC19
ZHCARD72
DSELL4
EISS52
EISSOS21
查询出用户下的无效对象
selectowner,object_name,replace(object_type,'','')object_type
to_char(created,'yyyy-mm-dd')ascreated
to_char(last_ddl_time,'yyyy-mm-dd')aslast_ddl_time,status
fromdba_objectswherestatus='INVALID'andowner='ZHCARD';
OWNEROBJECT_NAMEOBJECT_TYPE
ZHCARDPRC_BILLRULE_SMSPROCEDURE
ZHCARDPRC_HQT_JKA_YHS_DEALPROCEDURE
ZHCARDPRC_ISSU_ALL_BASE_0050PROCEDURE
ZHCARDPRC_ISSU_ALL_BASE_0033PROCEDURE
行迁移和行链接查询统计
selectowner,table_name,tablespace_name,chain_cntfromdba_tableswherechain_cnt>0;
查询索引深度索引深度越小,对数据库影响较小
selectOWNER||'.'||index_nameas"OWNER.INDEX_NAME",blevelfromdba_indexeswhereblevel>=4orderby2desc;
安全性管理
拥用SYSDBA权限的用户列表:
select*fromv$pwfile_users;
USERNAMESYSDBASYSOPER
SYSTRUETRUE
拥用DBA权限的用户列表:
coladmin_optionfora12
coldefault_rolefora12
select*fromdba_role_privswheregranted_role='DBA';
GRANTEEGRANTED_ROLEADMIN_OPTIONDEFAULT_ROLE
SYSTEMDBAYESYES
ZHCARDDBANOYES
SYSMANDBANOYES
SYSDBAYESYES
比较消耗资源的sql
PhysicalReads
selectb.username用户名,a.disk_reads磁盘读取量,a.executions执行时间,
a.disk_reads/decode(a.executions,0,1,a.executions)单位读取数,a.sql_textSQL语句
fromv$sqlareaa,dba_usersb
wherea.parsing_user_id=b.user_id
anda.disk_reads>10000
orderbydisk_readsdesc;
BufferGets
selectb.username用户名,a.buffer_getsbuffer读取量,a.executions执行时间,
a.buffer_gets/decode(a.executions,0,1,a.executions)单位读取数,a.sql_textSQL语句
fromv$sqlareaa,dba_usersb
wherea.parsing_user_id=b.user_id
anda.buffer_gets>10000
orderbybuffer_getsdesc;
查看某个表空间下有多少表
select*fromall_tableswheretablespace_name='TRAIN';
查看某个表空间下,某个用户有多少表
select*fromall_tableswheretablespace_name='TRAIN'andowner='TRAIN';
数据库已经安装的产品信息
select*fromv$option;
查最近一周每天的归档日志生成量
selectlogtime,
count(*),
round(sum(blocks*block_size)/1024/1024)mbsize
from(selecttrunc(first_time,'dd')aslogtime,a.BLOCKS,a.BLOCK_SIZE
fromv$archived_loga
wherea.DEST_ID=1
anda.FIRST_TIME>trunc(sysdate-7))
groupbylogtime
orderbylogtimedesc;
查当天每小时的各个实例的归档日志生成量
selectTHREAD#,
logtime,
count(*),
round(sum(blocks*block_size)/1024/1024)mbsize
from(selecta.THREAD#,
trunc(first_time,'hh')aslogtime,
a.BLOCKS,
a.BLOCK_SIZE
fromv$archived_loga
wherea.DEST_ID=1
anda.FIRST_TIME>trunc(sysdate))
groupbyTHREAD#,logtime
orderbyTHREAD#,logtimedesc;
查最近一周每天的各个实例的归档日志生成量
Sql代码
selectTHREAD#,
logtime,
count(*),
round(sum(blocks*block_size)/1024/1024)mbsize
from(selectTHREAD#,
trunc(first_time,'dd')aslogtime,
a.BLOCKS,
a.BLOCK_SIZE
fromv$archived_loga
wherea.DEST_ID=1
anda.FIRST_TIME>trunc(sysdate-7))
groupbyTHREAD#,logtime
orderbyTHREAD#,logtimedesc;
无效JOB(BroKen/FAILURES)情况统计
SELECTJOB,WHAT,NEXT_DATE,BROKEN,FAILURES
FROMDBA_JOBS
WHEREBROKEN='Y'
ORFAILURES>0
无效对象情况统计
SELECTOWNER,OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME
FROMDBA_OBJECTS
WHERESTATUS='INVALID'andLAST_DDL_TIME>to_date('20100101','yyyymmdd')
无效索引情况统计
setlinesize300
colindex_nameformata30
colownerformata10
coltable_nameformata30
coltablesapce_nameformata20
selectindex_name,owner,table_name,tablespace_namefromdba_indexeswhereownernotin('SYS','SYSTEM')andstatus!
='VALID';
无效约束情况统计
SELECTowner,
constraint_name,
table_name,
constraint_type,
status
FROMdba_constraints
WHEREstatus='DISABLED'
所有在线实例区情况统计
SELECT
inst_id
instance_numberinst_no
instance_nameinst_name
parallel
status
database_statusdb_status
active_statestate
host_namehost
FROMgv$instance
ORDERBYinst_id
数据库SID,创建时间,日志归档模式
selectname,created,log_modefromv$database;
数据库总数据量情况统计
selectround(sum(space))all_space_Mfrom
(
selectsum(bytes)/1024/1024spacefromdba_data_files
unionall
selectnvl(sum(bytes)/1024/1024,0)spacefromdba_temp_files
unional