- 12 Sep, 2023
- 0 Comments
- 1 Min Read
How to Recover Undo Datafile ?
How to Recover Undo Datafile ?
Â
To recover undo Datafile Follow the steps given below :
- Change the undo_management parameter to manual.
SQL> show parameter undo_managementNAME TYPE VALUE --------------------------- ----------- ---------------------------- undo_management string AUTO SQL> alter system set undo_management=manual scope=spfile; System altered. SQL> startup force Database mounted. ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: 'C:\ORACLE\PRODUCT\19c\ORADATA\PRIM\UNDOTBS01.DBF' SQL> show parameter undo_management NAME TYPE VALUE ------------------------------------ ----------- ------------------- undo_management string MANUAL
-
Drop the datafile and open the database:
SQL>
alter
database datafile 'C:\ORACLE\product\19c\oradata\prim\undotbs01.dbf'
offline drop;
Database altered.
SQL>
alter
database open;
Database altered.
SQL>
select
file#, status, name
from
v$datafile;
FILE# STATUS NAME
---------- ------- -------------------------------------------------
1 SYSTEM C:\ORACLE\product\19c\oradata\prim\system01.dbf
2 offline C:\ORACLE\product\19c\oradata\prim\undotbs01.dbf
3 ONLINE C:\ORACLE\product\19c\oradata\prim\sysaux01.dbf
4 ONLINE C:\ORACLE\product\19c\oradata\prim\users01.dbf
-
Drop the undo tablespace and create new one:
SQL> drop tablespace undotbs1 including contents; Tablespace dropped. SQL> create undo tablespace undotbs2 datafile 'C:\ORACLE\product\19c\oradata\prim\undotbs02.dbf' size 100m; Tablespace created. SQL>
4. Change the new undo_tablespace parameter to AUTO and reboot the database:
SQL> alter system set undo_management=auto scope=spfile; System altered. SQL> alter system set undo_tablespace=undotbs2 scope=spfile; System altered. SQL> startup force Database mounted. Database opened. SQL>