DM巡检流程

最新推荐文章于 2024-06-08 18:19:57 发布

m0_67665862

最新推荐文章于 2024-06-08 18:19:57 发布

阅读量410

点赞数

文章标签：数据库 sql linq

本文链接：https://blog.youkuaiyun.com/m0_67665862/article/details/127573718

版权

机器名（ip）

Cpu型号及核数 <cat /proc/cpuinfo>

内存大小及使用情况 <free -g>

磁盘分区及使用情况 <df -h>

磁盘调度算法 <cat /sys/block/sda/queue/scheduler>

网卡 <ethtool ethx>

操作系统及版本 <cat /etc/issue> / cat /etc/*lease*

应用类型OLTP/OLAP

cat dm.ini |grep OLAP_FLAG

数据库安装目录和剩余空间

数据库版本号

Select * from sys.v$version;

数据库授权key信息

select * from v$license;

数据库文件所在目录及剩余空间

所有数据文件所占磁盘空间

du -sh /dm/dmdata/

表空间信息

select c.*,d.used_per||'%' from V$TABLESPACE c join(SELECT a.id,100-(sum(b.free_size)*100/sum(b.total_size)) used_per FROM V$TABLESPACE a,V$DATAFILE b where a.id=b.GROUP_ID group by a.id) d on c.id=d.id order by c.id;

表空间个数（DM7）或文件组个数（DM6）

select * from "SYS"."V$TABLESPACE";

表空间使用率

select c.name,d.used_per||'%' ,d.AUTO_EXTEND ,d.MAX_SIZE from V$TABLESPACE c join(SELECT a.id,100-(sum(b.free_size)*100/sum(b.total_size)) used_per,b.AUTO_EXTEND ,b.MAX_SIZE

FROM V$TABLESPACE a,V$DATAFILE b where a.id=b.GROUP_ID group by a.id,b.AUTO_EXTEND,b.MAX_SIZE)d on c.id=d.id ;

数据文件个数

select A.NAME,A.FILE_NUM from v$TABLESPACE A;

控制文件个数

DM6：为dm01.ctl和dm02.ctl，以及每个库的两个镜像控制文件××01.ctl、××02.ctl

DM7：为dm.ctl

CPU占用率<top/nmon>

I/O速率<iostat -xm -t 1 /nmon> / 写入速率 dd if=/dev/zero of=/dmdata/disk_writetestxxxx.dbf bs=32k count=10000 oflag=direct,nonblock

读取速率 dd if=/dmdata/disk_writetestxxxx.dbf of=/dm/disk_readtestxxxx.dbf bs=32k count=10000 oflag=direct,nonblock

删除文件 cd /dmdata rm -f disk_writetestxxxx.dbf disk_readtestxxxx.dbf

网络速率<nmon>

初始化参数

看初始化日志或dm管理工具

select * from V$OPTION;

通用参数

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'MEMORY_POOL'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'HUGE_BUFFER'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'BUFFER'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'WORKER_THREADS'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'TASK_THREADS'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'IO_THR_GROUPS'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'CHECK_DB_IS_ACTIVE' --查询无

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'ENABLE_SPACELIMIT_CHECK'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'ENABLE_MONITOR'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'FAST_COMMIT' --如果发现不是0，必须改为0

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'USE_PLN_POOL'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'RS_CAN_CACHE'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'SVR_LOG'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'HA_INST_CHECK_IP'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'HA_INST_CHECK_PORT';

OLTP型参数

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'BUFFER_POOLS'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'FAST_POOL_PAGES'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'FAST_ROLL_PAGES'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'MULTI_PAGE_GET_NUM'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'SESS_PLN_NUM' --查询无

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'CLT_CONST_TO_PARAM'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'CKPT_RLOG_SIZE'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'CKPT_DIRTY_PAGES'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'CKPT_INTERVAL'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'CKPT_FLUSH_RATE'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'CKPT_FLUSH_PAGES'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'FORCE_FLUSH_PAGES'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'DIRECT_IO'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'BDTA_SIZE'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'OLAP_FLAG'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'FAST_RELEASE_SLOCK'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'NOWAIT_WHEN_UNIQUE_CONFLICT'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'UNDO_EXTENT_NUM'

union

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'UNDO_RETENTION';

重做日志文件

select file_id,"V$RLOGFILE".PATH,"V$RLOGFILE".CLIENT_PATH,"V$RLOGFILE".RLOG_SIZE/1024/1024 from SYS."V$RLOGFILE";

归档日志文件

查看dmarch.ini

SELECT *,(SELECT SUM(LEN)/(1024*1024)*100 FROM V$ARCH_FILE)/arch_space_limit||'%' used_per FROM V$DM_ARCH_INI;

SQL日志文件

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'SVR_LOG';

SQL日志文件是否异步刷新

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'SVR_LOG_ASYNC_FLUSH'; -- 0未开启，1开启

SQL日志文件是否配置上限

select PARA_NAME,PARA_VALUE,FILE_VALUE,MPP_CHK,PARA_TYPE from v$dm_ini where para_name = 'SVR_LOG_FILE_NUM';

活动会话数/会话连接数

Select count(1) from v$sessions where state='ACTIVE';

事务等待

Select * from v$trxwait;

用户信息

select * from DBA_USERS;

非系统用户权限信息

SELECT * FROM (

SELECT GRANTEE,GRANTED_ROLE PRIVILEGE,'ROLE_PRIVS' PRIVILEGE_TYPE,CASE ADMIN_OPTION WHEN 'Y' THEN 'YES' ELSE 'NO' END ADMIN_OPTION FROM DBA_ROLE_PRIVS

UNION SELECT GRANTEE,PRIVILEGE,'SYS_PRIVS' PRIVILEGE_TYPE,ADMIN_OPTION FROM DBA_SYS_PRIVS

UNION SELECT GRANTEE,PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME PRIVILEGE,'TABLE_PRIVS' PRIVILEGE_TYPE,GRANTABLE FROM DBA_TAB_PRIVS

)

WHERE GRANTEE IN (SELECT USERNAME FROM ALL_USERS WHERE USERNAME NOT IN ('SYS','SYSDBA','SYSSSO','SYSAUDITOR') )

ORDER BY GRANTEE,PRIVILEGE_TYPE,PRIVILEGE;

作业信息

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.WHAT

FROM SYSJOB.SYSJOBS A,SYSJOB.USER_JOBS B

WHERE A.ID=B.JOB;

作业调度信息

SELECT * FROM SYSJOB.SYSJOBSCHEDULES;

作业运行历史信息

SELECT *

FROM SYSJOB.SYSSTEPHISTORIES2 A

WHERE (SELECT COUNT(*)

FROM SYSJOB.SYSSTEPHISTORIES2 B

WHERE B.NAME = A.NAME

AND B.EXEC_ID >= A.EXEC_ID) <= 10

ORDER BY A.START_TIME DESC,A.NAME;

--资源限制信息 614

select b.name,

a.SESS_PER_USER,

a.CONN_IDLE_TIME,

a.FAILED_NUM,

a.LIFE_TIME,

a.REUSE_TIME,

a.REUSE_MAX,

a.LOCK_TIME,

a.GRACE_TIME,

a.LOCKED_STATUS,

a.LASTEST_LOCKED,

a.PWD_POLICY,

a.RN_FLAG,

a.ALLOW_ADDR,

a.NOT_ALLOW_ADDR,

a.ALLOW_DT,

a.NOT_ALLOW_DT,

a.LAST_LOGIN_DTID,

a.LAST_LOGIN_IP,

a.FAILED_ATTEMPS

from SYSUSERS a,SYS.SYSOBJECTS b where a.id=b.id;

错误日志记录

Select * from V$RUNTIME_ERR_HISTORY

--死锁历史信息615

select * from V$DEADLOCK_HISTORY;

--DBLINK 信息 612

select * from DBA_DB_LINKS;

--阻塞信息616

WITH TRX_TAB AS

(SELECT O1.NAME,L1.TRX_ID FROM V$LOCK L1,SYSOBJECTS O1 WHERE L1.TABLE_ID=O1.ID AND 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) SS

FROM V$LOCK L,V$SESSIONS S1,V$SESSIONS S2

WHERE L.TRX_ID=S1.TRX_ID AND L.ROW_IDX=S2.TRX_ID)

SELECT SYSDATE STATTIME,* FROM TRX_SESS where BLOCKED=1;

--错误日志信息617

SELECT EXEC_ID, NAME,STEPNAME,START_TIME,END_TIME,ERRTYPE,ERRCODE,ERRINFO

FROM (SELECT EXEC_ID, NAME,STEPNAME,START_TIME,END_TIME,ERRTYPE,ERRCODE,ERRINFO,

ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY EXEC_ID DESC) RN

FROM SYSJOB.SYSSTEPHISTORIES2) WHERE RN<=10;

--序列信息613

select * from DBA_SEQUENCES;

--系统信息701

select * from V$SYSTEMINFO;

--字典缓存信息 702

select * from V$DB_CACHE;

--数据缓冲信息 703

select * from V$BUFFERPOOL;

--内存池 704

select * from V$MEM_POOL;

--系统统计信息 705

select * from V$SYSSTAT where classid in (11,5) order by classid desc;

--会话统计 801

SELECT STATE,CLNT_IP,CLNT_TYPE,CURR_SCH,USER_NAME,COUNT(*) COUNTS FROM V$SESSIONS GROUP BY STATE,CLNT_IP,CLNT_TYPE,CURR_SCH,USER_NAME ORDER BY STATE;

--对象统计 802

select tablespace_name,'TABLE_OF_TS' OBJTYPE,count(*) COUNTS from all_tables group by tablespace_name union all

select * from ( select owner,object_type,count(*) from all_objects

where owner not in ('SYS','SYSTEM','SYSAUDITOR','SYSSSO','CTISYS') group by object_type,owner

order by 1,2);

最慢的20条SQL 804

SELECT TOP 20 START_TIME,TIME_USED/1000 TIME_USED,TOP_SQL_TEXT FROM V$SQL_HISTORY ORDER BY TIME_USED DESC;