Oracle Life -DBA的一天

jude_king發表於2016-06-27
Oracle
D1：活動狀態檢查
1.1 例項狀態
select instance_name,status from v$instance;
查詢返回例項名稱、狀態、正常狀態為 Open 。
1.2 會話資訊
select sessions_current,session_highwater from v$license 
輔助查詢，例項當前會話數和啟動最高連線會話數量。
select inst_id,username,count(*) from gv$session group by inst_id,username;
查詢資料庫連線數以例項和使用者組。
1.3引數檢查
select value from v$parameter where name='open_cursors 
查詢給定引數的設定值，示例引數預設值為300，通常中等規模資料庫 推薦設定為 1000.
1.4 引數修改
alter system set undo_retention=3600  commit='default 900' sid='*' scope=both 
修改給定的初始化引數， RAC 環境需要注意 SID 引數。
1.5 隱含引數
alter  system  set "_optimizer_use_feedback"=false scope = spfile;
為了解決特殊問題，有時需要設定下劃線開頭的隱含引數
1.6例項異常
當連線資料庫例項出現緩慢、掛起等現象，需要進行診斷和分析，甚至可能需要重新啟動資料庫例項。
  1.6.1 資訊採集
SQL > sqlplus -prelim / as sysdba
SQL > oradebug setmypid
SQL > oradebug unlimit
SQL > oradebug hanganalyze 3
SQL > oradebug dump systemstate 266
< 間隔一定時間，如20秒 執行下一次資料取樣>
SQL >oradebug hanganalyze 3
SQL > oradebug dump systemstate 266
師範命令，通過採集系統的Hang 資訊、系統狀態資訊等，可以分析系統掛起的原因，間隔取樣，可以用來對比變化，輔助分析。
 1.6.2 跟蹤
SQL> alter session set events '10046 trace name context foreve,level 12';
SQL > shutdown immediate;
SQL >startup mount;
SQL > alter session set events '10046 trace namecontext forever,level 12 ';
如果在資料庫關閉、啟動時遇到阻塞、掛起等，可以通過師範命令進行跟蹤，獲取跟蹤檔案進行分析；
1.6.3 安全停庫
SQL >altersystem checkpoint ;
SQL > alter system aechivelog current;
SQL >shutdown immediate;
如果資料庫出現異常需要重新啟動，可以通過示範命令執行檢查點、歸檔命令,然後嘗試以立即方式關閉資料庫。
1.6.4 強制停庫
SQL > shutdown abort ;
SQL > startup nomount;
SQL > alter database mount;
SQL > alter database open;
如果立即方式不能順利關閉資料庫，強制的關閉方式為abort  示範命令可以通過分步驟的方式執行資料庫啟動。
1.7 連線異常
當連線資料庫出現異常，需要檢測包括網路連通性，監聽器狀態等資訊。
1.7.1連通性
tnsping tns_name
在安裝具有ORACLE 客戶端的環境，可以通過tnsping  工具測試配置的服務名稱，觀察網路是否聯通以及以及響應時間，
1.7.2 監聽器
lsnrctl status listener 
lsnrctl status listener_scan1 
lsnrctl  service 
在資料庫伺服器上，可以通過lsnrctl  工具檢查監聽狀態和服務資訊，具體的監聽伺服器名稱可以在最後定義修改；
1.7.3 監聽 日誌檢查
adrci > show alert 
在伺服器上 可以通過 adrci 工具，顯示各類告警檔案，檢查監聽器日誌，可以診斷監聽問題；

D2 日誌資訊檢查
日誌資訊檢查
日誌狀態檢查資料庫各類日誌資訊，確認資料庫例項、叢集等是否出現錯誤，告警，如存在問題，需要進一步分析和應對。
2.1 告警日誌
$ORACLE_BASE/diag/rdbms//$ORACLE_SID/trace/alert_$ORACLE_SID.log

SQL > show parameter background_dump_dest
根據例項找到告警日誌，檢查例項是否存在 ORA - 錯誤提示等。
2.2 叢集日誌
$GRID_HOME/log//alert.log
$GRID_HOME/log//(cssd  crsd evmd  ohasd)/
在相應路勁找到RAC 叢集日誌，檢查是否存在錯誤提示資訊等。
2.3 ASM 日誌
$GRID_HOME/diag /asm/+asm//trace/alert_log
在相應的路勁找到ASM日誌，檢查是否存在錯誤提示資訊等。

2.4 trace 檔案
SQL > select value from v$diag_info where name='Default Trace File';
SQL > show parameter user_dump_dest 
獲取會話產生或全域性轉儲位置，在診斷時需要檢查最近日期檔案內容。

2.5 叢集狀態
$ crsctl status resource -t 
確保資源狀態顯示線上
2.6 errorstacl 分析 
當遇到ORA - 錯誤，二資料庫的輸出資訊不足時 可以採用 errorstack 進行跟蹤，採集更詳細的轉儲資訊；
SQL >alter system set events='600 trace name errorstack forever,level 10';
SQL >alter system set events='600 trace name errorstack off';
示例 顯示了對  ORA -600  錯誤設定跟蹤，並關閉。

D3 重做日誌維護
重做日誌維護
Oracle redo 日誌是資料庫的核心元件，檢查其狀態，維護其成員，監控其歸檔，稽核其效能，是DBA的重要工作。
3.2 REDO 成員
select  group#,member from v$log_file;
檢視日誌組合成員資訊
3.3 增加日誌組或成員
SQL > alter database add logfile group 10 ('/u01/app/oracle/oradata/ENMOEDU/log2c.rdo') size 500m; 
SQL > alter databese add logfile member '/u01/app/oracle/oradata/ENMOEDU/log3c.rdo' to group 10;
在日誌切換頻繁時，可能需要增加日誌組或者加大日誌
可以檢視alert 日誌 
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';  
select * from (select b.recid,b.first_time,a.first_time last_time,round((a.first_time-b.first_time)*24*60,2) minates  
from v$log_history a,v$log_history b  
where a.recid=b.recid +1   
order by a.first_time desc) 
where rownum<120
3.4 切換日誌
SQL > alter system switch logfile;
切換日誌組，開始寫入下一組日誌；
3.5 執行歸檔
alter system archivelog current
對當前的日誌組執行歸檔，切換到下一個日誌組，
在RAC 會對所有例項執行歸檔，
3.6 刪除日誌組或者成員
 SQL > alter database drop logfile group 1;
SQL > alter database drop logfile member '/u01/app/oracle/dbs/redo03.log';
刪除指定日誌組或日誌成員，注意只有對 inactive 狀態的日誌執行刪除操作。
3.7 歸檔檢查
SQL > archive log list;
檢查資料庫是否處於歸檔模式。
3.8 歸檔狀態變更；
SQL > alter database archivelog | noarchivelog;
在mount  狀態下變更歸檔模式，之一啟動歸檔模式之後
務必制定備份歸檔的日常策略，防止磁碟空間被耗盡。
3.8 調整歸檔路徑
SQL >alter system set log_aechive_dest_2='location=&path' sid = '&sid';

D4 空間資訊檢索
空間資訊檢索
確保資料儲存空間可用，定期檢查表空間餘量，進行表空間和檔案維護。
4.1 空間使用查詢
SQL > select * from  sys.sm$ts_used;
SQL > select * from sys.sm$ts_free;
檢視資料庫表空間的剩餘空間。
4.2 檔案資訊
SQL > select tablespace_name,file_name from dba_data_files;
檢視資料表空間的資料檔案資訊。

4.3 檔案維護
alter database datafile '& path‘ resize 900m;
alter tablespace  & tbs_name add datafile & path size 900m;
對資料庫的表空間容量進行擴容。

D5 鎖/閂資訊檢查
鎖/閂 資訊檢查
lock / latch  是資料庫控制併發的核心手段，檢查相關資訊可以監控資料庫的事務和執行狀況。
5.1 鎖資訊
SQL >select sid,type,lmode,ctime,block from v$lock where type in ('MR','AE');
檢視鎖會話ID 型別，持有時間等，
注意 如果blick >1 可能意味著阻塞了其他會話。
5.2 鎖故障排查
在資料庫出現鎖競爭和阻塞時，需要排查和處理鎖定，必要時通過KILL 阻塞程式消除鎖定。
5.2.1 查詢阻塞會話
SQL > select sid,sql_id,status,blocking_session from v$session where sid in (select session_id from v$locked_object);
查詢當前鎖事務中阻塞會話與被阻塞會話的sid ,sql_id 和狀態資訊
5.2.2 阻塞 SQL 文字
SQL > select sql_id,sql_text from v$sqltext where sql_id = '&sql_id' order by piece;
通過sql_id 查詢得到sql w文字，例如通過sql_id 查詢出阻塞的SQL 語句
5.2.3 鎖阻塞物件資訊
SQL > selectowner,object_name,object_type from dba_objects
where object_id in (select object_id from v$locked_object);
通過sql_id 查詢阻塞物件的詳細資訊如物件名稱，所屬使用者等；
5.2.4 殺阻塞會話
SQL >alter system kill session 'sid,serial#';
在oracle 例項內殺死阻塞的會話程式，其中sid ,serial # 為中止會話對應資訊，來自v$session 
5.2.5 殺系統程式
sql > select pro_spid,pro.program from v$session ses,v$process pro
where ses.sid=&sid and ses.paddr =pro.addr;
#kill -9 spid 
有時對於活動程式，在系統層面中止更為快速安全，示例找到系統程式號，然後kill 中止
注意 無論何時 需要認真分析，並且避免誤殺重要後臺程式。

5.3 閂檢查
select name,gets,misses,immediate_gets,spin_gets from v$latch order by 2;
檢查資料庫閂的使用情況，misses 、spin_gets 統計高的 需要關注。
5.3.1 閂使用檢查
SQL >select addr,gets from v$latch_children where name='cache buffers chains';
SQL > select hladdr,file#,dbablk from x$bh
where hladdr in (select addr from v$latch_children where addr='&addr');
僅供學習，通過獲得latch 的地址 找到該 latch  守護的 x$bh 中相關的  buffer ;

D6 等待和統計資料
等待和統計資料
wait 和statistics  資料分別代表了資料庫的等待和執行資料，觀察這些資料以瞭解資料庫的等待瓶頸 和健康程度。
6.1 等待事件查詢
select sid,event,wait_time_micro from v$session_wait order by 3;
通過等待時間和等待時間 ，瞭解資料庫當前連線會話的等待情況。
注意 如果會話眾多 需要限定查詢輸出行數。
6.2 TOP 10 等待時間
SQL>select * from (select event,total_waits,average_wait,time_waited from v$system_event where wait_class<>'ldle'
ORDER BY time_waited desc ) where rownum<=10;
檢視當前資料中 TOP 10 等待時間，需要分析和關注非空閒的顯著等待。
6.3 會話統計資料
SQL > select s.sid,s.statistic#,n.name,s.value from v$sesstat s, v$statname n
where s.statistics#=n.statistics# and n.name='redo size' and sid ='&dis';
查詢資料庫會話的統計資訊資料，示例查詢了  redo  的大小，sid 需要提供；
6.4 系統級統計資料
SQL> select * from v$sysstat where name = 'redo size';
查詢整個系統的統計資料，示例顯示資料庫例項啟動以來的redo 日誌生成量 

D7 物件檢查
物件檢查
表、索引、分割槽、約束等是資料庫的核心儲存物件，其核心資訊和物件維護是DBA重要的日常工作。
7.1 表資訊資料
SQL > select * from (select owner,table_name,num_rows from dba_tables order by num_rows desc nulls last)
where rownum < 11;
檢視錶的基本資訊資料：屬主，表名，記錄行數等。
7.2 表結構查詢
SQL > set long 12000
SQL > select dbms_metadata.get_ddl('table','&table_name','&user')
from dual;
根據提供的表名使用者（需大寫） 查詢表的表結構資訊(建表語句）。
7.3 表統計資訊
SQL > select owner,table_name,last_analyzed from dba_statistics where owner='&owner' and table_name='&table_name';
查詢給定使用者。給定表，查詢最後的統計資訊分析收集時間。統計資訊影響執行計劃。當SQL 執行異常是，需要重點分析統計資訊；
7.4表統計資訊的收集
SQL > exec dbms_stats.gather_table_stats (ownername=>&owner',tablename=>'&table_name');
收集統計資訊時一項複雜任務，需要詳細設計，示例對給出使用者、表名的物件採集統計資訊。
7.5 索引資訊資料
SQL > select * from (select index_name,table_name,num_rows,leaf_blocks,clustering_factor
from dba_indexes order by 5 desc null last)
where rownum<11;
查詢索引的基本資訊，示例輸出包括葉塊數和聚簇因子等 ，如聚簇因子 過高接近行數可能代表索引效率不高。
7.6 索引定義查詢
SQL > set log 12000
SQL > select dbms_metadata.get_ddl ('index','&table_name','&user')
from dual;
根據提供的表名和使用者名稱，查詢索引的建立語句；
7.7 索引統計資訊及收集的方法
SQL> select owner,index_name,last_analyzed from dba_ind_statistics 
where owner='&owner'and table__name='&table_name';
根據給定的使用者名稱，表使用者（需大寫），查詢索引資訊，尤其應關注最後分析時間。
SQL > exec dbms_stats.gather_table_stats ('user','index');
收集統計資訊時一項複雜任務，例項對給出使用者、索引名的物件採集統計資訊。
7.8 分割槽物件檢查
SQL >select table_name,partitioning_type,partition_count,status from dba_part_tables;
SQL >select table)name,partition_name,high_value from dba_tab_partitions where rownum<11;
檢視分割槽表的基本資訊；分割槽型別，數量。邊界值等 ；
7.9 
分割槽表定義查詢
SQL > select dbms_metadata_ddl('table','&part_table_name','user')from dual 
根據給定的分割槽表名、使用者（需大寫） 查詢表分割槽的結構資訊（建表語句）。
7.10 分割槽統計資訊相關
SQL> select owner,table_namepartition_name,last_analyzed from dba_tab_statistics
where owner = '&owner'and table_name ='&table_name';
SQL >   exec dbms_stats.gather_table_stats('owner','table_name')
檢視分割槽表的統計資訊收集時間，以及對分割槽表進行手工收集統計資訊，逐一分割槽表統計資訊收集非常複雜，需要深入研究做出正確策略；示例只是提供簡單的收集命令；
7.11 約束資訊
SQL>select constrsint_name,constraint_type from dba_constraints
where table_name='&table_name';
查詢指定表資料表的約束資訊，包括名稱和型別；

7.12 失效物件檢查
SQL > select owner,object_name,object_type,status from dba_objects where status <>'valid'
order by owner,object_name;
檢查資料庫中的失效物件資訊，通常執行健康的資料庫不應有失效的物件。
7.13 閃回查詢
7. 13.1 時間閃回
SQL > select * from &table_name as of timestapt to timestamp('2015-5-25 21:02:00','yyyy-mm-dd hh24:mi:ss');
閃回表資料，基於時間點的表資料閃回查詢。
7.13.2 SCN  閃回
SQL >select * from table_name as of scn & scn :
閃回表資料，基於SCN 的表資料查詢，需要提供SCN 
7.13.3 閃回 drop 
SQL > falshback table & table to before drop rename to & new_table;
閃回刪除操作，對已經刪除的表進行閃回回復並重新命名；

D8 AWR 報告檢查
AWR 報告檢查
通過AWR 報告交接日常高峰時段資料庫各項指標和執行狀況。通過對比觀察和基線的變化。通過趨勢分析持續關注資料庫日常執行狀態。
8.1 本地AWR
SQL >@?/rdbms/admin/awrrpt
生成本地AWR 報告資訊。需要根據提示輸入相應的資訊。
8.2 指定例項AWR 
SQL> @?/rdbms/admin/awrrpti
8.3 AWR 對比報告
SQL > @?/rdbms/admin/awrddrpt
生成本地AWR 時間段對比報告
8.4 指定例項對比
SQL>@?/rdbms/admin/awrddrpi
生成指定例項AWR時間段對比報告
8.5 AWR資訊提取
SQL > @?/rdbms/admin/awrextr
使用awrextr指令碼將AWR 效能資料匯出，可用與留錯或者異地分析。
8.6AWR 資訊分析
SQL> @?/rdbms/admin/awrload
通過AWRLOAD 可以將匯出的AWR 效能資料匯入到其他資料庫中。便於集中分析。


D9 SQL報告檢查
對TOP SQL進行持續關注和分析，通過SQL報告分析SQL的效率、效能，並做出報告和優化建議等。
9.1 本地SQLReport
SQL> @?/rdbms/admin/awrsqrpt
生成本地SQLReport報告
9.2 指定例項SQLReport
SQL>@?/rdbms/admin/awrsqrpi
生成指定例項SQLRerport報告
9.3 當前會話SQL Monitor Report
SELECT
dbms_sqltune.report_sql_monitor(session_id=> '&sid',
report_level=> 'ALL', TYPE=> '&type') as report
FROM dual;
生成當前會話的SQL Monitor Report
9.4 指定SQLID SQL Monitor Report
SELECT
dbms_sqltune.report_sql_monitor(sql_id=> '&sql_id',
report_level=> 'ALL', TYPE=> '&type') as report FROM dual;
生成指定SQL_ID的SQL Monitor Report
9.5 Explain SQL執行計劃
explain plan for
SELECT count(*) FROM user_objects;
SELECT * FROM table(dbms_xplan.display);
示例通過explain plan for方法獲取SQL執行計劃
9.6 Autotrace SQL執行計劃
SQL>set autotrace traceonly explain;
SQL>SELECT count(*) FROM user_objects;
SQL>set autotrace off;
通過SQL*Plus的 autotrace 功能獲取SQL執行計劃。
9.7 DBMS_XPLAN SQL執行計劃
SQL> SELECT * FROM
table(dbms_xplan.display_cursor('&sql_id',null,'advanced');
通過DBMS_XPLAN包獲取SQL執行計劃，sql_id 需要提供。
9.8 10053事件跟蹤
SQL>alter session set tracefile_identifier='10053';
SQL> alter session set events '10053 trace name context forever ,level 1';
SQL>
 session set events '10053 trace name context off';
通過10053事件來檢視執行計劃和詳細的SQL解析過程，trace檔案提供了Oracle如何選擇執行計劃的原因。

9.9 繫結變數
SELECT
dbms_sqltune.extract_bind(bind_data, 1).value_string
FROM wrh$_sqlstat WHERE sql_id ='&sql_id';
SELECT snap_id,name,position,value_string
FROM dba_hist_sqlbind WHERE sql_id= '&sql_id';
查詢SQL語句的繫結變數以及歷史繫結變數值資訊,需要給定SQL_ID資訊。

D10 定時任務檢查

檢查資料庫定時任務執行情況，確保後臺任務正確執行，尤其應關注統計資訊收集等核心任務。
10.1使用者定時任務
SQL> SELECTjob,log_user,last_date,next_date,interval,broken,failures
FROM dba_jobs;
查詢使用者的定時任務（job）資訊，確保任務在期望的時間成功執行，這是DBA的重要工作之一。
10.2系統定時任務
SQL> SELECTjob_name,start_date,repeat_interval
FROM dba_scheduler_jobs
查詢系統定時排程資訊，查詢顯示了任務名稱、初始啟動日期以及重複間隔。
10.3系統定時任務-11g
SQL> SELECTclient_name,mean_job_duration
FROM dba_autotask_client;
11g之後增加的字典表，記錄每個在7天和30天維護任務的統計資訊，查詢顯示名稱和平均執行時間。
10.4啟停統計資訊任務-10g
SQL>execdbms_scheduler.disable('SYS.GATHER_STATS_JOB');
SQL>execdbms_scheduler.enable('SYS.GATHER_STATS_JOB');
關閉和開啟oralce 10g 統計資訊自動採集任務
10.5啟停統計資訊任務-11g
SQL> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=> 'auto
optimizerstats collection', operation => NULL, window_name => NULL);
SQL> execDBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto
optimizer stats collection', operation=> NULL, window_name => NULL);
關閉和開啟Oralce 11g 統計資訊自動採集任務

資料備份重於一切，日常應檢查備份執行情況，並檢查備份的有效性，確保備份能夠保障資料安全，備份安全加密也應兼顧。

D11 備份
11.1全庫exp/imp

$ exp system/managerfile=/full.dmp log=/full.log full=y

$ imp system/managerfile=/full.dmp log=/full.log full=y

將資料庫全庫匯出匯入（示例以及以下示例顯示的是非Windows環境），

通過提供的使用者，執行全庫匯出。需要注意儲存位置和空間。

11.2使用者模式exp/imp

$exp enmo/enmo file=/enmo.dmp log=/enmo.log owner=enmo

$imp enmo/enmo file=/enmo.dmp log=/enmo.log fromuser=enmo

touser=enmo

將資料庫按指定使用者匯出

11.3表模式exp/imp

$ exp enmo/enmo file=/tables.dmplog=/tables.log tables=table_name

$ imp enmo/enmo file=/tables.dmplog=/tables.log tables=table_name

將資料庫按指定表匯出

11.4全庫模式expdp/impdp

$ expdp system/managerdirectory=svr_dir dumpfile=full.dmp full=y;

$ impdb system/managerdirectory=svr_dir dumpfile=full.dmp full=y;

將資料庫全庫匯出匯入, 注意directory是資料庫中建立的物件，指定伺服器上的儲存位置。

11.5使用者模式-expdp/impdp

$ expdp system/manager directory=svr_dirschemas=scott

dumpfile=expdp.dmp ;

$ impdp system/managerdirectory=svr_dir schemas=scott

dumpfile=expdp.dmpremap_schema=scott:enmo

remap_tablespace=users:testtbs;

將資料庫進行按使用者匯出和匯入示例，impdp示例中，分別重新對映了匯入的Schema和表空間。

11.6表模式-expdp/impdp

$ expdp scott/tiger directory=svr_dirtables=emp,dept

dumpfile=tables.dmp;

$ impdp scott/tigerdirectory=svr_dir dumpfile=tables.dmp

tables=emp,dept;

將資料庫進行按表匯出和匯入

11.7物理備份檢查

SQL> SELECTbackup_type,start_time,completion_time,block_size

FROM v$backup_set;

檢查備份集資訊，確保備份有效和及時是DBA的重要工作之一。RMAN的備份資訊記錄在控制檔案中。

11.8自動控制檔案備份

RMAN> show all;

RMAN> CONFIGURE CONTROLFILEAUTOBACKUP ON;

控制檔案對資料庫十分重要，建議啟動控制檔案的自動備份，示範顯示的是通過RMAN的設定。

11.9手動控制檔案備份

RMAN> backup currentcontrolfile;

SQL> alter database backupcontrolfile to '/back/control.bak';

通過RMAN或者SQL命令手動備份控制檔案，備份的是控制檔案的二進位制拷貝。

11.10轉儲控制檔案

SQL> alter session set events'immediate trace name controlf level 8';

通過以上命令轉儲控制檔案二進位制資訊到文字，研究這些資訊，可以極大加深對於資料庫的瞭解。

SQL> alter database backupcontrolfile to trace;

通過SQL命令轉儲控制檔案到文字，可以用於重建控制檔案。

11.11RMAN備份資料庫

RMAN> backup format'/data/backup/%U' database plus archivelog;

對於DBA備份是第一重要的工作，在歸檔模式下，

執行全庫備份可以簡化為示例的一個命令（需要根據容量進行分片）。

D12 基本資訊檢查

基本資訊包括版本、元件、補丁集等資訊，定期檢查資料庫資訊並登記在案是資料庫生命週期管理的重要內容之一。

12.1版本檢查

SQL> SELECT * FROM v$version;

檢視資料庫的版本資訊

12.2元件檢查

SQL> SELECT * FROM v$option;

檢視資料庫的元件資訊

12.3容量檢查

# asmcmd
ASMCMD>lsdg

SELECT group_number,disk_number,
mount_status,total_mb,free_mb
FROM v$asm_disk;

SELECT group_number,name,state,total_mb,free_mb
FROM v$asm_diskgroup;

如果使用了ASM管理，可以通過示例檢視ASM磁碟及磁碟組容量等資訊。

12.4PSU檢查

SQL> SELECT * FROM dba_registry_history;

查詢資料庫的版本升級歷史資訊。

# $ORACLE_HOME/OPatch/opatchlsinventory

查詢資料庫補丁歷史資訊，是系統級的命令工具。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29330852/viewspace-2121055/，如需轉載，請註明出處，否則將追究法律責任。

相關文章