oracle 运营维护_Oracle数据库日常运维常用脚本
大
中
小
Oracle數據庫日常運維常用腳本
1 查看所有數據文件
select file_name from dba_data_files
union
select file_name from dba_temp_files
union
select name from v$controlfile
union
select value from v$parameter where name='spfile'
union
select member from v$logfile;
2 查看正在運行的job
select
djr.sid? ?? ?? ?? ?? ?? ?? ?? ?sess,
djr.job? ?? ?? ?? ?? ?? ?? ?? ?jid,
dj.log_user? ?? ?? ?? ?? ?? ???subu,
dj.priv_user? ?? ?? ?? ?? ?? ? secd,
dj.what? ?? ?? ?? ?? ?? ?? ?? ?proc,
to_char(djr.last_date,'MM/DD') lsd,
substr(djr.last_sec,1,5)? ?? ? lst,
to_char(djr.this_date,'MM/DD') nrd,
substr(djr.this_sec,1,5)? ?? ? nrt,
djr.failures? ?? ?? ?? ?? ?? ? fail
from
sys.dba_jobs dj,
sys.dba_jobs_running djr
where
djr.job = dj.job
/
3 根據sid查找進程相關信息
select sid,serial#,sql_hash_value,PREV_HASH_VALUE,username,program,machine,process
from v$session where sid=&sid;
4 根據spid查找進程相關信息
select
a.sid,a.serial#,a.sql_hash_value,a.PREV_HASH_VALUE,
a.username,a.program,a.machine,a.process
from v$session a,v$process where a.paddr=b.addr and spid=&spid;
5 通過hash_value獲取sql語句
Select sql_text from v$sqltext where hash_value=&hash_value order by piece;
6 使用explain plan for 獲取執行計劃
Explain plan for &sql;? ?這里對應具體sql語句
Select * from table(dbms_xplan.display);
7 使用shell腳本+hash_value獲取執行計劃以及sql語句
------------shell script -------------------
$ORACLE_HOME/bin/sqlplus -s /nolog<
connect / as sysdba;
set lines 121
set pages 999
col sql_text format a80
select sql_text from
v\$sqltext_with_newlines
where hash_value=$1
order by piece;
set heading off
select '--------------------------------------------------------------------------------' from dual
union all
select '| Operation | PHV/Object Name | Rows | Bytes| Cost |' as "Optimizer Plan:" from dual
union all
select '--------------------------------------------------------------------------------' from dual
union all
select *
from (select
rpad('|'||substr(lpad(' ',1*(depth-1))||operation||
decode(options, null,'',' '||options), 1, 62), 63, ' ')||'|'||
rpad(decode(id, 0, '----- '||to_char(hash_value)||' -----'
, substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
||' ',1, 20)), 21, ' ')||'|'||
lpad(decode(cardinality,null,' ',
decode(sign(cardinality-10000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',
decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ',
decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
from v\$sql_plan
where hash_value = $1)
union all
select '--------------------------------------------------------------------------------' from dual;
exit
EOF
8 使用shell腳本+spid獲取執行計劃以及sql語句
$ORACLE_HOME/bin/sqlplus -s /nolog<
connect / as sysdba;
set lines 121
set pages 999
col sql_text format a80
select sql_text from
v\$sqltext_with_newlines a,v\$session b,v\$process c
where a.hash_value=b.sql_hash_value and b.paddr=c.addr
and c.spid=$1
order by a.piece;
set heading off
select '--------------------------------------------------------------------------------' from dual
union all
select '| Operation | PHV/Object Name | Rows | Bytes| Cost |' as "Optimizer Plan:" from dual
union all
select '--------------------------------------------------------------------------------' from dual
union all
select *
from (select
rpad('|'||substr(lpad(' ',1*(a.depth-1))||a.operation||
decode(a.options, null,'',' '||a.options), 1, 62), 63, ' ')||'|'||
rpad(decode(a.id, 0, '----- '||to_char(a.hash_value)||' -----'
, substr(decode(substr(a.object_name, 1, 7), 'SYS_LE_', null, a.object_name)
||' ',1, 20)), 21, ' ')||'|'||
lpad(decode(a.cardinality,null,' ',
decode(sign(a.cardinality-10000), -1, a.cardinality||' ',
decode(sign(a.cardinality-1000000), -1, trunc(a.cardinality/1000)||'K',
decode(sign(a.cardinality-1000000000), -1, trunc(a.cardinality/1000000)||'M',
trunc(a.cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(a.bytes,null,' ',
decode(sign(a.bytes-1024), -1, a.bytes||' ',
decode(sign(a.bytes-1048576), -1, trunc(a.bytes/1024)||'K',
decode(sign(a.bytes-1073741824), -1, trunc(a.bytes/1048576)||'M',
trunc(a.bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(a.cost,null,' ',
decode(sign(a.cost-10000000), -1, a.cost||' ',
decode(sign(a.cost-1000000000), -1, trunc(a.cost/1000000)||'M',
trunc(a.cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
from v\$sql_plan a,v\$session b,v\$process c
where a.hash_value =b.sql_hash_value and b.paddr=c.addr and c.spid=$1 )
union all
select '--------------------------------------------------------------------------------' from dual;
exit
EOF
9 查找當前活動進程以及對應具體操作
Select sid,serial#,username,status,command,machine,sql_hash_value,prev_hash_value
From v$session where status=’ACTIVE’ and type<>’BACKGROUND’;
然后通過sql_hash_value查找具體語句
10 查看數據版本以及位數
10.1 使用file
cd $ORACLE_HOME/bin
[oracle@localhost bin]$ file oracle
oracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.2.5, dynamically linked (uses shared libs), not stripped
10.2 查看視圖
/home/oracle> sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.7.0 - Production on Mon Sep 29 16:07:04 2008
Copyright (c) 1982, 2002, Oracle Corporation.??All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning and Real Application Clusters options
JServer Release 9.2.0.7.0 - Production
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE? ? 9.2.0.7.0? ?? ? Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production
10.3 檢查lib,lib32
cd $ORACLE_HOME/lib
cd $ORACLE_HOME/lib32
如果這兩個目錄都存在就說明是64位的,如果只有一個lib目錄說明是32位的。
10.4 查看登錄信息
bilut42:/app/orarac#sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.5.0 - Production on Mon Sep 29 15:14:00 2008
Copyright (c) 1982, 2002, Oracle Corporation.??All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 – Production
10.5 通過kopm$查看
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 1月 16 16:05:54 2009
Copyright (c) 1982, 2005, Oracle.??All rights reserved.
idle>conn sys/admin@emrep as sysdba
已連接。
sys@EMREP>select * from v$version;
BANNER
---------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE? ? 10.2.0.1.0? ?? ?Production
TNS for HPUX: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
sys@EMREP>select metadata from kopm$;
METADATA
----------------------------------------------------------------------------------
0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A050505050504050607080823472347081123081141B047008303690367130000
0
000000000000000000000000000000000000000000000000000000000000000000000
sys@EMREP>conn sys/admin@ora11g as sysdba
已連接。
sys@ORCL>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE? ? 11.1.0.6.0? ?? ?Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
sys@ORCL>select metadata from kopm$;
METADATA
------------------------------------------------------------------------------------------------------------------
0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A050505050504050607080823472323081123081141B0230083035407D0030000
0000000000000000000000000000000000000000000000000000000000000000000000
B047代表??64bit
B023代表 32bit
11 查看數據庫鎖信息
以上sql語句查詢出的都是堵塞進程,ctime代表堵塞的時間
select * from v$lock where block=1;
通過以下sql可以查看對應堵塞進程在做什么操作
Select a.sql_text from v$sqltext a,v$session b,v$lock c
Where a.hash_value=b.sql_hash_value and b.sid=c.sid and c.block=1;
12 殺數據庫進程
Select sid,serial# from v$session where sid=&sid;
Select b.spid from v$session a,v$process b where a.paddr=b.addr and a.sid=&sid;
方法一：從數據庫殺進程
Alter system kill session ‘&sid,&serial#’;
方法二：從操作系統殺掉進程
kill -9 spid
注意使用這種方法一定要確認所殺的進程是不是數據庫后臺進程，方法如下：
ps –ef|grep spid如果輸出是帶有LOCAL=NO信息可以殺掉，
如果不是需要在數據庫里面進一步確認：
select a.type from v$session a,v$process b
where a.paddr=b.addr and b.spid=&spid;
如果沒輸出就不是后臺進程。
13 監控索引
啟用索引監控
Alter index ind_name monitoring usage;
停止索引監控：
Alter index ind_name nomonitoring usage;
查看索引是否使用：
Select * from v$object_usage where index_name=’&index_name’;
打開索引一段時間后，查看以上視圖，如果monitoring=no說明索引沒有使用,可以刪掉
14 查看undo表空間使用情況
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,
s.program,
r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname? ? r,
sys.v_$session? ???s,
sys.v_$transaction t,
sys.v_$parameter? ?x
WHERE s.taddr = t.addr
AND r.usn? ?= t.xidusn(+)
AND x.name??= 'db_block_size';
15 查看temp表空間排序情況
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$sort_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;
16 查看表空間使用百分比
SELECT d.tablespace_name Name,
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') Size_M,
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999')||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0), '99999999.999') Used_M,
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00')||'%' Used
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.tablespace_name Name,
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') Size_M,
TO_CHAR(NVL(t.bytes, 0)/1024/1024,'99999999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999999.999') Used_M,
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00')||'%' Used
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND
d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY'
order by 4 desc;
17 查找無效的對象
select owner,object_type,object_name from dba_objects where status='INVALID';
select constraint_name,table_name from dba_constraints where status='INVALID';
18 查找未定義索引的表
select table_name from user_tables
where table_name not in (select table_name from user_ind_columns);
19 查找某個進程，并對它進行跟蹤
select s.sid,s.serial# from v$session s,v$process p where s.paddr=p.addr and p.spid=&1;
exec dbms_system.SET_SQL_TRACE_IN_SESSION(&1,&2,true);--開始跟蹤
exec dbms_system.SET_SQL_TRACE_IN_SESSION(&1,&2,false);--結束跟蹤
結束跟蹤后會在$ORACLE_BASE/admin/$SID/udump目錄下產生一個文件，這個文件里面包括相關sql的信息
20 顯示用戶正在執行什么操作
SELECT OSUSER,SERIAL#,SQL_TEXT
FROM V$SESSION, V$SQL
WHERE
V$SESSION.SQL_ADDRESS=V$SQL.ADDRESS
AND V$SESSION.STATUS = 'ACTIVE';
21 查找消耗CPU資源高的語句
Select hash_value,LIO
From (
Select HASH_VALUE，BUFFER_GETS/EXECUTIONS LIO from v$sqlarea
Where EXECUTIONS<>0 order by 2 desc)
Where rownum<11;
通過hash_value定位sql語句
22 查找消耗磁盤IO高的語句
Select hash_value,WIO
From (
Select HASH_VALUE，DISK_READS/EXECUTIONS WIO from v$sqlarea
Where EXECUTIONS<>0 order by 2 desc)
Where rownum<11;
通過hash_value定位sql語句
23 查看正在執行全表掃描語句相關信息
SELECT
sid,serial#,target,opname,sofar,totalwork,round((sofar/totalwork)*100,2)? ?completed,sql_hash_value
FROM gv$session_longops
WHERE sofar<>totalwork and totalwork<>0 and opname not like 'RMAN%'
order by 8;
通過sid可以查找對應進程信息。
通過sql_hash_value可以查找對應sql語句。
24 查看數據庫是否啟用歸檔
Select log_mode from v$database;
Noarchivelog代表未啟用歸檔
Archivelog? ?代表啟用歸檔
也可以在sys用戶下執行：archive log list;
SQL> archive log list;
Database log mode? ?? ?? ?? ???No Archive Mode
Automatic archival? ?? ?? ?? ? Disabled
Archive destination? ?? ?? ?? ?/app/oracle/product/10.2.0//dbs/arch
Oldest online log sequence? ???9
Current log sequence? ?? ?? ???10
25 查看數據庫使用什么優化器
SQL> show parameter optimizer_mode
NAME? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?TYPE? ?? ???VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode? ?? ?? ?? ?? ?? ?? ???string? ?? ?ALL_ROWS
修改優化器：
alter system set optimizer_mode=rule;
26 導入導出規則(exp/imp)
導出規則:使用低版本exp導出
導入規則:使用目標版本imp導入
+-----------+------------------------------+
|??EXPORT |? ?? ?IMPORT into:? ?? ?? ?|
|? ?from? ?+---------+---------+---------+
|? ? \/? ???| 9.2.0? ?| 10.1.0??| 10.2.0??|
+-----------+---------+---------+---------+
|??8.0.3? ? |EXP803 | EXP803 | EXP803 |
|??8.0.4? ? |EXP804 | EXP804 | EXP804 |
|??8.0.5? ? |EXP805 | EXP805 | EXP805 |
|??8.0.6? ? |EXP806 | EXP806 | EXP806 |
+-----------+---------+---------+---------+
|??8.1.5? ? |EXP815 | EXP815 | EXP815 |
|??8.1.6? ? |EXP816 | EXP816 | EXP816 |
|??8.1.7? ? |EXP817 | EXP817 | EXP817 |
+-----------+---------+---------+---------+
|??9.0.1? ? |EXP901 | EXP901 | EXP901 |
|??9.2.0? ? |EXP920 | EXP920 | EXP920 |
+-----------+---------+---------+---------+
| 10.1.0? ? |EXP920 |? ?? ?? ?? ? 1)? ? |
| 10.2.0? ? |EXP920 |? ?? ?? ?? ? 1)? ? |
+-----------+---------+---------+---------+
注:
1) 出于性能考慮建議使用expdp和impdp導入導出
2) Oracle Database 11g不支持exp和imp導入,可以使用,但是出現問題oracle不會解決該問題
+-----------+---------------------------------+
|??EXPORT |? ?? ? IMPORT into:? ?? ?? ? |
|? ?from? ?+---------+----------+----------+
|? ? \/? ???| 9.2.0? ?| 10.1.0? ?| 10.2.0??|
+-----------+---------+----------+----------+
|??8.0.3? ? |IMP920 | IMP1010 | IMP1020 |
|??8.0.4? ? |IMP920 | IMP1010 | IMP1020 |
|??8.0.5? ? |IMP920 | IMP1010 | IMP1020 |
|??8.0.6? ? |IMP920 | IMP1010 | IMP1020 |
+-----------+---------+----------+----------+
|??8.1.5? ? |IMP920 | IMP1010 | IMP1020 |
|??8.1.6? ? |IMP920 | IMP1010 | IMP1020 |
|??8.1.7? ? |IMP920 | IMP1010 | IMP1020 |
+-----------+---------+----------+----------+
|??9.0.1? ? |IMP920 | IMP1010 | IMP1020 |
|??9.2.0? ? |IMP920 | IMP1010 | IMP1020 |
+-----------+---------+----------+----------+
| 10.1.0? ? |IMP920 |? ?? ?? ?? ? 1)? ?? ?|
| 10.2.0? ? |IMP920 |? ?? ?? ?? ? 1)? ?? ?|
+-----------+---------+----------+----------+
注:
1) 出于性能考慮建議使用expdp和impdp導入導出
2) Oracle Database 11g不支持exp和imp導入,可以使用,但是出現問題oracle不會解決該問題
贊賞
共11人贊賞
本站是提供個人知識管理的網絡存儲空間，所有內容均由用戶發布，不代表本站觀點。如發現有害或侵權內容，請點擊一鍵舉報。
總結
以上是生活随笔為你收集整理的oracle 运营维护_Oracle数据库日常运维常用脚本的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 刺客信条英灵殿怎么设置中文
- 下一篇： 地下城与勇士DNF称号簿里边那个疯狂觉醒