| Recovery of a Noncritical Data File in Archivelog Mode [message #472066] |
Wed, 18 August 2010 11:55  |
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  |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
 |
Mahesh Rajendran
Messages: 10708
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 #472090 is a reply to message #472082] |
Wed, 18 August 2010 13:21 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
