rac监控脚本

#!/bin/bash
#srvctl status database -d rac
_control_file_bak=/home/oracle/backup/control$(date +%Y%m%d%H%M).bak
_listener_scan1=192.168.1.245
_protocol=tcp
_port=1521
_node=$(hostname | awk -F "." '{print $1}')
sqlplus -s  / as sysdba <<EOF
set pagesize 200;
set linesize 300;
col username for a25;
col account_status for a25;

select username,account_status from dba_users where account_status='OPEN';

--@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlpwdmg.sql;
col profile for a30;
select * from dba_profiles where resource_type='PASSWORD';

col current_instance_name for a30;
col host_name for a30;
col startup_t for a20;
select instance_name current_instance_name
 ,host_name
 ,startup_time
 ,status
 ,database_status
 from v\$instance;

select name,log_mode,open_mode from v\$database;

col host for a30
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;

col all_datafiles for a75;
select name all_datafiles from v\$datafile
   union
   select member from v\$logfile
   union
   select name from v\$controlfile
   union
   select name from v\$tempfile;

col all_asm_disks_path for a50
select path all_asm_disks_path
   from v\$asm_disk
   where group_number in (
   select group_number from v\$asm_diskgroup
   where name = 'ORAFLUSH');

col freespace_name for a35;
select tablespace_name freespace_name
 ,count(*) chunks_BLOCKS
 ,max(bytes/1024/1024) max_chunk_MB
 from dba_free_space
 group by tablespace_name;

--check tablespace room
select tablespace_name,sum(bytes)/1024/1024/1024 space_gb from dba_data_files group by tablespace_name;

--check redo log files;
select group# redo_log_group#
 ,status
 ,sequence#
 ,archived
 from v\$log;

--the times log swith
col day for a15
col h00 for 99
col h01 for 99
col h02 for 99
col h03 for 99
col h04 for 99
col h05 for 99
col h06 for 99
col h07 for 99
col h08 for 99
col h09 for 99
col h10 for 99
col h11 for 99
col h12 for 99
col h13 for 99
col h14 for 99
col h15 for 99
col h16 for 99
col h17 for 99
col h18 for 99
col h19 for 99
col h20 for 99
col h21 for 99
col h22 for 99
col h23 for 99
SELECT to_char(first_time, 'yyyy-mm-dd') DAY,
       COUNT(*) switch_times,
       SUM(decode(to_char(first_time, 'hh24'), '00', 1, 0)) h00,
       SUM(decode(to_char(first_time, 'hh24'), '01', 1, 0)) h01,
       SUM(decode(to_char(first_time, 'hh24'), '02', 1, 0)) h02,
       SUM(decode(to_char(first_time, 'hh24'), '03', 1, 0)) h03,
       SUM(decode(to_char(first_time, 'hh24'), '04', 1, 0)) h04,
       SUM(decode(to_char(first_time, 'hh24'), '05', 1, 0)) h05,
       SUM(decode(to_char(first_time, 'hh24'), '06', 1, 0)) h06,
       SUM(decode(to_char(first_time, 'hh24'), '07', 1, 0)) h07,
       SUM(decode(to_char(first_time, 'hh24'), '08', 1, 0)) h08,
       SUM(decode(to_char(first_time, 'hh24'), '09', 1, 0)) h09,
       SUM(decode(to_char(first_time, 'hh24'), '10', 1, 0)) h10,
       SUM(decode(to_char(first_time, 'hh24'), '11', 1, 0)) h11,
       SUM(decode(to_char(first_time, 'hh24'), '12', 1, 0)) h12,
       SUM(decode(to_char(first_time, 'hh24'), '13', 1, 0)) h13,
       SUM(decode(to_char(first_time, 'hh24'), '14', 1, 0)) h14,
       SUM(decode(to_char(first_time, 'hh24'), '15', 1, 0)) h15,
       SUM(decode(to_char(first_time, 'hh24'), '16', 1, 0)) h16,
       SUM(decode(to_char(first_time, 'hh24'), '17', 1, 0)) h17,
       SUM(decode(to_char(first_time, 'hh24'), '18', 1, 0)) h18,
       SUM(decode(to_char(first_time, 'hh24'), '19', 1, 0)) h19,
       SUM(decode(to_char(first_time, 'hh24'), '20', 1, 0)) h20,
       SUM(decode(to_char(first_time, 'hh24'), '21', 1, 0)) h21,
       SUM(decode(to_char(first_time, 'hh24'), '22', 1, 0)) h22,
       SUM(decode(to_char(first_time, 'hh24'), '23', 1, 0)) h23
  FROM v\$log_history
 WHERE first_time > trunc(SYSDATE - 30)
 GROUP BY ROLLUP(to_char(first_time, 'yyyy-mm-dd'));

 

 

 

col username for a20;
col machine for a25;
select sid current_session_case_sid,
    serial#,
 username,
 program,
 machine,
 status
  from v\$session;

--backup control
alter database backup controlfile to '$_control_file_bak';

 

--check the status of datafiles
col data_file_name for a70;
select file_name data_file_name,status from dba_data_files;

--check oracle job
--select job,log_user,last_date,failures from dba_jobs;

--check alert log file path
--col value for a70
--select * from v\$diag_info;

 

--debug scan-ip,modify the scan ip
alter system set local_listener='(description=(address_list=(address=(protocol=$_protocol)(host=$_listener_scan1)(port=$_port))))' scope=both sid=$_node;

EOF