"RAC 리스너 설정"의 두 판 사이의 차이
DB CAFE
(→tnsnames.ora 접속 오류시) |
(→RAC 리스너 설정) |
||
2번째 줄: | 2번째 줄: | ||
{{틀:고지 상자 | {{틀:고지 상자 | ||
|내용= (중요) RAC 환경에서는 리스너를 grid가 서비스로 관리하기때문에 srvctl 명령으로 관리를 해주어야 함 | |내용= (중요) RAC 환경에서는 리스너를 grid가 서비스로 관리하기때문에 srvctl 명령으로 관리를 해주어야 함 | ||
+ | * $GRID_HOME 의 리스너를 실행 해야함 ($ORACLE_HOME의 리스너를 실행하면 에러 발생(Not All Endpoints Registered)) | ||
* $ORACLE_HOME/network/admin/listener.ora 파일에 추가하는 방식과는 다른 방식 임. | * $ORACLE_HOME/network/admin/listener.ora 파일에 추가하는 방식과는 다른 방식 임. | ||
+ | |||
}} | }} | ||
== 리스너 상태 조회 == | == 리스너 상태 조회 == | ||
10번째 줄: | 12번째 줄: | ||
=== 리스너 나오지 않을때 === | === 리스너 나오지 않을때 === | ||
− | export 명령으로 ORACLE_HOME을 GRID_HOME으로 변경해준다. | + | export 명령으로 ORACLE_HOME을 $GRID_HOME으로 변경해준다. |
<source lang=sql> | <source lang=sql> | ||
export ORACLE_HOME=$GRID_HOME | export ORACLE_HOME=$GRID_HOME |
2024년 5월 20일 (월) 11:12 판
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 리스너 상태 조회[편집]
lsnrctl status
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) 로 변경후 접속
)
)