<< HR SESSION >>
[oracle@oracle spool]$ pwd
/home/oracle/spool
| [oracle@oracle spool]$ vi emp_sal.ctl UNRECOVERABLE LOAD DATA INFILE emp_sal.csv TRUNCATE INTO TABLE hr.emp_sal FIELDS TERMINATED BY ',' (employee_id, last_name, first_name, salary, department_id) :wq [oracle@oracle spool]$ sqlldr hr/hr control=emp_sal.ctl direct=true ... Load completed - logical record count 106. # bad file : 문제되는 데이터가 있을 경우 생성된다. [oracle@oracle spool]$ cat emp_sal.bad 178,Grant,Kimberely,7000, # log file : 작업 시 무조건 생성된다. [oracle@oracle spool]$ cat emp_sal.log ... Record 87: Rejected - Error on table HR.EMP_SAL, column DEPARTMENT_ID. Column not found before end of logical record (use TRAILING NULLCOLS) ... [oracle@oracle spool]$ vi emp_sal.ctl UNRECOVERABLE LOAD DATA INFILE emp_sal.csv TRUNCATE INTO TABLE hr.emp_sal FIELDS TERMINATED BY ',' TRAILING NULLCOLS -- null 값도 포함한다 (employee_id, last_name, first_name, salary, department_id) :wq [oracle@oracle spool]$ sqlldr hr/hr control=emp_sal.ctl direct=true Load completed - logical record count 106. [oracle@oracle spool]$ cat emp_sal.log ... Table HR.EMP_SAL: 106 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. ... |
# control file 안에 데이터가 같이 있을 경우 INFILE * 사용한다.
INFILE *
...
BEGINDATA
데이터1
데이터2
...
# control file과 데이터 파일의 위치가 다를 경우
INFILE 경로/파일명
| DROP TABLE hr.test PURGE; CREATE TABLE hr.test( id NUMBER CONSTRAINT test_id PRIMARY KEY, name VARCHAR2(30), phone VARCHAR2(20) ); [oracle@oracle spool]$ vi insa.dat 1,"JAMES","010-1000-0001" 2,"ORACLE","010-7777-7777" 3,"KIM","010-8888-8888" 3,"SCOTT","010-9999-9999" :wq ** 데이터 생성 시 공백라인 = 데이터로 인식하니 만들지 말자 |
| # Conventional load # 단점 : 수행 속도가 느리다. 리두발생을 한다. commit 발생한다. # 장점 : 제약조건을 체크해서 문제되는 데이터는 bad file에 입력해준다. 품질이 좋은 데이터만 입력된다. [oracle@oracle spool]$ vi insa.ctl LOAD DATA -- Conventional load 시 사용 INFILE insa.dat INSERT INTO TABLE hr.test FIELDS TERMINATED BY ',' (id, name, phone) :wq [oracle@oracle spool]$ sqlldr hr/hr control=insa.ctl Commit point reached - logical record count 4 [oracle@oracle spool]$ cat insa.bad 3,"SCOTT","010-9999-9999" >> PK 위반 [oracle@oracle spool]$ cat insa.log ... Record 4: Rejected - Error on table HR.TEST. ORA-00001: unique constraint (HR.TEST_ID) violated ... SQL> select * from hr.test; ID NAME PHONE ---------- ------------------------------ -------------------- 1 JAMES 010-1000-0001 2 ORACLE 010-7777-7777 3 KIM 010-8888-8888 >> 위반되는 데이터를 제외하고 생성되었다. |
| # Direct Path Load # 장점 : 수행속도가 빠르다. 테이블에 저장해야할 데이터를 메모리에서 모양을 만든 후 디스크로 바로 save 한다. # 단점 : 제약조건을 체크하지 않는다.(품질저하) [oracle@oracle spool]$ vi insa.ctl UNRECOVERABLE LOAD DATA INFILE insa.dat TRUNCATE -- 기존 데이터가 있어서 사용 INTO TABLE hr.test FIELDS TERMINATED BY ',' (id, name, phone) :wq [oracle@oracle spool]$ sqlldr hr/hr control=insa.ctl direct=true Load completed - logical record count 4. [oracle@oracle spool]$ cat insa.log ... The following index(es) on table HR.TEST were processed: index HR.TEST_ID was made unusable due to: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found ... select * from hr.test; ID NAME PHONE ---------- ------------------------------ -------------------- 1 JAMES 010-1000-0001 2 ORACLE 010-7777-7777 3 KIM 010-8888-8888 3 SCOTT 010-9999-9999 >> bad file이 생성되지 않아 문제 없어 보이지만 강제로 PK 위반된 데이터를 입력하여 INDEX가 깨지고 추가 DML 작업을 할 수 없는 상태이다. |
| SELECT * FROM user_constraints WHERE table_name = 'TEST'; STATUS : ENABLED VALIDATED : VALIDATED DEFERRABLE : NOT DEFERRABLE DEFERRED : IMMEDIATE SELECT * FROM user_ind_columns WHERE table_name = 'TEST'; SELECT * FROM user_objects WHERE status = 'INVALID'; >> INVALID 상태의 모든 객체는 확인해야한다. SELECT * FROM user_objects WHERE object_name = 'TEST_ID'; STATUS : VALID --> 믿지말자. >> 인덱스의 상태는 user_indexes / dba_indexes 여기서 봐야한다!!! SELECT * FROM user_indexes WHERE table_name = 'TEST'; STATUS : UNUSABLE --> 실행불가 # index 상태가 unusable 상태 일 경우 - 기존의 index scan이 table full scan으로 바뀐다. select * from hr.test where id = 1;  - INSERT 가 안된다. insert into hr.test(id,name,phone) values(4,'park','010-0001-1005'); ORA-01502: index 'HR.TEST_ID' or partition of such index is in unusable state ORA-01502: 인덱스 'HR.TEST_ID'또는 인덱스 분할영역은 사용할 수 없은 상태입니다 - DELETE 가 안된다. delete from hr.test where name = 'SCOTT'; ORA-01502: index 'HR.TEST_ID' or partition of such index is in unusable state ORA-01502: 인덱스 'HR.TEST_ID'또는 인덱스 분할영역은 사용할 수 없은 상태입니다 - UPDATE 가 안된다. UPDATE hr.test set id = 4 where name = 'SCOTT'; ORA-01502: index 'HR.TEST_ID' or partition of such index is in unusable state ORA-01502: 인덱스 'HR.TEST_ID'또는 인덱스 분할영역은 사용할 수 없은 상태입니다 >>> DML 작업 불가 |
| # 해결방법 | |
| 1. 제약조건 DISABLED 상태로 만들기 SELECT * FROM user_constraints WHERE table_name = 'TEST'; STATUS : ENABLED VALIDATED : VALIDATED DEFERRABLE : NOT DEFERRABLE DEFERRED : IMMEDIATE ALTER TABLE hr.test DISABLE CONSTRAINT test_id; SELECT * FROM user_constraints WHERE table_name = 'TEST'; STATUS : DISABLED VALIDATED : NOT VALIDATED SELECT * FROM user_objects WHERE object_name = 'TEST_ID'; >> DISABLED 상태가 되면서 INDEX는 자동 DROP |
|
| 2. EXCEPTIONS SCRIPT 활성화 SQL> @$ORACLE_HOME/rdbms/admin/utlexpt1 SELECT * FROM exceptions; |
|
| 3. HR계정> hr.test 오류 내역을 hr 소유자의 exceptions 테이블에 로드 한다. ALTER TABLE hr.test ENABLE VALIDATE CONSTRAINT test_id EXCEPTIONS INTO exceptions; ORA-02437: cannot validate (HR.TEST_ID) - primary key violated ORA-02437: cannot validate (HR.TEST_ID) - primary key violated SELECT * FROM exceptions; ROW_ID OWNER TABLE_NAME CONSTRAINT ------------------- ------- ----------- ---------- AAAVq/AAEAAAAJDAAD HR TEST TEST_ID AAAVq/AAEAAAAJDAAC HR TEST TEST_ID # SYS계정> hr.test 오류 내역을 sys 소유자의 exceptions 테이블에 로드 한다. ALTER TABLE hr.test ENABLE VALIDATE CONSTRAINT test_id EXCEPTIONS INTO sys.exceptions; |
|
| 4. rowid로 데이터 정보 찾아 업무팀에 데이터 확인요청 SELECT rowid, t.* FROM hr.test t WHERE rowid IN (SELECT row_id FROM exceptions); ROWID ID NAME PHONE ------------------- --- ------- ------------- AAAVq/AAEAAAAJDAAC 3 KIM 010-8888-8888 AAAVq/AAEAAAAJDAAD 3 SCOTT 010-9999-9999 |
|
| 5. 자료 수정 --> 업무팀 UPDATE hr.test SET id = 4 WHERE rowid = 'AAAVq/AAEAAAAJDAAD'; COMMIT; SELECT * FROM hr.test; |
|
| 6. 제약조건 복구 TRUNCATE TABLE exceptions; SELECT * FROM exceptions; ALTER TABLE hr.test ENABLE VALIDATE CONSTRAINT test_id EXCEPTIONS INTO exceptions; SELECT * FROM exceptions; SELECT * FROM user_constraints WHERE table_name = 'TEST'; STATUS : ENABLED VALIDATED : VALIDATED DEFERRABLE : NOT DEFERRABLE DEFERRED : IMMEDIATE SELECT * FROM user_constraints WHERE table_name = 'TEST'; STATUS : ENABLED VALIDATED : NOT VALIDATED SELECT * FROM user_objects WHERE object_name = 'TEST_ID'; STATUS : VALID SELECT * FROM user_ind_columns WHERE table_name = 'TEST'; INDEX_NAME : TEST_ID SELECT * FROM hr.test WHERE id = 1;  |
|
# 대량의 데이터를 load 작업 시
1. 테이블의 제약조건은 disable 상태로 변경한다.
2. sqlldr 를 통해서 direct path load 방식으로 로드한다.
3. 테이블의 제약조건을 enable 상태로 변경한다.
단, 제약조건 enable 상태로 변경 시에 제약조건에 위반되는 데이터를 찾아 변경해야한다.
| [문제] hr.employees 테이블에서 2005년 이전에 입사한 사원들의 employee_id, last_name, salary, hire_date 데이터를 추출해서 emp_before_2005.dat 파일로 저장해주세요. | |
| [oracle@oracle spool]$ pwd /home/oracle/spool [oracle@oracle spool]$ vi emp_before_2005.sql SET PAGESIZE 0 SET LINESIZE 200 SET ECHO OFF SET TERMOUT OFF SET TRIMSPOOL ON SET FEEDBACK OFF SPOOL emp_before_2005.dat SELECT employee_id||','||last_name||','||salary||','|| TO_CHAR(hire_date,'yyyy-mm-dd') FROM hr.employees WHERE hire_date < TO_DATE('2005-01-01','yyyy-mm-dd'); SPOOL OFF :wq SQL> @emp_before_2005.sql [oracle@oracle spool]$ cat emp_before_2005.dat |
|
| [문제] emp_before_2005.dat 파일에 있는 데이터를 SQL LOADER 를 이용해서 hr.emp_before_2005 테이블에 로드해주세요. | |
| create table hr.emp_before_2005( employee_id number, last_name varchar2(20), salary number, hire_date date); [oracle@oracle spool]$ vi emp_before_2005.ctl UNRECOVERABLE LOAD DATA INFILE emp_before_2005.dat INSERT INTO TABLE hr.emp_before_2005 FIELDS TERMINATED BY ',' (employee_id,last_name,salary,hire_date date 'yyyy-mm-dd') :wq [oracle@oracle spool]$ sqlldr hr/hr control=emp_before_2005.ctl direct=true Load completed - logical record count 24. [oracle@oracle spool]$ cat emp_before_2005.log ... Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- EMPLOYEE_ID FIRST * , O(") CHARACTER LAST_NAME NEXT * , O(") CHARACTER SALARY NEXT * , O(") CHARACTER HIRE_DATE NEXT * , O(") DATE yyyy-mm-dd Table HR.EMP_BEFORE_2005: 24 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. ... SELECT * FROM hr.emp_before_2005; EMPLOYEE_ID LAST_NAME SALARY HIRE_DAT ----------- -------------------- ---------- -------- 200 Whalen 16709 03/09/17 201 Hartstein 13000 04/02/17 203 Mavris 6500 02/06/07 ... |
|
| # 데이터 추출 vi emp_new.sql SET PAGESIZE 0 SET LINESIZE 200 SET ECHO OFF SET TERMOUT OFF SET TRIMSPOOL ON SET FEEDBACK OFF SPOOL emp_new.csv SELECT upper(last_name)||','||salary||','|| TO_CHAR(hire_date,'yyyy-mm-dd') FROM hr.employees; -- 데이터 추출 시 사원번호를 제외하였다. SPOOL OFF :wq SQL> @emp_new.sql [oracle@oracle spool]$ cat emp_new.csv DROP TABLE hr.emp_new PURGE; CREATE TABLE hr.emp_new( employee_id NUMBER, -- 테이블에는 사원번호가 있다! last_name VARCHAR2(20), salary NUMBER, hire_date DATE); |
| # sqlldr - sequence(max,1) max : 끝까지 만들어라 1 : 1부터 시작. vi emp_new.ctl UNRECOVERABLE LOAD DATA INFILE emp_new.csv INSERT INTO TABLE hr.emp_new FIELDS TERMINATED BY ',' (employee_id sequence(max,1), last_name, salary, hire_date date 'yyyy-mm-dd') :wq [oracle@oracle spool]$ sqlldr hr/hr control=emp_new.ctl direct=true Load completed - logical record count 106. select * from hr.emp_new; EMPLOYEE_ID LAST_NAME SALARY HIRE_DAT ----------- -------------------- ---------- -------- 1 OCONNELL 2600 07/06/21 2 GRANT 2600 08/01/13 3 WHALEN 16709 03/09/17 4 HARTSTEIN 13000 04/02/17 5 MAVRIS 6500 02/06/07 ... |
| # sqlldr - sequence(100,1) 100 : MAX안쓰면 100부터 시작 1 : +1 증가 단위 vi emp_new.ctl UNRECOVERABLE LOAD DATA INFILE emp_new.csv TRUNCATE INTO TABLE hr.emp_new FIELDS TERMINATED BY ',' (employee_id sequence(100,1), last_name, salary, hire_date date 'yyyy-mm-dd') :wq [oracle@oracle spool]$ sqlldr hr/hr control=emp_new.ctl direct=true Load completed - logical record count 106. select * from hr.emp_new; EMPLOYEE_ID LAST_NAME SALARY HIRE_DAT ----------- -------------------- ---------- -------- 100 OCONNELL 2600 07/06/21 101 GRANT 2600 08/01/13 102 WHALEN 16709 03/09/17 103 HARTSTEIN 13000 04/02/17 ... |
<< SYS SESSION >>
■ ORACLE NETWORK
1. Client - Server 환경(2-Tier)
client-server 네트워크는 client process(user process)가 server process에게 서비스를 요청하는 구조이다.
client 환경에서 구성하는 network 정보
server 환경에서 구성하는 network 정보
2. Client - middleware(application Tier) - server 환경(3-Tier)
middleware 환경에서 구성하는 network 정보
server 환경에서 구성하는 network 정보
■ Listener Process
- database server는 listener를 통해 client apllication에서 초기 연결을 수신한다.
- client 가 server에 대한 연결 요청을 하면 listener 그 정보를 받아서 server process를 생성해서 client가 사용할 수 있도록 한다.
client ----------> listener
<---------- server process
client → listener
↖ ↓
server process
# 기본적인 listener 정보
LISTENER 이름 LISTENER
PORT 1521
PROTOCOL TCP/IP, IPC
SID INSTANCE_NAME
HOST NAME HOST NAME
[oracle@oracle ~]$ cd $ORACLE_HOME/network/admin
[oracle@oracle admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@oracle admin]$ hostname
oracle
[oracle@oracle admin]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.56.102 oracle
[oracle@oracle admin]$ ps -ef | grep pmon
oracle 4069 1 0 Jan03 ? 00:00:09 ora_pmon_ora11g
oracle 26512 26150 0 15:38 pts/1 00:00:00 grep --color=auto pmon
[oracle@oracle spool]$ lsnrctl status
# pmon process가 listener한테 instance(services) 정보를 등록한다.
# dynamic 서비스 등록 (9i)
- 동적 서비스 등록은 오라클 인스턴스를 구성하는 프로세스 중 pmon 프로세스가 listener에게 인스턴스 서비스 등록을 한다.
- 기본적으로 pmon process는 TCP/IP의 기본 로컬 주소인 포트 1521에서 listener에게 서비스 등록을 한다.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
db_name.db_domain = servie_names = GLOBAL_DBNAME
SQL> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string ora11g
↑
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ora11g
+
SQL> show parameter db_domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string ora11g
싱글은 servie_names = instance_name
[oracle@oracle admin]$ pwd
/u01/app/oracle/product/11.2.0.4/db_1/network/admin
= $ORACLE_HOME/network/admin
| # 방법1. 기본 포트가 아닌 다른 포트를 이용해서 동적 서비스 등록하기 | |
| [oracle@oracle admin]$ lsnrctl stop [oracle@oracle admin]$ vi listener.ora port=1521 -> 1522 로 수정 [oracle@oracle admin]$ lsnrctl start ... Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) The listener supports no services The command completed successfully >> 리스너 no services SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string SELECT * FROM v$parameter WHERE name = 'local_listener'; ISSES_MODIFIABLE : FALSE ISSYS_MODIFIABLE : IMMEDIATE SQL> ALTER SYSTEM SET local_listener = '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1522)))'; System altered. SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1522))) [oracle@oracle admin]$ lsnrctl status ... Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "ora11g" has 1 instance(s). Instance "ora11g", status READY, has 1 handler(s) for this service... Service "ora11gXDB" has 1 instance(s). Instance "ora11g", status READY, has 1 handler(s) for this service... The command completed successfully >>> 연결되었다 sqldeveloper에서 포트 1522로 수정하여 사용 * 이미 접속한 상태에서는 lsnrctl 관계없이 사용가능하다. |
|
| # 복구 SQL> ALTER SYSTEM SET local_listener = ''; SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string [oracle@oracle admin]$ vi listener.ora 포트 수정 -> 기본 1521 [oracle@oracle admin]$ lsnrctl reload -- 적용이 잘 안된다. 또는 [oracle@oracle admin]$ lsnrctl stop [oracle@oracle admin]$ lsnrctl start [oracle@oracle admin]$ lsnrctl status ... Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "ora11g" has 1 instance(s). Instance "ora11g", status READY, has 1 handler(s) for this service... Service "ora11gXDB" has 1 instance(s). Instance "ora11g", status READY, has 1 handler(s) for this service... The command completed successfully |
|
| # 방법2. 기본 포트가 아닌 다른 포트를 이용해서 동적 서비스 등록하기 | |
| [oracle@oracle admin]$ lsnrctl stop [oracle@oracle admin]$ vi listener.ora port=1521 -> 1522 로 수정 # 클라이언트에서 구성하는 파라미터 파일 [oracle@oracle admin]$ vi tnsnames.ora LISTENER = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1522)) 맨 밑에 추가 SQL> ALTER SYSTEM SET local_listener ='LISTENER'; SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string LISTENER [oracle@oracle admin]$ lsnrctl start TNS-01106: Listener using listener name LISTENER has already been started [oracle@oracle admin]$ lsnrctl status ... 시간소요 Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "ora11g" has 1 instance(s). Instance "ora11g", status READY, has 1 handler(s) for this service... Service "ora11gXDB" has 1 instance(s). Instance "ora11g", status READY, has 1 handler(s) for this service... The command completed successfully |
|
| # 복구 [oracle@oracle admin]$ lsnrctl stop [oracle@oracle admin]$ vi listener.ora port=1521 [oracle@oracle admin]$ vi tnsnames.ora LISTENER = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1522)) 삭제 SQL> ALTER SYSTEM SET local_listener =''; SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string [oracle@oracle admin]$ lsnrctl start TNS-01106: Listener using listener name LISTENER has already been started [oracle@oracle admin]$ lsnrctl status ... 시간소요 Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "ora11g" has 1 instance(s). Instance "ora11g", status READY, has 1 handler(s) for this service... Service "ora11gXDB" has 1 instance(s). Instance "ora11g", status READY, has 1 handler(s) for this service... The command completed successfully |
|
| # 호스트 수정 | |
| [oracle@oracle admin]$ ifconfig ... enp0s8: flags=4163<up,broadcast,running,multicast> mtu 1500</up,broadcast,running,multicast> inet 192.168.56.102 netmask 255.255.255.0 broadcast 192.168.56.255 ... [oracle@oracle admin]$ vi listener.ora HOST = oracle -> 192.168.56.102 수정 [oracle@oracle admin]$ lsnrctl reload -- 잘 안된다 또는 [oracle@oracle admin]$ lsnrctl stop [oracle@oracle admin]$ lsnrctl start # 빨리 적용해라 SQL> ALTER SYSTEM REGISTER; [oracle@oracle admin]$ lsnrctl status ... Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.102)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "ora11g" has 1 instance(s). Instance "ora11g", status READY, has 1 handler(s) for this service... Service "ora11gXDB" has 1 instance(s). Instance "ora11g", status READY, has 1 handler(s) for this service... The command completed successfully |
|
| # 복구 [oracle@oracle admin]$ vi listener.ora HOST = oracle 수정 [oracle@oracle admin]$ lsnrctl stop [oracle@oracle admin]$ lsnrctl start # 빨리 적용해라 SQL> ALTER SYSTEM REGISTER; [oracle@oracle admin]$ lsnrctl status ... Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "ora11g" has 1 instance(s). Instance "ora11g", status READY, has 1 handler(s) for this service... Service "ora11gXDB" has 1 instance(s). Instance "ora11g", status READY, has 1 handler(s) for this service... The command completed successfully |
|
'Oracle 11g > Oracle DB' 카테고리의 다른 글
| 59 ORACLE Listener, DB Link, Server (1) | 2024.01.05 |
|---|---|
| 57 ORACLE Constraints, Spool, SQL Loader (1) | 2024.01.03 |
| 56-2 ORACLE 종속성 (0) | 2023.12.28 |
| 55 ORACLE Audit, Trigger (1) | 2023.12.27 |
| 54-2 ORACLE Audit (1) | 2023.12.26 |