Skip to main content
R
rleir17-Peridot
17-Peridot
February 28, 2024
Solved

expdp/impdp the DB for rehost

  • February 28, 2024
  • 3 replies
  • 2495 views

Team

Rehost from Prod to QA server: dump/restore the Oracle DB

Oracle expdp: we explored through the options with help in stackoverflow etc, and we probably got a good dump.  We copy the dump file to the QA server.

Oracle impdp: we suspect that we need to drop tables before doing the impdp.  What do we need to drop? Or can we just do impdp, counting on it to overwrite the old Windchill db contents? 

 

Alternate solution: would RMAN be a good tool to do this?

cheers -- Rick

Best answer by BenLoosli

When I build a new test server, I do the following steps:

  1. Export the DB with Windchill shutdown
  2. Copy the dump file to the new server
  3. Copy the following SQL files from the new Windchill server to the Oracle server: Create_user.sql, WTReversePK.sql, WTPrefixPK.sql, WipPK.sql and WTPK.sql
  4. Drop the current PDM user: drop user <PDM_Name> cascade;
  5. Run the Create_User.sql
  6. Create the backup directory
  7. Run the other f sql files copied over
  8. import the dumpfile
  9. Check for invalid packages and recompile - CS132877
  10. Gather DB statistics - CS129969

3 replies

S
SWeiler_994026116-Pearl
16-Pearl
February 28, 2024

You should drop the db user, and cascade; then run the create_user sql script:

Here is the PTC article you need: https://www.ptc.com/en/support/article/CS249705?source=search

 

drop user <dbuser> cascade;
@create_user.sql

    B
    23-Emerald III
    BenLoosli23-Emerald IIIAnswer
    23-Emerald III
    February 28, 2024

    When I build a new test server, I do the following steps:

    1. Export the DB with Windchill shutdown
    2. Copy the dump file to the new server
    3. Copy the following SQL files from the new Windchill server to the Oracle server: Create_user.sql, WTReversePK.sql, WTPrefixPK.sql, WipPK.sql and WTPK.sql
    4. Drop the current PDM user: drop user <PDM_Name> cascade;
    5. Run the Create_User.sql
    6. Create the backup directory
    7. Run the other f sql files copied over
    8. import the dumpfile
    9. Check for invalid packages and recompile - CS132877
    10. Gather DB statistics - CS129969
      T
      23-Emerald IV
      TomU23-Emerald IV
      23-Emerald IV
      February 28, 2024

      The creation scripts (step 3 and 5) seem unnecessary to me.  Importing the database automatically recreates the user.  Drop the user and import the dump file seems to be sufficient.

        jbailey
        jbailey18-Opal
        18-Opal
        February 29, 2024

        I think the create user step helps if you are defining permissions ahead of time that are only limited to what is necessary. Our work instructions have always been to drop, create, import, fix packages then run the rehost tool

          R
          rleir17-PeridotAuthor
          17-Peridot
          March 12, 2024

          @jbailey 

          @TomU 

          @BenLoosli 

          @SWeiler_9940261 

          Team, Thanks for the great answers.  All are worthy of being marked 'Accept as Solution'.  The moderators want me to choose! Nope, I will leave it un-marked unless everyone thinks there is a best choice.

          D
          18-Opal
          d_graham18-Opal
          18-Opal
          March 12, 2024

          @rleir 

           

          Personably I like drop cascade; create user; alter user identified by; impdb.

          I NEVER have the dB user password the same for multiple Windchill installation.

          Why you ask? Because some companies might use the same Oracle server for production and dev.

          There’s nothing wrong with that as long as you take precautions to insure they don’t get mixed up.

          Different passwords takes care of that.

           

          And even if they use different Oracle servers, someone could still screw it up, so having different passwords is one more gate keeper. Better safe than sorry.

           

          BTW, If @TomU  says create isn’t necessary I believe him.

          Terms & ConditionsCookie settingsAccessibility statement