Oracle : aide-mémoire Data Pump et sauvegarde/restauration RMAN
Commandes expdp/impdp, flashback, sauvegardes à froid et à chaud, et opérations RMAN courantes (liste, validation, restauration, maintenance).
Cet article est un aide-mémoire pour les exports et imports logiques avec Oracle Data Pump (expdp / impdp) et pour les sauvegardes physiques avec RMAN (sauvegarde à froid, à chaud, restauration et maintenance). Les noms de schémas, de répertoires et d'identifiants sont des exemples génériques.
Data Pump
Répertoires
Data Pump lit et écrit via un objet Oracle DIRECTORY.
select directory_name, directory_path from dba_directories;
create directory dp_dir as '/u02/oracle/datapump';
grant read, write on directory dp_dir to app_user;
-- modifier un répertoire existant
create or replace directory data_pump_dir as '/u03/oracle/data_pump/';
grant read, write on directory data_pump_dir to dba;
Suivre un job en cours
SELECT sl.sid, sl.serial#, sl.sofar, sl.totalwork, dp.owner_name, dp.state, dp.job_mode
FROM v$session_longops sl, v$datapump_job dp
WHERE sl.opname = dp.job_name
AND sl.sofar != sl.totalwork;
set linesize 400
set pagesize 10000
column username format A10
column sid format 99999999999
column opname format A40
column target format A40
column %DONE format A10
SELECT b.username, a.sid, b.opname, b.target,
round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%DONE", b.TIME_REMAINING,
to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') start_time
FROM v$session_longops b, v$session a
WHERE a.sid = b.sid
ORDER BY 6;
Exporter un schéma
Ajoutez compression=all pour réduire la taille du dump.
date=$(date '+%Y%m%d%H%M%S')
expdp system/<password> SCHEMAS=APPSCHEMA DIRECTORY=dp_dir compression=all parallel=4 dumpfile=exp_appschema_${date}.dmp logfile=exp_appschema_${date}.log
Lorsque la connexion en system n'est pas possible, connectez-vous en sysdba :
expdp \"sys/<password>@mydb as sysdba\" SCHEMAS=APPSCHEMA DIRECTORY=dp_dir DUMPFILE=appschema.dmp LOGFILE=appschema.log
Exporter pour une version cible plus ancienne
Vérifiez la version de la base, puis utilisez le paramètre VERSION pour que le dump soit lisible par la version plus ancienne :
select * from v$version;
expdp system/<password> SCHEMAS=APPSCHEMA DIRECTORY=dp_dir compression=all parallel=4 VERSION=11.2 dumpfile=expdp_appschema_${date}.dmp logfile=expdp_appschema_${date}.log
Importer un schéma
impdp system/<password> SCHEMAS=APPSCHEMA DIRECTORY=dp_dir parallel=4 DUMPFILE=exp_appschema_20220111092532.dmp LOGFILE=imp_appschema.log
Pour importer dans un autre schéma, utilisez remap_schema (et remap_tablespace si le tablespace diffère) :
impdp system/<password> SCHEMAS=APPSCHEMA DIRECTORY=dp_dir remap_schema=APPSCHEMA:APPSCHEMA_TEST dumpfile=exp_appschema_20191007170920.dmp logfile=imp_appschema_test.log
impdp app_user/<password> directory=data_pump_dir dumpfile=app.dmp remap_schema=APP:APP5 remap_tablespace=users:accounting partition_options=none
Si le schéma cible existe déjà et doit être remplacé, supprimez-le d'abord (en fermant au besoin ses sessions) :
SELECT sid, serial#, status FROM v$session WHERE username = 'APPSCHEMA';
ALTER SYSTEM KILL SESSION '784,36993' IMMEDIATE;
DROP USER appschema CASCADE;
Retrouver les schémas contenus dans un dump
Utilisez sqlfile= pour générer le DDL contenu dans le dump au lieu de l'importer :
impdp app_user/<password> directory=dp_dir dumpfile=app.dmp logfile=app.log sqlfile=app_ddl.txt
Cherchez les instructions CREATE USER dans le fichier généré pour savoir quels schémas l'import requiert. Par exemple :
-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
CREATE USER "APP" IDENTIFIED BY VALUES '<PASSWORD>'
DEFAULT TABLESPACE "APPDATA"
TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "APP";
GRANT CREATE SESSION TO "APP";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
GRANT "DBA" TO "APP";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
ALTER USER "APP" DEFAULT ROLE ALL;
Inconvénient : le fichier SQL peut être très volumineux si le dump l'est.
Export cohérent avec flashback
Récupérez le SCN courant, puis passez-le à flashback_scn :
SELECT current_scn FROM v$database;
SELECT DBMS_FLASHBACK.get_system_change_number FROM dual;
SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) FROM dual;
SELECT SCN_TO_TIMESTAMP(5474751) FROM dual;
expdp system/<password> schemas=APPSCHEMA directory=dp_dir flashback_scn=5474280 dumpfile=expdp_appschema_${date}.dmp logfile=expdp_appschema_${date}.log
Il est aussi possible de se placer à un instant donné. Dans un fichier de paramètres :
flashback_time="to_timestamp('09-05-2011 09:00:00', 'DD-MM-YYYY HH24:MI:SS')"
Avec échappement en ligne de commande :
expdp ..... flashback_time=\"to_timestamp\(\'09-05-2011 09:00:00\', \'DD-MM-YYYY HH24:MI:SS\'\)\"
Import direct via un database link
Avec network_link, aucun fichier dump intermédiaire n'est écrit sur la source. Créez d'abord le lien :
create database link SRC_DB connect to app_user identified by '<PASSWORD>' using 'SRC_DB';
grant CREATE DATABASE LINK to app_user;
impdp system/<password>@TARGET_DB SCHEMAS=APPSCHEMA DIRECTORY=dp_dir parallel=4 network_link=SRC_DB LOGFILE=imp_network.log
Déplacer un dump entre serveurs
tar -cjvf expd_appschema.tar.bz2 *
scp oracle@source-host:/u02/oracle/datapump/expd_appschema.tar.bz2 .
tar -xjvf expd_appschema.tar.bz2
Restaurer une seule table sous un autre nom
expdp system/<password> directory=dp_dir dumpfile=expdp_table_${date}.dmp logfile=expdp_table.log tables=APPSCHEMA.MY_TABLE
impdp system/<password> schemas=APPSCHEMA REMAP_TABLE=MY_TABLE:MY_TABLE_RESTORE directory=dp_dir dumpfile=expdp_table.dmp logfile=impdp_table.log indexes=N constraints=N triggers=N EXCLUDE=STATISTICS
Exclusions associées : EXCLUDE=CONSTRAINT, EXCLUDE=INDEX_STATISTICS (exclut les statistiques d'index) et EXCLUDE=TABLE_STATISTICS (exclut les statistiques de table).
Tables sans segment (création différée des segments)
Les tables créées avec la création différée des segments n'ont pas de segment avant le premier insert, et certains exports les ignorent. Pour désactiver la fonctionnalité et lister les tables concernées :
alter system set DEFERRED_SEGMENT_CREATION=FALSE scope=both;
show parameter DEFERRED_SEGMENT_CREATION
SELECT table_name FROM dba_tables WHERE owner = 'APPSCHEMA' AND segment_created = 'NO';
Allouer un extent pour une table existante :
ALTER TABLE appschema.my_table ALLOCATE EXTENT;
RMAN
Inspecter la configuration et les sauvegardes
export NLS_DATE_FORMAT='yyyy-mm-dd:hh24:mi:ss'
rman target /
SHOW ALL;
REPORT SCHEMA;
LIST BACKUP SUMMARY;
LIST BACKUP;
LIST BACKUP OF DATABASE SUMMARY;
LIST BACKUP OF ARCHIVELOG ALL SUMMARY;
LIST BACKUP sans SUMMARY donne le détail ; ajoutez TAG="xxx" pour filtrer sur une étiquette.
Valider une sauvegarde sans la restaurer
Simuler une restauration :
RESTORE DATABASE PREVIEW;
Lister les pièces de sauvegarde nécessaires à la récupération (adapté d'un article de dbi services, voir les liens ci-dessous). Le script écrit la liste des fichiers dans un fichier texte nommé d'après l'horodatage et le SCN :
echo "restore controlfile preview; restore database preview;" | NLS_DATE_FORMAT='yyyymmddhh24miss' rman target / | awk '
/Finished restore at /{timestamp=$4}
/Recovery must be done beyond SCN /{if ($7>scn) scn=$7 }
/^ *(Piece )Name: / { sub(/^ *(Piece )Name: /,"") ; files[$0]=1 }
END{ for (i in files) print i > "files-"timestamp"-SCN-"scn".txt" } '
Sauvegarde à froid (base arrêtée)
Lister les fichiers à copier :
SELECT name FROM v$datafile
UNION
SELECT member FROM v$logfile
UNION
SELECT name FROM v$controlfile
UNION
SELECT name FROM v$tempfile;
Ensuite, dans SQL*Plus, exécutez shutdown immediate, copiez les fichiers (par exemple dans une archive tar), puis startup. Pour restaurer, extrayez l'archive avec tar -xvf et démarrez la base.
La même sauvegarde à froid peut être scriptée avec RMAN :
rman target / << EOF
shutdown immediate
startup mount;
backup database format '/backup/TESTDB_%d_%t_%s.rman';
alter database open;
EOF
Sauvegarde à chaud (base ouverte)
La base doit être en mode ARCHIVELOG. Pour l'activer :
shutdown immediate
startup mount
alter database archivelog;
alter database open;
Vérifiez l'activation avec archive log list;, select log_mode from v$database; ou ps -ef | grep -i _arc. Consultez la Fast Recovery Area avec show parameter db_recovery_file_dest.
Sans RMAN, placez la base en mode backup, copiez les fichiers (même liste que pour la sauvegarde à froid), puis terminez le mode backup :
alter database begin backup;
-- copier les fichiers (tar ...)
alter database end backup;
Pour restaurer : shutdown immediate, extraction des fichiers, startup. Un message d'erreur apparaît après le démarrage, ce qui est attendu ; exécutez alors alter database end backup;. L'état peut être vérifié avec select * from v$backup;.
Avec RMAN, forcez d'abord un changement de fichier de log :
alter system switch logfile;
Sauvegarde complète avec les archive logs
run {
ALLOCATE CHANNEL ch_1 DEVICE TYPE DISK FORMAT '/u02/app/oracle/backup/df_%d_%t_%s_%p' MAXPIECESIZE 10G;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
backup tag 'DAILY_INC_L0' incremental level 0 database plus archivelog delete all input;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
release channel ch_1;
}
Autres formes :
backup database plus archivelog delete input;
backup as compressed backupset database format '/oracle/backup/%U';
Pour changer l'emplacement par défaut des sauvegardes :
run {
allocate channel d1 device type disk format '/backups/%U.bkp';
backup database include current controlfile;
release channel d1;
}
Configuration persistante
L'utilisation d'un catalogue de récupération est conseillée (register database; dans RMAN).
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/backup/%F';
CONFIGURE channel device type disk format '/u03/backup/%U' maxpiecesize 8 G;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
Déplacer les anciennes sauvegardes vers un autre dossier
backup backupset all format '/u03/lt_backup/%U' delete input;
backup backupset completed before 'TRUNC(SYSDATE)+1.5/24' format '/u03/lt_backup/%U' delete input;
Restaurer à un instant donné
run {
allocate channel ch_1 type disk;
set until time "to_date('2019-01-29:14:06:48', 'yyyy-mm-dd:hh24:mi:ss')";
restore database;
recover database;
}
Après une récupération incomplète, ouvrez la base avec :
alter database open resetlogs;
Pour restaurer jusqu'à un numéro de séquence de log, consultez d'abord les séquences courantes :
select sequence#, first_change#, to_char(first_time,'HH24:MI:SS') from v$log;
Maintenance : crosscheck et suppression
CROSSCHECK compare les sauvegardes enregistrées dans le fichier de contrôle (ou le catalogue de récupération) avec les fichiers présents sur disque et marque les manquantes comme expirées.
crosscheck backup;
crosscheck backup of database;
crosscheck backup of archivelog all;
list expired backup;
list expired backup of database;
list expired backup of archivelog all;
delete noprompt expired backup;
DELETE NOPROMPT OBSOLETE;
Pour la maintenance depuis un canal dédié :
allocate channel for maintenance device type disk;
crosscheck backup;
delete noprompt expired backup;
release channel;
Pour enregistrer un listing dans un fichier :
spool log to '/path/list.txt'
list backupset;
spool off
Anciens exp / imp
exp userid=system/<password> owner=app_schema file=app_schema.dmp log=exp_app_schema.log
imp system/<password> fromuser="app_schema" touser="app_schema" file="app_schema.dmp" commit=Y ignore=Y log=imp_app_schema.log
Créez le schéma cible avant l'import. Après l'import, recompilez les objets si le schéma contient des procédures, packages ou triggers, avec utlrp.sql du dossier $ORACLE_HOME/rdbms/admin.
Remarques
- Ne saisissez jamais un mot de passe réel sur la ligne de commande : utilisez une saisie interactive, un wallet ou un fichier de paramètres protégé.
- Utilisez pour le
DIRECTORYun chemin sur un système de fichiers disposant de suffisamment d'espace, dont l'utilisateur systèmeoracleest propriétaire. - Vérifiez toujours une sauvegarde avec
RESTORE DATABASE PREVIEWet testez les restaurations hors production.