Extract Data Guard Commands


Hi,
I found a great code for extracting data guard commands (reverse engineering)
I adjusted it to oracle 12.1 with far sync and it working fine.

Just remember to run on Primary & Far Sync instances the following command:
1. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_n='' scope=both sid='*';
(where n is 2 or above)
2. DGMGRL> disable configuration;
3. DGMGRL> remove configuration;

and then run the script:
displayconfig.sql
SET SERVEROUTPUT ON
SET LINESIZE 300
SET FEEDBACK OFF

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;
db_commands_RedoRoutes clob;
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 );

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
CASE l.role
WHEN 'PRIMARY'
THEN
outdoc := 'create configuration dr as primary database is ''' || l.name || ''' connect identifier is ' || l.connectdb || ';';
db_type := 'database';
--DBMS_OUTPUT.put_line ( outdoc );
db_headers_commands := db_headers_commands ||chr(10) || outdoc;
WHEN 'PHYSICAL'
THEN
outdoc := 'add database ''' || l.name || ''' as connect identifier is ' || l.connectdb || ';';
db_type := 'database';
----DBMS_OUTPUT.put_line ( outdoc );
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.connectdb || ';';
db_type := 'far_sync';
--DBMS_OUTPUT.put_line ( outdoc );
db_headers_commands := db_headers_commands ||chr(10) || outdoc;
END CASE;

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 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'
, 'ApplyLagThreshold'
, 'ApplyParallel'
, 'ArchiveLagTarget'
, 'Binding'
, 'DbFileNameConvert'
, 'DelayMins'
, 'DGConnectIdentifier'
, 'FastStartFailoverTarget'
, 'InstanceName'
, 'LogArchiveFormat'
, 'LogArchiveMaxProcesses'
, 'LogArchiveMinSucceedDest'
, 'LogArchiveTrace'
, 'LogFileNameConvert'
, 'LogShipping'
, 'LogXptMode'
, 'LogXptMode'
, 'MaxConnections'
, 'MaxFailure'
, 'NetTimeout'
, 'ObserverConnectIdentifier'
, 'PreferredApplyInstance'
, 'RedoCompression'
, 'RedoRoutes'
, 'ReopenSecs'
, 'StandbyArchiveLocation'
, 'StandbyFileManagement'
, 'StaticConnectIdentifier'
, 'TransportDisconnectedThreshold'
, 'TransportLagThreshold'
, 'ApplyInstanceTimeout' ) )
LOOP
IF db_type='far_sync' and q.name in ('DelayMins','ApplyInstanceTimeout','ApplyLagThreshold','ApplyParallel','StandbyFileManagement','ArchiveLagTarget','DbFileNameConvert','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;
DBMS_OUTPUT.put_line(db_headers_commands);
DBMS_OUTPUT.put_line(db_commands);
DBMS_OUTPUT.put_line('enable configuration;');
DBMS_OUTPUT.put_line(db_commands_RedoRoutes);
DBMS_OUTPUT.put_line('show configuration;');
END;
/
My original configuration
DGMGRL> show configuration

Configuration - dr

Protection Mode: MaxPerformance
Members:
pdb1 - Primary database
fdb1 - Far sync instance
sdb1 - Physical standby database
The script output:
create configuration dr as primary database is 'pdb1' connect identifier is pdb1;
add database 'sdb1' as connect identifier is sdb1;
add far_sync 'fdb1' as connect identifier is fdb1;

edit database pdb1 set property DGConnectIdentifier = 'pdb1';
edit database pdb1 set property LogXptMode = 'SYNC';
edit database pdb1 set property DelayMins = '0';
edit database pdb1 set property Binding = 'optional';
edit database pdb1 set property MaxFailure = '1';
edit database pdb1 set property MaxConnections = '1';
edit database pdb1 set property ReopenSecs = '15';
edit database pdb1 set property NetTimeout = '30';
edit database pdb1 set property RedoCompression = 'DISABLE';
edit database pdb1 set property LogShipping = 'ON';
edit database pdb1 set property ApplyInstanceTimeout = '0';
edit database pdb1 set property ApplyLagThreshold = '0';
edit database pdb1 set property TransportLagThreshold = '0';
edit database pdb1 set property TransportDisconnectedThreshold = '0';
edit database pdb1 set property ApplyParallel = 'AUTO';
edit database pdb1 set property StandbyFileManagement = 'AUTO';
edit database pdb1 set property ArchiveLagTarget = '0';
edit database pdb1 set property LogArchiveMaxProcesses = '4';
edit database pdb1 set property LogArchiveMinSucceedDest = '1';
edit database pdb1 set property DbFileNameConvert = 'pdb1, sdb1';
edit database pdb1 set property LogFileNameConvert = 'pdb1, sdb1';
edit database pdb1 set property FastStartFailoverTarget = 'sdb1';
edit database pdb1 set property InstanceName = 'pdb1';
edit database pdb1 set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=e20lora10)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1_DGMGRL)(INSTANCE_NAME=pdb1)(SERVER=DEDICATED)))';
edit database pdb1 set property StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST';
edit database pdb1 set property LogArchiveTrace = '0';
edit database pdb1 set property LogArchiveFormat = '%t_%s_%r.arc';
edit database sdb1 set property DGConnectIdentifier = 'sdb1';
edit database sdb1 set property LogXptMode = 'SYNC';
edit database sdb1 set property DelayMins = '0';
edit database sdb1 set property Binding = 'optional';
edit database sdb1 set property MaxFailure = '1';
edit database sdb1 set property MaxConnections = '1';
edit database sdb1 set property ReopenSecs = '15';
edit database sdb1 set property NetTimeout = '30';
edit database sdb1 set property RedoCompression = 'DISABLE';
edit database sdb1 set property LogShipping = 'ON';
edit database sdb1 set property ApplyInstanceTimeout = '0';
edit database sdb1 set property ApplyLagThreshold = '0';
edit database sdb1 set property TransportLagThreshold = '0';
edit database sdb1 set property TransportDisconnectedThreshold = '0';
edit database sdb1 set property ApplyParallel = 'AUTO';
edit database sdb1 set property StandbyFileManagement = 'AUTO';
edit database sdb1 set property ArchiveLagTarget = '0';
edit database sdb1 set property LogArchiveMaxProcesses = '4';
edit database sdb1 set property LogArchiveMinSucceedDest = '1';
edit database sdb1 set property DbFileNameConvert = 'pdb1, sdb1';
edit database sdb1 set property LogFileNameConvert = 'PDB1, sdb1, pdb1, sdb1';
edit database sdb1 set property FastStartFailoverTarget = 'pdb1';
edit database sdb1 set property InstanceName = 'sdb1';
edit database sdb1 set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=e20rora10)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sdb1_DGMGRL)(INSTANCE_NAME=sdb1)(SERVER=DEDICATED)))';
edit database sdb1 set property StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST';
edit database sdb1 set property LogArchiveTrace = '0';
edit database sdb1 set property LogArchiveFormat = '%t_%s_%r.arc';
edit far_sync fdb1 set property DGConnectIdentifier = 'fdb1';
edit far_sync fdb1 set property LogXptMode = 'ASYNC';
edit far_sync fdb1 set property Binding = 'optional';
edit far_sync fdb1 set property MaxFailure = '1';
edit far_sync fdb1 set property MaxConnections = '1';
edit far_sync fdb1 set property ReopenSecs = '15';
edit far_sync fdb1 set property NetTimeout = '30';
edit far_sync fdb1 set property RedoCompression = 'DISABLE';
edit far_sync fdb1 set property LogShipping = 'ON';
edit far_sync fdb1 set property TransportLagThreshold = '0';
edit far_sync fdb1 set property TransportDisconnectedThreshold = '0';
edit far_sync fdb1 set property LogArchiveMaxProcesses = '4';
edit far_sync fdb1 set property LogArchiveMinSucceedDest = '1';
edit far_sync fdb1 set property LogFileNameConvert = 'PDB1, fdb1, pdb1, fdb1';
edit far_sync fdb1 set property InstanceName = 'fdb1';
edit far_sync fdb1 set property StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST';
edit far_sync fdb1 set property LogArchiveTrace = '0';
edit far_sync fdb1 set property LogArchiveFormat = '%t_%s_%r.arc';
enable configuration;
edit far_sync fdb1 set property RedoRoutes = '(pdb1 : sdb1 ASYNC)(sdb1 : pdb1 ASYNC)';
edit database sdb1 set property RedoRoutes = '(LOCAL : fdb1 SYNC ALT =( pdb1 SYNC FALLBACK))';
edit database pdb1 set property RedoRoutes = '(LOCAL : fdb1 SYNC ALT =( sdb1 SYNC FALLBACK))';

show configuration;

Enjoy

Comments

Popular posts from this blog

Fixing & Registering ORACLE_HOMES in Central Inventory

Export in Pl/Sql via DBMS_DATAPUMP

Oracle 18c - New Features for Active Data Guard