Importing a Windchill Oracle Dump Using Data Pump
This procedure describes how to use the Oracle Data Pump utility to import the source system’s Windchill schema into the target system. The procedure assumes that you are familiar with the Oracle Data Pump tool and have access to its documentation. PTC recommends that the source schema username be used as the target schema username.
The <database_directory> should be created and read and write privileges to it should be granted to the user System. See the Oracle documentation for more details.
1. Ensure that the source_db_dump_file has been copied from the source system to the <database_directory> location on the target system.
2. Create target schema user using Oracle Configuration Utility in PTC Solution Installer. For more information, see the “Configuring a Remote Oracle Database to Work with the Windchill Server” section in
Post-Installation Activities.
3. Using SQL*Plus, login to the target database as the target schema user name and execute the following scripts:
◦ WTReversePK.sql
◦ WTPrefixPK.sql
◦ WipPk.sql
4. Import the Oracle dump using the Data Pump utility. For example, execute:
impdp system/<Password>@<targetdbsid> SCHEMAS=<sourcedb_username> DIRECTORY=<database_directory> DUMPFILE=<source_db_dump_file> logfile=<db_imp.log>
See the Oracle documentation for more details on the impdp utility.
You can safely ignore the following warnings:
ORA-31684: Object type USER:"SOURCEUSER" already existsORA-39112: Dependent object type INDEX_STATISTICS skipped,base object type INDEX
Using Data Pump to Import to a Different Target Schema Username
When importing to a different target schema username, the following additional steps are necessary:
1. Substitute the import command in Step 4 above with the following command as an example:
impdp system/<Password>@<targetdbsid> directory=<database_directory>
dumpfile=<db_dump_file> logfile= <db_imp.log>
remap_schema=<sourcedb_username>:<targetdb_username> transform=OID:n
2. Address errors from the impdp utility on index creation such as:
ORA-39083: Object type INDEX failed to create with error:
ORA-00904: "TEST"."WIPPK"."GETNORMALIZEDWIPSTATE": invalid identifier
Failing sql is:
CREATE UNIQUE INDEX "RVANG1"."VARIANTSPEC$UNIQUE50" ON "RVANG1"."VARIANTSPEC" ("IDA3MASTERREFERENCE", "VERSIONIDA2VERSIONINFO", "ITERATIONIDA2ITERATIONINFO", "RVANG"."WIPPK"."GETNORMALIZEDWIPSTATE"("STATECHECKOUTINFO"), "ONEOFFVERSIONIDA2ONEOFFVERSI") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 24576 NEXT 24576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREA
These function-based indexes reference the package in the original database user account. It is necessary to create those indexes manually. To do this, perform a search for the file containing, for example "EPMDOCUMENT$UNIQUE", under <Windchill>/db/sql and open that file, which contains the Index definition. Run the corresponding SQL to create the index (you may have to adjust the storage parameters). Repeat this procedure for all indexes that fail to create.
3. Address the following errors in the import log:
ORA-39082: Object type PACKAGE_BODY:"SourceUser"."BASELINEPK" created with compilation warnings
ORA-39082: Object type PACKAGE_BODY:" SourceUser "."EPMWORKSPACEPK" created with compilation warnings
If these occur, you need to recompile the invalid packages and package_bodies by executing following statement as the target database user:
SQL> alter package BASELINEPK compile body;
SQL> alter package EPMWORKSPACEPK compile body;
After the above steps are completed, the new database user account will contain data identical to the original one, including storage parameters for the segments.
If the import process reports other ORA-XXXXX errors, consult your on-site database administrator.
After the previous steps are completed, the new database user account will contain data identical to the original one, including storage parameters for the segments. You can verify this by executing the following SQL statement on both the source and target databases and comparing the results:
SQL> select count(*), object_type from user_objects group by object_type;
COUNT(*) OBJECT_TYPE
---------- -------------------
45 SEQUENCE
17 PACKAGE BODY
17 PACKAGE
423 LOB
2 TRIGGER
857 TABLE
2438 INDEX
53 TYPE