7 Replies Latest reply: Mar 6, 2014 9:12 AM by J.A. RSS

    Consolidation of multiple database diskgroups into only four diskgroups..

    AbhijitDey

      Hello,

       

      Database server hosts five SIDs. Each SID has its own set of four diskgroups - DATA/RECO/ARCH/REDO. The RECO size is sum total of DATA+ARCH+REDO diskgroup sizes. This is the layout for each of the five SIDs. The databases being migrated are of different sizes and accordingly diskgroup sizing is done after factoring in average 30% growth for 1year for each database. And then partitioning the DG into around 4 sets of LUNs(ASM disks). This gives rise to too many disparate LUN sizes.

       

      To integrate and consolidate all these five databases finally into only four diskgroups for that node, we need to migrate the respective database ASM disks online (of each DATA/RECO/ARCH/REDO DGs) probably into the largest Diskgroups of the largest database on that node. I understand this would create an interim need for additional storage (only for the migration). Once migration is done we can drop the older disks and release the LUNs back into storage. This will reduce the SAN/Storage need significantly over long term and allow better storage(ASM+LUN) management.

       

      DB Version: Oracle 11.2.0.4 Enterprise Edition

      OS Version: RedHat Enterprise Linux 6.3

       

      What would be best approach to this for the DBA..Would ASMCA be enough or should we use RMAN/scripting? Inputs with detailed steps and necessary scripts would be highly appreciated..

       

      Thanks in advance..

       

      Abhijit

        • 1. Re: Consolidation of multiple database diskgroups into only four diskgroups..
          J.A.

          are you talking about production environment ?

          how much time do you have to do the migration ?

           

          If I Understood well, this is your configuration:

          Server 1:

          DB1 -> 4 DB1 Diskgroups

          DB2 -> 4 DB2 Diskgroups

          DB3 -> 4 DB3 Diskgroups

          DB4 -> 4 DB4 Diskgroups

          DB5 -> 4 DB5 Diskgroups

           

          And you want to accomplish this:

          Server 1:

          DB1 -> 4 Global Diskgroups

          DB2 -> 4 Global Diskgroups

          DB3 -> 4 Global Diskgroups

          DB4 -> 4 Global Diskgroups

          DB5 -> 4 Global Diskgroups

           

          I think a strategy could be:

          1.      Create the New 4 Global DiskGroups à DATA/RECO/ARCH/REDO
          2.      Create a Rman backup as Image Copies  of your tablespace on the New Global Data Disk Group (Global DATA)
          3.      Switch the tablespace/Datafiles from the original location (old Disk Group) to the New Disk Group (Global Data)

          rman>sql "alter tablespace star_schema offline immediate";

          rman>switch tablespace star_schema to copy;

          rman>recover tablespace star_schema;

          rman>sql "alter tablespace star_schema online";

                 Note: For undo and System tablespace you have to shutdown and mount the database

             4.       Change the FRA (Fast Recovery Area) to the new Global DiskGroups

             5.       Move the controlFile to the new Global Disk Group

             6.       Do this procedure for each database on the Server

           

          I hoppe this help you

           

          Regards

           

          1. J.A.
          • 2. Re: Consolidation of multiple database diskgroups into only four diskgroups..
            hitgon

            Hi AbhijitDey

             

            Are you using ASMLib ?

            Are you using single instance database or RAC ?

             

            Thanks...

            • 3. Re: Consolidation of multiple database diskgroups into only four diskgroups..
              AbhijitDey

              Hi hitgon,

               

              nope I am not using ASMLib in RHEL 6.4. But ASMLib is used on OEL6 for the RAC databases in our environment.

               

              in this case, these are all Standalones and not RAC.

               

              Thanks again..


              • 4. Re: Consolidation of multiple database diskgroups into only four diskgroups..
                AbhijitDey

                Hi J.A.

                 

                Thanks for the reply.. Yes, I plan to do the consolidation this way.. sorry I didn't give the future layout earlier:

                All databases DB1+DB2+DB3+DB4+DB5  ===> consolidated into 4 Diskgroups (DATA/RECO/ARCH/REDO).

                 

                All these databases are Standalones and on a single HW database server. I was thinking of fitting in all the other databases into the "largest" DB3 database (if size fitment permits, I will not need to create new Diskgroups and LUNs). So the target diskgroups are:

                +DATA_DB3

                +RECO_DB3

                +ARCH_DB3

                +REDO_DB3

                 

                In that case, I will just need to do these steps (starting with the first DB1 database, one at a time ) -

                 

                •     Create a RMAN backup as Image Copies of your tablespace on to the New Data Disk Group (+DATA_DB3)
                •     Switch the tablespace/Datafiles from the original location (old Disk Group) to the New Disk Group (+DATA_DB3) <== these script/commands look like tablespace-level offline, copy and online commands. Is it possible to do this without taking the tablespaces offline? or, better still, database file-level movement without taking the database offline? Since we cannot afford to take the databases offline

                                         rman>sql "alter tablespace star_schema offline immediate";

                                         rman>switch tablespace star_schema to copy;

                                         rman>recover tablespace star_schema;

                                         rman>sql "alter tablespace star_schema online";

                                 Note: For undo and System tablespace you have to shutdown and mount the database

                •    Set the FRA (Fast Recovery Area) parameter settings DB1 to the new DiskGroups  <== so just redirecting the FRA parameter settings will be enough? Do i need to rerun another RMAN backup post migration?
                •    Move the DB1 controlFile to the new Global Disk Group                                         <== 3 copies of Control file = 1st in DATA, 2nd in ARCH, 3rd in REDO=> correct?

                 

                Repeat this procedure for rest databases on the Server.

                 

                Regards,

                Abhijit

                 

                • 5. Re: Consolidation of multiple database diskgroups into only four diskgroups..
                  J.A.

                  hi AbhijitDey

                   

                  <== these script/commands look like tablespace-level offline, copy and online commands. Is it possible to do this without taking the tablespaces offline? or, better still, database file-level movement without taking the database offline? Since we cannot afford to take the databases offline

                  > Yes, to do this procedure you have to put your tablespace Offline and for the System and Undo you have to shutdown the database. But its the faster way with RMAN because you dont need to Restore, instead you only do the switch and recover. (switch to images copies for fast recovery). 

                  > The best way to do this is using RMAN its not a good idea trying to do this at database file level.

                   

                  <== so just redirecting the FRA parameter settings will be enough? Do i need to rerun another RMAN backup post migration?

                  > Before you start any procedure, take a Rman backup (with controlFile and Spfile) and put this backup outside of the FRA.

                  > To move the FRA, Control File and Online Redologs, check this:

                  Change Oracle Flash Recovery Area location | Beyond Oracle

                   

                   

                  You have to check the size of your database, size of your backup, size of your (+DATA_DB3), time for the migration, etc, to find the best way to do the migration.  You say that you can afford take the databases offline.

                   

                  Maybe another option for you if to clone each database from ACTIVE Database.

                   

                  Example:

                  DB1 -> 4 DB1 Diskgroups

                  clone to

                  DB11 -> 4 DB3 Diskgroups  (+DATA_DB3)

                   

                  Advantages:

                  >You make a copy of your current DB1 on the new Diskgroup and new physical structure

                  >You can do this while the DB1 is open and working

                  >You can test the procedure and test the time it takes to do the cloning and polish the procedure

                  >You can do this without backup

                  >You can change the physical location of your new Database on the same server

                   

                  Disadvantages:

                  >You have to set a new SID on the new cloned database

                  >You have to change the applications to connect to this New Database

                  >Probably you have to stop the application while you are doing the cloning, so any transaction writes to the old Database and you lose it because its not cloned on the new >database(It depends on how many transaction you database receive)

                   

                   

                  I hope this help you, let me know if you have a better idea to accomplish your tasks

                   

                  J.A.

                  • 6. Re: Consolidation of multiple database diskgroups into only four diskgroups..
                    AbhijitDey

                    Thanks J.A!!

                     

                    Infact we cannot afford to take the DB offline at any time. But this method (1)Tablespaces offline through RMAN, 2)redirecting FRA and 3)Recover DB from RMAN Image copies) you suggested looks like still fastest given the challenges with the minimum downtime (which could possibly be agreed upon to be run at night time of ther server/node locations). The big advantage in this method the SID remains unchanged and any other method will impact the application teams negatively as they will need to re-configure to connect to the changed DB names.

                     

                    Downtime windows will increase as the database sizes grow - that is another challenge. Then we have to push them over to the weekend windows.

                     

                    What do you think?

                     

                    Thanks again..

                     

                    Abhijit

                    • 7. Re: Consolidation of multiple database diskgroups into only four diskgroups..
                      J.A.

                      Yes I think its a good strategy (switch to images copies for fast recovery) and also yes you have to ask a maintenance window.

                       

                      I think it could be better do the procedure one or two database for one maintenance window, if you do the four database at the same time you will have a lot of variables to worry about.

                       

                      Let me know how is everything

                       

                      Regards