12 Replies Latest reply: Jan 18, 2013 6:51 AM by CKPT RSS

    Standby Database Datafile Move

    mcarbon
      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
          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
            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
              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
                that link answers my questions! Thanks
                • 5. Re: Standby Database Datafile Move
                  CKPT
                  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
                    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
                      .
                      • 8. Re: Standby Database Datafile Move
                        Shivananda Rao
                        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
                          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
                            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
                              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
                                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 .