database_cloud_migration.sql
-- ------------------------------------------------------------------------------------- File Name : database_cloud_migration.sql-- Author : Pierre Montbleau-- Description : Oracle database cloud migration assessment and planning utility-- Purpose : Assess cloud readiness, estimate migration effort, and generate cloud migration plans-- Call Syntax : @F:\DBA\Scripts\database_cloud_migration.sql (cloud_target) (migration_method) (assessment_level)-- Parameters : cloud_target - Target cloud platform (AWS, AZURE, OCI, GCP, MULTI)-- migration_method - Migration approach (LIFT_SHIFT, REPLATFORM, REFACTOR, REHOST)-- assessment_level - Assessment depth (BASIC, STANDARD, COMPREHENSIVE)-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
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_cloud_migration_report_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || UPPER('&1') || '_' || UPPER('&2') || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
-- Start output redirectionSPOOL &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 500SET PAGESIZE 1000SET SERVEROUTPUT ON
PROMPT Starting Database Cloud Migration Assessment...PROMPT ==============================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Cloud Target: &1PROMPT Migration Method: &2PROMPT Assessment Level: &3PROMPT Timestamp: &&DATE1 &&TIME1PROMPT Output File: &output_filenamePROMPT
-- Validate parametersDECLARE v_cloud_target VARCHAR2(20) := UPPER('&1'); v_migration_method VARCHAR2(20) := UPPER('&2'); v_assessment_level VARCHAR2(20) := UPPER('&3');BEGIN IF v_cloud_target NOT IN ('AWS', 'AZURE', 'OCI', 'GCP', 'MULTI') THEN RAISE_APPLICATION_ERROR(-20001, 'Cloud target must be AWS, AZURE, OCI, GCP, or MULTI'); END IF; IF v_migration_method NOT IN ('LIFT_SHIFT', 'REPLATFORM', 'REFACTOR', 'REHOST') THEN RAISE_APPLICATION_ERROR(-20002, 'Migration method must be LIFT_SHIFT, REPLATFORM, REFACTOR, or REHOST'); END IF; IF v_assessment_level NOT IN ('BASIC', 'STANDARD', 'COMPREHENSIVE') THEN RAISE_APPLICATION_ERROR(-20003, 'Assessment level must be BASIC, STANDARD, or COMPREHENSIVE'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful'); DBMS_OUTPUT.PUT_LINE('Target Cloud: ' || v_cloud_target); DBMS_OUTPUT.PUT_LINE('Migration Strategy: ' || v_migration_method);EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- CLOUD READINESS ASSESSMENT: Database compatibility with cloud platforms-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. CLOUD MIGRATION READINESS ASSESSMENTPROMPT ========================================
COLUMN readiness_category FORMAT A30COLUMN current_status FORMAT A20COLUMN cloud_compatibility FORMAT A15COLUMN migration_effort FORMAT A15COLUMN recommendation FORMAT A60
SELECT 'Database Version Compatibility' as readiness_category, version as current_status, CASE WHEN version LIKE '11%' THEN 'LIMITED' WHEN version LIKE '12%' THEN 'COMPATIBLE' WHEN version LIKE '19%' THEN 'OPTIMAL' WHEN version LIKE '21%' THEN 'OPTIMAL' ELSE 'UNSUPPORTED' END as cloud_compatibility, CASE WHEN version LIKE '11%' THEN 'HIGH' WHEN version LIKE '12%' THEN 'MEDIUM' ELSE 'LOW' END as migration_effort, CASE WHEN version LIKE '11%' THEN 'Upgrade to 19c or 21c before migration' WHEN version LIKE '12%' THEN 'Consider upgrade during migration' ELSE 'Version optimal for cloud migration' END as recommendationFROM v$instanceUNION ALLSELECT 'Character Set Compatibility', (SELECT value FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET'), CASE WHEN (SELECT value FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET') IN ('AL32UTF8', 'UTF8') THEN 'COMPATIBLE' ELSE 'CONVERSION NEEDED' END, CASE WHEN (SELECT value FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET') IN ('AL32UTF8', 'UTF8') THEN 'LOW' ELSE 'HIGH' END, CASE WHEN (SELECT value FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET') IN ('AL32UTF8', 'UTF8') THEN 'Character set cloud-ready' ELSE 'Plan for character set conversion' ENDFROM dualUNION ALLSELECT 'Database Size', ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) || ' GB', CASE WHEN SUM(bytes) / 1024 / 1024 / 1024 < 1000 THEN 'OPTIMAL' WHEN SUM(bytes) / 1024 / 1024 / 1024 < 5000 THEN 'MANAGEABLE' ELSE 'CHALLENGING' END, CASE WHEN SUM(bytes) / 1024 / 1024 / 1024 < 1000 THEN 'LOW' WHEN SUM(bytes) / 1024 / 1024 / 1024 < 5000 THEN 'MEDIUM' ELSE 'HIGH' END, CASE WHEN SUM(bytes) / 1024 / 1024 / 1024 > 5000 THEN 'Consider data archiving or partitioning' ELSE 'Size manageable for cloud migration' ENDFROM dba_segmentsORDER BY 4 DESC; -- Use column position 4 (migration_effort) instead of alias
-- ------------------------------------------------------------------------------------- CLOUD SERVICE MAPPING: Oracle database to cloud service mapping-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. CLOUD SERVICE MAPPING RECOMMENDATIONSPROMPT =========================================
COLUMN cloud_provider FORMAT A10COLUMN recommended_service FORMAT A35COLUMN service_tier FORMAT A20COLUMN estimated_cost_tier FORMAT A15COLUMN fit_assessment FORMAT A15
SELECT 'AWS' as cloud_provider, CASE WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 100 THEN 'Amazon RDS for Oracle' ELSE 'Oracle on EC2' END as recommended_service, CASE WHEN (SELECT COUNT(*) FROM v$session WHERE type = 'USER') < 50 THEN 'db.t3.medium' WHEN (SELECT COUNT(*) FROM v$session WHERE type = 'USER') < 200 THEN 'db.r5.large' ELSE 'db.r5.xlarge' END as service_tier, 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 as estimated_cost_tier, 'EXCELLENT' as fit_assessmentFROM dualWHERE UPPER('AWS') IN ('AWS', 'MULTI')UNION ALLSELECT 'AZURE', CASE WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 100 THEN 'Azure Database for Oracle' ELSE 'Oracle on Azure VMs' END, CASE WHEN (SELECT COUNT(*) FROM v$session WHERE type = 'USER') < 50 THEN 'Standard_E4s_v3' WHEN (SELECT COUNT(*) FROM v$session WHERE type = 'USER') < 200 THEN 'Standard_E8s_v3' ELSE 'Standard_E16s_v3' END, 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, 'EXCELLENT'FROM dualWHERE UPPER('AZURE') IN ('AZURE', 'MULTI') -- Changed from 'AWS' to 'AZURE'UNION ALLSELECT 'OCI', 'Oracle Base Database Service', CASE WHEN (SELECT COUNT(*) FROM v$session WHERE type = 'USER') < 50 THEN 'VM.Standard.E2.2' WHEN (SELECT COUNT(*) FROM v$session WHERE type = 'USER') < 200 THEN 'VM.Standard.E2.4' ELSE 'VM.Standard.E2.8' END, 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, 'OPTIMAL'FROM dualWHERE UPPER('OCI') IN ('OCI', 'MULTI') -- Changed from 'AWS' to 'OCI'ORDER BY 1; -- Use column position instead of alias
-- ------------------------------------------------------------------------------------- MIGRATION EFFORT ESTIMATION: Comprehensive migration effort analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. MIGRATION EFFORT ESTIMATIONPROMPT ===============================
COLUMN effort_category FORMAT A35COLUMN complexity_level FORMAT A15COLUMN estimated_hours FORMAT 999COLUMN resource_requirements FORMAT A40COLUMN dependencies FORMAT A50
SELECT 'Database Assessment and Planning' as effort_category, 'MEDIUM' as complexity_level, 40 as estimated_hours, 'Cloud Architect, Senior DBA' as resource_requirements, 'Business requirements, compliance review' as dependenciesFROM dualUNION ALLSELECT 'Pre-Migration Optimization', 'HIGH', 80, 'DBA Team, Performance Engineer', 'Performance baseline, application dependencies'FROM dualUNION ALLSELECT 'Cloud Environment Setup', 'MEDIUM', 20, 'Cloud Engineer, Network Admin', 'Cloud account, networking, security groups'FROM dualUNION ALLSELECT 'Data 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 8 WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 500 THEN 24 ELSE 48 END, 'Primary DBA, Cloud DBA', 'Migration tool selection, network bandwidth'FROM dualUNION ALLSELECT 'Application Cutover', 'HIGH', 16, 'Application Team, DBA Team', 'Application testing, DNS updates'FROM dualUNION ALLSELECT 'Post-Migration Validation', 'HIGH', 24, 'QA Team, Business Users', 'Test cases, performance benchmarks'FROM dualORDER BY 3 DESC;
-- ------------------------------------------------------------------------------------- MIGRATION METHOD ANALYSIS: Strategy-specific considerations-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. MIGRATION STRATEGY ANALYSISPROMPT ===============================
COLUMN migration_strategy FORMAT A15COLUMN description FORMAT A60COLUMN pros FORMAT A80COLUMN cons FORMAT A80COLUMN suitability FORMAT A15
SELECT 'LIFT_SHIFT' as migration_strategy, 'Migrate as-is without changes (Rehost)' as description, 'Fastest migration, minimal changes, predictable outcome' as pros, 'Limited cloud benefits, may not optimize costs, same technical debt' as cons, 'HIGH' as suitabilityFROM dualWHERE UPPER('LIFT_SHIFT') = 'LIFT_SHIFT'UNION ALLSELECT 'REPLATFORM', 'Migrate to cloud-managed service (Lift-tinker-shift)', 'Reduced management overhead, some cloud benefits, good balance', 'Limited customization, vendor lock-in, service limitations', 'MEDIUM'FROM dualWHERE UPPER('LIFT_SHIFT') = 'REPLATFORM'UNION ALLSELECT 'REFACTOR', 'Re-architect for cloud-native services (Lift-reshape-shift)', 'Maximum cloud benefits, cost optimization, modern architecture', 'Highest effort, requires application changes, longest timeline', 'LOW'FROM dualWHERE UPPER('LIFT_SHIFT') = 'REFACTOR'UNION ALLSELECT 'REHOST', 'Move to cloud VMs without OS changes', 'Good control, familiar management, flexible configuration', 'Management overhead, less cloud-native, security responsibility', 'MEDIUM'FROM dualWHERE UPPER('LIFT_SHIFT') = 'REHOST'ORDER BY 5 DESC; -- Use column position (5th column) instead of alias
-- ------------------------------------------------------------------------------------- COST ESTIMATION: Cloud migration and operation cost analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. CLOUD MIGRATION COST ESTIMATIONPROMPT ===================================
COLUMN cost_category FORMAT A25COLUMN estimated_monthly_cost FORMAT A20COLUMN cost_drivers FORMAT A60COLUMN cost_savings_potential FORMAT A20COLUMN recommendations FORMAT A80
SELECT 'Compute Resources' as cost_category, CASE WHEN (SELECT COUNT(*) FROM v$session WHERE type = 'USER') < 50 THEN '$500 - $1,000' WHEN (SELECT COUNT(*) FROM v$session WHERE type = 'USER') < 200 THEN '$1,000 - $2,500' ELSE '$2,500 - $5,000' END as estimated_monthly_cost, 'vCPUs, memory, instance type, reserved vs on-demand' as cost_drivers, '20-40%' as cost_savings_potential, 'Use reserved instances, right-size instances, auto-scaling' as recommendationsFROM dualUNION ALLSELECT 'Storage Costs', CASE WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 100 THEN '$100 - $300' WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 500 THEN '$300 - $800' ELSE '$800 - $2,000' END, 'Storage type (SSD/HDD), IOPS, backup retention, snapshots', '15-30%', 'Use tiered storage, implement data lifecycle policies'FROM dualUNION ALLSELECT 'Data Transfer', '$50 - $200', 'Outbound data, cross-region transfer, internet egress', '10-20%', 'Optimize data location, use CDN, minimize cross-region transfers'FROM dualUNION ALLSELECT 'Backup and DR', CASE WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 100 THEN '$100 - $250' ELSE '$250 - $600' END, 'Backup storage, snapshot frequency, retention period', '25-50%', 'Use cloud-native backup services, implement lifecycle policies'FROM dualORDER BY 2 DESC;
-- ------------------------------------------------------------------------------------- MIGRATION RISK ASSESSMENT: Cloud migration risks and mitigation-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. CLOUD MIGRATION RISK ASSESSMENTPROMPT ===================================
COLUMN risk_category FORMAT A30COLUMN risk_description FORMAT A60COLUMN probability FORMAT A15COLUMN impact FORMAT A15COLUMN mitigation_strategy FORMAT A80
SELECT 'Data Security' as risk_category, 'Data exposure during migration or in cloud environment' as risk_description, 'MEDIUM' as probability, 'HIGH' as impact, 'Implement encryption in transit and at rest, use private networking' as mitigation_strategyFROM dualUNION ALLSELECT 'Performance', 'Performance degradation in cloud environment', 'HIGH', 'HIGH', 'Conduct performance testing, right-size instances, optimize queries'FROM dualUNION ALLSELECT 'Cost Overruns', 'Unexpected cloud costs exceeding budget', 'HIGH', 'MEDIUM', 'Implement cost monitoring, use budgeting tools, right-size resources'FROM dualUNION ALLSELECT 'Compliance', 'Regulatory compliance issues in cloud', 'MEDIUM', 'HIGH', 'Review cloud provider compliance certifications, data residency'FROM dualUNION ALLSELECT 'Technical Compatibility', 'Database features not supported in cloud', 'LOW', 'MEDIUM', 'Conduct feature compatibility assessment, plan workarounds'FROM dualORDER BY 4, 3;
-- ------------------------------------------------------------------------------------- MIGRATION TOOL RECOMMENDATIONS: Cloud migration tools and utilities-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. MIGRATION TOOLS AND UTILITIESPROMPT =================================
COLUMN migration_phase FORMAT A20COLUMN recommended_tool FORMAT A35COLUMN tool_type FORMAT A15COLUMN capabilities FORMAT A60COLUMN licensing FORMAT A15
SELECT 'Assessment' as migration_phase, 'Oracle Cloud Migrations' as recommended_tool, 'ORACLE' as tool_type, 'Database assessment, sizing, compatibility checking' as capabilities, 'FREE' as licensingFROM dualWHERE UPPER('AWS') IN ('OCI', 'MULTI')UNION ALLSELECT 'Assessment', 'AWS DMS Schema Conversion', 'AWS', 'Schema analysis, conversion assessment, effort estimation', 'FREE'FROM dualWHERE UPPER('AWS') IN ('AWS', 'MULTI')UNION ALLSELECT 'Data Migration', 'Oracle Data Pump', 'ORACLE', 'High-speed data export/import, metadata migration', 'INCLUDED'FROM dualUNION ALLSELECT 'Data Migration', 'AWS Database Migration Service', 'AWS', 'Continuous data replication, minimal downtime migration', 'PAID'FROM dualWHERE UPPER('AWS') IN ('AWS', 'MULTI')UNION ALLSELECT 'Data Migration', 'Azure Database Migration Service', 'AZURE', 'Online migration, schema and data movement', 'PAID'FROM dualWHERE UPPER('AWS') IN ('AZURE', 'MULTI')UNION ALLSELECT 'Validation', 'Oracle SQL Developer', 'ORACLE', 'Data validation, object comparison, testing', 'FREE'FROM dualORDER BY 1, 3; -- Use column positions: 1 = migration_phase, 3 = tool_type
-- ------------------------------------------------------------------------------------- MIGRATION TIMELINE: Project timeline and milestones-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. CLOUD MIGRATION TIMELINEPROMPT ============================
COLUMN migration_phase FORMAT A35COLUMN duration_weeks FORMAT 999COLUMN critical_milestones FORMAT A60COLUMN dependencies FORMAT A50COLUMN resource_requirements FORMAT A40
SELECT 'Planning and Assessment' as migration_phase, 2 as duration_weeks, 'Business case approval, cloud provider selection' as critical_milestones, 'Budget approval, stakeholder alignment' as dependencies, 'Cloud Architect, Project Manager' as resource_requirementsFROM dualUNION ALLSELECT 'Design and Preparation', 3, 'Architecture design, security planning, network setup', 'Cloud account setup, compliance review', 'Solution Architect, Security Engineer'FROM dualUNION ALLSELECT 'Pre-Migration Testing', 2, 'Test migration, performance validation, application testing', 'Test environment setup, data subset', 'DBA Team, QA Team'FROM dualUNION ALLSELECT 'Production Migration', 1, 'Data migration, application cutover, go-live', 'Change approval, backup completion', 'All Teams'FROM dualUNION ALLSELECT 'Post-Migration Optimization', 4, 'Performance tuning, cost optimization, documentation', 'Production stabilization', 'DBA Team, Cloud Engineer'FROM dualORDER BY 2;
-- ------------------------------------------------------------------------------------- CLOUD MIGRATION CHECKLIST: Pre-migration preparation tasks-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. CLOUD MIGRATION CHECKLISTPROMPT =============================
COLUMN checklist_item FORMAT A70COLUMN status FORMAT A15COLUMN responsible FORMAT A20COLUMN deadline FORMAT A15COLUMN priority FORMAT A10
SELECT 'Complete cloud provider account setup and billing configuration' as checklist_item, 'PENDING' as status, 'Cloud Architect' as responsible, 'T-8 Weeks' as deadline, 'HIGH' as priorityFROM dualUNION ALLSELECT 'Establish cloud networking and security configurations', 'PENDING', 'Network Engineer', 'T-6 Weeks', 'HIGH'FROM dualUNION ALLSELECT 'Conduct comprehensive database assessment and sizing', 'PENDING', 'Senior DBA', 'T-6 Weeks', 'HIGH'FROM dualUNION ALLSELECT 'Perform test migration and validate results', 'PENDING', 'DBA Team', 'T-4 Weeks', 'HIGH'FROM dualUNION ALLSELECT 'Develop rollback plan and procedures', 'PENDING', 'Project Manager', 'T-3 Weeks', 'MEDIUM'FROM dualUNION ALLSELECT 'Train operations team on cloud management', 'PENDING', 'Training Lead', 'T-2 Weeks', 'MEDIUM'FROM dualORDER BY 5, 4;
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment-- -----------------------------------------------------------------------------------
CLEAR COLUMNSSET VERIFY ON
-- ------------------------------------------------------------------------------------- COMPLETION: Cloud migration assessment summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ==============================================PROMPT CLOUD MIGRATION ASSESSMENT COMPLETEDPROMPT ==============================================PROMPT Cloud Target: &1PROMPT Migration Method: &2PROMPT Assessment Level: &3PROMPT Completion Time: &&DATE1 &&TIME1PROMPT Output File: &output_filenamePROMPT PROMPT Next Steps:PROMPT 1. Review migration risks and complexity assessmentPROMPT 2. Develop detailed cloud migration project planPROMPT 3. Conduct proof-of-concept in cloud environmentPROMPT 4. Secure budget and stakeholder approvalsPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ON
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
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_cloud_migration_report_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || UPPER('&1') || '_' || UPPER('&2') || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
-- Start output redirectionSPOOL &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 500SET PAGESIZE 1000SET SERVEROUTPUT ON
PROMPT Starting Database Cloud Migration Assessment...PROMPT ==============================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Cloud Target: &1PROMPT Migration Method: &2PROMPT Assessment Level: &3PROMPT Timestamp: &&DATE1 &&TIME1PROMPT Output File: &output_filenamePROMPT
-- Validate parametersDECLARE v_cloud_target VARCHAR2(20) := UPPER('&1'); v_migration_method VARCHAR2(20) := UPPER('&2'); v_assessment_level VARCHAR2(20) := UPPER('&3');BEGIN IF v_cloud_target NOT IN ('AWS', 'AZURE', 'OCI', 'GCP', 'MULTI') THEN RAISE_APPLICATION_ERROR(-20001, 'Cloud target must be AWS, AZURE, OCI, GCP, or MULTI'); END IF; IF v_migration_method NOT IN ('LIFT_SHIFT', 'REPLATFORM', 'REFACTOR', 'REHOST') THEN RAISE_APPLICATION_ERROR(-20002, 'Migration method must be LIFT_SHIFT, REPLATFORM, REFACTOR, or REHOST'); END IF; IF v_assessment_level NOT IN ('BASIC', 'STANDARD', 'COMPREHENSIVE') THEN RAISE_APPLICATION_ERROR(-20003, 'Assessment level must be BASIC, STANDARD, or COMPREHENSIVE'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful'); DBMS_OUTPUT.PUT_LINE('Target Cloud: ' || v_cloud_target); DBMS_OUTPUT.PUT_LINE('Migration Strategy: ' || v_migration_method);EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- CLOUD READINESS ASSESSMENT: Database compatibility with cloud platforms-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. CLOUD MIGRATION READINESS ASSESSMENTPROMPT ========================================
COLUMN readiness_category FORMAT A30COLUMN current_status FORMAT A20COLUMN cloud_compatibility FORMAT A15COLUMN migration_effort FORMAT A15COLUMN recommendation FORMAT A60
SELECT 'Database Version Compatibility' as readiness_category, version as current_status, CASE WHEN version LIKE '11%' THEN 'LIMITED' WHEN version LIKE '12%' THEN 'COMPATIBLE' WHEN version LIKE '19%' THEN 'OPTIMAL' WHEN version LIKE '21%' THEN 'OPTIMAL' ELSE 'UNSUPPORTED' END as cloud_compatibility, CASE WHEN version LIKE '11%' THEN 'HIGH' WHEN version LIKE '12%' THEN 'MEDIUM' ELSE 'LOW' END as migration_effort, CASE WHEN version LIKE '11%' THEN 'Upgrade to 19c or 21c before migration' WHEN version LIKE '12%' THEN 'Consider upgrade during migration' ELSE 'Version optimal for cloud migration' END as recommendationFROM v$instanceUNION ALLSELECT 'Character Set Compatibility', (SELECT value FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET'), CASE WHEN (SELECT value FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET') IN ('AL32UTF8', 'UTF8') THEN 'COMPATIBLE' ELSE 'CONVERSION NEEDED' END, CASE WHEN (SELECT value FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET') IN ('AL32UTF8', 'UTF8') THEN 'LOW' ELSE 'HIGH' END, CASE WHEN (SELECT value FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET') IN ('AL32UTF8', 'UTF8') THEN 'Character set cloud-ready' ELSE 'Plan for character set conversion' ENDFROM dualUNION ALLSELECT 'Database Size', ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) || ' GB', CASE WHEN SUM(bytes) / 1024 / 1024 / 1024 < 1000 THEN 'OPTIMAL' WHEN SUM(bytes) / 1024 / 1024 / 1024 < 5000 THEN 'MANAGEABLE' ELSE 'CHALLENGING' END, CASE WHEN SUM(bytes) / 1024 / 1024 / 1024 < 1000 THEN 'LOW' WHEN SUM(bytes) / 1024 / 1024 / 1024 < 5000 THEN 'MEDIUM' ELSE 'HIGH' END, CASE WHEN SUM(bytes) / 1024 / 1024 / 1024 > 5000 THEN 'Consider data archiving or partitioning' ELSE 'Size manageable for cloud migration' ENDFROM dba_segmentsORDER BY 4 DESC; -- Use column position 4 (migration_effort) instead of alias
-- ------------------------------------------------------------------------------------- CLOUD SERVICE MAPPING: Oracle database to cloud service mapping-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. CLOUD SERVICE MAPPING RECOMMENDATIONSPROMPT =========================================
COLUMN cloud_provider FORMAT A10COLUMN recommended_service FORMAT A35COLUMN service_tier FORMAT A20COLUMN estimated_cost_tier FORMAT A15COLUMN fit_assessment FORMAT A15
SELECT 'AWS' as cloud_provider, CASE WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 100 THEN 'Amazon RDS for Oracle' ELSE 'Oracle on EC2' END as recommended_service, CASE WHEN (SELECT COUNT(*) FROM v$session WHERE type = 'USER') < 50 THEN 'db.t3.medium' WHEN (SELECT COUNT(*) FROM v$session WHERE type = 'USER') < 200 THEN 'db.r5.large' ELSE 'db.r5.xlarge' END as service_tier, 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 as estimated_cost_tier, 'EXCELLENT' as fit_assessmentFROM dualWHERE UPPER('AWS') IN ('AWS', 'MULTI')UNION ALLSELECT 'AZURE', CASE WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 100 THEN 'Azure Database for Oracle' ELSE 'Oracle on Azure VMs' END, CASE WHEN (SELECT COUNT(*) FROM v$session WHERE type = 'USER') < 50 THEN 'Standard_E4s_v3' WHEN (SELECT COUNT(*) FROM v$session WHERE type = 'USER') < 200 THEN 'Standard_E8s_v3' ELSE 'Standard_E16s_v3' END, 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, 'EXCELLENT'FROM dualWHERE UPPER('AZURE') IN ('AZURE', 'MULTI') -- Changed from 'AWS' to 'AZURE'UNION ALLSELECT 'OCI', 'Oracle Base Database Service', CASE WHEN (SELECT COUNT(*) FROM v$session WHERE type = 'USER') < 50 THEN 'VM.Standard.E2.2' WHEN (SELECT COUNT(*) FROM v$session WHERE type = 'USER') < 200 THEN 'VM.Standard.E2.4' ELSE 'VM.Standard.E2.8' END, 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, 'OPTIMAL'FROM dualWHERE UPPER('OCI') IN ('OCI', 'MULTI') -- Changed from 'AWS' to 'OCI'ORDER BY 1; -- Use column position instead of alias
-- ------------------------------------------------------------------------------------- MIGRATION EFFORT ESTIMATION: Comprehensive migration effort analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. MIGRATION EFFORT ESTIMATIONPROMPT ===============================
COLUMN effort_category FORMAT A35COLUMN complexity_level FORMAT A15COLUMN estimated_hours FORMAT 999COLUMN resource_requirements FORMAT A40COLUMN dependencies FORMAT A50
SELECT 'Database Assessment and Planning' as effort_category, 'MEDIUM' as complexity_level, 40 as estimated_hours, 'Cloud Architect, Senior DBA' as resource_requirements, 'Business requirements, compliance review' as dependenciesFROM dualUNION ALLSELECT 'Pre-Migration Optimization', 'HIGH', 80, 'DBA Team, Performance Engineer', 'Performance baseline, application dependencies'FROM dualUNION ALLSELECT 'Cloud Environment Setup', 'MEDIUM', 20, 'Cloud Engineer, Network Admin', 'Cloud account, networking, security groups'FROM dualUNION ALLSELECT 'Data 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 8 WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 500 THEN 24 ELSE 48 END, 'Primary DBA, Cloud DBA', 'Migration tool selection, network bandwidth'FROM dualUNION ALLSELECT 'Application Cutover', 'HIGH', 16, 'Application Team, DBA Team', 'Application testing, DNS updates'FROM dualUNION ALLSELECT 'Post-Migration Validation', 'HIGH', 24, 'QA Team, Business Users', 'Test cases, performance benchmarks'FROM dualORDER BY 3 DESC;
-- ------------------------------------------------------------------------------------- MIGRATION METHOD ANALYSIS: Strategy-specific considerations-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. MIGRATION STRATEGY ANALYSISPROMPT ===============================
COLUMN migration_strategy FORMAT A15COLUMN description FORMAT A60COLUMN pros FORMAT A80COLUMN cons FORMAT A80COLUMN suitability FORMAT A15
SELECT 'LIFT_SHIFT' as migration_strategy, 'Migrate as-is without changes (Rehost)' as description, 'Fastest migration, minimal changes, predictable outcome' as pros, 'Limited cloud benefits, may not optimize costs, same technical debt' as cons, 'HIGH' as suitabilityFROM dualWHERE UPPER('LIFT_SHIFT') = 'LIFT_SHIFT'UNION ALLSELECT 'REPLATFORM', 'Migrate to cloud-managed service (Lift-tinker-shift)', 'Reduced management overhead, some cloud benefits, good balance', 'Limited customization, vendor lock-in, service limitations', 'MEDIUM'FROM dualWHERE UPPER('LIFT_SHIFT') = 'REPLATFORM'UNION ALLSELECT 'REFACTOR', 'Re-architect for cloud-native services (Lift-reshape-shift)', 'Maximum cloud benefits, cost optimization, modern architecture', 'Highest effort, requires application changes, longest timeline', 'LOW'FROM dualWHERE UPPER('LIFT_SHIFT') = 'REFACTOR'UNION ALLSELECT 'REHOST', 'Move to cloud VMs without OS changes', 'Good control, familiar management, flexible configuration', 'Management overhead, less cloud-native, security responsibility', 'MEDIUM'FROM dualWHERE UPPER('LIFT_SHIFT') = 'REHOST'ORDER BY 5 DESC; -- Use column position (5th column) instead of alias
-- ------------------------------------------------------------------------------------- COST ESTIMATION: Cloud migration and operation cost analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. CLOUD MIGRATION COST ESTIMATIONPROMPT ===================================
COLUMN cost_category FORMAT A25COLUMN estimated_monthly_cost FORMAT A20COLUMN cost_drivers FORMAT A60COLUMN cost_savings_potential FORMAT A20COLUMN recommendations FORMAT A80
SELECT 'Compute Resources' as cost_category, CASE WHEN (SELECT COUNT(*) FROM v$session WHERE type = 'USER') < 50 THEN '$500 - $1,000' WHEN (SELECT COUNT(*) FROM v$session WHERE type = 'USER') < 200 THEN '$1,000 - $2,500' ELSE '$2,500 - $5,000' END as estimated_monthly_cost, 'vCPUs, memory, instance type, reserved vs on-demand' as cost_drivers, '20-40%' as cost_savings_potential, 'Use reserved instances, right-size instances, auto-scaling' as recommendationsFROM dualUNION ALLSELECT 'Storage Costs', CASE WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 100 THEN '$100 - $300' WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 500 THEN '$300 - $800' ELSE '$800 - $2,000' END, 'Storage type (SSD/HDD), IOPS, backup retention, snapshots', '15-30%', 'Use tiered storage, implement data lifecycle policies'FROM dualUNION ALLSELECT 'Data Transfer', '$50 - $200', 'Outbound data, cross-region transfer, internet egress', '10-20%', 'Optimize data location, use CDN, minimize cross-region transfers'FROM dualUNION ALLSELECT 'Backup and DR', CASE WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 100 THEN '$100 - $250' ELSE '$250 - $600' END, 'Backup storage, snapshot frequency, retention period', '25-50%', 'Use cloud-native backup services, implement lifecycle policies'FROM dualORDER BY 2 DESC;
-- ------------------------------------------------------------------------------------- MIGRATION RISK ASSESSMENT: Cloud migration risks and mitigation-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. CLOUD MIGRATION RISK ASSESSMENTPROMPT ===================================
COLUMN risk_category FORMAT A30COLUMN risk_description FORMAT A60COLUMN probability FORMAT A15COLUMN impact FORMAT A15COLUMN mitigation_strategy FORMAT A80
SELECT 'Data Security' as risk_category, 'Data exposure during migration or in cloud environment' as risk_description, 'MEDIUM' as probability, 'HIGH' as impact, 'Implement encryption in transit and at rest, use private networking' as mitigation_strategyFROM dualUNION ALLSELECT 'Performance', 'Performance degradation in cloud environment', 'HIGH', 'HIGH', 'Conduct performance testing, right-size instances, optimize queries'FROM dualUNION ALLSELECT 'Cost Overruns', 'Unexpected cloud costs exceeding budget', 'HIGH', 'MEDIUM', 'Implement cost monitoring, use budgeting tools, right-size resources'FROM dualUNION ALLSELECT 'Compliance', 'Regulatory compliance issues in cloud', 'MEDIUM', 'HIGH', 'Review cloud provider compliance certifications, data residency'FROM dualUNION ALLSELECT 'Technical Compatibility', 'Database features not supported in cloud', 'LOW', 'MEDIUM', 'Conduct feature compatibility assessment, plan workarounds'FROM dualORDER BY 4, 3;
-- ------------------------------------------------------------------------------------- MIGRATION TOOL RECOMMENDATIONS: Cloud migration tools and utilities-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. MIGRATION TOOLS AND UTILITIESPROMPT =================================
COLUMN migration_phase FORMAT A20COLUMN recommended_tool FORMAT A35COLUMN tool_type FORMAT A15COLUMN capabilities FORMAT A60COLUMN licensing FORMAT A15
SELECT 'Assessment' as migration_phase, 'Oracle Cloud Migrations' as recommended_tool, 'ORACLE' as tool_type, 'Database assessment, sizing, compatibility checking' as capabilities, 'FREE' as licensingFROM dualWHERE UPPER('AWS') IN ('OCI', 'MULTI')UNION ALLSELECT 'Assessment', 'AWS DMS Schema Conversion', 'AWS', 'Schema analysis, conversion assessment, effort estimation', 'FREE'FROM dualWHERE UPPER('AWS') IN ('AWS', 'MULTI')UNION ALLSELECT 'Data Migration', 'Oracle Data Pump', 'ORACLE', 'High-speed data export/import, metadata migration', 'INCLUDED'FROM dualUNION ALLSELECT 'Data Migration', 'AWS Database Migration Service', 'AWS', 'Continuous data replication, minimal downtime migration', 'PAID'FROM dualWHERE UPPER('AWS') IN ('AWS', 'MULTI')UNION ALLSELECT 'Data Migration', 'Azure Database Migration Service', 'AZURE', 'Online migration, schema and data movement', 'PAID'FROM dualWHERE UPPER('AWS') IN ('AZURE', 'MULTI')UNION ALLSELECT 'Validation', 'Oracle SQL Developer', 'ORACLE', 'Data validation, object comparison, testing', 'FREE'FROM dualORDER BY 1, 3; -- Use column positions: 1 = migration_phase, 3 = tool_type
-- ------------------------------------------------------------------------------------- MIGRATION TIMELINE: Project timeline and milestones-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. CLOUD MIGRATION TIMELINEPROMPT ============================
COLUMN migration_phase FORMAT A35COLUMN duration_weeks FORMAT 999COLUMN critical_milestones FORMAT A60COLUMN dependencies FORMAT A50COLUMN resource_requirements FORMAT A40
SELECT 'Planning and Assessment' as migration_phase, 2 as duration_weeks, 'Business case approval, cloud provider selection' as critical_milestones, 'Budget approval, stakeholder alignment' as dependencies, 'Cloud Architect, Project Manager' as resource_requirementsFROM dualUNION ALLSELECT 'Design and Preparation', 3, 'Architecture design, security planning, network setup', 'Cloud account setup, compliance review', 'Solution Architect, Security Engineer'FROM dualUNION ALLSELECT 'Pre-Migration Testing', 2, 'Test migration, performance validation, application testing', 'Test environment setup, data subset', 'DBA Team, QA Team'FROM dualUNION ALLSELECT 'Production Migration', 1, 'Data migration, application cutover, go-live', 'Change approval, backup completion', 'All Teams'FROM dualUNION ALLSELECT 'Post-Migration Optimization', 4, 'Performance tuning, cost optimization, documentation', 'Production stabilization', 'DBA Team, Cloud Engineer'FROM dualORDER BY 2;
-- ------------------------------------------------------------------------------------- CLOUD MIGRATION CHECKLIST: Pre-migration preparation tasks-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. CLOUD MIGRATION CHECKLISTPROMPT =============================
COLUMN checklist_item FORMAT A70COLUMN status FORMAT A15COLUMN responsible FORMAT A20COLUMN deadline FORMAT A15COLUMN priority FORMAT A10
SELECT 'Complete cloud provider account setup and billing configuration' as checklist_item, 'PENDING' as status, 'Cloud Architect' as responsible, 'T-8 Weeks' as deadline, 'HIGH' as priorityFROM dualUNION ALLSELECT 'Establish cloud networking and security configurations', 'PENDING', 'Network Engineer', 'T-6 Weeks', 'HIGH'FROM dualUNION ALLSELECT 'Conduct comprehensive database assessment and sizing', 'PENDING', 'Senior DBA', 'T-6 Weeks', 'HIGH'FROM dualUNION ALLSELECT 'Perform test migration and validate results', 'PENDING', 'DBA Team', 'T-4 Weeks', 'HIGH'FROM dualUNION ALLSELECT 'Develop rollback plan and procedures', 'PENDING', 'Project Manager', 'T-3 Weeks', 'MEDIUM'FROM dualUNION ALLSELECT 'Train operations team on cloud management', 'PENDING', 'Training Lead', 'T-2 Weeks', 'MEDIUM'FROM dualORDER BY 5, 4;
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment-- -----------------------------------------------------------------------------------
CLEAR COLUMNSSET VERIFY ON
-- ------------------------------------------------------------------------------------- COMPLETION: Cloud migration assessment summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ==============================================PROMPT CLOUD MIGRATION ASSESSMENT COMPLETEDPROMPT ==============================================PROMPT Cloud Target: &1PROMPT Migration Method: &2PROMPT Assessment Level: &3PROMPT Completion Time: &&DATE1 &&TIME1PROMPT Output File: &output_filenamePROMPT PROMPT Next Steps:PROMPT 1. Review migration risks and complexity assessmentPROMPT 2. Develop detailed cloud migration project planPROMPT 3. Conduct proof-of-concept in cloud environmentPROMPT 4. Secure budget and stakeholder approvalsPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ON
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------