Avatar of bibi92
bibi92Flag for France asked on

get oracle database port

Hello,

I try to get oracle database port, the below script works if there is only one database by server.
How can I modify it for several databases :
#!/bin/sh

export ORAENV_ASK=NO
for BASE in $(ps -ef | grep pmon | grep -v "grep" | grep -v "awk" | grep -v "ASM" | awk -F "pmon_"  '{print$2} ')
do
export ORACLE_SID=$BASE
. oraenv $BASE


 ps -eaf | grep -v -E "grep|ps" | grep -i "tnslsnr" >/dev/null 2>&1
        if [ 0 -eq $? ]
        then
                TNSLSNRPID=$(ps -eaf | grep -v -E "grep|ps" | grep -i "tnslsnr" |\
                awk '{
                        print $2;
                }')
                export PORT_NAME=$(lsof -p $TNSLSNRPID 2>&1 |\
                awk '{
                        if ( "(LISTEN)" == $NF )
                        {
                                printf( "%s\n", substr( $(NF-1), 3) );
                        }
                }')
                port=$(awk '{
                        if ( index( $0, ENVIRON["PORT_NAME"]) )
                        {
                                if ( index( $0, "/tcp") )
                                {
                                        printf( "%s\n", substr( $2, 0, index( $2, "/")-1) );
                                }
                        }
                }' /etc/services)
              
        else
                ps -eaf | grep -v -E "grep|ps" | grep -i "tnslsnr"
fi				
${ORACLE_HOME}/bin/sqlplus -s / as sysdba << !
set pagesize 0
set echo off
set feed off
set LINES 2000
DECLARE
DELIMITER   VARCHAR2(1);
V_ORACLE_SID VARCHAR2 (256);
V_ORACLE_HOME VARCHAR2 (2000);
V_SPFILE VARCHAR2 (1000);
V_IP VARCHAR2 (30);
V_HOST VARCHAR2 (230);
BEGIN
DELIMITER := '/';
DBMS_SYSTEM.GET_ENV ('ORACLE_SID', V_ORACLE_SID );
DBMS_OUTPUT.PUT_LINE (V_ORACLE_SID||';'||V_IP||';'||'${port}');
END;
/
!

done
	

Open in new window


Thank you

Best regards
Shell ScriptingOracle Database

Avatar of undefined
Last Comment
johnsone

8/22/2022 - Mon
johnsone

Typically, all databases use the same port for the listener.  In my experience, it is pretty rare to be using more than one listener.
Alex [***Alex140181***]

I totally agree with johnsone! Whereas it is possible to run multiple Listeners....
ASKER
bibi92

Yes there are several listeners on servers thank you
Your help has saved me hundreds of hours of internet surfing.
fblack61
David Favor

This depends on your OS.

Here's a Ubuntu example looking for all IPs + Ports where dnsmasq is listening...

net16 # netstat -pluten | grep dnsmasq
tcp        0      0 10.101.55.1:53          0.0.0.0:*               LISTEN      0          478044577  832971/dnsmasq      
tcp        0      0 127.0.0.53:53           0.0.0.0:*               LISTEN      0          21431      1192/dnsmasq        
tcp        0      0 127.0.0.1:53            0.0.0.0:*               LISTEN      0          21427      1192/dnsmasq        
tcp6       0      0 fd42:a94f:9bb3:bfd6::53 :::*                    LISTEN      0          478044581  832971/dnsmasq      
tcp6       0      0 fe80::1c:5bff:feeb:c:53 :::*                    LISTEN      0          478044579  832971/dnsmasq      
tcp6       0      0 ::1:53                  :::*                    LISTEN      0          21429      1192/dnsmasq        
udp        0      0 10.101.55.1:53          0.0.0.0:*                           0          478044576  832971/dnsmasq      
udp        0      0 127.0.0.53:53           0.0.0.0:*                           0          21430      1192/dnsmasq        
udp        0      0 127.0.0.1:53            0.0.0.0:*                           0          21426      1192/dnsmasq        
udp        0      0 0.0.0.0:67              0.0.0.0:*                           0          478044571  832971/dnsmasq      
udp6       0      0 fd42:a94f:9bb3:bfd6::53 :::*                                0          478044580  832971/dnsmasq      
udp6       0      0 fe80::1c:5bff:feeb:c:53 :::*                                0          478044578  832971/dnsmasq      
udp6       0      0 ::1:53                  :::*                                0          21428      1192/dnsmasq        
udp6       0      0 :::547                  :::*                                0          478044573  832971/dnsmasq

Open in new window

ASKER
bibi92

Thank you the goal is to get oracle database port and by listener.

regards
Alex [***Alex140181***]

There is no "oracle database port"!

There is one Listener running on the server, which establishes the connections to the database... In rare cases, there may be more than 1 of those running at the same time on the same server....
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
johnsone

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
bibi92

yes sure it's translation issue the goal is to get one or several listener by server thank you
ASKER
bibi92

several listeners and several databases thank you
johnsone

Good luck.  Multiple listeners is a maintenance nightmare.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy