Les modes d'archivage

Si vous êtes un peu sérieux avec la durabilité de vos données, vous avez déjà activé le mode archive de vos WALs. Je l'explique assez souvent: dans le chemin d'écriture d'une donnée dans les fichiers de données, les WALs jouent un rôle critique et en garder une copie est une bonne idée.
En plus de peut-être vous sauver la vie un jour, cela permet également d'utiliser des fonctionnalités comme:

• Les sauvegardes physiques à chaud
• La réplication
• Le Point In Time Recovery

Dans cet article, je vais parler du paramètre archive_mode.

Archive_mode

Les valeurs les plus utilisées de ce paramètre sont 'off' (valeur par défaut) et 'on'. Cependant, si on regarde attentivement la documentation, on voit qu'il existe une valeur 'always' pour ce paramètre.

Lorsque le paramètre est valorisé à 'off', les WAL ne sont pas archivés. Une fois qu'ils ne sont plus utiles, ils ne sont pas copiés alors et lorsqu'on aura besoin de place, on les recyclera. C'est la valeur par défaut et, encore une fois, je vous conseille de ne pas utiliser cette valeur.

Lorsque le paramètre est valorisé à 'on', les WAL sont archivés lors du fonctionnement normal de l'instance (c'est à dire lorsque des requêtes envoyées par les clients modifient la structure ou les données).

La dernière valeur (qui est peu utilisée), 'always', permet de créer des WAL y compris lors des opérations de recovery. En effet, lorsqu'une instance est en recovery, elle modifie la structure et les données des bases de données.

On pourrait considérer qu'étant donné qu'on a les WAL qui ont permis le recovery, il n'est pas nécessaire de créer des nouveaux WAL. Cependant, si nous nous plaçons dans le cas d'une streaming replication, les blocs sont streamés directement et pour éviter d'envoyer à nouveau ces informations à la standby via les WALs complets (avec archive_command ou pg_receivexlog), il est possible de demander à la standby de créer ses propres WAL. Cela permet d'économiser un peu la bande passante.

Pour en savoir plus

Cette fonctionnalité est disponible depuis la version 9.5.
Vous trouverez la documentation officielle ici: https://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-ARCHIVE-MODE

Qui fait quoi sur mon instance ?

C'est une question assez fréquente chez les DBAs : voir les connexions actuelles sur l'instance et ce qu'elles font.
Ça permet de s'assurer que machin qui dit qu'il n'est pas connecté est vraiment déconnecté, que truc qui assure être connecté sur la bonne instance est vraiment connecté et aussi de voir l'activité actuelle sur l'instance.

La bonne nouvelle, c'est que la solution tient en une vue : pg_stat_activity.

Une simple requête permet donc d'avoir une bonne vue de l'activité de l'instance:

SELECT * FROM pg_stat_activity;

Pour en savoir plus

La documentation de la vue est accessible via psql ou sur la documentation officielle : https://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

Tester une connexion de type "replication"

Quand on veut mettre en place des sauvegardes physiques, on utilise un outil qui se connecte à l'instance en se faisant passer pour un nœud secondaire de réplication. C'est pour cette raison qu'il faut définir une connexion de type réplication dans pg_hba.conf (voir ici).
Mais comment peut-on tester cette connexion ?

C'est indiqué dans cette page de la documentation.

Il suffit donc de faire:

psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"

Attention, en version 9.3 (quoi, vous n'avez toujours pas migré?), il faudra utiliser cette syntaxe:

psql "dbname=postgres replication=true" -c "IDENTIFY_SYSTEM;"

Dropper les objets d'un user

Sous Oracle, un user possède son propre schéma dans lequel il peut stocker ses objets (principalement des vues et des fonctions/procédures stockées). Certains gèrent les droits ainsi. La suppression du user supprime alors les objets en cascade.
Sous PostgreSQL, il n'est pas possible de supprimer un rôle qui est encore propriétaire d'objets.

Suppression de tous les objets

Il est possible de supprimer tous les objets d'un user (par base de données) en faisant cette simple commande:

DROP OWNED BY doomed_role;

Changement de propriétaire pour tous les objets

On peut aussi vouloir conserver les objets dont le rôle est propriétaire tout en supprimant le rôle.

REASSIGN OWNED BY doomed_role TO successor_role;

Suppression d'objets particuliers

Si on souhaite supprimer toutes les fonctions, toutes les vues ou toutes les tables possédées par un user tout en conservant les autres objets, il va falloir utiliser un bout de code.

Les fonctions

Il est possible de supprimer toutes les fonctions détenues par un user en utilisant ce code:

create function drop_functions_by_owner(owner regrole, do_drop boolean default true)
returns setof text language plpgsql as $$
declare r text;
begin
    for r in 
        select format('%s.%s(%s)', 
            nspname, proname, pg_get_function_identity_arguments(p.oid))
        from pg_proc p
        join pg_namespace n on n.oid = pronamespace
        where p.proowner = $1
    loop
        if do_drop then
            execute format('drop function %s', r);
        end if;
        return next r;
    end loop;
end $$;

Les vues

Pour les vues détenues par un user, c'est cette fonction qu'on utilisera:

create function drop_views_by_owner(owner regrole, do_drop boolean default true)
returns setof text language plpgsql as $$
declare r text;
begin
    for r in 
        select format('%s.%s', nspname, relname)
        from pg_class c
        join pg_namespace n on n.oid = relnamespace
        where c.relowner = $1
        and relkind = 'v'
    loop
        if do_drop then
            execute format('drop view %s', r);
        end if;
        return next r;
    end loop;
end $$;

Les tables

Il suffit d'adapter le code du dessus:

create function drop_views_by_owner(owner regrole, do_drop boolean default true)
returns setof text language plpgsql as $$
declare r text;
begin
    for r in 
        select format('%s.%s', nspname, relname)
        from pg_class c
        join pg_namespace n on n.oid = relnamespace
        where c.relowner = $1
        and relkind = 'r'
    loop
        if do_drop then
            execute format('drop table %s cascade', r);
        end if;
        return next r;
    end loop;
end $$;

Pour en savoir plus 

La documentation prévoit une page sur la suppression des rôles : https://www.postgresql.org/docs/current/static/role-removal.html

Où sont mes logs (la suite) ?

Dans l'article Où sont mes logs je vous avais indiqué comment trouver les logs d'une instance PostgreSQL qui utilise le log collector (paramètre log_collector =on).
Mais que se passe-t-il si le log collector n'est pas activé ?

Stderr

Restons dans le cas par défaut où les logs sont envoyés sur stderr. Il s'agit du cas par défaut car la valeur par défaut du paramètre log_destination est stderr.
Ce n'est plus forcément la meilleure valeur, mais j'espère avoir le temps d'un autre article pour parler de ça...

Pid

Pour pouvoir trouver où sont vos logs, vous allez devoir récupérer le pid de votre process postgres principal.
Voici deux manières de le faire:

1. Comme un ingénieur système

   ps -ef | grep postgres
   postgres 5109 1 0 16:25 ? 00:00:00 /usr/lib/postgresql/9.6/bin/postgres -D /var/lib/postgresql/9.6/main -c config_file=/etc/postgresql/9.6/main/postgresql.conf
   postgres 5116 5109 0 16:25 ? 00:00:00 postgres: checkpointer process 
   postgres 5117 5109 0 16:25 ? 00:00:00 postgres: writer process 
   postgres 5134 5109 0 16:25 ? 00:00:00 postgres: wal writer process 
   postgres 5135 5109 0 16:25 ? 00:00:00 postgres: autovacuum launcher process 
   postgres 5136 5109 0 16:25 ? 00:00:00 postgres: archiver process  last was 00000008.history
   postgres 5137 5109 0 16:25 ? 00:00:00 postgres: stats collector process

2. Comme un DBA

   SELECT pg_backend_pid();
   pg_backend_pid
   ----------------
   5109
   (1 row)
   

Trouver les logs

Une fois le PID récupéré, vous trouverez les logs dans le répertoire /proc/<PID>/fd/2.
C'est là que vous verrez éventuellement si la sortie d'erreur est redirigée.
Par exemple sur un postgreSQL lancé avec le wrapper perl pg_ctlcluster, vous trouverez ceci:

sudo ls -l /proc/5109/fd/2
l-wx------ 1 postgres postgres 64 May 10 18:35 /proc/5109/fd/2 -> /var/log/postgresql/postgresql-9.6-main.log

Pour en savoir plus 

La documentation officielle prévoit une page complète sur le reporting d'erreur et le logging. https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html

Passer des options à pg_createcluster

J'adore les wrappers perl intégrés dans les distributions Debian (ou Debian-based). Mais il n'est pas toujours aisé de donner les paramètres que l'on souhaite.
J'ai voulu utiliser pg_createcluster en ajoutant l'option permettant d'avoir un checksum des données sur chaque page.

Createcluster.conf

Le fichier /etc/postgresql-common/createcluster.conf permet de spécifier ce paramètre dans la section:

initdb_options = '--data-checksums'

Si on ne souhaite pas modifier le fichier de paramètre, il est possible de spécifier ce paramètre dans la ligne de commande.

Dans la ligne de commande

Il est possible aussi de spécifier ce paramètre directement dans la ligne de commande ainsi:

sudo pg_createcluster 9.6 main -- '--data-checksums'

Vérifier

On peut ensuite vérifier que le paramètre a bien été pris en compte:

postgres=# show data_checksums;
data_checksums
----------------
on
(1 row)

 Pour en savoir plus

 Vous pouvez toujours lire la page man de pg_createcluster ou aller voir le code du wrapper.

Les surprises de pg_upgradecluster

J'aime beaucoup les wrappers perl qu'on trouve dans les distributions Debian pour gérer postgreSQL. Il n'empêche que j'ai rencontré un comportement bizarre l'autre jour chez un client.
Nous voulions utiliser l'option --link (ou -k) de pg_upgrade pour faire un upgrade plus rapide de notre base de données.
Pour tous les autres wrappers, il suffit de fournir l'option de l'outil postgreSQL au wrapper et le tour est joué. C'est ce que nous avons fait mais, là, les temps étaient très très longs.

Après avoir regardé la doc et le code, nous avons découvert que pg_upgradecluster utilise par défaut pg_dump pour faire un upgrade. Notre option -k n'était donc pas prise en compte...

Manquant de temps, nous avons donc fait l'upgrade à la main (création du nouveau cluster, arrêt des deux instances, lancement de pg_upgrade avec l'option -k).

Depuis, j'ai pu regarder pg_upgradecluster et j'ai pu confirmer que la ligne suivante fonctionne.

pg_upgradecluster -v 9.6 9.5 main -m upgrade -k 

Voici la procédure sous Debian pour faire un upgrade (on peut utiliser l'option -m dump pour faire des sauts de version majeure).

1. Installation de la nouvelle version

sudo apt-get install postgresql-9.6

2. Suppression du cluster main

sudo pg_dropcluster 9.6 main

3. Lancement de l'upgrade

sudo pg_upgradecluste -v 9.6 9.5 main -m upgrade

Pour en savoir plus :
La documentation de pg_upgrade est là.
Le code source de pg_upgradecluster est ici (et n'hésitez pas à regarder la man page).

Les extensions

PostgreSQL est un SGBD extensible et il a beaucoup d'extensions. Comment ça marche ?

Ajouter une extension

On peut ajouter une extension en jouant l'ordre DDL suivant:

CREATE EXTENSION extension_name

(Il y a des chance qu'il faille installer l'extension sur le serveur avant, suivez bien les instructions du manuel de l'extension).

Supprimer une extension

Là, le manuel de l'extension va rarement vous aider, mais c'est tout simple:

DROP EXTENSION extension_name

Vous pouvez alors droper les packages et autres codes source que vous aviez téléchargés et compilés pour installer l'extension en question.

Savoir où c'est utilisé

Les extensions, c'est chouette... Mais c'est mieux de savoir où c'est utilisé. Pour ce faire, le mieux est de requêter la table pg_extension.

 Pour en savoir plus

La documentation de CREATE EXTENSION est ici.
Celle de DROP EXTENSION est là.
Celle de ALTER EXTENSION est là.
Et celle de pg_extension est là.

Changer le mot de passe d'un user

Ça ne vous arrive jamais d'avoir un utilisateur tout penaud qui vient dire qu'il a oublié son mot de passe ? Ou celui de mauvaise foi qui jure ses grands dieux qu'il s'en souvient mais qu'il ne fonctionne plus ?
Bref, c'est toujours bien d'avoir cette petite ligne SQL sous la main:

ALTER ROLE "user_name" WITH PASSWORD 'new_password';

Petit rappel

ALTER USER est un alias d'ALTER ROLE. La commande ALTER USER n'existe que pour ne pas dérouter les admins et pour la rétro-compatilité avec les version 8.0 et inférieures.

Pour en savoir plus

La documentation officielle est disponible ici.

Où sont mes logs ?

Par défaut les logs PostgreSQL sont redirigés sur sderr. Mais ça ne veut pas dire qu'ils sont perdus!

log_destination

Il faut commencer par chercher où sont envoyés les logs. Si vous n'avez touché à rien, ils sont envoyés sur sderr:

cat /etc/postgresql/9.6/main/postgresql.conf | grep log_destination
#log_destination = 'stderr'        # Valid values are combinations of

Si ce n'est pas sderr, il faudra attendre que je fasse un autre article (ou lire la documentation officielle)... Désolée...

log_collector 

Dans le cas où le log_destination est à sderr, il faut absolument que le log_collector soit à 'on'. Dans le cas contraire, les logs redirigés vers stderr seront perdus sauf si la commande de démarrage de postgres inclue une redirection explicite de sderr.

cat /etc/postgresql/9.6/main/postgresql.conf | grep collector
                                                       # requires logging_collector to be on.
logging_collector = on                                 # Enable capturing of stderr and csvlog

 log_directory

 Enfin, le paramètre ultime qui indique où sont redirigés les logs est log_directory.

cat /etc/postgresql/9.6/main/postgresql.conf | grep log_directory

log_directory = '/var/lib/postgresql/pg_log'           # directory where log files are written,

Pour en savoir plus

La documentation officielle prévoit une page complète sur le reporting d'erreur et le logging.
https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html

Quel est le nom du WAL courant ?

Effectivement, c'est pas forcément utile au commun des mortels, mais on ne sait jamais...

WAL

Les WAL sous PostgreSQL (Write Ahead Log) sont le mécanisme qui assure la durabilité des données (le D dans ACID), tout en permettant de meilleures performances que si on écrivait directement dans les fichiers de données à chaque modification.
Les données sont d'abord écrites en mémoire. Au commit, elles sont écrites dans les WAL. Le checkpoint permet ensuite d'écrire les données dans les fichiers de données.

Le nom des WAL

Les WAL ont des petits noms forts sympathiques composés de 25 caractères (composés des id de timeline, block ID et segment ID, mais qui a besoin de descendre à ce niveau de détail?). Le premier est le 000000010000000000000000 et ça s'incrémente.

Le WAL courant

Ce que j'appelle le WAL courant est le WAL dans lequel PostgreSQL est en train d'écrire la transaction courante.
Une fonction permet de récupérer le numéro de la transaction courante. Une autre permet de convertir ce numéro en nom de WAL. Il faut donc combiner les deux:

SELECT pg_xlogfile_name(pg_current_xlog_location());
 pg_xlogfile_name 
--------------------------
 0000000A0000000000000077
(1 row)

Pour en savoir plus

Toute la documentation de ses fonctions et de leurs petites sœurs est disponible ici: https://www.postgresql.org/docs/9.6/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE

Utiliser pg_basebackup pour faire les sauvegardes de son instance

Pg_basebackup est l'utilitaire permettant de faire de vraies sauvegardes physiques de votre base de données. Si une sauvegarde logique seulement est nécessaire, pg_dump est là pour vous (et permettez moi de vous dire que vous avez une toute petite base :-) ). Sa mise en place nécessite de comprendre quelques notions de base.

Sauvegarde à chaud

pg_basebackup fait des sauvegardes à chaud, donc les sauvegardes des données ne sont pas suffisantes, il va falloir prendre en compte la portion de WAL écrite entre le début et la fin de la sauvegarde. De plus, cela permet de plus de faire du PITR (Point In Time Recovery) (si l'archivage est mis en place).
L'instance va voir la sauvegarde comme une réplication. Il faut donc procéder à  une modification du paramétrage de l'instance.

Paramétrage de l'instance

Il faut donc commencer par paramétrer l'instance. Attention, la modification de ce paramétrage nécessitera un redémarrage de l'instance.
Les paramètres à modifier sont:

• max_wal_sender: Par défaut ce paramètre vaut 0. pg_basebackup agit comme une standby qui récupère les WAL. Il faut positionner ce paramètre à nombre de standby+1 pour pouvoir faire une sauvegarde
• wal_level: si vous n'avez pas de souci de place, mettez ce paramètre à logical (on ne sait jamais, ça pourrait servir un jour). Le niveau minimum pour utiliser pg_basebackup est replica.
• archive_mode: cette partie est facultative, mais nécessaire au PITR. On peut donc setter ce paramètre à on.
• archive_command: cette partie est facultative,  mais nécessaire au PITR. Je vous laisse mettre la commande de votre choix.

Vous pouvez aussi setter d'autres paramètres liés aux WAL. Ce n'est pas nécessaire à pg_basebackup, mais tant qu'à activer les WAL, autant tuner le truc au mieux pour votre instance. Vous pouvez donc regarder archive_timeout, max_wal_size et min_wal_size.

Vous pouvez redémarrez votre instance.

Paramétrage de connexion

Il faut paramétrer pg_hba.conf comme si on avait une réplication qui se connectait à l'instance. C'est indiqué sur cette page.

The backup is made over a regular PostgreSQL connection, and uses the replication protocol. The connection must be made with a superuser or a user having REPLICATION permissions and pg_hba.conf must explicitly permit the replication connection.

J'ai paramétré ma connexion ainsi (attention il s'agit d'une simple base locale à but purement pédagogique. Pensez de votre côté à prendre en compte l'aspect sécurité, notamment sur la méthode d'authentification).

 host    replication    postgres    127.0.0.1/32    trust

Les messages d'erreur

Voici un petit florilège des messages d'erreur que vous pouvez rencontrer:

• pg_basebackup: directory "/my/backup/path/dir" exists but is not empty: pg_basebackup ne fait des sauvegardes que dans des répertoires vides. A vous de déplacer les backup sur votre système de stockage favori avant de faire un autre backup (ou de changer de répertoire).
• pg_basebackup: could not connect to server: FATAL:  no pg_hba.conf entry for replication connection from host "127.0.0.1", user "postgres", SSL on: Vous n'avez pas configué la connexion dans pg_hba.conf.
• pg_basebackup: FATAL: number of requested standby connections exceeds max_wal_senders: Vous n'avez pas correctement configuré max_wal_senders
• pg_basebackup: FATAL: WAL streaming (max_wal_senders > 0) requires wal_level "replica" or "logical": Vous n'avez pas correctement configuré wal_level
• pg_basebackup: NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup: Vous n'avez pas correctement configuré archive_mode et/ou archive_command

 Lancer la sauvegarde

Je vous conseille de lancer la sauvegarde avec les options -Ft (pour obtenir un fichier tar) et -z pour compresser le backup à la volée.
Voilà à quoi ressemble ma commande:

pg_basebackup -h localhost -D /my/backup/path/dir -Ft -z -P

 Le -P est optionnel. Il permet d'avoir une progression de la commande de backup. C'est totalement inutile dans un batch.

Pour en savoir plus

Vous pouvez vous plonger dans la doc: https://www.postgresql.org/docs/9.6/static/app-pgbasebackup.html

Voir les rôles/droits assignés à un user

C'est une demande récurrente. Pouvoir voir les rôles/droits accordés à un user. Si la base de données est bien gérée, tous les droits devraient être accordés via des rôles correctement nommés.

La vue dba_role_privs

Cette vue permet de données tous les rôles accordés et à qui ils ont été accordés. Les colonnes intéressantes sont celles-ci:

• grantee : Celui à qui on accorde le rôle
• granted_role : Le rôle autorisé

La vue dba_tab_privs

Mais comme dans la plupart des bases de données on trouve des droits accordés en dehors des rôles, il faut aller sur la vue dba_tab_privs. Cette vue permet de récupérer tous les droits accordés à un user sur un objet de la base. Les colonnes intéressantes sont celles-ci :

• grantee : Celui à qui on accorde des droits
• owner : Le propriétaire de l'objet sur lequel des droits ont été accordés
• table_name : Ce nom est ambigu, il s'agit bien de nom de l'objet (qui peut être une table ou pas)
• grantor : Celui qui a donné le droit
• privilege : Le droit accordé

Les vues role_*_privs

Si les rôles n'ont pas été correctement nommés et si la base n'est pas correctement documentée, il est possible d'oublier quels droits sont donnés par un rôle. C'est là que les vues role_role_privs, role_sys_privs et role_tab_privs peuvent vous aider. Malheureusement, le SQL ne permet pas de récursivité pour récupérer l'intégralité des rôles grantés à un autre rôle. Voir le script proposés après.

Tous les droits pour un user

La problématique est la même que pour les rôles. Il n'est pas possible de faire du récursif en SQL. Or, c'est nécessaire dans ce cas car on peut attribuer un rôle à un autre rôle un paquet de fois! Pour corriger ce problème, je vous conseille d'aller faire un tour sur le site de Pete Finnigan qui, même s'il a un sacré problème dans le choix des couleurs de son blog, a écrit une procédure stockée en PL/SQL qui peut déterminer l'ensemble des droits d'un rôle ou d'un user. http://www.petefinnigan.com/weblog/archives/00001243.htm

Pour en savoir plus

Vous pouvez regarder la définition des vues sur le livre Database Reference Oracle. En 11G : http://docs.oracle.com/cd/E11882_01/server.112/e40402/toc.htm

ORA-01654 sur SYSAUX

Je suis tombée sur une erreur ORA-01654 car le tablespace SYSAUX était plein. Je pense qu'il n'avait pas forcément été bien dimensionné au départ (il faut utiliser le script utlsyxsz pour estimer la taille nécessaire à SYSAUX).

Cependant, n'étant pas propriétaire de la base et n'ayant pas l'autorisation d'augmenter le-dit SYSAUX, j'ai du chercher à grappiller quelques Mo.

Utilisation de SYSAUX

Pour savoir ce qui utilise SYSAUX, il suffit de lancer cette requête :

SELECT  occupant_name "Coupable",
space_usage_kbytes/1048576 "Espace occupé (Go)",
schema_name "Schema"
FROM v$sysaux_occupants
ORDER BY 2 desc;

Dans mon cas, c'était AWR qui occupait plus de la moitié du SYSAUX.

AWR et droit d'utilisation

Pour ce client, AWR était d'autant plus inutile qu'il n'avait pas payé le tuning pack. Il n'avait donc pas le droit de générer et d'aller voir les rapports AWR. Il se trouve qu ele client ne savait même pas ce que c'était. j'ai donc eu l'autorisation de purger les snapshots.

Purge des snapshots

Pour purger les snapshots, il faut savoir quel est le plus ancien et quel est le plus récent. Cette requête donne ces infos.

SELECT snap_id, begin_interval_time, end_interval_time 
FROM SYS.WRM$_SNAPSHOT 
WHERE snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)
UNION 
SELECT snap_id, begin_interval_time, end_interval_time 
FROM SYS.WRM$_SNAPSHOT 
WHERE snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT); 

Il faut ensuite réutiliser ces valeurs dans le bloc PL/SQL suivant :

BEGIN
  dbms_workload_repository.drop_snapshot_range(low_snap_id => 14241, high_snap_id=>14321);
END;

Récupérer la place libérée

Il faut ensuite shrinker la table pour pouvoir réellement récupérer la place (il faut être connecté en sys as sysdba).

alter table sys.WRH$_ACTIVE_SESSION_HISTORY shrink space cascade;

Et après ?

La procédure ci-dessus n'est qu'une procédure d'urgence. Il faudra prévoir rapidement d'envisager au moins une des actions suivantes :

• Faire tourner le script utlsyxsz  pour évaluer le besoin réel d'espace de SYSAUX
• Ajouter un datafile à SYSAUX (ou augmenter les datafiles existants)
• Diminuer la fréquence et la rétention des snapshots d'AWR

ORA-19705 ou comment perdre le plus de temps possible

Je suis tombée par hasard sur une erreur ORA-19705 "tag value exceeds maximum length of 31 characters". J'ai donc appris à mes dépends qu'un tag de sauvegarde RMAN ne peut pas faire plus de 31 caractères.
Cela ne m'aurait pas posé de problème si RMAN me l'avait dit AU DEBUT de la sauvegarde, mais ce petit plaisantin a attendu d'avoir sauvegardé mes 100 Go de données pour planter tout à la fin!

La solution

Comme vous vous en doutez, la solution a été de réduire la taille de mon tag de sauvegarde, quitte à ce qu'il soit moins précis...
Tout ça pour ça!

D'un autre côté, si je n'avais pas perdu du temps aujourd'hui pour cette broutille, je n'aurai peut-être pas aussi bien retenu qu'un tag ne doit pas faire plus de 31 caractères!

Comment ne pas perdre le listener sous Windows 2012

Windows ne fonctionne pas comme Linux. Ce n'est pas une nouveauté. par contre, en arrêtant et redémarrant une base sous Windows, j'ai eu la surprise de perdre mon listener qui me découvrait 2 instances pour mon service alors qu'il n'y en avait qu'une :

Le service "rachel" comporte 2 instance(s).
L'instance "rachel", statut READY, comporte 1 gestionnaire(s) pour ce service.
L'instance "rachel", statut UNKNOWN, comporte 1 gestionnaire(s) pour ce service.

J'ai donc arrêté ma base et le listener ne retrouvait plus qu'une seule instance :

Le service "rachel" comporte 1 instance(s).
L'instance "rachel", statut UNKNOWN, comporte 1 gestionnaire(s) pour ce service.

Démarrage propre d'une instance sous Windows

Bien entendu, si la base est arrêtée, il n'est pas possible d'ouvrir sqlplus avec un nom de service (@rachel).
Voici comment faire correctement sous Windows :

set oracle_sid=rachel
sqlplus sys/*** as sysdba
SQL>startup

...et mon listener était content!

Le service "rachel" comporte 1 instance(s).
L'instance "rachel", statut READY, comporte 1 gestionnaire(s) pour ce service.

To_number, ORA-01722 et NLS_NUMERIC_CHARACTERS

Une utilisatrice m'a appelée à l'aide car elle n'arrivait pas à corriger ses données en base. Elle avait une colonne de type VARCHAR2 dans laquelle étaient stockés des nombres (modèle de données fourni avec un progiciel). Pour une quarantaine de lignes de sa table, elle avait des nombres avec 28 décimales après la virgules, alors qu'elle ne souhaitait en conserver que 2, cette précision excessive ayant causé un incident de production (là encore, la qualité du code du progiciel ne semble pas extraordinaire). Elle me dit que la fonction ROUND en fonctionne pas et que pourtant sa syntaxe est bonne.

L'erreur ORA-01722

Tout d'abord, habitude que j'ai acquise rapidement en étant au contact avec les utilisateurs : vérifier leurs dire. Je lui demande donc un exemple précis de données avec lesquelles elle joue et je lance sa requête, qui revient à ça (son champ était de type texte) :

SELECT round('355.7885219720776879089176240276',2)
FROM dual;

Ce à quoi Oracle me répond :

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

Le message est pourtant assez clair : il n'a pas reconnu un nombre.

La conversion explicite

Qu'à cela ne tienne, je tente une conversion explicite de ma chaîne de caractères en nombre :

SELECT to_number('355.7885219720776879089176240276')
FROM dual;

A nouveau ORA-01722. Je tente avec une virgule à la place du point :

SELECT to_number('355,7885219720776879089176240276')
FROM dual;

J'obtiens une réponse correcte. Il s'agit donc bien d'un souci de séparateur décimal. Le séparateur décimal est défini au niveau de la base dans le paramètre nls_numeric_characters.

Le paramètre nls_numeric_characters

Pour vérifier la valeur du paramètre, il suffit d'aller sur la vue v$nls_parameters.

SELECT *
FROM v$nls_parameters
WHERE parameter='NLS_NUMERIC_CHARACTERS'

-> La base me répond alors ", ". Cela signifie que pour la base un nombre a pour séparateur décimal la virgule et pour séparateur de groupe l'espace. Pour utiliser le paramètre nls_parameter sous Oracle, je suis obligée d'utiliser le paramètre format.

Le format d'un nombre

Pour modifier la valeur de nls_numeric_characters, il y a deux solutions :

• Soit on modifie la valeur au niveau de la sessions (ALTER session SET nls_numeric_characters=', ')
• Soit on précise la valeur à prendre en compte dans la fonction to_number, mais cela impose de préciser le format du nombre

Format des nombres sous Oracle

Ne me voyant pas expliquer la modification du paramètre nls_numeric_characters dans la sessions à mon utilisatrice, j'opte pour la deuxième option. Etant donné que la conversion implicite se plante, il va falloir lui donner le format de nos nombres. Il est bon de savoir que le format peu comporter plus de chiffres que le nombre en lui même mais en aucun cas il ne pourra en comporter moins. Cela nous donne donc ce format :

999D99999999999999999999999999999

Chaque 9 correspond à un chiffre, le D correspond au symbole représentant le séparateur décimal.

La conversion

Il faut donc convertir la chaîne de caractères en nombre, arrondir et reconvertir en chaîne de caractères.

SELECT to_char(round(to_number('355.7885219720776879089176240276','999D9999999999999999999999999999', 'NLS_NUMERIC_CHARACTERS=''. '''),2),'999D99','NLS_NUMERIC_CHARACTERS=''. ''')
FROM dual

Après, c'est vrai que la solution peut sembler complexe, mais avec des nombres stockées dans une colonne de type varchar, je ne peux pas faire de miracle. je suis DBA pas magicienne!

Pour aller plus loin

Vous pouvez consulter la documentation Oracle :

• Ora-01722 : http://docs.oracle.com/cd/E11882_01/server.112/e17766/e1500.htm#sthref843
• Le paramètre nls_numeric_characters : http://docs.oracle.com/cd/E11882_01/olap.112/e17122/dml_options069.htm#OLADM413
• Format des nombres : http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF51075
• To_char(number) : http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions201.htm#SQLRF06130
• To_number : http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions211.htm#SQLRF06140

Résoudre une erreur 1934

Le message d'erreur

J'ai eu la surprise de voir un ALTER INDEX échouer avec le message d'erreur suivant :

Échec de ALTER INDEX car les options SET suivantes comportent des paramètres incorrects : 'QUOTED_IDENTIFIER'. Assurez-vous que les options SET peuvent être utilisées avec vues indexées et/ou index sur les colonnes calculées et/ou index filtrés et/ou notifications de requêtes et/ou méthodes de type de données XML et/ou opérations d'index spatial.

Le paramètre QUOTED_IDENTIFIER

Comme expliqué dans le message, le problème vient du paramètre QUOTED_IDENTIFIER qui n'a pas la bonne valeur. A priori, il devrait être à ON et il est à OFF.

Je regarde donc dans les paramètres de ma base, le paramètre est bien setté à ON. Je regarde si mon user dans la base n'a pas d'options qui modifie ce paramètre mais non, rien. Je suis d'autant plus étonnée que la doc indique que la valeur par défaut de ce paramètre est ON. (http://msdn.microsoft.com/en-gb/library/ms174393.aspx)

La même doc m'indique quand même que ce paramètre doit être setté à ON pour les index sur les colonnes calculées et sur les vues indexées.

Dans mon cas, il s'agit d'une colonne calculée, je comprends donc que ça ne fonctionne pas, mais je ne sais toujours pas pourquoi mon paramètre est setté à OFF lorsque je lance mon script avec mon ordonnanceur alors qu'il fonctionne comme un charme quand je le lance à la main.

Sqlcmd

Dans un éclair de génie, je tape sqlcmd QUOTED_IDENTIFIER sou google et je tombe sur ce blog : http://www.anujchaudhary.com/2011/10/sqlcmd-quotedidentifier-is-off.html qui explique que par défaut le paramètre QUOTED_IDENTIFIER est à OFF via sqlcmd.

C'est assez étrange quand on sait que la valeur par défaut pour la base est ON... Plutôt que d'ajouter un paramètre -l au lancement de sqlcmd, j'ai préféré ajouter un SET QUOTED_IDENTIFER=ON au début de mon script de réindexation. ça me paraissait plus clair.

Pour aller plus loin

La doc Microsoft sur le paramètre QUOTED_IDENTIFIER : http://msdn.microsoft.com/en-gb/library/ms174393.aspx

Les surprises de CREATE TABLE AS

Pour certaines opérations, il n'est pas possible de faire un ALTER TABLE. Dans ce cas, on utilise généralement le mécanisme suivant :

• Créer la nouvelle table à partir de la première
• Supprimer l'ancienne table
• Renommer la nouvelle

Malheureusement, le CREATE TABLE AS d'Oracle n'inclue pas les objets liés à la table: index, contraintes et triggers.

Le script pour générer le script

J'ai écrit les requêtes suivantes qui devraient vous permettre de récupérer le script à exécuter. Il faudra remplacer les paramètres par les vrais noms des tables dans la partie centrale (création/drop/renommage de table)

/*Drop des triggers*/
SELECT 'DROP TRIGGER "'||owner||'"."'||trigger_name||'";' from all_triggers WHERE owner='&mon_schema' AND table_name='&ma_table';
/*Drop des index*/
SELECT 'DROP INDEX "'||owner||'"."'||index_name||'";' from all_indexes WHERE owner='&mon_schema' AND table_name='&ma_table' AND NOT (index_name LIKE 'PK_%' OR uniqueness='UNIQUE');
/*Drop des contraintes*/
SELECT 'DROP CONSTRAINT "'||owner||'"."'||constraint_name||'";' from all_constraints WHERE owner='&mon_schema' AND table_name='&ma_table' AND constraint_type='C';
SELECT 'DROP CONSTRAINT "'||owner||'"."'||constraint_name||'";' from all_constraints WHERE owner='&mon_schema' AND table_name='&ma_table' AND constraint_type='R';
SELECT 'DROP CONSTRAINT "'||owner||'"."'||constraint_name||'";' from all_constraints WHERE owner='&mon_schema' AND table_name='&ma_table' AND
 constraint_type='P';
 
/*Création de la nouvelle table*/
CREATE TABLE &mon_schema.&ma_table_new AS SELECT * FROM &mon_schema.&ma_table;
/*Drop de l'ancienne table*/
DROP TABLE &mon_schema.&ma_table;
/*Renommage de la nouvelle table*/
ALTER TABLE &mon_schema.&ma_table_new RENAME TO &mon_schema.&ma_table;


/*Recréation des contraintes*/
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', constraint_name, owner) from all_constraints WHERE owner='&mon_schema' AND table_name='&ma_table' AND
 constraint_type='P';
 SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', constraint_name, owner) from all_constraints WHERE owner='&mon_schema' AND table_name='&ma_table' AND constraint_type='R';
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', constraint_name, owner) from all_constraints WHERE owner='&mon_schema' AND table_name='&ma_table' AND constraint_type='C';
/*Recréation des index*/
SELECT DBMS_METADATA.GET_DDL('INDEX', index_name, owner) from all_indexes WHERE owner='&mon_schema' AND table_name='&ma_table' AND NOT (index_name LIKE 'PK_%' OR uniqueness='UNIQUE');
/*Recréation des triggers*/
SELECT DBMS_METADATA.GET_DDL('TRIGGER', trigger_name, owner) from all_triggers WHERE owner='&mon_schema' AND table_name='&ma_table';

La démarche

La démarche permettant d'obtenir des objets vraiment corrects avec les bons noms est donc la suivante :

• Générer le script
• Dropper les triggers sur la table d'origine
• Dropper les index sur la table d'origine
• Dropper les contraintes sur la table d'origine
• Créer la nouvelle table à partir de la table d'origine
• Dropper l'ancienne table
• Renommer la nouvelle table
• Recréer les contraintes sur la nouvelle table
• Recréer les index sur la nouvelle table
• Recréer les triggers sur la nouvelle table

Pour aller plus loin

La doc Oracle sur le package dbms_metadata : http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_metada.htm

Mon article sur comment dropper une table et ses contraintes associés sans faire un cascade : http://laetitia-avrot.blogspot.fr/2012/12/dropper-une-table-sans-cascade-ora-02449.html

Dropper une table sans cascade (ORA-02449)

J'ai un collègue développeur qui a décidé de coder tous les champs texte en clob das une seule table. Forcément, les performances ont commencé à en pâtir. Quand les données ont dues être intégrées à une base décisionnelle, ça a été un désastre.

Ebauche de solution

J'ai donc réfléchi avec lui pour transformer mes clobs en varchar2...
Malheureusement, Oracle ne permet pas de faire un ALTER TABLE pour modifier un champd de type clob en champ de type varchar2, même en mettant la fonction de conversion.
J'ai donc créé une nouvelle table (oui, l'ordre des colonnes était important car il y avait du SQL Loader impacté) et voulu dropper la table d'origine pour renommer la nouvelle...

ORA-02449: unique/primary keys in table referenced by foreign keys

Oracle est sympa sur ce coup-là : il nous donne un sacré coup de main dans les précisions :

Cause: An attempt was made to drop a table with unique or primary keys referenced by foreign keys in another table.
Action:  Before performing the above operations the table, drop the foreign key constraints in other tables. You can see what constraints are referencing a table by issuing the following command: SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

Bref, vous avez oublié de supprimer les contraintes liées à votre table.

La requête qui tue

J'ai donc concocté cette requête magique (oui, pas de user_constraints, mais dba_constraints car nous n'utilisons jamais les users applicatifs quand des humains se connectent) :

SELECT 'ALTER TABLE '||c.owner||'.'||c.table_name||' DROP CONSTRAINT '||c.constraint_name||';'
FROM dba_constraints p
JOIN dba_constraints c ON p.constraint_name=c.r_constraint_name
  AND p.owner=c.owner
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.table_name = '&ma_table'
AND p.owner='&mon_schema';

J'ai donc pu tranquillement dropper mes contraintes une par une pour dropper ma table.

Pour aller plus loin

La doc Oracle sur l'ORA-02449 : http://docs.oracle.com/cd/E11882_01/server.112/e17766/e2100.htm#sthref1269