Loading ...
Sorry, an error occurred while loading the content.
 

Re: Datamover import is very slow...

Expand Messages
  • leavens_summit
    I have had this experience recently as well. Here is what I did to resolve the issue. 1) Determine the cause of the waits via OEM or run a AWR. In my case we
    Message 1 of 18 , Jul 19, 2008
      I have had this experience recently as well. Here is what I did to
      resolve the issue.

      1) Determine the cause of the waits via OEM or run a AWR. In my case
      we had redo logs that were too small rolling over every 1-2 mins which
      led to excessive check pointing. I increased our redo logs so that
      the roll overs were 15-30 mins, in our case that was 3 x 1G logs.

      2) At the O/S level there was much paging due to improper tuning of
      file buffer cache and too many Oracle instance running. Since the
      server was a database server only we reduce the amount of memory
      allowed for file buffers, locked the SGA into RAM and ran the import
      when we could bring down the some of the database instances.

      3) First import ran from a windows box against an AIX server, second
      import we installed a PS_HOME and Tuxedo and ran the data mover on the
      database server.

      4) We disabled all triggers, dropped indexes, altered the tables to
      NOLOGGING, PARALLEL as this was a full import.

      These measures reduced the load time by at least 50%.

      You have to weigh this with the rebuild of the indexes. Of course
      afterwards we altered the tables back to LOGGING and NOPARALLEL and
      reduce the logs to somethting more resonable.

      John Leavens
      ApexIT

      --- In psftdba@yahoogroups.com, "Vinayan.K.C" <vinayankc@...> wrote:
      >
      > Hi Experts,
      > We are doing tablestructure import enabling UNICODE in to Oracle
      10.2.0.3 database with an SGA of 8GB. Datamover import is so slow as
      it's importing only 10 objects per minute. Any one experienced similar
      problem. Any resolutions? Thanks in advance. We are able to import the
      280 GB data within 9 hrs using Oracle imp utility.
      >
      > following datamover commands used in the datamover script.
      > SET INPUT D:\test.dat;
      > SET NO VIEW;
      > SET NO TRACE;
      > SET UNICODE ON;
      > SET STATISTICS OFF;
      > SET NO INDEX;
      > SET NO DATA;
      > IMPORT *;
      > Thanks
      > Vinayan
      >
    • william_gioioso@vanguard.com
      I am also looking for advice in speeding up our db refreshes. Our DB size is about 160 GB and it is taking 20+ hours to import using conventional exp/imp.
      Message 2 of 18 , Jul 28, 2008

        I am also looking for advice in speeding up our db refreshes.  Our DB size is about 160 GB and it is taking 20+ hours to import using conventional exp/imp.  Our DBA tried 10g Data Pump and saw no difference.  We tried a couple of other things (statistics off on import, and recordlength set to a large number).  We also recently deployed RMAN but DBA has not investigated using it for refreshing.

        Any advice appreciated.

        Thanks.
        Bill
        CONFIDENTIALITY STATEMENT. The information contained in this e-mail message, including attachments, is the confidential information of, and/or is the property of, Vanguard. The information is intended for use solely by the individual or entity named in the message. If you are not an intended recipient or you received this in error, then any review, printing, copying, or distribution of any such information is prohibited, and please notify the sender immediately by reply e-mail and then delete this e-mail from your system.
      • the dragon
        160 GB database can be moved and refreshed in about 2-3 hours. Use a cold backup and move the dbf files if you can afford a 10 minute outage, or a hot backup
        Message 3 of 18 , Jul 28, 2008
          160 GB database can be moved and refreshed in about 2-3 hours. Use a cold backup and move the dbf files if you can afford a 10 minute outage, or a hot backup and apply the log files. Very simple. Nothing personal, but only a fool uses export/import or datapump. And, RMAN can be used also.

          peace,
          clark 'the dragon' willis

          PSA: Salary <> Slavery. If you earn a salary, your employer is renting your services for 40 hours a week, not purchasing your soul. Your time is the only real finite asset that you have, and once used it can never be recovered, so don't waste it by giving it away.

          I work to live; I don't live to work.

          "Time is the coin of your life. It is the only coin you have, and only you can determine how it will be spent. Be careful lest you let other people spend it for you." -- Carl Sandburg (1878 - 1967)

          It is impossible to defeat an ignorant man in argument. -- William G. McAdoo

          Religion is regarded by the common people as true, by the wise as false, and by the rulers as useful. -- Seneca

          "I distrust those people who know so well what God wants them to do because I notice it always coincides with their own desires." - Susan B. Anthony



          ________________________________


          I am also looking for advice in speeding up our db refreshes. Our DB size is about 160 GB and it is taking 20+ hours to import using conventional exp/imp. Our DBA tried 10g Data Pump and saw no difference. We tried a couple of other things (statistics off on import, and recordlength set to a large number). We also recently deployed RMAN but DBA has not investigated using it for refreshing.

          Any advice appreciated.

          Thanks.
          Bill
          ________________________________

          _________________________________________________________________
          Stay in touch when you're away with Windows Live Messenger.
          http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_messenger2_072008
        • Shaun
          We ve got a 110Gb instance and refresh in just over the hour (65mins) with either Hot backup or RMAN refreshes, just like the dragon says :) but RMAN will be
          Message 4 of 18 , Jul 28, 2008
            We've got a 110Gb instance and refresh in just over the hour (65mins) with either Hot backup or RMAN refreshes, just like "the dragon" says :)

            but RMAN will be more scalable, hence why we're switching, i.e. won't slow down as much as the DB grows as it only ships the changes.

            regards
            Shaun

            ----- Original Message ----
            From: "william_gioioso@..." <william_gioioso@...>
            To: psftdba@yahoogroups.com
            Sent: Monday, 28 July, 2008 1:04:50 PM
            Subject: PeopleSoft DBA Forum Re: Datamover import is very slow...


            I am also looking for advice in speeding up our db refreshes.  Our DB size is about 160 GB and it is taking 20+ hours to import using conventional exp/imp.  Our DBA tried 10g Data Pump and saw no difference.  We tried a couple of other things (statistics off on import, and recordlength set to a large number).  We also recently deployed RMAN but DBA has not investigated using it for refreshing.

            Any advice appreciated.

            Thanks.
            Bill

          • Sha Parekh
            use rman duplicate command. I refresh a 400 gig financials db in under 3 hrs. It is easy to use and requires no production downtime... ... From: Shaun
            Message 5 of 18 , Jul 28, 2008
              use rman duplicate command. I refresh a 400 gig financials db in under 3 hrs. It is easy to use and requires no production downtime...

              --- On Mon, 7/28/08, Shaun <shaundl@...> wrote:
              From: Shaun <shaundl@...>
              Subject: Re: PeopleSoft DBA Forum Re: Datamover import is very slow...
              To: psftdba@yahoogroups.com
              Date: Monday, July 28, 2008, 8:31 AM

              We've got a 110Gb instance and refresh in just over the hour (65mins) with either Hot backup or RMAN refreshes, just like "the dragon" says :)

              but RMAN will be more scalable, hence why we're switching, i.e. won't slow down as much as the DB grows as it only ships the changes.

              regards
              Shaun

              ----- Original Message ----
              From: "william_gioioso@ vanguard. com" <william_gioioso@ vanguard. com>
              To: psftdba@yahoogroups .com
              Sent: Monday, 28 July, 2008 1:04:50 PM
              Subject: PeopleSoft DBA Forum Re: Datamover import is very slow...


              I am also looking for advice in speeding up our db refreshes.  Our DB size is about 160 GB and it is taking 20+ hours to import using conventional exp/imp.  Our DBA tried 10g Data Pump and saw no difference.  We tried a couple of other things (statistics off on import, and recordlength set to a large number).  We also recently deployed RMAN but DBA has not investigated using it for refreshing.

              Any advice appreciated.

              Thanks.
              Bill


            • the dragon
              FSCM 9.0 I need to create a user in DMO for people to use. I am starting with VP1 clone and removing all tools and security access. I have removed all the
              Message 6 of 18 , Jul 29, 2008
                FSCM 9.0
                 
                I need to create a user in DMO for people to use.  I am starting with VP1 clone and removing all tools and security access.  I have removed all the roles that I think have tools, but when I check tools access for the user I still see Data Mover and App Designer.  I am down to only 53 roles (below if you care).
                 
                I've looked at the security queries, and found all the PLs that have DM and AP designer, and then taken those PLs and seen what roles they are in, and none of the Roles are present in this new user.
                 
                Any hints? 
                 

                1AM_SS_ACCOUNTING_CLERK
                2AM_SS_MANAGER
                3App Developer
                4BAM Administrator
                5Budget Approver
                6CC_ADMINISTRATOR
                7CC_USER_PO
                8CC_USER_REQ
                9CLAIMS_MGR
                10CLERK
                11CONSUMER
                12Call Center Agent
                13Call Center Specialist
                14Cash Manager
                15Catalog Administrator
                16Catalog Partner
                17DEALING MANAGER
                18Demand Planner
                19EMPLOYEE
                20EOLT_ADMIN
                21EOPP_USER
                22Ent Utilities Administrator
                23Event Buyer
                24Event Seller
                25Forecaster
                26IT Asset Manager
                27Inventory Policy Planner
                28PAPP_USER
                29PBM User
                30PeopleSoft User
                31Plan Approver
                32ProcessSchedulerAdmin
                33Project Owner
                34RE_ADMIN
                35RE_SUPER
                36ReportSuperUser
                37SETTLEMENT_MGR
                38SP_ADMINISTRATOR
                39SP_APPROVER
                40SP_COORDINATOR
                41SP_EXECUTIVE
                42SP_EXPENSE_APPROVER
                43SP_INVOICE_MANAGER
                44SP_INV_APPROVER
                45SP_PLOG_APPROVER
                46SP_PROVIDER
                47SP_PROVIDER_CONTACT
                48SP_TIME_APPROVER
                49SP_WO_APPROVER
                50Supplier Contract Interested
                51VMI Manager
                52XMLP Power User
                53XMLP Report Developer

                 
                peace,
                clark 'the dragon' willis


                PSA: Salary <> Slavery. If you earn a salary, your employer is renting your services for 40 hours a week, not purchasing your soul. Your time is the only real finite asset that you have, and once used it can never be recovered, so don't waste it by giving it away.

                I work to live; I don't live to work.

                "Time is the coin of your life. It is the only coin you have, and only you can determine how it will be spent. Be careful lest you let other people spend it for you." -- Carl Sandburg (1878 - 1967)

                It is impossible to defeat an ignorant man in argument. -- William G. McAdoo

                Religion is regarded by the common people as true, by the wise as false, and by the rulers as useful. -- Seneca

                "I distrust those people who know so well what God wants them to do because I notice it always coincides with their own desires." - Susan B. Anthony





                Keep your kids safer online with Windows Live Family Safety. Help protect your kids.
              • william_gioioso@vanguard.com
                Guys, Thanks for the advice. The one thing we liked about exp/imp is that we get some defragmentation on import. This speed was tolerable when we kicked it
                Message 7 of 18 , Jul 30, 2008

                  Guys,

                  Thanks for the advice.  The one thing we liked about exp/imp is that we get some defragmentation on import. This speed was tolerable when we kicked it off at the end of the day and it would be ready next morning.  We've seen a lot of growth (not archiving data ...yet) so the import time has been increasing .  We will be looking at RMAN with our DBA.

                  --Bill
                  CONFIDENTIALITY STATEMENT. The information contained in this e-mail message, including attachments, is the confidential information of, and/or is the property of, Vanguard. The information is intended for use solely by the individual or entity named in the message. If you are not an intended recipient or you received this in error, then any review, printing, copying, or distribution of any such information is prohibited, and please notify the sender immediately by reply e-mail and then delete this e-mail from your system.
                • Ankur Shah
                  Can you share the RMAN scripts / docs that allows us to use for DB refresh Thanks Ankur ... From: Sha Parekh To: psftdba@yahoogroups.com
                  Message 8 of 18 , Aug 25, 2008
                    Can you share the RMAN scripts / docs that allows us to use for DB refresh
                     
                    Thanks
                     
                    Ankur

                    ----- Original Message ----
                    From: Sha Parekh <shaparekh@...>
                    To: psftdba@yahoogroups.com
                    Sent: Monday, July 28, 2008 10:53:43 AM
                    Subject: Re: PeopleSoft DBA Forum Re: Datamover import is very slow...

                    use rman duplicate command. I refresh a 400 gig financials db in under 3 hrs. It is easy to use and requires no production downtime...

                    --- On Mon, 7/28/08, Shaun <shaundl@btopenworld .com> wrote:
                    From: Shaun <shaundl@btopenworld .com>
                    Subject: Re: PeopleSoft DBA Forum Re: Datamover import is very slow...
                    To: psftdba@yahoogroups .com
                    Date: Monday, July 28, 2008, 8:31 AM

                    We've got a 110Gb instance and refresh in just over the hour (65mins) with either Hot backup or RMAN refreshes, just like "the dragon" says :)

                    but RMAN will be more scalable, hence why we're switching, i.e. won't slow down as much as the DB grows as it only ships the changes.

                    regards
                    Shaun

                    ----- Original Message ----
                    From: "william_gioioso@ vanguard. com" <william_gioioso@ vanguard. com>
                    To: psftdba@yahoogroups .com
                    Sent: Monday, 28 July, 2008 1:04:50 PM
                    Subject: PeopleSoft DBA Forum Re: Datamover import is very slow...


                    I am also looking for advice in speeding up our db refreshes.  Our DB size is about 160 GB and it is taking 20+ hours to import using conventional exp/imp.  Our DBA tried 10g Data Pump and saw no difference.  We tried a couple of other things (statistics off on import, and recordlength set to a large number).  We also recently deployed RMAN but DBA has not investigated using it for refreshing.

                    Any advice appreciated.

                    Thanks.
                    Bill



                  • Sha Parekh
                    are you setup to run rman?  If so, are you doing backups to disk or tape? ... From: Ankur Shah Subject: Re: PeopleSoft DBA Forum Re:
                    Message 9 of 18 , Aug 25, 2008
                      are you setup to run rman?  If so, are you doing backups to disk or tape?

                      --- On Mon, 8/25/08, Ankur Shah <ankursk71@...> wrote:
                      From: Ankur Shah <ankursk71@...>
                      Subject: Re: PeopleSoft DBA Forum Re: Datamover import is very slow...
                      To: psftdba@yahoogroups.com
                      Date: Monday, August 25, 2008, 3:20 PM

                      Can you share the RMAN scripts / docs that allows us to use for DB refresh
                       
                      Thanks
                       
                      Ankur

                      ----- Original Message ----
                      From: Sha Parekh <shaparekh@yahoo. com>
                      To: psftdba@yahoogroups .com
                      Sent: Monday, July 28, 2008 10:53:43 AM
                      Subject: Re: PeopleSoft DBA Forum Re: Datamover import is very slow...

                      use rman duplicate command. I refresh a 400 gig financials db in under 3 hrs. It is easy to use and requires no production downtime...

                      --- On Mon, 7/28/08, Shaun <shaundl@btopenworld .com> wrote:
                      From: Shaun <shaundl@btopenworld .com>
                      Subject: Re: PeopleSoft DBA Forum Re: Datamover import is very slow...
                      To: psftdba@yahoogroups .com
                      Date: Monday, July 28, 2008, 8:31 AM

                      We've got a 110Gb instance and refresh in just over the hour (65mins) with either Hot backup or RMAN refreshes, just like "the dragon" says :)

                      but RMAN will be more scalable, hence why we're switching, i.e. won't slow down as much as the DB grows as it only ships the changes.

                      regards
                      Shaun

                      ----- Original Message ----
                      From: "william_gioioso@ vanguard. com" <william_gioioso@ vanguard. com>
                      To: psftdba@yahoogroups .com
                      Sent: Monday, 28 July, 2008 1:04:50 PM
                      Subject: PeopleSoft DBA Forum Re: Datamover import is very slow...


                      I am also looking for advice in speeding up our db refreshes.  Our DB size is about 160 GB and it is taking 20+ hours to import using conventional exp/imp.  Our DBA tried 10g Data Pump and saw no difference.  We tried a couple of other things (statistics off on import, and recordlength set to a large number).  We also recently deployed RMAN but DBA has not investigated using it for refreshing.

                      Any advice appreciated.

                      Thanks.
                      Bill



                    • shajivps
                      Fyi, As suggested earlier, RMAN is the easiest way to refresh a db when it gets bigger. I refresh a 1.5TB psoft fin database under 6 hours. As for the
                      Message 10 of 18 , Aug 25, 2008
                        Fyi,

                        As suggested earlier, RMAN is the easiest way to refresh a db when it
                        gets bigger. I refresh a 1.5TB psoft fin database under 6 hours.

                        As for the scripts, it is more specific with the storage library you
                        are using. If you are not the DBA doing this work, dont even try it.
                        If you are the dba, I can give a template and the steps involved.
                        What is the storage library used at your site ?

                        Regards,

                        Shaji.

                        --- In psftdba@yahoogroups.com, Ankur Shah <ankursk71@...> wrote:
                        >
                        > Can you share the RMAN scripts / docs that allows us to use for DB
                        refresh
                        > Thanks
                        > Ankur
                        >
                        >
                        >
                        > ----- Original Message ----
                        > From: Sha Parekh <shaparekh@...>
                        > To: psftdba@yahoogroups.com
                        > Sent: Monday, July 28, 2008 10:53:43 AM
                        > Subject: Re: PeopleSoft DBA Forum Re: Datamover import is very slow...
                        >
                        >
                        > use rman duplicate command. I refresh a 400 gig financials db in
                        under 3 hrs. It is easy to use and requires no production downtime...
                        >
                        > --- On Mon, 7/28/08, Shaun <shaundl@btopenworld .com> wrote:
                        >
                        > From: Shaun <shaundl@btopenworld .com>
                        > Subject: Re: PeopleSoft DBA Forum Re: Datamover import is very slow...
                        > To: psftdba@yahoogroups .com
                        > Date: Monday, July 28, 2008, 8:31 AM
                        >
                        >
                        > We've got a 110Gb instance and refresh in just over the hour
                        (65mins) with either Hot backup or RMAN refreshes, just like "the
                        dragon" says :)
                        >
                        > but RMAN will be more scalable, hence why we're switching, i.e.
                        won't slow down as much as the DB grows as it only ships the changes.
                        >
                        > regards
                        > Shaun
                        >
                        >
                        > ----- Original Message ----
                        > From: "william_gioioso@ vanguard. com" <william_gioioso@ vanguard. com>
                        > To: psftdba@yahoogroups .com
                        > Sent: Monday, 28 July, 2008 1:04:50 PM
                        > Subject: PeopleSoft DBA Forum Re: Datamover import is very slow....
                        >
                        >
                        >
                        > I am also looking for advice in speeding up our db refreshes. Our
                        DB size is about 160 GB and it is taking 20+ hours to import using
                        conventional exp/imp. Our DBA tried 10g Data Pump and saw no
                        difference. We tried a couple of other things (statistics off on
                        import, and recordlength set to a large number). We also recently
                        deployed RMAN but DBA has not investigated using it for refreshing.
                        >
                        > Any advice appreciated.
                        >
                        > Thanks.
                        > Bill
                        >
                      • shajivps
                        Steps to restore/refresh UAT env using Rman. Partial scripts provided. Pre-Restore steps ================= 1. If you are using Filesystem for database, make
                        Message 11 of 18 , Aug 26, 2008
                          Steps to restore/refresh UAT env using Rman. Partial scripts provided.

                          Pre-Restore steps
                          =================

                          1. If you are using Filesystem for database, make sure you have
                          enough space on the UAT server compared to your prod server where the
                          data is sourced from.
                          2. If yo are using RAW volumes for your database, make sure you have
                          equal number of raw lvols on the UAT server as your prod server AND
                          sizes match.
                          3. What is important (whether it is RAW or FS) is that the total
                          space available on UAT should be equal to what is in production.
                          In case of FS, enough FS space should be available. In case of
                          RAW, equal number of lvols has to be available and the sizes of lvols
                          should match even if names doesnt match.
                          4. Get the peoplesoft application team to backup all the security
                          tables using data mover if they want to preserve the psoft internal
                          security.
                          5. Take a dump of all userid's in UAT environment since there will be
                          more app users in UAT (oracle side) with more privileges than in
                          production. You will need this script to recreate users/regrant
                          permissions
                          6. Reverse engineer the script for any database links since the db
                          links have to be recreated to point to other UAT environments. Db
                          links after restoring prod to UAT will point to other prod environments.
                          7. Any other precautions that needs to be taken specific to your
                          installation.


                          RMAN restore steps
                          ==================

                          1. Restore controlfile. If you are using Rman catalog, make a copy
                          of the catalog and use that instead of using production catalog.
                          --You dont need catalog to restore controlfile from autobackup.
                          --allocate channel has to be changed to what is specific to your site.
                          We use Ibm Tivoli-TDP and the syntax given
                          --Below is specific to our site.

                          connect target /
                          set dbid=<your dbid> ;
                          startup nomount;
                          run {
                          allocate channel tape01 type 'sbt_tape' parms
                          'BLKSIZE=2097152,ENV=(TDPO_OPTFILE=/users/oracle/local/prod/rman-tdp/config/fop1/tdpo_LB.opt)';

                          restore controlfile from autobackup maxseq=3 ;

                          release channel tape01;
                          }


                          2. Restore the database.

                          --Alter database mount; before next step.

                          connect target /

                          run {
                          allocate channel tape01 type 'sbt_tape' parms
                          'BLKSIZE=2097152,ENV=(TDPO_OPTFILE=/users/oracle/local/prod/rman-tdp/config/fop
                          1/tdpo_LB.opt)';

                          -- Line below is required if your source filename and target filename
                          differs.
                          -- In case you are restoreing to a different FS or a different raw volume.
                          -- 1 line per datafile required for every file that has a different
                          name on UAT w.r.t production.

                          set newname for datafile 1 to '<new file name>';

                          restore database until sequence=<log sequence no to which recovery
                          needed> thread=1 force;

                          Switch datafile 1;

                          --Above line required for Each file where the name of the file is
                          different on UAT w.r.t PROD

                          release channel tape01;
                          }


                          3. Recover the database.

                          -- In case your target filenames are different than your source
                          filenames, DO NOT CONNECT to Catalog during recovery.
                          -- If you are used to Rman restores, you already know this.


                          connect target /

                          run {
                          allocate channel tape01 type 'sbt_tape' parms
                          'BLKSIZE=2097152,ENV=(TDPO_OPTFILE=/users/oracle/local/prod/rman-tdp/config/fop1/tdpo_LB.opt)';
                          recover database until sequence=<recover upto sequence> thread=1 ;
                          release channel tape01;
                          }


                          4. If recovery completes sucessfully,

                          a. Alter database open resetlogs;
                          b. Recreate all your tempfiles.



                          POST Recovery operation:
                          =======================


                          1. Replace the contents of PS.PSDBOWNER so that this environment
                          points to the correct database. Otherwise this will connect to prod.
                          2. Change the password of SYSADM and any other oracle internal
                          accounts to match UAT password convention
                          3. Create missing userid's and grants (using script reverse
                          engineered in the pre-recovery step)
                          4. Recreate db links to point those to other UAT environments.
                          5. Any other cleanup required specific to your site.


                          If everything is all set, this will go pretty smooth. Restoring a
                          1.2TB Finance psoft database takes around 5-6 hours for us
                          over gigabit LAN. We backup using Fibre SAN.
                        • shajivps
                          Missed a step in this. The instance name on UAT is named same as prod during restore/recovery. We use a copy of the prod initfile for the UAT db during
                          Message 12 of 18 , Aug 26, 2008
                            Missed a step in this.

                            The instance name on UAT is named same as prod during
                            restore/recovery. We use a copy of the prod initfile for the UAT db
                            during restore/recovery. After restore is done, the db is renamed to
                            match the uat name in the initfile and controlfile(recreate controlfile).

                            Regards,

                            Shaji.

                            --- In psftdba@yahoogroups.com, "shajivps" <shajivps@...> wrote:
                            >
                            > Steps to restore/refresh UAT env using Rman. Partial scripts
                            provided.
                            >
                            > Pre-Restore steps
                            > =================
                            >
                            > 1. If you are using Filesystem for database, make sure you have
                            > enough space on the UAT server compared to your prod server where the
                            > data is sourced from.
                            > 2. If yo are using RAW volumes for your database, make sure you have
                            > equal number of raw lvols on the UAT server as your prod server AND
                            > sizes match.
                            > 3. What is important (whether it is RAW or FS) is that the total
                            > space available on UAT should be equal to what is in production.
                            > In case of FS, enough FS space should be available. In case of
                            > RAW, equal number of lvols has to be available and the sizes of lvols
                            > should match even if names doesnt match.
                            > 4. Get the peoplesoft application team to backup all the security
                            > tables using data mover if they want to preserve the psoft internal
                            > security.
                            > 5. Take a dump of all userid's in UAT environment since there will be
                            > more app users in UAT (oracle side) with more privileges than in
                            > production. You will need this script to recreate users/regrant
                            > permissions
                            > 6. Reverse engineer the script for any database links since the db
                            > links have to be recreated to point to other UAT environments. Db
                            > links after restoring prod to UAT will point to other prod
                            environments.
                            > 7. Any other precautions that needs to be taken specific to your
                            > installation.
                            >
                            >
                            > RMAN restore steps
                            > ==================
                            >
                            > 1. Restore controlfile. If you are using Rman catalog, make a copy
                            > of the catalog and use that instead of using production catalog.
                            > --You dont need catalog to restore controlfile from autobackup.
                            > --allocate channel has to be changed to what is specific to your site.
                            > We use Ibm Tivoli-TDP and the syntax given
                            > --Below is specific to our site.
                            >
                            > connect target /
                            > set dbid=<your dbid> ;
                            > startup nomount;
                            > run {
                            > allocate channel tape01 type 'sbt_tape' parms
                            >
                            'BLKSIZE=2097152,ENV=(TDPO_OPTFILE=/users/oracle/local/prod/rman-tdp/config/fop1/tdpo_LB.opt)';
                            >
                            > restore controlfile from autobackup maxseq=3 ;
                            >
                            > release channel tape01;
                            > }
                            >
                            >
                            > 2. Restore the database.
                            >
                            > --Alter database mount; before next step.
                            >
                            > connect target /
                            >
                            > run {
                            > allocate channel tape01 type 'sbt_tape' parms
                            >
                            'BLKSIZE=2097152,ENV=(TDPO_OPTFILE=/users/oracle/local/prod/rman-tdp/config/fop
                            > 1/tdpo_LB.opt)';
                            >
                            > -- Line below is required if your source filename and target filename
                            > differs.
                            > -- In case you are restoreing to a different FS or a different raw
                            volume.
                            > -- 1 line per datafile required for every file that has a different
                            > name on UAT w.r.t production.
                            >
                            > set newname for datafile 1 to '<new file name>';
                            >
                            > restore database until sequence=<log sequence no to which recovery
                            > needed> thread=1 force;
                            >
                            > Switch datafile 1;
                            >
                            > --Above line required for Each file where the name of the file is
                            > different on UAT w.r.t PROD
                            >
                            > release channel tape01;
                            > }
                            >
                            >
                            > 3. Recover the database.
                            >
                            > -- In case your target filenames are different than your source
                            > filenames, DO NOT CONNECT to Catalog during recovery.
                            > -- If you are used to Rman restores, you already know this.
                            >
                            >
                            > connect target /
                            >
                            > run {
                            > allocate channel tape01 type 'sbt_tape' parms
                            >
                            'BLKSIZE=2097152,ENV=(TDPO_OPTFILE=/users/oracle/local/prod/rman-tdp/config/fop1/tdpo_LB.opt)';
                            > recover database until sequence=<recover upto sequence> thread=1 ;
                            > release channel tape01;
                            > }
                            >
                            >
                            > 4. If recovery completes sucessfully,
                            >
                            > a. Alter database open resetlogs;
                            > b. Recreate all your tempfiles.
                            >
                            >
                            >
                            > POST Recovery operation:
                            > =======================
                            >
                            >
                            > 1. Replace the contents of PS.PSDBOWNER so that this environment
                            > points to the correct database. Otherwise this will connect to prod.
                            > 2. Change the password of SYSADM and any other oracle internal
                            > accounts to match UAT password convention
                            > 3. Create missing userid's and grants (using script reverse
                            > engineered in the pre-recovery step)
                            > 4. Recreate db links to point those to other UAT environments.
                            > 5. Any other cleanup required specific to your site.
                            >
                            >
                            > If everything is all set, this will go pretty smooth. Restoring a
                            > 1.2TB Finance psoft database takes around 5-6 hours for us
                            > over gigabit LAN. We backup using Fibre SAN.
                            >
                          • Ankur Shah
                            Yes , we are on rman. Backup to disk currently. Thanks Ankur ... From: Sha Parekh To: psftdba@yahoogroups.com Sent: Monday, August 25,
                            Message 13 of 18 , Sep 10, 2008
                              Yes , we are on rman. Backup to disk currently.
                               
                              Thanks
                               
                              Ankur

                              ----- Original Message ----
                              From: Sha Parekh <shaparekh@...>
                              To: psftdba@yahoogroups.com
                              Sent: Monday, August 25, 2008 4:28:51 PM
                              Subject: Re: PeopleSoft DBA Forum Re: Datamover import is very slow...

                              are you setup to run rman?  If so, are you doing backups to disk or tape?

                              --- On Mon, 8/25/08, Ankur Shah <ankursk71@yahoo. com> wrote:
                              From: Ankur Shah <ankursk71@yahoo. com>
                              Subject: Re: PeopleSoft DBA Forum Re: Datamover import is very slow...
                              To: psftdba@yahoogroups .com
                              Date: Monday, August 25, 2008, 3:20 PM

                              Can you share the RMAN scripts / docs that allows us to use for DB refresh
                               
                              Thanks
                               
                              Ankur

                              ----- Original Message ----
                              From: Sha Parekh <shaparekh@yahoo. com>
                              To: psftdba@yahoogroups .com
                              Sent: Monday, July 28, 2008 10:53:43 AM
                              Subject: Re: PeopleSoft DBA Forum Re: Datamover import is very slow...

                              use rman duplicate command. I refresh a 400 gig financials db in under 3 hrs. It is easy to use and requires no production downtime...

                              --- On Mon, 7/28/08, Shaun <shaundl@btopenworld .com> wrote:
                              From: Shaun <shaundl@btopenworld .com>
                              Subject: Re: PeopleSoft DBA Forum Re: Datamover import is very slow...
                              To: psftdba@yahoogroups .com
                              Date: Monday, July 28, 2008, 8:31 AM

                              We've got a 110Gb instance and refresh in just over the hour (65mins) with either Hot backup or RMAN refreshes, just like "the dragon" says :)

                              but RMAN will be more scalable, hence why we're switching, i.e. won't slow down as much as the DB grows as it only ships the changes.

                              regards
                              Shaun

                              ----- Original Message ----
                              From: "william_gioioso@ vanguard. com" <william_gioioso@ vanguard. com>
                              To: psftdba@yahoogroups .com
                              Sent: Monday, 28 July, 2008 1:04:50 PM
                              Subject: PeopleSoft DBA Forum Re: Datamover import is very slow...


                              I am also looking for advice in speeding up our db refreshes.  Our DB size is about 160 GB and it is taking 20+ hours to import using conventional exp/imp.  Our DBA tried 10g Data Pump and saw no difference.  We tried a couple of other things (statistics off on import, and recordlength set to a large number).  We also recently deployed RMAN but DBA has not investigated using it for refreshing.

                              Any advice appreciated.

                              Thanks.
                              Bill




                            • Ankur Shah
                              We use EMC SAN ... From: shajivps To: psftdba@yahoogroups.com Sent: Monday, August 25, 2008 4:44:10 PM Subject: PeopleSoft DBA Forum Re:
                              Message 14 of 18 , Sep 10, 2008
                                We use EMC SAN

                                ----- Original Message ----
                                From: shajivps <shajivps@...>
                                To: psftdba@yahoogroups.com
                                Sent: Monday, August 25, 2008 4:44:10 PM
                                Subject: PeopleSoft DBA Forum Re: Datamover import is very slow...

                                Fyi,

                                As suggested earlier, RMAN is the easiest way to refresh a db when it
                                gets bigger. I refresh a 1.5TB psoft fin database under 6 hours.

                                As for the scripts, it is more specific with the storage library you
                                are using. If you are not the DBA doing this work, dont even try it.
                                If you are the dba, I can give a template and the steps involved.
                                What is the storage library used at your site ?

                                Regards,

                                Shaji.

                                --- In psftdba@yahoogroups .com, Ankur Shah <ankursk71@. ..> wrote:
                                >
                                > Can you share the RMAN scripts / docs that allows us to use for DB
                                refresh
                                > Thanks
                                > Ankur
                                >
                                >
                                >
                                > ----- Original Message ----
                                > From: Sha Parekh <shaparekh@. ..>
                                > To: psftdba@yahoogroups .com
                                > Sent: Monday, July 28, 2008 10:53:43 AM
                                > Subject: Re: PeopleSoft DBA Forum Re: Datamover import is very slow...
                                >
                                >
                                > use rman duplicate command. I refresh a 400 gig financials db in
                                under 3 hrs. It is easy to use and requires no production downtime...
                                >
                                > --- On Mon, 7/28/08, Shaun <shaundl@btopenworl d .com> wrote:
                                >
                                > From: Shaun <shaundl@btopenworl d .com>
                                > Subject: Re: PeopleSoft DBA Forum Re: Datamover import is very slow...
                                > To: psftdba@yahoogroups .com
                                > Date: Monday, July 28, 2008, 8:31 AM
                                >
                                >
                                > We've got a 110Gb instance and refresh in just over the hour
                                (65mins) with either Hot backup or RMAN refreshes, just like "the
                                dragon" says :)
                                >
                                > but RMAN will be more scalable, hence why we're switching, i.e.
                                won't slow down as much as the DB grows as it only ships the changes.
                                >
                                > regards
                                > Shaun
                                >
                                >
                                > ----- Original Message ----
                                > From: "william_gioioso@ vanguard. com" <william_gioioso@ vanguard. com>
                                > To: psftdba@yahoogroups .com
                                > Sent: Monday, 28 July, 2008 1:04:50 PM
                                > Subject: PeopleSoft DBA Forum Re: Datamover import is very slow....
                                >
                                >
                                >
                                > I am also looking for advice in speeding up our db refreshes. Our
                                DB size is about 160 GB and it is taking 20+ hours to import using
                                conventional exp/imp. Our DBA tried 10g Data Pump and saw no
                                difference. We tried a couple of other things (statistics off on
                                import, and recordlength set to a large number). We also recently
                                deployed RMAN but DBA has not investigated using it for refreshing.
                                >
                                > Any advice appreciated.
                                >
                                > Thanks.
                                > Bill
                                >


                              Your message has been successfully submitted and would be delivered to recipients shortly.