Linux 、oracle 数据库运维监控常用语句和命令

最新推荐文章于 2023-06-05 13:27:49 发布

番茄你个西红4

最新推荐文章于 2023-06-05 13:27:49 发布

阅读量1.8k

点赞数 3

文章标签：数据库 oracle linux 运维

本文链接：https://blog.csdn.net/qq_21608393/article/details/78826393

版权

Oracle

1、查询oracle的连接数
select count(*) from v$session;
2、查询oracle的并发连接数
4select count(*) from v$session where status='ACTIVE';
3、查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username;
4、查看所有用户：
select * from all_users;
---用户登录不区分大小写
alter system set sec_case_sensitive_logon=false;
---用户解锁
alter user j2_hx account unlock;
alter user j2_hx identified by j2_hx;
grant dba,resource,connect to j2_hx;
5、查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs;
select * from user_sys_privs;
6、查看角色(只能查看登陆用户拥有的角色)所包含的权限
13select * from role_sys_privs;
7、查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
8、查看所有角色：
select * from dba_roles;
9、查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;
10、查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS;

Linux

查询文件最后300行
tail -n 300 log.xml >> log300.txt
查询心跳
select * from SYS.DBA_HIST_SYSMETRIC_SUMMARY t
查询磁盘组使用情况
select
col1 as "磁盘组名称",
col2 as "磁盘组总大小",
col3 as "磁盘组剩余大小",
round(col4,4)*100||'%' as "磁盘组使用率"
from(
select asmgroup. NAME as col1,
asmgroup.TOTAL_MB as col2,
asmgroup.FREE_MB as col3,
asmgroup.COLD_USED_MB/asmgroup.TOTAL_MB as col4

from v$asm_diskgroup asmgroup
);

#查看oracle alter 日志(153.12.72.110核心01 alter)
cd $ORACLE_BASE/diag/rdbms/gzltgshx/gzltgshx1/trace
tail -300f alert_gzltgshx1.log

#查看oracle alter 日志(10.104.168.156接收01 alter)
cd $ORACLE_BASE//diag/rdbms/gzltscjs/gzltscjs1/trace
tail -300f alert_gzltscjs1.log

#查询数据库集群心跳
select BEGIN_TIME         btime,
       INTSIZE,
       METRIC_NAME        mrtric_name,
       NUM_INTERVAL       val,
       MINVAL,
       MAXVAL,
       AVERAGE            avgval,
       STANDARD_DEVIATION std,
       SUM_SQUARES        sum_square
from dba_hist_sysmetric_summary
where METRIC_NAME in
       ('Network Traffic Volume Per Sec', 'I/O Megabytes     per Second')
   and rownum < 11
order by std desc;

select * from v$asm_diskgroup;
select col1 as "名称"，
col2 as "总大小"，
col3 as "剩余大小",
col4 as "使用比"
from(
select
a.NAME as col1,
a.TOTAL_MB as col2,
a.FREE_MB as col3,
round(nvl(a.COLD_USED_MB/a.TOTAL_MB,0),4)*100 || '%' as col4
from v$asm_diskgroup a
);

---查询死锁
select a.XIDUSN,
       a.OBJECT_ID,
       b.OWNER,
       b.OBJECT_NAME,
       a.SESSION_ID,
       a.ORACLE_USERNAME,
       a.OS_USER_NAME,
       a.PROCESS,
       a.LOCKED_MODE
from v$locked_object a, dba_objects b
where a.OBJECT_ID = b.OBJECT_ID

select count(*) from v$process --当前的连接数
select value from v$parameter where name = 'processes' --数据库允许的最大连接数
修改最大连接数:
alter system set processes = 300 scope = spfile;
重启数据库:
shutdown immediate;
startup;
--查看当前有哪些用户正在使用数据
SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine
from v$session a, v$sqlarea b
where a.sql_address =b.address order by cpu_time/executions desc;
select count(*) from v$session #连接数

select count(*) from v$session where status='ACTIVE'　#并发连接数
show parameter processes #最大连接
alter system set processes = value scope = spfile;重启数据库 #修改连接

SQL> Select count(*) from v$session where status='ACTIVE' ;

COUNT(*)
----------
20

SQL> Select count(*) from v$session;

COUNT(*)
----------
187

SQL> show parameter processes;

NAME TYPE VALUE
------------------------------------ ----------- ----------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 450
SQL>

并发指active,I SEE
SQL> select count(*) from v$session #连接数
SQL> Select count(*) from v$session where status='ACTIVE'　#并发连接数
SQL> show parameter processes #最大连接
SQL> alter system set processes = value scope = spfile;重启数据库 #修改连接

unix 1个用户session 对应一个操作系统 process
而 windows体现在线程
DBA要定时对数据库的连接情况进行检查，看与数据库建立的会话数目是不是正常，如果建立了过多的连接，会消耗数据库的资源。同时，对一些“挂死”的连接，可能会需要DBA手工进行清理。
以下的SQL语句列出当前数据库建立的会话情况:
select sid,serial#,username,program,machine,status
from v$session;
输出结果为:
SID SERIAL# USERNAME PROGRAM MACHINE STATUS
---- ------- ---------- ----------- --------------- --------
1 1 ORACLE.EXE WORK3 ACTIVE
2 1 ORACLE.EXE WORK3 ACTIVE
3 1 ORACLE.EXE WORK3 ACTIVE
4 1 ORACLE.EXE WORK3 ACTIVE
5 3 ORACLE.EXE WORK3 ACTIVE
6 1 ORACLE.EXE WORK3 ACTIVE
7 1 ORACLE.EXE WORK3 ACTIVE
8 27 SYS SQLPLUS.EXE WORKGROUP\\WORK3 ACTIVE
11 5 DBSNMP dbsnmp.exe WORKGROUP\\WORK3 INACTIVE
其中，
SID 会话(session)的ID号;
SERIAL# 会话的序列号，和SID一起用来唯一标识一个会话;
USERNAME 建立该会话的用户名;
PROGRAM 这个会话是用什么工具连接到数据库的;
STATUS 当前这个会话的状态，ACTIVE表示会话正在执行某些任务，INACTIVE表示当前会话没有执行任何操作;
如果DBA要手工断开某个会话，则执行:
alter system kill session \'SID,SERIAL#\'
sql语句
SQL语句如下：

SELECT username, machine, program, status, COUNT (machine) AS
连接数量
FROM v$session
GROUP BY username, machine, program, status
ORDER BY machine;

显示结果（每个人的机器上会不同）

SCHNEIDER|WORKGROUD\WANGZHENG|TOAD.exe|ACTIVE|1
SCHNEIDER|WORKGROUP\597728AA514F49D|sqlplusw.exe|INACTIVE|1
|WWW-Q6ZMR2OIU9V|ORACLE.EXE|ACTIVE|8
PUBLIC|||INACTIVE|0
select USER,sid,serial#,UTL_INADDR.GET_host_ADDRESS as host,SYS_CONTEXT('USERENV','ip_ADDRESS') as local,SYSDATE
from V$session;

10、# getconf LONG_BIT
32
(说明当前CPU运行在32bit模式下, 但不代表CPU不支持64bit)

11、# cat /proc/cpuinfo | grep flags | grep ' lm ' | wc –l
8(结果大于0, 说明支持64bit计算. lm指long mode, 支持lm则是64bit)

1.查看CPU信息命令
cat /proc/cpuinfo
AIX 查询SMLCTL
2.查看内存信息命令
cat /proc/meminfo
3.查看硬盘信息命令
fdisk -l

查看CPU信息（型号）
# cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
8 Intel(R) Xeon(R) CPU E5410 @ 2.33GHz
(看到有8个逻辑CPU, 也知道了CPU型号)

# cat /proc/cpuinfo | grep physical | uniq -c
4 physical id : 0
4 physical id : 1
(说明实际上是两颗4核的CPU)
PS：Jay added on 10th, May, 2011
# 其实是可能有超线程HT技术，不一定是有4核，也可能是2核4线程；当时还理解不清楚

# getconf LONG_BIT
32
(说明当前CPU运行在32bit模式下, 但不代表CPU不支持64bit)

# cat /proc/cpuinfo | grep flags | grep ' lm ' | wc -l
8
(结果大于0, 说明支持64bit计算. lm指long mode, 支持lm则是64bit)

再完整看cpu详细信息, 不过大部分我们都不关心而已.
# dmidecode | grep 'Processor Information'

查看内存信息
# cat /proc/meminfo

# uname -a
Linux euis1 2.6.9-55.ELsmp #1 SMP Fri Apr 20 17:03:35 EDT 2007 i686 i686 i386 GNU/Linux
(查看当前操作系统内核信息)

# cat /etc/issue | grep Linux
Red Hat Enterprise Linux AS release 4 (Nahant Update 5)
(查看当前操作系统发行版信息)

查看机器型号
# dmidecode | grep "Product Name"

查看网卡信息
# dmesg | grep -i eth
---RAC 节点脱离操作
清理RAC集群节点配置
/u01/app/11.2.0/grid/crs/install
./rootcrs.pl -verbose -deconfig -force
./roothas.pl -deconfig -force -verbose

查询oracle归档大小
show parameter db_recivery_file_dest_size;
select * from v$recovery_file_dest;

log_20161203.zip

alterlog_20161203.zip

----查询月增量
select C.tablespace_name AS "表空间名称",
       D."Total(MB)" AS "总量(MB)",
       D."Used(MB)" - C."Used(MB)" AS "增量(MB)",
       to_char(next_day(trunc(sysdate),1)-40,'yyyy/mm/dd')||'--'||to_char(next_day(trunc(sysdate),1)-11,'yyyy/mm/dd') "时间范围"
from (select B.name tablespace_name,
               case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024)
                    when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2)
                    END as "Total(MB)",
               round(A.tablespace_usedsize*8 / 1024) "Used(MB)",
               A.rtime
          from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B
         where A.tablespace_id = B.TS#
           and to_char(to_date(replace(rtime, '/', null),
                               'mmddyyyy hh24:mi:ss'),
                       'yyyymmdd hh24:mi') =
               to_char(next_day(trunc(sysdate),2)-14,'yyyymmdd hh24:mi')) C,
       (select B.name tablespace_name,
               case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024)
                    when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2)
                    END as "Total(MB)",
               round(A.tablespace_usedsize*8 / 1024) "Used(MB)",
               A.rtime
          from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B
         where A.tablespace_id = B.TS#
           and to_char(to_date(replace(rtime, '/', null),
                               'mmddyyyy hh24:mi:ss'),
                       'yyyymmdd hh24:mi') =
               to_char(next_day(trunc(sysdate),2)-7,'yyyymmdd hh24:mi')) D
where C.tablespace_name = D.tablespace_name
and C.tablespace_name like'TS%';

---清理归档日志
1、设置ORACLE环境变量 export ORACLE_SID=gzltgshx
2、使用命令rman 进入到数据库逻辑单元
3、connect target/
4、crosscheck archivelog all;
5、delete archivelog all completed before'sysdate -7';
6、yes

---表空间使用率
SELECT df.tablespace_name,
       COUNT(*) datafile_count,
       ROUND(SUM(df.BYTES) / 1048576 / 1024, 2) size_gb,
       ROUND(SUM(free.BYTES) / 1048576 / 1024, 2) free_gb,
       ROUND(SUM(df.BYTES) / 1048576 / 1024 -
             SUM(free.BYTES) / 1048576 / 1024,
             2) used_gb,
       ROUND(MAX(free.maxbytes) / 1048576 / 1024, 2) maxfree,
       100 - ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_used,
       ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_free
FROM dba_data_files df,
       (SELECT tablespace_name,
               file_id,
               SUM(BYTES) BYTES,
               MAX(BYTES) maxbytes
          FROM dba_free_space
         where bytes > 1024 * 1024
         GROUP BY tablespace_name, file_id) free
WHERE df.tablespace_name = free.tablespace_name(+)
   AND df.file_id = free.file_id(+)
GROUP BY df.tablespace_name
ORDER BY 8

----空间使用率
---表空间使用率
SELECT df.tablespace_name,
       COUNT(*) datafile_count,
       ROUND(SUM(df.BYTES) / 1048576 / 1024, 2) size_gb,
       ROUND(SUM(free.BYTES) / 1048576 / 1024, 2) free_gb,
       ROUND(SUM(df.BYTES) / 1048576 / 1024 -
             SUM(free.BYTES) / 1048576 / 1024,
             2) used_gb,
      /* ROUND(MAX(free.maxbytes) / 1048576 / 1024, 2) maxfree,
       100 - ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_used,
       ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_free*/
       ROUND((ROUND(SUM(df.BYTES) / 1048576 / 1024 -
             SUM(free.BYTES) / 1048576 / 1024,
             2))/(ROUND(SUM(df.BYTES) / 1048576 / 1024, 2)),2)*100 || '%' AS "使用比"
FROM dba_data_files df,
       (SELECT tablespace_name,
               file_id,
               SUM(BYTES) BYTES,
               MAX(BYTES) maxbytes
          FROM dba_free_space
         where bytes > 1024 * 1024
         GROUP BY tablespace_name, file_id) free
WHERE df.tablespace_name = free.tablespace_name(+)
   AND df.file_id = free.file_id(+)
   and df.TABLESPACE_NAME like'TS%'
GROUP BY df.tablespace_name
/* ORDER BY 8*/

10.07 19.14
09.56 19.35

---核心
SELECT df.tablespace_name,
       ROUND(SUM(df.BYTES) / 1048576 / 1024, 2) size_gb,
       ROUND(SUM(df.BYTES) / 1048576 / 1024 -
             SUM(free.BYTES) / 1048576 / 1024,
             2) used_gb,
      /* ROUND(MAX(free.maxbytes) / 1048576 / 1024, 2) maxfree,
       100 - ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_used,
       ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_free*/
       ROUND((ROUND(SUM(df.BYTES) / 1048576 / 1024 -
             SUM(free.BYTES) / 1048576 / 1024,
             2))/(ROUND(SUM(df.BYTES) / 1048576 / 1024, 2)),2)*100 || '%' AS "使用率"
FROM dba_data_files df,
       (SELECT tablespace_name,
               file_id,
               SUM(BYTES) BYTES,
               MAX(BYTES) maxbytes
          FROM dba_free_space
         where bytes > 1024 * 1024
         GROUP BY tablespace_name, file_id) free
WHERE df.tablespace_name = free.tablespace_name(+)
   AND df.file_id = free.file_id(+)
   and df.TABLESPACE_NAME like'TS%'
GROUP BY df.tablespace_name
/* ORDER BY 8*/

---查询
SELECT df.tablespace_name,
       ROUND(SUM(df.BYTES) / 1048576 / 1024, 2) size_gb,
       ROUND(SUM(df.BYTES) / 1048576 / 1024 -
             SUM(free.BYTES) / 1048576 / 1024,
             2) used_gb,
      /* ROUND(MAX(free.maxbytes) / 1048576 / 1024, 2) maxfree,
       100 - ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_used,
       ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_free*/
       ROUND((ROUND(SUM(df.BYTES) / 1048576 / 1024 -
             SUM(free.BYTES) / 1048576 / 1024,
             2))/(ROUND(SUM(df.BYTES) / 1048576 / 1024, 2)),2)*100 || '%' AS "使用率"
FROM dba_data_files df,
       (SELECT tablespace_name,
               file_id,
               SUM(BYTES) BYTES,
               MAX(BYTES) maxbytes
          FROM dba_free_space
         where bytes > 1024 * 1024
         GROUP BY tablespace_name, file_id) free
WHERE df.tablespace_name = free.tablespace_name(+)
   AND df.file_id = free.file_id(+)
   and df.TABLESPACE_NAME like'TS_GS%'
GROUP BY df.tablespace_name
/* ORDER BY 8*/

番茄你个西红4

关注

3
点赞
踩
19

收藏

觉得还不错? 一键收藏
0
评论
Linux 、oracle 数据库运维监控常用语句和命令

Oracle1、查询oracle的连接数select count(*) from v$session;2、查询oracle的并发连接数4select count(*) from v$session where status='ACTIVE';3、查看不同用户的连接数select username,count(username) from v$session wher
复制链接

扫一扫