|
oracle運維
1|0簡單命令使用
這個簡單了解就可以，我是從網(wǎng)上和書本收集而來。（我自己沒怎么用過）
1|1進入SQL*Plus
1|2退出SQL*Plus
1|3在sqlplus下得到幫助信息
列出全部SQL 命令和SQL*Plus 命令
列出某個特定的命令的信息
1|4顯示表結(jié)構(gòu)命令 DESCRIBE
1|5SQL*Plus 中的編輯命令
顯示SQL緩沖區(qū)命令
修改SQL 命令
首先要將待改正行變?yōu)楫斍靶?/p>
用CHANGE 命令修改內(nèi)容
重新確認是否已正常
使用INPUT 命令可以在SQL 緩沖區(qū)中增加一行或多行
1|6調(diào)用外部系統(tǒng)編輯器
可以使用DEFINE命令設置系統(tǒng)變量EDITOR 來改變文本編輯器的類型，在login.sql文件中定義如下一行
DEFINE_EDITOR=vi
1|7運行命令文件
1|8關(guān)于偵聽
1、新建/修改/刪除偵聽
以oracle 用戶登錄，運行netca，會跳出圖形配置界面。
2、打開偵聽
3、查看偵聽
4、關(guān)閉偵聽
2|0ORACLE的啟動和關(guān)閉
2|1在單機環(huán)境下
要想啟動或關(guān)閉ORACLE系統(tǒng)必須首先切換到ORACLE用戶下，如下
啟動oracle 數(shù)據(jù)庫命令
已連接到空閑例程
ORACLE 例程已經(jīng)啟動
關(guān)閉oracle 數(shù)據(jù)庫命令：
連接
關(guān)閉數(shù)據(jù)庫
3|0數(shù)據(jù)庫管理員日常工作
3|1檢查alterSID.log
這個日志文件位于參數(shù)BACKGROUND_DUMP_DEST 指定的目錄，可能通過以下
命令來查看。
SQL> SHOW PARAMETER background_dump_dest
在出現(xiàn)大故障前，數(shù)據(jù)庫有可能會報一些警告或錯誤信息，應該充分重視這
些信息，未雨綢繆，避免更大錯誤的發(fā)生。
檢查alterSID.log 的什么內(nèi)容。
?? 檢查數(shù)據(jù)庫是否出現(xiàn)過宕機（可能在晚間重啟而維護人員不知道）
?? Oracle 出錯信息，通過$grep ORA- alterSID.log 查找
?? 產(chǎn)品有關(guān)的問題：ORA-00600/ORA-07445 等錯誤
?? 相應的TRACE 文件
3|2環(huán)境確認
數(shù)據(jù)庫實例是否正常工作
SQL > select status from v$instance;
數(shù)據(jù)庫監(jiān)聽器是否正常工作
是否存在故障表空間
- SQL> select tablespace_name,status from dba_tablespace;
控制文件、日志文件是否正常
SQL> select * from v$controlfile;
SQL> select * from v$log;
SQL> select * from v$logfile;
性能監(jiān)測
?? 每天按業(yè)務峰值情況，對數(shù)據(jù)庫性能數(shù)據(jù)進行定時采集
??每天檢查數(shù)據(jù)庫的主要性能指標
??每天檢查最消耗資源的SQL 語句變化情況。
??每天檢查是否有足夠的資源
??檢查所有表空間的剩余情況
??識別出一些異常的增長
??檢查CPU、內(nèi)存、網(wǎng)絡等是否異常
4|0數(shù)據(jù)庫日常操作SQL
4|1查看表空間物理文件的名稱及大小
select tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) filesize
from dba_data_files
order by tablespace_name;
4|2查看表空間使用情況
select a.tablespace_name "表空間名稱",
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100, 2) "
占用率(%)",
round(a.bytes_alloc / 1024 / 1024, 2) "容量(M)",
round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) "空閑(M)",
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2)
"使用(M)",
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') "采樣時間"
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
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 2 desc;
4|3查看表空間的碎片程度
select tablespace_name, count(tablespace_name)
from dba_free_space
group by tablespace_name
having count(tablespace_name) > 10;
alter tablespace HS_USER_DATA coalesce;
alter table name deallocate unused;
4|4碎片程度
select tablespace_name, count(tablespace_name)
from dba_free_space
group by tablespace_name
having count(tablespace_name) > 10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name, block_id, bytes, blocks, segment_name
from dba_free_space
union all
select tablespace_name, block_id, bytes, blocks, segment_name
from dba_extents;
select * from ts_blocks_v;
select tablespace_name, sum(bytes), max(bytes), count(block_id)
from dba_free_space
group by tablespace_name;
?? 查看碎片程度高的表
SELECT segment_name table_name, COUNT(*) extents
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM dba_segments
GROUP BY segment_name);
4|5查看回滾段名稱及大小
select segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) InitialExtent,
(next_extent / 1024) NextExtent,
max_extents,
v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name;
4|6查看控制文件
select name from v$controlfile;
4|7查看日志文件
select member from v$logfile;
4|8查看表空間的使用情況
select sum(bytes) / (1024 * 1024) as free_space, tablespace_name
from dba_free_space
group by tablespace_name;
SELECT A.TABLESPACE_NAME,
A.BYTES TOTAL,
B.BYTES USED,
C.BYTES FREE,
(B.BYTES * 100) / A.BYTES "% USED",
(C.BYTES * 100) / A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;
4|9查看數(shù)據(jù)庫對象
select owner, object_type, status, count(*) count#
from all_objects
group by owner, object_type, status;
4|10查看數(shù)據(jù)庫的版本
Select version
FROM Product_component_version
Where SUBSTR(PRODUCT, 1, 6) = 'Oracle';
4|11查看Oracle字符集
select * from sys.props$ where name = 'NLS_CHARACTERSET';
4|12在某個用戶下找所有的索引
select user_indexes.table_name,
user_indexes.index_name,
uniqueness,
column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type,
user_indexes.table_name,
user_indexes.index_name,
column_position;
4|13表、索引的存儲情況檢查
select segment_name, sum(bytes), count(*) ext_quan
from dba_extents
where tablespace_name = '&tablespace_name'
and segment_type = 'TABLE'
group by tablespace_name, segment_name;
select segment_name, count(*)
from dba_extents
where segment_type = 'INDEX'
and owner = '&owner'
group by segment_name;
4|14查看數(shù)據(jù)庫的創(chuàng)建日期和歸檔方式
Select Created, Log_Mode, Log_Mode From V$Database;
4|15顯示所有數(shù)據(jù)庫對象的類別和大小
select type,
count(name) num_instances,
sum(source_size) source_size,
sum(parsed_size) parsed_size,
sum(code_size) code_size,
sum(error_size) error_size,
sum(source_size) + sum(parsed_size) + sum(code_size) +
sum(error_size) size_required
from dba_object_size
group by type
order by 1;
4|16RAC啟動歸檔
步驟：
- 以SYSDBA 身份登陸2 個節(jié)點，執(zhí)行
alter system set cluster_database=false scope =spfile sid=’*’;
設置歸檔路徑
alter system set log_archive_start=true scope=spfile;
- 2 個節(jié)點
- 在一個節(jié)點上執(zhí)行
startup mount
alter database archivelog;
shutdown immediate;
alter database open;
alter system set cluster_database=true scope =spfile sid=’*’;
shutdown immediate
4、分別啟動2 個節(jié)點，修改完畢
5|0Troubleshooting
5|1監(jiān)控事務的等待
select event,
sum(decode(wait_Time, 0, 0, 1)) "Prev",
sum(decode(wait_Time, 0, 1, 0)) "Curr",
count(*) "Totol"
from v$session_Wait
group by event
order by 4;
5|2查看一些等待信息
select sid, event
from v$session_wait
where event not like 'SQL%'
and event not like '%ipc%';
查看是否存在下面等常見的等待事件：
??buffer busy waits，
??free buffer waits，
??db file sequential read,
??db file scattered read,
??enqueue,latch free,
??log file parallel write,
??log file sync
5|3查看等待情況
SELECT v$waitstat.class,
v$waitstat.count count,
SUM(v$sysstat.value) sum_value
FROM v$waitstat, v$sysstat
WHERE v$sysstat.name IN ('db block gets', 'consistent gets')
group by v$waitstat.class, v$waitstat.count;
5|4回滾段查看
select rownum,
sys.dba_rollback_segs.segment_name Name,
v$rollstat.extents Extents,
v$rollstat.rssize Size_in_Bytes,
v$rollstat.xacts XActs,
v$rollstat.gets Gets,
v$rollstat.waits Waits,
v$rollstat.writes Writes,
sys.dba_rollback_segs.status status
from v$rollstat, sys.dba_rollback_segs, v$rollname
where v$rollname.name(+) = sys.dba_rollback_segs.segment_name
and v$rollstat.usn(+) = v$rollname.usn
order by rownum;
5|5回滾段的爭用情況
select name, waits, gets, waits / gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
5|6監(jiān)控表空間的 I/O 比例
select df.tablespace_name name,
df.file_name "file",
f.phyrds pyr,
f.phyblkrd pbr,
f.phywrts pyw,
f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
5|7監(jiān)控文件系統(tǒng)的I/O比例
select substr(a.file#, 1, 2) "#",
substr(a.name, 1, 30) "Name",
a.status,
a.bytes,
b.phyrds,
b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
5|8監(jiān)控SGA 的命中率
select a.value + b.value "logical_reads",
c.value "phys_reads",
round(100 * ((a.value + b.value) - c.value) / (a.value + b.value))
"BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 -- physical read total multi block requests
and b.statistic# = 39 -- physical read total bytes
and c.statistic# = 40; -- physical write total IO requests
__EOF__
|
