Error:

ORA-01578: ORACLE data block corrupted (file # 3, block # 136)
ORA_01110: data file 3: ‘/u01/oradata/ORCL/UNDOTBS01.DBF’

This procedure replaces the current UNDO tablespace which contains a corrupted block with a new clean tablespace.

1. Startup database in nomount mode.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 1.2761E+10 bytes
Fixed Size 2240256 bytes
Variable Size 8388608256 bytes
Database Buffers 4362076160 bytes
Redo Buffers 7634944 bytes
SQL>

2. Verify that you are using an spfile and not a pfile for your system paramaters.

SQL> show parameter spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string
SQL>

2a. If parameter spfile has a value, continue to step 3, if it does not perform 2b through 2e.

2b. Execute ‘create spfile’ command:

SQL> create spfile from pfile;
File created.
SQL>

2c. Shutdown the oracle instance.

SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>

2d. Startup the oracle instance in nomount mode.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.2761E+10 bytes
Fixed Size 2240256 bytes
Variable Size 8388608256 bytes
Database Buffers 4362076160 bytes
Redo Buffers 7634944 bytes
SQL>

2e. Verify the use of spfile with the show command.

SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string /opt/app/oracle/ORCL_db/dbs/sp
fileORCL.ora
SQL>

3. Get the values for parameters undo_management and undo_tablespaces.

SQL> show parameter undo_management

NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
SQL> show parameter undo_tablespace

NAME TYPE VALUE
———————————— ———– ——————————
undo_tablespace string UNDOTBS1
SQL>

4. Change the undo_management setting to manual.

SQL> alter system set undo_management=manual scope=spfile;
System altered.
SQL>

5. Mount and Open the database instance.

SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
SQL>

6. Create new undo tablespace.

SQL> create undo tablespace undotbs02 datafile ‘/u02/oradata/ORCL/UNDOTBS02.DBF’ size 5G;

Tablespace created.
SQL>

7. alter parameter undo_tablespace to undotbs02.

SQL> alter system set undo_tablespace=undotbs02 scope=spfile;
System altered.
SQL>

8. alter parameter undo_management to auto.

SQL> alter system set undo_management=auto scope=spfile;
System altered.
SQL>

9. Shutdown and startup the oracle instance.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1.2761E+10 bytes
Fixed Size 2240256 bytes
Variable Size 8388608256 bytes
Database Buffers 4362076160 bytes
Redo Buffers 7634944 bytes
Database mounted.
Database opened.
SQL>

10. Check parameters undo_management and undo_tablespaces.

SQL> show parameter undo_management

NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
SQL> show parameter undo_tablespace

NAME TYPE VALUE
———————————— ———– ——————————
undo_tablespace string UNDOTBS02
SQL>

That completes changing undotablespaces in 11g

Larry Catt
OCP