| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: backup of offline dropped datafile
Hi,
In fact Koen is right raising concern about backup/recovery - here is a test I did - tablespace USERS had two datafiles that were offline droped. The test was done on Oracle 10.2.
Probably the best option in this situation would be recreating tablespace.
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: ===========================================================RMAN-03002: failure of backup command at 12/23/2005 14:03:34 RMAN-06056: could not access datafile 5
RMAN> backup database skip inaccessible;
Starting backup at 23-DEC-05
using channel ORA_DISK_1
could not access datafile 5
skipping inaccessible file 5
could not access datafile 6
skipping inaccessible file 6
RMAN-06060: WARNING: skipping datafile compromises tablespace USERS
recoverabili
ty
RMAN-06060: WARNING: skipping datafile compromises tablespace USERS
recoverabili
ty
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\DATA\SYSTEM01.DBF input datafile fno=00004 name=C:\ORACLE\DATA\USERS01.DBF input datafile fno=00002 name=C:\ORACLE\DATA\UNDOTBS01.DBF input datafile fno=00003 name=C:\ORACLE\DATA\SYSAUX01.DBFchannel ORA_DISK_1: starting piece 1 at 23-DEC-05 channel ORA_DISK_1: finished piece 1 at 23-DEC-05 piece
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupsetincluding current control file in backupset channel ORA_DISK_1: starting piece 1 at 23-DEC-05 channel ORA_DISK_1: finished piece 1 at 23-DEC-05 piece
Regards
Mindaugas Navickas
OCP
> Tanel, Koen
>
> I do not think that Oracle would ever request a space from a datafile that is
> offline.
> From the scenario that Koen described, it doesn't look that storage been
> allocated for any objects in the dropped data file, so queries against
> objects
> shoudn't fail.
> Even if you recreate controlfiles/resetlogs (from backup or from trace),
> offline datafile will still apear in v$datafile - there is nothing you can do
> about it - but from my understanding it should not cause any issue.
>
> Regards
> Mindaugas Navickas
> OCP
> --- Tanel Põder wrote:
>
> > Hi,
> >
> > If your queries fail because of unaccessible data in this datafile then
> > there's not much you can do.
> >
> > If you add another datafile then you might get errors when segments need to
>
> > extend (into the offline datafile), this could be worked around by
> > preallocating extents for your segments. (it might not be enough when
> you're
> > doing direct path inserts though).
> >
> > From recovery part I don't see any other problems than additional
> complexity
> > and running with a tablespace in an abnormal state. I wouldn't personally
> > like to run my production database in such mode at all.
> >
> > Tanel.
> >
> > ----- Original Message -----
> > From: "Koen Van Langenhove"
> > To:
> > Sent: Friday, December 23, 2005 9:38 AM
> > Subject: backup of offline dropped datafile
> >
> >
> > > Hi,
> > > First things first:
> > > - 9.2.0.4 2-node RAC
> > > - solaris 8 / sun cluster 3.0
> > > - VxVM 3.2
> > > - production system, rather urgent
> > >
> > > we have this little situation where someone dropped a datafile using
> > > 'offline drop'. This was apparently not the best solution for its problem
>
> > > and we don't have archivelogs available to recover the datafile (only the
>
> > > archivelogs created during the backup are kept .. don't ask ..).
> > >
> > > As I understand it right now, there's no way we can "undrop" this
> > > datafile, our only option is to drop the tablespace and recreate it,
> which
> > > is what we'll do eventually. To get out of this situation we would like
> to
> > > continue for a while with this datafile dropped, while adding another
> > > datafile to make up for the lost space.
> > >
> > > Now, the question is, is this a good idea ? My main concern is
> > > backup/recovery, but any other hints would be highly appreciated.
> > >
> > > TIA,
> > > Koen
> > >
> > > PS the original problem:
> > > - added a datafile
> > > - the second node didn't have its symlink to the new vxvm volume
> > > - adding the datafile worked, everything fine on node 1
> > > - on node 2, got ORA-01157 on the new datafile
> > > - created symlink but the instance did not pick up the datafile
> > > - offline drop the datafile to bring the db into service again (while a
> > > 'alter system check datafiles' would have fixed it)
> > >
> > > --
> > > Regards,
> > > Koen
> > >
> > > Siemens COM
> > > COM D MN A
> > >
> > > Email : Koen.Van_Langenhove_at_siemens.com
> > > - - - - - - - Unix IS user-friendly .., it's just quite picky about its
> > > friends.
> > >
> > > --
> > > http://www.freelists.org/webpage/oracle-l
> > >
> > >
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
>
>
>
>
>
> __________________________________________________________
> Find your next car at http://autos.yahoo.ca
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 23 2005 - 20:19:58 CST
 |
 |