DataGuard Broker configuration backup
Thanks to Oracle Performance and Backup Blog DB for this story
Jan 19, 2012 10:17am
Some time ago I have seen question on oracle-l list about DataGuard Broker configuration backup. There was no clear answer and there were some suggestions to use "dgmgrl" and perl script to display and parse output. This topic was very interesting for me and I decided to dig deeper and find out how Oracle is keeping these information.
First step was to figure out how DataGuard Broker is gathering that data. You can enable debug option for dgmgrl tool and check how it works
piorovm$ dgmgrl -xml -debug
DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
[W000 01/19 11:44:50.82] Connecting to database using .
[W000 01/19 11:44:50.84] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 01/19 11:44:50.84] Broker version is '11.2.0.2.0'
Connected.
DGMGRL>
As you can see there is a dbms_drs package which is used for that.I have checked what I could find in package definition file ( dbmsdrs.sql ) and it was it. There are two examples for blocking and non-blocking queries. Last thing to figure out was to find a XML based command I can use with dbms_drs package to get configuration.
DGMGRL> show configuration verbose;
And then step after step I crated script showed below - displayconfig.sql
In 11.2 Oracle add new internal table X$DRC and it can be used to quick check of DataGuard Configuration. Object_id column can be decoded using following rules:
- bit 12 - object_id = 4096 - configuration
- bit 16 - object_id = 65536 - database (probably first database in site)
- bit 24 - object_id = 16777216 - first site
- bit 25 - object_id = 33554432 - second site
First database in first site will have following object_id = 16777216 + 65536 = 16842752.
SQL> set pagesize 1000
SQL> column value format a100
SQL> set linesize 200
SQL> select attribute, object_id, utl_raw.cast_to_varchar2(hextoraw(value_raw)) value FROM x$drc;
ATTRIBUTE OBJECT_ID VALUE
------------------------------ ---------- ----------------------
DRC 4096 fsf
intended_state 4096 ONLINE
protection_mode 4096 MaxAvailability
enabled 4096 YES
fast_start_failover 4096 ENABLED
observer_info 4096 client.local
fsfo_target 4096 testdg_b
SITE 16777216 testdg_a
DATABASE 16842752 testdg_a
intended_state 16842752 READ-WRITE-XPTON
connect_string 16842752 testdg_a.local
RAC 16842752 NO
enabled 16842752 YES
role 16842752 PRIMARY
INSTANCE 16842753 testdg
SITE 33554432 testdg_b
DATABASE 33619968 testdg_b
intended_state 33619968 PHYSICAL-APPLY-ON
connect_string 33619968 testdg_b.local
RAC 33619968 NO
enabled 33619968 YES
role 33619968 PHYSICAL
INSTANCE 33619969 testdg
Disclaimer:
I have tested that script with 11.1 and 11.2 on Linux with simple primary / standby configuration and with RAC primary. Please check this script first on your test environment before you will run it on production configuration.
regards,
Marcin
set serveroutput on
set linesize 200
declare
rid integer;
indoc varchar2(4000);
outdoc varchar2(4000);
p integer;
z XMLType;
y clob;
v_xml xmltype;
tout varchar2(4000);
begin
indoc:='<DO_CONFIGURE_DRC><ENUM_DRC/></DO_CONFIGURE_DRC>';
y := null;
rid :=dbms_drs.do_control(indoc);
outdoc :=NULL;
p:=1;
while (outdoc is null)
loop
outdoc:=dbms_drs.get_response(rid,p);
y := y || to_clob(outdoc);
end loop;
begin
while (outdoc is not NULL)
loop
p:=p+1;
outdoc:=dbms_drs.get_response(rid,p);
y := y || to_clob(outdoc);
end loop;
exception
when no_data_found then
NULL;
end;
z := XMLType(y);
dbms_drs.delete_request(rid);
for l in (select name , role, id, connectdb from XMLTABLE('/DRC/SITE/DATABASE' passing z COLUMNS name PATH '/DATABASE/@name', role PATH '/DATABASE/@role', id PATH '/DATABASE/@id', connectdb PATH '/DATABASE/@connect') )
loop
if (l.role = 'PRIMARY') then
outdoc := 'create configuration DGCONF as primary database is ''' || l.name || ''' connect identifier is ' || l.connectdb || ';';
dbms_output.put_line(outdoc);
else
outdoc := 'add database ''' || l.name || ''' as connect identifier is ' || l.connectdb || ';';
dbms_output.put_line(outdoc);
end if;
indoc:='<DO_CONFIGURE_SITE version="11.2"><GET_RESOURCE res_id="' || l.id || '"/></DO_CONFIGURE_SITE>';
y := null;
rid :=dbms_drs.do_control(indoc);
outdoc :=NULL;
p:=1;
while (outdoc is null)
loop
outdoc:=dbms_drs.get_response(rid,p);
y := y || to_clob(outdoc);
end loop;
begin
while (outdoc is not NULL)
loop
p:=p+1;
outdoc:=dbms_drs.get_response(rid,p);
y := y || to_clob(outdoc);
end loop;
exception
when no_data_found then
NULL;
end;
z := XMLType(y);
select XMLQuery('/RESOURCE/PROPERTY_LIST/VALUE' passing z returning content) into v_xml from dual;
for q in (select name , value , property_type as r from
XMLTABLE('/VALUE' passing v_xml COLUMNS name PATH '/VALUE/@name', value PATH '/VALUE/@value', property_type PATH '/VALUE/@property_type' )
where value is not null and name in
('AlternateLocation','ApplyInstanceTimeout','ApplyParallel','ArchiveLagTarget','Binding','BystandersFollowRoleChange','CommunicationTimeout',
'DbFileNameConvert','DelayMins','DGConnectIdentifier','FastStartFailoverAutoReinstate','FastStartFailoverLagLimit','FastStartFailoverPmyShutdown',
'FastStartFailoverTarget','FastStartFailoverThreshold','LogArchiveFormat','LogArchiveMaxProcesses','LogArchiveMinSucceedDest','LogArchiveTrace',
'LogFileNameConvert','LogShipping','LogXptMode','LsbyASkipCfgPr','LsbyASkipErrorCfgPr','LsbyASkipTxnCfgPr','LsbyDSkipCfgPr','LsbyDSkipErrorCfgPr','LsbyDSkipTxnCfgPr',
'LsbyMaxEventsRecorded','LsbyMaxSga','LsbyMaxServers','LsbyPreserveCommitOrder','LsbyRecordAppliedDdl','LsbyRecordSkipDdl','LsbyRecordSkipErrors','MaxConnections',
'MaxFailure','NetTimeout','ObserverConnectIdentifier','PreferredApplyInstance','RedoCompression','ReopenSecs','StandbyArchiveLocation','StandbyFileManagement'
))
loop
dbms_output.put_line('edit database ' || l.name || ' set property ''' || q.name || ''' = ''' || q.value || ''';' );
end loop;
dbms_drs.delete_request(rid);
end loop;
end;
/
