Importing Windchill Oracle Dump into Oracle Autonomous Database (23ai) using Data Pump
This section describes the process to import the data from the source system to the target system for an Oracle Autonomous Database. Perform the following steps to import an Oracle Autonomous database:
1. Copy the dump file that is exported from the source system and as described in Exporting the Oracle Source Database Using Data Pump, to the Azure Storage account container.
2. Create credentials on Oracle Autonomous database. Follow the steps below after the credentials are created.
◦ Download the Oracle Autonomous database wallet. Copy the wallet on your machine.
◦ Update (DIRECTORY="<Wallet location>")) in sqlnet.ora. This specifies the wallet location.
◦ Set environment variable TNS_ADMIN=<Wallet Location>. This is required for connecting to the oracle autonomous database user.
◦ Connect to the administrator user account of the Oracle Autonomous database instance that is created in step above and using SQL*Plus and run below query:
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => '<DumpDirName>',
username => <StoargeAccountName>,
password => '<StorageAccountAccesskey>' );
END;
/
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => '<DumpDirName>',
username => <StoargeAccountName>,
password => '<StorageAccountAccesskey>' );
END;
/
3. Create a target schema user. For more information, refer to “Creating a User in Oracle Autonomous Database” section from the Windchill Installation and Configuration Guide.
4. 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
These files can be found in: <Windchill>\db\sql\wnc\Foundation\nonmodeled\procedures.
5. Import the Oracle dump using the Data Pump utility. An example of the script is:
impdp admin/<Password> @ <TNS name for tpurgent connection string>
SCHEMAS=<sourcedb_username> REMAP_TABLESPACE=USERS:DATA
REMAP_TABLESPACE=BLOBS:DATA REMAP_TABLESPACE=INDX:DATA
REMAP_TABLESPACE=WCADUDIT:DATA DUMPFILE='<Dump file path in stotage account>’
CREDENTIAL=<DumpDirName> LOGFILE=<db_imp.log> exclude=INDEX
SCHEMAS=<sourcedb_username> REMAP_TABLESPACE=USERS:DATA
REMAP_TABLESPACE=BLOBS:DATA REMAP_TABLESPACE=INDX:DATA
REMAP_TABLESPACE=WCADUDIT:DATA DUMPFILE='<Dump file path in stotage account>’
CREDENTIAL=<DumpDirName> LOGFILE=<db_imp.log> exclude=INDEX
◦ Execute the following command to import the statistics to use the index_dump_file.dmp file.
impdp admin/<Password> @ <TNS name for tpurgent connection string>
SCHEMAS=<sourcedb_username> REMAP_TABLESPACE=USERS:DATA
REMAP_TABLESPACE=BLOBS:DATA REMAP_TABLESPACE=INDX:DATA
REMAP_TABLESPACE=WCADUDIT:DATA DUMPFILE='<Index Dump file path in stotage account>'
CREDENTIAL=<DumpDirName> LOGFILE=<db_imp.log> include=statistics
SCHEMAS=<sourcedb_username> REMAP_TABLESPACE=USERS:DATA
REMAP_TABLESPACE=BLOBS:DATA REMAP_TABLESPACE=INDX:DATA
REMAP_TABLESPACE=WCADUDIT:DATA DUMPFILE='<Index Dump file path in stotage account>'
CREDENTIAL=<DumpDirName> LOGFILE=<db_imp.log> include=statistics
6. Oracle Autonomous database does not support unstructured XML-type indexes. Errors encountered during XML index creation that are listed here, can be safely ignored.
ORA-29958: ODCI_FATAL returned by the implementation of the ODCIINDEXCREATE routine for index "ELEPHANT"."REPORTTEMPLATE$COMPOSITE".
ORA-01031: insufficient privileges
Failing sql is:
CREATE INDEX "ELEPHANT"."REPORTTEMPLATE$COMPOSITE" ON "ELEPHANT"."REPORTTEMPLATE" ("QML") INDEXTYPE IS "XDB"."XMLINDEX" PARAMETERS ('PATH TABLE (TABLESPACE INDX) PATH ID INDEX (TABLESPACE INDX) ORDER KEY INDEX (TABLESPACE INDX) VALUE INDEX (TABLESPACE INDX)')
ORA-01031: insufficient privileges
Failing sql is:
CREATE INDEX "ELEPHANT"."REPORTTEMPLATE$COMPOSITE" ON "ELEPHANT"."REPORTTEMPLATE" ("QML") INDEXTYPE IS "XDB"."XMLINDEX" PARAMETERS ('PATH TABLE (TABLESPACE INDX) PATH ID INDEX (TABLESPACE INDX) ORDER KEY INDEX (TABLESPACE INDX) VALUE INDEX (TABLESPACE INDX)')
To ensure compatibility between Windchill and Oracle Autonomous database, the Upgrade Manager performs the following actions:
◦ Drops unstructured XML indexes from Windchill out-of-the-box (OOTB) modeled REPORTTEMPLATE, OLDDNMAPPING, and XMLDOCUMENT tables.
◦ Automatically recreates these indexes using a supported Oracle Text Index type during the upgrade process.
 |
For custom XML indexes, you must take appropriate actions as outlined in the “Upgrade Guide” to ensure compatibility and successful migration.
|