tnsnames 설정으로 다른서버 db 접속

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 12.2.0.1, Oracle Database 18.0.0.0 

방법 : Oracle 12c db에서 Oracle 18c db 로 접속

#Oracle 18c db에서 lsnrctl start로 리스너를 켜준다.

$ lsnrctl start

#tnsnames.ora 파일을 열어 아래 내용을 추가한다

vi $ORACLE_HOME/network/admin/tnsnames.ora

oracle18 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.60)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ORCL)

    )

  )

#tnsping oracle18을 입력하여 통신 체크를 한다.

[oracle@oracle12 admin]$ tnsping oracle18

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 06-SEP-2018 11:25:45

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

Used parameter files:

/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.60)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))

OK (0 msec)

#이렇게 잘 나오는지 확인한다.

#접속을 테스트 해본다.

$ sqlplus system/oracle@oracle18

[oracle@oracle12 ~]$ sqlplus system/oracle@oracle18

SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 6 11:27:44 2018

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

Last Successful login time: Thu Sep 06 2018 11:17:37 +09:00

Connected to:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

SQL> select instance_name, version, status from v$instance;

INSTANCE_NAME VERSION    STATUS

---------------- ----------------- ------------

ORCL 18.0.0.0.0    OPEN

1 row selected.

참조 :