lunes, noviembre 28, 2022

Defragmentación de un tablespace mediante scripts.

En muchas ocasiones recurro a generar scripts a partir de consultas. Ejecuto en SQL*Plus algo parecido a:
SQL> select 'alter package '||owner||'.'||object_name||' compile;' sentencia   2> from dba_objects where status='INVALID' and object_type ='PACKAGE BODY'; SENTENCIA ------------------------------------------------------------------ alter package USUARIO.MAP02_CUACON02 compile; alter package USUARIO.MAP01_CUACON03 compile; alter package USUARIO.MAP01_FLUXHISTO03_CI compile; alter package USUARIO.MAP01_BALANCEBE03 compile; alter package USUARIO.MAP01_PAHISTOACT03 compile; alter package USUARIO.MAP01_RAPPELS03 compile; 6 filas seleccionadas.
y simplemente copio y pego las filas como comandos en la consola SQL. El procedimiento podrá parecer tosco, pero resulta muy versátil ejecutar una consulta que construya dinámicamente todas las sentencias SQL que se precisan para una determinada tarea y, lo más importante, que se adapten a cualquier entorno. Un ejemplo de ésto podría ser aplicado a la reorganización de los segmentos de un tablespace. Para la defragmentación de un tablespace, los objetos deben reconstruirse de la siguiente forma:    - Tablas:   ALTER TABLE xxxx MOVE STORAGE INITIAL tamaño K/M);    - Índices:   ALTER INDEX xxxx REBUILD [online];    - Tablas particionadas:  ALTER TABLE xxxx MOVE PARTITION .......;    - Índices particionados: ALTER INDEX xxxx REBUILD PARTITION ...... [online]; Teniendo en cuenta que mover una tabla en Oracle9i deja inválidos los índices, una vez ejecutados estos comandos por todas las tablas e índices del tablespace, sería preciso comprobar qué índices (o partitiones de índice) pueden haber quedado afectados y reconstruirlos. Habría otro detalle a tener presente: los segmentos reconstruidos sobre el mismo tablespace, dificilmente consiguen liberar el espacio contiguo al final de los ficheros, por lo que sería útil hacer un doble movimiento: mover primero a un tablespace vacío, y posteriormente mover los objetos al tablespace original de vuelta. Si bien esto no es complicado, sí resulta minucioso. Este script, en este caso, puede resultar tremendamente útil. Una vez defragmentado el tablespace, es preciso ejecutar el segundo script para reconstruir índices, lo que evitará que éstos se queden inválidos después del transporte. * Nota: Es preciso tener en cuenta el tamaño total de los objetos. Si éstos son grandes, conviene aumentar el tamaño definido como inicial. Lo mejor es utilizar tablespaces gestionados localmente con gestión automática de las extensiones para que Oracle decida, en tablas grandes, hacer extensiones mayores dinámicamente.
-- DEFRAGMENTA_TABLESPACE.SQL -- set verify off set lines 120 set pages 1000 define tablespace_origen =&TABLESPACE_DATOS define tablespace_destino=&TABLESPACE_VACIO   define t_extension_inicial=&TAMAÑO_EXTENSION_INICIAL select 'alter table '||owner||'.'||table_name||        ' move tablespace &tablespace_destino storage (initial &t_extension_inicial M);' from dba_tables where upper(tablespace_name)='&tablespace_origen' union all select 'alter table '||table_owner||'.'||table_name||        ' move partition '||partition_name||        ' tablespace &tablespace_destino storage (initial &t_extension_inicial M);' from dba_tab_partitions where upper(tablespace_name)='&tablespace_origen' union all select 'alter index '||owner||'.'||index_name||        ' rebuild tablespace &tablespace_destino storage (initial &t_extension_inicial M);' from dba_indexes where upper(tablespace_name)='&tablespace_origen' union all select 'alter index '||index_owner||'.'||index_name||        ' rebuild partition '||partition_name||        ' tablespace &tablespace_destino storage (initial &t_extension_inicial M);' from dba_ind_partitions where upper(tablespace_name)='&tablespace_origen' union all select 'alter table '||owner||'.'||table_name||        ' move tablespace &tablespace_origen storage (initial &t_extension_inicial M);' from dba_tables where upper(tablespace_name)='&tablespace_origen' union all select 'alter table '||table_owner||'.'||table_name||        ' move partition '||partition_name||        ' tablespace &tablespace_origen storage (initial &t_extension_inicial M);' from dba_tab_partitions where upper(tablespace_name)='&tablespace_origen' union all select 'alter index '||owner||'.'||index_name||        ' rebuild tablespace &tablespace_origen storage (initial &t_extension_inicial M);' from dba_indexes where upper(tablespace_name)='&tablespace_origen' union all select 'alter index '||index_owner||'.'||index_name||        ' rebuild partition '||partition_name||        ' tablespace &tablespace_origen storage (initial &t_extension_inicial M);' from dba_ind_partitions where upper(tablespace_name)='&tablespace_origen'; -- RECONSTRUYE_INDICES.SQL -- select 'alter index '||index_owner||'.'||index_name||        ' rebuild partition '||partition_name||        ' tablespace &tablespace_origen storage (initial &t_extension_inicial M);' SENTENCIA from dba_ind_partitions where upper(tablespace_name)='&tablespace_origen' union all select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where status='UNUSABLE' union all select 'alter index '||index_owner||'.'||index_name||        ' rebuild partition '||partition_name||';' from dba_ind_partitions where status='UNUSABLE'; TEST ==== SQL> @c:\reorganiza_tablespace Introduzca un valor para tablespace_datos: DM_ACTIVIDAD01 Introduzca un valor para tablespace_vacio: TS_VACIO Introduzca un valor para tama±o_extension_inicial: 1 'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'MOVETABLESPACETS_VACIOSTORAGE(INITIAL1M);' -------------------------------------------------------------------------------------------------------------- alter table USUARIO.TABLA_XXX02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXX02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXASMA02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXXX02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXXXX02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXPMA02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXP02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXAS02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXON02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXPMES02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXASMES02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXCIBOSMES02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXCIBOSMA02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXCIBOS02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXTASMES02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXTAS02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXTASMA02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXXXXY02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXMA02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXMES02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXACION02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXACIONMA02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXACIONMES02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXTVS02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXTVSMA02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXTVSMES02 move tablespace TS_VACIO storage (initial 1 M); alter table USUARIO.TABLA_XXXYY02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXY02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXASMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXX02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXX02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXPMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXP02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXAS02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXON02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXPMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXASMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXCIBOSMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXCIBOSMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXCIBOS02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXTASMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXTAS02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXTASMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXX02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXACION02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXACIONMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXACIONMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXTVS02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXTVSMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); alter table USUARIO.TABLA_XXXTVSMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M); 52 filas seleccionadas.
Una vez ejecutados todos estos comandos, lanzamos el script que consulta los índices inválidos con el fin de recompilarlos.
SQL> @c:\reconstruye_indices SENTENCIA ------------------------------------------------------------------- alter index USUARIO.INDICEXXXOSP02_01 rebuild; alter index USUARIO.INDICEXXXURAS02_01 rebuild; alter index USUARIO.INDICEXXXSION02_01 rebuild; alter index USUARIO.INDICEXXXOSPMES02_01 rebuild; alter index USUARIO.INDICEXXXRECIBOS02_01 rebuild; alter index USUARIO.INDICEXXXABTAS02_01 rebuild; alter index USUARIO.INDICEXXXDA02_01 rebuild; alter index USUARIO.INDICEXXXCIACION02_01 rebuild; alter index USUARIO.INDICEXXXTVS02_01 rebuild; alter index USUARIO.INDICEXXXTVSMA02_01 rebuild; alter index USUARIO.INDICEXXXTVSMES02_01 rebuild; 11 filas seleccionadas.

viernes, agosto 24, 2018

TOP 7 ERRORS in Oracle GoldenGate12c that will save your day

Recently I made a Udemy course about creating a replication environment using Oracle GoldenGate12c. I built a workshop everybody can follow step by step setting up a logical replication using two OraLinux virtual machines, and installing and configuring GoldenGate step by step.

Oracle GoldenGate12c Workshop

One of the things I wanted to add is a troubleshooting guide to solve the most common errors you may find when doing the replication for yourself, so here is the list of the...

Top 7 errors in Oracle GoldenGate12c
(when you are working with Oracle12c multitenant databases)

(... and of course, feel free to join the course with a special price here!) ;)


ERROR #1

OGG-00303  Oracle GoldenGate Manager for Oracle, mgr.prm:  Unable to connect to database using user c##gg_user. Ensure that the necessary privileges are granted to the user.

CAUSE: the user c##gg_user doesn't exist in the database or doesn't have the proper privileges.

SOLUTION: Ensure the user is created and privileges are granted as follows:

SQL> create user c##gg_user identified by gg_user
  2  default tablespace goldengate
  3  temporary tablespace temp;


User created.

SQL> alter user c##gg_user quota unlimited on goldengate;

User altered.

SQL> grant create session, connect, resource to c##gg_user container=all;

Grant succeeded.

SQL> grant alter any table, alter system to c##gg_user container=all;

Grant succeeded.

SQL> grant set container to c##gg_user;

Grant succeeded.

SQL> grant select any dictionary to c##gg_user container=all;

Grant succeeded.

SQL> grant dba to c##gg_user container=all;

Grant succeeded.

SQL> exec dbms_goldengate_auth.grant_admin_privilege
('c##gg_user',container=>'all')

PL/SQL procedure successfully completed.

SQL> exec dbms_goldengate_auth.grant_admin_privilege
('c##gg_user','capture',container=>'all')

PL/SQL procedure successfully completed.

SQL> exec dbms_goldengate_auth.grant_admin_privilege
('c##gg_user','apply',container=>'all')

PL/SQL procedure successfully completed.


***

ERROR #2

OGG-02091  Operation not supported because enable_goldengate_replication is not set to true.

CAUSE: The parameter enable_goldengate_replication is set to false

SOLUTION: Set the parameter to true by connecting to sqlplus as sysdba and enable the GoldenGate parameter with the following syntax:

SQL> alter system set enable_goldengate_replication=true;

System altered.


***

ERROR #3

OGG-02058  The Oracle source database is not configured properly to support integrated capture. The following configuration error must be fixed:  ARCHIVELOG mode must be enabled on this Oracle database.

CAUSE: The database is in NOARCHIVELOG mode and integrated capture needs ARCHIVELOG enabled.

SOLUTION: Enable archivelog in the database before set up the extract process and don´t forget to add the supplemental logging too!

[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 6 08:10:13 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup mount
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size      8798312 bytes
Variable Size    339742616 bytes
Database Buffers   486539264 bytes
Redo Buffers      3780608 bytes
Database mounted.


SQL> alter database archivelog;
Database altered.

SQL> archive log list
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     27
Next log sequence to archive   29
Current log sequence        29


SQL> alter database open;
Database altered.

SQL> alter database force logging;
Database altered.

SQL> alter database add supplemental log data (all) columns;
Database altered.

***

ERROR #4

OGG-00446  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Checkpoint table orcl.c##gg_user.checkpoint does not exist. Create this table with the  ADD CHECKPOINTTABLE command.

CAUSE: the checkpoint table does not exist as the defined name shown.

SOLUTION: Ensure the process definition and the checkpoint table have the exact same name.

***


ERROR #5

OGG-02022  Oracle GoldenGate Capture for Oracle, ext1.prm:  Logmining server does not exist on this Oracle database.

CAUSE: When a extract process can't access the Logmining database it's mainly for a strange limitation to only one extract for each pluggable database or because the extract process was not properly registered in the database.

SOLUTION: check if extract is registered and you have only one for pluggable database (pump extract doesn't count, as the source is the dirdat directory instead of the integrated log).

***

ERROR #6

OGG-15312  Oracle GoldenGate Command Interpreter for Oracle:  Cannot obtain database version information because verification of database login failed with next error: Not logged into database, use DBLOGIN.

CAUSE: login failed, not logged in

SOLUTION: check dblogin syntax and perform a proper login with the correct user and password.

***

ERROR #7

OGG-02650  Oracle GoldenGate Capture for Oracle, pump1.prm:  Source wildcard specification hr.* does not include a catalog name, but the source table name ORCL.HR.EMPLOYEES includes a catalog name.

CAUSE: when specifying a SOURCECATALOG you don't have to prefix the pluggable database in the table definition.

SOLUTION: remove the ORCL. prefix

***