{"id":886,"date":"2022-12-09T10:10:07","date_gmt":"2022-12-09T10:10:07","guid":{"rendered":"http:\/\/wp.chaoyu.nl\/?p=886"},"modified":"2024-04-08T14:34:14","modified_gmt":"2024-04-08T12:34:14","slug":"how-to-import-data-dump-into-oracle-oci-autonomous-database-atp","status":"publish","type":"post","link":"https:\/\/chaoyu.nl\/?p=886","title":{"rendered":"How to import Data d(p)ump into Oracle OCI Autonomous Database"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/21\/sutil\/oracle-datapump-import-utility.html#GUID-BBB3CB15-B136-423F-B1EA-BF93A935793F\">https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/21\/sutil\/oracle-datapump-import-utility.html#GUID-BBB3CB15-B136-423F-B1EA-BF93A935793F<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sutil\/oracle-data-pump-export-utility.html#GUID-8B512637-E259-4F20-B7C9-DCD0BB501D09\" data-type=\"link\" data-id=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sutil\/oracle-data-pump-export-utility.html#GUID-8B512637-E259-4F20-B7C9-DCD0BB501D09\">documentation <\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/oracle-base.com\/articles\/12c\/data-pump-enhancements-12cr2\" data-type=\"link\" data-id=\"https:\/\/oracle-base.com\/articles\/12c\/data-pump-enhancements-12cr2\">more information<\/a> on oracle-base<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" style=\"font-style:normal;font-weight:700\">PreRequisite<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>data dump export from your<strong> <\/strong>source<strong> <\/strong>database<\/li>\n\n\n\n<li>a host  (<a rel=\"noreferrer noopener\" href=\"https:\/\/www.linkedin.com\/pulse\/run-your-oracle-apex-apps-autonomous-database-own-domain-kurt-liu\/\" data-type=\"URL\" data-id=\"https:\/\/www.linkedin.com\/pulse\/run-your-oracle-apex-apps-autonomous-database-own-domain-kurt-liu\/\" target=\"_blank\">read here<\/a>) with sqlplus installed ( try OCI vm instance with image Oracle Cloud Developer Linux, it has sqlplus , sqlcl pre installed) and able to connect to Autonomous Database<\/li>\n\n\n\n<li>an Oracle OCI object storage bucket (upload your database dump to this bucket, your bucket can be private, no need to set it public. Once uploaded, copy the link via object details button, the link is needed later)<\/li>\n\n\n\n<li>an Oracle OCI user who has MANAGE Buckets\/Objects rights to this bucket<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" style=\"text-transform:uppercase\"><strong>Step !: Create an Auth TOKEN from OCI user<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">save the token somewhere safe (you can copy it during creation, once created it can <strong>NOT <\/strong>be displayed anymore)<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"3188\" height=\"694\" src=\"http:\/\/wp.chaoyu.nl\/wp-content\/uploads\/2022\/12\/image.png\" alt=\"\" class=\"wp-image-915\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<\/p>\n\n\n\n<h3 class=\"wp-block-heading has-black-color has-text-color\" style=\"text-transform:uppercase\"><strong>Step 2: use sqlplus login to your autonomous database using !!!&lt;ADMIN&gt; account and run<\/strong><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql line-numbers\">begin\n   dbms_cloud.create_credential(credential_name =&gt; 'OBJECT_STORE_CRED'\n                               ,username        =&gt; '${your objectuser}'\n                               ,password        =&gt; '${your auth token}');\nend;\n\/\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code lang=\"sql\" class=\"language-sql line-numbers\"># to list all created credentials\nSELECT credential_name, username, comments FROM all_credentials;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" style=\"text-transform:uppercase\"><strong>Step 3: create .par file<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">On the host machine, create a .par file e.g <em><strong>my_import.par<\/strong><\/em> and type the following content ( you can also work without the par file, but then u need to type everything all at once )<\/p>\n\n\n\n<h5 class=\"wp-block-heading\" style=\"text-transform:uppercase\"><strong>Infor about the parameters:<\/strong><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">Schemas is optional if your export is on schema level, remap_tablespace optional , remap_schema is optional as well. content optional, EXCLUDE is optional, table_exists_action optional. logfile optional<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"> <a rel=\"noreferrer noopener\" href=\"https:\/\/docs.oracle.com\/database\/121\/SUTIL\/GUID-96AB8902-B838-43D2-95B0-D35C7589ADC6.htm#SUTIL955\" data-type=\"URL\" data-id=\"https:\/\/docs.oracle.com\/database\/121\/SUTIL\/GUID-96AB8902-B838-43D2-95B0-D35C7589ADC6.htm#SUTIL955\" target=\"_blank\">read more here<\/a><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"bash\" class=\"language-bash line-numbers\">directory=DATA_PUMP_DIR    \ncredential=OBJECT_STORE_CRED\nschemas=${SOURCE_SCHEMA}\nremap_tablespace=${SOURCE_TABLESPACE}:${TARGET_TABLESPACE}\nremap_schema=${SOURCE_SCHEMA}:${TARGET_SCHEMA}\ndumpfile=${URL To your Dump file on your object storage}\ncontent=DATA_ONLY\nEXCLUDE=CONSTRAINT,REF_CONSTRAINT,INDEX \nTABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]\nLOGFILE=my_import_log.log<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>SKIP<\/strong>&nbsp;is default value. If SKIP is used then table replacing is not done.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>APPEND&nbsp;&nbsp;<\/strong>loads rows from the export files and leaves target existing rows unchanged.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>TRUNCATE<\/strong>&nbsp;deletes existing rows in target table and then loads rows from the export.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>REPLACE<\/strong>&nbsp;drops the existing table in the target and then creates and loads it from the export.<\/p>\n\n\n\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-8f761849 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:100%\">\n<p class=\"wp-block-paragraph\">&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<\/p>\n<\/div>\n<\/div>\n\n\n\n<h3 class=\"wp-block-heading\" style=\"text-transform:uppercase\"><strong>Step 4: execute import command <\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">when you have this .par file , type (It needs User ORACLE and export TNS_ADMIN folder )<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql line-numbers\">impdp userid=ADMIN\/${password}@${tns_name} parfile=${path to your .par file}<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">before you run this, you probably need to do the following to disable triggers and constraints if you do a data only import<\/p>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code lang=\"sql\" class=\"language-sql line-numbers\">select t.*\nfrom   user_constraints t;\n\nselect *\nfrom   user_triggers t;\n\n-- disable all Foreign key constraints\ndeclare\n   l_sql clob;\nbegin\n   for rec in (select t.constraint_name\n                     ,t.table_name\n               \n               from   user_constraints t\n               where  t.constraint_type in ('R')\n               and    t.status = 'ENABLED'\n               and    t.table_name not in ('flyway_schema_history'))\n   loop\n      l_sql := q'[ALTER TABLE ]' || rec.table_name || ' disable CONSTRAINT ' || rec.constraint_name;\n     -- dbms_output.put_line(l_sql);\n      execute immediate l_sql;\n   end loop;\n   for rec in (select t.constraint_name\n                     ,t.table_name\n               \n               from   user_constraints t\n               where  t.constraint_type in ('P'\n                                           ,'U')\n               and    t.status = 'ENABLED'\n               and    t.table_name not in ('flyway_schema_history'))\n   loop\n      l_sql := q'[ALTER TABLE ]' || rec.table_name || ' disable CONSTRAINT ' || rec.constraint_name;\n     -- dbms_output.put_line(l_sql);\n      execute immediate l_sql;\n   end loop;\n\nend;\n\n\n\n\n-- Enable all constraints\ndeclare l_sql clob;\nbegin\n   -- first primary key and unique key\n   for rec in (select t.constraint_name\n                     ,t.table_name\n               \n               from   user_constraints t\n               where  t.constraint_type in ('P'\n                                           ,'U')\n               and    t.status = 'DISABLED'\n               and    t.table_name not in ('flyway_schema_history'))\n   loop\n      l_sql := q'[ALTER TABLE ]' || rec.table_name || ' enable CONSTRAINT ' || rec.constraint_name;\n      --dbms_output.put_line(l_sql);\n      execute immediate l_sql;\n   end loop;\n\n   for rec in (select t.constraint_name\n                     ,t.table_name\n               \n               from   user_constraints t\n               where  t.constraint_type in ('R')\n               and    t.status = 'DISABLED'\n               and    t.table_name not in ('flyway_schema_history'))\n   loop\n      l_sql := q'[ALTER TABLE ]' || rec.table_name || ' enable CONSTRAINT ' || rec.constraint_name;\n      --dbms_output.put_line(l_sql);\n      execute immediate l_sql;\n   end loop;\n\nend;\n\n-- disable all triggers\ndeclare\n   l_sql clob;\nbegin\n   for rec in (select t.trigger_name\n               from   user_triggers t\n               where  t.status = 'ENABLED')\n   loop\n      l_sql := 'ALTER TRIGGER ' || rec.trigger_name || ' DISABLE';\n      --dbms_output.put_line(l_sql);\n      execute immediate l_sql;\n   end loop;\nend;\n-- enable all triggers\ndeclare\n   l_sql clob;\nbegin\n   for rec in (select t.trigger_name\n               from   user_triggers t\n               where  t.status = 'DISABLED')\n   loop\n      l_sql := 'ALTER TRIGGER ' || rec.trigger_name || ' ENABLE';\n      dbms_output.put_line(l_sql);\n      execute immediate l_sql;\n   end loop;\nend;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" style=\"text-transform:uppercase\"><strong>Step 5: access the log file<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">As long as your connection is good, even there are errors during import, it should finish with a log file. You can check the log file by running this sql. <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql line-numbers\">   select *\n   from   dbms_cloud.list_files('DATA_PUMP_DIR')\n   order  by last_modified desc;<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\" style=\"text-transform:uppercase\"><strong>Store your logfile to OCI object storage<\/strong><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">run this plsql code to upload log file to your bucket storage and so that you can download it and view it in detail<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql line-numbers\">\nbegin\n   dbms_cloud.put_object('OBJECT_STORE_CRED'\n                        ,'${URL}'\n                        ,'DATA_PUMP_DIR'\n                        ,'${file name you see in previous sql}');\nend;\n\/\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">e.g ${URL} &#8216;https:\/\/objectstorage.eu-amsterdam1.oraclecloud.com\/n\/aosdfasdf\/b\/DD\/o\/${anyname}.log&#8217;<\/p>\n\n\n\n<h1 class=\"wp-block-heading\"><strong>Troubleshooting<\/strong><\/h1>\n\n\n\n<p class=\"wp-block-paragraph\">if having ORA-28759: failure to open file<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">check sqlnet.ora file in \/op\/oracle\/network\/admin folder value of wallet location<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/21\/sutil\/oracle-datapump-import-utility.html#GUID-BBB3CB15-B136-423F-B1EA-BF93A935793F documentation more information on oracle-base PreRequisite &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;- Step !: Create an Auth TOKEN from OCI user save the token somewhere safe (you can copy it during creation, once created it can NOT be displayed anymore) &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;- Step 2: use sqlplus login to your autonomous database using !!!&lt;ADMIN&gt; account and run &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;- Step 3: create&#8230;<\/p>\n","protected":false},"author":1,"featured_media":811,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[41,40,43,42],"class_list":["post-886","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oci","tag-data-dump","tag-data-pump","tag-import-data-dump-into-autonomous-database","tag-oci-autonomous-database"],"_links":{"self":[{"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/posts\/886","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=886"}],"version-history":[{"count":57,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/posts\/886\/revisions"}],"predecessor-version":[{"id":1806,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/posts\/886\/revisions\/1806"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/media\/811"}],"wp:attachment":[{"href":"https:\/\/chaoyu.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=886"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=886"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=886"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}