Products

Gen

Issue/Introduction

This article describes a process to migrate a Gen CSE Oracle database from another OS platform to Linux.
It uses the Oracle Data Pump Utility (Full Transportable option) as per Oracle 19c documentation: Migrating and Converting Non-CDBs to a PDB with a Different Endian Operating System, Chapter 4, section 2 “Transporting a Database Using an Export Dump File”.

In this example, an export from a Windows Oracle CSE database is done and because Windows has the same Little Endian format as Linux no user-defined tablespace data file conversion is necessary for the import to Linux.

NOTE: This process has been tested by Gen L1 Support but no formal support of this is implied and site specific Oracle expertise should normally be used to achieve this.

Environment

Gen 8.6 Client Server Encyclopedia (CSE) for Linux

Resolution

Export the current CSE database

Preparation

On the source CSE stop the CSE iefmd process.
Connect to the Oracle CSE database instance using sqlplus as user sys or system and set the 3 user-defined tablespaces IEFSENCY_IDX , IEFSENCY_ROLL, IEFSENCY_TAB to READ ONLY per below.
Depending on how the database was originally created IEFSENCY_ROLL may not exist because it has been superseded by Oracle’s Automatic Undo Management (parameter undo_management set to AUTO ) i.e. the UNDOTBS1 tablespace listed below. However, if IEFSENCY_ROLL exists, even if it is not used, it still has to be exported because it is a user-defined tablespace.

===
SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
IEFSENCY_STMP ONLINE
IEFSENCY_ROLL ONLINE
IEFSENCY_TAB ONLINE
IEFSENCY_IDX ONLINE
IEFSENCY_TEMP ONLINE

8 rows selected.

SQL> ALTER TABLESPACE IEFSENCY_IDX READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE IEFSENCY_ROLL READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE IEFSENCY_TAB READ ONLY;

Tablespace altered.

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
IEFSENCY_STMP ONLINE
IEFSENCY_ROLL READ ONLY
IEFSENCY_TAB READ ONLY
IEFSENCY_IDX READ ONLY
IEFSENCY_TEMP ONLINE

8 rows selected.
===

If this database was originally created using CSE script crdbcse.sql then the small size of tablespace IEFSENCY_STMP will cause this error during the export:
ORA-39171: Job is experiencing a resumable wait.
Resumable error: ORA-01652: unable to extend temp segment by 128 in tablespace IEFSENCY_STMP

To avoid the error change the default temporary tablespace to the larger IEFSENCY_TEMP:
===
SQL > alter database default temporary tablespace IEFSENCY_TEMP;

Database altered.
===

Export process using Full Transportable option

Create parameter file exp_parfile.txt
===
FULL=Y
DUMPFILE=EXPORT_DBCSE.DMP
DIRECTORY= DATA_PUMP_DIR
TRANSPORTABLE=ALWAYS
LOGFILE=export_dbcse.log
===

Start a command prompt and set ORACLE_SID to the required database name e.g., DBCSE

C:\ >set ORACLE_SID=DBCSE

C:\ >echo %ORACLE_SID%
DBCSE

Change to the directory containing the parameter file and run the export command:
expdp system/system_password parfile='exp_parfile.txt'

===
Export: Release 19.0.0.0.0 - Production on Tue Sep 5 10:41:38 2023
Version 19.3.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** parfile='exp_parfile.txt'
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW" 5.882 KB 25 rows
. . exported "SYSTEM"."REDO_DB" 25.59 KB 1 rows
. . exported "SYS"."DAM_CONFIG_PARAM$" 6.531 KB 14 rows
. . exported "SYS"."TSDP_SUBPOL$" 6.328 KB 1 rows
. . exported "SYS"."TSDP_PARAMETER$" 5.953 KB 1 rows
. . exported "SYS"."TSDP_POLICY$" 5.921 KB 1 rows
. . exported "SYS"."AUD$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$" 0 KB 0 rows
. . exported "SYS"."TSDP_ASSOCIATION$" 0 KB 0 rows
. . exported "SYS"."TSDP_CONDITION$" 0 KB 0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$" 0 KB 0 rows
. . exported "SYS"."TSDP_PROTECTION$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
. . exported "SYS"."TSDP_SOURCE$" 0 KB 0 rows
. . exported "SYSTEM"."REDO_LOG" 0 KB 0 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 5.960 KB 2 rows
. . exported "SYS"."DBA_SENSITIVE_DATA" 0 KB 0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows
. . exported "SYS"."NACL$_ACE_EXP" 0 KB 0 rows
. . exported "SYS"."NACL$_HOST_EXP" 6.921 KB 1 rows
. . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows
. . exported "SYS"."SQL$TEXT_DATAPUMP" 95.22 KB 46 rows
. . exported "SYS"."SQL$_DATAPUMP" 7.632 KB 46 rows
. . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP" 76.57 KB 91 rows
. . exported "SYS"."SQLOBJ$DATA_DATAPUMP" 9.406 KB 2 rows
. . exported "SYS"."SQLOBJ$PLAN_DATAPUMP" 2.578 MB 6967 rows
. . exported "SYS"."SQLOBJ$_DATAPUMP" 14.60 KB 91 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
C:\ORACLE\19.3\DB_HOME\RDBMS\LOG\EXPORT_DBCSE.DMP
******************************************************************************
Datafiles required for transportable tablespace IEFSENCY_IDX:
C:\ORACLE\19.3\DB_HOME\DBS\IDXDBCSE.DBF
Datafiles required for transportable tablespace IEFSENCY_ROLL:
C:\ORACLE\19.3\DB_HOME\DBS\ROLLDBCSE.DBF
Datafiles required for transportable tablespace IEFSENCY_TAB:
C:\ORACLE\19.3\DB_HOME\DBS\TABDBCSE.DBF
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Tue Sep 5 10:43:24 2023 elapsed 0 00:01:45
===

Binary copy the 4 files to a temporary location on the Linux server
C:\ORACLE\19.3\DB_HOME\RDBMS\LOG\EXPORT_DBCSE.DMP
C:\ORACLE\19.3\DB_HOME\DBS\IDXDBCSE.DBF
C:\ORACLE\19.3\DB_HOME\DBS\ROLLDBCSE.DBF
C:\ORACLE\19.3\DB_HOME\DBS\TABDBCSE.DBF

NOTE: If the Endian format is different on the source OS and the target Linux (Littlle Endian) then the tablespace data files need to be converted per the “Task 3 Transport the Data Files for the User-Defined Tablespaces” step in the Oracle documentation. Also, see earlier Chapter 1 Transporting Databases.

On the source CSE revert the 3 user-defined tablespaces back to READ WRITE (ONLINE) and revert the temporary tablespace back to IEFSENCY_STMP i.e.
===
SQL> ALTER TABLESPACE IEFSENCY_IDX READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE IEFSENCY_ROLL READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE IEFSENCY_TAB READ WRITE;

Tablespace altered.

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
IEFSENCY_STMP ONLINE
IEFSENCY_ROLL ONLINE
IEFSENCY_TAB ONLINE
IEFSENCY_IDX ONLINE
IEFSENCY_TEMP ONLINE

8 rows selected.

SQL> alter database default temporary tablespace IEFSENCY_STMP;

Database altered.
===

Import into the new Linux CSE database

Preparation

CSE database creation.
After installing the Linux CSE software as root user (CSE on Linux Installation) create the CSE database as the Linux oracle user before configuring it.
It is assumed that the Oracle software has already been installed into the directory /opt/oracle/product/19c/dbhome_1 and the new CSE database will be named DBCSE.
The user oracle environment variables are set as follows:
ORACLE_HOME=/opt/oracle/product/19c/dbhome_1
ORACLE_SID=DBCSE
The directory for DBCSE data files needs to be created as /opt/oracle/oradata/DBCSE.

From the CSE software install directory, copy file /opt/Gen/CSE/cse/cse_oracle/initdbcse.ora to $ORACLE_HOME/dbs/initDBCSE.ora (NOTE: the case change to use uppercase DBCSE to ensure the file is found automatically in any future DBCSE startup).
Also, edit file $ORACLE_HOME/dbs/initDBCSE.ora and comment out this line because rollback segments are not needed with Automatic Undo Management:
#rollback_segments = (roll1, roll2, roll3, roll4)
A copy of the customised file is also attached to this article.

Copy the default script /opt/Gen/CSE/cse/cse_oracle/crdbcse.sql to create the database to create script crdbcse_custom.sql and customise as per below:

NOTES:

Customise directory names as required.
The startup nomount pfile command references file initDBCSE.ora, per the case change above.
"user" lines have been added to create passwords for the SYS and SYSTEM users. Modify sys_password and system_password as required.
Whatever is the character set of the database being exported that should also be used to create the new DBCSE database instead of us7ascii in the default script e.g. WE8ISO8859P1. This can be checked on the source database using SQL:
select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET'
The IEFSENCY_STMP tablespace is created with size 50M instead of 5M to avoid encountering the same Resumable error seen above during the export process:
ORA-39171: Job is experiencing a resumable wait.
Resumable error: ORA-01652: unable to extend temp segment by 128 in tablespace IEFSENCY_STMP
The sections that create rollback segment r1, the tablespace IEFSENCY_ROLL, rollback segments roll1, roll2, roll3, roll4 have been removed because Automatic Undo Management is being used. The tablespace IEFSENCY_ROLL may also be imported if it exists in the current CSE database.
The sections that create tablespaces IEFSENCY_IDX and tablespace IEFSENCY_TAB have been removed because those tablespaces will be imported.
"AUTOEXTEND ON" is added in the section that creates tablespace IEFSENCY_TEMP.
Also creation of user ency with user privileges and table access has been removed because the import process creates that user.

A copy of the customised script is attached to this article and listed below:
===
-- Copyright (c) 2023 Broadcom. All rights reserved.
-- Gen Client Server Encyclopedia.
-- Oracle Create database and tablespace for
-- Encyclopedia database DBCSE.

set echo on
whenever oserror exit failure
whenever sqlerror exit failure

startup nomount pfile=/opt/oracle/product/19c/dbhome_1/dbs/initDBCSE.ora

create database DBCSE
user sys identified by sys_password
user system identified by system_password
character set WE8ISO8859P1
controlfile reuse
logfile '/opt/oracle/oradata/DBCSE/log1DBCSE.dbf' size 4M reuse,
'/opt/oracle/oradata/DBCSE/log2DBCSE.dbf' size 4M reuse,
'/opt/oracle/oradata/DBCSE/log3DBCSE.dbf' size 4M reuse,
'/opt/oracle/oradata/DBCSE/log4DBCSE.dbf' size 4M reuse
datafile '/opt/oracle/oradata/DBCSE/sysDBCSE.dbf' size 240M reuse
autoextend on next 10M maxsize unlimited
extent management local
default temporary tablespace iefsency_stmp
tempfile '/opt/oracle/oradata/DBCSE/stmpDBCSE.dbf' size 50M reuse
sysaux datafile '/opt/oracle/oradata/DBCSE/auxDBCSE.dbf' size 150M reuse
autoextend on next 10M maxsize unlimited
undo tablespace undotbs1
datafile '/opt/oracle/oradata/DBCSE/undotbs01.dbf' size 20M reuse
autoextend on maxsize unlimited;

whenever sqlerror continue
set termout off
start $ORACLE_HOME/rdbms/admin/catalog
start $ORACLE_HOME/rdbms/admin/catproc

set termout on
whenever sqlerror exit failure

create temporary tablespace iefsency_temp
tempfile '/opt/oracle/oradata/DBCSE/tempDBCSE.dbf'
size 130M reuse
autoextend on
extent management local;

connect system/system_password
whenever sqlerror continue
set termout off
start $ORACLE_HOME/sqlplus/admin/pupbld.sql
set termout on

exit 0
===

Connect as default user sys from the Linux oracle account and run crdbcse_custom.sql:
===
$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 4 23:50:29 2023
Version 19.3.0.0.0

Connected to an idle instance.

SQL> start ./crdbcse_custom.sql
SQL> whenever oserror exit failure
SQL> whenever sqlerror exit failure
SQL>
SQL> startup nomount pfile=/opt/oracle/product/19c/dbhome_1/dbs/initDBCSE.ora
ORACLE instance started.

Total System Global Area 503314096 bytes
Fixed Size 8898224 bytes
Variable Size 251658240 bytes
Database Buffers 239075328 bytes
Redo Buffers 3682304 bytes
SQL>
SQL> create database DBCSE
2 user sys identified by sys9999
3 user system identified by sys9999
4 character set WE8ISO8859P1
5 controlfile reuse
6 logfile '/opt/oracle/oradata/DBCSE/log1DBCSE.dbf' size 4M reuse,
7 '/opt/oracle/oradata/DBCSE/log2DBCSE.dbf' size 4M reuse,
8 '/opt/oracle/oradata/DBCSE/log3DBCSE.dbf' size 4M reuse,
9 '/opt/oracle/oradata/DBCSE/log4DBCSE.dbf' size 4M reuse
10 datafile '/opt/oracle/oradata/DBCSE/sysDBCSE.dbf' size 240M reuse
11 autoextend on next 10M maxsize unlimited
12 extent management local
13 default temporary tablespace iefsency_stmp
14 tempfile '/opt/oracle/oradata/DBCSE/stmpDBCSE.dbf' size 50M reuse
15 sysaux datafile '/opt/oracle/oradata/DBCSE/auxDBCSE.dbf' size 150M reuse
16 autoextend on next 10M maxsize unlimited
17 undo tablespace undotbs1
18 datafile '/opt/oracle/oradata/DBCSE/undotbs01.dbf' size 20M reuse
19 autoextend on maxsize unlimited;

Database created.

SQL>
SQL> whenever sqlerror continue
SQL> set termout off

…

SQL> Rem *********************************************************************
SQL> Rem END catproc.sql
SQL> Rem *********************************************************************
SQL>
SQL> set termout on
SQL> whenever sqlerror exit failure
SQL>
SQL> create temporary tablespace iefsency_temp
2 tempfile '/opt/oracle/oradata/DBCSE/tempDBCSE.dbf'
3 size 130M reuse
4 autoextend on
5 extent management local;

Tablespace created.

SQL>
SQL> connect system/sys9999
Connected.
SQL> whenever sqlerror continue
SQL> set termout off
SQL>
SQL> exit 0
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
$
===

Find the value of the DIRECTORY name DATA_PUMP_DIR to know where to place the EXPORT_DBCSE.DBF file:
===
$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 5 00:57:27 2023
Version 19.3.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DATA_PUMP_DIR';

DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
DATA_PUMP_DIR
/opt/oracle/product/19c/dbhome_1/rdbms/log/

SQL>
===

Import process using Full Transportable option.

Copy file EXPORT_DBCSE.DMP to directory DATA_DUMP_DIR:
$ cp EXPORT_DBCSE.DMP /opt/oracle/product/19c/dbhome_1/rdbms/log

Copy the 3 tablespace files to the Oracle data file directory:
$ cp IDXDBCSE.DBF /opt/oracle/oradata/DBCSE
$ cp ROLLDBCSE.DBF /opt/oracle/oradata/DBCSE
$ cp TABDBCSE.DBF /opt/oracle/oradata/DBCSE

Create parameter file imp_parfile:
===
FULL=Y
DUMPFILE=EXPORT_DBCSE.DMP
DIRECTORY=DATA_PUMP_DIR
TRANSPORT_DATAFILES=
'/opt/oracle/oradata/DBCSE/IDXDBCSE.DBF',
'/opt/oracle/oradata/DBCSE/ROLLDBCSE.DBF',
'/opt/oracle/oradata/DBCSE/TABDBCSE.DBF'
LOGFILE=import_dbcse.log
===

Run the import command from the directory containing the parameter file:
impdp system/sys9999 parfile='imp_parfile'

===
Import: Release 19.0.0.0.0 - Production on Tue Sep 5 01:27:17 2023
Version 19.3.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Source time zone is -07:00 and target time zone is +00:00.
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** parfile=imp_parfile
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"IEFSENCY_STMP" already exists

ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists

ORA-31684: Object type TABLESPACE:"IEFSENCY_TEMP" already exists

Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
>>> Cannot set an SCN larger than the current SCN. If a Streams Capture configuration was imported then the Apply that processes the captured messages needs to be dropped and recreated. See My Oracle Support article number 1380295.1.
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SYS"."KU$_EXPORT_USER_MAP" 5.882 KB 25 rows
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
. . imported "SYSTEM"."REDO_DB_TMP" 25.59 KB 1 rows
. . imported "SYS"."AMGT$DP$DAM_CONFIG_PARAM$" 6.531 KB 14 rows
. . imported "SYS"."DP$TSDP_SUBPOL$" 6.328 KB 1 rows
. . imported "SYS"."DP$TSDP_PARAMETER$" 5.953 KB 1 rows
. . imported "SYS"."DP$TSDP_POLICY$" 5.921 KB 1 rows
. . imported "SYS"."AMGT$DP$AUD$" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_EVENTS$" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_JOBS$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_ASSOCIATION$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_CONDITION$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_FEATURE_POLICY$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_PROTECTION$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_SENSITIVE_DATA$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_SOURCE$" 0 KB 0 rows
. . imported "SYSTEM"."REDO_LOG_TMP" 0 KB 0 rows
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SYS"."AMGT$DP$AUDTAB$TBS$FOR_EXPORT" 5.960 KB 2 rows
. . imported "SYS"."DP$DBA_SENSITIVE_DATA" 0 KB 0 rows
. . imported "SYS"."DP$DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$FGA_LOG$FOR_EXPORT" 0 KB 0 rows
. . imported "SYS"."NACL$_ACE_IMP" 0 KB 0 rows
. . imported "SYS"."NACL$_HOST_IMP" 6.921 KB 1 rows
. . imported "SYS"."NACL$_WALLET_IMP" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQL$TEXT" 95.22 KB 46 rows
. . imported "SYS"."DATAPUMP$SQL$" 7.632 KB 46 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$AUXDATA" 76.57 KB 91 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$DATA" 9.406 KB 2 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$PLAN" 2.578 MB 6967 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$" 14.60 KB 91 rows
. . imported "SYSTEM"."SCHEDULER_JOB_ARGS_TMP" 0 KB 0 rows
. . imported "SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP" 0 KB 0 rows
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "SYSTEM"."SYS_EXPORT_FULL_01" 543.1 KB 3032 rows
. . imported "SYSTEM"."SYS_EXPORT_FULL_02" 546.2 KB 3042 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 3 error(s) at Tue Sep 5 01:28:20 2023 elapsed 0 00:01:02
===

There is no need to be concerned about the 3 errors:
ORA-31684: Object type TABLESPACE:"IEFSENCY_STMP" already exists
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"IEFSENCY_TEMP" already exists

Configure the Linux CSE

As the designated CSE Linux administrator user run cse_config to generate a new iefmd.ini file on the Linux CSE, using the documented steps here: CSE on Linux Configuration
For "Encyclopedia Database Configuration" and "Coordination Database Configuration" choose either the Update or Skip options. If the current CSE from which the DBCSE was exported is

already at level Gen 8.63 i.e. the same level as the new Linux CSE, then the Skip option should be used.
at an earlier Gen 8.6 version or earlier Gen 7.x or 8.x version, then the Update option should be used.

NOTE: For either of those options even though it still prompts for Encyclopedia ID, Name, and Description, those values will not be updated.

Start the CSE iefmd process using the documented steps here: Start a CSE on UNIX or Linux
Verify that the CSE is accessible via the CSE Clients and the expected models are visible.

Additional Information

An alternative approach

Use Oracle Data Pump Utility with the SCHEMAS option instead of the FULL option.
This method just exports the objects and data belonging to the CSE database user id e.g. ency, and imports it into the new CSE database

Export the current CSE database user id "ency"

Start a command prompt and set ORACLE_SID to the required database name e.g., DBCSE

C:\ >set ORACLE_SID=DBCSE

C:\ >echo %ORACLE_SID%
DBCSE

Run the export command:
expdp system/system_password DUMPFILE=ency.dmp SCHEMAS=ency

===
Export: Release 19.0.0.0.0 - Production on Tue Sep 5 11:30:01 2023
Version 19.3.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** DUMPFILE=ency.dmp SCHEMAS=ency
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
. . exported "ENCY"."DOBJ" 2.987 GB 13047113 rows
. . exported "ENCY"."DASC" 1.215 GB 21609681 rows
. . exported "ENCY"."DNAME" 267.5 MB 1461886 rows
. . exported "ENCY"."DTXT" 148.7 MB 1573732 rows
. . exported "ENCY"."SPRP" 3.251 MB 41332 rows
. . exported "ENCY"."SASC" 2.398 MB 25920 rows
. . exported "ENCY"."STRG" 219.6 KB 5623 rows
. . exported "ENCY"."SDIV" 164.5 KB 2278 rows
. . exported "ENCY"."SOBJ" 139.5 KB 1830 rows
. . exported "ENCY"."DSUBEX" 138.3 KB 5224 rows
. . exported "ENCY"."DMAX" 14.17 KB 14 rows
. . exported "ENCY"."DMDL" 15.03 KB 16 rows
. . exported "ENCY"."DCKOID" 9.890 KB 7 rows
. . exported "ENCY"."DIRENCY" 9.453 KB 1 rows
. . exported "ENCY"."DUSR" 8.617 KB 2 rows
. . exported "ENCY"."DSUBID" 8.445 KB 4 rows
. . exported "ENCY"."DIRUSER" 8.171 KB 2 rows
. . exported "ENCY"."DCKOHIST" 7.570 KB 29 rows
. . exported "ENCY"."DSUBDF" 7.109 KB 5 rows
. . exported "ENCY"."DENCY" 6.656 KB 1 rows
. . exported "ENCY"."DIRLOGON" 6.445 KB 2 rows
. . exported "ENCY"."DIRXCPID" 6.062 KB 41 rows
. . exported "ENCY"."DXCPID" 6.062 KB 41 rows
. . exported "ENCY"."DGRPUS" 0 KB 0 rows
. . exported "ENCY"."DMDLUS" 0 KB 0 rows
. . exported "ENCY"."DSETDF" 0 KB 0 rows
. . exported "ENCY"."DSETID" 0 KB 0 rows
. . exported "ENCY"."DSUBUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
C:\ORACLE\19.3\DB_HOME\RDBMS\LOG\ENCY.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Sep 5 11:32:14 2023 elapsed 0 00:02:12
===

Binary copy the file "C:\ORACLE\19.3\DB_HOME\RDBMS\LOG\ENCY.DMP" to a temporary location on the Linux server

Import into the new Linux CSE database

Preparation

CSE database creation.
The instructions for the initDBCSE.ora file as per the section above.
The instructions for the customisation of the default script /opt/Gen/CSE/cse/cse_oracle/crdbcse.sql are also the same as above except that "AUTOEXTEND ON" is also added for the creation of tablespaces IEFSENCY_IDX and IEFSENCY_TAB as well as IEFSENCY_TEMP.
A copy of the customised script crdbcse_custom_schemas.sql is attached to this article.

NOTES:

Customise directory names as required.
The startup nomount pfile command references file initDBCSE.ora, per the case change above.
"user" lines have been added to create passwords for the SYS and SYSTEM users. Modify sys_password and system_password as required.
Whatever is the character set of the database being exported that should also be used to create the new DBCSE database instead of us7ascii in the default script e.g. WE8ISO8859P1. This can be checked on the source database using SQL:
select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET'
The sections that create rollback segment r1, the tablespace IEFSENCY_ROLL, rollback segments roll1, roll2, roll3, roll4 have been removed because Automatic Undo Management is being used.
"AUTOEXTEND ON" is added in the sections that create tablespaces IEFSENCY_IDX, IEFSENCY_TAB and IEFSENCY_TEMP.

Import process

Copy file ENCY.DMP to directory DATA_DUMP_DIR:
$ cp ENCY.DMP /opt/oracle/product/19c/dbhome_1/rdbms/log

Run the import command:
impdp system/system_password DUMPFILE=ENCY.DMP SCHEMAS=ency

===
Import: Release 19.0.0.0.0 - Production on Wed Sep 6 03:35:56 2023
Version 19.3.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** DUMPFILE=ENCY.DMP SCHEMAS=ency
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ENCY" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "ENCY"."DOBJ" 2.987 GB 13047113 rows
. . imported "ENCY"."DASC" 1.215 GB 21609681 rows
. . imported "ENCY"."DNAME" 267.5 MB 1461886 rows
. . imported "ENCY"."DTXT" 148.7 MB 1573732 rows
. . imported "ENCY"."SPRP" 3.251 MB 41332 rows
. . imported "ENCY"."SASC" 2.398 MB 25920 rows
. . imported "ENCY"."STRG" 219.6 KB 5623 rows
. . imported "ENCY"."SDIV" 164.5 KB 2278 rows
. . imported "ENCY"."SOBJ" 139.5 KB 1830 rows
. . imported "ENCY"."DSUBEX" 138.3 KB 5224 rows
. . imported "ENCY"."DMAX" 14.17 KB 14 rows
. . imported "ENCY"."DMDL" 15.03 KB 16 rows
. . imported "ENCY"."DCKOID" 9.890 KB 7 rows
. . imported "ENCY"."DIRENCY" 9.453 KB 1 rows
. . imported "ENCY"."DUSR" 8.617 KB 2 rows
. . imported "ENCY"."DSUBID" 8.445 KB 4 rows
. . imported "ENCY"."DIRUSER" 8.171 KB 2 rows
. . imported "ENCY"."DCKOHIST" 7.570 KB 29 rows
. . imported "ENCY"."DSUBDF" 7.109 KB 5 rows
. . imported "ENCY"."DENCY" 6.656 KB 1 rows
. . imported "ENCY"."DIRLOGON" 6.445 KB 2 rows
. . imported "ENCY"."DIRXCPID" 6.062 KB 41 rows
. . imported "ENCY"."DXCPID" 6.062 KB 41 rows
. . imported "ENCY"."DGRPUS" 0 KB 0 rows
. . imported "ENCY"."DMDLUS" 0 KB 0 rows
. . imported "ENCY"."DSETDF" 0 KB 0 rows
. . imported "ENCY"."DSETID" 0 KB 0 rows
. . imported "ENCY"."DSUBUS" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Wed Sep 6 03:42:27 2023 elapsed 0 00:06:28
===

There is no need to be concerned about the error:
ORA-31684: Object type USER:"ENCY" already exists

Configure the Linux CSE

As the designated CSE Linux administrator user run cse_config to generate a new iefmd.ini file on the Linux CSE, using the documented steps here: CSE on Linux Configuration
For "Encyclopedia Database Configuration" and "Coordination Database Configuration" choose either the Update or Skip options. If the current CSE from which the schema was exported is

already at level Gen 8.63 i.e. the same level as the new Linux CSE, then the Skip option should be used.
at an earlier Gen 8.6 version or earlier Gen 7.x or 8.x version, then the Update option should be used.

NOTE: For either of those options even though it still prompts for Encyclopedia ID, Name, and Description, those values will not be updated.

Start the CSE iefmd process using the documented steps here: Start a CSE on UNIX or Linux
Verify that the CSE is accessible via the CSE Clients and the expected models are visible.

Migrate Gen CSE Oracle database to new Gen Linux CSE

Article ID: 272915

Updated On:

Products

Issue/Introduction

Environment

Resolution

Export the current CSE database

Preparation

Export process using Full Transportable option

Import into the new Linux CSE database

Preparation

Import process using Full Transportable option.

Configure the Linux CSE

Additional Information

An alternative approach

Export the current CSE database user id "ency"

Import into the new Linux CSE database

Preparation

Import process

Configure the Linux CSE

Related article

Attachments

Feedback