Recovery of lost Undo Datafile
Hi All,
This blog is about strange experience I had during the
restoration of database from cold backup.
I am working on R12 Upgrade project and as part of DBA
Upgrade activity I took cold backup of complete database and application at the
initial state of upgrade. Customer has provided us cifs file system to store our backups and it is somewhat different
to the normal unix file system. This file system is not case sensitive and it is replacing the files with the same name
but with different case and thus resulting in some loss of files.
Luckily/Unlucky in my current database I have UNDO Tablespace with 3 data files
and out of these two data files have same name with different case and thus
when I took a complete cold backup of database resulting in loss of data file.
Now I had to use this backup for building another database
and during recovery I found that one of the data files and when I investigated
I could identify that the undo data file 1 is replaced by undo data file 2. So,
As this is an undo data file I know that it doesn't contain user/system
data(database shutdown is graceful) and losing it doesn't lose any of our data.
Thus this gave us scenario of recovering database due to loss of undo data
file.
This cold backup can be helpful only if the database is
shutdown gracefully else the backup would be vane and I need to find another
source of backup for restoration. As this is cold backup I could go ahead in
recreating the lost undo data file and recover the database successfully.
Below is the procedure followed to restore the UNDO tablespace:
1) Verified the data files in backup location and showed
that both files are existing
[oratacrw@aoccol-101 CRP1Backup]$ pwd
/mnt/R12_Backups/CRP1Backup
[oratacrw@aoccol-101
CRP1Backup]$ ls -lrt APPS_UNDOTS1_2.DBF
-rwxrwSrwt 1 root root 2097160192 Aug 26 11:02
APPS_UNDOTS1_2.DBF
[oratacrw@aoccol-101
CRP1Backup]$ ls -lrt APPS_UNDOTS1_2*
-rwxrwxrwx 1 oratacrw dba 2097160192 Aug 26 11:02
APPS_UNDOTS1_2.dbf
2) Copy all the data files from backup location to data
files location
3) Modified the init parameters as per new db_name and also
changed below parameters for disabling auto undo management.
UNDO_MANAGEMENT=MANUAL
The
above setting makes database to use SYSTEM as undo tablespace instead of using
actual undo tablespace(by default)
4) Create spfile from modified pfile;
SQL> create spfile from pfile;
5) Recreate control file manually and shutdown the database.
6) Mount the database in restricted mode.
SQL> STARTUP RESTRICT MOUNT
ORACLE
instance started.
Total
System Global Area 1073741824 bytes
Fixed
Size 2089336 bytes
Variable
Size 427822728 bytes
Database
Buffers 629145600 bytes
Redo
Buffers 14684160 bytes
Database
mounted.
7) Offline drop the missing undo data file from database and
open the database.
SQL> ALTER DATABASE DATAFILE
'/u02/tacprefdata/APPS_UNDOTS1_01.dbf' OFFLINE DROP;
Database
altered.
SQL>
alter database open;
Database
altered.
8) Drop complete undo tablespace including datafiles.
SQL> DROP TABLESPACE
APPS_UNDOTS1 INCLUDING CONTENTS;
Tablespace dropped.
9) Recreate the undo tablespace with required datafiles.
SQL>
CREATE UNDO TABLESPACE APPS_UNDOTS1 DATAFILE
'/u02/tacprefdata/APPS_UNDOTS1_01.dbf' size 2G REUSE;
Tablespace
created.
10) shutdown the database and modify UNDO_MANAGEMENT parameter to "AUTO" and UNDO_TABLESPACE to "new undo ts"
SQL> shutdown immediate;
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
Init
parameters
undo_management = AUTO
undo_tablespace = APPS_UNDOTS1
11) Recreate spfile and bring up the database
SQL>
create spfile from pfile;
File
created.
SQL>
startup
ORACLE
instance started.
Total
System Global Area 1073741824 bytes
Fixed
Size 2089336 bytes
Variable
Size 427822728 bytes
Database
Buffers 629145600 bytes
Redo
Buffers 14684160 bytes
Database
mounted.
Database
opened
SQL>
sho parameter undo
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string APPS_UNDOTS1
Now the database is up and completely operational.
Thank you.
