내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
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 설정
|
1
2
3
4
5
6
7
8
9
10
|
$ 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 시도
|
1
2
3
4
5
6
7
8
9
10
11
12
|
$ 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 접속 시도
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
$ 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 설정
|
1
2
3
4
5
6
7
8
9
|
$ 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 시도
|
1
2
3
4
5
6
7
8
9
10
11
12
|
$ 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 접속 시도
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
$ 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)
|
1
2
3
4
5
|
$
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 설정
|
1
2
3
4
5
6
7
8
9
10
|
$ 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 시도
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
$ 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 접속 시도
|
1
2
3
4
5
6
7
8
9
10
11
|
$ 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 유저 패스워드 버전 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
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 재설정
|
1
2
3
|
$ cat $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
|
19c db system 유저 패스워드 변경 후 패스워드 버전 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
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 접속 시도
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
$ 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
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c 일반테이블 및 lob테이블 컬럼 수동 extent 추가 (0) | 2022.07.01 |
|---|---|
| 오라클 19c dba_users.password_versions이 user$ 값으로 변경해도 바뀔까 (2) | 2022.05.10 |
| 오라클 19c db oraInventory 재설정 (2) | 2022.01.06 |
| 오라클 19c rootcrs.sh unlock과 lock (0) | 2021.12.30 |
| 오라클 19c 리스너 로그 자동백업 파라미터 (0) | 2021.12.26 |