[Oracle - PDB접속 방법 use TNS]

이번글에서는 정말 별거아니지만.. oracle multitenant 구조에서 tns를 이용하여 CDB에서 PDB로 접속하는것이 아닌 sqlplus 유틸리티 이용 바로 접속 방법을 포스팅하도록 하겠습니다.

 

 

 

 

CDB -> PDB접속

$sqlplus / as sysdba         


## 현재 CDB
SQL> show con_name;          
CON_NAME
------------------------------
CDB$ROOT



SQL> show pdbs;               
    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 ORCLPDB              READ WRITE NO

SQL> alter session set container=orclpdb;     

 

Session altered.

SQL> show con_name;                  
CON_NAME
------------------------------
ORCLPDB

 

 

 

TNS이용 PDB접속

[oracle@oracle]$ lsnrctl status 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 28-APR-2021 06:38:32

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                26-APR-2021 18:31:02
Uptime                    1 days 12 hr. 7 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/network/admin/listener.ora
Listener Log File         /oracle/app/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "c0e7a25bc4f5608de053402828ac34d8" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclpdb" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

 

 

 

■ tnsnames.ora 수정

[oracle@oracle]$ cd $ORACLE_HOME/network/admin/


[oracle@oracle admin]$ vi tnsnames.ora
LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
ORCL =                                                                    ->기본 tns
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

A = 
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <IP>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)            -> DEDICATED로 설정하면 한 세션에 프로세스 1개  즉, 1대1방식     SHARED로 바꾸면 여러 프로세스를 다 같이 사용. 
      (SERVICE_NAME = orclpdb)                      -> PDB 서비스 네임
    )
  )




## tnsping 확인

[oracle@oracle admin]$ tnsping A 

 

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 28-APR-2021 06:42:54

 

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

 

Used parameter files:

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclpdb)))

OK (0 msec)

 

 

■ PDB접속

[oracle@oracle admin]$ sqlplus sys/root@A as sysdba             -> (sys유저/비밀번호@tns이름 sys역할)

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 28 06:44:34 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


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



SQL> show con_name         
ORCLPDB