Database Upgrade from 12c to 19c using Datapump

Database Upgrade from 12c to 19c using Datapump

Overview steps:

Step:1 Check the database size in source.
Step:2 check which tablespace holds the schema objects.
Step:3 compile invalid objects in source.
step:4 check the  count of invalid dba_objects in the source.
Step:5 create a directory for export purpose both in OS level and database level.
Step:6 Estimate the size of dumpfile, so that we can know when will the export get completed.
Step:7 export the database(TANSTAL).
Step:8 create a fresh database for import.
Step:9 Now create a directory in both OS level and database level for import purpose in the newly created database.
Step:10 import the database.(ZHIGOMA)
Step:11 Post upgrade steps in target database.
               11.1 compile invalid objects in target database.
               11.2 check whether any invalid objects present.
               11.3 Run the query to check for currently installed database components.

Step:12  Verify the timezone of the upgraded database.

Step:13  Check the CONSTRAINTS count in both source & target.it is used to display the constraints that are defined in the database.

Step:1 Check the database size in source .

SQL> col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p

Database Size             Used space         Free space
-------------------- -------------------- --------------------
    2 GB                    2 GB               0 GB

SQL>

Step:2 execute the following query to check which tablespace holds the schema objects.

set pagesize 130
break on Tablespace on Owner
column Objects format A20
select Tablespace_Name,Owner,COUNT(*)||’ tables’ Objects
from DBA_TABLES
group by Tablespace_Name,Owner
union
select Tablespace_Name, Owner, COUNT(*)||’ indexes’ Objects
from DBA_INDEXES
group by Tablespace_Name, Owner;

Step:3 compile invalid objects in source to reduce dependencies, so that we can find the best way to recompile it.

SQL> @?/rdbms/admin/utlrp.sql

COMP_TIMESTAMP UTLRP_BGN 2021-01-31 20:19:36

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.

COMP_TIMESTAMP UTLRP_END 2021-01-31 20:19:39

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#

0

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

step:4 check the  count of invalid dba_objects in the source.

SQL> select count(*) from dba_objects where status=’INVALID’;

COUNT(*)
----------
0

Step:5 create a directory for export purpose both in OS level and database level.

mkdir /u01/export

create directory export as '/u01/export';

Directory created

[oracle@orcldbs ~]$ mkdir export
[oracle@orcldbs ~]$ ls
12c.env Documents export oraprod.env Templates
19c.env Downloads Music Pictures utlrp.out
Desktop em13400_linux64-3.zip oradiag_oracle Public Videos

Create  a table employee in the user : rahul.

Step:6 Estimate the size of dumpfile by this we are able to know the completion time of export activity.

[oracle@orcldbs export]$ expdp directory=export full=Y nologfile=Y estimate_only=Y

Export: Release 12.2.0.1.0 - Production on Sun Jan 31 22:09:14 2021

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

Username: system
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** directory=export full=Y nologfile=Y estimate_only=Y 
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. estimated "SYS"."KU$_USER_MAPPING_VIEW" 64 KB
. estimated "ORDDATA"."ORDDCM_DOCS" 1.25 MB
. estimated "WMSYS"."WM$CONSTRAINTS_TABLE$" 320 KB
. estimated "SYS"."AUD$" 256 KB
. estimated "WMSYS"."WM$LOCKROWS_INFO$" 192 KB
. estimated "WMSYS"."WM$UDTRIG_INFO$" 192 KB
. estimated "LBACSYS"."OLS$AUDIT_ACTIONS" 64 KB
. estimated "LBACSYS"."OLS$DIP_EVENTS" 64 KB
. estimated "LBACSYS"."OLS$INSTALLATIONS" 64 KB
. estimated "LBACSYS"."OLS$PROPS" 64 KB
. estimated "SYS"."DAM_CLEANUP_EVENTS$" 64 KB
. estimated "SYS"."DAM_CLEANUP_JOBS$" 64 KB
. estimated "SYS"."DAM_CONFIG_PARAM$" 64 KB
. estimated "SYS"."TSDP_ASSOCIATION$" 64 KB
. estimated "SYS"."TSDP_CONDITION$" 64 KB
. estimated "SYS"."TSDP_FEATURE_POLICY$" 64 KB
. estimated "SYS"."TSDP_PARAMETER$" 64 KB
. estimated "SYS"."TSDP_POLICY$" 64 KB
. estimated "SYS"."TSDP_PROTECTION$" 64 KB
. estimated "SYS"."TSDP_SENSITIVE_DATA$" 64 KB
. estimated "SYS"."TSDP_SENSITIVE_TYPE$" 64 KB
. estimated "SYS"."TSDP_SOURCE$" 64 KB
. estimated "SYS"."TSDP_SUBPOL$" 64 KB
. estimated "SYSTEM"."REDO_DB" 64 KB
. estimated "SYSTEM"."REDO_LOG" 64 KB
. estimated "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$" 64 KB
. estimated "WMSYS"."WM$CONS_COLUMNS$" 64 KB
. estimated "WMSYS"."WM$ENV_VARS$" 64 KB
. estimated "WMSYS"."WM$EVENTS_INFO$" 64 KB
. estimated "WMSYS"."WM$HINT_TABLE$" 64 KB
. estimated "WMSYS"."WM$MODIFIED_TABLES$" 64 KB
. estimated "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$" 64 KB
. estimated "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$" 64 KB
. estimated "WMSYS"."WM$NESTED_COLUMNS_TABLE$" 64 KB
. estimated "WMSYS"."WM$NEXTVER_TABLE$" 64 KB
. estimated "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$" 64 KB
. estimated "WMSYS"."WM$RIC_LOCKING_TABLE$" 64 KB
. estimated "WMSYS"."WM$RIC_TABLE$" 64 KB
. estimated "WMSYS"."WM$RIC_TRIGGERS_TABLE$" 64 KB
. estimated "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$" 64 KB
. estimated "WMSYS"."WM$VERSION_HIERARCHY_TABLE$" 64 KB
. estimated "WMSYS"."WM$VERSION_TABLE$" 64 KB
. estimated "WMSYS"."WM$VT_ERRORS_TABLE$" 64 KB
. estimated "WMSYS"."WM$WORKSPACES_TABLE$" 64 KB
. estimated "WMSYS"."WM$WORKSPACE_PRIV_TABLE$" 64 KB
. estimated "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$" 64 KB
. estimated "LBACSYS"."OLS$AUDIT" 0 KB
. estimated "LBACSYS"."OLS$COMPARTMENTS" 0 KB
. estimated "LBACSYS"."OLS$DIP_DEBUG" 0 KB
. estimated "LBACSYS"."OLS$GROUPS" 0 KB
. estimated "LBACSYS"."OLS$LAB" 0 KB
. estimated "LBACSYS"."OLS$LEVELS" 0 KB
. estimated "LBACSYS"."OLS$POL" 0 KB
. estimated "LBACSYS"."OLS$POLICY_ADMIN" 0 KB
. estimated "LBACSYS"."OLS$POLS" 0 KB
. estimated "LBACSYS"."OLS$POLT" 0 KB
. estimated "LBACSYS"."OLS$PROFILE" 0 KB
. estimated "LBACSYS"."OLS$PROFILES" 0 KB
. estimated "LBACSYS"."OLS$PROG" 0 KB
. estimated "LBACSYS"."OLS$SESSINFO" 0 KB
. estimated "LBACSYS"."OLS$USER" 0 KB
. estimated "LBACSYS"."OLS$USER_COMPARTMENTS" 0 KB
. estimated "LBACSYS"."OLS$USER_GROUPS" 0 KB
. estimated "LBACSYS"."OLS$USER_LEVELS" 0 KB
. estimated "SYS"."FGA_LOG$FOR_EXPORT" 256 KB
. estimated "SYS"."SQL$TEXT_DATAPUMP" 192 KB
. estimated "SYS"."SQLOBJ$DATA_DATAPUMP" 192 KB
. estimated "SYS"."SQL$_DATAPUMP" 128 KB
. estimated "SYS"."SQLOBJ$AUXDATA_DATAPUMP" 128 KB
. estimated "SYS"."SQLOBJ$PLAN_DATAPUMP" 128 KB
. estimated "SYS"."SQLOBJ$_DATAPUMP" 128 KB
. estimated "SYSTEM"."SCHEDULER_JOB_ARGS" 128 KB
. estimated "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 128 KB
. estimated "SYS"."AUDTAB$TBS$FOR_EXPORT" 64 KB
. estimated "SYS"."DBA_SENSITIVE_DATA" 64 KB
. estimated "SYS"."DBA_TSDP_POLICY_PROTECTION" 64 KB
. estimated "SYS"."NACL$_ACE_EXP" 64 KB
. estimated "SYS"."NACL$_HOST_EXP" 64 KB
. estimated "SYS"."NACL$_WALLET_EXP" 64 KB
. estimated "MDSYS"."RDF_PARAM$" 16 KB
. estimated "WMSYS"."WM$EXP_MAP" 16 KB
. estimated "WMSYS"."WM$METADATA_MAP" 16 KB
Total estimation using BLOCKS method: 6.546 MB
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Sun Jan 31 22:09:32 2021 elapsed 0 00:00:11

Step:7 export the roles and privileges.

[oracle@orcldbs ~]$ expdp directory=export dumpfile=orclfull.dmp logfile=roles.log full=Y INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE

Step:8 Now it’s time to export our database(TANSTAL) 

[oracle@orcldbs ~]$ expdp system/oracle@tanstal DIRECTORY=export DUMPFILE=orclfull.dmp LOGFILE=full_exp.log FULL=YES;

Export: Release 12.2.0.1.0 - Production on Sun Jan 31 22:16:25 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/********@tanstal DIRECTORY=export DUMPFILE=orclfull.dmp LOGFILE=full_exp.log FULL=YES 
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW" 6.078 KB 37 rows
. . exported "SYSTEM"."REDO_DB" 25.59 KB 1 rows
. . exported "ORDDATA"."ORDDCM_DOCS" 252.9 KB 9 rows
. . exported "WMSYS"."WM$WORKSPACES_TABLE$" 12.10 KB 1 rows
. . exported "WMSYS"."WM$HINT_TABLE$" 9.984 KB 97 rows
. . exported "LBACSYS"."OLS$INSTALLATIONS" 6.960 KB 2 rows
. . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$" 7.078 KB 11 rows
. . exported "SYS"."DAM_CONFIG_PARAM$" 6.531 KB 14 rows
. . exported "SYS"."TSDP_SUBPOL$" 6.328 KB 1 rows
. . exported "WMSYS"."WM$NEXTVER_TABLE$" 6.375 KB 1 rows
. . exported "LBACSYS"."OLS$PROPS" 6.234 KB 5 rows
. . exported "WMSYS"."WM$ENV_VARS$" 6.015 KB 3 rows
. . exported "SYS"."TSDP_PARAMETER$" 5.953 KB 1 rows
. . exported "SYS"."TSDP_POLICY$" 5.921 KB 1 rows
. . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$" 5.984 KB 1 rows
. . exported "WMSYS"."WM$EVENTS_INFO$" 5.812 KB 12 rows
. . exported "LBACSYS"."OLS$AUDIT_ACTIONS" 5.757 KB 8 rows
. . exported "LBACSYS"."OLS$DIP_EVENTS" 5.539 KB 2 rows
. . exported "LBACSYS"."OLS$AUDIT" 0 KB 0 rows
. . exported "LBACSYS"."OLS$COMPARTMENTS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$DIP_DEBUG" 0 KB 0 rows
. . exported "LBACSYS"."OLS$GROUPS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$LAB" 0 KB 0 rows
. . exported "LBACSYS"."OLS$LEVELS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POL" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLICY_ADMIN" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLT" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROFILE" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROFILES" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROG" 0 KB 0 rows
. . exported "LBACSYS"."OLS$SESSINFO" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_COMPARTMENTS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_GROUPS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_LEVELS" 0 KB 0 rows
. . exported "SYS"."AUD$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$" 0 KB 0 rows
. . exported "SYS"."TSDP_ASSOCIATION$" 0 KB 0 rows
. . exported "SYS"."TSDP_CONDITION$" 0 KB 0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$" 0 KB 0 rows
. . exported "SYS"."TSDP_PROTECTION$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
. . exported "SYS"."TSDP_SOURCE$" 0 KB 0 rows
. . exported "SYSTEM"."REDO_LOG" 0 KB 0 rows
. . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows
. . exported "WMSYS"."WM$CONSTRAINTS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$CONS_COLUMNS$" 0 KB 0 rows
. . exported "WMSYS"."WM$LOCKROWS_INFO$" 0 KB 0 rows
. . exported "WMSYS"."WM$MODIFIED_TABLES$" 0 KB 0 rows
. . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_LOCKING_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$" 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_INFO$" 0 KB 0 rows
. . exported "WMSYS"."WM$VERSION_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$VT_ERRORS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 rows
. . exported "MDSYS"."RDF_PARAM$" 6.515 KB 3 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 5.953 KB 2 rows
. . exported "SYS"."DBA_SENSITIVE_DATA" 0 KB 0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows
. . exported "SYS"."NACL$_ACE_EXP" 0 KB 0 rows
. . exported "SYS"."NACL$_HOST_EXP" 6.976 KB 2 rows
. . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows
. . exported "SYS"."SQL$TEXT_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQL$_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$DATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$PLAN_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$_DATAPUMP" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 9.515 KB 12 rows
. . exported "WMSYS"."WM$EXP_MAP" 7.718 KB 3 rows
. . exported "WMSYS"."WM$METADATA_MAP" 0 KB 0 rows
. . exported "RAHUL"."EMPLOYEE" 688.6 KB 100000 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/home/oracle/orclfull.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Sun Jan 31 22:19:02 2021 elapsed 0 00:02:03

Step:9 create a fresh database for import.(the dumpfile that is being exported in tanstal db going to be imported in zhigoma) 

Step:10 Now create a directory in both OS level and database level for import purpose in the newly created database.

mkdir /u01/export 

create directory export as '/u01/export';

Step:11 import the roles and privileges to zhigoma db.

[oracle@orcldbs ~]$ impdp directory=export dumpfile=orclfull.dmp logfile=roles.log full=Y INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE

Step:12 Now it’s time to import our database.(ZHIGOMA)

[oracle@orcldbs ~]$ impdp system/oracle@zhigoma directory=export dumpfile=orclfull.dmp logfile=export.log full=Yes;

Import: Release 19.0.0.0.0 - Production on Mon Feb 1 01:21:26 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
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@zhigoma directory=export dumpfile=orclfull.dmp logfile=export.log full=Yes 
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER


Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
. . imported "SYSTEM"."REDO_DB_TMP" 25.59 KB 1 rows
. . imported "WMSYS"."E$WORKSPACES_TABLE$" 12.10 KB 1 rows
. . imported "WMSYS"."E$HINT_TABLE$" 9.984 KB 97 rows
. . imported "WMSYS"."E$WORKSPACE_PRIV_TABLE$" 7.078 KB 11 rows
. . imported "SYS"."AMGT$DP$DAM_CONFIG_PARAM$" 6.531 KB 14 rows
. . imported "SYS"."DP$TSDP_SUBPOL$" 6.328 KB 1 rows
. . imported "WMSYS"."E$NEXTVER_TABLE$" 6.375 KB 1 rows
. . imported "WMSYS"."E$ENV_VARS$" 6.015 KB 3 rows
. . imported "SYS"."DP$TSDP_PARAMETER$" 5.953 KB 1 rows
. . imported "SYS"."DP$TSDP_POLICY$" 5.921 KB 1 rows
. . imported "WMSYS"."E$VERSION_HIERARCHY_TABLE$" 5.984 KB 1 rows
. . imported "WMSYS"."E$EVENTS_INFO$" 5.812 KB 12 rows
. . imported "LBACSYS"."OLS_DP$OLS$AUDIT" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$COMPARTMENTS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$GROUPS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$LAB" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$LEVELS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$POL" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$POLS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$POLT" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$PROFILE" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$PROG" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$USER" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$AUD$" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_EVENTS$" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_JOBS$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_ASSOCIATION$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_CONDITION$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_FEATURE_POLICY$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_PROTECTION$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_SENSITIVE_DATA$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_SOURCE$" 0 KB 0 rows
. . imported "SYSTEM"."REDO_LOG_TMP" 0 KB 0 rows
. . imported "WMSYS"."E$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows
. . imported "WMSYS"."E$CONSTRAINTS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$CONS_COLUMNS$" 0 KB 0 rows
. . imported "WMSYS"."E$LOCKROWS_INFO$" 0 KB 0 rows
. . imported "WMSYS"."E$MODIFIED_TABLES$" 0 KB 0 rows
. . imported "WMSYS"."E$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$NESTED_COLUMNS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RIC_LOCKING_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RIC_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RIC_TRIGGERS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$UDTRIG_DISPATCH_PROCS$" 0 KB 0 rows
. . imported "WMSYS"."E$UDTRIG_INFO$" 0 KB 0 rows
. . imported "WMSYS"."E$VERSION_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$VT_ERRORS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 rows
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported "MDSYS"."RDF_PARAM$TBL" 6.515 KB 3 rows
. . imported "SYS"."AMGT$DP$AUDTAB$TBS$FOR_EXPORT" 5.953 KB 2 rows
. . imported "SYS"."DP$DBA_SENSITIVE_DATA" 0 KB 0 rows
. . imported "SYS"."DP$DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$FGA_LOG$FOR_EXPORT" 0 KB 0 rows
. . imported "SYS"."NACL$_ACE_IMP" 0 KB 0 rows
. . imported "SYS"."NACL$_HOST_IMP" 6.976 KB 2 rows
. . imported "SYS"."NACL$_WALLET_IMP" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQL$TEXT" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQL$" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$AUXDATA" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$DATA" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$PLAN" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$" 0 KB 0 rows
. . imported "SYSTEM"."SCHEDULER_JOB_ARGS_TMP" 0 KB 0 rows
. . imported "SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP" 9.515 KB 12 rows
. . imported "WMSYS"."E$EXP_MAP" 7.718 KB 3 rows
. . imported "WMSYS"."E$METADATA_MAP" 0 KB 0 rows
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "RAHUL"."EMPLOYEE" 688.6 KB 100000 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed at Mon Feb 1 01:23:21 2021 elapsed 0 00:01:54

Once the import is over, you can also review the log file.

13.1 compile invalid objects in target database.

SQL> @?/rdbms/admin/utlrp.sql

SQL> select count(*) from dba_objects where status=’INVALID’;

COUNT(*)
-------- 
0
SQL>  SELECT version FROM v$timezone_file;

   VERSION
-----------------
     32

13.3 Run the query to check for currently installed database components.

SQL> col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

13.4 Check the CONSTRAINTS count,it is used to display the constraints that are defined in the database.

SQL> SELECT constraint_type, count(*) AS num_constraints
FROM dba_constraints
GROUP BY constraint_type; 

C NUM_CONSTRAINTS

C 5784
F 12
O 181
R 327
P 864
V 11
U 250

7 rows selected.
SQL>

yes now we can login to the zhigoma database and we can verify our user(rahul) and the table(employee) in that user is  imported into this database or not.

We have successfully upgraded our database from 12 c to 19c using datapump !!!!