Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 9733 invoked from network); 7 Dec 2007 20:11:12 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 7 Dec 2007 20:11:12 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 140277D5AE5; Fri, 7 Dec 2007 21:11:13 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 25827-07; Fri, 7 Dec 2007 21:11:12 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7F8C27D5C4A; Fri, 7 Dec 2007 21:11:12 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 07 Dec 2007 20:24:16 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5F40C7D51A1 for ; Fri, 7 Dec 2007 20:24:16 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 18370-05-2 for ; Fri, 7 Dec 2007 20:24:16 -0500 (EST) Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.179]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 081377D5422 for ; Fri, 7 Dec 2007 20:24:16 -0500 (EST) Received: by py-out-1112.google.com with SMTP id u77so2035812pyb for ; Fri, 07 Dec 2007 17:24:15 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=cVD1VHa1q2DooWcnGwjDCWNLqUDmBDE0TXJURm8XSSE=; b=qLldXa26/QJ6PkW5dqUVHRpuBnVjQTqewqAKOXVGJAVo5BSzfOPZeE7vAKn2AL7+vfDkzMOasL4KmA/wsrUkoIrOOsHOhI4kjSwp++1rNEQRWIk47tIacitIgcy4rgpjnebrqN0bgSSBFZk3Y4BbdRSpoX0yMtvz42rkRze/U8Y= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=DwfLlpnKLmRH4itnGxFIKxjRo4npPtbETR/VXm5mL6qO6pADeaFQENFOJrml+OX+U9jSA8T/YIgBORGWUkFGkQgWy/Mk1uy+Y4xAZNv1FSFvvLQocU806g3lm/XySCCq2rMMEsj/nI0glQB6kmRex1Ch5iTMuGds6nCvJqiD8H8= Received: by 10.35.79.3 with SMTP id g3mr4164976pyl.1197077041870; Fri, 07 Dec 2007 17:24:01 -0800 (PST) Received: by 10.35.119.5 with HTTP; Fri, 7 Dec 2007 17:24:01 -0800 (PST) Message-ID: <74f79c6b0712071724t2877c614p4ded913b96ea5ccd@mail.gmail.com> Date: Fri, 7 Dec 2007 20:24:01 -0500 From: "Finn Jorgensen" To: david.barbour1@gmail.com Subject: Re: Change Datafile Location on Standby Database - Sanity Check Cc: oracle-l In-Reply-To: <69eafc3f0712070854n1925cb77y67086d24ff14301f@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_18178_17518839.1197077041865" References: <69eafc3f0712070854n1925cb77y67086d24ff14301f@mail.gmail.com> X-archive-position: 3798 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: finn.oracledba@gmail.com Precedence: normal Reply-to: finn.oracledba@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_Part_18178_17518839.1197077041865 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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.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 .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? > ------=_Part_18178_17518839.1197077041865 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
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@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?

------=_Part_18178_17518839.1197077041865-- -- http://www.freelists.org/webpage/oracle-l