"RAC 리스너 설정"의 두 판 사이의 차이
DB CAFE
(→tnsnames.ora 접속 오류시) |
|||
(같은 사용자의 중간 판 25개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
+ | = RAC 리스너 설정 = | ||
+ | {{틀:고지 상자 | ||
+ | |내용= (중요) RAC 환경에서는 리스너를 grid가 서비스로 관리하기때문에 srvctl 명령으로 관리를 해주어야 함 | ||
+ | * $ORACLE_HOME/network/admin/listener.ora 파일에 추가하는 방식과는 다른 방식 임. | ||
+ | }} | ||
== 리스너 상태 조회 == | == 리스너 상태 조회 == | ||
− | <source lang= | + | <source lang=sql> |
lsnrctl status | lsnrctl status | ||
</source> | </source> | ||
+ | |||
+ | === 리스너 나오지 않을때 === | ||
+ | export 명령으로 ORACLE_HOME을 GRID_HOME으로 변경해준다. | ||
+ | <source lang=sql> | ||
+ | export ORACLE_HOME=$GRID_HOME | ||
+ | </source> | ||
+ | |||
+ | === 리스너 확인 === | ||
<source lang=html> | <source lang=html> | ||
LSNRCTL for IBM/AIX RISC System/6000: Version 12.2.0.1.0 - Production on 22-OCT-2019 17:20:27 | LSNRCTL for IBM/AIX RISC System/6000: Version 12.2.0.1.0 - Production on 22-OCT-2019 17:20:27 | ||
45번째 줄: | 58번째 줄: | ||
The command completed successfully | The command completed successfully | ||
</source> | </source> | ||
+ | |||
== 리스너 추가 == | == 리스너 추가 == | ||
+ | * (기존에 LISTENER 가 존재 하고 신규로 LISTENER2를 추가하는 시나리오) | ||
− | + | === 리스너 신규 추가하기 === | |
- 리스너명은 LISTENER2, 포트는 1571로 리스너를 추가합니다. | - 리스너명은 LISTENER2, 포트는 1571로 리스너를 추가합니다. | ||
53번째 줄: | 68번째 줄: | ||
srvctl add listener -l LISTENER2 -p 1571 | srvctl add listener -l LISTENER2 -p 1571 | ||
</source> | </source> | ||
− | + | ||
+ | === crsctl로 상태 확인 및 시작하기 === | ||
+ | * crsctl 명령어로 확인 하는 방법 | ||
<source lang=html> | <source lang=html> | ||
crsctl stat res -t | crsctl stat res -t | ||
67번째 줄: | 84번째 줄: | ||
ora.LISTENER2.lsnr | ora.LISTENER2.lsnr | ||
OFFLINE OFFLINE rac1 | OFFLINE OFFLINE rac1 | ||
+ | </source> | ||
+ | * srvctl 명령어로 확인 하는 방법 | ||
+ | <source lang=bash> | ||
+ | srvctl status listener | ||
</source> | </source> | ||
+ | |||
+ | * LISTENER2 리스너 시작하기 | ||
<source lang=html> | <source lang=html> | ||
srvctl start listener -l LISTENER2 | srvctl start listener -l LISTENER2 | ||
</source> | </source> | ||
− | + | ||
+ | === 리스너 Port 확인 === | ||
<source lang=html> | <source lang=html> | ||
− | netstat -an | grep | + | netstat -an | grep 1571 |
− | + | tcp 0 0 192.168.80.163:1571 0.0.0.0:* LISTEN | |
− | tcp 0 0 192.168.80.163: | + | tcp 0 0 192.168.80.161:1571 0.0.0.0:* LISTEN |
− | tcp 0 0 192.168.80.161: | ||
</source> | </source> | ||
<source lang=html> | <source lang=html> | ||
− | + | crsctl stat res -t | |
+ | </source> | ||
+ | |||
+ | <source lang=bash> | ||
+ | srvctl status listener | ||
</source> | </source> | ||
92번째 줄: | 119번째 줄: | ||
ora.LISTENER.lsnr ONLINE ONLINE rac1 | ora.LISTENER.lsnr ONLINE ONLINE rac1 | ||
ora.LISTENER2.lsnr ONLINE ONLINE rac1 | ora.LISTENER2.lsnr ONLINE ONLINE rac1 | ||
− | + | ||
</source> | </source> | ||
− | + | ||
+ | ==== lsnrctl로 상태 확인하기 ==== | ||
<source lang=html> | <source lang=html> | ||
lsnrctl status | lsnrctl status | ||
</source> | </source> | ||
+ | |||
<source lang=html> | <source lang=html> | ||
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 06-AUG-2015 15:30:51 | LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 06-AUG-2015 15:30:51 | ||
155번째 줄: | 184번째 줄: | ||
The command completed successfully | The command completed successfully | ||
</source> | </source> | ||
− | |||
== 리스너 삭제 == | == 리스너 삭제 == | ||
199번째 줄: | 227번째 줄: | ||
<source lang=html> | <source lang=html> | ||
netstat -an | grep 1621 | netstat -an | grep 1621 | ||
− | tcp 0 0 | + | tcp 0 0 11.22.33.44:1621 0.0.0.0:* LISTEN |
− | tcp 0 0 | + | tcp 0 0 11.22.33.55:1621 0.0.0.0:* LISTEN |
</source> | </source> | ||
<source lang=html> | <source lang=html> | ||
207번째 줄: | 235번째 줄: | ||
<source lang=html> | <source lang=html> | ||
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 06-AUG-2015 16:12:16 | 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. | Copyright (c) 1991, 2013, Oracle. All rights reserved. | ||
230번째 줄: | 258번째 줄: | ||
</source> | </source> | ||
− | == | + | == 파라미터 변경 == |
+ | <source lang=console> | ||
+ | SQL> show parameter local_listener; | ||
+ | SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.22.33)(PORT=2222))' scope=both; | ||
+ | </source> | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
== client에서 접속하기 == | == client에서 접속하기 == | ||
− | + | * 12C RAC 환경 FAILOVER 접속용 TNS | |
1. tnsnames.ora 정보 | 1. tnsnames.ora 정보 | ||
<source lang=html> | <source lang=html> | ||
261번째 줄: | 286번째 줄: | ||
) | ) | ||
</source> | </source> | ||
+ | 2. 더 간단하게 | ||
+ | <source lang=html> | ||
+ | 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) | ||
+ | ) | ||
+ | ) | ||
+ | ) | ||
+ | </source> | ||
+ | |||
+ | |||
+ | === tnsnames.ora 접속 오류시 === | ||
+ | * RAC인경우 여러개의 서버가 존재 하므로 각각의 서버로 접속 하기위해서 service_name 이 아닌 sid로 접속함. | ||
+ | *: (SERVICE_NAME = ORCL) ==> (SID = rac1) 로 변경후 접속 | ||
+ | <source lang=html> | ||
+ | orcl = | ||
+ | (DESCRIPTION = | ||
+ | (ADDRESS_LIST = | ||
+ | (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521)) | ||
+ | ) | ||
+ | (CONNECT_DATA = | ||
+ | (SERVER = DEDICATED) | ||
+ | (SERVICE_NAME = ORCL) ==> (SID = rac1) 로 변경후 접속 | ||
+ | ) | ||
+ | ) | ||
+ | </source> | ||
+ | |||
+ | [[Category:oracle]] |
2024년 1월 12일 (금) 14:17 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 RAC 리스너 설정[편집]
android (중요) RAC 환경에서는 리스너를 grid가 서비스로 관리하기때문에 srvctl 명령으로 관리를 해주어야 함
- $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) 로 변경후 접속
)
)