Oracle inspection script and oracle Inspection

Oracle inspection script and oracle Inspection

 

#! /Bin/sh

# Get Hostname
HOSTNAME = 'hostname'

Logfile = xunjian _ 'date + % y % m % D'. log
Hostinfodir =/home/oracle/xunjian

If [[! -D $ hostinfodir]
Then
Mkdir-p $ hostinfodir
Fi

Date> $ hostinfodir/$ logfile

Echo "hostname" >>$ hostinfodir/$ logfile

Hostname> $ hostinfodir/$ logfile

Echo "uname-a" >>$ hostinfodir/$ logfile

Uname-a> $ hostinfodir/$ logfile

Echo "ulimit-a" >>$ hostinfodir/$ logfile

Ulimit-a> $ hostinfodir/$ logfile

Echo "cat/proc/cpuinfo"> $ hostinfodir/$ logfile

Cat/proc/cpuinfo> $ hostinfodir/$ logfile

Echo "cat/proc/meminfo"> $ hostinfodir/$ logfile

Cat/proc/meminfo> $ hostinfodir/$ logfile

Echo "vmstat 3 10" >>$ hostinfodir/$ logfile

Vmstat 3 10> $ hostinfodir/$ logfile

Echo "df-ha" >>$ hostinfodir/$ logfile

Df-ha> $ hostinfodir/$ logfile

Echo "ifconfig-a" >>$ hostinfodir/$ logfile

Ifconfig-a> $ hostinfodir/$ logfile

Echo "env | grep ORACLE" >>$ hostinfodir/$ logfile

Env | grep ORACLE> $ hostinfodir/$ logfile

Echo "cat $ ORACLE_HOME/network/admin/listener. ora"> $ hostinfodir/$ logfile

Cat $ ORACLE_HOME/network/admin/listener. ora> $ hostinfodir/$ logfile

Echo "cat $ ORACLE_HOME/network/admin/tnsnames. ora"> $ hostinfodir/$ logfile

Cat $ ORACLE_HOME/network/admin/tnsnames. ora> $ hostinfodir/$ logfile

Echo "===== osinfo end ========================"

Echo "========= oracleinfo begin ======="

Cd $ hostinfodir
Sqlplus/nolog <EOF
Conn/as sysdba;
Set termout off;
Set echo off;
Spool oracle.txt;
Set linesize 200;
Col name format a20;
Col value format a30;
-- Log file size mb:
Select 'Log file size unit mb: 'infoterm from dual;
Select round (bytes/(1024*1024), 2) total_g from v \ $ log where rownum <2;
-- Log file total number:
Select 'Log file total number: 'infoterm from dual;
Select count (*) from v \ $ log;
-- Log file number in one group:
Select 'Log file number in one group: 'infoterm from dual;
Select num from (select count (*) num from v \ $ logfile group by GROUP #) where rownum <2;
-- Sga info:
Show sga
Select 'sga info: 'infoterm from dual;
Select name, value from v \ $ parameter where name
In ('shared _ pool_size ', 'db _ cache_size', 'db _ block_buffers', 'Log _ buffer', 'large _ pool_size ', 'java _ pool_size ', 'pga _ aggregate_target ');
-- Database name
Select 'database name: 'infoterm from dual;
Select name from v \ $ database;
-- DB_BLOCK_SIZE
Select 'db _ BLOCK_SIZE: 'infoterm from dual;
Select value from v \ $ parameter where name in ('db _ block_size ');
-- Database version
Select 'database version: 'infoterm from dual;
Select version from v \ $ instance;
-- Datafile size;
Select 'datafile size: 'infoterm from dual;
Select round (sum (bytes)/1024/1024/1024) total_g from dba_data_files;
-- Datafile characterset
Select 'datafile characterset: 'infoterm from dual;
Select value from nls_database_parameters where parameter = 'nls _ CHARACTERSET ';
-- Archive
Select 'archive': 'infoterm from dual;
Select log_mode from v \ $ database;
Archive log list;
-- Datafile number:
Select 'datafile number: 'infoterm from dual;
Select count (*) from dba_data_files;
-- Tablespace number:
Select 'tablespace number: 'infoterm from dual;
Select count (*) from dba_tablespaces;
-- Controlfile number:
Select 'controlfile number: 'infoterm from dual;
Select count (*) from v \ $ controlfile;
-- Controlfile info:
Select 'controlfile info: 'infoterm from dual;
Col NAME format a60
Select * from v \ $ controlfile;
-- Logfile info:
Select 'logfile info: 'infoterm from dual;
Col member format a50;
Select * from v \ $ logfile order by GROUP #;
Select * from v \ $ log order by GROUP #;
-- Block_size info:
Select 'block _ size info: 'infoterm from dual;
Select distinct block_size from dba_tablespaces;
-- Datafile info:
Select 'datafile info: 'infoterm from dual;
Set linesize 500 pagesize 200
Col file_id for 9999999
Col file_name for a55
Col ts_name for a18
Col cur_mb for 99999
Col max_mb for 99999
Select file_id, tablespace_name ts_name, file_name, status, autoextensible,
Blocks/128 cur_mb, maxblocks/128 max_mb
From dba_data_files order by file_id;
-- Tempfile info:
Select 'tempfile info: 'infoterm from dual;
Select file_id, tablespace_name ts_name, file_name, status, autoextensible,
Blocks/128 cur_mb, maxblocks/128 max_mb
From dba_temp_files order by file_id;
-- Tablespace info:
Select 'tablespace info: 'infoterm from dual;
Set linesize 500 pagesize 200
Col tablespace_name format a20
Col contents for a9
Col ext_mgmt for a8
Col alloc_type for a9
Col ext_kb format 999999
Col ssm for a6
Col total_mb format a12
Col free_mb format a12
Col free_pct format a7
Select c. tablespace_name, c. contents, c. extent_management ext_mgmt,
C. allocation_type alloc_type, c. initial_extent/1024 ext_kb,
C. segment_space_management SSM, a. total_mb, B. free_mb,
To_char (100 * B. free_mb/a. total_mb, '2014. 99') free_pct
From
(Select tablespace_name, to_char (sum (bytes)/1048576, '192. 99') total_mb
From dba_data_files group by tablespace_name),
(Select tablespace_name, to_char (sum (bytes)/1048576, '192. 99') free_mb,
Count (*) free_exts, to_char (max (bytes)/1048576, '123') max_mb,
To_char (min (bytes)/1048576, '000000') min_mb
From dba_free_space group by tablespace_name) B,
Dba_tablespaces c
Where a. tablespace_name = B. tablespace_name (+) and c. tablespace_name = B. tablespace_name
Order by free_pct, tablespace_name;
-- System tablespace info:
Select 'System tablespace info: 'infoterm from dual;
Select owner, count (*) from dba_segments where tablespace_name = 'system' group by owner;
Select username, default_tablespace, temporary_tablespace from dba_users
Where default_tablespace = 'system' or temporary_tablespace = 'system ';
-- Invalid object info:
Select 'invalid object info: 'infoterm from dual;
Select count (*), owner from dba_objects where status = 'invalid' group by owner;
Select 'invalid index info: 'infoterm from dual;
Select owner, index_name from dba_indexes where status = 'unusable ';
-- Auth info:
Select 'auth info: 'infoterm from dual;
Select * from v \ $ pwfile_users;
Col grantee for a15;
Col granted_role for a15;
Col admin_option for a20;
Col default_role for a20;
Select * from dba_role_privs where granted_role = 'dba ';
Spool off;
Exit
EOF

Echo "========= oracleinfo end ==============="

Exit 0

 

 

 

This article is an original article. Do not reprint it. If you need to reprint, please indicate in detail the reprint Source