RAC 리스너 설정
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 RAC 리스너 설정[편집]
android (중요) RAC 환경에서는 리스너를 grid가 서비스로 관리하기때문에 srvctl 명령으로 관리를 해주어야 함
- $GRID_HOME 의 리스너를 실행 해야함 ($ORACLE_HOME의 리스너를 실행하면 에러 발생(Not All Endpoints Registered))
- $ORACLE_HOME/network/admin/listener.ora 파일에 추가하는 방식과는 다른 방식 임.
1.1 리스너 상태 조회[편집]
- grid 계정으로 확인 할것(oralce 계정이 아님)
srvctl status listener
-- or
lsnrctl status
1.1.1 RAC 환경에서 리스너 에러 발생시 확인 사항[편집]
- export 명령으로 ORACLE_HOME을 $GRID_HOME으로 변경해준다.(RAC는 $GRID_HOME을 사용함을 명심할것)
export ORACLE_HOME=$GRID_HOME
- ORACLE_HOME 의 리스너를 실행할 경우, CRS 에서 아래와 같은 상황이 생김
crsctl stat res -t
--------------------------------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS Local Resources --------------------------------------------------------------------------------------------------------- ora.DATA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2 ora.FRA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2 ora.LISTENER.lsnr
ONLINE INTERMEDIATE rac1
ONLINE ONLINE rac2 Not All Endpoints Registered. <== 에러 발생
.... 생략 ....
1.1.1.1 RAC 환경에서 리스너 오류시 리스너 재시작 순서[편집]
- 위와 같이 꼬였을 경우, 아래의 순서대로 리스너를 재시작 할것.
1. $ORACLE_HOME에 설정된 기존 리스너 멈추기
$/bin/lsnrctl stop
2. $GRID_HOME에 설정된 리스너 멈추기
$/bin/srvctl stop listener -n node_name
$/bin/srvctl stop scan_listener -i scan#
3. $GRID_HOME에 있는 LISTENER 와 LISTENER_SCAN1 재시작
$/bin/srvctl start listener -n node_name $/bin/srvctl start scan_listener -i scan#
4. crsctl stat res -t 명령으로 확인 (둘다 ONLINE 인지 확인)
crsctl stat res -t
1.1.2 리스너 확인[편집]
LSNRCTL for IBM/AIX RISC System/6000: Version 12.2.0.1.0 - Production on 22-OCT-2019 17:20:27
Copyright (c) 1991, 2017, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 12.2.0.1.0 - Production
Start Date 09-SEP-2019 19:55:51
Uptime 42 days 21 hr. 24 min. 36 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/12.2.0/gi_home/network/admin/listener.ora
Listener Log File /oracle/app/grid/diag/tnslsnr/xxxx/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=11.22.33.44)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=11.22.33.55)(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_MGMT" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "XXX" has 1 instance(s).
Instance "XXX2", status READY, has 1 handler(s) for this service...
Service "XXXDB" has 1 instance(s).
Instance "XXX2", status READY, has 1 handler(s) for this service...
Service "AAA" has 1 instance(s).
Instance "AAA2", status READY, has 1 handler(s) for this service...
Service "XXXX" has 1 instance(s).
Instance "XXXX2", status READY, has 1 handler(s) for this service...
The command completed successfully
1.2 리스너 추가[편집]
- (기존에 LISTENER 가 존재 하고 신규로 LISTENER2를 추가하는 시나리오)
1.2.1 리스너 신규 추가하기[편집]
- 리스너명은 LISTENER2, 포트는 1571로 리스너를 추가합니다.
srvctl add listener -l LISTENER2 -p 1571
1.2.2 crsctl로 상태 확인 및 시작하기[편집]
- crsctl 명령어로 확인 하는 방법
crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE rac1
ora.LISTENER2.lsnr
OFFLINE OFFLINE rac1
- srvctl 명령어로 확인 하는 방법
srvctl status listener
- LISTENER2 리스너 시작하기
srvctl start listener -l LISTENER2
1.2.3 리스너 Port 확인[편집]
netstat -an | grep 1571
tcp 0 0 192.168.80.163:1571 0.0.0.0:* LISTEN
tcp 0 0 192.168.80.161:1571 0.0.0.0:* LISTEN
crsctl stat res -t
srvctl status listener
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr ONLINE ONLINE rac1
ora.LISTENER2.lsnr ONLINE ONLINE rac1
1.2.3.1 lsnrctl로 상태 확인하기[편집]
lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 06-AUG-2015 15:30:51
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 06-AUG-2015 14:31:57
Uptime 0 days 0 hr. 58 min. 54 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/grid/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/base/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.80.161)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.80.163)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "racdb" has 1 instance(s).
Instance "racdb1", status READY, has 1 handler(s) for this service...
Service "racdbXDB" has 1 instance(s).
Instance "racdb1", status READY, has 1 handler(s) for this service...
The command completed successfully
lsnrctl status LISTENER2
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 06-AUG-2015 15:30:59
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER2)))
STATUS of the LISTENER
------------------------
Alias LISTENER2
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 06-AUG-2015 15:30:37
Uptime 0 days 0 hr. 0 min. 22 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/grid/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/11.2.0/grid/log/diag/tnslsnr/rac1/listener2/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER2)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.80.161)(PORT=1555)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.80.163)(PORT=1555)))
The listener supports no services
The command completed successfully
1.4 리스너 변경[편집]
1. 리스너 포트 변경하기 - 리스너명 LISTENER2의 포트를 1621로 변경합니다.
1) 리스너 중지
srvctl stop listener -l LISTENER2 -n rac1
2) 리스너 포트 변경
srvctl modify listener -l LISTENER2 -p 1621
3) 리스너 시작
srvctl start listener -l LISTENER2 -n rac1
4) 상태 확인
crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr ONLINE ONLINE rac1
ora.LISTENER2.lsnr ONLINE ONLINE rac1
netstat -an | grep 1621
tcp 0 0 11.22.33.44:1621 0.0.0.0:* LISTEN
tcp 0 0 11.22.33.55:1621 0.0.0.0:* LISTEN
lsnrctl status listener2
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 06-AUG-2015 16:12:16
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER2)))
STATUS of the LISTENER
------------------------
Alias LISTENER2
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 06-AUG-2015 16:11:40
Uptime 0 days 0 hr. 0 min. 36 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/grid/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/11.2.0/grid/log/diag/tnslsnr/rac1/listener2/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER2)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.80.161)(PORT=1621)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.80.163)(PORT=1621)))
The listener supports no services
The command completed successfully
1.5 파라미터 변경[편집]
SQL> show parameter local_listener;
SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.22.33)(PORT=2222))' scope=both;
1.6 client에서 접속하기[편집]
- 12C RAC 환경 FAILOVER 접속용 TNS
1. tnsnames.ora 정보
IDC_XXX =
(DESCRIPTION =
(LOAD_BALANCE = ON)
(FAILOVER=ON)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.22.33.44)(PORT = 1561))
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.22.33.55)(PORT = 1561))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.22.33.55)(PORT = 1561))
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.22.33.44)(PORT = 1561))
)
(CONNECT_DATA =
(SERVICE_NAME = XXX)
(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 3)(DELAY = 30))
)
)
2. 더 간단하게
IDC_XXX =
(description=
(address_list= (failover=yes)(load_balance=yes)
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.22.33.44)(PORT = 1561))
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.22.33.55)(PORT = 1561))
)
(connect_data=
(server=dedicated)
(service_name=IDC)
(failover_mode=
(type=session)
(method=basic)
(retries=180)
(delay=5)
)
)
)
1.6.1 tnsnames.ora 접속 오류시[편집]
- RAC인경우 여러개의 서버가 존재 하므로 각각의 서버로 접속 하기위해서 service_name 이 아닌 sid로 접속함.
- (SERVICE_NAME = ORCL) ==> (SID = rac1) 로 변경후 접속
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL) ==> (SID = rac1) 로 변경후 접속
)
)