Oracle常用维护命令

两个小黄鹂

已于 2023-07-11 17:29:32 修改

阅读量1.5k

点赞数

分类专栏： # Oracle 数据库文章标签： oracle 数据库 dba

于 2022-01-16 16:01:50 首次发布

本文链接：https://blog.csdn.net/u014650965/article/details/122523704

版权

数据库同时被 2 个专栏收录

56 篇文章 1 订阅

订阅专栏

Oracle

21 篇文章 0 订阅

订阅专栏

函数

raw转varchar2

select utl_raw.cast_to_raw('20220930') from dual;
select utl_raw.cast_to_varchar2('3230323230393330') from dual;

性能优化

当前事件

SELECT b.sid oracleID,
       b.username 登录Oracle用户名,
       b.serial#,
       spid 操作系统ID,
       paddr,
       sql_text 正在执行的SQL,
       b.machine 计算机名
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
   AND b.sql_hash_value = c.hash_value;

当前事件升级版

做成了shell脚本，实时打印信息，用于监控数据库状态

#!/bin/bash
while [ 1 ]
do
echo ""
echo "$ORACLE_SID wait session on `date`"
sqlplus -s / as sysdba <<!
COL EVENT FORMAT A25
COL USERNAME FORMAT A10
COL PROGRAM FORMAT A30
SET LIN 200
SET PAGESIZE 2000
COL OSUSER FORMAT A10
COL MACHINE FOR A10
SELECT SUBSTR(PROGRAM,1,30) PROGRAM,SID,SQL_ID,USERNAME,OSUSER,MACHINE,SUBSTR(EVENT,1,25) AS EVENT,FINAL_BLOCKING_SESSION AS BLOCKER,FINAL_BLOCKING_SESSION_STATUS AS BLCK_STATUS,FINAL_BLOCKING_INSTANCE AS BLCK_INS,TO_CHAR(LOGON_TIME,'mmdd hh24:mi') LONG_TIME,P1,P2,P3
FROM V\$SESSION
WHERE EVENT NOT LIKE '%SQL%'
AND TYPE<>'BACKGROUND'
AND WAIT_CLASS<>'Idle'
ORDER BY 1,2,4;
exit
!

查看历史事件

select s.event,count(*) from  V$ACTIVE_SESSION_HISTORY s
where s.sample_time > to_timestamp('2020-12-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
and s.sample_time < to_timestamp('2020-12-07 06:00:00','yyyy-mm-dd hh24:mi:ss')
group by s.event;

数据库前10条慢SQL

set lin 300
set pages 200
col sql_text for a140
select * from (select inst_id,program,sql_id,sql_text,sql_exec_start,elapsed_time/1000000 
from gv$sql_monitor 
where username!='SYS' 
and substr(program,1,6) != 'python'
and substr(program,1,7) != 'sqlplus'
and to_char(sql_exec_start,'yyyymmddhh24miss')<='20210806000000' 
order by 6 desc)
where rownum<=10;

DBTIME

查看系统繁忙程度

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_sysstat 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, 'mm-dd hh24:mi') || to_char (END_INTERVAL_TIME, '
hh24:mi') date_time, stat_name, round((e_value - nvl(b_value, 0)) / (extract(day
from(end_interval_time - begin_interval_time)) * 24 * 60 * 60 + extract(hour
from(end_interval_time - begin_interval_time)) * 60 * 60 + extract(minute
from(end_interval_time - begin_interval_time)) * 60 + extract(second
from(end_interval_time - begin_interval_time))), 0) per_sec
from sysstat
where(e_value - nvl(b_value, 0)) > 0 and nvl(b_value, 0) > 0;

进程对应SQL

通过top、ps等命令确认有问题的进程号，通过sql确认进程对应的sql

select a.sql_id,dbms_lob.substr(a.sql_fulltext)
from v$sqlarea a,v$session b,v$process c 
where a.sql_id=b.sql_id
and b.paddr=c.addr
and spid = '&spid';

查看SQL绑定变量

select
         sql_id
       , name
       , datatype_string
       , case datatype
                  when 180
                           then to_char(ANYDATA.accesstimestamp(t.value_anydata), 'YYYY/MM/DD HH24:MI:SS')
                           else t.value_string
         end as bind_value
       , last_captured
from
         gv$sql_bind_capture t
where
         sql_id = '&sql_id'
order by
         last_captured
       , name

查看SQL执行计划

12C开始，explain功能可以在DG备库使用

explain plan for select * from test;

select * from table(dbms_xplan.display());

查看SQL执行计划(SQL_ID)

select * from table(dbms_xplan.display_cursor('5bqb2tsapyjww',0,'ADVANCED'));

select * from table(dbms_xplan.display_cursor('fw2ja771v3cu8',null));

select * from table(dbms_xplan.display_cursor('2wd4jddrpy5np',null,'all'));

select distinct(plan_hash_value) from v$sql_plan t where sql_id='frcyq9xmmyc7j';

查看SQL耗时

select
	a.sql_text SQL语句
  , b.etime 执行耗时
  , c.user_id 用户ID
  , c.SAMPLE_TIME 执行时间
  , c.INSTANCE_NUMBER 实例数
  , u.username 用户名
  , a.sql_id SQL编号
from
	dba_hist_sqltext a
  , (
		select
			sql_id
		  , ELAPSED_TIME_DELTA / 1000000 as etime
		from
			dba_hist_sqlstat
		where
			ELAPSED_TIME_DELTA / 1000000 >= 1
	)                            b
  , dba_hist_active_sess_history c
  , dba_users                    u
where
	a.sql_id      = b.sql_id
	--and a.sql_id  ='24ambrj6zr7zc'
	and c.user_id = u.user_id
	and b.sql_id  = c.sql_id
	--and a.sql_text like '%IN%'
order by
	SAMPLE_TIME desc
  , b.etime desc
;

SQL执行历史

set lines 200
set pages 1000
col shijian for a13
col execu_d for 999999
col bg_d for 9999999999
col dr_d for 9999999999
col et_d for 99999999
col ct_d for 99999999
col io_time for 999999
col clus_time for 999999
col ap_time for 999999
col cc_time for 999999
col et_onetime for 999999

select
	to_char(b.END_INTERVAL_TIME, 'yyyy-mm-dd hh24') shijian
  , plan_hash_value
  , round(sum(a.EXECUTIONS_DELTA),1)             exec_cnt
  , round(sum(a.BUFFER_GETS_DELTA),1)            buffer_get
  , round(sum(a.DISK_READS_DELTA),1)             disk_read
  , round(sum(a.ELAPSED_TIME_DELTA / 1000000),1) et_d
  , round(sum(a.CPU_TIME_DELTA     / 1000000),1) ct_d
  , round(sum(IOWAIT_DELTA         / 1000000),1) io_time
  , round(sum(CLWAIT_DELTA         / 1000000),1) clus_time
  , round(sum(APWAIT_DELTA         / 1000000),1) ap_time
  , round(sum(ccwait_delta         / 1000000),1) cc_time
  , round(decode(sum(a.EXECUTIONS_DELTA)
				   , 0, sum(a.BUFFER_GETS_DELTA)
				   , round(sum(a.BUFFER_GETS_DELTA) / sum(a.EXECUTIONS_DELTA), 0)),1) get_onetime
  , round(decode(sum(a.EXECUTIONS_DELTA)
				   , 0, sum(a.rows_processed_delta)
				   , round(sum(a.rows_processed_delta) / sum(a.EXECUTIONS_DELTA), 0)),1) rows_onetime
  , round( decode(sum(a.EXECUTIONS_DELTA)
					, 0, sum(a.ELAPSED_TIME_DELTA / 1000)
					, round(sum(a.ELAPSED_TIME_DELTA / 1000) / sum(a.EXECUTIONS_DELTA), 0)),1) exec_ms
from
	dba_hist_sqlstat  a
  , dba_hist_snapshot b
where
	a.SNAP_ID             = b.SNAP_ID
	and a.INSTANCE_NUMBER = b.INSTANCE_NUMBER
	and a.sql_id          = '&1'
group by
	to_char(b.END_INTERVAL_TIME, 'yyyy-mm-dd hh24')
  , plan_hash_value
order by
	1
  , 2
;

长事务

SET LINES 999 PAGES 99
COL TARGET FOR A22
COL SOFAR FOR 99999999
COL TOTALWORK FOR 99999999
COL USERNAME FOR A15
COL OSUSER FOR A10
COL TERMINAL FOR A10
COL PROGRAM FOR A16
COL SPID FOR A10
COL SID_SERIAL FOR A15
COL TIME_REMAINING FOR 999999
COL TOTALTIME FOR A999999
COL STARTTIME FOR A20
SELECT (SELECT TO_CHAR(SYSDATE,'mm-dd hh24:mi:ss') from dual) "DATA",
	L.SID||','||L.SERIAL# AS SID_SERIAL,
	L.INST_ID,
	P.SPID,
	L.TARGET,
	L.SQL_ID,
	S.USERNAME,
	S.PROGRAM,
	S.OSUSER,
	S.TERMINAL,
	TO_CHAR(S.SQL_EXEC_START,'yyyy-mm-dd hh24:mi:ss') AS STARTTIME,
	S.STATE,
	L.TOTALWORK,
	L.SOFAR,
	L.ELAPSED_SECONDS+L.TIME_REMAINING AS TOTALTIME,
	L.TIME_REMAINING
FROM GV$SESSION_LONGOPS L,GV$PROCESS P,GV$SESSION S
WHERE P.ADDR=S.PADDR
AND L.SID=S.SID
AND L.TIME_REMAINING>0;

生成快照

要生成某段事件的AWR可以手动生成快照

begin
dbms_workload_repository.create_snapshot();
end;
/

系统信息

表信息

查看表的索引信息

set lin 200
COL COLUMN_NAME FOR A20
COL POS FOR 9999
COL TABLE_OWNER FOR A12
COL INDEX_TYPE FOR A10
COL INDEX_NAME FOR A29
COL BLEVEL FOR 99999
SELECT I.TABLE_NAME,I.TABLE_OWNER,I.INDEX_NAME,I.INDEX_TYPE,I.BLEVEL,I.NUM_ROWS,I.LAST_ANALYZED,C.COLUMN_POSITION POS,C.COLUMN_NAME
FROM DBA_INDEXES I,DBA_IND_COLUMNS C
WHERE I.TABLE_NAME=C.TABLE_NAME
AND I.TABLE_OWNER=C.TABLE_OWNER
AND I.INDEX_NAME=C.INDEX_NAME
AND I.OWNER=C.INDEX_OWNER
AND I.TABLE_NAME=UPPER('&TABLE_NAME') AND I.OWNER=UPPER('&OWNER') ORDER BY 3,8;

LOB字段信息

SELECT column_name, data_type
FROM all_tab_columns
WHERE  data_type LIKE '%LOB%'
and table_name in
(
'TEST2023',
'ADIN2023',
);

表空间

COL MAX FOR A15
COL TOAL_PCT_USED FOR A15
SET LIN 140
SET PAGES 100
SELECT A.TABLESPACE_NAME,
		TO_CHAR((A.BYTES_ALLOC/1024/1024/1024),'999999999999.99') CURR_GB,
		TO_CHAR((NVL(B.BYTES_FREE,0)/1024/1024/1024),'9999999999.99') CURR_FREE_GB,
		TO_CHAR(((NVL(B.BYTES_FREE,0)/A.USER_BYTES)*100),'9999999999.99') CURR_PCT_FREE,
		TO_CHAR((100-(NVL(B.BYTES_FREE,0)/A.USER_BYTES)*100),'9999999999.99') CURR_PCT_USED,
		TO_CHAR((MAXBYTES/1048576/1024),'9999999999.99') MAX,
		TO_CHAR(((A.BYTES_ALLOC-NVL(B.BYTES_FREE,0))/A.MAXBYTES)*100,'9999999999.99') TOAL_PCT_USED
	FROM (SELECT F.TABLESPACE_NAME,
			SUM(F.BYTES) BYTES_ALLOC,
			SUM(F.USER_BYTES) USER_BYTES,
			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(+) ORDER BY TOAL_PCT_USED;

ASM磁盘组

select free_mb,total_mb,name from v$asm_diskgroup;

归档量

SET LIN 200
COL H01 FOR 999
COL H02 FOR 999
COL H03 FOR 999
COL H04 FOR 999
COL H05 FOR 999
COL H06 FOR 999
COL H07 FOR 999
COL H08 FOR 999
COL H09 FOR 999
COL H10 FOR 999
COL H11 FOR 999
COL H12 FOR 999
COL H13 FOR 999
COL H14 FOR 999
COL H15 FOR 999
COL H16 FOR 999
COL H17 FOR 999
COL H18 FOR 999
COL H19 FOR 999
COL H20 FOR 999
COL H21 FOR 999
COL TOTAL FOR 999
set pages 300
SELECT SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH:MI:SS'),1,5) DAY,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22,
	SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23,
	COUNT(*) TOTAL
FROM GV$LOG_HISTORY
WHERE FIRST_TIME >= TO_CHAR(SYSDATE-10)
GROUP BY SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(FIRST_TIME,'MM/DD/RR HH:MI:SS'),1,5) DESC;

大表信息

set pages 200
col segment_name for a50
select segment_name,SEGMENT_GB from (select segment_name,sum(bytes)/1024/1024/1024 SEGMENT_GB from dba_segments 
where owner='&username' 
and segment_type='TABLE' 
group by segment_name 
order by 2) 
where SEGMENT_GB >=1;

隐含参数

set lin 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 '/_gc%' escape '/'
order by replace(i.ksppinm,'_','');

Dataguard

DG延迟

set lin 280
select * from v$dataguard_stats;

DG进程状态

select process,status from v$managed_standby;

系统变更

扩展表空间

alter tablespace ACCOUNTING add datafile size 1024m autoextend on next 10m;

resize数据文件

alter database datafile '[file_name]' resize 1024m;

磁盘组添加磁盘

set lin 200
col path for a30
col name for a30
select group_number,REDUNDANCY,DISK_NUMBER,MOUNT_STATUS,NAME,VOTING_FILE,FAILGROUP_TYPE,PATH from v$asm_disk;
create diskgroup BAKDG external redundancy disk '/dev/mapper/mpathg' name BAKDG_0000,'/dev/mapper/mpathh' name BAKDG_0001 attribute 'au_size'='4M', 'compatible.asm'='11.2.0.0.0','compatible.advm'='11.2.0.0.0';
alter diskgroup DATADG add disk '/dev/mapper/mpathf' name DATADG_0002;

创建共享逻辑卷

ASMCMD> volcreate -G bakdg -s 900G lvbak
ASMCMD> volinfo -G bakdg lvbak
Diskgroup Name: BAKDG

	 Volume Name: LVBAK
	 Volume Device: /dev/asm/lvbak-151
	 State: ENABLED
	 Size (MB): 921600
	 Resize Unit (MB): 32
	 Redundancy: UNPROT
	 Stripe Columns: 4
	 Stripe Width (K): 128
	 Usage: 
	 Mountpath: 

# mkfs -t acfs /dev/asm/lvbak-151	约1小时
# /sbin/acfsutil registry -a /dev/asm/lvbak-151 /backup
# /bin/mount -t acfs /dev/asm/lvbak-151 /bakcup
# chown -R oracle:oinstall /backup
#共享逻辑卷扩容
# /sbin/acfsutil size +1024G /backup

数据文件迁移

12C开始，数据文件支持在线迁移，无需将数据文件offline

alter database rename file '/u01/app/oracle/oradata/SCM2/redo02.log'
to '/home/oracle/oracle/oradata/SCM2/redo02.log';

备份

查看备份状态

set lin 280
set pages 1000
select ROW_LEVEL,ROW_TYPE,COMMAND_ID,OPERATION,STATUS,START_TIME,END_TIME,OPTIMIZED,OBJECT_TYPE,OUTPUT_DEVICE_TYPE,OSB_ALLOCATED from v$rman_status order by START_TIME,END_TIME;

两个小黄鹂

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
Oracle常用维护命令

性能优化当前事件SELECT b.sid oracleID, b.username 登录Oracle用户名, b.serial#, spid 操作系统ID, paddr, sql_text 正在执行的SQL, b.machine 计算机名FROM v$process a, v$session b, v$sqlarea cWHERE a.addr = b.paddr AND b.sql_hash_value =
复制链接

扫一扫