日常运维

最新推荐文章于 2023-01-18 21:57:37 发布

weixin_30693683

最新推荐文章于 2023-01-18 21:57:37 发布

阅读量69

点赞数

文章标签：数据库 awk 运维

原文链接：http://www.cnblogs.com/bondait/p/9717220.html

版权

#####连接数
TYXXTEST

查看活跃会话
SET LINE 1000 PAGES 1000
col username for a20
col machine for a20
col event for a30
col MODULE for a15
col p1 for 999999999999999
select INST_ID,sid,serial#,username, machine,status,MODULE,last_call_et,event,sql_id--,p1,p2,p3
from gv$session
where username is not null
and status='ACTIVE'
and inst_id=2
--and sql_id='7xr2ntsfzqcs8'
order by 1,4,8,7,5;

SET LINE 1000 PAGES 1000
col username for a20
col machine for a20
col event for a30
col MODULE for a15
col p1 for 999999999999999
select INST_ID,sid,serial#,username, machine,status,MODULE,last_call_et,event--,p1,p2,p3
from gv$session
where username ='SYS'
and status='ACTIVE'
order by 1,4,8,7,5;

节点最大进程
show parameter process

##连接数
select INST_ID,count(*)
--sid,serial#,username, machine,status,MODULE,last_call_et,event--,p1,p2,p3
from gv$session
where username is not null group by INST_ID;

INST_ID COUNT(*)--SID,SERIAL#,USERNAME,MACHINE,STATUS,MODULE,LAST_CALL_ET,EVENT--,P1,P2,P3
---------- ----------------------------------------------------------------------------------
1 274
2 112

##活跃数
select INST_ID,count(*)
--sid,serial#,username, machine,status,MODULE,last_call_et,event--,p1,p2,p3
from gv$session
where username is not null and status='ACTIVE' group by INST_ID;

INST_ID COUNT(*)--SID,SERIAL#,USERNAME,MACHINE,STATUS,MODULE,LAST_CALL_ET,EVENT--,P1,P2,P3
---------- ----------------------------------------------------------------------------------
1 7
2 6

##历史最大连接数
set linesize 400
select resource_name,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name in ('processes','sessions');
select * from v$resource_limit where resource_name in ('processes','sessions');

##查看数据库创建时间
select name,CREATED from v$database;

##查询某个进程对应的会话
select s.sid,s.sql_id,s.event,s.blocking_session from v$session s ,v$process p where s.paddr=p.addr and p.spid=&pid;

##查询实例的历史启动时间
select dbid,INSTANCE_NUMBER,STARTUP_TIME,DB_NAME from DBA_HIST_DATABASE_INSTANCE order by 3;

有一个导入的进度需要关注，需要从4A核心库使用telnet到10.161.1.147主机，然后使用ssh登陆到10.161.144.21主机上，
上面是12.2多组户模式的数据库，导入数据的pdb是crmpdb，1.147的oracle口令是User！234，144.21主机oracle口令是oeacle123

User!234

##通过sqid获取执行计划
select * from table(dbms_xplan.display_awr('02gbv93w8y4tt'));
select * from table(dbms_xplan.display_cursor('02gbv93w8y4tt',null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
BAL_RETURNACCTBOOK_INFO
##查询那个用户执行的SQL_ID
select user_id from v$active_session_history where sql_id='&1' and rownum<=10;
select username from dba_users where user_id='134';

##杀死LOCAL=NO进程
ps -ef | grep LOCAL=NO | grep sid|grep -v grep | awk '{print $2}' |xargs kill -9
ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}' |xargs kill -9

##杀进程
##查看阻塞会话
select 'This Session' || ' ' || a.sid || ',' || a.serial# ||
' is blocked by ' || a.BLOCKING_SESSION
from v$session a
where a.BLOCKING_SESSION is not null;

##查看sid对应的服务器进程
select spid from v$process a,v$session b where a.addr=b.paddr and b.sid=409;
select sid from v$process a,v$session b where a.addr=b.paddr and a.spid=409;

##操作系统层面杀掉进程
确认local=no
[oracle@bonda ~]$ ps -ef |grep 1905|grep -v grep
oracle 1905 1861 0 04:45 ? 00:00:01 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

##查询CPU较高的语句
select * from (select sql_id,cpu_time from v$sql order by cpu_time desc) where rownum<=10 order by rownum asc ;
select * from (select sql_text,sql_id,cpu_time from v$sql order by cpu_time desc) where rownum<=10 order by rownum asc ;

##查询会话中持有锁的对象的会话
set lines 200 pages 2000
col OBJECT_NAME for a25
col USERNAME for a12
col OSUSER for a12
col MACHINE for a12
col PROGRAM for a12
col owner for a20
select
sess.sid,
sess.serial#,
lo.process,
lo.locked_mode,
ao.owner,
ao.object_name,
sess.USERNAME,
sess.OSUSER,
sess.MACHINE,
sess.PROGRAM
from v$locked_object lo, dba_objects ao, v$session sess
where ao.object_id = lo.object_id
and lo.session_id = sess.sid;

##杀会话
alter system kill session 'sid,serial#';
select 'alter system kill session '||chr(39)||sid||','||serial#||chr(39)||';' from v$session where sid in (select blocking_session from v$session where event ='enq: TX - row lock contention');

##查询等待事件
set linesize 320
set pagesize 2000
col event for a60
select sid,event,--p1,p2,p3,
WAIT_TIME,SECONDS_IN_WAIT from V$session where event not like 'SQL%' and event not like 'rdbms%';
select event,count(*) from v$session group by event order by 2;

##OSWATCH目录
/oracle/tools/oswbb/
##启动OSWATCH
/oracle/tools/oswbb/startOSWbb.sh
##关闭OSWATCH
/oracle/tools/oswbb/stopOSWbb.sh
##OSWATCH计划任务
0,12 * * * * cd /oracle/tools/oswbb; nohup ./startOSWbb.sh 15 192 gzip 1>/oracle/tools/oswbb/startOSWbb.log 2>&1 &
##OSWATCH监控文件目录
/oracle/tools/oswbb/archive

##查询没有使用绑定变量的SQL
用下列语句来找出可以没有使用bind value的sql语句：
select FORCE_MATCHING_SIGNATURE, count(1)
from v$sql
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) >1000
order by 2 desc;

##查询执行计划
select * from table(dbms_xplan.display_cursor('fvg6c263u41uy',0));
select * from table(dbms_xplan.display_awr('b9c2srg04qhfx'));
fvg6c263u41uy

##根据SID获取完成SQL
set long 9999
select sql_fulltext from v$sqlarea where sql_id='&1';

##12c系统用户
AUDSYS Y
SYSBACKUP Y
SYSDG Y
SYSKM Y
OUTLN Y
XS$NULL Y
GSMADMIN_INTERNAL Y
GSMUSER Y
DIP Y
ORACLE_OCM Y
APPQOSSYS Y
XDB Y
ANONYMOUS Y
GSMCATUSER Y
WMSYS Y
OJVMSYS Y
OLAPSYS Y

##查询表上的DML操作
select INSERTS,UPDATES,DELETES,TIMESTAMP,TRUNCATED from dba_tab_modifications where table_name='TF_SO_CUST_HIS';

##删除更改时间在一个月前的文件
find /oracle/app/12.1.0/grid/rdbms/audit -mtime +5 |grep "\.aud"|xargs rm -f

##查看CPU的报警
sar 1 1|tail -1|awk '{print $5}'

##采集历史会话的状态
select * from dba_hist_active_sess_history
where
sample_time between to_timestamp ('2018-08-06 09:00:00', 'yyyy-mm-dd hh24:mi:ss')
and to_timestamp ('2018-08-06 10:00:00', 'yyyy-mm-dd hh24:mi:ss');

TEMP=/tmp
TMPDIR=/tmp
export TEMP TMPDIR
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/12.1.0
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/sbin:/usr/local/bin:$PATH
umask 022
export ORACLE_SID=TYXXPTDB2

/oracle/app/oracle/12.1.0/bin/rman target / <<EOF
delete noprompt force archivelog all completed before 'sysdate-4';
exit;
EOF
echo "`date +%Y-%m-%d_%H:%M:%S` delete archivelog finished"

##查看历史备份的状态
set line 200
col START_TIME for a30
col END_TIME for a30
select SESSION_KEY,
INPUT_TYPE,
STATUS,
to_char(START_TIME, 'yyyy-mm-dd hh24:mi') START_TIME,
to_char(END_TIME, 'yyyy-mm-dd hh24:mi') END_TIME,
ELAPSED_SECONDS / 3600
from v$rman_backup_job_details
where to_char(START_TIME, 'yyyy-mm-dd hh24:mi') > to_char(sysdate-4,'yyyy-mm-dd hh24:mi')
order by SESSION_KEY;

##查看RMAN备份的进度
set line 200 pages 1000
col MESSAGE for a60
col TARGET for a20
select sid,SERIAL#,TARGET,START_TIME,ELAPSED_SECONDS,TIME_REMAINING,MESSAGE,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
from v$session_longops
where 1=1 --and sid=2983 and SERIAL#=5
and TIME_REMAINING>0 ;

转载于:https://www.cnblogs.com/bondait/p/9717220.html

weixin_30693683

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
日常运维

#####连接数TYXXTEST查看活跃会话SET LINE 1000 PAGES 1000col username for a20col machine for a20col event for a30col MODULE for a15col p1 for 999999999999999select INST_ID,sid,serial#,username, mac...
复制链接

扫一扫