1 Clusterware sur la standby :
Il faut modifier le clusterware pour prendre en considération la bd et les instances.
Document de références :
Au préalable il faut créer un spfile sur l’ASM
Sur la standby :
# srvctl add database -d DRPOCRAD -n POCRAD -o /apps/oracle/11.2.0.2 -p +POCREDOL/DRPOCRAD/PARAMETERFILE/spfile.262.794920019 -r physical_standby -a POCREDOL,POCDBL,POCARCH
# srvctl add instance -d DRPOCRAD -i DRPOCRAD1 -n pocracl1
# srvctl add instance -d DRPOCRAD -i DRPOCRAD2 -n pocracl2
# srvctl config database -d DRPOCRAD
Database unique name: DRPOCRAD
Database name: POCRAD
Oracle home: /apps/oracle/11.2.0.2
Oracle user: oracle
Spfile: +POCREDOL/DRPOCRAD/PARAMETERFILE/spfile.262.794920019
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: DRPOCRAD
Database instances: DRPOCRAD1,DRPOCRAD2
Disk Groups: POCREDOL,POCDBL,POCARCH
Mount point paths:
Services:
Type: RAC
Database is administrator managed
srvctl stop database -d DRPOCRAD
srvctl start database –d DRPOCRAD
2 Active data guard:
Pour active dataguard :
ouverture de la standby en read only tout en appliquant les les logs.
alter database recover managed standby database cancel;
alter database open read only;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Afin de verifier :
SELECT 'Using Active Data Guard' ADG FROM V$MANAGED_STANDBY M, V$DATABASE D
WHERE M.PROCESS LIKE 'MRP%' and open_mode like 'READ ONLY%';
select inst_id,name,open_mode,database_role from gv$database;
INST_ID NAME OPEN_MODE DATABASE_ROLE
---------- --------- -------------------- ----------------
1 POCRAD READ ONLY WITH APPLY PHYSICAL STANDBY
2 POCRAD READ ONLY WITH APPLY PHYSICAL STANDBY
Pour que la standby démarre en mode read only automatiquement il faut modifier le clusterware.
srvctl modify database -d DRPOCRAD -n POCRAD -s open;
Information sur le statut via le broker :
DGMGRL> show database verbose 'DRPOCRAD'
Database - DRPOCRAD
Role: PHYSICAL STANDBY
Transport Lag: (unknown)
Apply Lag: 0 seconds
Real Time Query: ON
3 Broker :
Avantage les bascule par le grid enterprise manager.
Switch over / failover simple
Conversion en snapshot de la standby
3.1 Modification des parametres init
Sur la primaire :
alter system set dg_broker_config_file1='+POCDB/POCRAD/DATAGUARDCONFIG/dgb_config01.ora' scope=both sid='*';
alter system set dg_broker_config_file2='+POCREDO/POCRAD/DATAGUARDCONFIG/dgb_config02.ora' scope=both sid='*';
alter system set dg_broker_start =true scope=both sid='*';
Si on met les 2 fichiers de config sur le même dg on reçoit l’erreur ORA-02097
Sur la standby
alter database recover managed standby database cancel;
alter system set dg_broker_config_file1='+POCDBL/DRPOCRAD/DATAGUARDCONFIG/dgbl_config01.ora' scope=both sid='*';
alter system set dg_broker_config_file2='+POCREDOL/DRPOCRAD/DATAGUARDCONFIG/dgbl_config02.ora' scope=both sid='*';
alter system set dg_broker_start=true scope=both sid='*';
Sur les 4 serveurs j ai un process unix dmon_
3.2 Création de la configuration dans le broker :
create configuration dg_config as primary database is 'POCRAD' connect identifier is ‘POCRAD’;
add database 'DRPOCRAD' as connect identifier is ‘DRPOCRAD’;
enable configuration
Vérification à partir de n’importe quels serveurs:
DGMGRL> show configuration verbose
Configuration - dg_config
Protection Mode: MaxPerformance
Databases:
POCRAD - Primary database
DRPOCRAD - Physical standby database
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
3.3 Vérification en détails de la configuration :
A partir de la primaire voir la standby
show instance verbose 'DRPOCRAD1' on database 'DRPOCRAD';
show instance verbose 'DRPOCRAD2' on database 'DRPOCRAD';
A partir de la standby voir la primaire :
show instance verbose 'POCRAD1' on database 'POCRAD';
show instance verbose 'POCRAD2' on database 'POCRAD';
La log du broker est :
$ORACLE_BASE/diag_dest/rdbms/<$ORACLE_SID>/<$ORACLE_SID>/trace/drc<$ORACLE_SID>.log
3.4 Switchover et switchback avec le broker
Voir la doc id metalink : 1305019.1
On peut le lancer de n’importe qu elle serveur.
Avec le broker :
DGMGRL> switchover to 'DRPOCRAD';
DGMGRL> switchover to 'DRPOCRAD';
Performing switchover NOW, please wait...
New primary database "DRPOCRAD" is opening...
Operation requires shutdown of instance "POCRAD2" on database "POCRAD"
Shutting down instance "POCRAD2"...
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
shut down instance "POCRAD2" of database "POCRAD"
start up instance "POCRAD2" of database "POCRAD"
Sur l’ancienne standby pocracl1 et pocracl2
Le service sale_rw est up
Crsctl stat res -t
…
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE pocracl1
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE pocracl2
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE pocracl2
ora.cvu
1 ONLINE ONLINE pocracl2
ora.drpocrad.db
1 ONLINE ONLINE pocracl1 Open
2 ONLINE ONLINE pocracl2 Open
ora.drpocrad.sale_ro.svc
1 ONLINE OFFLINE
2 ONLINE OFFLINE
ora.drpocrad.sale_rw.svc
1 ONLINE ONLINE pocracl1
2 ONLINE ONLINE pocracl2
ora.drpocrad.stbpocrad.svc
1 ONLINE ONLINE pocracl1
2 ONLINE ONLINE pocracl2
ora.oc4j
1 ONLINE ONLINE pocracl2
ora.pocracl1.vip
1 ONLINE ONLINE pocracl1
ora.pocracl2.vip
1 ONLINE ONLINE pocracl2
ora.scan1.vip
1 ONLINE ONLINE pocracl1
ora.scan2.vip
1 ONLINE ONLINE pocracl2
ora.scan3.vip
1 ONLINE ONLINE pocracl2
Sur la nouvelle standby :
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE pocracm2
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE pocracm1
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE pocracm1
ora.cvu
1 ONLINE ONLINE pocracm1
ora.oc4j
1 ONLINE ONLINE pocracm2
ora.pocracm1.vip
1 ONLINE ONLINE pocracm1
ora.pocracm2.vip
1 ONLINE ONLINE pocracm2
ora.pocrad.db
1 ONLINE ONLINE pocracm1 Open,Readonly
2 ONLINE ONLINE pocracm2 Open,Readonly
ora.pocrad.sale_ro.svc
1 ONLINE ONLINE pocracm1
2 ONLINE ONLINE pocracm2
ora.pocrad.sale_rw.svc
1 ONLINE OFFLINE
2 ONLINE OFFLINE
ora.scan1.vip
1 ONLINE ONLINE pocracm2
ora.scan2.vip
1 ONLINE ONLINE pocracm1
ora.scan3.vip
1 ONLINE ONLINE pocracm1
En tant que client donc depuis mon PC je me connecte a sale_ro:
C:\Documents and Settings\mgranel>C:\Client11g\sqlplus.exe mg/mgmg@poccrs:1521/sale_ro
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 1 09:24:36 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
SQL> select * from V$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
--------------- ---------------- ----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU
----------------- --------- ------------ --- --------- ------- --------------- ---------- ---
DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
----------------- ------------------ --------- ---
2 POCRAD2 pocracm2.noam.transat.local
11.2.0.2.0 01-OCT-12 OPEN YES 2 STARTED ALLOWED NO
ACTIVE PRIMARY_INSTANCE NORMAL NO
Switch back
switchover to 'POCRAD';
4 Verification de GAP sequences
4.1 Verification de la primaire :
Verifie qu ona pas d erreur d’envoie des archives vers la standby :
SELECT inst_id,DEST_ID "ID",
STATUS "DB_status",
DESTINATION "Archive_dest",
ERROR "Error"
FROM gV$ARCHIVE_DEST WHERE DEST_ID <=3 and status!='INACTIVE';
INST_ID ID DB_status Archive_dest Error
---------- ---------- --------- ------------------------------------------------------- ---------------
2 1 VALID +POCARCH
2 2 VALID (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST
=drpoccrs.noam.transat.local)(PORT=1521)))(CONNECT_DATA
=(SERVICE_NAME=drpocrad)(SERVER=DEDICATED)))
1 1 VALID +POCARCH
1 2 VALID (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST
=drpoccrs.noam.transat.local)(PORT=1521)))(CONNECT_DATA
=(SERVICE_NAME=drpocrad)(SERVER=DEDICATED)))
On s informe sur la primaire du numero de sequences :
select thread#,max(sequence#) from gV$log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 112
2 44
4.2 Sur la standby
Les process
SELECT INST_ID,PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM gv$MANAGED_STANDBY ORDER BY 1;
INST_ID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
---------- --------- ------------ ---------- ---------- ---------- ----------
1 ARCH CLOSING 2 155 1 1
1 RFS IDLE 0 0 0 0
1 RFS IDLE 0 0 0 0
1 RFS IDLE 0 0 0 0
1 RFS IDLE 0 0 0 0
1 RFS IDLE 0 0 0 0
1 RFS IDLE 0 0 0 0
1 RFS IDLE 0 0 0 0
1 RFS IDLE 0 0 0 0
1 RFS IDLE 0 0 0 0
1 ARCH CLOSING 1 214 1 638
1 ARCH CONNECTED 0 0 0 0
1 ARCH CLOSING 2 153 1 633
1 ARCH CONNECTED 0 0 0 0
1 MRP0 APPLYING_LOG 1 215 6302 102400
1 ARCH CONNECTED 0 0 0 0
1 ARCH CLOSING 2 152 1 73
1 ARCH CLOSING 2 151 1 8
1 RFS IDLE 0 0 0 0
1 RFS IDLE 0 0 0 0
2 RFS IDLE 1 215 6304 1
2 RFS IDLE 2 156 6185 1
2 ARCH CONNECTED 0 0 0 0
2 ARCH CONNECTED 0 0 0 0
2 ARCH CONNECTED 0 0 0 0
2 ARCH CONNECTED 0 0 0 0
2 ARCH CLOSING 2 154 1 4
2 ARCH CONNECTED 0 0 0 0
2 ARCH CONNECTED 0 0 0 0
2 ARCH CONNECTED 0 0 0 0
En vue de supprimer par rman les archives déjà appliquer sur la standby :
select 'DELETE NOPROMPT COPY OF ARCHIVELOG UNTIL SEQUENCE '||max(sequence#)||' THREAD '||THREAD#||';' from v$archived_log
where applied='YES' and REGISTRAR='RFS' group by THREAD#;
'DELETENOPROMPTCOPYOFARCHIVELOGUNTILSEQUENCE'||MAX(SEQUENCE#)||'THREAD'||THREAD#
--------------------------------------------------------------------------------
DELETE NOPROMPT COPY OF ARCHIVELOG UNTIL SEQUENCE 112 THREAD 1;
DELETE NOPROMPT COPY OF ARCHIVELOG UNTIL SEQUENCE 47 THREAD 2;
Rman target /
DELETE NOPROMPT COPY OF ARCHIVELOG UNTIL SEQUENCE 112 THREAD 1;
DELETE NOPROMPT COPY OF ARCHIVELOG UNTIL SEQUENCE 47 THREAD 2
5 Services applicatifs a finir:
5.1 Pour les serveurs :
L idée est de construire 2 services un pour écrire et lire (sale_rw) sur la primaire et un autre juste pour lire (sale_ro) sur la standby afin de deporter la lecture sur la standby.
service
|
database_role
|
sale_rw
|
Primary
|
sale_ro
|
stdby
|
Creation des services:
Client jdbc :
Avec le os user oracle :
Sale_rw
Primaire:
srvctl add service -d POCRAD -s sale_rw -r POCRAD1,POCRAD2 -l PRIMARY -q FALSE -e NONE -m NONE -w 0 -z 0
standby
srvctl add service -d DRPOCRAD -s sale_rw -r DRPOCRAD1,DRPOCRAD2 -l PRIMARY -q FALSE -e NONE -m BASIC -w 0 -z 0
Sale_ro
Standby cluster – JDBC r/o service:
Primaire
srvctl add service -d POCRAD -s sale_ro -r POCRAD1,POCRAD2 -l PHYSICAL_STANDBY -q FALSE -e NONE -m BASIC -w 0 -z 0
Standby cluster – JDBC r/o service:
srvctl add service -d DRPOCRAD -s sale_ro -r DRPOCRAD1,DRPOCRAD2 -l PHYSICAL_STANDBY -q FALSE -e NONE -m BASIC -w 0 -z 0
For Sale_ro service, must run SRVCTL START SERVICE and SRVCTL STOP SERVICE on the primary: that’s how standby knows about this service through redo transmission
Srvctl start service –s sale_ro –d POCRAD
ajout sur la primaire
PROCEDURE CREATE_SERVICE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_NAME VARCHAR2 IN
NETWORK_NAME VARCHAR2 IN
GOAL NUMBER IN DEFAULT
DTP BOOLEAN IN DEFAULT
AQ_HA_NOTIFICATIONS BOOLEAN IN DEFAULT
FAILOVER_METHOD VARCHAR2 IN DEFAULT
FAILOVER_TYPE VARCHAR2 IN DEFAULT
FAILOVER_RETRIES NUMBER IN DEFAULT
FAILOVER_DELAY NUMBER IN DEFAULT
CLB_GOAL NUMBER IN DEFAULT
EDITION VARCHAR2 IN DEFAULT
begin
DBMS_SERVICE.CREATE_SERVICE(
service_name => 'sale_ro'
,network_name => ' sale_ro'
);
End;
/
ajout de ces 2 entrées dans le tnsnames ora du user oracle sur les serveurs :
sale_rw =
(DESCRIPTION =
(failover=on)
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP) (HOST=poccrs.noam.transat.local) (PORT=1521))
(ADDRESS=(PROTOCOL=TCP) (HOST=drpoccrs.noam.transat.local) (PORT=1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sale_rw)))
Sale_RO =
(DESCRIPTION =
(failover=on)
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP) (HOST=drpoccrs.noam.transat.local) (PORT=1521))
(ADDRESS=(PROTOCOL=TCP) (HOST=poccrs.noam.transat.local) (PORT=1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sale_ro)))
Un trigger pour gerer le demarrage du service en mode lecture :
CREATE OR REPLACE TRIGGER manage_service
after startup on database
DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('sale_rw');
ELSE
DBMS_SERVICE.START_SERVICE('sale_ro');
END IF;
END;
Si on essaie de créer une table sur la standby :
SQL> create table toto (t number);
create table toto (t number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
5.2 Cote clients jdbc :
Configure JDBC clients to use a connect descriptor
that includes an address list that includes the SCAN avec jdbc 11.2 sinon on est obligé de mettre les vip.
address for each site
"jdbc:oracle:thin:@" +
"(DESCRIPTION_LIST=" +
"(LOAD_BALANCE=off)" +
"(FAILOVER=on)" +
"(DESCRIPTION=" +
"(ADDRESS_LIST=" +
"(LOAD_BALANCE=on)" +
"(ADDRESS=(PROTOCOL=TCP)(HOST= poccrs.noam.transat.local)(PORT=1521)))" +
"(CONNECT_DATA=(SERVICE_NAME=sale_rw)))" +
"(DESCRIPTION=" +
"(ADDRESS_LIST=" +
"(LOAD_BALANCE=on)" +
"(ADDRESS=(PROTOCOL=TCP)(HOST= drpoccrs.noam.transat.local)(PORT=1521)))" +
"(CONNECT_DATA=(SERVICE_NAME=sale_rw))))";
Configurtion pour une reconnection rapides:
Setting the TCP_CONNTIMEOUT_STR property
enables the JDBC client to quickly traverse an
address_list
Properties prop = new Properties();
prop.put(oracle.net.ns.SQLnetDef.TCP_CONNTIMEOU
T_STR, ""+5000); // 5000ms
pds.setConnectionProperties(prop);
Configuration Example – JDBC App
Enable Fast Connection Failover (FCF) to receive FAN events
Configure Application to connect to all primary and
standby ONS daemons
pds.setFastConnectionFailoverEnabled(true);
pds.setONSConfiguration("nodes=hasun05:6200,has
un06:6200,hasun07:6200,hasun08:6200");
6 Backup rman sur la standby :
L idee est de faire les backups sur la standby car la promaire et la standby ont le meme dbid mais des db_unique_name different.
6.1 Pré requis
6.1.1 un catalog rman avec la primaire enregistrée :
An RMAN recovery catalog is required so that backups taken on one database
server can be restored to another database server. It is not sufficient to use only the
control file as the RMAN repository because the primary database will have no
knowledge of backups taken on the standby database.
[pocracm2.noam.transat.local:oracle:POCRAD2:/apps/oracle/diag/rdbms/pocrad/POCRAD2/trace]# rman target / catalog catalog_rman/ng0zi06@mtlunt68:1521/catalog
Recovery Manager: Release 11.2.0.2.0 - Production on Mon Oct 1 13:32:26 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: POCRAD (DBID=238222078)
connected to recovery catalog database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
6.1.2 Fast recovery AREA Enable flashback database :
Configure a fast recovery area for each database (the recovery area is local to a
database).
The fast recovery area is a single storage location on a file system or Oracle
Automatic Storage Management (Oracle ASM) disk group where all files needed
for recovery reside.
Primaire /standby :
Toujours se connecter a rman en utilisant un alias pas de rman target /
Exmple :
rman target sys/pocrad@drpocrad catalog catalog_rman/ng0zi06@mtlunt68:1521/catalog
alter system set db_recovery_file_dest='+POCARCH' scope=both sid='*';
Alter system set DB_RECOVERY_FILE_DEST_SIZE = 40G;
Arret de la bd et startup d une instance en mount
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=POCRAD' scope=spfile sid='*';
ALTER DATABASE FLASHBACK on;
shutdown de l instance et startup
RMAN configuration sur la primaire :
Le configure ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY c est pour rouler juste des delete archicelog sur la primaire.
Exemple :
L archive 652 n est pas supprime car pas appliquee sur la standby.
delete noprompt archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=177 instance=POCRAD2 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=47 instance=POCRAD2 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=157 instance=POCRAD2 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=42 instance=POCRAD2 device type=DISK
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=+POCARCH/pocrad/archivelog/2012_10_04/thread_1_seq_652.1658.795784355 thread=1 sequence=652
List of Archived Log Copies for database with db_unique_name POCRAD
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
3829 2 1362 A 04-OCT-12
Name: +POCARCH/pocrad/archivelog/2012_10_04/thread_2_seq_1362.435.795783591
3831 2 1363 A 04-OCT-12
Name: +POCARCH/pocrad/archivelog/2012_10_04/thread_2_seq_1363.1783.795783913
3835 2 1364 A 04-OCT-12
Name: +POCARCH/pocrad/archivelog/2012_10_04/thread_2_seq_1364.1644.795784353
deleted archived log
archived log file name=+POCARCH/pocrad/archivelog/2012_10_04/thread_2_seq_1362.435.795783591 RECID=3829 STAMP=795783591
deleted archived log
archived log file name=+POCARCH/pocrad/archivelog/2012_10_04/thread_2_seq_1363.1783.795783913 RECID=3831 STAMP=795783915
deleted archived log
archived log file name=+POCARCH/pocrad/archivelog/2012_10_04/thread_2_seq_1364.1644.795784353 RECID=3835 STAMP=795784355
Deleted 3 objects
Les backups sont pris sur la standby.
CONFIGURE RETENTION POLICY TO recovery window of 2 days;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
configure db_unique_name POCRAD connect identifier 'POCRAD';
configure db_unique_name DRPOCRAD connect identifier 'DRPOCRAD';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+POCDB/SNCFTESTRAC1.ORA';
RMAN> sql ' alter database enable block change tracking';
RMAN> LIST DB_UNIQUE_NAME OF DATABASE;
List of Databases
DB Key DB Name DB ID Database Role Db_unique_name
------- ------- ----------------- --------------- ------------------
3916785 POCRAD 238222078 PRIMARY POCRAD
3916785 POCRAD 238222078 STANDBY DRPOCRAD
Rman configuration dur la standby :
RMAN configuration parameters for database with db_unique_name DRPOCRAD are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'DB_RECOVERY' TO '%F';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
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 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 FALSE;
CONFIGURE DB_UNIQUE_NAME 'POCRAD' CONNECT IDENTIFIER 'POCRAD';
CONFIGURE DB_UNIQUE_NAME 'DRPOCRAD' CONNECT IDENTIFIER 'DRPOCRAD';
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+POCDBL/SNCFTESTRAC1.ORA';
Configure db_unique_name POCRAD connect identifier ‘POCRAD’;
run{
backup database;
backup archivelog all ;
backup current controlfile;
}
RESYNC CATALOG FROM DB_UNIQUE_NAME ALL;
7 Snapshot dataguard a faire :
The best part of this snapshot features is that the Snapshot Standby database in turn uses the Flashback Database technology to create a guaranteed restore point to which the database can be later flashed back to.All the features of the flashback are inherent in the snapshot standby.
Depuis la primaire et dans le broker :
convert database 'DRPOCRAD' to snapshot standby;
En sqlplus sur la standby :
alter database convert to snapshot st standby;
8 Annexes
8.2 Parameter Recommended Setting pour le spfile
- DB_NAME On a primary database, specify the name used when the database was created.
On a physical standby database, use the DB_NAMEof the primary database.
- DB_UNIQUE_NAME Specify a unique name for each database. This name stays with the database and
does not change, even if the primary and standby databases reverse roles.
- LOG_ARCHIVE_CONFIG The DG_CONFIGattribute of this parameter must be explicitly set on each
database in a Data Guard configuration to enable full Data Guard functionality.
Set DG_CONFIGto a text string that contains the DB_UNIQUE_NAMEof each
database in the configuration, with each name in this list separated by a comma.
- CONTROL_FILES Specify the path name for the control files on the primary database.
Example 3–1shows how to do this for two control files. It is recommended that
a second copy of the control file is available so an instance can be easily
restarted after copying the good control file to the location of the bad control
file.
- LOG_ARCHIVE_DEST_n Specify where the redo data is to bearchived on the primary and standby
systems. In Example 3–1:
■ LOG_ARCHIVE_DEST_1archives redo data generated by the primary
database from the local online redo log files to the local archived redo log
files in /arch1/chicago/.
■ LOG_ARCHIVE_DEST_2 is valid only for the primary role. This destination
transmits redo data to the remote physical standby destination boston
Note: If a fast recovery area was configured (with the DB_RECOVERY_FILE_
DESTinitialization parameter) and you havenot explicitly configured a local
archiving destination with the LOCATIONattribute, Data Guard automatically
uses the LOG_ARCHIVE_DEST_1initialization parameter (if it has not already
been set) as the default destination for local archiving. Also, see Chapter 15for
complete LOG_ARCHIVE_DEST_ninformation.
- LOG_ARCHIVE_DEST_STATE_n Specify ENABLEto allow redo transport services to transmit redo data to the
specified destination.
- REMOTE_LOGIN_PASSWORDFILE
This parameter must be set to EXCLUSIVEor SHAREDif a remote login
password file is used to authenticate administrative users or redo transport
sessions.
- LOG_ARCHIVE_FORMAT Specify the format for the archived redo log files using a thread (%t), sequence
number (%s), and resetlogs ID (%r).
- LOG_ARCHIVE_MAX_PROCESSES =integer
Specify the maximum number (from 1 to 30) of archiver (ARCn) processes you
want Oracle software to invoke initially. The default value is 4.
- FAL_SERVER Specify the Oracle Net service name of the FAL server (typically this is the
database running in the primary role). When the Chicago
the standby role, it uses the Boston
fetch (request) missing archived redo log files if Boston
automatically send the missing log files.
- DB_FILE_NAME_CONVERT Specify the path name and filename location of the primary database datafiles
followed by the standby location. This parameter converts the path names of the
primary database datafiles to the standby datafile path names. If the standby
database is on the same system as the primary database or if the directory
structure where the datafiles are located onthe standby site is different from the
primary site, then this parameter is required. Note that this parameter is used
only to convert path names for physicalstandby databases. Multiple pairs of
paths may be specified by this parameter.
- LOG_FILE_NAME_CONVERT Specify the location of the primary database online redo log files followed by
the standby location. This parameter converts the path names of the primary
database log files to the path names on the standby database. If the standby
database is on the same system as the primary database or if the directory
structure where the log files are located on the standby system is different from
the primary system, then this parameter is required. Multiple pairs of paths may
be specified by this parameter.
- STANDBY_FILE_MANAGEMENT Set to AUTOso when datafiles are added to or dropped from the primary
database, corresponding changes are made automatically to the standby
database.
8.3 Redo transport :
6.2.1.2 Redo Transport Authentication Using a Password File
If the SSL authentication requirements are not met, each database must use a remote
login password file. In a Data Guard configuration, all physical and snapshot standby
databases must use a copy of the password file from the primary database, and that
copy must be refreshed whenever the SYSOPERor SYSDBAprivilege is granted or
revoked, and after the password of any user with these privileges is changed.
When a password file is used for redo transport authentication, the password of the
user account used for redo transport authentication is compared between the database
initiating a redo transport session and the target database. The password must be the
same at both databases to create a redo transport session.
By default, the password of the SYSuser is used to authenticate redo transport
sessions when a password file is used. The REDO_TRANSPORT_USER database
initialization parameter can be used to select a different user password for redo
transport authentication by setting this parameter to the name of any user who has
been granted the SYSOPER privilege.
For administrative ease, Oracle recommends that
the REDO_TRANSPORT_USER parameter be set to the same value on the redo source
database and at each redo transport destination.
8.4 Cluster configuration:
Config de la primaire:
[pocracm2.noam.transat.local:grid:\:/apps/grid/network/admin]#srvctl config database -d POCRAD
Database unique name: POCRAD
Database name: POCRAD
Oracle home: /apps/oracle/11.2.0.2
Oracle user: oracle
Spfile: +POCDB/POCRAD/spfilePOCRAD.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: POCRAD
Database instances: POCRAD1,POCRAD2
Disk Groups: POCDB,POCARCH,POCREDO
Mount point paths:
Services: PRIPOCRAD
Type: RAC
Database is administrator managed
Configuration de la standby :
[pocracl2.noam.transat.local:oracle:DRPOCRAD2:/home/oracle]# srvctl config database -d DRPOCRAD
Database unique name: DRPOCRAD
Database name: POCRAD
Oracle home: /apps/oracle/11.2.0.2
Oracle user: oracle
Spfile: +POCREDOL/DRPOCRAD/PARAMETERFILE/spfile.262.794920019
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: DRPOCRAD
Database instances: DRPOCRAD1,DRPOCRAD2
Disk Groups: POCREDOL,POCDBL,POCARCH
Mount point paths:
Services: STBPOCRAD
Type: RAC
Database is administrator managed
8.5 Broker show configurations :
DGMGRL> show database verbose 'POCRAD';
Database - POCRAD
Role: PRIMARY
Instance(s):
POCRAD1
POCRAD2
Properties:
DGConnectIdentifier = 'POCRAD'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '8'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '+POCDBL, +POCDB, DRPOCRAD, POCRAD'
LogFileNameConvert = '+POCDBL, +POCDB, DRPOCRAD, POCRAD'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value
Database Status:
SUCCESS
DGMGRL> show database verbose 'DRPOCRAD';
Database - DRPOCRAD
Role: PHYSICAL STANDBY
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
DRPOCRAD1
DRPOCRAD2 (apply instance)
Properties:
DGConnectIdentifier = 'DRPOCRAD'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '8'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '+POCDB, +POCDBL, POCRAD, DRPOCRAD'
LogFileNameConvert = '+POCDB, +POCDBL, POCRAD, DRPOCRAD'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value
Database Status:
SUCCESS
DGMGRL>
8.6 Profile os de user oracle et grid
Oracle :
.bash_profile
[pocracl2.noam.transat.local:oracle:DRPOCRAD2:/home/oracle]# cat .bash_profile
# This is the default standard profile provided to a user.
# They are expected to edit it to meet their own needs.
MAIL=/usr/mail/${LOGNAME:?}
export TEMP=/tmp
export TMP=/tmp
export TMPDIR=/tmp
umask 022
export ORACLE_BASE=/apps/oracle
export ORACLE_HOME=$ORACLE_BASE/11.2.0.2
export GRID_HOME=/apps/grid
export TNS_ADMIN=$ORACLE_HOME/network/admin
export CRS_HOME=/apps/grid
export ORADBA=/home/oracle/BIN
PATH_ORIG=$PATH
#export PATH=$ORACLE_HOME/bin:$PATH:$ORACLE_HOME/OPatch:/bin:/usr/local/bin:/usr/local/sbin:/usr/sbin:/usr/bin:/usr/ccs/bin:/usr/bin:/opt/sfw/bin:/usr/openwin/bin:/apps/grid/bin:/usr/bin:/usr/ccs/bin
PATH=/usr/bin:/sbin:/usr/local/bin:/usr/local/bin/sudo:/usr/bin:/usr/sbin:/usr/ccs/bin:/bin:/usr/ucb/:/usr/share/man:/opt/bin:/usr/dt/bin:/usr/openwin/bin:/home/oracle/work/radix/liquibase-1.9.3-14jvm:$PATH:$ORACLE_HOME/OPatch:/opt/sfw/bin:$ORACLE_HOME/bin
PATH_ORA=$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
#stty erase '^H'
set history=2000
export VISUAL=/usr/bin/vi
export EDITOR=/usr/bin/vi
# set DISPLAY from IP info
if [ `who am i | awk '{echo $2}'` != "console" ]; then
IP=`who am i | cut -d\( -f2 | cut -d\) -f1`
DISPLAY=$IP:0.0
export DISPLAY
echo "DISPLAY set to '$DISPLAY'"
else
echo "No DISPLAY set."
fi
#
# ALIAS
alias ll='ls -altr'
alias sid='echo $ORACLE_SID'
#PROMPT
PS1="[`uname -n`:\$LOGNAME:\$ORACLE_SID:\$PWD]# "
set -o vi
export TERM=vt100
export ORACLE_SID=DRPOCRAD2
Grid :
export TEMP=/tmp
export TMP=/tmp
export EDITOR=vi
export TMPDIR=/tmp
umask 022
export ORACLE_BASE=/apps/oracle
export GRID_HOME=/apps/grid
export ORACLE_HOME=/apps/grid
export TNS_ADMIN=$GRID_HOME/network/admin
export CRS_HOME=/apps/grid
PATH_ORIG=$PATH
PATH=$GRID_HOME/bin:$GRID_HOME/OPatch:/sbin:/usr/local/bin:/usr/local/bin/sudo:/usr/sfw/bin:/usr/bin:/usr/sbin:/usr/ccs/bin:/bin:/usr/ucb/:/usr/share/man:/opt/bin:/usr/dt/bin:/usr/openwin/bin:$PATH:/opt/sfw/bin
export ORADBA=/home/grid/BIN
export PATH=$ORACLE_HOME/bin:$PATH
export TERM=vt100
PS1="[`uname -n`:\$LOGNAME:\:\$PWD]#"
set -o vi
# set DISPLAY from IP info
if [ `who am i | awk '{print $2}'` != "console" ]; then
IP=`who am i | cut -d\( -f2 | cut -d\) -f1`
DISPLAY=$IP:0.0
export DISPLAY
echo "DISPLAY set to '$DISPLAY'"
else
echo "No DISPLAY set."
fi
#
alias hasm='. $ORADBA/ora_profile asm'
alias crs='$GRID_HOME/bin/crsctl status res -t'
unalias ls
alias ll='ls -alrt'
alias log='vi /apps/grid/log/pocracm1/alertpocracm1.log'
8.7 Listener et tnsnames.ora :
POCRAD1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pocracm1-vip.noam.transat.local)(PORT = 1521))
(CONNECT_DATA =
(UR
(SERVER = DEDICATED)
(SID = pocrad1)
)
)
POCRAD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = poccrs.noam.transat.local)(PORT = 1521))
(CONNECT_DATA =
(UR
(SERVER = DEDICATED)
(SERVICE_NAME =POCRAD)
)
)
POCRAD2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pocracm2-vip.noam.transat.local)(PORT = 1521))
(CONNECT_DATA =
(UR
(SERVER = DEDICATED)
(SID = pocrad2)
)
)
DRPOCRAD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = drpoccrs.noam.transat.local)(PORT = 1521))
(CONNECT_DATA =
(UR
(SERVER = DEDICATED)
(SERVICE_NAME =DRPOCRAD)
)
)
DRPOCRAD1=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pocracl1-vip.noam.transat.local)(PORT = 1521))
(CONNECT_DATA =
(UR
(SERVER = DEDICATED)
(SID = DRPOCRAD1)
)
)
DRPOCRAD2=
(DESCRIPTION_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = pocracl2-vip.noam.transat.local)(PORT = 1521))
(CONNECT_DATA =
(UR
(SERVER = DEDICATED)
(SID = DRPOCRAD2)
)
)
sale_rw =
(DESCRIPTION =
(ADDRESS=(PROTOCOL=TCP) (HOST=drpoccrs.noam.transat.local) (PORT=1521))
(ADDRESS=(PROTOCOL=TCP) (HOST=poccrs.noam.transat.local) (PORT=1521))
(CONNECT_DATA =
(UR
(SERVER = DEDICATED)
(SERVICE_NAME = sale_rw)
)
)
sale_ro =
(DESCRIPTION =
(ADDRESS=(PROTOCOL=TCP) (HOST=drpoccrs.noam.transat.local) (PORT=1521))
(ADDRESS=(PROTOCOL=TCP) (HOST=poccrs.noam.transat.local) (PORT=1521))
(CONNECT_DATA =
(UR
(SERVER = DEDICATED)
(SERVICE_NAME = sale_ro)
)
)
[pocracl2.noam.transat.local:oracle:DRPOCRAD2:/apps/oracle/diag/rdbms/drpocrad/DRPOCRAD2/trace]# cat /apps/grid/network/admin/listener.ora
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=drpocrad2)(GLOBAL_DBNAME=drpocrad2_DGMGRL)(ORACLE_HOME=/apps/oracle/11.2.0.2)) (SID_DESC=(SID_NAME=drpocrad2)(GLOBAL_DBNAME=drpocrad2)(ORACLE_HOME=/apps/oracle/11.2.0.2)))
8.8 Attributs lors de la création de service par le clusterware :
8.8.1.1 srvctl add service
Adds services to a database and assigns them to instances. If you have multiple instances of a cluster database on the same node, then always use only one instance on that node for all of the services that node manages.
8.8.1.1.1 Syntax and Options
Use the srvctl add service command with one of the following syntax models:srvctl add service -d db_unique_name -s service_name
-r preferred_list [-a available_list] [-P {BASIC | NONE | PRECONNECT}]
[-l [PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY]
[-y {AUTOMATIC | MANUAL}] [-q {TRUE | FALSE}] [-j {SHORT | LONG}]
[-B {NONE | SERVICE_TIME | THROUGHPUT}] [-e {NONE | SESSION | SELECT}]
[-m {NONE | BASIC}] [-x {TRUE | FALSE}]
[-z failover_retries] [-w failover_delay]
srvctl add service -d db_unique_name -s service_name
-u {-r preferred_list | -a available_list}
srvctl add service -d db_unique_name -s service_name
-g server_pool [-c {UNIFORM | SINGLETON}] [-k network_number]
[-l [PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY]
[-y {AUTOMATIC | MANUAL}] [-q {TRUE | FALSE}] [-j {SHORT | LONG}]
[-B {NONE | SERVICE_TIME | THROUGHPUT}] [-e {NONE | SESSION | SELECT}]
[-m {NONE | BASIC}] [-P {BASIC | NONE | PRECONNECT}] [-x {TRUE | FALSE}]
[-z failover_retries] [-w failover_delay]
Table A-16 lists and describes all the srvctl add service options and whether they can be used when adding a service to a single-instance or Oracle RAC database.
Option
|
Description
|
-d db_unique_name
| Unique name for the database. |
-s service_name
| The service name. |
-r preferred_list
| A list of preferred instances used when the database is administrator managed. The list of preferred instances must be mutually exclusive with the list of available instances. Note: This option is available only with Oracle RAC and only for administrator-managed databases. |
-a available_list
| A list of available instances used when the database is administrator managed. The list of available instances must be mutually exclusive with the list of preferred instances. Note: This option is available only with Oracle RAC and only for administrator-managed databases. |
-g server_pool
| The name of a server pool used when the database is policy managed. Note: This option is available only with Oracle RAC and only for policy-managed databases. |
-c {UNIFORM | SINGLETON}
| The cardinality of the service, either UNIFORM (offered on all instances in the server pool) or SINGLETON (runs on only one instance at a time).Note: This option is available only with Oracle RAC and only for policy-managed databases. |
-k network_number
| The optional network number from which SCAN VIPs are obtained. If not specified, the SCAN VIPs are obtained from the same default network from which the nodeapps VIP is obtained.Note: This option is available only with Oracle RAC and only for policy-managed databases. |
