Oracle Database Migrations
Version 20c

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
     
     
     
Roles
     
     
     
System Privileges
     
     
     
 
Pre-Migration Discovery: General
Generate a Feature Usage Report spool /home/oracle/usage_report.html

SELECT output FROM TABLE(dbms_feature_usage_report.display_html);

spool off
Instance Startup Parameters CREATE PFILE '/home/oracle/pfileFromMem.lst' FROM memory;
Is Archive Logging enabled SELECT log_mode FROM v$database;
Is Compression Enabled On The Source SELECT owner, table_name
FROM dba_tables
WHERE compression IS NOT NULL
AND compression <> 'DISABLED';

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

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;
/
Is the Source a Contrainer Database


FILE#          BLOCK#     BLOCKS    CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- --------------------- ---------
        22     162844          4        10594075667556 CORRUPT
Are there corrupt blocks in the Source 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
Is Database Vault in use 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;
/

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

NAME                                          STATE
---------------------------------------- ----------
Oracle Account Management Controls                1
Oracle System Protection Controls                 1
Is Endian Conversion Required SELECT * FROM v$transportable_platform;
Are External Tables Present SELECT owner, table_name, type_owner, type_name, property
FROM dba_external_tables;
Is Flashback Logging enabled SELECT flashback_on FROM v$database;
Is Force Logging enabled SELECT force_logging FROM v$database;
Are Invalid Objects Present SELECT owner, object_name, object_type
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY 1,2,3;
Is Oracle In-Memory Database In Use 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
Are Broken and/or Disabled Jobs Present SELECT job, next_date, broken, failures
FROM dba_jobs
ORDER BY 2,1;

SELECT owner, job_name, next_date, enabled, failure_count
FROM dba_scheduler_jobs
ORDER BY 2,1;
Are Application Large Objects Present SELECT UNIQUE owner, data_type, data_type_owner
FROM dba_tab_cols
WHERE data_type NOT IN ('CHAR', 'DATE', 'NUMBER', 'VARCHAR2')

AND owner NOT IN (

  SELECT username FROM dba_users WHERE oracle_maintained = 'Y');
LOB segments in a 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');
Are Recyclebin Objects Present SELECT owner, type, COUNT(*)
FROM dba_recyclebin
GROUP BY owner, type
ORDER BY 1,2,3;
Non-Scalar Data Types SELECT UNIQUE type_owner, type_name
FROM dba_varrays
ORDER BY 1,2;

SELECT owner, type_name
FROM dba_types
WHERE owner IS NOT NULL
ORDER BY 1,2;
NLS Parameters SELECT *
FROM v$nls_parameters
ORDER BY 1;
Is Supplemental Logging enabled 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;
 
Pre-Migration Discovery: Transportable Tablespaces
  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;
/
 
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)'

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

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