Re: Change Datafile Location on Standby Database - Sanity Check

Always, always, always test in a smaller test environment when doing something you haven't tried before. Then you'll know for sure.

Finn

On Dec 7, 2007 11:54 AM, David Barbour <david.barbour1_at_gmail.com> wrote:

> Running Oracle 9.2.0.7 on AIX 5.3. We're moving to a new datafile
> structure on a new SAN. I preparation for the move, I've created a a
> standby database on the new SAN, attached to a different server (still AIX
> 5.3) It's using the new filesystem layout that has 9 locations for the
> datafiles vs. 4 we're currently using.
>
> The standby is running perfectly fine thank you, but I have a bit of a
> problem. Between the time we laid this out and the time we restored the
> production database to the standby and new locations, growth in the
> production system has caused us to come perilously close to running out of
> space in two of the filesystems. I need to move some of the Oracle
> datafiles currently in these two filesystems to one of the new filesystems.
>
>
> Before I screw this up, thought it wouldn't hurt to run it by the list.
>
> Currently standby_file_management is set to 'auto'. All the datafile
> locations for the standby are listed in the init<standby>.ora file using:
>
> db_file_name_convert=(
>
> '/oracle/PR1/sapdata1/autodata/autodata01.dbf','/oracle/PR1/sapdata1/autodata/autodata01.dbf',
>
> '/oracle/PR1/sapdata2/autoindex/autoindexes01.dbf','/oracle/PR1/sapdata2/autoindex/autoindexes01.dbf',
>
> '/oracle/PR1/sapdata1/bkpfd_1/bkpfd.data1','/oracle/PR1/sapdata1/bkpfd_1/bkpfd.data1',
>
> '/oracle/PR1/sapdata2/bkpfd_2/bkpfd.data2','/oracle/PR1/sapdata2/bkpfd_2/bkpfd.data2',
>
> '/oracle/PR1/sapdata3/bkpfd_3/bkpfd.data3','/oracle/PR1/sapdata3/bkpfd_3/bkpfd.data3',
>
> '/oracle/PR1/sapdata4/bkpfd_4/bkpfd.data4','/oracle/PR1/sapdata4/bkpfd_4/bkpfd.data4',
>
> '/oracle/PR1/sapdata1/bkpfi_1/bkpfi.data1','/oracle/PR1/sapdata5/bkpfi_1/bkpfi.data1',
>
> '/oracle/PR1/sapdata2/bkpfi_2/bkpfi.data2','/oracle/PR1/sapdata6/bkpfi_2/bkpfi.data2',
>
> '/oracle/PR1/sapdata3/bkpfi_3/bkpfi.data3','/oracle/PR1/sapdata7/bkpfi_3/bkpfi.data3',
>
> '/oracle/PR1/sapdata4/bkpfi_4/bkpfi.data4','/oracle/PR1/sapdata8/bkpfi_4/bkpfi.data4',.....................etc.
>
> Here's what I plan to do:
>
> On the Primary:
>
> 1. sqlplus> alter system set standby_file_management = 'manual';
>
> On the Standby:
>
> 1. sqlplus> alter system set standby_file_management = 'manual';
> 2. Cancel Managed Recovery
> 3. Shut down the database.
> 4. Move the datafile to the new location.
> 5. Edit the init<standby> .ora to relect the change.
> 7. Restart and mount the database using the new init.ora file.
> 8. Rename the datafile to reflect the change using 'alter database
> rename file ....'
> 9. sqlplus> alter system set standby_file_management = 'auto';
> 10.Start managed recovery.
>
> On the Primary:
>
> 1. sqlplus> alter system set standby_file_management = 'auto';
>
> Comments?
>

--
http://www.freelists.org/webpage/oracle-l