yrh3elMD.txt
(81.36 KB, 下载次数: 227)
2015-4-19 18:54 上传
点击文件名下载附件
rac 日常巡检
能操作。
[此贴子已经被作者于2015-04-19 18:54:33编辑过]
作者: 王硕 时间: 2015-4-19 18:25
1.如何查询数据库数据库的状态是mount,nomount还是open的状态?
SQL>
select status from v$instance;
STATUS
------------
OPEN
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
作者: 王硕 时间: 2015-4-19 18:25
2.列出配置的所有数据库
[oracle@node1 ~]$ srvctl config database
db
作者: 王硕 时间: 2015-4-19 18:25
3.查看所有实例和服务的状态
[oracle@node1 ~]$ srvctl status database -d db ------- db为数据库的名字
Instance db1 is running on node node1
Instance db2 is running on node node2
作者: 王硕 时间: 2015-4-19 18:26
4. 查看Oracle数据库名和实例名
SQL> select name from v$database;
NAME
---------
DB
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
db1
作者: 王硕 时间: 2015-4-19 18:26
5..列出RAC数据库的配置
[oracle@node1 ~]$ srvctl config database -d db ------db为数据库名
Database unique name: db
Database name: db
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/db/spfiledb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: db
Database instances: db1,db2
Disk Groups: DATA,ARCH
Services:
Database is administrator managed
作者: 王硕 时间: 2015-4-19 18:28
6.显示节点应用程序的配置 —（VIP、GSD、ONS、监听器）
[oracle@node1 ~]$ srvctl config nodeapps -n node1 -a -g -s -l -------node1 为节点名
-n <node_name> option has been deprecated.
-l option has been deprecated and will be ignored.
VIP exists.:node1
VIP exists.: /node1-vip/192.168.1.131/255.255.255.0/eth0
GSD exists.
ONS daemon exists. Local port 6100, remote port 6200
-n option has been deprecated and will be ignored.
Name: LISTENER
Network: 1, Owner: grid
Home: <CRS home>
/u01/grid on node(s) node2,node1
End points: TCP:1521
作者: 王硕 时间: 2015-4-19 18:28
7. Oracle进程检查
[oracle@node1 ~]$ ps -ef |grep ora_
oracle 6722 1 0 13:00 ? 00:00:00 ora_pmon_db1
oracle 6726 1 0 13:00 ? 00:00:00 ora_vktm_db1
oracle 6732 1 0 13:00 ? 00:00:00 ora_gen0_db1
oracle 6736 1 0 13:00 ? 00:00:00 ora_diag_db1
oracle 6740 1 0 13:00 ? 00:00:00 ora_dbrm_db1
oracle 6744 1 0 13:00 ? 00:00:00 ora_ping_db1
oracle 6748 1 0 13:00 ? 00:00:00 ora_psp0_db1
oracle 6752 1 0 13:00 ? 00:00:00 ora_acms_db1
oracle 6756 1 0 13:00 ? 00:00:03 ora_dia0_db1
oracle 6760 1 0 13:00 ? 00:00:01 ora_lmon_db1
oracle 6764 1 0 13:00 ? 00:00:03 ora_lmd0_db1
oracle 6768 1 0 13:00 ? 00:00:01 ora_lms0_db1
oracle 6774 1 0 13:00 ? 00:00:00 ora_rms0_db1
oracle 6778 1 0 13:00 ? 00:00:00 ora_lmhb_db1
oracle 6782 1 0 13:00 ? 00:00:00 ora_mman_db1
oracle 6786 1 0 13:00 ? 00:00:00 ora_dbw0_db1
oracle 6790 1 0 13:00 ? 00:00:00 ora_lgwr_db1
oracle 6794 1 0 13:00 ? 00:00:00 ora_ckpt_db1
oracle 6798 1 0 13:00 ? 00:00:00 ora_smon_db1
oracle 6802 1 0 13:00 ? 00:00:00 ora_reco_db1
oracle 6806 1 0 13:00 ? 00:00:00 ora_rbal_db1
oracle 6810 1 0 13:00 ? 00:00:00 ora_asmb_db1
oracle 6814 1 0 13:00 ? 00:00:01 ora_mmon_db1
oracle 6818 1 0 13:00 ? 00:00:00 ora_mmnl_db1
oracle 6822 1 0 13:00 ? 00:00:00 ora_d000_db1
oracle 6826 1 0 13:00 ? 00:00:00 ora_s000_db1
oracle 6835 1 0 13:00 ? 00:00:00 ora_mark_db1
oracle 6850 1 0 13:00 ? 00:00:03 ora_lck0_db1
oracle 6859 1 0 13:00 ? 00:00:00 ora_rsmn_db1
oracle 7002 1 0 13:01 ? 00:00:00 ora_arc0_db1
oracle 7007 1 0 13:01 ? 00:00:00 ora_arc1_db1
oracle 7011 1 0 13:01 ? 00:00:00 ora_arc2_db1
oracle 7015 1 0 13:01 ? 00:00:00 ora_arc3_db1
oracle 7095 1 0 13:02 ? 00:00:00 ora_gtx0_db1
oracle 7099 1 0 13:02 ? 00:00:00 ora_rcbg_db1
oracle 7120 1 0 13:02 ? 00:00:00 ora_qmnc_db1
oracle 7128 1 0 13:02 ? 00:00:01 ora_q000_db1
oracle 7132 1 0 13:02 ? 00:00:00 ora_q001_db1
oracle 7269 1 0 13:03 ? 00:00:01 ora_cjq0_db1
oracle 7454 1 0 13:06 ? 00:00:00 ora_pz99_db1
oracle 7556 1 0 13:07 ? 00:00:00 ora_smco_db1
oracle 10734 1 0 13:47 ? 00:00:00 ora_w000_db1
oracle 10809 1 0 13:48 ? 00:00:00 ora_q002_db1
oracle 10838 1 0 13:49 ? 00:00:00 ora_j000_db1
oracle 10842 1 0 13:49 ? 00:00:00 ora_j001_db1
oracle 10866 7792 0 13:49 pts/2 00:00:00 grep ora_
作者: 王硕 时间: 2015-4-19 18:29
8.CRS进程检查
[oracle@node1 ~]$ ps -df |grep d.bin
grid 5110 1 0 12:54 ? 00:00:00 /u01/grid/bin/gipcd.bin
root 5155 1 0 12:54 ? 00:00:01 /u01/grid/bin/cssdmonitor
root 5178 1 0 12:54 ? 00:00:01 /u01/grid/bin/cssdagent
grid 5199 1 0 12:54 ? 00:00:10 /u01/grid/bin/ocssd.bin
grid 5206 1 0 12:54 ? 00:00:00 /u01/grid/bin/diskmon.bin -d -f
root 5707 1 0 12:57 ? 00:00:00 /u01/grid/bin/octssd.bin reboot
grid 5904 1 0 12:58 ? 00:00:00 /u01/grid/bin/oclskd.bin
root 6014 1 0 12:58 ? 00:00:00 /u01/grid/bin/oclskd.bin
grid 6068 5723 0 12:58 ? 00:00:00 /u01/grid/bin/evmlogger.bin -o /u01/grid/evm/log/evmlogger.info -l /u01/grid/evm/log/evmlogger.log
oracle 6839 1 0 13:00 ? 00:00:00 /u01/grid/bin/oclskd.bin
oracle 11027 7792 0 13:51 pts/2 00:00:00 grep d.bin
[root@node1 ~]# su - grid
[grid@node1 ~]$ crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
[grid@node1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.ARCH.dg ora....up.type ONLINE ONLINE node1
ora.DATA.dg ora....up.type ONLINE ONLINE node1
ora....ER.lsnr ora....er.type ONLINE ONLINE node1
ora....N1.lsnr ora....er.type ONLINE ONLINE node1
ora.OVDISK.dg ora....up.type ONLINE ONLINE node1
ora.asm ora.asm.type ONLINE ONLINE node1
ora.db.db ora....se.type ONLINE ONLINE node1
ora.eons ora.eons.type ONLINE ONLINE node1
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE node1
ora....SM1.asm application ONLINE ONLINE node1
ora....E1.lsnr application ONLINE ONLINE node1
ora.node1.gsd application OFFLINE OFFLINE
ora.node1.ons application ONLINE ONLINE node1
ora.node1.vip ora....t1.type ONLINE ONLINE node1
ora....SM2.asm application ONLINE ONLINE node2
ora....E2.lsnr application ONLINE ONLINE node2
ora.node2.gsd application OFFLINE OFFLINE
ora.node2.ons application ONLINE ONLINE node2
ora.node2.vip ora....t1.type ONLINE ONLINE node2
ora.oc4j ora.oc4j.type OFFLINE OFFLINE
ora.ons ora.ons.type ONLINE ONLINE node1
ora....ry.acfs ora....fs.type ONLINE ONLINE node1
ora.scan1.vip ora....ip.type ONLINE ONLINE node1
[grid@node1 ~]$ crs_stat -ls
terminate called after throwing an instance of 'std::out_of_range'
what(): basic_string::substr
Aborted
作者: 王硕 时间: 2015-4-19 18:29
9.查看各个instance的alert.log
SQL> select name,value from V$diag_info
2 where name in('Diag Trace','Diag Alert');
NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
Diag Trace
/u01/app/oracle/diag/rdbms/db/db1/trace
Diag Alert
/u01/app/oracle/diag/rdbms/db/db1/alert
3、如果是查看文本格式的alert log，先进入 Diag Trace 目录。用文本编辑器打开alert_SID.log。
4、如果是查看XML格式的 alert log，进入 Diag Alert 目录。用文本编辑器打开log.xml.
作者: 王硕 时间: 2015-4-19 18:30
10.查看spfile.ora 查看profile
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/db/spfiledb.ora
[grid@node1 grid]$ asmcmd
ASMCMD> ls
ARCH/
DATA/
OVDISK/
ASMCMD> cd DATA
ASMCMD> cd DB
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfiledb.ora
ASMCMD>
作者: 王硕 时间: 2015-4-19 18:30
11.参看监听状态
[oracle@node1 ~]$ hostname
node1
[oracle@node1 ~]$ export ORACLE_SID=node1
[oracle@node1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-APR-2015 20:16:48
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 17-APR-2015 12:59:13
Uptime 0 days 7 hr. 17 min. 35 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/grid/network/admin/listener.ora
Listener Log File /u01/app/diag/tnslsnr/node1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.31)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.131)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "db" has 1 instance(s).
Instance "db1", status READY, has 1 handler(s) for this service...
Service "dbXDB" has 1 instance(s).
Instance "db1", status READY, has 1 handler(s) for this service...
The command completed successfully
作者: 王硕 时间: 2015-4-19 18:31
12.crs 日志文件位置
[grid@node1 ~]$ cd $ORACLE_HOME
[grid@node1 grid]$ ls
assistants dbs has ldap opmn relnotes
auth dc_ocm hs lib oracore root.sh
bin deinstall install log oraInst.loc scheduler
cdata demo install.platform md ord slax
cfgtoollogs diagnostics instantclient mdns oui sqlplus
clone eons inventory mesg owm srvm
crs evm javavm network perl sysman
csmig gipc jdbc nls plsql usm
css gns jdk oc4j precomp utl
ctss gnsd jlib ons racg wwg
cv gpnp JRE OPatch rdbms xdk
[grid@node1 grid]$ cd log
[grid@node1 log]$ ls
crs diag node1
作者: 王硕 时间: 2015-4-19 18:32
13.集群中所有正在运行的实例（sysdba下运行）
SQL> set pagesize 1000
SQL> set linesize 120
SQL> set echo on ----------------------set echo on，则显示出文件中的每条命令和该命令执行的结果，如果设为set echo off，则只显示命令执行的结果，而不显示出命令本身。
SQL> COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME
SQL> COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE
SQL> col tablespace_name format a15
SQL> host hostname
node1
SQL> SELECT
inst_id
, instance_number inst_no
, instance_name inst_name
, parallel
, status
, database_status db_status
, active_state state
, host_name host
FROM gv$instance
ORDER BY inst_id; 2 3 4 5 6 7 8 9 10 11
INST_ID INST_NO INST_NAME PAR STATUS DB_STATUS STATE
---------- ---------- ---------------- --- ------------ ----------------- ---------
HOST
----------------------------------------------------------------
1 1 db1 YES OPEN ACTIVE NORMAL
node1
2 2 db2 YES OPEN ACTIVE NORMAL
node2
SQL> SELECT inst_id, instance_name, host_name, VERSION, TO_CHAR (startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time, status, archiver, database_status FROM gv$instance;
INST_ID INSTANCE_NAME HOST_NAME VERSION
---------- ---------------- ---------------------------------------------------------------- -----------------
STARTUP_TIME STATUS ARCHIVE DATABASE_STATUS
------------------- ------------ ------- -----------------
1 db1 node1 11.2.0.1.0
2015-04-17 13:00:33 OPEN STARTED ACTIVE
2 db2 node2 11.2.0.1.0
2015-04-17 13:05:12 OPEN STARTED ACTIVE
作者: 王硕 时间: 2015-4-19 18:32
14.检查SGA和PGA
SQL> show sga
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 603981816 bytes
Database Buffers 230686720 bytes
Redo Buffers 2396160 bytes
SQL> select name ,value/1024/1024/1024 from gv$sysstat where name like '%pga%';
NAME VALUE/1024/1024/1024
---------------------------------------------------------------- --------------------
session pga memory 1.11519291
session pga memory max 1.17171147
session pga memory .900773883
session pga memory max .925554156
SQL> select name ,value/1024/1024/1024 from v$sysstat where name like '%pga%';
NAME VALUE/1024/1024/1024
---------------------------------------------------------------- --------------------
session pga memory 1.11802778
session pga memory max 1.17411909
作者: 王硕 时间: 2015-4-19 18:34
15.检查查询服务器的运行模式和数据库安装选项
SQL> select * from v$option;
PARAMETER VALUE
------------------------------- ----------------------------------
Objects TRUE
Real Application Clusters TRUE
Advanced replication TRUE
Bit-mapped indexes TRUE
Connection multiplexing TRUE
Connection pooling TRUE
Database queuing TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Parallel backup and recovery TRUE
Parallel execution TRUE
Parallel load TRUE
Point-in-time tablespace recovery TRUE
Fine-grained access control TRUE
Proxy authentication/authorization TRUE
Change Data Capture TRUE
Plan Stability TRUE
Online Index Build TRUE
Coalesce Index TRUE
Managed Standby TRUE
Materialized view rewrite TRUE
Materialized view warehouse refresh TRUE
Database resource manager TRUE
Spatial TRUE
Automatic Storage Management TRUE
Export transportable tablespaces TRUE
Transparent Application Failover TRUE
Fast-Start Fault Recovery TRUE
Sample Scan TRUE
Duplexed backups TRUE
Java TRUE
OLAP Window Functions TRUE
Block Media Recovery TRUE
Fine-grained Auditing TRUE
Application Role TRUE
Enterprise User Security TRUE
Oracle Data Guard TRUE
Oracle Label Security FALSE
OLAP TRUE
Basic Compression TRUE
Join index TRUE
Trial Recovery TRUE
Data Mining TRUE
Online Redefinition TRUE
Streams Capture TRUE
File Mapping TRUE
Block Change Tracking TRUE
Flashback Table TRUE
Flashback Database TRUE
Transparent Data Encryption TRUE
Backup Encryption TRUE
Unused Block Compression TRUE
Oracle Database Vault FALSE
Result Cache TRUE
SQL Plan Management TRUE
SecureFiles Encryption TRUE
Real Application Testing TRUE
Flashback Data Archive TRUE
DICOM TRUE
Active Data Guard TRUE
Server Flash Cache TRUE
Advanced Compression TRUE
XStream TRUE
Deferred Segment Creation TRUE
65 rows selected.
作者: 王硕 时间: 2015-4-19 18:35
16.用户检查
SQL> select username,account_status,default_tablespace,temporary_tablespace,created from dba_users;
USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
------------------------------ -------------------------------- ------------------------------ --------------------- ---------
MGMT_VIEW OPEN SYSTEM TEMP 15-AUG-09
SYS OPEN SYSTEM TEMP 15-AUG-09
SYSTEM OPEN SYSTEM TEMP 15-AUG-09
DBSNMP OPEN SYSAUX TEMP 15-AUG-09
SYSMAN OPEN SYSAUX TEMP 15-AUG-09
OUTLN EXPIRED & LOCKED SYSTEM TEMP 15-AUG-09
FLOWS_FILES EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09
MDSYS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09
ORDSYS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09
EXFSYS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09
WMSYS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09
APPQOSSYS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09
APEX_030200 EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09
OWBSYS_AUDIT EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09
ORDDATA EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09
CTXSYS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09
ANONYMOUS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09
XDB EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09
ORDPLUGINS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09
OWBSYS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09
SI_INFORMTN_SCHEMA EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09
OLAPSYS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09
SCOTT EXPIRED & LOCKED USERS TEMP 15-AUG-09
ORACLE_OCM EXPIRED & LOCKED USERS TEMP 15-AUG-09
XS$NULL EXPIRED & LOCKED USERS TEMP 15-AUG-09
MDDATA EXPIRED & LOCKED USERS TEMP 15-AUG-09
DIP EXPIRED & LOCKED USERS TEMP 15-AUG-09
APEX_PUBLIC_USER EXPIRED & LOCKED USERS TEMP 15-AUG-09
SPATIAL_CSW_ADMIN_USR EXPIRED & LOCKED USERS TEMP 15-AUG-09
SPATIAL_WFS_ADMIN_USR EXPIRED & LOCKED USERS TEMP 15-AUG-09
30 rows selected.
SQL> select a.username , a.temporary_tablespace "Temporary Tablespace" , b.contents
from dba_users a , dba_tablespaces b
where a.temporary_tablespace=b.tablespace_name
and b.contents <> 'TEMPORARY';
no rows selected
作者: 王硕 时间: 2015-4-19 18:35
17.控制文件检查
SQL> col name for a60
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ------------------------------------------------------------ --- ---------- --------------
+DATA/db/controlfile/current.260.776365491 NO 16384 1130
+ARCH/db/controlfile/current.256.776365491 YES 16384 1130
作者: 王硕 时间: 2015-4-19 18:35
18.无效对象检查
SQL> col OBJECT_NAME for a24
SQL> SELECT owner , object_name, object_type,status ,LAST_DDL_TIME FROM dba_objects WHERE status like 'INVALID';
no rows selected
作者: 王硕 时间: 2015-4-19 18:36
19.表空间和数据文件检查
1）数据文件
SQL> col file_name for a56
SQL> set linesize 300
SQL> select file_id,file_name,tablespace_name,autoextensible from dba_data_files;
select count(*) from v$datafile;
FILE_ID FILE_NAME TABLESPACE_NAME AUT
---------- -------------------------------------------------------- --------------- ---
4 +DATA/db/datafile/users.259.776365283 USERS YES
3 +DATA/db/datafile/undotbs1.258.776365283 UNDOTBS1 YES
2 +DATA/db/datafile/sysaux.257.776365283 SYSAUX YES
1 +DATA/db/datafile/system.256.776365281 SYSTEM YES
5 +DATA/db/datafile/undotbs2.264.776365753 UNDOTBS2 YES
SQL> show parameter db_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_files integer 200
SQL> select name from v$datafile
2 ;
NAME
--------------------------------------------------------------------------------
+DATA/db/datafile/system.256.776365281
+DATA/db/datafile/sysaux.257.776365283
+DATA/db/datafile/undotbs1.258.776365283
+DATA/db/datafile/users.259.776365283
+DATA/db/datafile/undotbs2.264.776365753
SQL> select member from v$logfile
2 ;
MEMBER
--------------------------------------------------------------------------------
+DATA/db/onlinelog/group_2.262.776365501
+ARCH/db/onlinelog/group_2.258.776365505
+DATA/db/onlinelog/group_1.261.776365493
+ARCH/db/onlinelog/group_1.257.776365499
+DATA/db/onlinelog/group_3.265.776365887
+ARCH/db/onlinelog/group_3.259.776365889
+DATA/db/onlinelog/group_4.266.776365891
+ARCH/db/onlinelog/group_4.260.776365903
8 rows selected.
SQL> select name from v$controlfile
2 ;
NAME
--------------------------------------------------------------------------------
+DATA/db/controlfile/current.260.776365491
+ARCH/db/controlfile/current.256.776365491
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/db/tempfile/temp.263.776365543
SQL> col name for a20
SQL> SELECT file#, ts#, NAME, status, BYTES / 1024 / 1024 size_mb FROM v$datafile UNION ALL SELECT file#, ts#, NAME, status, BYTES / 1024 / 1024 size_mb FROM v$tempfile;
FILE# TS# NAME STATUS SIZE_MB
---------- ---------- -------------------- ------- ----------
1 0 +DATA/db/datafile/sy SYSTEM 680
stem.256.776365281
2 1 +DATA/db/datafile/sy ONLINE 570
saux.257.776365283
3 2 +DATA/db/datafile/un ONLINE 95
dotbs1.258.776365283
4 4 +DATA/db/datafile/us ONLINE 5
ers.259.776365283
FILE# TS# NAME STATUS SIZE_MB
---------- ---------- -------------------- ------- ----------
5 5 +DATA/db/datafile/un ONLINE 25
dotbs2.264.776365753
1 3 +DATA/db/tempfile/te ONLINE 32
mp.263.776365543
6 rows selected.
作者: 王硕 时间: 2015-4-19 18:39
2）表空间
SQL> set linesize 300
SQL> col tablespace_name for a16
SQL> SELECT upper(f.tablespace_name) "tablespace_name",
d.Tot_grootte_Mb "tablespace(M)",
d.Tot_grootte_Mb - f.total_bytes "used(M)",
round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2) "use%",
f.total_bytes "free_space(M)",
round(f.total_bytes / d.Tot_grootte_Mb * 100,2) "free%",
f.max_bytes "max_block(M)"
FROM
(SELECT tablespace_name,
round(SUM(bytes)/(1024*1024),2) total_bytes,
round(MAX(bytes)/(1024*1024),2) max_bytes
FROM sys.dba_free_space
GROUP BY tablespace_name) f,
(SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb
FROM sys.dba_data_files dd
GROUP BY dd.tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name
ORDER BY 4 DESC;
tablespace_name tablespace(M) used(M) use% free_space(M) free% max_block(M)
---------------- ------------- ---------- ---------- ------------- ---------- ------------
SYSTEM 680 673.31 99.02 6.69 .98 6
SYSAUX 570 534.94 93.85 35.06 6.15 35.06
UNDOTBS2 25 11.31 45.24 13.69 54.76 4.5
USERS 5 1.31 26.2 3.69 73.8 3.69
UNDOTBS1 95 18.12 19.07 76.88 80.93 53
表空间的空间使用情况
SQL> SELECT df.tablespace_name, COUNT (*) datafile_count,
ROUND (SUM (df.BYTES) / 1048576) size_mb,
ROUND (SUM (free.BYTES) / 1048576, 2) free_mb,
ROUND (SUM (df.BYTES) / 1048576 - SUM (free.BYTES) / 1048576, 2 ) used_mb,
ROUND (MAX (free.maxbytes) / 1048576, 2) maxfree,
100 - ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_used,
ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_free
FROM dba_data_files df,
(SELECT tablespace_name, file_id, SUM (BYTES) BYTES, MAX (BYTES) maxbytes FROM dba_free_space GROUP BY tablespace_name, file_id) free
WHERE df.tablespace_name = free.tablespace_name(+) AND df.file_id = free.file_id(+)
GROUP BY df.tablespace_name ;
TABLESPACE_NAME DATAFILE_COUNT SIZE_MB FREE_MB USED_MB MAXFREE PCT_USED PCT_FREE
---------------- -------------- ---------- ---------- ---------- ---------- ---------- ----------
UNDOTBS1 1 95 76.88 18.13 53 19.08 80.92
SYSAUX 1 570 35.06 534.94 35.06 93.85 6.15
USERS 1 5 3.69 1.31 3.69 26.25 73.75
SYSTEM 1 680 6.69 673.31 6 99.02 .98
UNDOTBS2 1 25 13.69 11.31 4.5 45.25 54.75
表空间可用性检查
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
---------------- ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
临时表空间使用情况和性能检查
SQL> SELECT tablespace_name, extent_management, block_size, initial_extent, next_extent, min_extents, max_extents, pct_increase FROM dba_tablespaces WHERE C;
TABLESPACE_NAME EXTENT_MAN BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
---------------- ---------- ---------- -------------- ----------- ----------- ----------- ------------
TEMP LOCAL 8192 1048576 1048576 1 0
SQL> SELECT username, default_tablespace, temporary_tablespace FROM dba_users;
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------- ----------------------------- ------------------------------
MGMT_VIEW SYSTEM TEMP
SYS SYSTEM TEMP
SYSTEM SYSTEM TEMP
DBSNMP SYSAUX TEMP
SYSMAN SYSAUX TEMP
OUTLN SYSTEM TEMP
FLOWS_FILES SYSAUX TEMP
MDSYS SYSAUX TEMP
ORDSYS SYSAUX TEMP
EXFSYS SYSAUX TEMP
WMSYS SYSAUX TEMP
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
-------------------------- ---------------------------- ------------------------------
APPQOSSYS SYSAUX TEMP
APEX_030200 SYSAUX TEMP
OWBSYS_AUDIT SYSAUX TEMP
ORDDATA SYSAUX TEMP
CTXSYS SYSAUX TEMP
ANONYMOUS SYSAUX TEMP
XDB SYSAUX TEMP
ORDPLUGINS SYSAUX TEMP
OWBSYS SYSAUX TEMP
SI_INFORMTN_SCHEMA SYSAUX TEMP
OLAPSYS SYSAUX TEMP
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------- ---------------------------- ------------------------------
SCOTT USERS TEMP
ORACLE_OCM USERS TEMP
XS$NULL USERS TEMP
MDDATA USERS TEMP
DIP USERS TEMP
APEX_PUBLIC_USER USERS TEMP
SPATIAL_CSW_ADMIN_USR USERS TEMP
SPATIAL_WFS_ADMIN_USR USERS TEMP
SQL>select tablespace_name,initial_extent,next_extent,max_extents,pct_increase,extent_management,status from dba_tablespaces order by extent_management;
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS PCT_INCREASE EXTENT_MAN STATUS
---------------- -------------- ----------- ----------- ------------ ---------- ---------
SYSTEM 65536 2147483645 LOCAL ONLINE
SYSAUX 65536 2147483645 LOCAL ONLINE
UNDOTBS2 65536 2147483645 LOCAL ONLINE
TEMP 1048576 1048576 0 LOCAL ONLINE
USERS 65536 2147483645 LOCAL ONLINE
UNDOTBS1 65536 2147483645 LOCAL ONLINE
SQL> select tablespace_name,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
TABLESPACE_NAME EXTENT_MAN SEGMEN
---------------- ---------- ------
SYSTEM LOCAL MANUAL
SYSAUX LOCAL AUTO
UNDOTBS1 LOCAL MANUAL
TEMP LOCAL MANUAL
USERS LOCAL AUTO
UNDOTBS2 LOCAL MANUAL
作者: 王硕 时间: 2015-4-19 18:39
20.表
1、监控表的增长
SQL> select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='FOUNDER' ORDER BY bytes/1024/1024 desc;
作者: 王硕 时间: 2015-4-19 18:40
2、表和索引分析信息
SQL> SELECT 'table', COUNT (*) FROM dba_tables WHERE last_analyzed IS NOT NULL GROUP BY 'table'
UNION ALL
SELECT 'index', COUNT (*) FROM dba_indexes WHERE last_analyzed IS NOT NULL GROUP BY 'index';
'TABL COUNT(*)
----- ----------
table 2596
index 3704
作者: 王硕 时间: 2015-4-19 18:41
3.未建索引的表
SQL> SELECT /*+ rule */
owner, segment_name, segment_type, tablespace_name,
TRUNC (BYTES / 1024 / 1024, 1) size_mb
FROM dba_segments t
WHERE NOT EXISTS (
SELECT 'x'
FROM dba_indexes i
WHERE t.owner = i.table_owner
AND t.segment_name = i.table_name)
AND t.segment_type IN ('TABLE', 'TABLE PARTITION')
AND t.owner NOT IN ('SYS', 'SYSTEM')
ORDER BY 5 DESC;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME SIZE_MB
------------------------------ --------------------------------------------------------------------------------- ------------------ ---------------- ----------
SYSMAN SYS_IOT_OVER_67134 TABLE SYSAUX .3
SYSMAN SYS_IOT_OVER_69647 TABLE SYSAUX .1
SYSMAN SYS_IOT_OVER_67755 TABLE SYSAUX .1
DBSNMP MGMT_SNAPSHOT_SQL TABLE SYSAUX 0
DBSNMP MGMT_BASELINE TABLE SYSAUX 0
DBSNMP MGMT_BASELINE_SQL TABLE SYSAUX 0
DBSNMP MGMT_RESPONSE_CONFIG TABLE SYSAUX 0
WMSYS AQ$_WM$EVENT_QUEUE_TABLE_L TABLE SYSAUX 0
WMSYS SYS_IOT_OVER_13393 TABLE SYSAUX 0
EXFSYS EXF$VERSION TABLE SYSAUX 0
EXFSYS SYS_IOT_OVER_55269 TABLE SYSAUX 0
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME SIZE_MB
------------------------------ --------------------------------------------------------------------------------- ------------------ ---------------- ----------
EXFSYS SYS_IOT_OVER_55279 TABLE SYSAUX 0
EXFSYS EXF$VALIDIOPER TABLE SYSAUX 0
作者: 王硕 时间: 2015-4-19 18:43
21.sort_segment检查
SQL> select tablespace_name,extent_size db_blocks_per_extent,total_extents,
used_extents,free_extents from v$sort_segment; 2
TABLESPACE_NAME DB_BLOCKS_PER_EXTENT TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS
---------------- -------------------- ------------- ------------ ------------
TEMP 128 28 0 28
作者: 王硕 时间: 2015-4-19 18:44
22.数据库总大小
SQL> select round(sum(space)) all_space_M from
(
select sum(bytes)/1024/1024 space from dba_data_files
union all
select nvl(sum(bytes)/1024/1024,0) space from dba_temp_files
union all
select sum(bytes)/1024/1024 space from v$log
);
ALL_SPACE_M
-----------
1607
作者: 王硕 时间: 2015-4-19 18:44
23.检测连接数情况
（1）
SQL>select SW.Sid,S.Username,SW.Event,SW.Wait_Time,SW.State,SW.Seconds_In_Wait SEC_IN_WAIT
from v$session S,v$session_wait SW where S.Username is not null and SW.Sid=S.Sid
and SW.event not like '%SQL*Net%' order by SW.Wait_Time Desc;SP2-0042: unknown command "（1??" - rest of line ignored.
SQL> 2 3
SID USERNAME EVENT WAIT_TIME STATE SEC_IN_WAIT
---------- ------------------------------ ---------------------------------------------------------------- ---------- ------------------- -----------
31 SYS Streams AQ: waiting for messages in the queue 0 WAITING
（2）
SQL> select count(*) from v$session;
COUNT(*)
----------
42
（3）
SQL> select sid,serial#,username,program,machine,status from v$session;
SID SERIAL# USERNAME PROGRAM MACHINE STATUS
---------- ---------- ------------------------------ ------------------------------------------------ ---------------------------------------------------------------- --------
2 1 oracle@node1 (PMON) node1 ACTIVE
3 1 oracle@node1 (VKTM) node1 ACTIVE
4 1 oracle@node1 (GEN0) node1 ACTIVE
5 1 oracle@node1 (DIAG) node1 ACTIVE
6 1 oracle@node1 (DBRM) node1 ACTIVE
7 1 oracle@node1 (PING) node1 ACTIVE
8 1 oracle@node1 (PSP0) node1 ACTIVE
9 1 oracle@node1 (ACMS) node1 ACTIVE
10 1 oracle@node1 (DIA0) node1 ACTIVE
11 1 oracle@node1 (LMON) node1 ACTIVE
12 1 oracle@node1 (LMD0) node1 ACTIVE
作者: 王硕 时间: 2015-4-19 18:45
24.回滚段信息
1）信息1
SQL> col segment_name format a20
SQL> col tablespace_name format a20
SQL> select segment_name,owner,tablespace_name,
dba_rollback_segs.status
from dba_rollback_segs,v$Datafile where file_id=file#;
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
-------------------- ------ -------------------- ----------------
SYSTEM SYS SYSTEM ONLINE
_SYSSMU1_3780397527$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU2_2232571081$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU3_2097677531$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU4_1152005954$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU5_1527469038$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU6_2443381498$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU7_3286610060$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU8_2012382730$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU9_1424341975$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU10_3550978943 PUBLIC UNDOTBS1 ONLINE
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
-------------------- ------ -------------------- ----------------
$
_SYSSMU11_1945702759 PUBLIC UNDOTBS2 ONLINE
$
_SYSSMU12_3371397884 PUBLIC UNDOTBS2 ONLINE
$
_SYSSMU13_1140480427 PUBLIC UNDOTBS2 ONLINE
$
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
-------------------- ------ -------------------- ----------------
_SYSSMU14_928043570$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU15_2406539181 PUBLIC UNDOTBS2 ONLINE
$
_SYSSMU16_186429994$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU17_1256196737 PUBLIC UNDOTBS2 ONLINE
$
_SYSSMU18_1001431560 PUBLIC UNDOTBS2 ONLINE
$
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
-------------------- ------ -------------------- ----------------
_SYSSMU19_2591223951 PUBLIC UNDOTBS2 ONLINE
$
_SYSSMU20_1090697094 PUBLIC UNDOTBS2 ONLINE
$
21 rows selected.
作者: 王硕 时间: 2015-4-19 18:45
2）信息2
SQL> select segment_name,initial_extent,next_extent,min_extents,
owner,dba_rollback_segs.status status,optsize
from dba_rollback_segs,v$rollstat
where dba_rollback_segs.segment_id=v$rollstat.usn; 2 3 4
SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS OWNER STATUS OPTSIZE
-------------------- -------------- ----------- ----------- ------ ---------------- ----------
SYSTEM 114688 57344 1 SYS ONLINE
_SYSSMU1_3780397527$ 131072 65536 2 PUBLIC ONLINE
_SYSSMU2_2232571081$ 131072 65536 2 PUBLIC ONLINE
_SYSSMU3_2097677531$ 131072 65536 2 PUBLIC ONLINE
_SYSSMU4_1152005954$ 131072 65536 2 PUBLIC ONLINE
_SYSSMU5_1527469038$ 131072 65536 2 PUBLIC ONLINE
_SYSSMU6_2443381498$ 131072 65536 2 PUBLIC ONLINE
_SYSSMU7_3286610060$ 131072 65536 2 PUBLIC ONLINE
_SYSSMU8_2012382730$ 131072 65536 2 PUBLIC ONLINE
_SYSSMU9_1424341975$ 131072 65536 2 PUBLIC ONLINE
_SYSSMU10_3550978943 131072 65536 2 PUBLIC ONLINE
SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS OWNER STATUS OPTSIZE
-------------------- -------------- ----------- ----------- ------ ---------------- ----------
$
11 rows selected.
作者: 王硕 时间: 2015-4-19 18:45
3）信息3
SQL> select substr(V$rollname.NAME,1,20) "Rollback_Name",
substr(V$rollstat.EXTENTS,1,6) "EXTENT",
v$rollstat.RSSIZE, v$rollstat.WRITES,
substr(v$rollstat.XACTS,1,6) "XACTS",
v$rollstat.GETS,
substr(v$rollstat.WAITS,1,6) "WAITS",
v$rollstat.HWMSIZE, v$rollstat.SHRINKS,
substr(v$rollstat.WRAPS,1,6) "WRAPS",
substr(v$rollstat.EXTENDS,1,6) "EXTEND",
v$rollstat.AVESHRINK,
v$rollstat.AVEACTIVE
from v$rollname, v$rollstat
where v$rollname.USN = v$rollstat.USN
order by v$rollname.USN;
Rollback_Name EXTENT RSSIZE WRITES XACTS GETS WAITS HWMSIZE SHRINKS WRAPS EXTEND AVESHRINK AVEACTIVE
---------------- ------------ ---------- ---------- ------------ ---------- ------------ ---------- ---------- ------------ ------------ ---------- ----------
SYSTEM 6 385024 10844 0 108 0 385024 0 0 0 0 0
_SYSSMU1_3780397 5 2285568 579670 0 888 0 2285568 0 2 0 0 100105
527$
_SYSSMU2_2232571 5 2285568 1187420 0 1116 0 2285568 0 5 1 0 114278
081$
_SYSSMU3_2097677 5 2285568 1129796 0 1122 0 2285568 0 4 0 0 100090
531$
_SYSSMU4_1152005 5 2285568 445496 0 936 0 2285568 0 0 0 0 0
Rollback_Name EXTENT RSSIZE WRITES XACTS GETS WAITS HWMSIZE SHRINKS WRAPS EXTEND AVESHRINK AVEACTIVE
---------------- ------------ ---------- ---------- ------------ ---------- ------------ ---------- ---------- ------------ ------------ ---------- ----------
954$
_SYSSMU5_1527469 4 2220032 1390190 0 1168 0 2220032 0 3 1 0 114916
038$
_SYSSMU6_2443381 7 450560 1452278 0 1237 1 909312 1 28 10 458752 228769
498$
_SYSSMU7_3286610 3 188416 818110 0 1086 1 712704 1 15 7 524288 56609
060$
Rollback_Name EXTENT RSSIZE WRITES XACTS GETS WAITS HWMSIZE SHRINKS WRAPS EXTEND AVESHRINK AVEACTIVE
---------------- ------------ ---------- ---------- ------------ ---------- ------------ ---------- ---------- ------------ ------------ ---------- ----------
_SYSSMU8_2012382 5 2285568 1270898 0 1171 0 2285568 0 4 0 0 98181
730$
_SYSSMU9_1424341 13 843776 1006092 0 1095 0 843776 0 18 4 0 75949
975$
_SYSSMU10_355097 4 2220032 1243588 0 1120 0 2220032 0 3 1 0 96648
8943
11 rows selected.
作者: 王硕 时间: 2015-4-19 18:46
4）信息4
SQL> select r.name Rollback_Name,
p.pid Oracle_PID,
p.spid OS_PID,
nvl(p.username,'NO TRANSACTION') Transaction,
p.terminal Terminal
from v$lock l, v$process p, v$rollname r
where l.addr = p.addr(+)
and trunc(l.id1(+)/65536)=r.usn
and l.type(+) = 'TX'
and l.lmode(+) = 6
order by r.name;
ROLLBACK_NAME ORACLE_PID OS_PID TRANSACTION TERMINAL
---------------- ---------- ------------------------ --------------- ------------------------------
SYSTEM NO TRANSACTION
_SYSSMU10_355097 NO TRANSACTION
8943$
_SYSSMU1_3780397 NO TRANSACTION
527$
_SYSSMU2_2232571 NO TRANSACTION
081$
_SYSSMU3_2097677 NO TRANSACTION
ROLLBACK_NAME ORACLE_PID OS_PID TRANSACTION TERMINAL
---------------- ---------- ------------------------ --------------- ------------------------------
531$
_SYSSMU4_1152005 NO TRANSACTION
954$
_SYSSMU5_1527469 NO TRANSACTION
038$
_SYSSMU6_2443381 NO TRANSACTION
498$
ROLLBACK_NAME ORACLE_PID OS_PID TRANSACTION TERMINAL
---------------- ---------- ------------------------ --------------- ------------------------------
_SYSSMU7_3286610 NO TRANSACTION
060$
_SYSSMU8_2012382 NO TRANSACTION
730$
_SYSSMU9_1424341 NO TRANSACTION
975$
11 rows selected.
作者: 王硕 时间: 2015-4-19 18:46
5）回滚段的争用情况
SQL> select name ,waits ,gets ,waits/gets "Ratio" from v$rollstat a ,v$rollname b where a.usn=b.usn;
NAME WAITS GETS Ratio
-------------------- ---------- ---------- ----------
SYSTEM 0 111 0
_SYSSMU1_3780397527$ 0 893 0
_SYSSMU2_2232571081$ 0 1119 0
_SYSSMU3_2097677531$ 0 1127 0
_SYSSMU4_1152005954$ 0 939 0
_SYSSMU5_1527469038$ 0 1173 0
_SYSSMU6_2443381498$ 1 1242 .000805153
_SYSSMU7_3286610060$ 1 1091 .00091659
_SYSSMU8_2012382730$ 0 1176 0
_SYSSMU9_1424341975$ 0 1100 0
_SYSSMU10_3550978943 0 1125 0
NAME WAITS GETS Ratio
-------------------- ---------- ---------- ----------
$
11 rows selected.
作者: 王硕 时间: 2015-4-19 18:46
6）rollback信息
SQL> select substr(sys.dba_rollback_segs.SEGMENT_ID,1,5) "ID#",
substr(sys.dba_segments.OWNER,1,8) "Owner",
substr(sys.dba_segments.TABLESPACE_NAME,1,17) "Tablespace Name",
substr(sys.dba_segments.SEGMENT_NAME,1,12) "Rollback Name",
substr(sys.dba_rollback_segs.INITIAL_EXTENT,1,10) "INI_Extent",
substr(sys.dba_rollback_segs.NEXT_EXTENT,1,10) "Next Exts",
substr(sys.dba_segments.MIN_EXTENTS,1,5) "MinEx",
substr(sys.dba_segments.MAX_EXTENTS,1,5) "MaxEx",
substr(sys.dba_segments.PCT_INCREASE,1,5) "%Incr",
substr(sys.dba_segments.BYTES,1,15) "Size (Bytes)",
substr(sys.dba_segments.EXTENTS,1,6) "Extent#",
substr(sys.dba_rollback_segs.STATUS,1,10) "Status"
from sys.dba_segments, sys.dba_rollback_segs
where sys.dba_segments.segment_name = sys.dba_rollback_segs.segment_name and
sys.dba_segments.segment_type = 'ROLLBACK'
order by sys.dba_rollback_segs.segment_id;
ID# Owner Tablespace Name Rollback Name INI_Extent Next Exts MinEx MaxEx %Incr Size (Bytes) Extent# Status
---------- ---------------- ---------------------------------- ------------------------ -------------------- -------------------- ---------- ---------- ---------- ------------------------------ ------------ --------------------
0 SYS SYSTEM SYSTEM 114688 57344 1 32765 0 393216 6 ONLINE
作者: 王硕 时间: 2015-4-19 18:48
25.Redo log信息检查
1）Redo Log 文件状态
SQL> col member for a56
SQL> select f.member "member",
f.group# "group",
l.bytes/1024/1024 "size",
l.status
from v$logfile f, v$log l
where f.group#=l.group#
order by f.group#,f.member; 2 3 4 5 6 7
member group size STATUS
-------------------------------------------------------- ---------- ---------- ----------------
+ARCH/db/onlinelog/group_1.257.776365499 1 50 INACTIVE
+DATA/db/onlinelog/group_1.261.776365493 1 50 INACTIVE
+ARCH/db/onlinelog/group_2.258.776365505 2 50 CURRENT
+DATA/db/onlinelog/group_2.262.776365501 2 50 CURRENT
+ARCH/db/onlinelog/group_3.259.776365889 3 50 INACTIVE
+DATA/db/onlinelog/group_3.265.776365887 3 50 INACTIVE
+ARCH/db/onlinelog/group_4.260.776365903 4 50 CURRENT
+DATA/db/onlinelog/group_4.266.776365891 4 50 CURRENT
8 rows selected.
作者: 王硕 时间: 2015-4-19 18:49
2）LogGroup信息
SQL> SELECT group#, sequence#, bytes, members, status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 31 52428800 2 INACTIVE
2 32 52428800 2 CURRENT
3 23 52428800 2 INACTIVE
4 24 52428800 2 CURRENT
作者: 王硕 时间: 2015-4-19 18:49
3）关于log_buffer
SQL> select name,value from v$sysstat where name in('redo entries','redo buffer allocation retries');
NAME VALUE
-------------------- ----------
redo entries 88702
redo buffer allocati 0
on retries
作者: 王硕 时间: 2015-4-19 18:49
4）查询LOG大小及频率
SQL> set linesize 300
SQL> set pages 100
SQL>
SQL> column d1 form a20 heading "Date"
column sw_cnt form 99999 heading 'Number|of|Switches'
column Mb form 999,999 heading "Redo Size"
column redoMbytes form 999,999,9999 heading "Redo Log File Size (Mb)"
break on report
compute sum of sw_cnt on report
compute sum of Mb on report
var redoMbytes number;
begin
select max(bytes)/1024/1024 into :redoMbytes from v$log;
end;
/
print redoMbytes
select trunc(first_time) d1
, count(*) sw_cnt
, count(*) * :redoMbytes Mb
from v$log_history
group by trunc(first_time)
/SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4
PL/SQL procedure successfully completed.
SQL>
Redo Log File Size (Mb)
-----------------------
50
作者: 王硕 时间: 2015-4-19 18:50
26.IO情况检查
SQL> col file_name for a46
SQL> select
df.name file_name,
fs.phyrds reads,
fs.phywrts writes,
(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds)) readtime,
(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts)) writetime
from
v$datafile df,v$filestat fs
where df.file#=fs.file#
order by df.name;
select count(*) from v$session;
FILE_NAME READS WRITES READTIME WRITETIME
---------------------------------------------- ---------- ---------- ---------- ----------
+DATA/db/datafile/sysaux.257.776365283 4761 3110 9.64839319 .18488746
+DATA/db/datafile/system.256.776365281 8695 374 .045428407 .045454545
+DATA/db/datafile/undotbs1.258.776365283 148 519 7.11486486 .992292871
+DATA/db/datafile/undotbs2.264.776365753 58 61 14.6724138 .131147541
+DATA/db/datafile/users.259.776365283 6 2 0 0
作者: 王硕 时间: 2015-4-19 18:50
27.命中率相关检查
1）Shared Pool Size 命中率
SQL> select round((sum(gets)-sum(reloads))/sum(gets)*100,1) "libiary cache hit ratio %"
from v$librarycache where namespace
in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER');
libiary cache hit ratio %
-------------------------
98
作者: 王硕 时间: 2015-4-19 18:50
2）数据字典命中率
SQL> select round((1-sum(getmisses)/sum(gets))*100,1) "data dictionary hit ratio %"
from v$rowcache; 2
data dictionary hit ratio %
---------------------------
93.8
作者: 王硕 时间: 2015-4-19 18:50
3）锁竞争
SQL> select substr(ln.name,1,25) Name,
l.gets, l.misses,
100*(l.misses/l.gets) "% Ratio (STAY UNDER 1%)"
from v$latch l, v$latchname ln
where ln.name in ('cache buffers lru chain')
and ln.latch# = l.latch#; 2 3 4 5 6
NAME GETS MISSES % Ratio (STAY UNDER 1%)
-------------------- ---------- ---------- -----------------------
cache buffers lru ch 42225 0 0
ain
作者: 王硕 时间: 2015-4-19 18:50
4）排序命中率
SQL> select a.value "Sort(Disk)", b.value "Sort(Memory)",
round(100*(a.value/decode((a.value+b.value), 0,1,(a.value+b.value))),2) "% Ratio (STAY UNDER 5%)"
from v$sysstat a, v$sysstat b
where a.name = 'sorts (disk)'
and b.name = 'sorts (memory)'; 2 3 4 5
Sort(Disk) Sort(Memory) % Ratio (STAY UNDER 5%)
---------- ------------ -----------------------
0 30805 0
作者: 王硕 时间: 2015-4-19 18:51
5）数据缓冲区命中率
SQL> select round((1-(phy.value/(cur.value+con.value)))*100,1)||'%' ratio
from v$sysstat phy,v$sysstat cur,v$sysstat con
where phy.name='physical reads' and cur.name='db block gets' and con.name='consistent gets'; 2 3
RATIO
-----------------------------------------
98.7%
[此贴子已经被作者于2015-04-19 18:51:24编辑过]
作者: 王硕 时间: 2015-4-19 18:51
6）Miss LRU Hit命中率
SQL> column "Miss LRU Hit%" format 99.9999999;
col name format a40
select name, (sleeps/gets) "Miss LRU Hit%"
from v$latch where name ='cache buffers lru chain';SQL> SQL> 2
NAME Miss LRU Hit%
---------------------------------------- -------------
cache buffers lru chain .0000000
作者: 王硕 时间: 2015-4-19 18:51
7）检查内存排序性能
SQL> select a.name, to_char(value)
from v$statname a, v$sysstat
where a.statistic# = v$sysstat.statistic#
and a.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)'); 2 3 4
NAME TO_CHAR(VALUE)
---------------------------------------- ----------------------------------------
sorts (memory) 31095
sorts (disk) 0
sorts (rows) 1482328
作者: 王硕 时间: 2015-4-19 18:52
8）redo log buffer retry ratio
SQL> select to_char(r.value/e.value) "redo log buffer retry ratio"
from v$sysstat r,v$sysstat e
where r.name='redo buffer allocation retries'
and e.name='redo entries';
redo log buffer retry ratio
----------------------------------------
0
作者: 王硕 时间: 2015-4-19 18:52
9）wait等待检查
SQL> select count(*) total_in_wait from v$session_wait
where event='log buffer space'; 2
TOTAL_IN_WAIT
-------------
0
SQL> select event,total_waits,time_waited,average_wait
from v$system_event
where event like '%undo%'; 2 3
EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- ----------- ----------- ------------
undo segment extension 4 3 .76
SQL> select sid,seq#,event,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%'
and event not like 'rdbms%'; 2
SID SEQ# EVENT WAIT_TIME SECONDS_IN_WAIT
---------- ---------- ---------------------------------------------------------------- ---------- ---------------
2 2746 pmon timer 02
3 1 VKTM Logical Idle Wait 0 8392
5 36359 DIAG idle wait 00
7 2205 PING 01
10 31764 DIAG idle wait 00
12 39004 ges remote message 00
13 31731 gcs remote message 00
16 1528 heartbeat monitor sleep 05
21 843 smon timer 0 286
23 3989 ASM background timer 00
27 7629 wait for unread message on broadcast channel 00
31 37997 Streams AQ: waiting for messages in the queue 01
32 716 Streams AQ: waiting for time management or cleanup tasks 0 14
42 248 Space Manager: slave idle wait 02
46 7587 wait for unread message on broadcast channel 00
47 1086 Streams AQ: qmn coordinator idle wait 0 15
51 293 Streams AQ: qmn slave idle wait 0 15
55 3 VKRM Idle 0 3739
18 rows selected.
作者: 王硕 时间: 2015-4-19 18:52
28、查询lock锁
SQL> SELECT lpad(' ',DECODE(request,0,0,1))||sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
ORDER BY id1, request; 2 3 4
no rows selected
作者: 王硕 时间: 2015-4-19 18:54
TxrOQBz7.txt
(81.36 KB, 下载次数: 190)
2015-4-19 18:53 上传
点击文件名下载附件
rac 日常巡检
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |