Data Guard Observer das unbekannte WesenAutor: Ernst Leber19.11.2015 DOAG Nürnberg
Facts & Figures
Technologie-orientiertBranchen-unabhängig
HauptsitzRatingen
240 Beschäftigte
Gründung1994
NiederlassungFrankfurt am Main
Ausbildungs-betrieb
Inhabergeführt
Zertifizierter Partner von
Oracle, Microsoftund SAP
24 Mio. Euro
Umsatz
2
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenAgenda
3
Data Guard • Übersicht • Konfiguration
Observer • Konfiguration • User Defined Conditions • V$Views • Live Demo
Links
Mit diesem Text werden Terminal Outputs dargestellt
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenData Guard Konfiguration
4
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
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenData Guard Configuration
5
DGMGRL> show database verbose oracle
Database - orclb
Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Apply Rate: 0 Byte/s Real Time Query: OFF Instance(s): orcl
Database Status: SUCCESS
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenData Guard Configuration
6
Properties: DGConnectIdentifier = 'stby' ObserverConnectIdentifier = '' LogXptMode = 'SYNC' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '1800' LogArchiveMaxProcesses = '4' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '' LogFileNameConvert = '' FastStartFailoverTarget = 'orcla'
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenData Guard Configuration
7
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)'
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenData Guard
8
LSP: Logical Standby Process SQL Apply for logical DataguardMRP: Managed Recover Process DataGuardRFS: Remote Fileserver Kriegt REDO Daten und schreibt die in die Standby
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenData Guard at Work
9
select process,pid,status,client_process, thread#,sequence#,block# from v$managed_standby;
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenObserver Konfiguration
10
ORCLA Database 11.2.0.4
ORCLB Database 11.2.0.4
Observer 12.1.0.2
tnsnames.ora
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenObserver
11
• 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
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesentnsnames.ora
12
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenObserver Konfiguration Data Guard
13
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';
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenDatenbank Konfiguration Data Guard
14
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';
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenObserver Mode aktivieren
15
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>
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenObserver auf dem eigenen Server Starten
16
DGMGRL> connect sys@prim Password: Connected as SYSDBA.
DGMGRL> start observer Observer started
das war's!!
Observer
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenObserver Status I
17
DGMGRL> show configuration verbose;
Configuration - orcl
Protection Mode: MaxAvailability Databases: orcla - Primary database orclb - (*) Physical standby database
(*) Fast-Start Failover target
orcla
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenObserver Status II
18
Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' ObserverReconnect = '0' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' ObserverOverride = 'FALSE' ExternalDestination1 = '' ExternalDestination2 = '' PrimaryLostWriteAction = 'CONTINUE'
• Mehr Details dazu: http://docs.oracle.com/cd/E11882_01/server.112/e40771/dbpropref.htm#DGBKR3637
orcla
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenObserver Status III
19
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
Configuration Status: SUCCESS
DGMGRL>
orcla
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenUser defined Conditions
20
DGMGRL> ENABLE FAST_START FAILOVER CONDITION <condition>; DGMGRL> DISABLE FAST_START FAILOVER CONDITION <condition>; DGMGRL> ENABLE FAST_START FAILOVER CONDITION 27102; DGMGRL> 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
orcla
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenUser defined Conditions anzeigen
21
DGMGRL> show fast_start failover;
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 YES Corrupted Dictionary YES Inaccessible Logfile NO Stuck Archiver NO Datafile Offline YES Oracle Error Conditions: ORA-01422: exact fetch returns more than requested number of rows
orcla
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenData Guard Set Delay
22
DGMGRL> edit database orclb set property DelayMins=15;
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
orcla
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenDOKU: Protection Mode
23
• 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!
|
ORCLA Database 11.2.0.4
ORCLB Database 11.2.0.4
Observer 12.1.0.2
tnsnames.ora
Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenKill pmon
24
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenObserver Tests
25
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
Oberver
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenObserver Tests
26
Starten der DB auf 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
orcla
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenObserver Tests
27
Observer Log-file:
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
Oberver
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenObserver Tests
28
DGMGRL > show configuration;
Configuration - orcl
Protection Mode: MaxAvailability Databases: orclb - Primary database orcla - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status: SUCCESS
orclb
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenObserver Tests
29
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;
orclb
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenObserver Tests
30
Observer Output:
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
Oberver
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenObserver Tests
31
DGMGRL> show configuration;
Configuration - orcl
Protection Mode: MaxAvailability Databases: orcla - Primary database orclb - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status: SUCCESS
DGMGRL>
orclb
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenObserver Tests User Defined Condition
32
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> /
orcla
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenObserver Tests User Defined Condition
33
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.
orcla
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenFailover mit DBMS_DG
34
Beispielprogramm dazu:
set serveroutput on
declarestatus integer;beginstatus := 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;
orcla
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenV$Views
35
• v$managed_standbyStatus Information der Physical Standby Datenbank
• v$fs_failover_statsInformationen über Fast Start Failover (FSFO)
• v$fs_observer_histogramPing Statistiken, diese Tabelle kann für das Setzen von FastStartFailoverThreshold genutzt werden
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte WesenLinks
36
• 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
| Data Guard Observer das unbekannte Wesen
Data Guard Observer das unbekannte Wesen
37
|
Telefon: Telefax:
E-Mail: www.mt-ag.com
Vielen Dank.
+49 2102 30961-0+49 2102 30961-101
Ernst Leber38
www.mt-ag.com https://eleoracle.wordpress.com @eletwit