작업을 위해 sqlplus로 DB접속 시 @ORCL 같은 tnsname을 꼭 사용하여야만 하는 상황이 있습니다. local 환경에서 DB접속 시 tnsname을 사용하여 접속하는 방법 및 테스트 입니다


    준비 - tnsnames.ora 파일 수정

    tnsnames.ora 파일에 아래 내용을 추가해주세요 
    LOCDB_BEQ: TNS 별칭(원하는 이름으로 지정 가능) 
    PROTOCOL = BEQ: Bequeath Protocol을 사용하여 리스너 없이 로컬 접속 
    PROGRAM: Oracle 실행 파일 이름 
    ARGV0: Oracle 실행 파일과 SID를 조합 
    ARGS: '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))': 로컬 접속임을 명시 
    SID: 데이터베이스의 SID      
    
LOCDB_BEQ =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = BEQ)(PROGRAM = oracle)(ARGV0 = oracleent19)(ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))'))
    (CONNECT_DATA =
      (SID = ent19)
    )
  )


    테스트

    리스너 기동 상태에서 접속 테스트(모두 성공)

    
-- 리스너 상태 확인  
[ora19ent@svr admin]$ lsnrctl stat

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-FEB-2024 17:26:29

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=svr)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                22-FEB-2024 17:11:18
Uptime                    0 days 0 hr. 15 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ora19ent/product/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=svr)(PORT=1522)))
Services Summary...
Service "ent19" has 1 instance(s).
  Instance "ent19", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

-- tnsnames.ora 확인
[ora19ent@svr admin]$ cat tnsnames.ora 
ent19 =
(DESCRIPTION = 
  (ADDRESS_LIST = 
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1522))
  )
  (CONNECT_DATA =
   (SID = ent19)
  )
)


LOCDB_BEQ =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = BEQ)(PROGRAM = oracle)(ARGV0 = oracleent19)(ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))'))
    (CONNECT_DATA =
      (SID = ent19)
    )
  )
  
-- 접속 테스트 
[ora19ent@svr admin]$ sqlplus system/oracle@ent19

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 22 17:27:27 2024
Version 19.3.0.0.0

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

Last Successful login time: Thu Feb 22 2024 17:25:34 +09:00

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

SQL>


[ora19ent@svr admin]$ sqlplus system/oracle@locdb_beq

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 22 17:27:35 2024
Version 19.3.0.0.0

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

Last Successful login time: Thu Feb 22 2024 17:27:27 +09:00

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


    리스너 중지 후 접속 테스트(BEQ 프로토콜은 접속 가능)

    
-- 리스너 중지
[ora19ent@svr admin]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-FEB-2024 17:27:47

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=svr)(PORT=1522)))
The command completed successfully
[ora19ent@svr admin]$ 
[ora19ent@svr admin]$ 
[ora19ent@svr admin]$ lsnrctl stat

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-FEB-2024 17:27:52

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=svr)(PORT=1522)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
      

    -- 접속 테스트
[ora19ent@svr admin]$ sqlplus system/oracle@ent19

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 22 17:28:14 2024
Version 19.3.0.0.0

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

ERROR:
ORA-12541: TNS:no listener


Enter user-name: ^C

[ora19ent@svr admin]$ sqlplus system/oracle@locdb_beq

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 22 17:29:03 2024
Version 19.3.0.0.0

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

Last Successful login time: Thu Feb 22 2024 17:27:35 +09:00

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

SQL> select * from v$instance ;

INSTANCE_NUMBER INSTANCE_NAME       HOST_NAME                                                                         VERSION            VERSION_LEGACY    VERSION_FULL           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS   INSTANCE_ROLE            ACTIVE_ST BLO     CON_ID INSTANCE_MO EDITION FAMILY                                                                       DATABASE_TYPE
--------------- ---------------- ---------------------------------------------------------------- ----------------- ----------------- ----------------- --------- ------------ --- ---------- ------- --------------- ---------- --- ----------------- ------------------ --------- --- ---------- ----------- ------- -------------------------------------------------------------------------------- ---------------
                 1 ent19               svr                                                                                      19.0.0.0.0            19.0.0.0.0           19.3.0.0.0  22-FEB-24 OPEN        NO           1 STARTED                    ALLOWED NO  ACTIVE           PRIMARY_INSTANCE   NORMAL    NO               0 REGULAR     EE                                                                                      SINGLE

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[ora19ent@svr admin]$