Oracle a réaffirmé son leadership dans les bases de données par la sortie d’Oracle Database 12c, la première base de données au monde conçue pour le Cloud. Sur ce blog, vous trouverez une séries d’articles intitulés « Voyage vers l’Oracle Database 12c » vous permettant de découvrir Oracle Database 12c.
La grande nouveauté de cette version est la base multitenant, une grande première chez Oracle. La place occupée par la nouvelle base cloud (avec les options multitenant ) ou le grand intérêt qu’elle suscite a presque occulté les évolutions et améliorations apportées dans certaines fonctionnalités des versions précédentes.
Aujourd’hui, je consacre une série d’articles uniquement sur les changements et améliorations apportés par Oracle Database 12cR1 face aux problématiques habituelles de sauvegarde, de restauration et récupération (RMAN, FlashBack).
Dans Oracle Database 11g, vous pouvez effectuer des sauvegardes complètes et partielles d’une base des données et, à un autre niveau, des sauvegardes à chaud de tablespaces ou fichiers de données. Avec Oracle Database 12c, vous pouvez toujours effectuer ces mêmes types de sauvegarde. Le nouveau niveau pour la sauvegarde est le niveau de base de données pluggable (Pluggable Database). Nous verrons dans la suite qu’il y a de nouvelles fonctionnalités RMAN très intéressantes dans Oracle Database 12c.
Ordre SQL exécuté directement sans préfixe SQL
Une des nouvelles fonctionnalités RMAN introduite dans 12c est la possibilité d’exécuter des commandes SQL sans les préfixer du mot-clé SQL : SQL ‘commande sql’;
Le nouveau privilége SYSBACKUP
Un nouveau privilège est créé pour les utilisateurs effectuant les sauvegardes. Avant, les privilèges SYSDBA ou SYSOPER étaient utilisés ou attribués aux utilisateurs qui devaient effectuer des sauvegardes via RMAN.
Table Recover
Cette option ajoute une niveau de granularité sur les restaurations RMAN. Désormais plus besoin de restaurer dans un environnement parallèle, puis via DBLINK ou les outils d’import/export récupéré une table perdue (suite à une erreur de manipulation par exemple). Il faut noter que si Flashback est configuré et que les conditions nécessaires sont réunies, il évidement plus simple d’utiliser Flashback pour récupérer la table. On retrouve les trois options standards de la commande de restauration :
- UNTIL SCN
- UNTIL SEQUENCE
- UNTIL TIME
Principe :
Prérequis :
- La base de données cible doit être ouvert en mode read-write.
- La base de données cible doit être en mode ARCHIVELOG.
- Il faut disposer d’une sauvegarde RMAN de la table ou partition de la période en question.
NB : Si vous êtes en multitenant, désactivez les Triggers System de démarrage des bases pluggable, si ils sont activés pour éviter les erreurs RMAN-030XX.
PDB1@ORCL> create table hr.employees_demo as select * from hr.employees; Table created. PDB1@ORCL> select count(*) from hr.employees_demo; COUNT(*) ---------- 107 PDB1@ORCL> PDB1@ORCL> select current_scn from v$database; CURRENT_SCN ----------- 3161897 RMAN> backup pluggable database pdb1; Starting backup at 04-JUN-14 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/F0832BAF14721281E045000000000001/datafile/o1_mf_sysaux_9fxvnjdl_.dbf input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/F0832BAF14721281E045000000000001/datafile/o1_mf_system_9fxvnjdq_.dbf input datafile file number=00014 name=/u01/app/oracle/oradata/ORCL/F0832BAF14721281E045000000000001/datafile/o1_mf_apex_226_9gfgd96o_.dbf input datafile file number=00013 name=/u01/app/oracle/oradata/ORCL/F0832BAF14721281E045000000000001/datafile/o1_mf_users_9fxvoh6n_.dbf channel ORA_DISK_1: starting piece 1 at 04-JUN-14 channel ORA_DISK_1: finished piece 1 at 04-JUN-14 piece handle=/u01/app/oracle/fast_recovery_area/ORCL/F0832BAF14721281E045000000000001/backupset/2014_06_04/o1_mf_nnndf_TAG20140604T105530_9rydv3cw_.bkp tag=TAG20140604T105530 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45 Finished backup at 04-JUN-14Starting Control File and SPFILE Autobackup at 04-JUN-14 piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2014_06_04/o1_mf_s_849351377_9rydwl4j_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 04-JUN-14 RMAN> PDB1@ORCL> truncate table hr.employees_demo; Table truncated.
PDB1@ORCL> select count(*) from hr.employees_demo; COUNT(*) ---------- PDB1@ORCL>
RMAN> RECOVER TABLE HR.EMPLOYEES_DEMO OF PLUGGABLE DATABASE PDB1 UNTIL SCN 3161897 AUXILIARY DESTINATION '/tmp/backups' REMAP TABLE 'HR'.'EMPLOYEES_DEMO':'EMPLOYEES_DEMO_RECVR';Starting recover at 05-JUN-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=57 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=65 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=49 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=80 device type=DISK ............................................. datafile 13 switched to datafile copy input datafile copy RECID=15 STAMP=849433476 file name=/u01/app/oracle/exports/WHQX_PITR_PDB1_ORCL/datafile/o1_mf_users_9s0x235k_.dbf contents of Memory Script: { # set requested point in time set until scn 3213150; # online the datafiles restored or switched sql clone 'PDB1' "alter database datafile 13 online"; # recover and open resetlogs recover clone database tablespace "PDB1":"USERS", "SYSTEM", "UNDOTBS1", "SYSAUX", "PDB1":"SYSTEM", "PDB1":"SYSAUX" delete archivelog; alter clone database open resetlogs; } executing Memory Script executing command: SET until clause sql statement: alter database datafile 13 online Starting recover at 05-JUN-14 using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2 using channel ORA_AUX_DISK_3 using channel ORA_AUX_DISK_4 using channel ORA_AUX_DISK_5 starting media recovery archived log for thread 1 with sequence 120 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2014_06_05/o1_mf_1_120_9s0v4o4m_.arc archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2014_06_05/o1_mf_1_120_9s0v4o4m_.arc thread=1 sequence=120 media recovery complete, elapsed time: 00:00:00 Finished recover at 05-JUN-14 database opened contents of Memory Script: { sql clone 'alter pluggable database PDB1 open'; } executing Memory Script sql statement: alter pluggable database PDB1 open contents of Memory Script: { # create directory for datapump import sql 'PDB1' "create or replace directory TSPITR_DIROBJ_DPDIR as '' /u01/app/oracle/exports''"; # create directory for datapump export sql clone 'PDB1' "create or replace directory TSPITR_DIROBJ_DPDIR as '' /u01/app/oracle/exports''"; } executing Memory Script sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/exports'' sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/exports'' Performing export of tables... EXPDP> Starting "SYS"."TSPITR_EXP_whqx_gqfl": EXPDP> Estimate in progress using BLOCKS method... EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA EXPDP> Total estimation using BLOCKS method: 64 KB EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER EXPDP> . . exported "HR"."EMPLOYEES_DEMO" 17.06 KB 107 rows EXPDP> Master table "SYS"."TSPITR_EXP_whqx_gqfl" successfully loaded/unloaded EXPDP> ****************************************************************************** EXPDP> Dump file set for SYS.TSPITR_EXP_whqx_gqfl is: EXPDP> /u01/app/oracle/exports/tspitr_whqx_54160.dmp EXPDP> Job "SYS"."TSPITR_EXP_whqx_gqfl" successfully completed at Thu Jun 5 09:45:43 2014 elapsed 0 00:00:24 Export completed contents of Memory Script: { # shutdown clone before import shutdown clone abort } executing Memory Script Oracle instance shut down Performing import of tables... IMPDP> Master table "SYS"."TSPITR_IMP_whqx_BDkm" successfully loaded/unloaded IMPDP> Starting "SYS"."TSPITR_IMP_whqx_BDkm": IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA IMPDP> . . imported "HR"."EMPLOYEES_DEMO_RECVR" 17.06 KB 107 rows IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER IMPDP> Job "SYS"."TSPITR_IMP_whqx_BDkm" successfully completed at Thu Jun 5 09:46:49 2014 elapsed 0 00:00:47 Import completed Removing automatic instance Automatic instance removed auxiliary instance file /u01/app/oracle/exports/ORCL/datafile/o1_mf_temp_9s0x1ghj_.tmp deleted auxiliary instance file /u01/app/oracle/exports/ORCL/datafile/o1_mf_temp_9s0x15ho_.tmp deleted auxiliary instance file /u01/app/oracle/exports/WHQX_PITR_PDB1_ORCL/onlinelog/o1_mf_3_9s0x26mx_.log deleted auxiliary instance file /u01/app/oracle/exports/WHQX_PITR_PDB1_ORCL/onlinelog/o1_mf_2_9s0x25rg_.log deleted auxiliary instance file /u01/app/oracle/exports/WHQX_PITR_PDB1_ORCL/onlinelog/o1_mf_1_9s0x24yh_.log deleted auxiliary instance file /u01/app/oracle/exports/WHQX_PITR_PDB1_ORCL/datafile/o1_mf_users_9s0x235k_.dbf deleted auxiliary instance file /u01/app/oracle/exports/ORCL/datafile/o1_mf_sysaux_9s0wtwct_.dbf deleted auxiliary instance file /u01/app/oracle/exports/ORCL/datafile/o1_mf_system_9s0wtw5b_.dbf deleted auxiliary instance file /u01/app/oracle/exports/ORCL/datafile/o1_mf_sysaux_9s0wtvxl_.dbf deleted auxiliary instance file /u01/app/oracle/exports/ORCL/datafile/o1_mf_undotbs1_9s0wtw5q_.dbf deleted auxiliary instance file /u01/app/oracle/exports/ORCL/datafile/o1_mf_system_9s0wtw3f_.dbf deleted auxiliary instance file /u01/app/oracle/exports/ORCL/controlfile/o1_mf_9s0wtoc0_.ctl deleted auxiliary instance file tspitr_whqx_54160.dmp deleted Finished recover at 05-JUN-14 RMAN>
PDB1@ORCL> select count(*) from hr.employees_demo_recvr; COUNT(*) ---------- 107 PDB1@ORCL>
Multisection Image copies & incremental backups
En 11g, l’option MULTISECTION existait déjà mais n’était applicable qu’aux sauvegardes FULL de la base de données et des fichiers de données. En 12c, cette fonctionnalité a été étendue aux sauvegardes « Image Copies » et « Incremental Backups ».
Principe:
Image copies multisection:
RMAN> backup as copy section size 20M datafile 13; Starting backup at 05-JUN-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=55 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=69 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=65 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=47 device type=DISK allocated channel: ORA_DISK_5 channel ORA_DISK_5: SID=52 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00013 name=/u01/app/oracle/oradata/ORCL/F0832BAF14721281E045000000000001/datafile/o1_mf_users_9fxvoh6n_.dbf output file name=/u01/app/oracle/fast_recovery_area/ORCL/F0832BAF14721281E045000000000001/datafile/o1_mf_users_9s0f53jr_.dbf tag=TAG20140605T051306 RECID=3 STAMP=849417187 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 05-JUN-14 Starting Control File and SPFILE Autobackup at 05-JUN-14 piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2014_06_05/o1_mf_s_849417188_9s0f556k_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 05-JUN-14 RMAN>
Incremental backups multisection:
RMAN> backup incremental level 1 section size 200M database; Starting backup at 05-JUN-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=69 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=49 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=77 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=75 device type=DISK allocated channel: ORA_DISK_5 channel ORA_DISK_5: SID=55 device type=DISK no parent backup or copy of datafile 12 found no parent backup or copy of datafile 11 found channel ORA_DISK_1: starting incremental level 1 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/F0832BAF14721281E045000000000001/datafile/o1_mf_sysaux_9fxvnjdl_.dbf backing up blocks 1 through 25600 channel ORA_DISK_1: starting piece 1 at 05-JUN-14 channel ORA_DISK_2: starting incremental level 1 datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/F0832BAF14721281E045000000000001/datafile/o1_mf_system_9fxvnjdq_.dbf backing up blocks 1 through 25600 channel ORA_DISK_2: starting piece 1 at 05-JUN-14 channel ORA_DISK_3: starting incremental level 1 datafile backup set ................................................................................. channel ORA_DISK_5: backup set complete, elapsed time: 00:01:08 Finished backup at 05-JUN-14Starting Control File and SPFILE Autobackup at 05-JUN-14 piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2014_06_05/o1_mf_s_849417415_9s0fd82n_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 05-JUN-14RMAN
Comme nous venons de voir, il y a beaucoup d’évolutions et de nouveautés dans Oracle database 12c, il n’est pas obligatoire d’être en mode multitenant pour en bénéficier.
La suite au prochain numéro.