Home » RDBMS Server » Backup & Recovery » Recovery of a Noncritical Data File in Archivelog Mode (10g,xp)
Recovery of a Noncritical Data File in Archivelog Mode [message #472066] Wed, 18 August 2010 11:55 Go to next message
zahidbashir
Messages: 34
Registered: September 2009
Member
Hello,
While trying to simulate datafile corruption, i opened the datafile USERS_2_01.DBF which belongs to users2 tablespace and deleted its first few lines and saved it.
I confirmed the damage
SQL> select * from my_emp;
      select * from my_emp
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 12)
ORA-01110: data file 7: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS_2_01.DBF'


Before doing that i backed up users2 tablespace using RMAN
Rman> backup tablespace users2;


When i tried to restore the tablespace it gave me the following error
RMAN-03002: failure of restore command at 08/19/2010 13:48:32
ORA-19870: error reading backup piece E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AR
EA\ORCL\BACKUPSET\2010_08_19\O1_MF_NNNDF_TAG20100819T133055_66V540OW_.BKP
ORA-19573: cannot obtain exclusive enqueue for datafile 7


What does it mean?
I then tried to drop the tablespace
SQL> drop tablespace users_2 including contents;
drop tablespace users_2 including contents
*
ERROR at line 1:
ORA-01122: database file 7 failed verification check
ORA-01110: data file 7: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS_2_01.DBF'
ORA-01251: Unknown File Header Version read for file number 7


Please tell me what did i do wrong and how can i fix it.
Regards,
Re: Recovery of a Noncritical Data File in Archivelog Mode [message #472069 is a reply to message #472066] Wed, 18 August 2010 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-19573: cannot obtain exclusive enqueue for datafile 7

ORA-19573: cannot obtain %s enqueue for datafile %s
 *Cause:  The file access enqueue could not be obtained for a file
          specified in a backup, copy or restore operation.
          If the enqueue type shown is 'shared', then the file is the
          input file for a backup or copy.  If the type is 'exclusive', then
          the file is the output file for a datafile copy or restore which
          is attempting to overwrite the currently active version of that
          file - in this case, the file must be offline or the database must
          be closed.  If the type is 'read-only', then you are attempting
          to back up or copy this file while the database is in NOARCHIVELOG
          mode.
 *Action: Wait until the conflicting operation is complete, then retry
          the copy or backup.  If the database is in NOARCHIVELOG mode, then
          all files being backed up must be closed cleanly.

You still hold the file.

1/ Put the file offline drop
2/ Drop the file
3/ Restore/recover from a previous backup.

Regards
Michel

Re: Recovery of a Noncritical Data File in Archivelog Mode [message #472070 is a reply to message #472066] Wed, 18 August 2010 12:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
oracle#oerr ora 19573
19573, 00000, "cannot obtain %s enqueue for datafile %s"
// *Cause:  The file access enqueue could not be obtained for a file
//          specified in a backup, copy or restore operation.
//          If the enqueue type shown is 'shared', then the file is the
//          input file for a backup or copy.  If the type is 'exclusive', then
//          the file is the output file for a datafile copy or restore which
//          is attempting to overwrite the currently active version of that
//          file - in this case, the file must be offline or the database must
//          be closed.  If the type is 'read-only', then you are attempting
//          to back up or copy this file while the database is in NOARCHIVELOG
//          mode.
// *Action: Wait until the conflicting operation is complete, then retry
//          the copy or backup.  If the database is in NOARCHIVELOG mode, then
//          all files being backed up must be closed cleanly.



Quote:
SQL> drop tablespace users_2 including contents;


Try offline drop.

[Updated on: Wed, 18 August 2010 12:11]

Report message to a moderator

Re: Recovery of a Noncritical Data File in Archivelog Mode [message #472072 is a reply to message #472070] Wed, 18 August 2010 12:12 Go to previous messageGo to next message
zahidbashir
Messages: 34
Registered: September 2009
Member
Thanks alot for the quick response Smile
Re: Recovery of a Noncritical Data File in Archivelog Mode [message #472075 is a reply to message #472072] Wed, 18 August 2010 12:14 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Perhaps you just need to take it offline.
Re: Recovery of a Noncritical Data File in Archivelog Mode [message #472077 is a reply to message #472075] Wed, 18 August 2010 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sure, to be able to open the database, but to get back the data in it you have to restore and recover it.

Regards
Michel
Re: Recovery of a Noncritical Data File in Archivelog Mode [message #472082 is a reply to message #472077] Wed, 18 August 2010 12:40 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Yes - what I meant to say is that OP should offline it before restore-and-recover, I think perhaps he missed that step.
If you do an offline drop, is it possible to restore afterwards? I can't test right now.
Re: Recovery of a Noncritical Data File in Archivelog Mode [message #472090 is a reply to message #472082] Wed, 18 August 2010 13:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
C:\>rman target / nocatalog

Recovery Manager: Release 10.2.0.4.0 - Production on Mer. Ao¹t 18 20:13:18 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: MIKA (DBID=1530411502)
using target database control file instead of recovery catalog

RMAN> backup tablespace "TEST";

Starting backup at 18/08/2010 20:14:14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00007 name=E:\ORACLE\BASES\MIKA\TEST01.DBF
channel ORA_DISK_1: starting piece 1 at 18/08/2010 20:14:15
channel ORA_DISK_1: finished piece 1 at 18/08/2010 20:14:16
piece handle=E:\ORACLE\FLASH\MIKA\MIKA\BACKUPSET\2010_08_18\O1_MF_NNNDF_T
15 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18/08/2010 20:14:17

RMAN> exit

Recovery Manager complete.

C:\>sqlplus michel/michel

SQL*Plus: Release 10.2.0.4.0 - Production on Mer. Ao¹t 18 20:15:34 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> alter database datafile 'E:\ORACLE\BASES\MIKA\TEST01.DBF' offline drop;

Database altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

C:\>rman target / nocatalog

Recovery Manager: Release 10.2.0.4.0 - Production on Mer. Ao¹t 18 20:16:51 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: MIKA (DBID=1530411502)
using target database control file instead of recovery catalog

RMAN> restore tablespace "TEST";

Starting restore at 18/08/2010 20:17:06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=150 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00007 to E:\ORACLE\BASES\MIKA\TEST01.DBF
channel ORA_DISK_1: reading from backup piece E:\ORACLE\FLASH\MIKA\MIKA\BACKUPSE
6R8QR0H_.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\ORACLE\FLASH\MIKA\MIKA\BACKUPSET\2010_08_18\O1_MF_NNNDF_TAG20100
15
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 18/08/2010 20:17:09

RMAN> recover tablespace "TEST";

Starting recover at 18/08/2010 20:17:16
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 18/08/2010 20:17:21

RMAN> exit

Recovery Manager complete.

C:\>sqlplus michel/michel

SQL*Plus: Release 10.2.0.4.0 - Production on Mer. Ao¹t 18 20:17:38 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> alter database datafile 'E:\ORACLE\BASES\MIKA\TEST01.DBF' online;

Database altered.

SQL> select file_id, status from dba_data_files where file_name='E:\ORACLE\BASES\MIKA\TEST01.DBF';
   FILE_ID STATUS
---------- ---------
         7 AVAILABLE

SQL> select status from v$datafile where file#=7;
STATUS
-------
ONLINE

Short example but it is still possible; but maybe the "drop" option is not necessary.

Regards
Michel
Re: Recovery of a Noncritical Data File in Archivelog Mode [message #472096 is a reply to message #472066] Wed, 18 August 2010 14:06 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Thank you for that, I have never attempted to restore after offline-drop. I always simply offline the file. Clearly, enough information is retained in the controlfile about dropped datafiles to permit restore. I'll have to do some more reverse engineering.....
Previous Topic: Old archive logs after open resetlogs
Next Topic: restart database
Goto Forum:
  


Current Time: Fri Mar 29 00:22:17 CDT 2024