Techdata VINA 지식 공유

1)전체 검사 : block currupt inspection ( scope : all datafile)

rman target /

RMAN>  BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

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;

 

 

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