Re: "Mirroring" of DBF files

Howard,

I have just one thing to add, that I've used when moving files to new volumes:
(from memory, would have to test)

it is possible to:
for each tablespace where (contents != 'UNDO' or contents != 'TEMPORARY')
 and tablespace_name != 'SYSTEM'
 put the tablespace into READ ONLY mode
  for each datafile in the tablespace
   copy the datafile(s) in the operating system to the new volume  offline the tablespace
  for each datafile in the tablespace
   rename the datafile(s) to their new location  bring the tablespace back online
 bring the tablespace back into READ WRITE mode end

that could help to reduce the downtime window, depending upon what type of usage the segments in that tablespace are subject to.

Paul

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<3fabe94a$0$15336$afc38c87_at_news.optusnet.com.au>...
> "Keith Kris" <dejanews_at_keithkris.com> wrote in message
> news:c0a42db2.0311070605.58fa9baa_at_posting.google.com...
> > Hi all,
> >
> > I'm a UNIX admin, and only have minor oracle experience, so chances
> > are this is a stupid question (there are no stupid questions, only
> > stupid people.) We have a production database which is growing a
> > little large. We're in the process of adding more disk to allow
> > growth. We'd like to migrate the existing system away from the 12GB
> > volumes it currently uses, and toward 72GB volumes. We'd like to have
> > only one size of volume used on the system, so we'd need to move our
> > data from the old ones to the new larger filesystems. Talking to our
> > DBA, he says the only method to accomplish this is to shut down the
> > database, move the files, modify init.ora and bring the instance back
> > up. The problem with this is it would create too much down time
> > (we're moving more than a quarter terabyte.) Is there any method
> > within oracle to have the database either use two identical copies of
> > a DBF file simultaneously (essentially mirror them) or to have oracle
> > itself relocate a DBF file to prevent downtime? I'm open to ideas
> > here, so creative thinking is welcome.
> >
> > Thanks
> > Keith
>
> Hi Keith,
>
> In an idle moment, when the DBA is busy rebuilding some indexes for lack of
> anything productive to do, ask him the following questions:
>
> a) Where in the init.ora is there a single reference to the location of a
> data file?
> b) Has he ever heard of the 'alter tablespace X offline' command?
> c) Or the 'alter database rename file 'X" to 'Y' command?
> d) Or the 'alter tablespace X online' command?
>
> In other words, the movement of data files can be done with most of the
> database online, and only the file actually being moved gets put offline
> prior to its move at the O/S level. The command at (c) then tells the
> control file where the file has been moved to and step (d) then brings the
> thing fully back on line.
>
> Yes, there is still downtime between (b) and (d), but it affects that one
> data file only, not the entire database. If a user wants something out of
> the files which are still online, they'll get it without problems. (You
> *can* run into issues with foreign key constraints, for example... if the
> parent table is in the file you've just offlined, then updating the child
> table is not going to be possible. But apart from those sorts of issue, the
> only thing affected is what you choose to take offline at (b)).
>
> The nice thing about this approach is that you can move one file at a time
> on alternate Thursdays as the mood takes you. There's no requirement to cut
> over the entire database in one sitting. Just move what you what as you get
> to it. The other nice thing is that you (or your DBA, provided he remembers
> how to) can research what segments are inside each file before that file's
> outage, and determine the severity of any disruption that is likely to
> occurr beforehand... and advise people accordingly.
>
> There are just four gotchas. The SYSTEM tablespace can't be taken offline,
> and neither can your only rollback or undo tablespace. For UNDO/Rollback,
> I'd probably create a new tablespace on the new disks, and cut the entire
> database over to using it, rather than moving the existing one. New rollback
> segments can be brought online manually, and although the init.ora needs to
> be edited to bring them online automatically at each instance re-start,
> there's no requirement to bounce the instance at this stage if you've
> already done the job manually. Just edit the init.ora at any time, and leave
> its changes to be picked up next time you do bounce. If this is 9i and
> you're using automatic undo, then the same thing is true for the undo
> tablespace: bring it online manually, but get the init.ora right for the
> next bounce.
>
> Which leaves SYSTEM. That really does have to be moved with the entire
> database down, because you can't take it offline as in (b) above. But SYSTEM
> is seldom more than a couple hundred MB, so the downtime is measured in a
> minute or two at most, not hours.
>
> Thirdly, there's TEMP. That again wouldn't be moved, but created afresh on
> the new disks, and then cut over by means of either (a) altering every
> user's default temporary tablespace if this is 8i (a simple SQL script will
> do the job in seconds); or (b) issuing one SQL command to change the
> database's default temporary tablespace if this is 9i. When everything has
> gone quiet on the old TEMP tablespace, you simply drop it.
>
> And fourthly, there are the control files. These are the only components of
> a database that are actually mentioned in the init.ora, so until a complete
> database re-bounce, there's nothing you can do to move them. But they are
> usually no bigger than a couple of megabytes each, and copying them to the
> new disks would be a matter of seconds. The init.ora can be pre-edited, so
> the downtime involved is minimal. Combine the move of the control files with
> the bounce that's required to move SYSTEM and you won't be adding to the
> downtime significantly.
>
> All in all, it;s possible to move an entire database over to a new set of
> hard disks attached to the same server with a minimum of inconvenience to
> anyone. The 'real' data files can be moved over a period of days, weeks or
> months without the database as a whole ever needing to be shut down. The
> 'administrative' data files and control files can either be created afresh
> and cut-over to, or (for SYSTEM... and the controlfiles) a shutdown will be
> required... but that's a shutdown for minutes only, not hours.
>
> Regards
> HJR
Received on Fri Nov 07 2003 - 18:45:31 CST