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; <do_monitor version="11.2"><verify level="minor" object_id="4096"> </verify> </do_monitor>
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.
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
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 testdgDisclaimer:
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;
/
