How to Switch to a New Undo Tablespace

RMAN-03009: failure of backup

channel c3: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c3: starting piece 1 at 05-MAR-22
channel c3: finished piece 1 at 05-MAR-22
piece handle=/oraprod/RMAN/CURBKP/PROD_20220305_53376_1_FULL tag=BESCOM_FULL comment=NONE
channel c3: backup set complete, elapsed time: 00:00:01
channel c4: finished piece 1 at 05-MAR-22
piece handle=/oraprod/RMAN/CURBKP/PROD_20220305_53373_1_FULL tag=BESCOM_FULL comment=NONE
channel c4: backup set complete, elapsed time: 00:04:25
channel c2: finished piece 1 at 05-MAR-22
piece handle=/oraprod/RMAN/CURBKP/PROD_20220305_53371_1_FULL tag=BESCOM_FULL comment=NONE
channel c2: backup set complete, elapsed time: 00:05:25
channel c5: finished piece 1 at 05-MAR-22
piece handle=/oraprod/RMAN/CURBKP/PROD_20220305_53374_1_FULL tag=BESCOM_FULL comment=NONE
channel c5: backup set complete, elapsed time: 00:05:25
channel c1: finished piece 1 at 05-MAR-22
piece handle=/oraprod/RMAN/CURBKP/PROD_20220305_53370_1_FULL tag=BESCOM_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:05:35
released channel: c1
released channel: c2
released channel: c3
released channel: c4
released channel: c5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on c3 channel at 03/05/2022 15:08:40
ORA-19566: exceeded limit of 0 corrupt blocks for file /oraprod/VIS/db/apps_st/data/undo02.dbf

RMAN>

Recovery Manager complete.

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID


35 1756199 1 0 CORRUPT 0

1.show parameter undo_tablespace;

NAME TYPE VALUE


undo_tablespace string APPS_UNDOTS1

2.select tablespace_name, file_id, file_name,round (bytes / (1024 * 1024), 0) total_space from dba_data_files where tablespace_name=’APPS_UNDOTS1′

TABLESPACE_NAME FILED_ID FILE_NAME TOTAL SIZE

APPS_UNDOTS1 379 /oraprod/VIS/db/apps_st/data/undo01.dbf 12288

APPS_UNDOTS1 35 /oraprod/VIS/db/apps_st/data/undo02.dbf 10240

APPS_UNDOTS1 39 /oraprod/VIS/db/apps_st/data/undo03.dbf 10240

3.Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.

create undo tablespace APPS_UNDOTBS2 datafile ‘/oraprod/VIS/db/apps_st/data/apps_undo01.dbf’ size 12G;

  1. Switch to the new Undo tablespace

alter system set undo_tablespace = APPS_UNDOTBS2 scope=both;

5.Check the tablespace status
select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status;

TABLESPACE_NAME STATUS COUNT(*)


SYSTEM ONLINE 1
APPS_UNDOTS1 OFFLINE 39
APPS_UNDOTBS2 ONLINE 32

  1. Drop the old undo tablespace

SQL> Drop tablespace APPS_UNDOTS1 including contents and datafiles;

Leave a Comment

Your email address will not be published. Required fields are marked *