Oracle Database 11gR2 Migration/Upgrade to 19c using Data Pump 

Oracle Database 11gR2 migration/upgrade to 19c using Data Pump
Related Videos:
-------------------------
How to establish a BASIC SQL Developer connection to an Oracle Database
• How to establish a BAS...
Follow me:
----------
LinkedIn: / atsohmofor
Twitter: / bindag OR @Bindag
email: bobsukki@gmail.com
Telegram: NI TSOH
RU-vid: / @bobsukki OR NI TSOH
Related material:
---------------------------
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 data file storage structures
SELECT NAME FROM V$DATAFILE;
--Memory Type
--AMM vs ASMM
show parameter memory
--get info for tablespaces
SELECT TABLESPACE_NAME from DBA_TABLESPACES;
CREATE TABLESPACE "EXAMPLE" DATAFILE
'/u01/app/oracle/oradata/ORCL/example01.dbf' SIZE 104857600
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
--Compared list of tablespaces with thesame output from newly created database and create missing tablespace(s).
--DDL for creating tablespaces...use
SELECT dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;
--Create data pump directory object
--Make datapump directory on file system '/u01/export'
--Create directory object
SELECT * FROM DBA_DIRECTORIES;
CREATE OR REPLACE DIRECTORY EXPDIR AS '/u01/export';
--Validation after migration...
SELECT * from DBA_USERS;-- where account_status='OPEN';
SELECT username, account_status, default_tablespace, password_versions from dba_users;
select username, account_status, default_tablespace, password_versions, oracle_maintained from dba_users
where oracle_maintained='Y';
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;
export command:
expdp \'/ as sysdba\'
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','FLOWS_FILES','MDSYS','ORDDATA','ORDPLUGINS','SI_INFORMTN_SCHEMA','APPQOSSYS','DIP','ORACLE_OCM','OUTLN','ANONYMOUS','XS$NULL')"
import command:
impdp \'/ as sysdba\' directory=expdir full=y dumpfile=fullorclexp.dmp logfile=fullorclimp.log