오라클 데이터 복구
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 오라클 데이터 복구[편집]
2 FLASHBACK (TIMESTAMP 이용)[편집]
3 = == Row Level FlashBack
3.1 개요[편집]
android # 테이블에 전체적인 영향을 주지 않고 특정 row 만 복구가 가능하다
- UNDO 를 이용한다.
- commit 된 데이터만 복구한다.
- 용도별로 Flashback Version Query, Falshback Transaction Query 으로 나눌 수 있다.
- Flashback Version Query
- - 과거의 변경 이력을 조회한다.
- Falshback Transaction Query
- - 변경 작업을 취소하며, 궁극적으로 복구를 목적으로 한다.
3.1.1 주의사항[편집]
- Row Level Flashback 은 Undo Data를 사용하므로 해당 rollback Segment 가 다른 트렌젝션 작업으로 덮어 써지는 경우에는 사용할 수 없다.
- 트렌젝션이 자주 일어나는 시스템이거나, 많은 시간이 흘러버린다면 Row Level Flashback 사용이 불가능하며, 이 때는 clone Database 를 이용하여 시간기반으로 데이터를 복구해야 한다.
3.2 실습 시나리오[편집]
1. 테이블 생성 2. 데이터 입력 3. 데이터 변경 4. 변경 이력 조회 5. 복구
3.2.1 테이블 생성[편집]
create table map (key varchar2(20), name varchar2(20)) tablespace example;- key 와 name 으로 이루어진 간단한 테이블을 생성한다.
3.2.2 데이터 입력[편집]
SQL> insert into map values ('동물', '강아지');
SQL> insert into map values ('바다','배');
SQL> insert into map values ('하늘','비행기');
SQL> commit;3.2.3 데이터 변경[편집]
SQL> update map set name='배' where key='하늘';
SQL> update map set name='비행기' where key='바다';
SQL> commit;3.2.4 변경 이력 조회[편집]
- 이력조회를 위해 Flashback Version Query 수행.
select versions_startscn st_scn
, versions_endscn endscn
, versions_xid txid
, versions_operation opt
, &targetColumn
from &tableName versions between scn minvalue and maxvalue
where &whereColumn = &value- 변수 설명
- targetColumn : 변경이 일어난 컬럼 명 tableName : 변경이 일어난 테이블 명
- whereColumn : 변경이 일어난 컬럼을 찾기위해 조건에 들어갈 컬럼
- value : 조건 컬럼의 값
3.2.5 변경 이력 확인[편집]
Enter value for targetcolumn: name
old 6: , &targetColumn
new 6: , name
Enter value for tablename: map
old 8: &tableName versions between scn minvalue and maxvalue
new 8: map versions between scn minvalue and maxvalue
Enter value for wherecolumn: key
Enter value for value: '하늘'
old 9: where &whereColumn = &value
new 9: where key = '하늘'
ST_SCN ENDSCN TXID O NAME
---------- ---------- ---------------- - --------------------
604368 08002A004A010000 U 배
604359 604368 0700290028010000 I 비행기
--------------- 분석 ---------------
위의 결과값을 보면 처음에 '비행기'라는 값을 Insert 했고 '배'로 Update 한 것을 알 수 있다.
발생 시점을 시간으로 보고 싶다면 아래와 같이 scn_to_timestamp(scn) 함수를 사용하면 된다.SQL>select to_char(scn_to_timestamp(604368), 'YYYY-MM-DD:HH24:MI:SS') -- 위 결과에서 나온 SCN 으로 시간 조회
from dual;
TO_CHAR(SCN_TO_TIME
-------------------
2012-04-03:23:16:103.2.6 복구[편집]
select undo_sql
from flashback_transaction_query
where table_name='&tableName'
AND commit_scn between &st_scn and &end_scn
order by start_timestamp desc
--
Enter value for tablename: MAP
old 6: table_name='&tableName'
new 6: table_name='MAP'
Enter value for st_scn: 604359
Enter value for end_scn: 604368
old 7: AND commit_scn between &st_scn and &end_scn
new 7: AND commit_scn between 604359 and 604368
flashback_transaction_query
*
ERROR at line 4:
ORA-01031: insufficient privileges3.2.6.1 권한 오류 발생시 권한부여[편집]
- 현재 유저에게 flashback_transction_query 에 대해 권한이 없다.
- sys 사용자로 접속하여 " select any transaction " 권한을 부여 한다.
grant select any transaction to scott;3.2.7 재시도[편집]
select undo_sql
from flashback_transaction_query
where table_name='&tableName'
AND commit_scn between &st_scn and &end_scn
order by start_timestamp desc
-------------------------------------------------------
update "SCOTT"."MAP" set "NAME" = '배' where ROWID = 'AAAM40AAFAAAAQkAAB';
update "SCOTT"."MAP" set "NAME" = '비행기' where ROWID = 'AAAM40AAFAAAAQkAAC';
delete from "SCOTT"."MAP" where ROWID = 'AAAM40AAFAAAAQkAAB';
delete from "SCOTT"."MAP" where ROWID = 'AAAM40AAFAAAAQkAAC';
delete from "SCOTT"."MAP" where ROWID = 'AAAM40AAFAAAAQkAAA';===
4 = == Table Level Flashback
- 다른 테이블에 영향을 주지 않고 대상이 되는 테이블만 복구하는 기술이다.
- Table Level Flashback 은 두가지 방법으로 나눌 수 있다.
- - SCN 기반 Flashback
- - 시간 기반 Flashback
4.2 SCN 기반 Flashback[편집]
- 이벤트가 발생하기 전의 SCN 을 이용한 Flashback 기능을 사용하는 방법.
- 이 방법은 이벤트를 발생시키기 전에 미리 SCN을 기록해 두거나 , 변경 이력 조회 방법등으로 알아낸SCN 을 이용하여 Table Level 의 Flashback 방법
- 전제 조건
- Flashback을 사용하는 테이블은 row movement 가 활성화되어 있어야 한다.
4.2.1 복구실습[편집]
-- map 은 복구 대상 테이블 명
SQL> flashback table map to scn '604359' -- 테이블 레벨에서 SCN 604359 으로 Flashback 을 시도.
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
SQL> ALTER table map enable row movement;
Table altered.
SQL> flashback table map to scn '604359';
Flashback complete.- 설명
- 테이블 레벨에서 SCN 604359 으로 Flashback 을 시도한다.
- - 이 값은 앞선 포스팅에서( 4. 변경 이력 조회 ) 조회된 값이다.
- - 삭제전에 현재 SCN 조회를 하여 기록해 놨던 SCN 으로 사용해도 된다.
- 테이블에 row movement 가 설정되어 있지 않아서 메시지가 표시된다.
- 테이블에 row movement 를 활성화한다.
- flashback 을 재시도 한다.
4.3 시간 기반 Flashback[편집]
- SCN 과 마찬가지로 undo 를 사용하며 시간을 기반으로 Flashback 기능 사용.
4.3.1 복구실습[편집]
-- 1.대상 테이블 조회
SQL> select * from map;
KEY NAME
-------------------- --------------------
동물 강아지
바다 배
하늘 비행기
-- 2. 테이블 삭제
SQL> delete from map;
3 rows deleted.
-- 3. 복구
SQL> flashback table map to timestamp(systimestamp - interval '2' minute);
Flashback complete.
4.4 관련 에러[편집]
4.4.1 ORA-01466: unable to read data - table definition has changed[편집]
시간을 너무 뒤로 돌려서 해당 테이블이 존재하지 않은 시간대를 선택했거나, alter 명령어로 테이블을 수정시 수정 전 시간으로 Flashback 을 시도하면 위와같은 에러 발생
4.4.2 UNDO Data가 없을때[편집]
ORA-00604 : error occurred at recusive SQL... ORA-12801 error signaled in parallel... ORA-01555 : snapshot too old : rollback segment number ...
4.5 Recycle Bin[편집]
- 윈도우의 휴지통같은 기능
- 테이블을 drop 할때 purge 옵션을 주지 않으면 완전히 삭제되지 않고 복구가 가능한 상태로 바뀌게 되는데. 삭제되어 복구가 가능한 테이블들은 조회 할 수 있으며 복구 할 수 있다.
- 주의사항
- - 테이블이 삭제되면 다른영역으로 이동하는것이 아니라 상태만 변경.
- 그래서 테이블스페이스에 공간이 부족하게되면 우선적으로 recycle bin 이 차지하고 있는 영역을 사용하게 되는데 그러면 recycle bin 을 이용하여 복구가 불가능하다.
- - 인덱스가 존재하는 테이블을 삭제하고 flashback 을 사용하여 복구하면 인덱스도 같이 복구 된다.
- 하지만 인덱스 명이 BIN$. 으로 시작하므로 이름을 변경시켜 주던지 아니면 삭제하고 다시 생성한다.
4.5.1 인덱스 조회[편집]
select table_name, column_name,index_name
from user_ind_columns
where table_name='&tableName' -- map 테이블 인덱스 조회
-- map 테이블 삭제
SQL> drop table map;
Table dropped.
-- 테이블 존재하는지 확인
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
BIN$vL0lpMaYHDDgQAB/AQA0Tw==$0 TABLE
SYS_TEMP_FBT TABLE
-- 휴지통 확인
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
MAP BIN$vM4c+mQ5ZZ/gQAB/AQBU0Q==$0 TABLE 2012-04-04:06:37:26- select * from tab; 명령을 통한 조회에서는 이름만 표시되고 있으며 show recyclebin 명령은 원본 명과 recycle 명 타입 삭제된 시간 등을 알 수 있다.
4.6 테이블 복구[편집]
4.6.1 drop 한 테이블 명으로 복구[편집]
flashback table map to before drop;4.6.2 recycle bin name 으로 복구[편집]
flashback table "BIN$vM4c+mQ5ZZ/gQAB/AQBU0Q==$0" to before drop;
-- ORA-38312: original name is used by an existing object 이미 존재하는 경우 새이름으로 복구
flashback table "BIN$vM4c+mQ5ZZ/gQAB/AQBU0Q==$0" to before drop rename to map2;===
5 = == FLASHBACK DATABASE LEVEL
- 전통적인 백업 방법은 파일을 restore 하고 redo와 Archive File을 이용하여 복구
- Database Level Flashback 은 restore 과정을 생략하고 Flashback log 를 이용하여 복구 하는 것이 차이점
5.1 전통적인 복구 VS DATABASE FLASHBACK[편집]
| REDO,ARCHIVE파일 복구 | DATABASE FLASHBACK 복구 |
|---|---|
| Redo, archive만 사용하는 불완전 복구 | Database flashback(어느정도 redo도 사용) |
| 복구 시 백업파일을 복원해야한다. | 복원 불필요 |
| 복구 후 재차 복구 불가능(resetlog때문에) | 복구 후 재차 복구 가능 |
| Redo, archive파일사용 | Flashback log파일사용 |
| Database archivelog 모드만 가능 | Archivelog mode 이고 flashback database mode |
5.1.1 주의사항[편집]
- flashback log 는 Database snapshot 수준으로 남기므로 많은 용량을 필요로 한다.
- system Tablespace 내의 Table은 Flashback 으로 복구 불가능
- Control file 을 재생성시 Flashback Database 설정 초기화
5.2 DATABASE FLASHBACK 전제조건[편집]
- flashback log 설정 활성화
- archive log mode
- Flashback Database Mode 설정 활성화
5.3 DB 환경 설정[편집]
- Database Level 의 Flashback 을 사용하려면 아래와 같은 설정을 선행 해야 한다.
-- db_flashback_retention_target 분 설정
SQL> alter system set db_flashback_retention_target=30 scope=spfile;
-- 종료 (pfile 을 사용한다면 종료후 pfile 에 db_flashback_retention_target 을 설정한다.)
SQL>SHUTDOWN IMMEDIATE;
-- MOUNT 상태로 시작
SQL> startup mount;
-- archive log 시작
SQL> alter database archivelog;
-- flashback on
SQL> alter database flashback on;
-- database open
SQL> alter database open;
-- flashback 상태 조회
select flashback_on from v$database;
FLASHBACK_ON
------------------
YES5.4 시나리오[편집]
-- 1.테이블 생성
SQL> create table t ( no number,name varchar2(10)) tablespace example;
Table created.
-- 2. 데이터 입력
SQL> begin
for i in 1..1000 loop
insert into t values ( i , dbms_random.string('A', 9));
end loop;
commit;
end;
/
-- 3. Table Truncate
SQL> truncate table t;
Table truncated.
-- 4. 복구
-- sys 계정 로그인 (database shutdown 하기 위해)
SQL> conn / as sysdba
-- database shutdown
SQL> shutdown immediate;
-- 마운트 상태에서 복구
SQL> startup mount;
-- 시간은 알아서..
SQL>flashback database to timestamp (systimestamp - interval '10' minute);
Flashback complete.
-- database 를 open 한다.
SQL> alter database open resetlogs;===
데이터를 실수로 삭제(truncate는 복구 불가) 할 경우 특정시간 또는 시점으로 되돌릴 수 있는 기능. 플래시백 기능을 통해 as of timestamp 구문을 사용하여 삭제 전의 데이터를 조회하여 복구 가능.
5.5 FLASHBACK 설정 조건[편집]
1. 자동 언두 관리 시스템을 사용(UNDO_MANAGEMENT 파라미터를 AUTO로 설정)
UNDO_MANAGEMENT = AUTO2. 이전의 어느 시점까지의 언두(UNDO)정보를 보유하여 Flashback Query를 수행할것인지 UNDO_RETENTION 파라미터를 설정.
ALTER SYSTEM SET UNDO_RETENTION=12003.설정 확인 SQL
show paramter undo;
NAME TYPE
-----------------------------------------------
VALUE
------------------------
undo_management string
auto
auto_retention integer
1200
UNDOTBS1
SQL※ 사용자 권한 Flashback 기능을 이용하기 위해서 DBMS_FLASHBACK 패키지에 대한 EXECUTE권한 필요.
▶ Flashback을 사용할 수 있는 조건 모두 복구 할 수 있는 것은 아님.
시스템의 설정, 버퍼의 크기에 따라 복구 할 수 있는 시간은 제한적 이다. (오라클 서버의 메모리 세팅.)
제약조건이 추가되거나 컬림이 추가 된 경우, DROP한 후 PURGE한 경우 복원할 수 없다.
▶ 테이블 scn 번호로 복구
flashback table emp_test to scn 1406603;
-- scn번호확인 : select current_scn from v$database;- 기준시간으로 조회
SELECT * FROM [TABLE 명] AS OF TIMESTAMP TO_TIMESTAMP ( '20210412 190000', 'YYYYMMDD HH24MISS' )
WHERE [조회조건]- 10 분전 데이터 조회
SELECT *
FROM [TABLE 명] AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' MINUTE) -- 단위는 SECOND, MINUTE, HOUR, DAY
WHERE [조회조건]또는
SELECT *
FROM [TABLE 명] AS OF TIMESTAMP sysdate - 1/24/60*10
WHERE [조회조건]- 10 분전 데이터 조회 후 입력
INSERT INTO TB_XXX
SELECT *
FROM [TABLE 명] AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' MINUTE) -- 단위는 SECOND, MINUTE, HOUR, DAY
WHERE [조회조건]또는
INSERT INTO TB_XXX
SELECT *
FROM [TABLE 명]
AS OF TIMESTAMP TO_TIMESTAMP('20200101 102030','YYYYMMDD HH24MISS')
WHERE [조회조건]- 다른 테이블명으로 복구
FLASHBACK TABLE EMP.XTB_EVN
TO BEFORE DROP
RENAME TO EMP.XTB_EVN_COPY;- 이전테이블로 복구
FLASHBACK TABLE RTIS_DEV.XTB
TO BEFORE DROP;
FLASHBACK TABLE "BIN$hAmsdSFUTtrgUwEAAH8yIQ==$0"
TO BEFORE DROP;
▶ 인덱스
ALTER INDEX "BIN$UofUBMjYnkDgUOABLqNFXg==$0"
RENAME TO PK_TB_USER_INFO;▶ 프로시저
SELECT *
FROM DBA_SOURCE AS OF TIMESTAMP (SYSTIMESTAMP-INTERVAL '수치' 단위) -- 단위는 SECOND, MINUTE, HOUR, DAY
WHERE NAME = '프로시저이름'▶ EX)
SELECT *
FROM TEST_TABLE AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '20' MINUTE) -- 단위는 SECOND, MINUTE, HOUR, DAY
WHERE COMPANY = 'SK'테이블명과 조건을 입력하고 조회화면 20분 이전의 데이터가 조회된다.
5.7 데이터파일 복구[편집]
Complete Recovery – 백업 받지 않은 데이터파일 복구(1)
마지막 cold backup이나 hot backup 이후에 만들어진 테이블스페이스에 장애가 발생하는 경우인데 손상된 파일이 system 테이블스페이스에 속하지 않을 때의 복구 방법
TEST라는 테이블스페이스를 만들었는데 이 테이블스페이스에 포함된 데이터파일에 장애가 발생 했다고 가정(물론 이 파일은 백업이 되어 있지 않습니다.)
1.DB가 종료되어 있다면 MOUNT 단계까지 실행하여 백업 전에 손상된 데이터 파일이나 데이터파일이 포함된 테이블스페이스를 offline으로 변경. 그러나 만약 데이터베이스가 오픈 되어 있는 상태라면 데이터파일 이나 테이블스페이스만 offline으로 변경
SQL>alter database datafile ‘c:\oracle\oradata\wink\test01.dbf’ offlineOr
SQL>alter tablespace TEST offline;2. v$recover_file을 조회해서 복구 상태 확인
SQL>select * from v$recover_file;3.장애 파일을 다시 생성
SQL>alter database create datafile ‘c:\oracle\oradata\wink\test01.dbf’
as ‘c:\oracle\oradata\wink\test01.dbf’4.recover 명령으로 파일을 복구
SQL>recover tablespace TEST;5.테이블스페이스를 온라인으로…
alter tablespace test online;6 데이터파일 위치 변경(디스크 full 장애시 신규 디스크로 이동..)[편집]
1.신규 디스크로 데이터파일 이동(현재 DB가 죽어있는 상태..)
mv /data1/TS_XXX_D01.dbf /data2/TS_XXX_D01.dbf2.오라클 접속 후 데이터파일 변경
-- 1.접속
sqlplus / as sysdba;
-- 2.mount 상태로 시작
startup mount;
-- 3. datafile 변경
alter database rename file '/data1/TS_MIG_D01.dbf' to '/data2/TS_XXX_D01.dbf';
-- 4.DB open
alter database open;- 현재 데이터파일 상태 확인
SELECT STATUS,NAME,BYTES/1024/1024 MB
FROM V$DATAFILE;7 데이터파일 위치 변경(온라인 상태에서 변경)[편집]
1.온라인 상 변경 하는 방법
-- 1.offline 변경
alter database datafile ‘/data1/ACCMIG/ACCMIG/TS_MIG_I01.dbf’ offline;
-- 2.rename
alter database rename file '/data1/ACCMIG/ACCMIG/TS_MIG_I01.dbf' to '/data2/ACCMIG/ACCMIG/TS_MIG_I01.dbf';
-- 3.online 변경
alter database datafile ‘/data1/ACCMIG/ACCMIG/TS_MIG_I01.dbf’ online;8 리두(REDO) 로그 파일 이동[편집]
redo log file은 offline이 안되서 반드시 사용 안하게 만드려면 DB를 mount상태로 두고 작업해야 함
1.서버 종료후 mount로 시작
SQL> select status from v$instance;
SQL> shutdown immediate;
SQL> startup mount;2.이동할 디렉토리 생성
SQL> !mkdir /app/oracle/disk4
SQL> !mkdir /app/oracle/disk53.리두로그 파일 복사(이동)
-- 로그파일 정보
SQL> select member from v$logfile;
-- 신규디스큿에 복사
SQL> !cp /app/oracle/oradata/orcl/redo01.log /app/oracle/disk4/redo01_a.log
SQL> !cp /app/oracle/oradata/orcl/redo02.log /app/oracle/disk4/redo02_a.log
SQL> !cp /app/oracle/oradata/orcl/redo03.log /app/oracle/disk4/redo03_a.log
-- 리두로그파일 rename
SQL> alter database rename
file '/app/oracle/oradata/orcl/redo01.log'
to '/app/oracle/disk4/redo01_a.log';
SQL> alter database rename
file '/app/oracle/oradata/orcl/redo02.log'
to '/app/oracle/disk4/redo02_a.log'
SQL> alter database rename
file '/app/oracle/oradata/orcl/redo03.log'
to '/app/oracle/disk4/redo03_a.log';
-- 필요시 그룹(b) 추가
SQL> alter database add logfile member
'/app/oracle/disk5/redo01_b.log' to group 1,
'/app/oracle/disk5/redo02_b.log' to group 2,
'/app/oracle/disk5/redo03_b.log' to group 3;
-- 확인
SQL> select member from v$logfile;