1 2 Previous Next 15 Replies Latest reply: Sep 11, 2013 2:19 AM by Richard Harrison . RSS

    Best Way to Transfer Data

    ChaosAD77

      We are looking to move the data from a current 11G database (db1) on a RHEL4 environment to our new database (db2) server on RHEL6 with a SAN environment that will have the most current 11GR2 install. The new install will also be ASM, where the old one wasn't. They will be on the same network segment so transfer between the 2 should be pretty good.

       

      Right now db1 takes about 18 hrs to do a full export. That in turn then takes 14 hrs to import into db2. The size of DB1 is 600GB and after the import it is down to 200Gb due to retrieving so much wasted space.

       

      We try and keep a fairly high level of uptime, so even a small bit is frowned on. Is there a better way to get the data up-to-date on db2 so we can switch that to prod with a limited amount of down time? I'm guessing there is a better way then a 1 time full export, but not sure as that is the way I've usually done it.

       

      We are using Oracle 11GR2 on the new DB and it will be Standard Ed 1. The current DB is base 11G install on Standard Ed 1.

       

      The new SAN environment will be a raid 1+0 config with about 800GB raid 5 local storage as well.

       

      Thanks for any help! Please let me know if I can provide any helpful information as well.

        • 2. Re: Best Way to Transfer Data
          ChaosAD77

          Thanks, will do.

           

          I should mention we are using DBVisit for maintaining a secondary server. So not sure if that can be used somehow to help with this. The file structure will be different and of course going from non asm to asm, wasn't sure how that would work with a product like that.

          • 3. Re: Best Way to Transfer Data
            ChaosAD77

            It will be going to a whole new file structure and ASM. What I did with the import is already have the tablespace (same name) created with their related datafiles. The import then took all the data and entered it correctly. So with the archivelogs, as long as the tablespaces, tables, etc are there I'll be able to use them to bring DB2 update-to-date?

             

            Will be using same schemas and tablespace names, just the datafiles, file locations, and using ASM that will be different.

            • 4. Re: Best Way to Transfer Data
              Paul M.

              What I did with the import is already have the tablespace (same name) created with their related datafiles. The import then took all the data and entered it correctly. So with the archivelogs, as long as the tablespaces, tables, etc are there I'll be able to use them to bring DB2 update-to-date?

              No. Export/Import (exp or datapump, no difference) is a static picture, taken at a specific time, archivelogs can only be applied (recovery) after a RMAN restore.

               

              Also, I don't know what could be your downtime window, but using export/import the downtime will be (at minimum) the sum of export time + import time.

               

              Using the procedure in my post, the downtime won't be more than one hour (also depending on hardware performances).

               

              we are using DBVisit for maintaining a secondary server.

              I don't know that product, so I can't say much about it...

              • 5. Re: Best Way to Transfer Data
                ChaosAD77

                That makes sense, thank for the clarification.

                 

                Do you build the new database up through tablespace then do a rman restore or do you do a rman restore and somehow rename the datafiles to match the new file structure? Never did a restore for a db where the file structure changed, but the data / tablespaces remained the same.

                 

                Also, one of the current tablespaces contains many 32GB datafiles. When goign to ASM we were going to use 1 Big Tablespace for that. How would I convert the datafiles from old db to the new one considering that.I know I could use the following to convert:

                 

                db_file_name_convert=(/u01/oradata/scr9/,/u03/oradata/dg9a/)

                 

                Just not sure how that might work combining many datafiles into 1.

                 

                Thanks

                 

                * Edited

                Forgot to ask about other file restructure questions like redologs, fra, etc since they will change going into the ASM enviroment

                 

                Message was edited by: ChaosAD77

                • 6. Re: Best Way to Transfer Data
                  Paul M.
                  Do you build the new database up through tablespace then do a rman restore or do you do a rman restore and somehow rename the datafiles to match the new file structure?

                  The latter. See Performing RMAN Recovery: Advanced Scenarios

                   

                  Also, one of the current tablespaces contains many 32GB datafiles. When goign to ASM we were going to use 1 Big Tablespace for that.

                  RMAN can't do that, but... what does "many" mean ? You said your database is 600G (not a very big one). In one of my databases (1.7 TB) a tablespace has 36 datafiles (increasing), with no problems at all.

                   

                  Forgot to ask about other file restructure questions like redologs, fra, etc

                  About redologs see the link above. For others, like FRA, you can change them within init file, before starting the instance.

                  • 7. Re: Best Way to Transfer Data
                    ChaosAD77
                    RMAN can't do that, but... what does "many" mean ? You said your database is 600G (not a very big one). In one of my databases (1.7 TB) a tablespace has 36 datafiles (increasing), with no problems at all.


                    What I meant was we have about 10 files that 1 tablespace uses. We were going to use 1 Big Tablespace and use 1 datafile instead. Guess we can't do that in this scenario?

                    • 8. Re: Best Way to Transfer Data
                      Paul M.
                      we have about 10 files that 1 tablespace uses.

                      And are they so many ? Of course you know that a smallfile tablespace may have up to 1022 datafiles.

                      We were going to use 1 Big Tablespace and use 1 datafile instead. Guess we can't do that in this scenario?

                      Exactly, you can't, at least not automatically.

                      • 9. Re: Best Way to Transfer Data
                        ChaosAD77
                        Exactly, you can't, at least not automatically.

                        Thanks, was just wondering. Trying to get this all scripted out and kinks worked out since this is the dry run of doing this procedure.

                        • 10. Re: Best Way to Transfer Data
                          ChaosAD77

                          Random:

                           

                          Can I have a barebones DB installed initially? The one you can do through DBCA w/o data files? That way the redo logs are there, memory is setup properly, etc.

                           

                          With the ASM renaming, does ASM need a file ext or can I do the following

                           

                          SET NEWNAME FOR DATAFILE '/u01/app/oracle/oradata/forms1/forms_tbl03.dbf' TO '+DATA/FORMS1/DATAFILE/formstbl03';

                           

                          Thanks and sorry for all the q's. Just running into little things here and there and trying to figure it out so I have good grasp.

                          • 11. Re: Best Way to Transfer Data
                            Paul M.

                            Can I have a barebones DB installed initially? The one you can do through DBCA w/o data files?

                            Yes, you can, but you don't need redologs, since you'll have to open the database with resetlogs option, which recreates them.

                             

                            About memory parameters you can set them within init file (as I said before) before starting the instance.

                             

                            With the ASM renaming, does ASM need a file ext or can I do the following

                             

                            SET NEWNAME FOR DATAFILE '/u01/app/oracle/oradata/forms1/forms_tbl03.dbf' TO '+DATA/FORMS1/DATAFILE/formstbl03';

                            Yes, you can, physical files names are not relevant, as long as they are unique.

                            • 12. Re: Best Way to Transfer Data
                              ChaosAD77

                              Have gotten to the renaming part

                               

                              run{

                              ....

                              SET NEWNAME FOR DATAFILE 37TO 'forms_tbl18';

                              SET UNTIL SCN 123456;

                              RESTORE DATABASE;

                              SWITCH DATAFILE ALL;

                              RECOVER DATABASE;

                              }

                              I have my backup set (thats on external drive) cataloged, but after I run this I get the message below. It looks like it has marked some of the backups as expired (it's 2 month old backup). Would that affect it?

                               

                              executing command: SET NEWNAME

                               

                               

                              executing command: SET until clause

                               

                               

                              Starting restore at 08-AUG-13

                              allocated channel: ORA_DISK_1

                              channel ORA_DISK_1: SID=3 device type=DISK

                               

                               

                              RMAN-00571: ===========================================================

                              RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

                              RMAN-00571: ===========================================================

                              RMAN-03002: failure of restore command at 08/08/2013 14:53:45

                              RMAN-06026: some targets not found - aborting restore

                              RMAN-06023: no backup or copy of datafile 28 found to restore

                              ...

                               

                              * EDIT

                               

                              It appears to find datafile if I do a simple list:

                               

                              RMAN> list backup of datafile 1;

                               

                               

                              using target database control file instead of recovery catalog

                               

                               

                              List of Backup Sets

                              ===================

                               

                               

                               

                               

                              BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                              ------- ---- -- ---------- ----------- ------------ ---------------

                              23412   Incr 0  5.13G      DISK        00:21:49     02-JUN-13

                                      BP Key: 23412   Status: EXPIRED  Compressed: YES  Tag: FULL_BACKUP_WEEKLY_%

                                      Piece Name: /u55/full_backup_weekly_27ob6gd4_1_1.bkp

                                List of Datafiles in backup set 23412

                                File LV Type Ckp SCN    Ckp Time  Name

                                ---- -- ---- ---------- --------- ----

                                1    0  Incr 2443326316 02-JUN-13 /u01/app/oracle/oradata/forms1/system01.dbf

                               

                               

                              BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                              ------- ---- -- ---------- ----------- ------------ ---------------

                              23493   Incr 1  137.13M    DISK        00:04:43     03-JUN-13

                                      BP Key: 23493   Status: EXPIRED  Compressed: YES  Tag: INC_BACKUP_DAILY_%U

                                      Piece Name: /u55/inc_backup_daily_4oob9pge_1_1.bkp

                                List of Datafiles in backup set 23493

                                File LV Type Ckp SCN    Ckp Time  Name

                                ---- -- ---- ---------- --------- ----

                                1    1  Incr 2451633805 03-JUN-13 /u01/app/oracle/oradata/forms1/system01.dbf

                               

                               

                              BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                              ------- ---- -- ---------- ----------- ------------ ---------------

                              23569   Incr 1  163.36M    DISK        00:05:49     04-JUN-13

                                      BP Key: 23569   Status: EXPIRED  Compressed: YES  Tag: INC_BACKUP_DAILY_%U

                                      Piece Name: /u55/inc_backup_daily_74obce06_1_1.bkp

                                List of Datafiles in backup set 23569

                                File LV Type Ckp SCN    Ckp Time  Name

                                ---- -- ---- ---------- --------- ----

                                1    1  Incr 2459250035 04-JUN-13 /u01/app/oracle/oradata/forms1/system01.dbf

                               

                               

                              BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                              ------- ---- -- ---------- ----------- ------------ ---------------

                              23641   Incr 1  181.43M    DISK        00:04:17     05-JUN-13

                                      BP Key: 23641   Status: EXPIRED  Compressed: YES  Tag: INC_BACKUP_DAILY_%U

                                      Piece Name: /u55/inc_backup_daily_9cobf2cf_1_1.bkp

                                List of Datafiles in backup set 23641

                                File LV Type Ckp SCN    Ckp Time  Name

                                ---- -- ---- ---------- --------- ----

                                1    1  Incr 2466884508 05-JUN-13 /u01/app/oracle/oradata/forms1/system01.dbf

                               

                               

                              BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                              ------- ---- -- ---------- ----------- ------------ ---------------

                              23713   Incr 1  196.66M    DISK        00:04:18     06-JUN-13

                                      BP Key: 23713   Status: EXPIRED  Compressed: YES  Tag: INC_BACKUP_DAILY_%U

                                      Piece Name: /u55/inc_backup_daily_bkobhmkm_1_1.bkp

                                List of Datafiles in backup set 23713

                                File LV Type Ckp SCN    Ckp Time  Name

                                ---- -- ---- ---------- --------- ----

                                1    1  Incr 2474284580 06-JUN-13 /u01/app/oracle/oradata/forms1/system01.dbf

                               

                               

                              BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                              ------- ---- -- ---------- ----------- ------------ ---------------

                              23783   Incr 1  210.89M    DISK        00:08:57     07-JUN-13

                                      BP Key: 23783   Status: EXPIRED  Compressed: YES  Tag: INC_BACKUP_DAILY_%U

                                      Piece Name: /u55/inc_backup_daily_dqobkbln_1_1.bkp

                                List of Datafiles in backup set 23783

                                File LV Type Ckp SCN    Ckp Time  Name

                                ---- -- ---- ---------- --------- ----

                                1    1  Incr 2481516560 07-JUN-13 /u01/app/oracle/oradata/forms1/system01.dbf

                               

                               

                              BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                              ------- ---- -- ---------- ----------- ------------ ---------------

                              23843   Incr 1  219.43M    DISK        00:03:44     08-JUN-13

                                      BP Key: 23843   Status: EXPIRED  Compressed: YES  Tag: INC_BACKUP_DAILY_%U

                                      Piece Name: /u55/inc_backup_daily_fmobmv65_1_1.bkp

                                List of Datafiles in backup set 23843

                                File LV Type Ckp SCN    Ckp Time  Name

                                ---- -- ---- ---------- --------- ----

                                1    1  Incr 2486646137 08-JUN-13 /u01/app/oracle/oradata/forms1/system01.dbf

                               

                               

                              BS Key  Type LV Size

                              ------- ---- -- ----------

                              23887   Incr 0  5.22G

                                List of Datafiles in backup set 23887

                                File LV Type Ckp SCN    Ckp Time  Name

                                ---- -- ---- ---------- --------- ----

                                1    0  Incr 2488444422 09-JUN-13 /u01/app/oracle/oradata/forms1/system01.dbf

                               

                               

                                Backup Set Copy #1 of backup set 23887

                                Device Type Elapsed Time Completion Time Compressed Tag

                                ----------- ------------ --------------- ---------- ---

                                DISK        00:18:36     09-JUN-13       YES        FULL_BACKUP_WEEKLY_%U

                               

                               

                                  List of Backup Pieces for backup set 23887 Copy #1

                                  BP Key  Pc# Status      Piece Name

                                  ------- --- ----------- ----------

                                  23887   1   EXPIRED     /u55/full_backup_weekly_h2obou9k_1_1.bkp

                               

                               

                                Backup Set Copy #2 of backup set 23887

                                Device Type Elapsed Time Completion Time Compressed Tag

                                ----------- ------------ --------------- ---------- ---

                                DISK        00:18:36     08-AUG-13       YES        FULL_BACKUP_WEEKLY_%U

                               

                               

                                  List of Backup Pieces for backup set 23887 Copy #2

                                  BP Key  Pc# Status      Piece Name

                                  ------- --- ----------- ----------

                                  23940   1   AVAILABLE   /media/external_hdd/fullbackup/full_backup_weekly_h2obou9k_1_1.bkp

                               

                              .....

                               

                              Though when I run the following, it appears to not be there. I'm a little confused

                               

                              RMAN> LIST COPY OF DATAFILE 1;

                               

                               

                              specification does not match any datafile copy in the repository

                               

                              Message was edited by: ChaosAD77

                              • 13. Re: Best Way to Transfer Data
                                ChaosAD77

                                Still about the same place. Starting to think based on the logs from from RMAN that there is a file missing from the backup and thus a few datafiles not in there.

                                • 14. Re: Best Way to Transfer Data
                                  ChaosAD77

                                  - Turns out it was a bad backup missing some reference to data files

                                   

                                  I have 1 hopefully last q that hopefully you can give insight on

                                   

                                  Is it possible to do a full export of a 10g DB and then import that into 11GR2 using a set SCN and then turn around and put the archive logs from the source on there and catch it up to a certain point in time, thus reducing downtime of the source. The intent would be to catch it up to a reasonable time, shutdown the source, copy over remaining archive logs and catch it up to date then switch to using the target db.

                                   

                                  Is that feasible?

                                  1 2 Previous Next