Blog

Blog

Restaure d'une sauvegarde full avec Rman

Ça peut sembler le B.A.-BA mais c'est pas si simple...

Prérequis

Vous aurez besoin du DBID de votre base avant de pouvoir faire la sauvegarde... Vous pouvez le récupérer grâce à cette simple requête SQL (possible dès l'état MOUNT):
SELECT dbid FROM v$database;
Et si la base ne peut pas être montée (pb de controlfiles par exemple) ?
J'espère que vous avez gardé les logs de votre sauvegarde full... C'est ce qui devrait vous sauver!
connected to target database: YYY (DBID=xxx)

La procédure

Toutes les actions suivantes peuvent être effectuées sous rman. il n'est pas nécessaire de se connecter à sqlplus pour effectuer les startup ou open de la base de données. On suppose que la base de données est arrêtée et droppée.
  1. Settage du dbid
  2. SET dbid=xxx;
  3. démarrage en NOMOUNT de la base de données
  4. STARTUP NOMOUNT;
  5. Restaure des controlfiles
  6. RUN 
    {
    ALLOCATE CHANNEL t1 TYPE='your_type';
    RESTORE CONTROLFILE FROM TAG 'your_tag';
    RELEASE CHANNEL t1;  
    }
  7. Passage de la base en mode MOUNT
  8. ALTER DATABASE MOUNT;
  9. Restore de la base
  10. RUN 
    { 
    ALLOCATE CHANNEL t1 TYPE='your_type';  
    RESTORE DATABASE FROM TAG 'your_tag'; 
    RELEASE CHANNEL t1;  
    }
  11. Ouverture de la base
  12. ALTER DATABASE OPEN RESETLOGS;
Dans mon exemple, la sauvegarde full a a été faite à froid avec un tag.

Expdp (Export avec datapump)

Expdp est le pendant de pg_dump pour Oracle. L'outil d'import et l'outil d'export sont regroupés pour Oracle sous le joli nom de datapump.

Pré-requis

Pour utiliser datapump, vous devez avoir un directory Oracle. Par défaut, le directory DATA_PUMP_DIR est utilisé, mais seuls les users administrateurs ont accès à ce répertoire.

export full

Avec l'option FULL=y, expdp exporte la totalité du dump de la base source. Il faut avoir les droits EXP_FULL_DATABASE pour pouvoir effectuer cette commande.
expdp system/password DIRECTORY=expdp_dir DUMPFILE=export_name FULL=y LOGFILE=log_name

Import de schéma(s)

Vous pouvez décider de n'exporter qu'un ou quelques schéma(s) d'une base. pour ce faire, il faut préciser le(s)quel(s) dans le paramètre SCHEMA. Vous pouvez spécifier plusieurs schémas en les séparant par des virgules.
expdp system/password DIRECTORY=expdp_dir DUMPFILE=export_name SCHEMAS=schema_name LOGFILE=log_name

Export de table(s)

Comme vous l'aurez deviné, vous devrez utiliser le paramètre TABLES. Vous pouvez spécifier plusieurs tables en les séparant par des virgules
expdp system/password DIRECTORY=expdp_dir DUMPFILE=export_name TABLES=table_name LOGFILE=log_name

Autres options utiles

L'option CONTENT={ALL | DATA_ONLY | METADATA_ONLY} permet de sélectionner un import des données seulement.

Pour aller plus loin

La documentation complète est disponible ici :
10gr2:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#i1006293
11gr2:
http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dp_export.htm#i1006293

PostGIS

PostGIS est le cartouche de données géographiques de postgreSQL. C'est le pendant d'Oracle spatial (que je connais nettement moins).

Création d'une base de données géographiques

Pour créer une base de données géographique, c'est relativement simple : Il suffit de créer une base de données normale et d'y jouer les scripts lwpostgis.sql et spatial_ref_sys.sql (généralement dans le répertoire share d'installation de votre version de postgreSQL).

Ces scripts créent les fonctions, objets, procédures et tables nécessaires à la manipulation de données géographiques.

Créer une table comportant des données géographiques

C'est tout simple : on commence par créer une table normale (CREATE TABLE...) et on ajoute ensuite la (ou les) colonne(s) géographique(s) à la main grâce à :

AddGeometryColumn(
  schema_name,
  table_name,
  column_name,
  srid,
  type,
  dimension
)

Spatial_ref_sys et geometry_columns

PostGIS utilise deux tables pour fonctionner : spatial_ref_sys et geometry_columns.
La table spatial_ref_sys comporte les colonnes suivantes :
DEMO_GEO=# \d spatial_ref_sys
         Table "public.spatial_ref_sys"
  Column   |          Type           | Modifiers
-----------+-------------------------+-----------
 srid      | integer                 | not null
 auth_name | character varying(256)  |
 auth_srid | integer                 |
 srtext    | character varying(2048) |
 proj4text | character varying(2048) |
Indexes:
    "spatial_ref_sys_pkey" PRIMARY KEY, btree (srid)

Cette table comporte les données nécessaires à l'utilisation de plus de 3000 systèmes de projections différents. A priori, vous n'aurez pas besoin de mettre les mains dedans. Elle est générée et peuplée par le script spatial_ref_sys.sql.

La table geometry_columns va beaucoup plus nous intéresser lorsqu'il s'agira de jouer au rebouteux pour données géographiques malades...
Voici la description de cette table :
DEMO_GEO=# \d geometry_columns
Table "public.geometry_columns"
     Column       |          Type          | Modifiers
-------------------+------------------------+-----------
f_table_catalog   | character varying(256) | not null
f_table_schema    | character varying(256) | not null
f_table_name      | character varying(256) | not null
f_geometry_column | character varying(256) | not null
coord_dimension   | integer                | not null
srid              | integer                | not null
type              | character varying(30)  | not null
Indexes:
"geometry_columns_pk" PRIMARY KEY, btree (f_table_catalog, f_table_schema, f_table_name, f_geometry_column)

Cette table comporte toutes les données concernant les colonnes de types géométriques qui se trouvent dans votre base. Exportez seulement vos schémas ou tables comportant vos données géographiques en omettant les données de cette table et vous aurez de jolies surprises lors de votre import dans une autre BDD.

Voici la description des colonnes de cette table :
  • f_catalog, f_schema, f_table et f_geometry_column permettent d'identifier avec précision la table (f_catalog est toujours vide)
  • coord_dimension permet de savoir s'il s'agit de données en 2D, 3D ou 4D
  • srid est le srid du système de projection utilisé (cf spatial_ref_sys)
  • type permet de savoir le type de données géométriques stockées (point, lignes, polygones...)

Ajouter manuellement des données dans geometry_columns

Vous avez fait des exports/imports de données dans une nouvelle base mais il vous manque les données de geometry_columns. Malheureusement votre sauvegarde est corrompue et vous ne savez pas comment récupérer les données de geometry_columns. (Ne riez pas, ça arrive!)

Pour chaque table avec des données géographiques, vous trouverez facilement les données à mettre dans f_schema, f_table et f_geometry_column.

Pour ce qui est des données à mettre dans coord_dimension et type, il va falloir être plus sioux.
Votre alliée se nomme pg_constraint. pg_constraint est une table du catalogue qui regroupe toutes les données liées aux contraintes.

DEMO_GEO=# \d pg_constraint
    Table "pg_catalog.pg_constraint"
    Column     |    Type    | Modifiers
---------------+------------+-----------
 conname       | name       | not null
 connamespace  | oid        | not null
 contype       | "char"     | not null
 condeferrable | boolean    | not null
 condeferred   | boolean    | not null
 conrelid      | oid        | not null
 contypid      | oid        | not null
 confrelid     | oid        | not null
 confupdtype   | "char"     | not null
 confdeltype   | "char"     | not null
 confmatchtype | "char"     | not null
 conkey        | smallint[] |
 confkey       | smallint[] |
 conpfeqop     | oid[]      |
 conppeqop     | oid[]      |
 conffeqop     | oid[]      |
 conbin        | text       |
 consrc        | text       |
Indexes:
    "pg_constraint_oid_index" UNIQUE, btree (oid)
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conrelid_index" btree (conrelid)
    "pg_constraint_contypid_index" btree (contypid)

Pour chaque colonne de type géographique, vous aurez 3 contraintes, une pour le srid, une pour la dimension et une pour le type de géométrie.
Il ne vous reste donc plus qu'à lancer la requête magique suivante :
SELECT c.consrc
FROM pg_constraint c
  INNER JOIN pg_class r ON c.conrelid = r.oid
  INNER JOIN pg_attribute a ON a.attnum = ANY (c.conkey)
WHERE r.relname = 'table_avec_colonne_geo'
  AND a.attname = 'colonne_geo'
  AND c.conname LIKE 'enforce%'

Vous recevrez un résultat de ce type :
        consrc               |         conname
----------------------------------------------------------+
 (srid(the_geom) = 4326)     | enforce_srid_the_geom
 (ndims(the_geom) = 2)       | enforce_dims_the_geom
 ((geometrytype(the_geom) =  | enforce_geotype_the_geom
 'POLYGON'::text) OR         |
 (the_geom IS NULL))         | 
Vous trouvez donc le SRID (4326), la dimension (2) et le type (POLYGON).

Impdp (Import avec datapump)

Impdp est le pendant de pg_restore pour Oracle. L'outil d'import et l'outil d'export est regroupé pour Oracle sous le joli nom de datapump.

Pré-requis

Pour utiliser datapump, vous devez avoir un directory Oracle. Par défaut, le directory DATA_PUMP_DIR est utilisé, mais seuls les users administrateurs ont accès à ce répertoire.

Import full


Avec l'option FULL=y, impdp recharge la totalité du dump dans la base cible. Il faut avoir les droits IMP_FULL_DATABASE pour pouvoir effectuer cette commande.
impdp system/<password> DIRECTORY=expdp_dir DUMPFILE=<export_name> FULL=y LOGFILE=<log_name>

Import de schéma(s)


Vous pouvez décider de n'importer qu'un ou quelques schéma(s) d'une base. pour ce faire, il faut préciser le(s)quel(s) dans le paramètre SCHEMA. Le fichier dump utilisé peut être un dump full, un dump de schéma(s), un dump de table(s) ou un dump de tablespace(s). Vous pouvez spécifier plusieurs schémas en les séparant par des virgules.
impdp hr/hr DIRECTORY=expdp_dir DUMPFILE=<export_name> SCHEMAS=<schema_name> LOGFILE=<log_name>

Import de table(s)


Comme vous l'aurez deviné, vous devrez utiliser le paramètre TABLES. Vous pouvez spécifier plusieurs tables en les séparant par des virgules.
impdp system/<password> DIRECTORY=expdp_dir DUMPFILE=<export_name> TABLES=<table_name> LOGFILE=<log_name>

Autres options utiles

L'option CONTENT={ALL | DATA_ONLY | METADATA_ONLY} permet de sélectionner un import des données seulement.
L'option REMAP_SCHEMA=old_schema:new_schema permet de faire un mapping pour importer les données d'un schéma dans un autre.

Pour aller plus loin

La documentation complète est disponible ici :
10gr2: http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_import.htm#i1010670
11gr2: http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dp_import.htm#SUTIL903

Sqlplus

Sqlplus est un utilitaire en ligne de commandes qui permet d'exécuter des requêtes sur une base de données Oracle.
(On supposera que les fichiers de configuration, tnsnames.ora sur le client et sqlnet.ora et listener.ora sur le serveur sont correctement remplis.)

Connexion

Pour lancer sqlplus, il faut lui indiquer le login, le mot de passe et le nom de l'instance à laquelle vous voulez vous connecter (nom d'instance qui se trouve dans tnsnames.ora).
sqlplus login/password@instance
Pour un login avec le user sys, ne pas oublier de préciser " as sysdba" derrière le nom d'instance.
sqlplus sys/password@instance as sysdba
Attention, en Oracle 9 et inférieur, la chaîne de connexion est entre ".
sqlplus "login/password@instance"

Déconnexion

Pensez à vous déconnecter en quittant psql proprement. La simple méta-commande exit vous le permet.

Lancement d'un script

On peut exécuter un fichier sql avec sqlplus et récupérer la sortie dans un autre fichier :
sqlplus login/password@instance @fichier.sql 2>nomFichierTrace 1>&2

Requêtes SQL

Sous sqlplus, on peut lancer des requêtes SQL. Ces requêtes peuvent être écrites sur plusieurs lignes. Veillez cependant à terminer vos requêtes par un point virgule ( ; ) ou un antislash ( \ ).

Pour aller plus loin

La documentation complète de sqlplus est disponible ici :
10gr2 : http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/toc.htm
11gr2 : http://download.oracle.com/docs/cd/E11882_01/server.112/e16604/toc.htm

Outil d'analyse des volumes pour postgreSQL

J'ai eu besoin d'un outil pour récupérer rapidement et facilement tous les volumes de données par table. j'ai donc bricolé un bout de perl qui a juste le mérite d'être efficace.

(Je laisse ensuite le soin à Excel et aux tableaux croisés dynamiques de me permettre de faire des rapport sur les volumes par schéma, ou par base.)
Voici donc cet outil perl :
#!/usr/bin/perl
use Getopt::Long;
use strict;
#-----------------------------------------------------------------------------

sub usage 
{
print <<EOUSAGE;
perl $0 --in <fichierIn>
--Help            : ce message
--in <fichier>    : Le chemin compet du fichier comportant les noms des bases à analyser

Voici la requête SQL permettant de générer le fichier d'entrée :
\\pset format unaligned
\\pset border 0
\\pset tuples_only
SELECT datname as Nom
FROM pg_database
WHERE datname <> 'postgres'
AND datname NOT LIKE 'template%'
ORDER BY pg_database_size(datname) DESC

EOUSAGE
exit;
}

#-----------------------------------------------------------------------------
#   Main
#-----------------------------------------------------------------------------
my $help=0;
my $in;

GetOptions ("in=s" => \$in,
            "Help" => \$help);
usage() if ($help);
usage() if (!defined($in));
usage() if ($in eq '');

open(lignes,"<$in");
my @ligne = <lignes>;
open(out,">analyse_bases.sql");

my $mabdd;

#Génération des ordres de formatage pour la sortie à l'exécution sur script SQL généré
print(out "\\pset format unaligned\n");
print(out "\\pset border 0\n");
print(out "\\pset tuples_only\n");
# On génère le fichier de sortie
print(out "\\! touch /pgsql/save/analyse_bases.txt\n");

use constant REQUETE_TAILLE => "SELECT table_catalog, table_schema, table_name, pg_relation_size(table_schema || '.' || table_name) As Taille_donnees, pg_total_relation_size(table_schema || '.' || table_name) As Taille_totale_octets, pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) As taille_totale FROM information_schema.tables ORDER BY Taille_totale_octets DESC;\n";

foreach $mabdd (@ligne)
{  
    chomp($mabdd);
    # Génération de l'ordre de connexion à la BDD
    print(out "\\c $mabdd\n");
    #On peut commencer à écrire dans le ficheir de sortie
    print(out "\\o | cat - >> /pgsql/save/analyse_bases.txt\n");
    #On lance la requeête
    print(out REQUETE_TAILLE);
    #On arrête d'écrire dans le fichier de sortie
    print(out "\\o\n");    
}
Pour l'usage, on commence par générer la liste des bases de l'instance et on la met dans un fichier texte. On appelle ensuite le script avec en argument ce fichier. Le script nous génère un sql qu'il faut ensuite jouer sur la base.
Il n'y a plus qu'à récupérer le fichier analyses.txt

Psql

psql est un utilitaire en ligne de commandes qui permet d'exécuter des requêtes sur une base de données postgreSQL.

Connexion

Pour lancer psql, il faut lui indiquer a minima l'IP sur laquelle se connecter, le port sur lequel se connecter et le user avec lequel se connecter.
psql -h <host> -p <port> -U <user>
Normalement, psql vous demandera à ce moment là le mot de passe du user.
Si votre user n’a pas le même nom qu’une base de données, il faudra préciser le nom de la base de données pour vous connecter.
psql -h <host> -p <port> -U <user> -d <base>
Attention, <host> est plus souvent l’adresse IP de service que le nom de la machine !

Déconnexion

Pensez à vous déconnecter en quittant psql proprement. La simple méta-commande \q vous le permet.

Lancement d'un script

On peut exécuter un fichier sql avec psql et récupérer les logs générés de cette manière :
psql -h <host> -p <port> -U <user> -d <base> -f <nomFichier> 2>nomFichierTrace 1>&2

Requêtes SQL

Sous psql, on peut lancer des requêtes SQL. Ces requêtes peuvent être écrites sur plusieurs lignes. Veillez cependant à terminer vos requêtes par un point virgule ( ; ). Voici le prompt SQL normal :
<nombase>=#
Voici le prompt SQL qui indique qu’il attend la suite de la requête :
<nombase>-#
A tout moment, vous pouvez utiliser la touche « tabulation » pour obtenir une complétion automatique des noms d’objets SQL.

Meta-commands

Psql permet aussi d’envoyer des « méta-commandes » qui peuvent se révéler très pratiques. Voici une liste des plus utilisées :
\i <fichier> permet d’exécuter un fichier de commandes sql
\c <base> permet de se connecter à une base de données
\l permet de lister toutes les bases de données d’une instance
\dt permet de lister toutes les tables d’une base de données
\? permet de lister les méta-commandes de psql
\o <fichier> permet de stocker dans un fichier les résultats d’une requête
\h <commande> permet d’avoir la syntaxe de la commande SQL passée en argument

Pour aller plus loin

La documentation complète de psql est disponible ici :
8.1.5 : http://docs.postgresqlfr.org/8.1/app-psql.html
8.3.3 : http://docs.postgresqlfr.org/8.3/app-psql.html

Liste et taille des bases, des schémas et des tables en postgreSQL

Lors de la vie d'un projet, il faut suivre un minimum l'évolution des volumétries des différentes bases, schémas et tables. (Notamment la préconisation postgreSQL est de ne pas avoir de table plus grande que la RAM).

Liste et taille des bases d'une intance

La fonction pg_database_size donne la taille d'une base de données en octet. la fonction pg_size_pretty transforme un nombre en octet en utilisant l'unité la plus appropriée pour une lecture humaine.
La table pg_database du catalogue postgreSQL vous donnera la liste des base d'une instance.
Voici donc la requête :
SELECT datname as Nom, 
pg_database_size(datname) as Taille_en_Octets, 
pg_size_pretty(pg_database_size(datname)) as Taille
FROM pg_database
ORDER BY Taille_en_Octets DESC

Liste et taille des tables d'une base de données

La fonction pg_relation_size donne la taille des données d'une table. La fonction pg_total_relation_size donne la taille totale d'une table, en incluant les index et les tables TOAST.
La vue du catalogue information_schema tables donne la liste des tables de la base de données à laquelle vous êtes connecté.

SELECT table_name, 
pg_relation_size(table_schema || '.' || table_name) As Taille_donnees,
pg_total_relation_size(table_schema || '.' || table_name) As Taille_totale
FROM information_schema.tables
ORDER BY Taille_totale DESC
Pour rajouter le nom de la base, il suffit de rajouter la colonne table_catalog.

Liste et taille des schémas d'une base de données

La fonction pg_relation_size donne la taille des données d'une table. La fonction pg_total_relation_size donne la taille totale d'une table, en incluant les index et les tables TOAST.
En sommant toutes ces tailles par schéma, on arrive aux tailles des schémas.
La vue du catalogue information_schema tables donne la liste des tables de la base de données à laquelle vous êtes connecté.

Voici donc la requête à jouer :
SELECT table_schema, 
SUM(pg_relation_size(table_schema || '.' || table_name)) As Taille_donnees,
SUM(pg_total_relation_size(table_schema || '.' || table_name)) As Taille_totale
FROM information_schema.tables
GROUP BY table_schema
ORDER BY Taille_totale DESC
Pour rajouter le nom de la base, il suffit de rajouter la colonne table_catalog.

Lors de ces additions, j'ai eu un souci avec le type de sortie de sum qui ne permettait pas d'appliquer le pg_size_pretty (ne s'applique qu'à un bigint), je propose donc faire une conversion explicite ainsi :
pg_size_pretty(SUM(pg_total_relation_size(table_schema || '.' || table_name))::bigint)

Pg_restore

Pg_restore est un utilitaire en ligne de commandes qui permet d’importer tout ou partie d’une base de données postgreSQL.

Simple décompression

Pg_restore permet de décompresser les exports effectués avec l’option –Fc.
pg_restore –Fc –f <logFile> dumpFile

Import

Pg_restore peut aussi se connecter à une instance et restaurer une base de données à partir du fichier d’export. Pour se faire, il faut connaître a minima l’IP de service, le port et le user à utiliser.
pg_restore –h <host> -p <port> -U <user> -d <base> fichierDump
Si l’export a été créé avec l’option de compression –Fc (comme je vous conseille de faire), il faut utiliser l’option –Fc également avec pg_restore.
pg_restore –h <host> -p <port> -U <user> -d <base> -Fc fichierDump
Attention, le user et la base de données doivent avoir été créés au préalable.

Options avancées

Comme son cousin pg_dump, pg_restore supporte de nombreuses options qui permettent d’affiner l’import. Voici les principales :
-a n’importe que les données, les tables doivent déjà exister
-c supprime les objets de la base de données avant de les recréer
-n <schema> permet d’importer uniquement les objets du schéma spécifié
-s permet de définir les objets sans importer les données
-t <table> seule la table spécifiée est importée

Pour aller plus loin

La documentation complète de pg_restore est disponible ici :
8.1.5 : http://docs.postgresqlfr.org/8.1/app-pgrestore.html
8.3.3 : http://docs.postgresqlfr.org/8.3/app-pgrestore.html

Pg_dump

Pg_dump est un utilitaire en ligne de commandes qui permet d’exporter tout ou partie d’une base de données postgreSQL.

Export

Pour exporter toute une base de données, il faut a minima l’IP de service et le port de l’instance qui comporte la base de données à exporter. Il est important d’avoir un user qui a a accès à tous les objets de ladite table.
pg_dump -h <host> -p <port> -U <user> -f <fichierDeSortie> <base>
Normalement, psql vous demandera à ce moment là le mot de passe du user.
Une base de données, ça peut être TRES gros, pour cette raison, je vous conseille très fortement d’utiliser la compression à la volée au format postgreSQL (option –Fc).
pg_dump -h <host> -p <port> -U <user> -f <fichierDeSortie> -Fc <base>
Attention, <host> est plus souvent l’adresse IP de service que le nom de la machine !

Options avancées

Pg_dump fournit une pléiade d’options dont voici les plus importantes
-a permet d’exporter uniquement les données
-Ft permet d’obtenir des fichiers SQL compressés en tar
-n <schema> permet d’exporter uniquement le schéma spécifié
-s seule la définition des objets est exporté
-t <table> seule la table spécifiée est exportée

Pour aller plus loin

La documentation complète de pg_dump est disponible ici : :
8.1.5 : http://docs.postgresqlfr.org/8.1/app-pgdump.html
8.3.3 : http://docs.postgresqlfr.org/8.3/app-pgdump.html

Mode Archivelog (Oracle 10g, 11g)

Tout d'abord il faut savoir qu'un redémarrage d'instance est nécessaire pour passer du mode noarchivelog au mode archivelog. Il faut donc planifier ça avec le client.
Vous pouvez voir dans quel mode vous êtes en faisant :
SELECT name, log_mode FROM v$database;

Pour passer d'un mode noarchivelog à un mode archivelog, vous devez :
  1. Vérifier les paramètres db_recovery_file_dest et log_archive_dest_1
  2. Arrêter la base
  3. La redémarrer en mode mount
  4. Modifier le paramétrage
  5. Ouvrir la base
Voilà les étapes :
SHOW PARAMETER db_recovery_file_dest
SHOW PARAMETER log_archive_dest_1
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
Vous pouvez ensuite refaire la requête sur la vue v$database pour vous assurer que le changement a bien été pris en compte.
Pour passer en mode noarchivelog, on fait les étapes 2 à 5 :

SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE OPEN;