Oracle permet de créer une physical standby database sur une logical standby database. L’exemple de l’article s’appuie sur l’architecture construite pour la logical standby décrite dans l’article Oracle – Dataguard – Créer une Logical Standby Database
Dans l’article qui suit, le processus de création de cette physical standby database est décrit.
La difficulté essentielle réside dans l’intégration de la physical standby database dans Dataguard, en faisant en sorte de résoudre la cascade de standby sans que la primaire ne s’adresse à la physical standby database. Un peu de paramétrage s’avère nécessaire pour y parvenir.
Architecture de la solution
| Serveur | SID de l’instance | DB_UNIQUE_NAME | Rôle de base |
| dataguard01 | TESTDG | TESTDG_DG01 | Primary |
| dataguard02 | TESTDG | TESTDG_DG02 | Logical Standby |
| dataguard03 | TESTDG | TESTDG_DG03 | Physical Standby de TESTDG_DG02 |
Configuration listener de dataguard03
Le listener doit écouter obligatoirement sur deux global_dbname particuliers : <db_unique_name>_DGB et <db_unique_name>_DGMGRL. Ces deux noms globaux sont essentiels pour Dataguard et permettent d’accéder à l’instance sous forme service_name même si celle-ci est arrêtée.
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = dataguard01)(PORT = 1521)) ) )
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = TESTDG_DG01_DGB) (ORACLE_HOME = /oracle/ora112) (SID_NAME = TESTDG) ) (SID_DESC = (GLOBAL_DBNAME = TESTDG_DG01_DGMGRL) (ORACLE_HOME = /oracle/ora112) (SID_NAME = TESTDG) ) (SID_DESC = (GLOBAL_DBNAME = TESTDG_DG01) (ORACLE_HOME = /oracle/ora112) (SID_NAME = TESTDG) ) ) DEFAULT_SERVICE_LISTENER=TESTDG_DG01_DGB ADR_BASE_LISTENER = /oracle
Configuration de l’alias de connexion à TESTDG_DG03
Les alias de connexion doivent être déclarés sur les services spéciaux Dataguard avec une option très importante (en gras-rouge) permettant la connexion y compris à des instances arrêtées :
TESTDG_DG03_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dataguard03)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDG_DG03_DGB) (UR = A) ) )
L’alias de connexion à TESTDG_DG03 doit être reporté dans le fichier tnsnames.ora des deux autres serveurs, dataguard01 et dataguard02
Création de la physical standby database à partir de TESTDG_DG02
Création d’une sauvegarde de TESTDG_DG02 pour standby
La sauvegarde de TESTDG_DG02 doit être faite en sauvegardant le controlfile courant pour standby.
Sauvegarde complète de TESTDG_DG02 et des archivelogs
RMAN> backup database plus archivelog; Starting backup at 06-DEC-16 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=1 RECID=1 STAMP=929889249 input archived log thread=1 sequence=2 RECID=2 STAMP=929890538 input archived log thread=1 sequence=3 RECID=3 STAMP=929890542 input archived log thread=1 sequence=4 RECID=4 STAMP=929890554 input archived log thread=1 sequence=5 RECID=5 STAMP=929891211 input archived log thread=1 sequence=6 RECID=7 STAMP=929891431 input archived log thread=1 sequence=7 RECID=8 STAMP=929891801 input archived log thread=1 sequence=8 RECID=9 STAMP=929891803 input archived log thread=1 sequence=9 RECID=10 STAMP=929892097 input archived log thread=1 sequence=10 RECID=15 STAMP=929892136 input archived log thread=1 sequence=11 RECID=18 STAMP=929892178 input archived log thread=1 sequence=12 RECID=19 STAMP=929897944 channel ORA_DISK_1: starting piece 1 at 06-DEC-16 channel ORA_DISK_1: finished piece 1 at 06-DEC-16 piece handle=/oradata/testdg_dg02/10rmq7eo_1_1 tag=TAG20161206T165904 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 06-DEC-16 Starting backup at 06-DEC-16 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=00001 name=+DATA/testdg_dg02/datafile/system.261.929888053 input datafile file number=00002 name=+DATA/testdg_dg02/datafile/sysaux.271.929888061 input datafile file number=00003 name=+DATA/testdg_dg02/datafile/undotbs1.272.929888075 input datafile file number=00005 name=+DATA/testdg_dg02/datafile/test02.273.929888083 input datafile file number=00004 name=+DATA/testdg_dg02/datafile/test01.274.929888083 input datafile file number=00006 name=+HISTO/testdg_dg02/datafile/test03.258.929888085 channel ORA_DISK_1: starting piece 1 at 06-DEC-16 channel ORA_DISK_1: finished piece 1 at 06-DEC-16 piece handle=/oradata/testdg_dg02/11rmq7ep_1_1 tag=TAG20161206T165905 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 06-DEC-16 Starting backup at 06-DEC-16 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=13 RECID=20 STAMP=929897948 channel ORA_DISK_1: starting piece 1 at 06-DEC-16 channel ORA_DISK_1: finished piece 1 at 06-DEC-16 piece handle=/oradata/testdg_dg02/12rmq7et_1_1 tag=TAG20161206T165908 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 06-DEC-16 Starting Control File and SPFILE Autobackup at 06-DEC-16 piece handle=/oradata/testdg_dg02/c-2862720525-20161206-05 comment=NONE Finished Control File and SPFILE Autobackup at 06-DEC-16 RMAN> backup archivelog all; Starting backup at 06-DEC-16 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=1 RECID=1 STAMP=929889249 input archived log thread=1 sequence=2 RECID=2 STAMP=929890538 input archived log thread=1 sequence=3 RECID=3 STAMP=929890542 input archived log thread=1 sequence=4 RECID=4 STAMP=929890554 input archived log thread=1 sequence=5 RECID=5 STAMP=929891211 input archived log thread=1 sequence=6 RECID=7 STAMP=929891431 input archived log thread=1 sequence=7 RECID=8 STAMP=929891801 input archived log thread=1 sequence=8 RECID=9 STAMP=929891803 input archived log thread=1 sequence=9 RECID=10 STAMP=929892097 input archived log thread=1 sequence=10 RECID=15 STAMP=929892136 input archived log thread=1 sequence=11 RECID=18 STAMP=929892178 input archived log thread=1 sequence=12 RECID=19 STAMP=929897944 input archived log thread=1 sequence=13 RECID=20 STAMP=929897948 input archived log thread=1 sequence=14 RECID=21 STAMP=929897956 channel ORA_DISK_1: starting piece 1 at 06-DEC-16 channel ORA_DISK_1: finished piece 1 at 06-DEC-16 piece handle=/oradata/testdg_dg02/14rmq7f4_1_1 tag=TAG20161206T165916 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 06-DEC-16 Starting Control File and SPFILE Autobackup at 06-DEC-16 piece handle=/oradata/testdg_dg02/c-2862720525-20161206-06 comment=NONE Finished Control File and SPFILE Autobackup at 06-DEC-16 Sauvegarde du controlfile courant pour standby
1 | RMAN> <span style="color: #000080;"><strong>backup current controlfile for standby;</strong></span> |
1 | Starting backup at 06-DEC-16 |
1 | using target database control file instead of recovery catalog |
1 | allocated channel: ORA_DISK_1 |
1 | channel ORA_DISK_1: SID=164 device type=DISK |
1 | channel ORA_DISK_1: starting full datafile backup set |
1 | channel ORA_DISK_1: specifying datafile(s) in backup set |
1 | including standby control file in backup set |
1 | channel ORA_DISK_1: starting piece 1 at 06-DEC-16 |
1 | channel ORA_DISK_1: finished piece 1 at 06-DEC-16 |
1 | piece handle=/oradata/testdg_dg02/0urmq7ea_1_1 tag=TAG20161206T170010 comment=NONE |
1 | channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 |
1 | Finished backup at 06-DEC-16 |
1 | Starting Control File and SPFILE Autobackup at 06-DEC-16 |
1 | piece handle=/oradata/testdg_dg02/c-2862720525-20161206-07 comment=NONE |
1 | Finished Control File and SPFILE Autobackup at 06-DEC-16 |
Création de la physical standby database
La création de la physical standby nécessite d’une part le fichier orapw<SID> équivalent à celui de la logical standby database et d’autre part un fichier spfile adapté à son environnement.
Copie du fichier orapw<SID>
Dans une configuration Dataguard, les bases de données doivent avoir strictement le même fichier de mots de passe SYS. Ainsi le fichier créé sur la base de données primaire doit simplement être recopié d’un serveur à l’autre, dans le répertoire $ORACLE_HOME/dbs :
[oracle@dataguard03 ~]$ cd $ORACLE_HOME/dbs [oracle@dataguard03 dbs]$ scp dataguard02:/oracle/ora112/dbs/orapwTESTDG .
1
1 |
Préparation du fichier init.ora pour spfile de la physical standby
Le fichier init<SID>.ora servant à a création du spfile de la standby database peut être créé directement à partir de la base de données primaire (dataguard01, instance TESTDG) :
create pfile='/tmp/initTESTDG.ora.forstdby' from spfile;
Copier le fichier vers le serveur dataguard03, l’éditer et remplacer toutes les références à TESTDG_DG01 par TESTDG_DG03. Ajouter les db_file_name_convert et log_file_name_convert nécessaires au bon positionnement des fichiers de l’instance TESTDG_DG03 sur le serveur dataguard03. A ce propos, c’est bien TESTDG_DG02 qui alimente TESTDG_DG03 :
1
T
ESTDG.__db_cache_size=771751936
TESTDG.__java_pool_size=16777216
TESTDG.__large_pool_size=16777216
TESTDG.__oracle_base='/oracle'#ORACLE_BASE set from environment
TESTDG.__pga_aggregate_target=369098752
TESTDG.__sga_target=1107296256
TESTDG.__shared_io_pool_size=0
TESTDG.__shared_pool_size=285212672
TESTDG.__streams_pool_size=0
*.archive_lag_target=0
*.audit_file_dest='/oracle/admin/TESTDG/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA','+HISTO'
*.db_block_size=8192
*.db_domain='ANTIBES'
*.db_name='TESTDG'
*.db_file_name_convert='+DATA/testdg_dg02','+DATA/testdg_dg03','+HISTO/testdg_dg02','+HISTO/testdg_dg03'
*.log_file_name_convert='+DATA/testdg_dg02','+DATA/testdg_dg03','+HISTO/testdg_dg02','+HISTO/testdg_dg03'
*.db_recovery_file_dest='+HISTO'
*.db_recovery_file_dest_size=17825792000
*.db_unique_name='TESTDG_DG03'
*.dg_broker_start=TRUE
*.diagnostic_dest='/oracle'
*.fal_client='TESTDG_DG03_dg'
*.fal_server='testdg_dg02_dg'
*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.153)(PORT=1521)))'
*.log_archive_dest_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
TESTDG.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
TESTDG.log_archive_trace=0
*.open_cursors=300
*.pga_aggregate_target=367001600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='TESTDG.ANTIBES'
*.sga_target=1101004800
*.standby_file_management='AUTO'
Créer le fichier spfile pour l’instance TESTDG du serveur dataguard03 :
create spfile from pfile = '/tmp/initTESTDG.ora.forstdby';
Démarrage de la physical standby en NOMOUNT
SQL> startup nomount ORACLE instance started. Total System Global Area 1102344192 bytes Fixed Size 2227584 bytes Variable Size 318767744 bytes Database Buffers 771751936 bytes Redo Buffers 9596928 bytes
Création de la physical standby à l’aide de RMAN
[oracle@dataguard03 ~]$ rman target sys/xxxxxxx@testdg_dg02_dg auxiliary sys/xxxxxxx@testdg_dg03_dg
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Dec 6 17:00:42 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDG (DBID=2862720525)
connected to auxiliary database: TESTDG (not mounted)
RMAN> duplicate target database for standby dorecover nofilenamecheck;
Starting Duplicate Db at 06-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK
contents of Memory Script:
{
set until scn 1064724;
sql clone "alter system set control_files =
''+DATA/testdg_dg03/controlfile/current.267.929898065'', ''+HISTO/testdg_dg03/controlfile/current.278.929898065'' comment=
''Set by RMAN'' scope=spfile";
restore clone standby controlfile;
}
executing Memory Script
[...]
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=13
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=14
channel ORA_AUX_DISK_1: reading from backup piece /oradata/testdg_dg02/14rmq7f4_1_1
channel ORA_AUX_DISK_1: piece handle=/oradata/testdg_dg02/14rmq7f4_1_1 tag=TAG20161206T165916
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/testdg_dg03/archivelog/2016_12_06/thread_1_seq_13.261.929898087 thread=1 sequence=13
channel clone_default: deleting archived log(s)
archived log file name=+FRA/testdg_dg03/archivelog/2016_12_06/thread_1_seq_13.261.929898087 RECID=2 STAMP=929898088
archived log file name=+FRA/testdg_dg03/archivelog/2016_12_06/thread_1_seq_14.262.929898087 thread=1 sequence=14
channel clone_default: deleting archived log(s)
archived log file name=+FRA/testdg_dg03/archivelog/2016_12_06/thread_1_seq_14.262.929898087 RECID=1 STAMP=929898087
media recovery complete, elapsed time: 00:00:01
Finished recover at 06-DEC-16
Finished Duplicate Db at 06-DEC-16
Ajout de la physical standby dans Dataguard
DGMGRL> add database testdg_dg03 as connect identifier is testdg_dg03_dg maintained as physical; Database "testdg_dg03" added DGMGRL> enable database testdg_dg03 Enabled.
A ce point, la physical standby est prête à être connectée dans l’environnement Dataguard. En effet, Dataguard tente absolument de faire envoyer les redologs et les archivelogs en provenance de la primary database, alors qu’elle doit être alimentée par le logical standby database.
Connexion de TESTDG_DG03 à TESTDG_DG02
Configuration de TESTDG_DG02
Modifier fal_client, fal_server et log_archive_dest_2 pour les faire pointer sur les bonnes instances :
SQL> alter system set log_archive_dest_2 = 'service=testdg_dg03_dg lgwr async noaffirm db_unique_name=testdg_dg03 valid_for=(all_logfiles,standby_role)' scope=both; System altered. SQL> show parameter fal NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string TESTDG_DG02 fal_server string testdg_dg01_dg SQL> alter system set fal_client = 'testdg_dg02,testdg_dg03' scope = both; System altered. SQL> alter system set fal_server='testdg_dg01,testdg_dg02' scope = both; System altered.
Configuration de TESTDG_DG01
On constate que TESTDG_DG01 cherche à tout prix à envoyer ses redologs vers TESTDG_DG03 :
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_3 string service="testdg_dg03_dg", LGWR ASYNC NOAFFIRM delay=0 option al compression=disable max_fai lure=0 max_connections=1 reope n=300 db_unique_name="testdg_d g03" net_timeout=30, valid_for =(all_logfiles,primary_role) fal_client string testdg_dg02_dg,testdg_dg03_dg fal_server string testdg_dg01_dg log_archive_dest_state_3 string ENABLE
La destination 3 d’archives doit être différée ad vitam et fal_client doit être modifié :
SQL> alter system set log_archive_dest_state_3 = defer scope = both; System altered. SQL> alter system set fal_client = 'testdg_dg02' scope = both; System altered.
Configuration de TESTDG_DG03
Dataguard a aussi fait en sorte que TESTDG_DG03 reçoive ses redologs de TESTDG_DG01 au travers du fal_server :
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string testdg_dg03_dg fal_server string testdg_dg01_dg
Le fal_server doit pointer sur TESTDG_DG02 :
SQL> alter system set fal_server = 'testdg_dg02' scope = both; System altered.
Initialisation de la transmission des redologs entre TESTDG_DG02 et TESTDG_DG03
Relance de l’application des redologs sur TESTDG_DG03
SQL> alter database recover managed standby database cancel; System altered. SQL> alter database recover managed standby database using current logfile disconnect; System altered.
Relance de la transmission des redologs de TESTDG_DG02 vers TESTDG_DG03
SQL> alter system set log_archive_dest_state_2 = enable scope = both; System altered. SQL> alter system archive log current; System altered.
A ce point, dans le fichier alert_TESTDG.log de TESTDG_DG03 doit apparaître un message précisant l’activation du mode MAXIMUM PERFORMANCE ainsi que le redolog courant en transit :
Tue Dec 06 17:10:26 2016 Primary database is in MAXIMUM PERFORMANCE mode RFS[3]: Assigned to RFS process 23900 RFS[3]: Selected log 4 for thread 1 sequence 17 dbid -1432246771 branch 929889243 Tue Dec 06 17:10:26 2016 Media Recovery Waiting for thread 1 sequence 17 (in transit) Recovery of Online Redo Log: Thread 1 Group 4 Seq 17 Reading mem 0 Mem# 0: +HISTO/testdg_dg03/onlinelog/group_4.260.929898091
A ce point, TESTDG_DG03 est alimentée par TESTDG_DG02.
!!! ATTENTION !!! En cas de manipulation du Dataguard, ces reconfigurations de destination des redologs doivent être revérifiés de bout-en-bout. Dans tous les cas, Dataguard fera en sorte de tout faire partir de TESTDG_DG01 vers TESTDG_DG03 !