日常運維 #資料庫 #awk #運維

#####連接配接數

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

[[email protected] ~]$ 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

##建立函數索引

create index sname_index on t1(upper(sname)) tablespace MYSPACE;

##檢視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

日常運維

繼續閱讀

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

httpd服務的部署、啟動、配置和簡單優化一、部署二、啟動三、配置檔案

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

nginx 安裝錯誤資訊解決

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

Ambari介紹和架構原理

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述