Re: backup of offline dropped datafile
- From: Mindaugas Navickas <mnavickas@xxxxxxxxx>
- To: mnavickas@xxxxxxxxx, tanel.poder.003@xxxxxxx, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 23 Dec 2005 14:19:58 -0500 (EST)
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.
----------------------------------------------------------------
Starting backup at 23-DEC-05
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=23 devtype=DISK
could not read file header for datafile 5 error reason 1
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.DBF
channel ORA_DISK_1: starting piece 1 at 23-DEC-05
channel ORA_DISK_1: finished piece 1 at 23-DEC-05
piece
handle=C:\ORACLE\FLASH_RECOVERY_AREA\MN\BACKUPSET\2005_12_23\O1_MF_NNNDF_T
AG20051223T140941_1TRLVP81_.BKP tag=TAG20051223T140941 comment=NONE
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 backupset
including 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
handle=C:\ORACLE\FLASH_RECOVERY_AREA\MN\BACKUPSET\2005_12_23\O1_MF_NCNNF_T
AG20051223T140941_1TRLYDB3_.BKP tag=TAG20051223T140941 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 23-DEC-05
RMAN>
-----------------------------------------------------------------
Regards
Mindaugas Navickas
OCP
--- Mindaugas Navickas <mnavickas@xxxxxxxxx> wrote:
> 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 <tanel.poder.003@xxxxxxx> 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" <Koen.Van_Langenhove@xxxxxxxxxxx>
> > To: <oracle-l@xxxxxxxxxxxxx>
> > 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@xxxxxxxxxxx
> > > - - - - - - - Unix IS user-friendly .., it's just quite picky about its
> > > friends.
> > >
> > > --
> > > //www.freelists.org/webpage/oracle-l
> > >
> > >
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
>
>
>
>
>
> __________________________________________________________
> Find your next car at http://autos.yahoo.ca
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
__________________________________________________________
Find your next car at http://autos.yahoo.ca
--
//www.freelists.org/webpage/oracle-l
Other related posts:
