日常巡检脚本
主机配置
服务器型号：dmidecode |grep  Product
server name:hostname
OS VERSION:lsb_release -a
cpu:more /proc/cpuinfo |grep processor| wc -l
内存:grep MemTotal /proc/meminfo  or  free
主机型号:dmidecode |grep Product
内存参数:less /etc/sysctl.conf
oracle用户限制：ulimit -a
硬盘使用情况  df -h

数据库的当前版本
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
已安装的组件
 set linesize 1000
 set pagesize 1000
 select * from v$option--This view lists database options and features
已装载的产品选项
set linesize 1000
set pagesize 1000
col COMP_NAME for a40
select COMP_ID, COMP_NAME, VERSION,STATUS from   dba_registry;
--DBA_REGISTRY displays information about the components loaded into the database.

备份控制文件到alert文件中
Alter database backup controlfile to trace;或
alter database backup controlfile to 'path'

检查日志文件
set linesize 1000
set pagesize 1000
SQL>  select group#,thread#,bytes/1024/1024 size_MB , members, archived,status from v$Log;

    GROUP#    THREAD#    SIZE_MB    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
         1          1         10          1 YES INACTIVE
         2          1         10          1 NO  CURRENT
         3          1         10          1 YES UNUSED

select * From v$logfile order by 1

检查数据文件
select count(*),sum(bytes)/1024/1024/1024 from v$datafile;
select name,sum(bytes)/1024/1024/1024 from v$datafile group by name;
select TABLESPACE_NAME,FILE_ID, bytes/1024/1024 SIZE_MB,AUTOEXTENSIBLE AUT,STATUS,FILE_NAME from dba_data_files;
检测system表空间里的用户对象
SQL> select owner,segment_type,segment_name from dba_segments where owner not in('SYS','SYSTEM') and tablespace_name='SYSTEM' order by 1;

OWNER                          SEGMENT_TYPE       SEGMENT_NAME
------------------------------ ------------------ ---------------------------------------------------------------------------------
OUTLN                          TABLE              OL$
OUTLN                          TABLE              OL$HINTS
OUTLN                          LOBINDEX           SYS_IL0000000453C00021$$
OUTLN                          LOBSEGMENT         SYS_LOB0000000453C00021$$
OUTLN                          INDEX              OL$NODE_OL_NAME
OUTLN                          INDEX              OL$NAME
OUTLN                          INDEX              OL$SIGNATURE
OUTLN                          INDEX              OL$HNT_NUM
OUTLN                          TABLE              OL$NODES
表空间的使用率
SELECT df.TABLESPACE_NAME,FILES, extent_management ,sum_m as TOTAL_SIZE,
--sum(largest) as "MAXFREE_MB",
sum_free_m as "FREE_MB",to_char(100*sum_free_m/sum_m, '999.99') AS FREE_PCT
--,sum(blocks) as "FREE_EXTENTS"
FROM ( SELECT tablespace_name,count(file_id) as files ,
sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name,--max(bytes)/1024/1024 largest,
sum(bytes)/1024/1024 AS sum_free_m --,count(blocks) as blocks
FROM dba_free_space GROUP BY tablespace_name ) fs,
(select tablespace_name,extent_management from dba_tablespaces) ts
where df.tablespace_name=fs.tablespace_name and fs.tablespace_name=ts.tablespace_name;

检查数据库对象
select owner,replace(object_type,' ','_') as OBJECT_TYPE,count(*)
from dba_objects where owner not in ('SYS','SYSTEM') group by
owner,object_type order by owner,object_type;

检测无效对象
col OBJECT_NAME for a40
select OWNER,OBJECT_NAME,replace(OBJECT_TYPE,' ','_') as
OBJECT_TYPE,STATUS,TIMESTAMP from dba_objects where status='INVALID'

检查db link
select * from dba_db_links

检查b-tree blevel>4
select * From dba_indexes where BLEVEL>4

检查用户dba权限

select grantee,granted_role from dba_role_privs where granted_role='DBA';
--DBA_ROLE_PRIVS describes the roles granted to all users and roles in the database.

检查sysdba 用户
SELECT * FROM v$pwfile_users;
--This view lists users who have been granted SYSDBA and SYSOPER privileges as derived from the password file.