Backup/RMAN

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

잇꼬 2024. 1. 25. 16:34
728x90
반응형
SMALL

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

728x90
반응형
LIST