연돌군's Blog

Recovery Catalog 구성

- Recovery catalog 란 RMAN 사용시에 RMAN 으로 백업 복구 작업을 하고 관련 정보를 저장해 두는 장소

- Recovery catalog Server 가 있을 경우 Recovery catalog 에 정보를 저장

1. Data file 및 Archive redo log file 의 백업 셋과 copy 된 이미지에 대한 정보

2. 백업 대상 서버의 물리적인 구조

3. 자주 사용하는 백업 스크립트(Recovery catalog Server 를 사용할 경우만 해당)

- Catalog Server 사용시 접속 방법 : rman target / catalog rcuser/rcuser@rcserver

- Catalog Server 미 사용시 접속 : rman target /

- rman 실행시 오류 발생

- 확인해보면 rman 의 경로가 다른 것으로 되어 있기에 변경 해 주면 됩니다.

1. Recovery Catalog DB 생성

- 운영 DB 와 Catalog DB 가 필요하므로 Clone DB를 같은 서버에 생성해서 만들도록 하겠습니다.

- Clone DB 를 만드는 것은 저의 다른 글을 참고 하시길

-ORACLE_SID=rcserver 로 하고 생성

- Clone DB 에서 작업

- 복구 카탈로그를 저장할 테이블 스페이스(rc_tbs01)을 생성

- 복구 카탈로그를 관리할 사용자 계정(rcuser)를 생성하고 권한을 설정

- tnsnames.ora 파일을 수정해 줍니다.

- testdb 에서 rcserver 로 접속이 되는지 테스트 합니다.

- 이 테스트를 위해서는 당연히 리스너가 실행되고 있어야 합니다.

$lsnrctl

LSNRCTL>start listener3

- testdb à rcserver 로의 접속 테스트

- testdb 서버에 rman 으로 접속하되 recovery catalog 서버에 접속합니다.

- 복구 카탈로그를 생성 합니다.

-이제 복구 서버에 testdb 서버가 등록되어 있는지 확인 합니다.

- rcserver 서버에서 확인 합니다.

- 아직 testdb 서버의 SID 가 등록되지 않았습니다.

- 위 작업은 testdb 서버에서 하는 작업 입니다.

- 다시 rcserver 에서 testdb 가 등록되었는지 확인 합니다.

- rcserver 에서의 테이블 스페이스와 testdb 의 테이블 스페이스를 각각 비교

- rcserver 에서의 확인은 당연히 rcuser 로 접속하거나 rcuser.rc_datafile 로 테이블을 select 해야 하는 것은 당연지사.

- testdb 에서의 테이블 스페이스 구조와 rcserver 의 rc_datafile 의 카탈로그 테이블의 구조가 같은 것을 확인할 수 있습니다.

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 파일을 수정하여 다시 입력

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 를 가져 옵니다.

Clone DB (drop table 복구)

- 기존 testdb 에서 테이블을 생성하고 실습을 위해 drop 시킵니다.

- 또한 운영중인 DB 의 Redo log file 을 복사할 수는 없기에 Redo log group 의 두배 정도의 횟수만큼 switch 를 일으켜 줍니다. 그래서 아카이브 파일에 정보가 기록되게 하는 것 입니다.

SQL>alter system switch logfile;

- Clone DB 용 파라미터 파일을 testdb 파라미터 파일에서 복사한 후 아래 내용을 수정해 줍니다.

Control_files= '/data/temp2/control01.ctl'

db_name='testdb2'

- 예전에 백업해 두었던 데이터 파일을 temp2 로 복사 합니다.

- testdb2 를 위한 컨트롤 파일을 생성하기 위해 testdb DB 에서 컨트롤 파일 생성을 위한 Trace 를 생성 합니다.

- Redo log file 이 존재 하지 않기에 resetlogs 모드로 편지을 해줍니다.

(resetctl.sql)

$export ORACLE_SID=testdb2

- SID 값을 변경하여 testdb2 DB로 접속할 수 있게 파라미터를 변경해 줍니다.

- TESTDB2 로 접속한 것을 볼 수 있습니다.

- 그리고 트레이스를 실행시켜 mount 단계까지 올려줍니다.

- recover 명령어를 통해 불완전 복구를 수행 시키고 resetlogs 옵션으로 DB 를 open 시킵니다.

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

- 이제 운영 DB에 Data를 다시 넣기 위하여 Clone DB 에서 export 를 수행 합니다.

- 다음은 testdb 즉 운영DB에 다시 import 시키는 화면 입니다. 이 작업 전에

$export ORACLE_SID=testdb 명령어를 주어 운영DB 로 접속할 수 있게 환경을 맞춰줘야 합니다.

$imp scott/tiger file=tt100.dmp ignore=y

명령어만 주게되면 다음과 같은 오류를 볼 수가 있는데 이는 서로 다른 계정으로 imp 를 시킬 때 발생하는 오류입니다.

- 다음과 같이 fromuser=scott touser=scott 옵션을 주어 scott 계정으로 table을 import 시키게 만들어 주어야 합니다.

- 만약 system 계정으로 import 시키면 scott 계정의 table이 아닌 system.tt100 으로 입력되기 때문에 주의 하셔야 합니다.

- 다음과 같이 제대로 데이터가 입력된 것을 확인 할 수 있습니다.