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
SELECT d.PLATFORM_NAME, ENDIAN_FORMAT<br>
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d<br>
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;