Le tablespace SYSAUX fait partie de votre base de données au même titre que SYSTEM. Il n’est pas supporté de le recréer. Cela est notamment dû au fait que certaines tables qui le constituent ne sont pas recréées dans le script catalog.sql mais font parties des fichiers inclus dans $ORACLE_HOME/rdbms/admin/sql.bsq qui sont lancés automatiquement par la commande CREATE DATABASE. Bref, ce qui suit :

• Ne doit pas être utilisé
• Ne fonctionne sûrement pas avec votre version de base de données
• Dépend du système d’exploitation
• Peut être facilement évité par une sauvegarde régulière
• Ne peut être qu’une solution temporaire pour lancer une commande exp ou expdp par exemple
• Nécessite au moins une heure de mise en oeuvre
• a été testé sur une base de données 11.2 sur Linux x86

Si vous êtes voulez vous amuser sur une base de tests ou que vous êtes vraiment (mais alors vraiment !) dans les ennuis, vous pouvez peut-être lire ce qui suit ; cela dit, je vous conseille plutôt de chercher vos sauvegardes…

D’abord, évitez le suraccident

Commencer par passer du temps à vous assurer que ce que vous allez tenter nous vous envoie pas dans une situation encore plus critique que celle dans laquelle vous êtes déjà ! Faites donc, au minimum, une sauvegarde de votre base de données ; si possible, travaillez sur une copie…

Simuler l’accident

Au cas où vous en doutiez, vous ne pouvez pas supprimer SYSAUX :

drop tablespace SYSAUX including contents

*

ERROR at line 1:

ORA-13501: Cannot drop SYSAUX tablespace

On va donc lui mettre un petit coup derrière l’oreille histoire de…

cd /u01/app/oracle/oradata/WHITE

dd if=/dev/zero of=sysaux01.dbf bs=8k count=5000

sqlplus / as sysdba



alter system flush buffer_cache;



select * from dba_hist_snapshot;

              *

ERROR at line 1:

ORA-01115: IO error reading block from file  (block # )

ORA-01110: data file 2: '/u01/app/oracle/oradata/WHITE/sysaux01.dbf'

ORA-27072: File I/O error

Additional information: 4

Additional information: 6610

Remarquez que vous ne pouvez pas passer le tablespace offline ni le supprimer pour le recréer et que si vous partez pour un redémarrage, vous aurez une petite déconvenue:

startup force;

ORACLE instance started.



Total System Global Area  836976640 bytes

Fixed Size		    1347396 bytes

Variable Size		  490733756 bytes

Database Buffers	  339738624 bytes

Redo Buffers		    5156864 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file

ORA-01110: data file 2: '/u01/app/oracle/oradata/WHITE/sysaux01.dbf'

Indiquez votre intention de supprimer le fichier SYSAUX

Passer la votre base de données en mode mount et indiquez votre intention de supprimer le fichier correspondant à SYSAUX pour pouvoir démarrer votre base de données :

startup mount;



col file# new_value fno

select file#

  from v$datafile

  where ts# in (select ts#

                  from v$tablespace

                 where name='SYSAUX');



alter database datafile &&fno offline drop;

Démarrer en mode upgrade

Pour pourvoir supprimer SYSAUX, il faut que vous utilisiez le mode upgrade (ou migrate) :

shutdown immediate;

startup upgrade;

Supprimer le tablespace

Jusqu’ici tout était simple :

drop tablespace sysaux including contents;



*

ERROR at line 1:

ORA-29857: domain indexes and/or secondary objects exist in the tablespace

Pour supprimer SYSAUX, il faut faire le grand ménage. Pour cela, créez des scripts SQL et exécutez-les jusqu’à ce que plus aucune table, index, IOT, cluster, file d’attente, etc ne soit contenu dans le tablespace :

select 'drop user '||owner||' cascade;', count(*)

 from dba_segments

 where tablespace_name='SYSAUX'

   and owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'WMSYS')

 group by owner;

Pour SYS, SYSTEM, SYSMAN et WMSYS, c’est plus délicat car le drop user ne fonctionne pas… Il faut donc supprimer les objets 1 par 1 ; En outre, impossible de supprimer les files d’attente « à la régulière »; positionnez donc l’event 10851 :

alter session

  set events '10851 trace name context forever, level 2';



select 'drop table '||qt.OWNER||'.'||qt.QUEUE_TABLE||' cascade constraints purge;' cmd

  from dba_queue_tables qt,

      dba_tables t

 where qt.owner=t.owner

   and t.tablespace_name='SYSAUX'

   and qt.QUEUE_TABLE=t.table_name;



select  'drop table '||owner||'.'||iot_name||' purge;' cmd

  from dba_tables

 where tablespace_name='SYSAUX'

  and iot_name is not null;



select 'drop table '||l.OWNER||'.'||l.TABLE_NAME||' cascade constraints purge;' cmd

  from dba_lobs l

 where l.tablespace_name='SYSAUX';



select 'drop cluster '||owner||'.'||cluster_name||' including tables cascade constraints;' cmd

  from dba_tables

 where tablespace_name = 'SYSAUX'

   and cluster_name is not null;



select 'drop table '||table_owner||'.'||table_name||' cascade constraints purge;' cmd

from dba_tab_partitions

where tablespace_name='SYSAUX'

group by 'drop table '||table_owner||'.'||table_name||' cascade constraints purge;';



select 'drop table '||owner||'.'||table_name||' cascade constraint purge;' cmd

  from dba_tables

 where tablespace_name='SYSAUX';



select 'drop table '||table_owner||'.'||table_name||' cascade constraint purge;' cmd

  from dba_indexes

 where tablespace_name='SYSAUX';



select 'drop materialized view '||table_owner||'.'||table_name||';' cmd

  from dba_indexes

 where tablespace_name='SYSAUX';

Gérer les erreur ORA-604

Vous avez presque tout supprimé et des erreurs comme celles ci-dessous apparaissent:

ORA-00604: error occurred at recursive SQL level 1

ORA-00942: table or view does not exist

Si vous regardez en détail avec une trace 10046, vous vous apercevrez qu’il s’agit de référence du fait de l’utilisation de types aux tables plscope_identifier$ et plscope_action$ du fichier dplsql.bsq. Recréez ces 2 tables et leurs index en enlevant la référence à SYSAUX ; vous pourrez alors terminer de supprimer les tables:

select 'drop table '||table_owner||'.'||table_name||';' cmd

  from dba_indexes

 where tablespace_name='SYSAUX';



select 'drop table '||owner||'.'||table_name||';' cmd

  from dba_tables

 where tablespace_name='SYSAUX';

Supprimez le tablespace (enfin!)

Vous pouvez supprimer le tablespace :

drop tablespace SYSAUX

Supprimez de nouveau les 2 tables ci-dessus:

drop table plscope_identifier$ purge;

drop table plscope_action$ purge;

Si vous voulez éviter de réinstaller toutes les options, vous pouvez en outre lancer la commande qui suit :

select 'drop table '||table_name||' cascade constraints purge;'

  from dba_tables

 where table_name like '%REGISTRY$%'

 and owner='SYS';

Désactivez l’event 10851

Evidemment, vous pouvez utiliser un alter session set events '10851 trace name context off'; toutefois, le plus simple est de redémarrer l’instance en mode upgrade:

shutdown immediate;

startup upgrade;

Récréez SYSAUX

Vous pouvez recréer le tablespace comme ci-dessous ; les options sont importantes :

create tablespace SYSAUX

  datafile '/u01/app/oracle/oradata/WHITE/sysaux.dbf'

  size 250M extent management local

  SEGMENT SPACE MANAGEMENT AUTO;

Appliquez les fichier .bsq

Il faut que vous rejouiez l’ensemble des fichiers renvoyés par la commande ci-dessous ; en effet les tables associées sont créées avec la commande create database et pas dans les scripts catalog.sql ou catproc.sql

grep -i "tablespace sysaux" -l *.bsq

Pour cela, créez des copies et modifiez toutes les variables que vous chercherez dans vi avec la commande [ESC]+/"

Les tailles des champs dépendent de l’OS. Dans mon cas, ça donne pour vi la séquence de commande suivante pour chaque fichier:

:%s/"M_IDEN"/30/g

:%s/"S_OPFL"/38/g

:%s/"M_HOST"/128/g

:%s/"M_TERM"/255/g

:%s/"M_XDBI"/128/g

:%s/"S_PRFL"/16/g

:%s/"S_ACFL"/19/g

:%s/"M_VCSZ"/4000/g

:%s/"M_PIDL"/16/g

:%s/"M_DATF"/200/g

:%s/"M_IDBI"/16/g

:%s/"M_CSIZ"/2000/g

:%s/"KOTHCL"/17/g

:%s/"M_LTID"/30/g

:%s/"M_GBID"/64/g

:%s/"M_GTID"/64/g

:%s/"M_XCMT"/255/g

:%s/"M_UNML"/64/g

Une fois les scripts modifiés, vous pouvez les jouer ; dans mon cas, j’ai créé des copies .gg:

@daw.gg

@ddm.gg

@dlmnr.gg

@dmanage.gg

@doptim.gg

@dplsql.gg

@drac.gg

@drep.gg

@dsec.gg

@dtxnspc.gg

Rejouez catalog et catproc

Vous pouvez rejouer les scripts de création du catalogue :

@catalog

@catproc

Vérifiez que les composants sont bien ré-installés :

col comp_name format a50

col VERSION format a12

col status format a9

set lines 120

set tab off

select COMP_NAME, VERSION, STATUS

  from dba_registry

/



COMP_NAME                                          VERSION      STATUS

-------------------------------------------------- ------------ ---------

Oracle Database Catalog Views                      11.2.0.2.0   VALID

Oracle Database Packages and Types                 11.2.0.2.0   VALID

Vous aurez peut-être besoin de réinstaller certaines options pour utiliser votre base de données. Reportez-vous à la section correspondante de la documentation.

Redémarrez votre base de données

Pour terminer, redémarrez votre instance :

shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.



startup

ORACLE instance started.



Total System Global Area  941600768 bytes

Fixed Size                  1348096 bytes

Variable Size             562040320 bytes

Database Buffers          373293056 bytes

Redo Buffers                4919296 bytes

Database mounted.

Database opened.

Fuyez!

Comme vous le voyez, en utilisant les fichiers *.bsq, vous avez modifié au plus bas le dictionnaire de données. Je ne saurais trop vous conseiller de fuir cette base de données. Faite vite l’export de vos données et reconstruisez une base de données à partir de rien. Ca vous évitera d’autres grincements de dents…

Si vous n’êtes pas encore convaincu, rechercher une éventuelle corruption:

exec dbms_hm.run_check('Dictionary Integrity Check','check')



select f.description, f.damage_description

  from v$hm_finding f, v$hm_run r

 where f.run_id=r.run_id

   and r.name='check';

Oops ! Quand je vous le disais…

Pour plus d’informations à propos de SYSAUX, lisez :

• SYSAUX New Mandatory Tablespace in Oracle 10g and higher [ID 243246.1]
• les sections suivantes de la documentation SYSAUX tablespace et Managing the SYSAUX Tablespace