Oracle 46일차

[noarchive log mode에서의 복구]

redo log에 저장된 변경 내용의 유실 발생 -> 데이터 복구 제한적

1) 장애 복구까지의 필요 정보가 현 redo log에 존재하는 경우
-> 아카이브를 필요로 하지 않기 때문에 복구 가능

2) 장애 복구까지의 필요 정보가 현 redo log에 존재하지 않는 경우
-> 아카이브를 필요로 하는데 아카이브가 없어서 복구 불가능

[noarchive 복구 실습을 위한 세팅]

1. 현 상태 확인

select log_mode from v$database;
또는
> archive log list

2. noarchive log mode 전환

> shutdown immediate
> startup mount
> alter database noarchivelog;
> archive log list
> alter database open;

3. tablespace 생성

create tablespace test02 datafile '/home/oracle/oradata/db1/test02.dbf' size 100m autoextend on;

4. offline full backup (cold backup)

[case1 : 운영 중인 tablespace datafile 손상 – 모든 정보가 현재 redo에 존재]

마지막 백업 시점부터의 모든 정보가 현재 redo에 존재하는 경우다.

1. 테이블 생성 및 데이터 입력

create table scott.recover_test11(no number) tablespace test02;
insert into scott.recover_test11 values(1);
insert into scott.recover_test11 values(2);
insert into scott.recover_test11 values(3);
commit;

2. test02.dbf 파일 삭제

$ rm /home/oracle/oradata/db1/test02.dbf

3. log switch

alter system switch logfile;
-- 3번째 수행 시 DB 비정상 종료
-- ORA-01565: error in identifying file '/home/oracle/oradata/db1/test02.dbf'

4. test02.dbf datafile restore

$ cp /oracle12/backup/cold_backup/test02.dbf /home/oracle/oradata/db1

5. recover

> startup mount
> recover database;   -- 원래는 이거 쓰면 그냥 됨. 근데 redo log 적용되는 과정 보려고 아래로 진행함.

> select group#, sequence# from v$log;
또는
> @/home/oracle/log.sql
-- 이거 써서 어떤 redo group이 몇번 seq#인지 확인 후
> recover database until cancel;
-- 여기서 redo log sequence 순서대로 경로 입력해주기
---------------------------------------------------------------------
SQL> recover database until cancel;
ORA-00279: change 3332887 generated at 12/16/2024 10:12:59 needed for thread 1
ORA-00289: suggestion : /arch/1_40_1187453268.dbf
ORA-00280: change 3332887 for thread 1 is in sequence #40


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/oradata/db1/redo01.log
ORA-00279: change 3335329 generated at 12/16/2024 10:41:54 needed for thread 1
ORA-00289: suggestion : /arch/1_41_1187453268.dbf
ORA-00280: change 3335329 for thread 1 is in sequence #41
ORA-00278: log file '/home/oracle/oradata/db1/redo01.log' no longer needed for
this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/oradata/db1/redo02.log
ORA-00279: change 3335332 generated at 12/16/2024 10:41:55 needed for thread 1
ORA-00289: suggestion : /arch/1_42_1187453268.dbf
ORA-00280: change 3335332 for thread 1 is in sequence #42
ORA-00278: log file '/home/oracle/oradata/db1/redo02.log' no longer needed for
this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/oradata/db1/redo03.log
Log applied.
Media recovery complete.
---------------------------------------------------------------------

> alter database open noresetlogs;
-- 완전 복구니 noresetlogs.

select * from scott.recover_test11;
-- 전부 다 잘 나옴.

[case2 : 운영 중인 tablespace datafile 손상 – 모든 정보가 현재 redo에 존재X]

0. 먼저 cold backup 받아 놓기

1. 테이블 생성 및 데이터 입력

create table scott.recover_test100(no number) tablespace test02;
insert into scott.recover_test100 values(1);
insert into scott.recover_test100 values(2);
insert into scott.recover_test100 values(3);
commit;

2. log switch 3번 이상

alter system switch logfile;

3. test02.dbf 삭제

$ rm /home/oracle/oradata/db1/test02.dbf

4. DB 종료

> shutdown abort

5. test02.dbf restore

$ cp /oracle12/backup/cold_backup/test02.dbf /home/oracle/oradata/db1

6. recover (실패)

> startup mount

> recover database until cancel
-- 43번부터 필요한데 archive file은 없고 redo log는 46, 47, 48번이 남아있는 상태라서 복구 불가능

7. 장애난 datafile offline

지금처럼 더 이상의 복구가 불가능한 상황이라면 일단 DB open이 필수이므로 해당 datafile을 offline 시킨 후 open 시키거나,
불가할 경우 cold_backup을 full restore 해서 DB open.

> alter database datafile '/home/oracle/oradata/db1/test02.dbf' offline drop;

8. open 시도

-- 0) open
-- 완전 복구 시. 밑에는 불완전 복구 시도 시 쓰는 명령어.

-- 1) noresetlogs로
-- 기본적으로는 이게 맞음. 나머지 dbf는 문제가 없어서 그것들을 시점 일치하는 작업을 DB가 할 거임. datafile header에 recovery 상황을 기록, 현재 redo 내용을 유지하면서 DB open
> alter database open noresetlogs;

-- 2) resetlogs로
-- 굳이 redo reset할 필요 없긴 함. redo clear 때문에 정상 open 안될 수도 있음 -> hidden parameter 적용을 통해 강제 open 가능.
: _allow_resetlogs_corruption=true
-- init 파라미터에 위 파라미터 기술 후 init 파라미터를 사용한 DB open이후 다시 init 파라미터에서 위 파라미터 제거 후 다시 기동.
> alter database open resetlogs;

[case3 : 운영 중인 필수 tablespace datafile 손상 – 모든 정보가 현재 redo에 존재X]

1. 테이블 생성 및 데이터 입력

create table scott.recover_test200(no number) tablespace system;
insert into scott.recover_test200 values(1);
insert into scott.recover_test200 values(2);
insert into scott.recover_test200 values(3);
commit;

2. log switch 3번 이상

3. system01.dbf 삭제

$ rm /home/oracle/oradata/db1/system01.dbf

4. DB shutdown abort

5. system01.dbf restore

$ cp /oracle12/backup/cold_backup/system01.dbf /home/oracle/oradata/db1

6. recover (불가)

가장 먼저 recover가 안된다. 왜냐면 archive file도 없고, redo log file이 가진 seq#도 뒤의 것이라서 recover할 파일이 없다.

그 다음으로 case2번처럼 tablespace offline을 시도할 차례. case2번에서는 tbs offline으로 해결했지만 이번에는 필수 tbs라서 offline한 채로 DB open이 안된다. 따라서 full restore밖에 답이 없음.

7. full restore

데이터는 다 날아감.

[migration]

DB의 데이터를 다른 DB로 이관하는 것.
oracle server에서 제공하는 sqlldr, exp, expdp 등을 사용.
또는 유료화 tool(CDC tool)을 사용.

1. sqlldr :
– 외부 파일을 DB 적재를 도와주는 tool.
– orange와 같은 대부분의 접속tool들이 sqlldr 기능 제공

2. exp(export), expdp(export dump) / imp(import), impdp(import dump)
– DB 내의 데이터를 바이너리 파일 형태로 생성 -> 다른 DB에 이관

[sqlldr]

외부 파일 데이터를 DB로 import하는 방식.
테이블을 자동으로 생성해주지는 않는다.

** 사용법
$ sqlldr 유저명/비밀번호 control=컨트롤파일 log=로그파일 bad=패드파일 [기타옵션]

— userid : DB 접속 계명과 패스워드
— control : 적재 규칙을 가지고 있는 파일명
— log : 실행 결과를 저장할 로그 파일명
— bad : 적재 실패한 명령어를 저장하는 파일명

[case1 : csv파일 적재]

1. vi로 아래와 같이 파일 생성(test1.csv)

$ mkdir ~/sqlldr
$ cd ~/sqlldr
$ vi test1.csv
1,a
2,b
3,c
4,d

2. csv가 들어갈 테이블 생성

create table scott.sqlldr_test1(no number, name varchar2(10));

3. 적재

-- step1) 컨트롤 파일 생성
$ vi test1.ctl
load data
infile '/home/oracle/sqlldr/test1.csv'
into table sqlldr_test1
fields terminated by ','
(no, name)

-- step2) sqlldr 실행
$ sqlldr scott/oracle control=test1.ctl

select * from scott.sqlldr_test1;

[case2 : csv파일 적재 – header있는 경우]

1. vi로 아래와 같이 파일 생성(test2.csv)

$ cd ~/sqlldr
$ vi test2.csv
no,name
1,a
2,b
3,c
4,d

2. csv가 들어갈 테이블 생성

create table scott.sqlldr_test2(no number, name varchar2(10));

3. 적재

-- step1) 컨트롤 파일 생성
$ vi test2.ctl
options (skip=1)
load data
infile '/home/oracle/sqlldr/test2.csv'
into table sqlldr_test2
fields terminated by ','
(no, name)

-- step2) sqlldr 실행
$ sqlldr scott/oracle control=test2.ctl

select * from scott.sqlldr_test2;

[case3 : 컨트롤 파일에 적재할 데이터 직접 입력]

기존 테이블에 데이터 존재할 경우 옵션 선택

-- step1) 컨트롤 파일 생성
$ cd ~/sqlldr
$ vi test3.ctl
load data
infile *
into table sqlldr_test1
fields terminated by ','
(no, name)
begindata
1,"aaa"
2,"bbb"
3,"ccc"
4,"ddd"

-- step2) sqlldr 실행 (실패)
$ sqlldr scott/oracle control=test3.ctl
-- error남. SQL*Loader-601: For INSERT option, table must be empty.

-- step3) 컨트롤 파일 수정
$ vi test3.ctl
load data
infile *
replace
into table sqlldr_test1
fields terminated by ','
(no, name)
begindata
1,"aaa"
2,"bbb"
3,"ccc"
4,"ddd"

-- step4) sqlldr 실행 (성공)
$ sqlldr scott/oracle control=test3.ctl

select * from scott.sqlldr_test1;
        NO NAME
---------- ----------
         1 "aaa"
         2 "bbb"
         3 "ccc"
         4 "ddd"
-- 쌍따옴표가 그대로 출력되는 문제

-- step5) 쌍따옴표 제외 입력
$ vi test3.ctl
load data
infile *
append
into table sqlldr_test1
fields terminated by ','
optionally enclosed by '"'
(no,name)
begindata
1,"aaa"
2,"bbb"
3,"ccc"
4,"ddd"

select * from scott.sqlldr_test1;
        NO NAME
---------- ----------
         1 aaa
         2 bbb
         3 ccc
         4 ddd
         1 "aaa"
         2 "bbb"
         3 "ccc"
         4 "ddd"

[case4 : 필드 구분자가 명확하지 않거나 공백으로 분리 구분 시 공백의 수가 일정하지 않은 경우]

ex) 컴마로 분리된 파일인데 데이터가 컴마를 포함하고 있는 경우
800,smith,10
900,allen,30
1,200,king ,20

ex) 공백으로 각 필드를 구분, 공백의 수가 일정하지 않은 경우
1000 smith 800 0
1001 allen 1200 10
1002 ford 500 500

1. vi로 아래와 같은 파일 생성(test4.csv)

$ vi test4.csv
1000 smith  800   0
1001 allen 1200  10
1002 ford   500 500

2. table 생성

create table scott.sqlldr_test4
(no number,
name varchar2(10),
sal number,
comm number);

3. 적재 (잘 안 됨)

-- 컨트롤 파일 생성
$ vi test4.ctl
load data
infile '/home/oracle/sqlldr/test4.csv'
into table sqlldr_test4
fields terminated by ' '
(no,name,sal,comm)

-- sqlldr 실행
$ sqlldr scott/oracle control=test4.ctl

-- 조회 (잘못된 데이터 입력)
select * from scott.sqlldr_test4;
        NO NAME           SAL   COMM
---------- ---------- ---------- ----------
      1000 smith                        800
      1001 allen           1200
      1002 ford

4. 적재 (수정)

-- step1) 컨트롤 파일 수정
$ vi test4.ctl
load data
infile '/home/oracle/sqlldr/test4.csv'
replace
into table sqlldr_test4
trailing nullcols
(no position(1:4) integer external,
name position(6:10) char,
sal position(12:15) integer external,
comm position(17:19) integer external)

** external : os가 기본적으로 데이터를 char로 처리함. 숫자를 int로 인식시키기 위해 필요한 옵션

-- step2) sqlldr 실행
$ sqlldr scott/oracle control=test4.ctl

-- step3) 조회 (잘 입력됨)
select * from scott.sqlldr_test4;
        NO NAME           SAL   COMM
---------- ---------- ---------- ----------
      1000 smith             800          0
      1001 allen            1200         10
      1002 ford              500        500

[실습]

apply.csv 파일을 oel7 서버로 업로드 후(winscp – text) 해당 데이터를 새로운 테이블(sqlldr_apply)에 적재

1. 테이블 생성

create table scott.sqlldr_apply
(year number,
month number,
day number,
name varchar2(20),
id_passwd varchar2(40));

2. 적재

-- 컨트롤 파일 생성
$ vi apply.ctl
options (skip=1)
load data
infile '/home/oracle/sqlldr/apply.csv'
replace
into table sqlldr_apply
fields terminated by ','
(year,month,day,name,id_passwd)

-- sqlldr 실행
$ sqlldr scott/oracle control=applt.ctl