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.

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

RMAN> backup current controlfile for standby;

Starting backup at 06-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=164 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
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/0urmq7ea_1_1 tag=TAG20161206T170010 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-07 comment=NONE
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 .

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 :

TESTDG.__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 !

(Comments are closed)