Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
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.
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;
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;
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;
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);
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;
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;
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;
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;
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;
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;
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;
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;
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