1 2 Previous Next 18 Replies Latest reply: May 10, 2012 9:12 AM by 877440 RSS

    Migration advice

    user569151
      We have to migration a huge database.



      If using expdp and scp the dump to the linux box and then impdp, it will take long time.

      Anybody has good suggestions? What is the best way to migrate the data without longer downtime?

      what is the best way to expdp without index and then put index back? and those synonums/views will be all invalid, how to deal with this situation?

      Thank you all in advance.
        • 1. Re: Migration of 1 tb size production database from AIx to linux--need advice.
          User286067
          Have you considered rman for this? take rman backup, move to target host, convert datafiles (if necessary), recover and you are done. Metalink has detailed notes/steps on how to do this.
          • 2. Re: Migration
            user569151
            Cannot use Rman ,I think it is different environment:
            • 3. Re: Migration of 1 tb size production database from AIx to linux--need advice.
              User286067
              user569151 wrote:
              Cannot use Rman ,I think it is different environment: aix to linux. Am I right?
              Why not? I am sure you have reasons and we would like to hear them.
              • 4. Re: Migration of 1 tb size production database from AIx to linux--need advice.
                LKBrwn_DBA
                You should take rjamya's advice, check out the fine:
                Oracle® Database Backup and Recovery User's Guide
                http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmxplat.htm#CHDFHBFI
                :p
                • 5. Re: Migration of 1 tb size production database from AIx to linux--need advice.
                  user569151
                  I think to use transportable tablespace rman back up to migration requires to put tablespace in READ-only mode.

                  That might impact prod downtime.
                  • 6. Re: Migration of 1 tb size production database from AIx to linux--need advice.
                    user569151
                    Can you provide metalink note #, Please?

                    Thank you so much.
                    • 7. Re: Migration of 1 tb size production database from AIx to linux--need advice.
                      User286067
                      You can refer to metalink document 732053.1 to minimize datafile conversion using RMAN. Your organization needs to understand that they need to schedule sufficient time to migrate production. Half a day may be realistic, may be not. You need to try this migration by doing various methods, account for all times, and then run some kind of check to ensure that you didn't miss any data that needed to be brought over. This probably shouldn't be a rush job, test, test and then test again to make sure you get the results you need.

                      Expdp
                      * make sure you use sufficient number of parallel threads and dump files to unload data quickly
                      * you need to account time for (gzip), scp to destination
                      * run impdp in parallel
                      * allow time for indexes to be created and constraints be enabled (since for these, expdp will only carry metadata) this is not required for rman
                      * potentially allow time for stats collection

                      RMAN
                      * take backup in parallel streams
                      * scp to target
                      * do rman convert (you can minimize this by referring to document i mentioned above)
                      * restore and recover

                      Good luck and since your own setup/environment is unique to you, any time estimates someone else gives you are probably unrealistic for your environment, you have to calculate your own.
                      • 8. Re: Migration of 1 tb size production database from AIx to linux--need advice.
                        user569151
                        ok, Great. I will do testing this weekend and keep you guys posted. At the mean time, I will leave this post open.

                        Thanks.
                        • 9. Re: Migration of 1 tb size production database from AIx to linux--need advice.
                          user569151
                          Just come up a concern after talking to the app team.

                          The production not allow any READ ONLY tablespace mode for me to test during the weekend.

                          So what is the option here for me?

                          It seems only is expdp. I wonder expdp with dblink will be faster than just expdp and them scp the file over and then import?

                          Anybody has experience on this topic?
                          • 10. Re: Migration of 1 tb size production database from AIx to linux--need advice.
                            933796
                            your best bet would be to use rman. what you should use is rmans duplicate database command:

                            http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupdb.htm

                            this will copy your database and create it at the target. you can also do this live.

                            once the copying process is finished, you'll need to upgrade the database, since the your target is a different version.

                            but this should be fastest.
                            • 11. Re: Migration of 1 tb size production database from AIx to linux--need advice.
                              user569151
                              This is good idea. I will try this solution. Only one concern is there is firewall blocked from aix prod to linux prod, but I can request to get it removed.

                              I thought rman duplicate is very slow. I tried to do a rman duplicate from active database to create my standby database from another environment, it just time out after a whole day restoring datafiles.
                              • 12. Re: Migration of 1 tb size production database from AIx to linux--need advice.
                                Levi Pereira
                                user569151 wrote:
                                This is good idea. I will try this solution. Only one concern is there is firewall blocked from aix prod to linux prod, but I can request to get it removed.

                                I thought rman duplicate is very slow. I tried to do a rman duplicate from active database to create my standby database from another environment, it just time out after a whole day restoring datafiles.
                                Hi,
                                Duplicate (clone/standby) database from LINUX to AIX will not work.

                                You cannot restore Database from different Platform without convert the Database.
                                Check Matrix of Cross-Platform Database to see if your platform need or can be converted.
                                For each architecture and endian Oracle build their own binaries. (i.e Processor Power has is different binaries from Processor Intel)
                                For some architectures, Oracle succeeded in creating compatibility between some different architectures, the Table on MOS note 413484.1 describe it.


                                You can use Cross-Platform
                                See it: (HOWTO: Oracle Cross-Platform Migration with Minimal Downtime)
                                http://www.pythian.com/news/3653/howto-oracle-cross-platform-migration-with-minimal-downtime

                                http://levipereira.wordpress.com/2011/01/23/how-convert-full-database-10g-linux-x86-64bit-to-aix-64bit-different-endian-format/

                                Or you can use Golden Gate to migrate with zero (or near zero) downtime :
                                http://www.oracle.com/technetwork/middleware/goldengate/overview/ggzerodowntimedatabaseupgrades-174928.pdf

                                Regards,
                                Levi Pereira
                                • 13. Re: Migration of 1 tb size production database from AIx to linux--need advice.
                                  user569151
                                  THank you Levi so much for clearing the thoughts here.

                                  GoldenGate requires license?

                                  Last night I tried to kick off the export of the biggest table which is 550g in size, of which 500g is logsegment. I kicked off the job at 10pm last night and the log shows it still work on the metadata. Is it too long? I just want to test different scenarios first.

                                  Here is my par file:

                                  tables=aradmin.t506 DIRECTORY=data_pump_dir dumpfile=exp_t506_%U.dmp log file=exp_t506.log parallel=6 filesize=9000m compression=all

                                  Export log shows here:
                                  Estimate in progress using BLOCKS method...
                                  Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
                                  Total estimation using BLOCKS method: 530.0 GB
                                  Processing object type TABLE_EXPORT/TABLE/TABLE
                                  Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
                                  Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
                                  Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
                                  Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
                                  ~

                                  I saw 6 dmp files there , 5 of them already 9gb in size.

                                  How this happening? I am testing this at our other test environment which is just refreshed from prod weeks ago. So it is very close to the prod.

                                  I have another question here. We have a DR environment which is a exact copy of prod, it has more horse power on server. Can I do a expdp on dr db?

                                  Thanks again for all the inputs.
                                  • 14. Re: Migration of 1 tb size production database from AIx to linux--need advice.
                                    Levi Pereira
                                    Hi,
                                    GoldenGate requires license?
                                    Yes... check price named per user.. it's enough to do this job.
                                    http://www.oracle.com/us/corporate/pricing/technology-price-list-070617.pdf
                                    tables=aradmin.t506 DIRECTORY=data_pump_dir dumpfile=exp_t506_%U.dmp log file=exp_t506.log parallel=6 filesize=9000m compression=all
                                    I saw 6 dmp files there , 5 of them already 9gb in size.
                                    How this happening? I am testing this at our other test environment which is just refreshed from prod weeks ago. So it is very close to the prod.
                                    For each parallel process expdp use one dumpfile. So as you have 6 parallel process you will se 6 dumpfiles. You put limit of filesize of 9G, for that reason you see 9G of dumpfiles.

                                    I have another question here. We have a DR environment which is a exact copy of prod, it has more horse power on server. Can I do a expdp on dr db?
                                    Yes. you can do that without problem. Just put standby database in snapshot mode.
                                    See step by step here: {message:id=10155862}

                                    Regards,
                                    Levi Pereira
                                    1 2 Previous Next