Hello,
I'm migrating from an XE 11.g to 18.c. I've installed Oracle XE 18c on a Windows 10 platform. I'm following the instructions in https://docs.oracle.com/en/database/oracle/oracle-database/18/xeinw/exporting-and-importing-data-oracle-database-xe-11.2… I'm using the section 9.2 - Exporting and Importing Data for Oracle Application Express Users.
I exported the data from my original system, and it completed successfully. I transferred the dump file (DB11G.dmp) to my new system in the folder C:\SS_BACKUPS.
On the original source system the files are located in: C:\oraclexe\app\oracle\oradata\XE. The files there are:
APEX_1244616068553754.DBF
APEX_1306221113755490.DBF
APEX_1820439970947266.DBF
APEX_2234202002248635.DBF
CONTROL.DBF
SUSAUX.DBF
SYSTEM.DBF
TEMP.DBF
UNDOTBS1.DBF
USERS.DBF
On the new target system (different hardware) the files are located in: C:\app\product\18.0.0\oradata\XE. After a fresh install of 18C the files are:
CONTROL01.CTL
CONTROL02.CTL
REDO01.LOG
REDO02.LOG
REDO03.LOG
SYSAUX01.DBF
SYSTEM01.DBF
TEMP01.DBF
UNDOTBS01.DBF
USERS01.DBF
On the new target system I executed the following commands in SQLPLUS:
sqlplus SYS AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=XEPDB1;
SQL> CREATE DIRECTORY BACKUP_DIR AS 'C:\SS_LIBRARY\BACKUPS';
SQL> GRANT READ, WRITE ON DIRECTORY BACKUP_DIR TO SYSTEM;
SQL> GRANT DATAPUMP_IMP_FULL_DATABASE to SYSTEM;
Then from a privileged DOS prompt I ran the command:
impdp parfile=impdp_remap_directory.par
The contents of the PAR file are:
userid=system/(my_password)@localhost/xepdb1
full=Y
REMAP_DIRECTORY="'C:\oraclexe\app\oracle\oradata\XE\':'C:\app\product\18.0.0\oradata\XE\'"
directory=BACKUP_DIR
dumpfile=DB11G.dmp
logfile=impdpDB11G.log
The IMPDP generates several errors. Some of which the documentation section 9.2 tells us to ignore, like the SYSAUX:.
ORA-39083: Object type TABLESPACE:"SYSAUX" failed to create with error:
ORA-02236: invalid file name
Failing sql is:
CREATE TABLESPACE "SYSAUX" DATAFILE SIZE 5242880000 AUTOEXTEND ON NEXT 5242880000 MAXSIZE 6144M,'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF' SIZE 10485760 AUTOEXTEND ON NEXT 10
Note that it's identifying the file structure from the original (source) system. However, I get other errors, for example:
ORA-39083: Object type TABLESPACE:"APEX_1244616068553754" failed to create with error:
ORA-01119: error in creating database file 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\APEX_1244616068553754.DBF'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
Failing sql is:
CREATE TABLESPACE "APEX_1244616068553754" DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\APEX_1244616068553754.DBF' SIZE 31522816 AUTOEXTEND ON NEXT 8192 MAXSIZE 6144M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
It also doesn't create the one schema I use:
ORA-39083: Object type USER:"(MY_SCHEMA_NAME)" failed to create with error:
ORA-00959: tablespace 'APEX_1244616068553754' does not exist
Failing sql is:
CREATE USER "(MY_SCHEMA_NAME)" IDENTIFIED BY VALUES 'S:A08FF05747C34D6B99BE070E460C54943293CD966CDFE497E3D563A058BD;32177B42FCD932DE' DEFAULT TABLESPACE "APEX_1244616068553754" TEMPORARY TABLESPACE "TEMP"
In SQLPLUS when I run SELECT USERNAME from DBA_USERS; the schema name is not listed.
Do the "01" suffixes on the files in C:\app\product\18.0.0\oradata\XE have anything to do with this? It seems that when IMPDP doesn't find the "vanilla" DBF's (without the "01" suffix), it attempts to create them in the directory structure that exists on the original (source) system.
Any idea why I'm receiving these error?
Thanks for looking at this.