oracle db 10g - aide informatique n°1 –aide.informatique1.fr/wp-content/uploads/2016/01/24... ·...

Olivier DEHECQ – http://aide.informatique1.fr

2016

Oracle DB 10g ADMINISTRATION UNIX

OLIVIER DEHECQ

DEHECQ Olivier – http://aide.informatique1.fr 2

Table des matières Signalétique .................................................................................................................................................... 3

1 Généralités (RAPPELS) .......................................................................................................................... 4

2 La création d’une BD en utilisant les scripts ........................................................................................ 5

3 Les imports/exports .............................................................................................................................. 7

4 Optimisation .........................................................................................................................................11

5 Oracle sous Linux .................................................................................................................................17

6 Vues Matérialisées ...............................................................................................................................19

7 Le monitoring Index ............................................................................................................................21

8 Le partitionnement ..............................................................................................................................23

9 Datapump (export/import)..................................................................................................................25

10 La Haute Disponibilité sur Oracle .......................................................................................................30

11 Déroulé complet de l’installation d’une Oracle 11g sur Linux ..........................................................31

12 Sauvegarde / restauration (p288) ......................................................................................................32


Signalétique Nota, astuce :

Contient une partie serveur web qui traite les réponses statiques.

Important, à retenir :

Ceci est une chose importante

Commande MS-DOS C:\> c:\tomcat5.5\bin\startup.bat

Commande UNIX # /tomcat5.5/bin/startup.sh

Commande SQL # /tomcat5.5/bin/startup.sh

Chemin de fichier, dossier, emplacement sur le disque Fichier web.xml

Exemple de contenu de document

<Host name="localhost" appBase="webapps" unpackWARs="true" autoDeploy="true" />

Contenu du fichier web.xml

<welcome-file>index.html</welcome-file>

Contenu du fichier server.xml

port "8080" port d’écoute du connecteur

Autre contenu de fichier :

<role rolename="RUserHelloWorld"/>

Spécifique aux documents xml :

Balise

Nom de propriété

Valeur

Commentaire


1 Généralités (RAPPELS) Oracle est un produit multiplateforme

Mode standard (max 4proc) ;

Edition entreprise (de tout) ;

Personnel (mono licence, utile pour le développement)

Liberté de paramétrages, sécurité, stratégie de sauvegarde, import/export, administration centralisée,

serveur apache intégré.

Rôle de l’administrateur Oracle :

Organiser techniquement, création de BD, redimensionnement des objets, sécurité des accès,

cohérence de la base de données (procédures reprises)

Outils d’administration :

SQL*Plus est un outil en ligne de commande austère

Sous Windows sqltools est mieux et gratuit

toad est une usine à gaz et payant

Oracle Entreprise Manager (web) : instances, schéma, security manager, storage manager

Oracle Management Server : programmation de travaux, remontée d’évènements

SQL*Plus worksheet

SQL Developer : outil graphique fourni par Oracle

Architecture interne d’Oracle :

Fichiers : données et index (tablespace, datafile) ; journaux ; contrôle (cohérence du SGBDR)

Mémoire : SGA ; PGA

Processus : processus serveur ; processus d’arrière plan

Notion d’instance :

Ensemble constitué de zones mémoires et des processus d’une BD.

Fichier d’initialisation d’instance (init.ora)

L’instance est plus proche du moteur sous SQLServer

Conseil : 1 base / instance

La database :

Regroupement de l’ensemble des objets SQL

Les fichiers de données : datafiles, regroupés dans un ensemble appelé tablespace

Fichiers journaux de reprise, au moins deux fichiers

Fichiers de contrôles : infos sur les fichiers et les états de la BD

Le dictionnaire de données :

Ensemble de tables contenant les informations des différents objets. Ces infos sont liées à

l’utilisateur SYS.

Infos non explicites et non accessibles. Ne doivent pas être modifiés (uniquement par le noyau

oracle)

Les vues :

Vues créées par l’utilisateur : USER_XXX

Vues accessibles à l’utilisateur connecté : ALL_XXX

Vue des objets de la base : DBA_XXX

Vues de données de l’instance : V$XXX (renseignements sur l’activité de la BD)


2 La création d’une BD en utilisant les scripts On ne créé pas la base une fois l’assistant de création de base de données terminé : on génère les

scripts, qui vont être exécutés et vont permettre de générer la BD

Utilisation de l’assistance Oracle de Création de BD :

Créer une BD, usage général

Fichier FORM.bat :

mkdir C:\oracle\product\10.2.0\admin\FORM\adump

mkdir C:\oracle\product\10.2.0\admin\FORM\bdump

mkdir C:\oracle\product\10.2.0\admin\FORM\cdump

mkdir C:\oracle\product\10.2.0\admin\FORM\dpdump

mkdir C:\oracle\product\10.2.0\admin\FORM\pfile

mkdir C:\oracle\product\10.2.0\admin\FORM\udump

mkdir C:\oracle\product\10.2.0\db_1\cfgtoollogs\dbca\FORM

mkdir C:\oracle\product\10.2.0\db_1\dbs

mkdir C:\oracle\product\10.2.0\flash_recovery_area

mkdir C:\oracle\product\10.2.0\oradata\FORM

set ORACLE_SID=FORM

C:\oracle\product\10.2.0\db_1\bin\oradim.exe -new -sid FORM -startmode manual -spfile

C:\oracle\product\10.2.0\db_1\bin\oradim.exe -edit -sid FORM -startmode auto -srvcstart system

C:\oracle\product\10.2.0\db_1\bin\sqlplus /nolog @C:\TEMP\formation\FORM.sql

Création des répertoires, création du service (=instance), connexion à sqlplus

Fichier FORM.sql :

set verify off

PROMPT specify a password for sys as parameter 1;

DEFINE sysPassword = &1

PROMPT specify a password for system as parameter 2;

DEFINE systemPassword = &2

PROMPT specify a password for sysman as parameter 3;

DEFINE sysmanPassword = &3

PROMPT specify a password for dbsnmp as parameter 4;

DEFINE dbsnmpPassword = &4

host C:\oracle\product\10.2.0\db_1\bin\orapwd.exe

file=C:\oracle\product\10.2.0\db_1\database\PWDFORM.ora password=&&sysPassword force=y

@C:\TEMP\formation\CloneRmanRestore.sql

@C:\TEMP\formation\cloneDBCreation.sql

@C:\TEMP\formation\postScripts.sql

host "echo SPFILE='C:\oracle\product\10.2.0\db_1/dbs/spfileFORM.ora' >

C:\oracle\product\10.2.0\db_1\database\initFORM.ora"

@C:\TEMP\formation\postDBCreation.sql

Génération des mots de passe

Le fichier init.ora a aussi été défini. Il est facile de le modifier.

On peut modifier les fichiers à la main

Une fois que les scripts sont prêts, on lance FORM.bat

Cependant, le script ne crée pas le LISTENER. Il faut donc le créer à la main.


Création du listener :

Configuration Oracle Net

Configuration d’un nom de service de réseau local ; Ajouter ; « FORM » ;

Utiliser un numéro de port différent (conseillé)

2.1 Ouverture et fermeture d’une BD

Ouverture d’une BD :

STARTUP [FORCE][RESTRICT][PFILE=fichier] [OPEN[RECOVER]|MOUNT|NOMOUNT]

Fermeture d’une BD:

SHUTDOWN [ABORT|IMMEDIATE|NORMAL|TRASACTIONNAL]

2.2 Lister les paramètres d’une instance

SQL> SHOW PARAMETERS

2.3 Best practice

Optimiser le script pour le rendre autosuffisant pour la création d’une base de données puis le

sauvegarder à un emplacement sûr.

Lors d’une restauration d’un serveur défaillant, ce script pourra être réutilisé pour créer une instance.

Il ne restera alors plus qu’à plaquer la sauvegarde.


3 Les imports/exports

3.1 L’utilitaire exp (pour sauvegarder)

Aide en ligne : C:\> EXP HELP=Y

C:\> EXP utilisateur/password [paramètres]

Exemple :

C:\> EXP system/oracle file=c:\sauv.dmp owner=user1

C:\> EXP system/oracle full=Y file=c:\sauv.dmp

C:\sauv.dmp Nom du fichier tel qu’il sera sauvegardé

owner=user1 Seul le schéma user1 sera sauvegardé

full=Y (yes) Tous les schémas seront sauvegardés

Export d’une base

3.2 L’utilitaire imp (pour restaurer)

C:\> IMP utilisateur/password [paramètres]

Exemple :

C:\> IMP system/oracle file=c:\sauv.dmp log=c:\log.txt fromuser=user1 touser=user2

C:\> IMP system/oracle file=c:\sauv.dmp log=c:\log.txt full=Y ignore=Y

fromuser=user1 touser=user2 Les données dans User1 sont copiées vers User2

full=Y On importe tout

Import d’un fichier


3.3 Exemple : export de la base HR

La base de l’utilisateur HR = le schéma HR

Méthode : un fichier .bat qui s’exécute, stockant les paramètres de ce qu’on veut exporter

Il faut créer les deux fichiers suivants : export.bat + export.dat

Le but est de sauvegarder le schéma ORCL.HR

Le fichier export.bat :

set oracle_sid=ORCL

set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 REM ceci afin d’éviter bug

C:\oracle\product\10.2.0\Db_1\BIN\EXP system/oracle PARFILE=C:\Documents\export.dat

On peut avoir à remplacer system/oracle par system/oracle@ORCL (accès distant)

Le fichier C:\Documents\export.dat :

Buffer=65536

File=C:\Documents\export_HR.dmp -- emplacement de la sauvegarde

OWNER=HR ou FULL=Y

CONSISTENT=Y -- je ne prends que les données sures (commitées)

STATISTICS=NONE

Log=C:\Documents\export_HR.log -- fichier de log d’export

Il est possible de ne sélectionner que certaines tables, etc.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm#CEGFIAGE

Ne JAMAIS zipper les fichiers DUMP !

3.4 Exemple : import de la base HR

Le but est de restaurer la sauvegarde du schéma HR

Si on fait un fromuser… touser…, cela implique que l’utilisateur de touser existe !

Créer un schéma :

C:\> SET ORACLE_SID=ORCL

C:\> SQLPLUS SYS/oracle AS SYSDBA SQL> CREATE USER RHFORM IDENTIFIED BY password DEFAULT TABLESPACE EXAMPLE TEMPORARY TABLESPACE TEMP PROFILE DEFAULT; -- on accorde les privilèges à l'utilisateur RHFORM SQL> GRANT CONNECT TO RHFORM; SQL> GRANT RESOURCE TO RHFORM; SQL> GRANT SELECT_CATALOG_ROLE TO RHFORM; SQL> GRANT UNLIMITED TABLESPACE TO RHFORM; SQL> GRANT CREATE ANY TABLE TO RHFORM; SQL> GRANT CREATE ANY INDEX TO RHFORM; SQL> GRANT CREATE ANY VIEW TO RHFORM; SQL> GRANT EXECUTE ANY PROCEDURE TO RHFORM;

On peut maintenant se connecter avec RHFORM/password pour tester la création de l’utilisateur.

Le fichier import.bat :

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm#CEGFIAGE


C:\> set oracle_sid=ORCL

set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

C:\oracle\product\10.2.0\Db_1\BIN\IMP system/oracle@ORCL PARFILE=C:\Documents\import.dat

On peut avoir à remplacer system/oracle par system/oracle@ORCL (accès distant)

Le fichier C:\Documents\import.dat :

Buffer=65536

File=C:\Documents\export_HR.dmp emplacement de la sauvegarde

fromuser=HR nom du schéma à restaurer

tosuer=RHFORM ou FULL=Y si export en FULL=Y aussi

#tables=JOBS,JOB_HISTORY

STATISTICS=NONE

Log=C:\Documents\import_HR.log fichier de log d’import

fromuser… touser… est OBLIGATOIRE quand on ne fait pas un export FULL=Y

Résolution des erreurs :

En cas de plantage de l’import, avant de le relancer, il faut faire :

SQL> DROP USER RHFORM CASCADE;

Puis recréer l’utilisateur et réimporter

3.5 Exercice pratique imports/exports

Créer une instance Oracle avec une base de données EXAMPLE :

Créer le script, et modifier le fichier cloneDBCreation.sql

@...mkplug.sql

Connect sys/...

ALTER TABLESPACE EXAMPLE RENAME TO FORMATION; insérer cette ligne ici

Shutdown ...

Création du listener et du tnsnames :

Modifier le fichier listener.ora puis démarrer le service listener.

C:\> lsnrctl start LISTENER4

Modifier le fichier Tsnnames.ora afin d’ajouter l’entrée de l’instance créée

Lancer le script de création de Base de données clonedbcreation.sql

Faire les modifications suivantes dans la base de données « Exemple » :

Ajouter 3 lignes dans la table EMPLOYEES (departement IT) :

SQL>INSERT INTO HR.EMPLOYEES VALUES (hr.employees_seq.nextval, 'Olivier','Dehecq','ODEHECQ','01.02.03.04.05',SYSDATE,'IT_PROG',6000,NULL,NULL,60); SQL>INSERT INTO HR.EMPLOYEES VALUES (hr.employees_seq.nextval, 'Eric','Olliver','EOLLIVIER','01.02.03.04.06',SYSDATE,'IT_PROG',6000,NULL,NULL,60); SQL>INSERT INTO HR.EMPLOYEES VALUES (hr.employees_seq.nextval, 'Fabienne','Lenoir','FLENOIR','01.02.03.04.07',SYSDATE,'IT_PROG',6000,NULL,NULL,60); SQL>COMMIT;

Faire une sauvegarde (EXP) de la base de données :

Utiliser les fichiers export.bat + export.dat (cf. détails page précédente) OWNER=HR

Mettre les 3 employés dans le département IT HelpDesk :

SQL> UPDATE HR.EMPLOYEES SET DEPARTMENT_ID=230 WHERE EMPLOYEE_ID=208 OR EMPLOYEE_ID=209 OR EMPLOYEE_ID=210; SQL> COMMIT;

Simulation d'un crash Serveur :

Exploser tout dans le schéma HR


Restauration de la base de données (Remise en Condition Opérationnelle) :

Supprimer l’utilisateur HR :

SQL> DROP USER HR CASCADE;

Créer un nouvel utilisateur HR dans le tablespace FORMATION :

C:\> SET ORACLE_SID=ORACLE

C:\> SQLPLUS SYS/oracle AS SYSDBA SQL> CREATE USER RHORACLEIDENTIFIED BY password DEFAULT TABLESPACE FORMATION TEMPORARY TABLESPACE TEMP PROFILE DEFAULT; -- on accorde les privilèges à l'utilisateur HR SQL> GRANT CONNECT TO HR; SQL> GRANT RESOURCE TO HR; SQL> GRANT SELECT_CATALOG_ROLE TO HR; SQL> GRANT UNLIMITED TABLESPACE TO HR; SQL> GRANT CREATE ANY TABLE TO HR; SQL> GRANT CREATE ANY INDEX TO HR; SQL> GRANT CREATE ANY VIEW TO HR; SQL> GRANT EXECUTE ANY PROCEDURE TO HR;

Importer les données HR : fromuser=HR … touser=HR puis utiliser le script import.bat + import.dat

Faire une sauvegarde complète et partielle de la base de données.

Modifier le fichier export.dat pour FULL=Y (complète) ou OWNER=HR (partielle)

Programmer une sauvegarde complète tous les samedis à 02h00 :

Créer une tache dans le planificateur de tâches, pour exécuter le script export_FULL.bat

Programmer une sauvegarde partielle tous les jours de la semaine à 02h00

Créer une tache dans le planificateur de tâches, pour exécuter le script export_HR.bat


4 Optimisation

4.1 Gestion de la mémoire

Oracle 9g et inférieur :

N paramètres pour les zones mémoire

Oracle 10g :

1 paramètre SGA (utilisation d’ASMM) + 1 paramètre PGA

Automatic Shared Memory Management (ASMM) : on définit juste le SGA Target (taille

du SGA). Exemple : 1Go. Oracle distribue la mémoire entre les différents caches.

Paramètre de SGA : SGA_TARGET

Paramètre de PGA : PGA_AGGREGATE_TARGET

Oracle 11g :

1 paramètre pour PGA+SGA : MEMORY_TARGET

Processus dédié : à 1 processus client correspond 1 processus serveur.

Accès distant

Pour tester que le client accède au listener : tnsping

Toujours vérifier qu’on est VRAIMENT sur la base à laquelle on veut accéder !

SELECT * FROM V$INSTANCE

Oracle nécessite un client lourd : il faut installer l’application Client Oracle sur chaque client !

OFA : règles de nommages Oracle :

Les répertoires Admin, Product, Oradata doivent donc être au même niveau !


4.2 Mise à jour de Oracle

1. Sauvegarder la base

2. Arrêter la base

Gestion des services ou shutdown

3. Mise à Jour du produit

Utilisation d’Oracle Universal Installer (Setup ou runInstaller ou runInstaller.sh)

Attention à bien choisir le bon répertoire Oracle que l’on veut upgrader : sélectionner le

bon home dans la comboBox

4. Upgrade de la base C:\> dbua

Ne pas déplacer la BD : la recompiler (utlrp.sql)

Il se charge de redémarrer les services à l’issue

5. Tester le bon fonctionnement de l’installation SELECT comp_name, version, status FROM DBA_REGISTRY;

Démarrer le listener (C:\>lsnrctl start …)

Démarrer le service dbconsole

4.3 Gestion des utilisateurs (rappels)

Ajouter des utilisateurs (p217) :

CREATE User …

Ajouter des privilèges système (p225) :

GRANT create session TO User;GRANT connect to User;(connect = Role système)

Ajouter des privilèges objets (p227) :

GRANT [SELECT|UPDATE] ON toto.table1 TO user : on crée des rôles pour simplifier GRANT connect, ressource TO User;

Par rapport à Oracle10g, Oracle11g accorde beaucoup moins de droits au rôle connect

on peut se retrouver avec des problèmes de privilèges !

GRANT connect to … n’est pas recommandé ! Il vaut mieux un GRANT create session to …

Quotas tablespace (p219) :

La plupart du temps, pour ne pas se prendre la tête, on met un QUOTAS UNLIMITED

Profil (p221) :

Pour la gestion des mots de passe surtout

Ne jamais modifier les paramètres du profil DEFAULT

Audit :

On peut tracer tout ce que fait un utilisateur (même SYS)

4.4 Gestion des Tablespaces (p169)

C’est l’espace qui permet d’enregistrer les différents objets Oracle.


Gestion des tablespaces

La lecture séquentielle implique une perte de performance, mais on ne perd pas

nécessairement de performances.

4.4.1 La High Water Mark

Un SELECT * FROM Matable; (séquentiel), va lire du début jusqu’au bloc HWM

On peut faire des opérations de SHRINK pour diminuer la HWM

4.4.2 La fragmentation

Solution : il faut augmenter la taille des blocs


Solution : il faut augmenter le PCTFREE

Taille des blocs

4.5 Connexion en SQL Developer

A la place de localhost, mettre l’adresse IP (pas 127.0.0.1)

Lister les tablespaces :

SELECT tablespace FROM dba_tablespaces;

Lister les utilisateurs :

SELECT username FROM all_users;

4.6 Test des requêtes stockées dans le SHARED POOL

/* vidage du shared_pool */ ALTER SYSTEM flush SHARED_POOL; /* lancement des requetes, attention à l'orthographe */ SELECT sysdate from dual; SELECT sysdate FROM dual; SELECT value from v$parameter WHERE type='1'; /* vérification du contenu du cache shared pool */ SELECT sql_text,hash_value,executions

FROM v$sqlarea WHERE sql_text LIKE ('SELECT sysdate%') OR sql_text LIKE ('%v$parameter%');

/* lancement des requêtes, attention à l'orthographe */ SELECT sysdate FROM dual; SELECT value from v$parameter WHERE type='2'; /* vérification du contenu du cache shared pool */ -- il y a bien incrémentation du nombre d'exécution des requêtes SELECT sql_text,hash_value,executions

FROM v$sqlarea WHERE sql_text LIKE ('SELECT sysdate%') OR sql_text LIKE ('%v$parameter%');

Cela montre que le shared pool va stocker des requêtes différentes selon l’écriture

Il faut minimiser les différences de casse et maximiser l’utilisation des host variable et bind variable

(parsing)

“CURSOR_SHARING=EXACT” doit devenir “CURSOR_SHARING=SIMILAR” (Permet de ne pas différencier la

casse)


4.7 Les tables externes

Permet d’accéder à un fichier extérieur à la base comme si c’était une table

Principe de table externe

Créer un fichier texte C:\temp\table_externe.txt :

10|ligne1 11|ligne2 12|ligne3 5|ligne5

Création de l’objet directory :

CREATE DIRECTORY data_externe AS 'C:\temp' ;

Attribution des privilèges de lecture /écriture :

GRANT READ, WRITE ON DIRECTORY data_externe TO <user>;

Création de table :

CREATE TABLE tb_externe création de la table

(champ1 VARCHAR2(10), champ2 VARCHAR2(20)) ORGANIZATION EXTERNAL( TYPE ORACLE_LOADER driver Oracle DEFAULT DIRECTORY data_externe répertoire du fichier ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE délimiteur de ligne BADFILE 'table_externe.bad' FIELDS TERMINATED BY '|') délimiteur de champ LOCATION (data_externe:'table_externe.txt')); emplacement

Tester :

Se connecter avec <User>

Faire un select * from tb_externe;

4.8 La table temporaire

CREATE GLOBAL TEMPORARY TABLE tempo (…) ON COMMIT {PRESERVE|DELETE} ROWS;

Traitements batch :

agrégation des données d’autres tables pour créer une table de travail.

Traitement séquentiel de la table : lecture de la table temporaire

En fin de traitement, la table temporaire est supprimée automatiquement (pas d’utilisation du

tablespace d’undo)

4.8.1 Exemple

Session utilisateur1 :

SQL> CREATE GLOBAL TEMPORARY TABLE tempo (owner varchar2(20), tablename varchar2(36)) ON COMMIT PRESERVE ROWS;


ON COMMIT PRESERVE ROWS --garde les lignes lors du commit

ON COMMIT DELETE ROWS --supprime les lignes lors du commit

Intérêt de preserve rows : garder les lignes dans les tables temporaires lorsqu’on update les

autres tables (non temporaires)

Intérêt de delete rows : supprimer les lignes lors d’un commit, pour pouvoir charger d’autres

données dans la table temporaire

On évite absolument les opérations de DDL dans les scripts

SQL> INSERT INTO tempo SELECT owner,table_name from dba_tables; SQL> select count(*) from tempo; 1600 rows

Session utilisateur2 :

SQL> select count(*) from tempo; 0 rows (même après un COMMIT sur la session 1)

La table temporaire n’est visible que le temps de la session.

Donc visible que pour 1 utilisateur/batch

4.9 IOT (Indexed Organization Table)

Tables organisées en index. Maintenant, les données sont stockées dans l’index et non pas dans la

table contenant les données.

Schéma logique IOT

CREATE TABLE iot1 (Nom varchar2(30), Prenom varchar2(30), Id Number, CONTRAINT pk_iot PRIMARY KEY(Id)) ORGANIZATION INDEX;


5 Oracle sous Linux

5.1 Connexion X11 à un Linux

Sur le client :

Installer xming-fonts, xming, putty

Sur le serveur :

$ dhclient $ ifconfig

Sur le client :

Ouvrir putty

Options : SSH > X11 > allow X11 forwarding

Se connecter au serveur

Dans putty :

Se connecter en utilisateur oracle (mot de passe par défaut : oracle) # export DISPLAY=localhost:10.0 # xclock (pour tester l’interface graphique)

5.2 Installation d’Oracle

$ cd /tmp/ora…/ (aller dans le répertoire de l’installeur du moteur Oracle DB 10gR2)

$ ./runInstaller

Installer Oracle 10.2.0 en mode graphique, installation personnalisée

Décocher : OLAP, Partitioning, Spatial, Advanced security, Oracle Entreprise Manager Console

Exécuter les scripts demandés en tant que root

5.2.1 Installation du patch de mise à jour

$ cd /tmp/ora…/ (aller dans le répertoire de l’installeur du patch de mise à jour) $ ./runInstaller

Installer le patch Oracle 10.2.0.4 en mode graphique

Exécuter les scripts demandés en tant que root

5.2.2 Vérification de la version

$ cd $ORACLE_HOME $ cd Opatch $ ./opatchlsinventory

5.3 Création de la base de données

En général on utilise ses propres scripts ou ses templates

$ cd $ORACLE_HOME/bin $ ./dbca

Base de données personnalisée. On la nomme ORA10. On décoche Spatial.

$ export ORACLE_SID=ORA10 $ ./sqlplus system/oracle $ env

Doit renvoyer ORACLE_SID='ORA10'


5.3.1 Assistant réseau

$ ./netca

Configuration d’un processus d’écoute + configuration d’un nom de service de réseau local

Assistant création de BD + options : dbca

Assistant upgrade de BD : dbua

Assistant màj listener + tnsnames : netca


6 Vues Matérialisées Materialized View (MV) : vue contenant des données (contrairement aux vues classiques)

Au moment de la création de la vue, elle récupère les données et les stocke dans une structure

(tablespace).

rapide car ne reconstruit pas les données.

il faut faire des refresh de cette vue (attention à l’obsolescence des données)

6.1 Opérations à réaliser

Accès distant à la base Oracle sur SRV Windows

6.2 Exemple de mise en place

1 - Sur le serveur maitre (Serveur Windows) :

SQL> connect olivier/password@ORCL SQL> CREATE TABLE mv_tb_maitre ( id number, code varchar2(5), libelle varchar2(30), valeur number, flag char(1)); SQL>ALTER TABLE mv_tb_maitre ADD CONSTRAINT pk_mv_tb_maitre PRIMARY KEY (id); SQL> @c:\procInsert_MV.sql script d’insertion de données

2 - Lien entre deux bases :

Peut relier 2 bases distantes, ou 2 bases hébergées localement

Depuis la base ORA10 :

SQL> CREATE PUBLIC DATABASE LINK lk_mv CONNECT TO <userBaseDistante> IDENTIFIED BY <passwordBaseDistante> USING '<aliasDeServiceTNSCrééEtTesté>';

Par défaut le DB_LINK est PRIVATE (donc visible seulement par celui qui le crée)

Tester : SQL > SELECT * from sysdate@lk_mv;

3 - Créer la vue matérialisée :

SQL> CREATE MATERIALIZED VIEW mv_test TABLESPACE USERS emplacement de stockage BUILD IMMEDIATE construction immediate REFRESH COMPLETE reconstruit intégralement la table à chaque màj

NEXT sysdate + 10/1440 mise à jour toutes les 10 minutes (1440 : nb minutes dans 24h)

-- Si on veut toutes les 1h : 1/24 (24 : nb heures dans 24h) AS SELECT code, sum(valeur) from mv_tb_maitre@lk_mv WHERE flag='1' GROUP BY code;

4 - Tester :

SQL> SELECT * FROM mv_test;

Sur le serveur maître (Serveur Windows) :

SQL> UPDATE mv_tb_maitre SET valeur=valeur+100 WHERE flag='1'; SQL> COMMIT;


SQL> SELECT * FROM mv_tb_maitre;

Sur le serveur Esclave (Serveur Linux) :

SQL> ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY HH24:MI:SS'; SQL> SELECT * FROM user_mview_refresh_times; SQL> SELECT * FROM mv_test;


7 Le monitoring Index Accélère la recherche. Un index est très couteux en performances.

De plus, il faut vérifier que les index créés sont bien utilisés.

7.1 Exemple

Création d’une table et tests :

SQL> CREATE TABLE liste_tables AS SELECT * FROM dba_tables;

C’est un ordre DDL, il n’y a donc pas besoin de commiter

SQL> SELECT COUNT(*) FROM liste_tables;

Tests (dans SQL Developer):

SELECT * FROM liste_tables WHERE OWNER='DBSNMP';

[F10] donne le raisonnement logique.

On voit que la table est parcourue en FULL_SCAN : ( TABLE_ACCESS + OPTION=FULL)

En dessous de 20% de lignes ramenées, il est intéressant de créer un index. Sauf si la requête

est exécutée très rarement (1 fois par an).

SELECT * FROM V$object_usage; 0 ligne vue système sur l’utilisation des index

Création d’un index :

CREATE INDEX idx1_liste_tables ON liste_tables (owner);

On doit normalement mettre les index dans des tablespaces spécifiques

Mise sous surveillance de l’index :

ALTER INDEX idx1_liste_table monitoring usage;

On refait

SELECT * FROM V$object_usage;

1 ligne, used = no

SELECT * FROM liste_tables WHERE OWNER='DBSNMP';

Avec [F10], on voit bien que la requête utilise l’index

SELECT * FROM V$object_usage;

1 ligne, used = yes

7.2 Modes d’optimisation

Paramètre OPTIMIZER_MODE={ALL_ROWS|FIRST_ROWS}

Mode rule : ne pas utiliser : trop vieux ! Obsolète

Mode basé sur les coûts : statistiques sur chaque objet

il faut que les statistiques soient à jour quand on est en mode all_rows ou first_rows

Pour mettre à jour les statistiques :

Manuellement : Utilisation du paquet : @dbms_stats.sql (p275) ou

ANALYZE TABLE nomTable COMPUTE STATISTICS;

Automatiquement : Toutes les nuits, Oracle recalcule les statistiques

Pour vérifier que les statistiques sont à jour :

SELECT COUNT(*) FROM la_table_a_vérifier; SELECT table_name, last_analyzed FROM user_tables WHERE table_name='LISTE_TABLES';

On peut aussi utiliser dba_tables au lieu de user_tables


Exemple :

SELECT * FROM liste_tables WHERE OWNER='DBSNMP'; SELECT * FROM liste_tables WHERE UPPER(OWNER)='DBSNMP';

[F10] montre que toute la table est parcourue, l’index n’est plus utilisé

L’utilisation de la fonction dans la clause WHERE fait en sorte que l’index ne soit plus utilisé

Il faut donc créer des index sur fonctions.

Les index de fonction

CREATE INDEX monIndex ON liste_tables (UPPER(owner));

Ne fonctionne qu’avec la fonction UPPER

Il faut un index différent par fonction


8 Le partitionnement Gestion des accès aux grosses tables

Les morceaux sont plus petits, les performances sont donc normalement meilleures.

La clé de partitionnement :

La clé de partitionnement va permettre un partitionnement optimum ou non.

Dans chaque requête, pour la clause :

WHERE les colonnes de la clé de partitionnement = valeur

Si on définit bien la clé de partitionnement, les recherches seront plus rapides.

Prérequis :

A partir d’une table de 2Go, on peut étudier les clés de partitionnement.

Standard Edition : pas toutes les options

Enterprise Edition : toutes les options (dont le partitionnement !)

Types de partitionnement :

De v8.0 à v10g : range partitioning (entre des valeurs)

De v8i à v10g : hash partitioning (Oracle calcule une clé hash)

De v9i à v10g : list partitioning (Selon une liste de valeurs)

De v9i à v10g : composite partitioning (Une liste, et dans cette liste on partitionne)

Depuis v11g : Ajout de 5 méthodes

Partition By Range / fourchette de valeurs

DROP TABLE TB_RANGE_PARTITION; CREATE TABLE TB_RANGE_PARTITION ( CODE NUMBER, LIBELLE VARCHAR2(80), TYPE VARCHAR2(10), ADRESSE_1 VARCHAR2(80), VILLE VARCHAR2(20), DATE_CREATION DATE) PARTITION BY RANGE (DATE_CREATION) clé de partitionnement

(PARTITION P_2003 VALUES LESS THAN (TO_DATE('31/12/2003','DD/MM/YYYY') TABLESPACE ts_data03, PARTITION P_2004 VALUES LESS THAN (TO_DATE('31/12/2004','DD/MM/YYYY')) TABLESPACE ts_data04, PARTITION P_2005 VALUES LESS THAN (TO_DATE('31/12/2005','DD/MM/YYYY')) TABLESPACE ts_data05, PARTITION P_2006 VALUES LESS THAN (MAXVALUE) TABLESPACE ts_data06 ); partition “poubelle” ALTER TABLE TB_RANGE_PARTITION ADD ( CONSTRAINT PK_TB_RANGE_PARTITION PRIMARY KEY (CODE) USING INDEX TABLESPACE ts_INDEX);

Insertion dans la table partitionnée à partir table source

Les données doivent être réparties par année

INSERT INTO tb_range_partition SELECT * from tb_source;


Partition By Liste / dispatch en fonction d'une liste de valeurs :

DROP TABLE TB_LIST_PARTITION; CREATE TABLE TB_LIST_PARTITION( CODE NUMBER,

LIBELLE VARCHAR2(80), TYPE VARCHAR2(10), ADRESSE_1 VARCHAR2(80), VILLE VARCHAR2(20), DATE_CREATION DATE ) PARTITION BY LIST (ville) STORAGE (INITIAL 100K NEXT 10K PCTINCREASE 0) TABLESPACE Data0

( PARTITION Part_Maine_et_Loire VALUES ('ANGERS') TABLESPACE Data1, PARTITION Part_Loire_Atlantique VALUES ('NANTES','ANCENIS') TABLESPACE Data2, PARTITION Part_Paris VALUES ('PARIS') TABLESPACE Data3, PARTITION Part_Autres VALUES (DEFAULT)); partition poubelle : utilise le tablespace par default (Data0)

ALTER TABLE TB_LIST_PARTITION ADD (CONSTRAINT PK_TB_LIST_PARTITION PRIMARY KEY (CODE) USING INDEX TABLESPACE I_TEST);

Insertion dans la table partitionnée à partir table source

Les données doivent être réparties par Département

INSERT INTO tb_list_partition SELECT * from tb_source;

Partition Composite Range + liste :

CREATE TABLE TB_COMPOSITE_PARTITION ( ID NUMBER PRIMARY KEY, LIBELLE VARCHAR2(40), DATE_TRT DATE, FLAGNUMBER(1), CODE VARCHAR2(5)) TABLESPACE DATA PARTITION BY RANGE (DATE_TRT) SUBPARTITION BY LIST (FLAG) ( PARTITION P_DAT2004 VALUES LESS THAN (TO_DATE('31/12/2004','DD/MM/YYYY')), TABLESPACE DATA ( SUBPARTITION P_2004_1 VALUES (1) TABLESPACE DATA, SUBPARTITION P_2004_2 VALUES (2) TABLESPACE DATA, SUBPARTITION P_2004_3 VALUES (DEFAULT) TABLESPACE DATA), PARTITION P_DAT2005 VALUES LESS THAN (TO_DATE('31/12/2005','DD/MM/YYYY')), TABLESPACE DATA ( SUBPARTITION P_2005_1 VALUES (1) TABLESPACE DATA, SUBPARTITION P_2005_2 VALUES (2) TABLESPACE DATA, SUBPARTITION P_2005_3 VALUES (DEFAULT) TABLESPACE DATA ), PARTITION P_DAT2006 VALUES LESS THAN (TO_DATE('31/12/2006','DD/MM/YYYY')), TABLESPACE DATA ( SUBPARTITION P_2006_1 VALUES (1) TABLESPACE DATA, SUBPARTITION P_2006_2 VALUES (2) TABLESPACE DATA, SUBPARTITION P_2006_3 VALUES (DEFAULT) TABLESPACE DATA ), PARTITION P_DAT2007 VALUES LESS THAN (MAXVALUE), TABLESPACE DATA ( SUBPARTITION P_2007_1 VALUES (1) TABLESPACE DATA, SUBPARTITION P_2007_2 VALUES (2) TABLESPACE DATA, SUBPARTITION P_2007_3 VALUES (DEFAULT) TABLESPACE DATA ) );


9 Datapump (export/import) Datapump est une nouveauté 10g :

expdp d’une base en v11g ne peut pas faire impdp sur une base en v10g

Expdp sur v10g peut faire impdp sur v11g

Evidemment, les sauvegardes exp/imp sont incompatibles avec expdp/impdp

Expdp/impdp est plus rapide, plus convivial et plus sympa que exp/imp!

Exp/imp : les dump sont stockés sur le client

Expdp/impdp : les dump sont stockés sur le serveur Oracle

Il y a donc besoin d’un objet DIRECTORY pour que la base accède physiquement au serveur pour

copier le fichier dump.

9.1 Exports : expdp

Attention : il faut écraser le fichier dump existant AVANT de faire expdp. Donc un rm –f !

9.1.1 Marche à suivre pour exporter

1 – créer un directory :

DIRECTORY par défaut : DATA_PUMP_DIR (avec l’assistant)

Pour lister les DIRECTORY : SELECT * FROM dba_directories;

2 –Attribuer des privilèges sur le directory (aux utilisateurs qui font des exports/imports) :

GRANT read,write ON DIRECTORY data_pump_dir TO User1,User2;

3- Droits sur l’export/import (tout est controlé au niveau des privilèges) :

IMP_FULL_DATABASE droit d’importer


EXP_FULL_DATABASE droit d’exporter

GRANT EXP_FULL_DATABASE TO User1;

De plus sous Unix, l’utilisateur Oracle doit avoir les droits sur le répertoire (ex. chown)

4- Exécution de l’export/import :

9.1.2 Exemples d’utilisation de la commande expdp

Aide en ligne de commande :

$ expdp HELP=Y

Estimation de la taille nécessaire à l’export :

$ expdp system/oracle FULL=Y ESTIMATE_ONLY=Y

Sauvegarde niveau Database (ne sauvegarde jamais SYS):

EXPORT FULL (nécessite EXP_FULL_DATABASE)

$ expdp system/oracle [DIRECTORY=DATA_PUMP_DIR] DUMPFILE=<path\monDump.dtp> FULL=Y LOGFILE=<path\monDump.log>

Sauvegarde niveau Schéma :

EXP_FULL_DATABASE permet de sauvegarder tous les schémas, même ceux auxquels on n’a

pas accès.

Exemple : export de schéma(s)

$ expdp system/oracle [DIRECTORY=...] DUMPFILE=... LOGFILE=... SCHEMAS='toto,tata'

Dans ce cas, on met toujours SCHEMAS avec un S

Exemple : export de tous les schémas sauf le schéma TEST (voire aussi TOTO)

$ expdp system/oracle [DIRECTORY=DATA_PUMP_DIR] FULL=Y DUMPFILE=data_pump_dir2:expFile.dtp LOGFILE=expFile.log> EXCLUDE=SCHEMA:\"=\'TEST\'\" [EXCLUDE=SCHEMA:\"=\'TOTO\'\"]

Utilise le directory par défaut si il n’est pas indiqué (cas de LOGFILE=expFile.log)

\ pour que le Shell n’interprète pas les " et '

Sauvegarde niveau table :

Exemple : export de table(s)

$ expdp system/oracle [DIRECTORY=...] DUMPFILE=... LOGFILE=...TABLES='employes,conges'

Les cotes ' peuvent être nécessaires, OU PAS !

Dans ce cas, on met toujours TABLES avec un S

Il va vouloir exporter les tables du schéma correspondant à l’utilisateur (ici system),

sinon : user1.maTable1, user1.maTable2

Exemple : export du schéma TITI sauf les tables commençant pas ‘INS’

$ expdp system/oracle [DIRECTORY=DATA_PUMP_DIR] SCHEMA= DUMPFILE=data_pump_dir2:expFile.dtp LOGFILE=expFile.log> EXCLUDE=TABLE:\"LIKE \'INS%\'\"

Utilisation de 3 processus pour effectuer la tache

Bouffe des ressources, mais gain de temps

$ expdp… PARALLEL=3 …

Sauvegarde niveau tablespace :

Tablespace transportable

Olivier DEHECQ – http://aide.informatique1.fr

9.1.3 Déroulé complet d’export de base full

Sur Windows (base ORCL) :

1- Création d ‘un objet directory : monDir

2- GRANT

3- Export estimate

4- Export full

Créer le répertoire C:\SauvegardesOrcl sur le serveur Windows

C:\> sqlplus system/oracle@ORCL SQL>CREATE DIRECTORY monDir AS 'C:\SauvegardesOrcl' ; SQL>GRANT READ,WRITE ON DIRECTORY monDir TO odehecq;

C:\> expdp odehecq/password FULL=Y ESTIMATE_ONLY=Y

Estimation : 67Mo

C:\> expdp odehecq/password DIRECTORY=monDir DUMPFILE=monDir:exportOrcl.dtp FULL=Y

LOGFILE=exportOrcl.log

Sur Linux (base ORA10) :

Créer le répertoire /save/ORA10/ sur le serveur linux

Accorder les droits à l’utilisateur Oracle ($ chown oracle /save/ORA10)

$ ./sqlplus system/oracle@ORCL SQL> CREATE DIRECTORY monDir2 AS '/save/ORA10' ; SQL> GRANT READ,WRITE ON DIRECTORY monDir2 TO odehecq; $./expdpodehecq/password FULL=Y ESTIMATE_ONLY=Y

Estimation : 67Mo

$ ./expdpodehecq/password DIRECTORY=monDir2 DUMPFILE=monDir2:exportORA10.dtp FULL=Y LOGFILE=exportORA10.log

9.2 Import : impdp

9.2.1 Exemples d’utilisation de la commande impdp

Niveaux Database :

$ impdp system/oracle DIRECTORY=... DUMPFILE=... LOGFILE=... FULL=Y EXCLUDE= (au minimum on exclut le schéma SYSTEM) niveau Schéma

JAMAIS d’import FULL sans conditions ! (erreurs, écrasements, etc.)

Niveau Schéma :

$ impdp system/oracle DIRECTORY=... DUMPFILE=... LOGFILE=... SCHEMAS='schema1,schema2'

Les cotes ' peuvent être nécessaires, OU PAS !

La création du schéma est automatisée : 1) drop user schema1 cascade; 2) impdp …

Il n’y a plus besoin de create user, grant, etc.

Exemple : Clonage de schéma (ancien exp fromuser= touser=) :

$ impdp system/oracle DIRECTORY=... DUMPFILE=... LOGFILE=... SCHEMAS='schema1,schema2' REMAP_SCHEMA=schema1:newShema1,schema2:newSchema2

Transforme le schéma1 en newSchema1 et schema2 en newSchema2, en les créant si

besoin

Niveau Table : ça existe, mais on ne voit pas

Niveau Tablespace : ça existe, mais on ne voit pas

D. Olivier – http://aide.informatique1.fr 28

9.2.2 Déroulé complet d’import de base full

Serveur Linux (base ORA10) :

1. Prérequis :

Creation d’un tablespace “migration” 10M LOCAL MANAGEMENT AUTOALLOCATE

Copier le dump full ORCL (Windows) dans /tmp

Création d’un objet directory DIR_migration

Connaitre les tablespaces utilisés par les tables de scott dans ORCL

Importer les objets de la base ORCL, schéma scott (tablespace USERS) vers la base

ORA10, schéma toto, tablespace MIGRATION

Tester tout

2. Export des données

SQL> CREATE TABLESPACE migration DATAFILE '/oracle/oradata/ORA10/miragtion.dbf' SIZE32 M AUTOEXTEND ON BLOCKSIZE 8K; $ ./$ORACLE_HOME/bin/expdp odehecq/password@ORCL DIRECTORY=monDir DUMPFILE=full.dpf LOGFILE=full.log FULL=Y

3. Activation du partage sur le serveur Oracle

Activer samba sur Linux :

$ su - $ service smb start

Dans Windows : copier le full.dtp vers \\10.2.100.43\root\etc

Sur Linux ORA10

4. Création du répertoire des datapump

SQL> CREATE DIRECTORY DIR_migration AS '/tmp';


5. Lister les utilisateurs

SQL> select distinct tablespace_name FROM dba_tables WHERE owner='SCOTT';

6. Importer les données de scott vers scotty

$ ./impdp system/oracle DUMPFILE=FULL.DPF DIRECTORY=DIR_migration LOGFILE=impSchemaScoot.log SCHEMAS=SCOTT REMAP_SCHEMA=SCOTT:SCOTTY REMAP_TABLESPACE=USERS:MIGRATION

7. Vérifier les informations de scotty

SELECT distinct tablespace_name FROM dba_tables WHERE owner='SCOTTY';

Il faut remettre scotty (dont le compte est locked et password expiré) en état.

SQL> ATLER USER SCOTTY IDENTIFIED BY tigger account unlock;

1.1.1 Option TABLE_EXIST_ACTION … TABLE_EXISTS_ACTION=SKIP|APPEND|APPEND|TRUNCATE|REPLACE

SKIP : ne remplace pas les données existantes

APPEND : ajoute à la suite (attention aux PK !)

TRUNCATE : vide les données de la table puis réimporte (pas de modification de la structure)

REPLACE : supprime la table, et la recrée à partir de la nouvelle structure

9.2.3 L’import planifié : d’une base à l’autre pour répliquer

L’objectif est de répliquer les tables d’une base à une autre. Lors d’une modification de structure

d’une table, il faut que l’import ne génère pas d’erreur : table_exists_action=replace :


Schéma de la réplication planifié

Le parfile <fichier.txt> contient l’ensemble des paramètres de impdp :

… network_link=DB_LINK remap_schema=BDU:MANDATE table_exists_action=REPLACE …

Transfère les tables, mais ne génère pas de fichier dump.


10 La Haute Disponibilité sur Oracle

10.1 RAC : Real Application Cluster

Cluster Oracle

Pas de gain de performances, juste Haute Disponibilité

ASM : Automatic Storage Management. Sur chaque serveur, il faut une instance dédiée à l’ASM

Attention au taux de pannes (souvent la baie qui est la cause de pannes)

10.2 Dataguard

Système de secours

Infrastructure type Dataguard

La copie des Redolog Files vers le site distant est asynchrone. Au pire on perd quelques transactions.

En cas de panne de la base principale, la standy database devient base principale le temps qu’on

résolve la panne.

On utilise un serveur Oracle qui ne sert que pour la sécurité et il faut une version Oracle DB Enterprise

de chaque côté.


11 Déroulé complet de l’installation d’une Oracle 11g sur Linux Ajouter un disque dur (facultatif)

Ajouter un disque dur avec VMware, (au moins 5Go)

$ fdisk –l

Nota : /dev/sdb n’est pas monté

$ fdisk /dev/sdb

n nouvelle partition principale, utilise tout l’espace

w sauver et quitter

$ mkfs –t ext3 /dev/sdb $ mkdir /oracle11 $ mount /dev/sdb /oracle11 $ chown oracle /oracle11

Monter l’image .iso sur le linux :

$ mkdir /cdrom $ mount -o loop /tmp/Oracle_Database_11201_linux32.iso /cdrom

Ouvrir le fichier /cdrom/doc/index.html :

Lire Database Installation guide (important).

Installer Oracle 11G

$ ./cdrom/runInstaller

Installer dans /oracle11, ne sélectionner que les options nécessaires

Installer une base : ‘ORA11’ + listener et service d’écoute (si besoin) :

$ ./dbca $ ./netca

Créer le lien symbolique vers /oracle/product/11.2.0 :

$ ln -s /oracle11 /oracle/product/11.2.0

Lancer sqlplus :

$ ./sqlplus system/oracle

Au cas où sqlplus ne démarre pas après une installation, exécuter ./usr/local/bin/oraenv pour

réinitialiser les variables


12 Sauvegarde / restauration (p288)

12.1 Introduction

12.1.1 Il faut sécuriser les fichiers sensibles :

Les fichiers de contrôle (Controlfiles ) à multiplexer (au minimum 2 controlfiles)

Les fichiers online redo logs : n groupes de n membres (au minimum 3 groupes de 2

membres)

Mise en place d’une politique de sauvegarde

Politique de sauvegarde Mode de fonctionnement

Est-il acceptable de perdre des données ? Non : mode archivelog

Oui : mode noarchivelog

Est-il possible d’arrêter périodiquement la base Non : mode archivelog


Est-il possible d’effectuer une sauvegarde

complète pendant l’arrêt ?

Non : mode archivelog


Usage OLTP (beaucoup de mises à jour) ? OLTP : sauvegardes planifiées

Décisionnel : une sauvegarde puis plus rien

Usage Décisionnel (base en read only : pas

nécessairement de sauvegardes) ?

12.1.2 Possibilités de sauvegarde :

Logique :

export / export datapump

Il faut reconstruire la base avant l’import

À faire quand même en complément d’une sauvegarde physique

Physique :

Backup du serveur Oracle / copie des fichiers

Pas besoin d’import, rien à reconstruire


12.2 La sauvegarde physique

Cold backup :

Consiste en une sauvegarde à froid des fichiers, base arrêtée

Procédure :

1) arrêter la base (shutdown normal|immediate)

ou aussi : shutdown abort ; arrêter le listener ; startup ; shutdown normal

2) copie des fichiers suivants :

datafiles, controlfiles, redolog, (init.ora ou spfile), le fichier password PWD<SID>.ora,

archived redo log (si on est en mode archive)

Ne pas oublier de fichiers, et vérifier que la base est bien arrêtée AVANT

Le fichier PWD<SID>.ora (ou orapw<SID>) est situé dans $ORACLE_HOME/dbs ou $ORACLE_HOME/database

Hot backup :

Sauvegarde à chaud, base démarrée

export

backup Oracle

12.3 Mode Archivelog

Création des fichiers archivelog

On peut copier en miroir les archivelog dans 1 à 10 répertoires simultanément.

12.3.1 Mise en mode archivelog d’une base

Rappel : ALTER SYSTEM SET <param>=<valeur>

ARCHIVE_DEST_[n] répertoire (n allant de 1 à 10)

LOG_ARCHIVE_FORMAT formatage du nom de fichier (p291)

LOG_ARCHIVE_DEST_STATE_[n] un state par destination : ENABLE|DISABLE

[LOG_ARCHIVE_START] avant la 9i il fallait démarrer l’archivage (ou =AUTO)


Nouveautés 10g:

DB_RECOVERY_FILE_DEST la Flash Recovery Area (un repertoire)

DB_RECOVERY_FILE_DEST_SIZE taille maxi de la Flash Recovery Area

A propos de la Flash Recovery Area:

C’est un repertoire (que l’on définit généralement sur un autre volume), et qui permet notamment des

flashback de table. Tend à remplacer l’archivelog.

12.3.2 Cas concrets

Exemple : connaitre les valeurs des paramètres de Flash Recovery Area :

SQL> show parameter db_recovery;

Exemple : changer la taille du flash recovery area :

SQL> ALTER SYSTEM SET db_recovery_file_dest_size=5G SCOPE=BOTH;

Exemple : savoir en quel mode on est :

SQL> SELECT archiver FROM v$instance; STOPPED SQL> SELECT log_mode FROM v$database; NOARCHIVELOG

Exemple : passer en mode archive (nécessite un arrêt propre de la base)

SQL> shutdown immediate SQL> STARTUP MOUNT SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN; SQL> SELECT archiver FROM v$instance; STARTED SQL> SELECT log_mode FROM v$database; ARCHIVELOG SQL> ARCHIVE LOG LIST;

Simuler un switch :

SQL> ALTER SYSTEM SWITCH LOGFILE;

12.3.3 Déroulé complet de la mise en place d’une sauvegarde archivelog Unix

$ export ORACLE_SID=ORA10 $ sqlplus /nolog $ connect sys/oracle AS SYSDBA

Si la base est ouverte :

$ shutdown immediate SQL> show parameter db_recovery;

Si les paramètres ne sont pas définis ou sont mal définis :

SQL> ALTER SYSTEM SET db_recovery_file='/oracle/oradata/flash_recovery;


12.4 Sauvegarde des données : rman (p294)

12.4.1 Principes

Avant : backup à remplacer par rman

RMAN (Recovery Manager) : il y a des livres dessus, conséquents : beaucoup d’options

Sauvegarde complète / FULL :

Sauvegarde de toutes les données de la base de données

Sauvegarde incrémentielle :

Principe sauvegarde incrémentielle : différentielle/cumulative

Différentielle :

Prend peu de place

Long à restaurer

Cumulative

Prend beaucoup de place

Rapide à restaurer


12.4.2 Fonctionnement de rman

Rman est un outil indépendant du moteur Oracle

Principe de fonctionnement de rman

Le contenu des redolog file n’est pas sauvegardé ! (juste la structure)

Les backupSet sont stockés physiquement sur le disque.

Pour le fonctionnement de rman, il y a besoin du référentiel qui est stocké :

- Soit dans le controlfile (par défaut) : durée de rétention par défaut = 7 jours

- Soit dans le RMAN Recovery Catalog

Le référentiel stocke : infos de configuration, sauvegardes réalisées, structure base cible, etc.

12.4.3 Exemple de configuration de rman

C:\> SET ORACLE_SID=ORA10

C:\> rman target / target : BD à joindre, / : identifiant connexion

C:\> rman target sys/oracle@<alias> pour se connecter à une base distante

RMAN> SHOW ALL; liste des paramètres

La rétention :

Soit on indique le nombre de jeux de sauvegardes qu’on veut garder, soit on indique le nombre

de jours de sauvegardes qu’on veut. Les sauvegardes obsolètes sont écrasables

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2; 2 jeux de sauvegardes

On écrit tel que ça apparaitrait dans show all

RMAN> CONFIGURE RETENTION POLICY TO WINDOWS OF 3 DAYS; 3 jours de sauvegardes

L’emplacement de sauvegarde :

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK; sauvegarder sur disque

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; stocke le référentiel dans controlfile

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'h:\Sauvegarde\Oracle\%U'; p298

Configuration de l’emplacement et du nom de la sauvegarde


12.4.4 Déroulé de sauvegarde avec rman

RMAN> BACKUP [comment] quoi [options];

RMAN> BACKUP [AS BACKUPSET] DATABASE; = RMAN> BACKUP DATABASE;

Lister les sauvegardes :

RMAN> LIST BACKUP [SUMMARY];

RMAN> CROSSCHECK BACKUPSET; contrôler les références entre le catalogue et le disque

Exemple de sauvegarde :

Sauvegarde de base complète + archive log + supprime les archive logs originaux

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE Tag 'Data_Full' plus ARCHIVELOG Tag

'Arch_Full' delete all input;

12.4.5 Test : utilisation recovery automatique


C:\> set oracle_sid = ORCL

C:\> rman target /

RMAN> BACKUP DATABASE;

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE;

RMAN> report obsolete; voir les sauvegardes obsolètes

RMAN> list backup summary;

RMAN> delete obsolete; supprimer les sauvegardes obsolètes

RMAN> RESTORE DATABASE VALIDATE; pour tester

Sur Linux (base ORA10) :

$ export ORACLE_SID=ORA10 $ cd $ORACLE_HOME/bin $ ./rman target / RMAN> refaire les paramètres + BACKUP DATABASE; + RESTORE DATABASE VALIDATE; RMAN> exit; $ ./sqlplus odehecq/password SQL> CREATE TABLE tb_kill (code number, libelle varchar22(20)); SQL> INSERT INTO tb_kill VALUES (1,'ligne1'); SQL> COMMIT; $ ps –ef | grep dbw0 $ kill -9 <n°PID récupéré> $ ./sqlplus / as sysdba SQL> startup SQL> connect odehecq/password SQL> SELECT * FROM tb_kill;

Tout va bien !

$ less /oracle/admin/ORA10/bdump/alert_ORA10.log

/crash pour rechercher “/crash”

Completed crash recovery at …

Oracle a de lui-même fait un recovery

12.4.6 Test : suppression de controlfile

Sur linux (base ORA10) :

$ ./sqlplus system/oracle SQL> show parameter control_file; /oracle/oradata/ORA10/control01.ctl,/oracle/oradata/ORA10/control02.ctl,oracle/oradata/ORA10/control03.ctl

Sur une 2e session putty :

$ rm /oracle/oradata/ORA10/control01.ctl

Retour sur la 1ere session :

SQL> select * from v$instance; SQL> select count(*) from all_tables;


SQL> insert into odehecq.tb_kill values (2,'ligne2'); SQL> COMMIT;

Pour le moment, tout semble aller bien mais …

SQL> connect / as sysdba SQL> shutdown immediate SQL> shutdown abort SQL> startup $ less /oracle/admin/ORA10/bdump/alert_ORA10.log

ORA-00202: control file: '/oracle/oradata/ORA10/control01.ctl'

ORA-27037: unable to obtain file status

Résolution de la panne:

$ cp /oracle/oradata/ORA10/control02.ctl /oracle/oradata/ORA10/control01.ctl $ ./sqlplus connect / as sysdba SQL> shutdown immediate SQL> startup

Problème résolu, grâce à la redondance des fichiers contrôle

12.4.7 Test : suppression de datafile

Sur linux (base ORA10) :

$ ./sqlplus system/oracle SQL> SELECT tablespace_name FROM user_table WHERE table_name='TB_KILL'; SYSTEM SQL> ALTER TABLE tb_kill ADD (flag number); SQL> UPDATE tb_kill SET flag=10; SQL> SELECT * FROM tb_kill; SQL> COMMIT;

Sur une 2e session Putty :

$ rm /oracle/oradata/ORA10/system01.dbf

Retour sur la 1ere session :

SQL> alter system flush buffer_cache; SQL> SELECT * FROM tb_kill;

Ça ne fonctionne plus

12.5 Restauration d’une base

SQL> connect / as sysdba; SQL> shutdown abort

Copie de fichier : sauvegarde à froid :

Sauvegarde à froid = sauvegarde cohérente (au niveau des SCN des controlfiles) = consistent

backup

Sauvegarde à chaud :

Sauvegarde incohérente (désynchronisation entre les numéros de transaction selon les

tablespaces et les numéros de transaction des controlfiles) = inconsistent backup

1. Restore (restauration de la dernière sauvegarde)

2. Recovery (rejoue les transactions des redologs pour arriver au dernier numéro de

transaction)

Méthode pas à pas

$ ./rman target / RMAN> STARTUP MOUNT; RMAN> RESTORE TABLESPACE SYSTEM;

On vérifie que le fichier a bien été recréé RMAN> RECOVER TABLESPACE SYSTEM; RMAN> sql "alter database open"; RMAN> exit $ ./sqlplus system/oracle SQL> SELECT * FROM tb_kill;


12.5.1 Cas particulier : DROP d’une table non voulu

PITR (Point In Time Recovery) :

Méthode approximative, on espère arriver au plus près avant le « DROP TABLE … »

Long (il faut retrouver la bonne heure), sauf si on utilise le SCN

SCN : chaque transaction est notée :

Avec logminer, on trouve le numéro de la transaction « DROP TABLE … »

1. On récupère le numéro de transaction

2. On fait un RECOVER jusqu’à la (transaction -1)

Exemple de restauration avec le PITR :

$ ./sqlplus system/oracle SQL> SELECT current_scn FROM v$database; 1239801 SQL> DROP TABLE user.tb_kill; SQL> DESC user.tb_kill; la table est bien supprimée SQL> connect / as sysdba SQL> shutdown immediate;

Une sauvegarde consistante de la base permet tout de même de se prémunir d’une mauvaise

restauration

$ ./rman target / RMAN> STARTUP MOUNT; RMAN> RESTORE DATABASE; RMAN> RECOVER DATABASE UNTIL SCN 1239801; RMAN> sql "alter database open resetlogs"; resetlogs : reset des online redolog

Effectuer ensuite une sauvegarde complète avant de redonner la main aux utilisateurs, car ce n’est

plus la même incarnation de la base de données.

A CHAQUE INCARNATION : SAUVEGARDE COMPLETE

Schéma récapitulatif de l’exercice

$ ./sqlplus user/password SQL> SELECT * FROM tb_kill;

Aparté : Astuce : sauvegarde sur disque, puis sauvegarde sur robot

Fiable : en cas de pannes de robot, il y a toujours une sauvegarde. Gain de performances.

Moins cher

Moins ergonomique, besoin de connaitre rman