操作系统检查项1.1 检查透明大页是否关闭grep -i huge /proc/meminfocat /sys/kernel/mm/transparent_hugepage/enabled1.2 系统空间使用率df -h1.3 局部负载ps -ef |grep dmstop -Hp 对应DMPID1.4 内存负载vmstat 1 11根据实际情况修改秒数1.5 磁盘负载sar -d 1 11根据实际情况修改秒数数据库检查项2.1 数据库服务日志检查数据库服务日志记录了数据库服务启动刷检查点写归档、刷盘等一系列实例的运行过程按月生成一般默认命名为 dm_实例名称_月份.log。针对该日志的分析可了解以上过程的运行状态通过检查点刷盘也可以分析业务的繁忙程度作为调整内存、检查点参数的依据。通过 grep 筛选检查具有 ERROR 或者 FATAL 关键字的日志tail -500f dm_DMSERVER_XXX.log[/dm8/log]cat XXX.log | grep ERROR[/dm8/log]cat XXX.log | grep FATALDMAP 进程日志dmap 日志是记录 DMAP 插件的运情况的日志DMAP 日志的位置和数据库运行日志的位置相同命名方式为 dm_DMAP_日期.log如下图所示数据库备份日志数据库备份日志是记录了是数据库备份情况的日志主要用于记录数据库备份的过程信息判断备份是否完成的日志日志存放的位置和数据库运行日志相同命名格式为 dm_BAKES_日期.log如下图所示数据守护集群日志检查数据守护集群由至少两台数据库服务器组成相比单实例数据库多出了守护进程日志需要检查并且集群中的每一个节点的数据库日志守护进程日志都要检查。数据库守护进程日志数据守护进程日志主要记录守护进程对数据库进程运行情况的监控信息和守护集群之间的内部通讯信息守护进程日志的命名方式为 dm_watcher_日期.log2.2 数据库状态收集数据库基本信息收集select ‘版本’ as 名称,SVR_VERSION as 信息 from v$instance union allselect ‘数据库名’ as 名称,CUR_DATABASE() union allselect ‘实例名’,INSTANCE_NAME from v$instance union allselect ‘永久魔术值’,to_char(PERMANENT_MAGIC()) union allselect ‘簇大小’,SF_GET_EXTENT_SIZE ()||‘页’ union allselect ‘页大小’,PAGE()/1024||‘K’ union allselect ‘字符集’,DECODE(UNICODE,‘0’,‘GB18030’,‘1’,‘UTF-8’,‘2’,‘EUC-KR’) union allselect ‘大小写’,DECODE(SF_GET_CASE_SENSITIVE_FLAG,‘0’,‘不敏感’,‘1’,‘敏感’) union allselect ‘空格填充’,DECODE(BLANK_PAD_MODE,‘0’,‘否’,‘1’,‘是’) union allselect ‘日志大小’,MAX(RLOG_SIZE/1024/1024)||‘MB’ from v$rlogfile union allselect ‘归档状态’,DECODE(ARCH_MODE,‘Y’,‘启用’,‘N’,‘未启用’) from v$database union allselect ‘创建时间’, cast(CREATE_TIME as varchar(50)) from v$database;实例基本信息SELECT B.SVR_VERSION AS “数据库版本”, C.DB_MAGIC AS “数据库MAGIC”,A.NAME AS “数据库名”,B.INSTANCE_NAME AS “实例名”,B.STATUS$ AS “系统状态”,B.MODE$ AS “模式”,DECODE(A.ARCH_MODE,‘Y’,‘是’,‘N’,‘否’) AS “是否归档”,B.START_TIME AS “服务启动时间” FROM V$DATABASE A,V$INSTANCE B,V$RLOG C;查询LICENSE到期时间SELECT SERIES_NO AS “序列号”,CHECK_CODE AS “校验码”,DECODE(SERVER_SERIES,‘P’,‘个人版’,‘S’,‘标准版’,‘E’,‘企业版’,‘A’,‘安全版’,‘D’,‘开发版’) as “系列名称”,DECODE(SERVER_TYPE,‘1’,‘正式版’,‘2’,‘测试版’,‘3’,‘试用版’) as “版本类型”,EXPIRED_DATE as “到期时间”,AUTHORIZED_CUSTOMER AS “最终用户”,PROJECT_NAME AS “项目名称”,OS_TYPE AS “授权操作系统”,CPU_TYPE AS “授权CPU类型”,MAX_CPU_NUM AS “授权CPU个数”,DECODE(CLUSTER_TYPE,‘0000’,‘无’,‘0001’,‘DSC’,‘0010’,‘RWC’,‘0011’,‘RWC、DSC’,‘0100’,‘MPP’,‘0101’,‘MPP、DSC’,‘0110’,‘MPP、RWC’,‘0111’,‘MPP、RWC、DSC’,‘1000’,‘DW’,‘1001’,‘DW、DSC’,‘1010’,‘DW、RWC’,‘1011’,‘DW、RWC、DSC’,‘1100’,‘DW、MPP’,‘1101’,‘DW、MPP、DSC’,‘1110’,‘DW、MPP、RWC’,‘1111’,‘DW、MPP、RWC、DSC’) as “授权集群”,EXPIRED_DATE AS “有效期”,CONCURRENCY_USER_NUMBER AS “并发数” FROM V$LICENSE;所有参数信息SELECT PARA_NAME , PARA_VALUE ,MIN_VALUE , MAX_VALUE , MPP_CHK ,SESS_VALUE , FILE_VALUE, DESCRIPTION , PARA_TYPEFROM V$DM_INI查询REDO信息SELECT CLIENT_PATH AS “日志名”,PATH AS “路径”,(RLOG_SIZE/1024/1024) AS “文件大小M”,CREATE_TIME AS “创建时间” FROM V$RLOGFILE;归档日志检查SELECT CUR_LSN AS “当前LSN”,FILE_LSN AS “已经刷到盘上的LSN”,FLUSHING_PAGES AS “正在刷盘总页数”,(TOTAL_SPACE/1024/1024) AS “归档日志总空间M”,(FREE_SPACE/1024/1024) AS “归档日志剩余空间M” FROM V$RLOG;查询表空间信息DECLAREK INT:(SELECT cast(PAGE()/1024 as varchar));BEGINSELECTF.“PATH” 数据文件 ,F.CLIENT_PATH,G.NAME 所属表空间,F.TOTAL_SIZE*K/1024 ||‘M’ 文件大小 ,(CASE F.AUTO_EXTEND WHEN ‘1’ THEN ‘是’ ELSE ‘否’ END) 自动扩容,(CASE F.NEXT_SIZE WHEN ‘0’ THEN ‘默认值’ ELSE F.NEXT_SIZE||‘M’ END) 下次扩容值,F.MAX_SIZE||‘M’ 文件最大值FROM SYS.“V$DATAFILE” F LEFT JOIN SYS.V$TABLESPACE G ON F.GROUP_IDG.ID;END;/数据文件信息SELECT PATH as 文件路径,(TOTAL_SIZEPAGE/1024/1024)as 文件大小MB,(FREE_SIZEPAGE/1024/1024)as 剩余大小MB,(CAST((TOTAL_SIZE-FREE_SIZE)*100/TOTAL_SIZE AS NUMERIC(2,0))||‘%’) as 使用比例,DECODE(AUTO_EXTEND,‘0’,‘关闭’,‘1’,‘打开’) as 自动扩展,NEXT_SIZE as 扩充尺寸MB,MAX_SIZE as 扩充上限MB,CREATE_TIME as 创建时间,MODIFY_TIME as 修改时间 FROM V$DATAFILE;ASM检查SELECT GROUP_ID , GROUP_NAME ,N_DISK , AU_SIZE , EXTENT_SIZE,TOTAL_SIZE , FREE_SIZE ,TOTAL_FILE_NUM FROM V$ASMGROUP;SELECT GROUP_ID , DISK_ID ,DISK_NAME , DISK_PATH , SIZE ,FREE_AUNO , CREATE_TIME , MODIFY_TIMEFROM V$ASMDISK;SELECT FILE_ID , TYPE , PATH ,SIZE_BYTES , SIZE_TOTAL , CREATE_TIME , MODIFY_TIME,GROUP_ID , DISK_ID ,DISK_AUNO , AU_OFFSETFROM V$ASMFILE;会话统计信息SELECT STATE AS 状态,CLNT_IP AS 连接IP,COUNT(*)AS 数量 FROM V$SESSIONS GROUP BY STATE,CLNT_IP ORDER BY STATE;当前会话信息SELECT SESS_ID AS “会话ID”,DECODE(STATE,‘CREATE’,‘创建’,‘STARTUP’,‘启动’,‘IDLE’,‘空闲’,‘ACTIVE’,‘活动’,‘WAIT’,‘等待’,‘UNKNOWN’,‘未知’) AS “会话状态”,CREATE_TIME AS “会话创建时间”,TRX_ID AS “事务ID”,SQL_TEXT AS “SQL”,USER_NAME AS “当前用户”,CURR_SCH AS “当前模式”,CLNT_TYPE AS “连接类型”,DECODE(AUTO_CMT,‘Y’,‘是’,‘N’,‘否’) AS “是否自动提交”,DECODE(DDL_AUTOCMT,‘Y’,‘是’,‘N’,‘否’) AS “DDL是否自动提交”,APPNAME AS “连接程序名”,CLNT_IP AS “客户机IP”,CLNT_HOST AS “客户机名”,OSNAME AS “客户机系统” FROM V$SESSIONS;等待事件历史SELECT THREAD_ID AS “线程ID”,TRX_ID AS “事务ID”,WAIT_CLASS AS “等待类型号”,WAIT_OBJECT AS “等待对象”,WAIT_START AS “等待开始时间”,TRUNC(WAIT_TIME,6)/1000000 AS “等待时间” FROM V$WAIT_HISTORY;查询死锁历史SELECT SESS_ID AS “会话ID”,TRX_ID AS “事务ID”,SQL_TEXT AS “产生死锁的SQL”,HAPPEN_TIME AS “死锁发生时间” FROM V$DEADLOCK_HISTORY;锁情况汇总SELECT STATTIME , WT_TRXID ,BLK_TRXID , BLOCKED , WT_TABLE , WT_SESS , BLK_SESS ,WT_USER_NAME , BLK_USER_NAME ,SQL_TEXT , CLNT_IP , SSFROM (WITH TRX_TAB AS(SELECT O1.NAME,L1.TRX_IDFROM V$LOCK L1,SYSOBJECTS O1WHERE L1.TABLE_ID O1.IDAND O1.ID 0),TRX_SESS AS(SELECT L.TRX_ID WT_TRXID,L.ROW_IDX BLK_TRXID,L.BLOCKED,(SELECT NAME TABLE_NAME FROM TRX_TAB A WHERE A.TRX_ID L.TRX_ID) WT_TABLE,S1.SESS_ID WT_SESS,S2.SESS_ID BLK_SESS,S1.USER_NAME WT_USER_NAME,S2.USER_NAME BLK_USER_NAME,S1.SQL_TEXT,S1.CLNT_IP,DATEDIFF(SS, S1.LAST_SEND_TIME, SYSDATE) SSFROM V$LOCK L,V$SESSIONS S1,V$SESSIONS S2WHERE L.TRX_ID S1.TRX_IDAND L.ROW_IDX S2.TRX_ID)SELECT SYSDATE STATTIME,*FROM TRX_SESSWHERE BLOCKED 1)阻塞情况SELECT WT_SQL , CLNT_IP , SS , WT_TRXID , BLK_TRXIDFROM (WITH LOCKSAS (SELECT O.NAME,L.*,S.SESS_ID,S.SQL_TEXT,S.CLNT_IP,S.LAST_SEND_TIMEFROM V$LOCK L, SYSOBJECTS O, V$SESSIONS SWHERE L.TABLE_ID O.ID AND L.TRX_ID S.TRX_ID),LOCK_TRAS (SELECT TRX_ID WT_TRXID, TID BLK_TRXIDFROM LOCKSWHERE BLOCKED 1),RESAS (SELECT SYSDATE STATTIME,T1.NAME,T1.SESS_ID WT_SESSID,S.WT_TRXID,T2.SESS_ID BLK_SESSID,S.BLK_TRXID,T2.CLNT_IP,SF_GET_SESSION_SQL (T1.SESS_ID) FULSQL,DATEDIFF (SS, T1.LAST_SEND_TIME, SYSDATE) SS,T1.SQL_TEXT WT_SQLFROM LOCK_TR S, LOCKS T1, LOCKS T2WHERE T1.LTYPE ‘OBJECT’AND T1.TABLE_ID 0AND T2.LTYPE ‘OBJECT’AND T2.TABLE_ID 0AND S.WT_TRXID T1.TRX_IDAND S.BLK_TRXID T2.TRX_ID)SELECT DISTINCT WT_SQL,CLNT_IP,SS,WT_TRXID,BLK_TRXIDFROM RES)历史故障SELECT SESS_ID AS “会话ID”,TRX_ID AS “事务ID”,DECODE(SU_FLAG,‘U’,‘用户异常’,‘S’,‘系统异常’,‘P’,‘语法异常’) AS “错误类型”,SQL_TEXT AS “错误的SQL”,ECPT_CODE AS “错误提示”,ERR_TIME AS “产生时间” FROM V$RUNTIME_ERR_HISTORY;查询备份集select sf_bakset_backup_dir_add(‘DISK’,‘/home/dmdba/backup’);按实际备份目录为准SELECT BACKUP_PATH 备份目录,OBJECT_NAME 备份类型,BACKUP_TIME 备份时间 FROM V$BACKUPSET;备份具体信息查询SELECT DEVICE_TYPE , BACKUP_ID ,PARENT_ID , BACKUP_NAME ,BACKUP_PATH , decode(TYPE,0,‘基备份’,1,‘增量备份’,2,‘表备份’,3,‘归档备份’) , decode(LEVEL,0,‘联机备份’,1,‘脱机备份’) , decode(RANGE#,1,‘库备份’,2,‘表空间备份’,3,‘表级备份’,4,‘归档备份’) , OBJECT_NAME ,OBJECT_ID , BASE_NAME , BACKUP_TIME ,DESC# , ENCRYPT_TYPE ,COMPRESS_LEVEL , WITHOUT_LOG ,USE_PWR , PKG_SIZE , BEGIN_LSN,END_LSN , BKP_NUM ,DBF_NUM , PARALLEL_NUM , decode(BAKSET_TYPE,0,‘普通备份集NORMAL’,1,‘DDL_CLONE备份集DDL_CLONE’,2,‘联机拷贝备份集COPY’),MPP_FLAG , MIN_TRX_START_LSN , MIN_EXEC_VER , CUMULATIVE,MIN_DCT_VER , DDL_CLONE,是否有效FROM (select top 50 *,SF_BAKSET_CHECK(DEVICE_TYPE,BACKUP_PATH) 是否有效 from V$BACKUPSET order by BACKUP_TIME desc );查询用户信息SELECT USER_ID AS “用户ID”,USERNAME AS “用户名”,ACCOUNT_STATUS AS “状态”,DEFAULT_TABLESPACE AS “默认表空间”,CREATED AS “创建时间”,LOCK_DATE AS “锁定开始时间”,EXPIRY_DATE AS “密码有效期截止” FROM DBA_USERS;对象汇总SELECT DECODE(OBJECT_TYPE,‘SCH’,‘模式’,‘TABLE’,‘表’,‘VIEW’,‘视图’,‘MATERIALIZED VIEW’,‘物化视图’,‘PROCEDURE’,‘存储过程’,‘FUNCTION’,‘函数’,‘SEQUENCE’,‘序列’,‘TRIGGER’,‘触发器’,‘PACKAGE’,‘包’,‘PACKAGE BODY’,‘包主体’,‘CLASS’,‘类’,‘SYNONYM’,‘同义词’,‘DOMAIN’,‘域’,‘TYPE’,‘自定义类型’,‘INDEX’,‘索引’,‘CONSTRAINT’,‘约束’,‘SEQUENCE’,‘序列’,‘TABLE PARTITION’,‘分区表’) AS 对象名称,OWNER AS 所属用户,COUNT(*)AS 数量 FROM DBA_OBJECTS WHERE OWNER NOT IN (‘SYS’,‘SYSTEM’,‘SYSAUDITOR’,‘SYSJOB’,‘SYSSSO’,‘CTISYS’) GROUP BY OBJECT_TYPE,OWNER ORDER BY OWNER ASC,OBJECT_TYPE DESC;自增列信息SELECT owner , table_name ,COL_NAMEFROM (select b.owner,b.table_name,a.name COL_NAMEfrom SYS.SYSCOLUMNS a,dba_tables b ,sys.sysobjects cwhere a.INFO21and a.id c.idand c.name b.table_name)TOP 10 数据量表统计–CALL SP_CREATE_SYSTEM_PACKAGES(1); --创建系统包–CALL SP_DB_STAT_INIT (); --对库上所有表及索引 生成统计信息select * from (SELECT TABLE_NAME AS 表名,OWNER AS 所属用户,TABLESPACE_NAME AS 所属表空间,NUM_ROWS AS 行数 FROM DBA_TABLES WHERE OWNER NOT IN (‘SYS’,‘SYSTEM’,‘SYSAUDITOR’,‘SYSJOB’,‘SYSSSO’,‘CTISYS’) AND TABLESPACE_NAME ! ‘TEMP’ order by 4 desc) where rownum10 ;SELECT owner, segment_name,tablespace_name , size_mb , num_rowsFROM (SELECT top 10 d.owner,d.segment_name,d.tablespace_name,SUM(d.bytes) / 1024 / 1024 size_mb,(select num_rows from dba_tables nb where table_named.segment_name and nb.ownerd.owner) num_rowsFROM dba_segments dGROUP BY d.owner,d.segment_name,d.tablespace_nameORDER BY size_mb DESC)TOP 20 慢SQLSELECT SEQ_NO, SQL_ID,SESS_ID, SESS_SEQ, TRX_IDTHREAD_ID,TOP_SQL_TEXT,SEC_SQL_TEXT, THRD_SQL_TEXT,START_TIME, TIME_USED,IS_OVER, EXEC_ID, VM,STKFRM, STK_LEVEL,BYTES_DYNAMIC_ALLOCED, BYTES_DYNAMIC_FREED,CUR_SQL_NODE, MAL_ID,N_LOGIC_READ, N_PHY_READ,AFFECTED_ROWS, HARD_PARSE_FLAG,MPP_EXEC_IDFROM (SELECT TOP 20 * FROM V$SQL_HISTORY a where a.TOP_SQL_TEXT not like ‘%textarea%’ ORDER BY TIME_USED DESC)TOP 20 消耗内存SQLSELECT SESS_ID ,SQL_IDSQL_TEXT,MEM_USED_BY_K,FINISH_TIME ,N_RUNS ,SEQNO,TRX_ID ,SESS_SEQFROM (SELECT TOP 20 * FROM V$SYSTEM_LARGE_MEM_SQLS a where a.SQL_TEXT not like ‘%textarea%’ ORDER BY MEM_USED_BY_K DESC)TOP 20 MTAB语句SELECT EXEC_ID,SQL_TEXT,STK_LEVEL , CUR_SQL_NODE , OP_SEQ_NO,OP_TYPE$ , MTAB_USED_BY_M , MEM_USED_BY_M , MMT_FILE_ALLOC ,MMT_PAGES_USED , MTAB_TYPE,MAL_ID , MPP_EXEC_IDFROM (SELECT TOP 20 * FROM V$MTAB_USED_HISTORY a where a.SQL_TEXT not like ‘%textarea%’)TOP20 SORT 语句SELECT EXEC_ID ,SQL_TEXTSTK_LEVEL , CUR_SQL_NODE , OP_SEQ_NO , OP_TYPE$ , N_PAGES ,N_MERGE_SORT , SORT_BYTES ,SORT_ROWS , MAL_ID , MPP_EXEC_IDFROM (SELECT TOP 20 * FROM V$SORT_HISTORY a where a.SQL_TEXT not like ‘%textarea%’)AWR 信息收集SELECT * FROM SYS.WRM$_SNAPSHOT;CALL SYS.AWR_REPORT_HTML(1,2,‘/dm8/backup’,‘AWR.HTML’);清理所有的历史快照信息DBMS_WORKLOAD_REPOSITORY.AWR_CLEAR_HISTORY;JOB情况SELECT ID , NAME , ENABLE , USERNAME , CREATETIME ,MODIFYTIME , DESCRIBE , LAST_TIME , NEXT_TIME , WHATFROM (SELECT A.ID,A.NAME,A.“ENABLE”,A.USERNAME,A.CREATETIME,A.MODIFYTIME,A.DESCRIBE,B.LAST_DATE || ’ ’ || B.LAST_SEC LAST_TIME,B.NEXT_DATE || ’ ’ || B.NEXT_SEC NEXT_TIME,B.WHATFROM SYSJOB.SYSJOBS A,SYSJOB.DBA_JOBS BWHERE A.ID B.JOB)JOB运行情况SELECT EXEC_ID , NAME ,STEPNAME , START_TIME , END_TIME,ERRTYPE , ERRCODE ,ERRINFOFROM (SELECT EXEC_ID,NAME,STEPNAME,START_TIME,END_TIME,ERRTYPE,ERRCODE,ERRINFOFROM (SELECT EXEC_ID,NAME,STEPNAME,START_TIME,END_TIME,ERRTYPE,ERRCODE,ERRINFO,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY EXEC_ID DESC) RNFROM SYSJOB.SYSSTEPHISTORIES2)WHERE RN 10)DBLINK汇总SELECT OWNER , DB_LINK ,USERNAME , HOST , CREATEDFROM DBA_DB_LINKS