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
=================
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 | 통신판매신고번호 : 호 | 사이버몰의 이용약관 바로가기
'DB - ORACLE' 카테고리의 다른 글
[TD - ORACLE] PDB Automatic Startup (0) | 2020.07.07 |
---|---|
[TD - ORACLE] RAC 아카이브 로그 용량 확인 하기 (0) | 2020.07.02 |
[TD - ORACLE] ORACLE RAC IP 변경하기 (스캔 IP, 리스너 IP 포함) (0) | 2020.07.02 |