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