Moving from Previous Versions of Oracle Database XE to Oracle Database Free

8 Moving from Previous Versions of Oracle Database XE to Oracle Database Free

This topic explains how to export and import data between Oracle Database 21c Express Edition (XE) and Oracle Database 23ai Free.

Exporting and Importing Data between Oracle Database 21c XE and Oracle Database 23ai Free
Exporting and Importing Data between Oracle Database 23.x Free and Oracle Database 23ai Free

Note these points before you start the export and import process:

You cannot use Oracle Database Upgrade Assistant (Oracle DBUA) to perform an upgrade.
You cannot use Oracle Database Configuration Assistant (Oracle DBCA) to plug PDBs of previous versions to Oracle Database Free.
Oracle Database 18c XE users must first move to 21c XE (See, Moving from Previous Versions of Oracle Database XE to XE 21c), and then export data from 21c XE to import them into Oracle Database 23ai Free.

Exporting and Importing Data between Oracle Database 21c XE and Oracle Database 23ai Free

Learn how to export and import data between Oracle Database 21c Express Edition (XE) and Oracle Database 23ai Free.

Exporting Data

To export data from your 21c XE database:

As the root user, create a /opt/dump directory on the local file system for the DUMP_DIR directory object.
```
mkdir /opt/dump
chown -R oracle:oinstall /opt/dump
chmod -R 760 /opt/dump
```
Perform the following steps for each pluggable database (PDB). The steps in this section are for the PDB xepdb1.
1. Set the ORACLE_HOME and ORACLE_SID environment variables.
```
export ORACLE_SID=XE
export ORACLE_HOME=/opt/oracle/product/21c/dbhomeXE
```
2. Connect to the 21c XE database as user SYS using the SYSDBA privilege as the oracle user, and switch the container to xepdb1.
3. Create the directory object DUMP_DIR and grant READ and WRITE privileges on the DUMP_DIR directory to the SYSTEM user.
```
/opt/oracle/product/21c/dbhomeXE/bin/sqlplus / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=xepdb1;
SQL> CREATE DIRECTORY DUMP_DIR AS '/opt/dump';
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;
```
4. Export data from your 21c XE PDB xepdb1 to the dump folder.
```
/opt/oracle/product/21c/dbhomeXE/bin/expdp system/system_password@dbhost.example.com:1521/xepdb1 full=Y directory=DUMP_DIR dumpfile=expdb21c_xepdb1.dmp logfile=expdb21c_xepdb1.log
```
  Note:
  - Replace dbhost.example.com with your database host name. If required, replace 1521 with the port number the listener listens on.
  - The default PDB name created is xepdb1 in 21c and freepdb1 in 23ai.
Deinstall Oracle Database 21c XE if you plan to install 23ai Free on the same system. See Deinstalling Oracle Database XE for more information
Install Oracle Database 23ai Free.

Importing Data

To import data to your Oracle Database 23ai Free, perform the following steps for each PDB. The steps in this section are for importing data from PDB xepdb1 to freepdb1.

Set the ORACLE_HOME and ORACLE_SID environment variables.

export ORACLE_SID=FREE
export ORACLE_HOME=/opt/oracle/product/23ai/dbhomeFree

Connect to the 23ai Free database as user SYS using the SYSDBA privilege as the oracle user and switch the container to freepdb1.

Create the directory object DUMP_DIR and grant READ and WRITE privileges on the DUMP_DIR directory to the SYSTEM user.

/opt/oracle/product/23ai/dbhomeFree/bin/sqlplus / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=freepdb1;
SQL> CREATE DIRECTORY DUMP_DIR AS '/opt/dump';
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;

Import data to the 23ai Free PDB freepdb1 from the dump folder created during the export operation.
```
/opt/oracle/product/23ai/dbhomeFree/bin/impdp system/system_password@dbhost.example.com:1521/freepdb1 full=Y directory=DUMP_DIR dumpfile=expdb21c_xepdb1.dmp  logfile=impdb23ai_freepdb1.log
```
Note:
- Replace dbhost.example.com with your database host name. If required, replace 1521 with the port number the listener listens on.
- The default PDB name created is xepdb1 in 21c and freepdb1 in 23ai.

Ignore the following errors:

ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"USERS" already exists
ORA-31684: Object type USER:"PDBADMIN" already exists
ORA-39083: Object type PROC_SYSTEM_GRANT failed to create with error:
ORA-29393: user EM_EXPRESS_ALL does not exist or is not logged on
ORA-39083: Object type NETWORK_ACL:TABLE:NACL$_ACE_EXP failed to create with error:
ORA-01007: Reference to a variable not in SELECT clause.
ORA-39342: Data Pump did not import dependent objects for NETWORK_ACL due to the previous error
ORA-01007: Reference to a variable not in SELECT clause.

Exporting and Importing Data between Oracle Database 23.x Free and Oracle Database 23ai Free

Learn how to export and import data between Oracle Database 23.x Free and Oracle Database 23ai Free.

Note:

Oracle Database 23.x Free can refer to releases 23.2 or 23.3.

Exporting Data

To export data from Oracle Database 23.x Free:

As the root user, create a /opt/dump directory on the local file system for the DUMP_DIR directory object.
```
mkdir /opt/dump
chown -R oracle:oinstall /opt/dump
chmod -R 760 /opt/dump
```
Perform the following steps for each pluggable database (PDB). The steps in this section are for the PDB freepdb1.
1. Set the ORACLE_HOME and ORACLE_SID environment variables.
```
export ORACLE_SID=FREE
export ORACLE_HOME=/opt/oracle/product/23c/dbhomeFree
```
2. Connect to the 23.x Free database as user SYS using the SYSDBA privilege as the oracle user, and switch the container to freepdb1.
3. Create the directory object DUMP_DIR and grant READ and WRITE privileges on the DUMP_DIR directory to the SYSTEM user.
```
/opt/oracle/product/23c/dbhomeFree/bin/sqlplus / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=freepdb1;
SQL> CREATE DIRECTORY DUMP_DIR AS '/opt/dump';
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;
```
4. Export data from your 23.x Free PDB freepdb1 to the dump folder.
```
/opt/oracle/product/23c/dbhomeFree/bin/expdp system/system_password@dbhost.example.com:1521/freepdb1 full=Y directory=DUMP_DIR dumpfile=expdb23c_freepdb1.dmp logfile=expdb23c_freepdb1.log
```
  Note:
  - Replace dbhost.example.com with your database host name. If required, replace 1521 with the port number the listener listens on.
  - The default PDB name created is freepdb1 in 23.x.
Deinstall Oracle Database 23.x Free if you plan to install 23ai Free on the same system. See Deinstalling Oracle Database Free for more information
Install Oracle Database 23ai Free.

Importing Data

To import data to your Oracle Database 23ai Free, perform the following steps for each PDB. The steps in this section are for importing data from 23.x Free PDB freepdb1 and overwriting to the same PDB freepdb1 in 23ai Free.

Set the ORACLE_HOME and ORACLE_SID environment variables.

export ORACLE_SID=FREE
export ORACLE_HOME=/opt/oracle/product/23ai/dbhomeFree

Connect to the 23ai Free database as user SYS using the SYSDBA privilege as the oracle user and switch the container to freepdb1.

Create the directory object DUMP_DIR and grant READ and WRITE privileges on the DUMP_DIR directory to the SYSTEM user.

/opt/oracle/product/23ai/dbhomeFree/bin/sqlplus / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=freepdb1;
SQL> CREATE DIRECTORY DUMP_DIR AS '/opt/dump';
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;

Import data to the 23ai Free PDB freepdb1 from the dump folder created during the export operation.
```
/opt/oracle/product/23ai/dbhomeFree/bin/impdp system/system_password@dbhost.example.com:1521/freepdb1 full=Y directory=DUMP_DIR dumpfile=expdb23c_freepdb1.dmp logfile=impdb23ai_freepdb1.log
```
Note:
- Replace dbhost.example.com with your database host name. If required, replace 1521 with the port number the listener listens on.
- The default PDB name created is freepdb1 in 23.x.

Ignore the following errors:

ORA-39342: Data Pump did not import dependent objects for NETWORK_ACL due to the previous error
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"USERS" already exists
ORA-31684: Object type USER:"PDBADMIN" already exists
ORA-31684: Object type DIRECTORY:"DUMP_DIR" already exists