Thursday 10 June 2021

OCI - Moving Data from Autonomous Databases to on Premises database using impdp

OCI - Moving Data from Autonomous Databases to on Premises database using impdp 

  • Login in to autonomous database server with admin user.

C:\Users\varun>sqlplus admin/Cloud$123456@orcladb_medium


SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 7 19:16:09 2021

Version 19.3.0.0.0


Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Last Successful login time: Mon Jun 07 2021 17:53:17 +05:30


Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.5.0.0.0

  • Provide privileges' to schema to create directory

 

SQL> GRANT CREATE ANY DIRECTORY TO soe;




Grant succeeded.



SQL> GRANT DROP ANY DIRECTORY TO soe;



Grant succeeded.

  • Create root directory first , So inside root directory we will create dump directory for schema bucket .

 


SQL> CREATE OR REPLACE DIRECTORY ROOT_DIR AS '';

Directory created.

SQL> SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='ROOT_DIR';

OWNER
--------------------------------------------------------------------------------
DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
ORIGIN_CON_ID
-------------
SYS
ROOT_DIR
/u03/dbfs/BE1F7D7E50E2922EE0532210000A17B4/data/
           42


  •   Create directory staging2 after login to soe schemas so any directory stored inside data directory


SQL>  CREATE DIRECTORY staging2 AS 'stage2';




 

C:\Users\varun>expdp soe/Abc#123456789@orcladb_medium directory=staging2 dumpfile=adsoe%U.dmp  parallel=2 encryption_pwd_prompt=yes filesize=1G logfile=adsoe_export.log

Export: Release 19.0.0.0.0 - Production on Mon Jun 7 19:27:35 2021
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Encryption Password:  ( Write and rember encypted password it will be used for impdp  on premises database)
Starting "SOE"."SYS_EXPORT_SCHEMA_01":  soe/********@orcladb_medium directory=staging2 dumpfile=adsoe%U.dmp parallel=2 encryption_pwd_prompt=yes filesize=1G logfile=adsoe_export.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SOE"."ORDER_ITEMS"                         170.4 MB 3735896 rows
. . exported "SOE"."ORDERS"                              114.9 MB 1352070 rows
. . exported "SOE"."INVENTORIES"                         15.27 MB  902171 rows
. . exported "SOE"."LOGON"                               9.065 MB  442208 rows
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "SOE"."CUSTOMERS"                           4.983 MB   45703 rows
. . exported "SOE"."ADDRESSES"                           4.084 MB   55703 rows
. . exported "SOE"."CARD_DETAILS"                        2.402 MB   55703 rows
. . exported "SOE"."PRODUCT_DESCRIPTIONS"                224.1 KB    1000 rows
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
. . exported "SOE"."PRODUCT_INFORMATION"                 186.4 KB    1000 rows
. . exported "SOE"."EMP"                                 51.39 KB     879 rows
. . exported "SOE"."SALES"                               40.79 KB    1000 rows
. . exported "SOE"."WAREHOUSES"                          36.03 KB    1000 rows
. . exported "SOE"."JOB_HISTORY"                         14.61 KB     246 rows
. . exported "SOE"."JOBS"                                5.984 KB      23 rows
. . exported "SOE"."ORDERENTRY_METADATA"                 5.617 KB       4 rows
. . exported "SOE"."DEPT"                                5.640 KB       7 rows
. . exported "SOE"."V"                                       0 KB       0 rows
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Master table "SOE"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SOE.SYS_EXPORT_SCHEMA_01 is:
  /u03/dbfs/BE1F7D7E50E2922EE0532210000A17B4/data/stage2/adsoe01.dmp
  /u03/dbfs/BE1F7D7E50E2922EE0532210000A17B4/data/stage2/adsoe02.dmp
Job "SOE"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Jun 7 13:58:38 2021 elapsed 0 00:00:59
C:\Users\varun>
 

  • Check expdp dump files inside directory
SQL> col OBJECT_NAME for a60
SQL> SELECT OBJECT_NAME, BYTES/1024/1024 MB FROM DBMS_CLOUD.LIST_FILES('STAGING2') WHERE  OBJECT_NAME LIKE 'adsoe%';

OBJECT_NAME                                                          MB
------------------------------------------------------------ ----------
adsoe_export.log                                             .003703117
adsoe01.dmp                                                   .49609375
adsoe02.dmp                                                   322.59375

SQL> sho user
USER is "SOE"
SQL>


  • Copy the dump files from the directory in staging2 database to the staging bucket
  • Note: Provide privilege to staging2 directory by admin user

BEGIN
DBMS_CLOUD.PUT_OBJECT (
CREDENTIAL_NAME => 'ADMIN_CRED_NAME',
OBJECT_URI => 'https://objectstorage.ap-mumbai-1.oraclecloud.com/n/bmdsoh48o0kf/b/bucket-20210415-1806/o/adsoe01.dmp', 
DIRECTORY_NAME => 'staging2',
FILE_NAME => 'adsoe01.dmp');
END;
/ 
BEGIN
DBMS_CLOUD.PUT_OBJECT (
CREDENTIAL_NAME => 'ADMIN_CRED_NAME',
OBJECT_URI => 'https://objectstorage.ap-mumbai-1.oraclecloud.com/n/bmdsoh48o0kf/b/bucket-20210415-1806/o/adsoe02.dmp', 
DIRECTORY_NAME => 'staging2',
FILE_NAME => 'adsoe02.dmp');
END;
/ 



  • Download file to on premises local directory



IMPDP From autonomous db to on premises database
  •  Connect on premises database and create directory 

 

D:\ud\WINDOWS.X64_193000_db_home\bin>sqlplus sys/system123@pdbtest as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 8 15:29:04 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 pdbtest                           READ WRITE NO
SQL> CREATE DIRECTORY STAGING_DIR AS 'C:\Users\varun\OneDrive\Desktop\oci';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY STAGING_DIR TO soe;

Grant succeeded.

-------------------------------------------
SQL> CREATE USER SOE2 IDENTIFIED BY soe2 QUOTA unlimited ON users;

User created.

SQL> GRANT CONNECT TO soe2;

Grant succeeded.

SQL> GRANT CREATE TABLE TO soe2;

Grant succeeded.

SQL> GRANT CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE TO soe2;

Grant succeeded.

SQL> GRANT ALTER SESSION TO soe2;

Grant succeeded.


SQL> GRANT READ, WRITE ON DIRECTORY STAGING_DIR TO soe2;

Grant succeeded.

SQL> GRANT CREATE CREDENTIAL TO soe2;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_LOCK TO soe2;

Grant succeeded.

---------------------------------------------------------------------------

C:\Windows\system32>tnsping pdbtest

TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 09-JUN-2021 19:42:21

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
D:\ud\WINDOWS.X64_193000_db_home\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-H1NR6BO)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDBTEST)))
OK (20 msec)

  • IMPDP to on premesis database  pdbtest

 

C:\Windows\system32>impdp \"sys/system@pdbtest as sysdba\" directory=STAGING_DIR schemas=soe remap_schema=soe:soe2 dumpfile=adsoe01.dmp,adsoe02.dmp transform=segment_attributes:n encryption_pwd_prompt=yes

Import: Release 19.0.0.0.0 - Production on Wed Jun 9 19:44:05 2021
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Encryption Password:
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "sys/********@pdbtest AS SYSDBA" directory=STAGING_DIR schemas=soe remap_schema=soe:soe2 dumpfile=adsoe01.dmp,adsoe02.dmp transform=segment_attributes:n encryption_pwd_prompt=yes
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SOE2"."ORDER_ITEMS"                        170.4 MB 3735896 rows
. . imported "SOE2"."CUSTOMERS"                          4.983 MB   45703 rows
. . imported "SOE2"."V"                                      0 KB       0 rows
. . imported "SOE2"."DEPT"                               5.640 KB       7 rows
. . imported "SOE2"."PRODUCT_DESCRIPTIONS"               224.1 KB    1000 rows
. . imported "SOE2"."SALES"                              40.79 KB    1000 rows
. . imported "SOE2"."JOB_HISTORY"                        14.61 KB     246 rows
. . imported "SOE2"."INVENTORIES"                        15.27 MB  902171 rows
. . imported "SOE2"."WAREHOUSES"                         36.03 KB    1000 rows
. . imported "SOE2"."EMP"                                51.39 KB     879 rows
. . imported "SOE2"."ORDERENTRY_METADATA"                5.617 KB       4 rows
. . imported "SOE2"."PRODUCT_INFORMATION"                186.4 KB    1000 rows
. . imported "SOE2"."CARD_DETAILS"                       2.402 MB   55703 rows
. . imported "SOE2"."JOBS"                               5.984 KB      23 rows
. . imported "SOE2"."LOGON"                              9.065 MB  442208 rows
. . imported "SOE2"."ORDERS"                             114.9 MB 1352070 rows
. . imported "SOE2"."ADDRESSES"                          4.084 MB   55703 rows
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
ORA-39083: Object type PROCOBJ:"SOE2"."SOE_CREDENTIAL" failed to create with error:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'DBMS_CLOUD.CREATE_CREDENTIAL' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

Failing sql is:
BEGIN
dbms_cloud.create_credential('"SOE_CREDENTIAL"', username=>'SOE', password=>'NULL', enabled=>TRUE, windows_domain=>NULL, comments=>'{"comments":"Created via DBMS_CLOUD.create_credential"}');COMMIT; END;

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
ORA-39082: Object type PACKAGE BODY:"SOE2"."ORDERENTRY" created with compilation warnings

Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Wed Jun 9 19:47:13 2021 elapsed 0 00:03:00


C:\Windows\system32>


Check error in impdp and look into solution. in our case we compile the schema.

 


SQL> select count(*),object_type,status from dba_objects where owner='SOE2' group by status,object_type;

  COUNT(*) OBJECT_TYPE             STATUS
---------- ----------------------- -------
         7 SEQUENCE                VALID
         1 FUNCTION                VALID
         1 PACKAGE BODY            INVALID
         1 PROCEDURE               VALID
        25 TABLE                   VALID
         2 VIEW                    VALID
        33 INDEX                   VALID
         1 PACKAGE                 VALID

8 rows selected.


SQL> EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'SOE2');

PL/SQL procedure successfully completed.

SQL> select count(*),object_type,status from dba_objects where owner='SOE2' group by status,object_type;

  COUNT(*) OBJECT_TYPE             STATUS
---------- ----------------------- -------
         7 SEQUENCE                VALID
         1 FUNCTION                VALID
         1 PACKAGE BODY            VALID
         1 PROCEDURE               VALID
        25 TABLE                   VALID
         2 VIEW                    VALID
        33 INDEX                   VALID
         1 PACKAGE                 VALID

8 rows selected.

SQL>



Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)