expdp/impdp the DB for rehost

[rleir]

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

[SWeiler_9940261]

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

[BenLoosli · Accepted solution]

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

[TomU]

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]

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

[jbailey]

Works for me every time. Unless PTC messed something up of course 🙂

[rleir]

@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_graham]

@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.

[rleir]

Oh, okay.  All good answers but let's accept yours, Ben.