Module 106

Interroger, traiter et assurer la maintenance des bases de données

Jour 7 - Sauvegardes des bases de données et modification des bases de données

# Objectifs du jour

Objectifs du jour

Modification du Mindmap
Correction de l'exercice des index (Exercice 4.2)
Présentations des sauvegardes de base de données
Exercice sur les sauvegardes
Modification et migration d'un schéma
Exercice relatif à la modification d'un schéma

# Mindmap

Modification du Mindmap

Mise à jour du Mindmap

Exercice 4.2

Adaptation du MPD pour optimiser les performances avec les index

Correction de l'exercice sur les index

# Commandes SQL

https://divtec.gitbook.io/ict-106

# Sauvegardes des bases de données

Sauvegardes des bases de données

Pourquoi faire des sauvegardes ?

Éviter les pertes de données : erreurs, crash, suppression involontaire
Restaurer rapidement une base cohérente
Sécurité métier : éviter l’interruption d’un service critique
Gérer des environnements de test ou de développement à partir de données réelles
Préparer des migrations ou des montées de version

Vous aussi ça vous donne envie de jouer ?

# Sauvegardes des bases de données

Tester les sauvegardes

https://www.20min.ch/fr/story/obwald-il-efface-toutes-les-donnees-par-erreur-la-societe-fait-faillite-103311727

Une sauvegarde non testée = une sauvegarde inutile

Vérifier que le fichier de dump est lisible et complet
S'assurer que la restauration fonctionne
- Tester régulièrement la restauration

# Sauvegardes des bases de données

Bonnes pratiques

Tester les sauvegardes (régulièrement)
Identifier les erreurs potentielles (droits, chemins, noms de répertoire) ; Surveiller les logs pour détecter erreurs ou avertissements
Réaliser des tests de restauration partielle ou complète
Mesurer le temps de récupération
Documenter la procédure de récupération (et la tester avec une autre personne !)
Protéger les fichiers de dump (droits Unix/Windows, chiffrement si nécessaire)
Être prêt en cas d'incident réel

# Sauvegardes des bases de données

Eléments à sauvegarder en BDD

Les données (DATA)

Le contenu des tables (lignes)

La structure de la BDD (METADATA)

Tables, vues, séquences, index
Contraintes, clés primaires/étrangères
Procédures stockées, triggers, packages

Autres

Les utilisateurs, rôles, etc.
Les droits des utilisateurs, des rôles, etc.

# Sauvegardes des bases de données

Types de sauvegarde

Locale : sauvegarde sur le serveur ou un disque externe, clé USB > rapide et facile mais à risque

Complète : toute la base, tout le schéma

Partielle : un ou plusieurs schémas, tables

Incrémentale (avec RMAN) : uniquement les blocs modifiés

Sauvegarde locale vs distante

Distante : sur un serveur ou cloud, plus sûr

# Sauvegardes des bases de données

La règle du 3-2-1

3 copies des données

2 supports de stockage différents

1 copie originale (celle en production, utilisée tous les jours)

2 copies de sauvegarde (dump Oracle, copie système, etc.)

Réduit le risque de perte totale (par accident, bug, attaque…)
Si l’original est corrompu, il y a encore deux chances de
récupération

Exemples : Disque local + NAS / Clé USB + cloud / SSD interne + disque dur externe

Si un support tombe en panne, il reste une autre technologie indépendante

# Sauvegardes des bases de données

La règle du 3-2-1

1 copie hors site

Emplacement différent physiquement du système principal

Exemple :

Sur le cloud (Proton Drive, Google Drive, etc.)
Sur un autre serveur distant
Même un disque dur stocké ailleurs, sur un autre site, à la banque, chez soi, etc.

Pourquoi ?

Protection contre les sinistres majeurs : incendie, inondation, vol, etc.
Garantit qu’au moins une copie ne sera pas affectée par le même incident

# Sauvegardes des bases de données

La règle du 3-2-1

1 copie hors site

Attention notamment au ransomware :

Automatiser sans synchroniser, gérer les versions ou en lecture seule pour ne pas que les sauvegardes soit également affectés

Les bonnes pratiques restent valables : Tester les sauvegardes (régulièrement), avoir une procédure, etc.

# Sauvegardes des bases de données

Les outils (expdp/impdb et RMAN)

Aspect	expdp / impdp	RMAN (Recovery Manager)
🔍 Type de sauvegarde	Logique (objets, données, métadonnées SQL)	Physique (blocs disque de la base Oracle)
🧠 Ce qui est sauvegardé	Tables, vues, schémas, procédures, données	Fichiers de données (.dbf), control file, redo logs
🎯 Usage principal	Migration, duplication, tests, export partiel	Restauration intégrale après crash (niveau bas)
💾 Format	Fichier .dmp (lisible uniquement par Oracle)	Fichiers binaires internes Oracle
🔄 Restauration partielle	✅ Oui (table, schéma, données spécifiques)	❌ Non (base complète ou tablespace uniquement)
🛡️ Protection contre crash	❌ Non, pas suffisant seul	✅ Oui, complet + gestion des journaux
🔧 Facilité d’utilisation	Simple pour dev/test, scriptable	Plus complexe, réservé à la prod ou aux DBA

# Sauvegardes des bases de données

Les outils (expdp/impdb et RMAN)

Quand expdp/impdp suffit :

Applications simples ou non critiques
Besoin uniquement de la structure et des données
Pas besoin de rejouer des transactions / garder les journaux des erreurs, logs, etc.
Exemple : petite application interne, site vitrine, maquettes, projets de tests, etc.

Quand RMAN est nécessaire :

Données critiques ou volumineuses
Base utilisée 24/7 (avec journalisation active)
Besoin de continuité de service / reprise après sinistre
Exemple : ERP, base d’un hôpital, d’une banque, etc.

# Sauvegardes des bases de données

Commandes expdp / impdb

Commande pour export :

Exporter un schéma

Commande pour import :

expdp system/manager@xepdb1 SCHEMAS=magasin DIRECTORY=EXPORT_DIR DUMPFILE=magasin.dmp LOGFILE=export_magasin.log

impdp system/manager@xepdb1 SCHEMAS=magasin DIRECTORY=EXPORT_DIR DUMPFILE=magasin.dmp LOGFILE=magasin_import.log

Base entière (avec users, droits, etc)

Commande pour export :

Commande pour import :

expdp system/manager@xepdb1 FULL=Y DIRECTORY=EXPORT_DIR DUMPFILE=full_db.dmp LOGFILE=full.log

impdp system/manager@xepdb1 FULL=Y DIRECTORY=EXPORT_DIR DUMPFILE=full.dmp LOGFILE=magasin_import.log

# Sauvegardes des bases de données

Commandes expdp / impdb

Restaurer certaines tables uniquements

Commande pour import :

impdp magasin/magasin@xepdb1 TABLES=magasin.produits,magasin.commandes DIRECTORY=EXPORT_DIR DUMPFILE=magasin.dmp LOGFILE=import_tables.log

Uniquement les données ou la structure

Commande pour import de la structure uniquement :

impdp magasin/magasin@xepdb1 TABLES=produits DIRECTORY=EXPORT_DIR DUMPFILE=magasin.dmp LOGFILE=import_ddl_only.log CONTENT=METADATA_ONLY

Commande pour import des données uniquement :

impdp magasin/magasin@xepdb1 TABLES=produits DIRECTORY=EXPORT_DIR DUMPFILE=magasin.dmp LOGFILE=import_ddl_only.log CONTENT=DATA_ONLY

# Sauvegardes des bases de données

Planifier des sauvegardes automatiques

Généralement, les sauvegardes sont automatisées.

Voici le processus pour le faire automatiquement avec expdp :

#!/bin/bash

# === Config personnalisable ===
ORACLE_USER="system"
ORACLE_PASS="manager"
ORACLE_SVC="xepdb1"
EXPORT_DIR="EXPORT_DIR"
DUMP_PATH="/opt/exports"
RETENTION_DAYS=7

# === Générer timestamp ===
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DUMPFILE="full_export_${TIMESTAMP}.dmp"
LOGFILE="full_export_${TIMESTAMP}.log"

# === Lancer l'export FULL ===
expdp ${ORACLE_USER}/${ORACLE_PASS}@${ORACLE_SVC} \
  FULL=Y \
  DIRECTORY=${EXPORT_DIR} \
  DUMPFILE=${DUMPFILE} \
  LOGFILE=${LOGFILE}

# === Nettoyage des vieux dumps ===
find "${DUMP_PATH}" -type f -name "full_export_*.dmp" -mtime +${RETENTION_DAYS} -exec rm -f {} \;
find "${DUMP_PATH}" -type f -name "full_export_*.log" -mtime +${RETENTION_DAYS} -exec rm -f {} \;

Création d'un script qui utilise la commande expdb :

Exécuter le script automatiquement via cron ou planificateur Windows

crontab -e 0 2 * * * /opt/scripts/oracle_full_export.sh

# Sauvegardes des bases de données

Exercice 5.1

Exercice de sauvegarde et restauration

Exercice sauvegarde

https://divtec.gitbook.io/ict-106

# Modification d'un schéma de BDD

Modification d'un schéma de BDD

Objectifs

Modifier la structure d’une base existante
- LDD
Migrer des données vers une nouvelle table ou nouveau schéma
Garantir l’intégrité et l’exhaustivité des données

# Modification d'un schéma de BDD

Modification d'un schéma

LDD - Langage de Définition de Données : permet de créer, modifier ou supprimer la structure d’une base

Commandes utiles

ALTER TABLE
ADD COLUMN
DROP COLUMN
MODIFY COLUMN
RENAME COLUMN
ADD CONSTRAINT

# Modification d'un schéma de BDD

ALTER TABLE produits ADD nbr_stock_pro INT;

Commande ADD COLUMN

Exemple de add column pour ajouter le nombre de produits en stock :

ALTER TABLE produits DROP COLUMN description_pro;

Commande DROP COLUMN

Exemple pour supprimer la colonne description :

ALTER TABLE produits MODIFY nom_pro VARCHAR(255);

Commande MODIFY COLUMN

Modifier la colonne nom pour augmenter la longueur à 255 caractères

Commande ALTER TABLE

ALTER TABLE produits
{...}

Suivi des modifications souhaitées (voir points ci-dessous)

# Modification d'un schéma de BDD

ALTER TABLE produits RENAME COLUMN nom_pro TO appelation_pro;

Commande RENAME COLUMN

Renommer nom_pro en appelation_pro :

ALTER TABLE clients ADD CONSTRAINT ct_nn_email_cli CHECK (email_cli IS NOT NULL);

Commande ADD CONSTRAINT

Ajouter une contrainte non null sur l'email dans la table clients :

Autres commandes utiles

Copier une table et ses données :

Copier les données d'une table dans une autre :

CREATE TABLE produits_test AS SELECT * FROM produits;

INSERT INTO produits_test SELECT * FROM produits;
-- OU seulement certains champs
INSERT INTO produits_test (id_produit, nom, prix, id_categorie)
SELECT id_produit, nom, prix, id_categorie FROM produits;

# Modification d'un schéma de BDD

Attention aux modifications

Faire une sauvegarde : Avant toute opération de type ALTER TABLE, exporter la table ou la base
Vérifier les contraintes (notamment de clés étrangères) : Modifier ou supprimer une colonne liée à contrainte peut causer une erreur SQL ou casser une relation
Impact sur les applications connectées : Un champ renommé ou supprimé peut casser une application qui l’utilise
- Communication avec les développeurs ou tests de validation indispensables

# Modification d'un schéma de BDD

Attention aux modifications

Modifications destructrices (avec pertes de données)
- Diminuer la taille d’un champ : Tronque le champ ou provoque une erreur (c'est le cas en oracle)
- Changement de type : Oracle peut lever une erreur de conversion si le contenu ne correspond pas
- Suppression de colonne : Suppression immédiate et irréversible dans Oracle

# Modification d'un schéma de BDD

Comment procéder ?

On veut éviter de modifier brutalement une table en production.
Faire une sauvegarde de la base de données
Monter la base de données en test
Faire les modifications sur la base de données en test
Dupliquer ou modifier la table et insérer les données
Tester l'intégrité : Même nombre de lignes, aucune erreur, etc.
Documenter les changements
Tester les applications, communiquer avec les développeurs

# Sauvegardes des bases de données

Exercice 6.1

Modification de la base de données

Exercice de modification de la BDD

https://divtec.gitbook.io/ict-106

Merci de votre attention 😊
Bonne fin de journée !