Oracle – Logical Standby Database avec sa Physical Standby Database


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.

1
 

1
LISTENER =


1
  (DESCRIPTION_LIST =


1
    (DESCRIPTION =


1
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))


1
      (ADDRESS = (PROTOCOL = TCP)(HOST = dataguard01)(PORT = 1521))


1
    )


1
  )

1
SID_LIST_LISTENER =


1
  (SID_LIST =


1
    (SID_DESC =


1
      (GLOBAL_DBNAME = TESTDG_DG01_DGB)


1
      (ORACLE_HOME = /oracle/ora112)


1
      (SID_NAME = TESTDG)


1
    )


1
    (SID_DESC =


1
      (GLOBAL_DBNAME = TESTDG_DG01_DGMGRL)


1
      (ORACLE_HOME = /oracle/ora112)


1
      (SID_NAME = TESTDG)


1
    )


1
    (SID_DESC =


1
      (GLOBAL_DBNAME = TESTDG_DG01)


1
      (ORACLE_HOME = /oracle/ora112)


1
      (SID_NAME = TESTDG)


1
    )


1
  )

1
DEFAULT_SERVICE_LISTENER=TESTDG_DG01_DGB

1
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 :

1
TESTDG_DG03_DG =


1
(DESCRIPTION =


1
  (ADDRESS = (PROTOCOL = TCP)(HOST = dataguard03)(PORT = 1521))


1
  (CONNECT_DATA =


1
    (SERVER = DEDICATED)


1
    (SERVICE_NAME = TESTDG_DG03_DGB)


1
    <strong><span style="color: #ff0000;">(UR = A)</span></strong>


1
  )


1
)

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

1
RMAN&gt; <strong><span style="color: #000080;">backup database plus archivelog;</span></strong>


1
Starting backup at 06-DEC-16


1
current log archived


1
using channel ORA_DISK_1


1
channel ORA_DISK_1: starting archived log backup set


1
channel ORA_DISK_1: specifying archived log(s) in backup set


1
input archived log thread=1 sequence=1 RECID=1 STAMP=929889249


1
input archived log thread=1 sequence=2 RECID=2 STAMP=929890538


1
input archived log thread=1 sequence=3 RECID=3 STAMP=929890542


1
input archived log thread=1 sequence=4 RECID=4 STAMP=929890554


1
input archived log thread=1 sequence=5 RECID=5 STAMP=929891211


1
input archived log thread=1 sequence=6 RECID=7 STAMP=929891431


1
input archived log thread=1 sequence=7 RECID=8 STAMP=929891801


1
input archived log thread=1 sequence=8 RECID=9 STAMP=929891803


1
input archived log thread=1 sequence=9 RECID=10 STAMP=929892097


1
input archived log thread=1 sequence=10 RECID=15 STAMP=929892136


1
input archived log thread=1 sequence=11 RECID=18 STAMP=929892178


1
input archived log thread=1 sequence=12 RECID=19 STAMP=929897944


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/10rmq7eo_1_1 tag=TAG20161206T165904 comment=NONE


1
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01


1
Finished backup at 06-DEC-16

1
Starting backup at 06-DEC-16


1
using channel ORA_DISK_1


1
channel ORA_DISK_1: starting full datafile backup set


1
channel ORA_DISK_1: specifying datafile(s) in backup set


1
input datafile file number=00001 name=+DATA/testdg_dg02/datafile/system.261.929888053


1
input datafile file number=00002 name=+DATA/testdg_dg02/datafile/sysaux.271.929888061


1
input datafile file number=00003 name=+DATA/testdg_dg02/datafile/undotbs1.272.929888075


1
input datafile file number=00005 name=+DATA/testdg_dg02/datafile/test02.273.929888083


1
input datafile file number=00004 name=+DATA/testdg_dg02/datafile/test01.274.929888083


1
input datafile file number=00006 name=+HISTO/testdg_dg02/datafile/test03.258.929888085


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/11rmq7ep_1_1 tag=TAG20161206T165905 comment=NONE


1
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03


1
Finished backup at 06-DEC-16

1
Starting backup at 06-DEC-16


1
current log archived


1
using channel ORA_DISK_1


1
channel ORA_DISK_1: starting archived log backup set


1
channel ORA_DISK_1: specifying archived log(s) in backup set


1
input archived log thread=1 sequence=13 RECID=20 STAMP=929897948


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/12rmq7et_1_1 tag=TAG20161206T165908 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-05 comment=NONE


1
Finished Control File and SPFILE Autobackup at 06-DEC-16

1
RMAN&gt; <span style="color: #000080;"><strong>backup archivelog all;</strong></span>

1
Starting backup at 06-DEC-16


1
current log archived


1
using channel ORA_DISK_1


1
channel ORA_DISK_1: starting archived log backup set


1
channel ORA_DISK_1: specifying archived log(s) in backup set


1
input archived log thread=1 sequence=1 RECID=1 STAMP=929889249


1
input archived log thread=1 sequence=2 RECID=2 STAMP=929890538


1
input archived log thread=1 sequence=3 RECID=3 STAMP=929890542


1
input archived log thread=1 sequence=4 RECID=4 STAMP=929890554


1
input archived log thread=1 sequence=5 RECID=5 STAMP=929891211


1
input archived log thread=1 sequence=6 RECID=7 STAMP=929891431


1
input archived log thread=1 sequence=7 RECID=8 STAMP=929891801


1
input archived log thread=1 sequence=8 RECID=9 STAMP=929891803


1
input archived log thread=1 sequence=9 RECID=10 STAMP=929892097


1
input archived log thread=1 sequence=10 RECID=15 STAMP=929892136


1
input archived log thread=1 sequence=11 RECID=18 STAMP=929892178


1
input archived log thread=1 sequence=12 RECID=19 STAMP=929897944


1
input archived log thread=1 sequence=13 RECID=20 STAMP=929897948


1
input archived log thread=1 sequence=14 RECID=21 STAMP=929897956


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/14rmq7f4_1_1 tag=TAG20161206T165916 comment=NONE


1
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03


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-06 comment=NONE


1
Finished Control File and SPFILE Autobackup at 06-DEC-16

Sauvegarde du controlfile courant pour standby

1
RMAN&gt; <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 :

1
2
<span style="font-size: 8pt;">[oracle@dataguard03 ~]$ cd $ORACLE_HOME/dbs</span>
<span style="font-size: 8pt;">[oracle@dataguard03 dbs]$ scp dataguard02:/oracle/ora112/dbs/orapwTESTDG .</span>

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) :

1
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
TESTDG.__db_cache_size=771751936


1
TESTDG.__java_pool_size=16777216


1
TESTDG.__large_pool_size=16777216


1
TESTDG.__oracle_base='/oracle'#ORACLE_BASE set from environment


1
TESTDG.__pga_aggregate_target=369098752


1
TESTDG.__sga_target=1107296256


1
TESTDG.__shared_io_pool_size=0


1
TESTDG.__shared_pool_size=285212672


1
TESTDG.__streams_pool_size=0


1
*.archive_lag_target=0


1
*.audit_file_dest='/oracle/admin/TESTDG/adump'


1
*.audit_trail='db'


1
*.compatible='11.2.0.0.0'


1
*.control_files='+DATA','+HISTO'


1
*.db_block_size=8192


1
*.db_domain='ANTIBES'


1
*.db_name='TESTDG'


1
*.db_file_name_convert='+DATA/testdg_dg02','+DATA/testdg_dg03','+HISTO/testdg_dg02','+HISTO/testdg_dg03'


1
*.log_file_name_convert='+DATA/testdg_dg02','+DATA/testdg_dg03','+HISTO/testdg_dg02','+HISTO/testdg_dg03'


1
*.db_recovery_file_dest='+HISTO'


1
*.db_recovery_file_dest_size=17825792000


1
*.db_unique_name='TESTDG_DG03'


1
*.dg_broker_start=TRUE


1
*.diagnostic_dest='/oracle'


1
*.fal_client='TESTDG_DG03_dg'


1
*.fal_server='testdg_dg02_dg'


1
*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.153)(PORT=1521)))'


1
*.log_archive_dest_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'


1
*.log_archive_dest_state_2='ENABLE'


1
*.log_archive_format='%t_%s_%r.dbf'


1
TESTDG.log_archive_format='%t_%s_%r.dbf'


1
*.log_archive_max_processes=4


1
*.log_archive_min_succeed_dest=1


1
TESTDG.log_archive_trace=0


1
*.open_cursors=300


1
*.pga_aggregate_target=367001600


1
*.processes=150


1
*.remote_login_passwordfile='EXCLUSIVE'


1
*.service_names='TESTDG.ANTIBES'


1
*.sga_target=1101004800


1
*.standby_file_management='AUTO'

Créer le fichier spfile pour l’instance TESTDG du serveur dataguard03 :

1
create spfile from pfile = '/tmp/initTESTDG.ora.forstdby';

Démarrage de la physical standby en NOMOUNT

1
SQL&gt; <strong><span style="color: #000080;">startup nomount</span></strong>


1
ORACLE instance started.

1
Total System Global Area 1102344192 bytes


1
Fixed Size 2227584 bytes


1
Variable Size 318767744 bytes


1
Database Buffers 771751936 bytes


1
Redo Buffers 9596928 bytes

Création de la physical standby à l’aide de RMAN

1
[oracle@dataguard03 ~]$ <strong><span style="color: #000080;">rman target sys/xxxxxxx@testdg_dg02_dg auxiliary sys/xxxxxxx@testdg_dg03_dg</span></strong>

1
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Dec 6 17:00:42 2016

1
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

1
connected to target database: TESTDG (DBID=2862720525)


1
 connected to auxiliary database: TESTDG (not mounted)

1
<span style="color: #333333;">RMAN&gt; <strong><span style="color: #000080;">duplicate target database for standby dorecover nofilenamecheck;</span></strong></span>

1
Starting Duplicate Db at 06-DEC-16


1
 using target database control file instead of recovery catalog


1
 allocated channel: ORA_AUX_DISK_1


1
 channel ORA_AUX_DISK_1: SID=12 device type=DISK

1
contents of Memory Script:


1
 {


1
 set until scn 1064724;


1
 sql clone "alter system set control_files =


1
 ''+DATA/testdg_dg03/controlfile/current.267.929898065'', ''+HISTO/testdg_dg03/controlfile/current.278.929898065'' comment=


1
 ''Set by RMAN'' scope=spfile";


1
 restore clone standby controlfile;


1
 }


1
 executing Memory Script

1
[...]

1
starting media recovery

1
channel ORA_AUX_DISK_1: starting archived log restore to default destination


1
 channel ORA_AUX_DISK_1: restoring archived log


1
 archived log thread=1 sequence=13


1
 channel ORA_AUX_DISK_1: restoring archived log


1
 archived log thread=1 sequence=14


1
 channel ORA_AUX_DISK_1: reading from backup piece /oradata/testdg_dg02/14rmq7f4_1_1


1
 channel ORA_AUX_DISK_1: piece handle=/oradata/testdg_dg02/14rmq7f4_1_1 tag=TAG20161206T165916


1
 channel ORA_AUX_DISK_1: restored backup piece 1


1
 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01


1
 archived log file name=+FRA/testdg_dg03/archivelog/2016_12_06/thread_1_seq_13.261.929898087 thread=1 sequence=13


1
 channel clone_default: deleting archived log(s)


1
 archived log file name=+FRA/testdg_dg03/archivelog/2016_12_06/thread_1_seq_13.261.929898087 RECID=2 STAMP=929898088


1
 archived log file name=+FRA/testdg_dg03/archivelog/2016_12_06/thread_1_seq_14.262.929898087 thread=1 sequence=14


1
 channel clone_default: deleting archived log(s)


1
 archived log file name=+FRA/testdg_dg03/archivelog/2016_12_06/thread_1_seq_14.262.929898087 RECID=1 STAMP=929898087


1
 media recovery complete, elapsed time: 00:00:01


1
 Finished recover at 06-DEC-16


1
 Finished Duplicate Db at 06-DEC-16

Ajout de la physical standby dans Dataguard

1
DGMGRL&gt; <span style="color: #000080;"><strong>add database testdg_dg03 as connect identifier is testdg_dg03_dg maintained as physical;</strong></span>


1
Database "testdg_dg03" added


1
DGMGRL&gt; <span style="color: #000080;"><strong>enable database testdg_dg03</strong></span>


1
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 :

1
SQL&gt; <strong><span style="color: #000080;">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;</span></strong>

1
System altered.

1
SQL&gt; <strong><span style="color: #000080;">show parameter fal</span></strong>

1
NAME                                 TYPE        VALUE


1
------------------------------------ ----------- ------------------------------


1
fal_client                           string      TESTDG_DG02


1
fal_server                           string      testdg_dg01_dg


1
SQL&gt; <span style="color: #000080;"><strong>alter system set fal_client = 'testdg_dg02,testdg_dg03' scope = both;</strong></span>

1
System altered.

1
SQL&gt; <strong><span style="color: #000080;">alter system set fal_server='testdg_dg01,testdg_dg02' scope = both;</span></strong>

1
System altered.

Configuration de TESTDG_DG01

On constate que TESTDG_DG01 cherche à tout prix à envoyer ses redologs vers TESTDG_DG03 :

1
NAME                                 TYPE        VALUE


1
------------------------------------ ----------- ------------------------------


1
log_archive_dest_3                   string      service="testdg_dg03_dg", LGWR


1
                                                 ASYNC NOAFFIRM delay=0 option


1
                                                  al compression=disable max_fai

1
                                                 lure=0 max_connections=1 reope


1
                                                  n=300 db_unique_name="testdg_d


1
                                                  g03" net_timeout=30, valid_for


1
2
3
4
                                                  =(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é :

1
SQL&gt; <span style="color: #000080;"><strong>alter system set log_archive_dest_state_3 = defer scope = both;</strong></span>

1
System altered.

1
SQL&gt; <span style="color: #000080;"><strong>alter system set fal_client = 'testdg_dg02' scope = both;</strong></span>

1
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 :

1
NAME                                 TYPE        VALUE


1
2
3
------------------------------------ ----------- ------------------------------
fal_client                           string      testdg_dg03_dg
fal_server                           string      testdg_dg01_dg

Le fal_server doit pointer sur TESTDG_DG02 :

1
SQL&gt; <span style="color: #000080;"><strong>alter system set fal_server = 'testdg_dg02' scope = both;</strong></span>

1
System altered.

Initialisation de la transmission des redologs entre TESTDG_DG02 et TESTDG_DG03

Relance de l’application des redologs sur TESTDG_DG03

1
SQL&gt; <span style="color: #000080;"><strong>alter database recover managed standby database cancel;</strong></span>

1
System altered.

1
SQL&gt; <span style="color: #000080;"><strong>alter database recover managed standby database using current logfile disconnect;</strong></span>

1
System altered.

Relance de la transmission des redologs de TESTDG_DG02 vers TESTDG_DG03

1
SQL&gt; <strong><span style="color: #000080;">alter system set log_archive_dest_state_2 = enable scope = both;</span></strong>

1
System altered.

1
SQL&gt; <span style="color: #000080;"><strong>alter system archive log current;</strong></span>

1
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 :

1
Tue Dec 06 17:10:26 2016


1
<strong><span style="color: #ff0000;">Primary database is in MAXIMUM PERFORMANCE mode</span></strong>


1
RFS[3]: Assigned to RFS process 23900


1
RFS[3]: Selected log 4 for thread 1 sequence 17 dbid -1432246771 branch 929889243


1
Tue Dec 06 17:10:26 2016


1
<span style="color: #ff0000;"><strong>Media Recovery Waiting for thread 1 sequence 17 (in transit)</strong></span>


1
Recovery of Online Redo Log: Thread 1 Group 4 Seq 17 Reading mem 0


1
 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 !