Catégories
Expert SGBD

PostgreSQL ne démarre plus : PANIC could not locate a valid checkpoint record

Un message d’erreur qui peut arriver après avoir fait un ménage trop important dans les logs, un espace disque insuffisant ou un simple plantage du SGBD. Le service postgresql ne démarre plus correctement, que ce soit sur Linux ou sur Windows. En fouillant dans les logs du serveur PostgreSQL, on trouve ce message d’erreur :

PANIC: could not locate a valid checkpoint record

ou en français :

PANIC: n’a pas pu localiser un enregistrement d’un point de vérification valide

Ce tutoriel est basé sur un exemple concret : le service PostgreSQL Server ne démarre pas sur Windows et aucun message graphique n’explique le problème. Il explique comment résoudre l’erreur « PANIC: could not locate a valid checkpoint record » de PostgreSQL toutes versions, que l’on peut lire dans le fichier log courant. La réponse est un peu dans la question mais un accompagnement ne fait pas de mal pour corriger cette erreur bloquante. Ce guide montre la solution pour les versions Postgre inférieures à 10 (7, 8, 9) et supérieures/égales à 10 (10, 11…).

 

Solution à l’erreur Postgre : PANIC: could not locate a valid checkpoint record

Serveur PostreSQL 10 et 11

1. Ouvrir un Invite de commandes (cmd) en tant qu’Administrateur.

2. Naviguer dans le dossier d’installation « bin » de PGSQL :

cd C:\Program Files\PostgreSQL\10\bin

3. Exécuter la commande suivante pour vérifier, en indiquant le chemin des bases de données :

pg_resetwal DATADIR

soit par exemple :

pg_resetwal "D:\BasesPostgreSQL10"

4. Et celle-ci pour forter le reset :

pg_resetwal -f DATADIR

soit :

pg_resetwal -f "D:\BasesPostgreSQL10"

5. Le service PostgreSQL Server peut à nouveau démarrer.

 

Serveur PostreSQL 9

1. Ouvrir un Invite de commandes (cmd) en tant qu’Administrateur.

2. Naviguer dans le dossier d’installation « bin » de PGSQL :

cd C:\Program Files\PostgreSQL\9.6\bin

3. Exécuter la commande suivante pour vérifier, en indiquant le chemin des bases de données :

pg_resetxlog DATADIR

soit par exemple :

pg_resetxlog "D:\BasesPostgreSQL9"

4. Et celle-ci pour forter le reset :

pg_resetxlog -f DATADIR

soit :

pg_resetxlog -f "D:\BasesPostgreSQL9"

5. Le service PostgreSQL Server peut à nouveau démarrer correctement.

Catégories
Expert SGBD

Configuration mémoire (RAM) conseillée pour SQL Server

D’origine, un serveur SQL Server s’installe avec des paramètres par défaut qui ne sont pas vraiment optimisés. Etonnant ? Pas tant que ça. Microsoft s’assure que son logiciel SGBD puisse être installé sur un maximum de configurations matérielles et logicielles. Et pour être compatible avec le plus grand nombre, les optimisations ne sont pas appliquées avec une installation standard par défaut. Il faudra se documenter, avoir de l’expérience, suivre une formation ou demander un audit pour trouver les bons paramètres qui seront adaptés à l’usage du serveur SQL.

Les recommandations suivantes sont assez génériques mais sont applicables à la plupart des situations où une base de données SQL Server est utilisée par un logiciel, en production.

 

Configuration par défaut de Microsoft SQL Server

1. Ouvrir le logiciel Microsoft SQL Server Management Studio (SSMS).

2. Se connecter à un serveur de bases de données, local ou distant.

3. Faire un clic droit sur le nom du serveur puis Propriétés.

4. Cliquer sur la page « Mémoire » pour afficher la configuration actuelle de SQL Server. Par défaut, on retrouve :

  • Mémoire minimale du serveur : 0 Mo
  • Mémoire maximale du serveur : 2147483647 Mo (soit 2To)

 

Recommandations pour gérer la RAM avec SQL Server

A moins d’avoir plus de 2To de RAM sur ce serveur, il n’est pas recommandé de laisser SQL Server prendre toute la RAM possible, ceci au détriment des performances générales du système d’exploitation. Le SGBD va utiliser toute la RAM disponible pour se gaver en mémoire rapide, sans tenir compte des autres usages du serveur. Si la machine est aussi utilisée pour héberger une application métier (comptabilité, ERP, etc), c’est assurément des performances amoindries pour les autres logiciels. Si le serveur SQL est installé sur un poste de travail ou un ordinateur portable, par exemple chez un développeur, un commercial pour des démo ou sur le PC d’un consultant, il est également indispensable de limiter la mémoire maximale consommée par MSSQLSERVER. Deux règles pour définir cette valeur.

Selon la taille des bases de données

Admettons que le serveur SQL n’héberge qu’une seule BDD qui représente un espace disque de 3Go (taille du fichier MDF). Il n’y a pas vraiment d’intérêt que le service SQL Server occupe 7Go de RAM et mette à genou les autres processus actifs de Windows. La recommandation est de limiter la mémoire maximale à la taille de la base. Dans l’exemple d’une base qui pèse 3Go, on limitera donc la mémoire SQL Server à 3Go, ou 3072Mo. Les modifications sont immédiates, pas besoin de redémarrer l’instance, le service ou Windows.

Selon la quantité de RAM du serveur

Autre possibilité, limiter la mémoire du processus sqlserver.exe selon la quantité de mémoire vive du serveur. Si la machine (virtuelle ou physique) dispose de 8Go de RAM et qu’aucun autre rôle n’est attribué à ce serveur (pas d’hébergement d’application, de site web, d’autre rôle serveur), on laissera entre 1 et 2Go de RAM disponible au système d’exploitation Windows. Ainsi, on limitera la mémoire maximale de SQL Server à 6Go (6144Mo) ou 7Go (7168Mo). MSSQL n’est pas à cheval sur les Go et on pourra couper la poire en deux et indiquer 6500Mo.

Bien sûr, si le serveur en question abrite un SQL Server mais également d’autres rôles, il faudra limiter la mémoire en fonction de ces autres usages pour que chacun ait son espace de mémoire, sans perturber le bon fonctionnement des programmes voisins.

Catégories
Expert SGBD

Oracle : voir le jeu de caractères

Si les humains utilisent différents alphabets (latin, cyrillique, arabe…), les ordinateurs aussi communiquent avec différentes manières d’écrire. Les plus anciens codages de caractères sont le code international des signaux maritimes ou encore l’alphabet Morse (1838) mais les machines utilisent plutôt le standard américain ASCII, le Standard Unicode UTF ou d’autres caractères ISO. A titre d’information, le codage UTF-8 est utilisé par plus de 90% de sites web dans le monde, faisant de lui un standard pour l’échange d’informations.

Comme tout système informatique, le serveur de bases de données Oracle Database utilise lui aussi les jeux de caractères. Ceux-ci peuvent être choisis au niveau du SGBD ou un encodage peut être appliqué à chaque base de données. Ce tutoriel explique comment voir le jeu de caractères (characterset) utilisé par un serveur Oracle Database : WE8ISO8859P15, AL32UTF8, AL16UTF16…

Ce paramètre se configure lors de l’installation de Oracle Database :

On peut d’ailleurs modifier le jeu de caractères d’une BDD lors d’un export / import mais attention à la corruption et la perte de données.

 

Voir le jeu de caractères utilisé dans Oracle Database

1. Ouvrir une console SQL*Plus ou un Invite de commandes Windows et se connecter à sqlplus.

2. Copier / coller la commande suivante :

select * from nls_database_parameters;

3. Le résultat indique le Jeu de caractères utilisé à la ligne NLS_CHARACTERSET :

  • AL32UTF8 pour CESU-8, un codage de caractères variante d’UTF-8 depuis Oracle 9

  • WE8ISO8859P15 pour ISO/CEI 8859-15, le code numérique tenant sur 8 bits aux caractères de l’alphabet latin

  • AL16UTF16, une variante de UTF-8

Catégories
SGBD

Présentation Oracle Database 18c

Oracle ne sort pas régulièrement de nouvelle version de son serveur Database. L’édition précédente, la 12c, est disponible depuis 2013. Auparavant, la 11g R1 était sortie en septembre 2007 (la 11g R2 deux ans plus tard), la 10g R1 en 2003. C’est maintenant un rythme annuel de versions qu’Oracle nous a promis. Oracle Database 18c correspond à l’année 2018, la prochaine sera donc Oracle 19. La version 18.1 réservée au Cloud a été publiée en février 2018 mais l’édition On premise (on-prem) n’est disponible que depuis juillet 2018 avec la 18.3. Oracle Database 18c et Oracle 12c sont les seules versions encore supportées par l’éditeur.

Oracle a donc modifié la dénomination de ses versions du serveur de base de données mais aussi la fréquence de publication. Une version par an et une mise à jour par trimestre. L’annuelle dans un mode DevOps, les updates trimestrielles pour de la maintenance proactive. Et des révisions pour corriger des bugs. Ainsi, on obtient des numéros de version de type Année.Update.Révision (18.3.0, par exemple).

Pour se former à Oracle 18, il existe des formations : New Features for Administrators, High Availability New Features, Administration Workshop.

 

Principales nouveautés Oracle 18c

Disponibilité

  • Transparent Application Continuity augmente la disponibilité sans modification des applications
  • Meilleure disponibilité des services durant les maintenances planifiées (draining sessions)
  • Prise en charge d’un clone de la BDD par Oracle ASM 18c (Point-In-Time Database Clones)

Sécurité

  • Possibilité de créer un compte Oracle sans mot de passe (NO AUTHENTIFICATION) : Schema-Only Account
  • Intégration Active Directory (informations d’identification et autorisations) : Centrally Managed Users (CMU)

Sauvegarde RMAN

  • Automatisation de la synchronisation d’une base de secours à partir d’une base principale (RECOVER FROM SERVICE)
  • Possibilité de cloner une PDB active dans une autre CDB

Général

  • Optimisation de la fonction Automatic In-Memory
  • Les SQL Tuning Set (STS) sont maintenant inclus dans Oracle Database Enterprise (donc gratuit) et plus en option pack Tuning
  • Nouveau package DBMS_SQLSET disponible pour gérer les STS
  • SQL Tuning Advisor devient EXADATA Aware
  • Interrogation d’une table externe sans créer d’objet persistant avec un SELECT … EXTERNAL
  • Nouvelles opérations de maintenance disponible en ligne (sans arrêt) : DROP INDEX, ALTER TABLE MOVE, ALTER TABLE DROP CONSTRAINT, ALTER INDEX UNUSABLE…
  • Les bases Oracle 18c supportent Unicode 9.0

 

Documentation des nouveautés Oracle Database 18c

Télécharger “Introducing Oracle Database 18c” oracledatabase18c.pdf – Téléchargé 456 fois – 1 Mo

Catégories
Expert SGBD

Importer et exporter une base de données PostgreSQL avec pgAdmin

PostreSQL est un outil libre pour gérer ses bases de données. Le plus avancé des SGBD (world’s most advanced open source database, d’après eux) est compatible avec les systèmes d’exploitation Solaris, Linux, Unix, AIX, *BSD, macOS et Windows. Si la partie serveur est ainsi multiplateforme, la gestion peut se faire à distance en ligne de commande (psql) ou avec un client pgAdmin, outil d’administration graphique spécifique à Postgre. Il existe également les interfaces phpPgAdmin et Adminer pour accéder à un serveur PGSQL.

Ce tutoriel explique comment sauvegarder (exporter) et restaurer (importer) une base de données sur un serveur PostgreSQL avec l’outil graphique pgAdmin. Une méthode par interface graphique qui nécessite un compte avec les droits adéquats pour ces opérations de maintenance et d’administration. L’utilitaire pgAdmin4 permet d’accéder à un serveur PostgreSQL 9.x, 10.x, 11.x et sûrement aussi les versions ultérieures.

 

Import de base Postgre avec pgAdmin

1. Se connecter au serveur PGSQL avec pgAdmin (ici en version 4).

2. Créer une base de données vierge (clic droit, Create, Database) sauf si elle existe déjà.

3. Faire un clic droit sur la base de données, Restore.

4. A l’onglet General, dans le champ Filename, charger le fichier depuis le disque du serveur local ou un partage du réseau.

Si le fichier n’apparait pas dans la liste, modifier le Format de fichiers recherché en bas à droite.

5. Cliquer sur Restore pour importer la base. Un message « Successfully completed » indique que l’opération s’est bien déroulée.

 

Export de base PostgreSQL avec pgAdmin

1. Se connecter au serveur PG SQL avec pgAdmin4.

2. Faire un clic droit sur la base de données, Backup.

3. Indiquer où sauvegarder la base dans le champ Filename et préciser le nom du fichier avec son extension.

Par défaut, le format de sauvegarde est un .sql : modifier en format .backup si nécessaire.

4. Cliquer sur le bouton Backup pour lancer l’export.

Catégories
Expert SGBD Téléchargement

Télécharger et installer serveur Oracle Database Express gratuit

Si le prix des licences Oracle Database explose, mettant les clients dans une impasse financière ou les incitant à changer de SGBD pour leurs applications métier, l’entreprise californienne offre une version gratuite et limitée de son logiciel phare. Sous le nom complet de Oracle Database Express Edition 11g Release 2, il s’agit ainsi d’une version allégée du serveur de bases de données Oracle Database 11g R2, malheureusement indisponible dans la release 12c. Publiée depuis 2009, Oracle Express 11g R2 fait suite à Oracle Database 10g Express Edition sortie en 2005. Depuis, et si Oracle Database 12c version complète payante est sortie en juillet 2013, il n’y a pas eu d’édition gratuite de cette v12.

Au niveau des limitations, Oracle 11g Express Edition offre un maximum de 11Go pour les données utilisateur et 1Go pour la SGA (System Global Area). C’est souvent bien suffisant pour développer une application ou faire des démonstrations clients (ingénieur commercial, avant-vente technique…). Cela permet aussi de se familiariser avec l’environnement SGBDRO de Oracle Corporation sans débourser un centime. Oracle Database Express est l’équivalent de Microsoft SQL Server Express, l’édition gratuite du serveur de bases de données.

Ce guide explique où télécharger et comment installer Oracle Database Express Edition 11g R2, qui existe pour Windows (64 et 32 bits) ainsi que pour Linux x64. Le serveur gratuit Oracle est compatible avec les postes de travail Windows (10, 8, 7), il n’est pas obligatoire de l’installer sur un environnement Windows Server. Il est par contre nécessaire de posséder un compte Oracle pour le télécharger, ce qui est gratuit à créer.

 

Télécharger Oracle Database Express Edition 11g Release 2 (gratuit)

1. Aller sur la page de téléchargement Oracle Database 11g R2 Express.

Cela se trouve dans Oracle Technology Network > Database > Database Express Edition > Download.

2. Cliquer sur « Accept License Agreement » pour déverrouiller les liens de téléchargement.

3. Cliquer sur « Oracle Database Express Edition 11g Release 2 for Windows x64 » et s’identifier avec un login Oracle (gratuit).

4. Le téléchargement est immédiat.

 

Installer Oracle Database Express Edition 11g R2

1.. Décompresser le fichier OracleXE112_Win64.zip téléchargé (ou OracleXE112_Win32.zip ou oracle-xe-11.2.0-1.0.x86_64.rpm.zip pour Linux).

2. Ouvrir le dossier DISK1 et double cliquer sur setup.exe.

3. Bienvenue sur l’assistant d’installation Oracle Database 11g Express, InstallShield Wizard.

4. Par défaut, l’installation se fait dans C:\oraclexe et nécessite 632Mo d’espace disque.

5. Indiquer un mot de passe pour les comptes SYS et SYSTEM (le même password). Valider les paramètres d’installation en cliquant sur Install. Le Listener sera sur le port 1521, comme sur la version Enterprise de Oracle Database.

6. L’installation démarre et ne demande pas plus de renseignements. Cinq services locaux sont créés dont deux sont en démarrage automatique : OracleServiceXE et OracleXETNSListener. On peut les passer en démarrage manuel pour éviter de surcharger la RAM de l’ordinateur si le serveur Oracle n’est pas utilisé tous les jours.

7. Le fichier tnsnames.ora se trouve par défaut dans C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN.

8. On peut vérifier le bon fonctionnement du SGBD en s’y connectant soit en SQLPlus, en SQL Developer ou avec le SQL Command Line intégré à Oracle XE.

  • Ouvrir « Run SQL Command Line« 
  • connect
  • system ou sys
  • mot de passe défini lors de l’installation

Si le message « Connected » apparait, cela signifie que l’installation est validée. On peut maintenant importer des bases et travailler dessus, par exemple avec SQLDeveloper.

Catégories
Expert SGBD

SQL Server : activer les fonctions FullText et FileStream

Les versions les plus récentes de SQL Server supportent la recherche Full-Text (recherche en texte intégral) et le FileStream. Pour simplifier, ces fonctions améliorent les performances du SGBD dans la recherche et en utilisant le cache système de Windows pour le File Stream. Ce tutoriel explique comment activer FULLTEXT et FILESTREAM dans SQL Server. La recherche FullText est l’extraction en texte intégral et extraction sémantique de recherche. FILESTREAM est utile pour le stockage des fichiers dans la base de données.

C’est par exemple le cas des applications Sage 100c à partir de la version 3.00 (2018), Essentials ou Standard.

 

Actifer FullText et Filestream dans Microsoft SQL Server

1. Ouvrir le Gestionnaire de configuration de SQL Server (menu Démarrer, Programmes, Microsoft SQL Server XXXX, Outils de configuration, Gestionnaire de configuration SQL Server).

2. Dans le menu de gauche, cliquer sur Services SQL Server de l’instance à modifier.

3. A droite, double cliquer sur SQL Server (INSTANCE), par défaut SQL Server (MSSQLSERVER) ou SQL Server (SQLEXPRESS).

4. Aller sur l’onglet FILESTREAM et activer les options :

  • Cocher « Activer FILESTREAM pour l’accès Transact-SQL« 
  • Cocher « Activer FILESTREAM pour l’accès d’E/S de fichier« 
  • Laisser le Nom de partage Windows qui est proposé
  • Cocher « Autoriser les clients distants à avoir un accès aux données FILESTREAM« 

5. Ouvrir SQL Management Studio (menu Démarrer, Programmes, Microsoft SQL Server XXXX, SQL Management Studio).

6. Se connecter à l’instance en question.

7. Clic droit sur cette instance, Propriétés.

8. Aller sur la page Avancé.

9. Dans le groupe FILESTREAM, changer le Niveau d’accès FILESTREAM par Accès total activé.

 

Activation de FILESTREAM en ligne de commande

En ligne de commande, en requête TSQL sp_filestream_configure pour activer le FILESTREAM par SQL Management Studio.

EXEC sp_filestream_configure 
@enable_level = 3, 
@share_name = "MSSQLSERVER";
RECONFIGURE

S’il s’agit d’une activation post-installation de Filestream, il faut aussi configurer le niveau d’accès sur l’instance SQL :

EXEC sp_configure 'filestream_access_level', 2;
GO
RECONFIGURE
GO

Où la valeur se définit selon :

  • 0 : FILESTREAM désactivé
  • 1 : FILESTREAM activé pour TSQL
  • 2 : FILESTREAM activé pour TSQL et API Win32
Catégories
Expert SGBD

Oracle : voir ce qui consomme de la ressource temporaire

Le SGBD Oracle est un moteur puissant et très efficace.. quand on sait le gérer. Si des requêtes prennent plus de temps que d’habitude ou si le tablespace temporaire est saturé, il existe un moyen de voir les connexions qui consomment des ressources temporaires. C’est une manière de voir à un instant précis s’il y a de trop nombreuses connexions, des requêtes trop lourdes et pour comprendre pourquoi le fichier TEMP01.DBF augmente de manière inhabituelle.

Le tablespace temporaire TEMP est utilisé pour gérer les longues requêtes qui ne peuvent être exécutées dans la mémoire PGA (Program Global Area). Ce fichier peut donc rapidement se remplir si les demandes sont régulières, gourmandes en ressource ou si le serveur de base de données est mal configuré. Elément de réponse avec cette requête qui donne une vue sur l’utilisation du tablespace TEMP d’un serveur Oracle.

 

Voir l’utilisation de la mémoire temporaire Oracle

1. Ouvrir une connexion SQLPlus ou SQL Developer sur le serveur Oracle, avec un compte sysdba.

2. Copier / coller la requête suivante :

SELECT se.osuser, se.username, se.sid,
su.extents, su.blocks * to_number(rtrim(p.value)) as Space,
tablespace
FROM v$sort_usage su, v$parameter p, v$session se
WHERE p.name = 'db_block_size'
AND su.session_addr = se.saddr
ORDER BY se.username, se.sid

3. Le retour est de ce type :

Et dans le cas où il y a une grosse consommation de TEMP :

4. On peut ainsi identifier l’utilisateur Oracle et le SID qui utilisent le tablespace temporaire TEMP du serveur Oracle.

Catégories
Expert SGBD

Oracle : supprimer et recréer le tablespace Temp

Le SGBD Oracle fonctionne avec des tablespaces et les requêtes sont généralement effectuées en mémoire (PGA), sauf si celle-ci est insuffisante en taille. Oracle utilisera alors le TEMPORARY TABLESPACE nommé TEMP par défaut, avec le fichier TEMP01.DBF localisé au même endroit que les tablespaces USERS, SYSTEM, etc. S’il est créé par défaut avec une instance Oracle, le tablespace temporaire TEMP est plus que conseillé pour améliorer le temps de réponse des requêtes et pour ne pas utiliser le tbs SYSTEM.

Pour différentes raisons, que ce soit pour résoudre un problème, faire un test ou s’assurer d’avoir vidé le tablespace TEMP, on peut vouloir supprimer le tablespace temporaire TEMP et recréer un nouveau fichier DBF. Cela va donc créer un fichier vierge, sans rien dedans. Ce tutoriel fonctionne aussi pour déplacer le tablespace temp sur un autre disque ou une autre partition du serveur, pour par exemple bénéficier d’un stockage rapide SSD ou SAS au lieu d’un simple disque SATA. Cette méthode fonctionne avec toutes les versions de Oracle 12, 11g, 10g, 9i.

Mais cette méthode de bourrin n’est pas prévue pour un environnement de production. En cas de problème avec le tbs tmp en prod, il faut chercher à comprendre pourquoi Oracle consomme du tablespace temporaire, vérifier s’il n’y a pas des tris (ORDER BY, GROUP BY, UNION, DISTINCT…) qui tournent en boucle ou sans raison. On peut aussi créer différents fichiers TEMP pour affiner la consommation de la mémoire tampon du SGBD.

 

Supprimer le tablespace temporaire TEMP

Cette commande va supprimer le fichier TEMP01.DBF qui compose le tablespace temporaire. Chemin et nom de fichier à adapter selon la configuration Oracle, sur Windows ou Linux.

ALTER DATABASE TEMPFILE 'D:\oracle\oradata\orcl\TEMP01.dbf' DROP INCLUDING DATAFILES;

 

Créer un nouveau tablespace temporaire TEMP

Commande pour recréer un nouveau fichier DBF pour le tablespace TEMP, ici avec une taille initiale de 1Go et en autorisant l’auto-extension du fichier, par défaut jusqu’à 32Go.

ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\oracle\oradata\orcl\TEMP01.dbf' SIZE 1000M REUSE AUTOEXTEND ON;

On peut limiter la taille maximale du fichier TEMP.DBF avec l’option « MAXSIZE 5000M » avant le point virgule.

Là aussi, il faut adapter le chemin pour stocker le fichier avec les autres tbs du serveur Oracle ou au contraire sur un disque dédié afin d’améliorer les performances du serveur.

Catégories
Expert SGBD

Gérer PostgreSQL avec Oracle SQL Developer

L’outil SQL Developer a été développé par Oracle pour exécuter des requêtes sur son SGBD maison. Les bases Oracle 11g ou 12c sont donc nativement supportées par le logiciel SQL Developper mais il est également possible de se connecter à des bases montées sur un serveur PostgreSQL pour les gérer de la même manière.

Ce tutoriel explique comment utiliser l’application Oracle SQL Developer pour se connecter à une base PostgreSQL, hébergée sur un serveur Postgre. Tout passe par l’ajout d’un pilote tiers JDBC (Java DataBase Connectivity, même principe qu’un driver ODBC) dans SQL Developer pour supporter les bases PG.

La transition sur PostgreSQL sera ainsi facilitée pour les DBA qui travaillent sur Oracle, dans le cadre d’une migration ou d’une nouvelle application basée sur le système de gestion de base de données libre. PostgreSQL JDBC Driver (PgJDBC) autorise des programmes en Java à se connecter à des bases PostgreSQL, des outils tels que SQL Developer par exemple.

Le téléchargement du logiciel SQL Developer est gratuit.

 

Ajouter le support PostgreSQL dans SQL Developer

1. Télécharger le pilote sur la page JDBC Driver pour PostgreSQL.

2. Déplacer le fichier téléchargé dans un dossier dédié aux pilotes JDBC, par exemple dans le répertoire d’installation de sqldeveloper, sous-dossier « jdbc » ou dans un nouveau dossier créé pour l’occasion.

3. Ouvrir le logiciel Oracle SQL Developer.

4. Aller dans le menu Outils, Préférences. Dérouler Base de données, Pilotes JDBC tiers.

5. Cliquer sur « Ajouter une entrée » et charger le chemin du dossier des pilotes JDBC où se trouve le fichier téléchargé.

6. Valider par OK pour quitter l’écran de configuration des paramètres.

7. Créer une Nouvelle connexion. Un onglet « PostgreSQL » est apparu à côté de Oracle. Cliquer dessus et configurer la connexion au serveur PGSQL.

L’outil Oracle SQL Developer est maintenant capable de se connecter à des serveurs PostgreSQL pour effectuer des requêtes grâce à un plugin JDBC ajouté dans le programme Oracle.

Catégories
Expert SGBD

Oracle : fichier TEMP01.DBF trop volumineux

Le serveur de bases de données Oracle utilise des tablespaces au format .DBF, par exemple USERS01.DBF pour le tablespace USERS. Le fichier d’échange temporaire TEMP dispose également de son propre fichier système : TEMP01.DBF. Le tablespace temporaire est utilisé pour stocker les tris qui ne peuvent pas être exécutés en mémoire (RAM).

Ce tutoriel a été réalisé sur un serveur Oracle 12c mais les autres éditions sont également compatibles (11g, 10g, 9i). Le système d’exploitation exemple est Windows Server mais ces requêtes seront les mêmes sur un serveur Linux / UNIX. Exemple ci-dessous avec un fichier TEMP01.DBF qui pèse 32 / 33 Go.

 

Erreur liée au tablespace temporaire Oracle trop plein

Deux exemples d’erreur qui peuvent s’afficher lorsque le fichier du tablespace TEMP est rempli à 100%.

Lors d’une requête trop importante :

ORA-01652: impossible d’étendre le segment temporaire de 128 dans le tablespace TEMP
00000 –  « unable to extend temp segment by %s in tablespace %s »
*Cause:    Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
*Action:   Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

Ou lors d’un import de base de données :

ORA-39171: Le travail se heurte à une attente avec possibilité de reprise.
Resumable error: ORA-01652: impossible d’étendre le segment temporaire de 128 dans le tablespace TEMP
Resumable stmt:  BEGIN    SYS.KUPW$WORKER.MAIN(‘SYS_IMPORT_FULL_01’, ‘NOM’, 0);  END;
Resumable stmt status: SUSPENDED

 

Dans quels cas suivre ce tutoriel ?

Le fichier lié au tablespace Temp peut être configuré en Autoextend pour qu’il puisse s’agrandir au fur et à mesure des besoins. Il n’est pas normal que le fichier pèse plusieurs dizaines de gigaoctets mais ceci peut arriver selon le type d’utilisation du serveur Oracle (test chez un éditeur de logiciel, de trop nombreux kill de sessions, des requêtes gigantesques…). On pourrait simplement vider ou réduire ce tablespace mais ce n’est pas toujours possible. Si on ne peut pas agrandir le fichier TEMP01.DBF ou qu’on ne peut pas créer un TEMP02.DBF, il reste la solution de supprimer le fichier et de le créer à vide pour que le SGBD Oracle puisse à nouveau l’utiliser correctement. Cette procédure peut impliquer un arrêt temporaire du serveur de base de données.

Ce n’est évidemment pas une solution pérenne que de supprimer et recréer le tablespace temporaire car la même situation pourra se reproduire dans quelques temps. Ce tutoriel peut servir aux serveurs de test mais également dépanner une situation bloquante sur un serveur Oracle de production. L’idéal étant ensuite de chercher à comprendre ce qui consomme du temp et pourquoi pas créer différents fichiers temporaires pour identifier les consommations excessives.

 

Effacer et recréer le tablespace temporaire Oracle

1. Ouvrir une console SQLPlus en compte équivalent ‘sys’ ou un Invite de commandes et exécuter « sqlplus / as sysdba » pour être directement connecté avec un utilisateur ‘sys’. L’utilisation du cmd apporte le support du copier / coller pour les commandes qui seront à taper ensuite.

2. Supprimer le fichier du tablespace temporaire Oracle :

ALTER DATABASE TEMPFILE '[chemin du fichier]\TEMP01.DBF' DROP INCLUDING DATAFILES;

Par exemple : ALTER DATABASE TEMPFILE ‘D:\oracle\oradata\orcl\TEMP01.DBF’ DROP INCLUDING DATAFILES;

3. Le fichier TEMP01.DBF a été supprimé et le disque dur du serveur a récupéré la taille complète du fichier en question.

4. Créer un nouveau fichier TEMP01.DBF et l’attribuer au tempfile Oracle. Ici, on fixe sa taille initiale à 500Mo en autorisant l’extension automatique. Sur Windows, ce fichier DBF va grossir jusqu’à la taille de 32Go ou 33Go.

ALTER TABLESPACE TEMP ADD TEMPFILE '[chemin du fichier]\TEMP01.DBF' SIZE 500M REUSE AUTOEXTEND ON;

Par exemple : ALTER TABLESPACE TEMP ADD TEMPFILE ‘D:\oracle\oradata\orcl\TEMP01.DBF’ SIZE 500M REUSE AUTOEXTEND ON;

 

Erreur ORA-25152: TEMPFILE ne peut pas être supprimé maintenant

S’il y a un message d’erreur du type « ERREUR à la ligne 1 : ORA-25152: TEMPFILE ne peut pas être supprimé maintenant » : il faudra arrêter et relancer le service OracleServiceORCL (ou autre nom d’instance).

L’arrêter ne servirait à rien puisqu’il faut que le service Oracle soit démarré pour se connecter en SQL*Plus.

Catégories
Expert Sécurité SGBD

Oracle : désactiver l’expiration automatique des mots de passe

Par défaut, Oracle 11g et 12c demande de modifier le mot de passe des comptes utilisateurs tous les 180 jours. Il s’agit d’une sécurité imposée par Oracle pour sécuriser l’accès aux bases et au SGBD. S’il est facile de réactiver un compte après expiration du mot de passe, on peut préférer que les mots de passe users ne soient jamais expirés, pour éviter le blocage d’une application métier, d’une opération de sauvegarde ou d’un compte administrateur car les logins sys* sont également concernés par l’expiration par défaut des comptes Oracle.

Le message d’erreur que l’on peut rencontrer : ORA-28001 : The password has expired.

Ce tutoriel explique comment désactiver le délai de 180 jours avant l’expiration d’un mot de passe Oracle.

 

Vérifier la durée d’expiration des mots de passe utilisateurs

1. Ouvrir une session SQL Plus ou SQL Developer.

2. Taper la commande suivante :

SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME = 'PASSWORD_LIFE_TIME';

3. Le résultat indique le nombre de jours avant expiration d’un compte, dans la colonne LIMIT :

 

Supprimer l’expiration de compte Oracle

1. Toujours connecté au serveur Oracle avec SQL Plus ou SQL Developer.

2. Utiliser la commande suivante :

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

3. Le résultat doit être :

Profile DEFAULT modifié(e).

4. Vérifier que la modification ait été prise en compte en redemandant le PASSWORD_LIFE_TIME des DBA_PROFILES :

SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME = 'PASSWORD_LIFE_TIME';

On voit que la valeur est passée à UNLIMITED, c’est-à-dire sans illimité, sans limite en nombre de jours.

5. Cette modification ne s’applique pas aux comptes qui sont déjà expirés. S’il faut débloquer un login utilisateur, utiliser la commande suivante, décrite dans ce tutoriel.

ALTER USER username IDENTIFIED BY motdepasse ;
Catégories
Expert Sécurité SGBD

Oracle : modifier un mot de passe expiré

Par sécurité, il est recommandé de changer son mot de passe de temps en temps et il en est de même pour les comptes Oracle. Le serveur de bases de données demande d’ailleurs de le modifier régulièrement (tous les 180 jours, soit environ tous les 6 mois), toutefois sans prévenir à l’avance que le compte sera verrouillé si cette opération n’est pas effectuée (sauf si on utilise ce compte dans les 7 jours qui précèdent l’expiration). Et c’est justement un problème parce qu’on se retrouve un beau matin avec le message « ORA-28001 : the password has expired » et donc impossible de se connecter avec le compte utilisateur en question.

Cet autre tutoriel explique comment modifier son password Oracle depuis la console web Enterprise Manager mais ce guide montre comment changer le mot de passe d’un utilisateur Oracle en ligne de commande, avec SQLPlus ou SQLDeveloper.

Cette procédure fonctionne pour les serveurs de BDD Oracle 11g et 12c, qu’il soit installé sur Windows Server, Linux, UNIX ou dans un docker.

 

Mettre à jour un mot de passe utilisateur Oracle

1. Se connecter au serveur Oracle, local ou distant, en SQL Plus ou avec SQL Developer, avec un compte  utilisateur non expiré :

  • sqlplus user/password@serveurdistant   (où ‘distant‘ est l’alias de tnsnames.ora)
  • ouvrir une connexion SQL Developer sur le serveur en question

2. Saisir la commande suivante :

ALTER USER username IDENTIFIED BY "motdepasse" ;

username est le nom d’utilisateur et motdepasse le nouveau mot de passe.

Remarques : les guillemets sont optionnels mais indispensables en cas de caractères spéciaux, par exemple « mot&passe! » .

Il est possible de redéfinir le même mot de passe que celui précédemment utilisé. Cela ne modifiera donc pas le moyen d’accès des sessions enregistrées mais cela repoussera le délai d’expiration du compte.

3. Le retour doit être du type :

« Utilisateur modifié » (SQL Plus) ou « User username modifié(e) » (SQL Developer)

Et c’est reparti pour 180 jours de validité du mot de passe.

Catégories
Expert SGBD

SQL Server : lister les tables par taille espace disque

Les administrateurs de SGBD doivent souvent gérer des bases de données de plusieurs Go, voire le To. Quand une BDD gonfle de manière exponentielle et sans explication apparente, le plus simple est de regarder quelle table occupe le plus d’espace. Une simple requête nous donnera la réponse, pour éviter d’avoir à vérifier chacune des centaines de tables que peuvent utiliser les applications métier, un ERP ou un logiciel de comptabilité.

Il s’agit ici de voir la taille occupée par les tables de la database au format MSSQL, c’est-à-dire contenues dans le fichier MDF. Le journal (log) de cette DB se trouve quant à lui dans le fichier LDF mais ce n’est pas l’objet de ce guide.

Pour réaliser ce tutoriel, le logiciel gratuit Microsoft SQL Server Management Studio a été utilisé puisqu’il s’agit de l’utilitaire officiel pour gérer un serveur de bases de données Microsoft SQL Server. On peut cependant utiliser un autre programme pour sortir des informations d’une database SQL Server.

 

Requête SQL Server pour lister les tables par taille, nombre de lignes et espace disque occupé

1. Ouvrir le logiciel Microsoft SQL Server Management Studio ou tout autre outil permettant d’exécuter des requêtes sur une base SQL Server.

2. Se connecter avec un compte utilisateur SQL ou par une authentification Windows.

3. Faire un clic droit sur la base de données et choisir Nouvelle requête.

4. Dans le champ vide, taper la requête suivante :

SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255
GROUP BY t.Name, s.Name, p.Rows
ORDER BY UsedSpaceKB DESC

5. Cliquer sur le bouton « Exécuter » pour lancer la recherche.

6. Le résultat s’affiche avec une liste de toutes les tables de la BDD, triées par « UsedSpaceKB » donc l’espace utilisé en KB.

On peut maintenant cibler les recherches pour diminuer la taille de la table la plus volumineuse et ainsi réduire l’espace disque utilisé par le fichier MDF de la base SQL Server.

Catégories
Expert SGBD

Installer un serveur PostgreSQL 9 sur Windows

PostgreSQL est un SGBDRO (système de gestion de base de données relationnel-objet, ou ORDBMS en anglais), au même titre que le système Oracle. Avec une première version sortie en 1996, la licence gratuite et open source PostgreSQL est de plus en plus utilisée en entreprise, en tant qu’alternative sérieuse à Microsoft SQL Server ou Oracle (dont le coût des licences a explosé avec la version 12).

La version 9.0 de PostgreSQL est sorti en septembre 2010 et les release de mise à jour ont apporté de nouvelles fonctionnalités, jusuq’à la 9.6 qui a été publiée en septembre 2016 avec le dernier update mineur en novembre 2017 (9.6.6) et sera maintenue jusqu’en septembre 2021.

Dans la logique open source du SGBD, Postgre est compatible avec la plupart des systèmes Unix (BSD, Solaris, AIX, HP-UX et bien sûr Linux). Parmi les OS les plus courants, citons Ubuntu, Debian, SuSE, Red Hat, macOS, Solaris, FreeBSD et OpenBSD. Mais en tant que système de gestion de BDD populaire, PGSQL est aussi compatible avec les systèmes d’exploitation Microsoft Windows. L’édition 9.6.x est compatible avec Windows 7, 8, 10, Server 2008 / R2, Server 2012 / R2 (mais pas Windows Server 2016 pour lequel il faudra passer en PostgreSQL 10).

Ce tutoriel explique comment installer un serveur PostgreSQL 9 sur un système Windows. Le paquet fourni par EnterpriseDB est utilisé puisqu’il intègre le serveur Postgre, un outil graphique de gestion pgAdmin et un gestionnaire de paquets StackBuilder. L’alternative pour Windows est le setup de BigSQL qui embarque des outils de développement et d’intégration avec d’autres SGBD (Cassandra, Oracle, SQL Server, Hadoop).

 

Télécharger PostgreSQL 9 pour Windows

1. Aller sur la page Download for Windows : https://www.postgresql.org/download/windows/

2. A la partie « Interactive installer by EnterpriseDB » : cliquer sur « Download the installer » :

3. Sur la page de téléchargement, choisir PostgreSQL 9.6.6 et le système d’exploitation cible :

  • Windows x86-64 pour Windows 64 bits
  • Windows x86-32 pour Windows 32 bit

Puis cliquer sur « Download now » :

 

Installer PostgreSQL 9.6 sur Windows

1. Copier le setup d’installation sur le serveur PG.

2. Ouvrir le fichier postgresql-9.6.6-3-windows-x64.exe par exemple.

3. L’assistant peut télécharger le Runtime Microsoft Visual C++ 2013 si cela est nécessaire (demande une connexion internet).

4. Définir un dossier d’installation pour PostgreSQL9, par exemple D:\PostgreSQL9.

5. Renseigner le répertoire où seront enregistrées les données (bases), par défaut dans le dossier d’installation \data mais cela peut être une partition dédiée aux bases de données pgsql.

6. Indiquer un mot de passe superutilisateur (administrateur du serveur PostgreSQL).

8. Régler le port de connexion TCP à utiliser, par défaut 5432.

9. Spécifier la « locale utilisée » (langue et pays). On peut laisser « Locale par défaut » sauf si par exemple le système d’exploitation est en anglais mais que les bases de données seront en français (encodage).

10. Un récapitulatif résume ce qui sera installé et configuré. Cliquer sur Suivant pour démarrer l’installation du serveur PostgreSQL.

11. Un message indique que tout s’est bien passé et demande à ouvrir Stack Builder (optionnel). Cet outil permet notamment d’installer d’autres versions de PostgreSQL en parallèle de l’existante, pour par exemple avoir un PGSQL 10 à côté du 9, sur un autre port TCP.

12. Ouvrir pgAdmin4 ou un autre outil pour gérer le serveur PostgreSQL9.

Catégories
Expert Logiciels SGBD

Oracle : configurer le répertoire data_pump_dir (ORA-39087)

Ce tutoriel peut répondre à l’erreur Oracle « ORA-39087 : nom de répertoire DATA_PUMP_DIR non valide » ou en anglais « ORA-39087: directory name DATA_PUMP_DIR is invalid » que l’on peut avoir en faisant un export de datapump avec la commande expdp ou un import avec impdp. Peut-être est-ce parce que le Datapumpdir n’a jamais été défini sur le serveur Oracle.

La solution se trouve dans la définition de la variable « Data pump dir » vers le chemin de stockage des datapumps Oracle, une information à vérifier pour résoudre l’erreur ORA39087.

Ce DATA_PUMP_DIR existe depuis Oracle 10g R2, donc également sur les version 11g R1 / R2 et 12c R1 / R2.

 

Voir le chemin de DATA_PUMP_DIR

Avec SQL Plus ou SQL Developer, utiliser la commande suivante pour voir le chemin actuellement renseigné du Data_pump_dir du serveur Oracle :

SELECT directory_path FROM dba_directories WHERE directory_name = ‘DATA_PUMP_DIR’;

 

Modifier le chemin DATA_PUMP_DIR

Pour utiliser un autre emplacement du disque dur serveur ou un lecteur réseau, il faut d’abord supprimer le raccourci Data pump dir puis le recréer et donner les droits à l’utilisateur qui va importer et exporter les bases.

DROP DIRECTORY DATA_PUMP_DIR;

CREATE DIRECTORY DATA_PUMP_DIR as ‘D:\oracle\dbdump’;

GRANT read,write ON DIRECTORY DATA_PUMP_DIR TO utilisateur;

L’import et l’export de BDD doit à nouveau être possible, du moins sans erreur ORA-39087 à cause du Data_pump_dir.