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

Post a Comment

Popular posts from this blog

Silent Installation

Some times there are problems using installation with GUI, the reason can be: 1. Firewall 2. Unix security 3. Client software (missing or not working( and maybe some more reasons If your environment is cooperative helping you solve this - it is simple - go for it. But if you are on your own and can't reach "the right guys" - do it yourself " Silently " Look for the proper response file fits for your needs in /database/response/ Edit it and run the following command: $ ./runInstaller -force -invPtrLoc /oraInst.loc -silent -noconfig -ignoreSysPrereqs -responseFile /database/response/enterprise.rsp You can also run the installer without a response file as mentioned in Metalink Note 782918.1 $ ./runInstaller -silent -force -debug \ FROM_LOCATION="/mount/dvd/database/stage/products.xml" \ ORACLE_HOME="/u01/app/oracle/product/11.1.0/db_1" \ ORACLE_HOME_NAME="Ora11gDb1" ORACLE_BASE="/u01/app/oracle" \ TOPLEVEL_COMPONENT='{...
Read more

Data Guard - Changing IP Addresses

Image
When changing IP of a host we should update/recheck the following places: /etc/hosts or DNS listener.ora tnsnames.ora Database parameters (local_listener, remote_listener) Data Guard configuration This document is also relevant when changing the IP address of the connection between the hosts, other than the original IP addresses we used during the installation. When installation is done using the hostname and not the IP address, most of the changes are not relevant except for /etc/hosts. In this document, I will describe how to change the Data Guard Broker configuration. Dataguard configuration Show the environment DGMGRL> show configuration Configuration - dr   Protection Mode: MaxAvailability   Members:   pdb7 - Primary database     fdb7 - Far sync instance       sdb7 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS   (status upd...
Read more

Fixing & Registering ORACLE_HOMES in Central Inventory

Central Inventory location is determined by oraInst.loc located in: HP/Solaris /var/opt/oracle/oraInst.loc Linux /etc/oraInst.loc Windows \\HKEY_LOCAL_MACHINE\Software\Oracle\inst_loc To check if all oracle homes are registered within the Central Inventory run the following command: ${ORACLE_HOME}/oui/bin/opatch lsinventory -all Invoking OPatch 10.2.0.3.0 Oracle interim Patch Installer version 10.2.0.3.0 Copyright (c) 2005, Oracle Corporation. All rights reserved.. Oracle Home : /software/oracle/OEM10gR2/agent10g Central Inventory : /software/oracle/oraInventory from : /var/opt/oracle/oraInst.loc OPatch version : 10.2.0.3.0 OUI version : 10.2.0.3.0 OUI location : /software/oracle/OEM10gR2/agent10g/oui Log file location : /software/oracle/OEM10gR2/agent10g/cfgtoollogs/opatch/opatch....log Lsinventory Output file location : /software/oracle/OEM10gR2/agent10g/cfgtoollogs/....lsinventory.....txt ----------------------------------... List of Oracle Homes: ...
Read more