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@xxxxxxxxx>
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
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.  

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?




Other related posts: