By Simon Pane

We often see DBAs use somewhat cumbersome methods to document or write implementation steps that involve a combination of both OS (i.e. “Bash”) and Oracle (i.e. “SQL Plus”) commands.  However, with pipes and command concatenation, most Oracle utility commands can be executed more efficiently from a single or small number of OS commands.

Using a single command not only makes documentation and manual steps easier/cleaner to implement but also makes the commands trivial to automate or use at scale with tools such as Ansible.

Starting with a very simple example of a multi-step SQL Plus command, we often see documentation similar to:

$ sqlplus / as sysdba

SQL> SET lines 120

SQL> COLUMN filename FORMAT A80

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

SQL> SELECT status, filename FROM v$block_change_tracking;

SQL> exit

The corresponding more efficient single OS command using piping:

echo “

SET lines 120

COLUMN filename FORMAT A80

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

SELECT status, filename FROM v\$block_change_tracking;

” | sqlplus -s -L / as sysdba

Notice the need to negate the dollar sign from being a special SHELL character using the back-slash.  For Windows/PowerShell the same command works though using Windows-specific special characters. (When cutting and pasting, make sure to paste as plain text and watch-out for automatic special character conversions with EOLs and dashes.)

Of course, simple database startup and shutdown commands can similarly be handled through piping:

echo startup | sqlplus / as sysdba

echo shutdown immediate | sqlplus / as sysdba

Using the above is a tiny bit more efficient when manually starting/stopping databases than interactively going into SQL Plus.

A more complex and useful example is when we need to capture some information from the SQL output – maybe to use in a later step.  For example, maybe we want to capture the current SCN to use in a subsequent RMAN duplicate setup (possibly as part of a Data Guard setup):

`echo “select ‘export DUP_SCN=’||current_scn from v\\$database;” | sqlplus -s -L / as sysdba | grep ” [D]UP_SCN”`

echo “About to duplicate to SCN: ${DUP_SCN}”

Again, properly negating special characters (such as the “dollar sign” needs special attention and is usually the cause of this type of command not working as intended).

Some may know that we can also use command concatenation with RMAN commands.  For example:

echo “

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;

” | rman target=/

But usually, people don’t use the same sort of OS command line techniques with the Data Guard command-line interface (DGMGRL).  Documentation of steps often looks like (example of checking the standby status of a Data Guard configuration with a single physical standby):

$ dgmgrl

DGMGRL> connect /

DGMGRL> show configuration

DGMGRL> show database <standby database name from previous command>

DGMGRL> exit

Again, we can do this a little more efficiently for simplicity and to make the commands more programmatic for automated use by simply passing the Data Guard specific command in as a DGMGRL argument:

export STANDBY_DB=`dgmgrl -silent / “SHOW CONFIGURATION” | grep “Physical standby” | awk -F” ” ‘{ print $1 }’`

dgmgrl -silent / “show database ${STANDBY_DB}”

Conclusion

Of course, using script files is possible with all of these tools as well.  But when you don’t want to create an additional script file for simple steps, using multi-part OS command concatenated with pipes and other shell commands makes the commands easier to execute, more reliable, and usable in automation tools and other scripts.

Interested in Writing for SELECT?

Are you interested in getting your writing published and sharing your knowledge with other Oracle users? Quest Oracle Community is looking for writers to contribute to the SELECT Journal for our Quest IOUG Database & Technology Community audience. We are looking for writers with expertise in a variety of topics, including the following:

• Database sharding
• Database security
• Database performance
• Database development
• Database auditing
• And more!

For more information about how to get involved, please email [email protected].