16 - [Oracle 19C] connecting using tnsnames.ora

 

파일위치 : /u01/app/oracle/product/19/db_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.com)
    )
  )

 

 

 

SQL> conn sys/Manager123#@orcl as sysdba
Connected.
SQL>

 

 

 

ORCLPDB 를 아래와 같이 추가한다.

 

[oracle@test admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.com)
    )
  )


ORCLPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclpdb.com)
    )
  )

 

 

 

@tnsnames 를 추가해서 원하는 서비스에 접속이 가능하다

 

SQL>
SQL> conn system/Manager123#@orcl
Connected.
SQL> conn system/Manager123#@orclpdb
ERROR:
ORA-01109: database not open


Warning: You are no longer connected to ORACLE.
SQL> conn sys/Manager123# as sysdba
Connected.
SQL> alter pluggable database orclpdb open;

Pluggable database altered.

SQL> conn system/Manager123#@orclpdb
Connected.
SQL> show con_name

CON_NAME
------------------------------
ORCLPDB
SQL>
SQL> conn sys/Manager as sysdba
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

 

 

 

원격에서 접속할때 tnsnames.ora 파일 사용하는 방법, 내 컴퓨터에는 오라클 서버 host 이름을 oracle88 로 설정되어 있다.

 

# 자신의 컴퓨터에 tnsnames.ora 파일을 만들고 아래 내용을 추가해서 저장한다

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle88)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.com)
    )
  )


ORCLPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle88)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclpdb.com)
    )
  )