투명한 데이터 암호화
Arup Nanda

단 한 줄의 코드도 작성하지 않고 기밀 데이터를 투명하게 암호화합니다.

누군가가 데이터베이스 백업 테이프를 훔쳐가는 것은 조직에게는 최악의 악몽입니다. 물론 보안 시스템을 구축하고 매우 중요한 기밀 자산은 암호화하고 데이터베이스 서버에 방화벽을 설치했습니다. 그러나 침입자가 쉽게 접근했습니다. 침입자는 백업 테이프를 가져가서 틀림 없이 다른 서버에서 데이터베이스를 복원하여 데이터베이스를 시작하고 느긋하게 데이터를 보고 있습니다. 그러한 침입자로부터 데이터베이스 데이터를 보호하는 것은 훌륭한 업무방식이 아닙니다. 그것은 대부분의 법률, 규정, 지침을 준수하기 위한 요구 사항입니다. 이러한 취약성으로부터 데이터베이스를 어떻게 보호할 수 있겠습니까?

한 해결책은 데이터베이스의 기밀 데이터를 암호화하고 별도의 위치에 암호화 키를 저장하는 것입니다. 암호화 키 없이 훔쳐간 데이터는 쓸모가 없습니다. 그러나 두 상반된 개념 간의 균형을 유지해야 합니다. 그 개념이란 애플리케이션이 암호화 키에 액세스할 수 있는 편리성과 키 절도를 방지하는데 필요한 보안성입니다. 그리고 회사와 연방 정부 규정을 준수하기 위해 복잡한 코딩 없이 즉시 솔루션이 필요합니다.

Oracle Database 10g Release 2의 새로운 기능을 사용하여 그렇게 할 수 있습니다. 단 한 줄의 코드도 작성하지 않고 열을 암호화됨으로 선언할 수 있습니다. 사용자가 데이터를 삽입할 때 데이터베이스가 투명하게 데이터를 암호화하여 그 열에 저장합니다. 마찬가지로 사용자가 열을 선택할 때 데이터베이스가 데이터를 자동으로 해독합니다. 이러한 모든 프로세스가 투명하게 애플리케이션의 코드를 변경하지 않고 수행되기 때문에 이 기능에는 이에 잘 어울리는 TDE(투명한 데이터 암호화)라는 이름이 붙여졌습니다.

작동 방식

전에 Oracle Magazine 1/2월호 "데이터 자산의 암호화" 에서 암호화 기초에 대해 다루었습니다. 주요 사항을 다시 설명하면, 암호화는 암호화 알고리즘과 암호화 키를 일반 텍스트 입력 데이터에 적용해야 합니다. 그리고 암호화된 값을 성공적으로 해독하려면 동일한 알고리즘과 키 값을 알아야 합니다.

그 기사에서 저는 오라클이 제공하는 암호화 툴을 사용하여 암호화 기반 구조를 구축하는 방법을 설명하였습니다. 그러나 Oracle Database 10g Release 2와 TDE를 사용할 경우 암호화 기반 구조를 구축할 필요가 없습니다. 단지 암호화할 열을 정의하기만 하면Oracle Database 10g에서 그 열이 포함된 테이블에 대한 암호화 방식의 안전한 암호화 키를 생성하고 지정한 암호화 알고리즘을 사용하여 그 열의 일반 텍스트 데이터를 암호화합니다. 이 테이블 키를 보호하는 것이 매우 중요합니다. Oracle Database 10g는 지갑이라고 부르는 안전한 위치에 저장되는 마스터 키를 사용하여 데이터를 암호화합니다. 이 마스터 키는 데이터베이스 서버의 파일일 수 있습니다. 암호화된 테이블 키는 데이터 딕셔너리에 저장됩니다. 사용자가 암호화됨으로 정의된 열에 데이터를 입력할 경우 그림 1에서와 같이 Oracle Database 10g가 지갑에서 마스터 키를 가져와 데이터 딕셔너리의 암호화 키를 해독하고 입력 값에 대해 그 암호화 키를 사용하고 데이터베이스에 암호화된 데이터를 저장합니다.

그림 1: TDE(투명한 데이터 암호화) 작동 방식

테이블의 모든 열을 암호화할 수 있습니다. 그림 1과 같이 테이블에 열이 4개 있고 열 2와 3을 암호화할 경우 Oracle Database 10g는 테이블에 대하여 하나의 암호화된 테이블 키를 생성하고 그 키를 사용하여 두 열을 암호화합니다. 디스크에 열 1과 열 4의 값을 일반 텍스트로 저장하고 다른 두 열의 값은 암호화된 형식으로 저장됩니다. 데이터가 암호화되어 저장되기 때문에 백업과 아카이브된 로그 등 모든 다운스트림 구성 요소는 암호화된 형식을 갖습니다.

사용자가 암호화된 열을 선택할 경우 Oracle Database 10g는 투명하게 데이터 딕셔너리에서 암호화된 테이블 키를 검색하고 지갑에서 마스터 키를 인출한 다음 테이블 키를 해독합니다. 그런 다음 데이터베이스가 디스크의 암호화된 데이터를 해독하여 일반 텍스트로 사용자에게 반환합니다.

이 암호화된 데이터를 사용할 경우 디스크의 데이터가 도난되어도 훔친 데이터의 일부가 아니라 지갑에 저장되어 있는 마스터 키 없이 데이터를 검색할 수 없습니다. 지갑을 도난 당할 경우에도 지갑 암호 없이 마스터 키를 검색할 수 없습니다. 그러므로 그 침입자는 디스크를 훔치거나 데이터 파일을 복사하더라도 데이터를 해독할 수 없습니다. 이것은 여러 규정과 행정 명령(directive)에 대한 준수 요구 사항을 충족합니다. 그리고 이러한 모든 프로세스가 애플리케이션의 변경이나 복잡한 암호화 작성, 키 관리 시스템 없이 수행됩니다. 그러면 지금부터 TDE를 설정하고 사용하는 방법에 대해 설명하겠습니다.

단 한번의 설정

처음 TDE를 사용할 경우 지갑 위치를 지정하고 지갑 암호를 설정하고 지갑을 열어야 합니다.

1. 지갑 위치를 지정합니다.
처음 TDE를 설정할 경우 마스터 키를 저장할 지갑을 생성해야 합니다. 기본적으로 지갑은 디렉토리 $ORACLE_BASE/admin/$ORACLE_SID/wallet에서 생성됩니다. 그러므로 $ORACLE_BASE is /u01/app/oracle 및 $ORACLE_SID가 SWBT4일 경우 지갑은 디렉토리 /u01/app/oracle/admin/SWBT4/wallet에 저장됩니다. $ORACLE_HOME/network/admin 디렉토리에 저장되는 sqlnet.ora 파일에서 디렉토리를 지정하여 다른 디렉토리를 선택할 수 있습니다. 예를 들어 지갑을 /orawall 디렉토리에 저장하려면 sqlnet.ora 파일에 다음 행을 추가합니다.

ENCRYPTION_WALLET_LOCATION =
 (SOURCE=
   (METHOD=file)
     (METHOD_DATA=
       (DIRECTORY=/orawall)))

이 예제에서는 기본 위치가 선택된 것으로 가정합니다. 또한 지갑 위치를 정기적 백업에 포함시켜야 합니다.

2. 지갑을 생성합니다.
이제 지갑을 생성하고 지갑에 액세스하기 위한 암호를 설정합니다. 이 작업을 하려면 ALTER SYSTEM 권한을 가진 사용자로서 다음 명령을 실행합니다.

alter system set encryption key 
authenticated by "remnant";

이 명령은 다음을 수행합니다.

• 단계 1에서 지정한 위치에 지갑을 생성합니다.
• 지갑의 암호를 "remnant"로 설정합니다.
• 마스터 키를 저장하고 검색할 TDE에 대한 지갑을 엽니다.

암호는 대소문자를 구분하며 큰 따옴표로 묶어야 합니다. 암호 "remnant"는 어느 동적 성능 뷰나 로그에도 일반 텍스트로 표시되지 않습니다.

지갑 열기

지갑은 한 번만 생성되기 때문에 앞의 두 단계는 한 번만 수행해야 합니다. 그러나 데이터베이스 인스턴스가 시작된 후에 지갑을 명시적으로 열어야 합니다. 지갑을 생성할 때(앞의 단계 2) 사용할 지갑을 열어야 합니다. 지갑을 생성하고 암호를 설정한 후 데이터베이스를 열 때마다 동일한 암호를 사용하여 다음과 같이 지갑을 열어야 합니다.

alter system set encryption wallet open authenticated by "remnant";

지갑을 닫으려면 다음 명령을 실행합니다.

alter system set encryption wallet close;

TDE를 사용하려면 지갑을 열어야 합니다. 지갑이 닫혀 있는 경우 암호화되지 않은 열에 액세스할 수 있으나 암호화된 열에는 액세스할 수 없습니다.

열 암호화

TDE를 사용하여 열을 암호화하려면 단지 열 정의에 간단한 절, ENCRYPT를 추가하기만 하면 됩니다. 그러나 추가하기 전에 사용할 암호화 유형과 키 길이를 선택해야 합니다. 이 문제에 대한 자세한 설명은 위에서 언급한 "데이터 자산의 암호화" 기사를 참조하십시오.

정규 스키마에서 다음의 계정 소유자의 테이블이 있다고 가정합시다.

ACC_NO      NUMBER
ACC_NAME    VARCHAR2(30) 
SSN         VARCHAR2(9)

현재 테이블의 모든 데이터는 일반 텍스트입니다. 주민등록번호가 저장된 열 SSN을 변환하여 암호화됨으로 저장하려고 합니다. 다음 명령을 실행할 수 있습니다.

alter table accounts modify (ssn encrypt);

이 명령문은 다음 두 작업을 수행합니다.

• 테이블에 대한 암호화 키를 생성합니다. 암호화된 형식을 사용하여 동일한 테이블의 다른 열을 변경할 경우 동일한 테이블 키가 사용됩니다.
• 열의 모든 값을 암호화된 형식으로 변환합니다.

이 명령은 열의 데이터 유형이나 크기를 변경하지 않으며 트리거 또는 뷰를 생성하지 않습니다.

기본적으로 192비트 키를 사용하는 알고리즘 AES가 암호화에 사용됩니다. 명령에 해당 절을 추가적으로 지정하여 다른 알고리즘을 선택할 수 있습니다. 예를 들어 128비트 AES 암호화를 사용하려면 다음과 같은 명령을 사용합니다.

alter table accounts modify (ssn encrypt using 'AES128');

AES128, AES192, AES256 또는 3DES168(168비트 Triple DES 알고리즘)을 절로서 사용할 수 있습니다. 이 값은 설명할 필요 없이 자명합니다. 예를 들어AES256은 256비트 키를 사용하는 AES(Advanced Encryption Standard) 알고리즘을 의미합니다.

열을 암호화한 후 테이블을 설명할 때 다음이 나타납니다.

SQL> desc accounts

Name	       Null?	      Type
------------   ------------   --------------------------------------------------
ACC_NO		              NUMBER
ACC_NAME		      VARCHAR2(30)
SSN		              VARCHAR2(9) ENCRYPT

데이터 유형 다음의 ENCRYPT 키워드에 주의하십시오. 데이터베이스에서 암호화된 열을 찾으려면 데이터 딕셔너리 뷰 DBA_ENCRYPTED_COLUMNS를 검색할 수 있습니다. (SYS 소유 테이블에 대해 TDE를 사용할 수 없습니다.)

성능 고려 사항

암호화와 해독은 CPU 주기를 소비하기 때문에 성능에 대한 영향을 고려해야 합니다. 테이블의 암호화되지 않은 열에 액세스할 때 성능은 TDE가 없는 테이블과 다르지 않습니다. 그러나 암호화된 열에 액세스할 때 selects 시 해독하는 동안과 inserts 시 암호화하는 동안 작은 성능 오버헤드가 발생하며 따라서 열을 선택적으로 암호화해야 합니다. 열을 암호화할 필요가 없을 경우 다음을 사용하여 TDE를 해제할 수 있습니다.

alter table account modify (ssn decrypt);

또한 인덱스 사용을 고려하십시오. 위 예에서 열 SSN에 in_accounts_ssn이라는 이름의 인덱스가 있다고 가정합시다. ACCOUNTS 테이블에 대한 질의에 다음과 같이 동등 술어가 있을 경우

select * from accounts 
where ssn = '123456789';

인덱스 in_accounts_ssn이 사용됩니다. 질의에서 다음과 같이 LIKE 술어를 사용할 경우

select * from accounts 
where ssn like '123%';

인덱스가 무시되고 전체 테이블 스캔이 사용됩니다. 그 이유는 간단합니다. 인덱스의 B 트리 구조에서 앞 몇 문자가 동일한 값("fraternal", "fraternity" 등)은 물리적으로 서로 가까이 있습니다. LIKE 술어를 처리할 때 Oracle Database 10g는 패턴 일치를 사용하여 인덱스 항목을 검색하고 물리적 근접성이 인덱스 검색 속도를 증가시키므로 전체 테이블 스캔보다 좋습니다.

그러나 열을 암호화할 경우 인덱스의 실제 값이 암호화되었기 때문에 크게 다르고 따라서 인덱스 전역에 흩어져 있습니다. 그렇기 때문에 전체 테이블 스캔보다 인덱스 스캔이 더 많은 리소스를 사용합니다. 그래서 이 LIKE 술어 질의 예제에서 Oracle Database 10g는 인덱스를 무시하고 전체 테이블 스캔을 선택합니다. 동등 술어의 경우 패턴을 따르는 여러 값 대신에 특정 인덱스 항목이 검색됩니다. 그래서 인덱스를 사용한 실행 경로가 전체 테이블 스캔보다 속도가 빠르며 최적기가 인덱스 사용을 선택합니다. 암호화할 열을 결정할 때 암호화가 인덱스에 얼마나 영향을 주는지 고려하고 암호화 열을 포함하는 특정 질의를 재작성해야 하는 경우가 있으니 주의하십시오.

키와 암호 관리

누군가가 테이블 키를 알고 있거나 암호화된 테이블 키를 해독했을지도 모른다는 의심이 들 때에는 어떻게 할까요? 간단하게 테이블에 대한 새 키를 생성할 수 있고 간단한 명령을 실행하여 새로운 테이블 키를 사용하여 암호화된 열 값을 다시 생성할 수 있습니다. 또한 이 작업을 할 때 AES256 등 다른 암호화 알고리즘을 선택할 수도 있습니다. 다음 명령을 수행하여 두 작업을 수행할 수 있습니다.

alter table accounts rekey using 'aes256';

누군가가 지갑 암호를 알고 있을 경우 어떻게 할까요? Oracle Wallet Manager를 사용하여 암호를 변경할 수 있습니다. 이 그래픽 툴을 호출하려면 명령줄에 OWM을 입력합니다(그림 2 참조). 상단 메뉴에서 지갑 -> 열기를 선택하고 지정한 지갑 위치를 선택한 다음 지갑 암호를 입력합니다. 그런 다음 지갑 -> 암호 변경을 선택하여 암호를 변경합니다. 지갑 암호를 변경하여도 키는 변경되지 않습니다.

그림 2: Oracle Wallet Manager

암호화와 함께 "Salt"를 원하십니까?

암호화는 완전히 데이터를 숨기지만 때때로 데이터의 원본 일반 텍스트 값에 반복되는 값이 있을 경우 암호화된 데이터 값을 쉽게 추측할 수 있습니다. 예를 들어 급여 정보 테이블에 반복되는 값이 포함될 수 있습니다. 이 경우에 암호화된 값도 동일하며 침입자가 동일한 급여 항목을 판별할 수 있습니다. 그러한 경우를 방지하기 위해 입력 데이터가 같더라도 암호화된 값이 다르도록 데이터에 "salt"를 추가합니다. 기본적으로 TDE는 salt를 적용합니다. 암호화된 열에 대한 인덱스를 생성할 경우 인덱스에 salt를 포함시킬 수 없습니다. 예를 들어 SSN 열에서 salt를 제거하려면 다음을 실행합니다.

alter table accounts modify 
(ssn encrypt no salt);

salt를 사용하여 암호화된 열에 대한 인덱스를 생성할 경우 다음 예에서와 같이 오류가 발생합니다.

SQL> create index in_acc_01 
on accounts (ssn);

ORA-28338: cannot encrypt indexed column(s) with salt

salt를 사용하여 인덱스된 열을 암호화할 경우에도 동일한 오류가 발생합니다. 마찬가지로 암시적 인덱스 생성이 있을 경우, 즉 열이 기본 키의 일부이거나 unique로 정의된 경우 salt를 사용할 수 없습니다. 또한 열이 외래 키의 일부인 경우에도 salt를 사용할 수 없습니다.

TDE와 Data Pump 사용

기본적으로 Data Pump 엑스포트 유틸리티(EXPDP)를 사용하여 암호화된 열이 있는 테이블의 데이터를 엑스포트할 경우 생성되는 덤프 파일의 데이터는 일반 텍스트입니다(암호화된 열 데이터의 경우에도 마찬가지). 다음 명령은 ACCOUNTS 테이블(암호화된 열이 있음)을 엑스포트하고 경고를 반환합니다.

다음 단계 암호화에 대한 추가 정보를 참조하십시오. oracle.com/technology/oramag/oracle/05-jan/o15security.html www.dbazine.com/olc/olc-articles/nanda11 투명한 데이터 암호화에 대한 추가 정보를 참조하십시오. Oracle Database Advanced Security Administrator's Guide

$ expdp arup/arup tables=accounts

ORA-39173: Encrypted data has been stored unencrypted in dump file set.

이 메시지는 단지 경고이고 오류가 아니며 행이 엑스포트됩니다..

Data Pump 덤프 파일의 암호화된 열 데이터를 보호하려면 테이블을 엑스포트할 때 덤프 파일을 암호로 보호할 수 있습니다. EXPDP 명령에서 ENCRYPTION_PASSWORD 매개변수로 지정되는 이 암호는 이 엑스포트 프로세스에만 적용됩니다. 이것은 지갑 암호가 아닙니다. 목록 1은 암호 "pooh"를 사용하여 실행한 EXPDP 명령입니다. 목록 1의 명령에 대한 출력이 어떤 방식으로 암호 "pooh"를 표시하지 않는지 주목하십시오. 암호는 별표(*) 문자열로 숨겨집니다. 생성되는 덤프 파일에는 TDE로 암호화된 열의 일반 텍스트 데이터가 보이지 않습니다.

코드 목록 1: 암호로 보호된 덤프 파일의 엑스포트

$ expdp arup/arup ENCRYPTION_PASSWORD=pooh tables=accounts
 
Export: Release 10.2.0.0.0 - Beta on Friday, 01 July, 2005 16:14:06
 
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.0.0 - Beta
With the Partitioning, OLAP and Data Mining options
Starting "ARUP"."SYS_EXPORT_TABLE_01":  arup/******** ENCRYPTION_PASSWORD=********* tables=accounts 
Estimate in progress using BLOCKS method...
Processing ...

이 암호화된 덤프 파일을 임포트할 때 목록 2에서와 같이 엑스포트 시 사용한 동일한 암호를 지정해야 합니다.

코드 목록 2: 암호로 보호된 덤프 파일의 임포트

$ impdp arup/arup ENCRYPTION_PASSWORD=pooh tables=accounts table_exists_action=replace
 
Import: Release 10.2.0.0.0 - Beta on Friday, 01 July, 2005 16:04:20
 
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.0.0 - Beta
With the Partitioning, OLAP and Data Mining options
Master table "ARUP"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ARUP"."SYS_IMPORT_TABLE_01":  arup/******** ENCRYPTION_PASSWORD=********* table_exists_action=replace 
Processing ...

다음은 임포트 시 ENCRYPTION_PASSWORD 매개변수를 지정하지 않은 경우의 결과입니다.

$ impdp arup/arup tables=accounts

ORA-39174: Encryption password must 
be supplied.

다음은 틀린 암호를 지정한 경우의 결과입니다.

$ impdp arup/arup ENCRYPTION_PASSWORD
=piglet tables=accounts

ORA-39176: Encryption password is 
incorrect.

원본 엑스포트 유틸리티(EXP)는 암호화된 열이 있는 테이블을 엑스포트할 수 없습니다..

결론

공격으로부터 데이터를 보호하고 기업에 적용되는 많은 법률을 준수하는 일은 간단한 작업이 아닙니다. TDE를 사용하면 코딩이나 키 관리 복잡성 없이 데이터 암호화와 준수를 모두 수행할 수 있으므로 더 전략적 활동에 주력할 수 있습니다.

---

Arup Nanda (arup@proligence.com) 는 뉴욕주 White Plains에 소재한 Starwood Hotels and Resorts의 선임 DBA이며, Rampant Press에서 출판된 Oracle Privacy Security Auditing(2003)의 공동 저자입니다.

select * from v$sqltext where rownum < 100;

select * from v$sqlarea where rownum < 100;

select * from v$sql order by first_load_time desc;

출처 : http://oracleclub.com/article/12449

아시는 분은 아시리라 생각이 됩니다만 행여나 도움이 될까 해서 이렇게 몇 글자 적습니다.

우리 보통 일련번호를 만들 때면 max(일련번호)를 조회한 후 다음 쿼리를 시작하죠. 이 경우에는 두 번의 SQL 문장이 실행되었으므로 매우 비 효율적이라 할 수 있습니다.

그래서 이 단점을 보완한 것이 decode 문을 이용해서 한번에 처리하는 방법이 나왔습니다. 그러나 이 방법 역시 데이터가 적을 경우에는 상관이 없으나 대량의 데이터가 되면 상황은 완전히 달라지죠. 그 많은 데이터를 grouping 해야 하니까요.

그래서 채번 테이블이라는 것을 구성해서 일련번호가 일정 수준 이상까지는 증가하지 못하도록 구성을 하죠.

* PK 코드 + 구분 + 일련번호로 구성된 테이블이 있다고 하면 쿼리는 아래처럼 되겠죠.

select 최종일련번호 + 1

into     :v_주문일련번호

from    주문

where  코드 = '001'

and     구분 = 'off line'

그 후에는 보통 본 테이블에 입력을 하는 쿼리가 오겠죠.

그러나 이것도 여러명의 사용자가 이용할 경우에는 중복에 문제가 있기 때문에 FOR UPDATE를 통한 Lock을 걸어주어야 하므로 결국 잠금으로 인한 부하가 발생할 수 있습니다.

그래서 최종적으로 도출된 것이 인덱스 역방향 정렬을 이용한 채번 방법이라는 것이 있습니다.

/* i_01: 인덱스명 */

insert into 주문(주문일련번호, col2, col3, ...)

select /*+ INDEX_DESC(B i_01) +*/

from   주문 A,

         목록 B

where A.주문일련번호 = B.주문일련번호

and    rownum = 1;

인덱스 역방향 정렬을 이용하여 ROWNUM = 1에 해당하는 데이터를 조회하였으므로 성능도 좋고, 잠금으로 인한 대기현상도 나타나지 않겠죠.

Index of /lecture/oracle/sql

• Parent Directory
• ACCEPT.html
• ALLOCATE_EXTENT.html
• CURSOR.html
• DDL_clause.html
• DEALLOCATE_UNUSED.html
• LOGGING.html
• PARALLEL.html
• SQL_query_subquery.html
• ampersand_insert.html
• analyze.html
• associate_statistics.html
• audit/
• bigfile_create_database.html
• bigfile_tablespace.html
• call.html
• comment.html
• commit.html
• constraint_error.html
• constraint_prototype.html
• date_type_insert.html
• default_value_insert.html
• delete.html
• delete_error.JPG
• direct_load_insert.html
• disassociate_statistics.html
• explain_plan.html
• file_specification.html
• grant.html
• hint.html
• in_line_view_insert.html
• insert.html
• insert_error.JPG
• insert_multitable.html
• insert_multitable_conditional.html
• insert_multitable_first.html
• insert_multitable_unconditional.html
• insert_pivoting.html
• level.JPG
• lock_table.html
• merge.html
• merge_insert.html
• null_row_insert.html
• physical_attributes_clause.html
• purge.html
• rename.html
• returning.html
• revoke.html
• rollback.html
• savepoint.html
• size_clause.html
• sql_start.html
• storage_clause.html
• subquery_insert.html
• sysdate_insert.html
• transaction.html
• truncate.html
• update.html
• update_error.JPG
• with_check_option_insert.html

           MERGE INTO INSERTTABLE
           USING DUAL
           ON (ID = 1)
           WHEN MATCHED THEN
           UPDATE SET
           DATA = 'idoori'
           WHEN NOT MATCHED THEN
           INSERT (ID, DATA)
           VALUES (1, 'mudchobo')

ROWNUM의 동작 원리와 활용 방법
저자 - Tom Kyte

오라클 전문가 Tom Kyte가 ROWNUM의 동작 원리와 활용 방법에 대해 설명합니다.

이번 호의 Ask Tom 컬럼은 지금까지와는 조금 다른 내용을 담고 있습니다. 필자는 오라클 데이터베이스에서 Top-N 쿼리와 페이지네이션(pagination) 쿼리를 구현하는 방법에 대해 자주 질문을 받곤 합니다. 하나의 컬럼을 통해 이러한 질문에 한꺼번에 대답하기 위한 방편으로, < Effective Oracle by Design (Oracle Press, 2003)> 의 내용을 인용하기로 했습니다. 컬럼의 포맷에 맞게 책의 내용이 다소 수정되었음을 참고하시기 바랍니다.

결과 셋의 제한

ROWNUM은 오라클 데이터베이스가 제공하는 마술과도 같은 컬럼입니다. 이 때문에 많은 사용자들이 문제를 겪기도 합니다. 하지만 그 원리와 활용 방법을 이해한다면 매우 유용하게 사용할 수 있습니다. 필자는 주로 두 가지 목적으로 ROWNUM을 사용합니다.

• Top-N 프로세싱: 이 기능은 다른 일부 데이터베이스가 제공하는 LIMIT 구문과 유사합니다.
• 쿼리 내에서의 페이지네이션(pagination) – 특히 웹과 같은 "stateless" 환경에서 자주 활용됩니다. 필자는 asktom.oracle.com 웹 사이트에서도 이 테크닉을 사용하고 있습니다.

두 가지 활용 방안을 설명하기 전에, 먼저 ROWNUM의 동작 원리에 대해 살펴 보기로 하겠습니다

ROWNUM의 동작 원리

ROWNUM은 쿼리 내에서 사용 가능한 (실제 컬럼이 아닌) 가상 컬럼(pseudocolumn)입니다. ROWNUM에는 숫자 1, 2, 3, 4, ... N의 값이 할당됩니다. 여기서 N 은 ROWNUM과 함께 사용하는 로우의 수를 의미합니다. ROWNUM의 값은 로우에 영구적으로 할당되지 않습니다(이는 사람들이 많이 오해하는 부분이기도 합니다). 테이블의 로우는 숫자와 연계되어 참조될 수 없습니다. 따라서 테이블에서 "row 5"를 요청할 수 있는 방법은 없습니다. "row 5"라는 것은 존재하지 않기 때문입니다.

또 ROWNUM 값이 실제로 할당되는 방법에 대해서도 많은 사람들이 오해를 하고 있습니다. ROWNUM 값은 쿼리의 조건절이 처리되고 난 이후, 그리고 sort, aggregation이 수행되기 이전에 할당됩니다. 또 ROWNUM 값은 할당된 이후에만 증가(increment) 됩니다. 따라서 아래 쿼리는 로우를 반환하지 않습니다.

select * 
  from t 
 where ROWNUM > 1;

첫 번째 로우에 대해 ROWNUM > 1의 조건이 True가 아니기 때문에, ROWNUM은 2로 증가하지 않습니다. 아래와 같은 쿼리를 생각해 봅시다.

select ..., ROWNUM
  from t
 where <where clause>
 group by <columns>
having <having clause>
 order by <columns>;

이 쿼리는 다음과 같은 순서로 처리됩니다.

1. FROM/WHERE 절이 먼저 처리됩니다.
2. ROWNUM이 할당되고 FROM/WHERE 절에서 전달되는 각각의 출력 로우에 대해 증가(increment) 됩니다.
3. SELECT가 적용됩니다.
4. GROUP BY 조건이 적용됩니다.
5. HAVING이 적용됩니다.
6. ORDER BY 조건이 적용됩니다.

따라서 아래와 같은 쿼리는 에러가 발생할 수 밖에 없습니다.

select * 
  from emp 
 where ROWNUM <= 5 
 order by sal desc;

이 쿼리는 가장 높은 연봉을 받는 다섯 명의 직원을 조회하기 위한 Top-N 쿼리로 작성되었습니다. 하지만 실제로 쿼리는 5 개의 레코드를 랜덤하게(조회되는 순서대로) 반환하고 salary를 기준으로 정렬합니다. 이 쿼리를 위해서 사용되는 가상코드(pseudocode)가 아래와 같습니다.

ROWNUM = 1
for x in 
( select * from emp )
loop
    exit when NOT(ROWNUM <= 5)
    OUTPUT record to temp
    ROWNUM = ROWNUM+1
end loop
SORT TEMP

위에서 볼 수 있듯 처음의 5 개 레코드를 가져 온후 바로 sorting이 수행됩니다. 쿼리에서 "WHERE ROWNUM = 5" 또는 "WHERE ROWNUM > 5"와 같은 조건은 의미가 없습니다. 이는 ROWNUM 값이 조건자(predicate) 실행 과정에서 로우에 할당되며, 로우가 WHERE 조건에 의해 처리된 이후에만 increment 되기 때문입니다.

올바르게 작성된 쿼리가 아래와 같습니다.

select *
  from  
( select * 
    from emp 
   order by sal desc ) 
 where ROWNUM <= 5;

위 쿼리는 salary를 기준으로 EMP를 내림차순으로 정렬한 후, 상위의 5 개 레코드(Top-5 레코드)를 반환합니다. 아래에서 다시 설명되겠지만, 오라클 데이터베이스가 실제로 전체 결과 셋을 정렬하지 않습니다. (오라클 데이터베이스는 좀 더 지능적인 방식으로 동작합니다.) 하지만 사용자가 얻는 결과는 동일합니다.

ROWNUM을 이용한 Top-N 쿼리 프로세싱

일반적으로 Top-N 쿼리를 실행하는 사용자는 다소 복잡한 쿼리를 실행하고, 그 결과를 정렬한 뒤 상위의 N 개 로우만을 반환하는 방식을 사용합니다. ROWNUM은 Top- N쿼리를 위해 최적화된 기능을 제공합니다. ROWNUM을 사용하면 대량의 결과 셋을 정렬하는 번거로운 과정을 피할 수 있습니다. 먼저 그 개념을 살펴보고 예제를 통해 설명하기로 하겠습니다.

아래와 같은 쿼리가 있다고 가정해 봅시다.

select ... 
  from ... 
 where ... 
 order by columns;

또 이 쿼리가 반환하는 데이터가 수천 개, 수십만 개, 또는 그 이상에 달한다고 가정해 봅시다. 하지만 사용자가 실제로 관심 있는 것은 상위 N개(Top 10, Top 100)의 값입니다. 이 결과를 얻기 위한 방법에는 두 가지가 있습니다.

• 클라이언트 애플리케이션에서 쿼리를 실행하고 상위 N 개의 로우만을 가져오도록 명령
• • 쿼리를 인라인 뷰(inline view)로 활용하고, ROWNUM을 이용하여 결과 셋을 제한 (예: SELECT * FROM (your_query_here) WHERE ROWNUM <= N)

두 번째 접근법은 첫 번째에 비해 월등한 장점을 제공합니다. 그 이유는 두 가지입니다. 첫 번째로, ROWNUM을 사용하면 클라이언트의 부담이 줄어듭니다. 데이터베이스에서 제한된 결과 값만을 전송하기 때문입니다. 두 번째로, 데이터베이스에서 최적화된 프로세싱 방법을 이용하여 Top N 로우를 산출할 수 있습니다. Top-N 쿼리를 실행함으로써, 사용자는 데이터베이스에 추가적인 정보를 전달하게 됩니다. 그 정보란 바로 "나는N 개의 로우에만 관심이 있고, 나머지에 대해서는 관심이 없다"는 메시지입니다. 이제, 정렬(sorting) 작업이 데이터베이스 서버에서 어떤 원리로 실행되는지 설명을 듣고 나면 그 의미를 이해하실 수 있을 것입니다. 샘플 쿼리에 위에서 설명한 두 가지 접근법을 적용해 보기로 합시다.

select * 
  from t 
 order by unindexed_column;

여기서 T가 1백만 개 이상의 레코드를 저장한 큰 테이블이라고, 그리고 각각의 레코드가 100 바이트 이상으로 구성되어 있다고 가정해 봅시다. 그리고 UNINDEXED_COLUMN은 인덱스가 적용되지 않은 컬럼이라고, 또 사용자는 상위 10 개의 로우에만 관심이 있다고 가정하겠습니다. 오라클 데이터베이스는 아래와 같은 순서로 쿼리를 처리합니다.

1. T에 대해 풀 테이블 스캔을 실행합니다.
2. UNINDEXED_COLUMN을 기준으로 T를 정렬합니다. 이 작업은 "full sort"로 진행됩니다.
3. Sort 영역의 메모리가 부족한 경우 임시 익스텐트를 디스크에 스왑하는 작업이 수행됩니다.
4. 임시 익스텐트를 병합하여 상위 10 개의 레코드를 확인합니다.
5.쿼리가 종료되면 임시 익스텐트에 대한 클린업 작업을 수행합니다. .

결과적으로 매우 많은 I/O 작업이 발생합니다. 오라클 데이터베이스가 상위 10 개의 로우를 얻기 위해 전체 테이블을 TEMP 영역으로 복사했을 가능성이 높습니다.

그럼 다음으로, Top-N 쿼리를 오라클 데이터베이스가 개념적으로 어떻게 처리할 수 있는지 살펴 보기로 합시다.

select *
  from 
(select * 
   from t 
  order by unindexed_column)
 where ROWNUM < :N;

오라클 데이터베이스가 위 쿼리를 처리하는 방법이 아래와 같습니다.

1. 앞에서와 마찬가지로 T에 대해 풀-테이블 스캔을 수행합니다(이 과정은 피할 수 없습니다).
2. :N 엘리먼트의 어레이(이 어레이는 메모리에 저장되어 있을 가능성이 높습니다)에서 :N 로우만을 정렬합니다.

상위N 개의 로우는 이 어레이에 정렬된 순서로 입력됩니다. N +1 로우를 가져온 경우, 이 로우를 어레이의 마지막 로우와 비교합니다. 이 로우가 어레이의 N +1 슬롯에 들어가야 하는 것으로 판명되는 경우, 로우는 버려집니다. 그렇지 않은 경우, 로우를 어레이에 추가하여 정렬한 후 기존 로우 중 하나를 삭제합니다. Sort 영역에는 최대 N 개의 로우만이 저장되며, 따라서 1 백만 개의 로우를 정렬하는 대신N 개의 로우만을 정렬하면 됩니다.

이처럼 간단한 개념(어레이의 활용, N개 로우의 정렬)을 이용하여 성능 및 리소스 활용도 면에서 큰 이익을 볼 수 있습니다. (TEMP 공간을 사용하지 않아도 된다는 것을 차치하더라도) 1 백만 개의 로우를 정렬하는 것보다 10 개의 로우를 정렬하는 것이 메모리를 덜 먹는다는 것은 당연합니다.

아래의 테이블 T를 이용하면, 두 가지 접근법이 모두 동일한 결과를 제공하지만 사용되는 리소스는 극적인 차이를 보임을 확인할 수 있습니다.

create table t
as
select dbms_random.value(1,1000000) 
id, 
       rpad('*',40,'*' ) data
  from dual
connect by level <= 100000;

begin
dbms_stats.gather_table_stats
( user, 'T');
end;
/

Now enable tracing, via

exec 
dbms_monitor.session_trace_enable
(waits=>true);

And then run your top-N query with ROWNUM:

select *
  from
(select *
   from t
  order by id)
where rownum <= 10;
 

마지막으로 상위 10 개의 레코드만을 반환하는 쿼리를 실행합니다.

declare
cursor c is
select *
  from t
 order by id;
l_rec c%rowtype;
begin
    open c;
    for i in 1 .. 10
    loop
        fetch c into l_rec;
        exit when c%notfound;
    end loop;
    close c;
end;
/

이 쿼리를 실행한 후, TKPROF를 사용해서 트레이스 결과를 확인할 수 있습니다. 먼저 Top-N 쿼리 수행 후 확인한 트레이스 결과가 Listing 1과 같습니다.

Code Listing 1: ROWNUM을 이용한 Top-N 쿼리

select *
  from
(select *
   from t
  order by id)
where rownum <= 10

call         count     cpu	elapsed   disk     query      current    rows
--------     --------  -------  -------   -------  --------   --------   ------ 
Parse        1         0.00     0.00      0          0        0           0
Execute      1         0.00     0.00      0          0        0           0
Fetch        2         0.04     0.04      0        949        0          10
--------     --------  -------  -------   -------  --------   --------   ------ 
total        4         0.04     0.04      0        949        0          10

Rows                         Row          Source Operation
-----------------            ---------------------------------------------------
10                           COUNT STOPKEY (cr=949 pr=0 pw=0 time=46997 us)
10                           VIEW  (cr=949 pr=0 pw=0 time=46979 us)
10                           SORT ORDER BY STOPKEY (cr=949 pr=0 pw=0 time=46961 us)
100000                       TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400066 us)

이 쿼리는 전체 테이블을 읽어 들인 후, SORT ORDER BY STOPKEY 단계를 이용해서 임시 공간에서 사용되는 로우를 10 개로 제한하고 있습니다. 마지막 Row Source Operation 라인을 주목하시기 바랍니다. 쿼리가 949 번의 논리적 I/O를 수행했으며(cr=949), 물리적 읽기/쓰기는 전혀 발생하지 않았고(pr=0, pw=0), 불과 400066 백만 분의 일초 (0.04 초) 밖에 걸리지 않았습니다. 이 결과를 Listing 2의 실행 결과와 비교해 보시기 바랍니다.

Code Listing 2: ROWNUM을 사용하지 않은 쿼리

SELECT * FROM T ORDER BY ID
call         count     cpu	elapsed   disk     query      current    rows
--------     --------  -------  -------   -------  --------   --------   ------ 
Parse         1        0.00     0.00        0        0        0           0
Execute       2        0.00     0.00        0        0        0           0
Fetch        10        0.35     0.40      155      949        6          10
--------     --------  -------  -------   -------  --------   --------   ------ 
total        13        0.36     0.40      155      949        6          10

Rows                         Row          Source Operation
-----------------            ---------------------------------------------------
10                           SORT ORDER BY (cr=949 pr=155 pw=891 time=401610 us)
100000                       TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400060 us)

Elapsed times include waiting for the following events:

Event waited on                  Times
------------------------------   ------------
direct path write temp           33
direct path read temp             5

결과가 완전히 다른 것을 확인하실 수 있습니다. "elapsed/CPU time"이 크게 증가했으며, 마지막 Row Source Operation 라인을 보면 그 이유를 이해할 수 있습니다. 정렬 작업은 디스크 상에서 수행되었으며, 물리적 쓰기(physical write) 작업이 "pw=891"회 발생했습니다. 또 다이렉트 경로를 통한 읽기/쓰기 작업이 발생했습니다. (10 개가 아닌) 100,000 개의 레코드가 디스크 상에서 정렬되었으며, 이로 인해 쿼리의 실행 시간과 런타임 리소스가 급증하였습니다.

ROWNUM을 이용한 페이지네이션

필자가 ROWNUM을 가장 즐겨 사용하는 대상이 바로 페이지네이션(pagination)입니다. 필자는 결과 셋의 로우 N 에서 로우 M까지를 가져오기 위해 ROWNUM을 사용합니다. 쿼리의 일반적인 형식이 아래와 같습니다.

select * 
  from ( select /*+ FIRST_ROWS(n) */ 
  a.*, ROWNUM rnum 
      from ( your_query_goes_here, 
      with order by ) a 
      where ROWNUM <= 
      :MAX_ROW_TO_FETCH ) 
where rnum  >= :MIN_ROW_TO_FETCH;

where

여기서,

• FIRST_ROWS(N)는 옵티마이저에게 "나는 앞부분의 로우에만 관심이 있고, 그 중 N 개를 최대한 빨리 가져오기를 원한다"는 메시지를 전달하는 의미를 갖습니다.
• :MAX_ROW_TO_FETCH는 결과 셋에서 가져올 마지막 로우로 설정됩니다. 결과 셋에서 50 번째 – 60 번째 로우만을 가져오려 한다면 이 값은 60이 됩니다.
• :MIN_ROW_TO_FETCH는 결과 셋에서 가져올 첫 번째 로우로 설정됩니다. 결과 셋에서 50 번째 – 60 번째 로우만을 가져오려 한다면 이 값은 50이 됩니다.

이 시나리오는 웹 브라우저를 통해 접속한 사용자가 검색을 마치고 그 결과를 기다리고 있는 상황을 가정하고 있습니다. 따라서 첫 번째 결과 페이지(그리고 이어서 두 번째, 세 번째 결과 페이지)를 최대한 빨리 반환해야 할 것입니다. 쿼리를 자세히 살펴 보면, (처음의 :MAX_ROW_TO_FETCH 로우를 반환하는) Top-N 쿼리가 사용되고 있으며, 따라서 위에서 설명한 최적화된 기능을 이용할 수 있음을 알 수 있습니다. 또 네트워크를 통해 클라이언트가 관심을 갖는 로우만을 반환하며, 조회 대상이 아닌 로우는 네트워크로 전송되지 않습니다.

페이지네이션 쿼리를 사용할 때 주의할 점이 하나 있습니다. ORDER BY 구문은 유니크한 컬럼을 대상으로 적용되어야 합니다. 유니크하지 않은 컬럼 값을 대상으로 정렬을 수행해야 한다면 ORDER BY 조건에 별도의 조건을 추가해 주어야 합니다. 예를 들어 SALARY를 기준으로 100 개의 레코드를 정렬하는 상황에서 100 개의 레코드가 모두 동일한 SALARY 값을 갖는다면, 로우의 수를 20-25 개로 제한하는 것은 의미가 없을 것입니다. 여러 개의 중복된 ID 값을 갖는 작은 테이블을 예로 들어 설명해 보겠습니다.

SQL> create table t
  2  as
  3  select mod(level,5) id, 
     trunc(dbms_random.value(1,100)) data 
  4    from dual
  5  connect by level <= 10000;
Table created.

ID 컬럼을 정렬한 후 148-150 번째 로우, 그리고 148–151 번째 로우를 쿼리해 보겠습니다.

SQL> select *
  2    from
  3  (select a.*, rownum rnum
  4     from
  5  (select id, data
  6     from t
  7   order by id) a
  8   where rownum <= 150
  9  )
 10   where rnum >= 148;

 ID           DATA           RNUM
-------       ----------     -----------
0             38             148
0             64             149
0             53             150

SQL>
SQL> select *
  2    from
  3  (select a.*, rownum rnum
  4     from
  5  (select id, data
  6     from t
  7   order by id) a
  8   where rownum <= 151
  9  )
 10   where rnum >= 148;

 ID           DATA           RNUM
-------       ----------     -----------
0             59             148
0             38             149
0             64             150
0             53             151

로우 148의 경우 DATA=38의 결과가 반환되었습니다. 두 번째 쿼리에서는 DATA=59의 결과가 반환되었습니다. 두 가지 쿼리 모두 올바른 결과를 반환하고 있습니다. 쿼리는 데이터를 ID 기준으로 정렬한 후 앞부분의 147 개 로우를 버린 후 그 다음의 3 개 또는 4 개의 로우를 반환합니다. 하지만 ID에 중복값이 너무 많기 때문에, 쿼리는 항상 동일한 결과를 반환함을 보장할 수 없습니다. 이 문제를 해결하려면 ORDER BY 조건에 유니크한 값을 추가해 주어야 합니다. 위의 경우에는 ROWID를 사용하면 됩니다.

SQL> select *
  2    from
  3  (select a.*, rownum rnum
  4     from
  5  (select id, data
  6     from t
  7   order by id, rowid) a
  8   where rownum <= 150
  9  )
 10   where rnum >= 148;

 ID           DATA           RNUM
-------       ----------     -----------
0             45             148
0             99             149
0             41             150

SQL>
SQL> select *
  2    from
  3  (select a.*, rownum rnum
  4     from
  5  (select id, data
  6     from t
  7   order by id, rowid) a
  8   where rownum <= 151
  9  )
 10   where rnum >= 148;

 ID           DATA           RNUM
-------       ----------     -----------
0             45             148
0             99             149
0             41             150
0             45             151

이제 쿼리를 반복 실행해도 동일한 결과를 보장할 수 있게 되었습니다. ROWID는 테이블 내에서 유니크한 값을 가집니다. 따라서 ORDER BY ID 조건과 ORDER BY ROWID 기준을 함께 사용함으로써 사용자가 기대한 순서대로 페이지네이션 쿼리의 결과를 확인할 수 있습니다.

ROWNUM 개념 정리

지금까지 ROWNUM에 관련하여 아래와 같은 개념을 설명하였습니다.

• ROWNUM의 할당 원리와 잘못된 쿼리 작성을 피하는 방법
• ROWNUM이 쿼리 프로세싱에 미치는 영향과 웹 환경의 페이지네이션을 위한 활용 방안
• ROWNUM을 이용하여 Top N쿼리로 인한 TEMP 공간의 사용을 피하고 쿼리 응답 속도를 개선하는 방법

이번 오라클 매거진은 오픈월드 특별호로 기획되었습니다. 필자는 이번 행사를 통해 많은 사람들과 직접 대면할 수 있는 기회를 얻게 된 것을 무척 기쁘게 생각하고 있습니다. 행사에 참석하신다면 필자가 진행하는 세션에도 참석해 주실 것을 기대합니다. (필자는 데이터베이스 및 개발에 관련한 세션을 진행할 예정입니다.) 또 OTN이 주최하는 "Meet the Expert" 행사에도 참가할 것입니다. 이미 오래 전부터 오라클 오픈월드 행사에 참여해 왔지만, 일대일 또는 그룹으로 사용자와 만날 수 있다는 것은 언제나 즐거운 경험입니다. 필자가 진행하는 세션과 OTN 이벤트의 스케줄을 한 번 확인해 보시기 바랍니다.

또 필자는 블로그(tkyte.blogspot.com)를 통해 행사 현장의 뉴스와 사진을 제공해 드리고 있습니다. 그 밖에 OTN 에서도 포드캐스트, 동영상, 프리젠테이션 자료와 같은 행사 컨텐트를 다운로드하실 수 있습니다.

거의 대부분이 오라클 계정설정이나 dbcp 관련 세팅부분이다..
아이피 주소를 로컬이나 127.0.0.1 이나 아님 고정주소값으로 바꾸어서 해보거나
설정된 계정정보로 sqlplus 에서 테스트해보면 답이 나온다..

[DB 및 계정 생성]

root 로그인

# mysql -u root -p

Enter password:

DB 생성

mysql> create database 디비명;

Query OK, 1 row affected (0.00 sec)

계정 생성

mysql> grant usage on *.* to 아이디@localhost identified by "비밀번호";

Query OK, 1 row affected (0.00 sec)

생성한 DB 사용권한 부여

mysql> use 디비명;

Database changed

mysql> grant select, insert, update, delete, create, drop, index, alter on 디비명 to 아이디@localhost;

Query OK, 1 row affected (0.00 sec)

mysql> grant all privileges on 디비명.* to 아이디@localhost identified by "비밀번호";
Query OK, 0 rows affected (0.00 sec)
 
mysql> grant all privileges on 디비명.* to 아이디@"%" identified by "비밀번호";
Query OK, 0 rows affected (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

[DB 백업 및 복구]

전체 DB 백업

# mysqldump -uDBid -p DBname > 백업할파일명.sql

# password :          <=== 패스워드 입력

DB내의 특정 Table만 백업

# mysqldump -uDBid -p DBname TableName > 백업할파일명.sql

# password :          <=== 패스워드 입력

DB 복구

# mysql -uDBid -p DBname TableName < 복구할파일명.sql

# password :          <=== 패스워드 입력

2.3.13. 윈도우 환경에서 MySQL 설치 문제 해결

처음으로 MySQL을 설치하고 구동하면 아마도 여러 가지 에러가 발생할 수 있다. 이 섹션에서는 이러한 문제를 진단하고 에러를 해결하는 방법을 설명하기로 한다.

에러를 바로 잡기 위해 사용할 수 있는 첫 자료는 에러 로그이다.  MySQL 서버는 에러 로그를 사용해서 서버 구동에 문제를 일으키는 에러를 기록한다. 에러 로그는 my.ini가 지정하는 데이터 디렉토리 안에 있다. 디폴트 디렉토리 위치는 C:\Program Files\MySQL\MySQL Server 5.0\data이다.

가능한 에러에 관련된 또 다른 정보는 MySQL서비스 시작될 때 콘솔에서 보여지는 메시지이다. MySQL서버를 서비스 형태로 시작하는 것에 해당하는 에러 메시지를 보기 위해서는, mysqld를 서비스의 형태로 설치한 후에 NET START mysql 명령어를 사용한다.

아래의 예제에서는 처음으로 MySQL을 설치하고 구동 시킬 때 겪을 수 있는 일반적인 에러 메시지를 보여준다:

MySQL서버가 mysql 권한 데이터 베이스 또는 다른 중요 파일을 찾지 못한다면, 아래의 에러 메시지가 나타난다:
System error 1067 has occurred.

Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist

이러한 메시지는 MySQL의 기본 또는 데이터 디렉토리가 디폴트 디렉토리가 아닌 곳에 설치 되어 있을 경우에 종종 발생한다 (C:\Program Files\MySQL\MySQL Server 5.0 및 C:\Program Files\MySQL\MySQL Server 5.0\data).

이와 같은 경우는 MySQL이 새로운 위치에 업그레이드 또는 설치는 되지만, 구성 파일이 새로운 위치를 반영하지 않을 경우에도 발생할 수 있다. 또한, 구형 구성 파일과 새로운 구성 파일이 서로 충돌을 일으킬 경우에도 발생한다. MySQL을 업그레이들 할 때에는 반드시 구형 구성 파일을 삭제하거나 다른 이름으로 저장을 해 두어야 한다는 점을 유의한다.

MySQL을 C:\Program Files\MySQL\MySQL Server 5.0이 아닌 다른 곳에 설치를 했다면, MySQL이 구성 (my.ini) 파일을 통해 이 위치를 알 수 있게끔 해야 한다. my.ini 파일은 윈도우 디렉토리에 두어야 하는데, 일반적으로는 C:\WINDOWS 또는 C:\WINNT가 된다. 명령어 프롬프트에서 아래의 명령어를 입력하면 WINDIR 환경 변수 값으로부터 정확한 위치를 알아낼 수 있다:

C:\> echo %WINDIR%

노트 패드와 같은 텍스트 편집기를 사용해서 옵션 파일을 작성 및 수정할 수도 있다. 예를 들면, MySQL이 E:\mysql에 설치되었고 데이터 디렉토리가 D:\MySQLdata라면, 옵션 파일을 생성하고 [mysqld] 섹션이 basedir과 datadir 파라미터 값을 지정하도록 설정할 수 있다:

[mysqld]

# set basedir to your installation path

basedir=E:/mysql

# set datadir to the location of your data directory

datadir=D:/MySQLdata

윈도우 파라미터는 옵션 파일에서 백슬레쉬가 아닌 슬레쉬를 사용해서 경로를 지정한다는 점을 유의하자. 백슬레쉬를 사용하는 경우에는 두 번 연속 사용한다:

[mysqld]

# set basedir to your installation path

basedir=C:\\Program Files\\MySQL\\MySQL Server 5.1

# set datadir to the location of your data directory

datadir=D:\\MySQLdata

MySQL 구성 파일에 있는 datadir 값을 변경하고자 한다면, 서버를 재 구동하기 전에 반드시 기존 데이터 디렉토리에 있는 모든 내용을 옮겨 놓아야 한다.

기존 MySQL서비스를 중단 및 제거하지 않고 MySQL을 재 설치 또는 업그레이드 하고, 그 다음에 MySQL구성 마법사를 사용해서 MySQL을 설치한다면, 다음과 같은 에러가 나올 수 있다:
Error: Cannot create Windows service for MySql. Error: 0

이것은 구성 마법사가 서비스 설치를 시도하면서 동일한 이름을 가진 기존의 서비스를 찾는 경우에 발생한다.

이 문제를 해결하는 한 가지 방법은, 구성 마법사를 사용할 때 서비스 이름을 mysql이 아닌 다른 이름을 사용하는 것이다. 이것은 새로운 서비스가 정확히 설치 되도록 해 주지만, 대신에 이전의 서비스를 남기게 된다. 비록 이렇게 하는 것이 시스템에는 무해할 지라도, 더 이상 사용하지 않는 이전 서비스는 삭제하는 것이 더욱 바람직하다.

구형 mysql 서비스를 영원히 삭제하기 위해서는, 명령어 라인에서 관리자 권한 사용자 자격으로 아래의 명령어를 실행한다:

C:\> sc delete mysql

[SC] DeleteService SUCCESS

윈도우 버전에서 sc를 사용할 수 없다면, delsrv 유틸리티를  http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/delsrv-o.asp에서 다운 로드 받은 후에 delsrv mysql 신텍스를 사용하도록 한다.

[출처] ::: MySQL Korea ::: - http://www.mysqlkorea.co.kr/

MySQL Korea 사이트의 컨텐츠 소유권은 (주)아이티브릿지에 있으므로
허락 없이 이를 무단전재 하는 경우 저작권법에 민형사적 책임을 지게 되므로
절대 무단 사용을 금해 주시기 바 랍니다
MySQL Korea 저작권 공지 : http://www.mysqlkorea.co.kr/sub.html?mcode=others&scode=04

*** Windows XP에서 mysql 설치하기 ***

- mysql 다운사이트  : http://dev.mysql.com/downloads/
- 테스트 mysql 버전 : mysql-4.0.20a-win

* 설치

1. 압축을 풀고 setup.exe 실행.
2. 설치 두번째 화면에 my.ini 만들어야 한다는 메시지가 있다.
3. 메시지는 무시하고 계속 next 후 finish. 설치 끝

* 환경 설정

1. my.ini 파일을 생성 한 후 c:\windows디렉토리에 저장
---------------- my.ini -------------------
 [mysqld]
 basedir=c:\mysql
 datadir=c:\mysql\data
-------------------------------------------
2. mysql은 윈도우 부팅시 자동으로 시작된다. 수동으로 시작/종료는 command창에서 실행.
--------------------------------------------
 - 시작 : c:\mysql\bin\net start mysql
 - 종료 : c:\mysql\bin\net stop mysql
--------------------------------------------

에러 1)
서비스 이름이 잘못되었습니다
net helpmsg 2185을(를) 입력하면 도움말을 더 볼수있습니다

해결 1)
c:\windows\bin\winmysqladmin 실행 후 다시 net start mysql 명령 실행

에러 2)
LIBMYSQL.dll을 찾을 수 없습니다.

해결 2)
c:\mysql\lib\opt\libmysql.dll 파일을 c:\windows\bin에 복사 후
c:\windows\bin\winmysqladmin을 다시 실행 후 net start mysql 실행

3. root 로그인
c:\mysql\bin\mysql -u root -p mysql(기본 패스워드는 없다. 그냥 enter입력)

4. root 비밀번호 변경
mysql>use mysql;
mysql>update user set password=password('admin12') where user='root'
mysql>\q

5. mysql를 다시 시작한 후 설정한 비밀 번호로 로그인
mysql -u root -padmin12

작성일 : 2008.02.20 11:07



Oracle Commit 한 데이터 복구방법

C:\>sqlplus scott/tiger

SQL*Plus: Release 9.2.0.5.0 - Production on 토 Jan 26 01:09:34 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

다음에 접속됨:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM   DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------

         7369       SMITH     CLERK          7902 80/12/17           800                             20  

.

.

.

14 개의 행이 선택되었습니다.

SQL> delete from emp;

14 행이 삭제되었습니다.

SQL> commit;

커밋이 완료되었습니다.

// 실수로 Table Data를 삭제한 뒤 commit 명령 까지  실행 함.  (--);

SQL> select * from emp;

선택된 레코드가 없습니다.

SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    2     select empno, ename, job, mgr, hiredate, sal, comm, deptno
    3        from emp as of timestamp ( systimestamp - interval '10' minute) ;

14 개의 행이 만들어졌습니다.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM   DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------

         7369       SMITH     CLERK          7902 80/12/17           800                             20  

.

.

.

14 개의 행이 선택되었습니다.

SQL>

SQL> commit;

커밋이 완료되었습니다.

-- 실수로 삭제 한 데이터 복구 완료 .. (^^);

기존 오라클에서 empty_clob() 를 써서 하던 부분을 오라클 10g에서는 standard API로 clob를 사용할 수 있도록
수정되었다고 합니다.

직접 테스트 해보진 않아서 정확한 작동은 보장하진 않습니다 -_-;

1.  property 사용하여 clob 입력

import java.sql.Connection;
import java.sql.DriverManager;
import oracle.jdbc.OracleDriver;
import java.util.Properties;
..........		  

// Load the database details into the variables.
String url      = "jdbc:oracle:thin:@localhost:1521:orcl";
String user     = "scott";
String password = "tiger";

// Create the properties object that holds all database details
Properties props = new Properties();
props.put("user", user );
props.put("password", password);
props.put("SetBigStringTryClob", "true");

// Load the Oracle JDBC driver class.
DriverManager.registerDriver(new OracleDriver());     
 
// Get the database connection 
Connection conn = DriverManager.getConnection( this.url, this.props );
          

PreparedStatement pstmt = conn.prepareStatement(
                                  "INSERT INTO clob_tab VALUES(?)");

// Read a big file(larger than 32765 bytes). 
// Note: method readFile() not listed here. 
// It can be any method that reads a file.
String str = this.readFile("bigFile.txt");


// The string data is automatically transformed into a CLOB and 
// inserted into the database column. 
// Make sure that the Connection property - 'SetBigStringTryClob' is 
// set to true for the insert to happen.
pstmt.setString(1, str);
pstmt.executeUpdate();

2. OraclePreparedStatement 를 사용하여 clob 사용

import java.sql.*;
import java.io.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
..........

// Create SQL query to insert CLOB data and other columns in the database.
String sql = "INSERT INTO clob_tab VALUES(?)";
      
// Read a big file(larger than 32765 bytes). 
// Note: method readFile() not listed here. 
// It can be any method that reads a file.
String str = this.readFile("bigFile.txt");

// Create the OraclePreparedStatement object
opstmt = (OraclePreparedStatement)conn.prepareStatement(sql);

// Use the new method to insert the CLOB data (for data greater or lesser than 32 KB)
opstmt.setStringForClob(1,str);

// Execute the OraclePreparedStatement
opstmt.executeUpdate();
...........

3. clob 데이터 가져오기

.....
// Create a PreparedStatement object
PreparedStatement pstmt = null;
    
// Create a ResultSet to hold the records retrieved.
ResultSet rset = null;
.......


// Create SQL query statement to retrieve records having CLOB data from 
// the database.
String sqlCall = "SELECT clob_col FROM clob_tab";
pstmt= conn.prepareStatement(sqlCall);

// Execute the PrepareStatement
rset = pstmt.executeQuery();
     
String clobVal = null; 
      
// Get the CLOB value larger than 32765 bytes from the resultset
while (rset.next()) {
  clobVal = rset.getString(1);
  System.out.println("CLOB length: "+clobVal.length());     
}

import java.sql.Clob;
import weblogic.jdbc.common.OracleClob;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.CLOB;
import org.apache.commons.dbcp.DelegatingResultSet;

String query1 = "select content from "+table+" where no="+ no + " for update";

con = getConnection();
con.setAutoCommit(false);//꼭 setAutoCommit을 false로 지정

pstmt = con.prepareStatement(query1);
rs = pstmt.executeQuery();

while (rs.next()){

 /**********************************************
 * Tomcat
 * ********************************************/
 Clob clob = rs.getClob(1);
 Writer writer = ((CLOB)clob).getCharacterOutputStream();
 Reader src = new CharArrayReader(contentData.toCharArray());
 char[] buffer = new char[1024];
 int read = 0;
 while ( (read = src.read(buffer,0,1024)) != -1)
 {
   writer.write(buffer, 0, read); // write clob.
 }
 src.close();               
 writer.close();   

 /**********************************************
 * weblogic
 * ********************************************/
 Clob clob = rs.getClob(1);
 Writer writer = ((OracleClob)clob).getCharacterOutputStream();
 Reader src = new CharArrayReader(contentData.toCharArray());
 char[] buffer = new char[1024];
 int read = 0;
 while ( (read = src.read(buffer,0,1024)) != -1)
 {
   writer.write(buffer, 0, read); // write clob.
 }
 src.close();               
 writer.close();

 /**********************************************
 * sunone
 * ********************************************/
 Clob clob = rs.getClob(1);
 Writer characterStream = clob.setCharacterStream(0);
 characterStream.write(contentData);
 characterStream.close();

 /**********************************************
 * interstage

 * ********************************************/

 CLOB clob = ((OracleResultSet)((DelegatingResultSet)rs).getDelegate()).getCLOB(1);
 BufferedWriter writer = new BufferedWriter(clob.getCharacterOutputStream());
 writer.write(form.getContent());
 writer.close();

}

con.commit();
con.setAutoCommit(true);

Oracle9i Dataguard 구성 방법
2006년 1월 22일
민연홍(Oracle OCM), DBA LG카드 중형서버운영파트


미국의 911테러 이후 전세계 IT시스템의 화두는, 자연재해 또는 테러로 인해 서비스 손실을 입게 될 경우에도 이를 복구할 수 있는 Standby 시스템을 구축하는 것이 되었다. 서버뿐만 아니라 디스크 및 백업장비까지 손실되었을 경우에도 다른 지역에서 서비스를 기동할 수 있는 시스템 구성이 각광받게 된 것이다. 이러한 Standby시스템에서 오라클의 dataguard는 데이터 무손실을 구현할 수 있는 방법으로, 한국에서는 여러 금융기관에서도 사용하고 있다. 여기서는 dataguard에 대한 간단한 소개와 그 구성방법에 대해서 알아본다.

Part 1. dataguard 개요 및 아키텍처
  1) dataguard 란 무엇인가?
  2) switchover and failover
  3) standby DB의 종류
  4) dataguard의 서비스 종류
  5) protection mode
  6) dataguard의 시스템 구성(2가지 종류)

Part 2. standby DB 기동방법

PartⅠ. dataguard 개요 및 아키텍처

 1) dataguard 란 무엇인가?

   - primary DB와 standby DB를 동기화시켜, primary DB가 하드웨어 장애 등의 문제가 생겼을 경우 standby DB로 failover 또는
     switchover 시킬 수 있는 시스템 구성을 말한다.
   - Oracle Net을 통해서 primary DB의 변경정보를 standby DB로 적용시켜 운영된다.

      

 2) switchover and failover

   ① 자동실행이 아니라 DBA가 action을 취해야 한다.

   ② switchover
     - OS 작업 또는 서버 PM작업 시 사용(primary -> standby , standby -> primary)

   ③ failover
     - 디스크 fail 등 긴급상황에서 사용, dataguard 재구성 필요

 3) standby DB의 종류

   ① Physical standby database
     - block 대 block 기반으로 primary DB의 redo log를 적용시켜 standby DB를 동기화

   ② Logical standby database
     - 같은 schema 정의로 공유
     - primary DB의 sql 문장을 standby DB에 적용

 4) dataguard의 서비스 종류

   ① Log transport Services
     - primary DB에서 standby DB로 redo log 정보를 자동으로 전송

   ② Log Apply Services
     - redo log를 standby DB에 적용

   ③ Role Management Service
     - 데이터베이스는 primary/standby로 두 가지의 상대적으로 배타적인 role을 가진다.
       Role Management Service는 log transport service와 log apply service를 failover 또는 switchover의 상황에 동적으로
       변경할 수 있다.

 5) protection mode

   ① Maximum Protection - primary    DB와 standby DB의 redo log를 동기화 시킨다.
      standby DB가 네트워크 이상 등의 이유로 standby로의 전송이 안될 경우 primary DB를 halt시킨다.
      데이터는 서로 동기화되어 primary DB에서 commit을 하게 되면 standby DB에서 commit이 완료될 때까지 primary DB에서 commit 완료를 하지 않는다.
      성능에는 문제를 줄 소지가 있으나 failover 상황이 오더라도 데이터 손실은 없다. physical standby DB에만 가능하다.

   ② Maximum availability - Maximum Protection과 마찬가지로 primary DB와 standby DB를 동기화시킨다.
      단, standby DB가 네트워크 문제 등의 이유로 전송이 안될지라도 halt되지는 않는다.
      데이터는 maximum protection과 마찬가지로 primary DB에서 commit을 하게 되면 standby DB에서 commit이 완료될 때까지
      primary DB에서 commit 완료를 하지 않는다. 만약 standby DB가 unavailable상태일 경우 임시로 불일치 시킨다.
      또 다시 standby DB가 available하면 자동으로 동기화 시킨다. 성능에는 문제를 줄 소지가 있으나 failover 상황이 오더라도
      데이터 손실은 거의없다. physical standby, logical standby DB 모두 가능하다.

   ③ Maximum Performance - default protection mode이다. primary data에 대한 protection이 가장 낮다.
      primary database에 transaction이 수행되면 이것을 asynchronous 하게 standby DB에 적용한다.
      즉, maximum protection, maximum availability의 경우에는 standby DB에 적용(commit) 될 때까지 primary db의 transaction 이 적용(commit)되지 않았으나,
      Maximum Performance 모드에서는 비 동기화 시키므로 primary DB에서 standby DB가 transaction 적용이 끝날 때까지
      기다리지 않는다. 즉 standby db의 문제로 인해서 primary DB에 성능영향이 가지 않는다.
      단, failover시에는 약간의 데이터 손실을 가져올 수 있다.

 6) dataguard의 시스템 구성(2가지 종류)

  ① physical standby database 구성 (LGWR process를 사용한 Physical standby DB)
      Archiving to a Physical standby Destination Using the Logwriter Proces


      


     - primary db의 LGWR 프로세스가 standby DB로 redo log를 보내고, standby DB의 RFS 프로세스가 redo log를 standby redo
      log에 적용시킨다. archiving되면 archived redo logs가 되고 이것을 MRP process가 standby DB에 적용시킨다.

   ② logical standby DB 구성
     Archiving to a Logical standby Destination Using the Logwriter Process

      

     - logical standby DB는 primary DB에서 수행된 SQL문장을 LGWR프로세스가 standby DB로 보내고 RFS 프로세스가 받아서
      Archived redo logs에 쓴다. LSP (Logical standby process)가 standby DB에 적용시킨다.

     - primary DB에서 log switch가 일어나게 되면 standby DB의 RFS 프로세스에 이를 알려주어 log switch가 되도록 한다.

  - 주의 standby db의 startup 방식을 보면 아래와 같다. 아래 그림을 기억해두면 편하다.
    

   ① standby DB를 read only mode에서 managed recovery mode로 변경
     - 그대로 명령 또는 shutdown immediate 이후 재기동

     - 첫번째 방법

      SQL> alter database open read only;
      SQL> recover managed standby database disconnect; 

     - 두번째 방법

      SQL> shutdown immediate 
      SQL> startup nomount
      SQL> alter database mount standby database;
      SQL> recover managed standby database disconnect;

   ② shutdown 에서 managed recovery mode 로 변경

      SQL> startup nomount
      SQL> alter database mount standby database;
      SQL> recover managed standby database disconnect;

   ③ managed recovery mode 에서 read only mode 로 변경

      SQL> recover managed standby database cancel;
      SQL> alter database open read only;

   ④ read only standby DB 에서 managed recovery mode로 변경
      (먼저 standby DB에 연결된 모든 세션을 종료)

      SQL> recover managed standby database disconnect;

     - 만약 유저의 세션 때문에 실패할 경우

      SQL> shutdown immediate
      SQL> startup nomount
      SQL> alter database mount standby database;
      SQL> recover managed standby database disconnect;

  - 실습에서 사용 할 primary DB 이름은 MIN 이고, standby DB 이름은 STBY 이다.
  - 여기서는 하나의 서버에서 2개 DB를 구성하는 방법으로 수행한다.
  - 서로 다른 두 대의 서버에서도 같은 방법으로 구성 할 수 있다.
  - primary DB는 /data1/oradata/MIN에 구성되어 있다.
  - standby DB는 /data1/oradata/STBY에 구성되어 있다.

 1) 리스너 설정 및 기동

  아래와 같은 네트워크 설정을 해준다. 각 서버마다 설정해준다.
    - MIN DB에서 설정(primary DB)

vi $ORACLE_HOME/network/admin/listener.ora smsvr1_MIN =    (ADDRESS_LIST =          (ADDRESS= (PROTOCOL= TCP)(Host= smsvr1)(Port=2001))    ) SID_LIST_smsvr1_MIN =  (SID_LIST =    (SID_DESC =      (ORACLE_HOME= /u/pkg/oracle/product/9.2.0)      (SID_NAME = MIN)    )  )

  -STBY DB 에서 설정(physical standby DB)

vi $ORACLE_HOME/network/admin/listener.ora smsvr1_STBY =    (ADDRESS_LIST =          (ADDRESS= (PROTOCOL= TCP)(Host= smsvr1)(Port=2002))    ) SID_LIST_smsvr1_STBY =    (SID_LIST =     (SID_DESC =      (ORACLE_HOME= /u/pkg/oracle/product/9.2.0)     (SID_NAME= STBY)    )   )

 2) tnsnames.ora 설정

  tnsnames.ora 파일을 설정한다. 서로 네트워크가 가능하도록 하는데 이름을 재대로 써야 한다.
  초기화 파라미터에서 log_archvie_dest_2='service=STBY LGWR SYNC AFFIRM' 일 경우
  STBY는 tnsnames.ora 에서의 접속이름을 말한다.

- MIN DB 설정 primary DB에서 설정 ( standby DB로 가는 네트워크 구성)

vi $ORACLE_HOME/network/admin/tnsnames.ora STBY =   (DESCRIPTION =      (ADDRESS = (PROTOCOL= TCP)(Host= smsvr1)(Port= 2002))      (CONNECT_DATA = (SID = STBY))   )

- STBY DB 설정, standby DB에서 설정 (primary DB로 가는 네트워크 구성)

vi $ORACLE_HOME/network/admin/tnsnames.ora MIN=   (DESCRIPTION =      (ADDRESS = (PROTOCOL= TCP)(Host= smsvr1)(Port= 2001))      (CONNECT_DATA = (SID = MIN))   )

 3) 오라클 초기화 파라미터 설정

- MIN DB (primary DB)

vi $ORACLE_HOME/dbs/initMIN.ora db_name=MIN compatible=9.2.0 control_files=('/data1/oradata/MIN/control01.ctl','/data1/oradata/MIN/control02.ctl') shared_pool_size=64M db_cache_size=16M undo_management=auto background_dump_dest=/data1/oradata/MIN/bdump user_dump_dest=/data1/oradata/MIN/udump core_dump_dest=/data1/oradata/MIN/cdump log_archive_start=true log_archive_dest_1="location=/data1/oradata/MIN/archive1" log_archive_format=%t_%s.arc #### 데이터가드를 위해서 변경하지 말 것 remote_archive_enable=true dg_broker_start=true log_archive_dest_state_1=enable log_archive_max_processes=3 standby_file_management=auto db_file_name_convert='/data1/oradata/STBY','/data1/oradata/MIN' log_file_name_convert='/data1/oradata/STBY','/data1/oradata/MIN' standby_archive_dest='/data1/oradata/MIN/archive2' ##### standby DB이면 열고, primary DB이면 닫는다. #fal_server=STBY #fal_client=MIN #lock_name_space=STBY ## primary DB이면 열고, standby DB이면 닫는다. log_archive_dest_2='SERVICE=STBY LGWR SYNC'

- STBY DB (standby DB) -주의할 것은 DB이름은 MIN 이며 instance 이름과 SID는 STBY 이다.
즉, 기동을 할 때에도 ORACLE_SID=STBY 로 설정하여 기동한다.

vi $ORACLE_HOME/dbs/initSTBY.ora db_name=MIN compatible=9.2.0 control_files=('/data1/oradata/STBY/control01.ctl','/data1/oradata/STBY/control02.ctl') shared_pool_size=64M db_cache_size=16M undo_management=auto background_dump_dest=/data1/oradata/STBY/bdump user_dump_dest=/data1/oradata/STBY/udump core_dump_dest=/data1/oradata/STBY/cdump log_archive_start=true log_archive_dest_1="location=/data1/oradata/STBY/archive1" log_archive_format=%t_%s.arc ### 데이터가드를 위해서 변경하지 말 것 remote_archive_enable=true dg_broker_start=true log_archive_dest_state_1=enable log_archive_max_processes=3 standby_file_management=auto db_file_name_convert='/data1/oradata/MIN','/data1/oradata/STBY' log_file_name_convert='/data1/oradata/MIN','/data1/oradata/STBY' standby_archive_dest='/data1/oradata/STBY/archive2' ## standby DB일 경우 아래를 열기, primary DB일 경우 닫기 fal_server=MIN fal_client=STBY lock_name_space=STBY ##primary DB이면 열고, standby DB이면 닫는다. #log_archive_dest_2='SERVICE=MIN LGWR SYNC'

-초기화 파라미터 설정에서 주의해야 할 것을 먼저 보면,
standby_file_management=auto 로 되어 있어야 primary DB에서 물리적인 테이블스페이스 및 데이터파일 추가할 경우 standby DB에 자동으로 생성된다.

standby_file_management=auto로 되어 있을 경우
db_file_name_convert='/data1/oradata/STBY','/data1/oradata/MIN'

log_file_name_convert='/data1/oradata/STBY','/data1/oradata/MIN'
의 파라미터에서 ' A 부분','B 부분'에서 B부분이 자신의 primary DB의 경로를 말하고 A부분이 변환할 standby DB의 경로를 말한다. 파라미터를 확인해보자.

fal_server, fal_client 는 standby DB에서만 사용한다. fal_server는 primary DB를 설정하고 fal_client는 standby DB를 설정해둔다. 이것을 설정할 경우 primary DB와 standby DB에 redo log의 gap이 발생했을 경우 자동으로 맞추어주는 역할을 한다.
fal_server=MIN
fal_client=STBY
lock_name_space는 한 대의 서버에서 primary, standby DB를 운영할 경우 사 용한다. 앞에서 말한 바와 같이 primary , standby DB
db_name은 같으며 instance_name(SID)만 다를 뿐이다. 같은 DB이름을 가진 instance를 한 대의 서버에서 기동하기 위해서는
lock_name_space를 지정해 주어야 한다. 만약 두 대의 서버에서 구성한다면 lock_name_space파라미터는 지정할 필요가 없다.
이것은 standby DB에서만 지정한다.
lock_name_space=STBY

 4) primary DB를 online backup으로 standby DB 위치로 restore

    <MIN DB primary DB>

- primary DB를 24시간 서비스 이므로 shutdown이 불가능한 것을 가정하에 구성한다.
- primary DB를 online backup으로 복사한다.
- online backup을 하는 것이므로 primary DB의 redo log는 standby DB로 전송하지 않는다. standby DB구성 시 자동으로
standby DB에서 redo log가 생성된다.

SQL> select tablespace_name, file_name, bytes/1024/1024 mega from dba_data_files; 

TABLESPACE_NAME	FILE_NAME	MEGA
------------------------------------------------------------------------------------------------------------
SYSTEM	/data1/oradata/MIN/system01.dbf	250
UNDOTBS	/data1/oradata/MIN/undotbs.dbf	100
USERS	/data1/oradata/MIN/users01.dbf	100

SQL> select name , bytes/1024/1024 mega from v$tempfile;

NAME	MEGA
------------------------------------------------------------
/data1/oradata/MIN/temp01.dbf	100

- 여기서는 cp 명령이 standby DB로 전송하는 것을 뜻한다. ftp로 primary db의 백업을 전송한다.
   SQL> alter tablespace system begin backup;
   SQL> !cp /data1/oradata/MIN/system01.dbf /data1/oradata/STBY/system01.dbf
   SQL> alter tablespace system end backup;
   SQL> alter tablespace undotbs begin backup;
   SQL> !cp /data1/oradata/MIN/undotbs.dbf /data1/oradata/STBY/undotbs.dbf
   SQL> alter tablespace undotbs end backup;
   SQL> alter tablespace users begin backup;
   SQL> !cp /data1/oradata/MIN/users01.dbf /data1/oradata/STBY/users01.dbf
   SQL> alter tablespace users end backup;

- tempfile은 그대로 복사를 한다. begin backup, end backup 이 필요 없다. 단 tempfile 이어야 한다.
   SQL> !cp /data1/oradata/MIN/temp01.dbf /data1/oradata/STBY/temp01.dbf

 5) primary DB에서 standby control file을 생성해서 standby DB로 전송

    <MIN DB primary DB>

- primary DB에서 standby control file을 생성해서 standby DB로 전송한다
   SQL> alter database create standby controlfile as '/data1/oradata/STBY/stbyctl.ctl';

 6) standby DB에서 사용할 control file을 배치

    <STBY DB standby DB의 control file>

- standby DB에서 사용할 control file을, standby DB의 초기화 퍼라미터(initSTBY.ora)파일에 있는 control file 위치에 배치한다.
SQL> !cp /data1/oradata/STBY/stbyctl.ctl /data1/oradata/STBY/control01.ctl
SQL> !cp /data1/oradata/STBY/stbyctl.ctl /data1/oradata/STBY/control02.ctl

 7) standby DB 기동

    <STBY DB standby DB 에서 수행>

- standby DB를 기동시킨다. startup mount standby database 라는 명령은 없다. nomount까지 기동한 후 standby DB로 mount를 한다.

   SQL> startup nomount
   SQL> alter database mount standby database;

 8) standby DB에 standby redo log file 생성

    <STBY DB standby DB에서 수행>

우리는 처음으로 standby DB를 구성하였으므로 standby redo log를 추가해 주어야 한다.
여기서 중요한 것은 primary redo log와 standby redo log가 같은 크기를 가지고 있어야 한다.
만약 다를 경우에는 차후에 ORA-16139 media recovery required
에러가 발생하면서 takeover나 failover가 정상적으로 수행되지 않을 수 있다.

SQL> select * from v$logfile;

GROUP#	STATUS TYPE	MEMBER
---------------------------------------------------------------
1	ONLINE	/data1/oradata/STBY/log01a.log
2	ONLINE	/data1/oradata/STBY/log02a.log
3		/data1/oradata/STBY/log03a.log

SQL> alter database add standby logfile
           '/data1/oradata/STBY/stbylog01a.log' size 10M;
SQL> alter database add standby logfile
           '/data1/oradata/STBY/stbylog02a.log' size 10M;
SQL> alter database add standby logfile
           '/data1/oradata/STBY/stbylog03a.log' size 10M;
SQL> select * from v$logfile;

GROUP#STATUS	TYPE	MEMBER
------------------------------------------------------------------
1	ONLINE	/data1/oradata/STBY/log01a.log
2	ONLINE	/data1/oradata/STBY/log02a.log
3	ONLINE	/data1/oradata/STBY/log03a.log
4	STANDBY	/data1/oradata/STBY/stbylog01a.log
5	STANDBY	/data1/oradata/STBY/stbylog02a.log
6	STANDBY	/data1/oradata/STBY/stbylog03a.log

 9) primary DB에 standby redo log file 생성

    <MIN DB primary DB 에서 수행>

서버문제가 발생했을 경우 takeover를 해야 하므로 primary DB도 standby DB가 될 수 있기 때문에
미리 standby redo log를 만든다.

SQL> select * from v$logfile;

GROUP#>	STATUS TYPE	MEMBER
---------------------------------------------------------------
1	ONLINE	/data1/oradata/MIN/log01a.log
2	ONLINE	/data1/oradata/MIN/log02a.log
3	ONLINE	/data1/oradata/MIN/log03a.log

SQL> alter database add standby logfile
          '/data1/oradata/MIN/stbylog01a.log' size 10M;
SQL> alter database add standby logfile
          '/data1/oradata/MIN/stbylog02a.log' size 10M;
SQL> alter database add standby logfile
          '/data1/oradata/MIN/stbylog03a.log' size 10M;

SQL> select * from v$logfile;

GROUP#	STATUS TYPE	MEMBER
-------------------------------------------------------------
1	ONLINE	/data1/oradata/MIN/log01a.log
2	ONLINE	/data1/oradata/MIN/log02a.log
3	ONLINE	/data1/oradata/MIN/log03a.log
4	STANDBY	/data1/oradata/MIN/stbylog01a.log
5	STANDBY	/data1/oradata/MIN/stbylog02a.log
6	STANDBY	/data1/oradata/MIN/stbylog03a.log

 10) standby DB를 recovery managed mode로 기동

    <STBY DB standby DB 에서 수행>

standby DB를 recovery managed mode로 변경한다.
standby DB서버에서 MRP 프로세스가 생긴다.

   SQL> recover managed standby database disconnect;

 11) log switch 적용

    <MIN DB primary DB 에서 수행>

standby DB를 구성하는 동안 primary DB와 gap이 생겼을 것이다.
current redo log를 적용시킨다.

   SQL> alter system archive log current;

 12) primary DB 점검

            PROCESS STATUS
           ------------- ---------------
           ARCH       CLOSING
          ARCH       CLOSING
           LGWR       WRITING SQL> select dest_id "ID",destination,status,target, schedule,process,mountid mid from v$archive_dest order by dest_id;
=> destination 2번에 우리는 service=STBY로 설정하였다. STBY는 tnsnames.ora에 나오는 접속이름이었다. STATUS=VALID 상태이고 STANDBY 이어야 한다.


=> 아무런 에러도 나와서는 안된다. 여기에서 에러가 났다면 primary DB를 먼저 기동하고 standby DB를 기동했을 경우 발생할 수도 있으나, standby로 전송이 안된 것일 수도 있으므로 다른 것도 확인을 해보아야 한다. 만약 standby DB를 먼저 기동하고 recovery managed mode에서 MRP 프로세스를 띄우고 그리고 나서야 primary DB를 기동시켰다면 아래에서는 아무런 메세지도 나와서는 안된다. 예제에서는 primary DB를 먼저 기동했으므로 메세지가 발생했을 것이다.

        MESSAGE         TIMESTAMP
        --------------------------
        --------------------------- SQL> select dest_id id,database_mode db_mode,recovery_mode, protection_mode,standby_logfile_count "SRLs", standby_logfile_active ACTIVE, archived_seq# from v$archive_dest_status;
==> db_mode가 MOUNTED_STANDBY 이어야 한다. recovery_mode 가 managed가 되어 있어야 primary DB에서 전송된 redo log정보를 standby DB에 적용시킬 수 있다.

        ID DB_MODE         RECOVER PROTECTION_MODE      SRLs ACTIVE ARCHIVED_SEQ#
        --- --------------- ------- -------------------- ---- ------ -------------
       1 OPEN            IDLE    MAXIMUM PERFORMANCE     0      0        45
       2 MOUNTED-STANDBY MANAGED MAXIMUM AVAILABILITY    2      0        45
       3 OPEN            IDLE    MAXIMUM PERFORMANCE     0      0         0

        PROCESS STATUS
        ---------------- ------------
        ARCH    CONNECTED
        ARCH    CONNECTED
       MRP0    WAIT_FOR_LOG
     RFS     WRITING
     RFS     ATTACHED

<STBY DB standby DB>

vi $ORACLE_HOME/dbs/initSTBY.ora <원복시킨다> ##standby 이면 열고, primary DB이면 닫는다. fal_server=MIN fal_client=STBY lock_name_space=STBY ##primary DB이면 열고, standby DB이면 닫는다. #log_archive_dest_2='SERVICE=MIN LGWR SYNC'

18) failover
- primary DB가 있는 건물이 무너지고 디스크 이상으로 인해서 DB 데이터파일이 손상되었다.
standby DB를 긴급하게 기동시켜야 한다. MIN DB를 shutdown abort로 Down시키고(디스크 fail 장애),
standby DB를 primary DB로 기동시킨다.

- failover을 한 후에 시스템을 복구해서 MIN DB를 primary DB, STBY DB를 standby DB로 원래대로 구성하려면 Dataguard를 재구성 해야 한다.

즉, failover을 했다면 failover이후에 new primary DB인 STBY DB를 통해서 MIN DB를 standby DB로 구성하고
takeover 시키면 된다.

<MIN DB primary DB>
- 디스크장애상황
SQL> shutdown abort

<STBY DB standby DB>
- recovery managed mode를 해제(cancel)가 아닌 끝내도록(finish) 한다. primary DB로 변경한다.
SQL> recover managed standby database finish;
SQL> alter database commit to switchover to primary;
$ORACLE_HOME/dbs/initSTBY.ora 파일에서 primary DB로 파라미터를 설정하고 기동한다.
SQL> startup

<주의> 만약 standby DB인 STBY DB를 primary DB로 failover하던 도중 ORA-16139 media recovery required
에러가 나면서 recovery 하라고 나온다면?

이럴 경우엔 아래와 같은 명령을 사용하도록 한다.
Log stream을 standby DB에 적용하지 못한 경우에 발생할 수 있다.
이러한 사항은 여러 문제로 인해서 발생할 수 있으므로 발생했다면 오라클에 공식적으로 문의 해야한다.
아래와 같이 skip하면 skip하는 만큼의 gap을 DB에 적용하지 못할 수 있으니 주의해야 한다.
SQL> alter database recover managed standby database finish skip wait;
SQL> alter database commit to switchover to primary;
$ORACLE_HOME/dbs/initSTBY.ora 파일에서 primary DB로 파라미터를설정하고 기동한다.
SQL> startup

[출처] [본문스크랩] Oracle9i Dataguard 구성 방법 |작성자 이정현

//tnsnames.ora : 클라이언트 측 설정

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_2\Hsck\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

RESP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = IP_ADDRESS)(PORT = PORT_NUMBER))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = SIP)    
    )  
  )

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

////////////////////////////////////////////////////

//listener.ora ; Server 측 설정

# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_2\Hsck\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =    
     (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_2\Hsck)
      (PROGRAM=PLSExProc)
    )  
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = b)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      (
       (CONNECT_DATA =
        (SERVICE_NAME = RESP)    
        (SID_NAME=RESP)
    )
    )
  )