BACKUP RMAN en Enterprise d une bd RAC
1 INTRODUCTION :
La bd RADPROD en RAC sur 2 nœuds :
Mtlunt100 instance RADPROD1
Mtlunt101 instance RADPROD2
Taille de la bd
82G0 de données pour 141Go d’enveloppe.
2 SERVICES :
L idee est de dédié un service au backup afin d avoir un alias TNS spécifique en tout point de vue.
Mais aussi ne pas pénaliser l’application durant le backup.
On pourrait aller plus loin avec resource manager en gerant le nombre de CPU / la taille mémoire ….
Le service est créé via les commandes RAC
Srvctl create service -d RADPROD –s rman_back –n RADPROD1,RADPROD2
Srvctl start service -d RADPROD –s rman_back
3 Configuration RMAN
En Bleu les paramètres modifies :
LA recovery windows
Controlfile autobackup et le format sur disk ou sur TAPE
Configure channel pour le tape
Configure du parallelisme pour tape et pour disque a 2
Le « optimize LOAD » a FALSE
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 32 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+TESTBACKUP/%F';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD FALSE;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/appl/oracle/11.2.0.2/dbs/snapcf_RADPROD2.f'; # default
4 Script de backup Full
Dans tous les cas avec notre configuration le backup occupe 20Go
4.1 Sur disque sans parallélisme :
On se met sur une instance et on lance le backup a partir de la :
run {
allocate channel ch1 type disk connect 'sys/radprod@RADPROD2' format '+TESTBACKUP/rman.s%s.d%d.u.%U.bck';
crosscheck archivelog all;
backup AS COMPRESSED BACKUPSET archivelog all delete input ;
backup AS COMPRESSED BACKUPSET database filesperset 2;
backup current controlfile;
release channel ch1;
}
Durée du backup 80 minutes pour 20Go.
4.2 Sur disque avec parallélisme inter instance :
run {
allocate channel ch1 type disk connect 'sys/radprod@RADPROD2' format '+TESTBACKUP/rman.s%s.d%d.u.%U.bck';
allocate channel ch2 type disk connect 'sys/radprod@RADPROD1' format '+TESTBACKUP/rman.s%s.d%d.u.%U.bck';
crosscheck archivelog all;
backup AS COMPRESSED BACKUPSET archivelog all delete input ;
backup AS COMPRESSED BACKUPSET database filesperset 4;
backup current controlfile;
release channel ch1;
release channel ch2;
}
Durée 45 minutes
Avec 4 channels ouvert on passe à 30 minutes.
4.3 Sur tape sans parallelisme
run {
allocate channel ch2 type SBT_TAPE parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)' connect 'sys/radprod@RADPROD1' ;
backup AS COMPRESSED BACKUPSET archivelog all delete input ;
backup AS COMPRESSED BACKUPSET database filesperset 4;
release channel ch2;
}
Duree : 2h10
Taille = 20Go
Sans le conmpress backupset
Duree : 46 minutes
Taille 105Go
4.4 Sur TAPE avec parallélisme inter instance :
run {
allocate channel ch1 type SBT_TAPE parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)' connect 'sys/radprod@RADPROD1' ;
allocate channel ch2 type SBT_TAPE parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)' connect 'sys/radprod@RADPROD2' ;
#backup AS COMPRESSED BACKUPSET archivelog all delete input ;
backup AS COMPRESSED BACKUPSET database plus archivelog all delete input filesperset 4;
release channel ch1;
release channel ch2;
}
Duree :1h58
5 backup incrémental
5.1 Le principe :
5.1.1 rman backup incremental
Multilevel Incremental Backups
RMAN can create multilevel incremental backups. Each incremental level is denoted by a value of 0 or 1.
A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data.
The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.
A level 1 incremental backup can be either of the following types:
■ A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
■ A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0Incremental backups are differential by default.
The size of the backup file depends solely upon the number of blocks modified and the incremental backup level.
Note: Cumulative backups are preferable to differential backups when recovery time is more important than disk space, because
fewer incremental backups need to be applied during recovery.
Incrémental différentiel de level 1 :
Backup incremental level cumulatif :
On backup tout depuis le backup full tous les jours.
La restore est plus rapide car on a une full plus 1 cumulatif.
5.2 La périodicité
Dimanche Full
Reste de la semaine cumulatif level 1
5.3 Block change tracking
If you enable block change tracking, then you can improve performance by avoiding full scans of every input data file. You use the
BACKUP INCREMENTAL command to perform incremental backups.5.3.1 Size of the Block Change Tracking File
The size of the block change tracking file is proportional to the size of the database and the number of enabled threads of redo. The size of the block change tracking file can increase and decrease as the database changes. The size is not related to the frequency of updates to the database.Typically, the space required for block change tracking for a single instance is approximately 1/30,000 the size of the data blocks to be tracked. For an Oracle RAC environment, it is 1/30,000 of the size of the database, times the number of enabled threads.
The following factors that may cause the file to be larger than this estimate suggests:
· To avoid the overhead of allocating space as your database grows, the block change tracking file size starts at 10 megabytes. New space is allocated in 10 MB increments. Thus, for any database up to approximately 300 gigabytes, the file size is no smaller than 10 MB, for up to approximately 600 gigabytes the file size is no smaller than 20 megabytes, and so on.
· For each data file, a minimum of 320 kilobytes of space is allocated in the block change tracking file, regardless of the size of the data file. Thus, if you have a large number of relatively small data files, the change tracking file is larger than for databases with a smaller number of larger data files containing the same data.
5.3.2 Ajout du fichier :
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+REDO_RAD';
select FILENAME,status,BYTES from v$block_change_tracking;
FILENAME | STATUS | BYTES |
+REDO_RAD/radprod/changetracking/ctf.267.774614481' | ENABLED | 11599872 |
Donc la taille initiale est de 11Mo.
Juste on a un nouveau process ctwr sur chaque instance :
ps -ef | grep ct
oracle 3270 5969 0 10:41:22 ? 0:00 ora_ctwr_RADPROD2
5.4 Script de Backup
run {
allocate channel ch1 type disk connect 'sys/radprod@RADPROD2' format '+TESTBACKUP/rman.s%s.d%d.u.%U.bck';
allocate channel ch3 type disk connect 'sys/radprod@RADPROD2' format '+TESTBACKUP/rman.s%s.d%d.u.%U.bck';
crosscheck archivelog all;
backup AS COMPRESSED BACKUPSET archivelog all delete input ;
backup AS COMPRESSED BACKUPSET incremental level 1 cumulative database filesperset 4;
backup current controlfile;
release channel ch1;
release channel ch3;
release channel ch2;
release channel ch4;
}
Durée pour le premier backup (full de 45 minutes) c’est lié aussi au backup des archives.
Pour les suivants avec 2 Channel ou un Channel on est toujours autour de 45 minutes pour une taille de backup de 80Go
Pour verifier :
select f.name, F.TABLESPACE_NAME ,to_char(b.completion_time,'DD/MM/YYYY HH24:MI:SS'),INCREMENTAL_LEVEL,b.CHECKPOINT_CHANGE# ,USED_CHANGE_TRACKING
from v$backup_datafile b, v$datafile_header f
where b.file# = f.file# and trunc(b.completion_time)>trunc (sysdate -7) ;
5.5 Envoie des backup sur le robot suite a un backup sur disque :
run {
allocate channel t2 type 'SBT_TAPE';
BACKUP BACKUPSET ALL delete all input;
release channel t2;
}
6 Résumé dans un tableau :
type de BACKUP | COMPRESS | MEDIA | duree | taille Go | Nbre de channel |
FULL | YES | DISK | 45 | 20 | 2 |
FULL | YES | DISK | 30 | 20 | 4 |
FULL | YES | DISK | 80 | 20 | 1 |
Incre | YES | DISK | 45 | 16 | 2 |
FULL | YES | TAPE | 135 | 20 | 1 |
FULL | NO | TAPE | 45 | 100 | 1 |
FULL | NO | TAPE | 40 | 100 | 2 |
Incre | NO | TAPE | 30 | 80 | 2 |
Une bonne pratique est de faire un backup sur disque compresse puis de l envoyer sur bande :
1 – c est plus court
2 – moins gros
3 - Ca a l avantage en cas de restore d avoir sur disque ce qu il faut et de pouvoir faire une restore ou une copie de production sur un autre serveur (qa,TEST ….) via RMAN ce qui est plus rapides qu un dump.
7 Restore
On n’a pas testé la restore, j’énonce donc juste le principe basé sur la documentation.
Tous les datafiles
Par sqlplus
Startup mount
Par rman
Restore database;
Recover database
Un datafile applicatif
En mode open
On passe le datafile en offline
Dans rman
Restore datafile ;
Recover datafile;
On passe le datafile online
Perte de controlfile + tous les datafiles :
Startup nomount
Dans rman restore confrolfile ;
Alter database mount
Restore database;
Recover database;
Aucun commentaire:
Enregistrer un commentaire