DIA-49803: Purge not possible due to incompatible schema version.

Posted on 21 marzo, 2019 por admin

Hoy vamos a ver una sencilla entrada que tiene que ver con el adrci ( Interprete de comandos ADR ).
El otro dia me encontre con que algunos de los automatismos de limpieza de logs fallaban con el eror DIA-49803: Purge not possible due to incompatible schema version

[oracle@testserver ~]$ adrci
ADRCI: Release 12.1.0.2.0 - Production on Thu Mar 16 09:34:45 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
ADR base = "/u01/app/oracle/admin"
adrci> show homes
ADR Homes: 
diag/rdbms/test1/TEST1
diag/rdbms/test2/TEST2
adrci> purge -age 180 -type alert   
DIA-49803: Purge not possible due to incompatible schema version.

A que puede deberse esto ?
Si miramos en la documentacion de soporte de Oracle nos encontramos con algunos poibles bugs , como el bug Bug 27718599 o un efecto secundario del bug 28375106.
Pero, antes de meterse en berenjenales, lo mas sencillo es intentar migrar la version de los logs.

Como lo hacemos?

Algo tan sencillo como

adrci> migrate schema
Schema migrated.

Tras la ejecucion de este sencillo comando, veremos como nuestros comandos vuelven a funcionar correctamente

Errores psdgbt: bind csid (X) does not match session csid (YY)

Posted on 11 marzo, 2019 por admin

Responder

Hoy vamos a ver una entrada rapida y sencilla que nos ayudara a evitar una cascada de errores en el alert.log

Es posible que, la reiniciar alguna e nuestras bases de datos que se encuentran bajo la monitorizacion de un Enterprise manager veamos que el alert.log de nuestra base de datos comienza a mostrar continuamente errores del tipo

Errors in file /u01/app/oracle/diag/rdbms/TEST_stby/TEST/trace/TEST_ora_743.trc:
Mon March 11 12:24:47 2019
Errors in file /u01/app/oracle/diag/rdbms/TEST_stby/TEST/trace/TEST_ora_743.trc:
Mon March 11 12:25:06 2019
Errors in file /u01/app/oracle/diag/rdbms/TEST_stby/TEST/trace/TEST_ora_743.trc:

Si comprobamos la traza podemos ver como se trata de errores del tipo

psdgbt: bind csid (1) does not match session csid (46)
psdgbt: session charset is XXX

Donde el charset variara en funcion de nuestra base de datos, veamos una de estas trazas

ORACLE_HOME = /u01/app/oracle/product/12.1.0/db
System name: Linux
Node name: test.pamplona.name
Release: 3.8.13-118.19.12.el6uek.x86_64
Version: #2 SMP Tue Oct 31 12:31:15 PDT 2017
Machine: x86_64
Instance name: TEST
Redo thread mounted by this instance: 1
Oracle process number: 32
Unix process pid: 743, image: oracle@test.pamplona.name

*** 2019-03-11 21:20:06.886
*** SESSION ID:(2.57596) 2019-03-11 21:20:06.886
*** CLIENT ID:() 2019-03-11 21:20:06.886
*** SERVICE NAME:() 2019-03-11 21:20:06.886
*** MODULE NAME:(emagent_SQL_oracle_database) 2019-03-11 21:20:06.886
*** CLIENT DRIVER:(jdbcthin) 2019-03-11 21:20:06.886
*** ACTION NAME:(ME$icc_dbs_age_last_backup) 2019-03-11 21:20:06.886

psdgbt: bind csid (1) does not match session csid (46)
psdgbt: session charset is WE8ISO8859P15

*** 2019-03-11 21:20:06.886
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0)
—– Error Stack Dump —–
—– Current SQL Statement for this session (sql_id=a0qc12302fzfk) —–
begin dbms_application_info.set_module(:1 , :2 ); end;

La solucion para este problema es muy muy sencila, simplemente, reinicia el agente del enterprise manager del servidor donde se encuentra ubicada la base de datos.

Como siempre, mas informacion en soporte de Oracle
Doc ID 956222.1

Comprobar el estado de una operacion rman

Posted on 3 marzo, 2019 por admin

Responder

Vamos a volver a las entradas recopilatorias para dummies.

Hoy vamos a ver unas consultas realmente utiles en el uso diario de rman , con ellas, podremos saber cual es el estado de nuestra recuperacion y pode restimar el tiempo que queda.
Lo que vamos ha hacer es comprobar el tiemo que nos indica la propia base de datos en v$session_longops, para ello podemos usar estas dos consultas

--  Tiempo que le queda al rman para recuperar
col OPNAME for a30
select OPNAME,SOFAR/TOTALWORK*100 PCT, 
trunc(TIME_REMAINING/60) MIN_RESTANTES,
trunc(ELAPSED_SECONDS/60) MIN_ATEAGORA
from v$session_longops 
where TOTALWORK>0 and OPNAME like '%RMAN%';

O afinar un poco mas si lo que buscamos son los restores

--estimacion de los restores 
select OPNAME,SOFAR/TOTALWORK*100 PCT,
 trunc(TIME_REMAINING/60) MIN_RESTANTES,
  trunc(ELAPSED_SECONDS/60) MIN_ATEAGORA
  from v$session_longops 
where TOTALWORK>0 and OPNAME like '%RMAN: full datafile restore%';

Otra consulta que nos puede resultar muy util es el saber cuales de nuestros ficheros necesitan recuperacion

-- Info mas detallada de los ficheros y tablespaces de v$recover_file 
COL DF# FORMAT 999
COL DF_NAME FORMAT A70
COL TBSP_NAME FORMAT A15
COL STATUS FORMAT A7
COL ERROR FORMAT A20
COL CHANGE# FORMAT 999999999999999999
SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name,
d.STATUS, r.ERROR, r.CHANGE#, r.TIME
FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS#
AND d.FILE# = r.FILE# ;

Como siempre, podemos encontrar mas informacion en :

Documentacion de referencia de V$RECOVER_FILE
Documentacion de referencia de V$SESSION_LONGOPS
las consultas sobre rman de ORAFAQ

Limpiando el FLASHBACK de la FRA

Posted on 25 febrero, 2019 por admin

Responder

Hoy vamos a ver una de esas entradas que, nos puede volver algo locos, pero cuya solucion no dejan de ser asombrosamente sencilla.

Cuando tenemos servidores con un diskgroup de FRA compartido, podemos tener el caso de que la ocupacion de la FRA de una de ellas ponga en peligro la disponibilidad e todas ellas.
Esto se agrava aun mas en el caso de que tengamos el Flashback activado.
Veamos un caso practico , aqui tenemos una base de datos donde toda la ocupacion esta en el Flashback

+FRA/SIDTEST/ subdirectories size
Subdir             Used MB       Mirror MB
------             -------       ---------
ARCHIVELOG/           306             306
CHANGETRACKING/        12              12
CONTROLFILE/
FLASHBACK/          163251          163251
ONLINELOG/
     ------         -------       ---------
Total                163569          163569

SI llevamoS a cabo algunos chequeos sobre la FRA tenemos que


SQL> select NAME,FLASHBACK_ON from v$database;
NAME                                                        FLASHBACK_ON
------------------------------------------------------------ ------------------------------------------------------
SIDTEST                                                      YES

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

show parameter undo_retention
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
undo_retention                       integer                           900

SQL> show parameter DB_FLASHBACK_RETENTION_TARGET
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_flashback_retention_target        integer                           1440

SQL> show parameter  db_recovery_file_dest
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest                string                            +FRA
db_recovery_file_dest_size           big integer                       600G


SQL> show parameter  db_recovery_file_dest_size
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest_size           big integer                       600G


SQL> select  name,flashback_on   from   v$tablespace where flashback_on!='YES';
no rows selected

SQL> SELECT ESTIMATED_FLASHBACK_SIZE/1024/1024/1024 Gb  FROM V$FLASHBACK_DATABASE_LOG;
        GB
----------
 61.307373

SQL> SELECT FLASHBACK_SIZE/1024/1024/1024 Gb  FROM V$FLASHBACK_DATABASE_LOG;
        GB
----------
159.225609

El problema que tenemos, es que queremos liberar esos 160 Gb, pero no podemos.

Como hacerlo

Hay un truco muy sucio, pero efectivo, es modificar el parametro del tamaño del db_recovery_file_dest_size

En el momento en que seteamos el db_recovery_file_dest_size por debajo de laocupacion actual, la propia base de datos vacia ficheros

 select sum(FLASHBACK_SIZE)/1024/1024/1024
 from V$FLASHBACK_DATABASE_LOG;
SUM(FLASHBACK_SIZE)/1024/1024/1024
----------------------------------
                        159.225609

SQL>  alter system set db_recovery_file_dest_size=50G;
System altered.

SQL> alter system set db_recovery_file_dest_size=600G;
System altered.

select sum(FLASHBACK_SIZE)/1024/1024/1024
 from V$FLASHBACK_DATABASE_LOG;
SUM(FLASHBACK_SIZE)/1024/1024/1024
----------------------------------
                        48.3398438

Veamos que ha asado en el alert.log

ALTER SYSTEM SET db_recovery_file_dest_size='50G' SCOPE=BOTH;

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_22.12053.988373613

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_23.1360.988373647

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_24.12976.988373689

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_25.3952.988373743

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_26.3865.988373799

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_27.12048.988374121

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_28.12304.988374251

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_29.12489.988374301

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_30.1388.988374391

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_31.2666.988374467

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_32.3815.988374789

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_44.4188.988555917

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_45.3063.988555949

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_46.14572.988555983

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_47.12737.988556005

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_48.2546.988556023

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_49.4396.988556049

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_50.2232.988556075

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_51.11446.988556097

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_52.13247.988556125

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_53.13228.988556147

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_60.13017.1003940269

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_54.1819.988556173

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_61.4863.1004080645

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_62.11394.1004080677

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_63.11263.1004083037

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_33.13079.988374861

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_34.11600.988374963

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_35.11804.988377507

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_36.4013.988377931

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_37.3242.988378457

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_59.11364.1003507407

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_38.4922.988378951

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_1.13038.988205331

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_2.1565.988205339

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_64.10813.1004102837

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_3.1899.988209247

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_4.11864.988209287

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_5.1311.988209309

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_6.1715.988209341

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_65.9603.1004103287

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_7.11990.988209441

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_8.3989.988209567

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_9.12500.988209599

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_10.6424.988209633

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_55.1243.989342719

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_11.13330.988209659

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_12.12103.988209683

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_56.3201.989352547

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_66.8648.1004135213

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_67.1132.1004135251

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_68.5686.1004135281

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_69.10834.1004135313

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_70.11789.1004135357

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_71.11145.1004135395

Deleted Oracle managed file +FRA/SIDTEST/flashback/log_72.13516.1004135445

ALTER SYSTEM SET db_recovery_file_dest_size='600G' SCOPE=BOTH;

cOMO PODEIS VER.. SUCIO PERO EFECTIVO

Sqltunning desde sqlplus

Posted on 12 febrero, 2019 por admin

Responder

Vamos a volver a la entradas para dummies con un error muy comun.

Uno de los errores mas frecuentes que tenemos desde el enterprise manager es cuando intentamos lanzar el SQL Tuning Advisor sobre una consuta pesada y obtenemos el error

ORA-01555 caused by SQL statement below (SQL ID: g5wg4kxu9m4g3, Query Duration=14163 sec, SCN: 0x09a1.42a95c42):

Este error no nos lo esta dando el advisor en si, sino que es un error del enterprise manager, para poder obtener este advice lo haremos de la siguiente manera

Supongamos que buscamos llevarlo a cabo sobre la SQLID g5wg4kxu9m4g3 y que tenemos identificada esta consulta entre dos snapshots, los 73485 & 73486

Los pasos que debemos de seguir son :

Definimos la tarea


begin_snap  => 73485,
end_snap    =>73486,
sqlidf => g5wg4kxu9m4g3

--create task 
DECLARE
l_sql_tune_task_id  VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id=> 'g5wg4kxu9m4g3',
time_limit=> 1500,
task_name=> 'g5wg4kxu9m4g3_tuning_task',
description=> 'Tuning task for statement g5wg4kxu9m4g3',
scope    => DBMS_SQLTUNE.scope_comprehensive
);
   
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Ejecutamos la tarea

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'g5wg4kxu9m4g3_tuning_task');

Obtenemos el resultado

set pagesize 999
set long 65536
set longchunksize 65536
set linesize 200
select dbms_sqltune.report_tuning_task('g5wg4kxu9m4g3_tuning_task') from dual;

Como podemos ver, el uso basico del paquete DBMS_SQLTUNE es extremadamente sencillo

Dba stuff

Un cajon desastre para el día a día

Archivo de la categoría: 11g

DIA-49803: Purge not possible due to incompatible schema version.

Como lo hacemos?

Errores psdgbt: bind csid (X) does not match session csid (YY)

Comprobar el estado de una operacion rman

Limpiando el FLASHBACK de la FRA

Como hacerlo

Sqltunning desde sqlplus

Definimos la tarea

Ejecutamos la tarea

Obtenemos el resultado