Re: Change Datafile Location on Standby Database - Sanity Check

David,

It's been a while since I've worked on a 9.2 standby, but I believe you're doing some extra work here by maintaining all the datafiles in the db_file_name_convert parameter. You should only need to include files that have changed location there. For me, I'd view it risky to maintain a list of all datafiles for fear that I'd boo boo one of them somehow or miss a comma or quote somewhere and screw up the whole thing.

Your plan sounds reasonable to me--I expect it will work fine. However, I'd consider just doing the convert for those files that are in a different path on the standby. If the path on the standby matches the production system, I wouldn't mention them in the parameter.

Dan

• Original Message ---- From: David Barbour <david.barbour1_at_gmail.com> To: oracle-l <oracle-l_at_freelists.org> Sent: Friday, December 7, 2007 10:54:53 AM Subject: Change Datafile Location on Standby Database - Sanity Check

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.
6. Restart and mount the database using the new init.ora file.
7. Rename the datafile to reflect the change using 'alter database rename file ....'
8. 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