CloneDB와 exp/imp를 이용한 데이터 복구 - drop table

장애 상황

개발자가 실수로 아주 중요한 고객 테이블을 삭제했다. 삭제 시간은 거의 알고 있고, 백업 파일과 아카이브 리두 로그 파일도 전부 있다. 여분의 서버가 없고 즉시 긴급으로 복구해야 할 상황이다. 백업 파일과 아카이브 리두 로그 파일의 용량은 4T 정도여서 외부로 이동할 수도 없는 상황이다. 운영 중인 서버를 역시 중단할 수도 없다.

복구 방법

현재 운영 중인 서버에 하나의 인스턴스를 더 생성해서 장애난 테이블을 시간기반불완전 복구로 즉시 복구한 후 export 받고 운영서버에 import 하는 방법(CloneDB)를 사용

전제 조건

• 백업 파일 경로 : /data/backup/close/
• 운영DB SID : oracle
• clone DB SID : clonedb
• clone DB 파일 경로 : /data/temp/clone/

작업 순서

1) clone DB용 파라미터 파일 생성
2) 백업 데이터 파일 복원
3) 컨트롤 파일 재생성
4) 장애난 시점 직전까지 시간기반 복구로 테이블 복구
5) Export 수행 후 운영 DB로 import 수행
6) 복구 확인

0) 장애 상황 만들기

create tablespace test01
datafile '/ORACLE/app/oracle/oradata/ORACLE/test01.dbf' size 10M;

shutdown immediate;

cp /ORACLE/app/oracle/oradata/ORACLE/* /data/backup/close/

startup

create user scott
identified by tiger
default tablespace test01
quota unlimited on test01
quota 0m on system;

grant connect, resource to scott;

conn scott/tiger;

create table gogak
(no number,
name varchar2(10),
tel number(11))
tablespace test01;

begin
for i in 1..100 loop
  insert into gogak values (i, dbms_random.string('A',10),dbms_random.value(1,10));
end loop;
commit;
end;
/

select * from gogak;

commit;

select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;

save s.sql

@s

drop table gogak purge;

select * from gogak;

conn / as sysdba;

alter system switch logfile;

/

/

/

/

1) 복구 서버(clonedb)용 파라미터 파일 생성

파라미터 파일에서 변경해야 할 내용은 아래와 같습니다
1) db_name
2) control_files
3) memory_target 주석 처리

memory_target : 시스템 전역 가용 메모리양 정의

• 11g에서 AMM(Automatic Memory Management)를 사용할 경우 SGA, PGA가 사용할 최대 메모리양을 memory_target에 명시

create pfile from spfile;

cd $ORACLE_HOME/dbs

cp initoracle.ora initclonedb.ora

vi initclonedb.ora
...
db_name='clonedb'
...
control_files='/data/temp/clone/control01.ctl'
...
#*.memory_target=1572m

:wq!

2) cloneDB 용 control file 생성

• Data File 복사
• control file 생성, 내용 수정 및 실행

mkdir -p /data/temp/clone

cp /data/backup/close/*.dbf /data/temp/clone/

select instance_name from v$instance;

alter database backup controlfile to trace as '/home/oracle/re.sql';

control file 내용에서 변경할 내용은 아래와 같습니다.

• 1) DB 이름 변경
• 2) Redo Log File 경로 변경
• 3) Data File 경로 변경

vi /home/oracle re.sql
-- REUSE -> SET, "ORACLE" -> "CLONEDB", /ORACLE/app/oracle/oradata/ORACLE/ -> /data/temp/clone/ 으로 모두 변경
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORACLE" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/ORACLE/app/oracle/oradata/ORACLE/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/ORACLE/app/oracle/oradata/ORACLE/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/ORACLE/app/oracle/oradata/ORACLE/redo03.log'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/ORACLE/app/oracle/oradata/ORACLE/system01.dbf',
  '/ORACLE/app/oracle/oradata/ORACLE/sysaux01.dbf',
  '/ORACLE/app/oracle/oradata/ORACLE/undotbs01.dbf',
  '/ORACLE/app/oracle/oradata/ORACLE/users01.dbf',
  '/ORACLE/app/oracle/oradata/ORACLE/test01.dbf'
CHARACTER SET KO16MSWIN949
;

:%s/\/ORACLE\/app\/oracle\/oradata\/ORACLE\//\/data\/temp\/clone\/

-- 변경된 내용
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "CLONEDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/data/temp/clone/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/data/temp/clone/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/data/temp/clone/redo03.log'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/data/temp/clone/system01.dbf',
  '/data/temp/clone/sysaux01.dbf',
  '/data/temp/clone/undotbs01.dbf',
  '/data/temp/clone/users01.dbf',
  '/data/temp/clone/test01.dbf'
CHARACTER SET KO16MSWIN949
;

export ORACLE_SID=clonedb

@/home/oracle/re.sql

select status from v$instance;

select name from v$controlfile;

3) drop된 gogak 테이블 복구하기

운영서버(oracle)의 아카이브 리두 로그 파일을 복구서버(clonedb)로 복사

cp /ORACLE/app/oracle/fast_recovery_area/ORACLE/archivelog/2023_03_28/* /ORACLE/app/oracle/fast_recovery_area/CLONEDB/archivelog/2023_03_28/

테이블이 삭제되기 이전 시점으로 control file을 이용해 롤백(recover database using backup controlfile)

이후 resetlogs로 db open

resetlogs : redo log 초기화

• Redo Log file이 최신 파일이라면 noresetlogs
• control file을 새로 생성했거나 백업 받은 경우 resetlogs 이용
• 존재하는 리두 로그 파일은 초기화, 없는 리두 로그 파일은 생성

recover database until time '2023-03-28:10:03:22' using backup controlfile;

alter database open resetlogs;

select instance_name, status from v$instance;

select count(*) from scott.gogak;

진행 과정에서 ORACLE 디렉토리에 있는 아카이브 리두 로그 파일을 CLONEDB 아래로 복사하지 않으면 아래와 같은 문제 발생

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/ORACLE/app/oracle/fast_recovery_area/CLONEDB/archivelog/2023_03_28/o1_mf_1_7_%
u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

4) 복구된 gogak 테이블을 운영서버로 이동하기

export ORACLE_SID=clonedb
exp scott/tiger file=gogak.dmp tables=gogak

export ORACLE_SID=oracle
imp scott/tiger file=gogak.dmp ignore=y

select instance_name, status from v$instance;

select count(*) from scott.gogak;

CloneDB와 exp/imp를 이용한 데이터 복구 - drop tablespace

장애 상황

고객의 실수로 테이블 스페이스가 삭제되면서 중요한 gogak 테이블이 삭제되었다. 테이블 스페이스가 삭제된 시간을 대략 알고 있고(alert log에 기록됨) 현재 상태를 확인하던 도중에 지워진 테이블 스페이스의 데이터 파일 백업이 없다는 것을 발견했다. 다행히 아카이브 로그 파일은 존재하고 있다. 여분의 백업 서버가 없어서 운영서버에서 지금 즉시 복구해야 한다.

전제 조건

• 백업 파일 경로 : /data/backup/open
• 운영서버 SID : oracle
• 복구서버 SID : clonedb
• 복구서버 파일 경로 : /data/temp/clone

0) 장애 상황 만들기

select tablespace_name, bytes/1024/1024 MB, file_name
from dba_data_files;

vi ob.sql

alter tablespace system begin backup;
!cp -av /ORACLE/app/oracle/oradata/ORACLE/system01.dbf /data/backup/open/
alter tablespace system end backup;

alter tablespace sysaux begin backup;
!cp -av /ORACLE/app/oracle/oradata/ORACLE/sysaux01.dbf /data/backup/open/
alter tablespace sysaux end backup;

alter tablespace undotbs1 begin backup;
!cp -av /ORACLE/app/oracle/oradata/ORACLE/undotbs01.dbf /data/backup/open/
alter tablespace undotbs1 end backup;

alter tablespace users begin backup;
!cp -av /ORACLE/app/oracle/oradata/ORACLE/users01.dbf /data/backup/open/
alter tablespace undotbs1 end backup;

alter database backup controlfile to trace as '/data/backup/open/control.ctl';

:wq!

@ob.sql

create tablespace test01
datafile '/ORACLE/app/oracle/oradata/ORACLE/test01.dbf' size 10M;

create user scott
identified by tiger
default tablespace test01
quota unlimited on test01
quota 0m on system;

grant connect, resource to scott;

conn scott/tiger;

create table gogak
(no number,
name varchar2(10),
tel number(11))
tablespace test01;

begin
for i in 1..100 loop
  insert into gogak values (i, dbms_random.string('A',10),dbms_random.value(1,10));
end loop;
commit;
end;
/

select * from gogak;

commit;

select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;

save s.sql

@s

conn / as sysdba

drop tablespace test01 including contents and datafiles;

alter system switch logfile;

/

/

/

/

@s

-- alert log에서 테이블 스페이스 삭제 시간 확인
cd $ORACLE_BASE/diag/rdbms/oracle/ORACLE/trace
vi a*.log
...
2023-03-28T15:30:51.485938+09:00
drop tablespace test01 including contents and datafiles
2023-03-28T15:30:54.393787+09:00
Deleted file /ORACLE/app/oracle/oradata/ORACLE/test01.dbf
Completed: drop tablespace test01 including contents and datafiles
...

1) 복구서버용 파라미터 파일 생성

create pfile from spfile

cd $ORACLE_HOME/dbs

cp initoracle.ora initclonedb.ora

vi initclonedb.ora
...
db_name='clonedb'
...
control_files='/data/temp/clone/control1.ctl'
...
#*.memory_target=1572m

:wq!

2) 백업 데이터 파일 복원

cp /data/backup/open/*.dbf /data/temp/clone/

3) 컨트롤 파일 재생성

alter database backup controlfile to trace as '/home/oracle/re.sql'

vi /home/oracle/re.sql
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "CLONEDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/data/temp/clone/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/data/temp/clone/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/data/temp/clone/redo03.log'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/data/temp/clone/system01.dbf',
  '/data/temp/clone/sysaux01.dbf',
  '/data/temp/clone/undotbs01.dbf',
  '/data/temp/clone/users01.dbf'
CHARACTER SET KO16MSWIN949
;

:wq!

export ORACLE_SID=clonedb
@/home/oracle/re.sql

4) 아카이브 리두 로그 파일 복사

cp /ORACLE/app/oracle/fast_recovery_area/ORACLE/archivelog/2023_03_28/* /ORACLE/app/oracle/fast_recovery_area/CLONEDB/archivelog/2023_03_28/

5) Tablespace 복구하기

-- drop tablespace 명령을 하기 조금 전으로 시점을 잡습니다
recover database until time '2023-03-28:15:30:40' using backup controlfile;

ORA-00279: change 567149 generated at 03/28/2023 15:20:55 needed for thread 1
ORA-00289: suggestion :
/ORACLE/app/oracle/fast_recovery_area/CLONEDB/archivelog/2023_03_28/o1_mf_1_7_l2
52dyjc_.arc
ORA-00280: change 567149 for thread 1 is in sequence #7


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 5: '/ORACLE/app/oracle/oradata/ORACLE/test01.dbf'


ORA-01112: media recovery not started


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/data/temp/clone/system01.dbf
/data/temp/clone/sysaux01.dbf
/data/temp/clone/undotbs01.dbf
/data/temp/clone/users01.dbf
/ORACLE/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00005

-- test01.dbf 파일로 수정합니다
alter database create datafile '/ORACLE/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00005' 
as '/data/temp/clone/test01.dbf';

recover database until time '2023-03-28:15:30:40' using backup controlfile;

alter database open resetlogs;

select instance_name, status from v$instance;

select count(*) from scott.gogak;

6) 운영서버에 tablespace 복구

복구가 완료되면 clonedb에서 export 후 운영서버에서 import를 수행합니다.

옮겨줄 운영서버에 tablespace를 생성해주지 않으면, default tablespace에 table이 복원되고, tablespace를 생성해주면 해당 tablespace에 table이 복원됩니다.

tablespace는 scott 계정으로 옮겨줄 수 없습니다.

export ORACLE_SID=clonedb
exp \'/ as sysdba\' file=gogak.dmp tablespaces=test01

export ORACLE_SID=oracle

create tablespace test01
datafile '/ORACLE/app/oracle/oradata/ORACLE/test01.dbf' size 10M;

imp \'/ as sysdba\' file=gogak.dmp tablespaces=test01 ignore=y full=y

select count(*) from scott.gogak;

---

• 참고 : <실전 오라클 백업과 복구> - 서진수