Oracle Database Commands and Practical Examples: OCI- Moving data to Autonomous database using expdp -impdp

Data migration from on premises to autonomous cloud database 19c using expdp -impdp

Connect on premises database

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

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 19 15:32:56 2021

Version 19.3.0.0.0

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 PDB1 READ WRITE NO

SQL>

Downloads and install install_adb_advisor.sql
Follow Doc (Oracle Autonomous Database Schema Advisor (Doc ID 2462677.1))

Connect PDB database

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

3 PDB1 READ WRITE NO

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

Connect with sys user and follow the instructions.
The script takes in two parameters - <Advisor Schema> which is the Schema owner for ADB Schema Advisor; and <Password> the schema owner’s password.

sqlplus SYS AS SYSDBA @install_adb_advisor.sql <Advisor Schema> <Password>

 
D:\ud\WINDOWS.X64_193000_db_home\bin>sqlplus sys/system123@pdb1 as sysdba @D:\ud\install_adb_advisor.sql ADB_ADVISOR Abc#123456789



SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 19 15:36:45 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

old   1: CREATE USER &&1 identified by "&&2"
new   1: CREATE USER ADB_ADVISOR identified by "Abc#123456789sqlplus"

User created.

old   1: GRANT CREATE TABLE, CREATE SESSION, CREATE PROCEDURE TO &&1
new   1: GRANT CREATE TABLE, CREATE SESSION, CREATE PROCEDURE TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON V_$VERSION TO &&1
new   1: GRANT SELECT ON V_$VERSION TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON V_$PARAMETER TO &&1
new   1: GRANT SELECT ON V_$PARAMETER TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON V_$INSTANCE TO &&1
new   1: GRANT SELECT ON V_$INSTANCE TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON V_$DATABASE TO &&1
new   1: GRANT SELECT ON V_$DATABASE TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON NLS_DATABASE_PARAMETERS TO &&1
new   1: GRANT SELECT ON NLS_DATABASE_PARAMETERS TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_LOBS TO &&1
new   1: GRANT SELECT ON DBA_LOBS TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_INDEXES TO &&1
new   1: GRANT SELECT ON DBA_INDEXES TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_OBJECTS TO &&1
new   1: GRANT SELECT ON DBA_OBJECTS TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_SYNONYMS TO &&1
new   1: GRANT SELECT ON DBA_SYNONYMS TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_DEPENDENCIES TO &&1
new   1: GRANT SELECT ON DBA_DEPENDENCIES TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_TABLES TO &&1
new   1: GRANT SELECT ON DBA_TABLES TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_TAB_COLS TO &&1
new   1: GRANT SELECT ON DBA_TAB_COLS TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_CONSTRAINTS TO &&1
new   1: GRANT SELECT ON DBA_CONSTRAINTS TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_DB_LINKS TO &&1
new   1: GRANT SELECT ON DBA_DB_LINKS TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_PROFILES TO &&1
new   1: GRANT SELECT ON DBA_PROFILES TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_USERS TO &&1
new   1: GRANT SELECT ON DBA_USERS TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_TYPES TO &&1
new   1: GRANT SELECT ON DBA_TYPES TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_TAB_PARTITIONS TO &&1
new   1: GRANT SELECT ON DBA_TAB_PARTITIONS TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_TAB_SUBPARTITIONS TO &&1
new   1: GRANT SELECT ON DBA_TAB_SUBPARTITIONS TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_EXTERNAL_TABLES TO &&1
new   1: GRANT SELECT ON DBA_EXTERNAL_TABLES TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_SEGMENTS TO &&1
new   1: GRANT SELECT ON DBA_SEGMENTS TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_MVIEW_LOGS TO &&1
new   1: GRANT SELECT ON DBA_MVIEW_LOGS TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_FEATURE_USAGE_STATISTICS TO &&1
new   1: GRANT SELECT ON DBA_FEATURE_USAGE_STATISTICS TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_XML_TABLES TO &&1
new   1: GRANT SELECT ON DBA_XML_TABLES TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_XML_TAB_COLS TO &&1
new   1: GRANT SELECT ON DBA_XML_TAB_COLS TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_XML_SCHEMAS TO &&1
new   1: GRANT SELECT ON DBA_XML_SCHEMAS TO ADB_ADVISOR

Grant succeeded.

old   1: GRANT SELECT ON DBA_ILMOBJECTS TO &&1
new   1: GRANT SELECT ON DBA_ILMOBJECTS TO ADB_ADVISOR

Grant succeeded.

old   1: ALTER SESSION SET CURRENT_SCHEMA = &&1
new   1: ALTER SESSION SET CURRENT_SCHEMA = ADB_ADVISOR

Session altered.

TRUNCATE TABLE ADB_ADVISOR_CODES_TMP
               *
ERROR at line 1:
ORA-00942: table or view does not exist


TRUNCATE TABLE ADB_ADVISOR_INFO_TMP
               *
ERROR at line 1:
ORA-00942: table or view does not exist


TRUNCATE TABLE ADB_ADVISOR_OBJECTS_TMP
               *
ERROR at line 1:
ORA-00942: table or view does not exist


TRUNCATE TABLE ADB_ADVISOR_REJECTS_TMP
               *
ERROR at line 1:
ORA-00942: table or view does not exist


TRUNCATE TABLE ADB_ADVISOR_SCHEMAS_TMP
               *
ERROR at line 1:
ORA-00942: table or view does not exist


TRUNCATE TABLE ADB_ADVISOR_SYSDEPEND_TMP
               *
ERROR at line 1:
ORA-00942: table or view does not exist


TRUNCATE TABLE ADB_ADVISOR_SYSOBJECTS_TMP
               *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP TABLE ADB_ADVISOR_CODES_TMP
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP TABLE ADB_ADVISOR_INFO_TMP
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP TABLE ADB_ADVISOR_OBJECTS_TMP
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP TABLE ADB_ADVISOR_REJECTS_TMP
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP TABLE ADB_ADVISOR_SCHEMAS_TMP
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP TABLE ADB_ADVISOR_SYSDEPEND_TMP
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP TABLE ADB_ADVISOR_SYSOBJECTS_TMP
           *
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Index created.


Index created.


Index created.


Index created.


Package created.


Package body created.

No errors.
SQL>

Connect database schema advisor and run advisor report package and examine the report.

Note : Remove if any issue occur

TRUNCATE TABLE ADB_ADVISOR_SYSOBJECTS_TMP;
DROP INDEX ADB_ADVISOR_SYSOBJECTS_TMP_PK1;
TRUNCATE TABLE ADB_ADVISOR_REJECTS_TMP;
DROP INDEX ADB_ADVISOR_REJECTS_TMP_UK1;

 SQL> conn ADB_ADVISOR/Abc#123456789@pdb1Connected.
SQL> sho user
USER is "ADB_ADVISOR"
SQL> SET SERVEROUTPUT ON FORMAT WRAPPED
SQL> SET LINES 200
SQL> exec ADB_ADVISOR.REPORT(schemas=>'SOE', adb_type=>'ATP');
==========================================================================================
== ATP SCHEMA MIGRATION REPORT FOR SOE
==========================================================================================

ADB Advisor Version   : 19.4.0.0.0
Instance Name         : orcltest
Database Name         : ORCLTEST
Host Name             : DESKTOP-H1NR6BO
Database Version      : 19.0.0.0.0
Pluggable Database    : PDB1
Schemas Analyzed      : SOE
Analyzing for         : Autonomous Transaction Processing (Serverless)
Report Start date/time: 19-MAY-2021 15:50

------------------------------------------------------------------------------------------
-- SECTION 1: SUMMARY
------------------------------------------------------------------------------------------

                                           Objects         Objects         Total
                           Object          Will Not        Will Migrate    Objects
Object Type                Count           Migrate         With Changes    Will Migrate
-------------------------  --------------  --------------  --------------  --------------
CONSTRAINT                 60              0               0               60
DATABASE LINK              1               0               0               1
FUNCTION                   1               0               0               1
INDEX                      33              0               0               33
PACKAGE                    1               0               0               1
PACKAGE BODY               1               0               0               1
PROCEDURE                  1               0               0               1
SEQUENCE                   7               0               0               7
TABLE                      16              0               0               16
VIEW                       2               0               0               2
User Objects in SYS        68              68              0               0
User Objects in SYSTEM     0               0               0               0

------------------------------------------------------------------------------------------
-- SECTION 2: FOLLOWING OBJECTS WILL NOT MIGRATE
------------------------------------------------------------------------------------------

1) User-defined objects in SYS schema will not migrate (Count=68):
------------------------------------------------------------------
Note: User-defined objects were detected in SYS schema. Consider moving them out of SYS prior to migration.

Owner      Object Type                    Object Name
---------- ------------------------------ ----------------------------------------
SYS        DIRECTORY                      SOETEST
SYS        TABLE                          KUPC$DATAPUMP_QUETAB_1
SYS        TABLE                          SYSNT3C-X5UB#B#VU1+I=KXRT8#HO
SYS        INDEX                          SYS_FK0000072887N00099$
SYS        TABLE                          SYSNTV0QHSF+T-$UF0_M=$FESVJA=
SYS        INDEX                          SYS_FK0000072887N00178$
SYS        TABLE                          SYSNTNEQ07+WAT2G6BRH+-_P#T$ZE
SYS        INDEX                          SYS_FK0000072887N00183$
SYS        INDEX                          SYS_C007432
SYS        INDEX                          SYS_IL0000072887C00192$$
SYS        INDEX                          SYS_IL0000072887C00185$$
SYS        INDEX                          SYS_IL0000072887C00181$$
SYS        INDEX                          SYS_IL0000072887C00180$$
SYS        INDEX                          SYS_IL0000072887C00134$$
SYS        INDEX                          SYS_IL0000072887C00117$$
SYS        INDEX                          SYS_IL0000072887C00098$$
SYS        INDEX                          SYS_IL0000072887C00096$$
SYS        INDEX                          SYS_IL0000072887C00038$$
SYS        INDEX                          SYS_C007433
SYS        INDEX                          SYS_C007434
SYS        INDEX                          SYS_C007435
SYS        TABLE                          AQ$_KUPC$DATAPUMP_QUETAB_1_S
SYS        INDEX                          SYS_C007438
SYS        EVALUATION CONTEXT             AQ$_KUPC$DATAPUMP_QUETAB_1_V
SYS        TABLE                          AQ$_KUPC$DATAPUMP_QUETAB_1_T
SYS        VIEW                           AQ$KUPC$DATAPUMP_QUETAB_1_S
SYS        SEQUENCE                       AQ$_KUPC$DATAPUMP_QUETAB_1_N
SYS        INDEX                          SYS_IOT_TOP_72921
SYS        TABLE                          AQ$_KUPC$DATAPUMP_QUETAB_1_H
SYS        INDEX                          SYS_IOT_TOP_72923
SYS        TABLE                          AQ$_KUPC$DATAPUMP_QUETAB_1_L
SYS        TABLE                          SYS_IOT_OVER_72926
SYS        TABLE                          AQ$_KUPC$DATAPUMP_QUETAB_1_G
SYS        INDEX                          SYS_IOT_TOP_72926
SYS        TABLE                          AQ$_KUPC$DATAPUMP_QUETAB_1_I
SYS        INDEX                          SYS_IOT_TOP_72929
SYS        QUEUE                          AQ$_KUPC$DATAPUMP_QUETAB_1_E
SYS        VIEW                           AQ$_KUPC$DATAPUMP_QUETAB_1_F
SYS        VIEW                           AQ$KUPC$DATAPUMP_QUETAB_1
SYS        VIEW                           QT72887_BUFFER
SYS        VIEW                           AQ$KUPC$DATAPUMP_QUETAB_1_R
SYS        TABLE                          AQ$_KUPC$DATAPUMP_QUETAB_1_P
SYS        TABLE                          SYSNTO_6OH=T88W#NZSL+1LAKVGFJ
SYS        INDEX                          SYS_FK0000072942N00099$
SYS        TABLE                          SYSNTMED=8P23Q=ISZE_G6#W0K9UC
SYS        INDEX                          SYS_FK0000072942N00178$
SYS        TABLE                          SYSNT+STTK8UG6TEKK1$8_26VS8RH
SYS        INDEX                          SYS_FK0000072942N00183$
SYS        INDEX                          SYS_C007447
SYS        INDEX                          SYS_IL0000072942C00192$$
SYS        INDEX                          SYS_IL0000072942C00185$$
SYS        INDEX                          SYS_IL0000072942C00181$$
SYS        INDEX                          SYS_IL0000072942C00180$$
SYS        INDEX                          SYS_IL0000072942C00134$$
SYS        INDEX                          SYS_IL0000072942C00117$$
SYS        INDEX                          SYS_IL0000072942C00098$$
SYS        INDEX                          SYS_IL0000072942C00096$$
SYS        INDEX                          SYS_IL0000072942C00038$$
SYS        INDEX                          SYS_C007448
SYS        INDEX                          SYS_C007449
SYS        INDEX                          SYS_C007450
SYS        TABLE                          SYS_IOT_OVER_72971
SYS        TABLE                          AQ$_KUPC$DATAPUMP_QUETAB_1_D
SYS        INDEX                          SYS_IOT_TOP_72971
SYS        INDEX                          SYS_IL0000072971C00006$$
SYS        TABLE                          L$1
SYS        TABLE                          L$2
SYS        DATABASE LINK                  SYS_HUB


------------------------------------------------------------------------------------------
-- SECTION 3: FOLLOWING OBJECTS WILL MIGRATE WITH CHANGES
------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------
-- SECTION 4: MIGRATION ADDITIONAL INFO
------------------------------------------------------------------------------------------

1) Parallel DEGREE > 1 specified on INDEX (Count=22):
-----------------------------------------------------
Note: When migrating to ATP, if a PARALLEL clause is specified on the index in your database, it will
remain with the index when it gets created, via Data Pump or manual methods. This can lead to SQL statements
running in parallel unbeknownst to the end user. To specify serial execution, change the INDEX parallel
clause to NOPARALLEL or alter the PARALLEL degree to 1 before or after the migration.

Owner                Object Type          Object Name
-------------------- -------------------- ----------------------------------------
SOE                  INDEX                CARDDETAILS_CUST_IX
SOE                  INDEX                WAREHOUSES_PK
SOE                  INDEX                CUST_FUNC_LOWER_NAME_IX
SOE                  INDEX                PROD_CATEGORY_IX
SOE                  INDEX                ORDER_PK
SOE                  INDEX                CUST_ACCOUNT_MANAGER_IX
SOE                  INDEX                INV_PRODUCT_IX
SOE                  INDEX                ITEM_ORDER_IX
SOE                  INDEX                ORD_ORDER_DATE_IX
SOE                  INDEX                CUST_DOB_IX
SOE                  INDEX                CUST_EMAIL_IX
SOE                  INDEX                CARD_DETAILS_PK
SOE                  INDEX                WHS_LOCATION_IX
SOE                  INDEX                PROD_SUPPLIER_IX
SOE                  INDEX                PRD_DESC_PK
SOE                  INDEX                ORD_WAREHOUSE_IX
SOE                  INDEX                INV_WAREHOUSE_IX
SOE                  INDEX                ORDER_ITEMS_PK
SOE                  INDEX                ADDRESS_PK
SOE                  INDEX                ADDRESS_CUST_IX
SOE                  INDEX                PROD_NAME_IX
SOE                  INDEX                ITEM_PRODUCT_IX

2) User defined tablespaces are not allowed in ATP-S and ADW-S (Serverless) (Count=1):
--------------------------------------------------------------------------------------
Note: Creation of tablespaces is disallowed in ATP and ADW (Serverless). The tablespace clause gets ignored
and all objects get created in 'DATA' tablespace. The following is the list of schemas and the tablespaces
currently in use.

SOE                  SOETBS

3) Schema Owner's user attributes will be modified in ADB (Count=1):
--------------------------------------------------------------------
Note: The following schema owner's DEFAULT TABLESPACE and/or DEFAULT PROFILE will be modified in ADB.

DEFAULT TABLESPACE for SOE will be modified from 'SOETBS' to 'DATA'

4) Database Options currently in use but will not be available in the ADB (Count=1):
------------------------------------------------------------------------------------
Note: The following Database Options are detected as being used. ADB does not have these Options installed.
Please verify if the application/schema to be migrated depends on these options.

Tuning Pack

5) Database Parameters are detected as modified in the current database but can't be modified in the ADB (Count=3):
-------------------------------------------------------------------------------------------------------------------
Note: The following init parameters are modified in your database that you would not be able to modify
in ADB. Please refer to the Oracle Autonomous Database documentation on the parameters that you are
allowed to modify.

local_listener
nls_time_format
nls_time_tz_format


------------------------------------------------------------------------------------------
-- END OF REPORT
------------------------------------------------------------------------------------------
Report End Datetime   : 19-MAY-2021 15:50
Report Runtime        : +000000000 00:00:08.353000000
------------------------------------------------------------------------------------------


PL/SQL procedure successfully completed.

SQL>

Migrating soe Schema from on premises to the Autonomous Database

Connect and create create schema soe on Autonomous database on ADB

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

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 19 17:45:43 2021
Version 19.3.0.0.0

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

Last Successful login time: Wed May 19 2021 17:33:27 +05:30

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

SQL> DROP USER SOE CASCADE;
DROP USER SOE CASCADE
          *
ERROR at line 1:
ORA-01918: user 'SOE' does not exist


SQL> CREATE USER SOE IDENTIFIED BY Abc#123456789 ;

User created.

SQL> GRANT CONNECT TO SOE;

Grant succeeded.

SQL> GRANT DWROLE TO SOE;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO SOE;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_LOCK TO soe;

Grant succeeded.

SQL>

Create directory on premesis database and grant access to soe schema

  D:\udmey\WINDOWS.X64_193000_db_home\bin>sqlplus sys/system123@pdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 19 17:47:36 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> GRANT READ, WRITE ON DIRECTORY PUMP_DIR TO soe;
GRANT READ, WRITE ON DIRECTORY PUMP_DIR TO soe
                               *
ERROR at line 1:
ORA-22930: directory does not exist


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

Grant succeeded.

SQL> select DIRECTORY_NAME, DIRECTORY_PATH from DBA_DIRECTORIES WHERE DIRECTORY_NAME LIKE 'TEST%';

no rows selected

SQL> select DIRECTORY_NAME, DIRECTORY_PATH from DBA_DIRECTORIES WHERE DIRECTORY_NAME LIKE 'SOETEST%';

DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SOETEST
D:\udmey\original\soedump


SQL>

Take backup of soe schema using expdp

 
 
 D:\ud\WINDOWS.X64_193000_db_home\bin>expdp \"sys/system123@pdb1 as sysdba\" directory=SOETEST dumpfile=soe%u.dmp logfile=impdp_remap.log  schemas=soe EXCLUDE=cluster,db_link,statistics PARALLEL=2 LOGTIME=all METRICS=yes FLASHBACK_TIME=systimestamp ENCRYPTION_PWD_PROMPT=yes

Export: Release 19.0.0.0.0 - Production on Wed May 19 18:03:10 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:
19-MAY-21 18:03:30.942: Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "sys/********@pdb1 AS SYSDBA" directory=SOETEST dumpfile=soe%u.dmp logfile=impdp_remap.log schemas=soe EXCLUDE=cluster,db_link,statistics PARALLEL=2 LOGTIME=all METRICS=yes FLASHBACK_TIME=systimestamp ENCRYPTION_PWD_PROMPT=yes
19-MAY-21 18:03:31.292: W-1 Startup took 1 seconds
19-MAY-21 18:03:33.428: W-2 Startup took 0 seconds
19-MAY-21 18:03:34.236: W-2 Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
19-MAY-21 18:03:34.334: W-2      Completed 1 PACKAGE_BODY objects in 0 seconds
19-MAY-21 18:03:34.621: W-2 Processing object type SCHEMA_EXPORT/USER
19-MAY-21 18:03:34.625: W-2      Completed 1 USER objects in 0 seconds
19-MAY-21 18:03:34.791: W-2 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
19-MAY-21 18:03:34.795: W-2      Completed 1 DEFAULT_ROLE objects in 0 seconds
19-MAY-21 18:03:34.905: W-2 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
19-MAY-21 18:03:34.908: W-2      Completed 1 TABLESPACE_QUOTA objects in 0 seconds
19-MAY-21 18:03:34.969: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
19-MAY-21 18:03:35.479: W-2 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
19-MAY-21 18:03:35.482: W-2      Completed 1 PROCACT_SCHEMA objects in 0 seconds
19-MAY-21 18:03:35.867: W-1 Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
19-MAY-21 18:03:35.889: W-1      Completed 5 SEQUENCE objects in 0 seconds
19-MAY-21 18:03:37.537: W-2 Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
19-MAY-21 18:03:37.542: W-2      Completed 2 IDENTITY_COLUMN objects in 0 seconds
19-MAY-21 18:03:38.007: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
19-MAY-21 18:03:38.094: W-2 Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
19-MAY-21 18:03:38.100: W-1      Completed 16 TABLE objects in 2 seconds
19-MAY-21 18:03:38.101: W-2      Completed 1 PACKAGE objects in 1 seconds
19-MAY-21 18:03:38.332: W-1 Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
19-MAY-21 18:03:38.336: W-1      Completed 1 FUNCTION objects in 0 seconds
19-MAY-21 18:03:38.568: W-2 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
19-MAY-21 18:03:38.572: W-2      Completed 1 PROCEDURE objects in 0 seconds
19-MAY-21 18:03:38.811: W-2 Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
19-MAY-21 18:03:38.814: W-2      Completed 1 ALTER_PACKAGE_SPEC objects in 0 seconds
19-MAY-21 18:03:38.827: W-1 Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
19-MAY-21 18:03:38.831: W-1      Completed 1 ALTER_FUNCTION objects in 0 seconds
19-MAY-21 18:03:38.896: W-2 Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
19-MAY-21 18:03:38.900: W-2      Completed 1 ALTER_PROCEDURE objects in 0 seconds
19-MAY-21 18:03:40.435: W-2 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
19-MAY-21 18:03:40.501: W-2      Completed 26 INDEX objects in 1 seconds
19-MAY-21 18:03:40.788: W-1 Processing object type SCHEMA_EXPORT/VIEW/VIEW
19-MAY-21 18:03:40.792: W-1      Completed 2 VIEW objects in 2 seconds
19-MAY-21 18:03:40.908: W-2 Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
19-MAY-21 18:03:40.913: W-2      Completed 1 INDEX objects in 0 seconds
19-MAY-21 18:03:41.595: W-1 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
19-MAY-21 18:03:41.667: W-1      Completed 19 CONSTRAINT objects in 1 seconds
19-MAY-21 18:03:42.252: W-1 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
19-MAY-21 18:03:42.257: W-1      Completed 9 REF_CONSTRAINT objects in 0 seconds
19-MAY-21 18:03:42.437: W-2 Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
19-MAY-21 18:03:42.441: W-2      Completed 1 INDEX objects in 0 seconds
19-MAY-21 18:04:00.968: W-2 . . exported "SOE"."ORDERS"                              114.9 MB 1352070 rows in 16 seconds using direct_path
19-MAY-21 18:04:09.065: W-1 . . exported "SOE"."ORDER_ITEMS"                         170.4 MB 3735896 rows in 25 seconds using direct_path
19-MAY-21 18:04:09.233: W-2 . . exported "SOE"."INVENTORIES"                         15.27 MB  902171 rows in 8 seconds using direct_path
19-MAY-21 18:04:10.279: W-1 . . exported "SOE"."LOGON"                               9.065 MB  442208 rows in 1 seconds using direct_path
19-MAY-21 18:04:10.328: W-2 . . exported "SOE"."CUSTOMERS"                           4.983 MB   45703 rows in 1 seconds using direct_path
19-MAY-21 18:04:10.872: W-1 . . exported "SOE"."CARD_DETAILS"                        2.402 MB   55703 rows in 0 seconds using direct_path
19-MAY-21 18:04:10.879: W-2 . . exported "SOE"."ADDRESSES"                           4.084 MB   55703 rows in 0 seconds using direct_path
19-MAY-21 18:04:11.185: W-1 . . exported "SOE"."PRODUCT_DESCRIPTIONS"                224.1 KB    1000 rows in 1 seconds using direct_path
19-MAY-21 18:04:11.371: W-1 . . exported "SOE"."PRODUCT_INFORMATION"                 186.4 KB    1000 rows in 0 seconds using direct_path
19-MAY-21 18:04:11.435: W-1 . . exported "SOE"."EMP"                                 51.39 KB     879 rows in 0 seconds using direct_path
19-MAY-21 18:04:11.515: W-1 . . exported "SOE"."WAREHOUSES"                          36.03 KB    1000 rows in 0 seconds using direct_path
19-MAY-21 18:04:11.577: W-1 . . exported "SOE"."JOB_HISTORY"                         14.61 KB     246 rows in 0 seconds using direct_path
19-MAY-21 18:04:11.660: W-1 . . exported "SOE"."JOBS"                                5.984 KB      23 rows in 0 seconds using direct_path
19-MAY-21 18:04:11.742: W-1 . . exported "SOE"."ORDERENTRY_METADATA"                 5.617 KB       4 rows in 0 seconds using direct_path
19-MAY-21 18:04:11.823: W-1 . . exported "SOE"."DEPT"                                5.640 KB       7 rows in 0 seconds using direct_path
19-MAY-21 18:04:11.873: W-1 . . exported "SOE"."V"                                   5.460 KB       0 rows in 0 seconds using direct_path
19-MAY-21 18:04:12.178: W-2      Completed 16 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 27 seconds
19-MAY-21 18:04:13.193: W-2 Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
19-MAY-21 18:04:13.248: ******************************************************************************
19-MAY-21 18:04:13.249: Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
19-MAY-21 18:04:13.251:   D:\UDMEY\ORIGINAL\SOEDUMP\SOE01.DMP
19-MAY-21 18:04:13.252:   D:\UDMEY\ORIGINAL\SOEDUMP\SOE02.DMP
19-MAY-21 18:04:13.376: Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed May 19 18:04:13 2021 elapsed 0 00:00:54


D:\ud\WINDOWS.X64_193000_db_home\bin>

Connect Autonomous database and run credential is created to be passed to the Data Pump import utility

 
 
   C:\Users\varun>sqlplus soe/Abc#123456789@orcladb_medium

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 19 18:07:09 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.5.0.0.0

SQL> BEGIN
  2  DBMS_CLOUD.CREATE_CREDENTIAL (
  3   CREDENTIAL_NAME => 'SOE_CREDENTIAL',
  4   USERNAME => 'SOE',
  5   PASSWORD => 'Xyz#123456789' );
  6  END;
  7  /

PL/SQL procedure successfully completed.

Connect autonomous database using admin schema

run the following code to create a credential for the OCI admin user (the federated user) in
the soe schema. Pass to the USERNAME the OCI account username and pass to the PASSWORD
the authentication tokens of the user.

    SQL> conn admin/Cloud$123456@orcladb_medium
Connected.

SQL> col USERNAME format a25
SQL> col CREDENTIAL_NAME format a30
SQL> SELECT USERNAME, CREDENTIAL_NAME FROM DBA_CREDENTIALS WHERE OWNER='SOE';

USERNAME                  CREDENTIAL_NAME
------------------------- ------------------------------
SOE                       SOE_CREDENTIAL

SQL>

for drop 

SET DEFINE OFF
BEGIN
DBMS_CLOUD.DROP_CREDENTIAL (CREDENTIAL_NAME => 'ADMIN_CRED_NAME');
DBMS_CLOUD.CREATE_CREDENTIAL (
 CREDENTIAL_NAME => 'ADMIN_CRED_NAME_FED',
 USERNAME => 'oracleidentitycloudservice/<oci username>',
 PASSWORD => '<OCI user auth tokens>' 
);
END;
/

Connect ADB cloud console and create bucket and upload expdp backup on bucket.

Check the checksum of the dump files uploaded correctly or not both on premises and cloud console.

for Linux : md5sum

Run the following query to produce checksums for the dump files in the object storage in autonomous database

    SQL> sho user
USER is "ADMIN"
SQL>

SQL> col OBJECT_NAME for a20
SQL> col CHECKSUM for a35
SQL> SELECT CHECKSUM, OBJECT_NAME FROM DBMS_CLOUD.LIST_OBJECTS('ADMIN_CRED_NAME','https://objectstorage.ap-mumbai-1.oraclecloud.com/n/bmdsoh48o0kf/b/soe_expdp_bucket/');

CHECKSUM                            OBJECT_NAME
----------------------------------- --------------------
caf1e274d7333cd5c0cd325571b4508f-3  expdp_soe_testSOE01.
                                    DMP

c73a26bdc52cb9261d47139b7768382b-3  expdp_soe_testSOE02.
                                    DMP

View object details in the bucket and create pre authenticated details as mentioned in screenshot and copy URI of dump file in par file for IMPDP operations.

Copy URI details

Enter URI details in impdp par file below and perform impdp on autonomous database:

cat > soeimp.par << EOF

DIRECTORY=data_pump_dir

CREDENTIAL=SOE_CREDENTIAL

DUMPFILE=https://objectstorage.ap-mumbai-1.oraclecloud.com/p/CxZ416FzvjSy2BrOEkj-1ymwUIalWPd3s_2AcPDgCyOp3EmTx0bzly3J6SHOPP4_/n/bmdsoh48o0kf/b/soe_expdp_bucket/o/expdp_soe_testSOE01.DMP

,https://objectstorage.ap-mumbai-1.oraclecloud.com/p/0we6MtY0_MbhBrt1Cevaiv4bp4Bscw-laj-ZaYQdQZhY--yvw4ei5JNBSYkR5Jmn/n/bmdsoh48o0kf/b/soe_expdp_bucket/o/expdp_soe_testSOE02.DMP

ENCRYPTION_PWD_PROMPT=yes

TRANSFORM=segment_attributes:n

TRANSFORM=dwcs_cvt_iots:y

TRANSFORM=constraint_use_default_index:y

TRANSFORM=omit_encryption_clause:y

EXCLUDE=cluster,db_link

EOF

D:\ud\original\soedump>impdp soe/Abc#123456789@orcladb_medium parfile=impdp_soe.par

Import: Release 19.0.0.0.0 - Production on Wed May 19 19:29:54 2021
Version 19.3.0.0.0

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

Encryption Password:

D:\ud\original\soedump>impdp soe/Abc#123456789@orcladb_medium parfile=impdp_soe.par

Import: Release 19.0.0.0.0 - Production on Wed May 19 19:40:09 2021
Version 19.3.0.0.0

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

Encryption Password:
Master table "SOE"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SOE"."SYS_IMPORT_FULL_01": soe/********@orcladb_medium parfile=impdp_soe.par
Processing object type SCHEMA_EXPORT/USER
ORA-31685: Object type USER:"SOE" failed due to insufficient privileges. Failing sql is:
CREATE USER "SOE" IDENTIFIED BY VALUES 'S:28E06C14B429116220BDDEBC97383388DF306515AC3480AB242984315BB9;T:5AA74E9EDED97C866E90DE243359CC622711E7FC3D618519ACEB094933EA382E9F914CA152D20CEE51726BC96A89A0CA8074F734C1AE61AB13EB2AC29C70D19B1ED339CBF00FEE16528444DEBB9DDCD5' DEFAULT COLLATION "USING_NLS_COMP" DEFAULT TABLESPACE "SOETBS" TEMPORARY TABLESPACE "TEMP"

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
ORA-31685: Object type DEFAULT_ROLE:"SOE" failed due to insufficient privileges. Failing sql is:
ALTER USER "SOE" DEFAULT ROLE ALL

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
ORA-31685: Object type TABLESPACE_QUOTA:"SOE"."SOETBS" failed due to insufficient privileges. Failing sql is:
DECLARE TEMP_COUNT NUMBER; SQLSTR VARCHAR2(200); BEGIN SQLSTR := 'ALTER USER "SOE" QUOTA UNLIMITED ON "SOETBS"'; EXECUTE IMMEDIATE SQLSTR;EXCEPTION WHEN OTHERS THEN IF SQLCODE = -30041 THEN SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES WHERE TABLESPACE_NAME = ''SOETBS'' AND CONTENTS = ''TEMPORARY'''; EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT; IF TEMP_COUNT = 1 THEN RETURN; ELSE RAISE; END IF; ELSE RAISE; END IF;END;

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
ORA-31684: Object type SEQUENCE:"SOE"."LOGON_SEQ" already exists

ORA-31684: Object type SEQUENCE:"SOE"."ADDRESS_SEQ" already exists

ORA-31684: Object type SEQUENCE:"SOE"."ORDERS_SEQ" already exists

ORA-31684: Object type SEQUENCE:"SOE"."CARD_DETAILS_SEQ" already exists

ORA-31684: Object type SEQUENCE:"SOE"."CUSTOMER_SEQ" already exists

Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SOE"."ORDER_ITEMS" 170.4 MB 3735896 rows
. . imported "SOE"."ORDERS" 114.9 MB 1352070 rows
. . imported "SOE"."INVENTORIES" 15.27 MB 902171 rows
. . imported "SOE"."LOGON" 9.065 MB 442208 rows
. . imported "SOE"."CUSTOMERS" 4.983 MB 45703 rows
. . imported "SOE"."ADDRESSES" 4.084 MB 55703 rows
. . imported "SOE"."CARD_DETAILS" 2.402 MB 55703 rows
. . imported "SOE"."PRODUCT_DESCRIPTIONS" 224.1 KB 1000 rows
. . imported "SOE"."PRODUCT_INFORMATION" 186.4 KB 1000 rows
. . imported "SOE"."EMP" 51.39 KB 879 rows
. . imported "SOE"."WAREHOUSES" 36.03 KB 1000 rows
. . imported "SOE"."JOB_HISTORY" 14.61 KB 246 rows
. . imported "SOE"."JOBS" 5.984 KB 23 rows
. . imported "SOE"."ORDERENTRY_METADATA" 5.617 KB 4 rows
. . imported "SOE"."DEPT" 5.640 KB 7 rows
. . imported "SOE"."V" 5.460 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
ORA-31684: Object type PACKAGE:"SOE"."ORDERENTRY" already exists

Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
ORA-31684: Object type FUNCTION:"SOE"."CONSUME_CPU" already exists

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
ORA-31684: Object type PROCEDURE:"SOE"."SHOW_RECM" already exists

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
ORA-39111: Dependent object type ALTER_PACKAGE_SPEC:"SOE"."ORDERENTRY" skipped, base object type PACKAGE:"SOE"."ORDERENTRY" already exists

Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
ORA-39111: Dependent object type ALTER_FUNCTION:"SOE"."CONSUME_CPU" skipped, base object type FUNCTION:"SOE"."CONSUME_CPU" already exists

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39111: Dependent object type ALTER_PROCEDURE:"SOE"."SHOW_RECM" skipped, base object type PROCEDURE:"SOE"."SHOW_RECM" already exists

Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-31684: Object type VIEW:"SOE"."PRODUCTS" already exists

ORA-31684: Object type VIEW:"SOE"."PRODUCT_PRICES" already exists

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
ORA-31684: Object type PACKAGE_BODY:"SOE"."ORDERENTRY" already exists

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
Job "SOE"."SYS_IMPORT_FULL_01" completed with 17 error(s) at Wed May 19 14:11:21 2021 elapsed 0 00:01:07

Connect Autonomous database and verify schema objects below:

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

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 19 19:43:26 2021
Version 19.3.0.0.0

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

Last Successful login time: Wed May 19 2021 18:12:06 +05:30

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

SQL> sho user
USER is "ADMIN"
SQL> select count(*),object_type,status from dba_objects where owner='SOE' group by status,object_type;

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

9 rows selected.

drop the created credential if clean up required.

  conn soe/Abc#123456789@atporadb_tp

exec DBMS_CLOUD.DROP_CREDENTIAL (CREDENTIAL_NAME => 'SOE_CREDENTIAL')

Oracle Database Commands and Practical Examples

HOME

Sunday 6 June 2021

OCI- Moving data to Autonomous database using expdp -impdp

No comments:

Post a Comment