CONTEXTE
Il arrive très souvent qu’une standby soit désynchronisée sans avoir à disposition toutes les archivelogs nécessaires à la reconstruction.
Une des méthodes est alors la création d’une sauvegarde à partir du dernier SCN sur la PRIMAIRE et de l’appliquer sur la STANDBY. Cette méthode se fait en plusieurs étapes sur 11g, mais, depuis la 12c, une commande permet d’effectuer cette resynchronisation de façon presque automatique : RESTORE … FROM SERVICE
Message d'erreur
RMAN> RESTORE ARCHIVELOG FROM SEQUENCE 35 UNTIL SEQUENCE 40 THREAD 1 ;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/28/2020 14:10:42
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of archived log for thread 1 with sequence 40 and starting SCN of 2910481 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 39 and starting SCN of 2910478 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 38 and starting SCN of 2910475 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 37 and starting SCN of 2910472 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 36 and starting SCN of 2905587 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 35 and starting SCN of 2899203 found to restore
Architecture
PRIMAIRE
- DB_NAME : DB12
- TNS : DB12 (qui sera utilisé pour le nom de service)
STANDBY
- DB_NAME : DB12_STB
- TNS : DB12_STB
PREPARATION
Il faut tout d’abord arrêter le synchronisation et placer la standby en état MOUNT (si ce n’est pas déjà le cas).
Standby
SQL> shutdown immediate ;
SQL> startup mount ;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ou avec broker
DGMGRL> edit database 'db12_stb' set STATE='APPLY-OFF' ;
Récupérer le SCN de la STANDBY qui nous servira de point de départ pour la synchronisation.
Standby
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
3102761
Primaire
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh ;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
---------- -------------------------------------- ---------
1 /u02/oradata/DB12/system01.dbf 3158220
2 /u02/oradata/DB12/sysaux01.dbf 3158220
3 /u02/oradata/DB12/undotbs01.dbf 3158220
4 /u02/oradata/DB12/users01.dbf 3158220
Standby
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh ;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
---------- -------------------------------------- ---------
1 /u02/oradata/DB12_STB/system01.dbf 3102762
2 /u02/oradata/DB12_STB/sysaux01.dbf 3102762
3 /u02/oradata/DB12_STB/undotbs01.dbf 3102762
4 /u02/oradata/DB12_STB/users01.dbf 3102762
Dans cet exemple, tous les datafiles de la standby sont en retard par rapport à la primaire.
RECOVER ... FROM SERVICE
Cette étape va automatiquement :
- Créer un backup incrémental avec tous les changements de la primaire depuis le SCN indiqué
- Transférer ce backup sur la standby
Appliquer le backup sur la standby
Standby
[oracle]$ rman target sys/<DMP>
RMAN> RECOVER DATABASE FROM SERVICE DB12 NOREDO USING COMPRESSED BACKUPSET ;
Starting recover at 28-AUG-20
Starting implicit crosscheck backup at 28-AUG-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK
Finished implicit crosscheck backup at 28-AUG-20
Starting implicit crosscheck copy at 28-AUG-20
using channel ORA_DISK_1
Finished implicit crosscheck copy at 28-AUG-20
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service DB12
destination for restore of datafile 00001: /u02/oradata/DB12_STB/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service DB12
destination for restore of datafile 00002: /u02/oradata/DB12_STB/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service DB12
destination for restore of datafile 00003: /u02/oradata/DB12_STB/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service DB12
destination for restore of datafile 00004: /u02/oradata/DB12_STB/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished recover at 28-AUG-20
Et voila !
Il est possible de vérifier le FHSCN des datafiles pour constater qu’ils sont maintenant au même niveau.
Cependant, les controlfiles de la Standby contiennent encore la valeur des anciens SCN des datafiles. Il faut donc également rafraichir ces fichiers.
Standby
SQL> shutdown immediate ;
SQL> startup nomount ;
RMAN> RESTORE STANDBY CONTROLFILE FROM SERVICE DB12 ;
Starting restore at 28-AUG-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service DB12
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/oradata/DB12_STB/control01.ctl
output file name=/u02/fast_recovery_area/DB12_STB/control02.ctl
Finished restore at 28-AUG-20
Maintenant que nous avons restauré le controlfile, il faut vérifier que les datafiles connus correspondent bien à ceux de la standby.
Standby
SQL> alter database mount ;
RMAN> report schema ;
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 750 SYSTEM *** /u02/oradata/DB12/system01.dbf
2 550 SYSAUX *** /u02/oradata/DB12/sysaux01.dbf
3 590 UNDOTBS1 *** /u02/oradata/DB12/undotbs01.dbf
4 5 USERS *** /u02/oradata/DB12/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u02/oradata/DB12/temp01.dbf
Si, dans votre cas, le chemin correspond bien à vos datafiles sur la standby, alors vous pouvez sauter cette étape, sinon, effectuer les opérations suivantes :
RMAN> CATALOG START WITH '/u02/oradata/DB12_STB/'
List of Files Unknown to the Database
=====================================
File Name: /u02/oradata/DB12_STB/system01.dbf
File Name: /u02/oradata/DB12_STB/sysaux01.dbf
File Name: /u02/oradata/DB12_STB/undotbs01.dbf
File Name: /u02/oradata/DB12_STB/users01.dbf
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u02/oradata/DB12_STB/system01.dbf
File Name: /u02/oradata/DB12_STB/sysaux01.dbf
File Name: /u02/oradata/DB12_STB/undotbs01.dbf
File Name: /u02/oradata/DB12_STB/users01.dbf
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "/u02/oradata/DB12_STB/system01.dbf"
datafile 3 switched to datafile copy "/u02/oradata/DB12_STB/sysaux01.dbf"
datafile 4 switched to datafile copy "/u02/oradata/DB12_STB/undotbs01.dbf"
datafile 5 switched to datafile copy "/u02/oradata/DB12_STB/users01.dbf"
Depuis que nous sommes synchronisés sur le SCN récupéré précédemment, il peut arriver qu’un datafile ait été créé entre temps sur la PRIMAIRE. Si c’est le cas, il est possible de l’ajouter manuellement avec la même commande RESTORE … FROM SERVICE !
Primaire
SQL> select file# from v$datafile where creation_change# >= 3102761;
FILE#
----------
5
Standby
[oracle@standby]# rman target sys/MDP@DB12 auxiliary sys/MDP@DB12_STB
RMAN> run {
2> set newname for database to '/u02/oradata/DB12_STB/%b' ;
3> restore datafile 5 from service DB12 ;
4> }
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service DB12
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u02/oradata/DB12_STB/users02.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 02-SEP-20
Il ne reste plus qu’à cleaner les standby redolog et le tour est joué !
Standby
SQL> select GROUP# from v$logfile where TYPE='STANDBY' group by GROUP#;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 4 ;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 5 ;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 6 ;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 7 ;
FINALISATION
Il ne reste plus qu’à réactiver l’application des archivelogs.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION ;
ou avec broker
DGMGRL> edit database 'db12_stb' set STATE='APPLY-ON' ;
Et si vous souhaitez vous former sur Oracle Database, découvrez notre offre de formations officielles Oracle.
