oracle巡檢腳本
A. oracle設置環境變數腳本
#!/bin/bash
#centos oracle g 前期安裝環境檢查腳本
#將所需要安裝的包（rpm格式）與腳本放在一個目錄下
ss=$PWD
cd $ss
rpm ivh * rpm
#ORACLE_BASE=/opt/oracle
echo n 輸入ORACLE_BASE（默認/opt/oracle）:
read ORACLE_BASE
if [ z $ORACLE_BASE ]
then
ORACLE_BASE=/opt/oracle
fi
#ORACLE_HOME=$ORACLE_BASE/proct/ /db_
echo n 輸入 HOME （默認 proct/ /db_ ):
read HOME
if [ z $HOME ]
then
HOME=proct/ /db_
fi
ORACLE_HOME=$ORACLE_BASE/$HOME
#ORACLE_SID=oral
echo n 輸入 ORACLE_SID (默認 orcl):
read ORACLE_SID
if [ z $ORACLE_SID ]
then
困宴ORACLE_SID=orcl
fi
USER=oracle
SYSCTL=/etc/nf
LIMITS=/etc/security/nf
PAM=/etc/pam d/login
PROFILE=/etc/profile
BASH_PROFILE=/home/oracle/ bash_profile
#IPADDR=`ifconfig eth |grep inet addr |cut d : f | cut d f `
#HOSTS=/etc/hosts
#NEORK=/etc/sysconfig/neork
信尺局#hostname
#grep v HOSTNAME $NEORK > $NEORK
#echo HOSTNAME=$HOSTNAME >> $NEORK
#echo $IPADDR $HOSTNAME >> $HOSTS
#加入用戶
useradd g oinstall G dba $USER
groupadd oinstall
groupadd dba
mkdir p $ORACLE_BASE
chown R $USER:oinstall $ORACLE_BASE
#內核設置
cat >> $SYSCTL << EOF
kernel msgmni=
kernel shmmni=
kernel sem=
fs file max=
net ipv ip_local_port_range=
net ipv tcp_sack=
net ipv tcp_timestamps=
net ipv tcp_max_syn_backlog=
net ipv tcp_keepalive_time=
re rmem_default=
re rmem_max=
re wmem_default=
re wmem_max=
EOF
#限制設置
cat>> $LIMITS <<EOF
oracle soft nproc
oracle hard nproc
oracle滑讓 soft nofile
oracle hard nofile
EOF
#安全設置
cat >> $PAM <<EOF
session required /lib/security/pam_limits so
session required pam_limits so
EOF
#全局環境變數設置
cat >> $PROFILE <<EOF
if [ $USER = oracle ]; then
if [ $SHELL = /bin/ksh ]; then
ulimit p
ulimit n
else
ulimit u n
fi
fi
EOF
#oracle環境變數設置
cat >> $BASH_PROFILE <<EOF
export LC_CTYPE=en_US UTF
export ORACLE_SID=orcl
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/proct/ /db_
export TNS_ADMIN=$ORACLE_HOME/neork/admin
export ORA_DB=$ORACLE_HOME/dbs
export ORACLE_BDUMP=$ORACLE_BASE/shtkt/bmp
export ORACLE_TERM=xterm
export NLS_LANG=AMERICAN_AMERICA ZHS GBK
export ORA_NLS =$ORACLE_HOME/omon/nls/admin/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib
export LIBPATH=$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib
export CLASSPATH=$ORACLE_HOME/JRE/lib:$ORACLE_HOME/JRE/lib/rt jar:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export PATH
umask
EOF
source /home/oracle/ bash_profile
sleep
export DISPLAY=:
xhost +
export LC_ALL=en_US
lishixin/Article/program/Oracle/201311/18577
B. 誰有windows下巡檢Oracle RAC的腳本
1.列出配置的所有資料庫
srvctl config database
2.顯示指定集群資料庫的所有服務
srvctl config service -d GDTV
3.查看所有實例和服務的狀態
srvctl status database -d GDTV
4.查看單個實例的狀態
srvctl status service -d GDTV -s <service_name>
5.特定節點上節點應用程序的狀態
srvctl status nodeapps -n DBSERVER1
srvctl status nodeapps -n DBSERVER2
6.列出RAC資料庫的配置
srvctl config database -d GDTV
7.顯示節點應用程序的配置 —（VIP、GSD、ONS、監聽器）
srvctl config nodeapps -n DBSERVER1 -a -g -s -l
srvctl config nodeapps -n DBSERVER2 -a -g -s -l
C. oracle 如何編寫定時腳本
用job，具體用法：
declare
n_job binary_integer;
begin
dbms_job.submit(n_job, '你要執行的東西', sysdate, TRUNC(LAST_DAY(SYSDATE))+4+2/24);
end;
-- 每月4號執行
dbms_job.submit(n_job_01,'你調的東西',sysdate,'TRUNC(LAST_DAY(SYSDATE))+4+2/24');
-- 每分鍾執行一次
dbms_job.submit(n_job_01,'你調的東西',sysdate,'TRUNC(sysdate,』mi』) + 1 / (24*60)');
-- 凌晨兩點執行
dbms_job.submit(n_job_01,'你調的東西',sysdate,'TRUNC(sysdate) + 1 + 2/24');
-- 每周一凌晨2點執行 周一是每周的第二天next_day(sysdate,2)同理周二是第三天,next_day(sysdate,3)
dbms_job.submit(n_job_01,'你調的東西',sysdate,'TRUNC(next_day(sysdate,2))+2/24');
-- 每月1日凌晨兩點執行
dbms_job.submit(n_job_01,'你調的東西',sysdate,'TRUNC(LAST_DAY(SYSDATE))+1+2/24');
-- 每季第一天凌晨兩點執行
dbms_job.submit(n_job_01,'你調的東西',sysdate,'TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24');
-- 每年7月1日和1月1日凌晨2點
dbms_job.submit(n_job_01,'你調的東西',sysdate,'ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24');
-- 每年1月1日凌晨2點執行
dbms_job.submit(n_job_01,'你調的東西',sysdate, 'Add_months(trunc(sysdate,'yyyy'), 12) +2/24');
D. ORACLE DBA常用sql腳本工具-管理篇(1)
在較長時間的與oracle的交往中 每個DBA特別是一些大俠都有各種各樣的完成各種用途的腳本工具 這樣很方便的很快捷的完成了日常的工作 下面把我常用的一部分展現給大家 此篇主要側重於資料庫管理 這些腳本都經過嚴格測試 表空間統計 A 腳本說明 這是我最常用的一個腳本 用它可以顯示出資料庫中所有表空間的狀態 如表空間的大小 已使用空間 使用的百分比 空閑空間數及現在表空間的最大塊是多大 B 腳本原文:SELECT upper(f tablespace_name) 表空間名 d Tot_grootte_Mb 表空間大小(M) d Tot_grootte_Mb f total_bytes 已顫模使用空間乎亮(M) to_char(round((d Tot_grootte_Mb f total_bytes) / d Tot_grootte_Mb * ) ) 使用比 f total_bytes 空閑空間(M) f max_bytes 最大塊(M) FROM(SELECT tablespace_name round(SUM(bytes)/( * ) ) total_bytes round(MAX(bytes)/( * ) ) max_bytesFROM sys dba_free_spaceGROUP BY tablespace_name) f (SELECT dd tablespace_name round(SUM(dd bytes)/( * ) ) Tot_grootte_MbFROM sys dba_data_files ddGROUP BY dd tablespace_name) dWHERE d tablespace_name = f tablespace_nameORDER BY DESC; 查看無法擴展的段 A 腳本說明 ORACLE對一個段比如表段或索引無法擴展時 取決的並不是表空間中剩餘的空間是多少 而是取於這些剩餘空間中最大的塊是否夠表比索引的 NEXT 值大 所以有時一個表空間剩餘幾個G的空閑空間 在你使用時ORACLE還是提示某個表或索引無法擴展 就是由於這一點 這時說明空間的碎片太多了 這個腳本是找出無法擴展的段的一些信息 B 腳本原文 SELECT segment_name segment_type owner a tablespace_name tablespacename initial_extent/ inital_extent(K) next_extent/ next_extent(K) pct_increase b bytes/ tablespace max free space(K) b sum_bytes/ tablespace total free space(K) FROM dba_segments a (SELECT tablespace_name MAX(bytes) bytes SUM(bytes) sum_bytes FROM dba_free_space GROUP BY tablespace_name) bWHERE a tablespace_name=b tablespace_nameAND next_extent>b bytesORDER BY ; 查看段(表段 索引段)所使用空間的大小 A 腳本說明 有時你可能想知道一個表或一個索引佔用多少M的空間 這個腳本就是滿足你的要求的 把<>中的內容替換一下就可以了 B 腳本原文 SELECT owner segment_name SUM(bytes)/ / FROM dba_segmentsWHERE owner=<segment owner>And segment_name=<your table or index name>歲洞寬GROUP BY owner segment_nameORDER BY DESC; 查看資料庫中的表鎖 A 腳本說明 這方面的語句的樣式是很多的 各式一樣 不過我認為這個是最實用的 不信你就用一下 無需多說 鎖是每個DBA一定都涉及過的內容 當你相知道某個表被哪個session鎖定了 你就用到了這個腳本 B 腳本原文 SELECT A OWNER A OBJECT_NAME B XIDUSN B XIDSLOT B XIDSQN B SESSION_ID B ORACLE_USERNAME B OS_USER_NAME B PROCESS B LOCKED_MODE C MACHINE C STATUS C SERVER C SID C SERIAL# C PROGRAMFROM ALL_OBJECTS A V$LOCKED_OBJECT B SYS GV_$SESSION CWHERE ( A OBJECT_ID = B OBJECT_ID )AND (B PROCESS = C PROCESS ) ANDORDER BY ; 處理存儲過程被鎖 A 腳本說明 實際過程中可能你要重新編譯某個存儲過程理總是處於等待狀態 最後會報無法鎖定對象 這時你就可以用這個腳本找到鎖定過程的那個sid 需要注意的是查v$access這個視圖本來就很慢 需要一些布耐心 B 腳本原文 SELECT * FROM V$ACCESSWHERE owner=<object owner>And object<procere name> 查看回滾段狀態 A 腳本說明這也是DBA經常使用的腳本 因為回滾段是online還是full是他們的關懷之列嘛B SELECT a segment_name b statusFROM Dba_Rollback_Segs a v$rollstat bWHERE a segment_id=b usnORDER BY 看哪些session正在使用哪些回滾段 A 腳本說明 當你發現一個回滾段處理full狀態 你想使它變回online狀態 這時你便會用alter rollback segment rbs_seg_name shrink 可很多時侯確shrink不回來 主要是由於某個session在用 這時你就用到了這個腳本 找到了sid的serial#餘下的事就不用我說了吧 B 腳本原文SELECTr name 回滾段名 s sid s serial# s username 用戶名 s status t cr_get t phy_io t used_ublk t noundo substr(s program ) 操作程序FROM sys v_$session s sys v_$transaction t sys v_$rollname rWHEREt addr = s taddr and t xisn = r usn AND r NAME IN ( ZHYZ_RBS )ORDERBY t cr_get t phy_io 查看正在使用臨時段的session A 腳本說明 許多的時侯你在查看哪些段無法擴展時 回顯的結果是臨時段 或你做表空間統計時發現臨段表空間的可用空間幾乎為 這時按oracle的說法是你只有重新啟動資料庫才能回收這部分空間 實際過程中沒那麼復雜 使用以下這段腳本把佔用臨時段的session殺掉 然後用alter tablespace temp coalesce;這個語句就把temp表空間的空間回收回來了 B 腳本原文SELECT username sid serial# sql_address machine program tablespace segtype contentsFROM v$session se v$sort_usage suWHERE se saddr=su session_addr(待續) lishixin/Article/program/Oracle/201311/18647
E. 關於Oracle一些常用腳本的匯總(2)
碎片檢查
select tablespace_name sqrt(max(blocks)/sum(blocks))*
( /sqrt(sqrt(count(blocks)))) fsfi
from dba_free_space
group by tablespace_name order by ;
敏告fsfi值越小 碎片越大 自由空間碎片索引
檢查reverse_key index
select o object_name
from dba_objects o
where wner= DB_ACCT
AND O OBJECT_ID IN
(SELECT I OBJ# FROM SYS IND$ I
WHERE BITAND(I PROPERTY )= )
查具體後台進程號
select spid from v$session a v$process b where a PADDR=b ADDR and sid=
查看死鎖表
SELECT SID DECODE(BLOCK NO YES ) BLOCKER
DECODE(REQUEST NO YES ) WAITER
FROM V$LOCK
WHERE REQUEST > OR BLOCK >
ORDER BY block DESC;
查看剩餘表空間
select a tablespace_name free/total* pct_free free/ / free(M) from
(select sum(bytes) free tablespace_name from dba_free_space group by tablespace_name) a
(select sum(bytes) total tablespace_name from dba_data_files group by tablespace_name) b
where a tablespace_name=b tablespace_name
order by pct_free;
橋扒明查看創建索引的進度
select sid message from v$session_longops where sid = order by start_time
查看繳費到帳
SELECT AREA_ID to_char(sysdate yyyy mm dd hh :mi:ss ) to_char(count( )) as cnt FROM ACCT_PAY_INTERFACE
WHERE PAY_DATE>=sysdate and FLAG= group by AREA_Id
查看最消耗資源的sql
SELECT * FROM (SELECT PARSING_USER_IDEXECUTIONS SORTS MAND_TYPE
DISK_READS sql_text FROM v$sqlareaORDER BY disk_reads DESC ) WHERE ROWNUM<
查看佔用系此碼統資源的進程號spid
SELECT a username a machine a program a sid a serial# a status c piece c sql_text
FROM v$session a v$process b v$sqltext cWHERE b spid= AND b addr=a paddrAND a sql_address=c address(+)
ORDER BY c piece
查看佔用系統io較大的session
SELECT se sid se serial# pr SPID se username se status se terminal se program
se MODULE se sql_address st event st p text si physical_reads si block_changes
FROM v$session se v$session_wait st v$sess_io si v$process pr WHERE st sid=se sid AND st sid=si sid
AND se PADDR=pr ADDR AND se sid> AND st wait_time= AND st event NOT LIKE %SQL% ORDER BY physical_reads DESC
對檢索出的結果的幾點說明
我是按每個正在等待的session已經發生的物理讀排的序 因為它與實際的IO相關
你可以看一下這些等待的進程都在忙什麼 語句是否合理？
Select sql_address from v$session where sid=;
Select * from v$sqltext where address=;
執行以上兩個語句便可以得到這個session的語句
你也以用alter system kill session sid serial# ;把這個session殺掉
應觀注一下event這列 這是我們調優的關鍵一列 下面對常出現的event做以簡要的說明
a buffer busy waits free buffer waits這兩個參數所標識是dbwr是否夠用的問題 與IO很大相關的 當v$session_wait中的free buffer wait的條目很小或沒有的時侯 說明你的系統的dbwr進程決對夠用 不用調整 free buffer wait的條目很多 你的系統感覺起來一定很慢 這時說明你的dbwr已經不夠用了 它產生的wio已經成為你的資料庫性能的瓶頸 這時的解決辦法如下
a 增加寫進程 同時要調整db_block_lru_latches參數
示例 修改或添加如下兩個參數
db_writer_processes=
db_block_lru_latches=
a 開非同步IO IBM這方面簡單得多 hp則麻煩一些 可以與Hp工程師聯系
b db file sequential read 指的是順序讀 即全表掃描 這也是我們應該盡量減少的部分 解決方法就是使用索引 sql調優 同時可以增大db_file_multiblock_read_count這個參數
c db file scattered read 這個參數指的是通過索引來讀取 同樣可以通過增加db_file_multiblock_read_count這個參數來提高性能
d latch free 與栓相關的了 需要專門調節
e 其他參數可以不特別觀注
外部聯接 + 的用法
外部聯接 + 按其在 = 的左邊或右邊分左聯接和右聯接
若不帶 + 運算符的表中的一個行不直接匹配於帶 + 預算符的表中的任何行
則前者的行與後者中的一個空行相匹配並被返回 若二者均不帶 +
則二者中無法匹配的均被返回 利用外部聯接 +
可以替代效率十分低下的 not in 運算 大大提高運行速度 例如 下面這條命令執行起來很慢
select a empno from emp a where a empno not in
(select empno from emp where job= SALE );
倘若利用外部聯接 改寫命令如下:
select a empno from emp a emp b
where a empno=b empno(+)
and b empno is null
and b job= SALE ;
可以發現 運行速度明顯提高
如何更改UNDO tablespace
create undo tablespace undotbs datafile D:oracleproct oradataqaundotbs dbf size M;
alter system set undo_tablespace=undotbs scope=both;
create pfile from spfile;
alter tablespace undotbs offline;
drop tablespace undotbs including contents;
將表改成
ALTER TABLE t_monitor_real_minute NOLOGGING;
Oracle RAC的參數文件和單實例參數文件不同 所以修改參數文件時需要注意
首先設置歸檔路徑
SQL> alter system set log_archive_dest= /opt/oracle/archive scope=spfile sid= * ;
System altered
SQL> select sid name value from v$spparameter where name= log_archive_dest ;
SID NAME VALUE
* log_archive_dest /opt/oracle/archive
然後關閉兩個實例 啟動實例 更改資料庫為歸檔模式
SQL> shutdown immediate;
Database closed
Database di *** ounted
ORACLE instance shut down
SQL> startup mount;
ORACLE instance started
Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
Database mounted
SQL> alter database archivelog;
Database altered
SQL> alter database open;
Database altered
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archive
Oldest online log sequence
Next log sequence to archive
Current log sequence
lishixin/Article/program/Oracle/201311/17504
F. 幾個簡單常用的oracle許可權查詢腳本
你好：下面語句中有你需要的這個許可權:select any table(如果針對單個用戶的表話，這個沒有，只能一個表一個表的授權)
創建用戶並授權
上接第一步。
在SQL>後面輸入創建用戶的語句：
create user vpetl identified by vpetl
default tablespace TBS_ETL_DATA;
回車，出現「User created.」表示用戶創建成功。
在SQL>後面輸入給用戶授權的語句：
grant connect,resource,alter system,debug connect session,select any table,delete any table,drop any table,alter any table to vpetl;
回車，出現「Grant succeeded.」表示給用戶授權成功。
在SQL>後面輸入給用戶授權的語句：
grant execute on DBMS_LOCK to vpetl;
回車，出現「Grant succeeded.」表示給用戶授權成功。
在SQL>後面輸入給用戶授權的語句：
grant select on v_$session to vpetl;
回車，出現「Grant succeeded.」表示給用戶授權成功。
在SQL>後面輸入給用戶授權的語句：
grant select on v_$ACCESS to vpetl;
回車，出現「Grant succeeded.」表示給用戶授權成功。
在SQL>後面輸入給用戶授權的語句：
grant alter system to vpetl;
回車，出現「Grant succeeded.」表示給用戶授權成功。
在SQL>後面輸入給用戶授權的語句：
grant insert any table,update any table to vpetl;
回車，出現「Grant succeeded.」表示給用戶授權成功。
G. Linux下監控oracle運行的腳本設置為每天上午8時定時執行。
用at命令添加計劃隊列~~
具體用法請閱讀at自帶的幫助說明。
H. 在oracle中怎麼把查詢語句保存成腳本,然後執行
你可以先帆數建一aaa.TXT文空者件，保存SQL語句後，在SQL PLUS或是可以執行SQL語句的地方這樣寫 !c:\aaa.txt; 嘆號是執行的命令，一態虧首定要記得寫。