oracle11g 简单巡检

RAC测试环境：
            rac1  redhat 5.5     oracle  11.2.0.3.0
            rac2  redhat 5.5    oracle 11.2.0.3.0
DG测试环境：
           ws          redhat 5.5   oracle11gR2
           wsdg       redhat 5.5   oracle11gR2

虚拟机软件  VMware 10

1、主机配置信息

--操作系统：
[oracle@rac1 ~]$ uname -ar
Linux rac1 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux


--交换分区：
[oracle@rac1 ~]$ free -m
             total       used       free     shared    buffers     cached
Mem:          1000        988         12          0          2        292
-/+ buffers/cache:        693        307
Swap:         1983       1434        549


--数据库版本：
SYS@RACDB1> select * from v$version;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


--CPU：
[oracle@rac1 ~]$ grep "physical id" /proc/cpuinfo|sort -u|wc -l 
1
[oracle@rac1 ~]$ grep "cpu cores" /proc/cpuinfo|uniq
cpu cores       : 2
 
--内存：
[oracle@rac1 ~]$ grep Mem* /proc/meminfo 
MemTotal:      1024660 kB
MemFree:         14896 kB


--或者top命令


2、磁盘分区情况
[oracle@rac1 ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                      2.9G  636M  2.1G  24% /
/dev/mapper/VolGroup00-LogVol06
                       30G   13G   16G  44% /u01
/dev/mapper/VolGroup00-LogVol01
                      3.8G  1.2G  2.5G  33% /home
/dev/mapper/VolGroup00-LogVol03
                      7.8G  4.0G  3.5G  54% /usr
/dev/mapper/VolGroup00-LogVol04
                      2.9G  227M  2.5G   9% /var
/dev/mapper/VolGroup00-LogVol02
                      2.9G   72M  2.7G   3% /tmp
/dev/sda1              99M   13M   82M  14% /boot
tmpfs                 501M  212M  290M  43% /dev/shm


3、告警日志检查
--先看alert日志大小
[oracle@ws trace]$ ls -alsh alert_WS.log
280K -rw-r----- 1 oracle oinstall 274K Jul  1 09:36 alert_WS.log


--若日志文件太大，可将近一周的日志转储为新文件，查看新文件
--经查看近一周为6783-3342=3441行
[oracle@ws trace]$ tail -3441 alert_WS.log > /home/oracle/alert_WS_20150701.log
[oracle@ws trace]$ cd
[oracle@ws ~]$ vi alert_WS_20150701.log 


--rac日志
[grid@rac1 ~]$ cd /u01/app/11.2.0/grid/log/
[grid@rac1 log]$ ls
crs  diag  rac1
[grid@rac1 rac1]$ tail -100f alertrac1.log
2015-07-01 10:20:40.578
[crsd(5668)]CRS-2750:Cardinality violation detected on server 'rac2', resource 'ora.cvu' is in unexpected state. Details at (:CRSPE00104:) {2:46724:2} in /u01/app/11.2.0/grid/log/rac1/crsd/crsd.log.
2015-07-01 10:21:32.295
[crsd(5668)]CRS-2772:Server 'rac2' has been assigned to pool 'Generic'.
2015-07-01 10:21:32.395
[crsd(5668)]CRS-2772:Server 'rac2' has been assigned to pool 'ora.RACDB'.
[grid@rac1 rac1]$ tail -100f /u01/app/11.2.0/grid/log/rac1/crsd/crsd.log




4、表空间使用信息
--pct.free 不造咋算
select a.tablespace_name,
       total || 'M' as total,
       free || 'M' as free,
       round(free / total, 2) * 100 || '%' as "Free%",
       total - free || 'M' as used,
       round((total - free) / total, 2) * 100 || '%' as "Used%",
       c.autoextensible,
       c.online_status,
       a."Max Size",
       a."Max Size" - (total - free) as "Max Free",
       (a."Max Size" - (total - free))/a."Max Size" as "Max Free%"
  from (select tablespace_name, sum(bytes)/1024/1024 as total, decode(max(maxbytes),0,32767.984375*count(1),sum(maxbytes)/1024/1024) as "Max Size"
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) / 1024 / 1024 as free
          from dba_free_space
         group by tablespace_name) b,
       (select distinct tablespace_name, file_id, autoextensible,online_status
          from dba_data_files) c  
 where a.tablespace_name = b.tablespace_name
   and a.tablespace_name = c.tablespace_name;


5、 ASM磁盘组检查
select a.name gname,
       b.name dname,
       b.free_mb,
       b.total_mb,
       round(b.free_mb / b.total_mb,4) * 100 "free%",
       b.path,
       b.state
  from v$asm_diskgroup a, v$asm_disk b
where a.group_number = b.group_number


6、重做日志信息
select c.instance_name,
       a.thread#,
       a.group#,
       b.member,
       a.SEQUENCE#,
       a.status "log status",
       b.status,
       a.ARCHIVED,
       a.bytes / 1024 / 1024 || 'M' as "Size/MB"
  from v$log a, v$logfile b, gv$instance c
 where a.group# = b.group#
   and a.THREAD# = c.thread#
 order by a.thread#, a.group#


--重做日志近期切换频率
select trunc(a.first_time) as "Date",
       count(*) as "Total",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '00', 1, 0)) as "00",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '01', 1, 0)) as "01",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '02', 1, 0)) as "02",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '03', 1, 0)) as "03",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '04', 1, 0)) as "04",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '05', 1, 0)) as "05",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '06', 1, 0)) as "06",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '07', 1, 0)) as "07",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '08', 1, 0)) as "08",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '09', 1, 0)) as "09",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '10', 1, 0)) as "10",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '11', 1, 0)) as "11",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '12', 1, 0)) as "12",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '13', 1, 0)) as "13",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '14', 1, 0)) as "14",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '15', 1, 0)) as "15",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '16', 1, 0)) as "16",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '17', 1, 0)) as "17",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '18', 1, 0)) as "18",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '19', 1, 0)) as "19",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '20', 1, 0)) as "20",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '21', 1, 0)) as "21",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '22', 1, 0)) as "22",
       sum(decode(to_char(a.FIRST_TIME, 'hh24'), '23', 1, 0)) as "23"
  from v$log_history a
 where trunc(a.FIRST_TIME) > sysdate - 30
 group by trunc(a.first_time)
 order by trunc(a.first_time)


7、失效对象信息
select a.owner, a.object_name, a.object_type, a.status
  from dba_objects a
 where a.status <> 'VALID'


8、数据库备份信息
SYS@RACDB1> !rman target /


Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jul 7 11:49:09 2015


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


connected to target database: RACDB (DBID=879763884)


RMAN> list backup;


using target database control file instead of recovery catalog


List of Backup Sets
===================




BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    5.55M      DISK        00:00:07     15-JUN-15      
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20150615T154
。。。


RMAN> list backup summary;




List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
10      B  F  A DISK        15-JUN-15       1       1       NO         TAG20150615T154440
11      B  F  A DISK        15-JUN-15       1       1       NO         TAG20150615T154440
。。。


9、 DG状态配置检查
--主
SYS@WS> select open_mode, database_role from v$database;


OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY


SYS@WS> select group#,sequence# from v$log where status='CURRENT';


GROUP#  SEQUENCE#
------ ----------
     1         28
--备
SYS@SBDB> alter database recover managed standby database using current logfile disconnect from session;


Database altered.


SYS@SBDB> select open_mode, database_role from v$database;


OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY


SYS@SBDB> select max(sequence#) lastseq from v$archived_log where applied='YES' group by thread#;


   LASTSEQ
----------
        27
SYS@SBDB> select GROUP#,SEQUENCE#,ARCHIVED,FIRST_TIME,first_change#,LAST_CHANGE# from v$standby_log;


GROUP#  SEQUENCE# ARC FIRST_TIM FIRST_CHANGE# LAST_CHANGE#
------ ---------- --- --------- ------------- ------------
     4          0 NO
     5         28 YES 01-JUL-15       1545507      1557016
     6          0 YES


--查看备库alert日志  tail -100f
alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (SBDB)
Tue Jul 07 13:46:41 2015
MRP0 started with pid=24, OS id=6426 
MRP0: Background Managed Standby Recovery process started (SBDB)
 started logmerger process
Tue Jul 07 13:46:46 2015
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Completed: alter database recover managed standby database using current logfile disconnect from session
Media Recovery Waiting for thread 1 sequence 30 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 30 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/SBDB/st_redo01.log

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30187411/viewspace-1725819/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30187411/viewspace-1725819/