Extract client IP Adresses from the listener.log file.

The listener log file is a simple text file, so searching for specific information inside is easy; however, in its raw
form, it’s difficult to extract collated information.

The simple and best way to do that is the widely used and humble linux commands: grep, awk, uniq, sort, wc etc.

If you are not sure, you can find the location of the listener log file by using the listener control utility:

[oracle@testoradb diag]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 19-JAN-2012 07:55:07

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 22-AUG-2011 01:03:21
Uptime 150 days 7 hr. 51 min. 46 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/apps/oracle/testoradb/product/11.2.0/dbhome/network/admin/listener.ora
Listener Log File /u01/apps/oracle/testoradb/diag/tnslsnr/listener/alert/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testoradb)(PORT=1521)))
Services Summary...
Service "ORA_TEST" has 1 instance(s).
Instance "ORA_TEST", status READY, has 1 handler(s) for this service...
Service "testoraXDB" has 1 instance(s).
Instance "ORA_TEST", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@testoradb diag]$

Note the line that shows “Listener Log File,” which shows the directory of the listener log file.

[oracle@testoradb diag]$ cat listener.log | less
Mon Jul 18 02:56:26 2011
18-JUL-2011 02:56:26 * (CONNECT_DATA=(SERVICE_NAME=ORA_TEST)(CID=(PROGRAM=xHSSrv)(HOST=TEST_APP)(USER=apple))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.10)(PORT=44726)) *

establish * ORA_TEST * 0

The field Protocol Information has the following subfields:
PROTOCOL — the protocol that the client has used to connect, such as TCP.
HOST — the IP address of the client machine.
PORT — the port number established by the listener. (Note: It’s not the port number to which the listener is
listening, so this is not especially interesting to us.)

We can further narrow down on the IP addresses with the # of times they connected to the database on 18-Jan-2011 by:

[oracle@testoradb diag]$ cat listener.log | grep 18-JAN | grep CONNECT | awk -F* '{print $3}' | grep -o "192.*)" | grep -v 192.168.100.99 | awk -FPORT '{print $1}' | sort | uniq -c
5 192.168.100.32)(
255 192.168.100.33)(
19 192.168.100.34)(
60 192.168.100.56)(
11 192.168.100.58)(
1 192.168.100.62)(
6 192.168.100.71)(
9 192.168.100.163)(
1 192.168.100.164)(
12 192.168.100.165)(
5 192.168.100.166)(
2 192.168.100.167)(
2 192.168.100.169)(
[oracle@testoradb diag]$

It's rough but the info is here. After extracting the client IPs from the listener log file we then exclude the monitoring system's IP (192.168.100.99) from the list.

Cheers !
Harish.

Securing Oracle Database server using IPTables in Linux

Linux can help administrators create a strong firewall with the powerful, kernel-based netfilter/iptables software. As demonstrated below, iptables can create general or specific packet filters to allow or deny traffic. This enables administrators to protect their servers from a wide variety of hazards, including service attacks and hack attempts. As always, the best way to learn is to get your hands dirty and experiment with iptables on a testing machine.

This article is an example of how you cam secure a Oracle Database server using IPTables in Linux.

Edit the iptables file from the /etc/sysconfig directory:

[root]# vi /etc/sysconfig/iptables

#Nagios Server for real time alerts : 192.168.0.99
#Zabbix Server for historic perf. data : 192.168.0.98
#Trusted VLAN for SSH and SFTP traffic : 192.168.4.0
#Trusted IP's from untrusted VLAN : 192.168.16.xx

# Rule to enable PING from selected IP's
-A INPUT -p tcp -s 192.168.0.99 -j ACCEPT
-A INPUT -p tcp -s 192.168.0.98 -j ACCEPT

# Rule to enable monitoring from selected IP's
-A INPUT -m state --state NEW -m tcp -s 192.168.0.99 -p tcp --dport 5666 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -s 192.168.0.98 -p tcp --dport 10050 -j ACCEPT

# Rule to enable SSH / SFTP from Trusted VLAN
-A INPUT -m state --state NEW -m tcp -s 192.168.4.0/24 -p tcp --dport 20 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -s 192.168.4.0/24 -p tcp --dport 21 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -s 192.168.4.0/24 -p tcp --dport 22 -j ACCEPT

# Rule to enable Oracle port for IP’s of Application VM’s
-A INPUT -m state --state NEW -m tcp -s 192.168.16.20 -p tcp --dport 1521 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -s 192.168.16.21 -p tcp --dport 1521 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -s 192.168.16.22 -p tcp --dport 1521 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -s 192.168.16.23 -p tcp --dport 1521 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -s 192.168.16.24 -p tcp --dport 1521 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -s 192.168.16.25 -p tcp --dport 1521 -j ACCEPT

# Catch All Rule
-A INPUT -m state --state NEW -m tcp -p tcp -j DROP

Restart the iptables service

[root]# service iptables restart

And you are good to go !

Cheers !
Harish.

Step by step guide to install Oracle 11G R2 on x86_64 Linux (CentOS 5.5)

Hardware Requirements:

How to clear NFS locks during network crash or outage for Oracle datafiles.

Symptoms :

Database cannot be opened because old locks still exist on the filer stored data files.

Error : ORA-27086

Cause of this problem :

Data files that were open during network episode left in NFS locked state. Oracle cannot open the locked files

The lock recovery manager (NLM) in the Linux kernel uses uname -n to determine the host name while the rpc.statd process (NSM) uses gethostbyname() to determine the client's name. If these do not match, the recovery process will not work

Solution :

This solution indicates the recovery steps detailed herein should be taken in case Oracle is hung, but that PROPER ORACLE TROUBLESHOOTING AND SUPPORT METHODS SHOULD BE FOLLOWED FOR ANY DATABASE-HUNG ISSUES, independently of NetApp.

Oracle's database product does not typically hang after a network crash.

Summary of Corrective steps :

1) Shutdown Oracle databases

2) Unmount database volumes

3) Kill lockd/statd processes on UNIX host

4) Clear locks on filer

5) Remove the NFS lock files on the host.

6) Restart lockd/statd processes on UNIX host

7) Remount the database volumes on the UNIX host

8) Restart databases

Detailed Procedure:

1) Shutdown all Oracle databases being run by the affected server.

Issue the Oracle shutdown immediate command and verify that no database processes are still running by issuing the UNIX command ps -ef |grep -i ora on the UNIX database host.

If database processes are still running issue the Oracle shutdown abort command and use the UNIX command ps -ef | grep -i ora to verify that no database processes are still running.

If database processes are still running do the following from the UNIX command line:

ps -ef | grep ora to get process id's (pid's) of remaining Oracle processes

kill -9 pid for each remaining Oracle process.

2) Unmount all database volumes using the UNIX umount command.

3) Kill statd and lockd processes on the UNIX host in the order specified below:

Determine the process id's (pid's) of statd and lockd from the UNIX command line:

ps -ef |grep lockd

ps -ef |grep statd

kill [lockd_process_id]

kill [statd_process_id]

4) Remove locks from filer

Execute the following from the filer command line:

filer> priv set advanced

filer> sm_mon -l (In many cases specifying the host name does not clear all the affecting locks, so the recommendation is to NOT specify a hostname)

Delete all files in the filer's "/etc/sm" directory. (Remove the files only. Do NOT remove the "/etc/sm" directory itself.)

If the filer is running Data ONTAP 7.1 or higher run 'lock break -h [hostname]' to release any locks that still exist.

Note:

If the 'lock break -h [hostname]' doesn't work, ensure that the server name that you are entering is not the same as the one that the filer has.

If the locks are not cleared, run 'lock break -p nlm' (This also requires Data ONTAP 7.1 or higher). This will clear all the NFS locks on the filer. This will not sever any NFS connections, it will simply force the processes to re-request the locks for the files they are writing to.

5) Remove the NFS lock files on the host.

From TR-3183 - Using the Linux NFS Client with Network Appliance Storage,

rpc.statd uses gethostbyname() to determine the client's name, but lockd (in the Linux kernel) uses uname -n.

By changing the HOSTNAME= fully qualified domain name, lockd will use an FQDN when contacting the storage. If there is a lnx_node1.iop.eng.netapp.com and also a lnx_node5.ppe.iop.eng.netapp.com contacting the same NetApp storage, the storage will be able to correctly distinguish the locks owned by each client. Therefore, we recommend using the fully qualified name in /etc/sysconfig/network. In addition to this, sm_mon -l or lock break on the storage will also clear the locks on the storage which will fix the lock recovery problem.

Additionally, if the client's nodename is fully qualified (that is, it contains the hostname and the domain name spelled out), then rpc.statd must also use a fully qualified name. Likewise, if the nodename is unqualified, then rpc.statd must use an unqualified name. If the two values do not match, lock recovery will not work. Be sure the result of gethostbyname(3) matches the output of uname -n by adjusting your client's nodename in /etc/hosts, DNS, or your NIS databases.

6) Start the UNIX statd and lockd processes from the UNIX host command line in the order specified below:

/usr/lib/nfs/statd

/usr/lib/nfs/lockd

7) Mount the database volumes on the UNIX host.

8) Start the database(s) and test for availability.