Oracle Utilities Ruby client

This ruby client wraps the Oracle command line utilities - DGMGRL - SQLPLUS - Generic CLI

For further details on the CLI, please refer to docs.oracle.com/en/database/oracle/oracle-database/12.2/dgbkr/oracle-data-guard-broker-commands.html

Example: Run the utility

require 'mintpress-common'
require 'mintpress-utils'
require 'oracle-utils'

opts = {
  :ssh_hostname => "database-create-12cR2-oraclelinux-67",
  :ssh_username => "oracle",
  :ssh_port => 2222,
  :ssh_key => "~/Development/Workspaces/LimePoint/environmint-chef-repo/chef-repo/cookbooks/oracle-database/.kitchen/kitchen-vagrant/database-create-12cR2-oraclelinux-67/.vagrant/machines/default/virtualbox/private_key",
  :sudo => true,
  :sudo_user => "vagrant",
  :sudo_shell_type => "su",
  :log_level => "info"
}
client = MintPress::OracleDataGuardBroker::Client.new(opts)
dgmgrl = client.dgmgrl("/oracle/app/12.2.0/db")

# Connect to a database using DGMGRL
dgmgrl.connect(db_service_name: "ODB12CR2", db_username: "sys", db_password: "welcome1")

# Execute a command directly
dgmgrl.execute("SET TIME ON")
dgmgrl.execute("host echo 'this is a test'")
dgmgrl.execute("CREATE CONFIGURATION 'DRSolution' AS PRIMARY DATABASE IS 'ODB12CR2' CONNECT IDENTIFIER IS ODB12CR2;")
# Show current configuration
stdout = dgmgrl.show_configuration
puts stdout.to_s

# Remove configuration
dgmgrl.remove_configuration

# Create a new configuration
dgmgrl.create_configuration(name: "DR Configuration", primary_db: "ODB12CR2", connect_identifier: "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=database-create-12cR2-oraclelinux-67)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ODB12CR2)))")

# Show database configuration
stdout = dgmgrl.show_database("ODB12CR2")
puts stdout.to_s

# Show database configuration property 'LogArchiveFormat'
stdout = dgmgrl.show_database("ODB12CR2", "LogArchiveFormat")
puts stdout.to_s

# Show database instance configuration
stdout = dgmgrl.show_instance("ODB12CR2")
puts stdout.to_s

# Show database instance property 'StaticConnectIdentifier'
stdout = dgmgrl.show_instance("ODB12CR2", "StaticConnectIdentifier", "on database 'ODB12CR2'")
puts stdout.to_s

# Show DataGuard broker configuration
stdout = dgmgrl.show_configuration
puts stdout.to_s

# Show observer configuration
stdout = dgmgrl.show_observer
puts stdout.to_s

# Disconnect
if dgmgrl.connected?
  dgmgrl.close
end

Example: Run the utility (SQLPlus)

require 'oracle-utils'

opts = {
  connect_address: "dbhost1",
  connect_user: "vagrant",
  final_user: "oracle",
  keys: [ "~/.ssh/id_rsa" ],
  protocol: 'ssh',
  connect_port: 22
}

host1 = MintPress::Infrastructure::Host.new(opts.merge!(name: "dbhost1"))
client = MintPress::OracleUtils::Client.new(host: host1)

# Connect using DB host/port
sqlplus = client.sqlplus(oracle_home: "/oracle/app/xxx/db/12.2.0", 
                          db_username: "foo", 
                          db_password: "bah", 
                          db_host: "dbhost1",
                          db_port: 1521,
                          db_service_name: "ORADB")
sqlplus.connect
result = sqlplus.execute("SELECT 'Hello World!' from dual")
puts result

# Connect using SYSDBA
sqlplus = client.sqlplus(oracle_home: "/oracle/app/xxx/db/12.2.0", 
                          db_username: "sys", 
                          db_password: "foobar",
                          db_role: "SYSDBA", 
                          db_host: "dbhost1",
                          db_port: 1521,
                          db_service_name: "ORADB")
sqlplus.connect_admin
result = sqlplus.execute("SELECT 'Hello World!' from dual")
puts result

# Connect using DB connect descriptor
sqlplus = client.sqlplus(oracle_home: "/oracle/app/xxx/db/12.2.0", 
                          db_username: "foo", 
                          db_password: "bah", 
                          db_connect_descriptor: "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORADB)))")
sqlplus.connect
result = sqlplus.execute("SELECT 'Hello World using DB connect descriptor!' from dual")
puts result

# Pass optional Additional Environment Variables or SQL Properties when initializing SQLPlus
sqlplus = client.sqlplus(oracle_home: "/oracle/app/xxx/db/12.2.0", 
                          db_username: "sys", 
                          db_password: "foobar",
                          db_role: "SYSDBA", 
                          db_host: "dbhost1",
                          db_port: 1521,
                          db_service_name: "ORADB",
                          default_directory: "/tmp/temp1",  # optional; this will make MintPress cd into this directory before running sqlplus; 
                          sql_properties: {'feedback' => 'on', 'tab' => 'off'}, # optional; will set feedback to on and tab to off
                          environment_variables: {'VAR1' => 'Hello', 'VAR2' => 'Aloha'}; optional; will set the environment variables before executing sqlplus
                          )
sqlplus.connect_admin
result = sqlplus.execute("SELECT 'I have additional SQL properties and environment variables set and my default directory is different!' from dual")
puts result

License & Authors

• Author:: LimePoint (support@limepoint.com)

# MintPress® - Automation and Configuration Management
#
# Copyright © 2014 LimePoint. All rights reserved.
#
# This program and its contents are confidential and owned by LimePoint.
# Only licenced users are permitted to access and use of this file.
# This program (or any part of it) may not be disclosed, copied or used
# except as expressly permitted in LimePoint’s End User Licence Agreement.
#
# LimePoint® and MintPress® are Registered Trademarks of LimePoint IP Limited.
# For more information contact LimePoint at http://www.limepoint.com