1)전체 검사 : block currupt inspection ( scope : all datafile)
rman target /
RMAN>  BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

output message

List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              17439        104962          19934666  
  File Name: +DATA/RACDB/DATAFILE/system.273.952013871
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              69828           
  Index      0              13233           
  Other      0              4460            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              24143        104975          19934756  
  File Name: +DATA/RACDB/DATAFILE/sysaux.261.952013907
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              16156           
  Index      0              15143           
  Other      0              49518           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              67           8960            19564241  
  File Name: +DATA/RACDB/DATAFILE/undotbs1.262.952013933
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              8893            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              1665         9600            19934756  
  File Name: +DATA/RACDB/DATAFILE/undotbs2.266.952014039
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              7935            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              1            641             9583459   
  File Name: +DATA/RACDB/DATAFILE/users.267.952013933
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              5               
  Index      0              0               
  Other      0              634             

channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2               
Control File OK     0              1212            
Finished backup at 23/02/02

 
2)block corrupt check 
RMAN> select * from v$database_block_corruption ;
 
3) Create pfile='/oracle/pfile.sql' from spfile ; pfile 백업
 
파일 사이즈 구하기 (사전에 파일사이즈 체크해보기) 
select tablespace_name, file_id, file_name, round (bytes / (1024 * 1024), 0) total_space
from dba_data_files where tablespace_name='UNDOTBS1'; <=Mbyte
 

 

4)백업 복구본이 있으면 아래와 같이 확인 가능( if you have backupset , you can recover like below)
rman target /
RMAN> VALIDATE DATAFILE 13;
 
blockrecover datafile 13 block 2916108;
blockrecover datafile 13 block 2916106;
blockrecover datafile 13 block 1251234;
 
 

TABLESPACE_NAME, CONTENTS, EXTENT_MAN <=언두 페이블 스페이스 확인

SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT
FROM DBA_TABLESPACES
WHERE CONTENTS = 'UNDO';
 

 

set linesize 200
col file_id format 99
col tablespace_name format a20
select file_id, tablespace_name  , file_name  from dba_data_files
where file_id=13 ;
 
 
방법 2: undol 테이블 재 생성 및 이름 바꾸기 (undo 일 경우만 가능)  recreate undo and rename
1. CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE
   '/u01/app/oracle/oradata/XE/undotbs2.dbf'
   SIZE 500M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;
 
2. ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 scope=both;
 
3. 디비 내렸다. 디비 내렸다 올리기 ( DB shutdown && startup 
 
4. 기존 지우기
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
 
5. 새로운 테이블
rename 이름 바꾸기 바꾸기
alter tablespace undotbs1 rename to undotbs;
select value from v$spparameter where name = 'undo_tablespace';
 
 
6. 디폴트 테이블 스페이스 이름 바꾸기
show parameter undo_tablespace ;
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS3
사업자 정보 표시
(주)블루원 | 김홍태 | 서울특별시 용산구 원효로 4가 135 금홍 2빌딩 | 사업자 등록번호 : 106-86-76684 | TEL : 02-3272-7200 | Mail : support_ora@blueone.co.kr | 통신판매신고번호 : 호 | 사이버몰의 이용약관 바로가기

+ Recent posts