Oracle Database Migrations
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
Purpose This page is a work in progress ... do not expect to find it complete any time soon; and perhaps never. What it is intended to be is a collection of scripts and resources with value to those performing Oracle-to-Oracle migrations both within a single operating system and cross-platform between operating system.
Data Dictionary Objects
CDB_ALL_OBJECTS CDB_FREE_SPACE V$PARAMETER
CDB_ALL_TABLES CDB_TEMPFILES V$LOG
CBA_DATA_FILES V$NLS_PARAMETERS  
Roles
DBA    
System Privileges
CREATE ANY DIRECTORY CREATE SESSION SELECT ANY TABLE
CREATE ANY TABLE SELECT ANY DICTIONARY  
 
Pre-Migration Discovery: General
Generate a Feature Usage Report set pagesize 0
set linesize 999
set trimspool on
set trim on

spool /home/oracle/usage_report.html

SELECT output FROM TABLE(dbms_feature_usage_report.display_html);

spool off
Generate Heath Check Report -- go to https://support.oracle.com document 1366133.1, "SQL Tuning Health-Check Script
-- (SQLHC)." Download the script, sqlhc.zip, and run the capture script to obtain
-- valuable information about the source.


SELECT MAX(physical_read_requests) FROM v$sql;

MAX(PHYSICAL_READ_REQUESTS)
---------------------------
                      50336


SELECT sql_id FROM v$sql WHERE physical_read_requests = 50336;

SQL_ID
-------------
4dy540fw5qm7s


set pagesize 0
set linesize 999
set trimspool on
set trim on

spool /home/oracle/sqlhc_report.html

@/home/oracle/sqlhc.sql

Parameter 1:
Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)

Enter value for 1:
D

PL/SQL procedure successfully completed.

Parameter 2:
SQL_ID of the SQL to be analyzed (required)

Enter value for 2:
4dy540fw5qm7s

-- watch and wait when you see the "Generating ..." prompt

spool off

The file sqlhc_20210503_2307_4dy540fw5qm7s.zip was found at $ORACLE_HOME

[oracle@test21 ~]$ unzip sqlhc_20210503_2307_4dy540fw5qm7s.zip
Archive: sqlhc_20210503_2307_4dy540fw5qm7s.zip
inflating: sqlhc_20210503_2307_4dy540fw5qm7s_1_health_check.html
inflating: sqlhc_20210503_2307_4dy540fw5qm7s_2_diagnostics.html
inflating: sqlhc_20210503_2307_4dy540fw5qm7s_3_execution_plans.html
inflating: sqlhc_20210503_2307_4dy540fw5qm7s_4_sql_detail.html
extracting: sqlhc_20210503_2307_4dy540fw5qm7s_9_log.zip
inflating: sqlhc_20210503_2307_4dy540fw5qm7s_10_sql_tuning_advisor.out
inflating: sqlhc_20210503_2307_4dy540fw5qm7s_6_10053_trace_from_cursor.trc
extracting: sqlhc_20210503_2307_4dy540fw5qm7s_8_sqldx.zip
Instance Startup Parameters CREATE PFILE '/home/oracle/pfileFromMem.lst' FROM memory;
Advanced Compression DECLARE
 pfix dbms_id := prvt_compression.COMP_TMP_OBJ_PREFIX || '%';
 cval NATURAL;
BEGIN
  SELECT COUNT(*)
  INTO cval
  FROM seg$ s, ind$ i, obj$ o
  WHERE s.type# = 6
  AND o.owner# NOT IN (
    SELECT user_id FROM dba_users WHERE oracle_maintained = 'Y')
  AND bitand(s.spare1, 2048) = 2048
  AND bitand(s.spare1, 16777216 + 1048576) = 16777216
  AND s.ts# = i.ts#
  AND s.file# = i.file#
  AND s.block# = i.block#
  AND i.obj# = o.obj#
  AND o.name NOT LIKE pfix;

  dbms_output.put_line(TO_CHAR(cval));
END;
/
ADO / Heat Maps / ILM set serveroutput on

DECLARE
 i NUMBER;
 j NUMBER;
 k CLOB;
BEGIN
  sys.dbms_feature_heatmap(i, j, k);
  dbms_output.put_line('1: ' || i);
  dbms_output.put_line('2: ' || j);
  dbms_output.put_line('3: ' || k);
END;
/
Archive Logging SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG
Block Size SELECT tablespace_name, block_size
FROM dba_tablespaces
ORDER BY 2,1;

TABLESPACE_NAME  BLOCK_SIZE
---------------- ----------
AUDIT_TBSP             8192
CDBROOT_TEMP           8192
SYSAUX                 8192
SYSTEM                 8192
TEMP                   8192
UNDOTBS1               8192
USERS                  8192
UWDATA                 8192


SELECT name, value
FROM v$parameter
WHERE name LIKE 'db%k_cache_size'
ORDER BY 1;

NAME               VALUE
------------------ -----
db_16k_cache_size      0
db_2k_cache_size       0
db_32k_cache_size      0
db_4k_cache_size       0
db_8k_cache_size       0
Is Compression Enabled On The Source SELECT owner, table_name
FROM dba_tables
WHERE compression IS NOT NULL
AND compression <> 'DISABLED';

OWNER                     TABLE_NAME
------------------------- ------------------------------
SYS                       COMP_ALL_OPER


SELECT owner, table_name, index_name
FROM dba_indexes
WHERE compression IS NOT NULL
AND compression <> 'DISABLED';

OWNER         TABLE_NAME     INDEX_NAME
------------- -------------- -------------------
CTXSYS        DR$THS_PHRASE  DR_UNIQ_THS_PHRASE


set serveroutput on

DECLARE
 i NUMBER;
 j NUMBER;
 k CLOB;
BEGIN
  dbms_feature_adv_tabcmp(i, j, k);
  dbms_output.put_line('1: ' || i);
  dbms_output.put_line('2: ' || j);
  dbms_output.put_line('3: ' || k);
END;
/

DECLARE
 i NUMBER;
 j NUMBER;
 k CLOB;
BEGIN
  dbms_feature_adv_idxcmp(i, j, k);
  dbms_output.put_line('1: ' || i);
  dbms_output.put_line('2: ' || j);
  dbms_output.put_line('3: ' || k);
END;
/

DECLARE
 i NUMBER;
 j NUMBER;
 k CLOB;
BEGIN
  dbms_feature_hcc(i, j, k);
  dbms_output.put_line('1: ' || i);
  dbms_output.put_line('2: ' || j);
  dbms_output.put_line('3: ' || k);
END;
/

DECLARE
 i NUMBER;
 j NUMBER;
 k CLOB;
BEGIN
  dbms_feature_hccconv(i, j, k);
  dbms_output.put_line('1: ' || i);
  dbms_output.put_line('2: ' || j);
  dbms_output.put_line('3: ' || k);
END;
/

DECLARE
 i NUMBER;
 j NUMBER;
 k CLOB;
BEGIN
  dbms_feature_hccrll(i, j, k);
  dbms_output.put_line('1: ' || i);
  dbms_output.put_line('2: ' || j);
  dbms_output.put_line('3: ' || k);
END;
/
Container Database SELECT con_id, name, open_mode
FROM v$containers
ORDER BY 1;

CON_ID NAME OPEN_MODE
------ ------------------------------ ----------
     1 CDB$ROOT                       READ WRITE
     2 PDB$SEED                       READ ONLY
     3 PDBDEV                         READ WRITE
     4 PDBTEST                        MOUNTED
     5 PDBPROD                        READ WRITE
Corrupt Blocks col corruption_change# format 99999999999999999999

SELECT *
FROM v$database_block_corruption
ORDER BY 1,3;

FILE#          BLOCK#     BLOCKS    CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- --------------------- ---------
        22     162844          4        10594075667556 CORRUPT
CPU Utilization col end_snapshot format a30

SELECT dhss.snap_id, dhs.end_interval_time AS END_SNAPSHOT, ROUND(dhss.maxval,2) MAX_CPU
FROM dba_hist_sysmetric_summary dhss, dba_hist_snapshot dhs
WHERE metric_name = 'CPU Usage Per Sec'
AND dhss.snap_id = dhs.snap_id
AND dhs.end_interval_time > SYSDATE-7
ORDER BY 1;

 SNAP_ID   END_SNAPSHOT                    MAX_CPU
---------- ------------------------------ --------
   21383   10-MAR-21 08.20.19.063 AM          12.5
   21384   10-MAR-21 08.40.23.453 AM          10.2
   21385   10-MAR-21 09.00.27.805 AM          43.0
   21386   10-MAR-21 09.20.32.302 AM          12.4
   21387   10-MAR-21 09.40.36.817 AM          15.6
Database Links col host format a40

SELECT owner, db_link, username, host, valid, intra_cdb
FROM dba_db_links
ORDER BY 1,2;

OWNER    DB_LINK         USERNAME  HOST                   VAL INT
-------- --------------- --------- ---------------------- --- ---
PUBLIC   DBMS_CLRDBLINK            ORACLR_CONNECTION_DATA YES NO
PUBLIC   MGW_AGENT                 MGW_AGENT              YES NO
SYS      SYS_HUB                   SEEDDATA               YES NO
Database Vault DECLARE
 i NUMBER;
 j NUMBER;
 k CLOB;
BEGIN
  dbms_feature_database_vault(i, j, k);
  dbms_output.put_line('1: ' || i);
  dbms_output.put_line('2: ' || j);
  dbms_output.put_line('3: ' || k);
END;
/
1: 0
2: 0
3:


SELECT name, state
FROM dvsys.dv$policy
ORDER BY 1;

NAME                                          STATE
---------------------------------------- ----------
Oracle Account Management Controls                1
Oracle System Protection Controls                 1
Directory Objects SELECT owner, directory_name, directory_path
FROM dba_directories
ORDER BY 1,2;

OWNER  DIRECTORY_NAME           DIRECTORY_PATH
------ ------------------------ --------------------------------------------------
SYS    DATA_PUMP_DIR            /u01/app/oracle/admin/ORCL/dpdump/
SYS    DBMS_OPTIM_ADMINDIR      /u01/app/oracle/product/21.0.0/dbhome_1/rdbms/admin
SYS    DBMS_OPTIM_LOGDIR        /u01/app/oracle/product/21.0.0/dbhome_1/cfgtoollogs
SYS    JAVA$JOX$CUJS$DIRECTORY$ /u01/app/oracle/product/21.0.0/dbhome_1/javavm/admin/
SYS    OPATCH_INST_DIR          /u01/app/oracle/product/21.0.0/dbhome_1/OPatch
SYS    OPATCH_LOG_DIR           /u01/app/oracle/homes/OraDB20Home1/rdbms/log
SYS    OPATCH_SCRIPT_DIR        /u01/app/oracle/product/21.0.0/dbhome_1/QOpatch
SYS    ORACLE_BASE              /u01/app/oracle
SYS    ORACLE_HOME              /u01/app/oracle/product/21.0.0/dbhome_1
SYS    ORACLE_OCM_CONFIG_DIR    /u01/app/oracle/homes/OraDB20Home1/ccr/state
SYS    ORACLE_OCM_CONFIG_DIR2   /u01/app/oracle/homes/OraDB20Home1/ccr/state
SYS    SDO_DIR_ADMIN            /u01/app/oracle/product/21.0.0/dbhome_1/md/admin
SYS    XMLDIR                   /u01/app/oracle/product/21.0.0/dbhome_1/rdbms/xml
SYS    XSDDIR                   /u01/app/oracle/product/21.0.0/dbhome_1/rdbms/xml/schema
Endian Conversion SELECT * FROM v$transportable_platform;
Are External Tables Present SELECT owner, table_name, type_owner, type_name, property
FROM dba_external_tables;

OWNER  TABLE_NAME      TYPE_OWNER  TYPE_NAME         PROPERTY
------ --------------- ----------- ----------------- ------------
SYS    OPATCH_XML_INV  SYS         ORACLE_LOADER     ALL
Is Flashback Logging enabled SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
NO
Force Logging SELECT force_logging FROM v$database;

FORCE_LOGGING
--------------
YES
Free Space SELECT tablespace_name, ROUND(SUM(bytes/1024/1024/1024),1) FREE_GB
FROM dba_free_space
WHERE tablespace_name NOT LIKE '%UNDO%'
GROUP BY tablespace_name
ORDER BY 1;

TABLESPACE_NAME                FREE_GB
------------------------------ -------
AUDIT_TBSP                          .1
OLSTBS                              .1
SYSAUX                              .3
SYSTEM                               0
USERS                                0
UWDATA                              .2
Invalid Objects SELECT owner, object_name, object_type
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY 1,2,3;

OWNER      OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ -------------------------
C##UWCLASS UTL_RPADV                      PACKAGE BODY
In-Memory Database col value format 9999999999

SELECT *
FROM v$sga;

NAME                                 VALUE  CON_ID
------------------------------ ----------- -------
Fixed Size                         9277256       0
Variable Size                   2063597568       0
Database Buffers                3036676096       0
Redo Buffers                       7499776       0
Java Version Compatibility SELECT dbms_java.getVersion
FROM dual;

GETVERSION
------------
21.0.0.0.0.0


SELECT dbms_java.get_JDK_version
FROM dual;

GET_JDK_VERSION
---------------
1.8.0_271
Broken / Disabled Jobs SELECT job, next_date, broken, failures
FROM dba_jobs
ORDER BY 2,1;

no rows selected

SELECT owner, job_name, next_run_date, enabled, failure_count
FROM dba_scheduler_jobs
ORDER BY 2,1;

OWNER  JOB_NAME                       NEXT_RUN_DATE                            ENABL FAILURE_COUNT
------ ------------------------------ ---------------------------------------- ----- -------------
SYS    BSLN_MAINTAIN_STATS_JOB        06-DEC-20 12.00.00.036000 AM -06:00      TRUE              0
SYS    CLEANUP_NON_EXIST_OBJ          05-DEC-20 10.26.05.022000 PM -06:00      TRUE              0
SYS    CLEANUP_ONLINE_IND_BUILD       05-DEC-20 12.06.57.267000 PM -06:00      TRUE              0
SYS    CLEANUP_ONLINE_PMO             05-DEC-20 12.07.37.292000 PM -06:00      TRUE              0
SYS    CLEANUP_TAB_IOT_PMO            05-DEC-20 12.07.07.657000 PM -06:00      TRUE              0
SYS    CLEANUP_TRANSIENT_PKG          05-DEC-20 12.07.27.000000 PM -06:00      TRUE              0
SYS    CLEANUP_TRANSIENT_TYPE         05-DEC-20 10.26.05.022000 PM -06:00      TRUE              0
SYS    DRA_REEVALUATE_OPEN_FAILURES                                            TRUE              0
SYS    FGR$AUTOPURGE_JOB                                                       FALSE             0
SYS    FILE_SIZE_UPD                  08-MAY-21 09.35.55.168000 PM -05:00      TRUE              0
SYS    FILE_WATCHER                                                            FALSE             0
SYS    HM_CREATE_OFFLINE_DICTIONARY                                            FALSE             0
SYS    LOAD_OPATCH_INVENTORY                                                   FALSE             0
SYS    ORA$AUTOTASK_CLEAN             06-DEC-20 03.00.00.119000 AM US/MOUNTAIN TRUE              0
SYS    ORA$PREPLUGIN_BACKUP_JOB                                                FALSE             0
SYS    PMO_DEFERRED_GIDX_MAINT_JOB    06-DEC-20 02.00.00.056000 AM US/MOUNTAIN TRUE              0
SYS    PURGE_LOG                      06-DEC-20 03.00.00.119000 AM US/MOUNTAIN TRUE              0
SYS    RSE$CLEAN_RECOVERABLE_SCRIPT   06-DEC-20 12.00.00.916000 AM US/MOUNTAIN TRUE              0
SYS    SM$CLEAN_AUTO_SPLIT_MERGE      06-DEC-20 12.00.00.978000 AM US/MOUNTAIN TRUE              0
SYS    XMLDB_NFS_CLEANUP_JOB                                                   FALSE             0
Label Security DECLARE
 fb NUMBER;
 ac NUMBER;
 fi CLOB;
BEGIN
  lbacsys.feature_usage(fb, ac, fi);
  dbms_output.put_line(fb);
  dbms_output.put_line(ac);
  dbms_output.put_line(fi);
END;
/
0
0
No. of policies=0

PL/SQL procedure successfully completed.
Large Objects col data_type_owner format a30

SELECT UNIQUE owner, data_type, data_type_owner
FROM dba_tab_cols
WHERE data_type NOT IN ('CHAR', 'DATE', 'NUMBER', 'VARCHAR2', 'FLOAT', 'DOUBLE', 'RAW', 'NCHAR', 'ROWID', 'UROWID')
AND data_type NOT LIKE 'TIMESTAMP%'
AND data_type NOT LIKE 'BINARY%'
and data_type NOT LIKE 'LONG%'
AND owner NOT IN (

  SELECT username FROM dba_users WHERE oracle_maintained = 'Y');
LOB Segments in the System Tablespace SELECT owner, table_name, segment_name, tablespace_name
FROM dba_lobs
WHERE owner NOT IN (
  SELECT username
  FROM dba_users
  WHERE oracle_maintained='Y')
AND tablespace_name IN ('SYSTEM', 'SYSAUX', 'USERS', 'EXAMPLE');

no rows selected
LOB Data Dictionary Corruption SELECT owner, index_name, table_name, index_type, status, segment_created
FROM dba_indexes
WHERE index_type = 'LOB'
AND owner NOT IN (
  SELECT username FROM dba_users WHERE oracle_maintained='Y')
AND table_name NOT IN (
  SELECT table_name from dba_lobs);

OWNER    INDEX_NAME                TABLE_NAME     INDEX_TYPE  STATUS  SEG
-------- ------------------------- -------------- ----------- ------- ---
UWCLASS  SYS_IL0000048594C00101$$  PARTIES_JN     LOB         VALID   YES
UWCLASS  SYS_IL0000048594C00072$$  PARTIES_JN     LOB         VALID   YES
UWCLASS  SYS_IL0000102228C00015$$  WF_ATTRIBUTES  LOB         VALID   YES
UWCLASS  SYS_IL0000102228C00012$$  WF_ATTRIBUTES  LOB         VALID   YES
UWCLASS  SYS_IL0000102228C00031$$  WF_ATTRIBUTES  LOB         VALID   YES
UWCLASS  SYS_IL0000102228C00028$$  WF_ATTRIBUTES  LOB         VALID   YES
Memory Management AMM vs ASMM etc.
Network Access Control List and Network Access Entities SELECT owner, COUNT(*)
FROM dba_xs_aces
GROUP BY owner;

OWNER                       COUNT(*)
------------------------- ----------
SYS                               19
DBSFWUSER                          1


SELECT owner, COUNT(*)
FROM dba_xs_acls
GROUP BY owner;

OWNER                       COUNT(*)
------------------------- ----------
SYS                                5
DBSFWUSER                          1
Net*Services Configuration Take a copy of each of the following files, if they exist, and review them carefully.

CMAN.ORA
LDAP.ORA
LISTENER.ORA
SQLNET.ORA
TNSNAMES.ORA

Remember these files are not necessarily in $ORACLE_HOME/network/admin. The source database may be running on top of Oracle Grid Infrastructure and may have been configured based on an architecture that is not based upon Oracle's published standards.
NUMA -- Linux
numactl --hardware

numactl --show

-- Solaris
lgrpinfo

kstat -m lgrp
Password Version SELECT du.username, du.password_versions, u.ptime
FROM dba_users du, user$ u
WHERE du.username = u.name
AND du.password_versions IS NOT NULL
AND password_versions NOT LIKE '%12G%'
ORDER BY 2;

USERNAME      PASSWORD_VERSIONS  PTIME
------------  -----------------  --------------------
C##SAFEDBA      10G              29-JAN-2007 14:50:43
C##MAINTENANCE  10G 11G          15-MAR-2016 14:50:43
Non-Scalar Data Types SELECT UNIQUE type_owner, type_name
FROM dba_varrays
WHERE type_owner NOT IN (
  SELECT username
  FROM dba_users
  WHERE oracle_maintained='Y')
ORDER BY 1,2;

no rows selected

SELECT owner, type_name
FROM dba_types
WHERE owner IS NOT NULL
AND owner NOT IN (
  SELECT username
  FROM dba_users
  WHERE oracle_maintained='Y')
ORDER BY 1,2;

no rows selected
NLS Parameters SELECT *
FROM v$nls_parameters
ORDER BY 1;

PARAMETER                      VALUE                          CON_ID
------------------------------ ------------------------------ ------
NLS_CALENDAR                   GREGORIAN                           1
NLS_CHARACTERSET               AL32UTF8                            1
NLS_COMP                       BINARY                              1
NLS_CURRENCY                   $                                   1
NLS_DATE_FORMAT                DD-MON-YYYY HH24:MI:SS              1
NLS_DATE_LANGUAGE              AMERICAN                            1
NLS_DUAL_CURRENCY              $                                   1
NLS_ISO_CURRENCY               AMERICA                             1
NLS_LANGUAGE                   AMERICAN                            1
NLS_LENGTH_                    SEMANTICS BYTE                      1
NLS_NCHAR_CHARACTERSET         AL16UTF16                           1
NLS_NCHAR_CONV_EXCP            FALSE                               1
NLS_NUMERIC_CHARACTERS         .,                                  1
NLS_SORT                       BINARY                              1
NLS_TERRITORY                  AMERICA                             1
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM            1
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR        1
NLS_TIME_FORMAT                HH.MI.SSXFF AM                      1
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                  1
Oracle Maintained Schemas SELECT username
FROM dba_users
WHERE oracle_maintained = 'Y'
ORDER BY 1;
Partitioning by User set serveroutput on

DECLARE
 i NUMBER;
 j NUMBER;
 k CLOB;
BEGIN
  sys.dbms_feature_partition_user(i, j, k);
  dbms_output.put_line('1: ' || i);
  dbms_output.put_line('2: ' || j);
  dbms_output.put_line('3: ' || k);
END;
/
1: 1
2: 21.39
3:
1:T:RANGE::1::1::::|1:I:RANGE::1::1::L:NORMAL:NU|1:I:RANGE::1::1::L:LOB:U|1:I: RANGE::1::1::L: NORMAL:P:NORMAL:NU|
Persistent Tablespaces SELECT unique dt.owner, dt.pct_free, dt.pct_used, SUM(ds.bytes/1024/1024/1024) SIZE_GB
FROM dba_tables dt, dba_segments ds
WHERE dt.owner = ds.owner
AND dt.table_name = ds.segment_name
GROUP BY dt.owner, dt.pct_free, dt.pct_used
ORDER BY 1,2,3;

OWNER            PCT_FREE   PCT_USED   SIZE_GB
-------------- ---------- ---------- ----------
AUDSYS                                 .4089355
CTXSYS                 10              .0976563
DBSFWUSER              10              .012207
DBSNMP                 10              .0366211
DVSYS                  10              .1464844
...
OUTLN                  10         40   .0183105
SYS                     0         40   .0366211
...
WMSYS                  10              .1281738
XDB                    10              .201416
XDB                    99              .0427246
Profiles In Use SELECT profile, oracle_maintained, COUNT(*)
FROM dba_users
GROUP BY profile, oracle_maintained;

PROFILE                        O   COUNT(*)
------------------------------ - ----------
DEFAULT                        Y         37
DEFAULT                        N          1
Profiles Not In Use SELECT profile FROM dba_profiles
MINUS
SELECT profile FROM dba_users;

PROFILE
------------------------------
ORA_STIG_PROFILE
Real Application Clusters (RAC) set serveroutput on

DECLARE
 i NUMBER;
 j NUMBER;
 k CLOB;
BEGIN
  sys.dbms_feature_rac(i, j, k);
  dbms_output.put_line('1: ' || i);
  dbms_output.put_line('2: ' || j);
  dbms_output.put_line('3: ' || k);
END;
/

SELECT instance_name, instance_mode
FROM gv$instance;

INSTANCE_NAME    INSTANCE_MO
---------------- -----------
orabase21         REGULAR
Real Application Clusters One-Node set serveroutput on

DECLARE
 i NUMBER;
 j NUMBER;
 k CLOB;
BEGIN
  sys.dbms_feature_rond(i, j, k);
  dbms_output.put_line('1: ' || i);
  dbms_output.put_line('2: ' || j);
  dbms_output.put_line('3: ' || k);
END;
/
Real Application Security set serveroutput on

DECLARE
 i NUMBER;
 j NUMBER;
 k CLOB;
BEGIN
  sys.dbms_feature_ras(i, j, k);
  dbms_output.put_line('1: ' || i);
  dbms_output.put_line('2: ' || j);
  dbms_output.put_line('3: ' || k);
END;
/
Recyclebin SELECT owner, type, COUNT(*)
FROM dba_recyclebin
GROUP BY owner, type
ORDER BY 1,2,3;

SELECT owner, table_name, constraint_name, constraint_type
FROM dba_constraints
WHERE constraint_name LIKE 'BIN$%';

OWNER   TABLE_NAME                     CONSTRAINT_NAME                C
------- ------------------------------ ------------------------------ -
UWCLASS BIN$M5yT4BEoTsWD3p5c0Y1Shg==$0 BIN$qng+j3qhQLONpUKXrXlGFA==$0 C
UWCLASS BIN$M5yT4BEoTsWD3p5c0Y1Shg==$0 BIN$GkS6g5JoQOaU731WQ0lm9g==$0 P
Recyclebin Histograms SELECT owner, COUNT(*)
FROM dba_histograms
WHERE table_name LIKE 'BIN$%'
GROUP BY owner;
Resource Limits col resource_name format a25
col init_alloc format a11
col limit_value format a10
col pct_of_max format 9999.9

SELECT resource_name, initial_allocation INIT_ALLOC, current_utilization CUR_UTIL, max_utilization MAX_UTIL, limit_value,
ROUND(CASE (TRIM(limit_value)) WHEN ('UNLIMITED') THEN NULL ELSE (DECODE(limit_value,0,0,max_utilization/limit_value*100))
END,1) AS pct_of_max
FROM v$resource_limit
ORDER BY 1;

RESOURCE_NAME             INIT_ALLOC    CUR_UTIL   MAX_UTIL LIMIT_VALU PCT_OF_MAX
------------------------- ----------- ---------- ---------- ---------- ----------
branches                          554          0          0  UNLIMITED
cmtcallbk                         554          0          2  UNLIMITED
dml_locks                        2216          0         28  UNLIMITED
enqueue_locks                    6060         43         67       6060        1.1
enqueue_resources                2436         35         69  UNLIMITED
gcs_resources                       0          0          0  UNLIMITED
gcs_shadows                         0          0          0  UNLIMITED
ges_big_msgs                        0          0          0  UNLIMITED
ges_cache_ress                      0          0          0  UNLIMITED
ges_locks                           0          0          0  UNLIMITED
ges_procs                           0          0          0          0         .0
ges_reg_msgs                        0          0          0  UNLIMITED
ges_ress                            0          0          0  UNLIMITED
ges_rsv_msgs                        0          0          0          0         .0
k2q_locks                        1008          0          0  UNLIMITED
max_rollback_segments             554         22         22      65535         .0
max_shared_servers          UNLIMITED          1          2  UNLIMITED
parallel_max_servers               80          8         12      32767         .0
processes                         320         72         94        320       29.4
sessions                          504         79        111        504       22.0
smartio_buffer_memory               0          0          0  UNLIMITED
smartio_metadata_memory             0          0          0  UNLIMITED
smartio_overhead_memory             0          0          0  UNLIMITED
smartio_sessions                    0          0          0  UNLIMITED
sort_segment_locks          UNLIMITED          3          9  UNLIMITED
temporary_table_locks       UNLIMITED          0          5  UNLIMITED
transactions                      554          0          4  UNLIMITED
Resource Plans set serveroutput on

DECLARE
 i NUMBER;
 j NUMBER;
 k CLOB;
BEGIN
  sys.dbms_feature_resource_manager(i, j, k);
  dbms_output.put_line('1: ' || i);
  dbms_output.put_line('2: ' || j);
  dbms_output.put_line('3: ' || k);
END;
/
1: 0
2: 0
3: CPU Management: 1, Other Management: 0
Sample Queries 3-6 sample queries with plan and AWR
Scheduler Job Log -- some version of Oracle with some migration methods have failed when there
-- are too many entries in the Scheduler job log


SELECT COUNT(*)
FROM dba_scheduler_job_log;

 COUNT(*)
---------
    39041


-- the most recent migration failure occurred with 133,000+ rows in the view.
-- and was successful after they were purged.
Straddling Objects col ON1 format a30
col ON2 format a30
col TSP1 format a10
col TSP2 format a10

WITH q1 AS (
  SELECT rownum RN, object1, ts1, object2, ts2, reason_id FROM straddling_ts_objects),
     q2 AS (
  SELECT rownum RN, object1, ts1, object2, ts2, reason_id FROM straddling_ts_objects)
SELECT do1.object_name ON1, tsp1.name TSP1 ,do2.object_name ON2,  tsp2.name TSP2, q1.reason_id
FROM q1, q2, dba_objects do1, dba_objects do2, ts$ tsp1, ts$ tsp2
WHERE q1.rn = q2.rn
AND q1.object1 = do1.object_id
AND q2.object2 = do2.object_id
AND q1.ts1 = tsp1.ts#
AND q2.ts2 = tsp2.ts#;
Supplemental Logging SELECT supplemental_log_data_min MIN, supplemental_log_data_pk PK, supplemental_log_data_ui UI, supplemental_log_data_fk FK, supplemental_log_data_all "ALL", supplemental_log_data_pl PL, supplemental_log_data_sr SR
FROM v$database;

MIN      PK  UI  FK  ALL PL  SR
-------- --- --- --- --- --- ---
NO       NO  NO  NO  NO  NO  NO
Time Zone SELECT dbms_dst.get_latest_timezone_version
FROM dual;

GET_LATEST_TIMEZONE_VERSION
---------------------------
                         34
Temporary Tablespaces

Remember that in a CDB you may have one or more tablespaces in one or more containers
SELECT temporary_tablespace, COUNT(*)
FROM dba_users
GROUP BY temporary_tablespace;

TEMPORARY_TABLESPACE             COUNT(*)
------------------------------ ----------
CDBROOT_TEMP                           36


SELECT temporary_tablespace, COUNT(*)
FROM dba_users
GROUP BY temporary_tablespace;

TEMPORARY_TABLESPACE             COUNT(*)
------------------------------ ----------
TEMP                                   14
CDBROOT_TEMP                           69


ELECT tablespace_name, SUM(bytes)/1024/1024/1024 AS TMP_GB
FROM dba_temp_files
GROUP BY tablespace_name;

TABLESPACE_NAME                    TMP_GB
------------------------------ ----------
TEMP                            .01953125
CDBROOT_TEMP                    .09765625


SELECT file_id, tablespace_name, SUM(bytes)/1024/1024/1024 AS TMP_GB
2 FROM cdb_temp_files
3 GROUP BY file_id, tablespace_name
4* ORDER BY 1
SQL> /

   FILE_ID TABLESPACE_NAME                    TMP_GB
---------- ------------------------------ ----------
         1 TEMP                            .01953125
         4 TEMP                            .125
         5 CDBROOT_TEMP                    .09765625
         6 CDBROOT_TEMP                    .09765625
Trusted Servers col dbname format a20

SELECT *
FROM trusted_list$
ORDER BY 1,2;

DBNAME               USERNAME
-------------------- ------------------------------
+*                   *
Current UNDO Retention

It is not best practice to either create the new database with Oracle's default retention of 900 or just to use the previous retention. Best practice is to check the Undo Advisor for guidance

Remember than in a CDB you will likely have an undo tablespace for each container (root + PDBs)
SQL> col dbname format a20

SQL> show parameter undo

NAME               TYPE       VALUE
------------------ ---------- ----------
temp_undo_enabled  boolean    FALSE
undo_management    string     AUTO
undo_retention     integer    1400
undo_tab=lespace   string     UNDOTBS1

SELECT dbms_undo_adv.required_retention(SYSDATE-30, SYSDATE)
FROM dual;

DBMS_UNDO_ADV.REQUIRED_RETENTION(SYSDATE-30,SYSDATE)
----------------------------------------------------
                                                2879
What is the current database version SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
---------------------------------------------------------------------------------
BANNER_LEGACY                                                                    CON_ID
-------------------------------------------------------------------------------- ------
Oracle Database 21c Enterprise Edition Release 21.1.0.0.0 - Production
Oracle Database 21c Enterprise Edition Release 21.1.0.0.0 - Production
Version 21.1.0.0.0
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production                0
 
Pre-Migration Discovery: GoldenGate
Global Temporary Tables

Supplemental Logging cannot be enabled on Global Temporary Tables
SELECT owner, table_name
FROM dba_tables
WHERE temporary = 'Y'
AND owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')

OWNER      TABLE_NAME
---------- ------------------------------
UWCLASS    GTT_ZIP1
Tables with Identity Columns

There are significant issues around these tables with GoldenGate
SELECT table_name
FROM user_tables
WHERE has_identity = 'YES';

TABLE_NAME
------------------------------
T1
QNAME
Interval Partitioned Tables SELECT owner, object_name, object_type
FROM dba_objects do, partobj$ po
WHERE do.object_id = po.obj#
AND po.flags = 64
AND do.owner NOT IN (
  SELECT username FROM dba_users WHERE oracle_maintained = 'Y')
ORDER BY 1,3,2;

OWNER      OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ -------------------------
UWCLASS    INTERVAL_PART                  TABLE
Private Temporary Tables SELECT owner, table_name
FROM dba_private_temp_tables;

OWNER      OBJECT_NAME                    TABLESPACE_NAME
---------- ------------------------------ ----------------
UWCLASS    ORA$PTT_OCDR1                  TEMP
Sequences col sequence_owner format a30
col sequence_name format a30

SELECT sequence_owner, sequence_name, increment_by, cycle_flag
FROM dba_sequences
WHERE increment_by <> 1
ORDER BY 1,2;

SEQUENCE_OWNER                 SEQUENCE_NAME                  INCREMENT_BY C
------------------------------ ------------------------------ ------------ -
SYS                            IDGEN1$                                  50 N
WMSYS                          WM$LOCK_SEQUENCE                          2 N
Tables Not Validated Primary Keys SELECT owner, constraint_name, table_name
FROM dba_constraints
WHERE constraint_type = 'P'
AND validated = 'NOT VALIDATED'
ORDER BY 1,3;

OWNER   CONSTRAINT_NAME         TABLE_NAME
------- ----------------------- -------------------
SYSTEM  LOGMNR_ATTRCOL$_PK      LOGMNR_ATTRCOL$
SYSTEM  LOGMNR_ATTRIBUTE$_PK    LOGMNR_ATTRIBUTE$
SYSTEM  LOGMNR_CCOL$_PK         LOGMNR_CCOL$
...
SYSTEM  LOGMNR_CDEF$_PK         LOGMNR_CDEF$
SYSTEM  LOGMNR_COL$_PK          LOGMNR_COL$
SYSTEM  LOGMNR_COLTYPE$_PK      LOGMNR_COLTYPE$
Tables without Unique Constraints or Unique Indexes SELECT owner, table_name
FROM dba_tables
WHERE (owner, table_name) NOT IN (
  SELECT owner, table_name
  FROM dba_constraints
  WHERE constraint_type IN ('P','U'))
AND (owner, table_name) NOT IN (
  SELECT owner, table_name
  FROM dba_indexes
  WHERE uniqueness = 'UNIQUE')
AND owner NOT IN (
  SELECT username
  FROM dba_users
  WHERE oracle_maintained = 'Y')
ORDER BY 1,2;

OWNER                     TABLE_NAME
------------------------- -------------------------
AWRMINER                  DISK_SIZE
AWRMINER                  HIST_SNAPSHOT
AWRMINER                  MEMSTATS
AWRMINER                  OSSTAT
AWRMINER                  STAT_NV_PAIRS
GGADMIN                   MIG_TOOLS_LOG
GGADMIN                   MIG_TOOLS_RESTORE_POINT
GGADMIN                   SPFILE_MODS
 
Pre-Migration Discovery: Transportable Tablespaces
Self-contained tablespace check BEGIN
  IF dbms_tts.isselfcontained('uwdata, users', FALSE, TRUE) THEN
    dbms_output.put_line('Self Contained');
  ELSE
    dbms_output.put_line('Not Self Contained');
  END IF;
END;
/
Self Contained

PL/SQL procedure successfully completed.
Metadata Collection DECLARE
 delim CONSTANT CHAR(1) := ',';
 dbname         v$database.name%TYPE;
 instnum        v$instance.instance_number%TYPE;
 servname       v$instance.host_name%TYPE;
 dbsize         INTEGER;
 nodecnt        INTEGER;
 sgasize        INTEGER;
 acc            NUMBER;
 mcc            NUMBER;
 x              NUMBER;
BEGIN
  SELECT name INTO dbname FROM v$database;

  SELECT MAX(maxval)
  INTO x
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'CPU Usage Per Sec';

  SELECT UNIQUE instance_number
  INTO instnum
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'CPU Usage Per Sec'
  AND maxval = x;

  SELECT COUNT(*)
  INTO nodecnt
  FROM gv$instance;

  SELECT host_name
  INTO servname
  FROM gv$instance
  WHERE instance_number = instnum;

  SELECT MAX(DECODE(metric_name, 'CPU Usage Per Sec',
         ROUND(average/100,2), NULL)) avg_cpu_count,
         MAX(DECODE(metric_name, 'CPU Usage Per Sec',
         ROUND(maxval/100,2), NULL)) max_cpu_count
  INTO acc, mcc
  FROM (
    SELECT snap_id, instance_number INST, metric_name, average, maxval
    FROM dba_hist_sysmetric_summary
    WHERE instance_number = instnum
    AND metric_name = 'CPU Usage Per Sec');

  -- dbsize
  WITH d AS (SELECT SUM(bytes/1024/1024/1024) SB FROM dba_data_files),
  t AS (SELECT SUM(bytes/1024/1024/1024) SB FROM dba_temp_files)
  SELECT CEIL(SUM(d.sb + t.sb))
  INTO dbsize
  FROM d, t;

  -- sga
  SELECT CEIL(SUM(value))
  INTO sgasize
  FROM gv$sga
  WHERE inst_id = instnum;

  dbms_output.put_line(dbname || delim || servname || delim || dbsize || delim || acc ||
  delim || mcc || delim || nodecnt || delim || sgasize);
END;
/
 
Migration: File Transport
Data file copy using ASMCMD SELECT 'asmcmd cp /backup/PRODHR/datafiles/' || SUBSTR(name, INSTR(name,'/',-1,1)+1) || ' /+DATA/PRODHR/' || SUBSTR(name,INSTR(name, '/' , -1, 1) + 1) || ';' AS CP
FROM v$datafile
WHERE file# IN (
  SELECT file_id
  FROM cdb_data_files
  WHERE tablespace_name NOT IN ('SYSTEM', 'SYSAUX'))
ORDER BY file#;
Data file copy using DBMS_FILE_TRANSFER TBD
Data file Endian Conversion CONVERT DATAFILE
 '/tmp/transport_from_aix/u01/uwdata01.dbf',
 '/tmp/transport_from_aix/u01/uwdata02.dbf',
 '/tmp/transport_from_aix/u03/users01.dbf',
 '/tmp/transport_from_aix/u03/users02.dbf'
DB_FILE_NAME_CONVERT
 '/app/oracle/product/oradata','/stage/oradata',
 '/tmp/transport_from_solaris/hr','/stage/oradata'
FROM PLATFORM 'Linux IA (64-bit)'
 
Post-Migration
Stats Collection exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

exec dbms_stats.gather_system_stats('INTERVAL', 15); -- under load

PL/SQL procedure successfully completed.
Mitigate loss of privileges created by changes to SELECT ANY DICTIONARY CREATE ROLE missingsadprivs;

GRANT SELECT ON sys.default_pwd$ TO missingsadprivs;
GRANT SELECT ON sys.enc$ TO missingsadprivs;
GRANT SELECT ON sys.link$ TO missingsadprivs;
GRANT SELECT ON sys.user$ TO missingsadprivs;
GRANT SELECT ON sys.user_history$ TO missingsadprivs;
GRANT SELECT ON sys.xs$verifiers$ TO missingsadprivs;

GRANT missingsadprivs TO <username>;
External Jobs If external jobs will be run update $ORACLE_HOME/rdbms/admin/externaljob.ora
It is likely runuser should be oracle and rungroup should be oinstall

Related Topics
Database Security
Built-in Functions
Built-in Packages
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx