Oracle DBA告警处置方法（更新）

归档日志满了

rman target /
crosscheck archivelog all;
delete force noprompt archivelog all completed before 'sysdate-7'; ---sysdate-7 为保留7天的，要是还不够就再删多一点。

==如果出现rman登陆不了，到trace目录下清理一些trc 文件==

cd $ORACLE_BASE/rdbms/diag
find . -type f -mtime +3 | xargs ls -lrt
find . -type f -mtime +1 | xargs ls -lrt
find . -type f -mtime +7 | xargs rm -f

find ./ -mtime +14 -name "*.trm" |xargs rm -f

du -sh ./* | head -n 10 ---找到最大文件
某个文件很大的话用下面语句重定向
>xxx.trc

ORA-04031 

方法一

扩大share pool容量

查看各个池子大小
sqlplus / as sysdba ---在CBD中
show parameter sga
show parameter share
show parameter buffer
show parameter db_cache
alter system set shared_pool_size=$G sid='*' SCOPE = BOTH; ---更改前记着屏蔽告警

方法二

alter system flash shared_pool;

数据库有锁

锁表查询的代码有以下的形式
select count(*) from v$locked_object;
select * from v$locked_object;

查看哪个表被锁
select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id;

查看是哪个session引起的
select a.OS_USER_NAME, c.owner, c.object_name, b.sid, b.serial#, logon_time
from v$locked_object a, v$session b, dba_objects c
where a.session_id = b.sid
and a.object_id = c.object_id
order by b.logon_time;

查看数据库锁,诊断锁的来源及类型：
SELECT OBJECT_ID, SESSION_ID, LOCKED_MODE FROM V$LOCKED_OBJECT;

找出数据库的serial#,以备杀死：
SELECT T2.USERNAME, T2.SID, T2.SERIAL#, T2.LOGON_TIME
FROM V$LOCKED_OBJECT T1, V$SESSION T2
WHERE T1.SESSION_ID = T2.SID
ORDER BY T2.LOGON_TIME;

杀死该session
alter system kill session '1234,66356'; --其中1234为sid,66356为serial#.

 

Oracle导出awr报告

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); ---生成快照
@$ORACLE_HOME/rdbms/admin/awrrpt.sql ---导出awr报告

sz -b awrrpt_xxxx.html ---下载到本地

连接数

查看连接数：
select count(*) from v$session;
杀远程连接： --- 在操作系统下执行命令
ps -ef |grep LOCAL=NO|grep -v grep|cut -c 9-15|xargs kill -9 （过于危险）
查杀失效连接进程：
SET PAGESIZE 300
select '!kill -9 '||spid from v$process where addr in (select paddr from v$session where status ='INACTIVE' and [USERNAME <> '']);
查看是哪些主机连接进来的：
col MACHINE for a60;
col USERNAME for a20
set line 900 pagesize 900
select username, machine ,status ,count(*) from v$session group by username, machine ,status order by 4;
------------------------------
[oracle@xxxxx trace]$ tail -30000 listener.log | grep -E -o "HOST=([0-9]{1,3}.){3}[0-9]{1,3}" | sort -n | uniq -c | less
653 HOST=10.xxxx
32 HOST=10.xxxx
402 HOST=10.xxxx
3417 HOST=10.xxxx
1 HOST=10.xxxx
4 HOST=10.xxxx
1 HOST=10.xxxx
2 HOST=10.xxxx
1 HOST=188.xxxx
------------------------------
查看每小时连接数：
fgrep "16-JAN-2024" listener.log |fgrep "establish" | awk '{ print $1" " $2 }' | awk -F: '{ print $1 }' | sort | uniq -c

远程登录数据库

sqlplus usr/pwd@//host:port/sid

查询表空间余量(不显示已满表空间)

select total.TABLESPACE_NAME,
round(total.MB, 2) as TOTAL_MB,
round(total.MB - free.MB, 2) as USED_MB,
ROUND(free.MB, 2) as FREE_mb,
ROUND((1 - free.MB / total.MB) * 100, 2) as "USED_PCT(%)"
from (select a.TABLESPACE_NAME, sum(a.BYTES) / 1024 / 1024 as MB
from sys.dba_data_files a
group by a.TABLESPACE_NAME) total,
(select b.TABLESPACE_NAME,
count(1) as extends,
sum(b.BYTES) / 1024 / 1024 as MB,
sum(b.BLOCKS) as blocks
from sys.dba_free_space b
group by b.TABLESPACE_NAME) free
where total.TABLESPACE_NAME = free.TABLESPACE_NAME
order by "USED_PCT(%)" desc;

查询表空间余量(显示已满表空间)

select a.tablespace_name,round(a.tablespace_size * b.value / 1024 / 1024 / 1024, 2) SIZE_GB,round(a.used_space * b.value / 1024 / 1024 / 1024, 2) USED_GB,round(a.tablespace_size * b.value / 1024 / 1024 / 1024, 2) - round(a.used_space * b.value / 1024 / 1024 / 1024, 2) FREE_GB,ROUND(A.used_percent, 1)|| '%' used_percent from dba_tablespace_usage_metrics a, v$parameter b where b.NAME = 'db_block_size' and ROUND(A.used_percent, 1)>80 order by used_percent DESC;

Oracle各类日志存放位置

— 查询redo日志文件的位置

SELECT member FROM v$logfile;

— 查询archive日志文件的位置

SELECT dest_name FROM v$archive_dest;

— 查询trace日志文件的位置

shwo parameter background_dump_dest

— 查询alert日志文件的位置

SELECT value FROM v$parameter WHERE name=’background_dump_dest’;

RAC查看主备同步是否正常

select * from v$dataguard_stats;

Oracle job卡死

select b.USERNAME,c.JOB,b.sid,b.serial#,spid,d.NEXT_DATE,d.WHAT
from v$process a,v$session b,dba_jobs_running c,dba_jobs d
where a.ADDR= b.paddr
and b.sid= c.SID
and c.JOB =d.JOB
order by next_date;
alter system kill session'$SID,$SERIAL#' immediate;

Oracle操作系统进程相关命令

1. 通过会话SID查看操作系统进程号
select b.spid from v$session a,v$process b where a.PADDR=b.addr and a.SID='520';
2. 根据操作系统进程号查看会话SID
select a.sid,a.SERIAL# from v$session a,v$process b where a.PADDR=b.addr and b.spid='16343';
3. 根据进程号查看会话及SQL信息
要求进程处于ACTIVE状态
select a.spid,b.sid,c.hash_value,substr(c.sql_text, 0, 40),b.logon_time,b.program from v$process a, v$session b, V$SQL c

where a.addr = b.paddr and b.sql_hash_value = c.hash_value and a.spid ='';
4. 构造语句kill进程
select 'kill -9 ' || b.spid from v$session a,v$process b

where a.PADDR=b.addr and a.SID in (520)

Oracle停服务(目前无PDB适用)

stop service：
srvctl stop service -d racdb -s racdbservice -i racdb1
srvctl start service -d bjpaasb -s zz_xhb2 -i bjpaasb2

将SQL信息标准化输出

set pages 9999
set feedback off
set markup html on ------ 也可设置为csv格式方便用WPS打开 set markup csv on
spool mark.html ------ spool mark.csv
-- 需要抓取的sql内容
spool off
set markup html off ------ set markup csv off

查询执行时间大于10S的SQL

执行前修改a.last_LOAD_TIME过滤条件
select *
from (select v.sql_id,
v.sql_text,
v.sql_fulltext,
v.FIRST_LOAD_TIME,
v.last_load_time,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
v.EXECUTIONS,
v.LOADS,
v.cpu_time / v.executions / 1000 / 1000 ave_cpu_time,
v.ELAPSED_TIME / v.executions / 1000 / 1000 ave_time
from v$sql v) a
where a.last_LOAD_TIME > '2015-11-09/09:24:47' and ave_time > 10 and a.executions > 0 order by ave_time desc

如遇到过大文件不能完整导出可选择列导出为单列的TEXT文件。
或可参阅如下文档：
https://blog.csdn.net/tttzzzqqq2018/article/details/132207407

在线添加索引

create index idx on user_info(col1 [, col2]) online tablespace tbs;

如果想加快速度：

create index idx on user_info(col1 [, col2]) online tablespace tbs nologging parallel 8

创建索引后撤销补救措施

declare
isClean boolean;

begin
isClean := FALSE;
while isClean=FALSE loop
isClean := dbms_repair.online_index_clean(1419154,
dbms_repair.lock_wait);
dbms_lock.sleep(2);
end loop;

exception
when others then
RAISE;
end;
/

select event,count(*) from gv$session_wait where wait_class<>'Idle' group by event;

导出SQL Monitor来优化SQL

set trimspool on
set arraysize 512
set trim on
set pagesize 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool test_sql.html
select /*+ noparallel */ dbms_sqltune.report_sql_monitor (sql_id=>'【sql_id】', report_level=>'ALL', type=>'ACTIVE') from dual;
spool off
cat sqlmon.html

集群相关命令

重启集群：
./crsctl stop cluster [-all]
./crsctl start cluster

查询历史等待事件

dba_hist_active_sess_history
set linesize 1000 pagesize 1000
select p1,CURRENT_OBJ#, to_char(sample_time,'yyyymmdd hh24:mi') time ,EVENT,SQL_ID,count(*) from v$active_session_history where sample_time> to_date('20240105 14:42:52','yyyymmdd hh24:mi:ss') and sample_time<to_date('20240105 14:45:52','yyyymmdd hh24:mi:ss') and EVENT like '%enq: TX - row lock contention%' group by p1,CURRENT_OBJ#, to_char(sample_time,'yyyymmdd hh24:mi') ,EVENT,SQL_ID order by 4 ;

查看SGA、PGA使用率

select name,
total,
round(total - free, 2) used,
round(free, 2) free,
round((total - free) / total * 100, 2) pctused
from (select 'SGA' name,
(select sum(value / 1024 / 1024) from v$sga) total,
(select sum(bytes / 1024 / 1024)
from v$sgastat
where name = 'free memory') free
from dual)
union
select name,
total,
round(used, 2) used,
round(total - used, 2) free,
round(used / total * 100, 2) pctused
from (select 'PGA' name,
(select value / 1024 / 1024 total
from v$pgastat
where name = 'aggregate PGA target parameter') total,
(select value / 1024 / 1024 used
from v$pgastat
where name = 'total PGA allocated') used
from dual)
union
select name,
round(total, 2) total,
round((total - free), 2) used,
round(free, 2) free,
round((total - free) / total * 100, 2) pctused
from (select 'Shared pool' name,
(select sum(bytes / 1024 / 1024)
from v$sgastat
where pool = 'shared pool') total,
(select bytes / 1024 / 1024
from v$sgastat
where name = 'free memory'
and pool = 'shared pool') free
from dual)
union
select name,
round(total, 2) total,
round(total - free, 2) used,
round(free, 2) free,
round((total - free) / total, 2) pctused
from (select 'Default pool' name,
(select a.cnum_repl *
(select value
from v$parameter
where name = 'db_block_size') / 1024 / 1024 total
from x$kcbwds a, v$buffer_pool p
where a.set_id = p.LO_SETID
and p.name = 'DEFAULT'
and p.block_size =
(select value
from v$parameter
where name = 'db_block_size')) total,
(select a.anum_repl *
(select value
from v$parameter
where name = 'db_block_size') / 1024 / 1024 free
from x$kcbwds a, v$buffer_pool p
where a.set_id = p.LO_SETID
and p.name = 'DEFAULT'
and p.block_size =
(select value
from v$parameter
where name = 'db_block_size')) free
from dual)
union
select name,
nvl(round(total, 2), 0) total,
nvl(round(total - free, 2), 0) used,
nvl(round(free, 2), 0) free,
nvl(round((total - free) / total, 2), 0) pctused
from (select 'KEEP pool' name,
(select a.cnum_repl *
(select value
from v$parameter
where name = 'db_block_size') / 1024 / 1024 total
from x$kcbwds a, v$buffer_pool p
where a.set_id = p.LO_SETID
and p.name = 'KEEP'
and p.block_size =
(select value
from v$parameter
where name = 'db_block_size')) total,
(select a.anum_repl *
(select value
from v$parameter
where name = 'db_block_size') / 1024 / 1024 free
from x$kcbwds a, v$buffer_pool p
where a.set_id = p.LO_SETID
and p.name = 'KEEP'
and p.block_size =
(select value
from v$parameter
where name = 'db_block_size')) free
from dual)
union
select name,
nvl(round(total, 2), 0) total,
nvl(round(total - free, 2), 0) used,
nvl(round(free, 2), 0) free,
nvl(round((total - free) / total, 2), 0) pctused
from (select 'RECYCLE pool' name,
(select a.cnum_repl *
(select value
from v$parameter
where name = 'db_block_size') / 1024 / 1024 total
from x$kcbwds a, v$buffer_pool p
where a.set_id = p.LO_SETID
and p.name = 'RECYCLE'
and p.block_size =
(select value
from v$parameter
where name = 'db_block_size')) total,
(select a.anum_repl *
(select value
from v$parameter
where name = 'db_block_size') / 1024 / 1024 free
from x$kcbwds a, v$buffer_pool p
where a.set_id = p.LO_SETID
and p.name = 'RECYCLE'
and p.block_size =
(select value
from v$parameter
where name = 'db_block_size')) free
from dual)
union
select name,
nvl(round(total, 2), 0) total,
nvl(round(total - free, 2), 0) used,
nvl(round(free, 2), 0) free,
nvl(round((total - free) / total, 2), 0) pctused
from (select 'DEFAULT 16K buffer cache' name,
(select a.cnum_repl * 16 / 1024 total
from x$kcbwds a, v$buffer_pool p
where a.set_id = p.LO_SETID
and p.name = 'DEFAULT'
and p.block_size = 16384) total,
(select a.anum_repl * 16 / 1024 free
from x$kcbwds a, v$buffer_pool p
where a.set_id = p.LO_SETID
and p.name = 'DEFAULT'
and p.block_size = 16384) free
from dual)
union
select name,
nvl(round(total, 2), 0) total,
nvl(round(total - free, 2), 0) used,
nvl(round(free, 2), 0) free,
nvl(round((total - free) / total, 2), 0) pctused
from (select 'DEFAULT 32K buffer cache' name,
(select a.cnum_repl * 32 / 1024 total
from x$kcbwds a, v$buffer_pool p
where a.set_id = p.LO_SETID
and p.name = 'DEFAULT'
and p.block_size = 32768) total,
(select a.anum_repl * 32 / 1024 free
from x$kcbwds a, v$buffer_pool p
where a.set_id = p.LO_SETID
and p.name = 'DEFAULT'
and p.block_size = 32768) free
from dual)
union
select name,
total,
total - free used,
free,
(total - free) / total * 100 pctused
from (select 'Java Pool' name,
(select sum(bytes / 1024 / 1024) total
from v$sgastat
where pool = 'java pool'
group by pool) total,
(select bytes / 1024 / 1024 free
from v$sgastat
where pool = 'java pool'
and name = 'free memory') free
from dual)
union
select name,
Round(total, 2),
round(total - free, 2) used,
round(free, 2) free,
round((total - free) / total * 100, 2) pctused
from (select 'Large Pool' name,
(select sum(bytes / 1024 / 1024) total
from v$sgastat
where pool = 'large pool'
group by pool) total,
(select bytes / 1024 / 1024 free
from v$sgastat
where pool = 'large pool'
and name = 'free memory') free
from dual)
order by pctused desc;