归档日志满了
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;View Code 标签:1024,name,DBA,free,Oracle,告警,total,where,select From: https://www.cnblogs.com/guapixiong/p/17993801