운영 중에 data02.dbf 추가 후 삭제했을 경우

<ora11g session>
#) data02.dbf 추가 후 확인

alter tablespace data_tbs add datafile '/u01/app/oracle/oradata/ora11g/data02.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#;

 

#) 테이블 생성 후 확인

create table hr.emp_20240125 tablespace data_tbs as select * from hr.employees;
select f.tablespace_name, f.file_name
from dba_extents e, dba_data_files f
where f.file_id = e.file_id
and e.segment_name = 'EMP_20240125'
and e.owner = 'HR';

 #) 대량의 data 넣고 저장

insert into hr.emp_20240125 select * from hr.emp_20240125;
commit;

#) 용량 체크

select bytes/1024/1024 MB
from dba_segments
where segment_name = 'EMP_20240125'
and owner = 'HR';

select f.tablespace_name, f.file_name, count(*)
from dba_extents e, dba_data_files f
where f.file_id = e.file_id
and e.segment_name = 'EMP_20240125'
and e.owner = 'HR'
group by f.tablespace_name, f.file_name;

<rman session>
#) 백업 받아야 테이블스페이스 확인
RMAN> report need backup;
=> datafile 레벨로 backup 받기

#) 테이블스페이스 백업 받으면 중복되니 datafile로 받기
RMAN> backup datafile '/u01/app/oracle/oradata/ora11g/data02.dbf';

#) data_tbs 백업본 확인
=> data02.dbf 추가된 것 확인하기
RMAN> list backup of tablespace data_tbs;

<ora11g session>
#) 로그 스위치 발생 

alter system switch logfile;

#) 테이블 생성 후 테이블스페이스 확인

create table hr.dept_20240125 tablespace data_tbs as select * from hr.departments;

select f.tablespace_name, f.file_name, count(*)
from dba_extents e, dba_data_files f
where f.file_id = e.file_id
and e.segment_name = 'DEPT_20240125'
and e.owner = 'HR'
group by f.tablespace_name, f.file_name;

#) 로그 스위치 발생

alter system switch logfile;

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

#) 장애 유발 

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

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

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

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

=> DB 내려감.. 

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

#) DB mount 
RMAN> startup mount

#) 장애 확인
RMAN> list failure;
=> 과거 파일

#) schema 확인
=> 문제되는 data file size가 0으로 확인
RMAN> report schema;

## 해결 방안 ##
#) 문제되는 data02.dbf를 offline로 변경
RMAN> sql 'alter database datafile 6 offline';

#) DB open 올리기
RMAN> alter database open;

<ora11g session>
#) 재접속 후 data01.dbf에 있는 table 확인

conn / as sysdba
select count(*) from hr.dept_temp;

 

#) data02.dbf에 있는 table 확인

select count(*) from hr.emp_20240125;

#) 확인해보기

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>
#) data file 복구 하기
RMAN> restore datafile 6;

#) datafile 레벨로 recover 하기
RMAN> recover datafile 6;

#) online으로 변경하기 
RMAN> sql 'alter database datafile 6 online';

 

#) 스키마 확인
=> data02.dbf 용량 확인해보기
RMAN> report schema;

<ora11g session>
#) data02.dbf에 있는 table 확인해보기

select count(*) from hr.emp_20240125;

#) data file 상태 확인 

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

#) data_tbs 삭제 후 확인

drop tablespace data_tbs including contents and datafiles;

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> report obsolete;

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

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

#) 정책상 필요없는 아카이브 파일 삭제
RMAN> delete obsolete;

yes 입력

#) 필요없는 파일이 있는지 확인
RMAN> report obsolete;

#) 백업본 확인
=> 불필요한 게 지워졌는지 확인하기
RMAN> list backup;

<ora11g session>

! ls /home/oracle1/arch1

select sequence#, name, first_change#, next_change#, next_time
from v$archived_log
where name is not null
order by 1;