Oracle - Problèmes divers et solutions
Diverses astuces
- Intégrer des données UTF8 avec sqlplus
- logrotate alert.log
- OUI 19c - page d'accueil vide
- Purge des traces
- Recreate DBConsole
- Récupérer les touches de direction et l'historique dans sqlplus linux
- Services SYSTEMD base et listener
- switch user ("su" like)
- Erreur ORA-00600 - index, ou blocs, ou lob corrompu
- Erreur RMAN-03014 après upgrade d'une base RMAN
- Erreurs en connexion SYSDBA sous windows
- RMAN duplicate erreur "ORA-01843"
- Vérifier les possibilités de réduction des tablespaces
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 :
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';