Comment installer oracleasm sur Oracle Linux 6.x

ASM, Cluster RAC, Divers 1 Commentaire »

Installation d’oracleasmlib sur OL6.1

Ne le trouvant pas à télécharger sur oracle.com ou Metalink, j’ai tenté de l’installer avec yum.

D’aprés la note 1089399.1

“For RHEL6, Oracle will only provide ASMLib software and updates when configured with a kernel distributed

by Oracle. Oracle will not provide ASMLib packages for kernels distributed by Red Hat as part of RHEL6.

ASMLib updates will be delivered via Unbreakable Linux Network(ULN) which is available to customers with Oracle

Linux support. ULN works with both Oracle Linux or Red Hat Linux installations,

but ASMlib usage will require replacing any Red Hat kernel with a kernel provided by Oracle.”

Yum m’a proposé d’installer le kernel UEK dans lequel oracleasm est natif et de fait de mettre à jour certains packages

du même kernel.

Il faut au préalable avoir configuré son yum pour qu’il pointe sur le bon référentiel ou s’être enregistré sur Unbreakable Linux Network.

[root@racform1 app]# yum install oracleasm

Loaded plugins: refresh-packagekit

Setting up Install Process

Resolving Dependencies

–> Running transaction check

—> Package kernel-uek.x86_64 0:2.6.32-300.11.1.el6uek will be installed

–> Processing Dependency: kernel-uek-firmware = 2.6.32-300.11.1.el6uek for package: kernel-uek-2.6.32-300.11.1.el6uek.x86_64

–> Processing Dependency: dracut-kernel >= 004-242.0.3 for package: kernel-uek-2.6.32-300.11.1.el6uek.x86_64

–> Running transaction check

—> Package dracut-kernel.noarch 0:004-53.el6 will be updated

—> Package dracut-kernel.noarch 0:004-256.0.1.el6_2.1 will be an update

–> Processing Dependency: dracut = 004-256.0.1.el6_2.1 for package: dracut-kernel-004-256.0.1.el6_2.1.noarch

—> Package kernel-uek-firmware.noarch 0:2.6.32-100.34.1.el6uek will be updated

—> Package kernel-uek-firmware.noarch 0:2.6.32-300.11.1.el6uek will be an update

–> Running transaction check

—> Package dracut.noarch 0:004-53.el6 will be updated

—> Package dracut.noarch 0:004-256.0.1.el6_2.1 will be an update

–> Finished Dependency Resolution

Dependencies Resolved

==================================================================================================================

Package Arch Version Repository Size

==================================================================================================================

Installing:

kernel-uek x86_64 2.6.32-300.11.1.el6uek ol6_latest 21 M

Updating for dependencies:

dracut noarch 004-256.0.1.el6_2.1 ol6_latest 109 k

dracut-kernel noarch 004-256.0.1.el6_2.1 ol6_latest 20 k

kernel-uek-firmware noarch 2.6.32-300.11.1.el6uek ol6_latest 3.0 M

Transaction Summary

==================================================================================================================

Install 1 Package(s)

Upgrade 3 Package(s)

Total download size: 24 M

Is this ok [y/N]: y

Downloading Packages:

(1/4): dracut-004-256.0.1.el6_2.1.noarch.rpm | 109 kB 00:00

(2/4): dracut-kernel-004-256.0.1.el6_2.1.noarch.rpm | 20 kB 00:00

(3/4): kernel-uek-2.6.32-300.11.1.el6uek.x86_64.rpm | 21 MB 00:54

(4/4): kernel-uek-firmware-2.6.32-300.11.1.el6uek.noarch.rpm | 3.0 MB 00:07

——————————————————————————————————————

Total 383 kB/s | 24 MB 01:04

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Updating : kernel-uek-firmware-2.6.32-300.11.1.el6uek.noarch 1/7

Updating : dracut-004-256.0.1.el6_2.1.noarch 2/7

Updating : dracut-kernel-004-256.0.1.el6_2.1.noarch 3/7

Installing : kernel-uek-2.6.32-300.11.1.el6uek.x86_64 4/7

Cleanup : dracut-kernel-004-53.el6.noarch 5/7

Cleanup : dracut-004-53.el6.noarch 6/7

Cleanup : kernel-uek-firmware-2.6.32-100.34.1.el6uek.noarch 7/7

Installed:

kernel-uek.x86_64 0:2.6.32-300.11.1.el6uek

Dependency Updated:

dracut.noarch 0:004-256.0.1.el6_2.1 dracut-kernel.noarch 0:004-256.0.1.el6_2.1

kernel-uek-firmware.noarch 0:2.6.32-300.11.1.el6uek

Complete!

[root@racform1 app]#

Ensuite vu que c’est un module du noyau, il faut le charger : “Please note: Oracleasm kernel driver is included in OL 6 UEK, but requires an inital ‘modprobe oracleasm’ ».

[root@racform1 app]# modprobe oracleasm

On installe ensuite le seul package non natif d’Oracleasm.

[root@racform1 app]# rpm -ivh oracleasm-support-2.1.5-1.el6.x86_64.rpm

Préparation… ########################################### [100%]

1:oracleasm-support ########################################### [100%]

[root@racform1 app]#

On voit que le module oracleasm est présent mais pas démarré

[root@racform1 app]# /etc/init.d/oracleasm status

Checking if ASM is loaded: yes

Checking if /dev/oracleasm is mounted: no

[root@racform1 app]#

Comme dans les versions précedentes, il faut le configurer.

[root@RACFORM2 ~]# oracleasm configure -i

Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library

driver. The following questions will determine whether the driver is

loaded on boot and what permissions it will have. The current values

will be shown in brackets (’[]‘). Hitting <ENTER> without typing an

answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: oracle

Default group to own the driver interface []: dba

Start Oracle ASM library driver on boot (y/n) [n]: y

Scan for Oracle ASM disks on boot (y/n) [y]: y

Writing Oracle ASM library driver configuration: done

[root@RACFORM2 ~]#

Il ne reste plus qu’à le démarrer.

[root@racform1 app]# oracleasm init

Creating /dev/oracleasm mount point: /dev/oracleasm

Mounting ASMlib driver filesystem: /dev/oracleasm

[root@racform1 app]#

Et le tour est joué

[root@racform1 app]# /etc/init.d/oracleasm status

Checking if ASM is loaded: yes

Checking if /dev/oracleasm is mounted: yes

[root@racform1 app]#

Petite subtilité pour la suite, il arrive que l’installeur Oracle pour la couche cluster ne découvre pas les disques avec le chemin standard “ORCL:”, dans ce cas on peut faire la découverte des disques avec le chemin suivant : /dev/oracleasm/disks.

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

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

Comment échapper à Dataguard et à son coût ??

Divers pas de Commentaire »

Il existe un produit nettement moins cher que la solution Dataguard proposé par Oracle et qui de surcroit ne nécessite pas d’être en Enterprise edition, c’est un produit New zelandais, DBVISIT.

Il s’appuie sur les techniques des Standby Database.

Il n’est certes pas zéro perte de données mais peu de solutions le sont et surtout peu d’applicatifs le réclament.

Adaptable sur RAC et ASM, permettant de faire du switchover avec ASm à partir de la version 11, et facile à installer, c’est une belle alternative à Dataguard, sa complexité et son prix.

http://www.dbvisit.com/

Albanlepunk

Principes de base sécurité Oracle

Divers, Sécurité Oracle pas de Commentaire »

Les principaux sont expliqués ici :

Buffer Overflow

Divers pas de Commentaire »

Un buffer overflow (BOF) est littéralement un dépassement de la capacité d’un buffer de données. Rien de bien méchant, cela arrive fréquement lorsque l’on manipule des pointeurs et que l’on programme sans trop de précautions.
La conséquence est généralement un ‘crash’ du programme, qui tente un accès à des zones en dehors de son espace d’adressage (donne des segmentation fault ou protection fault).
La saturation ou le crash de serveur n’est généralement le but ultime des hackers. L’acquisition de privilèges ou l’ouverture de backdoor est plus souvent ciblée.

Dans le domaine de la sécurité cette technique du BOF est utilisée pour ‘dérouter’ la séquence normale des instructions d’un programme et le forcer à éxécuter une routine spécifique, permettant généralement l’obtention de droits ’super utilisateur’. Cette routine ou ce programme exploitant la faille est appelé un “exploit” (en Anglais dans le texte)

‘droits super utilisateur’ ne veut pas dire seulement obtention de l’UID ‘root’ ou ‘Administrateur’. Beaucoup d’applications utilisent des variables ou des tables pour gérér en interne des niveaux de privilèges indépendemment de l’OS. Des flags du type ‘useradmin=1′ ou ‘loginok=true’ ou ‘privileges=ALL’ sont monnaie courante dans les programmes et beaucoup plus facilement exploitables.
De l’importance des attaques par buffer oveflow

Ce problème est important de par sa notoriété et également de par sa persistance. Les premières mises en évidence de la possibilité de ce type d’attaque datent de 1995 et sont toujours d’actualité.
Statistiquement si l’on consulte les sites de référérence que sont le site du Cert et le site d’infosys security par exemple, on constate qu’une part importante des pbs traités concerne le BOF. Voir plus particulièrement le rapport du dernier trimestre 2003 du CERT.

Bien qu’abondamment documenté sur le web, ce type d’attaque nécessite une grande ténacité et de bonnes compétences en programmation de bas niveau (assembleur), en désassemblage (les sources sauf cas ideal de certains logiciels libres n’étant pas à la disposition des hackers) et en gestion de la mémoire et architecture système (structure et adressage de la pile, contenu des bibliothèques sytème notamment).
Des conditions d’occurence d’un buffer overflow

Un certain nombre de conditions sont indispensables à l’occurence d’un BOF :

  • code de programmation vulnérable

o langage utilisé permissif (C),
o fonctions utilisées laxistes (strcpy(), strcat(), sprintf(), vsprintf(), gets(), scanf())
o appels à des fonctions privilégiées (system()),
o utilisation de variable donnant des privilèges,
o absence de test du code, etc.

  • programme attaqué s’exécutant avec un niveau de privilège fort (uid root par exemple)
  • lisibilité / accessibilité du code (accès au source ou désassemblage explicite, pas de free(), ni de user_check())
  • ordonnancement des données dans le programme (variable importante ‘écrasable’ , adresse de retour de subroutines ‘écrasable’

mais aussi

  • exposition / intérêt du site
  • ténacité du hacker

exemple de buffer overflow minimal …qui ne sert pas à grand chose !

void f() {
int a[10];
a[20] = 3;
}

sur la plipart des machines, le programme se plante et produit une erreur de segmentation de mémoire, du style : segmentation fault, core dumped…

Exemple simple de Buffer Overflow sur flag de sécurité

int main(int argc, char *argv[]) {
char passwd_ok = 0;
char passwd[8];
strcpy(passwd, argv[1]);
if (strcmp(passwd, “niklas”)==0)
passwd_ok = 1;

if (passwd_ok) {

}

La mémoire ressemble à ca :

bof_variable.jpeg

on comprend bien qu’en envoyant le bon nombre de caractère le Hacker, va écrasé le flag PASSWD_OK et se dispenser de s’authentifier avec un mot de passe correct…

Un exemple idéal (un heap overflow)

D’après Ghost Rider “Introduction to Buffer Overflow”, www.governmentsecurity.org

Soit le petit programme C suivant

main(int argc, char **argv) {
char *somevar;
char *important;
somevar = (char *)malloc(sizeof(char)*4);
important = (char *)malloc(sizeof(char)*14);
strcpy(important, “command”); /*This one is the important variable*/
stcrpy(somevar, argv[1]);
….. Code here ….
}

Il présente qq caractéristiques remarquables :

  • Il utilise des appels de commande système via la variable ‘important’.
  • Il a (au moins) un paramètre d’entrée (argv[1]) stocké dans la variable ’somevar’.
  • La variable ’someva’r est allouée en mémoire AVANT ‘important’, on peut en déduire que son adresse sera probablement inférieure a celle de ‘important’.
  • Enfin, malgré cette précédence, ’somevar’ prendra sa valeur a l’execution APRES ‘important’

Modifions ce programme pour lui faire imprimer les adresses et les contenus, et executons le en lui passant le parametre ‘TOTO’ :

$mon_programme TOTO

0×8049700: T(0×616c62)
0×8049701: O(0×616c)
0×8049702: T(0×61)
0×8049703: O(0×0)
0×8049704: (0×0)
0×8049705: (0×0)
0×8049706: (0×0)
0×8049707: (0×0)
0×8049708: (0×0)
0×8049709: (0×19000000)
0×804970a: (0×190000)
0×804970b: (0×1900)
0×804970c: (0×19)
0×804970d: (0×63000000)
0×804970e: (0×6f630000)
0×804970f: (0×6d6f6300)
0×8049710: c (0×6d6d6f63)
0×8049711: o (0×616d6d6f)
0×8049712: m (0×6e616d6d)
0×8049713: m (0×646e616d)
0×8049714: a (0×646e61)
0×8049715: n (0×646e)
0×8049716: d (0×64)
0×8049717: (0×0)

On connait désormais le décalage d’adresse entre ’somevar’ et ‘important’.
Le C et strcpy() le permettant, on va se permettre un dépassement de buffer de la variable ’somevar’ pour substituer NOTRE commande à la ‘command’ d’origine.

0×8049700: T(0×646e6573)
0×8049701: O(0×2d646e65)
0×8049702: T(0×2d2d646e)
0×8049703: O(0×2d2d2d64)
0×8049704: - (0×2d2d2d2d)
0×8049705: - (0×2d2d2d2d)
0×8049706: - (0×2d2d2d2d)
0×8049707: - (0×2d2d2d2d)
0×8049708: - (0×2d2d2d2d)
0×8049709: - (0×2d2d2d2d)
0×804970a: - (0×2d2d2d2d)
0×804970b: - (0×2d2d2d2d)
0×804970c: - (0×2d2d2d2d)
0×804970d: - (0×6e2d2d2d)
0×804970e: - (0×656e2d2d)
0×804970f: - (0×77656e2d)
0×8049710: n (0×6377656e) <— c’est la !
0×8049711: e (0×6f637765)
0×8049712: w (0×6d6f6377)
0×8049713: c (0×6d6d6f63)
0×8049714: o (0×616d6d6f)
0×8049715: m (0×6e616d6d)
0×8049716: m (0×646e616d)
0×8049717: a (0×646e61)
0×8049718: n (0×646e)
0×8049719: d (0×64)
0×804971a: (0×0)

C’est la le miracle : on peut modifier le déroulement de l’exécution d’un programme sans en modifier le code (heureusement il faudrait le recompiler et reinstaller l’executable sur la cible). Le paramètre d’entrée est le seul point…d’entrée du programme et on peut faire une subsitution de code, si tant est que certains appels se fassent à travers des variables et qu’elles soient correctement ‘rangées’ en mémoire.

Un exemple un peu moins ideal (un stack overflow)

D’après airWalk, “Introduction to buffer overflows” - for interScape, may 1999

Soit le programme C suivant :

void someFunction(char *str) {
char buffer[16];
strcpy(buffer, str);
}
void main()
{
char bigString[256];
int i;
for( i = 0; i < 255; i++)
bigString[i] = ‘A’;
someFunction(bigString);
}

A l’exécution il provoque une erreur du type : ‘Segmentation violation’ .
Pourquoi ? Parce que c’est au sein, et plus précisément juste avant le ‘return’ de la fonction qu’a lieu un dépassement de buffer.
Les 240 ‘A’ supplémentaires vont écraser les zones mémoires suivant la fin de la variable ‘buffer’ , et en particulier l’adresse de retour de la fonction, en y subsituant une valeur (pleine de ‘A’) invalide…d’ou l’erreur sus nommée.
On perçoit que dans ce cas, si l’on si prend bien il est possible de modifier l’adresse de retour d’une fonction et donc modifier le déroulement de l’exécution d’un programme.

Reste maintenant un détail, écrire une exploit capable de menacer le système. A la différence de l’exemple précédent qui faisait des appels à des commandes système, que l’on remplaçait, celui ci n’en fait pas…c’est son coté moins idéal.

rappelons que “l’exploit” du programme est souvent conditionnée par le niveau de privilège d’exécution du programme hacké. Un uid root (ou son équivallent Windows administrateur) permet + facilement d’executer des commandes fatales.

Les “exploit”s de buffer overflow

Nous savons comment on peut derouter un programme en écrivant l’exacte quantité de mémoire et en écrasant l’adresse de retour de la routine par une nouvelle adresse. Rest e à faire exécuter un nouveau code.
Ou peut se trouver le nouveau code agressif ?
ll est impossible de modifier physiquemnt le programme compilé de la cible. Le nouveau code ne peut qu’être passé qu’AU SEIN des données modifiées responsables du buffer overflow !
La taille du code ne correspondant pas forcément exactement à l’offset entre début de buffer et adresse de retour à écraser, précédera le code par des NOPs.
Que contient le code agressif ?
L’hypothèse étant que le programme vulnérable a un niveau de privilège intéressant (root), une des exploits les + classiques sera de lancer un shell permettant d’executer des commandes ‘intéressantes’ : rm -R /*, cat /etc/passwd, mail, etc.
Le code sera d’abord écrit en C, compilé , linké (gcc) et désassemblé (avec gdb). Les codes hexadécimaux du programme seront ensuite passés directement dans le buffer.

Si certains octets du code sont à zero, certaines des fonctions vulnérables (comme strcpy()) risquent d’interpréter le 0×00 comme une fin de chaîne ;-((

Les moyens d’actions

  • Utiliser des langages ’sécurisés’ : Cyclone ou Java Vs C, strncpy() Vs strcpy()
  • tester le code avec des outils spécialisés (Flawfinder, BFBtester, StackGuard)
  • Patcher !

Pour plus de détails sur certains de ces aspects on pourra consulter la note du RSA : Countermeasures Againt BOF attacks ici : http://www.rsa.com/rsalabs/node.asp?id=2011

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.

securite-oracle-Droits systeme

Divers, Sécurité Oracle pas de Commentaire »

Privilèges système
Les privilèges systèmes d’accès aux objets s’intéressent plutôt au contenant qu’au contenu. Ils concernent principalement des ordres de création, de modification de structure et de suppression d’objets (SQL DDL plutot que DML)

Ce sont donc des privilèges d’assez haut niveau, que l’on réservera par exemple aux développeurs mais qui seront utilisés avec beaucoup de parcimonie en phase de production…

Liste de quelques privilèges système d’Oracle

Nom
du privilege
Type
d’action autorisée
ANALYZE
ANALYZE ANY
Analyser
toutes les tables, clusters, ou indexs
dans
la base de données.
AUDIT
AUDIT ANY
Auditer tous
les objets dans la base de données.
AUDIT SYSTEM
Auditer les
actions de type DBA
CLUSTER
CREATE CLUSTER
créer un
cluster .
CREATE ANY
CLUSTER
créer un
cluster dans tous les schémas.
ALTER ANY
CLUSTER
Modifier
tous les cluster dans la base de données.
DROP ANY
CLUSTER
Supprimer
tous les cluster dans la base de données.
DATABASE
ALTER DATABASE
Modifier
la structure physique de la base
DATABASE
LINK
CREATE DATABASE
LINK
Créer
des database links privés.
INDEX
CREATE ANY
INDEX
créer un
index dans tous les schemas sur toutes les tables.
ALTER ANY
INDEX
Modidier
tous les index dans la base de données.
DROP ANY
INDEX
Supprimer
tous les index dans la base de données.
PRIVILEGE
GRANT ANY
PRIVILEGE
Donner tous
les privileges système
PROCEDURE
CREATE PROCEDURE
Créer
des procedures stockées, fonctions, et packages .
CREATE ANY
PROCEDURE
Créer
des procedures stockées, fonctions, et packages dans tous les schemas.
(suppose ALTER ANY TABLE, BACKUP ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE,
INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, ou GRANT ANY TABLE.)
ALTER ANY
PROCEDURE
Compiler
toutes les procedures stockées, fonction, ou packages dans tous les
schemas.
DROP ANY
PROCEDURE
Supprimer
toutes les procedures, function, ou package stockés dans tous les
schema.
EXECUTE ANY
PROCEDURE
Executer
toutes les procedures ou functions dans tous les schema.
PROFILE
CREATE PROFILE
Créer
des profils.
ALTER PROFILE
Modifier
tous les profils dans la base de données.
DROP PROFILE
Supprimer
tous les profils dans la base de données.
ALTER RESOURCE
COST
Modifier
la ressource ‘cost’ dans toutes les sessions.
PUBLIC
DATABASE LINK
CREATE PUBLIC
DATABASE LINK
Créer
des database links publics.
DROP PUBLIC
DATABASE LINK
Supprimer
database links publics.
PUBLIC
SYNONYM
CREATE PUBLIC
SYNONYM
Créer
des synonyms publics.
DROP PUBLIC
SYNONYM
Supprimer
des synonyms publics.
ROLE
CREATE ROLE
Créer
des roles.
ALTER ANY
ROLE
Modifier
tous les roles dans la base de données.
DROP ANY
ROLE
Supprimer
tous les roles dans la base de données.
GRANT ANY
ROLE
Grant tous
les roles dans la base de données.
ROLLBACK
SEGMENT
CREATE ROLLBACK
SEGMENT
Créer
des rollback segments.
ALTER ROLLBACK
SEGMENT
Modifier
des rollback segments.
DROP ROLLBACK
SEGMENT
Supprimer
des rollback segments.
SESSION
CREATE SESSION
Se connecter
!!!
ALTER SESSION
faire des
ALTER SESSION .
RESTRICTED
SESSION
Se connecter
malgré un démarrage ‘RESTRICT’. (OSOPER et OSDBA donnent ce
privilege.)
SEQUENCE
CREATE SEQUENCE
crée une
sequence dans son schema.
CREATE ANY
SEQUENCE
Créer
toutes les sequences dans tous les schemas.
ALTER ANY
SEQUENCE
Modifier toutes
les sequence dans tous les schémas.
DROP ANY
SEQUENCE
Supprimer toutes
les sequence dans tous les schémas.
SELECT ANY
SEQUENCE
Reference
toutes les sequence dans tous les schémas.
SNAPSHOT
CREATE SNAPSHOT
Créer
des snapshots (clichés) dans son schema
.
(l’utilisateur doit aussi avoir le privilege CREATE TABLE.)
CREATE SNAPSHOT
Créer
des snapshots dans tous les schémas.
(
CREATE ANY TABLE nécessaire.)
ALTER SNAPSHOT
Modifier
tous les snapshots dans tous les schémas.
DROP ANY
SNAPSHOT
Supprimer
tous les snapshots dans tous les schémas.
SYNONYM
CREATE SYNONYM
créer un
synonym dans son schema.
CREATE SYNONYM
Créer
tous les synonyms dans tous les schémas.
DROP ANY
SYNONYM
Supprimer
tous les synonyms dans tous les schémas.
SYSTEM
ALTER SYSTEM
faire des
ALTER SYSTEM .
TABLE
CREATE TABLE
Créer
des tables ou des indexs dans son propre schéma
CREATE
ANY TABLE
Créer
des tables dans tous les schémas.
ALTER ANY
TABLE
Modifier
toutes les table dans tous les schémas et compiler toutes les vues dans
tous les schémas.
BACKUP ANY
TABLE
Réaliser
des exports incrémentaux.
DROP ANY
TABLE
Supprimer
ou vider toutes les table dans tous les schémas.
LOCK ANY
TABLE
Verrouiller
toutes les tables ou vues dans tous les schémas.
COMMENT ANY
TABLE
Commenter
toutes les tables, vues, ou colonnes dans son schema.
SELECT ANY
TABLE
Interroger
toutes les tables, vues, ou clichés dans tous les schémas.
INSERT ANY
TABLE
Insert rows
into toutes les table ou view dans tous les schémas.
UPDATE ANY
TABLE
Update rows
in toutes les table ou view dans tous les schémas.
DELETE ANY
TABLE
Delete rows
from toutes les table ou view dans tous les schémas.
TABLESPACE
CREATE TABLE
SPACE
Créer tablespaces;
add files to the operating system via Oracle, regardless of the l’utilisateur’s
operating system privileges.
ALTER TABLESPACE
Modifier tablespaces;
add files to the operating system via Oracle, regardless of the l’utilisateur’s
operating system privileges.
MANAGE TABLESPACE
Take toutes
les tablespace offline, bring toutes les tablespace online, et begin et
end backups of toutes les tablespace.
DROP TABLESPACE
Supprimer tablespaces.
UNLIMITED
TABLESPACE
Use an unlimited
amount of toutes lestablespace. This privilege overrides toutes les
specific quotas assigned. If revoked, the grantee’s schema objects remain
but further tablespace allocation is denied unless allowed by specific tablespace
quotas. This system privilege can be granted only to l’utilisateurs et
not to roles. In general, specific tablespace quotas are assigned instead
of granting this system privilege.
TRANSACTION
FORCE TRANSACTION
Fouce the
commit ou rollback of own in-doubt distributed transaction in the local
database.
FORCE ANY
TRANSACTION
Fouce the
commit ou rollback of toutes les in-doubt distributed transaction in the
local database.
TRIGGER
CREATE TRIGGER
crée un trigger
in own schema.
CREATE ANY
TRIGGER
Créer toutes
les trigger dans tous les schémas associated with toutes les table dans
tous les schémas.
ALTER ANY
TRIGGER
Enable, disable,
ou compile toutes les trigger dans tous les schémas.
DROP ANY
TRIGGER
Supprimer toutes
les trigger dans tous les schémas.
USER
CREATE ANY
USER
Créer l’utilisateurs;
assign quotas on toutes les tablespace, set default et tempouary
tablespaces, et assign a profile as part of a CREATE USER statement.
BECOME ANY
USER
Become another
l’utilisateur. (Required by toutes les l’utilisateur perfouming a full database
impout.)
ALTER USER
Modifier other
l’utilisateurs: change toutes les l’utilisateur’s passwoud ou authentication
method, assign tablespace quotas, set default et tempouary tablespaces,
assign profiles et default roles, in an ALTER USER statement. (Not required
to alter own passwoud.)
DROP USER
Supprimer another
l’utilisateur.
VIEW
CREATE VIEW
crée un view
in own schema.
CREATE ANY
VIEW
crée un view
dans tous les schémas. (Requires that l’utilisateur also have ALTER ANY
TABLE, BACKUP ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, COMMENT ANY TABLE,
SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE,
ou GRANT ANY TABLE.)
DROP ANY
VIEW
Supprimer toutes
les view dans tous les schémas.

erreur ORA-28112 failed to execute policy function

Divers, Musée des erreurs pas de Commentaire »

Ca veut bien dire ce que ca veut dire !
Une fois les policies créées,  les packages et fonctions testées, l’utilisation implicite des polices (par un SELECT sur la table sécurisée par exemple) peut renvoyer cette erreur.

Une xeception est arrivée lors de l’execution qui n’est pas ‘attrapée’ par le programme et remonte donc au noyau. Reste à savoir quelle exception et pourquoi…

Pour avoir plus d’infos, il faut voir le fichier trace *ora*.trc généré dans USER_DUMP_DEST!!!!!!
Voici un exemple de contenu de trace intéressant (chercher éventuellement la chaine ORA-28112 si le fichier est trop gros)

srv-test$ > more pprun_ora_237820.trc
Dump file /work/oracle/log/prod_ora_237820.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/10GDB
System name: AIX
Node name: sr-pprod-1
Release: 3
Version: 5
Machine: 005F0ADA4C00
Instance name: prod
Redo thread mounted by this instance: 1
Oracle process number: 23
———————————————————-
Policy function execution error:
Logon user : SYSTEM
Table/View : SC1.CONTROLE
Policy name : SC1_APP_RESTRICT
Policy function: SYSTEM.PK_SET_VPD.FN_RESTRICT
ORA-04063: package body “SYSTEM.PK_SET_VPD” has errors
ORA-06508: PL/SQL: could not find program unit being called: “SYSTEM.PK_SET_VPD”
ORA-06512: at line 1
*** 2008-09-11 11:39:48.898
———————————————————-
Policy function execution error:
Logon user : SYSTEM
Table/View : SC1.CONTROLE_RMI
Policy name : SC1_APP_RESTRICT
Policy function: SYSTEM.PK_SET_VPD.FN_RESTRICT
ORA-06508: PL/SQL: could not find program unit being called: “SYSTEM.PK_SET_VPD”
ORA-06512: at line 1

On voit qu’il ne trouve pas le package de la policy dans SYSTEM…parce qu’elle a été créé dans un autre schéma (SC1 sur cet exemple)

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;
/

utilisation des tables externes

Divers, scripts et trucs pas de Commentaire »

Un exemple de script pour gérer et utiliser des tables externes Oracle (external tables)

On va charger emp a partir d’un fichier externe CSV, en utilisant SQL et les tables externes Oracle ( Oracle 9i, Oracle 10g et + si affinités )

D’abord, creer un repertoire oracle de travail (Oracle directory) associé au repertoire ou se trouve le fichier d’entree.
Pour ce faire, SCOTT doit avoir le privilège Systeme ‘CREATE ANY DIRECTORY’
…et le CREATE TABLE bien sur !

sous SQL>

CONNECT SYSTEM/xx@db
GRANT CREATE ANY DIRECTORY TO scott;
CONNECT scott/tiger@db
create or replace directory dossier_temp as ‘/tmp’;
drop table table_externe_emp;

rem : si le user qui lit/ecrit dans la directory n’est pas celui qui l’a créé
(ici c’est SCOTT qui fait tout), ce uyser aura aussi besoin des droitsd R/W sur la directory :

SQL> grant read,write on directory dossier_temp to scott;

Créer une ‘table’ externe attachee au fichier d’entree

create table table_externe_emp
(EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory dossier_temp
access parameters
( records delimited by newline
badfile ‘charge_emp.bad’
logfile ‘charge_emp.log’
fields terminated by ‘,’
(empno, ename, job, mgr, hiredate date, sal, comm, deptno)
)
location (’emp.csv’)
)
– attention c’est important sinon ca marche moins bien !
reject limit unlimited
/

Lire la table externe

select * from table_externe_emp;

!! Attention en cas de problème regardez les fichiers LOG et BAD sur le serveur

– lecture table externe et INSERTION dans une autre cible
– on insère dans la table destination si elle existe

insert into mon_emp
select * from table_externe_emp;
commit;

– lecture table externe et CREATION/INSERTION dans une autre cible
– on peut aussi pour aller + vite recopier la table externe ET son contenu !

create table mon_emp
as select * from table_externe_emp;

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

ORA-01031 / ORA-06512

Divers, Musée des erreurs pas de Commentaire »

Probleme lors de l’export 10g
sur APEX et les applications utilisant des ‘domain index’ORA-01031: insufficient privileges
ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 257
ORA-06512: at line 1
EXP-00078: Error exporting metadata for index WWV_FLOW_OH_IDX. Index creation wi
ll be skipped
C’est un BUG en plus des droits standards d’export, il faut des GRANT SELECT explicites sur les tables ayant des index de domaine…

On fait un scrit pour trouver les tables à GRANTer :

SQL > select ‘GRANT SELECT ON ‘||OWNER||’.'||TABLE_NAME||’ TO user_qui_fait_lexport;’
from dba_indexes
where index_type=’DOMAIN’

–>

GRANT SELECT ON KF.STATES TO user_qui_fait_lexport;
GRANT SELECT ON KF.INTERSTATES TO user_qui_fait_lexport;
GRANT SELECT ON KF.OCEAN TO ORA_BAT;
GRANT SELECT ON KF.TERRITORIES TO ORA_BAT;

et executer les grant !

Calculer les statistiques pour tous les objets d’un schema

Divers, scripts et trucs pas de Commentaire »

Si l’optimiseur est un peu fatigué et que les requetes sql ne sont pas meix on peut toujours essayer d’aider le dit optimiseur en recalculant les statistiques pours tous les objets du schema.

Il existe un package Oracle pour ca en 9i et 10g, plutot que de faire de fastidieux ANALYZE TABLE…COMPUTE .

C’est DBMS_STATS et la procedure GATHER_SCHEMA_STATS

begin
dbms_stats.gather_schema_stats(’&OWNER’,DBMS_STATS.AUTO_SAMPLE_SIZE,false,
‘FOR ALL COLUMNS SIZE AUTO’,1,’GLOBAL’,true,null,null,’GATHER AUTO’);
end;
/

ou plus simplement (en utilisant les valeurs par defaut des parametres :

execute dbms_stats.gather_schema_stats(ownname=>’nom_du_proprietaire’);

Pour plus d’infos sur le paramétrage voir la doc officielle PL/SQL supplied packages

et la procedure GATHER_SCHEMA_STATS du package DBMS_STAT

Bonnes stats !

Introduction PL/SQL

Divers pas de Commentaire »

PL/SQL est un langage qui intègre SQL et permet de programmer de manière procédurale. Il est spécifique ? Oracle. Pour SQL Server il existe par exemple un équivallent : TRANSAC SQL

Globalement, avec PL/SQL on aura ? notre disposition un vrai langage de programmation moins intuitif mais aussi plus puissant que le SQL. Les 2 langages sont éminemment complémentaires.

Les principaux avantages / inconvénients sont les suivants :
- ne dispense pas de connaître le SQL,
- permet le traitement par bloc de SQL et donc optimise le trafic réseau,
- l’utilisation de variable de stockage et de type simple et strucuré dynamique (%TYPE, %ROWTYPE, etc)
- des traitements plus complexes, notamment pour la gestion des cas particuliers et des erreurs (traitement des exceptions),
- l’utilisation de librairies standards prédéfinies (supplied PLSQL packages, comme les RDBMS_xxx),
- un paramétrage et la création d’ordres SQL dynamiques.

Le PL/SQL peut être utilisé sous 3 formes :

* un bloc de code, executé comme une commande SQL, viua un interpréteur standard (SQL+ ou iSQL*PLus)
* un fichier de commande PL/SQL
* un programme stocké (procédure, fonction, package ou trigger)

ordres SQL supportés dans PL/SQL

Les instructions du langage de manipulation de données (LMD) et certaines instructions de gestion de transaction, ? savoir :
- INSERT, UPDATE, DELETE, SELECT,
- COMMIT, ROLLBACK, SAVEPOINT,
- LOCK TABLE,
- SET TRANSACTION READ ONLY.
Avec les versions antérieures ? la 9i, Les ordres du LDD ne sont pas supportés par défaut il faut utiliser un package spécial comme dans le script suivant.

Autres composants du langage PL/SQL

Les principales autres instructions (non SQL) du PL/SQL sont les suivantes :
CLOSE, EXECUTE IMMEDIATE, FETCH, FORALL, GOTO, IF, LOOP, NULL, OPEN, FOR, RAISE, RETURN

A cela s’ajoute les opérateurs :
d’affectation : ‘:=’, ‘(SELECT) INTO’ et ‘(FETCH) INTO’
+ les opérateurs classqiues du SQL : ||, >, >, >=, <=, <>, !=, …

La liste des mots réservés PL/SQL est disponible ici,
http://didier.deleglise.free.fr/plsql/plsql_mots_reserves.pdf…au format PDF.


Blocs et sections PL/SQL

Les blocs de code s’appellent également des blocs anonymes.
Ils commenceront simplement par un ‘BEGIN’ ou un ‘DECLARE’.
Ils sont composés de 1 ? 3 sections :
Type de section obligatoire ? Mots clés
section déclarative non DECLARE…
section exécutable oui BEGIN …END;
section de traitement des exceptions non EXCEPTION

la section ‘exception’ quand elle est présente est incluse dans la section exécutable et NON PAS ? la suite de celle ci.

(DD is proud to announce…)le plus petit bloc PL/SQL au monde:

SQL> BEGIN
NULL;
END;
.
/
Procedure PL/SQL terminee avec succes.

le bloc ne peut pas être vide et doit contenir au moins une instruction…le ‘.’ sous SQL*PLus sert a arrêter la saisie du code PL/SQL et le ‘/’ comme toujours ? executer le contenu du buffer de commande courant.

avec une déclaration de variable un (tout petit) bloc PL/SQL ca donne ça :

SQL> DECLARE x INTEGER;
BEGIN
X := 1;
END;
.
Procedure PL/SQL terminee avec succes.

un bloc avec des exceptions a donc la structure suivante :

DECLARE — mes déclarations de variables…
BEGIN — début de la section executable
– mes ordres SQL et PLSQL
EXCEPTION
– mon traitement des exceptions
END; — la fin du bloc executable

Gestion des exceptions

Les exceptions peuvent se déclencher automatiquement en cas d’erreur système ou de pb réseau par exemple, ou explicitement avec l’instruction ‘RAISE’

Il existe des exception prédéfinies, des exceptions génériques et des exceptions définies par l’utilisateur.

exemple :

DECLARE S VARCHAR(15);
BEGIN
S := ‘Salut’;
DBMS_OUTPUT.PUT_LINE(S||’ les gars’);
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(’On a une exception générique ici’);
END;

WHEN OTHERS permet de gérer toutes les exceptions qui ne sont pas prédéfinies dans Oracle.
De manière classique en utilisant le SQLCODE (le code de retour donnant le no de l’erreur Oracle survenue)


EXCEPTION
— pb de connexion, une exception prédéfinie
WHEN LOGIN_DENIED Then

— dans tous les autres cas…
WHEN OTHERS THEN
If SQLCODE = … Then …
Elsif SQLCODE = … Then …

End if ;
END;

Voici la liste de ces dernières :

Nom exception Code Erreur
———————— ———–
ACCESS_INTO_NULL ORA-06530
CASE_NOT_FOUND ORA-06592
COLLECTION_IS_NULL ORA-06531
CURSOR_ALREADY_OPEN ORA-06511
DUP_VAL_ON_INDEX ORA-00001
INVALID_CURSOR ORA-01001
INVALID_NUMBER ORA-01722
LOGIN_DENIED ORA-01017
NO_DATA_FOUND ORA-01403
NOT_LOGGED_ON ORA-01012
PROGRAM_ERROR ORA-06501
ROWTYPE_MISMATCH ORA-06504
SELF_IS_NULL ORA-30625
STORAGE_ERROR ORA-06500
SUBSCRIPT_BEYOND_COUNT ORA-06533
SUBSCRIPT_OUTSIDE_LIMIT ORA-06532
SYS_INVALID_ROWID ORA-01410
TIMEOUT_ON_RESOURCE ORA-00051
TOO_MANY_ROWS ORA-01422
VALUE_ERROR ORA-06502
ZERO_DIVIDE ORA-01476

NO_DATA_FOUND est fréquemment utilisée dans les programmes, lorsqu’on veut finir une boucle de lecture de lignes par exemple.
LOGIN_DENIED également, acr les pbs de connexions sont en pratique assez nombreux.

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

Row Level Security (RLS) …un peu plus loin

Divers, Sécurité Oracle 1 Commentaire »

DEMO de la sécurité au niveau LIGNE avec Oracle 10g
(A.K.A Row Level Security / RLS / Fine Grained Access Control / VPDs)

Le but de cette demo est de mettre en place une strategie sur la table
SCOTT.EMP, qui s’appuie sur un contexte utilisateur, défini de manière automatique..
Un user ne devra voir que la (les) ligne’s) qui le concerne dans la table,
même en faisant un ‘SELECT * FROM emp’.
ex : KING ne voit que la ligne de KING, CLARK la ligne de CLARK.
On créera un user Oracle ‘CLARK’ présent dans la table des employés,
pour tester les filtres appliqués au user connecté.

—————————-
– 0) Environnement de TEST
—————————-
– en tant que SYS

– créer un user spécifique pour mettre en place la sécurié
CREATE USER sec IDENTIFIED BY nbvcxw;
– les privileges minimum necessaire pour DEV1
GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE,
CREATE ANY CONTEXT TO sec;
GRANT EXECUTE ON DBMS_RLS TO sec;
GRANT EXECUTE ON DBMS_SESSION TO sec;
– rem on peut remplacer les 2 precedents par un
– GRANT EXECUTE_CATALOG_ROLE TO dev1 (moins ciblé / sécurisé)
CREATE USER clark IDENTIFIED BY clark;
GRANT CREATE SESSION TO clark;
GRANT SELECT ON scott.emp TO PUBLIC;
GRANT EXECUTE ON sec.pack_contexte_emp TO PUBLIC; (pour debug)
GRANT EXECUTE ON filtre_emp TO PUBLIC; (pour debug)

—————————
– 1) déclarer le contexte
—————————

CONNECT sec/nbvcxw
CREATE OR REPLACE CONTEXT contexte_employe
USING pack_contexte_emp;

——————————————————————————–
– 2) definir le contexte applicatif (code du package et attributs du contexte)
——————————————————————————–

– ici c’est le no d’emp et le niveau (chef ou nom)
– qui determineront les privileges d’acces ? l’applicatif

– les specs du package :

CREATE OR REPLACE PACKAGE pack_contexte_emp
AS
PROCEDURE def_contexte;
END;

– le corps du package :

CREATE OR REPLACE PACKAGE BODY pack_contexte_emp
IS
PROCEDURE def_contexte
IS
v_empno NUMBER;
nb_subordonnes NUMBER;
BEGIN

– on recupere le no de l’employe et sa fonction (chef ou non)
– et on definit les attributs du contexte en consequence
– le ‘NAMESPACE’ ‘contexte_employe’ a donc 2 attributs ici
– on ne se sert que du premier dans cet exemple…

– d’abord le no d’emp
– en utilisant le ‘SESSION_USER’ de SYS_CONTEXT
– equivallent a un SELECT username FROM dual
– rem : si le user n’apparait pas dans la table –> no data found (a trapper)

SELECT empno INTO v_empno FROM scott.emp
WHERE ename = SYS_CONTEXT(’USERENV’, ‘SESSION_USER’);

DBMS_SESSION.SET_CONTEXT(’contexte_employe’, ‘no_emp’, v_empno);

– puis son niveau de responsabilite : chef ou non
– si oui son no d’emp est le no de manager de quelqu’un…

SELECT COUNT(*) INTO nb_subordonnes
FROM scott.emp
WHERE mgr= v_empno;
IF (nb_subordonnes <> 0) THEN
DBMS_SESSION.SET_CONTEXT(’contexte_employe’, ‘niveau’, ‘chef’);
ELSE
DBMS_SESSION.SET_CONTEXT(’contexte_employe’, ‘niveau’, ‘employe’);
END IF;

END def_contexte;
END pack_contexte_emp;

– test

select SYS_CONTEXT(’USERENV’, ‘SESSION_USER’) from DUAL
exec sec.pack_contexte_emp.def_contexte

———————————————-
– 3) definir les fonctions limitant les acces
———————————————-

– la ou les fonctions vont retourner une chaine
– qui sera ajoutee a la clause WHERE (predicat supplementaire)
– on utilise le contexte ‘employe’ pour identifier le user

CREATE OR REPLACE PACKAGE filtre_emp
AS
FUNCTION vue_emp (owner VARCHAR2, objname VARCHAR2)
RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY filtre_emp
IS
FUNCTION vue_emp (owner VARCHAR2, objname VARCHAR2)
RETURN VARCHAR2
IS
predicat VARCHAR2(1000);
BEGIN
predicat := ‘empno=SYS_CONTEXT(”contexte_employe”,”no_emp”)’;
RETURN predicat;
END;
END;

– test

SELECT SYS_CONTEXT(’contexte_employe’,'no_emp’) FROM dual
SELECT sec.filtre_emp.vue_emp(’SCOTT’,'EMP’) FROM dual

————————————————————
– 4) creer stratégie d’acces (policy) attachée a la table
————————————————————

DBMS_RLS.ADD_POLICY (proprietaire_objet,nom_objet,nom strategie,proprietaire strategie,fonction_filtre, SQL_concerne) :
execute DBMS_RLS.ADD_POLICY (’scott’, ‘emp’, ‘policy_emp’,’sec’, ‘filtre_emp.vue_emp’, ‘SELECT’);

(si necessaire
DBMS_RLS.DROP_POLICY (nom_owner, nom_table, nom_police) :
execute DBMS_RLS.DROP_POLICY (’scott’, ‘emp’, ‘policy_emp’); )

–test

select * from sys.rls$ where PFSCHMA=’DEV1′;
OBJ# GNAME PNAME STMT_TYPE CHECK_OPT ENABLE_FLAG PFSCHMA PPNAME PFNAME PTYPE
52606 SYS_DEFAULT POLICY_EMP 513 0 1 DEV1 FILTRE_EMP VUE_EMP

———————————————————————
– 5) activer le contexte, avant l’acces aux données des utilisateurs
———————————————————————
– manuellement (ou géré par l’applicatif)
connect clark/clark
exec sec.pack_contexte_emp.def_contexte;

– ou automatiquement par un trigger, en tant que DBA !!

CREATE OR REPLACE TRIGGER dev1.active_contexte_emp
AFTER LOGON ON DATABASE
BEGIN
sec.pack_contexte_emp.def_contexte;
END;

——————-
– 6) Test VPD
——————-

SQL> connect clark/clark
SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
—– —– ——– —- ——– —- —- ——
7782 CLARK MANAGER 7839 09/06/81 2450 10

SQL> connect system/xdcfvgh
SQL> select * from emp;
aucune ligne sélectionnée.

et sans la VPD :

SQL> execute DBMS_RLS.DROP_POLICY (’dev1′, ‘emp’, ‘policy_emp’);
ou moins violent :
SQL> grant exempt access policy to DEV1;
SQL> connect dev1/dev1
SQL> select * from emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
—– ———- ——— ———- ——– ———- —– ——
7369 SMITH CLERK 7902 17/12/80 800 20
7499 ALLEN SALESMAN 7698 20/02/81 1600 300 30
7521 WARD SALESMAN 7698 22/02/81 1250 500 30
7566 JONES MANAGER 7839 02/04/81 2975 20
7654 MARTIN SALESMAN 7698 28/09/81 1250 1400 30
7698 BLAKE MANAGER 7839 01/05/81 2850 30
7782 CLARK MANAGER 7839 09/06/81 2450 10
7788 SCOTT ANALYST 7566 19/04/87 3000 20
7839 KING PRESIDENT 17/11/81 5000 10
7844 TURNER SALESMAN 7698 08/09/81 1500 0 30
7876 ADAMS CLERK 7788 23/05/87 1100 20
7900 JAMES CLERK 7698 03/12/81 950 30
7902 FORD ANALYST 7566 03/12/81 3000 20
7934 MILLER CLERK 7782 23/01/82 1300 10
1111 DEV1 Engineer 7782 01/01/07 2000 10

15 ligne(s) selectionnee(s).

L’erreur ORA-28112

l’utilisation des policies déclenche parfois une erreur ORA-28112 , même si les polices sont correctes et les packages aussi.
Voir le musée des erreurs - erreur ORA-28112 ” Failed to execute policy function ”

——-
Notes
——-

1) Pourquoi un compte SEC spécifique pour les objets nécessaire ? la VPD ?
- c’est + sécurisé, les données ne sont pas mélangées avec les règles d’accès
- c’est + facile a debugger : on peut soustraire ce compte aux règles des VPDs
- on évite les pbs d’accès récursif de la VPD

2) les parametres des fonctions de filtre sont OBLIGATOIRES !

3) Les DBAs soont normalement soumis aux stratégies comme tous les autres users.
Il est néanmoins possible d’outrepasser les VPDs
(pour le deboggage, pour l’administration, ou pour eviter les pbs de récursion)
SQL> grant exempt access policy to nom_user_cible;

4) une ORA-28112 = echec d’execution de la fonction de regle, lors du SELECT
==> la fonction est OK, mais il y a une exception qui n’est pas trappée par la fonction.
voir le fichier trace généré par l’erreur dans le répertoire USER_DUMP_DEST
pour résoudre le problème

5) vues du referentiel utiles
%_POLICIES, V$VPD_POLICY, RLS$, v$SQL

6) ne pas confondre les policies générales et les policies utilisées pour l’audit (Fine Grain Auditing).
Les packages, procédures et vues du dictionnaire ne sont pas les mêmes !
exemple : les packages DBMS_RLS (généraux) et DBMS_FGA (audit)

7) il n’existe pas de DISABLE_POLICY en 9i !!! mais un DBMS_RLS.ENABLE_POLICY avec ENABLE=….FALSE !!!

Introduction a la Sécurité au niveau ligne (RLS)

Divers, Sécurité Oracle pas de Commentaire »

Les prémices

Le principe de base des accès au données Oracle, s’appelle le ‘Discretionary Access Control’ ou DAC.
Il s’appuie sur des privilèges d’accès aux objets donnés aux utilisateurs concernés, grace ? la commande SQL ‘GRANT’.
Ainsi on donne ? Mr Clark le droit de consulter la table EMP de SCOTT, par la commande
GRANT SELECT ON scott.EMP TO clark’.
Ce controle d’accès ne gère pas de niveau plus fin que la table complète : soit CLARk ? a l’accès ? la totalité de la table ou ? aucune de ses lignes…
Ceci peut être affiné en utilisant des vues.
On peut limiter l’accès ? un niveau plus fin, par exemple autoriser CLARK ? ne lire que la ou les lignes qui le concerne, en créant une vue avec prédicat :

SQL> CREATE VIEW v_emp AS SELECT * FROM scott.emp
WHERE ename=user;
SQL> CONNECT clark/clark
SQL> SELECT * FROM scott.v_emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7782 CLARK MANAGER 7839 09-JUN-81 2450 10

Ceci peut néanmoins dans certains être contourné si Mr Clark iu d’autres utilisateurs accèdent directement ? la table sous jacente EMP.
La sécurité au niveau ligne (RLS) ou l’accès au niveau fin (Fine Grain Access Control ou FGAC)

sont une solution complètement ttransparente pour tous les users, ? ce type de besoin.
Faire du RLS (FGAC) nécessite
- 1 fonction predicat, qui limite les accès ? certaines lignes
Cette fonction, a 2 paramètres obligatoires (le proprietaire et le nom de l’objet filtré) et retourne

une chaine qui contient une expression booleenne.
- 1 strategie (policy) attachée ? la table qui met en oeuvre automatiquement cette fonction.
cette strategie, a un nom et doirt preciser l’objet filtré et la fonction utilisée.
Les stratégies sont gérées par un package prédéfini appelé DBMS_RLS. Le créateur de la startégie doit

donc avoir les droits d’EXECUTE sur ce package.
Pour la création on utilisera par exemple DBMS_RLS.ADD_POLICY(…)

Dans un Schmé sécurité (compte SEC) cela nous donne :

création de la fonction prédicat et de la stratégie

SQL> CONNECT sec/sec
SQL> CREATE OR REPLACE PACKAGE filtre_emp
AS
FUNCTION vue_emp (owner VARCHAR2, objname VARCHAR2)
RETURN VARCHAR2;
END;

SQL> CREATE OR REPLACE PACKAGE BODY filtre_emp
IS
FUNCTION vue_emp (owner VARCHAR2, objname VARCHAR2)
RETURN VARCHAR2
IS
predicat VARCHAR2(1000);
BEGIN
predicat := ‘ename=user’;
RETURN predicat;
END;
END;

– si nécessaire
execute DBMS_RLS.DROP_POLICY (’scott’, ‘emp’, ‘policy_emp’);
– et on crée donc…
execute DBMS_RLS.ADD_POLICY (’scott’,'emp’,'policy_emp’,’sec’,'filtre_emp.vue_emp’, ‘SELECT’);

Démontration du résultat de la stratégie mise en place

SQL> CONNECT clark/clark
SQL> SELECT COUNT(*) FROM scott.emp;
–> 1
SQL> CONNECT system/xxxx
SQL> SELECT COUNT(*) FROM scott.emp;
–> …
no row selected
car tout le monde est a priori sousmis ? la stratégie de sécurité !
y compris le DBA, et comme son nom d’utilisateur n’apparait pas dans la table EMP de SCOTT, le prédicat renvoie un résultat faux…et aucune ligne n’est affichée.

Nous verrons dans un prochain article comment créer une ébauche de VPD en associant ? cette stratégie un contexte utilisateur.

DBAs Oracle et Privilèges d’exploitation

Divers, Sécurité Oracle pas de Commentaire »

Certains ‘administrateurs’ : les opérateurs et techniciens d’exploitation, ou ‘exploitants’ pour faire court, n’ont pas forcément besoin d’un niveau de privilège DBA.

Les opérations concernés sont par exemple :

  • les démarrage / arrêts,
  • les sauvegardes / restaurations,
  • la planification et l’exécution de batch

Oracle fournit 2 niveaux de privilèges, qui peuvent être assimilés ? des niveaux de connexion, qui satisfont ces besoins : les privilèges d’exploitation ‘SYSDBA’ et ‘SYSOPER’.
Comme tout accès privilégié il s’acquiert via un processus d’identification / authentification.

Authentification locale au niveau du système d’exploitation

C’est une forme d’authentification externe, en ce sens que ce n’est pas Oracle qui contrôle la connexion grace a son référentiel interne. On se connecte directement (via telnet ou ssh par exemple) au système qui héberge le serveur de données, puis a la base locale, sans plus faire intervenir le réseau.
Ce type de connexion originale ne nécessite pas d’identifiant ni de mot de passe Oracle, mais d’être un utilisateur privilégié au niveau O.S.

note : un utilisateur quelconque, non privilégié de la base, peut aussi être défini avec une authentificatin externe, et se connecter localement avec une commande du type : sqlplus /

On peut dire que dans ce cas la sécurité est déportée au niveau O.S. et qu’Oracle accorde sa ‘confiance’ aux mécanismes d’identification / authentification de ce dernier.

exemples de connexion avec le client SQL standard :
connexion ‘normale’
$sqlplus scott/tiger
connexion avec authentification externe
$sqlplus / as sysdba
$sqlplus / as sysoper

Pour obtenir un ‘privilège’ d’exploitation il suffit d’appartenir au groupe utilisateur correspondant au niveau système :

privilège  gpe unix    groupe windows
SYSDBA     dba         ORA_DBA
SYSOPER    oper        ORA_OPER

Ces groupes sont créés lors de l’installation, et un administrateur système en ‘hérite’ automatiquement

note : Le ‘CONNECT INTERNAL’ des versions précédentes est définitivement obsolète et a été remplacé par le ‘CONNECT SYS AS SYSDBA. Parallèlement il n’est plus possible de se connecter SYS ‘tout court’ sans préciser ‘AS SYSDBA’.

Authentification distante au niveau du système d’explotation

Elle présente les mêmes caractéristiques que précédemment sauf que la base est située sur une machine distante de la connexion système courante.

La syntaxe de connexion devient donc :
$ sqlplus /@nom_base_distante AS SYSDBA (ou SYSOPER)

Un paramètre d’initialisation de la base : REMOTE_OS_AUTJENTICATION=TRUE autorise cette fonctionnalité.

Note importante : il est vivement conseillé pour des raisons de sécurité d’invalider cette possibilité.

Authentification via fichier de mots de passe
Dans ce cas de figure, les privilèges seront controlés a partir d’un fichier de mot de passe cryptés local.
exemple de création du fichier :
$ ORAPWD FILE=monfic PASSWORD=monpasse ENTRIES=100
avec
PASSWORD : le mot de passe de SYS
ENTRIES : le nb mas d’utilisateurs référencables dans le fichier.

on peut ensuite créer un utilisateur TOTO avec mot de passe TUTU et que le DBA lui donne le privilège oracle (et non pas système cette fois) nécessaire : SYSDBA ou SYSOPER :

$> sqlplus / AS SYSDBA
SQL> CREATE USER TOTO IDENTIFIED BY TUTU;
SQL> GRANT CREATE SESSION TO TOTO (qu’il aie le droit de se connecter quand même…)
$ GRANT SYSDBA TO TOTO (et lui donner le privilège d’exploitation qui va bien)

note : le paramètre d’initialisation REMOTE_LOGIN_PASSWORDFILE doit être a EXCLUSIVE (c’est le défaut) pour pouvoir utiliser et modifier le password file

Utilisateurs Oracle 10g

Divers, Sécurité Oracle 2 Commentaires »

La notion d’utilisateur

Quels que soient l’architecture utilisée,le programme client, ou votre profil utilisateur : administrateur, développeur ou simple utilisateur final, l’accès aux données d’une base exige de se connecter ? un compte utilisateur.

note : les utilitaires système d’export, d’import, de sauvegarde, et de chargement par exemple impliquent également une connexion ? un compre utilisateur.

Un compte peut éventuellement contenir des données (tables principalement) on l’appelle dans ce cas un SCHEMA. Il peut également être verrouillé par l’administrateur.

Pour créér un utilisateur on utilisera soit du SQL : instruction CREATE USER…
soit un outil d’administration graphique comme la console OEM ou la console GRID.
Pour le supprimer on utilisera la commande :
DROP USER nom_user CASCADE pour supprimer également les données associées.

La description de tous les utilisateurs est donnée par la vue SYS.DBA_USERS ou pour un user non privilégié dans USER_USERS

Les principaux attributs d’un utilisateur sont les suivants :
- le nom
- la méthode d’authentification…et le mot de passe associé le cas échéant
- ses espaces logiques de travail : TABLESPACEs par défaut et temporaire
- ses quotas de création de structures données sur ses TABLESPACES permanents (optionnel)
- ses droits
- son PROFIL de consommation de ressources (optionnel)

note : un utilisateur final n’a pas besoin de quota, puisque’il ne créé pas de structure, mais tout au plus insère des données dans des structures existantes.
Il n’a besoin que de privilèges minimaux, celui de se connecter et de consulter voire mettre ? jour quelques tables d’un schéma.

Voir l’article sur les Roles et l’article sur les privilèges objets pour plus d’infos

En pratique dans les entreprises il existe un nombre limités de types d’utilisateurs. Ceci aura pour conséquence de pouvoir limiter sensiblement les ‘groupes d’utilisateur’.
On trouve, du moins privilégié au + privilégié :
- l’utilisateur d’infocentre (consultation de données uniquement)
- l’utilisateur d’application (consultation et mise ? jour)
- le responsable ou administrateur d’application (idem + gestion des utilisateurs et de l’accès aux fonctionnalités de l’application)
- le développeur (droit de cration de tables, vues, indexs, procédures stockées, triggers, séquences,…)
- le DBA (accès en lecture total au dictionnaire et droits de consultation mis ? jour de toutes les données utilisateurs, tous les droits sur la base)

Les ‘profils’ (PROFILE) Oracle

Ces ‘profils’ assez mal nommés, fixent des limites de consommation de ressouces (temps de session et de connexion, consommation mémoire partagée et E/S disque), la politique de mot de passe des utilisateurs (expiration et historique) et peuvent être comme les roles, partagés par un groupe d’utilisateurs.

Il existe 2 profils prédéfinis : DEFAULT, pours les users lambdas et MONITORING_PROFILE utilisé pour le compte DBSNMP.

SQL> SELECT * FROM DBA_PROFILES
WHERE profile=’DEFAULT’

DEFAULT COMPOSITE_LIMIT 	  KERNEL 	UNLIMITED
DEFAULT SESSIONS_PER_USER 	  KERNEL 	UNLIMITED
DEFAULT CPU_PER_SESSION 	  KERNEL 	UNLIMITED
DEFAULT CPU_PER_CALL 		  KERNEL 	UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL 	UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL 	  KERNEL 	UNLIMITED
DEFAULT IDLE_TIME 		  KERNEL 	UNLIMITED
DEFAULT CONNECT_TIME 		  KERNEL 	UNLIMITED
DEFAULT PRIVATE_SGA 		  KERNEL 	UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS 	  PASSWORD 	30
DEFAULT PASSWORD_LIFE_TIME 	  PASSWORD 	UNLIMITED
DEFAULT PASSWORD_REUSE_TIME 	  PASSWORD 	UNLIMITED
DEFAULT PASSWORD_REUSE_MAX 	  PASSWORD 	UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION  PASSWORD 	NULL
PROFILE RESOURCE_NAME 		  RESOURCE 	LIMIT
DEFAULT PASSWORD_LOCK_TIME        PASSWORD 	UNLIMITED
DEFAULT PASSWORD_GRACE_TIME 	  PASSWORD 	UNLIMITED

Ils permettent par exemple de limiter le nombre de sessions simultanées (très pratique en cas de développeurs trop enthousiastes !)SQL> CREATE PROFILE deux_sessions_max LIMIT
SESSIONS_PER_USER 2
SQL> ALTER USER dev1 PROFILE deux_sessions

Les utilisateurs prédéfinis d’Oraclel 10g

Essentiellement 2 catégories, les utilisateurs d’administration ou système :
- SYS : DBA et propriétaire du dictionnaire
- SYSTEM : DBA et propriétaire de qq vues système et outils
- SYSMAN : utilisateur de la console OEM ou GRID
- DBSNMP : utile pour Oracle AGent qui remonte des infos sur la base locale ? la console
- XDB, pour la BD XML
et les users/schémas de démo :
- le célèbre SCOTT et ses tables EMP et DEPT
- HR, schéma “ressources Humaine”, purement relation au niveau du modèle de données
- OE, schéma “gestion des commandes” au modèle relationnel / objet
- SH, schéma “ventes”, modèle relationnel en étoile
et des users génériques
PUBLIC et ANONYMOUS

La liste exhaustive des USERS prédéfinis est donnée par le SQL suivant (principales colonnes seulement):

SQL> SELECT username, account_status, lock_date, default_tablespace,
temporary_tablespace, profile
FROM DBA_USERS

USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE
——– ————– —————— ——————– ——-
SYS OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
DBSNMP OPEN SYSAUX TEMP MONITORING_PROFILE
SYSMAN OPEN SYSAUX TEMP DEFAULT
SCOTT OPEN USERS TEMP DEFAULT

note : il est vivement conseillé de se connecter au minimum dans SYS ou SYSTEM. On créera un compte avec le role ‘DBA’ pour l’administration.
Il est également conseillé de verrouiller les comptes non utilisés et de changer leur mot de passe par défaut car ils peuvent présenter des failles de sécurité.

Les méthodes d’authentification

Pour assurer la confidentialitté d’accès aux données des utilisateurs, on doit s’authentifier pour se connecter ? la base de données.
Il existe 3 formes différentes d’authentification : par mot de passe, externe ou globale.

1) authentification par mot de passe
C’est une authentification classique, et répandue qui utilise un mot de passe crypté, stocké localement dans la table des utilisateurs de la base.

SQL> SELECT username, password FROM dba_users;

USERNAME PASSWORD
——– —————-
SYS 4C1C01757062C16D
SYSTEM D4DF7931AB130E37
DD EXTERNAL
SCOTT F894844C34402B67

2) authentification externe

Oracle offre une possibilité de se connecter sans fournir explicitement le mot de passe du compte. Ceci ne veut pas dire que le compte n’est pas protégé, mais que la protection est déporté au niveau de l’OS, ou en d’autres termes que le SGBD ‘fait confiance’ au système d’authentification de l’OS qui le porte. Pour que ceci soit possible il faut

- que l’utilisateur, soit défini comme authentifié de manière externe,
- que le nom du compte aie pour suffixe le nom du compte de l’OS et pour préfixe un préfixe générique défini dans les paramètres d’initialisation de la base (init.ora ou spfile) par la variable OS_AUTHENT_PREFIX.
Par défaut OS_AUTHENT_PREFIX vaut ‘OPS$’.
- que l’utilisateur se connecte ? Oracle en ne fournissant ni nom ni mot de passe mais simplement le séparateur ‘/’.
exemple :
— creation du user par le DBA
SQL> CREATE USER OPS$DD IDENTIFIED EXTERNALLY
– on pourra ensuite (moyennant que l’utilisateur aie au moins
– un privilège ‘CREATE SESSION’…) se connecter en externe :
– connexion ? l’OS
login: DD
pwd : *******
– connexion externe dans le compte OS
$> sqlplus /
SQL> user OPS$DD connected…

note : l’authentification externe permet d’une certaine façon de sécuriser les scripts Shell ou d’une manière générale les batchs, car le mot de passe Oracle n’apparait plus en clair dans les scripts

3) authentification globale

… ? compléter…

Quelques exemples de commandes SQL

Autant que faire se peut on utilisera la console, mais ca peut dépanner de connaitre un minimum de SQL (ca peut aussi épater les filles…mais lequelles?)

SQL> CREATE USER TOTO IDENTIFIED BY TUTU;
– créé un user toto avec mot de passe tutu
SQL> DROP USER TOTO
– supprime icelui
SQL> ALTER USER tete IDENTIFIED BY tata
– on modifie le mot de passe
SQL> CREATE USER dev1 IDENTIFIED BY xxx
DEFAULT TABLESPACE hr
TEMPORARY TABLESPACE temp
QUOTA 100M ON hr
PROFILE profile_standard_de_ma_societe

SQL> GRANT CREATE SESSION to dev1;
SQL> CREATE ROLE role_developpeur_standard;
– ce role sera partagé par tous les développeurs
SQL> GRANT CREATE TABLE, CREATE VIEW, CREATE INDEX,
CREATE PROCEDURE, CREATE SEQUENCE TO role_developpeur_standard;
SQL> GRANT ROLE role_developpeur_standard TO dev1 WITH GRANT OPTION;
– on donne le droit de crééer des objets standards
SQL> GRANT CREATE TRIGGER TO dev1;
– on rajoute un droit spécifique ? DEV1…

Sécurité Oracle 10g: Les rôles

Divers, Sécurité Oracle 4 Commentaires »

Les rôles
———

Il n’existe pas de notion de groupes d’utilisateur sous Oracle, mais la notion de rôle, qui permet de nommer un

groupe de privilèges. On peut affecter un rôle ? un ou n utilisateurs, voir ? un rôle.

Roles prédéfinis
—————-

Il existe un certain nombre de rôles prédéfinis, fournis avec Oracle 10g. Voici une liste de ces rôles avec les

privilèges système qu’ils offrent :
CONNECT :
Se connecter ! équivallent du privilède système ‘CREATE SESSION’

Attention !!! en version 10gR1 et antérieures, ce rôle donnait beaucoup + de privilèges, ? savoir :
ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE,
CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW
RESOURCE :
créer des données avec des quotas sur tous les tablespaces ->
CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE,
CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE

DBA :
Tous les privilèges système avec ADMIN OPTION

EXP_FULL_DATABASE :
export complet ou incrémental ->
SELECT ANY TABLE, BACKUP ANY TABLE, EXECUTE ANY PROCEDURE, EXECUTE ANY TYPE, ADMINISTER RESOURCE MANAGER, and

INSERT, DELETE, and UPDATE on the tables SYS.INCVID, SYS.INCFIL, and SYS.INCEXP. Also the following roles:

EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE.

IMP_FULL_DATABASE :
import full + tous les privilèges système + role

DELETE_CATALOG_ROLE :
droit DELETE sur la table SYS.AUD$

EXECUTE_CATALOG_ROLE : privilège EXECUTE sur les objets du dictionnaire + HS_ADMIN_ROLE.

SELECT_CATALOG_ROLE : accès en consultation au dictionnaire + HS_ADMIN_ROLE.

RECOVERY_CATALOG_OWNER : privilèges pour le propriétaire du catalogue de restauration ->
CREATE SESSION, ALTER SESSION, CREATE SYNONYM, CREATE VIEW, CREATE DATABASE LINK,
CREATE TABLE, CREATE CLUSTER, CREATE SEQUENCE, CREATE TRIGGER, and CREATE PROCEDURE

HS_ADMIN_ROLE
protection des accès (SELECT et EXECUTE) au référentiel HS (Heterogeneous Services) data dictionary tables

(grants SELECT) and packages
AQ_ADMINISTRATOR_ROLE
privilèges d’administration de l’ Advance Queuing. Notamment : ENQUEUE ANY QUEUE, DEQUEUE ANY QUEUE, et MANAGE

ANY QUEUE + SELECT sur les tables AQ + EXECUTE sur les packages AQ.

roles applicatifs
—————–
Des roles définis pour les applicatifs, en fonction des besoins et ? minima !
Ainsi on peut créer un rôle qui donne des droits de consultation sur les tables d’un schéma :

– creation du role
SQL> create role consult_finance;
– affectation des privileges au role
SQL> grant select on budget to consult_finance;
SQL> grant select on fournisseur to consult_finance;
SQL> grant select on client to consult_finance;
– cession du role aux utilisateurs
SQL> grant consult_finance to user_finance_1;
SQL> grant consult_finance to user_finance_2;

Note : un user ne peut pas hériter de plus de ‘MAX_ENABLED_ROLES’ paramètre d’initialisation de la base, dont la valeur par défaut est 30 en 10g. CEla parait peu probable pour un applicatif classique (voire déraisonnable).
Dans le cas d’une console d’administration centrale (console 10g GRID ou server manager console). Le user d’administration cionnecté hérite de tous les droits qu’il crée…ce qui est beaucoup plus fréquent ! et la limite MAX_ENABLED_ROLES peut être facilement atteinte et … empêcher une connexion ? la console !
Informations sur les rôles dans le référentiel (dictionnaire) Oracle 10g
————————————————————————

DBA_ROLES
Tous les roles !
DBA_ROLE_PRIVS, USER_ROLE_PRIVS
roles données aux users et/ou aux roles
ROLE_ROLE_PRIVS
roles donnés aux roles
ROLE_SYS_PRIVS
privilèges systèmes donnés aux roles (accessible au user)
ROLE_TAB_PRIVS
privilèges objets donnés aux roles (accessible au user)
SESSION_PRIVS
privilèges de la session courante
SESSION_ROLES
roles de la session courante

Les roles et la console GRID 10g
———————————

La console donne par defaut le role CONNECT, cela ne pose pas de pb de sécurité sur une base 10g…mais si on s’en sert pour administrer une base 9i (avec l’agent qui va bien) le role CONNECT offre presque tous les privilèges de création/suppression de données dans le schéma.

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……

Problèmes classiques sur les droits : 0RA-00942 , ORA-01031

Divers, Sécurité Oracle pas de Commentaire »

————————————————–
– une vue de BLAKE sur une table de SCOTT
————————————————–
connect scott
(scott a les droits sur la table)
create view vscott as select * from emp

connect blake
select * from scott.vscott
*
ERROR at line 1:
ORA-00942: table or view does not exist

connect scott
grant select on emp to blake

…le droit sur la table sert pour accéder …a la table
connect blake
select * from scott.vscott
*
ERROR at line 1:
ORA-00942: table or view does not exist

…Le droit sur la vue suffit
connect scott
grant select on vscott to blake

connect blake
select * from scott.vscott
–> OK

———————————————————
—KING accede a une vue de BLAKE sur une table de SCOTT
———————————————————
connect SYSTEM
grant select on scott.emp to blake;
create view blake.vblake as select * from scott.emp
connect SYSTEM
SQL> grant select on blake.vblake to king;
Autorisation de privilèges (GRANT) acceptée.
connect KING
select * from blake.vblake
*
ERROR at line 1:
ORA-01031: insufficient privileges

Si on avait tenté de donner les droits en tant que BLAKE
cela aurait été plus clair :
connect blake
grant select on blake.vblake to king
*
ERROR at line 1:
ORA-01720: grant option does not exist for ‘SCOTT.EMP’

LA SOLUTION
connect SCOTT (ou SYSTEM)
grant select on scott.emp to blake WITH GRANT OPTION

Droits sur les objets

Divers, Sécurité Oracle pas de Commentaire »

Pour des raisons de sécurité évidentes, un utilisateur autre que DBA n’a aucun droit a priori….même pas celui de se connecter ? la base de données !

Droits implicites

Par contre, le créateur d’un objet (TABLE, VUE , INDEX, etc.) est son propriétaire,et a implicitement des droits sur cet objet. Il possède tous les droits sur son contenu : consultation, mises ? jour, mais aussi suppression complète de sa structure (le contenant).

– exemple

– on suppose que l’utilisateur DD a reçu le droit de se connecter et de creer des tables…
SQL> connect DD/DD
SQL> create table essai (n integer);
Table created
– droit d’insertion (mais aussi suppression et modification) implicite
SQL> insert into essai values (1);
1 ligne créée.
SQL> commit;
Validation effectuée.
– a fortiori droit de consultation
SQL> select * from essai;
N
———-
1
– suppression de la table et de son contenu
SQL> drop table essai;
Table supprimée.Par contre un autre utilisateur (s’il n’est pas DBA) n’a a priori aucun droit sur les tables des autres:

– exemple

– on suppose que DD possede une table T1…
SQL> connect toto/toto
SQL> select * from dd.t1;
select * from dd.t1
*
ERREUR ? la ligne 1 :
ORA-00942: table or view does not exist

Droits explicites - GRANT et REVOKE

GRANT et REVOKE permettent respectivement de donner ou de supprimer les droits explicites d’accès en lecture ou

mise ? jour ? un utilisateur particulier, pour un objet particulier.
C’est en général le propriétaire de l’objet peut donner des droits d’accès ? un autrer utilisateur.

– exemples:

SQL> GRANT SELECT ON TAB_CLIENTS TO MARTIN
SQL> GRANT UPDATE, INSERT ON TAB_CLIENTS TO DUPONTpar défaut un DBA ne peut pas donner des droits d’accès ? un objet qui ne lui appartient pas. Le propriétaire doit lui céder les drois avec un grant option (!?) :

SQL> connect system/xxxx
Connected.
SQL> grant select on intranet.annuaire to scott;
ERROR at line 1: ORA-01031: insufficient privilegesLe type de privilège dépend évidemment de l’objet sur lequel il s’applique. Ceci est résumé ici :

SELECT (uniquement !) -> les séquences

SELECT, INSERT, UPDATE, DELETE -> les tables, les vues, les vues matérialisées

REFERENCE (possibilité de créer une clé étrangère sur la table) -> les tables et les vues

EXECUTE -> les procédures

INDEX, ON COMMIT REFRESH, QUERY REWRITE -> les tables

Il est possible de donner un privilège avec le droit de transférer ce privilège ? d’autres (? utiliser avec parcimonie). Ceci se fait avec l’otion ‘GRANT’ du GRANT (!) :GRANT SELECT ON TAB_CLIENTS TO DUPONT WITH GRANT OPTION
(on suppose que TAB_CLIENTS est un synonyme public visible par tout le monde). Dupont, même s’il n’est pas propriétaire pourra alors ? son tour faire :
GRANT SELECT ON TAB_CLIENTS TO MARTIN

une erreur très répandue consiste ? référencer un objet (dont on n’est pas proprétaire et sur lequel il n’y pas de synonyme) par son nom, SANS le préfixer par le nom du propriétaire. exemple : SELECT * from EMP au lieu de

SELECT * FROM SCOTT.EMP
ceci donne une erreur d’accès du type

ERROR at line 1:
ORA-00942: table or view does not exist (?!)

même si on a les droits de lecture…

Les privilèges du DBA

Divers, Sécurité Oracle pas de Commentaire »

DBA et droits Oracle (privilèges et roles)

Un utilisateur Oracle (déclaré au sein de la base, ? distinguer de l’utilisateur au niveau OS) peut être DBA.
Il a tous les ‘privilèges système’ AU SEIN DE LA BASE, et le droit de les transmettre (ADMIN OPTION)
Grace ? quoi, il peut essentiellement :
* consulter et mettre ? jour (SELECT, UPDATE, INSERT, DELETE) toutes les données utilisateur de la base
* créer, modifier des structures de données utilisateur (CREATE, ALTER, DROP) n’importe ou (ANY TABLESPACE)
* gérer des utilisateurs et des droits (CREATE/DROP USER, GRANT, REVOKE)
* consulter la totalité du dictionnaire
* exécuter des ordres d’administration purs (CREATE DATABASE, DATAFILE, TABLESPACE)

Il y a 2 utilisateurs privilégiés prédéfinis, SYS et SYSTEM (dont les mots de passe sont définis ? la création de la base ou par ‘ORAPWD’)
Ils sont tous les 2 DBA, mais SYS est plus privilégié en ce sens qu’il est propriétaire des tables et vues du dictionnaire.

Il existe un ensemble de privilège (ROLE) prédéfini nommé ‘DBA’ qui donne les privilèges nécessaire ? un DBA.
Après avoir créé un utilisateur ‘normal’ il suffit de lui donner ce rôle pour en faire un DBA :
SQL> GRANT DBA TO

note : Il existe un autre rôle prédéfini, parmi quelques dizaines, qui est également intéressant c’est le role ‘SELECT_CATALOG_ROLE’. Il est souvent utilisé par des progiciels ou applicatifs utilisant Oracle pour récupérer des méta données.

Privilèges d’exploitation (SYSDBA et SYSOPER)

Certains ‘administrateurs’ : les opérateurs et techniciens d’exploitation, ou ‘exploitants’ pour faire court, n’ont pas forcément besoin d’un niveau de privilège DBA.

Les opérations concernés sont par exemple :

* les démarrage / arrêts,

* les sauvegardes / restaurations,

* la planification et l’exécution de batch

Oracle fournit 2 niveaux de privilèges, qui peuvent être assimilés ? des niveuax de connexion, qui satisfont ces besoins : les privilèges d’exploitation ‘SYSDBA’ et ‘SYSOPER’.
Comme tout accès privilégié il s’acquiert via un processus d’identification / authentification.

Authentification locale au niveau du système d’explotation

C’est une forme d’authentification externe, en ce sens que ce n’est pas Oracle qui contrôle la connexion grace ? son référentiel interne. On se connecte directement (via telnet ou ssh par exemple) au système qui héberge le serveur de données, puis ? la base locale, sans plus faire intervenir le réseau.
Ce type de connexion originale ne nécessite pas d’identifiant ni de mot de passe Oracle, mais d’être un utilisateur privilégié au niveau O.S.

note : un utilisateur quelconque, non privilégié de la base, peut aussi être défini avec une authentificatin externe, et se connecter localement avec une commande du type : sqlplus /

On peut dire que dans ce cas la sécrité est déportée au niveau O.S. et qu’Oracle accorde sa ‘confiance’ aux mécanismes d’identification / authentification de ce dernier.

exemples de connexion avec le client SQL standard :
connexion ‘normale’
$sqlplus scott/tiger
connexion avec authentification externe
$sqlplus / as sysdba
$sqlplus / as sysoper

Pour obtenir un ‘privilège’ d’exploitation il suffit d’appartenir au groupe utilsateur correspondant au niveau système :

privilège  gpe unix    groupe windows
SYSDBA     dba         ORA_DBA
SYSOPER    oper        ORA_OPER

Ces groupes sont créés lors de l’installation, et un administrateur système en ‘hérite’ automatiquement

note : Le ‘CONNECT INTERNAL’ des versions précédentes est définitivement obsolète et a été remplacé par le ‘CONNECT SYS AS SYSDBA. Parallèlement il n’est plus possible de se connecter SYS ‘tout court’ sans préciser ‘AS SYSDBA’.

Authentification distante au niveau du système d’explotation

Elle présente les mêmes caratéristiques que précédemment saus que la base est située sur une machine distate de la connexion système courante.

La syntaxe de connexion devient donc :
$ sqlplus /@nom_base_distante AS SYSDBA (ou SYSOPER)

Un paramètre d’initialisation de la base : REMOTE_OS_AUTJENTICATION=TRUE autorise cette fonctionnalité.

Note importante : il est vivement conseillé pour des raisons de sécurité d’invalité cette possibilité.

Authentification via fichier de mots de passe
Dans ce cas de figure, les privilèges seront controlés ? partir d’un fichier de mot de passe cryptés local.
exemple de création du fichier :
$ ORAPWD FILE=monfic PASSWORD=monpasse ENTRIES=100
avec
PASSWORD : le mot de passe de SYS
ENTRIES : le nb mas d’utilisateurs référencables dans le fichier.

on peut ensuite créer un utilisateur TOTO avec mot de passe TUTU et que le DBA lui donne le privilège oracle (et non pas système cette fois) nécessaire : SYSDBA ou SYSOPER :

$> sqlplus / AS SYSDBA
SQL> CREATE USER TOTO IDENTIFIED BY TUTU;
SQL> GRANT CREATE SESSION TO TOTO (qu’il aie le droit de se connecter quand même…)
$ GRANT SYSDBA TO TOTO (et lui donner le privilège d’exploitation qui va bien)

note : le paramètre d’initialisation REMOTE_LOGIN_PASSWORDFILE doit être ? EXCLUSIVE (c’est le défaut) pour pouvoir utiliser et modifier le password file

DBA et droits niveau OS (système d’exploitation)

La fonction de DBA, nécessite des privilèges au niveau Système d’exploitation :
- pour l’installation,
- la maintenance,
- la gestion et l’execution de batchs, de scripts (SQL ou shell),
- les sauvegardes / restauration

Sur Unix / Linux :
Il existe un user Unix nommé ‘oracle’ et un groupe associé nommé ‘dba’.
Tous les fichiers Oracle, appartiennent ? ‘utilisateur Oracle.

On peut (doit ?) crééer autant d’utilisateur Unix que de DBAs dans l’entreprise ; dba1, dba2, appartenant au groupe ‘dba’.Ceci permet d’éviter les recouvrements et d’avoir une meilleure tracabilité.
On évitera de travailler connecté en tant qu’utilisateur ‘oracle’ pour éviter toute erreur de manipulation des fichiers Oracle.

Note : les programmes et processus, qui constituent le coeur d’Oracle, s’executent en tant qu’oracle, et ont conséquemment les droits nécessaires pour écrire dans les fichiers de données, journaux, archives, etc.

Le DBA et le super utilisateur ‘root’ :
lors de l’installation, il est nécessaire d’écrire dans certains répertoires protégés du système (/etc par exemple) ou d’exécuter certaines taches privilégiées.
Cependant l’installation se fait bien en tant qu’Oracle, et l’installeur demande simplement le privilège root pendant la période nécessaire ? ces opérations.
Il execute 2 scripts autonomes root.sh et rootpre.sh, en tant que root.
Il retourne ensuite en mode ‘normal’.
En production les no de ports TCP/IP utilisés par Oracle 10g sont tous > 1024, et ne nécessitent donc pas de privilèges particuliers.

Sur Windows :

Le principe est plus simple. L’install se fait en général en tant qu’administrateur système.

l’erreur Oracle ORA-12154

Divers, Musée des erreurs 4 Commentaires »

ORA-12154 Le service n’a pu être résolu
ORA-12154 TNS could not resolve service name

mots clés

problême SQL*Net, problême Oracle Net service, erreur SQL*Net, erreur Oracle Net, erreur ORA-12154, erreur Oracle ORA-12154, solution ORA-12154, résolution ORA-12154

Description du problème

problème de résolution de nom de service SQL*Net ou Oracle Net. En clair le nom de la base sur le réseau ne peut pas être traduit (résolu).

Tests

1) vérifier que la base est active sur le serveur, indépendamment du réseau :
- Se connecter directement au serveur
sous Unix / Linux : telnet ou SSH ,
sous Windows : prise de main a distance ou ouverture d’une session directe sur le serveur
On vérifie et positionne le cas échéant l’environnement Oracle de notre session sur le serveur :
- affectation du ORACLE_HOME (répertoire d’installation d’Oracle)
- ORACLE_SID (identifiant local de la base ) ? la bonne valeur,
sous Unix / Linux : export ORACLE_SID = nom_de_la_base
sous Windows : modifier la variable ORACLE_SID, sous DOS, avec la commande SET ORACLE_SID = nom_de_la_base
ou modifier la base de registre avec regedit (software \ oracle \ ORACLE_HOME et ORACLE_SID) avec la bonne valeur
Puis on tente une connexion locale dans un compte connu
cd ORACLE_HOME
cd bin
sqlplus scott/tiger ou sqlplus system/mot_de_passe_system
2) vérifier que le serveur SQLNET est actif
cd ORACLE_HOME
cd bin
lsnrctl stat
3) verifier la méthode de resolution de nom
A PARTIR DU POSTE CLIENT ! : tnsping nom_de_service
Cette commande indique quelle méthode de résolution est utilisée :
- Implicite avec EZCONNECT
- Locale, avec un fichier de traduction locale : ORACLE_HOME/network/admin/tnsnames.ora
- Distante avec un serveur Oracle Names ou LDAP ou DNS
On peut également le vérifier dans le fichier ORACLE_HOME/network/admin/sqlnet.ora ou sont indiquées dans la variable DIRECTORY_PATH, les méthodes de résolution utilisées et dans quel ordre

Résolution de l’erreur ORA-12154

cas 2 : si le serveur SQL*Net / Oracle*Net est inactif, le démarrer :
dans ORACLE_HOME/bin lancer la commande lsnrctl start
remarque : par défaut le serveur démarre sur le port 1521
cas 3 :
solution universelle : forcer la résolution de nom en local :
dans le fichier ORACLE_HOME/network/admin/sqlnet.ora on met la ligne DIRECTORY_PATH= (TNSNAMES)
puis on crée un fichier ORACLE_HOME/network/admin/tnsnames.ora avec la bonne définition du service, calqué sur celle trouvée dans le fichier tnsnames.ora du répertoire SAMPLE.
Il faudra préciser : le nom de la machine serveur (avec son domaine) ou son @IP, le no de port : 1521 et le nom de service Oracle déclaré sur le serveur.
Remarque : le nom de service réseau est souvent pour des raisons de simplicité,identique au nom de service sur le serveur
Solution adaptée :
Attention !!!! le nom se service sur le serveur doit être exactement identique ? celui précisé dans la résolution de nom (indiqué par le tnsping). On peut le vérifier sur le serveur dans le fichier ORACLE_HOME/network/admin/listener.ora. Notamment si le client cherche une base qui s’appelle TEST.mondomaine.fr on doit avoir dans le listener.ora du serveur également TEST.mondomaine.fr , au caractère près.
Resolution locale :
Idem précédemment : dans le fichier ORACLE_HOME/network/admin/sqlnet.ora on met la ligne DIRECTORY_PATH= (TNSNAMES)
puis on crée un fichier ORACLE_HOME/network/admin/tnsnames.ora avec la bonne définition du service, calqué sur celle trouvée dans le fichier tnsnames.ora du répertoire SAMPLE.
Il faudra préciser : le nom de la machine serveur (avec son domaine) ou son @IP, le no de port : 1521 et le nom de service Oracle déclaré sur le serveur.
Résolution LDAP :
**************** ? compléter
Résolution DNS :
dans le fichier ORACLE_HOME/network/admin/sqlnet.ora on met la ligne DIRECTORY_PATH= (HOSTNAME)
et l’on vérifie que le nom de la base est bien résolu par le serveur DNS : ping nom_base.
En cas de problème de DNS, on peut faire appel ? un fichier ‘hosts’ local pour la résolution.
Il se trouve dans /etc/hosts sur Unix et dans WINDOWS\SYSTEM32\DRIVERS\ETC \HOSTS

L’erreur ORA-1034

Divers, Musée des erreurs pas de Commentaire »

ORA-1034 Base Oracle non disponible
ORA-1034 Oracle not available

mots clés

ORA-1034, Oracle 0RA-1034 error, erreur Oracle ORA-1034, erreur Oracle 1034, connexion oracle impossible, oracle non disponible, impossible de se connecter, environnement Oracle, ORACLE_SID, startup,

Description du problème

C’est un problème de disponibilité de la base Oracle ? partir d’un client. Le client peut être un programme utilisateur, une application de gestion, ou un client Oracle : SQL*Plus, export (exp) , import (imp), SQL*Loader, etc.
Concrètement il est impossible de se connecter ? la base….
Ce n’est pas un problème de connexion réseau ou SQL*Net, ou Oracle Net Services, ceux ci déclenchent des erreurs spécifiques (voir plus loin).
A preuve cette erreur peut survenir même si l’on est connecté directement sur le serveur hébergeant la base Oracle, sans utiliser aucune couche réseau.
Cette erreur peut en général avoir 2 causes : soit la base Oracle n’est pas démarrée, soit elle est incorrectement identifiée et le client ne la trouve pas !

Tests

remarque : pour effectuer ces tests on doit au moins savoir quel est le nom de la base (l’identifiant) que l’on veut atteindre
- Se connecter directement au serveur
sous Unix / Linux : telnet ou SSH ,
sous Windows : prise de main a distance ou ouverture d’une session directe sur le serveur
- Vérifier que la base cible est démarrée
sous Unix / Linux : ps -ef|grep oracle et rechercher les process de fond standards d’Oracle (au moins dbwr, lgwr, smon, pmon) suffixés par le nom de la base.
sous Windows : ? partir de panneau de configuration, vérifier dans la liste des services, qu’il existe au moins un service qui s’appelle ‘oracle_NOM_DE_LA_BASE et qu’il est démarré.

Diagnostic

cas 1 : Si la base est démarrée, alors c’est qu’elle est mal identifiée par le client, et que le problème est au niveau de l’environnement du système d’exploitation client
cas 2 : Si la base n’est pas démarrée …il faut la démarrer

Résolution de l’erreur ORA-1034

cas 1 : modififier l’identification de la base.
sous Unix / Linux : export ORACLE_SID = nom_de_la_base
sous Windows : modifier la variable ORACLE_SID, sous DOS, avec la commande SET ORACLE_SID = nom_de_la_base
ou modifier la base de registre avec regedit (software \ oracle \ ORACLE_HOME \ ORACLE_SID) avec la bonne valeur
cas 2 :
vérifier que l’environnement est correct (ORACLE_SID) sinon le positionner commeau paragraphe précédent
arreter la base par sécurité (il se pourrait qu’elle soit dans un état intermédiaire, et que tous les process ne soient pa démarrés)
puis la redémarrer en utilisant sqlplus :
$ sqlplus /nolog
SQL connect sys as sysdba
SQL shutdown abort
SQL startup
vérifier que les process ou le service démarrent correctement

Introduction PHP / MySQL

Divers pas de Commentaire »

A l’instar d’Oracle , MySQL peut être accédée grace ? 2 extensions PHP différentes :

* l’extension standard ‘mysql‘ (ensemble de fonctions préfixées par mysql_)
* l’extension améliorée ou Improved MySQL extension ‘mysqli‘ (ensemble de fonctions préfixées par mysqli_)

l’extension standard ‘mysql’

Cette extension souvent associé ? la version basique du moteur MySQL, utilise 2 ressources principales :

* un identifiant de connexion
* un ensemble de lignes résultats (si le SQL est un SELECT bien sûr) : le result set

Les ordres SQL sont exécutés directement sans phase de préparation (parsing, bind), et non pas comme dans le cas général d’un accès aux BDs via un langage de script.

il n’y a pas non plus de gestion explicite de transaction (commit, rollback) réservée ? la version PRO. Les mises ? jour seront par défaut AUTOCOMMITées

Voici d’après le site officiel PHP, la liste complètes des fonctions de l’extension ‘mysql’ en PHP5

mysql_affected_rows — Retourne le nombre de lignes affectées lors de la dernière opération MySQL
mysql_change_user — Change le nom de l’utilisateur de la connexion active
mysql_client_encoding — Retourne le nom du jeu de caractères utilisé par le client MySQL
mysql_close — Ferme la connexion MySQL
mysql_connect — Ouvre une connexion ? un serveur MySQL
mysql_create_db — Crée une base de données MySQL
mysql_data_seek — Déplace le pointeur interne de résultat MySQL
mysql_db_name — Lit les noms des bases de données
mysql_db_query — Envoie une requête MySQL ? un serveur MySQL
mysql_drop_db — Efface une base de données MySQL
mysql_errno — Retourne le numéro d’erreur de la dernière commande MySQL
mysql_error — Retourne le texte associé avec l’erreur générée lors de la dernière requête
mysql_escape_string — Protège une chaîne pour la passer ? mysql_query
mysql_fetch_array — Retourne une ligne de résultat MySQL sous la forme d’un tableau associatif, d’un tableau indexé, ou les deuxmysql_fetch_assoc — Lit une ligne de résultat MySQL dans un tableau associatif
mysql_fetch_field — Retourne les données enregistrées dans une colonne MySQL sous forme d’objet
mysql_fetch_lengths — Retourne la taille de chaque colonne d’une ligne de résultat MySQL
mysql_fetch_object — Retourne une ligne de résultat MySQL sous la forme d’un objet
mysql_fetch_row — Retourne une ligne de résultat MySQL sous la forme d’un tableau
mysql_field_flags — Retourne des détails sur une colonne MySQL
mysql_field_len — Retourne la taille d’un champ de résultat MySQL
mysql_field_name — Retourne le nom d’une colonne dans un résultat MySQL
mysql_field_seek — Déplace le pointeur de résultat vers une position donnée
mysql_field_table — Retourne le nom de la table MySQL où se trouve une colonne
mysql_field_type — Retourne le type d’une colonne MySQL spécifique
mysql_free_result — Libère le résultat de la mémoire
mysql_get_client_info — Lit les informations sur le client MySQL
mysql_get_host_info — Lit les informations sur l’hôte MySQL
mysql_get_proto_info — Lit les informations sur le protocole MySQL
mysql_get_server_info — Lit les informations sur le serveur MySQL
mysql_info — Lit des informations ? propos de la dernière requête MySQL
mysql_insert_id — Retourne l’identifiant généré par la dernière requête INSERT MySQL
mysql_list_dbs — Liste les bases de données disponibles sur le serveur MySQL
mysql_list_fields — Liste les champs d’une table MySQL
mysql_list_processes — Liste les processus MySQL
mysql_list_tables — Liste les tables d’une base de données MySQL
mysql_num_fields — Retourne le nombre de champs d’un résultat MySQL
mysql_num_rows — Retourne le nombre de lignes d’un résultat MySQL
mysql_pconnect — Ouvre une connexion persistante ? un serveur MySQL
mysql_ping — Pingue le serveur MySQL, et s’y reconnecte au besoin
mysql_query — Envoi une requête ? un serveur MySQL
mysql_real_escape_string — Protège les caractères spéciaux d’une commande SQL
mysql_result — Retourne un champ d’un résultat MySQL
mysql_select_db — Sélectionne une base de données MySQL
mysql_stat — Retourne le statut courant du serveur MySQL
mysql_tablename — Lit le nom de la table qui contient un champ
mysql_thread_id — Retourne l’identifiant du thread MySQL courant
mysql_unbuffered_query — Exécute une requête SQL sans mobiliser les résultats MySQL

Afin d’y voir plus clair, nous allons tenter de les regrouper par types d’opération ou de fonctionnalités
Taxinomie des fonctions MySQL de PHP
connexion mysql_connect, mysql_connect, mysql_close, mysql_ping, (my_sql_select_db, mysql_change_user)
gestion d’erreurs mysql_error, mysql_errno
méta données DB

mysql_field_flags, mysql_field_name, mysql_field_len, mysql_field_table, mysql_field_type, mysql_list_fields, mysql_info, mysql_insert_id,

mysql__num_rows

mysql_list_tables, mysql_table_name
méta données Serveur

mysql_client_encoding, mysql_get_client_info, mysql_get_host_info, mysql_get_proto_info, mysql_get_server_info,

mysql_list_dbs, mysql_ list_processes, mysql_stat, mysql_thread_id
lecture lignes mysql_fetch_array, mysql_fetch_result_row, mysql_fetch_assoc, mysql_fetch_field, mysql_fetch_lengths, mysql_fetch_object, mysql_fetch_row,
execution SQL mysql_drop_db, mysql_db_query, mysql_query, mysql_unbuffered_query

l’extension améliorée ‘mysqli’

Cette extension souvent associé ? la version PRO du moteur MySQL, utilise 3 ressources principales :

* un identifiant de connexion
* un ordre SQL ‘préparé’ explicitement
* un ensemble de lignes résultats (si le SQL est un SELECT bien sûr) : le result set

Les principales fonctionnalités supplémentaires sont les suivantes :

* phase d’analyse préalable des ordres SQL (parsing) que l’on peut éviter de ré exécuter lors d’ordres SQL récurrents ou simililaires
* gestion de pramètres d’entrées associées ? des variables PHP : bind variables
* gestion de transaction (commit, rollback)
* accès direct dans le résult set
* gestion d’erreur et debugging amélioré

PHP5 étant fortement orienté objet, 3 classes prédéfinies sont fournies, qui peuvent agréablement pour les adeptes de la P.O.O se substituer aux fonctions classiques. :

* mysqli : une classe de connexion
* mysqli_statement : une classe pour la gestion de ordres SQL (éventueelemnt ‘préparés’)
* mysqli_result_set : une classe pour la gestion du résultat (lignes ramenées par le SELECT)

La liste complète des fonction de l’extension mysqli ainsi que la description détaillée des méthodes des classes prédéfinies est accessible dans la doc officielle de mysqli sur PHP.net

pour finir, INFOS INTERESSANTES SUR LE SUJET sur le site PHP MYSQL TUTORIAL