Sample Header Ad - 728x90

Exporting from Oracle Cloud, importing on local database

0 votes
0 answers
428 views
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?
Asked by natenjo (1 rep)
Apr 2, 2020, 06:20 AM