Pages

Tuesday, October 12, 2021

Error in RMAN due to a corruption in UNDO Tablespace. How to Fix RMAN-03009; ORA-19501; ORA-27061; Linux-x86_64 Error: 5: Input/output error

=====================
General
=====================
Error in RMAN due to a corruption in UNDO Tablespace

 
RMAN-03009: failure of backup command on dev_0 channel at 10/12/2021 03:02:30
ORA-19501: read error on file "/oracle_db/db1/db_igt/ora_undotbs_01.dbf", block number 65664 (block size=8192)
ORA-27061: waiting for async I/Os failed
Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 524288

=====================
Troubleshoot
=====================
Try to validate datafile using RMAN calidate option
SELECT file_id, file_name 
  FROM DBA_DATA_FILES WHERE file_name LIKE '%UNDO%';

3 /oracle_db/db1/db_igt/ora_undotbs_01.dbf

OS>rman nocatalog
RMAN>connect target /
RMAN>validate datafile 3;


~>% rman nocatalog
Recovery Manager: Release 11.1.0.7.0 - Production on Tue Oct 12 15:18:32 2021
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
RMAN> connect target /
connected to target database: IGT (DBID=9999999999)
using target database control file instead of recovery catalog
RMAN> validate datafile 3
2> ;
Starting validate at 12-OCT-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1613 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00003 name=/oracle_db/db1/db_igt/ora_undotbs_01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of validate command on ORA_DISK_1 channel at 10/12/2021 15:18:53
ORA-19501: read error on file "/oracle_db/db1/db_igt/ora_undotbs_01.dbf", block number 65664 (block size=8192)
ORA-27061: waiting for async I/Os failed
Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 1048576

The file in question is 2Gb in size
-rw-r----- 1 oracle dba  2097160192 Oct 12 15:22 ora_undotbs_01.dbf



Try same with dbv
dbv file=/oracle_db/db1/db_igt/ora_undotbs_01.dbf blocksize=8192 FEEDBACK=100
DBVERIFY - Verification starting : FILE = /oracle_db/db1/db_igt/ora_undotbs_01.dbf
................................................................................
................................................................................
DBV-00600: Fatal Error - [28] [27061] [0] [0]

=====================
Solution
=====================
Indeed there is a corruption in UNDO Tablespace
Need to drop + create new UNDO Tablespace


show parameter undo_tablespace
  
NAME             TYPE       VALUE
---------------- ---------- --------------
undo_tablespace  string     UNDOTBS

SELECT tablespace_name, file_id, file_name,round (bytes / (1024 * 1024), 0) total_space 
FROM dba_data_files 
WHERE tablespace_name='UNDOTBS';

TABLESPACE_NAME FILE_ID FILE_NAME                                TOTAL_SPACE

--------------- ------- ---------------------------------------- --------------
UNDOTBS         3       /oracle_db/db1/db_igt/ora_undotbs_01.dbf 2000

Steps
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/oracle_db/db1/db_igt/ora_undotbs_02.dbf' SIZE 2000M;
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 SCOPE=BOTH;
SELECT TABLESPACE_NAME, STATUS, COUNT(*) FROM DBA_ROLLBACK_SEGS GROUP BY TABLESPACE_NAME, STATUS;
SELECT STATUS,SEGMENT_NAME FROM DBA_ROLLBACK_SEGS WHERE STATUS NOT IN ('OFFLINE') AND TABLESPACE_NAME='UNDOTBS';
SELECT TABLESPACE_NAME, STATUS, COUNT(*) FROM DBA_ROLLBACK_SEGS GROUP BY TABLESPACE_NAME, STATUS;
DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;

Steps by Example
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/oracle_db/db1/db_igt/ora_undotbs_02.dbf' SIZE 2000M;
Tablespace created.

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 SCOPE=BOTH;

System altered.

SELECT TABLESPACE_NAME, STATUS, COUNT(*) FROM DBA_ROLLBACK_SEGS GROUP BY TABLESPACE_NAME, STATUS;

TABLESPACE_NAME                STATUS                 COUNT(*)
------------------------------ -------------------- ----------
SYSTEM                         ONLINE                        1
UNDOTBS                        OFFLINE                     135
UNDOTBS2                       ONLINE                       47

SELECT STATUS,SEGMENT_NAME FROM DBA_ROLLBACK_SEGS WHERE STATUS NOT IN ('OFFLINE') AND TABLESPACE_NAME='UNDOTBS';
no rows selected

DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

Now, optionally, can revert back to the original name
CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/oracle_db/db1/db_igt/ora_undotbs_01.dbf' SIZE 2000M;
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS SCOPE=BOTH;
SELECT TABLESPACE_NAME, STATUS, COUNT(*) FROM DBA_ROLLBACK_SEGS GROUP BY TABLESPACE_NAME, STATUS;
SELECT STATUS,SEGMENT_NAME FROM DBA_ROLLBACK_SEGS WHERE STATUS NOT IN ('OFFLINE') AND TABLESPACE_NAME='UNDOTBS';
SELECT TABLESPACE_NAME, STATUS, COUNT(*) FROM DBA_ROLLBACK_SEGS GROUP BY TABLESPACE_NAME, STATUS;
DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;


select file_id, file_name from dba_data_files where file_name LIKE '%undo%';

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------
         3 /oracle_db/db1/db_igt/ora_undotbs_01.dbf


Try now again to validate datafiles using RMAN. This time it should be successful.

OS>rman nocatalog
RMAN>connect target /
RMAN>validate datafile 3;
RMAN> validate datafile 3
2> ;

Starting validate at 12-OCT-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1526 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00003 name=/oracle_db/db1/db_igt/ora_undotbs_01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              255524       256000          16015824263262
  File Name: /oracle_db/db1/db_igt/ora_undotbs_01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              476

Finished validate at 12-OCT-21

No comments:

Post a Comment