백업 받은 tablespace 운영 중에 삭제했을 경우

■  백업 받은 테이블 스페이스를 삭제했을 경우, 삭제한 시간을 통해 불안전한 리커버리 

<ora11g session>
#) redo log 정보 확인

SELECT a.file#, a.name AS file_name, b.name AS tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
WHERE a.ts# = b.ts#;

 

<rman session>
RMAN> list backup;

<ora11g session>
#) tablespace 생성

create tablespace data_tbs datafile '/u01/app/oracle/oradata/ora11g/data_tbs01.dbf' size 10m;
SELECT a.file#, a.name AS file_name, b.name AS tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
WHERE a.ts# = b.ts#;

 

================ ================ ================ ================ ================ ========
alert_ora11g.log

================ ================ ================ ================ ================ ========

#) table 생성 

create table hr.data_emp tablespace data_tbs as select * from hr.employees;
select count(*) from hr.data_emp;

#) redo 정보 확인

SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status, b.first_time, b.first_change#, b.next_change#
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;

<rman session>
#) 스키마 확인
RMAN> report schema;

#) 백업 받기
RMAN> backup database;

#) 백업 확인 
RMAN> list backup;

<ora11g session>
#) table 생성 후 확인

create table hr.data_emp_new tablespace data_tbs as select * from hr.employees;
select count(*) from hr.data_emp_new;

#) 로그 스위치 발생

alter system switch logfile;

#) 그룹 확인

SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status, b.first_time, b.first_change#, b.next_change#
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;

#) redo log 정보 확인 

SELECT a.file#, a.name AS file_name, b.name AS tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
WHERE a.ts# = b.ts#;

# 장애 유발 #
#) 운영중인 tablespace를 삭제 

drop tablespace data_tbs including contents and datafiles;

================ ================ ================ ================ ================ ========

================ ================ ================ ================ ================ ========

<rman session>
RMAN> list backup;

#) 비정상적인 종료 
RMAN> shutdown abort

#) nomount 까지 올리기

RMAN> startup nomount

#) control file로 복구 하기
backup의 control file 위치
/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159197678_lv3zgz7w_.bkp

RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159197678_lv3zgz7w_.bkp';

#) DB를 mount단계까지 올리기
RMAN> alter database mount;

#) 시간대로 recover 하기
#1) 시간 설정하기
RMAN> sql 'alter session set nls_date_format = "YYYY-MM-DD HH24:MI:SS"';

################################# ################################ ###########################

################################# ################################ ###########################

#2) 작업형모드로 하기
RMAN> run {
set until time '2024-01-25 15:26:30';
restore database;
recover database;
}

#) DB를 불안전하게 오픈
RMAN> alter database open resetlogs;

#) 스키마 확인
RMAN> report schema;

<ora11g session>

conn / as sysdba
SELECT a.file#, a.name AS file_name, b.name AS tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
WHERE a.ts# = b.ts#;

 

#) table 확인

select count(*) from hr.data_emp;
select count(*) from hr.data_emp_new;

 

#) current한 그룹 확인

SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status, b.first_time, b.first_change#, b.next_change#
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;

 

#) 불안전한 복구를 했으니 이전에 있던 백업은 사용할 수 없다. 아카이브가 필요없어졌다.
#1) 불필요한 아카이브 확인
RMAN> report obsolete;

#2)불필요한 아카이브 삭제
RMAN> delete obsolete;

#3) 불필요한 아카이브 파일 확인
RMAN> report obsolete;

#) backup 다시 받기
RMAN> backup database;

#) 백업 확인
RMAN> list backup;