오라클 11g R2 client 19c 접속 19c client 11g R2 접속 시도

OS환경 : Oracle Linux 6.8, 8.1 (64bit)

 

DB 환경 : Oracle Database 11.2.0.1, 11.2.0.4, 19.3.0.0

 

방법 : oracle 11gR2 client 19c 접속 19c client 11gR2 접속 시도

oracle 11gR2 에서 19c로 접속해보는 테스트

11.2.0.4와 11.2.0.1 두 버전에서 테스트해봄
공식적으로는 클라이언트 db링크 모두 양방향 지원함(11gR2 중 11.2.0.3 또는 11.2.0.4만 해당됨)
(Client / Server Interoperability Support Matrix for Different Oracle Versions (문서 ID 207303.1)참조)

11.2.0.4 -> 19c 테스트

19c -> 11.2.0.4 테스트

11.2.0.1 -> 19c 테스트

 

서버, hostname, ip, sid

11gR2 : ora11, 192.168.137.11, orcl

19c : ora19, 192.168.137.19, oracle19

 

 

11g db와 19c db 모두 system계정을 사용하고 패스워드는 oracle 로 설정되어 있음

 

11.2.0.4 -> 19c 테스트

11g db서버 tnsnames.ora 설정

$ cat $ORACLE_HOME/network/admin/tnsnames.ora 
ora19a =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora19)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oracle19)
    )
  )

 

 

19c로 tnsping 시도

$ tnsping ora19a
 
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 10-MAY-2022 14:48:12
 
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
 
Used parameter files:
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora19)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oracle19)))
OK (0 msec)

성공

 

 

19c db 접속 시도

$ sqlplus system/oracle@ora19a
 
SQL*Plus: Release 11.2.0.4.0 Production on Tue May 10 14:48:31 2022
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
 
SQL> select instance_name, version from v$instance;
 
INSTANCE_NAME     VERSION
---------------- -----------------
oracle19     19.0.0.0.0

정상적으로 접속됨

 

 

19c -> 11.2.0.4 테스트

19c db서버 tnsnames.ora 설정

$ cat $ORACLE_HOME/network/admin/tnsnames.ora 
ora11a =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

 

 

11g로 tnsping 시도

$ tnsping ora11a
 
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-MAY-2022 14:51:16
 
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 = ora11)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)

성공

 

 

11g db 접속 시도

$ sqlplus system/oracle@ora11a
 
SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 10 14:51:33 2022
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select instance_name, version from v$instance;
 
INSTANCE_NAME     VERSION
---------------- -----------------
orcl         11.2.0.4.0

정상적으로 접속됨

 

 

11.2.0.1 -> 19c 테스트

11.2.0.1 클라이언트 설치 후 bash_profile 환경설정 적용(export)

$
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/client11201
export PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

 

 

11.2.0.1 client HOME의 tnsnames.ora 설정

$ cat $ORACLE_BASE/product/11.2.0/client11201/network/admin/tnsnames.ora 
ora19b =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora19)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oracle19)
    )
  )

 

 

19c로 tnsping 시도

$ tnsping ora19b
 
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 10-MAY-2022 15:09:33
 
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
 
Used parameter files:
/app/oracle/product/11.2.0/client11201/network/admin/sqlnet.ora
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora19)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oracle19)))
OK (0 msec)

성공

 

 

19c db 접속 시도

$ sqlplus system/oracle@ora19b
 
SQL*Plus: Release 11.2.0.1.0 Production on Tue May 10 15:10:08 2022
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
ERROR:
ORA-28040: No matching authentication protocol
 
 
Enter user-name: 

접속 안됨

 

 

19c db 유저 패스워드 버전 확인

SQL> 
set lines 200 pages 1000
col username for a30 
select username, password_versions from dba_users order by 1;
 
USERNAME               PASSWORD_VERSIONS
------------------------------ -----------------
ANONYMOUS
APPQOSSYS
AUDSYS
DBSFWUSER
DBSNMP
DIP
GGSYS
GSMADMIN_INTERNAL
GSMCATUSER
GSMUSER
IMSI                   11G 12C
MIGUSER                11G 12C
ORACLE_OCM
OUTLN
REMOTE_SCHEDULER_AGENT
SYS                   11G 12C
SYS$UMF
SYSBACKUP
SYSDG
SYSKM
SYSRAC
SYSTEM                   11G 12C
WMSYS
XDB
XS$NULL
 
25 rows selected.

현재 접속하려는 system 유저 패스워드 버전이 11G 12C임

 

 

19c db서버 sqlnet.ora 재설정

$ cat $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

 

 

19c db system 유저 패스워드 변경 후 패스워드 버전 확인

SQL> alter user system identified by oracle;
 
User altered.
 
SQL> 
set lines 200 pages 1000
col username for a30 
select username, password_versions from dba_users order by 1;
 
USERNAME               PASSWORD_VERSIONS
------------------------------ -----------------
ANONYMOUS
APPQOSSYS
AUDSYS
DBSFWUSER
DBSNMP
DIP
GGSYS
GSMADMIN_INTERNAL
GSMCATUSER
GSMUSER
IMSI                   11G 12C
MIGUSER                11G 12C
ORACLE_OCM
OUTLN
REMOTE_SCHEDULER_AGENT
SYS                   11G 12C
SYS$UMF
SYSBACKUP
SYSDG
SYSKM
SYSRAC
SYSTEM                   10G 11G 12C
WMSYS
XDB
XS$NULL
 
25 rows selected.

system 유저 패스워드 버전이 10G 11G 12C로 변경됨

 

 

다시 11.2.0.1 -> 19c db 접속 시도

$ sqlplus system/oracle@ora19b
 
SQL*Plus: Release 11.2.0.1.0 Production on Tue May 10 15:15:42 2022
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
 
SQL> select instance_name, version from v$instance;
 
INSTANCE_NAME     VERSION
---------------- -----------------
oracle19     19.0.0.0.0

정상적으로 접속됨

 

 

결론 : 11.2.0.4에서 19c로는 바로 접속이 되지만

11.2.0.1 버전에서는 19c 서버에서 sqlnet.ora 파일 수정을 해준 뒤 패스워드를 변경해줘야

정상적으로 접속이 가능함

 

 

참조 : 207303.1

https://positivemh.tistory.com/473

https://positivemh.tistory.com/420

https://positivemh.tistory.com/831