Re: Port Numbers

From: Mikhail Velikikh <mvelikikh_at_gmail.com>
Date: Fri, 3 Apr 2020 08:46:24 +0100
Message-ID: <CALe4Hpm1fpHaH4LJ8k04NactBU4AYSvMq2PzH8fBWZxud91ZxA_at_mail.gmail.com>

Hi folks,

There was the LISTENER_REGISTRATION event that seems to be replaced by the LREG_STATE event since the former does not work anymore in my 19c database. Sample usage:

1. dump lreg_state: SQL> alter session set events 'immediate lreg_state(1)';

Session altered.

2. get the tracefile name:

SQL> select value from v$diag_info where name='Default Trace File';

VALUE

---

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7476.trc

3. query V$DIAG_TRACE_FILE_CONTENTS for the server process' trace file:

SQL> select payload from v$diag_trace_file_contents where trace_filename='orcl_ora_7476.trc';

..skip..
Listener registration dumped to LREG's trace file:   /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lreg_3516.trc ..skip..

4. get the LREG trace file:

SQL> select payload from v$diag_trace_file_contents where trace_filename='orcl_lreg_3516.trc'

---

LREG State Dump Start

---

Last update: 131993 centi seconds (303 seconds ago) Flag: 0x4, 0x0
State: succ=1, wait=0, fail=0

CDB: root pdb 1 last pdb 4098 open max pdb 3

Dispatcher configuration index: cur 1 max 1

Network '-oracledefault-' pdb 1 :

  Local listeners:
* 0 - (ADDRESS=(PROTOCOL=TCP)(HOST=10.0.2.15)(PORT=1521)) pdb 1 dci 0 flg 0xa3*

       firewall=0, publish_service_acl=1
       state=1, err=0
       nse[0]=0, nse[1]=0, nte[0]=0, nte[1]=0, nte[2]=0
       ncre=0

  Remote listeners:

Listeners:

---

LREG State Dump End

---

Obviously, you could get to the LREG trace file directly through V$PROCESS, I was just showing what is written to the server process trace file:

SQL> select tracefile from v$process where pname='LREG';

TRACEFILE

---

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lreg_3516.trc

On Fri, 3 Apr 2020 at 04:49, Vanita Sharma Tyagi <dba.vanitasharma_at_gmail.com> wrote:

> Hi Martin

>

> Is there any x$ view where oracle store listener port number , I know we
> can check in listener file / lsnrctl status etc but we don't have access to
> host .. I just have access via remote sqlplus connection.
> Local_listener parameter is blank for some database , on few we have named
> listener or full address list .
> So wanted to know if oracle store port anywhere in x$ what I can query .

>

> On Fri, Apr 3, 2020, 01:09 Martin Berger <martin.a.berger_at_gmail.com>
> wrote:

>
>> Sumit,
>>
>> can you give some examples what you are searching for?
>> The most common connection - SQL*NET - is established by the listener.
>> The instance doesn't care a lot about it.
>>
>> What do you want to achieve?
>>
>> Martin
>>
>> Am Do., 2. Apr. 2020 um 08:28 Uhr schrieb Sumit Kumar T <
>> dba.tyagisumit_at_gmail.com>:
>>
>>> Hi Experts
>>>
>>> Is there any view / x$ view which can tell us all the port numbers
>>> configured for different database components . Or can we fetch from any OEM
>>> management views ?
>>>
>>> Br
>>> Sumit
>>>
>>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Apr 03 2020 - 09:46:24 CEST