How to check connection distributed among RAC Nodes

Below is the queries that can help in identifying the connections that are distributed among RAC nodes.

SELECT inst_id, count(*) "Database Sessions" FROM gv$session
 WHERE type = 'USER' GROUP BY inst_id;

If you need to check the output with the log-in time then you can use below query.

With log-in time (hour):

SELECT inst_id, TO_CHAR(logon_time, 'DD-MON-YYYY HH24') "Connection Time(In Hours)", count(*) "Database Sessions"
  FROM gv$session
 WHERE type = 'USER'
 GROUP BY inst_id, TO_CHAR(logon_time, 'DD-MON-YYYY HH24')
 ORDER BY inst_id, TO_CHAR(logon_time, 'DD-MON-YYYY HH24');

Message

Hello Friends,

I was busy with some personal work and so I was not able to keep track and provide an update on this blog. I am back and will keep posting on technical topics related to Oracle Database.

Please keep providing your feedback's on the topics so that we can know how we can serve you better.

Regards,
ingress

Message

Hello Friends,

I was busy with some personal work and so I was not able to keep track and provide an update on this blog. I am back and will keep posting on technical topics related to Oracle Database.

Please keep providing your feedback's on the topics so that we can know how we can serve you better.

Regards,
Bhavik

How to Turn ON Logging at Oracle Listner

Turn on logging for all listeners in order to capture Listener commands and brute force password attacks.

LSNRCTL> set current_listener

LSNRCTL> set password Password: listener>

LSNRCTL> set log_directory /network/admin

LSNRCTL> set log_file .log

LSNRCTL> set log_status on

LSNRCTL> save_config

This will help in identifying the Listener Commands and if there is any Brute force attack to the system.

How to Block Unwanted Connection @ Oracle Net Manager

It is completely depending on the type of application and network configuration you have in your environment, Node validation can be a powerful tool to restrict most traffic from the Listener. Currently I am supporting web applications database and I found that only web application require access to the Listener from the application servers and a limited number of clients for administration. The simplest method to determine valid IP addresses for checking nodes is through database auditing.

As I read in some notes, it is always recommended that we always have session level auditing enabled.

In Oracle 9i/10g Database the valid node checking lines are added to the $ORACLE_HOME/network/admin/sqlnet.ora file.

tcp.validnode_checking = yes

tcp.invited_nodes = (x.x.x.x | name, x.x.x.x | name)

tcp.excluded_nodes=( x.x.x.x | name, x.x.x.x | name)

Any one option can be included i.e. either the invited_nodes or excluded_nodes and not both. Only individual IP addresses or host names are allowed.

For more details I would request you to read Oracle Connection Manager reference material at Oracle Docs.

Thanks,

ingress

How to Block Unwanted Connection @ Oracle Net Manager

It is completely depending on the type of application and network configuration you have in your environment, Node validation can be a powerful tool to restrict most traffic from the Listener. Currently I am supporting web applications database and I found that only web application require access to the Listener from the application servers and a limited number of clients for administration. The simplest method to determine valid IP addresses for checking nodes is through database auditing.

As I read in some notes, it is always recommended that we always have session level auditing enabled.

In Oracle 9i/10g Database the valid node checking lines are added to the $ORACLE_HOME/network/admin/sqlnet.ora file.

tcp.validnode_checking = yes

tcp.invited_nodes = (x.x.x.x | name, x.x.x.x | name)

tcp.excluded_nodes=( x.x.x.x | name, x.x.x.x | name)

Any one option can be included i.e. either the invited_nodes or excluded_nodes and not both. Only individual IP addresses or host names are allowed.

For more details I would request you to read Oracle Connection Manager reference material at Oracle Docs.

Thanks,

Bhavik