Connect to Autonomous Data Warehouse Using Oracle Database Tools (SQL*Plus)

Description:-

Applications and tools connect to an Autonomous Data Warehouse instance using Oracle Net Services (also known as SQL*Net). Oracle Net Services enables a network session from a client application to an Oracle Database server.

When a network session is established, Oracle Net Services acts as the data courier for both the client application and the database. It is responsible for establishing and maintaining the connection between the client application and the database, as well as exchanging messages between them.

Oracle Net Services support a variety of connection types to the Autonomous Data Warehouse, including:

Oracle Call Interface (OCI), which is used by many applications written in C language. Examples include Oracle utilities such as Oracle SQL*Plus, SQL*Loader, and Oracle Data Pump.

Connect with SQL*Plus

SQL*Plus is a command-line interface used to enter SQL commands. SQL*Plus connects to an Oracle database.

To install and configure the client and connect to the Autonomous Data Warehouse using SQL*Plus, do the following:

Prepare for Oracle Call Interface (OCI), ODBC and JDBC OCI Connections.

Before making an Oracle Call Interface(OCI), ODBC, or JDBC OCI connection, do the following:

Step1:-Install Oracle Client software version 12.2.0.1 (or higher) on your computer. Either the full Oracle Client or the Oracle Instant Client may be used. The Instant Client contains the minimal software needed to make an Oracle Call Interface connection. The Instant Client is sufficient for most applications.

Step2:-Download client credentials and store the file in a secure folder on your computer. See Download Client Credentials (Wallets) here

Step3:-Unzip/uncompress the credentials file into a secure folder on your computer.

Copy this  (sqlnet.ora& tnsnames.ora) file to ?\network\admin

 

Step4:-Edit the sqlnet.ora file in the folder where you unzip the credentials file, replacing “?/network/admin” with the name of the folder containing the client

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))
SSL_SERVER_DN_MATCH=yes

To

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="DIRECTORY="D:\walletcloud")))
SSL_SERVER_DN_MATCH=yes

Step5:-Connect using a database user, password, and database service name provided in the tnsnames.ora file.

The Oracle Wallet is transparent to SQL*Plus because the wallet location is specified in the sqlnet.ora file. This is true for any Oracle Call Interface (OCI), ODBC, or JDBC OCI connection.

Successfully connected using Sql*Plus.

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DkeGfZsxfzXxHD6gTg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Devops Ansible Helps for Oracle DBA’s

Oracle Database Automation using Ansible Tool 

  • Installation and Configuration of Ansible Here
  • Oracle Automation-Create a DBA User Using Ansible Tool Here
  • Oracle Automation-Creating Oracle 12c Database Using Ansible Tool Here
  • Oracle Automation-Applying PSU patch in Oracle 12c Database Using Ansible Tool Here

New generation of IT Automation  tools

  • Ansible
  • Salt
  • Puppet
  • Chef

About this Tools:

What can be Automated using Ansible Tool?

  • Server Setup
  • Code , App Install & Versioning
  • Database Creation
  • Database Software Install
  • Prerequisites
  • Webserver
  • Upgrades
  • Network
  • Patching
  • Maintenance
  • Backup & Recovery
  • Install PackagesCheck more on Ansible here

Introduction Of ANSIBLE:

Ansible is an agent-less IT automation tool developed in 2012 by Michael DeHaan, a former Red Hat associate.

The Ansible design goals are for it to be: minimal, consistent, secure, highly reliable, and easy to learn.

A Simple Automation Tool which uses YAML Syntax to perform Tasks Ansible is Written on Python Ansible is available as Open Source

ANSIBLE ARCHITECTURE:

Ansible Requirements & Install :

Control Machine

  • Ansible Software
  • Python

How to Install Ansible

  • Using Yum– Linux
  • Using apt– Ubuntu & Debain
  • Using brew– Mac Os
  • Pip– Python
  • Clone From Git

Note : Python is required in Target Machine for Certain Modules

Copy authorized_keys of the ansible control machine to target hosts

Ansible Version & Config :

  • After installing ansible you can check its version

ansible –version

  • Ansible will show the default Config file /etc/ansible/ansible.cfg
  • You can set parameters related to ansible

Ansible Inventory :

  • Inventory is set of hosts where automation will be executed
  • Inventory is mandatory for ansible
  • Inventory can be of two types
  1. Static
  2. Dynamic
  • Static Inventory is a file in INI format which contains the host names. Hostnames can be grouped based on the infrastructure of your company
  • By default /etc/ansible/hosts is the referenced inventory in ansible.cfg file. You can modify to different location
  • Ansible inventory file can also be specified at the run time using -i option of ansible command
  • Dynamic inventory is for getting information directly from CMDB or from any of the Cloud Provider like AWS,Azure,Digital ocean etc.

Ansible Playbooks :

  • Playbooks are the key components of ansible
  • Playbooks are set of instructions/tasks that are executed in the hosts through ansible automation.
  • Playbooks reside in ansible Control Server
  • Playbooks are written using YAML
  • Playbooks are Idempotent

Ansible Playbooks – YAML:

  • YAML – YAML Ain’t Markup Language
  • Human Readable Data Serialization Language
  • Similar to JSON
  • Human friendly
  • YAML uses Space indentations and no tabs
  • If Spacing is not properly specified syntax error is thrown

YAML Syntax

  • Start of the file —-
  • Comments #
  • Strings – Not required to be quoted
  • Boolean – True or False
  • Lists ( Sequences) – Like Arrays in JSON , Use hyphens
  • Dictionaries ( Mappings) – Like Objects in JSON

Ansible Playbook – Explanation :

  • hosts: This lists the host or host group against which we want to run the task
  • remote_user – tells Ansible to use a particular user.
  • Tasks – list of actions you want to perform
  • The name parameter represents what the task is doing
  • Modules – yum and service, have their own set of parameters

Example:

  • YUM – the state parameter has the latest value and it indicates that the httpd latest package should be installed. (yum install httpd)
  • SERVICE – the state parameter with the started value indicates that the httpd service should be started. (/etc/init.d/start)
  • “enabled” parameter defines whether the service should start at boot or not. ( service enable httpd)  
  • become: True tells that the tasks should be executed with sudo access. If sudoers does not contain that user it will throw an error. (sudo su -)

Ansible Playbook Execution :

Save the file in any text editor or IDE with extension *.yml or *.yaml

Command to Execute ansible playbook ansible-playbook –i all httpd_install.yml

If Groups are present then you can specify the group name ansible-playbook –i web httpd_install.yml

To check Syntax ansible-playbook –i web httpd_install.yml –check-syntax

Verbose

ansible-playbook –i web httpd_install.yml –v

ansible-playbook –i web httpd_install.yml –vv

ansible-playbook –i web httpd_install.yml –vvv

Ansible parses the playbook from top to bottom approach.

Below is the order followed in parsing a playbook

  • Variables are loaded
  • Facts are gathered
  • Pre_tasks are performed
  • Handlers
  • Role execution
  • Task execution
  • Handlers
  • Post_tasks

Execution strategies – Linear & Free

Ansible Variables:

Variables can be defined in the playbooks.

Variables can also be declared in a separate *.yml file and can be referenced in the playbook

Variables can also be passed during the command line using –e option

Use register to capture the output of any module into a variable

Variables can be defined at the host level or group level in the inventory file.

Summary :

  • Ansible is a simple IT automation tool
  • Inventory is the collection of hosts where the automation will be executed
  • Playbooks are the heart of Ansible and they are combination of multiple tasks
  • Tasks are the actions performed in remote hosts
  • Roles are collection of playbooks, files ,variables and templates to perform specific configuration tasks

References:-

https://www.doag.org/formes/pubfiles/7375105/2015-K-INF-Frits_Hoogland-Automating__DBA__tasks_with_Ansible-Praesentation.pdf

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DkeGfZsxfzXxHD6gTg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba