Script para validar migración de datos

Fuentes:

https://docs.oracle.com/cd/B28359_01/server.111/b28310/toc.htm

https://www.morganslibrary.org/reference/pkgs/dbms_hm.html

https://www.oracle.com/technical-resources/articles/cloud/migrate-db-to-cloud-with-datapump.html

1. Validación rápida

1.1. Fecha y hora

Validaciones como usuario SYS

02. Validación detallada

1.- Validar cifras de sistema

2.- Validar cifras de control

3.- Validar cifras de negocio

-- 01. Script de verificación rapida - En el esquema de XXSCHEMA_CONTRATOS
-- 01. Script de verificación rapida - En el esquema de XXSCHEMA_CONTRATOS
SELECT
    *
FROM
    ( SELECT
           '01.1 CURRENT_TIMESTAMP -SESSIONTIMEZONE' seccion,
           ROWNUM pos,
           'CURRENT_TIMESTAMP' atributo,
           TO_CHAR(current_timestamp) valor
       FROM
           dual
       UNION -- TIMEZONE
       SELECT
           '01.2 CURRENT_TIMESTAMP -SESSIONTIMEZONE',
           ROWNUM,
           'SESSIONTIMEZONE',
           sessiontimezone
       FROM
           dual
       UNION -- v$nls_parameters
       SELECT
           '02. v$nls_parameters',
           ROWNUM,
           parameter,
           TO_CHAR(value)
       FROM
           v$nls_parameters
       UNION ---- TOTAL DE USUARIOS
       SELECT
           '03.1 all_users -------------------------',
           ROWNUM,
           'TOTAL_USUARIOS',
           TO_CHAR((
               SELECT
                   COUNT(*)
               FROM
                   all_users
               WHERE
                   username NOT IN(
                       'SPATIAL_CSW_ADMIN_USR', 'C##DBAAS_BACKUP', 'ORACLE', 'SCOTT', 'PDBUSER'
                   )
           ))
       FROM
           dual
       UNION --- USUARIOS.
       SELECT
           '03.2 all_users' descr,
           ROWNUM pos,
           'USERNAME ',
           username
       FROM
           all_users
       WHERE
           username NOT IN (
               'SPATIAL_CSW_ADMIN_USR',
               'C##DBAAS_BACKUP',
               'ORACLE',
               'SCOTT',
               'PDBUSER'
           )
       UNION ---- Conteo de objetos
       ------+ OBJETOS    
SELECT
           '04.0 OBJECTS -- ',
           ROWNUM pos,
           owner,
           TO_CHAR(num_objects)
       FROM
           (
               SELECT
                   owner,
                   COUNT(*) num_objects
               FROM
                   all_objects
               WHERE
                   owner IN (
                       'APE_ADMON',
                       'APE_AUDITORIA',
                       'APEX_LISTENER',
                       'APEX_180100',
                       'DBSNMP',
                       'DVF',
                       'DVSYS',
                       'FLOWS_FILES',
                       'ORDDATA',
                       'ORDS_METADATA',
                       'ORDSYS',
                       'OUTLN',
                       'PUBLIC',
                       'REMOTE_SCHEDULER_AGENT',
                       'SI_INFORMTN_SCHEMA',
                       'SOPORTE1',
                       'SOPORTE2',
                       'SOPORTE3',
                       'XXAPEP_BKP',
                       'XXAPEPP_JASPER',
                       'XXAPE_SUPP_PAYMENT',
                       'XXSCHEMA_CONTRATOS',
                       'XXSCHEMA_CONTRATOS_TEST'
                   )
               GROUP BY
                   owner

               ORDER BY
                   1,
                   2 DESC
           )    
    
UNION     ----------------------------------
    
SELECT
           '04.1 OBJECTS',
           ROWNUM pos,
           owner
           || '.'
           || object_type,
           TO_CHAR(num_objects)
       FROM
           (
               SELECT
                   owner,
                   object_type,
                   COUNT(*) num_objects
               FROM
                   all_objects
               WHERE
                   owner IN (
                       'APE_ADMON',
                       'APE_AUDITORIA',
                       'APEX_LISTENER',
                       'APEX_180100',
                       'DBSNMP',
                       'DVF',
                       'DVSYS',
                       'FLOWS_FILES',
                       'ORDDATA',
                       'ORDS_METADATA',
                       'ORDSYS',
                       'OUTLN',
                       'PUBLIC',
                       'REMOTE_SCHEDULER_AGENT',
                       'SI_INFORMTN_SCHEMA',
                       'SOPORTE1',
                       'SOPORTE2',
                       'SOPORTE3',
                       'XXAPEP_BKP',
                       'XXAPEPP_JASPER',
                       'XXAPE_SUPP_PAYMENT',
                       'XXSCHEMA_CONTRATOS',
                       'XXSCHEMA_CONTRATOS_TEST'
                   )
               GROUP BY
                   owner,
                   object_type
               ORDER BY
                   1,
                   2 DESC
           )    
       UNION -- Verificar secuencias
       SELECT
           tipo,
           ROWNUM pos,
           atributo,
           to_char(last_number)
       FROM
           (
               SELECT
                   '05.0 SECUENCIAS ------------' tipo,
                   sequence_owner   atributo,
                   SUM(last_number) last_number
               FROM
                   all_sequences
               WHERE
                   sequence_owner IN (
                       'XXSCHEMA_CONTRATOS',
                       'XXSCHEMA_CONTRATOS_TEST',
                       'APEX_180100'
                   )
               GROUP BY
                   sequence_owner
               ORDER BY
                   1,
                   2
           )
       UNION
       SELECT
           tipo,
           ROWNUM pos,
           atributo,
           to_char(last_number)
       FROM
           (
               SELECT
                   '05.1 SECUENCIAS' tipo,
                   sequence_owner
                   || '.'
                   || sequence_name atributo,
                   last_number
               FROM
                   all_sequences
               WHERE
                   sequence_owner IN (
                       'XXSCHEMA_CONTRATOS',
                       'XXSCHEMA_CONTRATOS_TEST',
                       'APEX_180100'
                   )
               ORDER BY
                   last_number DESC
           )
           UNION --- TABLA/REGISTROS -------------------
           select tipo, rownum, atributo, valor from (
select '06.0 Tablas/Registros ---------' tipo, owner atributo, to_char(count(table_name),'9999') || ',' || to_char(sum(num_rows),'999999999') valor
from (
select a.owner,  a.table_name, a.num_rows, a.sample_size, a.initial_extent, a.next_extent, a.min_extents, a.max_extents
from all_tables a
WHERE owner IN (
                       'APE_ADMON',
                       'APE_AUDITORIA',
                       'APEX_LISTENER',
                       'APEX_180100',
                       'PUBLIC',
                       'SOPORTE1',
                       'SOPORTE2',
                       'SOPORTE3',
                       'XXAPEP_BKP',
                       'XXAPEPP_JASPER',
                       'XXAPE_SUPP_PAYMENT',
                       'XXSCHEMA_CONTRATOS',
                       'XXSCHEMA_CONTRATOS_TEST'
                   )
) group by owner order by 2 desc
)
union
select tipo, rownum, atributo, valor from (
    select '06.1 Tablas/Registros' tipo, a.owner ||'.'|| a.table_name atributo, to_char(num_rows,'999999999') valor
    from all_tables a
    WHERE owner IN (
                       'APE_ADMON',
                       'APE_AUDITORIA',
                       'APEX_LISTENER',
                       'APEX_180100',
                       'PUBLIC',
                       'SOPORTE1',
                       'SOPORTE2',
                       'SOPORTE3',
                       'XXAPEP_BKP',
                       'XXAPEPP_JASPER',
                       'XXAPE_SUPP_PAYMENT',
                       'XXSCHEMA_CONTRATOS',
                       'XXSCHEMA_CONTRATOS_TEST'
    )
    order by num_rows desc
)


UNION ------------------+ Triggers ++++
select '07.0 ----------- Trigers -----------', rownum, owner, total from (
select owner , to_char(count(*),'9999') total
from ALL_TRIGGERS 
group by owner
ORDER BY 2 DESC
)

UNION
select tipo, rownum, atributo, valor from (
SELECT
    '07.1 Trigers' tipo,
    table_owner
    || '.'
    || table_name atributo,
    TO_CHAR(COUNT(*), '9999') valor
FROM
    (
        SELECT
            owner,
            table_owner,
            table_name,
            trigger_name,
            triggering_event,
            status
        FROM
            all_triggers
        WHERE
            owner IN (
                'XXSCHEMA_CONTRATOS_TEST',
                'XXSCHEMA_CONTRATOS'
            )
    )
GROUP BY
    table_owner
    || '.'
    || table_name
ORDER BY 3 desc
)
)           
       ORDER BY
           1,
           2,
           4;

Ver el tamaño de los TABLESPACES

SELECT
    df.tablespace_name   "Tablespace",
    totalusedspace       "Used MB",
    ( df.totalspace - tu.totalusedspace ) "Free MB",
    df.totalspace        "Total MB",
    round(100 *((df.totalspace - tu.totalusedspace) / df.totalspace)) "%. Free"
FROM
    (
        SELECT
            tablespace_name,
            round(SUM(bytes) / 1048576) totalspace
        FROM
            dba_data_files
        GROUP BY
            tablespace_name
    ) df,
    (
        SELECT
            round(SUM(bytes) /(1024 * 1024)) totalusedspace,
            tablespace_name
        FROM
            dba_segments
        GROUP BY
            tablespace_name
    ) tu
WHERE
    df.tablespace_name = tu.tablespace_name;

Objetos invalidos

http://www.dba-oracle.com/t_invalid_objects.htm

--- Opción compacta
COLUMN object_name FORMAT a30

SELECT
    owner,
    object_type,
    COUNT(*)
FROM
    (
        SELECT
            owner,
            object_type,
            object_name,
            status
        FROM
            dba_objects
        WHERE
            status = 'INVALID'
        ORDER BY
            owner,
            object_type,
            object_name
    )
GROUP BY
    owner,
    object_type
ORDER BY 3 DESC;    
-----+ SCRIPT PARA RECOMPILAR OBJETOS INVALIDOS +-----
set heading off;
set feedback off;
set echo off;
Set lines 999;

Spool run_invalid.sql

select
'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
all_objects
where
status = 'INVALID'
and
object_type in ('PACKAGE','FUNCTION','PROCEDURE')
;
spool off;
set heading on;
set feedback on;
set echo on;

@run_invalid.sql
-------+
https://oracle-base.com/articles/misc/recompiling-invalid-schema-objects
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT', compile_all => false);

Ejecutar Health checks

--- Mostrar el tipo de HealthChecks
SELECT name FROM v$hm_check WHERE internal_check='N';<br>
SELECT c.name check_name, p.name parameter_name, p.type,<br>
p.default_value, p.description<br>
FROM v$hm_check_param p, v$hm_check c<br>
WHERE p.check_id = c.id and c.internal_check = 'N'<br>
ORDER BY c.name;
---- Correr los que estén disponibles
BEGIN
    DBMS_HM.RUN_CHECK('Dictionary Integrity Check', 'my_run11');
    DBMS_HM.RUN_CHECK('Redo Integrity Check', 'my_run12');
    DBMS_HM.RUN_CHECK('Transaction Integrity Check', 'my_run13');
    DBMS_HM.RUN_CHECK('Data Block Integrity Check', 'my_run14');
    DBMS_HM.RUN_CHECK('CF Block Integrity Check', 'my_run15');
END;    
--- Ver los resultados
SELECT run_id, name, check_name, run_mode, status, src_incident, num_incident, error_number
FROM gv$hm_run
ORDER  BY 1;

Comparar plataformas

https://docs.oracle.com/en/database/oracle/oracle-database/18/spucd/transporting-a-database-using-an-export-dump-file.html#GUID-05DFEA70-FDAF-4FAF-BF26-37225151A7D7

SELECT d.PLATFORM_NAME, ENDIAN_FORMAT<br>
     FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d<br>
     WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;