Category: Linux

Linux configurations for Oracle

How to add a port to a service in firewalld

This post shows how to add port 5000 into filrewalld for Oracle Autonomous Health Framework (AHF) running on GI/RAC.

  1. List existing services:
    # firewall-cmd –get-services
  2. Add the port: If the service you want to modify already exists, use the following command to add the port: 

    # firewall-cmd –permanent –add-port=<port>/<protocol> –service=<service-name>
    eg.

    #firewall-cmd –permanent –add-port=5000/tcp –service=oracle_rac
  3. Reload firewalld: After making changes, reload firewalld to apply the new configuration:

    # firewall-cmd –reload
  4. Check the port added into service:

    # firewall-cmd –info-service=oracle_rac
    oracle_rac
    ports: 1521/tcp 5000/tcp
    protocols:
    source-ports:
    modules:
    destination:
    includes:
    helpers:

How to find the PID of the process using a specific port?

# lsof -i :9933
COMMAND     PID   USER   FD   TYPE     DEVICE SIZE/OFF NODE NAME
oracle_51 51639 oracle    6u  IPv4 3233899771      0t0  UDP localhost.localdomain:9933
# netstat -nlp|grep 9933
udp   1053952      0 127.0.0.1:9933          0.0.0.0:*                           51639/oracleTESTDB
# fuser -v -n udp 9933
                     USER        PID ACCESS COMMAND
9933/udp:            oracle    51639 F.... oracle_51639_TESTDB

Check Session id(SID) and SQL statement from OS process id(PID) in Oracle

SELECT b.spid,
a.sid,
a.serial#,
a.username,
a.osuser
FROM v$session a, v$process b
WHERE a.paddr = b.addr AND b.spid = '&spid'
ORDER BY b.spid

-- Check SQL statement associated with Process id in Oracle

SELECT RPAD('USERNAME : ' || s.username, 80) ||
RPAD('OSUSER : ' || s.osuser, 80) ||
RPAD('PROGRAM : ' || s.program, 80) ||
RPAD('SPID : ' || p.spid, 80) ||
RPAD('SID : ' || s.sid, 80) ||
RPAD('SERIAL# : ' || s.serial#, 80) ||
RPAD('MACHINE : ' || s.machine, 80) ||
RPAD('TERMINAL : ' || s.terminal, 80)
--RPAD('SQL TEXT : ' || q.sql_text, 80)
FROM v$session s ,v$process p ,v$sql q
WHERE s.paddr = p.addr AND s.sql_address = q.address AND s.sql_hash_value = q.hash_value
AND p.spid = '&spid'

How to Delete OS Audit Files in Oracle

When manually deleting OS audit files in Oracle, the following error occurs:

$ cd $ORCALE_HOME/rdbms

$ du -sh ./audit
38G     ./audit

$ cd audit
$ rm *.aud
-bash: /bin/rm: Argument list too long

SOLUTION

$ find ./ -name "*.aud" -mtime +30 -exec rm {} \;

OR

$ find ./ -name "*.aud"  -exec rm {} \;

timed out waiting for input: auto-logout

The connections to Linux server got disconnected with message “timed out waiting for input: auto-logout”.

It is an unhappy thing when you are run some kind of long running scripts or database SQLs.

The issue is coming from the variable ‘TMOUT’ in Linux environment.

$  cat /etc/profile.d/autologout.sh
export TMOUT=36000

$ grep TMOUT /etc/profile
TMOUT=14400

$ echo $SHELL
/bin/bash


$ cat /etc/bashrc|grep  TMOUT
TMOUT=3600

If you want to disable timeout, then reset variable TMOUT=0

$ export TMOUT=0