local listener regiester pdb监听的设置

最新推荐文章于 2023-12-22 10:09:23 发布

原创最新推荐文章于 2023-12-22 10:09:23 发布 · 244 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#oracle

GOAL

How to connect to PDB directly using connect string

SOLUTION

Make sure below are done.

a) The pdb's are open
b) The "local_listener" in the PDB points to either a fully specified host/port or if its lists a tnsnames entry, that the tnsnames entry is correct.
c) Run 'alter system register' in the pdb to have it re-establish connection with the listener.
d) If TNS_ADMIN is set, make sure it is correctly set to pointing to correct listener.ora or correct ORACLE HOME.

Example of the setup

Check, if PDB's are open

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
SQL>

Open PDB Database

SQL> alter pluggable database pdb1 open read only;

Pluggable database altered.

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL>

Verify if PDB's are open

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ ONLY NO
4 PDB2 READ WRITE NO
SQL>

Verify LOCAL_LISTENER parameter setup

SQL> show parameter local_listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string
SQL>

Verify listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

Note: Check if you are able to ping hostname

Setup LOCAL_LISTENER

SQL> Alter system set LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))' scope=both;

SQL> alter system register;

System altered.

SQL>

Setup TNS ENTRY in the tnsnames.ora

pdb1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1)
)
)

pdb2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb2)
)
)

Startup Listener

C:\windows\system32>lsnrctl start

LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 04-JUL-2022 11:01:30

Starting tnslsnr: please wait...

TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
System parameter file is D:\OracleDatabase\WINDOWS.X64_193000_db_home\network\admin\listener.ora
Log messages written to D:\OracleDatabase\oracle19c\diag\tnslsnr\PTHIRUVE-7420\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1539)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

C:\windows\system32>

Check if the pdb services are listed in the lsnrctl status command output.

C:\windows\system32>lsnrctl stat

LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 04-JUL-2022 11:02:41

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1539)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date 04-JUL-2022 11:01:37
Uptime 0 days 0 hr. 1 min. 8 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\OracleDatabase\WINDOWS.X64_193000_db_home\network\admin\listener.ora
Listener Log File D:\OracleDatabase\oracle19c\diag\tnslsnr\PTHIRUVE-7420\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1539)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=PTHIRUVE-7420.in.oracle.com)(PORT=5500))(Security=(my_wallet_directory=D:\ORACLEDATABASE\ORACLE19C\admin\orcl2\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "52448234712340b69f274bcc790ecfe0" has 2 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "700dbee1ddff4741a6114a788ccf7a80" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ORCL19CW" has 1 instance(s).
Instance "ORCL19CW", status UNKNOWN, has 1 handler(s) for this service...
Service "a4cf841224a54af3b5b89ca556da465b" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl1" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orcl1.in.oracle.com" has 1 instance(s).
Instance "orcl1.in.oracle.com", status UNKNOWN, has 3 handler(s) for this service...
Service "orcl1XDB" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orcl2" has 2 instance(s).
Instance "orcl2", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "orcl2XDB" has 1 instance(s).
Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s). <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< PDB1 service is listed
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s). <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< PDB2 service is listed
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "us7v19c" has 1 instance(s).
Instance "us7v19c", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

C:\windows\system32>

Also, you could check the same in the lsnrctl services.

C:\windows\system32>lsnrctl services

LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 04-JUL-2022 11:04:19

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1539)))
Services Summary...
Service "52448234712340b69f274bcc790ecfe0" has 2 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Instance "orcl2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "700dbee1ddff4741a6114a788ccf7a80" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "ORCL19CW" has 1 instance(s).
Instance "ORCL19CW", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "a4cf841224a54af3b5b89ca556da465b" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "orcl1" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "orcl1.in.oracle.com" has 1 instance(s).
Instance "orcl1.in.oracle.com", status UNKNOWN, has 3 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
"DEDICATED" established:0 refused:0
LOCAL SERVER
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "orcl1XDB" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: PTHIRUVE-7420, pid: 19856>
(ADDRESS=(PROTOCOL=tcp)(HOST=PTHIRUVE-7420.in.oracle.com)(PORT=49856))
Service "orcl2" has 2 instance(s).
Instance "orcl2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "orcl2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "orcl2XDB" has 1 instance(s).
Instance "orcl2", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: PTHIRUVE-7420, pid: 20212>
(ADDRESS=(PROTOCOL=tcp)(HOST=PTHIRUVE-7420.in.oracle.com)(PORT=49898))
Service "pdb1" has 1 instance(s). <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< PDB1 service is listed
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "pdb2" has 1 instance(s). <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< PDB2 service is listed
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "us7v19c" has 1 instance(s).
Instance "us7v19c", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully

C:\windows\system32>

Check tnsping to pdb1

C:\windows\system32>tnsping pdb1

TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 04-JUL-2022 10:42:31

Used parameter files:
D:\OracleDatabase\WINDOWS.X64_193000_db_home\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1)))
OK (10 msec)

Check tnsping to pdb2

C:\windows\system32>tnsping pdb2

TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 04-JUL-2022 10:42:33

Used parameter files:
D:\OracleDatabase\WINDOWS.X64_193000_db_home\network\admin\sqlnet.ora

C:\windows\system32>

Connect and Verify PDB Connections

C:\windows\system32>sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 4 10:42:53 2022
Version 19.3.0.0.0

Enter user-name: sys@pdb1 as sysdba <<<<<<<<<<<<<<<<<<<<<<<<<<< pdb1 connect string is used to connect to pdb1
Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show con_name

CON_NAME
------------------------------
PDB1 <<<<<<<<<<<<<<<<<<<<<<<<<<< Connected to pdb1
SQL>
SQL> show parameter instance_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string orcl1
SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl1
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ ONLY NO
SQL>

SQL> conn sys@pdb2 as sysdba <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< pdb2 connect string is used to connect to pdb2
Enter password:
Connected.
SQL> show con_name

CON_NAME
------------------------------
PDB2 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Connected to pdb2
SQL> show parameter instance_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string orcl1
SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl1
SQL>

If pdb services are not listed, perform below command and recheck.

SQL> alter system register;