This discussion is archived
6 Replies Latest reply: Feb 21, 2013 12:10 PM by 977635 RSS

relocating standby database

977635 Newbie
Currently Being Moderated
Hello.

I am running 11.2 w/DG on Solaris 10, using DG Broker

My question is whether it is as easy to relocate datafiles, tempfiles, redo logs, controlfiles, etc. for our standby database as it would be for any other database?

So, for example, our current standby database resides on Direct Attached Storage and we want to relocate it to new mounts points on our SAN.
Can I do this for our standby database simply by:

changing the init parameter STANDBY_FILE_MANAGEMENT to MANUAL (both primary and stby)
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL

Stop the managed standby apply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

shutting down the stby database
"copy" the files to new locations

edit pfile, bring stby database up to nomount mode using pfile, create new spfile, (to reflect new locations of controlfiles)
shut it back down, then bring it up to mount mode using spfile by default
Finally, rename (relocate) all the files
ALTER DATABASE RENAME FILE '<old file>' TO '<new file>';

Then, when I'm done, ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO (for both primary and standby).
(maybe even bounce the database again and bring it up in mount mode)

One other question is do I actually need to do anything on the primary side at all?
  • 1. Re: relocating standby database
    CKPT Guru
    Currently Being Moderated
    974632 wrote:
    Hello.

    I am running 11.2 w/DG on Solaris 10, using DG Broker

    My question is whether it is as easy to relocate datafiles, tempfiles, redo logs, controlfiles, etc. for our standby database as it would be for any other database?

    So, for example, our current standby database resides on Direct Attached Storage and we want to relocate it to new mounts points on our SAN.
    Can I do this for our standby database simply by:

    changing the init parameter STANDBY_FILE_MANAGEMENT to MANUAL (both primary and stby)
    ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL

    Stop the managed standby apply
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

    shutting down the stby database
    "copy" the files to new locations

    edit pfile, bring stby database up to nomount mode using pfile, create new spfile, (to reflect new locations of controlfiles)
    shut it back down, then bring it up to mount mode using spfile by default
    Finally, rename (relocate) all the files
    ALTER DATABASE RENAME FILE '<old file>' TO '<new file>';

    Then, when I'm done, ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO (for both primary and standby).
    (maybe even bounce the database again and bring it up in mount mode)

    One other question is do I actually need to do anything on the primary side at all?
    Yes you can do that.
    1) cancel MRP
    2) Put standby file management to manual.
    3) For control files , After shutdown You can simply copy the control files to the new location and perform the same changes in control_files
    4) For Data Files , Now after copying to the new location use the command you mentioned "ALTER DATABASE RENAME FILE '<old file>' TO '<new file>';"
    5) Change value of "DB_FILE_NAME_CONVERT" to point new locations.
    6) you can drop and recreate the redo log files to new location.
    7) Change value of "LOG_FILE_NAME_CONVERT" to point new locations.
    8) Put standby file management to AUTO.
    9) Start MRP

    These steps should be fine.
  • 2. Re: relocating standby database
    977635 Newbie
    Currently Being Moderated
    So, question regarding "db_file_name_convert".

    Currently our prod and standby database does not have any value set for db_file_name_convert, even though one file in standby is already different than primary. (but it works and is updated even though it is in a different location).

    So, how would I use db_file_name_convert?
    Would I have to do it for every single file that I'm relocating different than production?
    I assume, a separate entry for each file. Correct?

    See below:
    PRIMARY:
    SQL> sho parameter convert
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_file_name_convert                 string
    log_file_name_convert                string
    
    /opt/oracle/oradata3/REMEDY/remedy_data
    STANDBY
    SQL> sho parameter convert
    
    NAME                                 TYPE                             VALUE
    ------------------------------------ -------------------------------- ------------------------------
    db_file_name_convert                 string
    log_file_name_convert                string
    
    /opt/oracle/oradata4/REMEDY/remedy_data
  • 3. Re: relocating standby database
    CKPT Guru
    Currently Being Moderated
    So, question regarding "db_file_name_convert".
    Currently our prod and standby database does not have any value set for db_file_name_convert, even though one file in standby is already different than primary. (but it works and is updated even though it is in a different location).
    See, If primary control file record location as "/u01/prim/datafiles" and primary database dont know anything where datafiles exists on standby database. Lets suppose "/u02/standby/datafiles" . How primary will know and how it can create data files on standby?
    If you using ASM/OMF then of course you no need of such parameters. What is the standby_file_management value in standby? If you set auto then it create an unanmed file in $ORACLE_HOME/dbs and MRP will be killed. Further no recovery will be in progress.

    Either that data file has manually relocated.
    So, how would I use db_file_name_convert?
    Would I have to do it for every single file that I'm relocating different than production?
    I assume, a separate entry for each file. Correct?
    You no need to mention for each datafile, It corresponds to sub directory.
    Example: Primary data files in only one location "/u01/prim/datafiles" and standby contains in two locations as "/u01/stanby/datafiles", "/u02/standby/datafiles" then you have to configure as below.

    DB_FILE_NAME_CONVERT='/u01/prim/datafiles','/u01/stanby/datafiles','/u01/prim/datafiles','/u02/standby/datafiles';

    See carefully, I have provided one primary then standby again primary and then standby. In such way you have to configure.
    HTH.
  • 4. Re: relocating standby database
    977635 Newbie
    Currently Being Moderated
    Okay, that makes sense I guess.
    yes, I assume the file was relocated manually (it was before my time).

    So, in our case we do not use ASM (thankfully).
    Anyway, we have datafiles located:
    /opt/oracle/oradata1/REMEDY/*
    /opt/oracle/oradata2/REMEDY/*
    /opt/oracle/oradata3/REMEDY/*

    But, I will relocate them to:
    /u01/oradata/REMEDY/*
    /u02/oradata/REMEDY/*
    /u03/oradata/REMEDY/*
    /u04/oradata/REMEDY/*

    As you see, we will go from 3 mounts points to 4 mount points.
    And some files on oradata1 might move to /u03, /u04, or vice-versa, or some other combination.

    So, how would I do something like that?

    Also, I will totally relocate redo logs (but I guess I can do that manually anyway).


    I should also add that once I move standby, then I will do the same for primary (and all files will be in same locations after I do primary).
    They just want me to do standby first, as a sort of test before I do production.

    Edited by: 974632 on Feb 21, 2013 11:54 AM
  • 5. Re: relocating standby database
    CKPT Guru
    Currently Being Moderated
    974632 wrote:
    Okay, that makes sense I guess.
    yes, I assume the file was relocated manually (it was before my time).

    So, in our case we do not use ASM (thankfully).
    Anyway, we have datafiles located:
    /opt/oracle/oradata1/REMEDY/*
    /opt/oracle/oradata2/REMEDY/*
    /opt/oracle/oradata3/REMEDY/*

    But, I will relocate them to:
    /u01/oradata/REMEDY/*
    /u02/oradata/REMEDY/*
    /u03/oradata/REMEDY/*
    /u04/oradata/REMEDY/*

    As you see, we will go from 3 mounts points to 4 mount points.
    And some files on oradata1 might move to /u03, /u04, or vice-versa, or some other combination.
    Yes you can configure even if you have more than 3 mount points as specified above. You can take a look how to configure DB_FILE_NAME_CONVERT from below link

    http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams048.htm
    So, how would I do something like that?

    Also, I will totally relocate redo logs (but I guess I can do that manually anyway).
    Yes you have to do even for redo logs also.
    I should also add that once I move standby, then I will do the same for primary (and all files will be in same locations after I do primary).
    They just want me to do standby first, as a sort of test before I do production.
    yes, add it even in primary. it useful in case of swithcover to avoid future problems. If primary and standby same location there is no necessary to mention this parameter.
  • 6. Re: relocating standby database
    977635 Newbie
    Currently Being Moderated
    Okay, thank you for your suggestions and help.
    Ultimately, our standby will be exactly the same as prod, but there will be an interim period where they will be different.

Legend

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