database_migration_assistant.sql
-- ------------------------------------------------------------------------------------- File Name : database_migration_assistant.sql-- Author : Pierre Montbleau-- Description : Oracle database migration assessment and planning utility-- Purpose : Assess migration readiness, estimate effort, and generate migration plans-- Call Syntax : @F:\DBA\Scripts\database_migration_assistant.sql (migration_type) (target_version) (assessment_level)-- Parameters : migration_type - Type of migration (UPGRADE, PLATFORM, CLOUD, VERSION)-- target_version - Target database version (11g, 12c, 19c, 21c)-- assessment_level - Assessment depth (BASIC, DETAILED, COMPREHENSIVE)-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesset verify OFF
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_migration_assistant_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '_' || '&3' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting Database Migration Assistant...PROMPT ========================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Migration Type: &1PROMPT Target Version: &2PROMPT Assessment Level: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_migration_type VARCHAR2(20) := UPPER('&1'); v_target_version VARCHAR2(10) := UPPER('&2'); v_assessment_level VARCHAR2(20) := UPPER('&3'); v_current_version VARCHAR2(50);BEGIN IF v_migration_type NOT IN ('UPGRADE', 'PLATFORM', 'CLOUD', 'VERSION') THEN RAISE_APPLICATION_ERROR(-20001, 'Migration type must be UPGRADE, PLATFORM, CLOUD, or VERSION'); END IF; IF v_target_version NOT IN ('11G', '12C', '19C', '21C') THEN RAISE_APPLICATION_ERROR(-20002, 'Target version must be 11G, 12C, 19C, or 21C'); END IF; IF v_assessment_level NOT IN ('BASIC', 'DETAILED', 'COMPREHENSIVE') THEN RAISE_APPLICATION_ERROR(-20003, 'Assessment level must be BASIC, DETAILED, or COMPREHENSIVE'); END IF; -- Get current database version SELECT version INTO v_current_version FROM v$instance; DBMS_OUTPUT.PUT_LINE('Current Database Version: ' || v_current_version); DBMS_OUTPUT.PUT_LINE('Target Version: ' || v_target_version); DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- DATABASE COMPATIBILITY ASSESSMENT: Version compatibility analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. DATABASE COMPATIBILITY ASSESSMENTPROMPT =====================================
COLUMN compatibility_check FORMAT A40COLUMN current_status FORMAT A25COLUMN migration_impact FORMAT A15COLUMN recommendation FORMAT A60
SELECT 'Database Version Compatibility' as compatibility_check, CASE WHEN version LIKE '11%' AND '&2' IN ('19C', '21C') THEN 'DIRECT UPGRADE' WHEN version LIKE '12%' AND '&2' IN ('19C', '21C') THEN 'DIRECT UPGRADE' WHEN version LIKE '19%' AND '&2' = '21C' THEN 'DIRECT UPGRADE' ELSE 'UPGRADE PATH REQUIRED' END as current_status, CASE WHEN version LIKE '11%' AND '&2' IN ('19C', '21C') THEN 'MEDIUM' WHEN version LIKE '12%' AND '&2' IN ('19C', '21C') THEN 'LOW' ELSE 'HIGH' END as migration_impact, CASE WHEN version LIKE '11%' AND '&2' IN ('19C', '21C') THEN 'Plan for multi-version upgrade path' WHEN version LIKE '12%' AND '&2' IN ('19C', '21C') THEN 'Direct upgrade supported' ELSE 'Review Oracle upgrade documentation' END as recommendationFROM v$instanceUNION ALLSELECT 'Character Set Compatibility', 'COMPATIBLE', 'LOW', 'Character set migration not required'FROM v$nls_parametersWHERE parameter = 'NLS_CHARACTERSET' AND value IN ('AL32UTF8', 'UTF8')UNION ALLSELECT 'Block Size Compatibility', CASE WHEN (SELECT value FROM v$parameter WHERE name = 'db_block_size') = 8192 THEN 'STANDARD' ELSE 'CUSTOM' END, 'LOW', 'Standard block size - no issues'FROM dualWHERE (SELECT value FROM v$parameter WHERE name = 'db_block_size') = 8192ORDER BY 3 DESC;
-- ------------------------------------------------------------------------------------- DATABASE SIZE AND COMPLEXITY: Migration scope assessment-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. DATABASE SIZE AND COMPLEXITY ASSESSMENTPROMPT ===========================================
COLUMN size_metric FORMAT A30COLUMN current_value FORMAT A20COLUMN migration_complexity FORMAT A15COLUMN estimated_duration FORMAT A15
SELECT 'Total Database Size (GB)' as size_metric, TO_CHAR(ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2)) as current_value, CASE WHEN SUM(bytes) / 1024 / 1024 / 1024 < 100 THEN 'LOW' WHEN SUM(bytes) / 1024 / 1024 / 1024 < 500 THEN 'MEDIUM' ELSE 'HIGH' END as migration_complexity, CASE WHEN SUM(bytes) / 1024 / 1024 / 1024 < 100 THEN '2-4 hours' WHEN SUM(bytes) / 1024 / 1024 / 1024 < 500 THEN '4-8 hours' ELSE '8+ hours' END as estimated_durationFROM dba_segmentsUNION ALLSELECT 'Number of Schemas', TO_CHAR(COUNT(DISTINCT owner)), CASE WHEN COUNT(DISTINCT owner) < 10 THEN 'LOW' WHEN COUNT(DISTINCT owner) < 50 THEN 'MEDIUM' ELSE 'HIGH' END, CASE WHEN COUNT(DISTINCT owner) < 10 THEN '1-2 hours' WHEN COUNT(DISTINCT owner) < 50 THEN '2-4 hours' ELSE '4+ hours' ENDFROM dba_objectsWHERE owner NOT LIKE 'SYS%'UNION ALLSELECT 'Total Object Count', TO_CHAR(COUNT(*)), CASE WHEN COUNT(*) < 10000 THEN 'LOW' WHEN COUNT(*) < 50000 THEN 'MEDIUM' ELSE 'HIGH' END, CASE WHEN COUNT(*) < 10000 THEN '1-2 hours' WHEN COUNT(*) < 50000 THEN '2-4 hours' ELSE '4+ hours' ENDFROM dba_objectsWHERE owner NOT LIKE 'SYS%'ORDER BY 3 DESC;
-- ------------------------------------------------------------------------------------- DEPRECATED FEATURES ANALYSIS: Identify deprecated and obsolete features-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. DEPRECATED FEATURES ANALYSISPROMPT ================================
COLUMN feature_name FORMAT A50COLUMN detected_usage FORMAT A15COLUMN deprecation_status FORMAT A20COLUMN migration_action FORMAT A60
SELECT name as feature_name, TO_CHAR(detected_usages) as detected_usage, CASE WHEN currently_used = 'TRUE' THEN 'ACTIVELY USED' ELSE 'NOT USED' END as deprecation_status, 'Review and plan replacement strategy' as migration_actionFROM dba_feature_usage_statisticsWHERE name LIKE '%deprecated%' OR name LIKE '%obsolete%' AND detected_usages > 0 AND UPPER('&3') IN ('DETAILED', 'COMPREHENSIVE')ORDER BY 2 DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- DATABASE OBJECT COMPATIBILITY: Object-level compatibility checks-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. DATABASE OBJECT COMPATIBILITYPROMPT =================================
COLUMN object_type FORMAT A20COLUMN object_count FORMAT 999,999COLUMN compatibility_risk FORMAT A15COLUMN migration_notes FORMAT A60
SELECT object_type, COUNT(*) as object_count, CASE object_type WHEN 'JAVA CLASS' THEN 'HIGH' WHEN 'TYPE' THEN 'MEDIUM' WHEN 'PROCEDURE' THEN 'LOW' WHEN 'FUNCTION' THEN 'LOW' WHEN 'PACKAGE' THEN 'LOW' WHEN 'TRIGGER' THEN 'LOW' ELSE 'LOW' END as compatibility_risk, CASE object_type WHEN 'JAVA CLASS' THEN 'Java version compatibility check required' WHEN 'TYPE' THEN 'Review complex type definitions' WHEN 'PROCEDURE' THEN 'Standard PL/SQL - low risk' WHEN 'FUNCTION' THEN 'Standard PL/SQL - low risk' ELSE 'Standard object - minimal risk' END as migration_notesFROM dba_objectsWHERE owner NOT LIKE 'SYS%' AND object_type IN ('TABLE', 'INDEX', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER', 'TYPE', 'JAVA CLASS')GROUP BY object_typeORDER BY 3 DESC;
-- ------------------------------------------------------------------------------------- STORAGE MIGRATION ASSESSMENT: Tablespace and datafile analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. STORAGE MIGRATION ASSESSMENTPROMPT ================================
COLUMN tablespace_name FORMAT A25COLUMN size_gb FORMAT 999,999.99COLUMN datafile_count FORMAT 999COLUMN migration_complexity FORMAT A15COLUMN migration_strategy FORMAT A40
SELECT tablespace_name, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) as size_gb, COUNT(*) as datafile_count, CASE WHEN SUM(bytes) / 1024 / 1024 / 1024 > 100 THEN 'HIGH' WHEN COUNT(*) > 50 THEN 'MEDIUM' ELSE 'LOW' END as migration_complexity, CASE WHEN SUM(bytes) / 1024 / 1024 / 1024 > 100 THEN 'Consider transportable tablespaces' WHEN COUNT(*) > 50 THEN 'Consolidate datafiles during migration' ELSE 'Standard migration approach' END as migration_strategyFROM dba_data_filesGROUP BY tablespace_nameHAVING SUM(bytes) / 1024 / 1024 / 1024 > 1 -- Larger than 1GBORDER BY 2 DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- MIGRATION EFFORT ESTIMATION: Comprehensive effort analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. MIGRATION EFFORT ESTIMATIONPROMPT ===============================
COLUMN effort_category FORMAT A35COLUMN complexity_level FORMAT A15COLUMN estimated_hours FORMAT 999COLUMN resource_requirements FORMAT A40
SELECT 'Database Assessment and Planning' as effort_category, 'MEDIUM' as complexity_level, 8 as estimated_hours, 'Senior DBA, 1 person' as resource_requirementsFROM dualUNION ALLSELECT 'Pre-Migration Testing', 'HIGH', 16, 'DBA Team, Application Team'FROM dualUNION ALLSELECT 'Actual Migration Execution', CASE WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 100 THEN 'LOW' WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 500 THEN 'MEDIUM' ELSE 'HIGH' END, CASE WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 100 THEN 4 WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 500 THEN 8 ELSE 16 END, 'Primary DBA, Backup DBA'FROM dualUNION ALLSELECT 'Post-Migration Validation', 'HIGH', 8, 'DBA Team, QA Team'FROM dualUNION ALLSELECT 'Application Testing', 'HIGH', 24, 'Application Team, Business Users'FROM dualORDER BY 3 DESC;
-- ------------------------------------------------------------------------------------- MIGRATION RISK ASSESSMENT: Risk identification and mitigation-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. MIGRATION RISK ASSESSMENTPROMPT =============================
COLUMN risk_category FORMAT A25COLUMN risk_description FORMAT A60COLUMN probability FORMAT A15COLUMN impact FORMAT A15COLUMN mitigation_strategy FORMAT A80
SELECT 'Data Compatibility' as risk_category, 'Data type or character set incompatibility issues' as risk_description, 'LOW' as probability, 'HIGH' as impact, 'Perform comprehensive pre-migration testing with sample data' as mitigation_strategyFROM dualUNION ALLSELECT 'Performance Regression', 'Potential performance degradation after migration', 'MEDIUM', 'HIGH', 'Conduct performance benchmarking before and after migration'FROM dualUNION ALLSELECT 'Application Compatibility', 'Application code may not be compatible with new version', 'MEDIUM', 'HIGH', 'Test all critical application functions in test environment'FROM dualUNION ALLSELECT 'Migration Timeline', 'Unexpected issues causing timeline overruns', 'HIGH', 'MEDIUM', 'Build contingency time (20-30%) into migration schedule'FROM dualUNION ALLSELECT 'Resource Availability', 'Key team members unavailable during migration', 'LOW', 'MEDIUM', 'Cross-train team members and document procedures'FROM dualORDER BY 4,3;
-- ------------------------------------------------------------------------------------- MIGRATION STRATEGY RECOMMENDATIONS: Recommended migration approaches-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. MIGRATION STRATEGY RECOMMENDATIONSPROMPT ======================================
set lin 1000COLUMN migration_approach FORMAT A25COLUMN suitability FORMAT A15COLUMN pros FORMAT A80COLUMN cons FORMAT A80
SELECT 'In-Place Upgrade' as migration_approach, 'HIGH' as suitability, 'Fastest method, minimal data movement, preserves all settings' as pros, 'Higher risk, requires downtime, rollback complex' as consFROM dualWHERE UPPER('&1') = 'UPGRADE' AND (SELECT version FROM v$instance) LIKE '19%' AND '&2' = '21C'UNION ALLSELECT 'Data Pump Export/Import', 'MEDIUM', 'Clean target database, flexible timing, good for major version jumps', 'Longer downtime, requires sufficient storage, object recreation'FROM dualWHERE UPPER('&1') IN ('VERSION', 'PLATFORM')UNION ALLSELECT 'Transportable Tablespaces', 'HIGH', 'Very fast for large databases, minimal downtime, physical data movement', 'Complex setup, platform limitations, requires compatible versions'FROM dualWHERE UPPER('&1') = 'PLATFORM' AND (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) > 100UNION ALLSELECT 'GoldenGate Replication', 'MEDIUM', 'Near-zero downtime, continuous data sync, real-time migration', 'Complex setup, licensing cost, additional infrastructure'FROM dualWHERE UPPER('&1') = 'CLOUD' AND UPPER('&3') = 'COMPREHENSIVE'ORDER BY 2 DESC;
-- ------------------------------------------------------------------------------------- MIGRATION CHECKLIST: Pre-migration preparation tasks-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. MIGRATION PREPARATION CHECKLISTPROMPT ===================================
COLUMN checklist_item FORMAT A60COLUMN status FORMAT A15COLUMN responsible FORMAT A20COLUMN deadline FORMAT A15
SELECT 'Complete database backup before migration' as checklist_item, 'PENDING' as status, 'Primary DBA' as responsible, 'T-1 Day' as deadlineFROM dualUNION ALLSELECT 'Validate backup restore procedure', 'PENDING', 'Backup DBA', 'T-7 Days'FROM dualUNION ALLSELECT 'Test migration in non-production environment', 'PENDING', 'Migration Team', 'T-14 Days'FROM dualUNION ALLSELECT 'Communicate downtime schedule to stakeholders', 'PENDING', 'Project Manager', 'T-7 Days'FROM dualUNION ALLSELECT 'Prepare rollback plan and procedures', 'PENDING', 'Primary DBA', 'T-3 Days'FROM dualUNION ALLSELECT 'Document post-migration validation steps', 'PENDING', 'QA Team', 'T-5 Days'FROM dualORDER BY 4;
-- ------------------------------------------------------------------------------------- COMPLETION: Migration assessment summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ========================================PROMPT DATABASE MIGRATION ASSESSMENT COMPLETEDPROMPT ========================================PROMPT Migration Type: &1PROMPT Target Version: &2PROMPT Assessment Level: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Next Steps:PROMPT 1. Review migration risks and complexity assessmentPROMPT 2. Develop detailed migration project planPROMPT 3. Schedule migration testing in non-productionPROMPT 4. Prepare stakeholders and communication planPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesset verify OFF
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_migration_assistant_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '_' || '&3' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting Database Migration Assistant...PROMPT ========================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Migration Type: &1PROMPT Target Version: &2PROMPT Assessment Level: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_migration_type VARCHAR2(20) := UPPER('&1'); v_target_version VARCHAR2(10) := UPPER('&2'); v_assessment_level VARCHAR2(20) := UPPER('&3'); v_current_version VARCHAR2(50);BEGIN IF v_migration_type NOT IN ('UPGRADE', 'PLATFORM', 'CLOUD', 'VERSION') THEN RAISE_APPLICATION_ERROR(-20001, 'Migration type must be UPGRADE, PLATFORM, CLOUD, or VERSION'); END IF; IF v_target_version NOT IN ('11G', '12C', '19C', '21C') THEN RAISE_APPLICATION_ERROR(-20002, 'Target version must be 11G, 12C, 19C, or 21C'); END IF; IF v_assessment_level NOT IN ('BASIC', 'DETAILED', 'COMPREHENSIVE') THEN RAISE_APPLICATION_ERROR(-20003, 'Assessment level must be BASIC, DETAILED, or COMPREHENSIVE'); END IF; -- Get current database version SELECT version INTO v_current_version FROM v$instance; DBMS_OUTPUT.PUT_LINE('Current Database Version: ' || v_current_version); DBMS_OUTPUT.PUT_LINE('Target Version: ' || v_target_version); DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- DATABASE COMPATIBILITY ASSESSMENT: Version compatibility analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. DATABASE COMPATIBILITY ASSESSMENTPROMPT =====================================
COLUMN compatibility_check FORMAT A40COLUMN current_status FORMAT A25COLUMN migration_impact FORMAT A15COLUMN recommendation FORMAT A60
SELECT 'Database Version Compatibility' as compatibility_check, CASE WHEN version LIKE '11%' AND '&2' IN ('19C', '21C') THEN 'DIRECT UPGRADE' WHEN version LIKE '12%' AND '&2' IN ('19C', '21C') THEN 'DIRECT UPGRADE' WHEN version LIKE '19%' AND '&2' = '21C' THEN 'DIRECT UPGRADE' ELSE 'UPGRADE PATH REQUIRED' END as current_status, CASE WHEN version LIKE '11%' AND '&2' IN ('19C', '21C') THEN 'MEDIUM' WHEN version LIKE '12%' AND '&2' IN ('19C', '21C') THEN 'LOW' ELSE 'HIGH' END as migration_impact, CASE WHEN version LIKE '11%' AND '&2' IN ('19C', '21C') THEN 'Plan for multi-version upgrade path' WHEN version LIKE '12%' AND '&2' IN ('19C', '21C') THEN 'Direct upgrade supported' ELSE 'Review Oracle upgrade documentation' END as recommendationFROM v$instanceUNION ALLSELECT 'Character Set Compatibility', 'COMPATIBLE', 'LOW', 'Character set migration not required'FROM v$nls_parametersWHERE parameter = 'NLS_CHARACTERSET' AND value IN ('AL32UTF8', 'UTF8')UNION ALLSELECT 'Block Size Compatibility', CASE WHEN (SELECT value FROM v$parameter WHERE name = 'db_block_size') = 8192 THEN 'STANDARD' ELSE 'CUSTOM' END, 'LOW', 'Standard block size - no issues'FROM dualWHERE (SELECT value FROM v$parameter WHERE name = 'db_block_size') = 8192ORDER BY 3 DESC;
-- ------------------------------------------------------------------------------------- DATABASE SIZE AND COMPLEXITY: Migration scope assessment-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. DATABASE SIZE AND COMPLEXITY ASSESSMENTPROMPT ===========================================
COLUMN size_metric FORMAT A30COLUMN current_value FORMAT A20COLUMN migration_complexity FORMAT A15COLUMN estimated_duration FORMAT A15
SELECT 'Total Database Size (GB)' as size_metric, TO_CHAR(ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2)) as current_value, CASE WHEN SUM(bytes) / 1024 / 1024 / 1024 < 100 THEN 'LOW' WHEN SUM(bytes) / 1024 / 1024 / 1024 < 500 THEN 'MEDIUM' ELSE 'HIGH' END as migration_complexity, CASE WHEN SUM(bytes) / 1024 / 1024 / 1024 < 100 THEN '2-4 hours' WHEN SUM(bytes) / 1024 / 1024 / 1024 < 500 THEN '4-8 hours' ELSE '8+ hours' END as estimated_durationFROM dba_segmentsUNION ALLSELECT 'Number of Schemas', TO_CHAR(COUNT(DISTINCT owner)), CASE WHEN COUNT(DISTINCT owner) < 10 THEN 'LOW' WHEN COUNT(DISTINCT owner) < 50 THEN 'MEDIUM' ELSE 'HIGH' END, CASE WHEN COUNT(DISTINCT owner) < 10 THEN '1-2 hours' WHEN COUNT(DISTINCT owner) < 50 THEN '2-4 hours' ELSE '4+ hours' ENDFROM dba_objectsWHERE owner NOT LIKE 'SYS%'UNION ALLSELECT 'Total Object Count', TO_CHAR(COUNT(*)), CASE WHEN COUNT(*) < 10000 THEN 'LOW' WHEN COUNT(*) < 50000 THEN 'MEDIUM' ELSE 'HIGH' END, CASE WHEN COUNT(*) < 10000 THEN '1-2 hours' WHEN COUNT(*) < 50000 THEN '2-4 hours' ELSE '4+ hours' ENDFROM dba_objectsWHERE owner NOT LIKE 'SYS%'ORDER BY 3 DESC;
-- ------------------------------------------------------------------------------------- DEPRECATED FEATURES ANALYSIS: Identify deprecated and obsolete features-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. DEPRECATED FEATURES ANALYSISPROMPT ================================
COLUMN feature_name FORMAT A50COLUMN detected_usage FORMAT A15COLUMN deprecation_status FORMAT A20COLUMN migration_action FORMAT A60
SELECT name as feature_name, TO_CHAR(detected_usages) as detected_usage, CASE WHEN currently_used = 'TRUE' THEN 'ACTIVELY USED' ELSE 'NOT USED' END as deprecation_status, 'Review and plan replacement strategy' as migration_actionFROM dba_feature_usage_statisticsWHERE name LIKE '%deprecated%' OR name LIKE '%obsolete%' AND detected_usages > 0 AND UPPER('&3') IN ('DETAILED', 'COMPREHENSIVE')ORDER BY 2 DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- DATABASE OBJECT COMPATIBILITY: Object-level compatibility checks-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. DATABASE OBJECT COMPATIBILITYPROMPT =================================
COLUMN object_type FORMAT A20COLUMN object_count FORMAT 999,999COLUMN compatibility_risk FORMAT A15COLUMN migration_notes FORMAT A60
SELECT object_type, COUNT(*) as object_count, CASE object_type WHEN 'JAVA CLASS' THEN 'HIGH' WHEN 'TYPE' THEN 'MEDIUM' WHEN 'PROCEDURE' THEN 'LOW' WHEN 'FUNCTION' THEN 'LOW' WHEN 'PACKAGE' THEN 'LOW' WHEN 'TRIGGER' THEN 'LOW' ELSE 'LOW' END as compatibility_risk, CASE object_type WHEN 'JAVA CLASS' THEN 'Java version compatibility check required' WHEN 'TYPE' THEN 'Review complex type definitions' WHEN 'PROCEDURE' THEN 'Standard PL/SQL - low risk' WHEN 'FUNCTION' THEN 'Standard PL/SQL - low risk' ELSE 'Standard object - minimal risk' END as migration_notesFROM dba_objectsWHERE owner NOT LIKE 'SYS%' AND object_type IN ('TABLE', 'INDEX', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER', 'TYPE', 'JAVA CLASS')GROUP BY object_typeORDER BY 3 DESC;
-- ------------------------------------------------------------------------------------- STORAGE MIGRATION ASSESSMENT: Tablespace and datafile analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. STORAGE MIGRATION ASSESSMENTPROMPT ================================
COLUMN tablespace_name FORMAT A25COLUMN size_gb FORMAT 999,999.99COLUMN datafile_count FORMAT 999COLUMN migration_complexity FORMAT A15COLUMN migration_strategy FORMAT A40
SELECT tablespace_name, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) as size_gb, COUNT(*) as datafile_count, CASE WHEN SUM(bytes) / 1024 / 1024 / 1024 > 100 THEN 'HIGH' WHEN COUNT(*) > 50 THEN 'MEDIUM' ELSE 'LOW' END as migration_complexity, CASE WHEN SUM(bytes) / 1024 / 1024 / 1024 > 100 THEN 'Consider transportable tablespaces' WHEN COUNT(*) > 50 THEN 'Consolidate datafiles during migration' ELSE 'Standard migration approach' END as migration_strategyFROM dba_data_filesGROUP BY tablespace_nameHAVING SUM(bytes) / 1024 / 1024 / 1024 > 1 -- Larger than 1GBORDER BY 2 DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- MIGRATION EFFORT ESTIMATION: Comprehensive effort analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. MIGRATION EFFORT ESTIMATIONPROMPT ===============================
COLUMN effort_category FORMAT A35COLUMN complexity_level FORMAT A15COLUMN estimated_hours FORMAT 999COLUMN resource_requirements FORMAT A40
SELECT 'Database Assessment and Planning' as effort_category, 'MEDIUM' as complexity_level, 8 as estimated_hours, 'Senior DBA, 1 person' as resource_requirementsFROM dualUNION ALLSELECT 'Pre-Migration Testing', 'HIGH', 16, 'DBA Team, Application Team'FROM dualUNION ALLSELECT 'Actual Migration Execution', CASE WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 100 THEN 'LOW' WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 500 THEN 'MEDIUM' ELSE 'HIGH' END, CASE WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 100 THEN 4 WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 500 THEN 8 ELSE 16 END, 'Primary DBA, Backup DBA'FROM dualUNION ALLSELECT 'Post-Migration Validation', 'HIGH', 8, 'DBA Team, QA Team'FROM dualUNION ALLSELECT 'Application Testing', 'HIGH', 24, 'Application Team, Business Users'FROM dualORDER BY 3 DESC;
-- ------------------------------------------------------------------------------------- MIGRATION RISK ASSESSMENT: Risk identification and mitigation-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. MIGRATION RISK ASSESSMENTPROMPT =============================
COLUMN risk_category FORMAT A25COLUMN risk_description FORMAT A60COLUMN probability FORMAT A15COLUMN impact FORMAT A15COLUMN mitigation_strategy FORMAT A80
SELECT 'Data Compatibility' as risk_category, 'Data type or character set incompatibility issues' as risk_description, 'LOW' as probability, 'HIGH' as impact, 'Perform comprehensive pre-migration testing with sample data' as mitigation_strategyFROM dualUNION ALLSELECT 'Performance Regression', 'Potential performance degradation after migration', 'MEDIUM', 'HIGH', 'Conduct performance benchmarking before and after migration'FROM dualUNION ALLSELECT 'Application Compatibility', 'Application code may not be compatible with new version', 'MEDIUM', 'HIGH', 'Test all critical application functions in test environment'FROM dualUNION ALLSELECT 'Migration Timeline', 'Unexpected issues causing timeline overruns', 'HIGH', 'MEDIUM', 'Build contingency time (20-30%) into migration schedule'FROM dualUNION ALLSELECT 'Resource Availability', 'Key team members unavailable during migration', 'LOW', 'MEDIUM', 'Cross-train team members and document procedures'FROM dualORDER BY 4,3;
-- ------------------------------------------------------------------------------------- MIGRATION STRATEGY RECOMMENDATIONS: Recommended migration approaches-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. MIGRATION STRATEGY RECOMMENDATIONSPROMPT ======================================
set lin 1000COLUMN migration_approach FORMAT A25COLUMN suitability FORMAT A15COLUMN pros FORMAT A80COLUMN cons FORMAT A80
SELECT 'In-Place Upgrade' as migration_approach, 'HIGH' as suitability, 'Fastest method, minimal data movement, preserves all settings' as pros, 'Higher risk, requires downtime, rollback complex' as consFROM dualWHERE UPPER('&1') = 'UPGRADE' AND (SELECT version FROM v$instance) LIKE '19%' AND '&2' = '21C'UNION ALLSELECT 'Data Pump Export/Import', 'MEDIUM', 'Clean target database, flexible timing, good for major version jumps', 'Longer downtime, requires sufficient storage, object recreation'FROM dualWHERE UPPER('&1') IN ('VERSION', 'PLATFORM')UNION ALLSELECT 'Transportable Tablespaces', 'HIGH', 'Very fast for large databases, minimal downtime, physical data movement', 'Complex setup, platform limitations, requires compatible versions'FROM dualWHERE UPPER('&1') = 'PLATFORM' AND (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) > 100UNION ALLSELECT 'GoldenGate Replication', 'MEDIUM', 'Near-zero downtime, continuous data sync, real-time migration', 'Complex setup, licensing cost, additional infrastructure'FROM dualWHERE UPPER('&1') = 'CLOUD' AND UPPER('&3') = 'COMPREHENSIVE'ORDER BY 2 DESC;
-- ------------------------------------------------------------------------------------- MIGRATION CHECKLIST: Pre-migration preparation tasks-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. MIGRATION PREPARATION CHECKLISTPROMPT ===================================
COLUMN checklist_item FORMAT A60COLUMN status FORMAT A15COLUMN responsible FORMAT A20COLUMN deadline FORMAT A15
SELECT 'Complete database backup before migration' as checklist_item, 'PENDING' as status, 'Primary DBA' as responsible, 'T-1 Day' as deadlineFROM dualUNION ALLSELECT 'Validate backup restore procedure', 'PENDING', 'Backup DBA', 'T-7 Days'FROM dualUNION ALLSELECT 'Test migration in non-production environment', 'PENDING', 'Migration Team', 'T-14 Days'FROM dualUNION ALLSELECT 'Communicate downtime schedule to stakeholders', 'PENDING', 'Project Manager', 'T-7 Days'FROM dualUNION ALLSELECT 'Prepare rollback plan and procedures', 'PENDING', 'Primary DBA', 'T-3 Days'FROM dualUNION ALLSELECT 'Document post-migration validation steps', 'PENDING', 'QA Team', 'T-5 Days'FROM dualORDER BY 4;
-- ------------------------------------------------------------------------------------- COMPLETION: Migration assessment summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ========================================PROMPT DATABASE MIGRATION ASSESSMENT COMPLETEDPROMPT ========================================PROMPT Migration Type: &1PROMPT Target Version: &2PROMPT Assessment Level: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Next Steps:PROMPT 1. Review migration risks and complexity assessmentPROMPT 2. Develop detailed migration project planPROMPT 3. Schedule migration testing in non-productionPROMPT 4. Prepare stakeholders and communication planPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------