1] Broker
Configuration
Dataguard Broker is a unified administration
interface of every Dataguard components.
-
Startup Start
in MOUNT mode is enough, Broker does the open RW or Recover
-
Switchover on
Planned Maintenance
-
Failover on Incident
-
Initialization Init
parameters Management
12c Create Dataguard
Broker Configuration - DGMGRL (Doc ID 1583588.1)
|
Broker
|
DB_NAME
|
Fichiers de configuration
|
Log
|
|
host
|
PRI12C
|
/rdo1/pri12c/ctl/dr1PRI12C.dat
/rdo2/pri12c/ctl/dr2PRI12C.dat
|
/work/oracle/pri12c/diag/rdbms/pri12c/PRI12C/trace/drcPRI12C.log
|
|
host-stb
|
STD12C
|
/rdo1/std12c/ctl/dr1PRI12C.dat
/rdo2/std12c/ctl/dr2PRI12C.dat
|
/work/oracle/std12c/diag/rdbms/std12c/STD12C/trace/drcSTD12C.log
|
Pre-requisite on both Primary and Standby Instance
sqlplus -s '/ as
sysdba' <<!
alter system set
dg_broker_start=true ;
alter system set
log_Archive_dest_2='';
!
Run « dgmgrl » to create Bkroker confguration
dgmgrl -echo
<<!
connect ${con_str}
CREATE
CONFIGURATION 'PRI12CDG' AS PRIMARY DATABASE IS 'PRI12C' CONNECT IDENTIFIER IS PRI12CTEC
;
add database STD12C
as connect identifier is STD12CTEC ;
enable
configuration ;
show
configuration;
!
Set Protection Mode to « Max Availability »
dgmgrl -echo
<<!
connect ${con_str}
edit database 'PRI12C'
set property LogXptMode='SYNC';
edit database STD12C
set property LogXptMode='SYNC';
EDIT CONFIGURATION
SET PROTECTION MODE AS MAXAVAILABILITY;
show
configuration;
System altered.
System altered.
System altered.
System altered.
DGMGRL for IBM/AIX RISC System/6000: Version
12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for
information.
DGMGRL> connect SYS/sys_psw@PRI12C
Connected as SYSDBA.
DGMGRL> CREATE CONFIGURATION 'PRI12CDG' AS PRIMARY
DATABASE IS 'PRI12C' CONNECT IDENTIFIER IS PRI12CTEC ;
Configuration "PRI12CDG" created with
primary database "PRI12C"
DGMGRL> add database STD12C as connect identifier
is STD12CTEC ;
Database "std12c" added
DGMGRL> enable configuration ;
Enabled.
DGMGRL> show configuration;
Configuration - PRI12CDG
Protection
Mode: MaxPerformance
Members:
PRI12C -
Primary database
std12c -
Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
(status updated 15 seconds ago)
DGMGRL> DGMGRL> DGMGRL for IBM/AIX RISC
System/6000: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for
information.
DGMGRL> connect SYS/sys_psw@PRI12C
Connected as SYSDBA.
DGMGRL> edit database 'PRI12C' set property
LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit database STD12C set property
LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS
MAXAVAILABILITY;
Succeeded.
DGMGRL> show configuration;
Configuration - PRI12CDG
Protection
Mode: MaxAvailability
Members:
PRI12C -
Primary database
std12c -
Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
(status updated 32 seconds ago)
DGMGRL> DGMGRL>
Last correction
$ dgmgrl
DGMGRL for IBM/AIX RISC
System/6000: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013,
Oracle. All rights reserved.
Welcome to DGMGRL, type
"help" for information.
DGMGRL> connect sys/sys_psw
Connected as SYSDG.
DGMGRL> sho database
verbose STD12C
Unrecognized command
"sho", try "help"
DGMGRL> show database
verbose STD12C
Database - std12c
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 64.00 KByte/s
Active Apply Rate: 2.16 MByte/s
Maximum Apply Rate: 3.79 MByte/s
Real Time Query: OFF
Instance(s):
STD12C
Properties:
DGConnectIdentifier = 'std12ctec'
ObserverConnectIdentifier = ''
LogXptMode = 'SYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/data/pri12c/, /data/std12c/'
LogFileNameConvert = '/rdo1/pri12c/, /rdo1/std12c/,
/rdo2/pri12c/, /rdo2/std12c/'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
StaticConnectIdentifier =
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PRI12C)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=STD12C_DGMGRL.cg13.fr)(INSTANCE_NAME=STD12C)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/arch/std12c'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
Wrong Host -> Modify the item :
DGMGRL> EDIT DATABASE 'std12c'
SET PROPERTY StaticConnectIdentifier =
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=STD12C)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=STD12C_DGMGRL.cg13.fr)(INSTANCE_NAME=STD12C)(SERVER=DEDICATED)))'
;
Property
"staticconnectidentifier" updated
2] Post
Configurations
Database startup Trigger modification
Takes care
of new Dataguard service names ( technical ).
CREATE OR REPLACE TRIGGER
CD13_START_SERVICES AFTER STARTUP on DATABASE
declare
inst_name
varchar2(16) ;
nn number;
begin
select
INSTANCE_NAME INTO
INST_NAME from
v$instance;
execute immediate 'alter system set
service_names='''||inst_name||'.<domain>''';
for v_loop in (select name from
dba_services where upper(name) not like '%DGB' and upper(name) not like '%DGMGRL' and upper(name) not like ''||substr(inst_name,1,4)||'%' order by 1) LOOP
SELECT count(1) INTO nn from
v$active_services WHERE UPPER(name)=UPPER(v_loop.name);
IF (nn = 0) then
DBMS_SERVICE.START_SERVICE(service_name => ''||v_loop.name||'');
end IF;
end loop;
end;
/
Take care of the error « RFS network connection lost at host 'STD12CTEC'
error 3135 »
Comment the entry in « sqlnet.ora » file
# To specify the
time, in seconds, for a database server to complete a send operation to clients
after establishing a connection.
###
standby no ### SQLNET.SEND_TIMEOUT=600
3] Dataguard
Broker Switchover
DGMGRL>
connect SYS/<psw>@STD12C
DGMGRL>
SWITCHOVER TO 'std12c' ;
Switchover
succeeded, new primary is "std12c"
4] Dataguard
Broker Failover
DGMGRL>
connect SYS/<psw>@STD12C
DGMGRL>
FAILOVER TO 'std12c' ;
Failover
succeeded, new primary is "std12c"
$
dgmgrl
DGMGRL
for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - 64bit Production
Copyright
(c) 2000, 2005, Oracle. All rights reserved.
Welcome
to DGMGRL, type "help" for information.
DGMGRL>
connect sys/<sys_psw>
Connected.
DGMGRL>
show configuration ;
Configuration
Name: PRI12C
Enabled: YES
Protection Mode: MaxProtection
Fast-Start Failover: DISABLED
Databases:
PRI12C - Physical standby database
STD12C - Primary database
Current
status for "PRI12C":
Error:
ORA-16625: cannot reach the database
DGMGRL>
FAILOVER TO 'PRI12C' ;
Performing
failover NOW, please wait...
Failover
succeeded, new primary is "PRI12C"
DGMGRL>
show configuration ;
Configuration
Name: PRI12C
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
PRI12C - Primary database
STD12C - Physical standby database
(disabled)
Current
status for "PRI12C":
SUCCESS
5] Dataguard
Broker Reinstate
Following a Switchover
( Planned ) or Failover ( Crash ), the former Primary DB doesn’t need to be
created again, assuming we have Flashback Logs enabled.
We just have
to « rewind » the DB to the start-SCN of the new-Primary, then to
synchronize both ( recover ) :
We may then
Switchover to get back to normal Primary and Secondary.
5.1 Enable Flashback Database
DB_FLASHBACK_RETENTION_TARGET
specifies the upper limit (in minutes) on how far back in time the database may
be flashed back. How far back one can flashback a database depends on how much
flashback data Oracle has kept in the flash recovery area.
SQL>
alter system set db_flashback_retention_target = 15 ;
System
altered.
Primary
Instance in MOUNT mode, connect / as sysdba
SQL>
show parameter flash
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
db_flashback_retention_target integer 1440
SQL>
alter system set db_recovery_file_dest_size = 200M;
System
altered.
SQL>
alter system set db_recovery_file_dest ='/work/oracle/pri12c/flash_area' ;
System
altered.
SQL>
alter system set dg_broker_start = FALSE ;
System
altered.
SQL>
shutdown immediate ;
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL>
startup mount exclusive ;
ORACLE
instance started.
Total
System Global Area 494927872 bytes
Fixed
Size 2097440 bytes
Variable
Size 356519648 bytes
Database
Buffers 134217728 bytes
Redo
Buffers 2093056 bytes
Database
mounted.
SQL>
alter database flashback on ;
Database
altered.
set lines
500 pages 5000
col
STANDBY_BECAME_PRIMARY_SCN for 9999999999999999999999999999
select
NAME,OPEN_MODE,LOG_MODE,OPEN_RESETLOGS,LAST_OPEN_INCARNATION#,FLASHBACK_ON,STANDBY_BECAME_PRIMARY_SCN
from
v$database;
NAME LOG_MODE OPEN_RESETL LAST_OPEN_INCARNATION#
FLASHBACK_ON
STANDBY_BECAME_PRIMARY_SCN
---------
------------ ----------- ---------------------- ------------------
--------------------------
PRI12C ARCHIVELOG NOT ALLOWED 1 YES 1.0255E+10
SQL>
alter system set dg_broker_start = TRUE ;
System
altered.
SQL>
select open_mode from v$database ;
OPEN_MODE
----------
READ
WRITE
SQL>
-- broker started it
PRI12C $
ls -ltr /work/oracle/pri12c/flash_area/PRI12C/flashback
total 0
-rw-r----- 1 oracle
dba 8200192 Mar 13 17:39
o1_mf_9l3psns9_.flb
Secondary
Instance, connect / as sysdba
$ sqlplus
/ as sysdba
SQL*Plus:
Release 10.2.0.5.0 - Production on Thu Mar 13 17:23:19 2014
Copyright
(c) 1982, 2010, Oracle. All Rights
Reserved.
Connected
to:
Oracle
Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
alter system set db_recovery_file_dest_size = 200M;
System
altered.
SQL>
!mkdir /work/oracle/std12c/flash_area
SQL>
alter system set db_recovery_file_dest ='/work/oracle/std12c/flash_area' ;
System
altered.
$ ls
-ltrR /work/oracle/std12c/flash_area/
total 0
5.2 Reinstate Database
Current status for "PRI12C":
Error: ORA-16661: the
standby database needs to be reinstated
DGMGRL>
connect SYS/<psw>@STD12C
DGMGRL>
reinstate database PRI12C ;
Reinstating
database "PRI12C", please wait...
Operation
requires shutdown of instance "PRI12C" on database "PRI12C"
Shutting
down instance "PRI12C"...
ORA-01109:
database not open
Database
dismounted.
ORACLE
instance shut down.
Operation
requires startup of instance "PRI12C" on database "PRI12C"
Starting
instance "PRI12C"...
ORACLE
instance started.
Database
mounted.
Continuing
to reinstate database "PRI12C" ...
Reinstatement
of database "PRI12C" succeeded
