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:

$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.2.0 - Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration;

Configuration - PeppiEnKokki

  Protection Mode: MaxAvailability
  Databases:
    peppi - Primary database
    kokki - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

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:

DGMGRL> help sql

Executes a SQL statement

Syntax:

  SQL "<sql statement>";

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:

DGMGRL> show database kokki 

Database - kokki

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    v1120

Database Status:
SUCCESS

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:

DGMGRL> connect sys/oracle@kokki
Connected.
DGMGRL> sql "alter database open";
Succeeded.

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:

DGMGRL> show database kokki 

Database - kokki

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    v1120

Database Status:
SUCCESS

My physical standby database is now in Real Time Query mode, which proves that the SQL statement indeed executed on “kokki”

Let’s see what happens if the SQL statement being executed encounters an error:

DGMGRL> sql "alter database open";
ORA-01531: a database already open by the instance

Failed.

The output we receive from DGMGRL is pretty clear that the SQL statement encountered an error by showing us the resulting error message.

So far everything behaved as I expected, but I was a little bit disappointed after attempting to perform a query from within DGMGRL:

DGMGRL> sql "select * from hr.employees";
ORA-24374: define not done before fetch or execute and fetch

Failed.

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

About these ads