Attention au Real Time Query !!!! by albanlepunk

Administration Oracle, dataguard pas de Commentaire »

Comment se prémunir d’un passage non souhaité en Real Time Query.

Comme vous le savez, dans une configuration Dataguard, il est possible de faire de la consultation “temps réel”!
C’est à dire que la base applique les redologs qu’elle reçoit de sa base primaire tout en étant ouverte en lecture seule.
C’est un Option de l’Enterprise Edition : Le Real Time Query.

Seulement lorsque votre serveur va redémarer, si vous n’avez pas géré ce cas particulier dans votre script de redémarrage,
votre base standby va démarrer en startup normal et se mettre par défaut dans le mode Real Time Query.

SQL> SELECT open_mode FROM   V$DATABASE;

READ ONLY WITH APPLY

Comment faire pour ne pas utiliser malgré soi cette option payante?

il faut positionner (comme souvent) un paramètre caché : ‘_query_on_physical’
Il va nous prémunir d’un démarrage en mode Real Time Query en renvoyant une erreur “ORA-16669 : instance cannot be opened because the Active Data Guard option is disabled.”
Mais on pourra toujours ouvrir la base en lecture seule manuellement.

SQL> alter system set “_query_on_physical”=false scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  812529152 bytes

Fixed Size                  2264280 bytes

Variable Size             960781800 bytes

Database Buffers           54654432 bytes

Redo Buffers                3498640 bytes
Database mounted.
ORA-16669: instance cannot be opened because the Active Data Guard option is
disabled

SQL> alter database open read only;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ ONLY

On est donc sûr de ne pas utiliser cette option malencontreusement!!!

Passer une base RAC en archivelog.

Administration Oracle, Cluster RAC pas de Commentaire »

Pour passer une base clusterisée en mode archivelog, rien de plus simple.

Se connecter sur l’instance du premier nœud et arrêter l’instance :

[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 13 14:24:30 2010
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connecte a :
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> archive log list
mode Database log              mode No Archive
Archivage automatique             Desactive
Destination de l’archive             USE_DB_RECOVERY_FILE_DEST
Sequence de journal en ligne la plus ancienne     57
Sequence de journal courante            59
SQL>

On voit bien que la base n’est pas en mode archivelog et que la destination des archives est positionnée par défaut dans la Flash Recovery Area.

Normalement on arrête les instances sur tous les nœuds et on passe la base en archivelog.

Que se passe t il si on oublie d’arrêter la deuxième instance du cluster??

On va donc oublier d’arrêter l’instance sur le noeud 2 avant de modifier la base.

On fait la manip sur le noeud1.

SQL> shutdown immediate
Base de donnees fermee.
Base de donnees demontee.
Instance ORACLE arretee.
SQL> startup mount
Instance ORACLE lancee.
Total System Global Area  477073408 bytes
Fixed Size                  1337324 bytes
Variable Size             209717268 bytes
Database Buffers          260046848 bytes
Redo Buffers                5971968 bytes
Base de donnees montee.
SQL>
SQL> alter database archivelog;
Base de donnees modifiee.

SQL> alter database open;
Base de donnees modifiee.

On génère quelques archives :

SQL> alter system switch logfile;
Systeme modifie.

Le cluster gère le cas et arrête lui même l’instance “oubliée” sans mettre un seul message d’erreur!

On est donc forcée de la redémarrer à la main et elle prend obligatoirement la modification.

SQL> alter system archive log current;
Systeme modifie.

Si on vérifie :

SQL> select name, THREAD#, SEQUENCE#, ARCHIVED from v$archived_log;

+DGFRA/racdb/archivelog/2010_12_13/thread_1_seq_63.265.737650775
1         63 YES

NAME
——————————————————————————–
THREAD#  SEQUENCE# ARC
———- ———- —
+DGFRA/racdb/archivelog/2010_12_13/thread_2_seq_4.266.737650777
2          4 YES

La base archive bien les deux threads correspondants aux deux nœuds.

Albanlepunk

Le paramètre caché _allow_resetlogs_corruption

Administration Oracle, Divers, Rman, scripts et trucs pas de Commentaire »

Dans cet article nous allons voir un exemple de récupération de données après restauration et quand la base ne veut quand même pas s’ouvrir.

Par exemple un disque lâche sur le serveur et on perd le datafile présent dessus, pour restaurer convenablement ce datafile, il nous faut les archives générées entre le dernier backup et le crash mais il nous manque les archives en question. On ne peut donc plus ouvrir la base et manque de chance, ce datafile contient des tables super importantes.

On sait pertinemment que l’on va perdre des données mais on aimerait en perdre le moins possible quitte à utiliser des moyens “spéciaux” pour cela.

A cause de l’intégrité faussée de la base, on ne peut plus l’ouvrir normallement.

Le paramètre caché qui permet de démarrer la base soit en mode  désynchronisée c’est à dire avec au moins un de ses entêtes de datafiles incorrect soit parce qu’on a  endommagé un ou plusieurs redo logs ou encore parce qu’il nous manque une archive ou deux pour restaurer un datafile est _allow_resetlogs_corruption.

Dans le cas ou votre datafile contiendrait des données type index ou que l’on peut facilement recréer, le mieux serait toujours de dropper le tablespace, puis de le recréer / recharger mais dans le cas ou les données ne sont pas facilement reconstructibles quoi faire?

L’idée à suivre pourrait être la suivante :

Restaurer le datafile ou la base à partir du dernier backup.

Jouer les archives jusqu’au dernier numéro de séquence correct (pour cela regarder dans rman : list backup of archivelog all).

Arrêter et redémarrer l’instance avec le paramètre _allow_resetlogs_corruption = TRUE.

Exporter les données souhaitées.

Restaurer la base à partir du dernier backup.

Mettre le datafile offline.

Dropper et recréer le tablespace.

Réimporter les données “récupérées”. Vous risquez néanmoins d’avoir des décalages applicatifs selon les cas, vous n’êtes donc peut être pas exempt de correction des données.

Re backuper la base correctement.

Dans notre cas, la base refuse de s’ouvrir après une restauration RMAN.

la restauration until logseq s’est bien passée, le recover database aussi. Il n’y a qu’au moment d’ouvrir la base en open resetlogs que la base réclame “une récupération de données après défaillance matérielle”.

Ceci à cause d’un datafile dont la mise offline ne s’était pas déroulé correctement.

J’ai donc remis ce datafile online, redémarré avec le paramètre _allow_resetlogs_corruption à TRUE qui a permis de corriger l’entête du fichier.

Puis on arrête la base et on la redémarre normalement.

BEMOL & RESTRICTION : Oracle ne maintient les bases qui ont été démarrées avec ce paramètre, elles sont potentiellement corrompues. C’est donc en dernier ressort et dans le cas désespérés qu’il faut l’utiliser et tout de suite après exporter les données et les réimporter dans une belle base toute neuve.

Alban le punk

clean up - delete Enterprise manager ( EM ) console Alerts

Administration Oracle pas de Commentaire »

The GRID console , though a very useful tool, has the very bad habit to maintain the display of irrelevant ALERTS on its main / Home page.
To get rid of these informations is not obvious nor , of course, documented by Oracle.

SYSMAN account includes 3 relevant objects related to Alerts :
MGMT_SECURITY , actually a View, based on the TABLE SYSMAN.MGMT_VIOLATIONS
MGMT_CURRENT_SECURITY , actually a View, based on the TABLE SYSMAN.MGMT_CURRENT_VIOLATIONS

Here is its description :

Column Name	ID	Data Type	Null?

TARGET_GUID	1	RAW (16)	N
METRIC_GUID	2	RAW (16)	N
KEY_VALUE	3	VARCHAR2 (256 Byte)	N
COLLECTION_TIMESTAMP	4	DATE	N
SEVERITY_CODE	5	NUMBER	N
SEVERITY_TYPE	6	NUMBER	N
SEVERITY_DURATION	7	NUMBER	Y
SEVERITY_GUID	8	RAW (16)	Y
ANNOTATED_FLAG	9	NUMBER	Y
NOTIFICATION_STATUS	10	NUMBER	Y
MESSAGE	11	VARCHAR2 (4000 Byte)	Y
MESSAGE_NLSID	12	VARCHAR2 (64 Byte)	Y
MESSAGE_PARAMS	13	VARCHAR2 (4000 Byte)	Y
ACTION_MESSAGE	14	VARCHAR2 (4000 Byte)	Y
ACTION_NLSID	15	VARCHAR2 (64 Byte)	Y
ACTION_MESSAGE_PARAMS	16	VARCHAR2 (4000 Byte)	Y
ADVISORY_ID	17	VARCHAR2 (64 Byte)	Y
LOAD_TIMESTAMP	18	DATE	Y
USER_NAME	19	VARCHAR2 (64 Byte)	Yand

DELETE_CURRENT_SEVERITY a procedure that  belongs to SYSMAN.EM_SEVERITY package.
Here is its description :

PROCEDURE delete_current_severity (
p_target_guid IN RAW,
p_metric_guid IN RAW,
p_key_value   IN VARCHAR2);

Note : A trigger deltes Rows from MGMT_CURRENT_SSEVERITY AFTER DELETE from MGMT_SEVERITY !!?

A full description of the parameters  can be obtained via the Dictionnary :

SQL> select column_name, comments
from  dba_col_comments
where table_name=’MGMT_SEVERITY’

COLUMN_NAME	COMMENTS

ADVISORY_ID	Advisory ID of the severity
LOAD_TIMESTAMP	Date and time when the severity was loaded
USER_NAME	Name of the user to load the severity
TARGET_GUID	 The target guid of the severity
METRIC_GUID	 The metric guid of the severity
KEY_VALUE	 The key value of the severity
COLLECTION_TIMESTAMP	 The timestamp at which the severity occurred
SEVERITY_CODE	The severity codes for error, warnin, critical etc.
    These codes are backwards compatible with EM 9i and EM 10gR1.
      15 - CLEAR
      18 - INFO
      20 - WARNING
      25 - CRITICAL
     115 - AGENT UNREACHABLE CLEART
     125 - AGENT UNREACHABLE START
     215 - BLACKOUT END
     225 - BLACKOUT START
     315 - METRIC ERROR END
     325 - METRIC ERROR START
    Codes 115 and above are applicable only for response/status metric
SEVERITY_TYPE	 The severity type allows an application that is selecting
      from this table to filter the rows returned by the type of
      severity.  Values in this column are:
           0 - METRIC THRESHOLD ALERT
           1 - AVAILABILITY
SEVERITY_DURATION	The delta time, in hours, from when the severity was logged
    until it was cleared.
SEVERITY_GUID	 The unique id of the severity. Defaults to SYS_GUID()
ANNOTATED_FLAG	A flag to indicate whether the severity is annotated or not.
NOTIFICATION_STATUS	The column used by the notification sub system to determine
    notification status of the severity.
MESSAGE	The message of the severity.  The messages usually contain
    details about what triggered this severity.
MESSAGE_NLSID	The NLS ID of the severity message.
MESSAGE_PARAMS	URL encoded parameters separated by "&" to be used to
    format the severity message.
ACTION_MESSAGE	Suggested action message in english for this severity
ACTION_NLSID	The NLS ID of the action message.
ACTION_MESSAGE_PARAMS	URL encoded parameters for translating action message

Pour avoir des infos sur les alertes :

SELECT distinct s.target_guid, s.metric_guid,t.target_name,t.target_type,
    m.metric_name,   s.collection_timestamp, s.key_value
       FROM mgmt_targets t ,mgmt_current_severity s, mgmt_metrics m
            where s.target_guid = t.target_guid
            and m.metric_guid = s.metric_guid
            and t.target_type='oracle_database' -- interested only in database resultes (not hosts, application server,...)
            and s.collection_timestamp < to_timestamp('01/07/2010','DD/MM/YYYY') -- my deadline
            and t.target_name like 'my_database%'

To clean up, use something like (be careful could delete more rows than you expoect (i have to check that later on…)):

delete from mgmt_severity
WHERE (target_guid,metric_guid) IN
(SELECT s.target_guid,s.metric_guid
FROM mgmt_targets t ,mgmt_severity s
where s.target_guid = t.target_guid
and t.target_type=’oracle_database’
and s.collection_timestamp < to_timestamp(’25/06/2010′,’DD/MM/YYYY’)
and t.target_name like ‘pprtr%’)

Solution erreur ORA-01157 , ORA-01110 : datafile perdu , corrompu ou effacé

Administration Oracle, Musée des erreurs, Rman, Sécurité Oracle pas de Commentaire »

En 6 clics

(oui je sais c’est un peu long)
L’hypothese est toujours qu’on ait une suavegarde RMAN en place qui tienne la route ( voir mon article : Sauvegarde Oracle 11g avec RMAN en 5 clics , pour + d’infos)
Avec la console OEM :
Cliquer sur l’onglet ‘Availability’, puis le lien ‘perform recovery’, choisir ‘recovery scope = datafile’ , clic ‘recover’, clic ‘Next’, clic ‘Next’ et ‘Submit’.
C’est fini ;-(

En 2 commandes

On va voir ici comment récupérer un fichier perdu…sans savoir lequel ni connaitre RMAN !
En fait 2 commandes suffisent : ADVISE FAILURE et REPAIR FAILURE. C’est tout bonnement miraculeux ca s’appelle Data Recovery Advisor !

Supposons qu’on ait donc perdu un datafile.
Au bout d’un moment (si, si) Oracle va finir par s’en apercevoir et on aura une erreur ORA-1157.
Oracle ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: ‘/tmp/dd.dbf’ DATAFILE

Les étapes de restauration sont très simples grace à DATA RECOVERY ADVISOR disponible avec Oracle 11g.
Concrètement ce Data recovery advisor consiste en qq commandes que l’on pourra lancer sous RMAN.

3 commandes utiles :

RMAN> LIST FAILURE : liste les pbs en cours détectés (fichiers manquents, corrompus, etc…)

RMAN> ADVISE FAILURE : propose des solution de recovery et produit un script associé

RMAN> REPAIR FAILURE : execute le script de récupération

Voici un exemple concret :

Connexion a RMAN

$> rman target sys@portab2-a-dd:1521/DD1.dd.com
connected to target database: DD1 (DBID=1767735647, not open)
RMAN>

Recherche de problemes éventuels :

RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
1242 HIGH OPEN 23-JUL-10 One or more non-system datafiles are missing

Ou si l’on souhaite avoir des infos plus détaillées sur le problème on utilise l’ID qui nous intéresse , ici 1242

RMAN> list failure 1242 detail;

List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
1242 HIGH OPEN 23-JUL-10 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 1242
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
1245 HIGH OPEN 23-JUL-10 Datafile 6: ‘/tmp/dd.dbf’ is missing
Impact: Some objects in tablespace DD might be unavailable

En suite la partie Assistance / conseil proprement dite (ADvisor) :

RMAN> advise failure;

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=155 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /tmp/dd.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
—— ——————
1 Restore and recover datafile 6
Strategy: The repair includes complete media recovery with no data loss
Repair script: /oracle/diag/rdbms/dd1/DD1/hm/reco_1841908370.hm

On peut dès lors récupérer le fichier de données effacé en une étape :

RMAN> REPAIR FAILURE

Ou si l’on veut mieux maitriser ce que l’on fait, visualiser le script et lancer les étapes de restauration et recovery manuellement :

Vérification du script généré :

$> more /oracle/diag/rdbms/dd1/DD1/hm/reco_1841908370.hm
# restore and recover datafile
restore datafile 6;
recover datafile 6;

Restauration (récupération) du fichier perdu à partir des Backups Sets de la sauvegarde RMAN :

RMAN> restore datafile 6;

Starting restore at 23-JUL-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /tmp/dd.dbf
channel ORA_DISK_1: reading from backup piece /oracle/flash_recovery_area/DD1/DD1/backupset/2010_07_22/o1_mf_nnnd1_BACUP_INCR_DEL_BAC_0_64jlhkvc_.bkp
channel ORA_DISK_1: piece handle=/oracle/flash_recovery_area/DD1/DD1/backupset/2010_07_22/o1_mf_nnnd1_BACUP_INCR_DEL_BAC_0_64jlhkvc_.bkp tag=BACUP_INCR_DEL_BAC_072210033828
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 23-JUL-10

Récupération de données supplémentaires et synchronisation des fichiers si nécessaire :

RMAN> recover datafile 6;

Starting recover at 23-JUL-10

using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-JUL-10

Démarrage et arret automatique Oracle 11g sous Linux

Administration Oracle pas de Commentaire »

Démarrage automatique sous linux / Unix

Oracle (et le listener s’il n’est pas protégé par un mot de passe) peuvent être démarrés automatiquement comme un service.  L’implantation des fichiers et les ‘run level’ utilisés au boot dépendent des distributions.
Voici un exemple qui fonctionne sous Ubuntu :

  1. se positionner dans le répertoire d’initialisation qui va bien, ici /etc/init.d
  2. créer un fichier  /etc/oraclectl comme suit avec les bonnes valeur de ORACLE_HOME
    et de compte propriétaire oracle

    # les 2 commentaires ‘chkconfig’ et ‘description’ suivants sont OBLIGATOIRES
    # on fournit liste_run_level priorite_start priorite_stop, ici 2,3,5 et 80 , 20
    # chkconfig: 235 80 20
    # description: ajout de service auto pour start/stop oracle#!/bin/bash
    ORACLE_OWNER=”oracle”
    ORACLE_HOME=”/oracle/db11a”
    case “$1″ in
    start)
    echo -n $”Starting Oracle DB:”
    su - $ORACLE_OWNER -c “$ORACLE_HOME/bin/dbstart $ORACLE_HOME”
    echo “OK”
    ;;
    stop)
    echo -n $”Stopping Oracle DB:”
    su - $ORACLE_OWNER -c “$ORACLE_HOME/bin/dbshut $ORACLE_HOME”
    echo “OK”
    ;;
    *)
    echo $”Usage: $0 {start|stop}”
    esac

  3. changer le groupe (dba en général) et les permissions du fichier

    $> sudo chgrp dba oraclectl
    $> sudo chmod 750 oraclectl

  4. tester le script a la main

$>  ./oraclectl  start

  1. ajouter le service oraclectl
    Le moyen le plus simple pour ce faire plutôt que de bidouiller les rc0.d , rc1.d, rc2.d …et autres liens symboliques est d’utiliser la commande chkconfig qui va bien (cf parametres mlis en commentaires en début du script précédent

$> sudo chkconfig –add oraclectl
# ou sans utiliser les commentaires du script (on precise explicitement les levels :
$> chkconfig –level 235 oraclectl on
$> #verifier :$> chkconfig -l oraclectl
oraclectl 0:off 1:off 2:on 3:on 4:off 5:on 6:off
$> # verifier si necessaire le runlevel courant
$> runlevel
N 2

Apres un reboot de la machine  le service oracle devrait démarrer automatiquement…

Migrer le Charset de WE8ISO8859P1 à W8ISOMSWIN1252 en Cluster RAC

Administration Oracle, Divers, scripts et trucs pas de Commentaire »

Passage du charset de WE8ISO8859P1 à W8ISOMSWIN1252 sous

RAC

Pour intégrer le passage à l’euro.

Le charset W8ISOMSWIN1252  est un subset de WE8ISO8859P1, il n’y a donc aucun risque mais que des avantages à passer en WE8ISOMSWIN1252 plutôt qu’en WE8ISO8859P15 qui possède mui aussi le caractère ‘€’ (y compris sur linux cf Note Metalink 264294.1).

Sur le nœud 1

[oracle@rac1 script]$ export ORACLE_SID=SID1

[oracle@rac1 script]$ sql

SQL*Plus: Release 10.2.0.4.0 - Production on Wed May 26 14:16:36 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

With the Real Application Clusters option

SQL> create pfile=’/app/oracle/product/10.2.0/db_1/dbs/initSID1.ora’ from spfile;

File created.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Sur le nœud 2

[oracle@rac2 ~]$ export ORACLE_SID=SID2

[oracle@rac2 ~]$ sql

SQL*Plus: Release 10.2.0.4.0 - Production on Wed May 26 14:14:49 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

With the Real Application Clusters option

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

Sur le nœud 1

Dans /app/oracle/product/10.2.0/db_1/dbs on commente les  deux lignes  suivantes cluster_database_instances et cluster_database dans initSID1.ora.

SQL> startup mount pfile=’/app/oracle/product/10.2.0/db_1/dbs/initSID1.ora’

ORACLE instance started.

Total System Global Area 1577058304 bytes

Fixed Size 2084232 bytes

Variable Size 503317112 bytes

Database Buffers 1056964608 bytes

Redo Buffers 14692352 bytes

Database mounted.

SQL> alter system ENABLE RESTRICTED SESSION;

System altered.

SQL> alter database open;

Database altered.

SQL> ALTER DATABASE character SET WE8MSWIN1252 ;

Database altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

On dé commente les paramètres dans initSID1.ora.

Avant d’arrêter le noeud 1 penser à recréer le spfile dans ASM.

On re démarre les deux instances

Nœud 1

SQL> startup

ORACLE instance started.

Total System Global Area 1577058304 bytes

Fixed Size 2084232 bytes

Variable Size 503317112 bytes

Database Buffers 1056964608 bytes

Redo Buffers 14692352 bytes

Database mounted.

Database opened.

SQL> quit

Nœud 2

SQL> startup

ORACLE instance started.

Total System Global Area 1577058304 bytes

Fixed Size 2084232 bytes

Variable Size 503317112 bytes

Database Buffers 1056964608 bytes

Redo Buffers 14692352 bytes

Database mounted.

Database opened.

SQL> quit

Le tour est joué.

albanlepunk

probleme demarrage console entreprise manager 11g et solutions

Administration Oracle, Musée des erreurs pas de Commentaire »

Suite à des création successives de BDs la console standalone (db control) ne veut plus démarrer pour la BD courante.

$> emctl start dbconsole…failed

L’environnement minimal

…il en faut un !
Le <ORACLE_HOME> doit être correct, le < ORACLE_SID > doit être positionné sur la base cible et le <HOSTNAME>  (nom logique local de la machine) avoir une valeur correcte.
Si tel n’est pas le cas, Oracle ne trouve pas la configuration et on a un message d’erreur du genre

OC4J Configuration issue /oracle/db11g/oc4j/j2ee/OC4J_DBConsole_monpc_TEST not found.

La raison en est qu’il existe des répertoires suffixés avec la valeur de <HOSTNAME> et < ORACLE_SID> notamment :

<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<HOSTNAME>_<ORACLE_SID>
exemple : /oracle/db11g/oc4j/j2ee/OC4J_DBConsole_monpc_TEST
<ORACLE_HOME><HOSTNAME>_<ORACLE_SID>
exemple : /oracle/db11g/monpc_TEST

Accessoirement la base et le listener doivent être démarrés pour que la console soit complètement fonctionnelle.

rem : on peut cependant lancer la console sans rien et s’en servir pour démarrer la base et le listener…mais je ne vous le conseille pas

Solution brute

1) recréer le référentiel de la console dans la base ( ou repository) qui correspond au schema SYSMAN :

$> emca -repos recreate

2) reconfigurer la console :

$> emca -config dbcontrol db

Faire un duplicate avec RMAN sous windows! by albanlepunk

Administration Oracle, Rman pas de Commentaire »

# Créer le fichier de mot de passe pour la base clone

orapwd file=$ORACLE_HOMEdbsorapwCLONE.ora password=XXXXXXXXXX

# Créer le fichier d’init pour la base clone

# Copier le init.ora de la base cible

SQL> create pfile=’$ORACLE_HOMEdbsinitCLONE.ora’ from spfile;

File created.

#  Apporter les modifs nécéssaires au fichier init.

db_file_name_convert = (’$ORADATACLONE’, ‘$ORADATACLONE’)
log_file_name_convert = (’$ORADATACLONE’, ‘$ORADATACLONE’)
control_files = ‘$ORADATACLONEcontrol01.ctl’
, ‘$ORADATACLONEcontrol02.ctl’
, ‘$ORADATACLONEcontrol03.ctl’
db_name = ‘CLONE’
instance_name = ‘CLONE’
background_dump_dest = ‘$ADMINCLONEbdump’
core_dump_dest = ‘$ADMINCLONEcdump’
user_dump_dest = ‘$ADMINCLONEudump’
service_names = ‘SERVICE.CLONE’
log_archive_dest_1 = ‘location=$ORADATACLONEarchive MANDATORY’

# Créer un nouveau service windows pour la base CLONE à dupliquer.

oradim -new -sid CLONE -intpwd D:oracleproduct10.2.0db_1databaseorapwCLONE.ora.ora -startmode auto -pfile ‘D:oracleproduct10.2.0db_1databaseinitCLONE.ora’

# Créer les repertoires admin

mkdir $ORADATACLONE
mkdir $ORADATACLONEarchive
mkdir $ORADATACLONEbdump
mkdir $ORADATACLONEcdump
mkdir $ORADATACLONEcreate
mkdir $ORADATACLONEpfile
mkdir $ORADATACLONEscripts
mkdir $ORADATACLONEudump

# Démarrer l’instance CLONE

set ORACLE_SID=CLONE

sqlplus “/ as sysdba”

SQL> startup nomount

# Modifier le listener et le tnsnames pour que l’on puisse se connecter à la base auxiliaire

sqlplus “sys/XXXXXXXX@CLONE as sysdba”

# Vérifier que la base cible soit montée ou ouverte

# Se connecter à la base cible et la base auxiliaire avec RMAN

rman target sys/XXXXXXXX@TARGET auxiliary sys/xxxxxxxx@CLONE

connected to target database: TARGET (DBID=3850478880)
connected to auxiliary database: CLONE (not mounted)

RMAN>

# Lancer la commande duplicate
# On peut mettre un critère de temps.
# ex : duplicate target database to CLONE until time ‘SYSDATE-1′;.
# ou dans un script run

duplicate target database to CLONE;

Ex de script jusqu’à la sequence X du Thread Y (because RAC)

run
{ sql ‘alter system archive log current’;
set until sequence=11212 THREAD=2;
allocate auxiliary channel ch1 type disk;
allocate auxiliary channel ch2 type disk;
allocate auxiliary channel ch3 type disk;
allocate auxiliary channel ch4 type disk;
duplicate target database to ‘DBRESTAU’ pfile=’D:oracleproduct10.2.0db_1databaseinitCLONE.ora’ nofilenamecheck; }
exit

expdp / impdp - nouveaux export / import 10g Oracle

Administration Oracle, Divers pas de Commentaire »

En Oracle 10g il existe un nouvel executable permettant de faire des export / import optimisés.

Quelques différences minimales à connaitre par rapport à l’export / import classique :

  • les executables s’appellent respectivement expdp et impdp (toujours dans $ORACLE_HOME/bin)
  • appelable aussi via un package standard : le package SYS.DBMS_DATAPUMP
  • on peut filtrer des tables (option INCLUDE / EXCLUDE)
  • on peut filtrer des lignes (option QUERY )
  • le parametre OWNER est remplace par SCHEMA
  • necessite un objet Oracle ‘ directory ‘ pour fonctionner
  • peut être parallelisé

exemple d’import

SQL> create or replace directory dd_dir as ‘/work/pprun/oexp’;
SQL> grant read, write on directory dd_dir to public;
$> mpdp system schemas=SCOTT logfile=impSCOTT.log directory=dd_dir dumpfile=expSCOTT.dmp

Infos uttiles dans le dictionnaire :

vue DBA_DATAPUMP_JOB :  jobs actifs de DataPump et  statuts
vue DBA_DATAPUMP_SESSIONS :  les sessions d’utilisateurs ayant lancé des jobs  DataPump.

Jobs , queues et ordonnancement

Administration Oracle, Divers 1 Commentaire »

La 10 et la 11g offrent un nouveau package pour gérer les jobs : DBMS_SCHEDULER en remplacement de DBMS_JOB (voir en fin d’article une migration, ou un résumé des changement de l’un à l’autre extrait de la doc officielle.
Plusieurs avantages :
- une gestion des droits plus fine,
- des jobs nommés (finis les job_id peu explicites),
- une gestion des intervalles de temps simplifiée (finis les calculs savants avec SYSDATE mais une frequence horaire, hebdomadaire, mensuelle,…)

Prerequis

Qu’il existe au moins une JOB QUEUE et le process associé

SQL> SHOW PARAMETER JOB

ou

SQL> select name
from v$bgprocess
where name like ‘CJQ%’

Le user créateur du JOB doit aussi avoir le privilège system ‘CREATE JOB’ …ou etre DBA

Infos minimales pour créer / planifier un JOB

Job Name: le nom du job (unique / aux noms d’objets)
Job Type: 3 types possibles : PLSQL_BLOCK, STORED_PROCEDURE, EXECUTABLE. .
Job Action: un bloc, un nom de proc, ou un chemin complet d’executable
Start Date: date de debut (la 1ere fois ou le job s’execute , format : TIMESTAMP WITH TIME ZONE
Enabled: TRUE ou FALSE. Attention !!! par default le job est créé DISABLED
Repeat Interval: intervalle de répétition. On peut utiliser l’aritmetique de date ‘old style’ comme avec DBMS_JOB, ou préférablement la notion de fréquence :
exemple :FREQ=WEEKLY;BYDAY=MON,THU;BYHOUR=18;BYMINUTE=0
les valeurs possibles sont BYMONTH, BYMONTHDAY, BYYEARDAY, BYDAY, BYHOUR, BYMINUTE, BYWEEKNO

On peut facilement tester l’execution en lancant le Job sans attendre…

exec DBMS_SCHEDULER.run_job (job_name => ‘job_dd’);

Création d’un Job

On utilise le package DBMS_SCHEDULER et la procédure CREATE_JOB

BEGIN
DBMS_SCHEDULER.create_job (
job_name => ‘job_dd’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN NULL; /* a fe ren */ END;’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=DAILY’,
enabled => TRUE,
comments => ‘c est pas obligatoire mais un bon commentaire…’);
END;
/

– SUPPRESSION

exec DBMS_SCHEDULER.drop_job (job_name => ‘job_dd’ );

remarque : si l’on ne veut pas supprimer un JOB définitivement mais simplement empechere son activation pendant un certain temps, on peut mettre son statut à DISABLED :

dbms_scheduler.disable

Des infos dans le dictionnaire ?

A minima

SQL> select job_name, enabled
from user_scheduler_jobs;

ou avec un peu plus de détails sur la planification

SQL> select JOB_NAME , JOB_CREATOR , JOB_TYPE , JOB_ACTION , STATE ,
START_DATE , REPEAT_INTERVAL , LAST_START_DATE , NEXT_RUN_DATE
FROM USER_SCHEDULER_JOBS

JOB_NAME JOB_CREATOR JOB_TYPE JOB_ACTION STATE START_DATE REPEAT_INTERVAL NEXT_RUN_DATE
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
JOB_DD SYSTEM PLSQL_BLOCK BEGIN NULL;END; SCHEDULED 08-SEP-08 11.00.03 FREQ=DAILY 09-SEP-08 11.00.03

mais aussi les vues
DBA_SCHEDULER_SCHEDULES - informations sur les jobs planifiés courants
DBA_SCHEDULER_PROGRAMS , DBA_SCHEDULER_PROGRAM_ARGS - informations sur les programmes

De DBMS_JOB à DBMS_SCHEDULER

créer un Job

VARIABLE jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:jobno, ‘INSERT INTO employees VALUES (7935, ”SALLY”,
”DOGAN”, ‘’sally.dogan@xyzcorp.com”, NULL, SYSDATE, ”AD_PRES”, NULL,
NULL, NULL, NULL);’, SYSDATE, ‘SYSDATE+1′);
COMMIT;
END;
/

equivalent statement using DBMS_SCHEDULER is the following:

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => ‘job1′,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘INSERT INTO employees VALUES (7935, ”SALLY”,
”DOGAN”, ‘’sally.dogan@xyzcorp.com”, NULL, SYSDATE,”AD_PRES”, NULL,
NULL, NULL, NULL);’);
start_date => SYSDATE,
repeat_interval => ‘FREQ = DAILY; INTERVAL = 1′);
END;
Modifier un Job

BEGIN
DBMS_JOB.WHAT(31, ‘INSERT INTO employees VALUES (7935, ”TOM”, ”DOGAN”,
”tom.dogan@xyzcorp.com”, NULL, SYSDATE,”AD_PRES”, NULL,
NULL, NULL, NULL);’);
COMMIT;
END;
/

equivalent à

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => ‘JOB1′,
attribute => ‘job_action’,
value => ‘INSERT INTO employees VALUES (7935, ”TOM”, ”DOGAN”,
”tom.dogan@xyzcorp.com”, NULL, SYSDATE, ”AD_PRES”, NULL,
NULL, NULL, NULL);’);
END;
/

supprimer un Job de la Job Queue

BEGIN
DBMS_JOB.REMOVE(14144);
COMMIT;
END;
/

équivallent à

BEGIN
DBMS_SCHEDULER.DROP_JOB(’myjob1′);
END;
/

Tablespaces et fichiers

Administration Oracle, Divers 2 Commentaires »

De l’utilité des tablespaces

Un tablespace ou espace disque logique, est une partition logique de la base contenant un ou plusieurs fichiers.
Un fichier appartient à 1 et 1 seul tablespace.
Par défaut un tablespace à la création est ON LINE et donc accessible, il peut être mis OFFLINE (et les fichiers qu’il contient par conséquent) pour en interdire l’accès ou pour certaines opérations de maintenance

Il existe toujours deux tablespace baptisés SYSTEM et SYSAUX .
- SYSTEM : contient le dictionnaire de données et segment d’annulation SYSTEM
- SYSAUX : contient les informations nécessaires aux composants et outils supplémentaires
et traditionnellement on créera également
- ‘TEMP’ : pour les données ’swappées’ sur disque lors d’opération de tri ou de fusion trop volumineuses en mémoire
- ‘UNDO’ : pour les segment d’annulation, qui stockent les images avant, lors des ROLLBACKS

Outre ces tablespaces ’système’ qui servent en quelque sorte à la cuisine interne d’Oracle, il faudra bien tout de même stocker quelques données (et indexs)
Ici plusieurs stratégies sont possibles :
- séparation des indexs et des datas,
- séparation des différents domaines fonctionnels

note : Il serait possible également de stocker les datas, les index dans ces SYSTEM ou SYSAUX.
Ceci est vivement déconseillé, car on aurait ainsi une base minimale peu structurée.

Les tablespaces sont donc utiles pour répartir les données, les index, mais aussi les segments d’annulations et les espaces temporaires sur plusieurs espaces logiques et disques.
Ils permettent :
- performance (répartitions des accès disques),
- souplesse (séparation fonctionnelle ou métier, meilleure granularité des sauvegardes),
- sécurité (séparation des infos systèmes des données utilisateurs)

SQL de base pour la gestion des TBS

SQL> CREATE TABLESPACE …
SQL> DROP TABLESPACE…
SQL> ALTER TABLESPACE…

exemples

SQL> CREATE TABLESPACE COMPTA DATAFILE
‘E:orantdatabaseTESTcompta1TEST.ora’ SIZE 100M;
SQL> ALTER TABLESPACE COMPTA OFFLINE;
SQL> ALTER TABLESPACE COMPTA ADD DATAFILE
‘E:orantdatabaseTESTcompta2TEST.ora’ SIZE 100M;
SQL> DROP TABLESPACE COMPTA INCLUDING CONTENTS AND DATAFILE;
SQL> ALTER TABLESPACE CHARGEMENT_BATCH NOLOGGING;
SQL> ALTER TABLESPACE INFOCENTRE READ ONLY;

Description des tablespaces et fichiers de la base courante dans les vues
DBA_TABLESPACES , DBA_DATA_FILES, DBA_FREE_SPACE du dictionnaire.

SQL> SELECT TABLESPACE_NAME “Nom TBS”, CONTENTS “Type de contenu”, STATUS “EN ligne?”, LOGGING “Journalise?”, BIGFILE FROM DBA_TABLESPACES;

Nom TBS Type de
contenu EN ligne? Journalise?
——– —— — ———–
SYSTEM PERMANENT ONLINE LOGGING NO
UNDOTBS1 UNDO ONLINE LOGGING NO
SYSAUX PERMANENT ONLINE LOGGING NO
TEMP TEMPORARY ONLINE NOLOGGING NO
USERS PERMANENT ONLINE LOGGING NO
EXAMPLE PERMANENT ONLINE LOGGING NO
6 rows selected.

Tablespaces et fichiers

Un tablespace contient AU MOINS un fichier. Celui-ci est créé lors de la création du tablespace, de manière automatique par
Oracle, en fonction des paramètres donnés par la commande CREATE ou ALTER tablespace (emplacement du fichier, nom, taille, et mode d’extension).

note : Lors de la suppression du tablespace (DROP TABLESPACE…) les fichiers correspondant ne sont PAS SUPPRIMES par Oracle par défaut. Utilisez la clause ‘AND DATAFILE’…

exemples

SQL>
Nom_Tbs   Nom_Fic.                                       MO  AUTOEXTENSILE
USERS     C:ORACLEPRODUCT10.1.0ORADATAORCLUSERS01.DBF    5   YES
SYSAUX    C:ORACLEPRODUCT10.1.0ORADATAORCLSYSAUX01.DBF   230 YES
UNDOTBS1  C:ORACLEPRODUCT10.1.0ORADATAORCLUNDOTBS01.DBF  30  YES
SYSTEM    C:ORACLEPRODUCT10.1.0ORADATAORCLSYSTEM01.DBF   440 YES
EXAMPLE   C:ORACLEPRODUCT10.1.0ORADATAORCLEXAMPLE01.DBF  150 NO
EXAMPLE   C:ORACLEPRODUCT10.1.0ORADATAORCLEXAMPLE012.DBF 10  NO

Extension et gestion d’espace des tablespaces et des fichiers

La taille d’un tablespace est la taille de son (ses) fichier(s) d’origine.
Pour augmenter la taille d’un tablespace, il y a 2 solutions :
* Ajouter un fichier au tablespace, qui sera chainé au premier (ALTER TABLESPACE toto ADD DATAFILE…)
* mettre le fichier du tablespace en AUTO extension (ALTER DATABASE DATAFILE toto.dbf AUTOEXTEND ON)
Une table (et tout segment en général) , peut “s’étaler” sur plusieurs fichiers. Ainsi le fait qu’une table sature un tablespace n’est pas bloquant il suffit d’augmenter la taille du tablespace.

autoextension des fichiers

ATTENTION : la clause AUTOEXTEND spécifie la taille d’extension du fichier d’un tablespace. La clause STORAGE INITIAL, NEXT, MINEXTENTS … spécifie la taille d’extension d’UN SEGMENT du tablespace par exemple une table. Ces 2 paramètres sont totalement indépendants. La preuve en est qu’une table (un segment de données) est forcément en allocation dynamique alors qu’un fichier peut avoir une taille fixe (AUTOEXTEND OFF)

note : Le changement de mode AUTOEXTEND se fait avec la commande ‘ALTER DATABASE’ pour les ‘SMALLFILE’ et ‘ALTER TABLESPACE’ pour les ‘BIGFILE’

exemples

SQL> - passage en AUTO extension d’un fichier de tablespace existant
SQL> ALTER DATABASE DATAFILE ‘E:orantdatabaseTESTUsr1TEST.ora’ AUTOEXTEND ON;
SQL> ALTER DATABASE DATAFILE ‘C:ORACLEPRODUCT10.1.0ORADATAORCLEXAMPLE01.DBF’
AUTOEXTEND OFF
SQL> - ajout d’un ficheir auto extensible jusqu’a 100 MO
SQL> ALTER TABLESPACE toto ADD DATAFILE ‘E:orantdatabaseTESTTEST.ora’ SIZE 10M AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

extension des segments

* clause LOCAL : Tablespaces gérés localement (Locally managed tablespaces)
Anciennement les tablespaces étaient gérés au niveau du dictionnaire de données, la gestion de l’espace physique (allocation / libération de blocs) se fait désormais dans l’entête du fichier(s) du tablespace. Une table binaire d’allocation (bitmap) y
est maintenue. C’est le fonctionnement par défaut (sauf pour le tablespace SYSTEM)
Avantages :
* pas de contention en mise à jour au niveau du dictionnaire
* et conséquemment pas d’utilisation de Rollback segment pour ces transactions
* pas de soucis de gestion de l’espace (calcul d’un storage adéquat)
* “coalesce” automatique (fusion des espaces libres contigus pour optimiser l’espace libre)
Evidemment la clause “DEFAULT STORAGE” est invalide pour les tablespaces gérés localement.

* Clause AUTOALLOCATE
C’est Oracle qui gère !

* Clause UNIFORM
Les extents ont tous la meme taille, par défaut 1MO, sinon elle est précisée par le paramètre ‘SIZE’

* clause STORAGE
Les règles et les statistiques d’allocations sont gérées au niveau du dictionnaire.
Pour plus d’informations voir le chapitre sur les ’segments et extents’
changement des paramètres d’un tablespace existant
ALTER TABLESPACE SYSTEM
DEFAULT STORAGE ( INITIAL 100K NEXT 100K MINEXTENTS 1 MAXEXTENTS 300 PCTINCREASE 1);

Quelques exemples de syntaxe

SQL> CREATE TABLESPACE COMPTA DATAFILE ‘E:orantdatabaseTESTcompta1TEST.ora’
SIZE 100M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
SQL> CREATE TABLESPACE COMPTA DATAFILE ‘E:orantdatabaseTESTcompta1TEST.ora’ SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 500K;
SQL> CREATE TABLESPACE COMPTA DATAFILE ‘E:orantdatabaseTESTcompta1TEST.ora’ SIZE 100M EXTENT MANAGEMENT DICTIONARY;

Architecture (s) Oracle

Administration Oracle, Divers pas de Commentaire »

Architectures locales et réparties

On peut distinguer essentiellement 3 types d’architecture globale de systèmes d’information basés sur Oracle :

  • architecture locale
    Tout est sur le même serveur matériel, programme client et serveur de données Oracle

    archi locale

  • architecture client/serveur
    On a 2 poles : client + serveur
    On a un programme client (un exécutable) sur le poste client, dit alors ‘client lourd’ . Le PC communique avec le serveur de données sur un serveur distant, via le réseau, et la couche Oracle Net.

    archi 2 tier

  • architecture 3 tiers
    On a 3 poles : cleint / serveur d’application /serveur de donnéesPas de programme client. Un navigateur suffit sur le poste de travail (dit alors ‘client léger’). Il dialogue avec le seveur de données distant via http.

archi 3 tier

note : il existe également des architectures n- tiers plus anecdotiques, mais conceptuellement intéressantes

Instance ?
Rappel : Une instance est caractérisée par son identificateur : SID, généralement une variable ORACLE_SID positionnée dans l’environnement.

Une instance active en mémoire ce sont :
- des programmes de fond, services ou processus, qui assurent la maintenance du serveur de données et les entrées / sorties fichiers
- des process server (dédiés ou non à un utilisateur)
- une zone globale partagée : la SGA, qui contient essentiellement du cache de buffers
- des zones mémoires dédiées aux utilisateurs : les PGAs (Private Global Area)

instance gene

Schéma de l’architecture générale en mémoire

La SGA
La SGA est essentiellement un cache mémoire qui contient des infos partagées de la base.

Les process de fond
Ils permettent de paralléliser et désynchroniser les accès multi-utilisateur à la base.
Nous allons lister les principaux :

  • DBWn Database Writer
    Ecrit les données modifiées, du cache de données de la SGA vers les fichiers de données. On peut en avoir plusieurs (max 20) suivant la valeur du parametre DB_WRITER_PROCESSES
  • LGWR Log Writer
    Ecrit les mises à jour, du cache de LOG de la SGA vers les ou le fichiers REDOLOG, suivant qu’on utilise des LOGs multiplexés ou non.
  • CKPT Checkpoint
    Signale l’occurence d’un point de reprise (flush de tous les buffers de données), à DBWR
  • PMON Process Monitor
    Libère les ressources et nettoie le cache en cas d’échec d’un process utilisateur
  • SMON System Monitor
    Surveille les process de l’instance et assure les restaurations d’instance
  • RECO Recoverer
    Gère les transactions distribuées (commit à 2 phases)

et optionnellement :

  • ARCn Archiver
    sauvegarde le REDOLOG qui vient d’être terminé
  • Dnnn Dispatcher
    Distribue les accès utilisateurs, vers les serveurs partagés, en architecture multiplexée (Shared Server)
  • Snnn Shared Server
    Process utilisateurs partagé, en architecture multiplexée
  • CJQn Coordinateur de jobs batch
    Jnnn Process fils dédiés aux Jobs
  • QMNn - Queue monitor
    gestionnaire de file d’attente, pour l’option Oracle Advanced QUeuing
  • Pnnn Esclave d’execution Parallele
    Exécution des requêtes parallèles. Le nb max de process est donné par : PARALLEL_MAX_SERVERS
  • LCKn Lock monitor
    verrouillage des ressources utilisées par plusieurs instances
  • LMS Global cache service
    Gestion des ressources inter-instances en architecture cluster (RAC)

instance detail

note : sous Unix/Linux ces process correspondent à des process Unix, qui appartiennent à l’utilisateur Oracle et s’exécutent de manière ‘déconnectée’ (background).
On peut facilement en avoir la liste avec la commande suivante :
$> ps -ef |grep oracle

process unix

Sous Windows ils correspondent à des services :

services

Contraintes d’intégrité

Administration Oracle, Divers pas de Commentaire »

Les contraintes sont des règles de gestion, qui doivent (normalement) être vérifiées lors des mises ? jour (INSERT, UPDATE, DELETE).
Ces contrôles sont centralisés dans la base, au niveau de la structure de la table elle même et non pas déportées sur l’application cliente.
Une mise ? jour qui ne satisfait pas une contrainte, déclenche une erreur d’exécution :’…CONSTRAINT VIOLATED…’
Dans certains cas les contraintes peuvent être momentanément invalidées (DISABLEd ou DEFERRED).

Il y a plusieurs types de contraintes :

- NULL : valeur indéfinie (??)
- NOT NULL : valeur obligatoire (non indéfinie)
- DEFAULT : une valeur par defaut si non renseignée
- UNIQUE : !
- PRIMARY KEY : clé primaire (identifiant de la table, obligatoire ET unique)
- FOREIGN KEY : clé étrangère ‘colonne qui référence la clé primaire d’une autre table)
- CHECK : doit vérifier une condition

On peut créer des contraintes directement avec l’instruction ‘CREATE TABLE’ ou en ajouter / modifier / supprimer, respectivement avec les instructions ‘ALTER TABLE ADD | MODIFY |DROP CONSTRAINT …’

exemples de contraintes

– exemple de table avec des colonnes …
– et des contrainte NOT NULL, UNIQUE, PRIMARY KEY et CHECK

SQL> DROP TABLE employe_dd;

SQL> CREATE TABLE employe_dd
( employee_id NUMBER(6) PRIMARY KEY
, last_name VARCHAR2(25) NOT NULL
, salary NUMBER(8,2)
, CONSTRAINT emp_salary_min_dd CHECK (salary > 0)
, email VARCHAR2(25) NOT NULL
, CONSTRAINT emp_email_dd UNIQUE (email)
, commission_pct NUMBER(2,2)
, department_id NUMBER(4)
) ;

– exemple de table avec clé primaire et étrangère NOMMEES rajoutées ? posteriori
– ici la contrainte NOT NULL est nommée…

SQL> DROP TABLE departement_dd;

SQL> CREATE TABLE departement_dd
( department_id NUMBER(4)
, department_name VARCHAR2(30) CONSTRAINT dept_name_dd_nn NOT NULL
, location_id NUMBER(4)
) ;

SQL> ALTER TABLE departement_dd
ADD ( CONSTRAINT dept_id_dd_pk PRIMARY KEY (department_id));

SQL> ALTER TABLE employe_dd
ADD ( CONSTRAINT emp_dept_dd_fk FOREIGN KEY (department_id)
REFERENCES departement_dd );

Note : la contrainte d’intégrité référentielle impose qu’un employé appartienne a un département EXISTANT.
Symétriquement, si l’on veut supprimer un département qui a des employes on viole la contrainte :
SQL> delete from departments where department_id=10;
–> erreur : ORA-02292: violation de contrainte (HR.EMP_DEPT_FK) d’intégrité - enregistrement fils existant

Il existe une clause ‘ON DELETE CASCADE’ qui permet de déclencher une suppression automatique des lignes ‘filles’ si une ligne ‘mère’ est supprimée : supprimer par exemple tous les emplyés associés ? un departement qui vient d’être supprimé…

SQL> ALTER TABLE employees
ADD ( CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments ON DELETE CASCADE )
SQL> select count(*) from employees
WHERE department_id=100;
–> 6
SQL> DELETE from departments WHERE department_id=100;
–> 1 row deleted
mais les lignes de ‘employees’ correspondantes ont également été supprimées :
SQL> select count(*) from employees
WHERE department_id=100;
–> 0

Infos dans le référentiel

SQL> select * from user_constraints

ou + précisément

SQL> select constraint_name, constraint_type, table_name,
search_condition,r_constraint_name, status
from user_constraints
where table_name like ‘%DD’

CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_CONST_NAME STATUS
—————– ————— ———- —————- ———— ——
EMP_SALARY_MIN_DD C EMPLOYE_DD salary > 0 - ENABLED
SYS_C004030 C EMPLOYE_DD EMAIL IS NOT NULL - ENABLED
SYS_C004029 C EMPLOYE_DD LAST_NAME IS NOT NULL - ENABLED
DEPT_NAME_DD_NN C DEPARTEMENT_DD D_NAME IS NOT NULL - ENABLED
EMP_DEPT_DD_FK R EMPLOYE_DD - DEPT_ID_DD_PK ENABLED
SYS_C004032 P EMPLOYE_DD - - ENABLED
EMP_EMAIL_DD U EMPLOYE_DD - - ENABLED
DEPT_ID_DD_PK P DEPARTEMENT_DD - - ENABLED

invalidation de contraintes

Les clauses ‘DEFERRED’ et ‘DEFERRABLE’ permettent de différer l’application de la contrainte ? la fin de la
transaction :

SQL> create table test(n number check ( n in (10, 20) ) deferrable initially deferred );
SQL> alter session set constraint = deferred;
SQL> insert into test values(100);
–> OK
SQL> insert into test values(200);
SQL> commit;
–> erreur sur la transaction
ORA-02091: transaction annulée
ORA-02290: violation de contraintes (HR.SYS_C004039) de vérification

SQL> alter session set constraint = immediate;
SQL> insert into test values (100);
–> erreur sur l’insertion :
ORA-02290: violation de contraintes (HR.SYS_C004039) de vérification

On peut également utiliser les clause ENABLE/DISABLE de ‘ALTER TABLE’
ENABLE VALIDATE : s’applique aux lignes existantes dans la table
ENABLE NOVALIDATE : aux ligne futures
DISABLE : inhibe la contrainte

SQL> CREATE TABLE test (n number , constraint nb_ok check ( n in (10, 20) ));
SQL> insert into test values (100);
– erreur : ORA-02290: violation de contraintes (HR.NB_OK) de vérification
SQL> ALTER TABLE test DISABLE CONSTRAINT nb_ok ;
SQL> insert into testing values(100);
–> 1 ligne insérée (la contrainte était inhibée)

ALTER TABLE test ENABLE NOVALIDATE CONSTRAINT nb_ok;
–> OK : Table Altered…

ALTER TABLE test ENABLE VALIDATE CONSTRAINT nb_ok;
–> erreur : ORA-02293: impossible de valider (HR.NB_OK)
- violation d’une contrainte de contrôle

la clause validate, force le controle de la contraine sur la lignes existantes et la valeur ‘100′ insérée précédemment est non conforme…

Voici un exemple complet de création de mini base avec contraintes, d’après les tables de démo HR officielles d’Oracle :

Rem
Rem $Header: hr_cre.sql 29-aug-2002.11:44:03 hyeh Exp $
Rem Copyright (c) 2001, 2002, Oracle Corporation. All rights reserved.
Rem

REM ****************************************************
REM Create the REGIONS table to hold region information for locations
REM HR.LOCATIONS table has a foreign key to this table.

Prompt ****** Creating REGIONS table ….

CREATE TABLE regions
( region_id NUMBER
CONSTRAINT region_id_nn NOT NULL
, region_name VARCHAR2(25)
);

CREATE UNIQUE INDEX reg_id_pk
ON regions (region_id);

ALTER TABLE regions
ADD ( CONSTRAINT reg_id_pk
PRIMARY KEY (region_id)
) ;

REM ********************************************************
REM Create the COUNTRIES table to hold country information for customers
REM and company locations.
REM OE.CUSTOMERS table and HR.LOCATIONS have a foreign key to this table.

Prompt ****** Creating COUNTRIES table ….

CREATE TABLE countries
( country_id CHAR(2)
CONSTRAINT country_id_nn NOT NULL
, country_name VARCHAR2(40)
, region_id NUMBER
, CONSTRAINT country_c_id_pk
PRIMARY KEY (country_id)
)
ORGANIZATION INDEX;

ALTER TABLE countries
ADD ( CONSTRAINT countr_reg_fk
FOREIGN KEY (region_id)
REFERENCES regions(region_id)
) ;

REM ********************************************************
REM Create the LOCATIONS table to hold address information for company departments.
REM HR.DEPARTMENTS has a foreign key to this table.

Prompt ****** Creating LOCATIONS table ….

CREATE TABLE locations
( location_id NUMBER(4)
, street_address VARCHAR2(40)
, postal_code VARCHAR2(12)
, city VARCHAR2(30)
CONSTRAINT loc_city_nn NOT NULL
, state_province VARCHAR2(25)
, country_id CHAR(2)
) ;

CREATE UNIQUE INDEX loc_id_pk
ON locations (location_id) ;

ALTER TABLE locations
ADD ( CONSTRAINT loc_id_pk
PRIMARY KEY (location_id)
, CONSTRAINT loc_c_id_fk
FOREIGN KEY (country_id)
REFERENCES countries(country_id)
) ;

Rem Useful for any subsequent addition of rows to locations table
Rem Starts with 3300

CREATE SEQUENCE locations_seq
START WITH 3300
INCREMENT BY 100
MAXVALUE 9900
NOCACHE
NOCYCLE;

REM **************************************************
REM Create the DEPARTMENTS table to hold company department information.
REM HR.EMPLOYEES and HR.JOB_HISTORY have a foreign key to this table.

Prompt ****** Creating DEPARTMENTS table ….

CREATE TABLE departments
( department_id NUMBER(4)
, department_name VARCHAR2(30)
CONSTRAINT dept_name_nn NOT NULL
, manager_id NUMBER(6)
, location_id NUMBER(4)
) ;

CREATE UNIQUE INDEX dept_id_pk
ON departments (department_id) ;

ALTER TABLE departments
ADD ( CONSTRAINT dept_id_pk
PRIMARY KEY (department_id)
, CONSTRAINT dept_loc_fk
FOREIGN KEY (location_id)
REFERENCES locations (location_id)
) ;

Rem Useful for any subsequent addition of rows to departments table
Rem Starts with 280

CREATE SEQUENCE departments_seq
START WITH 280
INCREMENT BY 10
MAXVALUE 9990
NOCACHE
NOCYCLE;

REM *********************************************
REM Create the JOBS table to hold the different names of job roles within the company.
REM HR.EMPLOYEES has a foreign key to this table.

Prompt ****** Creating JOBS table ….

CREATE TABLE jobs
( job_id VARCHAR2(10)
, job_title VARCHAR2(35)
CONSTRAINT job_title_nn NOT NULL
, min_salary NUMBER(6)
, max_salary NUMBER(6)
) ;

CREATE UNIQUE INDEX job_id_pk
ON jobs (job_id) ;

ALTER TABLE jobs
ADD ( CONSTRAINT job_id_pk
PRIMARY KEY(job_id)
) ;

REM ***********************************************
REM Create the EMPLOYEES table to hold the employee personnel
REM information for the company.
REM HR.EMPLOYEES has a self referencing foreign key to this table.

Prompt ****** Creating EMPLOYEES table ….

CREATE TABLE employees
( employee_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name VARCHAR2(25)
CONSTRAINT emp_last_name_nn NOT NULL
, email VARCHAR2(25)
CONSTRAINT emp_email_nn NOT NULL
, phone_number VARCHAR2(20)
, hire_date DATE
CONSTRAINT emp_hire_date_nn NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT emp_job_nn NOT NULL
, salary NUMBER(8,2)
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6)
, department_id NUMBER(4)
, CONSTRAINT emp_salary_min
CHECK (salary > 0)
, CONSTRAINT emp_email_uk
UNIQUE (email)
) ;

CREATE UNIQUE INDEX emp_emp_id_pk
ON employees (employee_id) ;

ALTER TABLE employees
ADD ( CONSTRAINT emp_emp_id_pk
PRIMARY KEY (employee_id)
, CONSTRAINT emp_dept_fk
FOREIGN KEY (department_id)
REFERENCES departments
, CONSTRAINT emp_job_fk
FOREIGN KEY (job_id)
REFERENCES jobs (job_id)
, CONSTRAINT emp_manager_fk
FOREIGN KEY (manager_id)
REFERENCES employees
) ;

ALTER TABLE departments
ADD ( CONSTRAINT dept_mgr_fk
FOREIGN KEY (manager_id)
REFERENCES employees (employee_id)
) ;

Rem Useful for any subsequent addition of rows to employees table
Rem Starts with 207

CREATE SEQUENCE employees_seq
START WITH 207
INCREMENT BY 1
NOCACHE
NOCYCLE;

REM ***********************************************
REM Create the JOB_HISTORY table to hold the history of jobs that
REM employees have held in the past.
REM HR.JOBS, HR_DEPARTMENTS, and HR.EMPLOYEES have a foreign key to this table.

Prompt ****** Creating JOB_HISTORY table ….

CREATE TABLE job_history
( employee_id NUMBER(6)
CONSTRAINT jhist_employee_nn NOT NULL
, start_date DATE
CONSTRAINT jhist_start_date_nn NOT NULL
, end_date DATE
CONSTRAINT jhist_end_date_nn NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT jhist_job_nn NOT NULL
, department_id NUMBER(4)
, CONSTRAINT jhist_date_interval
CHECK (end_date > start_date)
) ;

CREATE UNIQUE INDEX jhist_emp_id_st_date_pk
ON job_history (employee_id, start_date) ;

ALTER TABLE job_history
ADD ( CONSTRAINT jhist_emp_id_st_date_pk
PRIMARY KEY (employee_id, start_date)
, CONSTRAINT jhist_job_fk
FOREIGN KEY (job_id)
REFERENCES jobs
, CONSTRAINT jhist_emp_fk
FOREIGN KEY (employee_id)
REFERENCES employees
, CONSTRAINT jhist_dept_fk
FOREIGN KEY (department_id)
REFERENCES departments
) ;

Compatibilité import / export 9i / 10g

Administration Oracle, Divers 1 Commentaire »

En résumé
version import = version base cible
version export V9 compatible avec base 10 et 11G

matrice de compatibilté export / import

+———+————————————————————+ |EXPORT | IMPORT into: | | from +——-+——–+——–+——–+——–+——–+——-+ | \/ | 8.1.x | 8.1.7 | 9.0.1 | 9.2.0 | 10.1.0 | 10.2.0 | 11.1.0| +———+——-+——–+——–+——–+——–+——–+——-+ |5.x 1) 2)|EXP5x | EXP5x | EXP5x | EXP5x | EXP5x | EXP5x | EXP5x | |6.x 2)|EXP6x | EXP6x | EXP6x | EXP6x | EXP6x | EXP6x | EXP6x | |7.x 3)|EXP7x | EXP7x | EXP7x | EXP7x | EXP7x | EXP7x | EXP7x | +———+——-+——–+——–+——–+——–+——–+——-+ |8.0.3 |EXP803 | EXP803 | EXP803 | EXP803 | EXP803 | EXP803 | EXP803| |8.0.4 |EXP804 | EXP804 | EXP804 | EXP804 | EXP804 | EXP804 | EXP804| |8.0.5 |EXP805 | EXP805 | EXP805 | EXP805 | EXP805 | EXP805 | EXP805| |8.0.6 |EXP806 | EXP806 | EXP806 | EXP806 | EXP806 | EXP806 | EXP806| +———+——-+——–+——–+——–+——–+——–+——-+ |8.1.5 |EXP815 | EXP815 | EXP815 | EXP815 | EXP815 | EXP815 | EXP815| |8.1.6 |EXP81x | EXP816 | EXP816 | EXP816 | EXP816 | EXP816 | EXP816| |8.1.7 |EXP81x | EXP817 | EXP817 | EXP817 | EXP817 | EXP817 | EXP817| +———+——-+——–+——–+——–+——–+——–+——-+ |9.0.1 |EXP81x | EXP817 | EXP901 | EXP901 | EXP901 | EXP901 | EXP901| |9.2.0 | N/S | EXP817 | EXP901 | EXP920 | EXP920 | EXP920 | EXP920| +———+——-+——–+——–+——–+——–+——–+——-+ |10.1.0 4)| N/S | EXP817 | EXP901 | EXP920 | 4) | |10.2.0 4)| Not Supported | EXP920 | 4) | +———+——-+——–+——–+——–+——–+——–+——-+ |11.1.0 4)| Not Supported | EXP920 | 4) | +———+——-+——–+——–+——–+——–+——–+——-+

Quelques tests

——————————————————-

srv1:/oracle/9iDB/bin >exp scott/tiger@b9 file=/tmp/exp9b9.dmp

Export: Release 9.2.0.6.0 - Production on Tue Mar 11 16:52:39 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Export terminated successfully without warnings.

——————————————————-

srv1:/oracle/10GDB/bin > exp scott/tiger@b10 file=/tmp/exp10b10.dmp

Export: Release 10.2.0.3.0 - Production on Tue Mar 11 17:05:44 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64
bit Production
With the Partitioning, OLAP and Data Mining options

Export terminated successfully without warnings.

——————————————————-

srv1:/oracle/10GDB/bin > exp scott/tiger@b9 file=/tmp/exp10b9.dmp

Export: Release 10.2.0.3.0 - Production on Tue Mar 11 16:49:35 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 41:
PLS-00302: component ‘SET_NO_OUTLINES’ must be declared
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored
EXP-00000: Export terminated unsuccessfully

——————————————————-

srv1:/oracle/10GDB/bin >imp scott/tiger@b9 file=/tmp/exp9b9.dmp

Import: Release 10.2.0.3.0 - Production on Tue Mar 11 16:56:51 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

IMP-00058: ORACLE error 6550 encountered
ORA-06550: line 1, column 33:
PLS-00302: component ‘SET_NO_OUTLINES’ must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
IMP-00000: Import terminated unsuccessfully

——————————————————-

srv-1:/oracle/9iDB/bin >exp scott/tiger@b10 file=/tmp/exp9b10.dmp

Export: Release 9.2.0.6.0 - Production on Tue Mar 11 17:00:56 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64
bit Production
With the Partitioning, OLAP and Data Mining options

EXP-00008: ORACLE error 37002 encountered
ORA-37002: Oracle OLAP failed to initialize. Please contact Oracle OLAP tech
nical support.
ORA-33262: Analytic workspace EXPRESS does not exist.
ORA-06512: at “SYS.DBMS_AW”, line 93
ORA-06512: at “SYS.DBMS_AW”, line 122
ORA-06512: at “SYS.DBMS_AW_EXP”, line 473
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_AW_EXP.schema
_info_exp
. exporting statistics
Export terminated successfully with warnings.

——————————————————-

srv1:/oracle/9iDB/bin >imp scott/tiger@b9 file=/tmp/exp10b10.dmp

Import: Release 9.2.0.6.0 - Production on Tue Mar 11 17:13:43 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Producti
on
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining op
tions
JServer Release 9.2.0.6.0 - Production

IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully

Paramètres d’instance et de session

Administration Oracle, Divers pas de Commentaire »

Les paramètres d’initialisation

Les ressources utilsées par une base, essentiellement ressources mémoires, buffers, processus et fichiers sont paramétrables, et permettent d’ajuster son fonctionnement, voire de faire de l’optimisation (tuning).
Certains paramètres ont des valeurs libres (dans un domaine de valeurs, bien sûr), d’autres plus restrictifs sont dépendants du systèmes d’exploitation, ou dérivés d’autres paramètres.

Lors de son démarrage, une instance Oracle lit ses paramètres dans un fichier, dit de démarrage ou d’initialisation.
La totalité des paramètres d’initialisation est décrite dans la documentation Oracle Oracle 10gR2 reference (attention PDF de 30MO!).

note : augmenter les ressources allouées ? la base en augmentant les valeurs de certains

paramètres est une bonne idée…jusqu’? un certain point. La mémoire ne peut pas,on s’en doute, être entièrement dévolue ? la SGA…
Quelle que soit la base il est obligatoire de spécifier explicitement au strict minimum 2 paramètres :

‘DB_NAME’ : le nom de la base de données
‘CONTROL_FILES’ : la liste des fichiers de contrôle utilisés

Les autres paramètres les plus fréquemment utilisés sont les suivants (S:statique, M:modifiable, D: dérivé d’un autre paramètre)

COMPATIBLE S Compatibilité arrière avec les versions précédentes (>= 9.2.0)
DB_DOMAIN S Nom de domaine, qui associé au DB_NAME donne un nom de base uniqueNom de domaine, qui associé au DB_NAME donne un nom de base unique
DB_RECOVERY_FILE_DEST M Répertoire de destination de la zone de FLASH recovery
LOG_ARCHIVE_DEST_n M Répertoire des fichiers REDO LOGS archivés
NLS_LANGUAGE D Langue utilisée (fonction de NLS_LANG dans l’environnement Unix ou windows)
NLS_TERRITORY M le territoire (France par ex). Impacte les dates, et la monnaie
OPEN_CURSORS M nb max de cursor ouvrable par une application
PGA_AGGREGATE_TARGET M si 0, dimensionnement auto de la PGA, pour les proccess utilisateur server
PROCESSES S nb max de process connectés ? Oracle (background et user)
SESSIONS D = 1,1 * PROCESSES
SGA_TARGET D si 0 auto dimensionnement de la SGA
SHARED_SERVER M 0 si serveur dédié, n = nb de serveurs partagés lancés au startup instance
SP_FILE S emplacement du SP File
UNDO_MANAGEMENT S Manual | Auto, par defaut Manual indique qu’on utilise des Rollback segments

Paramètres statiques et dynamiques

Certains paramètres peuvent être modifiés en temps réel, ils sont dits dynamiques, d’autres seront pris en compte au prochain redémarrage de la base, ils sont dits statiques.
Les paramètres dynamiques peuvent être modifiés pour la durée de la session,, pour la durée de l’instance ou de manière permanente avec la commande ‘ALTER SESSION’ ou ‘ALTER SYSTEM’.
Voici les syntaxes :

ALTER SESSION SET =
ALTER SYSTEM SET = [SCOPE = MEMORY | SPFILE | BOTH] [DEFERRED] [COMMENT = '']

exemples

SQL> ALTER SESSION SET NLS_LANGUAGE=’FRENCH’;


Informations sur les paramètres

On peut utiliser les commandes SQL*Plus SHOW :

SQL> SHOW PARAMETER

active_instance_count integer  
aq_tm_processes integer 0
archive_lag_target integer 0
asm_diskgroups string  
asm_diskstring string  
asm_power_limit integer 1
audit_file_dest string F:ORACLEPRODUCT10.2.0ADMIN ORCLADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE
background_core_dump string partial
background_dump_dest string F:ORACLEPRODUCT10.2.0ADMIN ORCLBDUMP
backup_tape_io_slaves… boolean… FALSE…

ou en filtrant avec un mot clé :

SQL> SHOW PARAMETER AUDIT

audit_file_dest string F:ORACLEPRODUCT10.2.0ADMIN ORCLADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE

ou utiliser des vues virtuelles du dictionnaire :

V$PARAMETER : les paramètres en cours d’utilisation (de la session donc…)
V$PARAMETER2 : les mêmes, mais mieux présentés…
V$SPPARAMETER : les paramètres du SPFILE (valable au (re)démarrage de l’instance)
V$SYSTEM_PARAMETER : paramètres de l’instance en cours en mémoire

SQL> select a.name, a.value courante, sp.value sp_file, mem.value memoire
from v$parameter a, v$spparameter sp, v$system_parameter mem
where a.name=sp.name
and mem.name=a.name
and a.name = ‘open_cursors’

NAME COURANTE SP_FILE MEMOIRE
open_cursors 1100 1100 1100

SQL> alter system set open_cursors=1200;
– on modifie la valeur courante de l’instance, ce qui a un impact sur la session
– mais ne sera plus valable au redemarrage de la base (spfile non modifié…)

NAME COURANTE SP_FILE MEMOIRE
open_cursors 1200 1100 1200

SQL> — infos sur parametres, par defaut ou non ?
SQL> SELECT NAME , VALUE, ISDEFAULT, DESCRIPTION
FROM V$PARAMETER

NAMEtracefile_identifier VALUE ISDEFAULTTRUE DESCRIPTIONtrace file custom identifier
lock_name_space   TRUE lock name space used for generating lock names for standby/clone database
processes 150 FALSE user processes
sessions 170 TRUE user and system sessions
timed_statistics TRUE TRUE maintain internal timing statistics
timed_os_statistics 0 TRUE internal os statistic gathering interval in seconds

SQL> — combine de parametres spécifiés dans le SPFILE ?
SQL> SELECT count(*)
FROM V$SPPARAMETER
WHERE ISSPECIFIED IS TRUE
COUNT(*)
———-
23

Récupération rapide d’une table …dans la poubelle (RECYCLE BIN)

Administration Oracle, Divers 1 Commentaire »

Les suppressions de tables, ne sont plus désormais physiques et définitives, mais mettent simplement l’objet ? la corbeille (RECYCLE BIN). Tant que cette dernière n’est pas vidée, il est possible de récupérer l’objet supprimé.

skull Attention ! pour pouvoir être utilisée la poubelle …doit être active. On peut vérifier le paramètre d’initilalisation : SQL> SHOW PARAMETER RECYCLE , qui doit être ? ‘ON’.

skullskull Attention encore plus !! les commandes de gestion de la poubelle ne sont pas des commandes SQL , mais SQL*Plus donc même si vous êtes en 10g mais avec un client autre que SQL+ 10g , les commandes ne seront pas reconnues. Vous aurez des messages du genre,
- sous i*SQLPlus : Command beginning “purge tabl…” is not available in iSQL*Plus
- sous SQLPlus : SP2-0734: unknown command beginning “purge scot…” - rest of line ignored.
Des infos sur le contenu de la poubelle dans le dictionnaire…

Les informations concernant la corbeille peuvent être consultées dans le dictionnaire :

SQL> SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
————- ————— ———– ———
T1 BIN$EkKm2r93vZfgQKjAhw1XeA==$0 TABLE 2006-04-25:15:51:52

ou via les tables USER_RECYCLEBIN et DBA_RECYCLEBIN.

exemple

SQL> select original_name nom, object_name “nom interne”,
operation, createtime “date création “,
droptime “date suppression “, can_undrop “récupérable ? “, can_purge “purgeable ?”
FROM user_recyclebinNOM NOM INTERNE OPERATION date création date suppression récupérable? purgeable?
— —————————— ——— ——————- ——————- ———— ———-
T1 BIN$Ejia7J3wZRXgQKjAhw0prg==$0 DROP 2006-04-25:15:10:17 2006-04-25:15:10:22 YES YES
T2 BIN$Ejia7J3vZRXgQKjAhw0prg==$0 DROP 2006-04-25:15:09:30 2006-04-25:15:09:35 YES YES

note : les colonnes CREATETIME et DROPTIME sont des…VARCHAR (?)

exemples de récupération

SQL> FLASHBACK TABLE T2 TO BEFORE DROP
SQL> FLASHBACK TABLE “BIN$Ejia7J3vZRXgQKjAhw0prg==$0″ TO BEFORE DROP

note : on peut donc également utiliser le nom interne ET CE QUELQUESOIT LA COMMANDE SQL !
Il est conseillé de l’encadrer par des guillemets pour masquer les caractères spéciaux

Une table T1 peut avoir été supprimée plusieurs fois, et apparaitre plusieurs fois dans la corbeille.
Pour éviter toute ambiguité, il faut vérifier le contenu de la poubelle avant récupération
et utiliser le nom interne le cas échéant plutot que le nom logique.

exemple

SQL> create table t1 (n integer);
SQL> drop table t1;
SQL> create table t1(new_n integer);
SQL> drop table t1;
et dans la corbeille on a :
NOM nom interne OPERATION date création date suppression
T1 BIN$EkKm2r92vZfgQKjAhw1XeA==$0 DROP 2006-04-25:15:51:14 2006-04-25:15:51:28
T1 BIN$EkKm2r93vZfgQKjAhw1XeA==$0 DROP 2006-04-25:15:51:44 2006-04-25:15:51:52SQL> desc “BIN$EkKm2r93vZfgQKjAhw1XeA==$0″
Name Type
NEW_N NUMBER(38)

SQL> –c’est bien celle que je veux recuperer…dont acte
SQL> FLASHBACK TABLE “BIN$EkKm2r93vZfgQKjAhw1XeA==$0″ TO BEFORE DROP;

Suppression permanente

On peut supprimer une table de la corbeille avec la commande ‘PURGE’ :

SQL> PURGE TABLE T3

ou indirectement en utilisant des commandes affectant le trablespace utilisateur :

SQL> PURGE TABLESPACE users; — purge tous les objets du tablespace
SQL> PURGE TABLESPACE users USER scott; –purge tous les objets de SCOTT du tablespace USERS

ou vider en une fois la poubelle utilisateur ou la poubelle générale :

SQL> PURGE recyclebin;
SQL> PURGE dba_recyclebin;

Eviter la poubelle

On peut également supprimer physiquement et directement une table, en évitant de la conserver dans la corbeille donc, avec l’option ‘PURGE’ de la commande ‘DROP TABLE’

SQL> DROP TABLE T5 PURGE

Ou invalider l’utilisation de la poubelle de manière transversale,
grace au paramètre d’initialisation ‘RECYCLEBIN’

SQL> show parameter recyclebin
NAME TYPE VALUE
recyclebin string ON

SQL> ALTER SYSTEM SET recyclebin = OFF;
System Altered

SQL> show parameter recyclebin
NAME TYPE VALUE
recyclebin string OFF

lister et arreter des JOBs (travaux) Oracle

Administration Oracle, Divers pas de Commentaire »

SQL> connect system/xxxx
SQL> select job, schema_user, next_date, what
from dba_jobs;

JOB SCHEMA_USER   NEXT_DATE
**** ************ **********
4001 FLOWS_030000 07/01/2008
4002 FLOWS_030000 07/01/2008
215  SYSTEM       07/01/2008

on récupère le no de Job dans la colonne ‘JOB’ de la table ‘DBA_JOBS’ et on supprime le Job choisi, grace au package PL/SQL fourni RDBMS_JOB

SQL> exec dbms_job.remove(215);
PL/SQL procedure successfully completed.

remarque : avec la console GRID 10g, qui surveille une base 9i (avec l’agent qui va bien) les jobs n’apparaissent pas dans la console, d’ou l’utilité de cette procédure manuelle.

mode archivage (archivelog mode) et Oracle 10g

Administration Oracle, Divers 1 Commentaire »

Paramètres intéressants

LOG_ARCHIVE_START
est obsolète en 10g …et donc inutile (si précisé, provoque un Warning au démarrage de la base)
DB_RECOVERY_FILE_DEST
spécifie la zone de récupération (recovery area) et par défaut la zone d’archivage
LOG_ARCHIVE_DEST_n
destination de l’archivage des Redolog files (n allant de 1 ? 10, dans le sens croissant de la paranoïa)

La syntaxe est la suivante : LOG_ARCHIVE_DEST_n=”LOCATION=repertoire_archivage”

Vérification du mode actuel

SQL> archive log list
mode Database log mode Archive
Archivage automatique Active
Destination de l’archive USE_DB_RECOVERY_FILE_DEST
Sequence de journal en ligne la plus ancienne 65
Sequence de journal suivante a archiver 67
Sequence de journal courante 67
SQL>

on voit que l’ARCHIVE_DEST n’est pas positionné ici et que l’on utilise la recovery area par défaut, ce que l’on vérifie :

NAMETYPE VALUE
——– ——- ———-
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_2 string

SQL> show parameters db_recovery

NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /oracle/recovery_area
db_recovery_file_dest_size big integer 2G

Passage en mode archivage

on peut utiliser le script suivant (qui ne change guère des précédentes versions…)

#archive_mode.sql

set instance MA_BASE
connect sys/password as sysdba
shutdown
startup mount;
# Enable database ARCHIVELOG mode
alter database archivelog;
# Shut down and restart the database instance.
shutdown immediate
startup

Les process (Unix)
Ils sont par défaut au nombre de 2, lorsque l’archivage est activé, et s’appellent ora_arcN_ORACLE_SID :

$> ps -ef|grep arc

oracle? ? ? 6834? ? ? ? 1? 0 Aug13 ?? ? ? ? ? ? ? 00:00:00 ora_arc0_DBADE
oracle? ? ? 6836? ? ? ? 1? 0 Aug13 ?? ? ? ? ? ? ? 00:00:00 ora_arc1_DBADE

Migration Oracle 9.2.0.5 en 9.2.0.6 + Patch UTL_FILE

Administration Oracle, Divers 2 Commentaires »

1] Préparation

Arret de toutes les bases
Arret listener (ex /bin/su - oracle “-c lsnrctl stop listener”)
Bien penser ? arréter l’agent 9i : agentctl stop
Pareil pour apache /oracle/9iDB/apache/apache/ ./apachectl stop
Et eventuellement le deuxième listener si y en a un!!!!
Back up eventuel

2] Migration du Software Oracle 9iDB 9.2.0.5 en 9.2.0.6

$ ORACLE_HOME=/oracle/9iDB
$ ORACLE_SID=DEVUN
$ export ORACLE_HOME ORACLE_SID

Aller dans le repertoire ou il y a le patch

cd Repertoire_patchset/Disk1
Lancer l’installer : ./runInstaller

A l’ecran de bienvenue, cliquer sur suivant.
Selectionner l’emplacement du products.xml qui est dans repertoire_patchset/Disk1/stage/products.xml
Verifier le oracle_home

A l’ecran de résumé cliquer sur install.
Selectionner le patch que vous souhaitez installer.
Quand on vous le demande, exécuter le script $ORACLE_HOME/root.sh sous root.
Cliquer exit pour sortit.

3] Passage du patch UTL_FILE

Dézipper le quelque part.
Pour l’appliquer :
dans le fichier : $ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml
changer le 610 par 212
cd /xxxxxxxxx/patches/4081980/
Mettre le bon PATH.
ex PATH=../OPatch:$PATH

Arret de toutes les bases
opatch apply

4] Redémarrage base une par une

1) Startup migrate

Prés requis : java_pool_size >= 150Mo
shared_pool_size >= 150Mo
100Mo d’espace libre dans le tablespace SYSTEM

sqlplus /nolog
SQL> connect / as sysdba
SQL> startup migrate
SQL> spool patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> spool off

Remarque : il se peut que vous ayez une erreur lié au java a peu prés au milieu du script
le script continue et se termine correctement.

Restart the database:

SQL> shutdown immediate
SQL> startup
SQL> @?/rdbms/admin/utlrp.sql

2) Suite du pb utl_file

Ouverture des bases et sur chacune d’elles passer :
SQL>@?/rdbms/admin/utlfile.sql
SQL>@?/rdbms/admin/prvtfile.plb

3) Redémarrer le listener : lsnrctl start
et l’agent 9i, Apache etc……

Les vues matérialisées

Administration Oracle 12 Commentaires »

Une vue matérialisée (VM) est un moyen simple de créer une vue physique d’une table. Comme son nom l’indique et ? la différence d’une vue standard, les données sont dupliquées. On l’ utilise ? des fins d’optimisation de performance, lorsque le select associé est particulièrement complexe ou lourd, ou pour faire des réplications de table.
La ‘fraicheur’ des données de la VM dépend des options choisies. Le décalage entre les données de la table maître et la VM peut être nul (raffraichissement synchrone) ou d’une durée planifiée : heure, jour,etc.
Suivant le contexte il existe différents types de VMs possibles : sur clé primaire, rowid, et plus ou moins complexes : avec fonctions aggrégées, sous requêtes, jointures, etc.

Bien que séduisants ces objets devront être utilisés avec parcimonie et ? bon escient. Il pourra être utile de faire des tests de refresh (complete et fast), de bien peser les volumétries, doser les fréquence de refresh en fonction des besoins réels, etc.

si on n’a pas l’option “réplication avancée” d’Oracle (Oracle Advanced replication option) les VM ne pourront être utilisées qu’en lecture seule


Syntaxe minimale

SQL > CREATE MATERIALIZED VIEW MV1
AS SELECT * FROM scott.emp

créé une vue matérialisée simple, copie conforme de EMP de SCOTT, avec par défaut un raffraîchissement ? la demande sur clé primaire.

par défaut le contenu de la VM est initialisé, la VM est remplie, lors de sa création. Il est possible de crééer une VM vide avec l’otion BUILD DEFERRED (le défaut est BUILD IMMEDIATE)

le CREATE créé aussi de manière transparente une table de même nom que la vue matérialisée et une clé primaire
Infos dans le référentiel / dictionnaire de données

Il existe essentiellement 2 tables sur les vues matérialisées : USER_MVIEWS et USER_MVIEW_LOGS dont voici les descriptions :

USER_MVIEWS
OWNER, MVIEW_NAME, CONTAINER_NAME, QUERY, QUERY_LEN , UPDATABLE, UPDATE_LOG, MASTER_ROLLBACK_SEG , MASTER_LINK, REWRITE_ENABLED , REWRITE_CAPABILITY , REFRESH_MODE, REFRESH_METHOD, BUILD_MODE , FAST_REFRESHABLE , LAST_REFRESH_TYPE, LAST_REFRESH_DATE, STALENESS , AFTER_FAST_REFRESH, UNKNOWN_PREBUILT, UNKNOWN_PLSQL_FUNC, UNKNOWN_EXTERNAL_TABLE, UNKNOWN_CONSIDER_FRESH, UNKNOWN_IMPORT, COMPILE_STATE, USE_NO_INDEX

USER_MVIEW_LOGS
:
LOG_OWNER , MASTER , LOG_TABLE , LOG_TRIGGER, ROWIDS , PRIMARY_KEY , OBJECT_ID , FILTER_COLUMNS, SEQUENCE, INCLUDE_NEW_VALUES

Ainsi le create materialized view MV1 précédent nous renverrait (essentiellement) les infos suivantes dans le dictionnaire

select MVIEW_NAME, QUERY ,UPDATABLE, REFRESH_MODE, REFRESH_METHOD, BUILD_MODE, FAST_REFRESHABLE
from user_mviews

MVIEW_NAME
———-
QUERY
—–
UPDATABLE REFRESH_MODE REFRESH_METHOD BUILD_MODE FAST_REFRESHABLE
——— ———— ————– ———- —————-
MV1
SELECT “EMP”.”EMPNO” “EMPNO”,”EMP”.”ENAME” “ENAME”,”EMP”.”JOB” “JOB”,”EMP”.”MGR”…
N DEMAND COMPLETE IMMEDIATE DML

On vérifie également les objets créés implicitement :

SQL> select object_name, object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
———— —————-
MV1 TABLE
MV1 MATERIALIZED VIEW
PK_EMPNO INDEX

le mot SNAPSHOT fréquemment utilisé est en fait en voie d’obsolescence et synonym ede ‘MVIEW’ dans le dictionnaire

Les droits et contraintes particulières

En général (sauf cas d’école) la vue matérialisée sera dans un schéma, voire dans le cas de réplication sur une base de données différente de la table maître. Dans ce cas la gestion des droits sera un peu plus délicate.
opération droit sur son propre schéma +  droit sur un autre schéma
CREATE MATERIALIZED VIEW + CREATE TABLE
REFRESH FAST SELECT ON la table de LOG concernée+  SELECT ON ANY TABLE ;-(
clause QUERY REWRITE QUERY REWRITE +  GLOBAL QUERY REWRITE
clause ON COMMIT REFRESH priv obj + ON COMMIT REFRESH priv system ON COMMIT REFRESH
clause PREBUILT TABLE priv SELECT + WITH GRANT OPTION sur table container

pour faire un faire un FAST REFRESH il faut un droit direct sur la table de LOG, un ROLE y compris DBA ou SYSDBA n’est pas suffisant. voir le fast refresh plus loin pour plus de détails

Pour une création simple il faut en résumé :

  • le droit CREATE MATERIALIZED VIEW
  • le droit CREATE TABLE (car une MV c’est une MV …. +  une TABLE)
  • le GRANT SELECT sur les objets accédés par la vue

Note :

  • on peut créer une vue matérialisée sur une vue
  • on peut créer une vue matérialisée sur une vue matérialisée
  • par défaut la table sous jacente doit avoir une clé primaire…

Les différents types de vues matérialisées

VM sur clé primaire
c’est le défaut. A utiliser sauf exception (voir paragraphe suivant)
VM sur rowid
SQL> CREATE MATERIALIZED VIEW MV2
REFRESH with ROWID
AS SELECT * FROM scott.emp

Utile lorsque la vue ne contient pas de (ou pas toutes les colonnes de la) clé primaire

contraintes (assez fortes !) :
* ne permet pas le fast refresh si il n’a pas eu un complet avant
* interdit si SELECT avec distinct, group by, connect by, fonctions d’aggregats, opérateur ensemblistes, sous requêtes

rem : on peut vérifier si le rafraichissement des VMs est actifs dans USER_JOBS

SQL> select what from user_jobs;

WHAT
——————————————————————————–
dbms_refresh.refresh(’”DDELEGLI”.”REF_GROUP_2″‘);
dbms_refresh.refresh(’”DDELEGLI”.”REF_GROUP_1″‘);


Modalités de rafraîchissement

Méthodes de refresh

il existe 3 méthodes : FAST, COMPLETE ou FORCE

  • FAST REFRESH ( incrémental) c’est la méthode la + efficace, elle utilise des journaux spécifiques traçant les modification de la table maître : les LOGs.( Voir l’instruction CREATE MATERIALIZED VIEW LOG…)
  • COMPLETE effectue le refresh complet en executant le SELECT de définition de la MV
  • FORCE effectue un FAST si possible, sinon un COMPLET. C’est la méthode par défaut.

rappel : le fait que la VM soit initialisée avec des valeurs ou rafraichie ou non n’est pas du tout lié à la méthode (y compris FORCE!) cela dépend respectivement de l’option ‘build’ pour l’initialisation et du mode, synchrone ou non pour le raffraichissement…
méthode complete
c’est la méthode par défaut. Toutes les lignes de la table maître sont (re) synchronisées. Ca peut donc couter du temps mais ca fait les économies de place de la table log….

SQL> create materialized view mv2 refresh complete
as select * from scott.emp;
vue matérialisée créée


méthode refresh partiel ou rapide ou ‘fast’

Si on essaie de faire la meme VM avec un refresh FAST…on obtient une erreur :

SQL> create materialized view mv2
2 refresh FAST
3 as select * from scott.emp;
as select * from scott.emp
*
ERREUR a la ligne 3 : ORA-23413: table “SCOTT”.”EMP” does not have a materialized view log

C’est très clair (on l’a dit + haut) avec cette méthode ‘incrémentale’ il faut tracer les mises a jour faites sur la table maitre dans des fichiers LOG…encore faut il qu’ils existent !
On le fait (faute de mieux ou de droits adéquats ?) dans le compte maitre (le compte de SCOTT) et après on eut faire des FAST REFRESH…

SQL> connect scott/tiger
SQL> create materialized view log on emp;
Journal de vue materialisee cree.

Les logs, comme on peut le voir dans le référentiel sur les vues matérialisées, prennent le nom de la table maître préfixé par ‘MLOG$_’. Donc ici MLOG$_EMP.
on retente le coup et on obtient encore une erreur ORA-1031 “insufficient privilege” car on n’a pas les accèsm nécessaires, non pas sur SCOTT.EMP mais sur SCOTT.MLOG$EMP.
On pourra donner un SELECT ANY TABLE (c’est la solution parfois proposée dans le forums) ou plus finement un
SQL> GRANT SELECT ON SCOTT.MLOG$_EMP TO le_user_propriétaire_de_la_VM

Modes de refresh

Globalement synchrone (répercuté en temps réel) ou asynchrone (mis dans une queue et traité en différé) , mais il existe en fait 3 modes, 3 ‘fréquences’ de refresh :

  • synchrone, sur commit : clause ON COMMIT
    par définition ne nécessite pas de commande REFRESH c’est la mise à jour COMMITée de la table maître qui déclenche automatiquement le REFERESH.
  • asynchrone, à la demande : clause ON DEMAND. C’est le mode par défaut.
    On utilise la procédure standard DBMS_MVIEW.refresh qui est publique (DBMS_MVIEW est un synonyme public sur SYS.DBMS_SNAPSHOT et il existe un GRANT EXECUTE TO PUBLIC ON SYS.DBMS_SNAPSHOT ).
    Ne nécessite donc pas de droit particulier pour être exécuté (pour le rafraichissement d’une vue matérialisée dans un autre schéma voir les exemples ci-après).
    exemple :

SQL> execute DBMS_MVIEW.REFRESH(’MA_VM’);

  • asynchrone, cyclique : clauses START WITH et NEXT, qui précise une date de début et une période de temps intermédiaire

on peut changer de fréquence de refresh avec la commande ALTER :

SQL> ALTER MATERIALIZED VIEW mv1
refresh complete
start with sysdate next sysdate + n

cette commande sans la clause NEXT, ne peut pas être dévoyée pour provoquer un refresh immediat d’une vue non à jour

Infos complémentaires sur la planification des refresh

L’automatisation du raffraichissement des vues matérialisées par Oracle se fait grace à des JOBS Oracle. On peut donc avoir des infos, notamment sur les refresh à venir, dans les vues du dictionnaires associées au JOBs.
En pratique il n’y a pas de colonne NEXT REFRESH dans les vues concernant les vues matérialisées, ce manque est donc comblé.

AInsi la requête suivante nous donne des infos sur le LAST REFRESH, le NEXT REFRESH, et comment a été défini l’intervalle entre les 2 dans la vue matérialisée.

SQL> SELECT job, SCHEMA_USER,
TO_CHAR(last_date, ‘DD/MM/YYY HH:MI’) “Dernier refresh”,
TO_CHAR(next_date, ‘DD/MM/YYY HH:MI’) “Prochain refresh”,
interval “Intervalle”,
what “Refresh concerné”
FROM dba_jobs
WHERE what LIKE ‘%refresh%’
AND SCHEMA_USER=’SCOTT’

JOB SCHEMA_USER Dernier refresh Prochain refres Intervalle
Refresh concerné
229 SCOTT 11/09/008 05:00 12/09/008 05:00 trunc(sysdate + 1) + 17/24 dbms_refresh.refresh(’”SCOTT”.”MV1″‘);
230 SCOTT 12/09/008 08:00 14/09/008 12:00 trunc(sysdate + 1) + 1 dbms_refresh.refresh(’”SCOTT”.”MV2″‘);


Des exemples variés

  • rafraichissement synchrone sur commit de la table maître (on commit refresh)

Des que les modifs de la table maitre sont commitées elle sont automatiquement synchronisées (répercutées) dans le VM.

SQL> create materialized view mv2 refresh complete
as select * from scott.emp;
vue matérialisée créée
SQL> insert into scott.emp(empno, ename) values (1111,’DD’);
SQL> select ename from mv2 where empno=1111;
aucune ligne selectionnee
SQL> commit;
SQL> select ename from mv2 where empno=1111;
ENAME
—–
DD

  • rafraichissement asynchrone à la demande (on demand)

SQL> execute DBMS_MVIEW.REFRESH(’MA_VM’);
SQL> — si la vue appartient à un autre schéma

SQL> connect system/xxx

SQL> grant ALTER ANY SNAPSHOT TO user1
SQL> connect user1/xxx
SQL> execute DBMS_MVIEW.REFRESH(’user2.MA_VM’);

  • rafraichissement asynchrone à la demande (on demand) d’une vue d’un groupe

La vue matérialisée doit faire partie d’un groupe a raffraichir (refresh group)
On peut le creer avec le package DBMS_REFRESH et la procédure MAKE(). Une VM peut être ajouté lors de la
création du groupe ou plus tard avec la procédure ADD().
SQL> execute DBMS_REFRESH.MAKE(’ref_group_1′,’MV1′,null,null);
– cree le refresh group ‘ref_group_1′ et y ajoute la VM ‘MV1′
Une fois cela fait on peut faire le refresh effectif.
On utilise encore le package DBMS_REFRESH et l’une de ses procédures de refresh : REFRESH(), REFRESH_ALL_MVIEWS, ou REFRESH_DEPENDENT)
SQL> execute DBMS_REFRESH.REFRESH(’ref_group_1′);

Attention ! DBMS_REFRESH.refresh  comme son nom ne l’indique pas, ne sert qu’à rafraichir les GROUPES et nécessite des droits sur SYS.DBMS_REFRESH ! Pour une VM simple on utilise DBMS_MVIEW.refresh .
Dans le cas on on utilise DBMS_REFRESH.REFRESH pour une VM simple on obtient une erreur :

execute DBMS_REFRESH.REFRESH(’dd’);
BEGIN DBMS_REFRESH.REFRESH(’dd’); END;
*
ERROR at line 1:
ORA-23404: refresh group “HR”.”DD” does not exist
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.DBMS_REFRESH”, line 23
ORA-06512: at “SYS.DBMS_REFRESH”, line 195
ORA-06512: at line 1

  • rafraichissement asynchrone cyclique (a date ou heure fixe)

on utilise la clause START WITH. SI elle est omise, Oracle se basera sur l’heure de création de la vue
matérialisée.
et la clause NEXT avec en paramètre une expression basée sur SYSDATE.
rafraîchissemebt auto tous les jours :

SQL> create materialized view mv4
refresh next sysdate + 1
as select * from scott.emp;
– et ci apres rafraîchissement toutes les minutes
– !!!!! pas vraiment conseillé… :
SQL> create materialized view mv4
refresh next sysdate + 1/24/60
as select * from scott.emp

  • raffraichissement d’une vue matérilaisée  tous les jours à la même heure , tous les jours à 8H par exemple :

SQL> CREATE materialized view scott.mv1
refresh complete
START WITH trunc(sysdate) + 8/24
NEXT trunc(sysdate) + 32/24
WITH PRIMARY KEY
AS
SELECT * FROM scott.emp


  • Exemple (pénible) de création de vue avec REFRESH ON COMMIT

SQL> create materialized view mvdd
2 refresh on commit
3 as select * from scott.bonus;
as select * from scott.bonus
*
ERROR at line 3:
ORA-01031: insufficient privileges
Elapsed: 00:00:00.96
SQL> select * from scott.bonus;
DD

Elapsed: 00:00:01.05
SQL>
SQL> grant on commit refresh to system;

Grant succeeded.

Elapsed: 00:00:01.35
SQL> create materialized view mvdd
2 refresh on commit
3 as select * from scott.bonus;
as select * from scott.bonus
*
ERROR at line 3:
ORA-12014: table ‘BONUS’ does not contain a primary key constraint
Elapsed: 00:00:01.30
SQL> desc scott.bonus
Name Null? Type
—————————————– ——– —————————-
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
SAL NUMBER
COMM NUMBER

SQL> alter table scott.bonus modify (ename primary key);

Table altered.

Elapsed: 00:00:00.30
SQL> create materialized view mvdd
2 refresh on commit
3 as select * from scott.bonus;

Materialized view created.

Vues utiles du dictionnaire concernant les VMs

SQL> select * from dict where table_name like ‘DBA_%MVIEW%’ ;

DBA_BASE_TABLE_MVIEWS : All materialized views with log(s) in the database
DBA_MVIEWS : All materialized views in the database
DBA_MVIEW_AGGREGATES : Description of the MV aggregates accessible to dba
DBA_MVIEW_ANALYSIS : Description of the materialized views accessible to dba
DBA_MVIEW_COMMENTS : Comments on all materialized views in the database
DBA_MVIEW_DETAIL_RELATIONS : Description of MV detail tables accessible to dba
DBA_MVIEW_JOINS : Description of join between 2 columns in the WHERE clause of a MV DBA_MVIEW_KEYS : Description of the columns that appear in the GROUP BY list of a materialized view accessible to dba
DBA_MVIEW_LOGS : All materialized view logs in the database
DBA_MVIEW_LOG_FILTER_COLS : All filter columns (excluding PK cols) being logged in the MV logs
DBA_MVIEW_REFRESH_TIMES : All fast refreshable materialized views and their last refresh times for each master table
DBA_REGISTERED_MVIEWS : Remote materialized views of local tables
DBA_REGISTERED_MVIEW_GROUPS : Materialized view repgroup registration information
DBA_TUNE_MVIEW : Catalog View to show the result after executing TUNE_MVIEW() API

Les paramètres d’initialisation

Administration Oracle 3 Commentaires »

Lors de son démarrage, une instance Oracle lit ses paramètres dans un fichier, dit de démarrage ou d’initialisation.
Ce fichier peut être de 2 types :
- un ‘init.ora’, fichier texte simple en lecture seule
- un ’sp file’, fichier de paramètre serveur, binaire, en lecture / écriture.

Dans l’ordre Oracle recherche ses paramètres PAR DEFAUT dans :
* spfile$ORACLE_SID.ora file ? l’emplacement par défaut
* spfile.ora
* et un fichier init.ora par défaut init$ORACLE_SID.ora.

Il est toujours possible de spécifier explicitement un fichier de parametres lors du démarrage de la base :

SQL> startup PFILE=/mon_chemin/mon_fic_init.ora
ou
SQL> startup SPFILE=/mon_chemin/mon_fic_spfile

Le fichier INIT.ORA

Il est éditable avec un éditeur de texte standard. Les modifications faites en son sein sont prises en compte lors du redémarrage de la base.Son contenu est de la forme suivante :

# commentaire
#
nom_parametre = valeur
nom_parametre = ( valeur1, valeurs2, … )

Un fichier init.ora MINIMISSIME contient 2 paramètres :

‘DB_NAME’ : le nom de la base de données
‘CONTROL_FILES’ : la liste des fichiers de contrôle utilisés

Les autres paramètres les plus fréquemment utilisés sont les suivants (S:statique, M:modifiable, D: dérivé d’un autre parametre)

COMPATIBLE S : Compatibilité arrière avec les versions précédentes (>= 9.2.0)
DB_DOMAIN S : Nom de domaine, qui associé au DB_NAME donne un nom de base unique
DB_RECOVERY_FILE_DEST M : Répertoire de destination de la zone de FLASH recovery
LOG_ARCHIVE_DEST_n M : Répertoire des fichiers REDO LOGS archivés
NLS_LANGUAGE D : langue utilisée (fonction de NLS_LANG dans l’environnement Unix ou windows)
NLS_TERRITORY M : le territoire (France par ex). Impacte les dates, et la monnaie
OPEN_CURSORS M : nb max de cursor ouvrable par une application
PGA_AGGREGATE_TARGET M : si 0, dimensionnement auto de la PGA, pour les proccess utilisateur server
PROCESSES S : nb max de process connectés ? Oracle (background et user)
SESSIONS D : = 1,1 * PROCESSES
SGA_TARGET D : si 0 auto dimensionnement de la SGA
SHARED_SERVER M : 0 si serveur dédié, n = nb de serveurs partagés lancés au startup instance
SP_FILE S : emplacement du SP File
UNDO_MANAGEMENT S : Manual | Auto, par dafaut Manual indique qu’on utilise des Rollback segments

Les noms et emplacements standards de ce fichier INIT.ORA sont les suivants :
sur Unix/Linux $ORACLE_HOME/dbs/init$ORACLE_SID.ora
sur Windows %ORACLE_HOME%\\database\\init%PRACLE8SID%.ora

note : il existe un fichier init.ora d’exemple (sample init.ora file) abondamment commenté sur chaque plate forme installé.
Il se trouve
sur Unix/Linux dans $ORACLE_HOME/dbs/
sur Windows dans %ORACLE_HOME%\\admin\\samplepfile

Le fichier SPFILE

Le fichier spfile, est aussi un fichier externe localisé sur le serveur de données, qui contient les paramètres.

Ceux ci sont persistants, c’est a dire qu’on peut les modifier ? n’importe quel moment, et que ces changements resteront pérennes au del? du re démarrage de la base. Ceci se fait avec la commande ALTER SYSTEM.

exemple :

SQL> ALTER SYSTEM SET DB_FILES=500 SCOPE = SPFILE;

On peut créer un SPFILE ex nihilo avec l’assistant de création de base de données ‘dbca’ ou ? partir d’un ‘init.ora’ existant avec la commande ‘CREATE SPFILE’.

exemple :

SQL> CREATE SPFILE = ” FROM PFILE = ”

Les noms et emplacements standards de ce fichier SPFILE sont les suivants :
sur Unix/Linux $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora
sur Windows %ORACLE_HOME%databasespfile%ORACLE_SID%.ora

Pour savoir quel SPFILE est effectivement utilisé on peut faire sous SQL+ :

SQL> SHOW PARAMETER SPFILE
NAME     TYPE              VALUE
-----     ---------         ------------------
SPFILE     STRING          F:ORACLEPRODUCT10.2.0DB_2 DBSSPFILEORCL.ORA

Enfin, un fichier init.ora peut être quasiment vide et simplement référencer un SPFILE, par un genre ” d’inclusion’ :

# exemple de init.ora referencant simplement un spfile :
#
SPFILE=’spfile.ora’