내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
OS환경 : Oracle Linux 4.8, 6.8 (64bit)
DB 환경 : Oracle Database 9.2.0.8, 12.2.0.4
방법 : oracle 9i client 12c 접속 12c client 9i 접속 시도
oracle 9i 에서 12c로 접속해보고 가능하면 db link 연결도 확인해보는 테스트
공식적으로는 클라이언트 db링크 모두 양방향 지원하지 않음
(Client / Server Interoperability Support Matrix for Different Oracle Versions (문서 ID 207303.1)참조)
테스트
9i db와 12c db 모두 system계정을 사용하고 패스워드는 oracle 로 설정되어 있음
9i db서버 tnsnames.ora 설정
1
2
3
4
5
6
7
8
9
10
|
oracle12 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.50)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA12)
)
)
|
tnsping 시도
1
2
3
4
5
6
7
8
9
10
11
12
|
$ tnsping oracle12
TNS Ping Utility for Linux: Version 9.2.0.8.0 - Production on 13-MAR-2021 21:39:20
Copyright (c) 1997, 2006, Oracle Corporation. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.50)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORA12)))
OK (0 msec)
|
성공
12c db서버 tnsnames.ora 설정
1
2
3
4
5
6
7
8
9
10
|
oracle9 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA9)
)
)
|
tnsping 시도
1
2
3
4
5
6
7
8
9
10
11
12
13
|
$ tnsping oracle9
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 13-FEB-2019 09:10:55
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.10)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORA9)))
OK (0 msec)
|
성공
12c db서버 sqlnet.ora 설정(8)
1
2
3
4
|
$ vi $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
|
2개 파라미터 추가
12c db서버 패스워드 변경
1
2
3
4
5
6
7
8
9
10
11
12
13
|
$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 13 14:33:44 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> alter user system identified by oracle;
User altered.
|
12c 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
35
36
37
38
39
40
|
$ lsnrctl stop
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 13-FEB-2019 14:34:34
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12)(PORT=1521)))
The command completed successfully
$ lsnrctl start
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 13-FEB-2019 14:34:35
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Starting /oracle/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/oracle12/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 13-FEB-2019 14:34:35
Uptime 0 days 0 hr. 0 min. 5 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/oracle12/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
|
12c -> 9i 접속 시도
1
2
3
4
5
6
7
8
9
|
$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 13 14:35:46 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
@> conn system/oracle@oracle9
ERROR:
ORA-03134: Connections to this server version are no longer supported.
|
ORA-03134: Connections to this server version are no longer supported.
버전이 호환되지 않아 접속되지 않음
9i -> 12c 접속 시도
1
2
3
4
5
6
7
8
9
|
$ sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 - Production on Sun Mar 14 03:14:20 2021
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
> conn system/oracle@oracle12
세그멘테이션 오류
$
|
세그멘테이션 오류가 나오고 바로 sqlplus 창에서 나가짐
12c db서버 sqlnet.ora 재설정(10)
1
2
3
4
|
$ vi $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
|
해당 파라미터 10으로 변경
12c db서버 패스워드 변경, 12c db서버 리스너 재기동
9i -> 12c 접속 시도
1
2
3
4
5
6
7
8
9
|
$ sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 - Production on Sun Mar 14 03:16:47 2021
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
> conn system/oracle@oracle12
ERROR:
ORA-28040: No matching authentication protocol
|
인증 문제로 접속 안됨
12c db서버 sqlnet.ora 재설정(9)
1
2
3
4
|
$ vi $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=9
SQLNET.ALLOWED_LOGON_VERSION_SERVER=9
|
해당 파라미터 9으로 변경
12c db서버 패스워드 변경, 12c db서버 리스너 재기동
9i -> 12c 접속 시도
1
2
3
4
5
6
7
8
|
$ sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 - Production on Sun Mar 14 03:21:23 2021
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
> conn system/oracle@oracle12
세그멘테이션 오류
|
처음 했을때와 동일 증상 발생
https://cafe.naver.com/prodba/48948 글작성자와 https://cafe.naver.com/prodba/49664 글에 의하면
9i -> 12c로 접속은 된다고 하는데 왜 나는 접속부터 안되는지 모르겠음..
참조 :
https://cafe.naver.com/prodba/49664
https://cafe.naver.com/prodba/48948
'ORACLE > Admin' 카테고리의 다른 글
로그마이너 log miner 사용법 12cR2 이상 (2) | 2019.02.15 |
---|---|
오라클 drop database 데이터베이스 삭제 (0) | 2019.02.15 |
sqlplus spool 기능 사용 시 명령어 안나오게 하는방법 (0) | 2019.02.11 |
로그마이너 Supplemental logging 기능 (0) | 2019.02.11 |
date 타입과 timestamp 타입 조회 (0) | 2019.01.28 |