Executing SQL statements from within the Data Guard broker (DGMGRL)

While teaching the Oracle11g release 2 Data Guard course last week my students and I found a new feature of the Data Guard broker (DGMGRL). We discovered that it is now possible to execute SQL statements directly from within DGMGRL. This new feature became available after installing the first Oracle Database 11g release 2 patchset (11.2.0.2.0).

Before demonstrating this useful addition to DGMGRL let us have a look at my demo Data Guard setup:

The output above shows that I have a primary database called “peppi” and a physical standby database called “kokki”. Regular visitors of my blog recognize this setup from my previous Data Guard postings. Please note the version number (11.2.0.2.0) of DGMGRL.

Lets explore the new “sql” statement by issuing a “help sql” statement to DGMGRL:

As shown above the usage for the “sql” DGMGRL statement is very straightforward. So let’s give it a try by asking for a log switch on my primary database:

DGMGRL> sql "alter system switch logfile";
Succeeded.

And yes, it indeed works as advertised! This feature is not restricted to the primary database. We can also issue SQL statements against a standby database. Before issuing a SQL statement to “kokki” let’s have a look at its current state:

The output above shows that “kokki” is doing just fine and that Real Time Query (RTQ) is currently inactive. We can activate RTQ by issuing an “alter database open” statement to “kokki”. Up until version 11.2.0.1.0 this had to be done using SQL*Plus, or a similar tool, but starting from version 11.2.0.2.0 this can now be issued directly from within DGMGRL as shown below:

In order to issue a SQL statement to “kokki” we first need to connect to it by means of the DGMGRL “connect” statement after which we can issue the “alter database open” SQL statement. The output above shows that this succeeds without errors but we can verify this ourselves by executing a “show database” DGMGRL statement:

The above output makes it pretty clear that we cannot execute queries from within DGMGRL. Apparently this is because DGMGRL is not expecting results back from the database. I don’t know if this is by design or by mistake, but it seems to me that currently queries and DML statements are not supported by DGMGRL.

However being able to execute DDL statements directly from within DGMGRL is a real cool feature to have! Thanx Larry ;-)-Harald

Like this:

LikeLoading...

Related

This entry was posted on December 4, 2010 at 17:50 and is filed under Oracle.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.

3 Responses to “Executing SQL statements from within the Data Guard broker (DGMGRL)”