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.