백업&복구 9.2 - 불완전복구 시간기반 실습추가 - Archive log mode 장애복구

drop table, update table, delete table 장애
 
 -  방법은 기존 시간기반 불완전복구 방법과 같다.
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-문제1. 시작
 
○ 파일이 삭제 된 후에 테이블이 drop
  : drop table 복구하기 (백업이 없는 경우)
 : (1일)-백업, (2일)-테이블생성, (3일)-rm 테이블스페이스, (4일)-drop 테이블
 
1. (1일) 현재 상태 조회 & 안전을 위한 백업
SYS> select name from v$datafile;
 
NAME
--------------------------------------------------
/data/imsy/system01.dbf
/data/imsy/undotbs01.dbf
/data/imsy/sysaux01.dbf
/home/oracle/oradata/testdb/users01.dbf
/data/imsy/example01.dbf
 
SYS> select name from v$controlfile;
 
NAME
--------------------------------------------------
/data/imsy/control01.ctl
/data/imsy/control02.ctl
/data/imsy/control03.ctl
 
SYS> select member from v$logfile;
 
MEMBER
--------------------------------------------------
/data/imsy/redo01_a.log
/data/imsy/redo02_a.log
/data/imsy/redo01_b.log
/data/imsy/redo03_a.log
/data/imsy/redo02_b.log
/data/imsy/redo03_b.log
 
6 rows selected.
 
SYS> shutdown immediate;
 
$ cp /data/imsy/*.dbf /data/backup/close/
$ cp /home/oracle/oradata/testdb/users01.dbf /data/backup/close/
$ cp /data/imsy/*.ctl
$ cp /data/imsy/*.log
 
SYS> startup
 
 
 
 
2. (2일) 테이블스페이스 TS_B 생성 후 scott.tt500 테이블 생성
 
SYS> create tablespace ts_b
  2  datafile '/data/imsy/ts_b01.dbf' size 10M;
 
SYS> create table scott.tt500 (no number) tablespace ts_b;
SYS> insert into scott.tt500 values (1);
SYS> insert into scott.tt500 values (2);
SYS> insert into scott.tt500 values (3);
SYS> commit;
 
SYS> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') from dual;
 
TO_CHAR(SYSDATE,'YYYY-MM-DD:HH24:MI:SS
--------------------------------------
2012-02-03:15:19:35
 
SYS> select * from scott.tt500;
 
 
 
 
3. (3일)- rm으로 해당파일 삭제 ~ (4일)- drop 테이블
 
$ rm -fr /data/imsy/ts_b01.dbf
 
SYS> drop table scott.tt500 purge;
에러
 
SYS> select * from scott.tt500;
에러
 
 
 
4. 필요한 파일 Restore (/data/imsy2/에 Restore)
 
SYS> shutdown abort;
SYS> shutdown immediate;
 
$ mkdir /data/imsy2/
 
① 백업해놓은 DATAFILE
$ cp /data/backup/close/*.dbf /data/imsy2/
 
 
② 현재 쓰고있는 controlfile, redo logfile
$ cp /data/imsy/*.ctl /data/imsy2/
$ cp /data/imsy/*.log /data/imsy2/
 
 
5. Recover - 파라미터 파일 및 컨트롤파일 정보 수정
$ vi /home/oracle/product/10g/dbs/inittestdb.ora
*.control_files='/data/imsy2/control01.ctl','/data/imsy2/control02.ctl','/data/imsy2/control03.ctl'
 
SYS> startup mount;
 
 
SYS> select name from v$controlfile;
 
NAME
--------------------------------------------------
/data/imsy2/control01.ctl
/data/imsy2/control02.ctl
/data/imsy2/control03.ctl
 
 
SYS> select name from v$datafile;
 
NAME
--------------------------------------------------
/data/imsy/system01.dbf
/data/imsy/undotbs01.dbf
/data/imsy/sysaux01.dbf
/home/oracle/oradata/testdb/users01.dbf
/data/imsy/example01.dbf
/data/imsy/ts_b01.dbf
 
SYS> alter database rename file '/data/imsy/system01.dbf' to '/data/imsy2/system01.dbf';
SYS> alter database rename file '/data/imsy/undotbs01.dbf' to '/data/imsy2/undotbs01.dbf';
SYS> alter database rename file '/data/imsy/sysaux01.dbf' to '/data/imsy2/sysaux01.dbf';
SYS> alter database rename file '/home/oracle/oradata/testdb/users01.dbf' to '/home/oracle/oradata/testdb/users01.dbf';
SYS> alter database rename file '/data/imsy/example01.dbf' to '/data/imsy2/example01.dbf';
 
(필요없는 users01.dbf 와 example01.dbf는 복원하지 말고 offline drop가능
SYS> alter database datafile '/home/oracle/oradata/testdb/users01.dbf' offline drop;
SYS> alter database datafile '/data/imsy/example01.dbf' offline drop;
)
 
 
※ 핵심 - 백업해둔 DATAFILE이 없을경우 log에서 해당 정보를 가져와 새로 생성해 줘야 한다.
          이때, 해당 테이블스페이스는 offline상태로 해주고, create해준후 생성한 파일을 online해준다.
 
SYS> alter database datafile '/data/imsy/ts_b01.dbf' offline
SYS> alter database create datafile '/data/imsy/ts_b01.dbf'
  2  as '/data/imsy2/ts_b01.dbf';
SYS> alter database datafile '/data/imsy2/ts_b01.dbf' online;
 
SYS> select name from v$datafile;
 
 
 
 
SYS> select member from v$logfile;
 
MEMBER
--------------------------------------------------
/data/imsy/redo01_a.log
/data/imsy/redo02_a.log
/data/imsy/redo01_b.log
/data/imsy/redo03_a.log
/data/imsy/redo02_b.log
/data/imsy/redo03_b.log
 
6 rows selected.
 
 
SYS> alter database rename file '/data/imsy/redo01_a.log' to '/data/imsy2/redo01_a.log';
SYS> alter database rename file '/data/imsy/redo01_b.log' to '/data/imsy2/redo01_b.log';
SYS> alter database rename file '/data/imsy/redo02_a.log' to '/data/imsy2/redo02_a.log';
SYS> alter database rename file '/data/imsy/redo02_b.log' to '/data/imsy2/redo02_b.log';
SYS> alter database rename file '/data/imsy/redo03_a.log' to '/data/imsy2/redo03_a.log';
SYS> alter database rename file '/data/imsy/redo03_b.log' to '/data/imsy2/redo03_b.log';
 
 
SYS> select member from v$logfile;
 
 
 
SYS> recover database until time '';
 
SYS> alter database open resetlogs;
 
SYS> select * from scott.tt500;
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-문제1. 끝
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-문제2. 시작
○ 잘못된 update 장애 복구하기
 : 테이블이름 = scott.test02  update후 commit 전 시간확인
 : 경로 = /home/oracle/temp
 
1. 현재 상태 조회 & 안전을 위한 백업
 
SYS> select name from v$datafile;
 
NAME
--------------------------------------------------
/data/imsy2/system01.dbf
/data/imsy2/undotbs01.dbf
/data/imsy2/sysaux01.dbf
/home/oracle/oradata/testdb/users01.dbf
/data/imsy/example01.dbf
/data/imsy2/ts_b01.dbf
 
6 rows selected.
 
SYS> select name from v$controlfile;
 
NAME
--------------------------------------------------
/data/imsy2/control01.ctl
/data/imsy2/control02.ctl
/data/imsy2/control03.ctl
 
SYS> select member from v$logfile;
 
MEMBER
--------------------------------------------------
/data/imsy2/redo01_a.log
/data/imsy2/redo02_a.log
/data/imsy2/redo01_b.log
/data/imsy2/redo03_a.log
/data/imsy2/redo02_b.log
/data/imsy2/redo03_b.log
 
6 rows selected.
 
$cp /data/imsy2/*.dbf /data/backup/close/
$cp /home/oracle/oradata/testdb/users01.dbf /data/backup/close/
$cp /data/imsy2/*.ctl /data/backup/close/
$cp /data/imsy2/*.log /data/backup/close/
 
 
2. table 생성 후 장애 발생
 
SYS> create table scott.test02 (no number,name varchar2(10)) tablespace ts_b;
 
SYS> insert into scott.test02 (1, 'AAA');
SYS> insert into scott.test02 (2, 'BBB');
SYS> commit;
SYS> insert into scott.test02 (3, 'CCC');
SYS> commit;
 
복구할 시간 조회
SYS> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;
 
 
3. 잘못된 업데이트후 commit
 
SYS> update scot.test02
  2  set name = 'DDD';
 
SYS> commit;
 
SYS> select * from scott.test02;
내용 전부 DDD로 바뀌어있음
 
 
4. 파일 복원(restore) - /home/oracle/temp/
 
SYS> shutdown immediate;
$ cp /data/backup/close/*.dbf /home/oracle/temp/
$ cp /data/imsy2/*.log /home/oracle/temp/
$ cp /data/imsy2/*.ctl /home/oracle/temp/
 
 
5. Recover - 파라미터 파일 및 딕셔너리 정보 수정
 
$ vi /home/oracle/product/10g/dbs/inittestdb.ora
*.control files='/home/oracle/temp/control01.ctl','/home/oracle/temp/control02.ctl','/home/oracle/temp/control03.ctl'
 
SYS> startup mount;
 
SYS> select name from v$datafile;
 
SYS> alter database rename file '' to '';
SYS> alter database rename file '' to '';
SYS> alter database rename file '' to '';
SYS> alter database rename file '' to '';
SYS> alter database rename file '' to '';
SYS> alter database rename file '' to '';
 
 
 
SYS> select member from v$logfile;
 
SYS> alter database rename file '' to '';
SYS> alter database rename file '' to '';
SYS> alter database rename file '' to '';
SYS> alter database rename file '' to '';
SYS> alter database rename file '' to '';
SYS> alter database rename file '' to '';
 
 
SYS> recover database until time '';
SYS> alter database open resetlogs;
SYS> select * from scott.test02;
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 문제2. 끝
 
 
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=문제3. 시작
○ 잘못된 delete 장애 복구하기
-나머지 내용 위와 같음-
☆ 장애상황생성 : 장애 일으키기 전 ~ delete 장애 일으키는 과정
 
SYS> createtable scott.test03 (no number, name varchar2(10)) tablespave test;
 
SYS> !vi ct.sql
1  begin
2  for i in 1..2000 loop
3     insert into scott.test03 values (i,dbms_random.string('U',5));
4  end loop;
5  commit;
6 end;
7 /
SYS> @ct
 
SYS> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') from dual;
복구에 필요한 시간
 
SYS> delete from scott.test03 purge;
SYS> commit;
SYS> select * from scott.test03;
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 문제3. 끝
 
 
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=문제4. 시작
○ 잘못된 drop user 장애 복구하기
-나머지 내용 위와 같음-
 
☆ 현재 user 상황 확인
 
SYS> col username for a10
SYS> col default_tablespace for a10
SYS> col temporary_tablespace for a10
SYS> select username,default_tablespace DT, temporary_tablespace TT from dba_users;
 
 
☆ 장애상황생성 : 장애 일으키기 전 ~ drop user 장애 일으키는 과정
 
1.신규 사용자 생성
 
SYS> create temporary tablespace temp3
  2  tempfile '/home/oracle/oradata/testdb/temp03.dbf' size 10M;
 
SYS> create user tuser
  2  identified by abcd
  3  default tablespace test
  4  temporary tablespace temp3;
 
SYS> grant connect, resource to tuser;
 
SYS> conn tuser/abcd;
 
TUSER> create table test04 (no number);
 
TUSER> create table test05 (no number);
 
TUSER> insert into test04 values (04);
 
TUSER> insert into test04 values (05);
 
TUSER> commit;
 
TUSER> select * from test04;
TUSER> select * from test05;
 
TUSER> select to_char(sysdata, 'YYYY-MM-DD:HH24:MI:SS') from dual;
이 시간으로 복구
 
 
SYS> drop user tuser;
에러
SYS> drop user tuser cascade;
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=문제4. 끝