Executing SQL statements from within the Data Guard broker (DGMGRL)
Posted by Harald van Breederode on December 4, 2010
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 (220.127.116.11.0).
Before demonstrating this useful addition to DGMGRL let us have a look at my demo Data Guard setup:
$ dgmgrl / DGMGRL for Linux: Version 18.104.22.168.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 (22.214.171.124.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 126.96.36.199.0 this had to be done using SQL*Plus, or a similar tool, but starting from version 188.8.131.52.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 ;-)