Re: "Mirroring" of DBF files

"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:

1. Where in the init.ora is there a single reference to the location of a data file?
2. Has he ever heard of the 'alter tablespace X offline' command?
3. Or the 'alter database rename file 'X" to 'Y' command?
4. 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 keaves 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 - 12:49:36 CST