Tables du schema SH ( Sales History )

Script pour creer le SCHEMA SH de demo : ==> télécharger ICI

Prise en main de la base : configuration et architecture générale
Utilisation basique d’Enterprise Manager Console (EM)
(http://serveur_de_cours:no_port/em)

Ressources EM utilisées : Page d’accueil et
onglet ‘Administration’, rubriques ’stockage’, ‘configuration’, ‘objets de la base’ et ‘utilisateurs’

Connectez vous dba1/dba1 et répondez aux sections suivantes :

1) quelle version d’Oracle est exactement utilisée ?
quelle est la configuration du serveur hote (RAM, disque, CPU, @IP)
quelle est la taille mémoire globale (SGA) utilisée par Oracle,
et quelle est la taille réservée au cache de tampon des données ?
2) quelle taille fait en gros la base physique et ou sont implantés ses fichiers ?
3) les fichiers de controle sont il multiplexés ? Si oui qu’en pensez vous ?
4) les fichiers journaux sont ils multiplexés ?
5) combien y a t il de tablespaces purement ‘utilisateur’ ?
6) en recherchant les paramètres d’initialisation ‘NLS’ (National Language Support) de la base,précisez dans quelle langue est configuré le serveur ?

Le but est de créer une nouvelle instance qui soit une copie d’une base existante en terme de paramétrage, afin de créer ensuite une base physique de production.

1) localiser et visualiser votre fichier SPFILE

2) créer un fichiet init<SID>.ora (PFILE) à partir de votre SPFILE

3) renommer le fichier en init<SID>_PRO.ora , supprimer tous les paramètres physiques, renommer le DB_NAME en <SID>_PRO

4) positionner le ORACLE_SID et lancer la nouvelle instance UNIQUEMENT (non montée, non ouverte)

Le but de cet exercice est de configurer l’accès à une base distante à partir de notre serveur de données.
On utilisera pour ce faire Enterprise manager et la résolution locale de nom.
En résumé notre seveur deviendra le ‘client’ d’une nouvelle base de données, et l’on constituera ainsi une architecture minimale à 3 poles (3-tiers).
Chaque stagiaire devra créer un nouveau nom de service réseau TESTn, qui pointe sur la base ORCL du serveur de cours.

Etapes :
- lancer la console Enterprise Manager (http://serveur_de_cours:no_port/em)
- vérifier que le LISTENER est actif
- cliquer sur le lien ‘Administration Service réseau’ en bas à gauche
- choisissez ‘Résolution locale des noms’ dans la liste déroulante ‘Administrer’
- créer un nouveau nom de service
- puis une nouvelle adresse (avec les bons paramètres TCP/IP) sur laquelle va pointer ce nom de service
- tester une connexion scott/tiger sur TESTn
- quels fichiers ont été modifiés sur le serveur ?

0) télécharger l’instant client 11gR2 lite + sqlplus
(rendez vous sur otn.oracle.com rubrique ‘téléchargement’, version 11gR2
1) creer un répertoire d’accueil genre c:oracle_iclient_11gr2
2) dézipper tous les fichier dans CE répertoire
3) positionner (étendre) le PATH dans vos variable d’environnement SYSTEME pour pointer sur LE répertoire qui contient SQL*Plus
4) tester une connexion EZconnect avec SQL*Plus vers un serveur distant

par exemple : nom_logique_du_serveur ou @IP : 10.28.18.33
port 1521
service : XE
user/passwd : HR/HR

(Vérifier que le pare-feu du serveur ne bloque pas les entrées sur le port 1521)

5) 5) faire en sorte de résoudre un nom logique de connexion (Alias Oracle*Net) pour se connecter simplement avec
sqlplus hr/hr@HR

créer un TNSNAMES.ORA sur le client avec les bonnes informations
(utiliser un fichier SAMPLE)
faire en sorte que Oracle retrouve ce fichier avec la variable d’environnement
TNS_ADMIN

Evolution des TBS
—————————
Visualisez les infos sur le TBS ‘USERS’
Quel taille fait il? Quel est son pourcentage de remplissage actuel ?
Quelles sont ses règles d’extension ? (méthode auto ou non, taille de l’extension, limite)

Création (pseudo) manuelle de TBS
———————————————
En vous aidant de la console Enterprise Manager pour afficher l’ordre SQL qui va bien.
Connectez vous DB1/DBA1.
Préparez la création d’un espace disque logique ‘TBSn’ permanent,
géré localement, contenant un fichier ‘tbsn_1.dbf’
de 50K, auto extensible jusqu’a 150K par paquet de 50K.

A l’aide la console EM
- creer un utilisateur USERn
avec les caractéristiques suivantes :
Espace disque logique par defaut TBSn, TBS temporaire TEMP, quota sur TBSn 50K
et les privileges minimum pour pouvoir créer des tables
- verifier la possibilité de création d’une table et les caractéristiques son implantation
en creant une table sous i*SQLPLUS
: CREATE TABLE nom_table (nom_col type , nom_col type, …)

Pour la suite des TPs donner le role DBA a USERn

Creation de role de type Infocentre et developpeur :
- créer un user USERn_INT (pous faire des interrogations uniquement)
- créer un role CONSn qui ait le droit de consulter la table EMP et DEPT de SCOTT.
- affecter ce role à USERn_INT et tester
- creer un role de type developpeur DEVn
- doit-on lui donner le role ‘CONNECT’ ? ‘DBA’ ?
- quels sont les privilèges système adéquats pour qu’il puisse travailler…les affecter au role
- affecter le role à USERn et tester la création d’une procédure stockée vide par exemple

Verification des droits d’acces du role SELECT_CATALOG_ROLE
(acces complet en lecture aux tables du dictionnaire appartenant a SYS) :

- donner ‘SELECT_CATALOG_ROLE’ à USERn:
- vérifier qu’il existe une table ‘LINK$’ dans le schema ‘SYS’
qui documente les accès reseau a une autre base
- vérifier les droits d’accès aux objets du role ‘SELECT_CATALOG_ROLE’
- pouvez vous accéder au dictionnaire à partir de ce USER ? à la table ‘LINK$’ ?
- à votre avis pourquoi ?

-1) lister les comptes / schémas préinstallés sur Oracle (date de création ?). Vérifier leur statut : ouvert ou verrouillé
- 2) lister les users ayant le rôle DBA
- 3) lister les users ayant un privilège système de type ‘ANY’
- 4) lister les users ayant le privilège execute sur les objets stockés de SYS
- 5) vérifier les paramètres sensibles de sécurité de la base : notamment REMOTE_OS_AUTHENT, UTL_FILE, AUDIT_TRAIL
- 6) Vérifier les permissions des fichiers du noyau Oracle et de la DB
- 7) vérifier les utilisateurs unix ou WIndows appartenant au groupe DBA

TP ORACLE : mise en place de comptes , utilisateurs et schémas sécurisés

Documentation utile :
Le cours : http://coursdba.estsurinternet.com
Le manuel de référence SQL 10g ou 11g disponible dur http://tahiti.oracle.com

Nous allons créer et configurer 2 instances de BDs, une de développement et une de production . Chacune pourra héberger des dizaines voire des centaines d’application.
Pour ce TP nous allons ‘développer’ et mettre en production une application de gestion des ressources humaines (RH). Ces bases devront présenter des garanties de sécurité minimales…

1) si ce n’est pas fait, terminez l’installation d’ Oracle 10g ou 11g sur votre machine et créez une BD par défaut : ‘ORCL’.
Remarque : si vous NE POUVEZ PAS FAIRE AUTREMENT et souhaitez travailler sur la base du serveur EXIA, suffixez tous vos objets (tablespaces, fichiers, table, user par un numero distinct pour éviter les conflits).

2) tester une connexion avec la console EM et i*SQLPlus

3) avec la console, configurer ORCL pour en faire une base de développement
et mémoriser toutes les opérations dans un script SQL pour pouvoir le rejouer sur une autre base le cas échéant (il existe sur la plupart des écrans un bouton ‘Afficher le SQL’ sinon voir la doc) :
- créer deux tablespace RHDEV_DAT et RHDEV_IDX de 10 MO chacun, autoextensibles jusqu’a 50MO par paquet de 10 MO
- créer un user RHDEV, sans droit
et avec quota illimité sur les 2 Tbs et RHDEV_DAT comme tbs par défaut
- créer un role ROLE_DEV permettant de se connecter (créer des sessions), créer des tables, des vues, des index, packages, procs et fonctions, des synonymes, des database links
- donner ce role a RHDEV

4) créer les objets initiaux dans le schéma RHDEV:
- améliorer le script SQL donné en annexe (le modèle relationnel est loin d’etre satisfaisant),
pour que EMP.EMPNO et DEPT.DEPTNO soient des PKs, et que EMP.DEPTNO soit une FK sur DEPT.DEPTNO
- Une montée en charge importante étant prévisible, créer des index sur EMP et DEPT, sachant que le recherches porteront souvent sur le nom de l’employé et son no de département (les index…dans le TBS d’index…!).
- se connecter RHDEV et lancer le script de création modifié, des tables du schema, puis vérifier le remplissage des tbs a la console
- en vous appuyant sur le code suivant, sous SQLDevelopper, créer une fonction stockée qui renvoie le salaire moyen d’un département dont le no est passé en paramètre
sal_moyen NUMBER;
BEGIN
SELECT AVG(sal) INTO sal_moyen FROM emp
WHERE deptno = 10;
RETURN sal_moyen;
END;
/
tester la fonction en faisant un SELECT f_sal_moyen(20) FROM DUAL;

5) tester la montée en charge…
on va remplir artificiellement la table EMP, en faisant n fois : INSERT INTO emp SELECT * FROM emp; !
résoudre les pbs éventuels de contrainte d’intégrité…
vérifier avec la console le remplissage du tbs RH_DAT

Vos developpements sont terminés. Vous pouvez verrouiller le compte, arrêter “l’instance” ORCL et passer en production…

6) créer une base de Production avec l’utilitaire ‘assistant de création/configuration de base de données’ (ou dbca sur Linux)

- créer 2 tbs RH_DAT et RH_IDX comme les précedent
- créer un user RH propriétaire des données
- créer les données
- créer un role RH_CONS avce les droits de consulation de RH
- créer un role RH_UTIL avec droits de mise a jour sur RH
- créer un user JMARTIN qui ne peut que consulter les données
- vérifiez ! puis le cas échéant créez des synonymes privés pour JMARTIN sur les objets de RH
- créer un user JDUPONT qui peut faire des consultations et des mises à jour, et executer la fonction ‘f_sal_moyen’
- tester
- verrouiller le compte RH

7) donnez vos commentaires sur ces créations, quel était le but de ces différentes actions ?

respirez…soufflez.

—————————————————————————-
Annexe : script de creation des tables de demo…
—————————————————————————-
–
– Copyright (c) Oracle Corporation 1988, 1999. All Rights Reserved.
–
– NAME
– demobld.sql
–
– DESCRIPTION
– This script creates the SQL*Plus demonstration tables in the
– current schema. It should be STARTed by each user wishing to
– access the tables. To remove the tables use the demodrop.sql
– script.
–
– USAGE
– SQL> START demobld.sql
–
–

SET TERMOUT ON
PROMPT Building demonstration tables. Please wait.
SET TERMOUT OFF

DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;

CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
(7369, ‘SMITH’, ‘CLERK’, 7902,
TO_DATE(’17-DEC-1980′, ‘DD-MON-YYYY’), 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, ‘ALLEN’, ‘SALESMAN’, 7698,
TO_DATE(’20-FEB-1981′, ‘DD-MON-YYYY’), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, ‘WARD’, ‘SALESMAN’, 7698,
TO_DATE(’22-FEB-1981′, ‘DD-MON-YYYY’), 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, ‘JONES’, ‘MANAGER’, 7839,
TO_DATE(’2-APR-1981′, ‘DD-MON-YYYY’), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, ‘MARTIN’, ‘SALESMAN’, 7698,
TO_DATE(’28-SEP-1981′, ‘DD-MON-YYYY’), 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, ‘BLAKE’, ‘MANAGER’, 7839,
TO_DATE(’1-MAY-1981′, ‘DD-MON-YYYY’), 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, ‘CLARK’, ‘MANAGER’, 7839,
TO_DATE(’9-JUN-1981′, ‘DD-MON-YYYY’), 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, ‘SCOTT’, ‘ANALYST’, 7566,
TO_DATE(’09-DEC-1982′, ‘DD-MON-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, ‘KING’, ‘PRESIDENT’, NULL,
TO_DATE(’17-NOV-1981′, ‘DD-MON-YYYY’), 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, ‘TURNER’, ‘SALESMAN’, 7698,
TO_DATE(’8-SEP-1981′, ‘DD-MON-YYYY’), 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, ‘ADAMS’, ‘CLERK’, 7788,
TO_DATE(’12-JAN-1983′, ‘DD-MON-YYYY’), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, ‘JAMES’, ‘CLERK’, 7698,
TO_DATE(’3-DEC-1981′, ‘DD-MON-YYYY’), 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, ‘FORD’, ‘ANALYST’, 7566,
TO_DATE(’3-DEC-1981′, ‘DD-MON-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, ‘MILLER’, ‘CLERK’, 7782,
TO_DATE(’23-JAN-1982′, ‘DD-MON-YYYY’), 1300, NULL, 10);

CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, ‘ACCOUNTING’, ‘NEW YORK’);
INSERT INTO DEPT VALUES (20, ‘RESEARCH’, ‘DALLAS’);
INSERT INTO DEPT VALUES (30, ‘SALES’, ‘CHICAGO’);
INSERT INTO DEPT VALUES (40, ‘OPERATIONS’, ‘BOSTON’);

CREATE TABLE BONUS
(ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER,
COMM NUMBER);

CREATE TABLE SALGRADE
(GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

CREATE TABLE DUMMY
(DUMMY NUMBER);

INSERT INTO DUMMY VALUES (0);

COMMIT;

SET TERMOUT ON
PROMPT Demonstration table build is complete.

EXIT

But : se servir de DataPump pour faire une duplication / sauvegarde de schéma (un compte utilisateur et toutes ses données).
Pour des raisons pratiques, le schema source et le schema cible seront dans la même instance de BD, mais on aurait pu faire exactement la même chose pour transférer les données d’un schéma vers une autre base…

1) créer une directory TMP_PUMP pointant sur un répertoire temporaire de votre disque  (C:…temp ou /tmp). Donner les droits de lecture / écriture à system.
rappel: les DIRECTORY appartiennent TOUJOURS à SYS, il faut donc être SYS pour pouvoir donner les droits sur la directory ….créé par SYSTEM (!?)

2) avec la console, dupliquer l’utilisateur HR en HR_SAVE (qui sera vide mais aura les même privlèges que HR).
Soit on utilisera la fonction ‘CREER COMME’ de la console : onglet / administration / utilisateur / rechercher HR / creer comme…
Soit on fera un CREATE USER en SQL puis un GRANT CONNECT , RESOURCE (droits minimum pour pouvoir créer des objets…)

3) exporter le schema HR dans un fichier, avec datapump

4) importer ce fichier dans HR_SAVE

5) vérifier les logs

1) connectez vous SYSTEM ou DBA et vérifiez les prérequis de configuration pour pouvoir faire du FLASHBACK.

• existence d’un UNDO tablespace de taille raisonnable
• rétention de 30 minutes
• UNDO_MANAGEMENT en AUTO
• droit de FLASHBACK pour SCOTT sur la table EMP

Vous pouvez utiliser la console ou la commande SHOW PARAMETER  sous SQLPlus et aussi la vue DBA_TABLESPACES.
Le cas échéant positionnez ces parametres.

2) Connectez vous SCOTT, puis  supprimez la ligne concernant ‘KING’ dans la table SCOTT.EMP
Commitez.

3) Essayez de rattraper votre erreur en vérifiant l’état des données il y a 2 minutes, puis en réinsérant la ligne disparue

Recupération de table grace à la recovery Area

Vérifier que la poubelle est active au niveau instance
et l’activer si nécessaire
Creer une table T1 avec une colonne CHAR, inserer une ou 2 lignes
Creer deux tables T2 et T3 avec une colonne INTEGER
Supprimer la table T1 et T2 logiquement
Supprimer la table T3  physiquement
Vérifier le contenu de la poubelle
Récuperer la table T1
Purger si nécessaire la corbeille

0) creer si necessaire le schema SH avec les scripts fournis

1) sous SQL+ lancer la requete suivante :
SQL>
SELECT p.prod_subcategory, t.calendar_month_desc, c.cust_city,
  2	    SUM(s.amount_sold) AS sum_amount_sold,
  3	    COUNT(s.amount_sold) AS count_amount_sold
  4  FROM   sales s, products p, times t, customers c, countries
  5  WHERE  s.time_id=t.time_id
  6  AND    s.prod_id=p.prod_id
  7  AND    s.cust_id=c.cust_id
  8  and t.calendar_year=1999
  9  and country_iso_code='US'
 10* GROUP BY p.prod_subcategory, t.calendar_month_desc, c.cust_city

2) Que fait elle ? Verifier son temps d'execution : 

3) afficher le PLAN d'execution

4) creer une VM correspondante

5) Verifier le temps d'execution ET LE PLAN DEXECUTION sur la VM

6) verifier si le QUERY REWRITE est actif sur la base

7) créer une nouvelle VM comme la precedente mais avec l'option QUERY REWRITE

  re tester le SELECT de depart...et admirez