Oracle Cloud: Some issues with the old IMP & EXP utilities
As I am still helping one customer to explore the Oracle Cloud, I am learning myself something new every time and I am also facing some issues on that way.
The last one is related to the old IMP and EXP tools.
One of the automated steps is to export some tables from one database onto customer’s local client and then to import them into another database. Customer wants to keep it so running and wants to avoid any possible change in the application, and that’s why these old utilities are still used instead of the ExpDp & ImpDp. Otherwise, if they would like to use ExpDp & ImpDp, they may need to implement some other way of transfering those export dump files from one database server to the other one, and then import them there. One of the options could be to use DBMS_CLOUD.PUT_OBJECT and then GET_OBJECT, but that’s not the main topic of this post.
This setup works with on-premises database, but it doesn’t work completely with an Oracle Cloud ATP database.
First issue arose by establishing the connection to the database – running the exp tool, this error message has been displayed:
Export: Release 19.0.0.0.0 - Production on Wed Nov 11 17:48:31 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
EXP-00056: ORACLE error 56605 encountered
ORA-56605: DRCP: Session switching and migration not allowed
EXP-00000: Export terminated unsuccessfully
The description of this error is:
"ORA-56605: DRCP: Session switching and migration not allowed".
// *Cause: Application tried to switch or migrate session across connections. // *Action: This usage is irrelevant in the Database Resident connection pooling // context and is not supported. Release existing session.
The problem was the parameter SERVER=POOLED set within tnsnames.ora for that connection string, which allows the usage of the DRCP. I copied that net name and created a new one without that parameter, and the problem has been solved. According to the Oracle Support, also Forms applications could face this issue and DRCP connection shouldn’t be used in that case. Note: I didn’t tested any Forms Application with an Oracle ATP database, so I cannot confirm it.
After solving that issue, the next one arose trying to export some tables using the exp utility:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported
About to export specified tables via Conventional Path … EXP-00111: Table SYNC_DEV resides in an Encrypted Tablespace DATA and will not be exported EXP-00111: Table SYNC_MAIN resides in an Encrypted Tablespace DATA and will not be exported EXP-00111: Table SYNC_PARAM resides in an Encrypted Tablespace DATA and will not be exported … . . exporting table EXP_PARAMETERS 1 rows exported
Export terminated successfully with warnings.
According to Oracle Support, the export from the tables which reside in an encrypted tablespace (TDE tablespace is enabled by default in the ATP database) is not supported for the exp utility. Now the customer really has to change this step and to use ExpDp & ImpDp.