This discussion is archived
12 Replies Latest reply: Jan 18, 2013 4:51 AM by CKPT RSS

Standby Database Datafile Move

mcarbon Newbie
Currently Being Moderated
Hi All

Oracle v10g (10.2.0.4)
Windows 2003

I need to move some datafiles onto a larger volume on our primary database. I have no issue with this task, except for some uncertainty on what to do with the Standby database?

Our Standby database (on the other side of the atlantic) has the exact same drive/file layout as the primary. I have a larger volume ready for the move on the Standby.

We have standby_file_management set to auto.

I assume that the data dictionary/control file information (datafile locations) will be updated via the logs being shipped? I assume I would need to manually move the files on the standby database?

Has anyone practiced this recently? could you give me a little guidance.

Much appreciated.
  • 1. Re: Standby Database Datafile Move
    mseberg Guru
    Currently Being Moderated
    Hello;

    I have moved standby database to larger volumes by setting the primary to defer and then shutting down the standby database.

    Then I scp the cold database files to the larger volume and since I use Unix/Linux I rename the volume.

    If you do it right Oracle never knows about the move and Data Guard just catches up.

    Does this make sense?

    Can supply dynamic SQL for the move too.
    set heading off
    set feedback off
    set pagesize 100
    set linesize 400
    
    select 'scp '||a.name ||' server_name:' || a.name as newname from v$datafile a;
    
    
    
    select 'scp '||a.name ||' server_name:' || a.name as newname from v$controlfile a;
    
    
    select 'scp '||a.member ||' server_name:' || a.member as newname from v$logfile a;
    You can also :

    Use rsync to increase a mount point size.

    http://www.visi.com/~mseberg/linux/rsync_oracle.html





    Best Regards

    mseberg
  • 2. Re: Standby Database Datafile Move
    CKPT Guru
    Currently Being Moderated
    user3655049 wrote:
    Hi All

    Oracle v10g (10.2.0.4)
    Windows 2003

    I need to move some datafiles onto a larger volume on our primary database. I have no issue with this task, except for some uncertainty on what to do with the Standby database?

    Our Standby database (on the other side of the atlantic) has the exact same drive/file layout as the primary. I have a larger volume ready for the move on the Standby.

    We have standby_file_management set to auto.

    I assume that the data dictionary/control file information (datafile locations) will be updated via the logs being shipped? I assume I would need to manually move the files on the standby database?

    Has anyone practiced this recently? could you give me a little guidance.

    Much appreciated.
    You no need to modify datafile locations on standby. structure can be different from primary and standby. Even primary can be ASM and standby can be non-ASM.
    So no need to worry. But after changing the files location, you have to update DB_FILE_NAME_CONVERT parameter on databases.

    Some more information to you, check this http://docs.oracle.com/cd/E11882_01/server.112/e17022/manage_ps.htm#i1034172
    >
    When you rename one or more datafiles in the primary database, the change is not propagated to the standby database. Therefore, if you want to rename the same datafiles on the standby database, you must manually make the equivalent modifications on the standby database because the modifications are not performed automatically, even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.
    >

    Edited by: CKPT on Jan 17, 2013 9:02 PM
  • 3. Re: Standby Database Datafile Move
    mcarbon Newbie
    Currently Being Moderated
    I do want to move the file on the standby also, as the original volumes on the primary matched the standby, in which case the space is becoming restricted on the standby too.
  • 4. Re: Standby Database Datafile Move
    mcarbon Newbie
    Currently Being Moderated
    that link answers my questions! Thanks
  • 5. Re: Standby Database Datafile Move
    CKPT Guru
    Currently Being Moderated
    user3655049 wrote:
    I do want to move the file on the standby also, as the original volumes on the primary matched the standby,
    Then you no need any changes on standby, if mount points is going to match.
    in which case the space is becoming restricted on the standby too.
    I didn't get your question exactly, as per my understanding.... If the space is problem on your standby, then you can move the files to other mount points where you have enough space (or) you have to add more volumes to the drive.
  • 6. Re: Standby Database Datafile Move
    mcarbon Newbie
    Currently Being Moderated
    I thought the link covered it, but unfortunately not. Renaming the file is not an issue, but I do wonder if this process maybe similar.

    I will try to explain again:

    We have 3 datafiles belonging to a data tablespace existing on one drive on our primary database/server. The drive has about 2gb left, and as I prefer to keep a tidy system, I dont want to create a datafile belonging to this data tablespace on a seperate drive. So I have a new bigger drive presented to the server of which I wish to move all the datafiles relating to this tablespace. I have no issues with this move on the primary, but I am unsure how to mirror the change on the standby database/server. The drive on the standby server is also getting tight. I have a new drive on the standby box labelled the same as with the Primary.

    What steps do i need to take after doing the following on the Primary?:

    1. Shutdown immediate
    2. Move the physical files either with windows explorer or a host move command
    3. startup mount
    4. alter database rename file 'G:\ORADATA\DATA.DBF' to 'I:\ORADATA\DATA.DBF';
    (other moves would be required)
    5. alter database open

    Sorry for any confusion.

    Edited by: user3655049 on 17-Jan-2013 08:49
  • 7. Re: Standby Database Datafile Move
    mcarbon Newbie
    Currently Being Moderated
    .
  • 8. Re: Standby Database Datafile Move
    Shivananda Rao Guru
    Currently Being Moderated
    Hello,

    The steps are right. Make sure that the standby is not under recovery while moving/renaming the files.
    You can refer this Move Datafiles on standby server without moving Primary Oracle 11g r2


    Regards,
    Shivananda
  • 9. Re: Standby Database Datafile Move
    mseberg Guru
    Currently Being Moderated
    Got it.

    Sorry for delay.

    Would follow the Oracle MOS document :

    How to Rename a Datafile in Primary Database Within in Physical Dataguard Configuration [ID 733796.1]

    Best Regards

    mseberg
  • 10. Re: Standby Database Datafile Move
    mcarbon Newbie
    Currently Being Moderated
    Thanks for your advice.

    I will be moving the files first on the Primary. before doing so, should I be halting the log shipping? As when i run the 'Alter database rename', wont that instruction go across in the logs? Will the standby then expect the local (to standby) files to have been moved?
  • 11. Re: Standby Database Datafile Move
    Shivananda Rao Guru
    Currently Being Moderated
    I will be moving the files first on the Primary. before doing so, should I be halting the log shipping? As when i run the 'Alter database rename', wont that instruction go across in the logs? Will the standby then expect the local (to standby) files to have been moved?
    Not required to stop the log shipping on primary. Make sure that you set the STANDBY_FILE_MANAGEMENT to MANUAL on standby database before doing the change.


    Regards,
    Shivananda
  • 12. Re: Standby Database Datafile Move
    CKPT Guru
    Currently Being Moderated
    mcarbon wrote:
    Thanks for your advice.

    I will be moving the files first on the Primary. before doing so, should I be halting the log shipping? As when i run the 'Alter database rename', wont that instruction go across in the logs? Will the standby then expect the local (to standby) files to have been moved?
    I think you haven't read what i posted in previous reply. Once again posting for you.

    >
    When you rename one or more datafiles in the primary database, the change is not propagated to the standby database. Therefore, if you want to rename the same datafiles on the standby database, you must manually make the equivalent modifications on the standby database because the modifications are not performed automatically, even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO .

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points