DBA Hub

📋Steps in this guide1/2

How to Recover Undo Datafile ?

To recover undo Datafile Follow the steps given below :

oracle configurationintermediate
by OracleDba
13 views
1

Overview

- Change the undo_management parameter to manual. - Drop the datafile and open the database: Drop the datafile and open the database: - Drop the undo tablespace and create new one: Drop the undo tablespace and create new one:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
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

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

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

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>
2

Section 2

Code/Command (click line numbers to comment):

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
 force
Database mounted.
Database opened.
SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!