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 !

M106 - Jour 7

By paranor

M106 - Jour 7

  • 20

More from paranor