- 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.
1234567891011121314151617181920212223242526
SQL>showparameter undo_managementNAME TYPE VALUE--------------------------- ----------- ----------------------------undo_management string AUTOSQL>altersystem set undo_management=manual scope=spfile;System altered.SQL>startupforceDatabase mounted.ORA-01157: cannot identify/lock data file 2 - see DBWR trace fileORA-01110: data file 2:'C:\ORACLE\PRODUCT\19c\ORADATA\PRIM\UNDOTBS01.DBF'SQL>showparameter undo_managementNAME TYPE VALUE------------------------------------ ----------- -------------------undo_management string MANUAL
-
Drop the datafile and open the database:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 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, namefrom 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:
1 2 3 4 5 6 7 8 9 10 11 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SQL>alter system set undo_management=auto scope=spfile;System altered. SQL>alter system set undo_tablespace=undotbs2 scope=spfile;System altered. SQL>startup forceDatabase mounted.Database opened.SQL> |