연돌군's Blog

Row Level Flashback

- 다음과 같이 kim 을 hong 으로 yeon 을 choi 로 update 를 수행 했습니다.

- 이제 hong 의 data 를 원래의 kim 으로 돌려 보겠습니다.

- 이 때 필요한 쿼리가 있는데 Flashback Version Query 입니다. 이 쿼리는 해당 데이터의 과거 변경 이력을 전부 찾아주는 쿼리 입니다. 9i 에서는 Flashback Query 가 지원되었는데 이는 특정 시점의 변경 내역만 알 수 있었습니다.

꼬한 Oracle 10g 부터 Version Query 가 추가 되면서 특정 시점만 조회하던 것보다 많은 구간을 검색할 수 있게 되었습니다.

Flashback Transaction Query 는 변경 사항을 취소시켜 이전 값으로 돌려주는 쿼리 입니다.

- 우선 데이터를 복구하기 위해 변경 이력을 확인해 보겠습니다.

- 위 내용은 kim 이 Insert(I) 되었다가 hong 으로 update(U) 되었다는 의미입니다. 이때 위와 같이 변경 건수가 적다면 찾기가 쉬울 수 있으나 여러 번의 변경이 이루어졌다면 시간으로 짐작을 해야 합니다. 이 때 변경 시간 확인은

Scn_to_timestamp() 라는 함수를 이용하여 추적 가능합니다.

SQL>select scn_to_timestamp(1156551) from dual;

- 위 내용을 보면 scn 번호 1156551 이 11년 10월 3일 8시 39분 00 초에 발생한 것을 확인할 수 있습니다.

- 이렇게 변경 사항을 찾은 뒤 transaction Query 를 수행하여 원래 데이터로 돌리면 됩니다.

- Flashback version Query (권한 필요 없음)

- Flashback transaction Query (Flashback_transaction_query 라는 뷰를 select 할 수 있는 권한이 필요)

- 권한이 없다면 다음과 같은 오류가 발생

SQL>grant select any transaction to scott;

- 가장 위의 쿼리가 변경해야하는 작업의 쿼리이므로 실행

백업파일이나 아카이브 로그에 문제 있을 때 복구하기

(Clone DB // Export&Import 사용)

- noarchive log mode 로 변경

- alter database noarchivelog;

- begin backup 없이 copy 명령어로 백업 수행

- 백업파일의 SCN 정보다 서로 불일치

-

- conn / as sysdba;

SQL>!rm –rf /home/temp1/example01.dbf ß장애 발생

SQL>shutdown abort;

ORACLE instance shut down.

SQL>startup

- 이제 ORACLE_SID 를 testdb3 로 하고

/data/temp2 아래에서 복구 수행

- db_name='testdb3'

- control_file='/data/temp2/control01.ctl'

- testdb 에서 trace 파일 생성 (컨트롤파일 재성성을 위해)

- resetlogs 용으로 remake.sql 파일 수정

- redo log file 들을 temp2 로 복사

$cp /data/temp1/redo* /data/temp2

-> testdb 의 redo log 파일을 사용할 것이다.

- 복구시도

-> 하지만 복구가 되지 않는다. 아카이브 파일이 없어서 복구가 안됨

-> 히든 파라미터 적용하기

- 파라미터 파일에 다음을 적용하고 DB를 종료하고 다시 시작한다.

SQL>alter session set events '10015 trace name adjust_scn level 1';

- 안 올 라 온 다 ㅡ,ㅡ;;

SQL>alter session set events '10015 trace name adjust_scn level 2';

다시 시도 level 2 로 바꿔준다. 이렇게 3까지 간다.

이것도 안되면

1 startup mount

2. recover database

3. alter database open

이렇게만 실행

- alert log 파일을 보면 인스턴스를 open 시 강제로 terminated 시킨다. 이렇게 될 경우 위 처럼 수행하면 됨.

이렇게 확인 후 파라미터 파일에서 3개의 히든 파라미터를 삭제 시켜주어야 한다.

이 후 전체 DB 를 export 로 백업을 받아야 함.

이렇게 해도 안올라오면 답이 없다

SQL Loader

- SQL LOADER 는 다른 응용프로그램에 저장되어 있는 대량의 데이터를 오라클로 이전할 때 사용되는 유틸리티

- 확인사항

1) NLS_LANG 환경변수 확인

2) ORACLE_HOME 환경변수 확인

3) LD_LIBRARY_PATH 환경변수 확인

4) ORA_NLS33 혹은 ORA_NLS10 환경변수 확인

-> ORA_NLSxx 환경변수는 Character set 을 저장하고 있는 .nlb 파일의 위치를 지정하는 변수

- oracle 7 Ver

->ORA_NLS32=$ORACLE_HOME/ocommon/nls/admin/data

- Oracle 9 Ver

->ORA_NLS33 = $ORACLE_HOME/ocommon/nls/admin/data

->ORA_NLS10 = $ORACLE_HOME/nls/data

- Oracle 8,8i,9 Ver

->ORA_NLS33 = $ORACLE_HOME/ocommon/nls/admin/data

- Oracle 10g 이상

->ORA_NLS10=$ORACLE_HOME/nls/data

<SQL Loader>

- SQL Loader 의 특징

1. 입력 파일은 여러 개 가질 수 있다

2. 입력 파일의 여러 개의 레코드를 입력 시 하나의 논리적 레코드로 가공하여 한꺼번에 입력이 가능

3. 입력 필드가 고정길이나 가변길이로 가능

4. 입력 데이터가 문자, Binary, 날짜, packed 10진 데이터, zone 10진 데이터 등의 여러 가지 형태가 가능

5. 하나의 입력 데이터로 여러 테이블에 동시 입력이 가능

6. 테이블의 기존 데이터를 변경하거나 지우고 다시 입력하거나 추가 하는 등의 작업도 가능

7. SQL 함수를 사용하여 입력 가능

8. Sequence 를 사용하여 데이터를 순차적으로 입력 가능

$sqlldr (옵션 확인)

- 위 옵션에서 errors 옵션을 변경 후 작업

- Default 50 개에서 이상으로 변경 후 작업하는 것을 권장

- 만약 50개의 error 가 발생하면 더 이상 진행이 되지 않으므로 큰 용량의 데이터를 입력할 경우 충분하게 설정하는 것을 권장

EX 1> 입력할 데이터가 외부 프로그램 자료일 경우

- 위의 엑셀 파일을 csv(쉼표로 구분) 파일로 저장 (dept2.csv)

- dept2.csv 를 오라클 홈 으로 옮김.

- 입력할 테이블 dept2 를 생성

- SQL Loader 의 control file 을 생성

load data

infile "/home/oracle/dept2.csv"

into table dept2

fields terminated by ','

(deptno,dname,loc)

- 다음과 같이 실행

- 결과 확인

- 작업을 수행하면 Control file 과 같은 이름으로 확장자만 .log 로 작업 내용이 기록됨

EX 2> 입력 데이터가 control file 내부에 있을 경우

load data

infile *

replace

into table dept20

fields terminated by ',' optionally enclosed by '"'

(deptno,dname,loc)

begindata

12,"서울점","강남구"

22,"대전점","유성구"

33,"제주점","서귀포시"

41,"서울본사","서울"    

다음과 같이 dept2_02.ctl 파일을 생성 합니다.

load data

infile * ß 입력 데이터가 control file 내부에 있을 경우

Append ß 기존 내용 뒤에 신규 내용이 추가

into table dept20

fields terminated by ',' optionally enclosed by '"' ß 한글이"" 로 묶여 있다는 뜻

(deptno,dname,loc)

Begindata ß데이터 시작 부분

12,"서울점","강남구"

22,"대전점","유성구"

33,"제주점","서귀포시"

41,"서울본사","서울"

EX 3> 각종 파일 사용하기 (Bad file, discard file)

SQL>alter table dept2

2 modify deptno number(2);

- 오류 발생을 위해 다음과 같이 수정

- Data 확인

- 다음과 같이 수행

- 300,"제주점","서귀포시" 데이터가 누락됨

- dept2_02.log 파일 확인

- dept2_02.bad 파일을 수정하여 다시 입력

Overview of Primary Components

Oracle Instance

- Oracle Database 에 연결하는 것을 의미한다.

- 항상 하나의 DB에 한 개의 Instance 가 Open 됨

- Memory structures 와 Background Process structures 로 구성됨.

Oracle Memory Structure

- Oracle 의 메모리 구조는 SGA 와 PGA 로 구성

- System Global Area (SGA) :

→ 인스턴스가 시동되면 할당되고 Oracle Instance 의 주요한 요소

→ 모든 프로세스가 공유

- Program Global area (PGA) :

→ 서버 프로세스가 시작되어지면 할당된다.

→ 사용자별로 생성되어 별도로 사용됨.

→ Parse 작업 수행

System Global Area

- SGA 는 여러 개의 Memory structure 들로 구성됨.

→ Shared Pool

→ Database Buffer Cache

→ Redo Log Buffer

→ Java Pool

→ Large Pool

- SGA_MAX_SIZE parameter 로 변경 가능

Shared Pool

- 최근 실행된 SQL 구문들과 최근에 사용되어진 data 정의들이 저장됨

- 주요 기능을 수행하는 2개의 주요 메모리 구조로 구성되어짐

1. Library Cache

2. Data Dictionary Cache

- SHARED_POOL_SIZE 파라미터로 설정 됨

SQL>alter system set

2 shared_pool_size = 64M;

Library Cache

- 가장 최근 사용 되어진 SQL 과 PL/SQL 들에 관한 정보가 저장됨

- 일반적으로 사용되어지는 구문들의 공유가 가능함

- Soft Parse 과정을 위한 메모리 구조

- LRU Algorithm 에 의하여 메모리 관리가 이루어진다.

- Shared Pool 의 크기에 의해 Library Cache 의 크기가 결정된다.

Data Dictionary Cache

- DB 에서 최근 사용 되어진 사용자 정의들의 집합.

- database file, table, index,column,user,privilege 등에 관한 정보가 포함됨

- Parse 과정에서 서버 프로세스가 접근하게 되는데 이 때 구문의 유효성과 개체 이름을 확인하게 된다.

- Data Dictionary Cache 안에 원하는 정보가 존재 한다면 쿼리와 DML 성능에 향상을 가져오게 된다.

- Hard Parse 를 위한 메모리 구조

- Shared Pool 의 크기에 의해 Library Cache 의 크기가 결정된다.

Database Buffer Cache

- 데이터 파일로부터 검색되어진 Data 블록 들이 복사되어 저장된다.

- 데이터를 검색하거나 Update 할 때 좋은 성능을 얻기 위해 존재한다.

- LRU 알고리즘을 통해 관리된다.

- DB_BLOCK_SIZE 가 블록 사이즈를 결정

-> 8i = 2k

-> 9i = 4k

-> 10g,11g = 8k

- 독립된 서브 캐쉬로 구성됨

-> DB_CACHE_SIZE – Database buffer cache 전체 크기

-> DB_KEEP_CACHE_SIZE – 중요한 Data 를 보관 (LRU 적용 안함)

-> DB_RECYCLE_CACHE_SIZE – 잘 사용되자 않는 Data

- 다음 붉은 두가지 설정은 권장 사항이 아님

- 유동적으로 크기 변경이 가능

SQL>alter system set DB_CACHE_SIZE = 96M;

- DB_CACHE_ADVICE=on/off 는 buffer cache 의 크기를 변경했을 때의 성능을 예측하는 Advisory 기능을 다음 파라미터로 제공함

- 다음의 다이나믹 퍼포먼스 뷰를 통해 확인가능

-> V$DB_CACHE_ADVICE

dDB Link 설정

Step 1: listener.ora (Server 측)

- 다음과 같이 여러 개의 리스너를 설정할 수 있다. 다만 운영중인 DB 의 SID_NAME 은 정확해야 하며, Port 번호는 달라야 한다.

Step 2: tnsnames.ora (Client 측)

- 접속을 시도할 서버측의 정보를 입력해 주면 된다.

- 여기서는 IP 주소는 172.16.7.121 이며 Port 는 1522 이고 서버측 Service_name 즉 ORACLE_SID 는 testdb 이다.

Step 3: Link 생성 (Client 측)

SQL> create database link <LINK 명> ß 임의로 정할 수 있음

2 connect to <username> identified by <Password>

3 using '<별칭>' ßtnsnames.ora 에 설정해 둔 접속할 Server Allias

- 위 tnsnames.ora 설정대로 한다면 using 'centos';

Step4:

SQL>select * from scott.test@<link 명>

Ex à SQL>select * from scott.test@clink;

- Link 확인

SQL> select * from user_db_links;

SQL> select * from all_db_links;

실전! 오라클 백업과 복구 국내도서>컴퓨터/인터넷 저자 : 서진수 출판 : 생능출판사 2010.09.06 상세보기

- drop table 된 Data 를 clone DB 와 DB Link 를 이용하여 복구해 보겠습니다.

- 우선 archive mode 를 확인 합니다.

 

- 오픈 백업을 수행 합니다.

- 실습을 위해 다음과 같이 Table 을 생성하고 Data를 삽입 합니다.

SQL> create table scott.ctest01

2 (no number,

3 name varchar2(10),

4 address varchar2(20)) tablespace clone_test;

SQL> begin

2 for i in 1..100000 loop

3 insert into scott.ctest01 values(i,dbms_random.string('A;,10),dbms_random.string('A',20));

4 end loop;

5 commit;

6 end;

7 /

- 로그 마이어로 확인 하거나 다음과 같이 시간을 기록해 둡니다.

- 운영 DB 에서 PFile을 복사하여 CloneDB용 Pfile 을 생성합니다.

- 컨트롤 파일의 경로와 디비 명을 바꿔 주셔야 합니다.

$cp inittestdb.ora initclone1.ora

- 그리고 원본 testdb 즉 운영 DB 에서 컨트롤 파일 생성 스크립트를 만듭니다.

- Redo log file 을 이용하여 복구를 할 수 없고 Archive log 를 이요하여야 하기 때문에 Resetlogs 용으로 스크립트를 수정합니다.

$export ORACLE_SID=clone1

- 그리곤 생성한 스크립트를 이용하여 DB 를 Mount 단계까지 올려 줍니다.

- recover database 쿼리를 이용하여 아까 기록한 시간대로 복구를 해주어야 합니다. 물론 controlfile 의 SCN 이 다르기 때문에 using backup controlfile 구문도 추가해 주어야 합니다.

SQL>alter database open resetlogs;

- resetlogs 옵션으로 DB 를 Open 시킵니다.

- 그리고 Data 가 복구 되었는지 확인 합니다.

- 복구가 된 것을 확인 할 수 있습니다.

- 운영서버(testdb)에서 clone DB로 DB Link 생성하기

- 운영서버의 ORACLE_HOME/network/admin/tnsnames.ora 수정

- 만약 tnsnames.ora 파일이 존재하지 않는다면 netca 를 실행하여 리스너 구성을 해주시기 바랍니다.

- ORACLE_HOME/network/admin/listener.ora

- 운영DB 의 listener.ora 파일에서 Clone DB 의 리스너 정보를 등록 시켜줘야 합니다.

- 이제 운영서버에서 Clone DB 로 연결하는 DB Link 를 생성합니다.

SQL> create database link clink ß DB Link 명(임의로 지정)

2 connect to scott identified by tiger ß clone DB에 연결할 계정

3 using 'clonedb'; ß 이 이름은 tnsnames.ora 파일에 있는 이름을 사용하여야 합니다.

-리스너를 중지 시키고 다시 실행 시키면서 리스너를 테스트 해 봅시다.

- 다음은 리스너를 중지 시키고 연결 되는지 확인해 봅니다.

-다시 리스너를 실행 시킵니다. (listener2)

- listener2 의 상태를 확인 합니다.

- tnsping clonedb 명령어를 통해 리스너가 제대로 동작하고 통신이 되는지 확인 합니다.

- 운영 DB 에서 Clone DB 로 접속할 수 있는지 확인 합니다.

SQL> select count(*) from scott.ctest01@<DBLink명>

- 다음과 같은 방법으로 Clone DB 에서 복구한 Data 를 가져 옵니다.