Oracle datafile recovery after hotback

From: Dina <djgupt_at_gmail.com>
Date: 18 Jul 2005 09:14:43 -0700
Message-ID: <1121703282.970081.181090@g47g2000cwa.googlegroups.com>

Hello Oracle Gurus,

I came across a dabase recovery problem today (18th July-05) while doing some backup / recovery testing in test environment. The scenario is being explained in detail as below:

1.18-JUL-2005:10:18 am:

Hot backup of all files in tablespace users(it has only one file C:\ORACLE\ORADATA\DB2\USERS01.DBF) on
Backup File saved as <backup path>\users01_svd_on_18jul051018am.dbf

Tablespace "USERS" was brought out of backup mode (alter tablespace users end backup)

2. At 10:21 AM: some transactions were entered in a table which uses tablespace "USERS" and data was committed.

3. At 10:25 AM: Tablespace USERS was brought OFFLINE. Following command was used to copy hotbackup file to oracle datafile: copy <backup path>\users01_svd_on_18jul051018am.dbf C:\ORACLE\ORADATA\DB2\USERS01.DBF
This overwrote the actual datafile with a file which is not consistant with control file ie log files need to be applied to bring it to consistant level.
Mistake: I didn't copy the current existing datafile (C:\ORACLE\ORADATA\DB2\USERS01.DBF) before overwriting it with previous hot backup copy.

4.At 10:26 AM: Tried to bring tablespace USERS online but got following error.

SQL> alter tablespace users ONLINE;
alter tablespace users oNline
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: 'C:\ORACLE\ORADATA\DB2\USERS01.DBF'

<<DBWR trace file contains following error message>>

---

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'C:\ORACLE\ORADATA\DB2\USERS01.DBF'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 82076)


------------------------------------------------------------------

I think this happened because datafile
C:\ORACLE\ORADATA\DB2\USERS01.DBF is offline and it is inconsistant also.

5. I shut downed database and mounted the database and tried a desperate attempt to recover the datafile as below:

SQL> startup mount force
ORACLE instance started.

Total System Global Area 135338868 bytes

Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes

Database mounted.
SQL> recover tablespace users;

ORA-00283: recovery session canceled due to errors
ORA-01110: data file 3: 'C:\ORACLE\ORADATA\DB2\USERS01.DBF'
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'C:\ORACLE\ORADATA\DB2\USERS01.DBF'

Can anybody let me know what is option left for me to recover my tablespace USERS back without loosing the transactions that were entered after the last hotbackup at 10:18 am. Thanks a lot in advance for your precious time and effort.

Regards,
Dina Received on Mon Jul 18 2005 - 11:14:43 CDT