Oracle - Problèmes divers et solutions

Diverses astuces

Intégrer des données UTF8 avec sqlplus

La base doit être en UTF8.
Forcer l'environnement du client sqlplus. Avant de la lancer :

set (ou export) NLS_LANG=_.UTF8

logrotate alert.log

A ajouter dans /etc/logrotate.d/alert_orcl

#alert log base ORCL
/oracle/ora11g/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log {
   monthly
   rotate 3
   notifempty
   missingok
   copytruncate
   nocreate
   compress
}

OUI 19c - page d'accueil vide

Lorsqu'on lance OUI (runInstaller) à partir d'un serveur Linux (exemple : Rocky linux 8) via ssh et X forwarding, si la page n'affiche aucun contenu :

image-1695908312045.png

 On peut démarrer runInstaller avec l'option "-debug". On voit alors (ici sur l'image) qu'il y a un problème avec CV_ASSUME_DISTID. Effectivement, cette installation est faite sur une distribution Rocky 8, le problème ici n'est pas "Rocky" mais "8".

On commence donc par vérifier la version exacte

$ cat /etc/redhat-release 
Rocky Linux release 8.8 (Green Obsidian)

Et on lance runIstaller en initialisant la variable

$ CV_ASSUME_DISTID=8.8 ./runInstaller 

Purge des traces

A planifier en CRONTAB

#!/bin/sh
export ORACLE_HOME=/oracle/ora11g/dbhome
/oracle/ora11g/dbhome/bin/adrci exec = "show home"|awk '{if (NR >= 2) print $1}'|while read rep_diag
do
 echo "purge de $rep_diag";date
 /oracle/ora11g/dbhome/bin/adrci exec = "set home $rep_diag;purge -age 10080"
done

find /oracle/ora11g/dbhome/rdbms/audit -mtime +90 -exec rm -f {} \;

exit 0

Recreate DBConsole

Initialiser ORACLE_HOSTNAME avec le FQDN EXACT du serveur
Initialiser ORACLE_SID (mais peut-être pas nécessaire? A vérifier)

set ORACLE_SID=MYDB
set ORACLE_HOSTNAME=oraclehost.local

emca -config dbcontrol db -repos recreate

Répondre aux questions (port listener = 1521, password des users ORACLE sys, system,dbsnmp et sysman)

A la fin, si erreur de sécurisation (le https n'a pas pu être généré), tenter :

emctl stop dbconsole
emctl config emkey -repos -sysman_pwd <mot-de-passe-SYSMAN>
emctl secure dbconsole -sysman_pwd <mot-de-passe-SYSMAN>
emctl stop dbconsole

mais ça ne fonctionne pas forcément... Dans ce cas, la console reste accessible en http "simple".

Vérifier qu'elle tourne :

export ORACLE_SID=nom_de_l_instance
export ORACLE_UNQNAME=nom_unique_de_la_base
emctl status dbconsole

Récupérer les touches de direction et l'historique dans sqlplus linux

Installer rlwrap
lancer sqlplus par :

rlwrap sqlplus user/password

si le client est en version 10g XE, on peut aussi modifier /usr/lib/oracle/xe/app/oracle/product/10.2.0/client/scripts/sqlplus.sh

Services SYSTEMD base et listener

Vérifier dans /etc/oratab que la base est listée et est à "Y" car dbstart/dbshut s'appuient dessus.

Environnement (/etc/sysconfig/env.orcl):

ORACLE_BASE=/opt/oracle/
ORACLE_HOME=/opt/oracle/product/12.2.0/db_1
ORACLE_SID=ORCL
ORACLE_USER=oracle
ORACLE_GROUP=oinstall
ORACLE_LISTENER=LSN_ORCL

Service systemd de démarrage de la base (ex : /usr/lib/systemd/system/oracleORCL.service) :

[Unit]
Description=Oracle databases service
Before=shutdown.target multi-user.target 
After=opt-oracle.mount

[Service]
Type=forking
EnvironmentFile=/etc/sysconfig/env.orcl
User=$ORACLE_USER
Group=$ORACLE_GROUP
ExecStart=$ORACLE_HOME/bin/dbstart $ORACLE_HOME &
ExecStop=$ORACLE_HOME/bin/dbshut $ORACLE_HOME

[Install]
WantedBy=multi-user.target

Service systemd de démarrage du listener (ex : /usr/lib/systemd/system/listener.service) :

[Unit]
Description=Oracle listener 
After=network.target 

[Service]
RemainAfterExit=yes
EnvironmentFile=/etc/sysconfig/env.orcl
ExecStart=$ORACLE_HOME/bin/lsnrctl start $ORACLE_LISTENER &
ExecStop=$ORACLE_HOME/bin/lsnrctl start $ORACLE_LISTENER

[Install]
WantedBy=multi-user.target

switch user ("su" like)

Créer un script "su.sql" :

whenever sqlerror exit
column password new_value pw

-- test access to dba_users and if the user exists
declare
   l_passwd varchar2(45);
begin
   select password into l_passwd
      from sys.dba_users
      where username = upper('&1');
end;
/

-- select password in variable pw
select password
   from sys.dba_users
   where username = upper( '&1' );
/

-- change the password for "Hello"
alter user &1 identified by Hello;
connect &1/hello

-- once connected, change password back
alter user &1 identified by values '&pw';
show user
whenever sqlerror continue

Erreur ORA-00600 - index, ou blocs, ou lob corrompu

On trouve des erreurs ORA-0600 dans l'alert.log. Il peut s'agir de blocs disque corrompus suite à un problème d'accès disque, ou d'un index désynchronisé, ou d'un LOB corrompu dans la table (liste non exhaustive !).
ORA-00600: internal error code, arguments: [13011], [117675], [32437212], [0], [32705125], [0], [], [], [], [], [], []

NOTE : il existe un décodeur d'ORA-00600 sur Metalink :
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=153788.1

NOTE 2 : Si l'erreur ORA-00600 ne génère pas des codes décodables, en général le fichier trace associé à l'erreur 0600 permet de trouver la requête, donc la table, affectée.

Trouver la table concernée par le bloc corrompu

Trouver le fichier et la table concernée (voir FIX ORA-00600 (1) pour la liste des paramètres de l'erreur 0600):

SELECT owner, object_name, object_type, object_id, data_object_id
FROM dba_objects
WHERE data_object_id = 117675;

ANALYZE et VALIDATE STRUCTURE

D'abord analyser la table :

analyze table myschema.mytable validate structure cascade;

L'analyse de la table ne renvoie pas d'erreur

Si la commande ne renvoie pas d'erreur (00600), il faut faire de même avec ses indexes. Pour trouver les indexes :

select index_name from dba_indexes where owner='MYSCHEMA' and tble_name='MYTABLE';

Puis, pour chaque index trouvé, valider la structure :

analyze index MYSCHEMA.<MYTABLE_IND1> validate structure;

Si la table a trop d'index, on peut essayer de trouver lequel a un problème par :

SQL> alter session set max_dump_file_size = unlimited;
SQL> ALTER SESSION SET tracefile_identifier = 'mon_fichier_trace';
SQL> analyze table MYSCHEMA."MYTABLE" validate structure cascade into invalid_rows;

Dans 'mon_fichier_trace', on trouve une ligne de ce type :

 

row not found in index tsn: 1 rdba: 0x00817bfa

On recherche l'index correspondant à "00817bfa" :

SELECT owner, segment_name, segment_type, partition_name
FROM DBA_SEGMENTS
WHERE header_file = (SELECT file#
FROM v$datafile
WHERE rfile# =
dbms_utility.data_block_address_file(
to_number('00817bfa','XXXXXXXX'))
AND ts#= 1)
AND header_block = dbms_utility.data_block_address_block(
to_number('00817bfa','XXXXXXXX'));

Sinon, on peut essayer de générer un script pour les analyser tous :

select 'analyze index '||owner||'.'||index_name||' validate structure' from dba_indexes where owner='MYSCHEMA' and table_name='MYTABLE';

L'analyse de la table renvoie des erreurs ORA-00600 ou ORA-01499 (échec de référence croisée à la table/index - voir fichier de trace)

SQL> analyze table myschema.mytable validate structure cascade;
analyze table myschema.mytable validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

la première chose à faire est de valider (par DB Verify : utilitaire "dbv") que ce n'était pas un problème physique sur le fichier du tablespace. Trouver le tablespace et le fichier contenant la table :

SQL> select tablespace_name from dba_tables where table_name='MYTABLE';
TABLESPACE_NAME
------------------------------
MYTBLSPCE

SQL> select file_name from dba_data_files where tablespace_name='MYTBLSPCE';
FILE_NAME
---------------------------------
/u01/ORADATA/MYDB/MYTBLSPCE01.DBF

SQL> show parameter db_block_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

Lancer dbv :

$ dbv file='MYTBLSPCE01.DBF' blocksize='8192'
DBVERIFY: Release 11.2.0.4.0 - Production on Fri Apr 26 16:35:54 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/ORADATA/MYDB/MYTBLSPCE01.DBF

DBVERIFY - Verification complete

Total Pages Examined         : 3440640
Total Pages Processed (Data) : 3102612
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 320
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 139223
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 198485
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2784020020 (1.2784020020)

Ici analyse sans erreur, ce n'est donc pas une erreur physique, mais bien une désynchronisation d'un index ou un lob corrompu. On reconstruit donc les indexes.

Si dbv renvoie des erreurs, le fichier lui-même est physiquement corrompu sur disque ! Il n'y a alors pas d'autre moyen que de re-créer la base proprement sur un disque valide et réimporter la dernière sauvegarde viable.

Reconstruction des indexes

SQL> ALTER INDEX "MYSCHEMA"."MYTABLE_IND1" REBUILD;
Index altered.

Cependant, il peut arriver que le REBUILD ne suffise pas; où qu'on décide de recréer les index dans un autre tablespace par sécurité.

Re-création des indexes

SQL> set long 200000 pagesize 0
SQL> select dbms_metadata.get_ddl('INDEX','MYTABLE_IND1','MYSCHEMA') from dual;

DBMS_METADATA.GET_DDL('INDEX','MYTABLE_IND1','MYSCHEMA')
-------------------------------------------------------------------------------

  CREATE INDEX "MYSCHEMA"."MYTABLE_IND1" ON "MYSCHEMA"."MYTABLE" ("COL1")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MYTBLSPCE"

SQL> ALTER INDEX "MYSCHEMA"."MYTABLE_IND1" UNUSABLE;
Index altered.

SQL> DROP INDEX "MYSCHEMA"."MYTABLE_IND1";
Index dropped.

SQL> CREATE INDEX "MYSCHEMA"."MYTABLE_IND1" ON "MYSCHEMA"."MYTABLE" ("COL1")
Index created.

Si l'index ne peut pas être recréé; exemple :

SQL> drop index "MYSCHEMA"."MYTABLE_IND1";
drop index "MYSCHEMA"."MYTABLE_IND1"
                *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

alors il faut le reconstruire avec REBUILD, en espérant que ça suffira; ou le déplacer (il sera reconstruit par Oracle à la destination).

LOB : déplacer le LOB dans un autre tablespace

Si les indexes sont reconstruits sans erreurs mais que la table continue à générer des erreurs, et qu'elle contient des colonnes LOB, il peut s'agir d'un LOB corrompu. Dans ce cas, déplacer le LOB pour le reconstruire (voir Déplacer un LOB dans un autre tablespace)

Dernier recours : dupliquer la table en excluant les blocs corrompus

Le code suivant permet de dupliquer la table "tab1" vers une table "tab1_new" sans les lignes illisibles :

REM Create a new table based on the table that is producing errors with no rows:
create table MYSCHEMA.MYTABLE_NEW
as
select * 
from MYSCHEMA.MYTABLE -- eventually, can add : "partition(<PARTITION_NUM>)"
where  1=2;

REM Create the table "bad_rows" to keep track of ROWIDs pointing to affected rows:
create table bad_rows (row_id rowid
                      ,oracle_error_code number);
set serveroutput on
DECLARE
  TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
  CURSOR c1 IS select /*+ index(tab1) */ rowid
  from MYSCHEMA.MYTABLE tab1 -- eventually, can add : "partition(<PARTITION_NUM>)"
  -- eventually add a filter to exclude others not necessary rows. Ex : where IMEI is NOT NULL;
  ;
  r RowIDTab;
  rows NATURAL := 20000;
  bad_rows number := 0 ;
  errors number;
  error_code number;
  myrowid rowid;
BEGIN
  OPEN c1;
  LOOP
   FETCH c1 BULK COLLECT INTO r LIMIT rows;
   EXIT WHEN r.count=0;
   BEGIN
    FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
     insert into MYSCHEMA.MYTABLE_NEW 
     select /*+ ROWID(A) */ *
     from MYSCHEMA.MYTABLE A -- eventually, can add : "partition(<PARTITION_NUM>)"
     where rowid = r(i);
   EXCEPTION
   when OTHERS then
    BEGIN
     errors := SQL%BULK_EXCEPTIONS.COUNT;
     FOR err1 IN 1..errors LOOP
      error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
      if error_code in (1410, 8103, 1578) then
       myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
       bad_rows := bad_rows + 1;
       insert into bad_rows values(myrowid, error_code);
      else
       raise;
      end if;
     END LOOP;
    END;
   END;
   commit;
  END LOOP;
  commit;
  CLOSE c1;
  dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/

Ressources

FIX ORA-0600 (1) : http://dmbadi.blogspot.com/2019/06/how-to-fix-ora-600-12700-internal-error.html
FIX ORA-0600 (2) : https://oracle-base.com/articles/misc/detect-and-correct-corruption
DBMS_REPAIR : https://www.jobacle.nl/?p=746
DUMP_ORPHAN_KEYS, SKIP_CORRUPT_BLOCKS : http://www.dba-oracle.com/concepts/dump_orphan_keys.htm
DBMS_REPAIR.FIX_CORRUPT_BLOCK : http://www.dba-oracle.com/t_fix_corrupt_blocks.htm
DB_ULTRA_SAFE, DB_BLOCK_CHECK* : http://www.dba-oracle.com/t_db_block_checking.htm
TOUT + DBV : http://www.datadisk.co.uk/html_docs/oracle/db_corruption.htm

Erreur RMAN-03014 après upgrade d'une base RMAN

PROBLEME :

La base RMAN a été upgradée de 11.2.0.2 en 11.2.0.4. La base elle-même a subit un "startup upgrade"+upgrade, et redémarrage, mais lors des sauvegardes on a l'erreur :

The database reported error while performing requested operation.
SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so
CHILDERROR:1
Process exit code: 1, Signal: 0
RMAN PID=21788
APPERROR:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of allocate command at 03/13/2019 19:52:30
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 03/13/2019 19:52:30
RMAN-10015: error compiling PL/SQL program
RMAN-10014: PL/SQL error 0 on line 3244 column 3: Statement ignored
RMAN-10014: PL/SQL error 302 on line 3244 column 22: component 'GETPOLLEDREC' must be declared
RMAN-10014: PL/SQL error 0 on line 2280 column 6: Statement ignored
RMAN-10014: PL/SQL error 306 on line 2280 column 13: wrong number or types of arguments in call to 'BEGINRMANOUTPUTRESYNC'
RMAN-10014: PL/SQL error 0 on line 1757 column 12: Statement ignored
RMAN-10014: PL/SQL error 306 on line 1757 column 12: wrong number or types of arguments in call to 'CHECKTABLESPACE'
RMAN-10014: PL/SQL error 0 on line 1189 column 9: Statement ignored
RMAN-10014: PL/SQL error 302 on line 1189 column 25: component 'ISROUTDUPLICATERECORD' must be declared
Recovery Manager complete.

SOLUTION :

il faut AUSSI (condition mal documentée) mettre à jour le CATALOG RMAN.

Procédure :
Connexion à une base quelconque , en ouvrant le catalogue

$ . oraenv
ORCL
$ rman target / catalog RMAN/RMANPASSWD@RMAN
RMAN> upgrade catalog;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

recovery catalog upgraded to version 11.02.00.04
DBMS_RCVMAN package upgraded to version 11.02.00.04
DBMS_RCVCAT package upgraded to version 11.02.00.04

Ressortir de RMAN et revenir afin qu'il reconnecte le catalog par rapport à la base, sinon on aura l'erreur :

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20021:
database not set
RMAN-06031: could not translate database keyword)

Lancer ensuite une commande quelconque, list backup par exemple, ce qui force la resynchronisation :

RMAN> list backup summary;
starting full resync of recovery catalog
full resync complete

La resynchronisation remonte dans le catalogue RMAN les backups présents dans les control files, s'il y en a. Après le resync, tous les backups sont donc accessibles à partir du catalogue.

Erreurs en connexion SYSDBA sous windows

ORA-01031

En connexion SYSDBA avec SQLPLUS sous Windows, on a l'erreur :

ORA-01031: insufficient privileges

Solution :

Editer dbhome_1\NETWORK\ADMIN\SQLNET.ORA
Changer SQLNET.AUTHENTICATION_SERVICES=(NONE) en "(NTS)"

ORA-12560

en connexion locale SYSDBA avec sqlplus sous Windows, bien que l'utilisateur appartienne bien au groupe oradba, on a une erreur :


TNS-12560: TNS : erreur d'adaptateur de protocole

Verifier que le client Oracle n'a pas été installé par-dessus le serveur ! Dans ce cas, le PATH dirige par défaut vers sqlplus du client, qui n'a pas toutes les variables.
Il faut éditer le PATH pour supprimer les références au path "client_1"

RMAN duplicate erreur "ORA-01843"

RMAN termine avec cette erreur :

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/10/2019 15:01:11
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01843: ce n'est pas un mois valide

La commande DUPLICATE (peut arriver aussi avec RESTORE ou BACKUP) est pourtant formatée correctement

SET UNTIL TIME "to_date('09-10-2019_20:30:00','DD-MM-YYYY_HH24:MI:SS')";

Mais parfois ça ne suffit pas. Il faut donc initialiser NLS_LANG avec le bon code page avant de lancer RMAN (en ligne de commande ou dans le script bash qui lance rman) :
export NLS_LANG=AMERICAN

Vérifier les possibilités de réduction des tablespaces

La requête suivante indique l'espace libre et surtout le HWM pour estimer les réductions de fichiers possibles :

set lines 2000
col file_name format A66
def blocksize = 4096

select file_name,
ceil( blocks*'&blocksize'/1024/1024) TOTAL,
ceil( (nvl(hwm,1)*'&blocksize')/1024/1024 ) HWM,
ceil( blocks*'&blocksize'/1024/1024) - ceil( (nvl(hwm,1)*'&blocksize')/1024/1024 ) RECOVERABLE
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
  from dba_extents
  group by file_id ) b
where a.file_id = b.file_id(+) and a.tablespace_name = '&tsname';