DBA的日常工作

四、空間信息檢查

確保數(shù)據(jù)存儲空間可用，定期檢查表空間余量，進(jìn)行表空間和文件維護(hù)。

4.1 空間使用查詢

SQL> SELECT * FROM sys.sm$ts_used;
查看數(shù)據(jù)庫表空間的使用信息。

SQL> SELECT * FROM sys.sm$ts_free;
查看數(shù)據(jù)庫表空間的剩余空間。

4.2 文件信息

SELECT tablespace_name,file_name
FROM dba_data_files;

查看數(shù)據(jù)庫表空間的數(shù)據(jù)文件信息。

4.3 文件維護(hù)

alter database datafile '&path' resize 900M;

alter tablespace &tbs_name add datafile '&path' size 900M;

對數(shù)據(jù)庫的表空間容量進(jìn)行擴(kuò)容。

五、鎖/閂信息檢查

Lock/Latch是數(shù)據(jù)庫控制并發(fā)的核心手段，檢查相關(guān)信息可以監(jiān)控數(shù)據(jù)庫的事務(wù)和運行狀況。

5.1鎖信息

SQL>SELECT sid, type, lmode, ctime, block

FROMv$lock WHERE type not in ('MR','AE');

查看鎖會話ID，類型，持有時間等,

注意如果block >1,可能意味著阻塞了其他會話。

5.2鎖故障排查

在數(shù)據(jù)庫出現(xiàn)鎖競爭和阻塞時，需要排查和處理鎖定，必要時通過Kill阻塞進(jìn)程消除鎖定。

5.2.1查詢阻塞會話

SQL>SELECT sid,sql_id,status,blocking_session

FROMv$session WHERE sid in(SELECT session_id FROM v$locked_object);

查詢當(dāng)前鎖事物中阻塞會話與被阻塞會話的sid,sql_id和狀態(tài)信息

5.2.2阻塞SQL文本

SQL> SELECT sql_id,sql_text FROMv$sqltext

WHERE sql_id='&sql_id' ORDER BYpiece;

通過sql_id查詢得到SQL文本，例如通過sql_id查詢出阻塞的SQL語句。

5.2.3鎖阻塞對象信息

SQL>SELECT owner,object_name,object_type FROM dba_objects

WHEREobject_id in (SELECT object_id FROM v$locked_object);

通過sid查詢阻塞對象的詳細(xì)信息如對象名稱，所屬用戶等

5.2.4殺阻塞會話

SQL>altersystem kill session 'sid,serial#';

在Oracle實例內(nèi)殺死阻塞的會話進(jìn)程,其中sid,serial# 為中止會話對應(yīng)信息，來自v$session。

5.2.5殺系統(tǒng)進(jìn)程

SQL>SELECTpro.spid,pro.program

FROMv$session ses,v$process pro

WHEREses.sid=&sid and ses.paddr=pro.addr;

#kill -9 spid

有時對于活動進(jìn)程，在系統(tǒng)層面中止更為快速安全，示例找到系統(tǒng)進(jìn)程號，然后kill中止。

注意：無論何時，需要認(rèn)真分析，并且避免誤殺重要后臺進(jìn)程。

5.3閂檢查

SELECTname,gets,misses,immediate_gets,spin_gets

FROMv$latch ORDER BY 2;

檢查數(shù)據(jù)庫閂的使用情況，misses、SPIN_GETS統(tǒng)計高的，需要關(guān)注。

5.3.1 閂使用檢查

SQL>SELECT addr,gets FROM v$latch_children

WHEREname='cache buffers chains';

SQL>SELECT hladdr,file#,dbablk FROM x$bh

WHEREhladdr in (SELECT addr FROM v$latch_children WHERE addr='&addr');

僅供學(xué)習(xí)：通過獲得Latch的地址，找到該Latch守護(hù)的X$BH中相關(guān)的Buffer。

六、等待和統(tǒng)計數(shù)據(jù)

Wait和Statistics數(shù)據(jù)分別代表了數(shù)據(jù)庫的等待和運行數(shù)據(jù)，觀察這些數(shù)據(jù)以了解數(shù)據(jù)庫的等待瓶頸和健康程度。

6.1等待事件查詢

SELECT sid,event,wait_time_micro

FROM v$session_wait ORDER BY 3;

通過等待事件和等待時間，了解數(shù)據(jù)庫當(dāng)前連接會話的等待情況。

注意，如果會話眾多，需要限定查詢輸出行數(shù)。

6.2TOP10等待事件

SQL> SELECT * FROM (

SELECTEVENT,TOTAL_WAITS,AVERAGE_WAIT,TIME_WAITED

FROM v$system_event WHEREwait_class<>'Idle'

ORDER BY time_waited desc) WHERE rownum<=10;

查看當(dāng)前數(shù)據(jù)中TOP10等待事件信息，需要分析和關(guān)注非空閑的顯著等待。

6.3會話統(tǒng)計數(shù)據(jù)

SQL>SELECT s.sid,s.statistic#,n.name,s.value

FROM v$sesstat s,v$statname n

WHERE s.statistic#=n.statistic# andn.name='redo size' and sid='&sid';

查詢數(shù)據(jù)庫會話的統(tǒng)計信息數(shù)據(jù)，示例查詢了Redo的大小，SID需要提供。

6.4系統(tǒng)級統(tǒng)計數(shù)據(jù)

SQL>SELECT * FROM v$sysstat WHERE name='redo size';

查詢整個系統(tǒng)的統(tǒng)計數(shù)據(jù)，示例顯示數(shù)據(jù)庫實例啟動以來的REDO日志生成量。

表、索引、分區(qū)、約束等是數(shù)據(jù)庫的核心存儲對象，其核心信息和對象維護(hù)是DBA重要的日常工作。

7.1表信息數(shù)據(jù)

SQL>SELECT * FROM (

SELECTowner,table_name,num_rows

FROMdba_tables ORDER BY num_rows desc nulls last )

WHERErownum < 11;

查看表的基本信息數(shù)據(jù)：屬主，表名，記錄行數(shù)等。

7.2表結(jié)構(gòu)查詢

SQL> set long 12000

SQL> SELECTdbms_metadata.get_ddl('TABLE','&table_name','&user')

FROM dual;

根據(jù)提供的表名和用戶（需大寫），查詢表的表結(jié)構(gòu)信息(建表語句)。

7.3表統(tǒng)計信息

SQL>SELECT owner,table_name,last_analyzed FROM dba_tab_statistics

WHEREowner='&owner' and table_name='&table_name';

查詢給定用戶、給定表（需大寫），查詢最后的統(tǒng)計信息分析收集時間。統(tǒng)計信息影響執(zhí)行計劃，當(dāng)SQL執(zhí)行異常時，需要重點分析統(tǒng)計信息。

7.4表統(tǒng)計信息收集

SQL>exec dbms_stats.gather_table_stats(ownname=>'&owner',

tabname=>'&table_name');

收集統(tǒng)計信息是一項復(fù)雜任務(wù)，需要詳細(xì)設(shè)計，示例對給出用戶、表名的對象采集統(tǒng)計信息。

7.5索引信息數(shù)據(jù)

SQL>SELECT * FROM (

SELECTindex_name,table_name,num_rows,leaf_blocks,clustering_factor

FROMdba_indexes ORDER BY 5 desc nulls last)

WHERErownum<11;

查詢索引的基本信息,示例輸出包括葉塊數(shù)和聚簇因子等，如聚簇因子過高接近行數(shù)可能代表索引效率不高。

7.6索引定義查詢

SQL>set long 12000

SQL>SELECT dbms_metadata.get_ddl('INDEX','&table_name','&user')

FROM dual;

根據(jù)提供的表名和用戶名，查詢索引的建立語句。

7.7索引統(tǒng)計信息及收集方法

SQL>SELECT owner,index_name,last_analyzed FROM dba_ind_statistics

WHEREowner='&owner' and table_name='&table_name';

根據(jù)給定的用戶名，表名稱（需大寫），查詢索引信息，尤其應(yīng)關(guān)注最后分析時間。

SQL>exec dbms_stats.gather_index_stats(ownname=>'&owner',

indname=>'&index_name');

收集統(tǒng)計信息是一項復(fù)雜任務(wù)，示例對給出用戶、索引名的對象采集統(tǒng)計信息。

7.8分區(qū)對象檢查

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;

查看分區(qū)表的基本信息：分區(qū)類型，數(shù)量,邊界值等。

7.9分區(qū)定義查詢

SQL> SELECTdbms_metadata.get_ddl('TABLE',

'&part_table_name','user')FROM dual;

根據(jù)給定的分區(qū)表名、用戶（需大寫），查詢分區(qū)表的結(jié)構(gòu)信息（建表語句）。

7.10分區(qū)統(tǒng)計信息相關(guān)

SQL>SELECT owner,table_name,partition_name,last_analyzed

FROMdba_tab_statistics

WHERE owner='&owner' andtable_name='&table_name';

SQL>exec dbms_stats.gather_table_stats(ownname=>'&owner',

tabname=>'&table_name');

查看分區(qū)表的統(tǒng)計信息收集時間，以及對分區(qū)表進(jìn)行手工收集統(tǒng)計信息，注意分區(qū)表統(tǒng)計信息收集非常復(fù)雜，需要深入研究做出正確策略，示例僅提供最簡單的采集命令。

7.11 約束信息

SQL>SELECTconstraint_name,constraint_type FROM DBA_CONSTRAINTS

WHEREtable_name='&table_name';

查詢指定數(shù)據(jù)表的約束信息，包括名稱和類型。

7.12失效對象檢查

SQL>SELECT owner,object_name,object_type,status

FROMdba_objects WHERE status <>'VALID'

ORDERBY owner,object_name;

檢查數(shù)據(jù)庫中的失效對象信息，通常運行健康的數(shù)據(jù)庫中不應(yīng)有失效的對象。

7.13閃回查詢

閃回查詢功能對于恢復(fù)DML及部分DDL誤操作非常便利，DBA必備技能。

7.13.1時間閃回

SQL>SELECT * FROM &table_name as of timestamp

to_timestamp('2015-02-0400:02:09','yyyy-mm-dd hh34:mi:ss');

閃回表數(shù)據(jù),基于時間點的表數(shù)據(jù)閃回查詢。

7.13.2 SCN閃回

SQL>SELECT * FROM &table_name as of scn &scn;

閃回表數(shù)據(jù)，基于scn的表數(shù)據(jù)查詢，需要提供SCN，如果不明確SCN，可以通過時間點閃回查詢。

7.13.3 閃回DROP

SQL>flashback table &old_table to before drop rename to &new_table;

閃回刪除操作，對已經(jīng)刪除的表進(jìn)行閃回回復(fù)并重命名。

參考：

http://www.eygle.com/archives/2009/11/scn_to_timestamp.html

http://www.eygle.com/archives/2005/03/eoaoracle10gaef.html

八、AWR報告檢查

通過AWR報告了解日常高峰時段數(shù)據(jù)庫各項指標(biāo)和運行狀況，通過對比報告觀察和基線的變化，通過趨勢分析持續(xù)關(guān)注數(shù)據(jù)庫日常運行狀態(tài)。

8.1本地AWR

SQL>@?/rdbms/admin/awrrpt

生成本地AWR報告信息，需要根據(jù)提示輸入相應(yīng)的信息。

8.2指定實例AWR

SQL>@?/rdbms/admin/awrrpti

生成指定實例AWR報告

8.3AWR對比報告

SQL>@?/rdbms/admin/awrddrpt

生成本地AWR時間段對比報告

8.4指定實例對比

SQL>@?/rdbms/admin/awrddrpi

生成指定實例AWR時間段對比報告

8.5AWR信息提取

SQL>@?/rdbms/admin/awrextr

使用awrextr腳本將AWR性能數(shù)據(jù)導(dǎo)出，可以用于留錯或者異地分析。

8.6AWR信息加載

SQL>@?/rdbms/admin/awrload

通過awrload，可以將導(dǎo)出的AWR性能數(shù)據(jù)導(dǎo)入到其他數(shù)據(jù)庫中，便于集中和分析。

九、SQL報告檢查

對TOP SQL進(jìn)行持續(xù)關(guān)注和分析，通過SQL報告分析SQL的效率、性能，并做出報告和優(yōu)化建議等。

9.1 本地SQLReport

SQL> @?/rdbms/admin/awrsqrpt

生成本地SQLReport報告

9.2 指定實例SQLReport

SQL>@?/rdbms/admin/awrsqrpi

生成指定實例SQLRerport報告

9.3 當(dāng)前會話SQL Monitor Report

SELECT

dbms_sqltune.report_sql_monitor(session_id=> '&sid',

report_level=> 'ALL', TYPE=> '&type') as report

FROM dual;

生成當(dāng)前會話的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

參考：http://www.eygle.com/archives/2012/09/sqloracle_sql_monitor_report.html

9.5 Explain SQL執(zhí)行計劃

explain plan for

SELECT count(*) FROM user_objects;

SELECT * FROM table(dbms_xplan.display);

示例通過explain plan for方法獲取SQL執(zhí)行計劃

9.6 Autotrace SQL執(zhí)行計劃

SQL>set autotrace traceonly explain;

SQL>SELECT count(*) FROM user_objects;

SQL>set autotrace off;

通過SQL*Plus的 autotrace 功能獲取SQL執(zhí)行計劃。

9.7 DBMS_XPLAN SQL執(zhí)行計劃

SQL> SELECT * FROM

table(dbms_xplan.display_cursor('&sql_id',null,'advanced');

通過DBMS_XPLAN包獲取SQL執(zhí)行計劃，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> <execute sql statements>

SQL> alter session set events '10053 trace name context off';

通過10053事件來查看執(zhí)行計劃和詳細(xì)的SQL解析過程，trace文件提供了Oracle如何選擇執(zhí)行計劃的原因。

參考：http://www.eygle.com/archives/2011/02/dba_event_10046_10053.html

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信息。

參考：http://www.eygle.com/archives/2010/11/dbms_sqltune_bindata.html

十、定時任務(wù)檢查

檢查數(shù)據(jù)庫定時任務(wù)執(zhí)行情況，確保后臺任務(wù)正確執(zhí)行，尤其應(yīng)關(guān)注統(tǒng)計信息收集等核心任務(wù)。

10.1用戶定時任務(wù)

SQL> SELECTjob,log_user,last_date,next_date,interval,broken,failures

FROM dba_jobs;

查詢用戶的定時任務(wù)（job）信息，確保任務(wù)在期望的時間成功執(zhí)行，這是DBA的重要工作之一。

10.2系統(tǒng)定時任務(wù)

SQL> SELECTjob_name,start_date,repeat_interval

FROM dba_scheduler_jobs

查詢系統(tǒng)定時調(diào)度信息，查詢顯示了任務(wù)名稱、初始啟動日期以及重復(fù)間隔。

10.3系統(tǒng)定時任務(wù)-11g

SQL> SELECTclient_name,mean_job_duration

FROM dba_autotask_client;

11g之后增加的字典表，記錄每個在7天和30天維護(hù)任務(wù)的統(tǒng)計信息，查詢顯示名稱和平均執(zhí)行時間。

10.4啟停統(tǒng)計信息任務(wù)-10g

SQL>execdbms_scheduler.disable('SYS.GATHER_STATS_JOB');

SQL>execdbms_scheduler.enable('SYS.GATHER_STATS_JOB');

關(guān)閉和開啟oralce 10g 統(tǒng)計信息自動采集任務(wù)

10.5啟停統(tǒng)計信息任務(wù)-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);

關(guān)閉和開啟Oralce 11g 統(tǒng)計信息自動采集任務(wù)

十一、備份

數(shù)據(jù)備份重于一切，日常應(yīng)檢查備份執(zhí)行情況，并檢查備份的有效性，確保備份能夠保障數(shù)據(jù)安全，備份安全加密也應(yīng)兼顧。

11.1全庫exp/imp

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

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

將數(shù)據(jù)庫全庫導(dǎo)出導(dǎo)入（示例以及以下示例顯示的是非Windows環(huán)境），

通過提供的用戶，執(zhí)行全庫導(dǎo)出。需要注意存儲位置和空間。

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

將數(shù)據(jù)庫按指定用戶導(dǎo)出

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

將數(shù)據(jù)庫按指定表導(dǎo)出

11.4全庫模式expdp/impdp

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

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

將數(shù)據(jù)庫全庫導(dǎo)出導(dǎo)入, 注意directory是數(shù)據(jù)庫中創(chuàng)建的對象，指定服務(wù)器上的存儲位置。

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;

將數(shù)據(jù)庫進(jìn)行按用戶導(dǎo)出和導(dǎo)入示例，impdp示例中，分別重新映射了導(dǎo)入的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;

將數(shù)據(jù)庫進(jìn)行按表導(dǎo)出和導(dǎo)入

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;

控制文件對數(shù)據(jù)庫十分重要，建議啟動控制文件的自動備份，示范顯示的是通過RMAN的設(shè)置。

11.9手動控制文件備份

RMAN> backup currentcontrolfile;

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

通過RMAN或者SQL命令手動備份控制文件，備份的是控制文件的二進(jìn)制拷貝。

11.10轉(zhuǎn)儲控制文件

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

通過以上命令轉(zhuǎn)儲控制文件二進(jìn)制信息到文本，研究這些信息，可以極大加深對于數(shù)據(jù)庫的了解。

SQL> alter database backupcontrolfile to trace;

通過SQL命令轉(zhuǎn)儲控制文件到文本，可以用于重建控制文件。

11.11RMAN備份數(shù)據(jù)庫

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

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

執(zhí)行全庫備份可以簡化為示例的一個命令（需要根據(jù)容量進(jìn)行分片）。

參考：http://www.eygle.com/archives/2004/11/use_rman_plusarchivelog_option.html

十二、基本信息檢查

基本信息包括版本、組件、補(bǔ)丁集等信息，定期檢查數(shù)據(jù)庫信息并登記在案是數(shù)據(jù)庫生命周期管理的重要內(nèi)容之一。

12.1版本檢查

SQL> SELECT * FROM v$version;

查看數(shù)據(jù)庫的版本信息

12.2組件檢查

SQL> SELECT * FROM v$option;

查看數(shù)據(jù)庫的組件信息

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;

查詢數(shù)據(jù)庫的版本升級歷史信息。

# $ORACLE_HOME/OPatch/opatchlsinventory

查詢數(shù)據(jù)庫補(bǔ)丁歷史信息，是系統(tǒng)級的命令工具。