Monday, 25 February 2019

email alert configure in Linux/UNIX/Solaris/Windows server for Database health monitoring

The following process we configure for email alert to check Database Health

Step 1. Configure SMTP client from where we will send mail

[oracle@said dbalert]$ cat /etc/mail.rc
# This is the configuration file for Heirloom mailx (formerly
# known under the name "nail".
# See mailx(1) for further options.
# This file is not overwritten when 'make install' is run in
# the mailx build process again.

# Sccsid @(#)nail.rc 2.10 (gritter) 3/4/06

# Do not forward to mbox by default since this is likely to be
# irritating for most users today.
set hold

set smtp=smtp://10.11.1.204          ----smtp server information
set from="db.alert@saidrase.info"  ----email account from where mail send

 Step 2. configure a shell script into smtp client server from where mail send to destination mail address


#!/bin/bash
NOW=$(date +"%M-%H-%m-%d-%Y")
MAIL_TO="saidrase@yahoo.com","rasel.said@gmail.com"
ssh oracle@10.88.1.112 /export/home/oracle/dbalert/dbinfo_mis.sh
scp oracle@10.88.1.112:/export/home/oracle/dbalert/MIS_Database_Info.html /u01/dbalert/
ssh oracle@10.88.1.112 rm -f /export/home/oracle/dbalert/MIS_Database_Info.html
##ssh oracle@10.88.1.112 df -h >>/u01/dbalert/Database_Info.html
##ssh oracle@10.88.1.112 tail -30 /d01/app/oracle/diag/rdbms/bacdbpr/bacdb/trace/alert_bacdb.log >>/u01/dbalert/Database_Info.html
echo "

File System Information    

"  >> /u01/dbalert/MIS_Database_Info.html
echo "" >> /u01/dbalert/Database_Info.html
echo "

Alert Log Information    

"  >> /u01/dbalert/MIS_Database_Info.html
echo "" >> /u01/dbalert/MIS_Database_Info.html
echo "10.88.1.112 Server Health Check Information. Please Download the attached file" | mail -v  -a /u01/dbalert/MIS_Database_Info.html -s "MIS Database Server Health Check" $MAIL_TO
rm /u01/dbalert/alert_mis.txt /u01/dbalert/mis_fs.txt
mv /u01/dbalert/MIS_Database_Info.html /u01/dbalert/MIS_Database_Info_$NOW.html
exit


Step 3. Prepare a shell script to connect Database into monitoring Database server

#!/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
PATH=$ORACLE_HOME/bin:$HOME/bin:/usr/bin/X11:/usr/bin/X11R6:/usr/local/sbin:/sbin:/bin:/emc/EMCpower/reloc/EMCpower/bin/sparcv9:/etc/emc/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/openwin/bin:.; export PATH
ORACLE_PATH=$ORACLE_HOME/bin:.; export ORACLE_PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
LD_LIBRARY_PATH_64=$ORACLE_HOME/lib; export LD_LIBRARY_PATH_64
CLASSPATH=$CLASSPATH:$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib; export CLASSPATH
ORACLE_SID=orcl; export ORACLE_SID
sqlplus  -s "/ as sysdba" << EOF
set trimspool on
SET LINE 1000
SET ECHO OFF
SET VERIFY OFF
SET TERMOUT OFF
SET HEADING OFF
SET FEEDBACK OFF
SET wrap off
SET MARKUP HTML ON
column TABLESPACE_NAME for a30
spool on
spool /home/oracle/dbalert/MIS_Database_Info.html
@/home/oracle/dbalert/version_MIS.sql
spool off
exit;
EOF
exit;

Step 4. Prepare a sql script to collect data from Database server 

SET MARKUP HTML ON

SET MARKUP HTML OFF

conn c##monitor/""@dbname

select '

' || 'MIS Database Server Healthcheck Information (10.88.1.112)' || '' || '' || '

' from dual;

select  '

' || 'Tablespace Information used more than 85% in MISDB PDB' || '' || '' || '' || '

' from dual;
conn c##monitor/""@dbname

SET MARKUP HTML ON
SET PAGES 800
SET HEADING ON

select a.TABLESPACE_NAME,a.d "No of DataFile",round(a.BYTES) "TOTAL_SIZE_(M)",a.BYTES-b.BYTES "USED (M)",round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) "%USED"
from
(
  select TABLESPACE_NAME,count(file_name) D,sum(BYTES)/1048576 BYTES
  from dba_data_files
  group by TABLESPACE_NAME
) a,
(
  select TABLESPACE_NAME,sum(BYTES)/1048576 BYTES, max(BYTES)/1048576 largest
  from dba_free_space
  group by TABLESPACE_NAME
)b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.TABLESPACE_NAME not like '%UNDO%'
and round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) > 85
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;

set heading on timing off

SET HEADING OFF
SET MARKUP HTML OFF

select  '

' || 'Log Switch Information' || '' || '' || '' || '

' from dual;
SET MARKUP HTML ON

SET HEADING ON
col day for a11
SELECT to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'9999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'9999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'9999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'9999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'9999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'9999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'9999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'9999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'9999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'9999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'9999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'9999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'9999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'9999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'9999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'9999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'9999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'9999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'9999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'9999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'9999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'9999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'9999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'9999') "23"
,sum(1) "TOTAL_IN_DAY"
from v$log_history where first_time > sysdate-5
GROUP by to_char(first_time,'YYYY-MON-DD') order by 1 asc;

SET MARKUP HTML OFF
SET HEADING OFF

SET HEADING OFF

SET MARKUP HTML OFF
select  '

' || 'MISDB Database User Login Since Last 6 Hours' || '' || '' || '' || '

' from dual;
SET MARKUP HTML ON

SET HEADING ON
col Logon_Time for a22
col machine for a25

Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss') Logon_Time,osuser,status,USERNAME,schemaname,machine
from v$session
where type !='BACKGROUND'
AND osuser not in ('root','oracle','grid','WIN-MQ01PK54063$','BSAPP','OracleHomeUser')
and trunc(logon_time)=trunc(sysdate-1/4);

SET MARKUP HTML OFF
SET HEADING OFF

SET MARKUP HTML OFF
select  '

' || 'MISDB Database Invalid Object Information' || '' || '' || '' || '

' from dual;
SET MARKUP HTML ON

SET HEADING ON
col owner for a22


SELECT owner,count(*) Total_Invalid_Objects FROM dba_objects where status='INVALID' group by owner;

SET MARKUP HTML OFF
SET HEADING OFF

SET MARKUP HTML OFF
select  '

' || 'MISDB Database FRA Information' || '' || '' || '' || '

' from dual;
SET MARKUP HTML ON

SET HEADING ON
col file_type for a22

SELECT file_type,percent_space_used FROM v$flash_recovery_area_usage;

SET MARKUP HTML OFF
SET HEADING OFF

SET MARKUP HTML OFF
select  '

' || 'MISDB Database UpTime Information' || '' || '' || '' || '

' from dual;
SET MARKUP HTML ON

SET HEADING ON
col InsName for a22

SELECT INSTANCE_NAME InsName,to_char(STARTUP_TIME,'DD-MON-YY hh24:mi:ss')  StartupTime FROM  v$instance ;

SET MARKUP HTML OFF
SET HEADING OFF

Posted by at No comments:
Labels: , ,

Saturday, 23 February 2019

How to check Manufacturer information in Linux

Option 1#
[root@said-db-serv ~]# dmidecode -t system
# dmidecode 3.0
Getting SMBIOS data from sysfs.
SMBIOS 3.0.0 present.

Handle 0x0100, DMI type 1, 27 bytes
System Information
Manufacturer: Dell Inc.
Product Name: PowerEdge R440
Version: Not Specified
Serial Number: 1XZ19R2
UUID: 4C4C4544-0058-5A10-8031-B1C04F395232
Wake-up Type: Power Switch
SKU Number: SKU=NotProvided;ModelName=PowerEdge R440
Family: PowerEdge

Handle 0x0C00, DMI type 12, 5 bytes
System Configuration Options
Option 1: NVRAM_CLR: Clear user settable NVRAM areas and set defaults
Option 2: PWRD_EN: Close to enable password

Handle 0x2000, DMI type 32, 11 bytes
System Boot Information
Status: No errors detected

[root@said-db-serv ~]#

Option 2#

[root@said-db-serv ~]# lshw -C system
utility-db-serv.bankasia-bd.com
    description: Rack Mount Chassis
    product: PowerEdge R440 (SKU=NotProvided;ModelName=PowerEdge R440)
    vendor: Dell Inc.
    serial: 1XZ19R2
    width: 64 bits
    capabilities: smbios-3.0.0 dmi-3.0.0 smp vsyscall32
    configuration: boot=normal chassis=rackmount family=PowerEdge sku=SKU=NotProvided;ModelName=PowerEdge R440 uuid=44454C4C-5800-105A-8031-B1C04F395232
  *-pnp00:00
       product: PnP device PNP0b00
       physical id: 9c
       capabilities: pnp
       configuration: driver=rtc_cmos
  *-pnp00:01
       product: PnP device PNP0c02
       physical id: 9d
       capabilities: pnp
       configuration: driver=system
  *-pnp00:05
       product: PnP device PNP0c02
       physical id: a1
       capabilities: pnp
       configuration: driver=system
  *-pnp00:06
       product: PnP device PNP0c02
       physical id: a2
       capabilities: pnp
       configuration: driver=system
[root@said-db-serv ~]# 
Posted by at No comments:
Labels: ,

Monday, 18 February 2019

step by step password less SSH configuration in Linux



Source IP: 10.11.1.109
Destination: 10.11.1.98

Step 1. Login into 10.11.1.109 server

 Create Authentication SSH-Kegen Keys on

[oracle@Said-Server ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/oracle/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
84:2b:b3:20:79:ec:c2:65:0a:0d:b5:34:c3:33:d7:ff oracle@Said-Server
The key's randomart image is:
+--[ RSA 2048]----+
| .=  .           |
| o++. ..         |
|. .+  ...        |
| =     o.        |
|+ =oo . S.       |
|o++. +    E      |
|.o. .            |
| .               |
|                 |
+-----------------+
[oracle@Said-Server ~]$

Step 2: Create .ssh directory in 10.11.1.98 server

 [oracle@Said-Server ~]$ ssh oracle@10.11.1.98 mkdir -p .ssh
oracle@10.11.1.98's password:
[oracle@Said-Server ~]$

Step 3: Upload Generated public key to 10.11.1.98 server

[oracle@Said-Server ~]$ cat .ssh/id_rsa.pub | ssh oracle@10.11.1.98 'cat >> .ssh/authorized_keys'
oracle@10.11.1.98's password:
[oracle@Said-Server ~]$

Step 4: set permission on 10.11.1.98 server

[oracle@Said-Server ~]$ ssh oracle@10.11.1.98 "chmod 700 .ssh; chmod 640 .ssh/authorized_keys"
[oracle@Said-Server ~]$

Step 5: Login from 10.11.1.106 to 10.11.1.98 server without password

[oracle@Said-Server ~]$ ssh oracle@10.11.1.98
Last login: Tue Feb 19 09:33:29 2019 from 10.11.208.115
[oracle@ncbsstd ~]$

Posted by at No comments:
Labels: , , ,
Subscribe to: Posts (Atom)