June | 2020 | DBA survival BLOG

Everybody knows that you can get the Data Guard configuration in dgmgrl with the command:

show configuration;

1	show configuration;

but few know that this information is actually available in x$drc:

SQL> desc x$drc
 Name            Null?    Type
 --------------- -------- ---------------
 ADDR                     RAW(8)
 INDX                     NUMBER
 INST_ID                  NUMBER
 CON_ID                   NUMBER
 OBJECT_ID                NUMBER
 ATTRIBUTE                VARCHAR2(30)
 VALUE                    VARCHAR2(4000)
 PARENT_ID                VARCHAR2(15)
 STATUS                   VARCHAR2(30)
 MESSAGE                  VARCHAR2(256)
 ERRNUM                   NUMBER
 VALUE_RAW                RAW(4095)
 ERRTIME                  NUMBER

SQL> desc x$drc

Name Null? Type

--------------- -------- ---------------

ADDR RAW(8)

INDX NUMBER

INST_ID NUMBER

CON_ID NUMBER

OBJECT_ID NUMBER

ATTRIBUTE VARCHAR2(30)

VALUE VARCHAR2(4000)

PARENT_ID VARCHAR2(15)

STATUS VARCHAR2(30)

MESSAGE VARCHAR2(256)

ERRNUM NUMBER

VALUE_RAW RAW(4095)

ERRTIME NUMBER

The table design is not very friendly, because it is a mix of ATTRIBUTE/VALUE pairs (hence many rows per object) and specific columns.

So, in order to get something usable to show the databases and their status, the only solution is to make use of PIVOT().

SELECT
    piv.*,
    obj.status
FROM
    (
        SELECT
            object_id,
            attribute,
            value
        FROM
            x$drc
        WHERE
            object_id IN (
                SELECT
                    object_id
                FROM
                    x$drc
                WHERE
                    attribute = 'DATABASE'
            )
    ) drc PIVOT (
        MAX ( value )
        FOR attribute
        IN (
    'DATABASE'                     DATABASE                     ,
    'intended_state'               intended_state               ,
    'connect_string'               connect_string               ,
    'RAC'                          RAC                          ,
    'enabled'                      enabled                      ,
    'role'                         role                         ,
    'receive_from'                 receive_from                 ,
    'ship_to'                      ship_to                      ,
    'dgb_connect'                  dgb_connect                  ,
    -- 'static_connect_identifier'    static_connect_identifier    ,
    'FSFOTargetValidity'           FSFOTargetValidity           
	)
    )
    piv
    JOIN x$drc obj ON ( obj.object_id = piv.object_id
                        AND obj.attribute = 'DATABASE' );

SELECT

piv.*,

obj.status

FROM

(

SELECT

object_id,

attribute,

value

FROM

x$drc

WHERE

object_id IN (

SELECT

object_id

FROM

x$drc

WHERE

attribute = 'DATABASE'

)

) drc PIVOT (

MAX ( value )

FOR attribute

IN (

'DATABASE' DATABASE ,

'intended_state' intended_state ,

'connect_string' connect_string ,

'RAC' RAC ,

'enabled' enabled ,

'role' role ,

'receive_from' receive_from ,

'ship_to' ship_to ,

'dgb_connect' dgb_connect ,

-- 'static_connect_identifier' static_connect_identifier ,

'FSFOTargetValidity' FSFOTargetValidity

)

piv

JOIN x$drc obj ON ( obj.object_id = piv.object_id

AND obj.attribute = 'DATABASE' );

To get it in a friendly format, I recommend using SQLcl and settting

set sqlformat ansiconsole

1	set sqlformat ansiconsole

This is what I get on a simple two databases configuration (primary/standby):

  OBJECT_ID DATABASE         INTENDED_STATE      CONNECT_STRING                        RAC   ENABLED   ROLE       RECEIVE_FROM   SHIP_TO          DGB_CONNECT                           FSFOTARGETVALIDITY   STATUS
   16842752 ludocdb2_site1   READ-WRITE-XPTON    newbox01:1521/ludoCDB2_SITE1_DGMGRL   NO    YES       PRIMARY    -N/A-          ludocdb2_site2   newbox01:1521/ludoCDB2_SITE1_DGMGRL   2                    SUCCESS
   33619968 ludocdb2_site2   PHYSICAL-APPLY-ON   newbox02:1521/ludoCDB2_SITE2_DGMGRL   NO    YES       PHYSICAL   -UNKNOWN-      -N/A-            newbox02:1521/ludoCDB2_SITE2_DGMGRL   1                    SUCCESS

OBJECT_ID DATABASE INTENDED_STATE CONNECT_STRING RAC ENABLED ROLE RECEIVE_FROM SHIP_TO DGB_CONNECT FSFOTARGETVALIDITY STATUS

16842752 ludocdb2_site1 READ-WRITE-XPTON newbox01:1521/ludoCDB2_SITE1_DGMGRL NO YES PRIMARY -N/A- ludocdb2_site2 newbox01:1521/ludoCDB2_SITE1_DGMGRL 2 SUCCESS

33619968 ludocdb2_site2 PHYSICAL-APPLY-ON newbox02:1521/ludoCDB2_SITE2_DGMGRL NO YES PHYSICAL -UNKNOWN- -N/A- newbox02:1521/ludoCDB2_SITE2_DGMGRL 1 SUCCESS

and with a real-time cascade standby:

  OBJECT_ID DATABASE        INTENDED_STATE       CONNECT_STRING                             ENABLED ROLE       RECEIVE_FROM    SHIP_TO                FSFOTARGETVALIDITY STATUS
   16842752 toolcdb1_site1  READ-WRITE-XPTON     newbox01:1521/TOOLCDB1_SITE1_DGMGRL        YES     PRIMARY    -N/A-           toolcdb1_site2         2                  SUCCESS
   33619968 toolcdb1_site2  PHYSICAL-APPLY-ON    newbox02:1521/TOOLCDB1_SITE2_DGMGRL        YES     PHYSICAL   toolcdb1_site1  toolcdb1_site3:RTC_ON  5                  WARNING
   50397184 toolcdb1_site3  PHYSICAL-APPLY-ON    newbox03:1521/TOOLCDB1_SITE3_DGMGRL        YES     PHYSICAL   toolcdb1_site2  -N/A-                  5                  WARNING

OBJECT_ID DATABASE INTENDED_STATE CONNECT_STRING ENABLED ROLE RECEIVE_FROM SHIP_TO FSFOTARGETVALIDITY STATUS

16842752 toolcdb1_site1 READ-WRITE-XPTON newbox01:1521/TOOLCDB1_SITE1_DGMGRL YES PRIMARY -N/A- toolcdb1_site2 2 SUCCESS

33619968 toolcdb1_site2 PHYSICAL-APPLY-ON newbox02:1521/TOOLCDB1_SITE2_DGMGRL YES PHYSICAL toolcdb1_site1 toolcdb1_site3:RTC_ON 5 WARNING

50397184 toolcdb1_site3 PHYSICAL-APPLY-ON newbox03:1521/TOOLCDB1_SITE3_DGMGRL YES PHYSICAL toolcdb1_site2 -N/A- 5 WARNING

(interesting to note the leading :RTC_ON in the ship_to attribute).

Although it is much easier to get this information from DGMGRL, get it programmatically is more sure/flexible using the SQL interface, as you know what you want to get, no matter how the dgmgrl syntax changes.

Looking forward to have the REST APIs in a future version of Data Guard 🙂

—

Ludovico

Tired of formatting the tnsnames.ora to make it more readable, I have taken the nice awk examples from Jeremy Schneider: https://ardentperf.com/2008/11/28/parsing-listenerora-with-awk-and-sed/ and created a function to format all files .ora (lisp-like config files).

Example, before:

$ cat tnsnames.ora
LUDOCDB1_SITE2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP )(HOST = newbox02 )(PORT = 1522 ))
    (CONNECT_DATA =
      (SERVICE_NAME = LUDOCDB1_SITE2_DGMGRL )
    )
  )
LUDOCDB1_SITE1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP )(HOST = newbox01 )(PORT = 1522 )) (CONNECT_DATA = (SERVICE_NAME = LUDOCDB1_SITE1_DGMGRL )
    )
  )
LUDOCDB1 = (DESCRIPTION = (CONNECT_TIMEOUT = 5 )(TRANSPORT_CONNECT_TIMEOUT = 3 ) (ADDRESS_LIST = (LOAD_BALANCE = OFF ) (ADDRESS = (PROTOCOL = TCP )(HOST = newbox01 )(PORT = 1522 )) (ADDRESS = (PROTOCOL = TCP )(HOST = newbox02 )(PORT = 1522 ))) (CONNECT_DATA = (SERVICE_NAME = LUDOCDB1_RW )))

# read only:
LUDOCDB1_RO =
  (DESCRIPTION =
    (CONNECT_TIMEOUT = 5 )(TRANSPORT_CONNECT_TIMEOUT = 3 )
    (ADDRESS_LIST = (LOAD_BALANCE = OFF )
      (ADDRESS = (PROTOCOL = TCP )(HOST = newbox02 )(PORT = 1522 ))
      (ADDRESS = (PROTOCOL = TCP )(HOST = newbox01 )(PORT = 1522 ))
    ) (CONNECT_DATA =
      (SERVICE_NAME = LUDOCDB1_RO )
    )
  )

$ cat tnsnames.ora

LUDOCDB1_SITE2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP )(HOST = newbox02 )(PORT = 1522 ))

(CONNECT_DATA =

(SERVICE_NAME = LUDOCDB1_SITE2_DGMGRL )

)

LUDOCDB1_SITE1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP )(HOST = newbox01 )(PORT = 1522 )) (CONNECT_DATA = (SERVICE_NAME = LUDOCDB1_SITE1_DGMGRL )

)

LUDOCDB1 = (DESCRIPTION = (CONNECT_TIMEOUT = 5 )(TRANSPORT_CONNECT_TIMEOUT = 3 ) (ADDRESS_LIST = (LOAD_BALANCE = OFF ) (ADDRESS = (PROTOCOL = TCP )(HOST = newbox01 )(PORT = 1522 )) (ADDRESS = (PROTOCOL = TCP )(HOST = newbox02 )(PORT = 1522 ))) (CONNECT_DATA = (SERVICE_NAME = LUDOCDB1_RW )))

# read only:

LUDOCDB1_RO =

(DESCRIPTION =

(CONNECT_TIMEOUT = 5 )(TRANSPORT_CONNECT_TIMEOUT = 3 )

(ADDRESS_LIST = (LOAD_BALANCE = OFF )

(ADDRESS = (PROTOCOL = TCP )(HOST = newbox02 )(PORT = 1522 ))

(ADDRESS = (PROTOCOL = TCP )(HOST = newbox01 )(PORT = 1522 ))

) (CONNECT_DATA =

(SERVICE_NAME = LUDOCDB1_RO )

)

and after:

# cat tnsnames.ora | tidy_dotora
LUDOCDB1_SITE2=
   (DESCRIPTION=
     (ADDRESS=
       (PROTOCOL=TCP)
       (HOST=newbox02)
       (PORT=1522)
     )
     (CONNECT_DATA=
       (SERVICE_NAME=LUDOCDB1_SITE2_DGMGRL)
     )
   )

LUDOCDB1_SITE1=
   (DESCRIPTION=
     (ADDRESS=
       (PROTOCOL=TCP)
       (HOST=newbox01)
       (PORT=1522)
     )
     (CONNECT_DATA=
       (SERVICE_NAME=LUDOCDB1_SITE1_DGMGRL)
     )
   )

LUDOCDB1=
   (DESCRIPTION=
     (CONNECT_TIMEOUT=5)
     (TRANSPORT_CONNECT_TIMEOUT=3)
     (ADDRESS_LIST=
       (LOAD_BALANCE=OFF)
       (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=newbox01)
         (PORT=1522)
       )
       (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=newbox02)
         (PORT=1522)
       )
     )
     (CONNECT_DATA=
       (SERVICE_NAME=LUDOCDB1_RW)
     )
   )
# read only:

LUDOCDB1_RO=
   (DESCRIPTION=
     (CONNECT_TIMEOUT=5)
     (TRANSPORT_CONNECT_TIMEOUT=3)
     (ADDRESS_LIST=
       (LOAD_BALANCE=OFF)
       (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=newbox02)
         (PORT=1522)
       )
       (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=newbox01)
         (PORT=1522)
       )
     )
     (CONNECT_DATA=
       (SERVICE_NAME=LUDOCDB1_RO)
     )
   )

# cat tnsnames.ora | tidy_dotora

LUDOCDB1_SITE2=

(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(HOST=newbox02)

(PORT=1522)

)

(CONNECT_DATA=

(SERVICE_NAME=LUDOCDB1_SITE2_DGMGRL)

)

LUDOCDB1_SITE1=

(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(HOST=newbox01)

(PORT=1522)

)

(CONNECT_DATA=

(SERVICE_NAME=LUDOCDB1_SITE1_DGMGRL)

)

LUDOCDB1=

(DESCRIPTION=

(CONNECT_TIMEOUT=5)

(TRANSPORT_CONNECT_TIMEOUT=3)

(ADDRESS_LIST=

(LOAD_BALANCE=OFF)

(ADDRESS=

(PROTOCOL=TCP)

(HOST=newbox01)

(PORT=1522)

)

(ADDRESS=

(PROTOCOL=TCP)

(HOST=newbox02)

(PORT=1522)

)

(CONNECT_DATA=

(SERVICE_NAME=LUDOCDB1_RW)

)

# read only:

LUDOCDB1_RO=

(DESCRIPTION=

(CONNECT_TIMEOUT=5)

(TRANSPORT_CONNECT_TIMEOUT=3)

(ADDRESS_LIST=

(LOAD_BALANCE=OFF)

(ADDRESS=

(PROTOCOL=TCP)

(HOST=newbox02)

(PORT=1522)

)

(ADDRESS=

(PROTOCOL=TCP)

(HOST=newbox01)

(PORT=1522)

)

(CONNECT_DATA=

(SERVICE_NAME=LUDOCDB1_RO)

)

The AWK script:

tidy_dotora () {
 
# Heavily based on Jeremy Scheider's work:
# https://ardentperf.com/2008/11/28/parsing-listenerora-with-awk-and-sed/
#
# you can source this function in bash and use it like:
# cat $TNS_ADMIN/listener.ora | tidy_dotora 
 
awk '
 
# function for padding
function pad (string, len, char) {
	ret = string;
	for ( padi = length(string); padi<len ; padi++) {
			ret = sprintf("%s%s",ret,char);
	}
	return ret;
}
	
BEGIN {
	level=1;
	first=1;
	lastcomment=0;
}
	
{
#MAIN
 
	# just skip any comments and print as is
	if ($0 ~ "^[[:space:]]*#") {
		if (lastcomment==0) {
			printf("\n");
		}
		print;
		lastcomment=1;
		next;
	}
	lastcomment=0;
	
	# this puts every occurrence of =, ( and ) in different tokens
	gsub(/=/,"`=");
	gsub(/\(/,"`(");
	gsub(/\)/,"`)");
	split($0,tokens,"`");
 
	i=1; while(i in tokens) {
 
		# trim token and continue if empty
		gsub(/ /, "",tokens[i]);
		if(!tokens[i]) {i++; continue;}
 
		# got ( "open bracket": new level begins
		# increase the level, newline and pad
		if(tokens[i]~"^[(]") {
			level++;
			printf ("\n");
			printf (pad("", 2*level-1, " "));
		}
	
		# got ) "close bracket" : level ends
		# decrease the level but newline only if another one was closed immediately before
		if(tokens[i]~"^[)]") {
			level--;
			if (wentdown==1) {
				printf("\n");
				printf (pad("", 2*level+1, " "));
			}
			wentdown=1;
		} else {
			wentdown=0;
		}
	
		# if level==1 and is alphanumeric, it is a "TOP" entry (LISTENER, SID_LIST_LISTENER or property)
		# add extra line (and eventually track it for other usage)
		if(level==1 && i==1 && tokens[i]~"[A-Za-z]") {
			TOP=tokens[i];
			if (first==1) {
				first=0;
			} else {
				printf "\n\n";
			}
		}
 
		printf (tokens[i]);
		i++;
	}
}
END {
	# new line at the end of file
	printf("\n");
}' 
}

tidy_dotora () {

# Heavily based on Jeremy Scheider's work:

# https://ardentperf.com/2008/11/28/parsing-listenerora-with-awk-and-sed/

# you can source this function in bash and use it like:

# cat $TNS_ADMIN/listener.ora | tidy_dotora

awk '

# function for padding

function pad (string, len, char) {

ret = string;

for ( padi = length(string); padi<len ; padi++) {

ret = sprintf("%s%s",ret,char);

}

return ret;

}

BEGIN {

level=1;

first=1;

lastcomment=0;

}

{

#MAIN

# just skip any comments and print as is

if ($0 ~ "^[[:space:]]*#") {

if (lastcomment==0) {

printf("\n");

}

print;

lastcomment=1;

next;

}

lastcomment=0;

# this puts every occurrence of =, ( and ) in different tokens

gsub(/=/,"`=");

gsub(/\(/,"`(");

gsub(/\)/,"`)");

split($0,tokens,"`");

i=1; while(i in tokens) {

# trim token and continue if empty

gsub(/ /, "",tokens[i]);

if(!tokens[i]) {i++; continue;}

# got ( "open bracket": new level begins

# increase the level, newline and pad

if(tokens[i]~"^[(]") {

level++;

printf ("\n");

printf (pad("", 2*level-1, " "));

}

# got ) "close bracket" : level ends

# decrease the level but newline only if another one was closed immediately before

if(tokens[i]~"^[)]") {

level--;

if (wentdown==1) {

printf("\n");

printf (pad("", 2*level+1, " "));

}

wentdown=1;

} else {

wentdown=0;

}

# if level==1 and is alphanumeric, it is a "TOP" entry (LISTENER, SID_LIST_LISTENER or property)

# add extra line (and eventually track it for other usage)

if(level==1 && i==1 && tokens[i]~"[A-Za-z]") {

TOP=tokens[i];

if (first==1) {

first=0;

} else {

printf "\n\n";

}

printf (tokens[i]);

i++;

}

END {

# new line at the end of file

printf("\n");

}

I have included the function in the COE github repo. More functions to come (hopefully).

—

Ludo

DBA survival BLOG

Some DBA stuff and lots of Oracle Data Guard

Monthly Archives: June 2020

How to get the Data Guard broker configuration from a SQL query?

Awk to format the files .ora (listener.ora, tnsnames.ora, etc)