1 Architecture
Voila la configuration qu’on va batir :
Primary
|
STANDBY
| |
Clusterware
|
11GR2 Grid infra (11.2.0.2)
|
11GR2 Grid infra (11.2.0.2)
|
Cluster Nodes
|
pocracm1,pocracm2
|
pocracl1,pocracl2
|
SCAN
| ||
SCAN listener Host/port
|
poccrs 1521
|
drpocrcs 1521
|
VIPs
|
pocracm1-vip,pocracm2-vip
|
pocracl1-vip,pocracl2-vip
|
DB_UNIQUE_NAME
|
pocrad
|
drpocrad
|
DB_NAME
|
POCRAD
|
POCRAD
|
DB Instances
|
POCRAD1,POCRAD2
|
DRPOCRAD1,DRPOCRAD2
|
DB LISTENER
|
LISTENER
|
LISTENER
|
DB Listener Host/port
|
1521
|
1521
|
DB STORAGE
|
ASM
|
ASM
|
File Management
|
FS
|
FS
|
GRID_HOME
|
/apps/grid
|
/apps/grid
|
ORACLE_HOME
|
/appl/oracle/11.2.0.2
|
/appl/oracle/11.2.0.2
|
11g R2 RAC version
|
11.2.0.2
|
11.2.0.2
|
OS
|
linux
|
Linux
|
service
|
Sale_rw
|
Sale_ro
|
archivelog
| ||
ASM
|
taille en Go
|
ASM
|
POCDB
|
40
|
POCDBL
|
POCOCR
|
2
|
POCOCRL
|
POCREDO
|
10
|
POCREDOL
|
POCARCH
|
40
|
POCARCH
|
IP
Primaire :
network identity
|
IP
|
defined name
|
adresse host 1
|
10.132.0.40
| |
private1
|
192.168.20.101
| |
adresse vip1
|
10.132.0.42
|
pocracm1-vip
|
adress host 2
|
10.132.0.41
| |
private2
|
192.168.20.102
| |
adresse vip2
|
10.132.0.43
|
pocracm2-vip
|
scan vip1
|
10.132.0.44
|
POCCRS
|
scan vip2
|
10.132.0.45
|
POCCRS
|
scan vip3
|
10.132.0.46
|
POCCRS
|
Standby :
network identity
|
IP
|
defined name
|
adresse host 1
|
10.132.0.47
| |
private1
|
192.168.30.101
| |
adresse vip1
|
10.132.0.49
|
pocracl1-vip
|
adress host 2
|
10.132.0.48
| |
private2
|
192.168.30.102
| |
adresse vip2
|
10.132.0.50
|
pocracl2-vip
|
scan vip1
|
10.132.0.51
|
DRPOCCRS
|
scan vip2
|
10.132.0.52
|
DRPOCCRS
|
scan vip3
|
10.132.0.53
|
DRPOCCRS
|
2 pré requis
Installer la grid et les binaires identiques sur les 4 serveurs c est pas l’objet du document.
Mais a souligner qu’au niveau OS on a 2 users distincts :
Grid : pour le clusterware oracle,
Oracle pour la bd.
2.1 Primaire :
La bd doit être en mode archivelog
Pour vérifier :
Par défaut elle sera maximum performance
Select dbid, archivelog_mode, force loging from V$database;
On modifiera pour être synchrones.
Explication sur les différentes protections :
Maximum performance This is the default protection mode. It provides the highest
level of data protection that is possible without affecting the performance of a primary
database. This is accomplished by allowing transactions to commit as soon as all redo
data generated by those transactions has beenwritten to the online log. Redo data is
also written to one or more standby databases, but this is done asynchronously with
respect to transaction commitment, so primary database performance is unaffected by
delays in writing redo data to the standby database(s).
This protection mode offers slightly less data protection than maximum availability
mode and has minimal impact onprimary database performance.
Note: Asynchronously committed transactions are not protected by
Data Guard against loss until the redogenerated by those transactions
has been written to the standby redo log of at least one synchronized
standby database.
Maximum availability This protection mode provides the highest level of data
protection that is possible without compromising the availability of a primary
database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to the standby redo log on at
least one synchronized standby database. If the primary database cannot write its redo
stream to at least one synchronized standby database, it operates as if it were in
maximum performance mode to preserve primary database availability until it is
again able to write its redo stream to a synchronized standby database.
This protection mode ensures zero data loss except in the case of certain double faults,
such as failure of a primary database after failure of the standby database.
2.1.1 Pour modifier la protection :
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};
Avec le broker:
DGMGRL> edit database 'POCRAD' set property 'LogXptMode'='SYNC';
DGMGRL> edit database 'DRPOCRAD' set property 'LogXptMode'='SYNC';
DGMGRL> edit configuration set protection mode as maxavailability;
Pour verifier :
SQL> select protection_mode from V$database;
PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY
Les bd doivent être en spfile avec un spfile commun sur ASM.
Le comptaible doit être identiques entre toutes les instances.
select inst_id, value from gv$parameter where name='compatible';
3 Modifications de parametres init
Pour connaitre les valeurs:
set linesize 500 pages 0
col value for a90
col name for a50
select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2',
'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile',
'log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert',
'log_file_name_convert', 'standby_file_management');
Primary :
NAME
|
VALUE
|
db_file_name_convert
|
‘+POCDBL’,’ +POCDB’,’DRPOCRAD’,’POCRAD’
|
log_file_name_convert
|
‘+POCREDOL’,’ +POCREDO’, ’DRPOCRAD’,’POCRAD’
|
log_archive_dest_1
|
LOCATION=+POCARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=POCRAD
|
log_archive_dest_2
|
SERVICE=DRPOCRAC ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DRPOCRAD'
|
log_archive_dest_state_1
|
enable
|
log_archive_dest_state_2
|
enable
|
fal_server
|
DRPOCRAD
|
log_archive_config
|
DG_CONFIG=(POCRAD,DRPOCRAD)
|
log_archive_format
|
%t_%s_%r.dbf
|
log_archive_max_processes
|
8
|
standby_file_management
|
AUTO
|
remote_login_passwordfile
|
EXCLUSIVE
|
db_name
|
POCRAD
|
db_unique_name
|
POCRAD
|
En cas de difficulté lies au tnsnames.ora on peut y mettre :
Une chaine de connection du type :
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=poccrs.noam.transat.local)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=pocrad)(SERVER=DEDICATED)))
Pour le log archive_dest_2 on peut utiliser un alias du tnsnames.ora (exemple sur les standby)
Standby :
NAME
|
VALUE
|
db_file_name_convert
|
+POCDB, +POCDBL, POCRAD, DRPOCRAD
|
log_file_name_convert
|
+POCDB, +POCDBL, POCRAD, DRPOCRAD
|
log_archive_dest_1
|
LOCATION=+POCARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DRPOCRAD
|
log_archive_dest_2
|
SERVICE=POCRAC ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=POCRAD
|
log_archive_dest_state_1
|
enable
|
log_archive_dest_state_2
|
enable
|
fal_server
|
POCRAD
|
log_archive_config
|
DG_CONFIG=(POCRAD,DRPOCRAD)
|
log_archive_format
|
%t_%s_%r.dbf
|
log_archive_max_processes
|
8
|
standby_file_management
|
AUTO
|
remote_login_passwordfile
|
EXCLUSIVE
|
db_name
|
POCRAD
|
db_unique_name
|
DRPOCRAD
|
Voir en annexe les 2 spfiles complets
Explication sur le paramétrage des log_archive_dest_2 :
Paramètres log archive dest 2
The NET_TIMEOUT attribute is used to specify how long the LGWRprocess will block
waiting for an acknowledgement that redo data has been successfully received by a
destination that uses the synchronous redo transport mode. If an acknowledgement is not received within NET_TIMEOUTseconds, the redo transport connection is
terminated and an error is logged.
Oracle recommends that the NET_TIMEOUTattribute be specified whenever the
synchronous redo transport mode is used, so that the maximum duration of a redo
source database stall caused by a redo transport fault can be precisely controlled. See
The AFFIRM attribute is used to specify that redo received from a redo source database
is not acknowledged until it has been written to the standby redo log. The NOAFFIRM
attribute is used to specify that received redo is acknowledged without waiting for
received redo to be written to the standby redo log.
The REOPEN attribute is used to specify the minimum number of seconds between
automatic reconnect attempts to a redo transport destination that isinactive because of
a previous error.
The COMPRESSION attribute is used to specify that redo data is transmitted to a redo
transport destination in compressed form. Redo transport compression can
significantly improve redo transport performance on network links with low
bandwidth and high latency.
Redo transport compression is a feature of the Oracle Advanced Compression option. You must purchase a license for this option before using the redo transport
compression feature.
Donc on fera quelque chose comme ca quand on sera en mode maximum availability :
Sur le primaire
Alter system set LOG_ARCHIVE_DEST_2='SERVICE=DRPOCRAD SYNC NET_TIMEOUT=30 VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) REOPEN=90 DB_UNIQUE_NAME=DRPOCRAD' scope=both sid=’*’;
Sur la standby
Alter system set LOG_ARCHIVE_DEST_2='SERVICE=POCRAD SYNC NET_TIMEOUT=30 VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) REOPEN=90 DB_UNIQUE_NAME=POCRAD' scope=both sid=’*’;
4 Tnsnames et listener
Ils doivent être identiques sur tous les neouds :
A noter que le tnsnames.ora pour le user oracle doit être dans
$ORACLE_HOME/network/admin car le broker a besoin de path pour prendre en conpte les alias tns.
POCRAD1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pocracm1-vip.noam.transat.local)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = pocrad1)
)
)
POCRAD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = poccrs.noam.transat.local)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =POCRAD)
)
)
POCRAD2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pocracm2-vip.noam.transat.local)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = pocrad2)
)
)
DRPOCRAD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = drpoccrs.noam.transat.local)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =DRPOCRAD)
)
)
DRPOCRAD1=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pocracl1-vip.noam.transat.local)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = DRPOCRAD1)
)
)
DRPOCRAD2=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pocracl2-vip.noam.transat.local)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = DRPOCRAD2)
)
)
5 Les standby redologs :
Il sont nécessaires pour être synchrones entre primaire et standby.
The standby redo log must have at least one more redo log group than the redo log at
the redo source database, for each redo threadat the redo source database. At the redo
source database, query the V$LOGview to determine how many redo log groups are in
the redo log at the redo source database and query the V$THREADview to determine
how many redo threads exist at the redo source database.
Caution: Whenever a redo log group is added to a primary database,
a log group must also be added to the standby redo log of each
standby database in the configuration. Otherwise, the standby
database may become unsynchronized after a primary log switch,
which could temporarily prevent a zero data loss failover or cause a
primary database operating in maximum protection mode to shut
down.
2 threads avec 2 redolog de 52M donc 6 standby redologs
alter database add standby logfile thread 1 group 5 '+POCREDO' size 52428800;
alter database add standby logfile thread 1 group 6 '+POCREDO' size 52428800;
alter database add standby logfile thread 1 group 7 '+POCREDO' size 52428800;
alter database add standby logfile thread 2 group 8 '+POCREDO' size 52428800;
alter database add standby logfile thread 2 group 9 '+POCREDO' size 52428800;
alter database add standby logfile thread 2 group 10 '+POCREDO' size 52428800;
Sur la standby :
Pour les créer il faut arrêter le recover managed:
alter database add standby logfile thread 1 group 5 '+POCREDOL' size 52428800;
alter database add standby logfile thread 1 group 6 '+POCREDOL' size 52428800;
alter database add standby logfile thread 1 group 7 '+POCREDOL' size 52428800;
alter database add standby logfile thread 2 group 8 '+POCREDOL' size 52428800;
alter database add standby logfile thread 2 group 9 '+POCREDOL' size 52428800;
alter database add standby logfile thread 2 group 10 '+POCREDOL' size 52428800;
Si le backup de la primaire est pris après la création et que le paramètre log_file_name_convert est positionné, il seront créer automatiquement.
6 Backup RMAN sur la primaire pour construire la standby :
RMAN configuration parameters for database with db_unique_name DRPOCRAD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/apps/oracle/backup/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/apps/oracle/backup/%d_t%t_s%s_p%p';
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 TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/apps/oracle/11.2.0.2/dbs/snapcf_DRPOCRAD2.f'; # default
run
{
allocate channel ch1 type disk format '/apps/oracle/backup/Primary_bkp_for_stndby_%U';
backup database;
backup current controlfile for standby;
}
Faut noter que la primaire et la standby auront le même dbid :
Primaire /standby :
SQL> select DBID from v$database;
DBID
----------
238222078
Donc au niveau du catalog rman on peut faire des backups et des rman de la standby ou la primaire (cf voir plus loin)
7 Construction de la standby
7.1 prerequis :
· copier et modifier les init comme décrit au dessus sur les 2 noeuds
· copier le backup sur un des noeuds standby
· copier les fichier orapw<SID> d’une des instances primaires vers les standby en prenant soit d’adapter les SID dans le nom du fichier
Tout les fichiers orapwd doivent être identiques :
Scp orapwPOCRAD2 pocracm1 : /apps/oracle/11.2.0.2/dbs/orapwPOCRAD1
Scp orapwPOCRAD2 pocracl1 : /apps/oracle/11.2.0.2/dbs/orapwDRPOCRAD1
Scp orapwPOCRAD2 pocracl2 : /apps/oracle/11.2.0.2/dbs/orapwDRPOCRAD2
Pour vérifier on peut faire la commande unix strings des fichiers ca doit être identiques sur tous lesnoeuds.
7.2 Restore rman a partir de la standby :
On fait la restore a partir d’une des standby.
En sqlplus passer la bd en nomount avec un init complet mais provisoire mais complet .
A vérifier les path dans l init.
A créer aussi sur les standby le directory des backup (dans notre cas /apps/backup).
Dans RMAN :
rman target sys/pocrad@POCRAD1 auxiliary /
duplicate target database for standby nolfilenamecheck;
7.3 Recover managed :
C est l’application des archivelog sur la standby.
Demarrer le recover managed :
La commande d’origine a évoluer ci-dessous les différentes syntaxes :
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;
Oracle a introduit une notion de Delay pour l’application des logs :
7.2.2 Specifying a Time Delay for the Application of Archived Redo Log Files
In some cases, you may want to create a time lag between the time when redo data is
received from the primary site and when it is applied to the standby database. You can
specify a time interval (in minutes) to protect against the application of corrupted or
erroneous data to the standby database.
When you set a DELAYinterval, it does not
delay the transport of the redo data to the standby database. Instead, the time lag you
specify begins when the redo data is completely archived at the standby destination.
Specifying a Time Delay
You can set a time delay on primary and standby databases using the
DELAY=minutesattribute of the LOG_ARCHIVE_DEST_ninitialization parameter to
Canceling a Time Delay
You can cancel a specified delay interval as follows:
■ For physical standby databases, use the NODELAYkeyword of the RE
MANAGED STANDBY DATABASE clause:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;
Arret du recover managed :
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Le Process unix associé au recover managed mrp_
Ps –ef | grep mrp
A noter qu il ne tourne que sur un instance de base standby.
Aucun commentaire:
Enregistrer un commentaire