DM8日常运维必须要懂的几个命令

本文涉及的产品
日志服务 SLS,月写入数据量 50GB 1个月
简介: DM8日常运维必须要懂的几个命令

检查表空间

  1. SQL>SELECT a.tablespace_name "表空间名称",
  2. total / (1024 * 1024) "表空间大小(M)",
  3. free / (1024 * 1024) "表空间剩余大小(M)",
  4. (total - free) / (1024 * 1024 ) "表空间使用大小(M)",
  5. total / (1024 * 1024 * 1024) "表空间大小(G)",
  6. free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
  7. (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
  8. round((total - free) / total, 4) * 100 "使用率 %"
  9. FROM (SELECT tablespace_name, SUM(bytes) free
  10. FROM dba_free_space
  11. GROUP BY tablespace_name) a,
  12. (SELECT tablespace_name, SUM(bytes) total
  13. FROM dba_data_files
  14. GROUP BY tablespace_name) b
  15. WHERE a.tablespace_name = b.tablespace_name;

检查临时表空间

  1. SQL>SELECT a.tablespace_name "表空间名称",
  2. total / (1024 * 1024) "表空间大小(M)",
  3. free / (1024 * 1024) "表空间剩余大小(M)",
  4. (total - free) / (1024 * 1024 ) "表空间使用大小(M)",
  5. total / (1024 * 1024 * 1024) "表空间大小(G)",
  6. free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
  7. (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
  8. round((total - free) / total, 4) * 100 "使用率 %"
  9. FROM (SELECT tablespace_name, SUM(bytes) free
  10. FROM dba_free_space
  11. GROUP BY tablespace_name) a,
  12. (SELECT tablespace_name, SUM(bytes) total
  13. FROM dba_data_files
  14. GROUP BY tablespace_name) b
  15. WHERE a.tablespace_name = b.tablespace_name;

image.svg

查看数据文件信息

  1. SQL>select * from V$DATAFILE;

image.svg

查看表空间与数据文件关系

  1. SQL>SELECT
  2.        TS.NAME,
  3.        DF.PATH
  4. FROM
  5.        V$TABLESPACE AS TS,
  6.        V$DATAFILE   AS DF
  7. WHERE
  8.        TS.ID = DF.GROUP_ID;

image.svg

查看是否有死锁

  1. SQL> select * from V$DEADLOCK_HISTORY;

image.svg

查看数据库是否存在阻塞

  1. SQL> WITH TRX_TAB AS
  2. (SELECT O1.NAME,L1.TRX_ID FROM V$LOCK L1,SYSOBJECTS O1 WHERE L1.TABLE_ID=O1.ID AND O1.ID<>0),
  3. TRX_SESS AS (
  4. 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,
  5. S1.SESS_ID WT_SESS,S2.SESS_ID BLK_SESS,
  6. 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
  7. FROM V$LOCK L,V$SESSIONS S1,V$SESSIONS S2
  8. WHERE L.TRX_ID=S1.TRX_ID AND L.ROW_IDX=S2.TRX_ID)
  9. SELECT SYSDATE STATTIME,* FROM TRX_SESS where BLOCKED=1;

image.svg

内存池信息检查

  1. SQL> select * from V$DB_CACHE;

image.svg

  1. SQL> select * from V$BUFFERPOOL;

image.svg

  1. SQL> select * from V$MEM_POOL;

image.svg

数据库统计信息检查

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

image.svg

会话统计

  1. SQL> SELECT STATE,CLNT_IP,CLNT_TYPE,CURR_SCH,USER_NAME,COUNT(*)
  2. COUNTS FROM V$SESSIONS GROUP BY STATE,CLNT_IP,CLNT_TYPE,CURR_SCH,USER_NAME
  3. ORDER BY STATE;

image.svg

对象统计

  1. SQL>select tablespace_name,'TABLE_OF_TS' OBJTYPE,count(*) COUNTS from all_tables group by tablespace_name union all
  2. select * from ( select owner,object_type,count(*) from all_objects
  3. where owner not in ('SYS','SYSTEM','SYSAUDITOR','SYSSSO','CTISYS') group by object_type,owner
  4. order by 1,2);

image.svg

表行数统计

  1. SQL>begin
  2. for rec in (select owner,table_name from all_tables where owner not in ('SYS','SYSAUDITOR','SYSSSO','CTISYS')) loop
  3. call SP_TAB_STAT_INIT(rec.owner,rec.table_name);
  4. end loop;
  5. end;
  6. /
  7. select owner,table_name,tablespace_name,status,num_rows from all_tables where owner not in ('SYS','SYSAUDITOR','SYSSSO','CTISYS')
  8. order by owner,num_rows desc;

image.svg

dblink检查

  1. SQL> select * from DBA_DB_LINKS;

最慢的20条SQL统计

  1. SQL>SELECT TOP 20 START_TIME,TIME_USED/1000 TIME_USED,TOP_SQL_TEXT FROM V$SQL_HISTORY ORDER BY TIME_USED DESC;
  2. SELECT * FROM V$SYSTEM_LONG_EXEC_SQLS ORDER BY EXEC_TIME DESC;

image.svg

内存高的20条SQL统计

  1. SQL>select * from  V$SYSTEM_LARGE_MEM_SQLS order by mem_used_by_k desc;

image.svg

前 20 条长耗时等待事件统计

  1. SQL> SELECT top 20 * FROM V$SYSTEM_EVENT ORDER BY TOTAL_WAITS DESC;

image.svg

查看所有作业信息

  1. --查看所有作业信息
  2. SELECT A.ID,A.NAME,A."ENABLE",A.USERNAME,A.CREATETIME,A.MODIFYTIME,A.DESCRIBE,
  3. B.LAST_DATE||' '||B.LAST_SEC LAST_TIME,B.NEXT_DATE||' '||B.NEXT_SEC NEXT_TIME,B.WHAT
  4. FROM SYSJOB.SYSJOBS A,SYSJOB.USER_JOBS B
  5. WHERE A.ID=B.JOB;
  6. --查看所有作业调度信息
  7. SELECT * FROM SYSJOB.SYSJOBSCHEDULES;

查看用户信息,包括用户名、用户密码、默认表空间、索引表空间、数据文件等信息

  1. SQL> select * from DBA_USERS;

查看密码策略

  1. SQL> Select * from v$dm_ini where para_name='PWD_POLICY';

查看 redo 日志信息,包括路径,大小,可用空间,总空间

  1. SQL>SELECT
  2.        A.FILE_ID    ,
  3.        A.PATH       ,
  4.        A.CLIENT_PATH,
  5.        A.RLOG_SIZE  ,
  6.        B.FREE_SPACE ,
  7.        B.TOTAL_SPACE,
  8.        B.CUR_FILE
  9. from
  10.        (
  11.                select * from V$RLOGFILE where true
  12.        )  A,
  13.        (
  14.                select * from V$RLOG where true
  15.        )  B;

image.svg

查看归档日志是否打开

  1. SQL> select name,arch_mode from v$database;

SQL 命令方式开启归档

  1. SQL> ALTER DATABASE MOUNT;
  2. executed successfully
  3. used time: 1.439(ms). Execute id is 0.
  4. SQL> ALTER DATABASE ARCHIVELOG;
  5. executed successfully
  6. used time: 5.425(ms). Execute id is 0.
  7. SQL> ALTER DATABASE ADD ARCHIVELOG 'DEST=/dm/arch, TYPE=LOCAL, FILE_SIZE=2048, SPACE_LIMIT=10240';
  8. executed successfully
  9. used time: 1.101(ms). Execute id is 0.
  10. SQL> ALTER DATABASE OPEN;
  11. executed successfully
  12. used time: 8.692(ms). Execute id is 0.
  13. SQL> select name,arch_mode from v$database;

  14. LINEID     NAME ARCH_MODE
  15. ---------- ---- ---------
  16. 1          PROD Y

检查 SVR_LOG 参数

  1. SQL> select * from v$parameter where name like 'SVR_LOG%';

开启日志

  1. SQL> SP_REFRESH_SVR_LOG_CONFIG();
  2. DMSQL executed successfully
  3. used time: 0.435(ms). Execute id is 64434.
  4. SQL> sp_set_para_value(1,'SVR_LOG',1);
  5. DMSQL executed successfully
  6. used time: 8.896(ms). Execute id is 64435.
  7. SQL> select * from v$parameter where name like 'SVR_LOG%';

  8. LINEID     ID          NAME         TYPE VALUE    SYS_VALUE FILE_VALUE DESCRIPTION                                                                                                                 DEFAULT_VALUE ISDEFAULT  
  9. ---------- ----------- ------------ ---- -------- --------- ---------- --------------------------------------------------------------------------------------------------------------------------- ------------- -----------
  10. 1          550         SVR_LOG_NAME SYS  SLOG_ALL SLOG_ALL  SLOG_ALL   Using which sql log sys in sqllog.ini                                                                                       NULL          1
  11. 2          553         SVR_LOG      SYS  1        1         1          Whether the Sql Log sys Is open or close. 1:open, 0:close, 2:use switch and detail mode. 3:use not switch and simple mode.  0             0

  12. used time: 5.476(ms). Execute id is 64436.

开启AWR报告

检查数据库DBMS_WORKLOAD_REPOSITORY系统包的启用状态(0:未启用;1:已启用)以及awr快照信息和表空间信息


  1. SQL> select sf_check_awr_sys;
  2. SQL> select * from sys.wrm$_snapshot;
  3. SQL> select tablespace_name from dba_tablespaces;

开启AWR功能

SQL> DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(10);

image.svg

创建awr报告(可以手动创建,也可以等待,数据库会按照间隔时间自动创建)

  1. SQL> DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

查看已经生成的快照信息

  1. SQL> select * from sys.wrm$_snapshot;

生成html报告

  1. SQL> SYS.AWR_REPORT_HTML(1,2,'/home/dmdba/','dm_awr_html.html');

image.svg

清理AWR快照

例1:清理快照id为1-2(包含快照号为1和2的快照)之间的快照:

  1. SQL> DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(1,2);

例2:清理所有的历史快照信息

  1. SQL> DBMS_WORKLOAD_REPOSITORY.AWR_CLEAR_HISTORY;
  2. SQL> select count(*) from sys.wrm$_snapshot;

2 、修改快照配置信息,包括修改快照生成的时间间隔、快照保留时间等

例1:先确认当前数据库环境的快照保存信息(查询WRM$_WR_CONTROL表),然后再修改快照生成的时间间隔。

  1. SQL> select * from sys.wrm$_wr_control;

例如设置快照生成时间间隔为30分钟

  1. SQL> DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(30);

开启日志挖掘

  1. SQL> SP_CREATE_SYSTEM_PACKAGES (1,'DBMS_LOGMNR');
  2. DMSQL executed successfully
  3. SQL> select SF_CHECK_SYSTEM_PACKAGE('DBMS_LOGMNR');
  4. LINEID     SF_CHECK_SYSTEM_PACKAGE('DBMS_LOGMNR')
  5. ---------- --------------------------------------
  6. 1          1
  7. used time: 0.942(ms). Execute id is 78402.

确认参数

  1. SQL> select para_name, para_value from v$dm_ini where para_name in ('ARCH_INI','RLOG_APPEND_LOGIC');

  2. LINEID     PARA_NAME         PARA_VALUE
  3. ---------- ----------------- ----------
  4. 1          RLOG_APPEND_LOGIC 0
  5. 2          ARCH_INI          1

  6. used time: 5.339(ms). Execute id is 78404.

切换归档日志

  1. SQL> alter system archive log current;
  2. executed successfully
  3. used time: 4.457(ms). Execute id is 0.
  4. SQL> select name , first_time , next_time , first_change# , next_change# from v$archived_log;

  5. LINEID     NAME                                                           FIRST_TIME                 NEXT_TIME                  FIRST_CHANGE#        NEXT_CHANGE#        
  6. ---------- -------------------------------------------------------------- -------------------------- -------------------------- -------------------- --------------------
  7. 1          /dm/arch/ARCHIVE_LOCAL1_0x71597500_EP0_2024-08-08_15-40-08.log 2024-08-08 15:40:08.729349 2024-08-08 16:31:23.311618 43481                85293

  8. used time: 0.352(ms). Execute id is 78405.

添加一个或多个需要分析的归档日志文件

  1. SQL> dbms_logmnr.add_logfile('/dm/arch/ARCHIVE_LOCAL1_0x71597500_EP0_2024-08-08_15-40-08.log');
  2. DMSQL executed successfully
  3. used time: 1.740(ms). Execute id is 78406.
  4. SQL>

查看通过 ADD_LOGFILE 添加的归档日志文件,可以通过动态视图 V$LOGMNR_LOGS 进行查询

  1. SQL> select low_scn, next_scn, low_time, high_time, log_id, filename from v$logmnr_logs;

  2. LINEID     LOW_SCN              NEXT_SCN             LOW_TIME                   HIGH_TIME                  LOG_ID      FILENAME                                                      
  3. ---------- -------------------- -------------------- -------------------------- -------------------------- ----------- --------------------------------------------------------------
  4. 1          43481                85293                1900-01-01 00:00:00.000000 1900-01-01 00:00:00.000000 0           /dm/arch/ARCHIVE_LOCAL1_0x71597500_EP0_2024-08-08_15-40-08.log

  5. used time: 0.614(ms). Execute id is 78407.
  6. SQL>dbms_logmnr.start_logmnr(
  7. Options=>2128,
  8. Starttime=>to_date('2024-08-08 15:00:00','YYYY-MM-DD HH24:MI:SS'),
  9. Endtime=>to_date('2024-08-08 16:33:00','YYYY-MM-DD HH24:MI:SS'));

查看归档日志文件的分析结果

  1. SQL>select TIMESTAMP ,START_TIMESTAMP ,COMMIT_TIMESTAMP ,OPERATION ,OPERATION_CODE ,ROLL_BACK ,TABLE_NAME ,ROW_ID ,USERNAME ,DATA_OBJ# , DATA_OBJV# , SQL_REDO, REDO_VALUE, UNDO_VALUE
  2. from V$LOGMNR_CONTENTS;
  3. SQL>select operation_code , scn, sql_redo , timestamp ,seg_owner, table_name from
  4. V$LOGMNR_CONTENTS where seg_owner = 'SYSDBA' and operation_code in (3,1,2);

终止日志挖掘

  1. SQL> DBMS_LOGMNR.END_LOGMNR();
  2. DMSQL executed successfully
  3. used time: 0.720(ms). Execute id is 78426.
相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
相关文章
|
2月前
|
SQL 运维 监控
DM日常运维高频命令总结
DM日常运维高频命令总结
75 3
|
2月前
|
运维 Oracle 前端开发
Oracle 11g RAC集群日常运维命令总结
Oracle 11g RAC集群日常运维命令总结
59 2
|
2月前
|
SQL 运维 调度
DM8日常运维命令总结(二)
DM8日常运维命令总结(二)
67 2
|
2月前
|
SQL 运维 Oracle
入门级Oracle 11g日常运维命令总结
入门级Oracle 11g日常运维命令总结
28 1
|
2月前
|
移动开发 运维 网络协议
运维必备 | Linux netstat命令详解
运维必备 | Linux netstat命令详解
|
2月前
|
数据采集 运维 监控
运维笔记:流编辑器sed命令用法解析
运维笔记:流编辑器sed命令用法解析
47 5
|
2月前
|
运维 Rust 监控
Linux高效运维必备:fd命令深度解析,文件描述符管理从此得心应手!
【8月更文挑战第23天】本文介绍了一款名为fd的命令行工具,该工具基于Rust语言开发,旨在以更直观的语法和更快的速度替代传统的`find`命令。通过本文,您可以了解到如何安装fd以及一些基本用法示例,比如使用正则表达式匹配文件名、排除特定目录等。此外,文章还展示了如何结合`ps`和`lsof`命令来查找特定文件并显示其文件描述符,从而帮助您更好地管理和监控Linux系统中的文件与进程。
86 0
|
3月前
|
数据采集 算法 API
开发与运维命令问题之安装和使用ToolLLaMa如何解决
开发与运维命令问题之安装和使用ToolLLaMa如何解决
34 0
|
3月前
|
API 开发者 运维
开发与运维命令问题之Gorilla-CLI提升命令行交互体验如何解决
开发与运维命令问题之Gorilla-CLI提升命令行交互体验如何解决
17 0
|
16天前
|
敏捷开发 运维 Prometheus
构建高效运维体系:从基础架构到自动化管理
本文探讨了如何通过优化基础架构、引入自动化工具和流程,以及加强团队协作,构建高效的运维体系。通过案例分析和实践建议,帮助运维人员实现系统的稳定性、可靠性和可维护性。