---查找对应时段日志
select g.FIRST_TIME,g.COMPLETION_TIME,g.* from v$archived_log g
where g.completion_time>to_date('2023-11-17 01:30:00','yyyy-mm-dd hh24:mi:ss')
and dest_id=1
order by g.FIRST_TIME
-----挖取
EXECUTE dbms_logmnr.add_logfile(logfilename=>'+ARCH/xxxxxx/ARCHIVELOG/2023_06_21/thread_1_seq_237261.1754.1140090135');
EXECUTE dbms_logmnr.add_logfile(logfilename=>'+ARCH/xxxxxx/ARCHIVELOG/2023_06_21/thread_2_seq_236836.2363.1140089553');
EXECUTE dbms_logmnr.add_logfile(logfilename=>'+ARCH/xxxxxx/ARCHIVELOG/2023_06_21/thread_2_seq_236837.549.1140090133');
EXECUTE dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
create table tmp_logmnr_contents tablespace xxxxx as select * from v$logmnr_contents where operation 'INTERNAL';
commit;
EXECUTE dbms_logmnr.end_logmnr;
--------查dbtime
set linesize 200
set pagesize 20000
col DATE_TIME for a45
col STAT_NAME for a10
WITH sysstat AS (
SELECT sn.begin_interval_time begin_interval_time,sn.end_interval_time end_interval_time,ss.stat_name stat_name,
ss.VALUE e_value,lag (ss.VALUE, 1) over (ORDER BY ss.snap_id) b_value
FROM DBA_HIST_SYS_TIME_MODEL ss,dba_hist_snapshot sn
WHERE trunc (sn.begin_interval_time) >= sysdate - 7
AND ss.snap_id = sn.snap_id
AND ss.dbid = sn.dbid
AND ss.instance_number = sn.instance_number
AND ss.dbid = (SELECT dbid FROM v$database)
AND ss.instance_number = (SELECT instance_number FROM v$instance)
AND ss.stat_name = 'DB time')
SELECT to_char (BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh24:mi') || to_char (END_INTERVAL_TIME,'hh24:mi') date_time,
stat_name,round((e_value - nvl(b_value, 0))/60/1000/1000 ,2) dbtime_value
FROM sysstat
WHERE(e_value - nvl(b_value, 0)) > 0 AND nvl (b_value, 0) > 0 ;
----查历史绑定变量
set linesize 600
col sql_id for a15
col name for a10
col position for 999
col datatype for 999
col DATATYPE_STRING for a20
col value_string for a35
col timelx for a35
select sql_id ,child_number,name ,position ,datatype_string ,value_string ,to_char(anydata.accesstimestamp(value_anydata),'yyyy-mm-dd hh24:mi:ss') as timelx
From v$sql_bind_capture where sql_id = 'xxxxxxxx';
ORACLE_HOME/network/admin/sqlnet.ora 新增：
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
--------------------查看回滚事务
select * from gV$FAST_START_TRANSACTIONS where state='RECOVERING'
-----------------------多端口
srvctl modify listener -p 1521,1530
----统计监听信息
grep "xx.xx.xx.xx" listener.log.20230110.110901 |grep -v establish
统计一天内每小时的session请求数
# fgrep "13-JAN-2015 " listener.log |fgrep "establish" |awk '{print $1 " " $2}' |awk -F: '{print $1 }' |sort |uniq -c
指定的一小时每分钟session请求数
# fgrep "13-JAN-2015 11:" listener.log |fgrep "establish" |awk '{print $1 " " $2}' |awk -F: '{print $1 ":" $2 }' |sort |uniq -c
指定的一小时每秒session请求数
# fgrep "13-JAN-2015 11:30" listener.log |fgrep "establish" |awk '{print $1 " " $2}' |awk -F: '{print $1 ":" $2 ":" $3 }' |sort |uniq -c
指定的一小时内每分钟连接创建失败数
#fgrep "11-JAN-2015 11:" listener.log |awk '{ if ( $NF != 0 ) print $0 }'|awk '{print $1 " " $2}' |awk -F: '{print $1 ":" $2 }' |sort |uniq -c
指定的一小时内每IP请求数
fgrep "30-JAN-2018 15:" listener.log |fgrep "establish"|awk -F* '{print $3}'|awk -F= '{ print $4}'|sed -e 's/......$//g'|sort |uniq -c|sort
fgrep "APR-2023" listener.log |fgrep "establish"| fgrep "xxxxsrv_w"| awk -F* '{print $3}'|awk -F= '{ print $4}'|sed -e 's/......$//g'|sort |uniq -c|sort
fgrep "APR-2023" listener.log |fgrep "establish"| fgrep "xxxxxsrv_w"| awk -F* '{print $3}'|awk -F= '{ print $4}'|sed -e 's/......$//g'|sort |uniq -c|sort
grep "HOST=.*establish.*\* 0" listener.log.20230201.101101 | awk -F'*' '{match($3,/[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+/); ip = substr($3,RSTART,RLENGTH);cnt[ip]+=1;last[ip]=$1;}END {for (i in cnt) printf "%-16s %9s %19s\n",i,cnt[i],last[i];}' | sort -k 1
-------------------批量删除
DECLARE
CURSOR cur IS select rowid from xxxx.xxxxxxx partition(xxxxxxx_2022M10_1);
TYPE rec IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER;
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT INTO recs LIMIT 10000;
FORALL i IN 1 .. recs.COUNT
delete from xxxxx.xxxxxxxx where rowid=recs(i);
COMMIT;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
END;
/
-------------------------------------收集统计信息
select 'exec dbms_stats.set_table_prefs('''||owner||''','''||table_name||''',''INCREMENTAL'',''TRUE'');'
from (select distinct owner, table_name from DBA_TAB_STATISTICS where stattype_locked IN ('ALL','DATA','CACHE') and owner not in ('SYS','SYSMAN','SYSTEM','WMSYS'));
select 'exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'''||owner||''',tabname=>'''||table_name||''',method_opt=>''for all columns size auto'',cascade=>true,force=>true,degree=>16);'
from (select distinct owner, table_name from DBA_TAB_STATISTICS where stattype_locked IN ('ALL','DATA','CACHE') and owner not in ('SYS','SYSMAN','SYSTEM','WMSYS'));
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'xxxx',tabname=>'xxxx',method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>16,no_invalidate=>false);
exec DBMS_STATS.gather_database_stats(method_opt=>'for all columns size auto',cascade=>true,degree=>16,no_invalidate=>false);
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'xxxx' ,method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>16,no_invalidate=>false);
select 'exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'''||username||''', method_opt=>''for all columns size auto'',cascade=>true,force=>true,degree=>16,no_invalidate=>false);'
from dba_users where username in
-- no_invalidate=>false 立即失效
----------------------19c pdb恢复bug
alter system set "_min_undosegs_for_parallel_fptr"=0 scope=both sid='*' ;
exec dbms_service.create_service('xxxx','xxxx');
exec dbms_service.start_service('xxxx');
exec dbms_service.stop_service('xxx');
exec dbms_service.delete_service('xxxx');
set lines 200
col name for a20
col pdb for a20
select con_id,name,enabled,pdb from cdb_Services order by 1;
-----------------修改awr
select SRC_DBNAME,SNAP_INTERVAL，RETENTION from dba_hist_wr_control;
exec dbms_workload_repository.modify_snapshot_settings(retention=>15*24*60);
-- 历史的
找最大redo 的对象：
select *
from (
SELECT to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI') snap_time,
dhsso.object_name,
SUM(db_block_changes_delta)
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhsso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhsso.obj#
AND dhss.dataobj# = dhsso.dataobj#
AND begin_interval_time > sysdate - 120 / 1440
GROUP BY to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'),
dhsso.object_name
order by 3 desc)
where rownum sysdate -3
AND dhss.sql_id = dhst.sql_id
group by dhss.sql_id
order by 3 desc ;
-- 明细
SELECT to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'),
dbms_lob.substr(sql_text, 4000, 1),
dhss.instance_number,
dhss.sql_id,
executions_delta,
rows_processed_delta
FROM dba_hist_sqlstat dhss, dba_hist_snapshot dhs, dba_hist_sqltext dhst
WHERE UPPER(dhst.sql_text) LIKE '%***%'
AND dhss.snap_id = dhs.snap_id
AND dhss.instance_Number = dhs.instance_number
AND dhss.sql_id = dhst.sql_id
order by to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI');
------PSU
Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (Doc ID 2118136.2)
---------------EXADATA PSU
Exadata Database Machine and Exadata Storage Server Supported Versions (Doc ID 888828.1)
-------------------------------------------------绑定执行计划
-- add plan to baseline
set serveroutput on
declare
n pls_integer;
begin
n := dbms_spm.load_plans_from_cursor_cache(sql_id => 'xxxxxx',
plan_hash_value => 2275149734,
fixed => 'NO',
enabled => 'NO');
dbms_output.put_line('Loaded: '||n||' plans.');
end;
/
set serveroutput off
-- query plan base line
select signature,sql_handle,plan_name from dba_sql_plan_baselines; 'SQL_79c1d14a660634eb',
plan_name=>'SQL_PLAN_7mhfj99m0cd7b94ecae5c',
attribute_name=>'enabled', attribute_value=>'YES');
END;
/
-- diable one baseline
DECLARE
cnt NUMBER;
BEGIN
cnt := sys.dbms_spm.alter_sql_plan_baseline(
sql_handle=>'SQL_79c1d14a660634eb',
plan_name=>'SQL_PLAN_7mhfj99m0cd7b94ecae5c',
attribute_name=>'enabled', attribute_value=>'NO');
END;
/
-----------------------
select SQL_ID, SQL_PROFILE,PLAN_HASH_VALUE from V$SQL where SQL_ID='xxxxxx';
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(Name => '', Attribute_Name => 'STATUS', Value => 'DISABLED');
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(Name => '');
