6 Replies Latest reply: Feb 21, 2013 2:10 PM by 977635 RSS

    relocating standby database

    977635
      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
          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
            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
              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
                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
                  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
                    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.