[ORACLE] 19c RAC 환경 listener.ora 리스너 수정 및 오렌지 연결

Notice

Recent Posts

Recent Comments

Link

« 2025/04 »
일	월	화	수	목	금	토
		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

Tags more

Archives

Today

Total

관리 메뉴

DoubleDBDeep

[ORACLE] 19c RAC 환경 listener.ora 리스너 수정 및 오렌지 연결 본문

ORACLE/Administration

[ORACLE] 19c RAC 환경 listener.ora 리스너 수정 및 오렌지 연결

DBCAMI 2023. 3. 22. 13:40

오렌지 연결을 위해서 .. 리스너 설정

10.0.1.101 -> 10.0.5.101 로 변경 필요

10.0.1.102 -> 10.0.5.102 로 변경 필요

현재 상태 확인

1번 노드

# RAC1
[rac1|oracle:/u01/app/19c/grid/network/admin]> lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-MAR-2023 15:33:40

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                21-MAR-2023 14:33:51
Uptime                    0 days 0 hr. 59 min. 49 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19c/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.101)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.201)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_CRS" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "rac" has 1 instance(s).
  Instance "rac1", status READY, has 1 handler(s) for this service...
Service "racXDB" has 1 instance(s).
  Instance "rac1", status READY, has 1 handler(s) for this service...
The command completed successfully

2번 노드

# RAC2
[rac2|root:/root]> lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-MAR-2023 15:21:29

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                21-MAR-2023 14:34:31
Uptime                    0 days 0 hr. 46 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19c/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.102)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.202)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_CRS" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "rac" has 1 instance(s).
  Instance "rac2", status READY, has 1 handler(s) for this service...
Service "racXDB" has 1 instance(s).
  Instance "rac2", status READY, has 1 handler(s) for this service...
The command completed successfully

로컬 리스너 파라미터 확인

1번 노드

SQL> show parameter local_listener

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
local_listener			     string	  (ADDRESS=(PROTOCOL=TCP)(HOST=
						 10.0.1.201)(PORT=1521))

2번 노드

SQL> show parameter local_listener

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
local_listener			     string	  (ADDRESS=(PROTOCOL=TCP)(HOST=
						 10.0.1.202)(PORT=1521))

listener.ora 파일 확인 (GRID_HOME)

1번 노드

LISTENER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
    )
  )

2번 노드

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))

>>> IPC는 로컬에서 프로세스간 통신 방법으로 외부에서는 해당 방법으로 통신할 수 없음 ! 프로토콜에 TCP 방식을 추가해야함

listener.ora 파일 수정 (GRID_HOME)

vi $GRID_HOME/network/admin/listener.ora

1번 노드

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER)) 
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.101)(PORT = 1521))
    )
  )

2번 노드

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER)) 
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.102)(PORT = 1521))
    )
  )

listener 수정

srvctl modify listener -l [리스너명] -p [포트번호]

listener 재기동 및 확인

1번 노드

[rac1|oracle:/u01/app/19c/grid/network/admin]> srvctl stop listener -l LISTENER -n rac1
[rac1|oracle:/u01/app/19c/grid/network/admin]> srvctl start listener -l LISTENER -n rac1
[+ASM1|oracle:/u01/app/19c/grid/network/admin]> lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-MAR-2023 17:29:03

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                21-MAR-2023 17:22:54
Uptime                    0 days 0 hr. 6 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19c/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.5.101)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.101)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.201)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_CRS" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "rac" has 1 instance(s).
  Instance "rac1", status READY, has 1 handler(s) for this service...
Service "racXDB" has 1 instance(s).
  Instance "rac1", status READY, has 1 handler(s) for this service...
The command completed successfully

2번 노드

[rac2|oracle:/u01/app/19c/grid/network/admin]> srvctl stop listener -l LISTENER -n rac2
[rac2|oracle:/u01/app/19c/grid/network/admin]> srvctl start listener -l LISTENER -n rac2
[rac2|oracle:/u01/app/19c/grid/network/admin]> lsnrctl status LISTENER
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-MAR-2023 17:29:17

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                21-MAR-2023 17:23:09
Uptime                    0 days 0 hr. 6 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19c/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.5.102)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.102)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.202)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_CRS" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "rac" has 1 instance(s).
  Instance "rac2", status READY, has 1 handler(s) for this service...
Service "racXDB" has 1 instance(s).
  Instance "rac2", status READY, has 1 handler(s) for this service...
The command completed successfully

local_listener parameter 변경

1번 노드

SQL> alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.101)(PORT = 1521))' scope=both sid='rac1' ;

System altered.

SQL> show parameter local_listener

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
local_listener			     string	 (ADDRESS = (PROTOCOL = TCP)(HO
						 ST = 10.0.5.101)(PORT = 1521))

2번 노드

SQL> alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.102)(PORT = 1521))' scope=both sid='rac2';

System altered.

SQL> show parameter local_listener 

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
local_listener			     string	 (ADDRESS = (PROTOCOL = TCP)(HO
						 ST = 10.0.5.102)(PORT = 1521))

리스너 수동 등록

SQL> alter system register ;

System altered.

클라이언트 (로컬 컴퓨터) tnsnames.ora 수정 및 orange 접속

tnsnames.ora 수정

경로 : oracle client 설치 경로/network/admin/tnsnames.ora

전 여깁니다

RAC1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac)
    )
  )

RAC2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac)
    )
  )

orange 접속

접속 정보 입력

TNS : tnsnames.ora 에 작성한 tns명

Username : 접속할 [rac] Database의 유저 아이디

Password : 유저 비밀번호

Authentication Mode : SYS로 접속 시 SYSDBA 권한으로 접속해야 함

Comment : 해당 접속에 대한 코멘트

Label : 해당 접속시 라벨명

Oracle Home : 클라이언트 홈

> Connect

오류 : ORA-28040 일치하는 인증 프로토콜 없음 / No matching authentication protocol

원인 : 맞지 않는 client와 oracle server 버전 또는 ojdbc14.jar 사용 시

해결 방안

리눅스 Oracle 19c 서버의 sqlnet.ora에 내용 추가 (두 노드 모두 진행)

>> $ORACLE_HOME/network/admin/sqlnet.ora 수정

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

# sqlplus에서 패스워드 버전 확인
SQL> col username for a20
SQL> select username, password_versions from dba_users ;

USERNAME	     PASSWORD_VERSIONS
-------------------- -----------------
SYS		     10G 11G 12C HTTP
SYSTEM		     10G 11G 12C HTTP

오렌지 접속 성공 ~

728x90

'ORACLE > Administration' 카테고리의 다른 글

[ORACLE] 18c -> 19c RAC AutoUpgrade.jar 를 사용한 업그레이드 (0)	2023.04.19
[ORACLE] ASM administration (0)	2023.03.22
[ORACLE] 12c -> 19c RAC GUI (DBUA) 사용한 Upgrade (0)	2023.03.21
ORACLE 12c RAC 환경 ASMlib DISK GROUP에 DISK 추가하기 (Virtual Box, sqlplus 환경) (0)	2023.03.20
ORACLE 19c RAC 환경 ASMFD DISK GROUP에 DISK 추가하기 (Virtual Box, asmca gui 환경) (0)	2023.02.03

'ORACLE/Administration' Related Articles

DoubleDBDeep

[ORACLE] 19c RAC 환경 listener.ora 리스너 수정 및 오렌지 연결 본문

[ORACLE] 19c RAC 환경 listener.ora 리스너 수정 및 오렌지 연결

현재 상태 확인

로컬 리스너 파라미터 확인

listener.ora 파일 확인 (GRID_HOME)

listener.ora 파일 수정 (GRID_HOME)

listener 수정

listener 재기동 및 확인

local_listener parameter 변경

클라이언트 (로컬 컴퓨터) tnsnames.ora 수정 및 orange 접속

tnsnames.ora 수정

orange 접속

'ORACLE > Administration' 카테고리의 다른 글

티스토리툴바