Impasse suite à une tentative d'export

Après avoir tenté un export d'un schéma sur une base de données avec l'outil EXPDP, j'ai obtenu une erreur, qui malheureusement, je ne me souviens pas. Suite à cette erreur, j'ai tenté à nouveau un export mais sans succès car rien n'aboutissait.

J'ai alors vérifié l'état de la session dans la vue « v$session » et, j'ai remarqué que ma session provenant de l'outil EXPDP était bloquée par une autre. La session bloquante correspondait à ma précédente session qui était tombée en erreur.

Pour me permettre de réaliser l'export, j'ai alors décidé de terminer la session avec la commande "Alter system kill session". À ma grande surprise, la base de données m'a retournée une erreur me disant que la session n'existe pas. Suite à cela, j'ai décidé d'être plus radicale puis de terminer la processus de la session directement sur le serveur. Encore là, je ne trouvais pas de processus correspondant à la session. Me voilà dans une impasse... Le seul moyen que j'ai pu faire pour m'en sortir a été de redémarrer l'instance de la base de données avec la commande "shutdown abort" car la commande "shutdown immediate" ne parvenait pas à arrêter les processus.

Si vous avez déjà rencontré ce genre de problème et que vous l’avez résolu autrement, merci de partager avec moi.

Recouvrement suite à une copie en mode "begin backup"

Pour répliquer rapidement un environnement complet, nous utilisons le logiciel "ShadowImage". Cette opération permet de créer une image complet d'un serveur pour ensuite diagnostiquer des problèmes majeurs survenu sur cet environnement.

L'environnement répliqué consiste à un serveur Sun Solaris 10 sur lequel réside une base de données 10gR2 Enterprise Edition.

Pour que la cpoie répliquée de la base de données soit fonctionnelle, nous mettons celle-ci en mode "begin backup" avant de démarrer le processus de copie. Malgré cela, une fois que le processus est terminé, nous rencontrons des problèmes avec la base de données copiée. Voici les problèmes rencontrées avec leurs solutions :

Recouvrement du tablespace SYSTEM
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/uprodz2-obd001_u02/oradata/P059/system01.dbf'

Mettre la base de données en mode "mount" :

sqlplus / as sysdba
shutdown immediate
startup mount

2 scénarios possibles :

1. Recouvrement à partir d'un copie de sécurité des controlfiles

1.1 Récupérer des anciens CONTROLFILE qui furent générés par la commande :

alter database backup controlfile
to '/u01/home/dba/oracle/admin/ORCL/udump/control.2010-09-16-09:00.bkp';

prendre un copie du controlfile puis copier le backup controlfile aux 2 endroits

1.2 Recouvrer la base de données à l'aide des controlfiles récupérés

Recover database until time '2010-10-15 09:00:00' using backup controlfile;
OU
Recover database using backup controlfile until cancel;

Si nécessaire, ne pas hésiter à proposer les redofiles non archivés comme fichier d'archive afin de compléter le recouvrement

1.3 Ouvrir la base de données

alter database open resetlogs;

il est fortement conseillé de prendre une copie de sécurité de toute la base de données avant d'exécuter une ouverture de la base de données avec l'option "RESETLOGS".

2. Recouvrement à partir des controlfiles courants (actuels)

2.1 Recouvrer la base de données avec la commande suivante à partir des controlfiles actuels

Recover database until time '2010-10-15 09:00:00' using backup controlfile;

Si nécessaire, ne pas hésiter à proposer les redofiles non archivés comme fichier d'archive afin de compléter le recouvrement

2.2 Ouvrir la base de données

alter database open resetlogs;

il est fortement conseillé de prendre une copie de sécurité de toute la base de données avant d'exécuter une ouverture de la base de données avec l'option "RESETLOGS".


Corruption du tablespace UNDO

Lors du démarrage de la base de données, si celle-ci tarde beaucoup à démarrer ou que le démarrage renvoi une erreur ORA-600 avec l'argument [4194], c'est probablement occasionné par une corruption dans les segments du tablespace " undo ".

Avant de procéder tel qu'indiqué, assurez-vous que le fichier " alert_ORCL.log " contient bel et bien des lignes affichant le code d'erreur mentionné précédemment.

Compte tenu que le contenu du tablespace " undo " sert à conserver les transactions non sauvegardées (non commit), on peut détruire ces données sans danger. Voici les étapes pour recréer le tablespace " undo " :

sqlplus / as sysdba

Alter database open;

create undo tablespace undo2
datafile '/u05/oradata/ORCL/undo02.dbf' size 50 m
autoextend on;

alter system set undo_tablespace = undo2 ;

drop tablespace undo including contents and datafiles ;

create undo tablespace undo
datafile '/u05/oradata/ORCL/undo01.dbf' size 1000 m
autoextend on ;

alter system set undo_tablespace = undo ;

drop tablespace undo2 including contents and datafiles ;

Pour réaliser les commandes précédentes, il se peut que vous soyez dans l'obligation de désactiver la gestion automatique des segments " undo " :

sqlplus / as sysdba
startup mount
create pfile from spfile;

Modifier le paramètre suivant dans le fichier " initORCL.ora " :
undo_management = MANUAL

shutdown immediate
startup pfile=initORCL.ora

Une fois l'intervention terminée, n'oubliez pas de revenir en mode "spfile".


Il y a un autre cas, que l'un de mes collègues a rencontré. Il a eu à recréer les controlfiles pour ressusciter la base de données.

Recompilation de packages PL/SQL sous Oracle RAC 11gR2

Suite à la mise en place d'une nouvelle version d'un package PL/SQL, certaines applications obtenaient l'erreur suivante :

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "ABC.PKG_CALC_PAMNT"
ORA-06508: PL/SQL: could not find program unit being called: "ABC.PKG_CALC_PAMNT"
ORA-06512: at "ABC.PKG_FACTRN", line 4

Habituellement, cette erreur disparait dès le prochain accès au package. C'est le cas pour la session active sauf que, lorsqu'il y avait une nouvelle connexion avec le même compte Oracle, l'erreur réapparaissait.

Étrangement, cette erreur ne se produisait pas pour certaines applications... pourquoi? Et bien, c'est surement parce que nous utilisons les services Oracle pour la répartition de la charge sur les différents noeuds hébergeant les instances du Cluster RAC 11gR2. Compte tenu que cette erreur était, disons-le, absurde, j'ai commencé à douter de la "fraîcheur" des caches en mémoire. Après quelques essais, je me suis rendu compte que le problème survenait sur une instance et non sur les autres. J'ai alors décidé de forcer une réinitialisation du "shared pool" pour que la nouvelle définition de l'objet y soit stockée. Pour ce faire, j'ai exécuté la commande : "Alter system flush shared_pool;" et, effectivement, l'erreur n'a par réapparu.

ORA-01078 au démarrage d'une instance Oracle 11g sous ASM

J'ai rencontré cette erreur lors d'un redémarrage de la base de données suite à des modifications de paramètres de bases de données qui sont stockés dans un SPFILE.

La base de données est de la version Oracle 11gR2 et elle est liée à un Grid Infrastructure qui utilise Oracle ASM et Oracle Restart.

Voici les étapes que j'ai suivi pour résoudre le problème :

--
oracle@romeo:/tmp> srvctl stop database -d F100
oracle@romeo:/tmp> srvctl start database -d F100
PRCR-1079 : Failed to start resource ora.f100.db
ORA-01078: failure in processing system parameters
CRS-2674: Start of 'ora.f100.db' on 'romeo' failed

--
-- Tentative de démarrage avec SQL*Plus
--
oracle@romeo:/tmp> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mer. Mai 5 12:07:49 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORA-01078: failure in processing system parameters
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 1577058304 cannot be set to more than MEMORY_TARGET 1358954496.

--
-- Tentative de correction de la valeur du paramètre en erreur
--
SQL> alter system set SGA_MAX_SIZE=100 scope=spfile;
alter system set SGA_MAX_SIZE=100 scope=spfile
*
ERROR at line 1:
ORA-01034: ORACLE not available
ID de processus : 0
ID de session : 0, Numéro de série : 0

--
-- Essai de création du pfile à l'image du spfile
--
SQL> create pfile='/tmp/pfileEC.ora' from spfile;
create pfile='/tmp/pfileEC.ora' from spfile
*
ERROR at line 1:
ORA-01565: error in identifying file '?/dbs/spfile@.ora'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

--
-- Afficher l'emplacement exact du SPFILE
--
SQL> ! cat $ORACLE_HOME/dbs/initF100.ora
SPFILE='+SYSDG01/F100/spfileF100.ora'

--
-- Création du pfile à l'image du spfile
--
SQL> create pfile='/tmp/pfileEC.ora' from spfile='+SYSDG01/F100/spfileF100.ora';

File created.

SQL> exit

--
-- Éditer le pfile pour enlever la ligne SGA_MAX_SIZE
--
oracle@romeo:/tmp> vi pfileEC.ora

--
-- Redémarrer la base de données avec le PFILE pour s'assurer
-- aucune autre erreur
--
oracle@romeo:/tmp> sqlplus / as sysdba


SQL*Plus: Release 11.2.0.1.0 Production on Mer. Mai 5 12:12:40 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to an idle instance.

SQL> startup pfile='/tmp/pfileEC.ora'

ORACLE instance started.

Total System Global Area 1871208448 bytes
Fixed Size 2149152 bytes
Variable Size 1325405408 bytes
Database Buffers 536870912 bytes
Redo Buffers 6782976 bytes
Database mounted.
Database opened.

--
-- Création du spfile à l'image du pfile
--
SQL> Create spfile='+SYSDG01/F100/spfileF100.ora' from pfile='/tmp/pfileEC.ora';

File created.

--
-- Arrêt de la base de données
--
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

--
-- Redémarrer la base de données à partir du Grid Infrastructure
--
oracle@romeo:/tmp> srvctl start database -d F100

Calculer le MEMORY_TARGET sous 11g

À partir des valeurs actuelles du SGA et du PGA, vous pouvez facilement déterminer les valeurs des nouveaux paramètres relatifs à la gestion automatique de la mémoire. Pour simplifier le calcul, je me suis bâtit une requête qui me propose les valeurs de base. La voici :

Select '-- Memory Target = '
||round(to_char((qry_sga_target.value+
greatest(qry_pga_target.value,
qry_pga_alloc.value)))/1024/1024)||' MB'||chr(10)||
'Alter system set memory_target='
||to_char((qry_sga_target.value+
greatest(qry_pga_target.value,
qry_pga_alloc.value)))||' scope=spfile;'||chr(10)||
'-- Memory Max Target = '
||round(to_char((qry_sga_max.value+
greatest(qry_pga_target.value,
qry_pga_alloc.value)))/1024/1024)||' MB'||chr(10)||
'Alter system set memory_max_target='
||to_char((qry_sga_max.value+
greatest(qry_pga_target.value,
qry_pga_alloc.value)))||' scope=spfile;'||chr(10)||
'Alter system reset sga_target scope=spfile;'||chr(10)||
'Alter system reset pga_aggregate_target scope=spfile;'||chr(10)||
'-- SGA_MAX_SIZE ne doit pas etre superieur a MEMORY_TARGET'||chr(10)||
'Alter system set sga_max_size='
||to_char((qry_sga_target.value+
greatest(qry_pga_target.value,
qry_pga_alloc.value)))||' scope=spfile;' CMD_SQL
from (select value from v$parameter
where name='sga_target') qry_sga_target,
(select value from v$parameter
where name='pga_aggregate_target') qry_pga_target,
(select value from v$pgastat
where name='maximum PGA allocated') qry_pga_alloc,
(select value from v$parameter
where name='sga_max_size') qry_sga_max;


Il ne vous reste qu'à estimer le MEMORY_MAX_TARGET.

Manipuler avec soins car contient des paramètres standards, cachés et Hints

L'une des premières vérifications que je vous suggère fortement de faire lorsque vous effectuez de l'optimisation de requêtes est de vérifier la valeur des paramètres de bases de données qui ont été personnalisées (modifiées).

Dans plusieurs cas que j'ai rencontrés, surtout dans le cadre de projet de migration, certains paramètres avaient été modifiés pour contourner des problèmes (bugs) et, suite à la migration, ceux-ci sont la plupart du temps plus nécessaires.

Utiliser la commande "ALTER SESSION" pour ceux qui sont modifiable dynamiquement. Vous pourrez vérifier le comportement de vos requêtes avant d'appliquer le changement de façon permanente (ALTER SYSTEM) sur la base de données.

Autres conseils, évitez de modifier les paramètres de base de données, surtout ceux cachés. Vous aurez des problèmes lors des futures migrations. Si vous n'avez pas le choix, je vous conseille de bien documenter leur utilisation.

C'est la même situation pour les HINTS de requêtes, ce n'est que des diachylons et… souvenez-vous qu'un diachylon fini toujours par se décoller :)

Comportement des statistiques systèmes en mode RAC

Lors de la collecte des statistiques systèmes d'un environnement RAC, on peut déclencher la collecte à partir de n'importe quelle instance. Tout s'effectue correctement... À vrai dire, pas exactement car les autres instances attachées au cluster ne prennent pas en compte les nouvelles valeurs.

Je me suis aperçu de ce problème quand une requête est devenue gourmande. Le plan d'exécution de la requête était complètement différent sur 2 instances. En me connectant sur l'une des instances du cluster, j'ai vérifié les valeurs des statistiques (select * from sys.aux_stats$). C'est à ce moment que j'ai remarqué qu'elles avaient été régénérées récemment et l'un de mes collègues me l'a confirmé. J'ai aussi eu la confirmation que la lenteur avait été observée depuis la date de la régénération des statistiques. Donc, je pouvais déduire que la collecte de statistiques systèmes était l'élément déclencheur.

Que se soit sur l'une ou l'autre des instances du cluster, les mêmes statistiques sont supposées être utilisées dont je suspecte que le problème soit au niveau de la mémoire ou d'une certaine cache. J'ai tenté une simple purge du "shared pool" et celle des données mais sans succès. Comme à l'habitude, j'ai fait une petite recherche sur Metalink et effectivement, il y a un problème de répertorier sur la base de données 10.2.0.3. (ID 7645777.8) concernant un plan d'exécution différent sur des instances RAC. La solution : Recycle the instances... Qu'est-ce que cela veut dire exactement ?!? J'imagine que c'est l'équivalent d'un redémarrage d'instance et, si c'est le cas, et bien, je ne peux pas me le permettre. Vu que ce bug concerne les statistiques systèmes alors, j'ai décidé de pousser la vérification en influençant l'optimisateur pour qu'il n'utilise pas les statistiques liées au CPU. Pour ce faire, j'ai utilisé le hint "NO_CPU_COSTING". Les résultats furent concluants, j'ai obtenu le même plan d'exécution que sur l'instance où la requête est optimale.

Tant qu'à pousser, j'ai décidé d'effectuer une trace de l'optimisateur sur l'exécution de la requête sur chacune des instances avec la commande :

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1'

Les 2 fichiers de trace ont démontrés que les statistiques systèmes sont différentes donc ça confirme que l'optimisateur n'utilise pas les mêmes valeurs d'une instance à l'autre... Inquiétant ?

Après réflexion, je me suis dit, pourquoi ne pas répéter ou presque les mêmes étapes de collecte de statistiques systèmes mais cette fois-ci, en étant connecté sur l'instance n'ayant pas le même comportement ou si vous préférez, le même plan d'exécution. Alors, j'ai effectué ceci :

-- Établir une connexion sur l'instance qui n'utilise pas les nouvelles valeurs
connect sys@inst1 as sysdba

-- Prendre une copie des statistiques actuelles
Drop table stats_bkp;
EXECUTE DBMS_STATS.CREATE_STAT_TABLE ('SYS','stats_bkp','SYSAUX');
EXECUTE DBMS_STATS.EXPORT_SYSTEM_STATS (stattab => 'stats_bkp', statid => 'stats_bkp', statown => 'SYS');
select * from sys.stats_bkp;

-- Charger les valeurs
execute DBMS_STATS.DELETE_SYSTEM_STATS;
EXECUTE DBMS_STATS.IMPORT_SYSTEM_STATS (stattab => 'stats_bkp', statid => 'stats_bkp', statown => 'SYS');
select * from sys.aux_stats$;

-- Initialiser les espaces mémoires
Alter system flush shared_pool;
Alter system flush buffer_cache;

Et bien oui, ça fonctionne! La requête a maintenant le même plan d'exécution que sur l'autre instance. Stop! Le problème s'est déplacé sur l'autre instance... oups! Ok, alors, je vais refaire la même chose, mais cette fois-ci sur l'instance où le problème s'est déplacé. Suite à l'exécution des commandes précédentes, le plan d'exécution est redevenu celui d'auparavant et, sans impacter le plan d'exécution sur l'autre instance.

Je crois qu'un redémarrage de l'instance aurait pu régler le problème sauf que lorsque nous sommes sur un environnement de production, cela n'est pas toujours une solution et ce, même si c'est un environnement RAC à moins que vous preniez le temps de déplacer tous les services vers une autre instance puis que vous patientez jusqu'à ce que toutes les sessions se soient reconnectés vers la nouvelle instance.

Modification de la valeur du paramètre "service_names"

Lorsque vous créer vos services puis que vous appliquez le changement avec la commande « ALTER SYSTEM », il faut que chacun des services soit entre apostrophes.

Si vous traitez la ligne comme une seule chaîne, vous obtiendrez une erreur vous disant que vous avez atteint la taille maximum autorisée pour un paramètre qui est de 255 caractères.

Mauvaise façon
Alter system set service_names = 'RH_FRMT, SALES_FRMT' scope=both;

Bonne façon
Alter system set service_names = 'RH_FRMT', 'SALES_FRMT' scope=both;

Pour générer la commande (extraction) à partir des valeurs déjà assignées, j’utilise cette commande :

Select 'Alter system set service_names = '''
||replace(value,', ',''', ''')
||''' scope=both;'
from v$parameter where name = 'service_names';

Bug CONNECT BY sous 10.2.0.3.0

Lors de l'exécution d'une requête hiérarchique sur une vue qui utilise la clause CONNECT BY... START WITH, l'erreur suivante se produit :

Dans SQL*Plus :
ORA-03113: end-of-file on communication channel

Dans l'Alert log :
ORA-07445: exception encountered: core dump [qknLazOpn()+4] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []

Cette erreur est un Bug documenté chez Oracle (bug #5234379)

Voici un cas test pour reproduire le problème:

CREATE TABLE FOO (ID NUMBER, PARENT_ID NUMBER);

CREATE VIEW BAR (ID, PARENT_ID) AS
SELECT ID, PARENT_ID
FROM FOO
WHERE EXISTS (
SELECT 'x' FROM DUAL
UNION ALL
SELECT 'x' FROM DUAL);

SELECT ID, PARENT_ID
FROM BAR
CONNECT BY PARENT_ID = PRIOR ID
START WITH PARENT_ID = 0;


Ce bug n'est pas corrigé avec le Patchset 10.2.0.3.0. Par contre, il existe une alternative (workaround) :

Alter system set "_optimizer_connect_by_cost_based" = false scope=both;

Redimensionner les journaux (redo log files)

Voici un script que j'ai créé pour me simplifier la vie lorsque je veux redimensionner les journaux (redo logs). Vous pouvez l'exécuter sans crainte car il n'exécute aucune commande qui modifiera les fichiers. Ce script bâtit tout simplement les commandes à exécuter ultérieurement pour redimensionner les redo log files :

set define on wrap on numwidth 12 serveroutput on
set pagesize 999 termout on arraysize 2 linesize 2000
Accept vTailRedo prompt 'Taille (en Mbytes) des Redolog (Nul=20M): ' default 20

Declare
vTailRedo number := &&vTailRedo; -- Taille (en Mbytes) des redo files

vLigne varchar2(4000);
vCommt varchar2(20);
vMember varchar2(4000);

Cursor cur_log is
Select i.instance_name,l.group#,l.thread#,l.status,
round((l.bytes/1024/1024),0) taill
from v$log l, v$instance i
order by l.thread#, l.group#;

Cursor cur_member(pNoGroup Number) is
Select rownum,l.group#,l.member
from v$logfile l
where l.group# = pNoGroup
order by l.group#,l.member;

Begin
dbms_output.put_line('-- ****************************');
dbms_output.put_line('-- Redimensionner les redo logs');
dbms_output.put_line('-- ****************************');
dbms_output.put_line('-- => Le statut du redolog file doit être INACTIF');
dbms_output.put_line(chr(10));
For i in cur_log
-- Extraire les groupes
Loop
dbms_output.put_line('-- Thread/Groupe # 'i.thread#'/'i.group#
' => Status: 'i.status
' => Taille actuelle : 'i.taill'M');
if vTailRedo = i.taill then
vCommt := '-- ';
else
vCommt := null;
end if;
vLigne := vCommt'Alter database drop logfile group 'i.group#';'chr(10)
vCommt'Alter database 'chr(10)
vCommt' add logfile thread 'i.thread#
' group 'i.group#' (';
For J in cur_member(i.group#)
-- Extraire les membres du groupe
Loop
if j.rownum > 1 then
vLigne := vLigne', ';
end if;
-- Vérifier si ASM est utilisé
if substr(j.member,1,1) = '+' then
vMember:= substr(j.member,1,(instr(j.member,'/',1)-1));
else
vMember := j.member;
end if;
vLigne := vLigne''''vMember'''';
End loop;
vLigne := vLigne') size 'vTailRedo'M reuse;';
dbms_output.put_line(vLigne);
dbms_output.put_line(chr(10));
vLigne := null;
End loop;
dbms_output.put_line('-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
dbms_output.put_line('Commandes à effectuer pour changer le REDO'
' Log active/courant');
dbms_output.put_line('-- Pour forcer le switch de groupe de redo log :');
dbms_output.put_line('ALTER SYSTEM SWITCH LOGFILE;');
dbms_output.put_line('-- Archiver le redo courant et activer le suivant');
dbms_output.put_line('ALTER SYSTEM CHECKPOINT;');
End;
/