Data Guard Observer
das unbekannte Wesen
Autor: Ernst Leber
19.11.2015 DOAG Nürnberg
Facts & Figures
Technologie-orientiert
Branchen-unabhängig
Inhabergeführt
Ausbildungsbetrieb
24 Mio.
Euro
Umsatz
Hauptsitz
Ratingen
Gründung
1994
Niederlassung
Frankfurt am Main
240
Beschäftigte
Zertifizierter
Partner von
Oracle,
Microsoft
und SAP
2
Data Guard Observer das unbekannte Wesen
Agenda
Data Guard
• Übersicht
• Konfiguration
Observer
• Konfiguration
• User Defined Conditions
• V$Views
• Demo
Links
Mit diesem Text werden Terminal Outputs dargestellt
3
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
Data Guard Konfiguration
ORLCA
Database
11.2.0.4
ORCLB
Database
11.2.0.4
DGMGRL> show configuration;
Configuration - orcl
Protection Mode: MaxAvailability
Databases:
orcla - Primary database
orclb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
4
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
Data Guard Configuration
DGMGRL> show database verbose orcla
Database - orcla
Role:
Intended State:
Transport Lag:
Apply Lag:
Apply Rate:
Real Time Query:
Instance(s):
orcl
Database Status:
SUCCESS
5
| Data Guard Observer das unbekannte Wesen
PHYSICAL STANDBY
APPLY-ON
0 seconds (computed 0 seconds ago)
0 seconds (computed 0 seconds ago)
0 Byte/s
OFF
Data Guard Observer das unbekannte Wesen
Data Guard Configuration
6
Properties:
DGConnectIdentifier
ObserverConnectIdentifier
LogXptMode
DelayMins
Binding
MaxFailure
MaxConnections
ReopenSecs
NetTimeout
RedoCompression
LogShipping
PreferredApplyInstance
ApplyInstanceTimeout
ApplyParallel
StandbyFileManagement
ArchiveLagTarget
LogArchiveMaxProcesses
LogArchiveMinSucceedDest
DbFileNameConvert
LogFileNameConvert
FastStartFailoverTarget
| Data Guard Observer das unbekannte Wesen
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
'prim'
''
'SYNC'
'0'
'optional'
'0'
'1'
'300'
'30'
'DISABLE'
'ON'
''
'0'
'AUTO'
'AUTO'
'1800'
'4'
'1'
''
''
'orclb'
Data Guard Observer das unbekannte Wesen
Data Guard Configuration
InconsistentProperties
= '(monitor)'
InconsistentLogXptProps
= '(monitor)'
SendQEntries
= '(monitor)'
LogXptStatus
= '(monitor)'
RecvQEntries
= '(monitor)'
ApplyLagThreshold
= '0'
TransportLagThreshold
= '0'
TransportDisconnectedThreshold = '30'
SidName
= 'orcl'
StaticConnectIdentifier
=
'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel6-prim)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=ORCLA)(INSTANCE_NAME=orcl)
(SERVER=DEDICATED)))'
StandbyArchiveLocation
= 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation
= ''
LogArchiveTrace
= '0'
LogArchiveFormat
= '%t_%s_%r.dbf'
TopWaitEvents
= '(monitor)'
7
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
Data Guard
LSP: Logical Standby Process SQL
Apply for logical Dataguard
MRP: Managed Recover Process
DataGuard
RFS: Remote Fileserver Kriegt REDO
Daten und schreibt die in die Standby
8
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
Data Guard at Work
select process,pid,status,client_process,
thread#,sequence#,block#
from v$managed_standby;
9
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
Observer Konfiguration
ORCLA
Database
11.2.0.4
ORCLB
Database
11.2.0.4
tnsnames.ora
Observer
12.1.0.2
10
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
Observer
• Eigener Server
• DB Version >= Datenbankversion
• Laufender Data Guard Broker
• Switch Over / Failover
• Standby und Observer keine Verbindung zu Primary
• User defined Condition erfüllt
• Shutdown abort der Primary
• Kein automatischer Switch Back
• Automatisches Reinstate
• Nicht nach User Defined Conditions
• Empfehlung sys Username und Password im Wallet
11
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
tnsnames.ora
12
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
Observer Konfiguration Data Guard
DGMGRL > connect sys@prim
DGMGRL > edit database orcla set property 'LogXptMode'='SYNC';
DGMGRL > edit database orclb set property 'LogXptMode'='SYNC';
DGMGRL > edit database orcla set property
'FastStartFailoverTarget'='stby';
DGMGRL > edit database orclb set property
'FastStartFailoverTarget'='prim';
DGMGRL > edit database orcla set property 'StandbyFileManagement'='AUTO';
DGMGRL > edit database orclb set property 'StandbyFileManagement'='AUTO';
13
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
Datenbank Konfiguration Data Guard
Primary:
SQL > alter database flashback on;
SQL > alter system set undo_retention=3600 scope=spfile;
Standby:
DGMGRL > edit database orclb set state='APPLY-OFF';
SQL > alter database flashback on;
SQL > alter system set undo_retention=3600 scope=spfile;
DGMGRL > edit database orclb set state='APPLY-ON';
14
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
DOKU: Protection Mode
• Maximum Availability
• This protection mode provides the highest level of data protection
that is possible without compromising the availability of a primary
database. REDO is written sync.
• Maximum Performance
• This protection mode provides the highest level of data protection
that is possible without affecting the performance of a primary
database. Default Mode. Redo is written async
• Maximum Protection
• This protection mode ensures that no data loss will occur if the
primary database fails.
• Fast Start Failover kann nicht aktiviert werden!
15
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
Observer Mode aktivieren
DGMGRL > ENABLE FAST_START FAILOVER;
DGMGRL> show configuration;
Configuration - orcl
Protection Mode: MaxAvailability
Databases:
orcla - Primary database
Warning: ORA-16819: fast-start failover observer not started
orclb - (*) Physical standby database
Warning: ORA-16819: fast-start failover observer not started
Fast-Start Failover: ENABLED
Configuration Status:
WARNING
DGMGRL>
16
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
Observer auf dem eigenen Server Starten
DGMGRL> connect sys@prim
Password:
Connected as SYSDBA.
DGMGRL> start observer
Observer started
das war's!!
17
| Data Guard Observer das unbekannte Wesen
Obser
ver
Data Guard Observer das unbekannte Wesen
Observer Status I
DGMGRL> show configuration verbose;
Configuration - orcl
Protection Mode: MaxAvailability
Databases:
orcla - Primary database
orclb - (*) Physical standby database
(*) Fast-Start Failover target
18
| Data Guard Observer das unbekannte Wesen
orcla
Data Guard Observer das unbekannte Wesen
Observer Status II
Properties:
FastStartFailoverThreshold
OperationTimeout
FastStartFailoverLagLimit
CommunicationTimeout
ObserverReconnect
FastStartFailoverAutoReinstate
FastStartFailoverPmyShutdown
BystandersFollowRoleChange
ObserverOverride
ExternalDestination1
ExternalDestination2
PrimaryLostWriteAction
=
=
=
=
=
=
=
=
=
=
=
=
'30'
'30'
'30'
'180'
'0'
'TRUE'
'TRUE'
'ALL'
'FALSE'
''
''
'CONTINUE'
• Mehr Details dazu: http://docs.oracle.com/cd/E11882_01/server.112/e40771/dbpropref.htm#DGBKR3637
19
| Data Guard Observer das unbekannte Wesen
orcla
Data Guard Observer das unbekannte Wesen
Observer Status III
Fast-Start Failover: ENABLED
Threshold:
Target:
Observer:
Lag Limit:
Shutdown Primary:
Auto-reinstate:
Observer Reconnect:
Observer Override:
Configuration Status:
SUCCESS
DGMGRL>
20
| Data Guard Observer das unbekannte Wesen
30 seconds
orclb
ele-oel7
30 seconds (not in use)
TRUE
TRUE
(none)
FALSE
orcla
Data Guard Observer das unbekannte Wesen
User defined Conditions
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
ENABLE FAST_START FAILOVER CONDITION <condition>;
orcla
DISABLE FAST_START FAILOVER CONDITION <condition>;
ENABLE FAST_START FAILOVER CONDITION 27102;
DISABLE FAST_START FAILOVER CONDITION "Datafile Offline";
Condition
Default
Datafile Offline
enabled
Corrupted Controlfile
enabled
Corrupted Dictionary
enabled
Inaccessible Logfile
disabled
Stuck Archiver
disabled
Error Code
disabled
21
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
User defined Conditions anzeigen
DGMGRL> show fast_start failover;
orcla
Fast-Start Failover: ENABLED
Threshold:
30 seconds
Target:
orclb
Observer:
ele-oel7
Lag Limit:
30 seconds (not in use)
Shutdown Primary:
TRUE
Auto-reinstate:
TRUE
Observer Reconnect: (none)
Observer Override: FALSE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile
Corrupted Dictionary
Inaccessible Logfile
Stuck Archiver
Datafile Offline
Oracle Error Conditions:
ORA-01422: exact fetch returns
22
| Data Guard Observer das unbekannte Wesen
YES
YES
NO
NO
YES
more than requested number of rows
Data Guard Observer das unbekannte Wesen
Data Guard Set Delay
DGMGRL> edit database orclb set property DelayMins=15;
orcla
Im Logfile der Standby DB:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
………
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
MRP0: Background Media Recovery cancelled with status 16037
……
MRP0: Background Media Recovery process shutdown (orcl)
Managed Standby Recovery Canceled (orcl)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT
Attempt to start background Managed Standby Recovery process (orcl)
Mon Aug 31 12:48:22 2015
MRP0 started with pid=30, OS id=5937
MRP0: Background Managed Standby Recovery process started (orcl)
………
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 24 (in transit)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT
23
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
Kill pmon
ORCLA
Database
11.2.0.4
ORCLB
Database
11.2.0.4
tnsnames.ora
Observer
12.1.0.2
24
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
Observer Tests
Kill pmon auf primary!
Observer Log:
14:10:29.09 Sunday, August 02, 2015
Initiating Fast-Start Failover to database "orclb"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "orclb"
14:10:31.81 Sunday, August 02, 2015
25
| Data Guard Observer das unbekannte Wesen
Oberv
er
Data Guard Observer das unbekannte Wesen
Observer Tests
Starten der DB auf ORCLA:
orcla
SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 1 19:37:49 2015
Copyright (c) 1982, 2013, Oracle.
All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1787138048 bytes
Fixed Size
2254104 bytes
Variable Size
486542056 bytes
Database Buffers
1291845632 bytes
Redo Buffers
6496256 bytes
Database mounted.
ORA-16649: possible failover to another database
prevents this database from being opened
26
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
Observer Tests
Observer Log-file:
Oberv
er
14:12:17.45 Sunday, August 02, 2015
Initiating reinstatement for database "orcla"...
Reinstating database "orcla", please wait...
Operation requires shut down of instance "orcl" on database "orcla"
Shutting down instance "orcl"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires start up of instance "orcl" on database "orcla"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "orcla" ...
Reinstatement of database "orcla" succeeded
14:13:12.22 Sunday, August 02, 2015
27
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
Observer Tests
DGMGRL > show configuration;
Configuration - orcl
Protection Mode: MaxAvailability
Databases:
orclb - Primary database
orcla - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS
28
| Data Guard Observer das unbekannte Wesen
orclb
Data Guard Observer das unbekannte Wesen
Observer Tests
SQL > shutdown abort;
Observer Output:
13:58:35.65 Sunday, August 30, 2015
Initiating Fast-Start Failover to database "orcla"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "orcla"
13:58:38.15 Sunday, August 30, 2015
startup mount;
29
| Data Guard Observer das unbekannte Wesen
orclb
Data Guard Observer das unbekannte Wesen
Observer Tests
Observer Output:
Oberv
er
14:01:11.00 Sunday, August 30, 2015
Initiating reinstatement for database "orclb"...
Reinstating database "orclb", please wait...
Operation requires shut down of instance "orcl" on database "orclb"
Shutting down instance "orcl"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires start up of instance "orcl" on database "orclb"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "orclb" ...
Reinstatement of database "orclb" succeeded
14:02:03.63 Sunday, August 30, 2015
30
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
Observer Tests
DGMGRL> show configuration;
Configuration - orcl
Protection Mode: MaxAvailability
Databases:
orcla - Primary database
orclb - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS
DGMGRL>
31
| Data Guard Observer das unbekannte Wesen
orclb
Data Guard Observer das unbekannte Wesen
Observer Tests User Defined Condition
orcla
DGMGRL> ENABLE FAST_START FAILOVER CONDITION 1422;
SQL> create table tab1 (col1 number);
SQL> insert into tab1 values(1);
SQL> insert into tab1 values(1);
SQL> insert into tab1 values(1);
SQL> insert into tab1 values(1);
SQL> Commit;
SQL> DECLARE
SQL>
v_col1 number;
SQL> BEGIN
SQL> select col1 into v_col1 from tab1;
SQL> END
SQL> /
32
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
Observer Tests User Defined Condition
orcla
alert_prim.log:
Fri Sep 18 14:08:24 2015
Fore: user-configured ORA-1422 requesting FSFO
Fri Sep 18 14:08:35 2015
A user-configurable Fast-Start Failover condition was detected. The
primary is shutting down due to ORA-01422: exact fetch returns more than
requested number of rows.
Database ORCLA will not be automatically reinstated.
33
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
Failover mit DBMS_DG
Beispielprogramm dazu:
orcla
set serveroutput on
declare
status integer;
begin
status := dbms_dg.initiate_fs_failover('Failover Requested');
dbms_output.put_line('Fast-Start Failover is disabled: Expected status = ORA-16646');
dbms_output.put_line('
Actual Status = ORA-' || status);
end;
/
exit;
34
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
V$Views
• v$managed_standby
Status Information der Physical Standby Datenbank
• v$fs_failover_stats
Informationen über Fast Start Failover (FSFO)
• v$fs_observer_histogram
Ping Statistiken, diese Tabelle kann für das Setzen von FastStartFailoverThresh
genutzt werden
35
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
Links
• Data Guard Broker
• docs.oracle.com/cd/E11882_01/server.112/e40771/toc.htm
• Properties
• http://docs.oracle.com/cd/E11882_01/server.112/e40771/dbpropref.htm#DGBKR3637
• Data Guard Concepts and Administration
• docs.oracle.com/cd/E11882_01/server.112/e41134/toc.htm
• DBMS_DG Package
• https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_dg.htm#ARPLS920
• dgmgrl Syntax
• http://docs.oracle.com/cd/B28359_01/server.111/b28295/dgmgrl.htm
36
| Data Guard Observer das unbekannte Wesen
37
www.mt-ag.com
https://eleoracle.wordpress.com
@eletwit
Vielen Dank.
38
Ernst Leber
Telefon:
+49 2102 30961-0
Telefax: +49 2102 30961-101
E-Mail: [email protected]
|
www.mt-ag.com

Oracle 2014 6