Extract Data Guard Commands on Oracle 12.2

 

Hi, These days I’m starting to work on Oracle 12.2, leaving behind the old versions. Since my main domain is Oracle Data Guard, I posted a blog about Oracle 12c Release 2 New Features for Active Data Guard.
One of the big new features I missed is that the internal Data Guard Broker configuration was changed dramatically in 12.2. Last week, I tried to Extract Data Guard Commands and realized it is not working as before, 🙁
so I sat down and adjusted it to work on 12.2. This time, I enhanced the output with some more important information.

Run the following script as sysdba:
displayconfig122.sql:

PROMPT disable FAST_START FAILOVER;
PROMPT disable configuration;
PROMPT remove configuration;
DECLARE
rid INTEGER;
indoc VARCHAR2 (4000);
outdoc VARCHAR2 (4000);
p INTEGER;
z XMLTYPE;
y CLOB;
v_xml XMLTYPE;
tout VARCHAR2 (4000);
db_type VARCHAR2 (10);
db_headers_commands CLOB;
db_commands CLOB;
general_commands CLOB;
db_commands_RedoRoutes CLOB;
v_last_role VARCHAR2 (50) := NULL;
v_new_role VARCHAR2 (50);
v_protect_mode_no NUMBER;
protect_mode VARCHAR2 (50);
v_fast_start_failover VARCHAR2 (50);
v_enabled VARCHAR2 (50);
BEGIN
indoc := '';
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);
SELECT XMLQUERY ('/DRC' PASSING z RETURNING CONTENT) INTO v_xml FROM DUAL;
FOR l
IN (WITH drc_data
AS (SELECT xt.*
FROM XMLTABLE ('/DRC'
PASSING v_xml
COLUMNS conf PATH '/DRC/@name'
, protect_mode PATH '/DRC/@protect_mode'
, fast_start_failover PATH '/DRC/@fast_start_failover'
, enabled PATH '/DRC/@enabled'
, valuess XMLTYPE PATH '/DRC/SITE') xt)
, site_data
AS (SELECT conf
, protect_mode
, fast_start_failover
, enabled
, xt2.*
FROM drc_data dd
LEFT OUTER JOIN XMLTABLE ('/SITE' PASSING dd.valuess COLUMNS name PATH '/SITE/@name', id PATH '/SITE/@site_id') xt2
ON 1 = 1)
SELECT *
FROM site_data)
LOOP
v_protect_mode_no := l.protect_mode;
v_fast_start_failover := l.fast_start_failover;
v_enabled := l.enabled;
indoc := '';
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' PASSING z RETURNING CONTENT) INTO v_xml FROM DUAL;
FOR q
IN (WITH resource_data
AS (SELECT xt.*
FROM XMLTABLE ('/RESOURCE'
PASSING v_xml
COLUMNS res_id PATH '/RESOURCE/@res_id', valuess XMLTYPE PATH '/RESOURCE/PROPERTY_LIST/VALUE') xt)
, values_data
AS (SELECT res_id, xt2.*
FROM resource_data dd
LEFT OUTER JOIN
XMLTABLE ('/VALUE'
PASSING dd.valuess
COLUMNS name PATH '/VALUE/@name', VALUE PATH '/VALUE/@value', property_type PATH '/VALUE/@property_type') xt2
ON 1 = 1)
SELECT *
FROM values_data
WHERE VALUE IS NOT NULL
AND name NOT IN ('ENABLED'
, 'STATUS'
, 'ERROR_NUM'
, 'ERROR_TEXT'
, 'ClusterDatabase'
, 'DbChangeCritical'
, 'DbIsCritical'
, 'DbDisplayName'
, 'GUIProperty3'
, 'IncarnationTable'
, 'SRLStatus'
, 'SidName'
, 'HostName'
, 'SRLStatus'
, 'DataGuardSyncLatency'
, 'InstanceName'))
LOOP
v_last_role := v_new_role;
SELECT UTL_RAW.cast_to_varchar2 (HEXTORAW (value_raw))
INTO v_new_role
FROM x$drc
WHERE attribute = 'role' AND object_id = q.res_id;
IF v_last_role <> v_new_role OR v_last_role IS NULL
THEN
CASE v_new_role
WHEN 'PRIMARY'
THEN
outdoc :=
'create configuration ' || l.conf || ' as primary database is ''' || l.name || ''' connect identifier is ''' || l.name || ''';';
db_type := 'database';
db_headers_commands := db_headers_commands || CHR (10) || outdoc;
WHEN 'PHYSICAL'
THEN
outdoc := 'add database ''' || l.name || ''' as connect identifier is ''' || l.name || ''';';
db_type := 'database';
db_headers_commands := db_headers_commands || CHR (10) || outdoc;
WHEN 'FAR_SYNC_INSTANCE'
THEN
outdoc := 'add far_sync ''' || l.name || ''' as connect identifier is ''' || l.name || ''';';
db_type := 'far_sync';
db_headers_commands := db_headers_commands || CHR (10) || outdoc;
END CASE;
END IF;
IF db_type = 'far_sync'
AND q.name IN ('DelayMins'
, 'ApplyInstanceTimeout'
, 'ApplyLagThreshold'
, 'ApplyParallel'
, 'StandbyFileManagement'
, 'ArchiveLagTarget'
, 'DbFileNameConvert'
, 'LsbyMaxSga'
, 'LsbyMaxServers'
, 'ApplyInstances'
, 'LsbyMaxEventsRecorded'
, 'StaticConnectIdentifier')
THEN
NULL;
ELSE
IF q.name = 'RedoRoutes'
THEN
db_commands_RedoRoutes :=
'edit '
|| db_type
|| ' '
|| l.name
|| ' set property '
|| q.name
|| ' = '''
|| q.VALUE
|| ''';'
|| CHR (10)
|| db_commands_RedoRoutes;
ELSE
--DBMS_OUTPUT.put_line ( 'edit ' || db_type || ' ' || l.name || ' set property ' || q.name || ' = ''' || q.VALUE || ''';' );
db_commands :=
db_commands || CHR (10) || 'edit ' || db_type || ' ' || l.name || ' set property ' || q.name || ' = ''' || q.VALUE || ''';';
END IF;
END IF;
END LOOP;
dbms_drs.delete_request (rid);
END LOOP;
indoc := '';
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 C IN (SELECT name, VALUE
FROM XMLTABLE ('/VALUE' PASSING v_xml COLUMNS NAME PATH '/VALUE/@name', VALUE PATH '/VALUE/@value')
WHERE VALUE IS NOT NULL
AND NAME NOT IN ('ENABLED'
, 'STATUS'
, 'ERROR_NUM'
, 'ERROR_TEXT'
, 'MIV'
, 'PRIMARY_SITE_ID'
, 'HEALTH_CHECK_INTERVAL'
, 'HEALTH_CHECK_ENABLED'
, 'DRC_UNIQUE_ID'
, 'DRC_UNIQUE_ID_SEQUENCE'
, 'EXT_COND'
, 'OVERALL_PROTECTION_MODE'
, 'ObserverHB'
, 'FSFO_MIV'
, 'MANAGED_STANDBY_MASK'
, 'RoleChangeHistory'
, 'FastStartFailoverConditions'
, 'FastStartFailoverTgtSwitchInt'
, 'FastStartFailoverOBID1'
, 'FastStartFailoverOBID2'
, 'FastStartFailoverOBID3'
, 'ObserverVersion1'
, 'ObserverVersion2'
, 'ObserverVersion3'
, 'FastStartFailoverMode'
, 'Configuration_Name'
, 'ObserverName1'
, 'ObserverName2'
, 'ObserverName3'))
LOOP
general_commands := general_commands || CHR (10) || 'edit configuration set property ' || c.NAME || ' = ''' || c.VALUE || ''';';
END LOOP;
dbms_drs.delete_request (rid);
DBMS_OUTPUT.put_line (db_headers_commands);
DBMS_OUTPUT.put_line (db_commands);
DBMS_OUTPUT.put_line ('EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;');
IF v_enabled = 'yes'
THEN
DBMS_OUTPUT.put_line ('enable configuration;');
END IF;
DBMS_OUTPUT.put_line (db_commands_RedoRoutes);
CASE v_protect_mode_no
WHEN 3
THEN
protect_mode := 'MAXPERFORMANCE';
WHEN 2
THEN
protect_mode := 'MAXAVAILABILITY';
WHEN 1
THEN
protect_mode := 'MAXPROTECTION';
END CASE;
IF v_protect_mode_no <> 3
THEN
DBMS_OUTPUT.put_line ('EDIT CONFIGURATION SET PROTECTION MODE AS ' || protect_mode || ';');
END IF;
IF v_fast_start_failover = 'ENABLED'
THEN
DBMS_OUTPUT.put_line ('ENABLE FAST_START FAILOVER;');
END IF;
DBMS_OUTPUT.put_line ('show configuration;');
DBMS_OUTPUT.put_line (general_commands);
END;
/

 

Enjoy.

Yossi Nixon
Chief Database Architect
Twitter: @YossiNixon
LinkedIn: https://www.linkedin.com/in/ynixon/