백업 받지 않은 tablespace의 데이터 파일 손상되었을 경우

<rman session>

RMAN> list backup;

RMAN> report schema;

#) backup 필요한지 확인
RMAN> report need backup;

#) backup 지우기
RMAN> delete backup;

Do you really want to delete the above objects (enter YES or NO)? yes(입력)

#) backup 받아야 하는게 있는지 확인
RMAN> report need backup;

#) 설정 및 옵션 

RMAN> show all;

#) 백업본 확인 
RMAN> list backup;

#) backup 받기
=> control file + spfile, 부분적 checkpoint
RMAN> backup database;

#) backup 확인
RMAN> list backup;
...
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T095224_lv3d68jx_.bkp => 물리적인 위치(backup 위치)

  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 2241112    25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
  2       Full 2241112    25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
  4       Full 2241112    25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
  5       Full 2241112    25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
  9       Full 2241112    25-JAN-24 /u01/app/oracle/oradata/ora11g/undo01.dbf
=> full check point

...

  SPFILE Included: Modification time: 25-JAN-24 => 물리적 위치(controlfile)
  SPFILE db_unique_name: ORA11G
  Control File Included: Ckp SCN: 2241145      Ckp time: 25-JAN-24
 => 부분적 check_point

■ 백업 받지 않은 테이블 스페이스에 데이터 파일 손상
#) tablespace 확인

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#;

=> checkpoint 개념 / user 관리&객체관리 

#) tablespace 생성 후 확인

create tablespace data_tbs datafile '/u01/app/oracle/oradata/ora11g/data01.dbf' size 5m;
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#;

#) 샘플 테이블 생성 후 확인
=> control file 저장 / redo log 저장

create table hr.dept_temp tablespace data_tbs as select * from hr.departments;
select count(*) from hr.dept_temp;

#) redo log 정보 확인

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;

#) 로그 스위치 강제 발생 

alter system switch logfile;

#) 아카이브 확인

! ls /home/oracle1/arch1

select sequence#, name, first_change#, next_change#, next_time
from v$archived_log;

#) data_tbs 장애 유발

! rm /u01/app/oracle/oradata/ora11g/data01.dbf
! ls /u01/app/oracle/oradata/ora11g/data01.dbf

#) DB 내리기(DB 작업상)

shutdown immediate

=> channel 끊김 / full checkpoint 발생

=============================================================================================
=> alert_ora11g.log : data file 깨짐!

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

#) 재접속

conn / as sysdba

#) 확인(backup 받지 않는 data file 만 손상)

! ls /u01/app/oracle/oradata/ora11g/data01.dbf

<rman session>
[oracle1@oracle ~]$ rman target /

#) DB mount
RMAN> startup mount

#) 장애 확인하기
RMAN> list failure;

#) 장애 자세히 보기
RMAN> list failure 82 detail;

#) 백업 확인하기 
RMAN> list backup;

#) 특정한 file에 대해서 백업본 보기
RMAN> list backup of tablespace data_tbs;

#) data01.dbf 0으로 보이는것은 깨졌기 때문
RMAN> report schema;

## 해결 방안 ##
#) offline 변경 
=> database 레벨
RMAN> sql 'alter database datafile 3 offline';

RMAN> alter database open;

<ora11g session>

conn / as sysdba
select status from v$instance;

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>
#) data01.dbf의 빈껍데기 생성, /u01/app/oracle/oradata/ora11g/data01.dbf 깨짐
RMAN> sql "alter database create datafile ''/u01/app/oracle/oradata/ora11g/data01.dbf''";

#) recover 하기
=> redo 적용
RMAN> recover tablespace data_tbs;

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

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

#) online으로 변경
RMAN> sql 'alter database datafile 3 online';

<ora11g session>
#) tablespace 확인

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#;

#) 테이블 확인

select count(*) from hr.dept_temp;

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

#) backup 받을게 있는지 확인
RMAN> report need backup;

#) data_tbs 백업받기
=> 부분적으로 받기
RMAN> backup tablespace data_tbs;

#) data_tbs backup 받았는지 확인
RMAN> list backup of tablespace data_tbs;

#) 백업 확인해보기
RMAN> list backup;