1.---------------------------------- hang分析
sqlplus -prelim / as sysdba
<==当数据库已经很慢或者hang到无法连接
oradebug setmypid
oradebug unlimit;
oradebug -g all dump systemstate 266;
等1~2分钟
oradebug -g all dump systemstate 266;
oradebug tracefile_name;
另一个session 进行 hang analyze
oradebug setmypid
oradebug setinst all;
oradebug unlimit
oradebug -g all hanganalyze 3
等1~2分钟
oradebug -g all hanganalyze 3
oradebug tracefile_name;
-----------
3，prelim 无法登录后，使用 gdb 调试
$ ps -ef|grep pmon
oracle 28288 10 04:42 ?00:00:00 ora_pmon_R11202
$ gdb $ORACLE_HOME/bin/oracle 28288
然后查看这个进程的trace文件：
$ more R11202_pmon_28288.trc
--------------------------------------------------------------------备份信息
create table ash1031 as select * from gv$active_session_history g where g.SAMPLE_TIME>sysdate-1/24;
create table opencursor1031 as select * from gv$open_cursor ;
create table session1031 as select * from gv$session ;
create table sql1031 as select * from gv$sql ;
--------锁表
select b.username,b.sid,b.serial#,logon_time,a.LOCKED_MODE,d.OBJECT_NAME from v$locked_object a , v$session b,dba_objects d
where a.session_id = b.sid
and a.OBJECT_ID=d.OBJECT_ID
order by b.LOGON_TIME;
---------------------查看慢SQL
with m as (select * from ( select inst_id, PARSING_SCHEMA_NAME, sql_id, last_active_time,sum(t.EXECUTIONS), round(sum(t.ELAPSED_TIME)/1e6)||'s' ELAPSED_TIME,
round(sum(t.ROWS_PROCESSED)/decode(sum(t.EXECUTIONS),0,1,sum(t.EXECUTIONS))) avg_rows,
round(sum(t.ELAPSED_TIME)/decode(sum(t.EXECUTIONS),0,1,sum(t.EXECUTIONS))/1e3,2) avg_ms, round(ratio_to_report(sum(t.ELAPSED_TIME)) over(),4)*100||'%' as time_ratio
from gv$sql t where LAST_ACTIVE_TIME >sysdate -1/24 and PARSING_SCHEMA_NAME not in ('SYS')
group by inst_id,PARSING_SCHEMA_NAME,sql_id , last_active_time
order by sum(t.ELAPSED_TIME) desc ) where rownum<20
),
l as ( select inst_id, sql_id, sql_fulltext, row_number () over (partition by m.inst_id, m.sql_id order by (m.sql_id ) ) row1 from gv$sql m
where PARSING_SCHEMA_NAME not in ('SYS') and LAST_ACTIVE_TIME >sysdate -1/24
)
select m.*, sql_fulltext from l ,m where l.sql_id = m.sql_id and l.inst_id = m.inst_id
and row1=1 order by time_ratio;
----------------------------------查看行锁
select 'blocker('||lb.sid||':'||sb.username||')-sql:'|| qb.sql_text blockers,
'waiter ('||lw.sid||':'||sw.username||')-sql:'|| qw.sql_text waiters
from v$lock lb,
v$lock lw,
v$session sb,
v$session sw,
v$sql qb,
v$sql qw
where lb.sid=sb.sid
and lw.sid=sw.sid
and sb.prev_sql_addr=qb.address
and sw.sql_address=qw.address
and lb.id1=lw.id1
and sw.lockwait is not null
and sb.lockwait is null
and lb.block=1;
------------------------------查慢 sql
select a.sql_id,trunc(a.elapsed_time/a.executions) avg_us from v$sqlarea a,
(select sql_id,trunc(sum(b.elapsed_time_total)/sum(b.executions_total)) avg_us from dba_hist_sqlstat b
where b.snap_id=(select max(snap_id) from dba_hist_sqlstat c where b.sql_id=c.sql_id)
and b.parsing_schema_name not in ('XS$NULL','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','ORACLE_OCM','MDDATA','DIP','SPATIAL_WFS_ADMIN_USR','DBSNMP','FLOWS_FILES','MDSYS','WMSYS','ORDDATA','CTXSYS','ANONYMOUS','SI_INFORMTN_SCHEMA','ORDSYS','EXFSYS','APPQOSSYS','APEX_030200','OWBSYS_AUDIT','SYSMAN','XDB','ORDPLUGINS','OWBSYS','OLAPSYS','SYS','SYSTEM','OUTLN','MGMT_VIEW','SCOTT','TSMSYS')
and b.executions_total>10 group by sql_id) d
where a.sql_id=d.sql_id and a.EXECUTIONS>10
and a.elapsed_time/a.executions>10*d.avg_us
and a.elapsed_time/a.executions>100000 and last_active_time >sysdate - 1/24/8
----------------重建索引
sqlplus / as sysdba <<EOF
alter session set workarea_size_policy=manual;
alter session set sort_area_size=1073741824;
alter index xxxx.xxxxxxx_PKP rebuild parallel 8;
exit;
EOF
---------------------count数据
sqlplus / as sysdba <<EOF
set pagesize 0 linesize 2000
set term off verify off feedback off headsep off
exit;
EOF
----------------------------------------分区表补丁
A.应用补丁，请使用如下步骤
===
1, Go https://updates.oracle.com/download/19614585.html
2, Select a Release: Oracle 11.2.0.4.200414, Platform or Language: Linux x86-64
3, Download & follow "View Readme" to install
B.您也可以尝试如下的方法，二者均可
===
1.清除shared pool : alter system flush shared_pool
2.设置参数 : alter system set "_part_access_version_by_number" =false scope=spfile sid='*'; 重启实例
--------------------------------------------oracle 从库hang住补丁
https://updates.oracle.com/download/19684789.html
Select a Release ==> Oracle 11.2.0.4.180717
$ opatch apply online -connectString <SID_Node1>:<Username_Node1>:<Password_Node1>:<Node1_Name>,<SID_Node2>:<Username_Node2>:<Password_Node2>:<Node2_Name>,<SID_NodeN>:<Username_NodeN>:<Password_NodeN>:<NodeN_Name>
$ORACLE_HOME/OPatch/opatch apply online -connectString ORCL:xxxx:"xxxxxxx":
-------------------加磁盘指定大小
ALTER DISKGROUP DATAC1 ADD DISK '/dev/asm-DATA_51' SIZE 2097152M
DISK '/dev/asm-DATA_52' SIZE 2097152M
DISK '/dev/asm-DATA_53' SIZE 2097152M
DISK '/dev/asm-DATA_54' SIZE 2097152M
DISK '/dev/asm-DATA_55' SIZE 2097152M;
-----------------kill远程连接
ps -ef |grep ora| grep LOCAL=NO | grep ORCL |grep -v grep |awk '{print $2}' | xargs kill -9
-----------------导出excel
vi tmain.sql
set linesize 200
set term off verify off feedback off pagesize 999
set markup html on entmap ON spool on preformat off
spool test_tables.xls
@get_tables.sql
spool off
exit
@tmain.sql
