불완전복구(Incomplete Recovery) - update, delete, drop
drop table 명령으로 삭제된 테이블 복구하기
장애 상황
고객사이트에서 실수로 중요한 테이블 하나가 삭제되었는데 반드시 복구해야 한다는 요청이 들어왔습니다. 지워진 시간을 물어보니 대략 시간은 알고 있다고 합니다. 삭제된 시간을 알고 있기에 시간 기반 불완전 복구 방법으로 복구하기로 결정했습니다.
Step 0) 장애 상황 세팅
SQL> create tablespace test
2 datafile '/ORA19/app/oracle/oradata/ORACLE19/test01.dbf' size 10M;
SQL> !vi df.sql
set line 200
col ts_name for a10
col file_name for a50
select a.name "TS_NAME", b.name "FILE_NAME", b.bytes/1024/1024 MB, b.status
from v$tablespace a, v$datafile b
where a.ts#=b.ts#
/
SQL> @df
-- cold backup
SQL> shutdown immediate;
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/*.dbf /data/backup/close/
SQL> startup;
SQL> create table khyup.test01
2 (no number,
3 hdate date)
4 tablespace test;
SQL> insert into khyup.test01 values (1,sysdate);
SQL> insert into khyup.test01 values (2,sysdate);
SQL> insert into khyup.test01 values (3,sysdate);
SQL> commit;
SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;
SQL> save tt.sql
SQL> insert into khyup.test01 values (4,sysdate);
SQL> commit;
SQL> @tt
SQL> insert into khyup.test01 values (5,sysdate);
SQL> commit;
SQL> @tt
-- khyup.test01 테이블이 지워지는 장애가 발생
SQL> drop table khyup.test01 purge;
SQL> @tt
-- 테이블이 삭제되어 조회되지 않습니다. 복구해야 합니다.
SQL> select * from khyup.test01;
복구 시나리오 1 - 모든 데이터 파일 백업 복원 후 복구
실무에서는 테이블이 삭제된 시점을 대략적으로만 아는 경우가 많거나 이마저도 모르는 경우가 많습니다. 실패할 확률도 많고 복구 작업을 몇 번을 반복해야 합니다. 그래서 원본 파일은 그대로 두고 임시 경로를 복구 경로로 지정해서 복구하도록 하겠습니다. /home/oracle/temp/로 복구 경로를 지정하겠습니다.
-- 복구에 필요한 파일들을 모두 임시경로에 복원
SQL> create pfile from spfile;
$ rm -rf $ORACLE_HOME/dbs/spfile<SID>.ora
SQL> shutdown immediate;
SQL> !
$ mkdir /home/oracle/temp
-- 백업된 Data File 복원
$ cp /data/backup/close/*.dbf /home/oracle/temp/
-- 현재 사용 중인 control file 복사
$ cp /ORA19/app/oracle/oradata/ORACLE19/*.ctl /home/oracle/temp
-- 현재 사용 중인 redo log file 복사
$ cp /ORA19/app/oracle/oradata/ORACLE19/*.log /home/oracle/temp
-- control file, data file, redo log file 위치 변경
$ vi /ORA19/app/oracle/product/19c/dbs/init<SID>.ora
SQL> startup mount;
SQL> select name from v$controlfile;
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/system01.dbf'
3 to '/home/oracle/temp/system01.dbf';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf'
3 to '/home/oracle/temp/undotbs01.dbf';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf'
3 to '/home/oracle/temp/sysaux01.dbf';
SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
2 from v$logfile a, v$log b
3 where a.group#=b.group#
4 order by 1,2;
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo01.dbf'
3 to '/home/oracle/temp/redo01.dbf';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo02.dbf'
3 to '/home/oracle/temp/redo02.dbf';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo03.dbf'
3 to '/home/oracle/temp/redo03.dbf';
우리는 이전에 테이블이 drop된 시간을 알고 있습니다. 그 시간보다 살짝 이전 시간으로 복구를 수행합니다.
-- 복구하기
SQL> recover database until time '<복구 시간>';
SQL> alter database open resetlogs;
SQL> select *
2 from khyup.test01;
불완전복구 시도 시 ALTER DATABASE OPEN RESETLOGS 를 실행하는 이유
: 복구 시도 후 SCN#를 보게 되면, data file의 SCN#와 redo log, control file SCN#가 다른 경우가 생깁니다.(복구 중에 SCN#를 고치지는 않으므로 restore할때, 어떤 파일들을 restore하느냐에 따라 다른 경우가 생김.) 이 SCN#들을 통일하기 위하여,alter database open resetlogs으로 DB를 OPEN하게 됩니다.
RESETLOGS 하게 되면 일어나는 일들
: control file, redo log file(Archive log file들도) 들이 영향을 받습니다.
1. CONTROL FILE 의 SCN#를 DATA FILE SCN# 기준으로 변경합니다.
2. Redo log file이 존재하면, file들이 초기화됩니다.(존재하지 않으면, 새로 만듭니다.)
(resetlogs 한 시점 이전의 archive log file, redo log file은 없어도 되는 자료가 되어버립니다.)
- 위와 같은 특징들 때문에 위에서 설명한 주의 사항대로 임시 경로를 사용해서 복구를 시도해야 여러번 복구를 시도할 수 있습니다.
- 참고: https://gyh214.tistory.com/90
연습문제: 잘못된 update 수행 후 commit한 장애 복구하기
장애 상황
고객 사이트에서 update 작업 도중 잘못된 where 조건으로 잘못된 update가 수행되었다는 연락을 받았습니다. 그 후에 commit이 수행되어서 rollback도 할 수 없는 상황이 되어 버렸습니다. update한 시간은 정확하게 알고 있다고 합니다.
테이블 이름은 khyup.test02, 복구 경로는 /home/oracle/temp입니다. 모든 데이터 파일은 /data/backup/close/에 백업되어 있습니다.
Step 0) 장애 상황 세팅
SQL> shutdown immediate;
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/*.dbf /data/backup/close/
SQL> startup;
SQL> !vi df.sql
set line 200
col ts_name for a10
col file_name for a50
col m for 9999
select a.name "TS_NAME", b.name "FILE_NAME", b.bytes/1024/1024 MB, b.status
from v$tablespace a, v$datafile b
where a.ts#=b.ts#
SQL> @df
SQL> create tablespace test
2 datafile '/ORA19/app/oracle/oradata/ORACLE19/test01.dbf' size 10M;
SQL> create user khyup
2 identified by 1234
3 default tablespace test
4 quota unlimited on test
5 quota 0m on system;
SQL> create table khyup.test02
2 (no number,
3 name varchar2(10))
4 tablespace test;
SQL> insert into khyup.test02 values (1,'aaa');
SQL> insert into khyup.test02 values (2,'bbb');
SQL> commit;
SQL> insert into khyup.test02 values (3,'ccc');
SQL> commit;
-- update 작업 전 시간 표시(여기로 복구할 겁니다)
SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;
SQL> save tt.sql
SQL> update khyup.test02
2 set name = 'DDD';
SQL> commit;
SQL> select * from khyup.test02;
SQL> shutdown immediate;
SQL> !cp /data/backup/close/*.dbf /home/oracle/temp/
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/*.ctl /home/oracle/temp/
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/*.log /home/oracle/temp/
SQL> !vi /ORA19/app/oracle/product/19c/dbs/init<SID>.ora
or
SQL> alter system set control_files='/home/oracle/temp/control01.ctl'
2 scope=spfile;
SQL> select name from v$controlfile;
SQL> startup mount;
SQL> @df
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/system01.dbf'
3 to '/home/oracle/temp/system01.dbf';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf'
3 to '/home/oracle/temp/undotbs01.dbf';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf'
3 to '/home/oracle/temp/sysaux01.dbf';
SQL> @df
SQL> select member from v$logfile;
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo01.log'
3 to '/home/oracle/temp/redo01.log';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo02.log'
3 to '/home/oracle/temp/redo02.log';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo03.log'
3 to '/home/oracle/temp/redo03.log';
SQL> select member from v$logfile;
SQL> recover database until time '<update 이전 시간>';
SQL> alter database open resetlogs;
SQL> select * from khyup.test02;
연습문제: 잘못된 delete 장애 복구하기
장애 상황
고객사에서 잘못된 delete 작업 후 commit을 수행하였다는 연락을 받았습니다. 복구에 필요한 모든 파일은 존재하며, delete를 한 날짜 역시 알고 있습니다.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
SQL> create table khyup.test03
2 (no number,
3 name varchar2(10))
4 tablespace test;
SQL> !vi ct.sql
1 begin
2 for i in 1..2000 loop
3 insert into khyup.test03 values (i, dbms_random.string('U',5));
4 end loop;
5 commit;
6 end;
7 /
SQL> @ct
SQL> shutdown immediate;
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/*.dbf /data/backup/close/
SQL> startup;
SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;
SQL> delete from khyup.test03 purge;
SQL> commit;
SQL> select * from khyup.test03;
SQL> shutdown immediate;
SQL> !cp /data/backup/close/*.dbf /home/oracle/temp/
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/*.ctl /home/oracle/temp/
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/*.log /home/oracle/temp/
SQL> !vi /ORA19/app/oracle/product/19c/dbs/init<SID>.ora
or
SQL> alter system set control_files='/home/oracle/temp/control01.ctl'
2 scope=spfile;
SQL> select name from v$controlfile;
SQL> startup mount;
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/system01.dbf'
3 to '/home/oracle/temp/system01.dbf';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf'
3 to '/home/oracle/temp/undotbs01.dbf';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf'
3 to '/home/oracle/temp/sysaux01.dbf';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf'
3 to '/home/oracle/temp/khyup01.dbf';
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
SQL> select member from v$logfile;
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo01.log'
3 to '/home/oracle/temp/redo01.log';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo02.log'
3 to '/home/oracle/temp/redo02.log';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo03.log'
3 to '/home/oracle/temp/redo03.log';
SQL> select member from v$logfile;
SQL> recover database until time '<delete 이전 시간>';
SQL> alter database open resetlogs;
SQL> select count(*) from khyup.test03;
연습문제: 잘못된 drop user 장애 복구
Step 0) 장애 상황 세팅
SQL> set line 200;
SQL> col username for a10;
SQL> col default_tablespace for a10
SQL> col temporary_tablespace for a10
SQL> select username, default_tablespace DT, temporary_tablespace TT
2 from dba_users;
SQL> select name from v$controlfile;
SQL> select member from v$logfile;
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
SQL> shutdown immediate;
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/* /data/backup/close/
SQL> startup;
SQL> create tablespace test
2 datafile '/ORA19/app/oracle/oradata/ORACLE19/test01.dbf' size 10M;
SQL> create temporary tablespace temp3
2 tempfile '/ORA19/app/oracle/oradata/ORACLE19/temp03.dbf' size 10M;
SQL> create user tuser
2 identified by abcd
3 default tablespace test
4 temporary tablespace temp3
5 quota unlimited on test
6 quota 0m on system;
SQL> grant connect, resource to tuser;
SQL> conn tuser/abcd
SQL> create table test04 (no number);
SQL> create table test05 (no number);
SQL> insert into test04 values (04);
SQL> insert into test05 values (05);
SQL> commit;
SQL> select * from test04;
SQL> select * from test05;
-- drop user 이전 시간 기록
SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS');
SQL> conn / as sysdba;
SQL> drop user tuser cascade;
-- 스키마가 지워져 에러 발생
SQL> select * from tuser.test04;
Step 1) 복구
SQL> select status from v$instance;
SQL> select name from v$controlfile;
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/*.ctl /home/oracle/temp/
SQL> alter system set control_files='/home/oracle/temp/control01.ctl',
2 '/home/oracle/temp/control02.ctl',
3 '/home/oracle/temp/control03.ctl',
4 scope=spfile;
SQL> shutdown immediate;
SQL> !cp /data/backup/close/*.dbf /home/oracle/temp/
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/*.log /home/oracle/temp/
SQL> startup mount;
SQL> select name from v$controlfile;
SQL> select member from v$logfile;
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo01.log'
3 to '/home/oracle/temp/redo01.log';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo02.log'
3 to '/home/oracle/temp/redo02.log';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo03.log'
3 to '/home/oracle/temp/redo03.log';
SQL> select member from v$logfile;
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/system01.dbf'
3 to '/home/oracle/temp/system01.dbf';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf'
3 to '/home/oracle/temp/undotbs01.dbf';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf'
3 to '/home/oracle/temp/sysaux01.dbf';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/test01.dbf'
3 to '/home/oracle/temp/test01.dbf';
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
SQL> recover database until time '<drop user 이전 시간>';
SQL> alter database open resetlogs;
SQL> select * from tuser.test04;
SQL> select * from tuser.test05;
drop tablespace 명령으로 삭제된 테이블스페이스 복구하기 - 백업된 Controlfile이 있을 경우
drop tablespace의 경우 drop table과 약간 다릅니다. 기본적으로 복구를 할 때 control file의 tablespace의 정보를 기반으로 복구하게 되는데, drop tablespace는 control file의 tablespace 정보를 모두 삭제합니다. 즉, control file에 등록되지 않은 tablespace는 복구 대상이 아닌 것입니다. 실제 data file이 존재한다 하더라도 control file에 tablespace 정보가 없으면 무용지물입니다.
만약 drop tablespace를 수행했다면 복구하는 방법은 2가지가 있습니다.
첫 번째 방법은 백업된 control file을 이용해서 복구하는 방법이고, 나머지 하나는 뒷부분에서 살펴볼 삭제된 tablespace 정보를 redo / archive log 파일을 사용해 강제로 control file에 등록시키는 방법입니다.
이번에는 첫 번째 방법을 실습해봅시다.
Step 0) 장애 환경 세팅
SQL> shutdown immediate;
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/* /data/backup/close/
SQL> startup
SQL> create tablespace test
2 datafile '/ORA19/app/oracle/oradata/ORACLE19/test01.dbf' size 10M;
SQL> create user khyup
2 identified by 1234
3 default tablespace test
4 temporary tablespace temp_test
5 quota unlimited on test
6 quota 0m on system;
SQL> create table khyup.test06
2 (no number)
3 tablespace test;
SQL> insert into khyup.test06 values (6);
SQL> commit;
SQL> select * from khyup.test06;
SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS');
-- drop tablespace 장애 발생
SQL> drop tablespace test including contents and datafiles;
-- 에러 발생
SQL> select * from khyup.test06;
테이블 삭제나 사용자 삭제의 경우 복구가 가장 힘든 이유는 장애가 발생한 정확한 시간을 모르기 때문입니다. 하지만 drop tablespace의 경우 alert log 파일에 tablespace가 삭제된 시간을 정확하게 기록해 주기 때문에 시간을 찾는 것은 훨씬 쉽습니다.
$ vi $ORACLE_BASE/diag/rdbms/<DB_UNIQUE_NAME>/<SID>/trace/alert_testdb.log
SQL> !cp /data/backup/close/*.ctl /home/oracle/temp/
SQL> alter system set control_files='/home/oracle/temp/control01.ctl'
2 scope=spfile;
SQL> shutdown abort;
SQL> !cp /data/backup/close/*.dbf /home/oracle/temp/
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/*.log /home/oracle/temp/
SQL> startup mount;
SQL> select status from v$instance;
SQL> select name from v$controlfile;
SQL> select name from v$datafile;
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/system01.dbf'
3 to '/home/oracle/temp/system01.dbf';
...
SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo01.log'
3 to '/home/oracle/temp/redo01.log';
...
SQL> select member from v$logfile;
이제 recover를 실행할 텐데, 평소처럼 하게 될 경우 control file의 SCN보다 redo log file의 scn이 앞서게 되어 에러가 발생합니다. 그래서 복구 옵션 중에 using backup control file을 추가합니다. 이 옵션은 control file이 data file보다 더 오래되었다는 것을 oracle에게 알려주는 옵션입니다. 이 옵션을 주면 oracle은 control file의 SCN은 무시하고 우리가 설정하는 시간이나 until cancel 옵션으로만 복구 시점을 결정하게 됩니다.
SQL> recover database until time '<drop tablespace 이전 시간>';
SQL> recover database until time '<drop tablespace 이전 시간>' using backup controlfile;
SQL> alter database open resetlogs;
SQL> select * from khyup.test06;
불완전복구에서
resetlogs옵션을 주는 이유
1. 모든 log의 sequence 정보가 0으로 초기화됩니다. 즉, 이렇게 되면 이전에 생성되었던 archive log file은 더 이상 복구에 사용될 수 없게 됩니다.
2. data file의 checkpoint scn 정보는 open 시점의 scn으로 업데이트
3.dictionary의 data file의 정보와 control file의 data file 정보를 비교해서dictionary에 있지만 control file에 없는 data file은MISSINGXXXXXXX으로 가짜 엔트리를 생성합니다.
- resetlogs 옵션으로 DB가 open된 경우 이전에 받아 두었던 백업 파일은 사용할 수 없는 경우가 대부분이므로 반드시 다시 백업을 받아야 합니다.
연습문제: Drop Tablespace 장애 복구하기
1일: Ts_a: a.dbf, Ts_b: b.dbf, Ts_c: c.dbf, Control file, Redo log file
2일: Ts_d: d.dbf 생성 / 데이터 입력, control file만 백업
3일: Ts_d drop tablespace
4일: Ts_d tablespace 복구
신규 TS의 Ts_d의 백업 data file은 없고, Ts_d의 정보를 가진 control file 백업만 있는 상황에서 3일에 삭제된 tablespace Ts_d를 복구해야만 합니다.
SQL> select name from v$controlfile;
SQL> select member from v$logfile;
SQL> select name from v$datafile;
SQL> create tablespace test
2 datafile '/ORA19/app/oracle/oradata/ORACLE19/test01.dbf' size 10M;
SQL> create table test7 (no numer) tablespace test;
SQL> insert into test7 values (7);
SQL> commit;
SQL> alter database backup controlfile to '/data/backup/open/control01.ctl';
SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;
SQL> drop tablespace test including contents and datafiles;
-- Tablespace가 삭제된 시간을 alert log에서 확인
$ vi $ORACLE_BASE/diag/rdbms/<DB_UNIQUE_NAME>/<SID>/trace/a*.log
SQL> !cp /data/backup/open/*.ctl /home/oracle/temp/
SQL> alter system set control_files='/home/oracle/temp/control01.dbf'
2 scope=spfile;
SQL> shutdown immediate;
SQL> !cp /data/backup/close/system01.dbf /home/oracle/temp/
SQL> !cp /data/backup/close/sysaux01.dbf /home/oracle/temp/
SQL> !cp /data/backup/close/undotbs01.dbf /home/oracle/temp/
SQL> startup mount;
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/system01.dbf'
3 to '/home/oracle/temp/system01.dbf';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf'
3 to '/home/oracle/temp/sysaux01.dbf';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf'
3 to '/home/oracle/temp/undotbs01.dbf';
SQL> alter database
2 create datafile '/ORA19/app/oracle/oradata/ORACLE19/test01.dbf'
3 as '/home/oracle/temp/test01.dbf';
SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo01.log'
3 to '/home/oracle/temp/redo01.log';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo02.log'
3 to '/home/oracle/temp/redo02.log';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo03.log'
3 to '/home/oracle/temp/redo03.log';
SQL> recover database until time '<drop tablespace 이전 시간>' using backup controlfile;
SQL> alter database open resetlogs;
SQL> select * from test7;참고
- <실전 오라클 백업과 복구> - 서진수
Devops Engineer