데이터이관시 제약조건, SPOOL, SQL LOADER , ORACLE NETWORK

YoonSeo Park ·2024년 1월 3일
SQL * Loadermigrationspool
0

Oracle Architecture

목록 보기
6/11

2024/01/03

■ 제약조건 체크 여부

select * from user_constraints

DEFERRABLE

  • NOT DEFERRABLE
    : 지연할 수 없다.(DML을 수행하는 순간 즉시 체크), 기본값
  • DEFERRABLE
    : 지연가능 할 수 있다.

DEFERRED

  • INITIALLY IMMEDIATE
    : DML문 완료되면 제약조건을 검사한다.(기본값)
  • INITIALLY DEFERRED
    : DML문에 대한 COMMIT문을 수행 할때 제약조건 검사를 수행한다.

create 제약조건

create table emp_sal(
    salary number constraint sal_ck check(salary>100) deferrable initially immediate,
    bonus number constraint bounus_ck check(bonus>0) deferrable initially deferred
);

select * from user_constraints where table_name = 'EMP_SAL';

insert into emp_sal(salary,bonus) values(90,5); -- 오류 salary : initially immediate

insert into emp_sal(salary,bonus) values(101,-5);

commit; -- 오류 bonus : initially deferred
---
commit
오류 보고 -
ORA-02091: 트랜잭션이 롤백되었습니다
ORA-02290: 체크 제약조건(HR.BOUNUS_CK)이 위배되었습니다
02091. 00000 -  "transaction rolled back"
*Cause:    Also see error 2092. If the transaction is aborted at a remote
           site then you will only see 2091; if aborted at host then you will
           see 2092 and 2091.
*Action:   Add rollback segment and retry the transaction.
---
  • 현재 세션에서 모든 제약조건을 지연가능하도록 수행 하겠다는 의미 ( 트랜잭션이 끝나면 자동으로 해지 된다. )
  • not deferrable 로 설정되어 있을 경우 불가능하다.
set constraints all deferred;
또는
alter session set constraint = deferred;
---
Constraints ALL을(를) 성공했습니다.
insert into emp_sal(salary,bonus) values(90,5); 

insert into emp_sal(salary,bonus) values(101,-5);

commit; -- 오류
  • 현재 세션에서 모든 제약조건을 즉시 수행 하겠다는 의미 ( 트랜잭션이 끝나면 자동으로 해지 된다. )
set constraints all immediate;
또는
alter session set constraint = immediate;
---
Constraints ALL을(를) 성공했습니다.
  • bounus_ck 제약조건 여부 체크를 commit 시에 체크하도록 설정
set constraints bounus_ck deferred;
  • create 제약조건 (기본값)으로 하고, all deferred 으로 변경
drop table emp_sal purge;

create table emp_sal(
    salary number constraint sal_ck check(salary>100) not deferrable initially immediate,
    bonus number constraint bonus_ck check(bonus>0) not deferrable initially immediate
);

set constraints all deferred;
insert into emp_sal(salary,bonus) values(90,5); -- 오류
insert into emp_sal(salary,bonus) values(101,-5); -- 오류

오류 이유
: not deferrable로 설정되어 있을 경우 set constraints all deferred 설정이 안된다.

▶ 데이터가 들어가 있는 테이블에 제약조건 추가할때 시나리오

  • 현장에서는 테이블을 구성하고 제약조건없이 대량의 데이터 입력을 먼저한다 (제약조건을 구성하고 하면 안정적이지만 넣을때마다 체크하므로 속도가 떨어진다.)
    , 그 후에 제약조건을 추가한다.

● 제약조건이있는 대량의 데이터 이행 할때 시나리오

▶ 1. 테이블생성, 데이터 추가

drop table test purge;
create table test(id number,name char(10), sal number);

insert into test(id,name,sal) values(1,'a',1000);
insert into test(id,name,sal) values(2,'b',100);
insert into test(id,name,sal) values(1,'c',2000);
commit;

alter table test add constraint test_id_pk primary key(id);
--- 오류
alter table test add constraint test_id_pk primary key(id)
오류 보고 -
ORA-02437: (HR.TEST_ID_PK)을 검증할 수 없습니다 - 잘못된 기본 키입니다
02437. 00000 -  "cannot validate (%s.%s) - primary key violated"
*Cause:    attempted to validate a primary key with duplicate values or null
           values.
*Action:   remove the duplicates and null values before enabling a primary
           key.
---

▶ 2. 제약 조건을 생성하지만, disable
: DISABLED, NOT VALIDATED 세트이다.
사용하지않고, 적용하지 않겠다.

alter table test add constraint test_id_pk primary key(id) disable;

select * from user_constraints where table_name = 'TEST';

▶ 3. 제약조건을 적용시키기전에 적용안된 데이터를 담을 테이블 생성 (스크립트 사용해도 되고, exceptions 구조처럼 테이블을 만들어줘도 된다.)

SQL> @$ORACLE_HOME/rdbms/admin/utlexpt1

Table created.

SQL> desc exceptions
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROW_ID                                             ROWID
 OWNER                                              VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 CONSTRAINT                                         VARCHAR2(30)
 
SQL> select rowid, name, id,sal from test;

ROWID              NAME               ID        SAL
------------------ ---------- ---------- ----------
AAAVpFAAEAAAAJWAAA a                   1       1000
AAAVpFAAEAAAAJWAAB b                   2        100
AAAVpFAAEAAAAJWAAC c                   1       2000

AAAVpF : data object id (6)
AAE : file id (3)
AAAAJW : block id (6)
AAA : row slot (3)

  • 가장빠른 조회 방법 (rowid를 이용)
select rowid from hr.employees;

select *
from hr.employees
where rowid = 'AAAVPhAAFAAAADPAAV';
  • 확인
select * from user_objects where object_name = 'TEST';
select * from user_segments where segment_name = 'TEST';
[sys session]
select * from dba_data_files;
select * from dba_segments where segment_name = 'TEST';
select * from dba_extents where segment_name = 'TEST';
  • 데이터 넣기
insert into test(id,name,sal) values(5,'j',3000);
commit;
select * from test;
select * from user_constraints where table_name = 'TEST';

▶ 4. 제약조건 enable 로 변경하고 exceptions 처리, 확인후 변경

  • 제약조건 enable로 변경
alter table test enable constraint test_id_pk exceptions into exceptions;
select * from exceptions;
  • 처음부터 제약조건 생성시 disable 하지 않고, exceptions에 넣어주는게 더 간편하다.
alter table test add constraint test_id_pk primary key(id) exceptions into exceptions;
  • 확인
select rowid, t.* from test t
where rowid in (select row_id from exceptions);
  • 확인후 문제되는것 변경 (업무팀에서 하는일)
update test
set id = 6
where rowid = 'AAAVpFAAEAAAAJWAAC';

▶ 5. exceptions 테이블 데이터 삭제하고, 다시 enable로 변경후 enable인지 확인

  • exceptions 테이블 truncate후 enable로 변경
truncate table exceptions;
alter table test enable constraint test_id_pk exceptions into exceptions;
또는
alter table test add constraint test_id_pk primary key(id) exceptions into exceptions;
  • 확인
select * from exceptions;
select * from user_constraints where table_name = 'TEST';

▶ enable novalidate 성격

: 현재 데이터는 검증하지 않고, 새로운 데이터부터 검증

alter table test add constraint test_sal_ck check(sal>=1000) enable novalidate;
select * from user_constraints where table_name = 'TEST';

insert into test(id,name,sal) values(10,'k',500);
--- 오류
insert into test(id,name,sal) values(10,'k',500)
오류 보고 -
ORA-02290: 체크 제약조건(HR.TEST_SAL_CK)이 위배되었습니다
---

▶ enable novalidate -> enable validate 변경 시나리오

truncate table exceptions;
alter table test enable constraint test_sal_ck exceptions into exceptions;
-- enable = enable validate
select * from exceptions;

select rowid, t.* from test t
where rowid in (select row_id from exceptions);

update test
set sal = 1100
where rowid = 'AAAVpFAAEAAAAJWAAB';

truncate table exceptions;
alter table test enable constraint test_sal_ck exceptions into exceptions;
select * from user_constraints where table_name = 'TEST';

▶ disable validate 성격

: DML 불허

  • 특정테이블의 DML 불허해야 할 경우 일일히 DML 권한부여 회수를 하기 보다는 어느 한개의 제약조건을 disable validate 로 변경한다.
alter table test disable validate constraint test_sal_ck;

■ 제약조건 상태

  • disable novalidate (기본값)
    : 제약조건을 생성은 하지만 제약조건에 대한 체크는 하지 않겠습니다. 즉 새롭게 들어오는 데이터 기존 데이터에 대해서 제약조건 검증하지 않겠습니다.
    , 데이터 이관작업할때 disable novalidate 변경후 이관한 다음 enable로 변경

  • disable validate
    : 제약조건을 생성하지만 새로운 데이터에 대해서 DML은 불허한다.

  • enable validate (기본값)
    : 제약조건을 생성하면서 새로운 데이터 기존 데이터 다 검증한다.

  • enable novalidate
    : 제약조건을 생성하면서 새로운 데이터만 검증한다. 기존 데이터에 대해서는 검증하지 않는다.

    하지만 primary key와 unique 제약조건은 enable novalidate 성격을 수행하더라도 기존 데이터도 검증한다.

따라서 제약조건 걸 상황이 오면 enable novalidate 로 변경후 나중에 enable validate 변경후 exceptions를 업무팀에 준다.

■ SPOOL

: sqldeveloper에서 csv파일로 export 하는것처럼 만들어주기.

  • 파일 만들어주기
[oracle@oracle ~]$ vi emp_sal.sql

set pagesize 0 # : 한페이지로 지정할 라인 수, 0으로 설정하면 컬럼은 출력하지 않습니다.
set linesize 200 # : 한라인에 표시할 글자의 수
set echo off # : 실행하는 SQL문을 출력하지 않습니다.
set termout off # : SQL문으로 실행한 결과를 표시하지 않습니다.
set trimspool on # : 라인뒤 공백을 제거
set feedback off # : SQL문의 실행한 결과 건수를 표시하지 않습니다. 

spool emp_sal.csv # : SQL문의 실행결과를 spool file로 생성한다.

-- 이 부분만 변경해주면 된다. csv파일처럼 쉼표를 넣어준다.
select employee_id||','||last_name||','||first_name||','||salary||','||department_id as emp_info
from hr.employees;

spool off # : SQL문의 실행결과를 spool file로 생성을 멈추겠다.
  • sql문 실행
[oracle@oracle spool]$ pwd
/home/oracle/spool

[oracle@oracle spool]$ sqlplus hr/hr
SQL> ! pwd
/home/oracle/spool -- sqlplus한 디렉토리가 바로 현재 위치가된다.

SQL> @emp_sal.sql

■ SQL LOADER (csv파일 -> 테이블에 이관 작업)

: 외부 파일에서 오라클 데이터베이스의 테이블로 data를 load(insert) 하는 프로그램

  • 형태만 있는 테이블 생성
drop table emp_sal purge;

create table emp_sal
as
select employee_id,last_name,first_name,salary,department_id
from hr.employees
where 1=2;

select * from emp_sal;
  • 컨트롤 파일 만들기
[oracle@oracle spool]$ vi emp_sal.ctl

UNRECOVERABLE LOAD DATA # : 리두정보를 담지않는다
INFILE emp_sal.csv

INSERT INTO TABLE hr.emp_sal
FIELDS TERMINATED BY ','
(employee_id,last_name,first_name,salary,department_id)
  • 컨트롤 파일 실행
[oracle@oracle spool]$ sqlldr hr/hr control=emp_sal.ctl direct=true

SQL*Loader: Release 11.2.0.4.0 - Production on Sun Dec 24 02:42:06 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Load completed - logical record count 107.
drop table test purge;

create table hr.test(id number constraint test_id_pk primary key,
                        name varchar2(30), phone varchar2(20));



[oracle@oracle spool]$ vi insa.ctl

UNRECOVERABLE LOAD DATA
INFILE *
INSERT INTO TABLE hr.test
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
# begindata 의 "" 없애주고 넣어준다.
(ID, NAME, PHONE)
BEGINDATA
1,"JAMES","010-1000-0001"
2,"ORACLE","010-1122-2222"
3,"KIM","010-9999-2222"

[oracle@oracle spool]$ sqlldr hr/hr control=insa.ctl direct=true

SQL*Loader: Release 11.2.0.4.0 - Production on Sun Dec 24 02:58:19 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Load completed - logical record count 3.
  • INSERT
    : 비어 있는 테이블에 입력할때 사용
  • REPLACE
    : 기존행을 delete 한 후 데이터를 입력한다.
  • TRUNCATE
    : 테이블을 TRUNCATE 한 후 새로운 데이터를 입력한다.
  • APPEND
    : 새로운 데이터를 기존 테이블의 추가한다.
UNRECOVERABLE LOAD DATA
INFILE *
# INSERT/REPLACE/TRUNCATE/APPEND
INTO TABLE hr.test
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(ID, NAME, PHONE)
BEGINDATA
1,"JAMES","010-1000-0001"
2,"ORACLE","010-1122-2222"
3,"KIM","010-9999-2222"
  • APPEND
    : 새로운 데이터를 기존 테이블에 제약조건 상관 없이 추가를 한다. 따라서 제약조건이 망가지는 것을 주의를 해줘야한다.
  • 제약조건에 맞지 않는 데이터 삭제 시나리오
alter table test disable constraint test_id_pk;

truncate table exceptions;
alter table test enable constraint test_id_pk exceptions into exceptions;

select rowid, t.* from test t
where rowid in (select row_id from exceptions);

delete from test
where rowid in (
'AAAVpgAAEAAAAJUAAA',
'AAAVpgAAEAAAAJUAAB',
'AAAVpgAAEAAAAJUAAC'
);

truncate table exceptions;
alter table test enable constraint test_id_pk exceptions into exceptions;

SELECT * FROM user_indexes WHERE table_name = 'TEST';

2023/01/04

  • bad 파일
    : bad 파일은 로드 작업 중에 오류로 처리된 레코드들이 기록되는 파일입니다. 오류가 발생한 레코드들은 bad 파일에 저장되며, 이 파일을 확인하여 어떤 레코드에서 문제가 발생했는지를 확인할 수 있습니다.
  • log 파일
    : log 파일은 sqlldr 작업의 세부 정보, 오류 메시지, 성공 또는 실패한 레코드의 통계 등을 기록하는 데 사용됩니다. 로그 파일을 확인하면 데이터 로드 작업에 대한 자세한 정보를 파악할 수 있습니다.
  • TRAILING NULLCOLS: 널 값도 포함해서 생성
UNRECOVERABLE LOAD DATA
INFILE emp_sal.csv
TRUNCATE
INTO TABLE hr.emp_sal
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(employee_id, last_name, first_name, salary, department_id)
[oracle@oracle spool]$ vi insa.ctl

LOAD DATA
INFILE insa.dat
INSERT
INTO TABLE hr.test
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(ID, NAME, PHONE)
[oracle@oracle spool]$ vi insa.dat

1,"JAMES","010-1000-0001"
2,"ORACLE","010-1122-2222"
3,"KIM","010-9999-2222"
3,"SCOTT","010-2229-2222"

▶ Conventional load

: 로우 단위로 데이터베이스로 적재하는 방법입니다.
데이터 파일의 각 로우를 읽어서 로우를 삽입(insert)합니다.
인덱스 및 제약 조건이 존재하면 그에 따라 검사하고 갱신합니다.

  • 장점
    : 제약조건을 체크해서 문제되는 데이터는 bad file에 입력해준다. 품질이 좋은 데이터만 입력된다.
  • 단점
    : 수행속도가 느리다, 리두 발생을 한다.
sqlldr hr/hr control=insa.ctl

▶ Direct path load

: 대량의 데이터를 효율적으로 로드하는 방법으로, 데이터를 버퍼 캐시를 거치지 않고 직접 데이터 파일에서 데이터베이스로 로드합니다.
데이터 파일의 내용을 일시적으로 테이블의 임시 세그먼트에 저장하고, 이후에 대량의 블록을 한 번에 데이터베이스에 적재합니다.

  • 장점
    : 수행속도가 빠르다.
    테이블에 저장해야할 데이터를 메모리에서 모양을 만든 후 디스크에 save한다.
  • 단점
    : 제약조건을 체크하지 않는다.
sqlldr hr/hr control=insa.ctl direct=true

■ 제약조건을 어기고 Direct path load 할 경우

  • 이 방법으로 제약조건 index의 상태를 봐야한다. unusable 상태이면 사용불가하다.
    SELECT status from dba_indexes where table_name ='TEST';
  • 제약조건은 VALID 상태이지만 사용할수 없다. (따라서 이걸로 보면 안된다)
    select status from dba_objects where object_name = 'TEST_ID_PK';

▶ index 상태가 unusable 상태일 경우

  1. 기존의 index scan이 table full scan 으로 바뀐다.
select * from hr.test where id=1;
  1. INSERT가 안된다.
insert into hr.test(id,name,phone) values(4,'PARK','010-9991-1111');
--- 오류
insert into hr.test(id,name,phone) values(4,'PARK','010-9991-1111')
오류 보고 -
ORA-01502: 인덱스 'HR.TEST_ID_PK'또는 인덱스 분할영역은 사용할 수 없은 상태입니다
  1. delete가 안된다.
delete from hr.test where name='SCOTT';
--- 오류
delete from hr.test where name='SCOTT'
오류 보고 -
ORA-01502: 인덱스 'HR.TEST_ID_PK'또는 인덱스 분할영역은 사용할 수 없은 상태입니다
  1. update가 안된다.
update hr.test
set id = 4
where name ='SCOTT';
-- 오류
update hr.test
set id = 4
where name ='SCOTT'
오류 보고 -
ORA-01502: 인덱스 'HR.TEST_ID_PK'또는 인덱스 분할영역은 사용할 수 없은 상태입니다

▶ 해결방법

  1. 제약조건 disable로 변경
alter table hr.test disable constraint test_id_pk;
select * from dba_constraints where owner ='HR' and table_name = 'TEST';
select * from dba_indexes where owner ='HR' and table_name = 'TEST';
  1. exceptions 테이블 만들기 (스크립트 사용)
SQL> @$ORACLE_HOME/rdbms/admin/utlexpt1
Table created.
  1. enable로 변경, exceptions 테이블에 로드
truncate table sys.exceptions;
alter table hr.test enable constraint test_id_pk exceptions into sys.exceptions;
-- 주의 : sys 세션에서 실행하여도 exceptions 소유자를 쓰지 않으면 hr의 exceptions 테이블로 로드해진다.
  1. rowid 확인
select rowid, t.* 
from hr.test t
where rowid in (select row_id from exceptions);
  1. 문제되는것 변경 (업무팀에 전달)
update hr.test
set id = 4
where rowid ='AAAVpzAAEAAAAJTAAD';
commit;
  1. enable로 변경
truncate table sys.exceptions;
alter table hr.test enable constraint test_id_pk exceptions into sys.exceptions;
  1. 확인
select * from dba_constraints where owner ='HR' and table_name = 'TEST';
select * from dba_indexes where owner ='HR' and table_name = 'TEST';
select * from dba_ind_columns where index_owner = 'HR' and table_name = 'TEST';

▶ 대량의 데이터를 load 작업시

  1. 테이블의 제약조건은 disable 상태로 변경
  2. sqlldr 를 통해서 direct path load 방식으로 로드
  3. 테이블의 제약조건은 enable 상태로 변경
    ( 단, 제약조건 enable 상태로 변경시에 제약조건에 위반되는 데이터를 찾아서 변경해야 한다 )

문제

[문제1]
hr.employees 테이블에서 2005년 이전에 입사한 사원들의
employee_id, last_name, salary, hire_date 데이터를 추출해서
emp_before_2005.csv 파일로 저장해주세요
emp_before_2005.dat 파일에 있는 데이터를 SQL LOADER를 이용해서
hr.emp_before_2005 테이블에 로드해주세요

  • 테이블 생성
create table hr.emp_before_2005(
	employee_id number,
	last_name varchar2(30),
	salary number,
	hire_date date);
  • spool (sql파일 만들기)
[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.csv

select employee_id||','||last_name||','||salary||','||hire_date
from hr.employees
where hire_date < to_date('2005/01/01','yyyy/mm/dd');

spool off

주의 만약 to_char(hire_date,'yyyy/mm/dd')
이면 파일에 로드가 안된다 따라서 컨트롤파일을 (hire_date date 'yyyy/mm/dd') 형식으로 만든다.

  • 실행
SQL> ! pwd
/home/oracle/spool

SQL> @emp_before_2005.sql
  • 컨트롤 파일 만들기
[oracle@oracle spool]$ vi emp_before_2005.ctl
UNRECOVERABLE LOAD DATA
INFILE emp_before_2005.csv
TRUNCATE
INTO TABLE hr.emp_before_2005
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(employee_id, last_name, salary, hire_date)
  • sql loader
[oracle@oracle spool]$ sqlldr hr/hr control=emp_before_2005.ctl direct=true
  • 확인
SQL> select * from hr.emp_before_2005;

▶ 데이터 로드할때 컬럼을 시퀀스하게 넣고 싶을때

UNRECOVERABLE LOAD DATA
INFILE emp_new.csv
TRUNCATE
INTO TABLE emp_new
FIELDS TERMINATED BY ','
(employee_id sequence(max,1), last_name, salary, hire_date date 'yyyy/mm/dd')

sequence(max,1) : 1부터 끝까지
sequence(max,1) : 100부터 시작 1씩증가

■ ORACLE NETWORK

  1. Client - Server 환경 (2-Tier)
    : client - server 네트워크는 client process (user process) 가 server process에서 서비스를 요청하는 구조입니다.

client 환경에서 구성하는 network 정보
server 환경에서 구성하는 network 정보

  1. Client - middleware(application tier) - server 환경 (3-Tier)

middleware 환경에서 구성하는 network 정보
server 환경에서 구성하는 network 정보

■ listener process

: db server 는 listener 를 통해 client application 에서 초기 연결을 수신한다.

  • client 가 server 에 대한 연결 요청을 하면 listener 그 정보를 받아서 server process를 생성해서 client 사용할 수 있도록 한다.

기본적으로 listener 정보

  • listener name
    : LISTENER
  • PORT
    : 1521
  • PROTOCOL
    : TCP/IP
  • SID
    : Oracle 데이터베이스의 SID, 예를 들면 ORCL
  • HOST NAME
    : HOST 이름
[oracle@oracle spool]$ cd $ORACLE_HOME/network/admin
[oracle@oracle admin]$ pwd
/u01/app/oracle/product/11.2.0.4/db_1/network/admin
[oracle@oracle admin]$ ls
listener.ora  samples  shrept.lst  tnsnames.ora
[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]$ ps -ef | grep pmon
oracle    6240     1  0 Dec23 ?        00:00:11 ora_pmon_ora11g
oracle   27711 24731  0 09:42 pts/1    00:00:00 grep --color=auto pmon
  • 리스너 종료 (이미 연결이 되어있는것은 서버 프로세스를 만들어서 연결이 된다.)
[oracle@oracle admin]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-DEC-2023 09:44:09

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521)))
The command completed successfully
  • pmon process 가 listener 한테 instance (services) 정보를 등록한다.
  • pmon
    : 데이터베이스 인스턴스의 상태를 모니터링하고 관리하는 역할을 수행합니다.

dynamic 서비스 등록
: 동적 서비스 등록은 오라클 인스턴스를 구성하는 프로세스중 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 = service_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

▶ 기본포트가 아닌 다른 포트를 이용해서 동적 서비스 등록하는 방법 1

  1. lsnrctl stop
[oracle@oracle admin]$ lsnrctl stop
  1. 리스너 포트 수정
[oracle@oracle admin]$ cd $ORACLE_HOME/network/admin
[oracle@oracle admin]$ pwd
/u01/app/oracle/product/11.2.0.4/db_1/network/admin
[oracle@oracle admin]$ ls
listener.ora  samples  shrept.lst  tnsnames.ora
[oracle@oracle admin]$ vi 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 = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
  1. lsnrctl start
[oracle@oracle admin]$ lsnrctl start
  1. lsnrctl 정보 확인
SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string

[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 = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
  1. 포트번호 변경 ( 주의:마지막 괄호확인)
SQL> alter system set local_listener = '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1522)))';

System altered.
  1. 확인
SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (DESCRIPTION = (ADDRESS = (PRO
                                                 TOCOL = TCP)(HOST = oracle)(PO
                                                 RT = 1522)))

▶ 기본포트가 아닌 다른 포트를 이용해서 동적 서비스 등록하는 방법 2

  1. lsnrctl stop
[oracle@oracle admin]$ lsnrctl stop
  1. 리스너 포트 수정
[oracle@oracle admin]$ cd $ORACLE_HOME/network/admin
[oracle@oracle admin]$ pwd
/u01/app/oracle/product/11.2.0.4/db_1/network/admin
[oracle@oracle admin]$ ls
listener.ora  samples  shrept.lst  tnsnames.ora
[oracle@oracle admin]$ vi 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 = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
  1. vi tnsames.ora 에 마지막줄 추가
[oracle@oracle admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORA11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11g)
    )
  )
LISTENER = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1522))
  1. lsnrctl start
[oracle@oracle admin]$ lsnrctl start
  1. lsnrctl 정보 확인
SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string

[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 = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
  1. 포트번호 변경 ( 주의:마지막 괄호확인)
SQL>  alter system set local_listener = 'LISTENER';

System altered.
  1. 확인
SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER

▶ 기본 포트(1521) 로 변경

  • 기본포트로 변경
[oracle@oracle admin]$ sqlplus / as sysdba
SQL> alter system set local_listener = '';
  • 확인
SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string
  • 리스너 stop,start
[oracle@oracle admin]$ lsnrctl stop
[oracle@oracle admin]$ lsnrctl start

▶ 호스트이름=IP주소 변경

: etc/host 에 등록했으므로 IP주소, HOST이름은 같다

  • vi listener.ora에서 HOST 변경
[oracle@oracle admin]$ vi 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 = 192.168.56.104)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
  • 리스너 stop,start
[oracle@oracle admin]$ lsnrctl stop
[oracle@oracle admin]$ lsnrctl start
  • 서비스가 더디게 될때, 백그라운드 푸시
SQL> alter system register;

2023/01/05

▶ static 서비스 등록

: listener.ora 메뉴얼하게 서비스 등록해준다.

  • listener.ora 파일은 어디있나?
    : $ORACLE_HOME/network/admin
  • listener.ora 편집
[oracle@oracle admin]$ vi listener.ora

SID_LIST_L01 =
	(SID_LIST =
		(SID_DESC =
			(GLOBAL_DBNAME = ora11g)
			(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
			(SID_NAME = ora11g)
	)


L01 =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1522)))

ADR_BASE_LISTENER = /u01/app/oracle
  • static 하게 구성한 listener 시작
[oracle@oracle admin]$ lsnrctl start L01
  • static 하게 구성한 listener 상태확인
[oracle@oracle admin]$ lsnrctl status L01
  • static 하게 구성한 listener 중지
[oracle@oracle admin]$ lsnrctl stop L01
  • 기본 listener 상태확인
[oracle@oracle admin]$ lsnrctl status
  • 기본 listener 중지
[oracle@oracle admin]$ lsnrctl stop

■ client 환경에서 오라클에 접속

  1. 간단한 연결 (Easy connect)
  • 기본적으로 활성화
  • 클라이언트 측 구성이 필요 없다.
  • TCP/IP 만 지원
  1. 로컬 이름 지정
  • 클라이언트 측 이름 분석(names resolution) 필요
  • 모든 oracle net 프로토콜 지원
  • $ORACLE_HOME/network/admin/tnsnames.ora

▶ TNS 만들기 (로컬 이름 지정)

  • tnsnames.ora 파일 경로
C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora
  • tnsnames.ora 파일에 ora11g 추가
XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-6FUPLFQ)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

ora11g =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1522))
    (CONNECT_DATA =
      (SERVICE_NAME = ora11g)
    )
  )



EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
    ) 
    (CONNECT_DATA = 
      (SID = CLRExtProc) 
      (PRESENTATION = RO) 
    ) 
  )
  • sqldeveloper에서 접속

▶ 데이터베이스 링크

: 데이터베이스 링크는 다른 데이터베이스의 객체에 액세스할 수 있도록 하는 데이터베이스 객체입니다.

● 1. client XE DB에 tnsnames.ora 내용 추가

$ORACLE_HOME/network/admin/tnsnames.ora

ora11g =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1522))
    (CONNECT_DATA =
      (SERVICE_NAME = ora11g)
    )
  )

● 2. XE db에서 remote ora11g로 접속

  • ora11g 버전 (TNS로 접속)
    C:\Users\itwill>sqlplus hr/hr@ora11g
    SQL> conn hr/hr@ora11g
  • XE버전으로 접속
    C:\Users\itwill>sqlplus hr/hr

● 3. xe에 있는 hr유저에서 시스템 권한 확인

C:\Users\itwill>sqlplus hr/hr

SQL> select * from session_privs;

PRIVILEGE
--------------------------------------------------------------------------------
...
CREATE DATABASE LINK
...

● 4. xe에 있는 hr 에 데이터베이스 링크를 생성

  • DB링크 생성 (ora11g의 hr 유저)
CREATE DATABASE LINK ora11g_hr
CONNECT TO hr identified by hr
USING 'ora11g'; -- tns정보

SELECT * FROM user_db_links;

SELECT * FROM departments@ora11g_hr;
  • DB링크 생성 (ora11g의 insa01 유저)
CREATE DATABASE LINK ora11g_insa01
CONNECT TO insa01 identified by oracle
USING 'ora11g'; -- tns정보

SELECT * FROM user_db_links;

SELECT * FROM dept@ora11g_insa01;
  • XE db의 테이블과 다른 db 테이블을 DB링크를 통해서 조인하기
select e.employee_id, e.last_name, d.department_name
from employees e, dept@ora11g_insa01 d
where e.department_id = d.department_id
and e.job_id = 'AD_VP';
  • DB LINK 생성
CREATE DATABASE LINK ora11g_insa01
CONNECT TO insa01 identified by oracle
USING 'ora11g'; -- tns정보
  • DB LINK 삭제
DROP DATABASE LINK ora11g_insa01;

● 5. XE dba 로 접속해서 PUBLIC DATABASE LINK 생성

> sqlplus / as sysdba

  • PUBLIC DB 링크 생성
CREATE PUBLIC DATABASE LINK ora11g_insa01
CONNECT TO insa01 identified by oracle
USING 'ora11g';

SELECT * FROM dba_db_links;

● 6. XE hr로 접속

SELECT * FROM all_db_links;
select e.employee_id, e.last_name, d.department_name
from employees e, dept@ora11g_insa01 d
where e.department_id = d.department_id
and e.job_id = 'AD_VP';

● 7. XE dba로 접속해서 public synonym 생성

  • PUBLIC SYNOYM 생성
CREATE PUBLIC SYNONYM ora11g_dept FOR dept@ora11g_insa01;
  • 확인
SELECT * FROM dba_synonyms WHERE synonym_name = 'ORA11G_DEPT';

● 8. XE hr로 접속해서 synonym 확인

SELECT * FROM all_synonyms WHERE synonym_name = 'ORA11G_DEPT';
select e.employee_id, e.last_name, d.department_name
from employees e, ora11g_dept d
where e.department_id = d.department_id
and e.job_id = 'AD_VP';

● 9. XE dba로 접속해서

  • PUBLIC synonym 삭제
DROP PUBLIC SYNONYM ora11g_dept;
  • PUBLIC DB 링크 삭제
DROP PUBLIC DATABASE LINK ora11g_insa01;

■ server 구성

▶ 1. dedicated server process

  • user process 와 server process 가 1:1 환경으로 구성
  • 하나의 server process 가 하나의 user process 만 처리하는 프로세스

dedicated server 환경에서는 server process 안에 PGA안에 UGA가 있다.

PGA (Program Global Area)

  • stack space (바인드 변수 생성)
    UGA (User Global Area) (pga안에 uga가 있다.)
    • user session data
    • cursor state

▶ 2. shared server process

  • user process 와 server process 가 N:1 환경으로 구성
  • 하나의 server process 가 여러 user process 를 처리하는 프로세스
  • user process 는 dispatcher 와 연결하는 방식
  • pmon 프로세스는 dispatcher 정보를 listener 한테 등록한다.
  • 이점
    • instance 에 대한 process 수를 줄인다
    • 메모리 사용량과 시스템 오버헤드를 줄인다.

shared server 환경에서는 server process 안에 PGA에는 stack space (바인드 변수 생성) 영역만 있고 UGA는 SGA 영역에 저장된다.

PGA (Program Global Area)

  • stack space (바인드 변수 생성)

※ shared server 환경에서 수행하지 말아야 할 작업, 무조건 dedicated server 환경에서 수행해야하는 작업

  • 데이터베이스 관리
  • 백업 및 복구 작업
  • 대량로드 작업
  • 일반적인 dba 작업

▶ shared server 환경으로 구성 (ora11g에서 수행)

  1. dispatcher 구성
alter system set dispatchers = "(protocol=tcp)(dispatcher=2)";
alter system set max_dispatchers = 10;
  • 확인
SQL> show parameter dispatchers

NAME            TYPE    VALUE                        
--------------- ------- ---------------------------- 
dispatchers     string  (protocol=tcp)(dispatcher=2) 
max_dispatchers integer 10
  1. shared server process 구성
  • instance 시작시에 생성할 shared server process 수
alter system set shared_servers=2;
  • 최대로 사용할 수 있는 shared server process 수
alter system set max_shared_servers=10;
SQL> show parameter shared_servers
NAME               TYPE    VALUE 
------------------ ------- ----- 
max_shared_servers integer 10    
shared_servers     integer 2
  1. 윈도우 환경의 tns 파일에 추가
  • dedicated server
ora11g_d =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11g)
    )
  )
  • shared server
ora11g_s =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = ora11g)
    )
  )

근데, 스태틱 리스너에서 dispatcher가 안떠서 default 리스너를 이용해서 dispatcher를 띄웠다.
◆ 왜? 스태틱 리스너에서는 안될까?..

  • default 리스너 추가해준다.
[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 = 192.168.56.104)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )



SID_LIST_L01 =
        (SID_LIST =
                (SID_DESC =
                        (GLOBAL_DBNAME = ora11g)
                        (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
                        (SID_NAME = ora11g)
                )
        )


L01 =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1522)))

ADR_BASE_LISTENER = /u01/app/oracle
profile
YoonSeo Park
DB 공부를 하고 있는 사람입니다. 글을 읽어주셔서 감사하고 더 좋은 글을 쓰기 위해 노력하겠습니다 :)
이전 포스트

Audit(감사), 간단한 문제

다음 포스트

server process, instance recovery, backup

0개의 댓글