oracle 查询统计
dstat可以动态查看io等性能
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
0 0 99 0 0 0| 47k 1479k| 0 0 | 0 0 |3302 4815
0 0 100 0 0 0|3072B 102k| 200k 232k| 0 0 |4486 7721
0 0 100 0 0 0|4096B 978k|2232B 2150B| 0 0 |4219 7472
0 0 100 0 0 0|3072B 486k| 13k 14k| 0 0 |4338 7617
0 0 100 0 0 0|3072B 258k| 981B 1107B| 0 0 |4902 8246
0 0 99 0 0 0| 806k 278k| 31k 228k| 0 0 |5271 8770
1 1 98 0 0 0| 64k 122k|4177B 27k| 0 0 | 11k 15k
0 0 100 0 0 0|4608B 98k| 931B 1457B| 0 0 |5126 9573
1.是否锁定统计信息
select owner,table_name from dba_tab_statistics where statype_locked='ALL';
2.rowid算出文件号号块行号和object_id
select dbms_rowid.rowid_object(rowid) object_id
,dbms_rowid.rowid_relative_fno(rowid) file_id
,dbms_rowid.rowid_block_number(rowid) block_id
,dbms_rowid.rowid_row_number(rowid) row_number
from emp;
3.文件号块号反查出segment_name
select segment_name from dba_extents where file_id=7 and 1158 between block_id and block_id+blocks-1;
file_id=7
1158是块号
4.dump导出7号文件1158号块sql
alter system dump datafile 7 block 1158;
5.v$process和v$session 关联
select spid from v$process where addr in (select paddr from v$session where sid=1051);
6.找出dump文件路径
select value from v$diag_info where name='Default Trace File';
block_row_dump:
tab 0, row 0, @0x1f2d
tl: 83 fb: --H-FL-- lb: 0x0 cc: 6
col 0: [ 3] c2 64 0a
col 1: [ 3] 72 65 64
col 2: [ 8] 63 79 6c 69 6e 64 65 72
col 3: [ 3] c2 07 42
col 4: [ 7] 78 79 02 11 0c 01 01
col 5: [50]
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 35 34
tab 0, row 1, @0x1edd
tl: 80 fb: --H-FL-- lb: 0x0 cc: 6
col 0: [ 3] c2 64 14
col 1: [ 3] 72 65 64
col 2: [ 5] 70 72 69 73 6d
col 3: [ 3] c2 02 08
col 4: [ 7] 78 79 02 11 0c 29 01
col 5: [50]
col 0: [ 3] c2 64 14 取出来，转换成实际
select replace('70 72 69 73 6d',' ','') from dual;
REPLACE('78790
--------------
707269736d
select utl_raw.cast_to_varchar2('707269736d') from dual;这里只支持varchar2
UTL_RAW.CAST_TO_VARCHAR2('707269736D')
--------------------------------------------------------------------------------
prism
7.加密后效果
执行加密：
alter table C##HRZ.BRICKS modify(servnumber encrypt using 'AES128' NO SALT);
重开一个窗口
alter system dump datafile 7 block 1158;
把trace文件在转出tab 0, row 30, @0x13a9tl: 111 fb: --H-FL-- lb: 0x2 cc: col 0: [ 3] c2 64 2f
col 1: [ 3] 72 65 64
col 2: [36]
e1 f2 f0 a3 85 76 0c 64 59 be 17 fa 38 c6 17 54 44 4d b7 ff 3f 76 4d fc 7b
31 05 b0 b3 46 f0 96 8f 8c d1 7f
col 3: [ 3] c2 04 23
col 4: [ 7] 78 79 02 12 11 29 01
col 5: [50]
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 35 35
tab 0, row 31, @0x13a0
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x01c0020c.19
tab 0, row 32, @0x1331
tl: 111 fb: --H-FL-- lb: 0x2 cc: 6
col 0: [ 3] c2 64 31
col 1: [ 3] 72 65 64
select replace('e1 f2 f0 a3 85 76 0c 64 59 be 17 fa 38 c6 17 54 44 4d b7 ff 3f 76 4d fc 7b 31 05 b0 b3 46 f0 96 8f 8c d1 7f',' ','') from dual;
REPLACE('E1F2F0A385760C6459BE17FA38C61754444DB7FF3F764DFC7B3105B0B346F09
------------------------------------------------------------------------
e1f2f0a385760c6459be17fa38c61754444db7ff3f764dfc7b3105b0b346f0968f8cd17f
select utl_raw.cast_to_varchar2('e1f2f0a385760c6459be17fa38c61754444db7ff3f764dfc7b3105b0b346f0968f8cd17f') from dual;
UTL_RAW.CAST_TO_VARCHAR2('E1F2F0A385760C6459BE17FA38C61754444DB7FF3F764DFC7B3105
--------------------------------------------------------------------------------
???v
dY??8?TDM???vM?{1??F??
加密之后转出来是乱码了
开启WALLET
Oracle数据库加密表空间详解及操作过程 - 墨天轮 (modb.pro)
临时log
SELECT s.username, s.SID, u.TABLESPACE, u.CONTENTS, u.segtype,
ROUND (u.blocks * 8192 / 1024 / 1024, 2) mb
FROM v$session s, v$tempseg_usage u
WHERE s.saddr = u.session_addr AND u.CONTENTS = 'TEMPORARY'
ORDER BY mb DESC;
//##查看RMAN备份进度和进程19c
#!/bin/bash
export ORACLE_BASE=/home/db/oracle
export GRID_HOME=/home/db/grid/product/19.3.0
export ORACLE_HOME=/home/db/oracle/product/19.3.0
export PATH=$ORACLE_HOME/bin:$GRID_HOME/bin:$PATH:$ORACLE_HOME/OPatch
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE/rdbms/jlib:$ORACLE_HOME/network/jlib
export ORACLE_SID=CNN000042
export Today=`date '+%Y%m%d%H%M%S'`
rman target / log /home/db/oracle/rman/log/fullback_$Today.log <<EOF
run{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
backup section size 100G database root format '/home/db/oracle/rman/cnn00004_root/full_%d_%I_%T_%U.bk' tag='root_backup';
backup section size 100G datafile 2,4,6,8 format '/home/db/oracle/rman/cnn00004_seed/full_%d_%I_%T_%U.bk' tag='seed_backup';
backup section size 100G pluggable database cnn00004_01 format '/home/db/oracle/rman/cnn00004_01/full_%d_%I_%T_%U.bk' tag='cnn00004_01_backup';
###backup section size 100G pluggable database cnn00004_02 format '/home/db/oracle/rman/cnn00004_02/full_%d_%I_%T_%U.bk' tag='cnn00004_02_backup';
###backup section size 100G pluggable database cnn00004_03 format '/home/db/oracle/rman/cnn00004_03/full_%d_%I_%T_%U.bk' tag='cnn00004_03_backup';
### backup archivelog all not backed up 1 times 说明 - 备份过一次及以上的归档均不备份
backup archivelog all not backed up 1 times format '/home/db/oracle/rman/cnn00004_arch/arch_%d_%I_%T_%U.bk';
backup current controlfile format '/home/db/oracle/rman/cnn00004_control/c_%T_%t.bk';
backup spfile format '/home/db/oracle/rman/cnn00004_spfile/s_%T_%t.bk';
crosscheck backup;
crosscheck archivelog all;
delete noprompt expired backup of controlfile;
delete noprompt obsolete redundancy 3;
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
}
EOF
//##11gRMAN备份
run{
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
ALLOCATE CHANNEL ch00 TYPE DISK;
ALLOCATE CHANNEL ch01 TYPE DISK;
backup format '/mnt/backupfile/ora_full_%T_%t_%U' full database;
crosscheck backupset;
delete noprompt expired backup;
delete noprompt obsolete DEVICE TYPE DISK;
sql 'alter system archive log curent';
backup format '/mnt/backupfile/ora_arch_%T_%t_%U' archivelog all;
release channel ch00;
release channel ch01;
}
//##查看RMAN运行情况
select sid,SERIAL#,CONTEXT,SOFAR,TOTALWORK,round(sofar/totalwork*100,2) "%_complete" from v$session_longops where opname like '%RMAN%' and totalwork !=0;rman运行进度
select sid,spid,client_info from v$process p,v$session s where p.addr=s.paddr and client_info like '%rman%';rman运行进程id
select to_char(start_time,'YYYYMMDD-HH24MISS'),to_char(end_time),input_bytes,status from v$rman_backup_job_details;查看rman历史运行情况
//##杀RMAN会话
crsctl start crs -exc1 -nocrs
//归档使用率
select * from v$flash_recovery_area_usage;单实例快速恢复区
Delete archivelog until time 'sysdate-1/288'; 1/288=1/24/12=5分钟
Delete archivelog until time 'sysdate-1/480'; 1/480=1/24/20=3分钟
delete archivelog until time 'sysdate-1/720'; 1/720=1/24/30=2分钟
delete archivelog until time 'sysdate-1/1440';1/1440=1/24/60=1分钟
delete force archivelog until time ’sysdate-4/24‘； 4/24就是4小时 //不让删除则加force
//如何查看备份数据量
select input_bytes,output_bytes from v$rman_backup_job_details;
//查看ap的ip
select distinct machine from v$session;
linux:
netstat -tuln 看foreign address
//KILL SESSION 方式快速回滚
select xid,usn,state,pid,undoblocksdone,undoblockstotal,parentusn from v$fast_start_transactions;
//如何判断一个表中是否有重复值
select count(*),col1,col2 from C##HRZ.BRICKS group by col1,col2 having count(*) >=2
topevent看直方图--》看log file sync输出：MS1 MS2 MS4 MS8 MS16 MS32 MS64 MS128
MS128代表128微妙（10的负6次方秒）
MS1代表1微秒（10的负6次方秒）
//##logfilesync时间查看
select inst_id,event,wait_time_milli,wait_count,last_update_time from gv$event_histogram where lower(event)='log file sync' order by LAST_UPDATE_TIME;
select instance_number,event_name,wait_time_milli,wait_count,snap_id from dba_hist_event_histogram where lower(event_name)='log file sync' and rownum<10 order by snap_id;
v$system_event -----V$SYSTEM_EVENT displays information on total waits for an event. Note that the TIME_WAITED and AVERAGE_WAIT columns will contain a value of zero on those platforms that do not support a fast timing mechanism. If you are running on one of these platforms and you want this column to reflect true wait times, then you must set TIMED_STATISTICS to TRUE in the parameter file; doing this will have a small negative effect on system performance.
V$ACTIVE_SESSION_HISTORY
ash看time_waited
SELECT TO_CHAR(sample_time,'yyyymmdd hh24:mi:ss.ff'),
time_waited
from v$active_session_history
where event='log file sync'
and rownum<10;
time_waited是mi croseconds是10的负6次方秒
//*一分钟求*均logfilesync
mos原语句
col minute for a12 tru
col event for a30 tru
col program for a40 tru
col total_wait_time for 999999999.999
col avg_time_waited for 999999999.999
select to_char(sample_time,'Mondd_hh24mi') minute,inst_id,event,
sum(time_waited)/1000 TOTAL_WAIT_TIME,count(*) WAITS,
avg(time_waited)/1000 AVG_TIME_WAITED
from gv$active_session_history
where event = 'log file sync'
group by to_char(sample_time,'Mondd_hh24mi'),inst_id,event
having avg(time_waited)/1000>&&threshold
order by 1,2;
*1分钟求*均
可用
select to_char(sample_time,'YYYYMMDDHH24MI') minute,
sum(time_waited)/1000 TOTAL_WAIT_TIME,count(*) WAITS,
avg(time_waited)/1000 AVG_TIME_WAITED
from gv$active_session_history
where event = 'log file sync'
and to_char(sample_time,'YYYYMMDDHH24MI')>'202303200110'
AND to_char(sample_time,'YYYYMMDDHH24MI')<'202303200111'
group by to_char(sample_time,'YYYYMMDDHH24MI');
log+cpu
select sum(decode(event,'log file sync',time_waited_micro,0)) lfs_waited
,sum(decode(event,'log file parllel',time_waited_micro,0)) lfpw_waited
,sum(decode(event,'db file sequential read',time_waited_micro,0)) seqr_waited
,sum(decode(event,'log file sync',time_waited,0)) lfs_waits
,sum(decode(event,'log file parllel',time_waited,0)) lfpw_waites
,sum(decode(event,'db file sequential read',time_waited,0)) seqr_waits
from v$system_event;
//##历史视图dba_hist_system_event
plsql改写，一秒一次求差值
select event,time_waited_micro mus,time_waited,average_wait from v$system_event where event='log file sync';
s求一分钟*均，加入除以total_waits
加带分母zero判断
set serveroutput on
declare
v_lfs1 number;
v_ttw1 number;
v_lfs2 number;
v_ttw2 number;
v_d number;
v_ex number;
begin
select time_waited_micro,total_waits into v_lfs1,v_ttw1 from v$system_event where event='log file sync';
dbms_lock.sleep(5);//改成10就是10秒钟。实测和ash及grafana结果一致。
select time_waited_micro,total_waits into v_lfs2,v_ttw2 from v$system_event where event='log file sync';
v_ex :=v_ttw2-v_ttw1;
if v_ex=0 then
dbms_output.put_line('zero');
else
v_d :=(v_lfs2-v_lfs1)/1000/(v_ex);
dbms_output.put_line('v_lfs1'||v_lfs1||'v_lfs2'||'v_lfs2'||'lfs_waited'||v_d);
end if;
end;
/
//表碎片率
with blocksize as (select value blocksize from v$parameter where name='db_block_size')
select a.owner||'.'||a.table_name name
,partition_name part_name
,round(100*(1-num_rows*avg_row_len/b.blocksize/decode(blocks,0,1,blocks))) fragment_pct
,object_type
,num_rows
,blocks
,last_analyzed
,stattype_locked
,stale_stats
from dba_tab_statistics a,blocksize b
WHERE
a.table_name='BRICKS'
and a.owner='C##HRZ';
DECODE(a,b,c,b/a)
参数意义：
当a=b时，输出c，否则输出b/a
decode(status,'OPEN','正常','异常')
select blocks from dba_tab_statistics where table_name='BRICKS' and owner='C##HRZ';
exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'BRICKS',cascade=>true,degree=>8,force=>TRUE);
//查看索引要看索引碎片率（就像表要看高水位）---逻辑读时高时低，且无索引失效，可能是碎片率高
看碎片率
dailycheck 里查索引碎片语句，查的是top20
条件是先排序后分页
order by fragment_pct)
where rownum<21
with seg as (select owner,segment_name||decode(partition_name,null,null,'.'||partition_name)segment_name,blocks,bytes segsize
from dba_segments
where owner not in
('SYSTEM','SYS','OUTLN','ORACLE_OCM','DBSNMP','APPQOSSYS','WMSYS','DIP','TSMSYS',
'EXFSYS','XDB','ANONYMOUS','MDSYS','ORDPLUGINS','ORDSYS','SI_INFORMTN_SCHEMA',
'SYSMAN','MGMT_VIEW','XS$NULL','ORDDATA','FLOWS_FILES','DMSYS','CTXSYS','OLAPSYS',
'MDDATA','SCOTT')
and segment_type like 'INDEX%'
and segment_name not like 'BIN$%==$0'
and bytes/1024/1024 > 8)
select * from (
select b.owner||'.'||b.index_name name
,round(100*(1-b.leaf_blocks/a.blocks)) fragment_pct
,leaf_blocks
,distinct_keys
,num_rows
,last_analyzed
from seg a,
(select owner,index_name,leaf_blocks,distinct_keys,num_rows,last_analyzed,initial_extent
from dba_indexes) b
where a.owner=b.owner
and a.segment_name=b.index_name
and a.segsize>b.initial_extent
union all
select b.owner||'.'||b.index_name name
,round(100*(1-b.leaf_blocks/a.blocks)) fragment_pct
,leaf_blocks
,distinct_keys
,num_rows
,last_analyzed
from seg a,
(select index_owner owner,index_name||'.'||partition_name index_name
,leaf_blocks,distinct_keys,num_rows,last_analyzed,initial_extent
from dba_ind_partitions) b
where a.owner=b.owner
and a.segment_name=b.index_name
and a.segsize>b.initial_extent
order by fragment_pct)
where rownum < 21;
//每个会话的Oracle数据库用户和当前用户全局区域（UGA）内存使用情况
SELECT USERNAME, VALUE || 'bytes' "Current UGA memory"
FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name
WHERE sess.SID = stat.SID
AND stat.STATISTIC# = name.STATISTIC#
AND name.NAME = 'session uga memory';
//##查看sql历史执行计划
SET PAGESIZE 10000
SET LINE 300COL EVENT FOR A30select TO_CHAR(SAMPLE_TIME,'YYYY-MM-DD HH24:MI:SS') TIME,SQL_ID,EVENT,USER_ID FROM dba_hist_active_sess_history WHERE SQL_ID='2d1p0p5k3f8fu' ORDER BY 1 desc;alter session set tracefile_identifier='10053';
alter session set events='10053 trace name context forever,level 1';select count(*) from v$rman_status;alter session set events '10053 trace name context off';select value from v$diag_info where name='Default Trace File';grid@cjc-db-02:/home/grid$ ocrcheckgrid@cjc-db-02:/home/grid$ crsctl query css ocrdiskselect /*+rule*/ b.owner||'.'||b.index_name name
,round(100*(1-b.leaf_blocks/a.blocks)) fragment_pct
,b.leaf_blocks
,b.distinct_keys
,b.num_rows
,b.last_analyzed
from dba_segments a,dba_indexes b
where a.owner=''
and a.segment_name=''
and a.index_name='';
//online 建立索引取消
再次创建索引报ORA-00955错误，ORA-00095：name is already used by an existing object.
DBMS_REPAIR.ONLINE_INDEX_CLEAN执行成功，但报错依然
select dbms_repair.online_index_clean(<problem index object_id>) from dual;
检查dba_objects,SYS_*的表和索引仍然存在。
DBMS_REPAIR.ONLINE_INDEX_CLEAN(DBMS_REPAIR.ALL_INDEX_ID)
如果在出现问题的对象的数据库活动不能停下来，则如下的PL/SQL block来处理
注：加上dbms_repair.lock_wait表示不是立刻清理，需要不断的寻找资源锁，直到抢到为止
declare
isClean boolean;
begin
isClean := FALSE;
while isClean=FALSE loop
isClean := dbms_repair.online_index_clean(dbms_repair.all_index_id,
dbms_repair.lock_wait);
dbms_lock.sleep(2);
end loop;
exception
when others then
RAISE;
end;
/
http://blog.itpub.net/28218939/viewspace-2638333/原文档
//purge_cursor不行的话加noparallelddl取消表的并行度
alter table tablename noparallel;
//v$session 和v$sql关联查询 看哪个ap上来的
//加上等待事件
select a.SID,
a.SERIAL#,
a.TYPE,
a.BLOCKING_SESSION,
a.EVENT,
a.STATE,
a.MACHINE,
b.SQL_ID,
b.SQL_TEXT,
b.PLAN_HASH_VALUE
from v$session a,v$sql b
where a.SQL_ID=B.SQL_ID
and b.SQL_ID=''
AND b.SQL_TEXT LIKE '%%'
AND b.plan_hash_value='';
V$SQL和v$active_session_history关联查询
select a.SID,
a.SERIAL#,
a.TYPE,
a.BLOCKING_SESSION,
a.EVENT,
a.STATE,
a.MACHINE,
b.SQL_ID,
b.SQL_TEXT,
b.PLAN_HASH_VALUE
from v$active_session_history a,v$sql b
where a.SQL_ID=B.SQL_ID
and b.SQL_ID=''
AND b.SQL_TEXT LIKE '%%'
AND b.plan_hash_value='';
//简单查看MACHINE
select
a.sid,a,serial#,
a.machine,b.sql_id,
b.sql_text,
b.plan_hash_value
from v$session a,v$sql b
where a.SQL_ID=B.SQL_ID
and b.SQL_ID=''
AND b.SQL_TEXT LIKE '%%'
AND b.plan_hash_value='';
//看连接
select distinct machine from v$session;
netstat -an|grep
//数据泵预导出测试
取代getddl
用dba_tab_columns查字段类型
//查数据库会话对应的操作系统SPID
select spid from v$process where addr in (select paddr from v$session where sid=1053)
//查看操作系统用户对应的sid
select sid,serial#,username,sql_id,module,program from v$session where paddr in (select addr from v$process where spid in (ps -ef|grep LOCAL=YES));
//解析失败查看 $ORACLE_HOME/perl/bin/perl profile.pl
//死锁查看$ORACLE_HOME/perl/bin/perl lockchain.pl
//死锁杀会话$ORACLE_HOME/perl/bin/perl seskill.pl
//##获得当前数据库scn
select dbms_flashback.get_system_change_number from dual;
数据导出
expdp cjc/cjc@cjcpdb DIRECTORY=expdp_dir DUMPFILE=cjc_t1.dmp logfile=cjc_expdp_t1.log
tables=cjc.t1 flashback_scn=3658620
数据导入
impdp chen/chen@chenpdb DIRECTORY=expdp_dir DUMPFILE=cjc_t1.dmp logfile=cjc_impdp_t1.log
remap_schema=cjc:chen remap_tablespace=cjctbs:chentbs table_exists_action=REPLACE
//##查看真实的执行计划，不过需要游标在内存中
select plan_hash_value,child_number from v$sql where sql_id= '1juuwsjz2vvsy';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID,CHILD_NUMBER));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1juuwsjz2vvsy',0)); ---例如
//##通过下边语句列出awr中的执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('1juuwsjz2vvsy'));
//##最经常使用的查看执行计划方法
explain plan for + sql语句;
select * from table(dbms_xplan.display);
//##查看高级的执行计划
explain plan for + sql语句;
select * from table(dbms_xplan.display(NULL,NULL,'advanced -projection'));
//##查看带有A-ROWS的执行计划
select /*+ gather_plan_statistics */ ename from t2 where empno=7902;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
//##udev配置
ACTION=="add|change", KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id --whitelisted --replace-whitespace --device=%N", RESULT=="", RUN+='/bin/raw /dev/raw/raw1 %N'
multipath udev 配置
KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360050764008380da7000000000000005",SYMLINK+="asm-asmdisk01",OWNER="grid",GROUP="asmadmin",MODE="0660"
KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360050764008380da7000000000000006",SYMLINK+="asm-asmdisk02",OWNER="grid",GROUP="asmadmin",MODE="0660"
KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360050764008380da7000000000000007",SYMLINK+="asm-asmdisk03",OWNER="grid",GROUP="asmadmin",MODE="0660"
KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360050764008380da7000000000000008",SYMLINK+="asm-asmdisk04",OWNER="grid",GROUP="asmadmin",MODE="0660"
KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360050764008380da7000000000000009",SYMLINK+="asm-asmdisk05",OWNER="grid",GROUP="asmadmin",MODE="0660"
KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360050764008380da700000000000000a",SYMLINK+="asm-asmdisk06",OWNER="grid",GROUP="asmadmin",MODE="0660"
KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360050764008380da700000000000000b",SYMLINK+="asm-asmdisk07",OWNER="grid",GROUP="asmadmin",MODE="0660"
配置完成之后重启
udev trigger
查看 uuid：
for i in b c d e f g; do /usr/lib/udev/scsi_id -g -u -d /dev/sd$i; done
创建 UDEV：
for i in b c d e f g;
do
echo "KERNEL==\"sd*\", ENV{DEVTYPE}==\"disk\",SUBSYSTEM==\"block\", PROGRAM==\"/lib/udev/scsi_id -g -u -d \$devnode\",RESULT==\"`/usr/lib/udev/scsi_id -g -u /dev/sd$i`\",RUN+=\"/bin/sh -c 'mknod /dev/asm-disk$i b \$major \$minor; chown grid:asmadmin /dev/asm-disk$i; chmod 0660 /dev/asm-disk$i'\"">>/etc/udev/rules.d/99-oracle-asmdevices.rules
done
/sbin/udevadm trigger --type=devices --action=change
生成对应硬件设备文件：
udevadm control -R
c
ls -ltr /dev/asm*
//##正确udev的配置方式
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="1ATA_VBOX_HARDDISK_VB028931ab-67c49e98", SYMLINK+="asmdisks/asmdisk01", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="1ATA_VBOX_HARDDISK_VBb60ff90f-fe336617", SYMLINK+="asmdisks/asmdisk02", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="1ATA_VBOX_HARDDISK_VBcd3c03c2-6ba29590", SYMLINK+="asmdisks/asmdisk03", OWNER="grid", GROUP="asmadmin", MODE="0660"
udevadm trigger
//##删除用户下所有sequence
declare owner_name varchar(20);
cursor cur1 is select sequence_name from user_sequences;
begin
owner_name := '用户名';
for cur2 in cur1 loop
execute immediate 'drop sequence '||owner_name||'.'||cur2.sequence_name;
end loop;
end;
/
//建议
@？/rdbms/admin/sqltrpt.sql
//收集 hanganlyze
# sqlplus Username/Password as sysdba
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug tracefile_name
SQL> oradebug hanganalyze 3
//查询library cache pin相关信息
col sid for a30
col sql_text for a40
col owner for a40
col object for a20
set lines 1000
SELECT distinct decode(kglpnreq,0,'holding_session:'||s.sid,'waiting_session: '||s.sid) sid,
s.SERIAL#, kglpnmod "Pin Mode", kglpnreq "ReqPin",a.sql_text,kglnaown "Owner", kglnaobj "Object"
FROM x$kglpn p, v$session s,v$sqlarea a,v$session_wait sw,x$kglob x
WHERE p.kglpnuse=s.saddr
AND kglpnhdl=sw.p1raw
and kglhdadr=sw.p1raw
and sw.event like 'library cache%'
and (a.hash_value, a.address) IN (
select
DECODE (sql_hash_value,
0,
prev_hash_value,
sql_hash_value
),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
from v$session s2
where s2.sid=s.sid
)
;
////查询library cache pin/lock
select Distinct /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
od.to_owner object_owner,
od.to_name object_name,
oc.Type,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested,
xw.KGLNAOBJ wait_sql,xh.KGLNAOBJ hold_sql
from dba_kgllock w, dba_kgllock h, v$session w1,
v$session h1,v$object_dependency od,V$DB_OBJECT_CACHE oc,x$kgllk xw,x$kgllk xh
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
And od.to_address = w.kgllkhdl
And od.to_name=oc.Name
And od.to_owner=oc.owner
And w1.sid=xw.KGLLKSNM
And h1.sid=xh.KGLLKSNM
And (w1.SQL_ADDRESS=xw.KGLHDPAR And w1.SQL_HASH_VALUE=xw.KGLNAHSH)
And (h1.SQL_ADDRESS=xh.KGLHDPAR And h1.SQL_HASH_VALUE=xh.KGLNAHSH)
;
//未使用绑定变量的语句
select a.*,substr(b.sql_text,1,99) sql_text from (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) > &a
order by 2) a,v$sql by
where a.sql_id=b.sql_id;
//left join参考语句
set line222
col pagesize1000
col TABLESPACE_NAME for a40
select tbs_used_info.tablespace_name,
tbs_used_info.alloc_mb,
tbs_used_info.used_mb,
tbs_used_info.max_mb,
tbs_used_info.free_of_max_mb,
tbs_used_info.used_of_max || '%' used_of_max_pct
from (select a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024) alloc_mb,
round((a.bytes_alloc - nvl(b.bytes_free,
0)) / 1024 / 1024) used_mb,
round((a.bytes_alloc - nvl(b.bytes_free,
0)) * 100 / a.maxbytes) used_of_max,
round((a.maxbytes - a.bytes_alloc + nvl(b.bytes_free,
0)) / 1048576) free_of_max_mb,
round(a.maxbytes / 1048576) max_mb
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,
'YES',
f.maxbytes,
'NO',
f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)) tbs_used_info
order by tbs_used_info.used_of_max desc;
//grid打补丁
grid执行 opatch apply -oh /home/db/grid/product/11.2.0 -local /home/db/grid/patch/17414345
oracle执行 opatch apply -oh /home/db/oracle/product/11.2.0 -local /home/db/oracle/patch/17414345
//查看隐含参数数值
set linesize 333
col name for a35
col description for a66
col value for a30
SELECT i.ksppinm name,
i.ksppdesc description,
CV.ksppstvl VALUE
FROM sys.x$ksppi i, sys.x$ksppcv CV
WHERE i.inst_id = USERENV ('Instance')
AND CV.inst_id = USERENV ('Instance')
AND i.indx = CV.indx
AND i.ksppinm LIKE '%¶m%'
ORDER BY REPLACE (i.ksppinm, '_', '');
//查看最*解析情况
select name,value from v$mystat a join v$statname b
on a.statistic#=b.statistic# where b.name like 'parse count%';
//*一周*均归档日志量
select avg(log_size_G) from(
select to_char(COMPLETION_TIME,'YYYYMMDD') snap_time,
nvl(round(sum(blocks*block_size)/1024/1024/1024),0) log_size_G
from v$archived_log
where dest_id=1
and COMPLETION_TIME<to_date(to_char(trunc(sysdate),'yyyy-mm-dd hh24:mi'),'yyyy-mm-dd hh24:mi')
and COMPLETION_TIME>to_date(to_char(trunc(sysdate-7),'yyyy-mm-dd hh24:mi'),'yyyy-mm-dd hh24:mi')
group by to_char(COMPLETION_TIME,'YYYYMMDD'))
;
//*一周每日归档日志生成量
select to_char(COMPLETION_TIME,'YYYYMMDD') snap_time,
nvl(round(sum(blocks*block_size)/1024/1024/1024),0) log_size_G
from v$archived_log
where dest_id=1
and COMPLETION_TIME<to_date(to_char(trunc(sysdate),'yyyy-mm-dd hh24:mi'),'yyyy-mm-dd hh24:mi')
and COMPLETION_TIME>to_date(to_char(trunc(sysdate-7),'yyyy-mm-dd hh24:mi'),'yyyy-mm-dd hh24:mi')
group by to_char(COMPLETION_TIME,'YYYYMMDD')
;
//查看数据泵导出数据量
select owner,sum(bytes)/1024/1024 from dba_segments group by owner;
//查看表碎片高水位
SELECT table_name,
ROUND ( (blocks * 8), 2) "高水位空间 k",
ROUND ( (num_rows * avg_row_len / 1024), 2) "真实使用空间 k",
ROUND ( (blocks * 10 / 100) * 8, 2) "预留空间(pctfree) k",
ROUND (
( blocks * 8
- (num_rows * avg_row_len / 1024)
- blocks * 8 * 10 / 100),
2)
"浪费空间 k"
FROM user_tables
WHERE temporary = 'N'
ORDER BY 5 DESC;
//undo
@undousage
active不高，不需要扩容。因为expired 和unexpired都可以覆盖。
active高过60%，需要马上扩容
1.@asql 查看当前语句
2.@trans 看事务
//extents 层次看临时段v$sort_segment
v$sort_segment 看 used_extents和free_extents的比例
select total_extents,free_extents,used_extents,added_extents from v$sort_segment;
//temp临时表空间使用率
select c.tablespace_name,
to_char(c.bytes / 1024 / 1024 / 1024, '99,999.999') total_gb, to_char((c.bytes - d.bytes_used) / 1024 / 1024 / 1024, '99,999.999') free_gb, to_char(d.bytes_used / 1024 / 1024 / 1024, '99,999.999') use_gb, to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use from (select tablespace_name, sum(bytes) bytes from dba_temp_files GROUP by tablespace_name) c, (select tablespace_name, sum(bytes_cached) bytes_used from v$temp_extent_pool GROUP by tablespace_name) d where c.tablespace_name = d.tablespace_name;//rac hang死情况使用
sqlplus -prelim / as sysdba
oradebug setorapname reco
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 266
--等一分钟后再次执行hanganalyze分析
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 266
oradebug tracefile_name;
SELECT d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc'
AS "trace_file_name"
FROM (SELECT p.spid FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE FROM v$parameter
WHERE NAME = 'user_dump_dest') d;
本地
select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat))
alter table test_emp pt_1 deallocate unused;
(19条消息) oracle之truncate table 的drop storage和reuse storage_守望者_xwm的博客-CSDN博客
//sql monitor
set trimspool on
set arraysize 512
set trim on
set pagesize 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool sqlmon.html
select /*+ noparallel */ dbms_sqltune.report_sql_monitor (sql_id=>'input your SQL ID', report_level=>'ALL', type=>'ACTIVE') from dual;
select /*+ noparallel */ dbms_sqltune.report_sql_monitor (sql_id=>'input your SQL ID', report_level=>'ALL', type=>'TEXT') from dual;
spool off
cat sqlmon.html
//删除归档
delete archivelog until time 'SYSDATE-1/288';
//RMAN修改时间格式
alter session set nls_date_format='YYYYMMDD HH24:MM:SS';
//数据泵导出导入以及授权
create or replace directory dpdir as '/home/db/oracle/dpdir';
grant read,write on directory dpdir to scott;
select to_char(CURRENT_SCN) FROM v$database;
expdp scott/tiger schemas=scott dumpfile=scott_bak%U.dmp directory=dump_dir job_name=XXXjob logfile=expdp2_$today.log filesize=2G parallel=4 cluster=n flashback_scn=XXX;
declare owner_name varchar(20);
cursor cur1 is select sequence_name from user_sequences;
begin
owner_name := '用户名';
for cur2 in cur1 loop
execute immediate 'drop sequence '||owner_name||'.'||cur2.sequence_name;
end loop;
end;
/
impdp 'system/gkH#2021@10.6.150.9:11521/CDB00002_12' directory=dump_dir dumpfile=scott_bak%U.dmp logfile=expdp2_$today.log schemas=scott TABLE_EXISTS_ACTION=replace parallel=4;
select job_name,state from dba_datapump_jobs;
//停止数据泵
select job_name,state from dba_datapump_jobs;
expdp system/oracle attach=job_name
Export>stop_job=immediate
ATTACH 命令查看 expdp help=y
//dba_jobs 视图
select * from dba_jobs where broken='Y';
//dba_jobs_running 和dba_scheduler_runing_job
select count(*) from dba_jobs_runing;
select count(*) from dba_scheduler_running_job;
//行迁移/行链接
select owner,table_name,tablespace_name,chain_cnt from dba_tables where chain_cnt>0;
select owner||'.'||index_name as "OWNER.INDEX_NAME",blevel from dba_indexes where blevel>=4 order by 2 desc;
//ash视图
dba_hist_active_sess_history
v$active_session_history
//一段时间内执行的事件
select event，count(*)
from v$active_session_history
where sample_time>to_date('202303100005','YYYYMMDDHH24MI')
AND sample_time<to_date('202303100005','YYYYMMDDHH24MI')
group by event
order by 2;
//查看阻塞等待事件
set linesize 300
col module for a20
col machine for a20
col event for a35
select
/*+ parallel(t,4) */
to_char(sample_time,'yyyy-mm-dd hh24:mi:ss'),
sql_id,
session_id,
event,
blocking_inst_id,
blocking_session,
time_waited,
current_obj#,
module,
machine,
sql_plan_hash_value,
session_type
from v$active_session_history t
where 1=1
and sample_time > to_date('202304040011','YYYYMMDDHH24MI')
and sample_time < to_date('202304040015','YYYYMMDDHH24MI')
order by 1;
//blocking_inst_id 看gc类的
//time_waited 看等待时间
//QC_SESSION_ID 看并行执行会话 session_id
//last_ddl_time
select object_id,object_name,last_ddl_time from dba_objects where object_name='';
表：IBPMUSER.BE_PERFORMANCE_LOG
select dbms_metadata.get_ddl('TABLE','BE_PERFORMANCE_LOG','IBPMUSER') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','PK_DEPT','USERNAME') FROM DUAL;
select dbms_metadata.get_ddl('CONSTRAINT','cons_name','USERNAME') FROM DUAL;
select dbms_metadata.get_ddl('REF_CONSTRAINT','cons_name','USERNAME') FROM DUAL; //看外键约束必须指定REF_CONSTRAINT
select dbms_metadata.get_ddl('VIEW','view_name','username') from dual;
select dbms_metadata.get_ddl('DB_LINK','OBJECT_NAME','USERNAME') FROM DUAL;
//用户创建ddl
set linesize 232
col a fro a300
select dbms_metadata.get_ddl('USER','USERNAME') a from dual;
//@sesskill
alter system kill session ',' immediate;
//是否锁定统计信息
select owner,table_name from dba_tab_statistics where stattype_locked='ALL';
undo
smon 清理idle的undo
//rollback_seg dba_rollback_segs没有大小，主要是看online/offline的status状态
select status,count(*) from dba_rollback_segs group by status;
//dba_segments 看undo segment
select SEGMENT_NAME,sum(bytes)/1024/1024 from dba_segments where tablespace_name like '%UNDO%' or segment_type like '%UNDO%' group by segment_name order by 2；
//dba_undo_extents
select * from dba_undo_extents where segment_name like '';
//
dba_tab_columns
dba_ind_columns
dba_cons_columns
//01分区表数据分布
select table_name,partition_name,
num_rows
from dba_tab_partitions where table_name='';
//分区索引数据分布
select index_name,
tablespace_name,
partition_name,
num_rows
from dba_ind_partitions where index_name like '%IND%';
//02分区查看分区方式、子分区方式、分区数量
select PARTITIONING_TYPE,SUBPARTITIONING_TYPE,PARTITION_COUNT from dba_part_tables where table_name='';
//03查看分区键
select owner,name,object_type,column_name,column_position from dba_part_key_columns where name ='';
//分区表索引是分区索引还是全局索引
dba_part_indexes
select INDEX_NAME,PARTITIONING_TYPE,PARTITION_COUNT,LOCALITY from dba_part_indexes;
//分区索引状态 usable可用的
select index_name,partition_name,status from dba_ind_partitions;
//重建分区索引
alter index index_name rebuild online;
alter index index_name rebuild partition partition_name online;
//分区索引统计信息
dba_tab_partitions
//索引统计信息
dba_indexes
//dba_tab_pratitions//统计信息是否锁、LAST_ANALYZED、STALE_STATS是否过期
//统计信息收集
//收集全库统计信息
sqlplus \'as sysdba\'
exec dbms_stats.gather_database_stats(no_invalidate=>false,degree=>8);
exec dbms_stats.gather_database_stats(no_invalidate=>false,degree=>8,estimate_percent=>15);
//收集schema统计信息
exec dbms_stats.gather_schema_stats('SCOTT');
exec dbms_stats.gather_schema_stats('SCOTT',estimate_percent=>15);
//收集表统计信息
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',cascade=>true,degree=>8,force=>TRUE);
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>false,degree=>16);
size 1 是不收直方图
size auto 是自动决定是否收集直方图
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for all columns size auto',cascade=>TRUE,no_invalidate=>false,degree=>16);
//查看是否收了直方图//直方图中列的桶数
select num_buckets from dba_tab_col_statistics where table_name='';
//收集索引统计信息
exec dbms_stats.gather_index_stats('SCOTT','IND_EMPNO',estimate_percent=>15);
//收集分区统计信息
验证Oracle收集统计信息参数granularity数据分析的力度 - jerome_lamb - 博客园 (cnblogs.com)
BEGIN
dbms_stats.gather_table_stats(
ownname=> 'SCOTT2' ,
tabname=> 'PAR_T0',
--partname=> 'p1',
estimate_percent=> 100 ,
cascade=> TRUE,
--method_opt=>'for all columns size 1 ',
--method_opt=>'for all columns size repeat ',
method_opt=>'for all indexed columns size skewonly ',
no_invalidate=>FALSE,
granularity=>'AUTO',
degree=>4
);
END ;
/
//收集分区统计
exec dbms_stats.gather_table_stats(user,'TEST3',partname => 'SYS_P261',granularity => 'PARTITION');
exec dbms_stats.gather_table_stats(user,'TEST3',partname => 'SYS_P261',granularity => 'PARTITION'，cascade=>true,degree=8);
此方法收集partition 会导致全表统计信息为0
使用绑定变量的话不知道落在哪个分区上
即使分区有了统计信息
也可能无法走正确索引
//统计信息copy
exec dbms_stats.gather_table_stats(owner=>'user',tabname=>'TEST3',partname => 'SYS_P261',granularity => 'PARTITION'，cascade=>true,degree=8);
exec dbms_stats.copy_table_stats(owner=>'user',tablename=>'TEST3',SRCPARTNAME='SYS_P261',DSTPARTNAME='SYS_P263',FORCE=>TRUE);
//统计信息锁定
exec dbms_stats.lock_table_stats('NEWCMS','S03T1_WF_HIST_AVY');
通过dba_tab_statistics
//统计信息解锁
exec dbms_stats.unlock_table_stats('NEWCMS','S03T1_WF_HIST_AVY');
//统计信息开并行
select dbms_stats.get_param('degree') from dual;
exec dbms_stats.set_param('degree','4');
//11g禁用统计信息收集
exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
//11g启用统计信息收集
exec DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');
//11G查看是否收集统计信息
select owner,job_name,enabled from dba_scheduler_jobs where job_name='BSLN_MAINTAIN_STATS_JOB';
//作业
select * from dba_autotask_window_clients;
//tm锁
除了增删改以外
建索引和insert append高水位插入也有可能产生tm锁；
应急
1.未有死锁的tx
@event
@lock
@locktree
perl lockchain.pl
@trans-->>看事务开始时间、行数和块数
//要看有锁的session的信息 看是不是idle ----看是不是应用会话没有提交。或者是会话状态异常。
select sid,serial#,machine,codule,LOGON_TIME,PREV_SQL,status from v$session;
看status看进程状态
看PREV_SQL_ID查
//v$open_cursor 查看sid查可能有
2.死锁的情况
死锁本来oracle可以解
但是因为一直发，发的速度块，所以无法来的及解决
在1的基础上 perl lockchain.pl 和perl sesskill.pl
set linesize 132
col sid format a12
col event format a30
col sql_text for a40
col object_name for a35
with session1 as
(select /*+materialize*/
sid,
sql_id,
event,
blocking_session,
row_wait_obj#
from v$session)
select LPAD(' ', LEVEL )|| sid sid,
sql_id,
event,
owner||decode(owner,null,null,'.')||object_name object_name,
substr(sql_text,1,40) sql_text
from session1 s
left outer join dba_objects on (object_id = row_wait_obj#)
left outer join v$sqlarea using (sql_id)
where sid in (select blocking_session from session1)
or blocking_session is NOT NULL
connect by PRIOR sid=blocking_session
start with blocking_session IS NULL;
自动统计信息收集情况查看
select y.window_name ,x.state ,x.start_date ,x.status ,x.job_duration ,y.window_duration ,y.actual_duration from (select a.job_name ,b.state ,to_char(a.actual_start_date,'mm/dd_hh24:mi') start_date ,substr(to_char(a.run_duration),4,7) job_duration ,a.status from dba_scheduler_job_run_details a, dba_scheduler_jobs b where a.job_name=b.job_name and a.job_name='GATHER_STATS_JOB' and a.actual_start_date > sysdate -15)x, (select window_name ,to_char(actual_start_date,'mm/dd_hh24:mi') start_date ,substr(to_char(window_duration),4,7) window_duration ,substr(to_char(actual_duration),4,7) actual_duration from dba_scheduler_window_details) y where x.start_date=y.start_date order by 3
本文来自博客园，作者：蚌壳里夜有多长，转载请注明原文链接：https://www.cnblogs.com/dbahrz/p/17227405.html
