0

I am currently experiemting with oracle databases. I have a free tier at Oracle Cloud, with a free instance of autonomous database. As I like to have my backups locally I did an export, basically following this tutorial, but on a local VM.

Export works fine and I moved it to the Oracle object storage, with the script described in the blog post.

Next, I wanted to import it into a local Oracle database I have setup on a VM on my local PC. (Guest operating System: Oracle Linux, DB: Oracle 18 Enterprise)

How I do the export from Oracle cloud:

[root@oracle-linux-vm ~]# expdp admin@dbname_high TABLES=TABLE1 data_options=group_partition_table_data parallel=1 dumpfile=table.dmp

Export: Release 18.0.0.0.0 - Production on Thu Apr 2 08:05:19 2020
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "ADMIN"."SYS_EXPORT_TABLE_01":  admin/********@dbname_high TABLES=TABLE1 data_options=group_partition_table_data parallel=1 dumpfile=table.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ADMIN"."TABLE1"                            5.078 KB       2 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "ADMIN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ADMIN.SYS_EXPORT_TABLE_01 is:
  /u03/dbfs/A0AF0C3CCE4D3568E0535A18000AD882/data/dpdump/table.dmp
Job "ADMIN"."SYS_EXPORT_TABLE_01" successfully completed at Thu Apr 2 06:07:37 2020 elapsed 0 00:02:06

[root@oracle-linux-vm ~]#


I download the file from Oracle object storage and move it to the DATA_PUMP_DIR Folder

[root@oracle-linux-db-ee ~]# ls -al /opt/oracle/admin/ORCLCDB/dpdump
total 172
drwxrwxrwx. 2 oracle oinstall     23 Apr  2 06:12 .
drwxr-x---. 6 oracle oinstall     64 Mar 31 06:41 ..
-rw-r--r--. 1 oracle oinstall 176128 Apr  2 06:10 table.dmp
[root@oracle-linux-db-ee ~]#

When trying to import the data, it always fails.

In my local DB/VM:

[root@oracle-linux-db-ee ~]# impdp admin/pwd4admin@ORCLCDB  dumpfile=table.dmp logfile=1.log TABLES=TABLE1
DIRECTORY=DATA_PUMP_DIR

Import: Release 18.0.0.0.0 - Production on Thu Apr 2 05:31:46 2020
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
ORA-39002: invalid operation

Looking at the logfile, I can't get a hint what is going wrong:

[root@oracle-linux-db-ee ~]# cat /opt/oracle/admin/ORCLCDB/dpdump/1.log
;;;
Import: Release 18.0.0.0.0 - Production on Thu Apr 2 06:16:52 2020
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "ADMIN"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Job "ADMIN"."SYS_IMPORT_TABLE_01" successfully completed at Thu Apr 2 06:17:02 2020 elapsed 0 00:00:06
Job "ADMIN"."SYS_IMPORT_TABLE_01" successfully completed at Thu Apr 2 06:17:02 2020 elapsed 0 00:00:07

The schema "admin" is already created manually on my local db.

Any ideas what I am doing wrong?

  • Are you sure that the export file has transfered by scp or by another tool ni binary mode ? If yes, it could be a time zone setting issue: try to run on target database exec dbms_dst.begin_upgrade(<version>) with the instance in startup upgrade mode. – pifor Apr 2 at 7:14
  • pifor hat the correct hint. The the servers are running different timezone files. 'SELECT * FROM V$TIMEZONE_FILE;' returns version 33 on the Oracle cloud database. On my local database I it returns 31 :-( .. As I don't have Oracle subscription, as this is a project I am experimenting with in my free time and wanted to learn some oracle (cloud) for a free time project I guess I will have to use spool to backup my data from oracle cloud... I am afraid that they will close my account and I loose my data... – natenjo Apr 4 at 16:57
  • To resolve this Oracle Patch 28852325 would be needed (from my understanding) – natenjo Apr 4 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.