ORACLE/TUNING2008. 11. 7. 17:10

옵티마이저의 비용계산 방법과 실행원리

반응형

옵티마이저의 비용계산 방법과 실행원리
시스템의 성능 향상을 위한 노력

이번호 technical tips에서는 SQL문의 성능을 결정하는 비용기반 옵티마이저의 핵심 원리 중에 비용계산 방법에 대해 알아 보도록 하겠다. 대부분의 개발자들이 작성하는 SQL문은 비용기반 옵티마이저 환경에서 작성된 실행계획을 통해 실행 되어지는데, 이때 옵티마이저의 비용계산 방법에 대해 정확히 이해한다면 더 좋은 성능을 보장 받는 SQL문을 작성할 수 있을 것이다.

저자_주종면, 오라클 ACE, PLAN 정보기술(www.plandb.co.kr / Jina6678@paran.com)

1. SQL문 처리과정

옵티마이저의 비용계산 방법을 소개하기 전에 우선 SQL문의 처리과정의 대해 알아보자.
사용자가 실행하는 SQL문은 파서(Parser)에게 전달되고 파서는 데이터 딕셔너리 정보를 참조하여 SQL문에 대한 구문분석(Syntax와 Symantics)을 수행한다. 이 결과를 파스-트리(Parse-Tree)라고 한다.
파스-트리는 옵티마이저에게 전달되는데 오라클 데이터베이스에는 공식기반 옵티마이저(Rule-Based Optimizer)와 비용기반 옵티마이저(Cost-Based Optimizer)가 있다. 비용기반 옵티마이저에 의해 산출된 적정 플랜(Optimal Plan)은 로우 소스 생성기(Row Source Generator)에게 전달되고 이것은 실행 계획(Execution Plan)으로 결정된다.
우리가 SET AUTOTRACE, SQL*TRACE와 TKPROF와 같은 튜닝 도구들을 통해 참조할 수 있는 결과에 바로 이 실행계획이 포함되어 있다. 이 실행계획은 SQL 실행엔진(SQL Execution Engine)에 의해 테이블과 인덱스를 참조하여 그 결과를 사용자에게 리턴하게 되는 것이다.

이어서, 비용기반 옵티마이저가 어떤 비용계산 방법을 통해 적절한 실행 계획을 찾아내는지를 소개할 것이다. 개발작업 때 처음부터 좋은 실행계획을 작성할 수 있도록 SQL문을 작성한다면 SQL 튜닝에 대한 불필요한 시간과 비용을 줄여 나감으로써 좋은 성능의 시스템을 개발할 수 있는 첫걸음이 되는 것이다.

 

 

2. 비용기반 옵티마이저의 구조

 

 

이번에는 구체적으로 비용기반 옵티마이저의 아키텍처에 대해 알아보도록 하겠다.
CBO(Cost Based Optimizer)가 어떻게 비용을 계산하고 어떻게 실행계획을 작성하는지를 알기 위해서는 보다 구체적으로 CBO의 아키텍처의 대해 알고 있어야 한다.
<그림 2>에서와 같이 CBO는 쿼리 변형기(Query Transformer), 비용 계산기(Estimator), 쿼리 작성기(Query Generator) 3가지 구조로 구성되어 있다. 그럼, 각 구성 요소와 비용계산 알고리즘을 통해 실행계획 작성 방법에 대해 알아 보자.

 

 

3. 쿼리 변형기(Query Transformer)

 

 

쿼리 변형기는 파서(Parser)에 의해 구문 분석된 결과를 전달 받아 잘못 작성된 SQL문을 정확한 문장으로 변형시키는 역할을 수행한다.

? 잘못된 데이터 타입으로 조건 값을 검색하면 변형된다.
(S_DATE 컬럼은 날짜 컬럼인데 문자 값을 검색할 때 사용하는 인용부호를 사용한 경우)

 

SQL> SELECT * FROM emp WHERE s_date = '1999-01-01';
--> SQL> SELECT * FROM emp WHERE s_date = TO_DATE('1999-01-01');

? LIKE 연산자는 %(와일드 카드)와 함께 검색하는 경우 사용되지만, 그렇지 않은 경우
=(동등) 조건으로 변형되어 검색된다.

SQL> SELECT * FROM emp WHERE ename LIKE '주종면‘;
--> SQL> SELECT * FROM emp WHERE ename = ‘주종면’;

? BETWEEN ~ AND 조건은 > AND < 조건으로 변형되어 검색된다.

SQL> SELECT * FROM emp WHERE salary BETWEEN 100000 AND 200000;
--> SQL> SELECT * FROM emp WHERE salary >= 100000 and salary <= 200000;

? 인덱스가 생성되어 있는 컬럼의 IN 연산자의 조건은 OR 연산자의 조건으로 변형된다.

SQL> SELECT * FROM emp WHERE ename IN ('SMITH', 'KING');
--> SQL> SELECT * FROM emp WHERE ename = 'SMITH' or ename = 'KING';

? 인덱스가 생성되어 있는 컬럼의 OR 연산자의 조건은 UNION ALL로 변형된다.

SQL> SELECT * FROM emp WHERE ename = 'SMITH' or sal = 1000;
SQL> SELECT * FROM emp WHERE ename = 'SMITH'
UNION ALL
SELECT * FROM emp WHERE sal = 1000;

 

이와 같이 쿼리 변형기는 부적절하거나 잘못 작성된 SQL문장을 정확한 문장으로 변형시켜주는 역할을 수행하는 알고리즘이다.

 

 

4. 비용 계산기(Estimator)

 

 

비용 계산기는 비용기반 옵티마이저가 가지고 있는 비용 계산 공식에 의해 다양한 실행방법 중에 가장 좋은 성능의 실행계획을 찾아 주는 알고리즘이다.

 

1) 테이블과 인덱스의 통계정보

 

먼저, ANALYZE 명령어에 의해 수집되는 통계정보의 상태와 용어에 대해 설명하겠다. 먼저, 테이블에 대한 통계정보이다.

 

SQL> ANALYZE TABLE big_emp COMPUTE STATISTICS;
SQL> ANALYZE TABLE big_dept COMPUTE STATISTICS;

SQL> SELECT table_name, blocks, num_rows, avg_row_len
FROM user_tables
WHERE table_name = ‘BIG_EMP’ or table_name = ‘BIG_DEPT’

TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN
--------------------------------------------
BIG_DEPT 1 289 23
BIG_EMP 180 28955 43

NUM_ROWS : 해당 테이블의 전체 행수
AVG_ROW_LEN : 행 하나의 평균 길이

SQL> SELECT table_name, column_name, low_value, high_value, num_distinct
FROM user_tab_columns
WHERE table_name = ‘BIG_EMP‘ or table_name = ‘BIG_DEPT‘;

TABLE_NAME COLUMN_NAME LOW_VALUE HIGH_VALUE NUM_DISTINCT
----------------------------------------------------------------------
BIG_EMP EMPNO C102 C3036464 28955
BIG_EMP ENAME 4144414D53 57415244 14
BIG_EMP JOB 414E414C595354 53414C45534D414E 8
BIG_EMP MGR C24C43 C25003 6
BIG_EMP HIREDATE 77B7060D010101 78680604010101 713
BIG_EMP SAL 80 C24E6233 3982
BIG_EMP COMM 80 C20F 5
BIG_EMP DEPTNO 80 C164 98
BIG_EMP GROUPNO 31 32 2

LOW_VALUE : 해당 컬럼에 저장되어 있는 가장 최소값에 대한 암호화 결과
HIGH_VALUE : 해당 컬럼에 저장되어 있는 가장 최대값에 대한 암호화 결과
NUM_DISTINCT : 해당 컬럼의 저장되어 있는 유일한 값의 개수

 

다음은 인덱스에 대한 통계정보이다.

 

SQL> CREATE INDEX i_big_emp_deptno ON big_emp (deptno);
SQL> ANALYZE INDEX i_big_emp_deptno COMPUTE STATISTICS;

SQL> SELECT index_name index_name,
num_rows num_rows,
avg_leaf_blocks_per_key l_blocks,
avg_data_blocks_per_key d_blocks,
clustering_factor cl_fac,
blevel blevel,
leaf_blocks leaf
FROM user_indexes;

INDEX_NAME NUM_ROWS L_BLOCKS D_BLOCKS CL_FAC BLEVEL LEAF
----------------------------------------------------------------------------------
I_BIG_EMP_DEPTNO 28853 1 51 5036 1 57

NUM_ROWS : 인덱스 행 수
L_BLOCKS : 하나의 LEAF 블록에 저장되어 있는 인덱스 키의 수
D_BLOCKS : 하나의 DATA 블록에 저장되어 있는 인덱스 키의 수
CL-FAC : CLUSTER FACTOR
BLEVEL : INDEX의 DEPTH
LEAF : LEAF 블록의 수

 

통계 정보는 오라클 10g 이전 버전까지는 사용자가 실행하는 ANALYZE 명령어에 의해 생성되었으며 10g 버전부터는 오라클 서버의 자동화된 알고리즘에 의해 자동 생성된다.
오라클 9i 버전 때까지는 사용자에 의해 통계정보를 생성해 주지 않으면 비용기반 옵티마이저는 부정확한 실행계획을 작성함으로써 성능이 저하되는 경우들이 많이 발생했었다.
<그림 3>은 통계정보가 생성되어 있지 않은 경우 비용기반 옵티마이저가 참조하는 통계정보의 기본 값이다. 데이터를 저장하고 있는 테이블과 인덱스의 실제 구조정보와 다른 값을 참조하기 때문에 결론적으로 좋은 실행계획을 작성하지 못하는 것이다.

 

2) 용어에 대한 이해
SQL> SELECT * FROM big_emp;

Execution Plan
----------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=28955 Bytes=1042380)
1 0 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=19 Card=28955 Bytes=1042380)

COST : SQL문을 실행하여 조건을 만족하는 행을 검색하는데 소요되는 횟수
CARDINALITY : 전체 테이블에서 SQL문의 조건을 만족하는 행 수

 

3) Cardinality

일반적으로 cardinality는 SQL문이 실행되었을 때 조건을 만족하는 행수를 의미하는 것이긴 하지만 이것은 검색되는 컬럼이 어떤 속성을 가지고 있느냐에 따라 계산 공식이 달라진다.

 

3-1) Distinct Cardinality(Unique-Key)인 경우

이 경우는 주로 Full Table Scan과 같이 테이블 전체 행을 검색하는 경우의 cardinality를 계산하는 공식이다.

SQL> SELECT count(*) FROM big_dept;
count(*)
------------------
289

Cardinality = 조건을 만족하는 테이블의 행 수 = 289

SQL> ANALYZE TABLE big_dept COMPUTE STATISTICS;
SQL> SELECT * FROM big_dept ;

Execution Plan
--------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=289 Bytes=5202)
1 0 TABLE ACCESS (FULL) OF 'BIG_DEPT' (Cost=1 Card=289 Bytes=5202)

 

3-2) Efficient Cardinality(Non-Unique-Key)인 경우

SQL> SELECT count(*) FROM big_dept; --> 289 행
SQL> SELECT distinct loc FROM big_dept; --> 7 행

Cardinality = 테이블의 전체 행수 / Distinct-Key 수 = 289 / 7
= 41

SQL> ANALYZE TABLE big_dept COMPUTE STATISTICS;
SQL> SELECT * FROM big_dept WHERE loc = ‘LA’;

Execution Plan
---------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=41 Bytes=738)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_DEPT' (Cost=1 Card=41 Bytes=738)

 

3-3) Group Cardinality(Group by 절)인 경우

SQL> ANALYZE TABLE big_emp COMPUTE STATISTICS;

Cardinality = Distinct-Key 수 - 1

SQL> SELECT deptno, sum(sal) FROM big_emp Group by deptno; --> 99 행

Execution Plan
-----------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=131 Card=98 Bytes=588)
1 0 SORT (GROUP BY) (Cost=131 Card=98 Bytes=588)
2 1 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=57 Card=28955 Bytes=173730)

 

4) Selectivity

선택도는 전체 테이블에서 SQL문의 조건을 만족하는 행이 분포되어 있는 비율을 의미하며 검색 되어지는 컬럼의 성격에 따라 계산 공식이 달라진다.

 

4-1) Unique-Key/Primary-Key의 경우

SELECT * FROM emp WHERE empno = 200;
--> Selectivity = 0.01 (좋은 선택도)

 

4-2) Non Unique-Key의 경우

SELECT * FROM emp WHERE ename = ‘SMITH’;
--> Selectivity = 1 / distinct-keys
--> 1/ 4 = 0.25

 

4-3) 값을 가진 비동등 조건식의 경우

SELECT * FROM emp WHERE empno < 200;
--> Selectivity = (범위값 - 최소값) / (최대값 - 최소값)
= (200 - 1) / (29999 - 1)
= 199 / 29998 = 0.007

SELECT * FROM emp WHERE empno > 200;
--> Selectivity = (범위값 - 최소값) / (최대값 - 최소값)
= (29799 - 1) / (29999 - 1)
= 29798 / 29998 = 0.9
SELECT * FROM emp WHERE empno BETWEEN 100 AND 200;
--> Selectivity = (최대 조건값 - 최소 조건값) / (최대값 - 최소값)
= (200 - 100) / (29999 - 1)
= 100 / 29998 = 0.003

 

4-4) 바인드 변수를 가진 비동등식의 경우

SELECT * FROM emp WHERE empno < :a ;
--> Selectivity = 0.25 % (나쁜 선택도)

SELECT * FROM emp WHERE empno BETWEEN :a AND :b ;
--> Selectivity = 0.5 % (나쁜 선택도)

 

5. 비용 계산 방법

 

지금까지 비용기반 옵티마이저가 비용을 계산하기 위해 알아야 할 여러 가지 내용에 대해 알아보았다. 그럼 지금부터는 다양한 SQL문의 비용 계산 공식에 대해 알아보자.

 

5-1) Full Table Scan인 경우

Cost = 전체 블록 수 / DB_FILE_MULTIBLOCK_READ_COUNT의 보정 값

인덱스를 사용하지 않고 해당 테이블의 첫 번째 블록부터 전체 블록을 검색해야 하는 전체 테이블 스캔의 경우에는 init.ora 파일에 정의되어 있는DB_FILE_MULTOBLOCK_READ_COUNT 파라메터 값에 의해 비용이 계산된다.
이 파라메터는 FULL TABLE SCAN의 경우 한번에 하나의 I-O로는 성능을 기대할 수 없기 때문에 보다 빠른 성능을 기대하기 위해 제공되는 다중 블록 읽기를 위한 파라메터이다. 즉, 한번 I-O에 8개, 16개, 32개, 64개의 다중 블록을 읽게 하기 위함이다.

SQL> SHOW PARAMETER db_file_multiblock_read_count
NAME TYPE VALUE
-------------------------------------------------------------------------------------
db_file_multiblock_read_count integer 16
SQL> SELECT * FROM big_emp;

Execution Plan
----------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=28955 Bytes=1042380)
1 0 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=19 Card=28955 Bytes=1042380)

앞에서 소개된 비용 계산 공식을 적용해보면 COST = 180 / 16 = 11.25의 결과가 나와야 하는데 실제 비용은 COST=19의 결과가 계산되었다 !!
이것은 DB_FILE_MULTIBLOCK_READ_COUNT 파라메터의 실제 값처럼 한번 I-O에 8, 16, 32, 64개의 블록을 읽을 수는 없기 때문에 파라메터의 실제 값이 아닌 보정 값으로 비용을 계산했기 때문이다. <그림 4>의 왼쪽 표는 ACTUAL (DB_FILE_MULTIBLOCK_READ_COUNT 파라메터 값)에 따른 Adjusted(보정 값)이며 <그림 4>의 오른쪽 그림은 이 파라메터가 실제로 성능의 영향을 미치게 되는 영향도를 그림으로 나타낸 것이다.
즉, COST = 19는 (180 / 10.398) +1의 계산 공식 결과임을 알 수 있다.
사용자가 실행하는 SQL문의 실행계획이 FULL TABLE SCAN으로 결정되도록 유도하기 위해서는 이 파라메터 값을 조절하면 된다.

 

SQL> ALTER SESSION SET db_file_multiblock_read_count = 8;
SQL> SELECT * FROM big_emp;

Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=28955 Bytes=1042380)
1 0 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=29 Card=28955 Bytes=1042380)

 

위 SQL문의 비용은 Cost = (180 / 6.589) + 1 = 29 이다.
파라메터 값의 변경에 따라 비용이 달라지는 것을 확인할 수 있을 것이다.

 

SQL> ALTER SESSION SET db_file_multiblock_read_count = 32;
SQL> SELECT * FROM big_emp;

Execution Plan
-------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=28955 Bytes=1042380)
1 0 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=13 Card=28955 Bytes=1042380)

위 SQL문의 비용은 Cost = (180 / 16.409) + 1 = 13이다.

 

 

5-2) Unique Index Scan인 경우

Cost = blevel + 1

UNIQUE INDEX를 이용한 비용은 LEAF 블록의 DEPTH +1 이 된다.

SQL> CREATE UNIQUE INDEX I_big_emp_empno ON BIG_EMP (EMPNO);
SQL> ANALYZE INDEX I_big_emp_empno compute statistics;

SQL> SELECT INDEX_NAME, BLEVEL FROM USER_INDEXES
WHERE INDEX_NAME = 'I_BIG_EMP_EMPNO';

INDEX_NAME BLEVEL
-------------------------------------------------
I_BIG_EMP_EMPNO 1

SQL> SELECT /*+index(big_emp I_big_emp_empno )*/ ename
FROM big_emp
WHERE empno = 7499;
--------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_EMP' (Cost=2 Card=1 Bytes=20)
2 1 INDEX (UNIQUE SCAN) OF 'I_BIG_EMP_EMPNO' (UNIQUE) (Cost=1 Card=100)

위 SQL문의 비용은 Cost = 1 + 1 = 2이다.

5-3) Fast Full Index Scan인 경우

Cost = leaf_blocks / db_block_size

SQL> CREATE INDEX emp_job_deptno ON BIG_EMP (job, deptno);
SQL> ANALYZE INDEX emp_job_deptno compute statistics;
SQL> SELECT INDEX_NAME, LEAF_BLOCKS FROM USER_INDEXES
WHERE INDEX_NAME = 'EMP_JOB_DEPTNO';

INDEX_NAME LEAF_BLOCKS
--------------------------------------------------------
EMP_JOB_DEPTNO 89

SQL> SHOW PARAMETER DB_BLOCK_SIZE
NAME VALUES
----------------------------------------------
DB_BLOCK_SIZE 8

SQL> SELECT /*+index_ffs(big_emp big_emp_job_deptno)*/ job, deptno
FROM big_emp
WHERE deptno >= 1 and deptno <= 100
----------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=37 Bytes=703)
1 0 INDEX (FAST FULL SCAN) OF 'EMP_JOB_DEPTNO (Cost=10 Card=37 Bytes=703)

위 SQL문의 비용은 Cost = 89 / 8 = 10 이다.

 

5-4) Index Range Scan인 경우

Cost = blevel + (Selectivity X leaf_blocks) + (Selectivity X Cluster Factor)
SQL> ALTER SESSION SET optimizer_index_cost_adj = 100; SQL> ANALYZE
TABLE big_emp COMPUTE STATISTICS;
SQL> ANALYZE INDEX i_big_emp_deptno COMPUTE STATISTICS;

선택도= 1/98 (Distinct.. deptno.. 98.... ...... .... .. USER_TAB_COLUMNS
참조)
Cluster-Factor= 5036 (USER_INDEXES 참조)

SQL> SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS FROM USER_INDEXES
WHERE INDEX_NAME = 'I_BIG_EMP_DEPTNO'

INDEX_NAME BLEVEL LEAF_BLOCKS
---------------------------------------------------------------------------
I_BIG_EMP_DEPTNO 1 57

SQL> SELECT /*+INDEX(BIG_EMP i_big_emp_deptno)*/ *
FROM BIG_EMP WHERE DEPTNO = 10 ;

Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=53 Card=294 Bytes=10584)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_EMP' (Cost=53 Card=294 Bytes=10584)
2 1 INDEX (RANGE SCAN) OF 'I_BIG_EMP_DEPTNO' (NON-UNIQUE) (Cost=1 Card=294)

위 SQL문의 비용은 Cost = 1 + (1/98 * 57) +(1/98 * 5036) = 53이다.

SQL> ALTER SESSION SET optimizer_index_cost_adj = 50;
SQL> SELECT /*+INDEX(BIG_EMP)*/ * FROM BIG_EMP WHERE DEPTNO = 10 ;

Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=294 Bytes=10584)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_EMP' (Cost=27 Card=294 Bytes=10584)
2 1 INDEX (RANGE SCAN) OF 'I_BIG_EMP_DEPTNO' (NON-UNIQUE) (Cost=1 Card=294)

위 SQL문의 비용은 Cost = 53 X 0.5 = 27이다.

SQL> ALTER SESSION SET optimizer_index_cost_adj = 150;
SQL> SELECT /*+INDEX(BIG_EMP)*/ * FROM BIG_EMP WHERE DEPTNO = 10 ;

Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=80 Card=294 Bytes=10584)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_EMP' (Cost=80 Card=294 Bytes=10584)
2 1 INDEX (RANGE SCAN) OF 'I_BIG_EMP_DEPTNO' (NON-UNIQUE) (Cost=1 Card=294)

위 SQL 문의 비용은 Cost = 53 X 1.5 = 80이다.

 

5-5) Sort-Merge Join인 경우

다음은 소트-머지 조인의 경우 비용 계산 공식이다.
Cost = (Outer 테이블의 Sort Cost +Inner 테이블의 Sort Cost) -1
SQL> SELECT /*+use_merge(big_dept big_emp)*/ *
FROM big_emp, big_dept
WHERE BIG_EMP.DEPTNO = BIG_DEPT.DEPTNO;

Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=452 Card=28853 Bytes=1558062)
1 0 MERGE JOIN (Cost=452 Card=28853 Bytes=1558062)
2 1 SORT (JOIN) (Cost=7 Card=289 Bytes=5202)
3 2 TABLE ACCESS (FULL) OF 'BIG_DEPT' (Cost=1 Card=289 Bytes=5202)
4 1 SORT (JOIN) (Cost=446 Card=28955 Bytes=1042380)
5 4 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=57 Card=28955 Bytes= 1042380)

위 SQL문의 비용은 Cost = (outer-sort-cost + inner-sort-cost) - 1
= 7 + 446 - 1 = 452 이다.

5-6) Nest-Loop 조인의 경우

다음은 중첩루프 조인의 경우 비용 계산 공식이다.

Cost = Outer 테이블의 Cost + (Inner 테이블의 Cost * Outer 테이블의 Cardinality)

SQL> SELECT /*+ use_nl(big_emp big_dept)
index(big_emp I_big_emp_deptno)*/ *
FROM big_emp, big_dept
WHERE BIG_EMP.DEPTNO = BIG_DEPT.DEPTNO;

Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18786 Card=28853 Bytes= 1558062)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_EMP' (Cost=65 Card=28955 Bytes=1042380)
2 1 NESTED LOOPS (Cost=18786 Card=28853 Bytes=1558062)
3 2 TABLE ACCESS (FULL) OF 'BIG_DEPT' (Cost=1 Card=289 Bytes=5202) <-- OUTER 테이블
4 2 INDEX (RANGE SCAN) OF 'I_BIG_EMP_DEPTNO' (NON-UNIQUE) (Cost=1 Card=28955)

위 SQL문의 비용은 Cost = outer-cost + (inner-cost * outer card)
= 1 + (65 * 289)
= 1 + 18785
= 18786

SQL> CREATE INDEX i_big_dept_deptno ON big_dept (deptno);
SQL> ANALYZE TABLE big_dept COMPUTE STATISTICS;
SQL> SELECT /*+use_nl(big_emp big_dept)
index(big_dept I_big_dept_deptno)
ordered*/ *
FROM big_emp, big_dept
WHERE BIG_EMP.DEPTNO = BIG_DEPT.DEPTNO;

Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=57967 Card=28853 Bytes= 1558062)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_DEPT' (Cost=2 Card=289 Bytes= 5202)
2 1 NESTED LOOPS (Cost=57967 Card=28853 Bytes=1558062)
3 2 TABLE ACCESS (FULL) OF 'BIG_EMP'(Cost=57 Card=28955 Bytes=1042380) <--OUTER ......
4 2 INDEX (RANGE SCAN) OF 'I_BIG_DEPT_DEPTNO' (NON-UNIQUE) (Cost=1 Card=289)

위 SQL문의 비용은 Cost = outer-cost + (inner-cost * outer card)
= 57 + (2 * 28955)
= 57 + 57910
= 57967


5-7) Hash Join인 경우

다음은 해시 조인의 경우 비용 계산 공식이다.

Cost = (Outer 테이블의 Cost × #Hash 파티선수 +Inner 테이블의 Cost) + 2

SQL> SELECT /*+hash(big_emp)*/ *
FROM BIG_EMP, BIG_DEPT
WHERE BIG_EMP.DEPTNO = BIG_DEPT.DEPTNO;

Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=60 Card=28853 Bytes=1558062)
1 0 HASH JOIN (Cost=60 Card=28853 Bytes=1558062)
2 1 TABLE ACCESS (FULL) OF 'BIG_DEPT' (Cost=1 Card=289 Bytes=5202)
3 1 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=57 Card=28955 Bytes= 1042380)

위 SQL문의 비용은 Cost = outer-cost + inner-cost + Sort Cost + 2
= 1 + 57 + 2
= 60

 

 

6. 실행계획 생성기(Plan Generator)

사용자가 실행한 SQL문은 쿼리 변형기의 비용 계산기에 의해 여러 가지 유형의 실행계획으로 비용 분석된다. 그 중에 가장 적은 비용으로 실행되어질 수 있는 실행계획 하나가 선택되는데 이것을 Optimal Plan이라고 한다.
다음 문장들은 동일한 결과를 제공하지만 실행계획 생성기에 의해 가장 적은 비용의 실행계획을 선택한 결과이다.

 

6-1) 적정 플랜(Optimal Plan)

Index Scan인 경우

SELECT ename
FROM big_emp
WHERE deptno = 20 AND empno BETWEEN 100 AND 200
ORDER BY ename;
Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=12)
1 0 SORT (ORDER BY) (Cost=9 Card=1 Bytes=12)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_EMP' (Cost=5 Card=1 Bytes=12)
3 2 INDEX (RANGE SCAN) OF 'I_BIG_EMP_EMPNO' (UNIQUE) (Cost=2 Card=1)

이 실행계획은 비용기반 옵티마이저에 의해 I_BIG_EMP_EMPNO 인덱스가 선택되었으며 이때 계산된 I-O COST는 9이다.
I_BIG_EMP_DEPTNO 인덱스가 선택된 경우

SELECT /*+index(big_emp I_BIG_EMP_DEPTNO)*/ ename
FROM big_emp
WHERE deptno = 20 AND empno BETWEEN 100 AND 200
ORDER BY ename;

Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=1 Bytes=12)
1 0 SORT (ORDER BY) (Cost=70 Card=1 Bytes=12)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_EMP' (Cost=66 Card=1 Bytes=12)
3 2 INDEX (RANGE SCAN) OF 'I_BIG_EMP_DEPTNO' (NON-UNIQUE) (Cost=2 Card=1)

이 실행계획은 I_BIG_EMP_DEPTNO 인덱스가 선택되었으며 이때 계산된 I-O COST는 70이다.

Full Table Scan인 경우

SELECT /*+full(big_emp)*/ ename
FROM big_emp
WHERE deptno = 20 AND empno BETWEEN 100 AND 200
ORDER BY ename;
Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=61 Card=1 Bytes=12)
1 0 SORT (ORDER BY) (Cost=61 Card=1 Bytes=12)
2 1 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=57 Card=1 Bytes=12)

이 실행계획은 Full Table Scan이 선택되었으며 이때 계산된 IO COST는 61이다. 결론적으로, 5-1, 5-2, 5-3의 SQL문장들은 동일한 문장, 동일한 결과를 제공하지만 이 문장이 실행될 수 있는 실행계획은 다양하다는 것을 알 수 있다.
이와 같이, 비용기반 옵티마이저는 여러 가지 실행계획 중에 가장 비용이 적게 발생하는 I_BIG_EMP_EMPNO 인덱스를 이용한 실행계획을 Optimal Plan으로 선택하게 된다.

 

6-2) 비용계산 분석 명령어

다음 문장은 비용분석기(Estimator)와 실행계획 생성기(Plan Generator)에 의해 비용 분석된 결과를 모니터링 하는 방법이다.
SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
SQL> SELECT *
FROM BIG_EMP, BIG_DEPT, ACCOUNT
WHERE BIG_EMP.DEPTNO = BIG_DEPT.DEPTNO
AND ACCOUNT.CUSTOMER = BIG_EMP.EMPNO
SQL> EXIT
[C:\] CD C:\ORACLE\ADMIN\ORA92\UDUMP
[C:\] DIR
ORA92_ORA_xxxx.trc <-- 워드패드 편집기를 통해 결과 확인

 

<분석결과>

*** 2005-07-17 10:41:27.000
*** SESSION ID:(10.976) 2005-07-17 10:41:27.000
QUERY
SELECT * FROM BIG_EMP, BIG_DEPT, ACCOUNT
WHERE BIG_EMP.DEPTNO = BIG_DEPT.DEPTNO AND ACCOUNT.CUSTOMER
= BIG_EMP.EMPNO

***************************************
GENERAL PLANS
***************************************
Join order[1]: BIG_DEPT [BIG_DEPT] BIG_EMP [BIG_EMP] ACCOUNT
[ACCOUNT]
Now joining: BIG_EMP [BIG_EMP] *******
NL Join
Outer table: cost: 2 cdn: 289 rcz: 19 resp: 2
Inner table: BIG_EMP
Access path: tsc Resc: 19
Join: Resc: 5493 Resp: 5493
Join cardinality: 28853 = outer (289) * inner (28955) * sel (3.4480e-003)
[flag=0]
Best NL cost: 5493 resp: 5493
SM Join
Outer table:
resc: 2 cdn: 289 rcz: 19 deg: 1 resp: 2
Inner table: BIG_EMP
Best SM cost : 257

***********************
Join order[2]: BIG_DEPT [BIG_DEPT] ACCOUNT [ACCOUNT] BIG_EMP
[BIG_EMP]
Now joining: ACCOUNT [ACCOUNT] *******

***********************
Join order[3]: BIG_EMP [BIG_EMP] BIG_DEPT [BIG_DEPT] ACCOUNT
[ACCOUNT]
Now joining: BIG_DEPT [BIG_DEPT] *******

***********************
Join order[4]: BIG_EMP [BIG_EMP] ACCOUNT [ACCOUNT] BIG_DEPT
[BIG_DEPT]
Now joining: ACCOUNT [ACCOUNT] *******


***********************
Join order[5]: ACCOUNT [ACCOUNT] BIG_DEPT [BIG_DEPT] BIG_EMP
[BIG_EMP]
Now joining: BIG_DEPT [BIG_DEPT] *******

***********************
Join order[6]: ACCOUNT [ACCOUNT] BIG_EMP [BIG_EMP] BIG_DEPT
[BIG_DEPT]
Now joining: BIG_EMP [BIG_EMP] *******

 

<분석결과 평가>

 

분석된 결과 중에 Join order[n]는 여러 개의 테이블을 조인하는 경우 어떤 테이블부터 검색하여 어떤 순서에 의해 조인해 나가는 방법인지 분석하는 경우를 나타낸다.
Join order[1]에서 Best NL cost: 5493은 중첩루프 조인의 비용 결과이며, Best SM cost : 257은 소트-머지 조인의 경우 비용 결과이다.
비용기반 옵티마이저는 하나의 조인순서가 결정되면 다양한 실행 방법들에 대한 비용을 일일이 계산하게 된다. 그 중에 가장 적은 비용이 발생하는 조인 순서와 실행 방법을 실행 계획으로 선택하게 되는 것이다.

 

 

 

제공 : DB포탈사이트 DBguide.net
반응형

'ORACLE > TUNING' 카테고리의 다른 글

오라클 hint 사용법  (2) 2008.11.24
Transaction internals  (0) 2008.11.11
접속 부하가 있을때 Multi listener구성하기  (0) 2008.06.12
자동화 통계수집 ＆ AWR ＆ ASH  (0) 2008.02.01
Toad를 이용한 DB튜닝방법  (0) 2007.11.16
Posted by [PineTree]
ORACLE/Migration2008. 11. 7. 15:03

Dangtong 의 오라클 <Data Pump Export / Import 3편>

반응형


  

7. Data Pump Import 모드

   지금 까지  Data Pump Export 대해 자세히 알아 보았습니다. 데이터베이스 내에 있는 오브

   젝트를 운영체제 파일시스템으로 옮기는 작업을 Data Pump Export 라고 한

   다면 Data Pump Import 작업은 운영체제 파일시스템에 있는 오브젝트 들을 데이터 베이스

   내의 테이블로 옮기는 작업 입니다.  impdp 명령어를 통하여 사용할 수 있으며,

   Data Pump Import 작업에서 처럼 Command 라인, par 파일, Interactive Mode 모두 사용 하

   실 수 있습니다.

 

    1) 파일 및 디렉토리 관련 파라메타

      

 

impdp dangtong/edu2006 DIRECTORY=datapump_dir1 DUMPFILE=datapump.dmpSCHEMAS=SCOTT

 

       과 같이 DIRECTORY 는 디렉토리 오브젝트를 받는 파라메타 이고 DUMPFILE 파라메타

       는 Import 될 파일명, SQLFILE 은 작업 수행동안 수행될 DDL문을 저장할 파일이름

       이며, 디렉토리 관련 파라메타 로 설정 됩니다.

    2) 필터링 관련 파라메타

       필터링 파라관련 파라메타 에는 COTENT,INCLUDE,EXCLUDE,TABLE_EXISTS_ACTION 파라메

       터가 있습니다. COTENT,INCLUDE,EXCLUDE 파라메타는 Export 와 마찬가지로 사용 하

       실수 있으며,TABLE_EXISTS_ACTION 파라메타는 오직 Import 작업시에만 사용 할 수 있

       습니다.

       ① COTENT : CONTENT 파라메타는 DATA_ONLY,ALL,METADATA_ONLY 3가지 값을 가질 수 있

       으며, CONTENT=DATA_ONLY 형식으로 사용 하실 수 있습니다.

       

 

impdp dangtong/edu2006 DIRECTORY=datapump_dir1 DUMPFILE=datapump.dmp

SCHEMAS=SCOTT CONTENT=DATA_ONLY

 

       ② INCLUDE : INCLUDE=OBJECT_NAME:"='조건'" 형식으로 사용하실 수 있으며, 오브

       젝트의 종류에는 앞서 배운 것 과 같이 TABLE,INDEX,PORCEDURE,FUNCTION 등이 있습

       니다.

       

 

impdp dangtong/edu2006 DIRECTORY=datapump_dir1 DUMPFILE=datapump.dmp

SCHEMAS=SCOTT INCLUDE=TABLE:"='SAL'"

 

       SCOTT 유저의 테이블을 Import 하되 SAL 테이블 만 포함 시키라는 명령 이 됩니다

       SCOTT 유저가 EMP,SAL,SALARY 3개의 테이블을 가졌다고 가정하고 하나의 덤프파일에

       3개의 테이블을 Export 받았고, 위와 같은 import 명령을 내린다면 3개의 테이블중

       오직 SAL 테이블 만을 Import 하게 됩니다.

       ③ EXCLUDE : EXCLUDE=OBJECT_NAME:"='조건'" 형식으로 사용하실 수 있으며, 마찬가지

       로 오브젝트 종류는 INCLUDE 와 같습니다.

 

       

 

impdp dangtong/edu2006 DIRECTORY=datapump_dir1 DUMPFILE=datapump.dmp

SCHEMAS=SCOTT EXCLUDE=TABLE:"='SAL'"

 

       SCOTT 유저의 테이블을 Import 하되 SAL 테이블을 제외한 나머지 테이블을 Import

       하라는 명령이 되겠죠? 마찬가지로 SCOTT 유저가 EMP,SAL,SALARY 3개의 테이블을 가졌

       다고 가정하고 하나의 덤프파일에 3개의 테이블을 Export 받았고, 위와 같은 import

       명령을 내린다면 3개의 테이블중 SAL 을 제외한 EMP,SALARY 테이블만 Import 될 것

       입니다.

       ④ TABLE_EXISTS_ACTION : Import 시에 중요한 옵션입니다. 우리가 Data Pump 를 통

       해 작업을 하게될 경우 같은 이름의 테이블이 존재할 때가 있습니다. 만약 테이블이

       존재 하더라도 Import 하고자 하는 데이터의 row 수가 다를 수고 있고 같을 수도 있

       을 겁니다. 즉, 테이블은 존재하지만 데이터의 내용은 차이가 난다는 거죠.

       이러한 경우에 사용할 수 있는 유용한 파라메타가  TABLE_EXISTS_ACTION 입니다.

       TABLE_EXISTS_ACTION 파라메타는  SKIP,APPEND,TRUNCATE,REPLACE 의 값을 가질수 있으

       며 각 값의 의미는 다음과 같습니다.

       - SKIP     : 같은 테이블을 만나면 지나치고 다음 테이블을 Import 합니다.

       - APPEND   : 같은 테이블을 만나면 기존의 데이터에 추가하여 Import 합니다.

       - TRUNCATE : 같은 테이블을 만날경우 기존의 테이블을 TRUNCATE 하고 새로운 데이

                    터를 Import 합니다.

       - REPLACE  : 같은 테이블을 만날 경우 기존의 테이블을 DROP 하고 테이블을 재생성

                    한후 데이터을 Import 합니다.

       

 

impdp dangtong/edu2006 DIRECTORY=datapump_dir1 DUMPFILE=datapump.dmp

SCHEMAS=SCOTT TABLE_EXISTS_ACTION=SKIP  

 

       위와 같이 실행하면 같은 테이블을 만날경우 그냥 지나치고 다른 테이블을 Import

       하겠죠?

      2) JOB 관련 파라메타

       앞서 학습한 JOB_NAME,STATUS,PARALLEL 파라메타를 Export 와 같은 방법으로 사용

       하실 수 있습니다. Export 와 마찬가지로 PARALLEL 작업시에 dumpfile 의 개수를

       %u를 사용하여 지정하여 주거나, 명시적으로 ','를 사용하여 PARALLEL 개수 만큼

       파일을 지정 하셔야 합니다.

 

      3) 리맵핑 관련 파라메타

      리맵핑 관련 파라메타에는 REMAP_SCHEMA,REAMP_DATAFILE,REMAP_TABLESPACE 가 있으며,

      이들 파라메타 를 통하여 우리는 다른 데이터베이스 로 Import 시에 많은 유연성을 제

      공 받을 수 있습니다.

      ① REMAP_SCHEMA : A 유저 스키마로 Export 받은 데이터를 B 유저 스키마로 Import 하

      고자 할때 사용 합니다.

      

      

 

impdp dangtong/edu2006 DIRECTORY=datapump_dir1 DUMPFILE=datapump.dmp

SCHEMAS=SCOTT REMAP_SCHEMA=SCOTT:DANGTONG

 

      위와 같이 수행한후 TABLE의 OWNER 을 조회 한다면 DANGTONG 유저의 소유로 테이블

      이 등록 되었음을 확인 하실수 있습니다.

      ② REMAP_DATAFILE : 전체 데이타베이스 시스템을 Data Pump 를 통하여 옮기고자 할때

      Export 된 daumfile 에는 DataFile 정보까지 포함하게 됩니다. 하지만 다른시스템의

      디스크 경로 상에는 존재하지 않는 경로이기 때문에 Import에 실패하게 됩니다. 이러한

      경우에 사용 할 수 있는 파라메타가 REAMP_DATAFILE 입니다. Export 된 dumpfile 이

      Datafile 정보를 포함한 경우에만 해당합니다.

      

 

impdp dangtong/edu2006 FULL=Y DIRECTORY=datapump_dir1

DUMPFILE=datapump.dmp 

REMAP_DATAFILE='/db1/data/lvol01':'/db2/data/lvol01',

               '/db1/data/lvol02':'/db2/data/lvol02'     

                                .

                                .

                                .

 

 

      ③ REMAP_TABLESPACE : Export 받은 데이터 속한 TABLESPACE에서 다른 테이블 스페이

      스로 REMAPPING 하고 하는 경우 사용할 수 있응 파라메타 입니다.

      

 

impdp dangtong/edu2006 REMAP_TABLESPACE='scott_tsb':'dangtong:tbs'

DIRECTORY=datapump_dir1 DUMPFILE=datapump.dmp SCHEMAS=SCOTT

 

      4) 네트웍 링크 파라메타

      Export 에서와 마찬가지로 DB LINK를 이용하여 원격지 데이터베이스에 대해 Import

      작업을 수행할 수 있습니다.

 

      5) Interactive mode 파라메타

      Export 에서와 마찬가지로 Ctrl + C 를 통하여 Interactive mode 로 진입할 수 있으며

      작업을 통제 할 수 있습니다.

 

   8. Data Pump 모니터링 하기  

   이번 장에서는 SQL을 통한 작업 모니터링 방법에 대하여 학습해 보도록 하겠습니다.

   작업의 진행 경과와 작업속성들 그리고 얼마나 많은 작업들이 존재 하는가를 알 수

   있습니다.

      1) 관련 조회 테이블 및 VIEW 들

     

         ① DBA_DATAPUMP_JOBS 현재 실행중인 작업의 속성들을 살펴 볼 수 있는 테이블

            입니다.

         SQL> select * from dba_datapump_jods;

         로 조회 하시면  다음과 같은 컴럼이 나옵니다.

         - OWNER_NAME  : DB 작업 계정

         - JOB_NAME    : 작업의 명칭

         - JOB_MODE,   : FULL,TABLE,INDEX,TABLESPACE 등이 있습니다.

         - STATE       : EXECUTING(수행중),DEFINING ,UNDEFINED, NOT RUNNING 의 값을

                         가집니다.

         ② Pump Session 확인

        Select sid,serial# from v$session session,dba_data_session pump_session

        where session.saddr = pump_session.saddr;

         로 조회 하시면 현재 Data Pump 를 통해 수행 중인 모든 Session 들과 상태들을

         모니터링 할 수 있습니다.

         ③ Data Pump  의  모니터링

         SELECT opname,target_desc,sofar,totalwork,(sofar/totalwork*100) Percentage

         FROM v$session_longops;

         opname  : JOBNAME 과 같습니다.

         TOTALWORK : 총 수행하여야할 용량을 가르키며 단위는 Megabytes 입니다.

         sofar     : 현재 수행한 용량 을 가르키며 단위는  Megabytes 입니다.

         target_desc : 작업의 종류를 말합니다. IMPORT/EXPORT 가 값이 될수 있습니다.

반응형

'ORACLE > Migration' 카테고리의 다른 글

하나의 dmp파일로 여러 유저에 넣을 때 tbs 분배  (0) 2010.02.04
Table생성 스크립트 파일 뽑아내기  (0) 2009.08.28
Dangtong 의 오라클 <Data Pump Export / Import 2편>  (0) 2008.11.07
10g Data Pump Export / Import 사용하기  (0) 2008.11.07
[ORACLE] 테이블간 자료이동 insert.. select, create.. select  (0) 2007.10.09
Posted by [PineTree]
ORACLE/Migration2008. 11. 7. 15:01

Dangtong 의 오라클 <Data Pump Export / Import 2편>

반응형


  

 5. Data Pump Export 사용하기

   이제 본격적으로 Data Pump Export 를 사용하는 방법과, 여러 가지 옵션들에 대해 살펴

   보고, 실 상황에서 옵션들이 어덯게 동작하는지 테스트 해보는 시간을 가져 보도록 하겠습

   니다.

     1) 컴맨드 라인을 이용한 Data Pump 사용

     $ expdp system/manager DIRECTORY=datapump_dir1 DUMPFILE=dangntong_dump01.dmp

     컴맨드 라인을 이용하여 보기와 같이 expdp 를 사용하실 수 있습니다. 커맨들 라인을

     이용 할 때는 비교적 적은 수의 옵션들이 사용되거나 간단한 구문일 때 이용하시는 것이

     좋습니다. 복잡하고, 옵션이 많게 되면 수정 하거나, 잘못 타이핑할 때 시간이 많이

     걸리게 됩니다.

     2) 파라메타 파일을 이용한 Data Pump 사용

     파라메타 파일에 다음과 같이 기록합니다. 파일명은 dangtong.par입니다.

     ⓛ dangtong.par 파일을 다음과 같이 작성하세요

     SCHEMAS=SCOTT

     DIRECTORY=datapump_dir1

     DUMPFILE=dangtong_dump01.dmp

 LOGFILE=dangtong_dump.log  

     $ expdp dangtong/imsi00 PARFILE=dangtong.par

 

   6. Data Pump Export 모드

    1) Full export 모드

    FULL 파라메타를 사용합니다.

    데이터 베이스 전체를 export 받을수 있습니다. 한가지 주의 할점은

    EXPORT_FULL_DATABASE 롤이 Full export 받고자 하는 사용자 에게 부여되어 있

    어야 합니다.

    2) 스키마 모드

    SCHEMAS 파라메타를 사용합니다.

    하나의 유저가 소유하고 있는 데이타및 오브젝트 전체를 export 받고자 할때  사용할 수

    있는 모드입니다.

    3) 테이블스페이스 모드

    TABLESPACE 파라메타를 사용합니다.

    하나 이상의 테이블스페이스에 대해 해당 테이블스페이스에 속한 모든 테이블을 받을수

    있습니다. 만약 TRANSPORT_TABLESPACES 파라메타를 이용한다면, 테이블 뿐 아니라 테이

    블스페이스의 메타데이타 까지 export 받게 되어 다른 서버에 dump 파일을 카피 한 후

    import 하게 되면 테이블 스페이스 및 테이블이 자동으로 생성됩니다.

    4) 테이블 모드

    TABLES 파라메타를 사용합니다.

    하나 이상의 테이블을 export 받을 때 사용합니다.

 

   7. Data Pump Export 파라메타

    1) 파일 및 디렉토리 관련 파라메타

       파라메타 :DIRECTORY,DUMPFILE,FILESIZE,PARFILE,LOGFILE,NOLOGFILE,COMPRESSION

       ① DIRECTORY : 디렉토리 오브젝트를 참조 하는 DIRECTORY 파라메타를 이용하여

          덤프 파일의 위치 및 로그 파일의 위치를 지정할 수 있습니다.

          DIRECTORY=directory_object_name  형식으로 사용할 수 있습니다.

       ② DUMPFILE  : Export 받아 파일시스템에 저장될 덤프파일의 이름을 지정하는 파

          라메터 입니다. 파라메타를 사용할 때 다음을 기억하시고 사용하시면 됩니다.

          - %U 를 사용하여 여러 개의 덤프 파일을 구분할 수 있습니다.

            DUMPFILE=DANGTONG_DUMO_%U.dmp 로 파라메타를 정의 합니다. 만약 덤프 파일

            이 10개가 생성 된다고 가정하면 DANGTONG_DUMO_01.dmp 부터 DANGTONG_DUMO_10.dmp

            까지 %U 부분이 자동 증가하여 파일을 구분하여 줍니다. %U의 범위는 01~99 까

            지입니다.

          - ',' 를 이용하여 여러게의 파일명을 구분할 수 있습니다. 예를 들어 다음과 같이

            DUMPFILE=DANGTONG_DUMO_01.dmp,DANGTONG_DUMO_02.dmp,DANGTONG_DUMO_03.dmp 라고

            정의 할 수 있습니다.

          - 만약 DUMPFILE 파라메타를 지정하지 않는다면 expdat.dmp 라는 파일명으로 오

            라클이 자동 선언하게 됩니다.

       ③ FILESIZE  : Export 받는 1개 파일의 최대 크기를 지정하는 파라메타 입니다.

          만약 총데이터 량이 10Gigabyte 이고 FILESIZE 를 1Gigabyte 로 지정하였다면

          1Gigabyte 크기의 dump file 이 10개 만들어 지게 됩니다.

          FILESIZE=N [ BYTES | KILOBYTES | MEGABYTES | GIGABYTES ] 형식으로 쓸 수 있습

    니다.

 ④ PARFILE   : 파일에 파라메타 들을 저장해두고 Data Pump 를 이용할 때 마다 참조

    하여 작업을 수행하고 싶을 때 PARFILE 파라메타 를  사용할 수 있습니다.

    PARFILE=filename.par 형식으로 사용할 수 있으며, 파일 확장자는 아무런 영향을

    미치지 않습니다.

 ⑤ LOGFILE and NOLOGFILE : 로그파일명을 지정하는 파라메타 입니다.

    LOGFILE=logfile_name 형식으로 사용 하시면 됩니다. 파라메타 를 설정하지 않

    는다면 export.log 라는 파일명으로 로그가 남게 됩니다. 로그파일을 남기고 싶

    지 않을 때는 NOLOGFILE 파라메타 를 사용하시면 됩니다.

 ⑥ COMPRESSION : 오라클에서 EXPORT 시에 메타데이터는 압축을 하여 파일에 저장

    하게 됩니다. COMPRESSION 파라메타를 사용 하지 않을 경우에는 덤프파일 내에

    메타데이타가 압축되어 보관됩니다. COMPRESSION 파라메타 에는 METADATA_ONLY,    

 NONE 두개의 옵션이 있으며,METADATA_ONLY 는 파라메타를 사용하지 않으면 디펄

 트로 인식되는 옵션입니다. COMPRESSION=OPTION_NAME 형식으로 사용하시면 됩니다.

 

$expdp dangtong/imsi00 DIRECTORY=datapump_dir1 DUMPFILE=dump.dmp COMPRESSION=NONE

 

    2) Export 모드 관련 파라메타

      파라메타 :FULL,SCHEMAS,TABLES,TABLESPACES,TRANSPORT_TABLESPACES

     TRANSPORT_FULL_CHECK 가 있으며, TRANSPORT_FULL_CHECK 파라메타를 제외한 파라메타

     들은 여러분들 께서 이미 "6. Data Pump Export 모드" 에서 학습 하셨습니다. 그럼

     TRANSPORT_FULL_CHECK 파라메타에 대해서만 학습 하도록 하겠습니다.

     TRANSPORT_FULL_CHECK 파라메타는 Export 작업시에 테이블스페이스 내에 존재하는 테

     이블과 인덱스의 의존성을 검사 할 것인지 하지 않을 것인지를 설정하는 파라메타 이

     며 'Y' 또는 'N' 두개의 값만을 허용 하는 파라메타 입니다. TRANSPORT_FULL_CHECK

     파라메타는 TRANSPORT_TABLESPACES 와 같이 사용 되어 집니다.

     ① 'Y' 일경우 TABLESPACE 내에 테이블만 있고 인덱스가 없다면 작업은 실패합니다.

        반드시 INDEX도 같은테이블 스페이스에 존재 해야합니다.

     ② 'Y' 일경우 TABLESPACE 내에 인덱스만 존재하고 테이블이 없다면 작업은 실패합니다.

        반드시 TABLE 또한 존재 해야합니다.

     ③ 'N' 일경우 TABLESPACE 내에 테이블만 있고 인덱스가 없다면 작업은 성공합니다.

     ④ 'N' 일경우 TABLESPACE 내에 인덱스만 있고 테이블이 없다면 작업은 실패합니다.

   

    3) Export 필터링 관련 파라메타

     파라메타 :CONTENT,EXCLUDE,EXCLUDE,QUERY 파라메타가 있으며, 이러한 파라메타들은

     어떤 데이터를 Export 된 파일에 포함시킬지 결정 하는 파라메타 입니다.

     ① CONTENT :3개의 옵션을 가질 수 있으면 옵션 들은 다음과 같습니다.

        - ALL : 테이블과 메터데이터를 포함한 모든것을 포함시키겠다는 옵션

       

 

$ expdp dangtong/edu2006 DUMPFILE=datadump.dmp CONTENT=ALL

        - DATA_ONLY : 테이블 데이터만 포함 시키겠다는 옵션

       

 

$ expdp dangtong/edu2006 DUMPFILE=datadump.dmp CONTENT=DATA_ONLY

        - METADATA_ONLY : 메타데이터 만을 포함하겠다는 옵션이며, 이경우 Export된

          파일을 이용해 다른 데이터베이스에 Import할 경우 테이블 구조만 생성되게

          됩니다.

        $ expdp dangtong/edu2006 DUMPFILE=datadump.dmp CONTENT=METADATA_ONLY

     ② EXCLUDE and INCLUDE  : 원하는 오브젝트를 선택하여 받을 수 있습니다.

        그렇다면 EXCLUDE 와 INCLUDE 파라메타가 가질 수 있는 오브젝트의 종류에는 어떤

        것들이 있을까요? 오라클에서 오브젝트란 유저스키마, 테이블, 인덱스, 프로시져

        등을 통칭해서 오브젝트라고 합니다. 파라메타의 사용방법은 아래와 같습니다.

        - SCOTT 유저와 관련된 모든것을 Export 받고 싶은데 단, BONUS 테이블을 제외하고

        받고 싶다면 아래와 같이 하시면 됩니다.

       

 

$ expdp dangtong/edu2006 dumpfile=ex_dump.dmp schemas=scott

  exclude=TABLES:"='BONUS'"  

        - SCOTT 유저와 관련된 모든 것을 Export 받고 싶은데 단, EMP 테이블의 인덱스는 받

          지 않고 싶다면  다음과 같이 하시면 됩니다.

       

 

$ expdp dangtong/edu2006 dumpfile=ex_dump.dmp schemas=scott

  exclude=INDEX:\"='EMP%'\"  

 

        [exclude | include]=object_name:조건 형식으로 사용하실 수 있습니다.

 

     ③ QUERY : 테이블 내에 있는 데이터 중 특정 조건에 만족하는 데이터 만을 Export 받

        고자 할때 사용 하는 파라메타 입니다. 사용방법은 다음과 같습니다.

        QUERY=SCHEMA.TABLE: "조건" 이며 다음과 같은 예들을 볼 수 있습니다.

        - QUERY=SCOTT.EMP: "where SAL > 1200 '

        SCOTT유저의 EMP 테이블을 Export 하되 SAL 컬럼의 값이 1200 보다 큰 값들만 Export

        한다는 뜻입니다.

     ④ SAMPLE : 오라클 10g 에서 새롭게 지원하는 기능중 하나로써 테이블의 데이터를

        Export 할때 퍼센트를 정하여 지정된 퍼센트 만큼의 데이터를 샘플링 해서 뽑을

        때 사용 하는 옵션입니다. 사용방법은 아래와 같습니다.

        

 

$ expdp dangtong/edu2020 DIRECTORY=datapump_dir1 DUMPFILE=datapump.dmp

  SAMPLE=scott.emp:20

     

          SCOTT 유저의 EMP 테이블의 데이터 중 20%만을 Export 하게 됩니다.

        - 입력 가능한 PERCENT 의 범위는 0.000001 ~ 100 까지 입니다.

 

    4) 네트웍링크 파라메타

    원격지 데이터 베이스에 있는 데이터에 접근하여 로컬 데이터베이스 머신에 Export

    된 덤프 파일을 저장하고자 한다면 NETWORK_LINK 파라메타를 사용함으로써 가능합니다.

    원격지 데이터는 DB_LINK를 통해 가져올 수 있으며 NETWORK_LINK 파라메타 를 사용하기

    위해서는 원격지 데이터베이스의 테이블에 대한 DB_LIBK 를 만들어 놓아야 합니다.

    A 데이터베이스에 B 테이터베이스의 EMP 테이블을 소유한 SCOTT_B 유저에 대한

    DB LINK link_b_scott_b 이 존재 한다면 다음과 같이 NETWORK_LINK 파라메타를 사용

    하여 export 할 수 있습니다.

    

 

$ expdp dangtong/edu2006 DIRECTORY=datapump_dir1 dumpfile=datapump.dmp

  NETWORK_LINK=EMP@link_b_scott LOGFILE=datapump.log

    5) 암호화 관련 파라메타

    Export 되는 데이터중 일부 컬럼이 암호화 되어 있거나, 중요한 데이터 라면

    ENCRYPTION_PASSWORD 파라메타를 이용하여 Export 시에 암호를 설정 하여 Export

    된 데이터가 위 변조 되지 못하게 설정할 수 있습니다. 사용 방법은 아래와 같습니다.

    

 

$expdp dangtong/edu2006 TABLES=EMP DUMPFILE=datapump.dmp

 ENCRYPTION_PASSWORD=abcdef

    

    위와 같이 설정 하게 되면 차후 Import 시에 패스워드를 물어 보게 됩니다.

     

    6) JOB 관련 파라메타

    JOB 관련 파라메타 에는 JOB,STATUS 가 있습니다.

    ① JOB : JOB 파라메타를 설정하면 Data Pump 의 작업 명을 오라클에서 자동할당 하지

       않고 JOB 파라메타에 주어진 이름으로 등록 하게 되게 됩니다. 작업 마스터 테이블에

       작업명이 등록괴어 작업에 대한 정보들을 JOB 파라메타에 등록된 이름으로 조회할 수

       있습니다.

    ② STATUS :STATUS 파라메타는 Data Pump Export 작업시에 작업의 갱신된 내용을 STATUS

       에 설정된 크기의 시간 간격으로 진행상태를 보고 받고자 할때 사용하는 파라메타 입

       니다. STATUS=30 이면 30초 간격으로 작업결과를 갱신하여 보여 주게 됩니다. 만약 이

       파라메타를 설정하지 않으면 디펄트는 0입니다. 디펄트로 설정하게 되면 거의 실시간

       으로 작업 정보를 보여 주게 됩니다.

    ③ FLASHBACK_SCN :System Change Number(SCN)는 시스템의 테이블이나 오브젝트가 변경

    되었을 때  변경 되는 SCN값을 가집니다. FLASHBACK_SCN 파라메타를 이용하여 SCN 값을

    지정할 경우에 파라메타에 설정된 SCN  기준 이전까지의 상태를 받게 됩니다.

    

 

$expdp dangtong/edu2006 dircetory=datapump_dir1 dumpfile=datapump.dmp

 FLASHBACK_SCN=120001

 

    ④ FLASHBACK_TIME : FLASHBACK_TIME은 번호 대신에 시간 값을 가집니다. FLASH_BACK

    파라메타를 사용하면 파라메타에 지정된 시간까지 의 변경사항만을 Export 하게 됩니다.

    FLASHBACK_TIME 의 값은 TIMESTAMP 형식의 값을 가지며 TO_TIMESTAMP 함수를 사용하여

    설정할 수 있습니다.

    ⑤ PARALLEL : PARALLEL 파라메타를 사용할 경우 Export 작업시에 프로세스를 필요한

    숫자 만큼 만들어 수행 함으로써 작업의 속도를 향상 시킬 수 있습니다. 디펄트 값은

    1로 설정되어 있으며, 주의할 점은 PARALLEL 에 지정된 갯수 만큼의 dumpfile 을 지정

    해주어야 합니다. 앞서 배운 %U 를 사용 하면 지정된 PARALLEL 갯수 만큼 자동으로 파일

    을 만들게 됩니다.

    

 

$expdp dangtong/edu2006 direcotry=datapump_dir1 dumpfile=datapump%U.dmp

 PARALLEL=3

    위와 같이 설정하게 되면 datapump01.dmp, datapump02.dmp, datapump03.dmp 3개의 덤프

    파일이 생성 됩니다.

    

 

$expdp dangtong/edu2006 direcotry=datapump_dir1 dumpfile=(datapump1.dmp,

 datapump2.dmp, datapump3.dmp) PARALLEL=3

 

    위와 같이 %U를 사용하지 않고 사용자가 직접 3개의 파일명을 ',' 로 구분하여 입력해도

    무방 합니다.

    ⑥ ATTACH : ATTACH 파라메타 를 이용하여 Interactive Mode 로 들어 갈수 있습니다.

     오라클에서는 작업을 제어하고 모니터링 하기 위해 Interactive Mode 를 제공합니다.

     Interactive mode 로 들어가는 방법은 2가지가 있으며 다음과 같습니다.

      - Crtl + C 를 입력 함으로써 들어 갈 수 있습니다.

        $expdp dangtong/edu2006 directory=datapump_dir1                

         table=scott.emp dumpfile=datapump.dmp

         LOGFILE=datapump.log JOBNAME=MYJOB

        작업로그.........

        ................. -> 작업에 대한 로그가 떨어질때 Crtl + C 를 누르게 되면

        export> _         -> 와 같이 프롬프트 상태로 진입하게 됩니다.

        로그가 멈춘다고 해서 작업이 중단 된게 아니라 여러분 께서는 이상태에서 Inter

         active mode 명령을 사용하여 작업을 모티너링 하고 작업을 제어 할수 있습니다.

 

      - $expdp username/password ATTACH=SCHEMA.JOB_NAME 형식 으로 원하는 작업의 

        Interactive mode 로 들어 갈수 있습니다.

        

 

 $expdb dangtong/edu2006 ATTACH=scott.MYJOB

        하게 되면 조금 전에 실행한 작업의 Interactive mode 로 들어 가게 됩니다.

        이처럼 ATTACH 파라메타는 현재 수행 중신 작업의 Interactive mode 로 진입 하는데

        사용 되어 지며 InterActive Mode 명령에는 다음과 같은 것들이 있습니다.

        

명령어

설명

ADD_FILE

 덤프파일 을 추가 할 때 사용합니다.

CONTINUE_CLIENT

 Interactive Mode 에서 Logging Mode 로 전환 할 때 사용합니다.

EXIT_CLIENT

 Client Session 을 종료하고 Job 의 상태에서 벗어납니다.

HELP

 Interactive mode 도움말페이지

KILL_JOB

 작업을 삭제합니다.

PARALLEL

 현재 수행중인 작업의 프로세스 개수를 조정할때 사용합니다.

START_JOB

 실패한 작업이나 중단된 작업을 다시 시작시킬 때 사용합니다.

STATUS

 현재 작업상태를 모니터링 할 때의 갱신 시간을 설정합니다.

STOP_JOB

 작업의 실행을 중단하고 Client 를 종료합니다.

[출처] Dangtong 의 오라클 <Data Pump Export / Import 2편>|작성자 당통
반응형

'ORACLE > Migration' 카테고리의 다른 글

Table생성 스크립트 파일 뽑아내기  (0) 2009.08.28
Dangtong 의 오라클 <Data Pump Export / Import 3편>  (0) 2008.11.07
10g Data Pump Export / Import 사용하기  (0) 2008.11.07
[ORACLE] 테이블간 자료이동 insert.. select, create.. select  (0) 2007.10.09
2G 넘는 파일의 export/import(Unix)  (0) 2007.02.12
Posted by [PineTree]
ORACLE/Migration2008. 11. 7. 14:59

10g Data Pump Export / Import 사용하기

반응형

Dangtong 의 오라클 <Data Pump Export / Import 1편>

 

우리가 데이터 베이스 내에 있는 정보들을 운영체제 파일 시스템으로 옮기거나 혹은 그 반대

의 경우를 위해 사용해 오던 것이  export/import 였다면 ,오라클 Data Pump 는 우리가 사용

해 오던 export/import 의 기능에 다양하고 강력한 기능들을 추가 한 것입니다.  

오라클 10g 에서는 export/import와 Data Pump export/import 두 가지 기능을 모두를 지원

하고 있지만, Data Pump import/export 를 알고난 후 에는 더 이상 기존에

사용해오던 export/import 를 사용하실 필요성을 느끼지 못하게 되실 겁니다.  왜냐구요?

그만큼 강력한 기능을 제공한답니다.  자 그럼 Data Pump 의 세계로 들어가 볼까요?

※잠깐만~~!!

Export/Import와 Data pump는 서로 호환되지 않습니다. 즉 Export유틸리티를 이용하여 백업 받은 파일을 Data pump 를 이용하여 Import할 수 없으며, 마찬가지로 Data Pump 를 통해 Export 된 데이터는 Export/Import 유틸리티를 통해 Import할 수 없습니다.

 

1. Data Pump export/import 의 잇점

   Data Pump export/import 를 사용함으로서 얻을 수 있는 잇점은 다음과 같습니다.  

    1) JOB 콘트롤 가능

    Interactive mode 를 통하여 Data Pump 작업을 통제 할 수 있습니다. 작업을 중단시키고

    재시작 할 수 있으며 동적으로 dump file 을 할당 할 수 있습니다. 에러가 나더라도 작업

    이 중지 될 뿐 언제든지 원인을 수정하고 재수행 할 수 있습니다.

    2) 병렬수행지원

    PARALLEL 파라메타 를 이용하여 프로세스의 Data Pump 작업의 프로세스를 병렬화 할수

    있습니다. 병렬화 된 프로세스는 여러게의 데이타 파일에 각각 데이터를 쓰거나 여러

    개의 데이터 파일로 부터 데이터를 읽어 데이터베이스에 저장합니다. 병렬 수행이 가능

    함으로써 이전 보다 훨씬 강력한 수행 속도를 보장합니다.

    3) 작업에 필요한 디스크공간을 미리 예상

    ESTIMATE 파라메타를 이용하여 작업 시작 전에 필요한 물리적인 디스크 공간을 예상 할

    수 있습니다.

    4) 원격지 수행

    DB LINK를 통하여 원격지 데이터에 대한 Data Pump Import/Export 를 수행 할 수 있습

    니다.

    5) Remapping 지원

    유저 스키마, 테이블 스페이스, 데이터파일 등과 같은 정보들의 Data Pump Import/

    Export 시에 변경 할 수 있습니다. 이러한 기능은 데이터 마이그레이션 시에 보다 많

    은 유연성을 제공하는데 큰 역할을 합니다.

2. Data Access Methods

    1) Direct-path

    메모리를 거의 사용하지 않고 파일에 direct 로 쓰게 되는 방법입니다. 메모리

    사용이 적고 속도가 빠르며, 데이터 컨버전에 시간이 걸리지 않습니다.

    2) External tables

    메타 데이터를 데이터베이스에 저장하고 데이터 는 파일시스템에 존재하게 함으로써

    대용량 데이터를 Export/Import 할 때 사용합니다.

    이두가지 모드는 오라클이 자동으로 판단하여 수행하게 됩니다.

※잠깐만~~!!!

Direct-path 가 되지 않는 경우

. 클러스터 테이블인 경우

. 테이블에 활성화된 트리거가 존재 할 경우

. 글로벌 인덱스를 가진 테이블이 하나의 파티션에 존재 할 경우

. LOB 칼럼 에 있는 도메인 인덱스

. insert 모드에서 fine-grained access control 이 enable 인 경우

. BFILE 을 가진 테이블 인 경우

 

External Table이란?

. Create TABLE ~~ ORGANIZATION EXTERNAL 문을 통해 만들어진 테이블입니다.

. 실질적인 데이터는 데이터 베이스 내에 존재하는 것이 아니라 물리적 디스크에 논리

  적 공간을 할당 받아 데이터를 저장하며, 파일형태로 존재합니다.

. 저장되는 데이터는 READ ONLY 데이터 이며 인덱스를 생성할 수 없습니다.

. DML 작업을 수행할 수 없습니다.

. MEAT-DATA in DATABASE, DATA in OS 라고 압축 설명 할 수 있습니다.

 

3. Data Pump의 권한 설정

    좀더 다양한 옵션과 Data Pump 의 모든 기능을 자유자재로 사용하고

    자 한다면, 시스템에 설정된 EXP_FULL_DATABASE, IMP_FULL_DATABASE 롤을 부여 함으로써

    가능합니다. 일단 다음과 같이 유저를 생성하고 두 권한 모두를 생성된 사용자에 게 주는

    실습을 해 보도록 합시다.

    1) 사용자 생성

       create user dangtong identified by imsi00

       default tablespace USERS

       temporary tablespace temp;

    2) 권한부여

       grant connect, resource to dangtong;

    3) 모든 테이블에 대한 select 권한 부여

       grant select any table to dangtong;

    4) EXP_FULL_DATABASE,IMP_FULL_DATABASE 권한 부여

       grant EXP_FULL_DATABASE, IMP_FULL_DATABASE to ecampus;

       

    이렇게 함으로써 모든 데이터 베이스 오브젝트에 대한 Data Pump 권한을 획득

    하였습니다. 그럼 이제 실제적으로 Data Pump 를 이용하여 Export/Import 를 실습해 보실

    모든 준비가 되었습니다.   

 

    4. Data Pump 파일 오브젝트

    1) Data Pump 가 사용하는 파일의 종류

       ⓛ Dump File : 테이블로부터 데이터 또는 메타 데이터를 로드하여 저장된 파일

       ② Log File  : Data Pump 작업 중에 발생 하는 메세지나 결과를 기록하는 파일

       ③ SQL File  : Data Pump 는SQLFILE 이라는 옵션을 사용합니다. 이옵션을 사용할 경

                       우 Data Pump Import 작업이 수행되는 동안 DDL 문을 수행할 수 있게

     해주는 옵션입니다.(자세한 사항은 이후에 다룸)

 

    2) Data Pump 디렉토리 오브젝트

      

 

       Data Pump 는 디렉토리 오브젝트를 참조하여 Dump 파일을 쓰게 됩니다.

       그림과 같이 사용자 A는 DO1,DO2 에 허가(GRANT)되어 실재 존재하는 Dir1 과

       Dir2를 사용할 수 있게 됩니다. Data Pump 가 Export 받은 데이터를 Dir1, Dir2 모두

       에 저장할 수 있다. 반면, 사용자 B는 DO1에 만 (Grant) 되어 Dir1 에만 접근할 수 있

       습니다. 이처럼 Data Pump를 이용하게 되면 디렉토리에 대한 권한까지 설정할 수 있

  습니다.

  

       ⓛ 사용 중인 디렉토리 오브젝트의 조회

          SELECT * FROM dba_directories;   

       ② 디렉토리 오브젝트 추가

          CREATE DIRECTORY datapump_dir1 as '/temporary/ora_tmp';  

          '/temporary/ora_tmp' 에 대한 디렉토리 오브젝트 datapump_dir1 을 생성하는

          명령문 입니다.

       ③ 디렉토리 오브젝트에 대한 권한 설정

          GRANT READ,WRITE ON DIRECTORY datapump_dir1 to dangtong;

          ecampus 유저에 대해 datapump_dir1 에 대한 쓰기 및 읽기 권한을 할당하는 명령문 입니다.

          이제 Data pump 를 통해 Export 받을 때 ecampus 유저는 다음과 같이 지정함으로서 '/temporary/ora_tmp'

          에 Export된 덤프 파일을 저장할 수 있습니다.

 expdp dangtong/imsi00 DIRECTORY=datapump_dir1 Tables=EMP dumpfile=dangtong_dump1.dmp

         

       ④ 디펄트 디렉토리 설정하기

          Data Pump 를 사용할 때마다 디렉토리지정을 하지 않고 묵시적으로 사용하고 싶다

          면 운영체제 환경변수에 DATA_DUMP_DIR 을 만들고 그 값으로 디렉토리 오브젝트명

          을 입력 하면 됩니다.

          $ export DATA_DUMP_DIR datapump_dir1

          위와 같이 선언하게 되면 이제 다음과 같이 디렉토리를 지정하지 않아도 됩니다.

          $ expdp ecampus/password  dumpfile=ecam_dump01.dmp Tables= test_00

[출처] 10g Data Pump Export / Import 사용하기|작성자 당통

반응형

'ORACLE > Migration' 카테고리의 다른 글

Table생성 스크립트 파일 뽑아내기  (0) 2009.08.28
Dangtong 의 오라클 <Data Pump Export / Import 3편>  (0) 2008.11.07
Dangtong 의 오라클 <Data Pump Export / Import 2편>  (0) 2008.11.07
[ORACLE] 테이블간 자료이동 insert.. select, create.. select  (0) 2007.10.09
2G 넘는 파일의 export/import(Unix)  (0) 2007.02.12
Posted by [PineTree]
ORACLE/ADMIN2008. 11. 4. 14:15

sqlplus 사용법 (ORACLE)

반응형
1. DB를 사용하기위해 ORACLE로 LOGIN 방법
가. SQLPLUS [Enter]
User ID : xxxxxx
password : xxxxxxxx
나. SQLPLUS user_id [Enter]
password : xxxxxxxx
다. SQLPLUS user_id/password [Enter]
※ 다. 와 같은 방법으로 Login 하게 되면 사용자명과 암호가 노출된다
그러므로 가장좋은 방법은 가, 그리고 나. 이다

2. SQLPLUS 상에서는 Auto Commit이 안된다. 그러므로 중간 중간 " COMMIT "를
수행한다.

3. SQL 문장의 Terminate 는 ==> “ ; ”

4. 한 화면씩 보여주는 기능
==> set pause on; (설정)
set pause off; (해제)
※ 설정을 하고나서 SQL 명령을 수행하면 커서가 그 다음 LINE에서 대기하고
있으므로 [Enter] Key를 친다. 그 다음부터는 Enter키로 한화면씩 보면 됨

5. SQL 명령어를 모를 경우 ?
==> help [명령어]

6. Unix 명령어를 사용하기 위해서는 ?
==> “ ! ” 를 붙여 사용한다.

7. 바로전에 실행한 SQL문을 FILE로 저장하려면 ?
==> “ save ”

8. 파일의 내용을 메모리로 불러오려면 ?
==> “ get ” 를 붙여 사용한다.

9. 메모리로 불러온 SQL문이나 메모리에 있는 명령을 실행 하려면 ?
==> “ / ” 를 붙여 사용한다.

10. 바로 전에 수행한 명령어를 편집하려면 ?
==> “ ed ” 를 붙여 사용한다.

11. 바로 전에 수행한 명령어 보려면 ?
==> “ l ” 를 붙여 사용한다.

12. SQL문이 있는 FIle을 바로 실행하려면 ?
==> “ @ ” 또는 " Start " 를 붙여 사용한다.

13. set heading off / on
==> Columns 명을 나타내지 않는다.

14. set arraysize line_number(숫자);
==> 한번에 DATA를 가져오는 단위

15. set timing on / off
==> sql문을 수행하는데 소요되는 시간을 나타내어 준다.
=======================================================================================================================
=======================================================================================================================
-. DB에 접속                     : CONN
 -. 파일 편집 및 실행          : EDIT, START
 -. 환경 설정                      : SET
 -. 표시 형식                      : COLUMN
 -. 변수 사용                      : &, ACCEPT
 -. 기타                              : DESC, HELP
 
*버퍼에 있는 명령어 편집
A : 라인끝에 텍스트를 추가함
C/old/new : old 를 new로 바꿈
I text : 다음 line 에 text를 추가함
L : 전체 문자을 보여줌
n text : n라인 전체를 text로 바꿈
R : buffer에 있는 문장 실행
edit : buffer에 있는 문장을 file로 부름
 
*SQL문장을 파일로 저장, 저장된 명령어를 실행할수 있는 sqlplus명령어
save a : 버퍼에있는 문장을 a.sql 파일에 저장
get a : a.sql 파일에 있는 문장을 버퍼로 불러옴
start a : a를 실행
host shell : 호스트 쉘로 나감
!vi a.sql : unix인경우에 a.sql.을 vi 로 편집
 
*set (환경설정)
colsep (text) : 칼럼이 표시될때 칼럼의 구별문자 기본값 공백
feedback (off | on) : 선택된 행이 몇 행인지 표시함 기본값은 6행이상일때 on
heading (off | on) : 컬럼에 대한 heading을 표시함 기본값은 on
linesize (n) : 한줄에 표시될 텍스트숫자 기본값80
pages (n) : 한페이지당 표시되는 라인수 기본값 24
paues (on | off | text) : 엔터키를 누를때마다 화면이 지나감 기본값은 off
timing (on | off) : sql문장이 처리되는 시간을 표시 기본값은 off
verify (on | off) : & 변수로 값을 받을 경우 화면에 확인하기 위해 old,new를 표시할 것인지 기본값은  on
show all : 환경이 어떻게 설정 되었는가 확인
* 항상 로그인시 같은 환경을 유지하고 싶을경우 glogin.sql파일에 set 으로 설정
desc 테이블 구조
help topic 도움말
라인의 가로사이즈는 linesize를 늘려서 조정하시면 되고
 
조회된 로우수가 20행정도인데... 10개정도씩 컬럼명이 반복된다면
 
pagesize를 늘려주시면 됩니다. Default값을 알고 계시다면
 
-- linesize도 record 길이만큼 지정하여 아래로 구분되지 않도록 합니다.
SQL>SET LINESIZE 300

-- 명령이 display되지 않도록 합니다.
SQL>SET ECHO OFF

-- 조회 결과가 화면에 나오지 않도록 합니다.
SQL>SET TERM OFF

-- data가 들어가는 화일 이름을 지정 합니다.
SQL>SPOOL C:\test.txt

-- 스크립트 파일을 실행 시킵니다.
SQL>@C:\SpoolSelect.sql

SQL>SPOOL Off

test.txt file이 생성 되었는지 확인 합니다.


2. 필드 값 구분하기.

1-1.select empno||' '||ename from emp; <== Tab으로 구분

1-2.select empno||','||ename from emp; <== 콤마로 구분

2-1.select empno||'"'||ename from emp; <== 필드 값에 큰따옴표 붙이는 방법
(작은 따옴표는 예약어 이기 때문에 에러 남)


SQL> set linesize 180
 
SQL> set pagesize 30
sql > column 컬럼명 format a10
이렇게 하면 표시되는 컬럼에 대한 사이즈를 조절 할 수 있습니다.
현재 TITEMID의 내용은 짧은데 자리를 많이 차지하고 있죠?
이럴경우
sql> column TITEMID format a10
이렇게 하면 10자리만큼만 차지하게 됩니다.
varchar type이면 col 컬럼명 format a20 --> 20으로 열너비조정
number라면 col 컬럼명 format 999,999 --> 9하나가 한자리
set heading off -> 컬럼의 heading..그러니깐 name, value..이런게 계속 나오지 않게..하려면 이걸 쓰죠..
spool on 또는 spool path+파일명
->이렇게 하면 쿼리 결과를 파일로 저장할 수 있죠.
SQL> spool on
SQL> select * from product_component_version;
NLSRTL                         3.4.1.0.0       Production
Oracle8i Enterprise Edition    8.1.7.4.0       Production
PL/SQL                         8.1.7.4.0       Production
TNS for HPUX:                  8.1.7.4.0       Production
SQL> spool off
SQL> !ls
LOCK          TOOL          app           jre           nohup.out     oui           tempwork
STC           admin         doc           led.sh        on.lst        script        test.sql
Script        afiedt.buf    hiraScript    led_test.sql  oraInventory  temp.sql
SQL> !vi on.lst
"on.lst" 8 lines, 387 characters
SQL> select * from product_component_version;
NLSRTL                         3.4.1.0.0       Production
Oracle8i Enterprise Edition    8.1.7.4.0       Production
PL/SQL                         8.1.7.4.0       Production
TNS for HPUX:                  8.1.7.4.0       Production
SQL> spool off

set pause on 하면 한 페이지씩 볼 수 있죠.
 
SQL> help index
 ACCEPT        DBA          PAUSE            /
 APPEND        DEFINE       PRINT            SPOOL
 @             DEL          PROMPT           SQLPLUS
 @@            DESCRIBE     QUIT             START
 ARCHIVE LOG   DISCONNECT   RECOVER          STARTUP
 ATTRIBUTE     EDIT         REMARK           STORE
 BREAK         EXECUTE      REPFOOTER        TIMING
 BTITLE        EXIT         REPHEADER        TTITLE
 CHANGE        GET          RESERVED WORDS   UNDEFINE
 CLEAR         HELP         RUN              VARIABLE
 COLUMN        HOST         SAVE             WHENEVER OSERROR
 COMPUTE       INPUT        SET              WHENEVER SQLERROR
 CONNECT       LIST         SHOW
 COPY          PASSWORD     SHUTDOWN

SQL> help column
 COLUMN
 ------
 Specifies display attributes for a given column, such as:
     -   text for the column heading
     -   alignment of the column heading
     -   format for NUMBER data
     -   wrapping of column data
 Also lists the current display attributes for a single column
 or all columns.
 COL[UMN] [{column | expr} [option...] ]
 where option represents one of the following clauses:
     ALI[AS] alias
     CLE[AR]
     FOLD_A[FTER]
     FOLD_B[EFORE]
     FOR[MAT] format
     HEA[DING] text
     JUS[TIFY] {L[EFT] | C[ENTER] | C[ENTRE] | R[IGHT]}
     LIKE {expr | alias}
     NEWL[INE]
     NEW_V[ALUE] variable
     NOPRI[NT] | PRI[NT]
     NUL[L] text
     OLD_V[ALUE] variable
     ON|OFF
     WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]
 For detailed information on this command, see the SQL*Plus User's
 Guide and Reference.

SQL> show all
appinfo is ON and set to "SQL*Plus"
arraysize 15

[출처] sqlplus 사용법(Oracle) |작성자 파란공원
반응형

'ORACLE > ADMIN' 카테고리의 다른 글

Oracle 10.2.0.1에서 10.2.0.3으로 rman백업을 이용한 업그레이드 메모  (0) 2008.11.21
[Oracle] Datafile Size 변경하기  (0) 2008.11.19
Oracle Data Dictionary Views  (0) 2008.09.12
MMAN 백그라운드 프로세스를 통한 자동 공유 메모리 관리  (0) 2008.08.12
Archive log mode 설정방법.  (0) 2008.06.19
Posted by [PineTree]
ORACLE/SQL2008. 10. 29. 18:30

Oracle 날짜 관련 함수

반응형

Oracle 날짜 관련 함수

 

select /* 오늘날짜 시분초 포함*/
              to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')
    from dual

 
select /* 오늘날짜 00시 00분 00초 */
              to_char(trunc(sysdate),'yyyy/mm/dd hh24:mi:ss')
    from dual
 
select /* 오늘날짜 00시 00분 00초 위와 동일*/
              to_char(trunc(sysdate,'dd'),'yyyy/mm/dd hh24:mi:ss')
    from dual

select /* 이번달 1일 00시 00분 00초 */
              to_char(trunc(sysdate,'mon'),'yyyy/mm/dd hh24:mi:ss')
    from dual

select /* 올해 1월 1일 00시 00분 00초 */
              to_char(trunc(sysdate,'year'),'yyyy/mm/dd hh24:mi:ss')
    from dual
 
select /* 올해 1월 1일 00시 00분 00초 */
              to_char(to_date('2002','yyyy'),'yyyy/mm/dd hh24:mi:ss')
    from dual
 
select /* 2월 1일 00시 00분 00초 */
              to_char(to_date('200202','yyyymm'),'yyyy/mm/dd hh24:mi:ss')
    from dual

select /* 2월 2일 00시 00분 00초 */
              to_char(to_date('20020202','yyyymmdd'),'yyyy/mm/dd hh24:mi:ss')
    from dual
 
select /* 2월 2일 00시 00분 01초 */
              to_char(to_date('20020202','yyyymmdd')+1/68400,'yyyy/mm/dd hh24:mi:ss')
    from dual
 
select /* 2월 2일 00시 00분 00초 -> 한달뒤*/
              to_char(add_months(to_date('20020202','yyyymmdd'),1),'yyyy/mm/dd hh24:mi:ss')

 from dual
 
from en-core
laalaal~
 
 
날짜 빼기
 
밑에 날짜 빼기가 있던데 요건 약간 다르게..
(1) 현재 날자에서 하루를 빼고 싶다고 하면
            select sysdate() - 1 from dual
(2) 1시간을 빼고 싶으면
            select sysdate() - 1/24 from dual
(3) 1분을 빼고 싶으면
            select sysdate() - 1/24/60
(q) 1초를 빼고 싶은면 어떻게 할까요? ^^
 
======================================================================================
- 날짜형 함수

    SYSDATE : 현재 시스템의 날짜 및 시간을 구함

    LAST_DAY : 지정한 날짜의 해당 월의 마지막 날짜를 구함

    MONTHS_BETWEEN : 두 날짜 사이의 개월 수를 구함

    ADD_MONTHS : 지정한 날짜로부터 몇 개월 후의 날짜를 구함

    ROUND : 날짜에 대한 반올림

    TRUNC : 날짜에 대한 버림

 

    SYSDATE : SYSDATE → 10-MAY-99

    LAST_DAY(날짜값) : LAST_DAY('17-FEB-98') → 28-FEB-98

   MONTHS_BETWEEN(날짜값1, 날짜값2) : MONTHS_BETWEEN('26-APR-97','22-JUL-95') → 21.1290323

   ADD_MONTHS(날짜값, 숫자값) : ADD_MONTHS('22-JUL-95',21) → 22-APR-97

      ROUND(날짜값, 자리수) : 현재 날짜가 1999년 5월 10일이라고 가정하자.

                              ROUND(SYSDATE,'MONTH') → 01-MAY-99

      TRUNC(날짜값, 자리수) : 현재 날짜가 1999년 5월 10일이라고 가정하자.

                              TRUNC(SYSDATE,'YEAR') → 01-JAN-99

 

  - 날짜에 대한 산술연산

    날짜 + 숫자 : 날짜 특정한 날로부터 몇일 후의 날짜 계산

    날짜 - 숫자 : 날짜 특정한 날로부터 몇일 전의 날짜 계산

    날짜 - 날짜 : 숫자 두 날짜 사이의 차이를 숫자로 계산

 

- 변환형 함수

    TO_CHAR : 숫자나 날짜를 문자열로 변환

    TO_NUMBER : 문자를 숫자로 변환

    TO_DATE : 문자를 날짜로 변환

 

      - TO_CHAR에서 숫자를 문자로 변환시에 형식에 사용되는 요소

          9 : 일반적인 숫자를 나타냄

          0 : 앞의 빈자리를 0으로 채움

          $ : dollar를 표시함

          L : 지역 통화 단위(ex \)

          . : 소숫점을 표시함

          , : 천단위를 표시함

      - TO_CHAR에서 날짜를 문자로 변환시에 형식에 사용되는 요소

          SCC : 세기를 표시 S는 기원전(BC) 

          YEAR : 연도를 알파벳으로 spelling

          YYYY : 4자리 연도로 표시

          YY : 끝의 2자리 연도로 표시

          MONTH : 월을 알파벳으로 spelling

          MON : 월의 알파벳 약어

          MM : 월을 2자리 숫자로 표시

          DAY : 일에 해당하는 요일

          DY :  일에 해당하는 요일의 약어

          DDD,DD,D : 연도,월,일 중의 날짜를 숫자로 표시

          HH , HH24 : (1-12) , (0-23)중의 시간을 표시

          MI : 분을 표시

          SS : 초를 표시

          AM(A.M.),PM(P.M.) : 오전인지 오후인지를 표시

 

      TO_CHAR(문자값,‘형식’)

        숫자를 문자로 변환 : TO_CHAR(350000,'$999,999')→ $350,000

        숫자를 날짜로 변환 : TO_CHAR(SYSDATE,'YY/MM/DD')→ 95/05/25

      TO_DATE(문자값, ‘형식’) : TO_DATE('10 SEPTEMBER 1992','DD MONTH YYYY')→10-SEP-92

      TO_NUMBER(문자값) : TO_NUMBER('1234')→ 1234

반응형

'ORACLE > SQL' 카테고리의 다른 글

Oracle 과 Mssql 날짜비교 함수  (0) 2008.11.24
RTRIM  (0) 2008.11.24
Oracle 날짜형 데이터의 연산  (0) 2008.06.17
PL/SQL  (0) 2008.02.22
PL/SQL (13) - 커서(cursor)  (0) 2008.02.21
Posted by [PineTree]
ORACLE/ADMIN2008. 9. 12. 02:57

Oracle Data Dictionary Views

반응형

All_all_tables : user가 access할수있는 모든 Table
All_catalog : user가 access할수있는 모든 Table, Views, synonyms, sequence
All_clusters : user가 access할수있는 모든 clusters
All_col_comments : user가 access할수있는 모든 Table,Views에 대한 칼럼comments

 
All_col_privs : user에게 또는 Public에게 허용된 모든 칼럼에 대한 권한.
All_col_privs_made : user가 부여한 칼럼에 대한 권한.
All_col_privs_recd : user에게 또는 Public에게 허용된 모든 칼럼에 대한 권한.
All_coll_types : user가 access 할수 있는 모든 collection type
All_cons_columns : 제약조건에 관련된 칼럼, access할수 있는 대한 정보
All_constraints : access할수 있는 테이블에 대한 제약조건.
All_db_links : user가 access 할수 있는 데이터베이스 link
All_def_audit_opts : 오브젝트가 생성될때 적용될수있는 default오브젝트감사내용.
All_dependencies : user가 access할수있는 오브젝트간의 dependencies(참조,link)
All_directories : user가 access할 수 있는 모든 directories (owner 는 항상 sys)
All_errors : user가 access할수있는 모든 objects(view,procedure,package, function,
packagebody) 에 대한 에러.
All_ind_columns : user가 access할수있는 테이블에 대한 인덱스의 칼럼.
All_ind_partitions : user가 access할수있는 인덱스partition, partition에 대한
storage매개변수, Analyze명령에 의해 결정된 partition통계.
All_indexes : user가 access할수있는 테이블의 인덱스.
이 view의 통계를 수집하기위해, Analyze명령을 사용한다.
병렬partition인텍스탐색을 지원한다.
All_labels : system labels 에 대한 Oracle server view.
All_libraries : user가 access할 수 있는 모든 libraries.
All_lobs : user가 access할 수 있는 모든테이블에 포함된 LOBs.
All_method_params : user가 access할 수 있는 method와 그method의 parameter.
All_method_results :
All_nested_tables : user가 access할수있는테이블내의 Nested Table
All_object_tables : user가 access할수있는테이블의모든정보.
All_objects : user가 access할수있는objects(index partition,table partition,package,
package body, trigger)
All_part_col_statistics : user가 access할 수 있는 테이블partition에 대한 칼럼통계와
막대그래프화된 정보.
All_part_histograms : user가 access할수있는 테이블partition의 histograms에 대한
histogram정보.
All_part_indexes : user가 access할수있는 모든partition된 index의 partition정보.
All_part_key_columns :user가 access할수있는 partition된 objects의 partition key
칼럼에 대한정보
All_part_tables : user가 access할수있는partition된 Table에 대한 partition정보.
All_refresh : user가 access할수있는모든 refresh groups.
All_refresh_children : user가 access할 수 있는 refresh groups 안의 모든objects
All_refs : user가 access할 수 있는 칼럼중 REF칼럼과, REF속성.
All_registered_snapshots : 모든 등록된 snapshots.
All_sequences : user가 access할수있는 sequences.
All_snapshot_logs : 모든 snapshot logs.
All_snapshot_refresh_times : 모든 snapshot refresh times.
All_snapshots : user가 acces할수있는 모든 snapshots.
All_source : user가 access할수있는 모든 stored objects의 text source.
All_synonyms : user가 access할수있는 모든 synonyms.
All_tab_col_statistics : 'User_tab_columns' view안의 정보에대한 칼럼통계와 그래프정보
All_tab_columns : user가 access할수있는모든 table, views, clusters에 대한 칼럼.
이view를 활용하기위해서는 Analyze명령어를 사용한다.
All_tab_comments : user가 access할 수 있는 모든 table, views에 대한 comments.
All_tab_histograms : user가 access할수있는table, views에 대한 histograms.
All_tab_partitions : user가 access할수 있는 각각의 테이블partition에 대한
partition정보, storage parameter, Analyze명령에 의한 통계정보등을 서술한다.
All_tab_privs : user혹은 PUBLIC가 부여받은 오브젝트권한.
All_tab_privs_made : user가 부여한 user권한과 오브젝트권한.
All_tab_privs_recd : user 또는 PUBLIC이 부여받은 오브젝트권한.
All_tables : user가 access할 수 있는 모든 테이블.
Analyze명령으로 이 view의 통계를 얻을 수 있다.
All_triggers : user소유의 trigger, user소유테이블의 trigger, 또는 user가
CREATE ANY TRIGGER 권한을 갖고있다면, 모든 트리거에 대한 정보.
All_trigger_cols : user소유의 trigger, user소유테이블의 trigger, 또는 user가
CREATE ANY TRIGGER 권한을 갖고있다면, 모든 트리거에 대한 칼럼정보.
All_type_attrs : user가 access할 수 있는 type의 attributes.
All_type_methods : user가 access할수있는type의 methods.
All_types : user가 access할 수 있는 type.
All_updatable_columns : join view에서 update가능한 칼럼에 대한 정보.
All_users : 데이터베이스의 모든 user에 대한 정보.
All_views : user가 access할수있는view의 텍스트.
Audit_actions : 감사추적type코드 정보.
catalog : Oracle 5.0 version과의 호환정보를 포함한다.
이 view의 사용은 추천할만하지 못하다.
cat : user_catalog 에 대한 synonym.
chained_rows : ANALYZE LIST CHAINED ROWS 명령에 대한 default table.
clu : user_clusters 테이블의 synonym.
code_pieces : dba_object_size 와 user_object_size view를 create 시에 사용됨.
code_size : dba_object_size 와 user_object_size view를 create 시에 사용됨.
col : Oracle 5.0version 호환정보를 포함하고 있다.
cols : user_tab_columns view 의 synonym.
column_privileges : user가 부여한권한,부여받은권한, owner인권한,
또는 PUBLIC에게 부여받은 권한에 대한 칼럼정보.
Dba_2pc_neighbors : 진행중인 트랜잭션에 대한 연결 및 종료에 대한 정보.
Dba_2pc_pending : recovery를 기다리는 분산된트랜잭션에 대한 정보.
Dba_all_tables : 데이터베이스내의 모든테이블(object table, relational table).
Dba_audit_exists : "AUDIT NOT EXISTS" and "AUDIT EXISTS"에 의해 생성된 감사추적요소.
Dba_audit_object : 시스템내의 모든 object에 대한 감사추적기록.
Dba_audit_session : 세션연결과 종료에 관련된 모든 감사 추적기록.
Dba_audit_statement : GRANT, REVOKE, AUDIT, NOAUDIT, ALTER SYSTEM 관련된 감사추적기록.
Dba_audit_trail : 모든 감사추적요소.
Dba_blockers : 누군가가 스스로 걸지않은 lock이 해제되기를 기다리는 session정보.
Dba_catalog : 모든 데이터베이스 table, views, synonyms 과 sequence에 대한 정보.
Dba_clu_columns : cluster칼럼과 table칼럼의 mapping정보.
Dba_clusters : 데이터베이스내에 있는 모든 cluster.
Dba_col_comments : 데이터베이스내의 모든 table, views의 칼럼에대한 comments.
Dba_col_privs : 데이터베이스내의 칼럼에 대한 모든권한.
Dba_coll_types : 데이터베이스내의 모든 collection type, VARRAYs, nested tables,
object tables 등에 대한 정보.
Dba_constraints : 모든테이블에 대한 constraint(primary, check, unique,
referential integrity, with check option on a view, with read only on a view) 정보.
Dba_cons_columns : constraint 정의안에 있는 access가능한 칼럼에 대한 정보.
Dba_data_files : 데이터베이스파일에 관한 정보.
Dba_db_links : 데이터베이스내의 모든 Link.
Dba_Ddl_locks : 데이터베이스내의 모든 DDL lock과 DDL lock이 현저하게 요구되는 사항에 관한정보.
Dba_dependencies : object 에 대한 Dependence.(REF, HARD)
Dba_directories : 데이터베이스내의 모든 directory objects.
Dba_Dml_locks : 데이터베이스내에 구성된모든 DML lock과 DML lock이 현저하게 요구되는사항에 관한정보.
Dba_errors : 데이터베이스내의 저장된 object에 대해 가장최근에 발생된 error.
Dba_exp_files : export파일에 대한 정보.
Dba_exp_objects : 점진적으로 export 되고있는 object에 대한 정보.
Dba_exp_version : 가장최근에 export된 session에 대한 version 정보.
Dba_extents : 데이터베이스내의 모든 세그먼트를 이루는 extents에 대한 정보.
Dba_free_space : 모든 테이블스페이스내의 free extents의 정보.
Dba_free_space_coalesced : 테이블스페이스내의 합쳐진 공간에 대한 통계정보.
Dba_indexes : 데이터베이스내의 모든 index. 통계정보를 얻기위해 Analyze를 사용.
Dba_ind_columns : 모든테이블과 클러스터에서 인덱스를 구성하는 칼럼에 대한정보.
Dba_ind_partitions : 각각의 index파티션에 대해서, 파티션정보, 파티션에대한
storage 매개변수, Analyze에 결정된 파티션통계자료.
Dba_jobs : 데이터베이스에 있는 모든 Jobs.
Dba_jobs_running : 데이터베이스내에 현재 실행중인 모든 Jobs.
Dba_libraries : 데이터베이스내의 모든 libraries.
Dba_lobs : 모든 테이블에 포함된 LOBs.
Dba_locks : 데이터베이스내에 생성된 모든 lock, latch과 lock,latch가
현저하게 요구되는 사항에 대한 정보.
Dba_method_params : 데이터베이스내에 type에 대한 method 매개변수.
Dba_method_results : 데이터베이스내에 type에 대한 method results.
Dba_nested_tables : 모든테이블내에 포함된 nested table에 대한 정보.
Dba_object_size : PL/SQL object에 대한 size, bytes.
Dba_object_tables : 데이터베이스내에 모든 object tables.
Dba_objects : 데이터베이스내에 모든 objects.(index partition, table partition,
package,package_body,trigger)
Dba_obj_audit_opts : 모든 table, view에 대한 감사 option.
Dba_part_col_statistics : 모든 table 파티션에 대한 칼럼통계와 그래프정보.
Dba_part_histograms : 모든 table 파티션의 histogram에 대한 데이터(endpoint).
Dba_part_indexes : 모든 partition index에 대한 정보.
Dba_part_key_columns : 모든 partition된 object에 대한 분할키칼럼정보.
Dba_part_tables : 모든 partition된 table에 대한 정보.
Dba_priv_audit_opts : 시스템과 user에 의해 감사를 받고있는 시스템 privileges.
Dba_profiles : 모든 profiles과 해당 profile의 limit을 나타냄.
Dba_queue_schedules : 메시지를 전달하는 schedule.
Dba_queue_tables : 데이터베이스내에 생성된 모든 queue테이블의
queue type의 name과 type.
Dba_Queus : 데이터베이스내의 모든 queue에 대한 동작특성.
Dba_rchild : refresh group 안의 모든 children object.
Dba_refresh : 모든 refresh group 에 대한 정보.
Dba_refresh_children : refresh group 안의 모든 object에 대한 정보.
Dba_refs : 데이터베이스내의 모든 테이블의 REF칼럼과, REF 속성을 가진 칼럼.
Dba_refistered_snapshot_groups : 모든 snapshot 사본 그룹.
Dba_registered_snapshots : 지역테이블의 원격snapshot 에 대한 정보.
Dba_rgroup : 모든 refresh group.
Dba_roles : 모든 데이터베이스내에 존재하는 roles.
Dba_role_privs : user와 role에 부여된 role에 대한 정보.
Dba_rollback_segs : rollback segments 에 대한 정보.
Dba_segments : 모든 데이터베이스 segment에 대한 할당된 storage에 대한 정보.
Dba_sequences : 모든 데이터베이스내의 sequences 에 대한 정보.
Dba_snapshot_logs : 모든 데이터베이스내의 snapshot_logs.
Dba_snapshot_refresh_times : snapshot refresh 한 시간.
Dba_snapshots : 모든 데이터베이스내의 snapshots.
Dba_source : 모든 데이터베이스내의 저장object 의 source를포함.
Dba_stmt_audit_opts : system, user에 의한 현재의 감사option에 대한 정보.
Dba_synonyms : 데이터베이스내의 모든 synonyms
Dba_sys_privs : user에게 부여된 system privilege와 role.
Dba_tab_col_statistics : Dba_tab_columns view에 있는정보에 대한 칼럼통계와 그래프정보
Dba_tab_columns : 모든 table, view, cluster에 대한 칼럼정보. Analyze명령어사용.
Dba_tab_comments : 데이터베이스내의 모든 table, view에 대한 주석.
Dba_tab_histograms : 모든 table의 칼럼에 대한 histogram.
Dba_tab_partitions : 각각의 table partition에 대해서, partition level의 partition정보와,
partition의 storage매개변수 ,Analyze 에의해 결정된 여러 partition통계정보.
Dba_tab_privs : 모든 데이터베이스내의 object에 부여된 권한.
Dba_tables : 모든 데이터베이스내의 관계형테이블에 관한정보.Analyze로 통계정보를 얻을수 있다.
Dba_tablespaces : 모든 테이블스페이스에 관한정보.
Dba_triggers : 모든 데이터베이스내의 trigger 정보.
Dba_trigger_cols : 모든 trigger에서 사용된 칼럼정보.
Dba_ts_quotas : 모든 user에게 할당된 tablespace.
Dba_type_attrs : 데이터베이스내의 type에 대한 속성.
Dba_type_methods : 데이터베이스내의 모든 type에 대한 methods.
Dba_types : 데이터베이스내의 모든 추상적데이터type.
Dba_updatable_columns : join view에서 데이터베이스관리자가
update할수있는칼럼정보.
Dba_users : 데이터베이스내의 모든 user정보.
Dba_views : 모든 데이터베이스내의 view의 text.
Dbms_alert_info : 등록된 alert정보.
Dbms_lock_allocated : 사용자에게 할당된 lock정보.
Deptree : utldtree.sql 에의해 생성되며, object의 dependency tree정보를 포함함.
'Sys' user인 경우. 이 object에 관련된 공유커서를 나타내고,
다른 user인 경우공유커서이외의 object를 나타낸다.
다른 user는 공유커서정보를 얻기위해, Sys.deptree를 access할수있다.

Dictionary : data dictionary table, view에 대한 정보.
Dict_columns : data dictionary table, view에 대한 칼럼.
Error_size : Dba_obejct_size 와 user_obejct_size view를 create 할때 사용된다.
Exceptions : 무결성제약조건에 위배되는 정보를 포함. utlexcpt.sql 로 생성.
File_lock : 병렬서버view. 초기화파라미터 GC_FILE_TO_LOCKS 에 명시된,
데이터파일에 PCM lock의 mapping정보.
File_ping : 병렬서버view.각데이타파일에 할당된 block의 수.
GC_FILES_TO_LOCKS 최적값을 구하기 위해 현존하는 데이터파일의
access방법을 결정하는데 이 정보를사용할 수 있다.
FILEXT$ : DBA_DATA_FILES 와 동일. (DBA_DATA_FILES의 사용을 추천)
GLOBAL_NAME : 현제 데이터베이스의 유일한 이름.
HS_ALL_CAPS : 모든 비 Oracle Data store (FDS) 와 관련된 특성에 관한정보.
HS_ALL_DD : 모든 비 Oracle Data store(FDS)에 대한 Data dictionary.
HS_ALL_INITS : 비 Oracle Data store(FDS)에 대한 초기화 매개변수.
HS_BASE_CAPS : 비 Oracle Data store(FDS)에 대한 기본특성에 관한정보.
HS_BASE_DD : 비 Oracle Data store(FDS)에 대한 Data dictionary.
HS_CLASS_CAPS : 비 Oracle Data store(FDS)에 포함된 class-specific 특성정보.
HS_CLASS_DD : 비 Oracle Data store(FDS) class_specific data dictionary.
HS_CLASS_INIT : 비 Oracle Data store(FDS) class-specific 초기화 매개변수.
HS_EXTERNAL_OBJECT_PRIVILEGES : user에게 부여된 object권한.
HS_EXTERNAL_OBJECTS : oracle server에서 access가능한 external obejct.
HS_EXTERNAL_USER_PRIVILEGES : 어느 특정object에 국한되지않은 모든 부여된권한
HS_FDS_CLASS : 비 oracle (FDS) class 에 관한 정보.
HS_FDS_INST : 비 oracle (FDS) instance에 관한정보.
HS_INST_CAPS : instance-specific 특성정보.
HS_INST_DD : 비 oracle (FDS) instance-specific data dictionary 변경정보.
HS_INST_INIT : 비 oracle (FDS) instance-specific 초기화 매개변수정보.
IDEPTREE : UTLDTREE.sql 로 생성하고, 관련tree를 나타냄.

Deptree의 자동정렬버젼.
INDEX_HISTOGRAM : Analyze index...validate structure 명령에 대한정보.
INDEX_STATS : 마지막 Analyze index..validate structure 명령에 대한정보.
NLS_DATABASE_PARAMETERS : 데이터베이스의 NLS 매개변수.
NLS_INSTANCE_PARAMETERS : instance의 NLS 매개변수.
NLS_SESSION_PARAMETERS : user session의 NLS 매개변수.

OBJ : user_objects 의 synonym.
PARSED_PIECES : Dba_object_size, User_object_size view를 생성시에 필요.
PARSED_SIZE : Dba_obejct_size, User_object_size view를 생성시에 필요.
Plan_table : explain plan의 결과에 대한 table. utlxplan.sql로 생성.
Product_component_version : Oracle 제품군의 버전과 상태설명.


Pstubtbl : Pstub utility에 의해 생성된 stub에 관한정보.
Publicsyn : public synonym 에 관한 정보.
Public_dependency : object와 관련된 dependencies.(parent object)
Resource_cost : 각각의 resource에 대한 cost.
Resource_map : 각각의 resource에 대한 정보.(resource name, resource number)
Role_role_privs : 다른 role에 부여된 role정보.(user가 access가능한 role에 한해)
Role_sys_privs : 다른 role에 부여된 system role정보(user가 access가능한role에 한해)
Role_tab_privs : 다른 role에 부여된 table privileges정보.
(user가 access가능한role에 한해)

SEQ : user_sequences 의 synonym.
Session_privs : 현재 user에게 사용가능한 권한.
Session_roles : 현재 user에게 사용가능한 roles.
Source_size : Dba_object_size, User_object_size view를 생성시 필요.
Stmt_audit_option_map : 감사 option type code정보.
Syn : user_synonyms 에 대한 synonym.
Synonyms : Oracle ver 5.와 호환성을 포함. not recommend
Syscatalog : Oracle ver 5.와 호환성을 포함. not recommend
Sysfiles : Oracle ver 5.와 호환성을 포함. not recommend
Syssegobj : Oracle ver 5.와 호환성을 포함. not recommend
System_privilege_map : system privilege code에 대한 정보.
Sys_objects : object ID와 object type 그리고 segment data block주소를 매핑하는정보.
Tab : Oracle ver 5.와 호환성을 포함. not recommend
Table_privileges : user가 부여한, 부여받은, 소유한, 그리고 PUBLIC으로
부여된 object 권한정보. Oracle ver 6.과 호환성을 포함. not recommend.
Table_privilege_map : access 가능한 권한code/권한명칭 정보.
Tabs : User_tables 의 synonym.
Tabquotas : Oracle ver 5.와 호환성을 포함. not recommend
Trusted_servers : 분산환경에서 서버가 신뢰할만한지를 나타냄.
Tp_pitr_check : catpitr.sql 에 의해 생성. 테이블스페이스의 point-in-time복구를
방해할지도 모르는 dependencies혹은 restriction에 관한 정보제공.
Ts_pitr_objects_to_be_dropped : 테이블스페이스의 point-in-time복구수행의 결과
손실된 object에 대한 정보. (point-in-time recovery의 경우만 해당).
User_all_tables : user가 사용가능한 테이블(object table, relational table)정보.
User_arguments : user가 access가능한 object의 매개변수정보.
User_Audit_object : cataudit.sql로 생성. object에 관련된 감사추적기록.
User_Audit_session : cataudit.sql로 생성. user의 연결/종료에 관련된 감사추적기록.
User_Audit_statement : cataudit.sql로 생성. user에 의해 실행된 GRANT,REVOKE,
AUDIT, NOAUDIT, ALTER SYSTEM 명령에 대한 감사추적기록.


User_Audit_trail : user와 관련된 전반적인 사항의 감사추적기록.
User_catalog : user 소유의 table, views, synonyms, sequences 의 이름과 type.
User_clusters : user소유의 cluster.
User_clu_columns : user table 의 칼럼과 cluster칼럼과의 매핑테이블.
User_col_comments : user 의 table, view의 칼럼에 대한 주석.
User_col_privs : user 가 소유한, 부여한, 부여받은 칼럼에 대한 권한.
User_col_privs_made : user 소유 object의 칼럼에 대한 권한.
User_col_privs_recd : user가 부여받은 칼럼에 대한 권한.
User_coll_types : user가 명명한 collection type정보.
User_constraints : user소유 테이블의 제약조건정의.
User_cons_columns : user소유 제약조건에 정의된 칼럼에 대한정보.
User_db_links : user소유 데이터베이스링크에 대한정보.
User_dependencies : user소유 object에 대한 dependencies.
User_errors : user소유 저장 object에 대한 현재의 에러.
User_extents : user소유 object에 속하는 세그먼트의 extent 정보.
User_free_space : user가 access가능한 테이블스페이스내의 free extent 정보.
User_indexes : user 소유의 indexes. Analyze명령을 사용해야함. 병렬서버를 지원.
User_ind_columns : user소유 index 또는 user소유 table 의 칼럼정보.
User_ind_partitions : user소유의 index partition각각에 대한설명과, partition정보,
partition의 storage 매개변수, Analyze명령으로 결정된 여러partition통계
User_jobs : user소유의 모든 job.(export/import, execution)
User_libraries : user소유의 모든 libraries .
User_lobs : user소유의 table에포함된 LOBs정보.
internal LOBs( BLOBs, NCLOBs) 만해당, external LOBs(i.e, BFILES)은 아님.
User_method_params : user type의 method 매개변수.
User_method_results : user type의 method 의 results.
User_nested_tables : user소유 테이블에 포함된 nested tables.
User_object_tables : user가 사용가능한 object table.
User_objects : user소유의 object.(index partition, table partition, package,
packagebody, trigger)
User_object_size : user소유의 PL/SQL object.
User_obj_audit_opts : cataudit.sql로 생성. user소유의 table,view에 대한 감사option
User_part_col_statistics : user소유의 tablepartition정보에 대한 칼럼통계와 그래프정보.
User_part_histograms : user가 access할수있는 table partition의 histogram에 대한
그래프데이터(end-pointer).
User_part_key_columns : user소유의 partition object의 partition key칼럼에 대한정보.
User_part_indexes : 모든 user소유의 partition index의 partition정보.
User_part_tables : user소유의 partition table에 대한 object 레벨의 partition정보.

User_password_limits : user에게 적용된 password profile parameter.
User_queue_tables : user소유 스키마에 생성된 queue table내부의 queues정보.
User_Queues : user스키마의 모든 queue에 대한 동작 특성을 나타냄.
User_refresh : 모든 refresh group.
User_refresh_children : user가 소유한 refresh group 내부의 object에 관한정보.
User_refs : user소유테이블의 object type칼럼중 REF칼럼, REF속성.
User_resource_limits : 현재 user의 resource 한계.
User_role_privs : user에게 부여된 roles.
User_segments : user오브젝트에 포함된 데이터베이스 segments의 storage할당정보.
User_sequences : user 소유의 sequences.
User_snapshots : user 가 볼수있는 snapshots.
User_snapshot_logs : user 소유의 모든 snapshot logs.
User_source : user소유 저장 objects 의 모든 text source.
User_snapshot_refresh_times : snapshot refresh time.
User_synonyms : user소유의 synonym.
User_sys_privs : user에게 부여된 system 권한.
User_tab_col_statistics : user_tab_columns view에 대한 칼럼통계와
그래프정보를 나타냄.
User_tab_columns : user소유의 table, view, cluster의 칼럼정보.(Analyze명령사용)
User_tab_comments : user소유의 table, view에 대한 주석.
User_tab_histograms : user소유 table의 칼럼에 대한 histogram.
User_tab_partitions : user소유 table partition에 대한, partition 레벨의 분할정보와,
partition의 storage매개변수, Analyze에 의해 집계된 여러통계정보.
User_tab_privs : user가 소유한, 부여한, 부여받은 object에 대한 권한 정보.
User_tab_privs_made : user가 소유한 object에 관한 모든 권한.
User_tab_privs_recd : user가 부여받은 object 권한정보.
User_tables : user소유의 relational table에 대한 정보. (Analyze명령사용)
User_tablespaces : user가 access 가능한 tablespaces에 대한 설명.
User_triggers : user가 소유한 triggers 정보.
User_trigger_cols : user가 소유한 또는 user테이블에 있는 trigger안의 column 정보.
User_ts_quotas : user에게 할당된 tablespace quotas 정보.
User_types : 테이블안의 user소유의 type.
User_type_attrs : user type의 속성을 나타냄.
User_type_methods : user type의 methods를 나타냄.
User_updatable_columns : join view에서 사용자에게 update가 허용된 칼럼정보.
User_users : 현재 user에 관한 정보.
User_views : user 소유의 view에 대한 text.

반응형

'ORACLE > ADMIN' 카테고리의 다른 글

[Oracle] Datafile Size 변경하기  (0) 2008.11.19
sqlplus 사용법 (ORACLE)  (0) 2008.11.04
MMAN 백그라운드 프로세스를 통한 자동 공유 메모리 관리  (0) 2008.08.12
Archive log mode 설정방법.  (0) 2008.06.19
다국어 지원을 위한 데이터베이스 구축 방안  (0) 2008.04.26
Posted by [PineTree]
ORACLE/RAC2008. 8. 25. 20:30

Modifying the VIP or VIP Hostname of a 10g Oracle Clusterware Node

반응형
Modifying the VIP or VIP Hostname of a 10g Oracle Clusterware Node
  문서 ID: 공지:276434.1 유형: BULLETIN
  마지막 갱신 날짜: 06-AUG-2007 상태: PUBLISHED
<LINK target="new" href="/images/metalink/generic/kmstyles.css" type=text/css rel=stylesheet>

In this Document
  Purpose
  Scope and Application
  Modifying the VIP or VIP Hostname of a 10g Oracle Clusterware Node
     Planning for VIP changes
     Verifying Current VIP configuration
     Stopping Resources
     Syntax for modifying nodeapps
     Starting Resources Back Up
  References

Applies to:

Oracle Server - Enterprise Edition - Version:
Information in this document applies to any platform.

Purpose

The purpose of this note is to illustrate the changing of a Virtual IP Address (VIP) or VIP hostname or other VIP parameters in an Oracle10g RAC/Oracle Clusterware environment.   Caution: This note can only be used to change the VIP IP address or Hostname or other parameters associated with the VIP. It is not meant to cover changing the public hostname or the public host IP address.

Scope and Application

Oracle 10g use a VIP (Virtual IP) for clients to connect to the database. This VIP is a static IP with a hostname defined and resolved thru DNS. During the installation of Oracle Clusterware users are prompted to enter a Virtual IP and Virtual hostname for each of the node in the cluster. These are stored within the OCR (Oracle Cluster Registry) and different components within 10g HA framework depend on these VIPs.   If for some reason the need arises  to change either the VIP or the VIP/hostname - perhpas due to a data center move, or changing IP subnets in a Data Center, this procedure should be followed.


Modifying the VIP or VIP Hostname of a 10g Oracle Clusterware Node

Planning for VIP changes



Changing the VIP involves modification of the nodeapps, which includes the Virtual IP address, the GSD, the Listener, and Oracle Notification Services (ONS).   The VIP can be modified while the nodeapps are running, however changes will not take effect until the VIP, and hence the nodeapps, are restarted.
Depending on the version of Oracle Clusterware that you are running, other resources on a node, such as database instances and ASM instances, are dependent on the VIP,  so stopping the nodeapps may cause other resources to be stopped - therefore, this change should be made during a scheduled outage.

In most cases, changing Data Centers or IP addresses within a Data Center will already incur an outage for other reasons, because changes need to be made at the operating system level, and servers may even need to be moved - so there is most likely a scheduled outage for this type of maintenence already.

However, in some cases - for example, if a VIP IP address was perhaps mistyped in DNS and needs to be corrected, you may be able to minimize the downtime by only modifying a single node.  Also, as of 10.2.0.3 and higher versions, the ASM instance and DB instances no longer depend on the VIP, so it is possible to stop the nodeapps without bringing down the ASM instance or DB instance - but client connectivity is limited while the VIP is offline.


Verifying Current VIP configuration

Below, we will provide examples on how to modify the nodeapps to change the VIP or VIP hostname.  


1. The first step that should be done is to confirm the current configuration of the VIP.  This is most easily accomplished by running the command:

 srvctl config nodeapps -n <nodename> -a

Using the '-a'  switch will give you the current VIP Hostname, VIP IP address and interface.   The following example is from a Windows System.   Note that the nodename is 'node1' as passed via the '-n'  argument.    

C:\>srvctl config nodeapps -n node1 -a 
VIP exists.: /node1-v/10.148.44.94/255.255.255.0/Public

These outputs show that:

The VIP Hostname is 'node1-v'
The VIP IP address is '10.148.44.94'
The VIP subnet mask is '255.255.255.0'
The Interface Name used by the VIP is called 'Public'

Any of the above configuration parameters associated with the VIP can be changed if they were originally input incorrectly, or perhaps they need to be changed due to external reasons (such as a data center move, or IP change as mentioned above).

 

Note:
In addition, you can also verify the IP address and interface of the VIP by running the 'ifconfig' command or 'ifconfig -a', and looking for the IP address associated with an interface ending in ':1'. The example below, on Linux, shows the interface as 'eth0:1'

=============================
eth0:1 Link encap:Ethernet HWaddr 00:01:03:2C:69:BB
inet addr:192.168.1.125 Bcast:192.168.1.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
...
...
=============================

On the Windows platform, run the command 'ipconfig /all'. This will list all IP addresses bound to a given adapter, including the VIP, but it will not be obvious just from this output which one is the VIP address and which is the actual host IP address.   Therefore, using 'srvctl config nodeapps -n <nodename> -a'  is the surest bet.

2. Once you have determined what the current configuration of the nodeapps is, be sure to make note of what values will remain unchanged, and what the correct/new values that you need will be.   For example, if you are just changing the "Interface Name"   you will need to know what the correct interface name is, but you also need to note the VIP hostname, IP address and subnet mask to ensure that this information is re-entered correctly.

Stopping Resources

3.  Once you are ready to make the change, stop all resources that are dependent on the VIP on a given node. This includes all RAC database instances on that node, as well as the ASM instance on that node, if it exists and is being used   (Note that starting with 10.2.0.3, the dependency between the VIP and the database and ASM instances has been removed):

3a). Stop database instances:
$ srvctl stop instance -d grid -i grid1

Where the database name is specified via the '-d' option, and the instance on the appropriate node is specified with the '-i' option.

Alternatively, to stop the entire database, on all nodes, you can issue the stop database command:

$ srvctl stop database -d grid

3b). To stop the ASM instance on the node, issue the following command:

$ srvctl stop asm -n node1

This command should be issued for each ASM instance using the appropriate node name.  Alternatively, it is also possible to stop these resources via SQL*Plus and/or, on the Windows platform by stopping the associated services.

4. Next, stop the nodeapps using srvctl - i.e:

$ srvctl stop nodeapps -n node1

This will stop the VIP, GSD, Listener, and ONS daemon currently running on the nodes specified

If this is being done as part of a data center move, then you will most likely be stopping these resources prior to moving the equipment.   In that case, you may want to disable the resources after stopping them, to prevent them from re-starting once the machines are brought back up and the Oracle Clusterware stack is started.   You can do this via commands such as:

srvctl disable database -d grid
srvctl disable asm -n node1
srvctl disable nodeapps -n node1
etc.



5. Verify that the VIP is no longer running by executing the 'ifconfig -a' or 'ipconfig /all' command again, and confirm that the IP address is no longer listed as running in the output.

If the interface still shows as online, this may be an indication that a resource which is dependent on the VIP is still running. The crs_stat command can help to show resources that are still online.

6. Make any changes necessary to all nodes' /etc/hosts files (on Unix/Linux), or the
\WINDOWS\System32\drivers\etc\hosts file (on Windows) and/or make the necessary
DNS changes, to associate the new IP address with the old hostname.

Syntax for modifying nodeapps


6. To make the actual modification to the nodeapps, the Oracle Clusterware stack must be up on the node where you are running srvctl from.   To modify the nodeapps  use the 'srvctl modify nodeapps' command with the following syntax:

srvctl modify nodeapps -n <node_name> [-o <oracle_home>] [-A <new_vip_address>]

Options Description:
-n <node_name> Node name.
-o <oracle_home> Oracle home for the cluster database.
-A <new_vip_address> The node level VIP address (<name|ip>/netmask[/if1[|if2|...]]).

As noted previously, any of the above parameters can be changed from their original values  (though it is unlikely that the ORACLE_HOME would change), provided that the match the expected characteristics. 

So - for example, be sure that the interface name specified is the correct name as seen from the OS (refer to Note 283684.1 ), be sure that the subnet mask used for the VIP matches the subnet mask used for the actual public IP addresses, and that the VIP hostname is correctly registered in DNS and/or the hosts file.   An example of the 'modify nodeapps' command is as follows:

$ srvctl modify nodeapps -n node1 -A 192.168.2.125/255.255.255.0/eth0

It should be noted that for the first parameter, you can specify either the hostname associated with the VIP, or the IP address associated with the VIP.    Either way, the srvctl command will actually attempt to resolve the IP to a hostname, or the hostname to an IP, and it will store both entries in the OCR.  So, assuming that the virtual hostname of 'node1-v'  resolves to an IP address 192.168.2.125, the below command would have the same effect as the command using the IP address:

$ srvctl modify nodeapps -n node1 -A  node1-v/255.255.255.0/eth0
Note that the interface names are case senstive on all platforms.  On some platforms, such as Windows, the Interface Name may have spaces in it - i.e. "Local Area Connection 1".   If that is the case, you must enclose the interface name in double quotes - i.e.

srvctl modify nodeapps -n node1 -A 192.168.2.125/255.255.255.0/"Local Area Connection 1"

On Unix and Linux systems, this command should be run as root. Attempting to run this command
as the 'oracle' user or software owner will result in the following error:

PRKO-2117 : This command should be executed as the system privilege user.

On Windows systems, this command should be run as the user who did the original installation.   This account should be an account with Local Administrator privileges on each node.

7.  After making the change, you should verify that it is correct by re-running

'srvctl config nodeapps -n <nodename> -a'

Double-check the output to confirm that all parameters are correct. 

Starting Resources Back Up


7. Once the modify nodeapps has been executed, you can re-start node-level applications via srvctl with the following syntax:

srvctl start nodeapps -n <node_name>
i.e.:

$ srvctl start nodeapps -n rnode1


If any resources (such as database or ASM) were previously disabled, then they should now be re-enabled and re-started as well.



Repeat the same steps for all the nodes in the cluster.  Since SRVCTL is a cluster wide management tool, you can accomplish these tasks for any specific nodes from one node, without the need to login individually to each of the cluster nodes.

Note: If only the IP address is changed, it should not be necessary to make changes to the LISTENER.ORA and TNSNAMES.ORA, provided they are using the vip hostnames for
the 'HOST=' entries.

If changing both the hostname and the VIP for a node, it will be necessary to modify the LISTENER.ORA and change the 'HOST=' entries to the new VIP hostname. This can be done manually, or by using the NETCA to reconfigure the listener. In addition, changes may need to be made to the TNSNAMES.ORA of any clients connecting to the old HOSTNAME.


In addition to modifying the nodeapps after an IP address or network change of some type, it may also be necessary to modify the networks that are stored in the OCR.   If that is the case, please refer to the following note:

Note 283684.1 How to Change Interconnect/Public Interface IP Subnet in a 10g Cluster

For complete srvctl syntax on 10gR1, Refer to Appendix B of the RAC Admin Guide:
http://download-west.oracle.com/docs/cd/B13789_01/rac.101/b10765/toc.htm

For complete srvctl syntax on 10gR2, Refer to Appendix E of the RAC Admin Guide:
http://download-west.oracle.com/docs/cd/B19306_01/rac.102/b14197/toc.htm

반응형

'ORACLE > RAC' 카테고리의 다른 글

클러스터의 시작 및 종료  (0) 2008.12.23
10gR2 RAC Oracle 설치관련 명령어 모음  (0) 2008.12.12
OCR, Voting disk 다른 스토리지로 이관  (0) 2008.08.12
RAC Option Remove  (0) 2008.07.30
CRS 와 10G REAL APPLICATION CLUSTERS  (0) 2008.06.05
Posted by [PineTree]
ORACLE/RAC2008. 8. 12. 18:57

OCR, Voting disk 다른 스토리지로 이관

반응형

지지난 주에 제가 작업 했던 내용 기술합니다.

 

사용하던 스토리지 교체로 ocr 과 voting disk 를 새로운 스토리지로 이관하는 내용입니다.

 

작업 하기 전에 준비되어야 하는 것은 기존 스토리지와 새로운 스토리지 모두 서버에서 바라 볼수 있어야 합니다.

 

이 작업은 하드웨어 엔지니어분께 얘기하면 알아서 해주실 겁니다.

 

기존 스토리지의 볼륨 그룹은 rac1 이고 새로운 스토리지의 볼륨 그룹은 rac2 입니다.

 

ocr은 replace 하고, voting 은 add 하고 delete 합니다.

 

작업 하시기 전 불의의 사고(?) 에 대비해 ocr 과 voting disk 모두 백업 하시고요,

 

저는 이런식으로 했는데 상황에 따라 다른 여러가지 방법이 존재할 수 있을 겁니다.

 

note 428681.1 를 참고 했으며, 꼭 읽어 본 후 작업 하세요.

 

-------------------------------------------------------------------------------------------------------------------

모든 작업은 root 계정으로 한다.

OCR 은 crs 가 start 상태에서 작업하며, voting disk 는 crs 가 down 상태에서 한다.

 

1. OCR 현재 상태 확인

[db1:/oramedia/patch/6455161] su - root
Password:
Sun Microsystems Inc.   SunOS 5.10      Generic January 2005
cdSourcing //.profile-EIS.....
root@db1 # cd $ORA_CRS_HOME
root@db1 # cd bin
root@db1 # ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     524044
         Used space (kbytes)      :       4100
         Available space (kbytes) :     519944
         ID                       : 1967014107
         Device/File Name         : /dev/md/rac1/rdsk/d201
                                    Device/File integrity check succeeded
         Device/File Name         : /dev/md/rac1/rdsk/d202
                                    Device/File integrity check succeeded

         Cluster registry integrity check succeeded

 

2. OCR 다른 스토리지로 이관
root@db1 # ocrconfig -replace ocr /dev/md/rac2/rdsk/d201
root@db1 # ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     524044
         Used space (kbytes)      :       4100
         Available space (kbytes) :     519944
         ID                       : 1967014107
         Device/File Name         : /dev/md/rac2/rdsk/d201
                                    Device/File integrity check succeeded
         Device/File Name         : /dev/md/rac1/rdsk/d202
                                    Device/File integrity check succeeded

         Cluster registry integrity check succeeded

root@db1 # ocrconfig -replace ocrmirror /dev/md/rac2/rdsk/d202
root@db1 # ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     524044
         Used space (kbytes)      :       4100
         Available space (kbytes) :     519944
         ID                       : 1967014107
         Device/File Name         : /dev/md/rac2/rdsk/d201
                                    Device/File integrity check succeeded
         Device/File Name         : /dev/md/rac2/rdsk/d202
                                    Device/File integrity check succeeded

         Cluster registry integrity check succeeded

 

3. voting disk 현재 상태 확인

root@db1 # crsctl query css votedisk
 0.     0    /dev/md/rac1/rdsk/d203
 1.     0    /dev/md/rac1/rdsk/d204
 2.     0    /dev/md/rac1/rdsk/d205

located 3 votedisk(s).
root@db1 # crsctl add css votedisk /dev/md/rac2/rdsk/d203
Cluster is not in a ready state for online disk addition <= 온라인 상태라 등록이 안된다.

 

4. crs 중지 (모든 노드)

root@db1 # crsctl stop crs
Stopping resources. This could take several minutes.
Successfully stopped CRS resources.
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.

 

5. voting disk 추가 및 기존 디스크 제거

root@db1 # crsctl add css votedisk /dev/md/rac2/rdsk/d203 -force
Now formatting voting disk: /dev/md/rac2/rdsk/d203
successful addition of votedisk /dev/md/rac2/rdsk/d203.
root@db1 # crsctl delete css votedisk /dev/md/rac1/rdsk/d203 -force
successful deletion of votedisk /dev/md/rac1/rdsk/d203.
root@db1 # crsctl add css votedisk /dev/md/rac2/rdsk/d204 -force
Now formatting voting disk: /dev/md/rac2/rdsk/d204
successful addition of votedisk /dev/md/rac2/rdsk/d204.
root@db1 # crsctl delete css votedisk /dev/md/rac1/rdsk/d204 -force
successful deletion of votedisk /dev/md/rac1/rdsk/d204.
root@db1 # crsctl add css votedisk /dev/md/rac2/rdsk/d205 -force
Now formatting voting disk: /dev/md/rac2/rdsk/d205
successful addition of votedisk /dev/md/rac2/rdsk/d205.
root@db1 # crsctl delete css votedisk /dev/md/rac1/rdsk/d205 -force
successful deletion of votedisk /dev/md/rac1/rdsk/d205.

 

6. crs 스타트 (모든 노드)
root@db1 # crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly

 

7. voting disk 확인

root@db2 # crsctl query css votedisk
 0.     0    /dev/md/rac2/rdsk/d204
 1.     0    /dev/md/rac2/rdsk/d205
 2.     0    /dev/md/rac2/rdsk/d203

located 3 votedisk(s).

 

8. ocr.loc 파일에서 ocr 경로 확인

root@db2 # cat /var/opt/oracle/ocr.loc
#Device/file /dev/md/rac1/rdsk/d202 getting replaced by device /dev/md/rac2/rdsk/d202
ocrconfig_loc=/dev/md/rac2/rdsk/d201
ocrmirrorconfig_loc=/dev/md/rac2/rdsk/d202

[출처] OCR, Voting disk 다른 스토리지로 이관 (ProDBA) |작성자 해코

반응형

'ORACLE > RAC' 카테고리의 다른 글

10gR2 RAC Oracle 설치관련 명령어 모음  (0) 2008.12.12
Modifying the VIP or VIP Hostname of a 10g Oracle Clusterware Node  (0) 2008.08.25
RAC Option Remove  (0) 2008.07.30
CRS 와 10G REAL APPLICATION CLUSTERS  (0) 2008.06.05
ORACLE RAC 100무정지를 향한 아름다운 도전  (0) 2007.06.25
Posted by [PineTree]
ORACLE/ADMIN2008. 8. 12. 02:19

MMAN 백그라운드 프로세스를 통한 자동 공유 메모리 관리

반응형

(Oracle 10g) MMAN 백그라운드 프로세스를 통한 자동 공유 메모리 관리
========================================

PURPOSE
--------

이 문서에서는 Oracle database 10g의 Self Managing 기능 중의 하나인
자동 공유 메모리 관리 기능에 대하여 알아보고 SGA_TARGET 이라는
새로운 파라미터와 MMAN이라는 새로운 백그라운드 프로세스에 대하여
소개하기로 한다.


Explanation
-----------
1. 개요

SGA_TARGET 파라미터를 이용한 자동 SGA 튜닝이 어떻게 이루어지는지
그 원리를 알아보도록 한다.
자동 SGA 튜닝은 Oracle database 10g의 ADDM을 가능하게 하는 요소인
메모리 advisor가 그 기능을 수행한다.

자동 공유 메모리 관리 기능이 갖는 장점은 다음과 같은 몇 가지가 있다.
첫째, workload가 변함에 따라 자동으로 공유 메모리가 적용이 된다.
둘째, 메모리의 활용률을 극대화한다.
세째, out-of-memory라는 메모리 부족 발생으로 인한 에러를 예방할 수 있다.

즉, 오라클의 공유 메모리 영역 중 Shared pool size, Buffer cache size,
Large pool size, Java pool size를 매뉴얼하게 셋팅할 필요가 없다.
가용한 메모리의 사용을 보다 효과적으로 해주는 것 뿐만 아니라,
메모리 자원을 얻는 데 필요한 비용을 줄여줄 수 있다.
무엇보다 dynamic하고 flexible한 메모리 관리 구조를 통하여
오라클 데이타베이스 관리를 단순화시켜 준다.


2. MMAN 백그라운드 프로세스

공유 메모리의 자동 튜닝을 위하여 MMAN이라는 백그라운드 프로세스가
새로이 등장하였다.
MMAN이라는 백그라운드 프로세스가 5분 마다 주기적으로 수집한
작업 부하(Workload) 정보를 바탕으로 동적으로 구성이 된다.
메모리는 가장 필요한 곳으로 동적으로 할당이 된다.

SPFILE을 사용하면 MMAN이 변경한 파라미터들의 정보가 자동으로
SPFILE에 저장이 된다.
그러므로, 가능한 Oracle 9i 이상부터는 SPFILE 의 사용을 권장한다.
왜냐 하면 다음과 같은 세 가지 장점이 있기 때문이다.

첫째, 각 부분 크기의 권장안을 인스턴스 종료 후에도 보관할 수 있다.
둘째, 저장되어진 각 파라미터들의 사이즈는 데이타베이스 기동 시 할당이 된다.
세째, 각 파라미터들의 최적의 값을 찾는 데 드는 비용을 줄일 수 있다.

이렇게 MMAN 이라는 프로세스에 의해서 자동 공유 메모리 관리 기능이
구현이 되는 것이다.


3. SGA_TARGET 파라미터를 통한 자동 공유 메모리 관리

자동 공유 메모리 관리 기능을 사용하게 되면 오라클 SGA 관련 네 가지
파라미터들을 DBA가 일일이 셋팅할 필요가 없다.
오라클의 공유 메모리 크기는 SGA_TARGET 이라는 파라미터 하나로 다 조절이 된다.
SHARED_POOL_SIZE, DB_CACHE_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE 라는
파라미터들을 구성하는 데 관여하지 않아도 된다는 뜻이다.
과거에는 이러한 파라미터들을 너무 낮게 잡게 되면 성능도 저하될 뿐만 아니라
out-of-memory error인 ORA-4031 ERROR를 자주 만나게 되었고 메모리 낭비도 있었다.

그러나, Oracle database 10g의 New Feature는 SGA_TARGET이라는 새로운
파라미터만 셋팅하여도 되게 설계되었다.
SGA_TARGET이라는 파라미터는 해당 인스턴스에 필요한 SGA의 최대 크기를 나타낸다.
이 파라미터는 SGA 내의 모든 메모리들을 포함한다.
즉, Automatic하게 사이즈가 결정되는 파라미터, 매뉴얼하게 결정되는 파라미터,
startup 시에 할당되는 internal metadata 할당을 다 포함한다.
10g 이전 버젼처럼 SGA의 TOTAL 크기를 정확히 컨트롤하는 것이 어렵지가 않다는 뜻이다.

SGA_TARGET 파라미터를 셋팅할 때에는 다음을 염두에 두어야 한다.

1) SGA 영역 중 자동 구성으로 조절되지 않는 영역이 있는데
Redo Log buffer 와 Fixed SGA 영역, BUFFER KEEP, RECYCLE과 관련된 파라미터,
STREAMS POOL 관련 파라미터들이다.

2) SGA_TARGET 파라미터가 0으로 셋팅되면 자동 공유 메모리 기능은 DISABLE된다.
SGA_TARGET 파라미터의 DEFAULT 값은 0이다.
SGA_TARGET 파라미터가 0이 아닌 어떤 값으로 셋팅이 되어 있어도
SHARED_POOL_SIZE 또는 DB_BLOCK_BUFFERS 와 같은 파라미터들은 여전히
셋팅할 수는 있다.

3) SGA_TARGET 파라미터가 설정되어 있지 않거나 0 으로 셋팅되어 있을 경우,
자동 튜닝 파라미터들은 10g 이전 버젼에서와 같이 관리된다.
단, 예외는 있다. SHARED_POOL_SIZE가 그 경우인데, 내부적인 STARTUP 시
소모되는 오버헤드는 포함이 된다. 이 부분이 SHARED_POOL_SIZE에 포함이 된다.
Oracle 10g 이전 버젼에서는 이 부분이 SHARED_POOL_SIZE에 포함되지 않았으나
10g부터는 포함되게 되어 SHARED_POOL_SIZE를 좀 더 크게 잡아야 한다.
구체적으로 10g에서는 SHARED_POOL_SIZE를 32m 정도 더 크게 잡아야 한다.
예를 들어, Oracle 9i에서 SHARED_POOL_SIZE를 256M를 사용했었다면,
Oracle 10g에서는 같은 효과를 얻으려면 288M 정도로 잡아야 한다.

4) SGA_TARGET 파라미터가 0이 아닌 값으로 셋팅이 되어 있는 경우
자동으로 튜닝되는 SGA 파라미터들은 모두 기본적으로 0으로 셋팅이 된다.
이러한 파라미터들은 자동 공유 메모리 관리 알고리즘에 의하여 자동으로
사이즈가 결정이 된다.
그러나, 만약 이러한 자동 튜닝 파라미터들이 0이 아닌 어떤 값으로
셋팅이 되어 있다면 명시한 값은 자동 튜닝 알고리즘에 의해 최저값을
나타낸다.
예를 들어, SGA_TARGET 파라미터가 8G로 셋팅되어 있고, SHARED_POOL_SIZE가
1G로 셋팅되어 있다면 SHARED_POOL_SIZE는 절대 1G 아래로 떨어지지
않음을 뜻한다.

V$SGA_Dynamic_components 뷰를 조회하면 자동 튜닝 component들의
실제 사이즈를 확인할 수 있다.

SELECT component, current_size/1024/1024
FROM v$sga_dynamic_components;


4. 수동으로 튜닝되는 SGA 파라미터 설정

SGA의 몇몇 구성 파라미터들은 자동으로 튜닝되지 않아서 매뉴얼하게 튜닝을 해야 한다.
다음은 수동으로 튜닝되는 SGA 파라미터들이다.

- KEEP 및 RECYCLE 버퍼 캐시
- 멀티 블록 사이즈 캐시(DB_nK_cache_size)
- 로그 버퍼
- 스트림즈 POOL

수동으로 튜닝되는 파라미터들은 반드시 사용자에 의해 명시되어져야 한다.
이런 파라미터들은 10g 이전 버젼에서와 같이 정밀하게 크기가 제어되어야 한다.
매뉴얼하게 튜닝되는 이러한 파라미터들은 SGA_TARGET에는 포함되지만,
자동으로 튜닝되지는 않는다.
예를 들어, SGA_TARGET 이 8G이면 MANUAL하게 수동으로 조절되는 파라미터들의
사이즈의 합을 1G로 잡으면 7G는 자동으로 오라클이 알아서 설정한다.


5. SGA_TARGET 파라미터의 설정 변경

SGA_TARGET 파라미터의 RESIZING이란 SGA_TARGET 파라미터의
설정 변경을 의미한다.
SGA_TARGET 초기화 파라미터의 특징은 다음과 같다.

첫째, 운영 중에 동적으로 변경이 가능하다.
둘째, SGA_MAX_SIZE 내에서 크기를 증가시킬 수 있다.
세째, 모든 구성 요소의 최저값의 합까지 크기를 감소시키는 것이 가능하다.
또한, SGA_TARGET 파라미터의 설정은 오직 자동으로 튜닝할 수 있는
파라미터들에만 영향을 준다.

예를 들어, SGA_MAX_SIZE가 10G이고, SGA_TARGET이 8G로 가정을 한다.
만약, DB_KEEP_CACHE_SIZE가 1G로 셋팅이 된다고 하면 SGA_TARGET 을
최대 9G까지 늘릴 수 있다.
추가적인 1G는 SGA_TARGET에 의해 조절되는 자동 튜닝 파라미터들에게 분배가 된다.
DB_KEEP_CACHE_SIZE 는 영향을 받지 않는다는 뜻이다.
이것은 SGA_TARGET 파라미터를 줄일 때에도 해당된다.


6. 자동 공유 메모리 관리 비활성화

이 기능을 비활성화하려면 SGA_TARGET 파라미터를 0으로 설정하면 된다.
이렇게 SGA_TARGET 파라미터를 0으로 설정하게 되면 자동 튜닝 파라미터들은
그 당시의 값으로 설정된다.
그리고, 전체 SGA 크기에는 영향을 미치지 않는다.

예) 변경 전 값
SGA_TARGET=8G
SHARED_POOL_SIZE=1G

변경 후 값
SGA_TARGET=0
SHARED_POOL_SIZE=2G
DB_CACHE_SIZE=4G
LARGE_POOL_SIZE=512M
JAVA_POOL_SIZE=512M


SGA_TARGET이 8G로 셋팅되어 있고, SHARED_POOL_SIZE 파라미터가 1G로
설정되어 있다가 SGA_TARGET을 0으로 변경하면 자동 튜닝 파라미터들은
그 당시의 값으로 셋팅되고 전체 SGA 크기는 이전 SGA 크기를 초과하지 않는다.
SHARED_POOL_SIZE가 2G로 셋팅이 된 것은 내부적으로 측정하여 결정된 수치이다.


7. 동적 SGA 파라미터의 수동 변경

자동 튜닝되는 파라미터를 수동으로 변경할 경우 다음과 같은 영향을 가진다.

1) 만약 파라미터의 새로이 반영되는 값이 현재의 값보다 클 경우에는 즉시 반영이 된다.
그러나, 새로이 반영되는 값이 현재의 값보다 작을 경우에는 현재의 값에는
즉시 변화가 없고 최저값으로 셋팅이 된다.

2) 자동으로 튜닝되는 파라미터들을 수동으로 변경하였을 경우에는
SGA의 자동 튜닝 부분에 영향을 준다.
RESIZE 수행 시에 사용되는 메모리는 자동 튜닝 파라미터들로부터
더해지거나 감해질 뿐 매뉴얼하게 조절되는 파라미터들에는 영향을 주지 않는다.


Example
--------
다음은 자동 튜닝 파라미터 설정 시 V$PARAMETER 조회 결과 예이다.

SGA_TARGET=8G
DB_CACHE_SIZE=0
JAVA_POOL_SIZE=0
LARGE_POOL_SIZE=0
SHARED_POOL_SIZE=0

SELECT name, value, isdefault
FROM V$PARAMETER
WHERE name LIKE '%size%';

만약 SGA_TARGET 파라미터가 0이 아닌 값으로 셋팅이 되어 있는 경우
자동 튜닝되는 파라미터들은 값을 명시하지 말라는 뜻이다.
V$PARAMETER 뷰를 조회하였을 때 자동으로 튜닝되는 이러한 SGA 파라미터들의
값은 모두 0이다.
이것이 정상이고, isdefault 컬럼 값은 TRUE 이다.

즉, 자동 공유 메모리 관리 기능을 구현할 때에는 이 파라미터들은
매뉴얼하게 설정을 하지 않으면 된다.


Reference Documents
-------------------
Oracle Database 10g New Features

반응형

'ORACLE > ADMIN' 카테고리의 다른 글

sqlplus 사용법 (ORACLE)  (0) 2008.11.04
Oracle Data Dictionary Views  (0) 2008.09.12
Archive log mode 설정방법.  (0) 2008.06.19
다국어 지원을 위한 데이터베이스 구축 방안  (0) 2008.04.26
오라클에서 유니코드의 사용  (0) 2008.04.26
Posted by [PineTree]

티스토리툴바