Migration using Datapump 11g to 19c

 select instance_name, status, version, host_name from v$instance;

 

SELECT value AS db_ncharset FROM nls_database_parameters WHERE parameter = 'NLS_NCHAR_CHARACTERSET';

 

--Examine datafile storage structure

select name from v$datafile;

 

--- get info for tablespaces

select tablespace_name from dba_tablespaces;

 

--- compared list of tablespaces with the same output from newly created database and create missing tablespace (s).

--- DDL for creating tablesapces ... use

SELECT dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;

 

--- create datapump directory objects 

--- make datapump directory on on file system

--- create directory objects

select * from dba_directories;

create or replace directory expdir as '';

 

--- Validation after migration---

select * from dba_users;---where account_status='OPEN';

select username, account_status, default_tablespace, password_versions from dba_users;

select distinct schema, other_schemas from dba_registry;-- (oracle maintained users in 11g) 

--- Export all schemas except those maintained by oracle

select schema, other_schemas from dba_registry;

--During the import if you had users with 10G password versions, these accounts will be locked and expired, unless the SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 10 or 11

--- Object Counts

--- Number of oracle schema objects and their sizes : [preferred]

select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB" from (select owner, count(*) obj_cnt from dba_objects group by owner) obj , (select owner, ceil(sum(bytes)/1024/1024) seg_size from dba_segments group by owner) seg where obj.owner = seg.owner(+) order by 3 desc ,2 desc, 1;

 

object count should match, size does not matter.

-- Memory type

-- AMM vs ASMM

show parameter memory;

if memory_target is used, it means AMM is used.

vi fullorcl.par

directory=EXPDIR

full=Y

dumpfile=fullorclexp.dmp

logfile=fullorclexp.log

exclude=statistics

exclude=schema:"IN (''SYS','SYSTEM','APEX_030200','CTXSYS','EXFSYS','MDSYS','OLAPSYS','ORDSYS','OWBSYS','SYSMAN','WMSYS','XDB

 

expdp \'/ as sysdba\' parfile=fullorcl.par 

vi imporcl.par

directory=EXPDIR

full=Y

dumpfile=fullorclexp.dmp

logfile=fullorclimp.log

 

impdp \'/ as sysdba\' parfile=fullorcl.par 

 

Ref : https://www.youtube.com/watch?v=JYMRpgEJ5lc