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

What is Anisble?

Ansible is an open source automation platform. It is very, very simple to setup and yet powerful. Ansible can help you with configuration management, application deployment, task automation.

Ansible is an open-source IT automation engine which can remove drudgery from your work life, and will also dramatically improve the scalability, consistency, and reliability of your IT environment.

It can also do IT orchestration, where you have to run tasks in sequence and create a chain of events which must happen on several different servers or devices.

Example : If you have a group of web servers behind a load balancer. Ansible can upgrade the web servers one at a time and while upgrading it can remove the current web server from the load balancer and disable it in your Nagios monitoring system. So in short you can handle complex tasks with a tool which is easy to use.

What Ansible can automate?

Provisioning: Set up the various servers you need in your infrastructure.

Configuration management: Change the configuration of an application, OS, or device; start and stop services; install or update applications; implement a security policy; or perform a wide variety of other configuration tasks.

Application deployment: Make DevOps easier by automating the deployment of internally developed applications to your production systems.

Is Ansible Free?

YES!, Ansible is free to use and can be downloaded and installed from a number of sources, it will currently only run on a Linux or Mac that has Python installed. It will not run on Windows.

There are also some paid products i.e Ansible Engine a version of Ansible with full support from Red Hat and also Ansible Tower a GUI front end to drive Ansible core Ansible Tower is licensed on a per node basis.

But if you just want to download Ansible and use it for your home or production use – it is free to use.

The Keys Features of Ansible

Agentless

There is no software or agent to be installed on the client that communicates back to the server.

Idempotent

No matter how many times you call the operation, the result will be the same.

Simple and extensible

Ansible is written in Python and uses YAML for playbook language, both of which are considered relatively easy to learn.

What are Ansible roles?

Roles provide a framework for fully independent, or interdependent collections of variables, tasks, files, templates, and modules. In Ansible, the role is the primary mechanism for breaking a playbook into multiple files. This simplifies writing complex playbooks, and it makes them easier to reuse.
An Ansible playbook is an organized unit of scripts that defines work for a server configuration managed by the automation tool Ansible. … An Ansible playbook contains one or multiple plays, each of which define the work to be done for a configuration on a managed server. Ansible plays are written in YAML.
By default Ansible modules require python to be present in the target machines, since they are all written in python. … Another is speaking to any devices such as routers that do not have any Python installed. In any other case, using the shell or command module is much more appropriate.

Oracle Automation-Applying PSU patch in Oracle 12c Database Using Ansible Tool

Description:-

In this article we are going to see Oracle Automation-Applying PSU patch in Oracle 12c Database Using Ansible Tool

Let’s start the Demo:-

Steps to apply oracle PSU patch using Ansible :-

Step 1:- Download the ansible-oracle-psu role from  github site.Click Here

Step 2:-Copy the ZIP file to ansible server and unzip the file

[oracle@ansible Desktop]# unzip ansible-oracle-psu.zip
Archive: ansible-oracle-master.zip
1bbb35d690c102e23488537754ff8d1e0a99dc15
creating: ansible-oracle-master/
[oracle@ansible Desktop]# ls -lrt
total 4272
drwxr-xr-x. 7 root root 4096 Jul 30 11:12 ansible-oracle-psu
-rwxrw-rw-. 1 ansible ansible 349507 Jul 30 22:33 ansible-oracle-psu.zip

Step 3:- Ansible playbook file for applying path in ORACLE_HOME

Sample file:- 

- hosts: servers
  roles:
     - role: psu-apply

Role name: Ansible role psu-apply to apply Oracle patches (PSU) to single instance (SI) and RAC instances.

Step 4:- Copy the p6880880_121010_Linux-x86-64.zip(opatch utility upgrade) and PSU patch(p27338041_121020_Linux-x86-64.zip) file to the target server.

TARGET :-

[oracle@test OPatch]$ cd /u01/
[oracle@test u01]$ ls -lrt

drwxr-xr-x.  4 oracle oinstall        38 Jul 26 15:36 app
-rwxrwxrwx. 1 oracle oinstall 496419192 Aug 1 19:22 p27338041_121020_Linux-x86-64.zip
-rwxrwxrwx. 1 oracle oinstall 99183505 Aug 2 12:14 p6880880_121010_Linux-x86-64.zip

Step 5:- Create a role folder under /etc/ansible & copy the ansible-oracle-psu folder and rename as psu-apply folder

[oracle@ansible ansible]$ mkdir roles

[oracle@ansible ansible]$ mv ansible-oracle-psu   psu-apply

[oracle@ansible ~]$ cd /etc/ansible/roles/psu-apply/
[oracle@ansible psu-apply]$ ls -lrt
total 16
-rwxrwxr-x. 1 oracle oinstall 1300 Sep 19 2017 README.md
drwxrwxr-x. 2 oracle oinstall 51 Aug 10 23:58 files
-rwxrwxr-x. 1 oracle oinstall 62 Aug 11 00:04 patch.yml
drwxrwxr-x. 2 oracle oinstall 21 Aug 12 11:15 meta
drwxrwxr-x. 2 oracle oinstall 4096 Aug 12 13:20 templates
drwxrwxr-x. 2 oracle oinstall 21 Aug 12 13:51 defaults
drwxrwxr-x. 2 oracle oinstall 4096 Aug 12 14:06 tasks
drwxrwxr-x. 2 oracle oinstall 21 Aug 12 14:06 vars

Step 6:- Run the tree command and see the execution of .yml files to apply a patch for ORACLE_HOME.

[root@ansible roles]# tree psu-apply
psu-apply
├── defaults
│   └── main.yml
├── files
│   ├── check_patches.sql
│   └── getMOSPatch.sh
├── meta
│   └── main.yml
├── patch.retry
├── patch.yml
├── README.md
├── tasks
│   ├── conflict_check.yml
│   ├── dbbp_rac.yml
│   ├── dbbp_si.yml
│   ├── extract_files.yml
│   ├── main.yml
│   ├── oneoff_apply.yml
│   ├── oneoff_rollback.yml
│   ├── pre_checks.yml
│   ├── psu_rac.yml
│   ├── psu_si.yml
│   ├── rollback_patch.yml
│   ├── rollback_rac.yml
│   ├── rollback_si.yml
│   ├── run_catbundle.yml
│   └── ru_si.yml
├── templates
│   ├── getMOSPatch.sh.cfg.j2
│   ├── ocmrsp.expect.j2
│   ├── patch_list.j2
│   ├── quarterly_patch_list.j2
│   └── srvctl_state.j2
└── vars
    └── main.yml

6 directories, 29 files

Role Variables :-

vars/main.yml

patch_type: psu dictionary variable for information for 12.1.0.2 and 11.2.0.4

defaults/main.yml
patch_name: APR2018 # Should match quarterly_patches dictionary variable defined in vars/main.yml
shutdown_listener: true # set to false if patching an empty oracle home (ie new 12c install)
rollback_psu: false # set to true if rolling back current PSU.

Required Inventory Variables
oracle_install_type: valid values are SI or RAC. Used to determine if host is a single instance database install or part of a RAC cluster.

oracle_stage_install: directory that contains all oracle install files

oracle_stage: directory to store logs and scripts used during playbook execution.

oracle_version: expects values of 11.2.0.4 or 12.1.0.2

Step 7:- In main.yml file,provide the below entries

[oracle@ansible defaults]$ cat main.yml
---

patch_name: APR2018 # Should match psu dictionary variable defined in vars/main.yml

shutdown_listener: true # set to false if patching an empty oracle home (ie new 12c install)
rollback_psu: false # set to true if rolling back current PSU.

oracle_home: "/u01/app/oracle/product/12.1.0/dbhome_1/"
oracle_base: /u01/app/oracle
tns_admin: "{{ oracle_home }}/network/admin"

oracle_stage_install: /u01
oracle_stage_base: /u01
oracle_install_type: si
oracle_version: 12.1.0.2
patch_type: psu
patch_directory: /u01

env:
ORACLE_HOME: "{{ oracle_home }}"
ORACLE_BASE: "{{ oracle_base }}"
TNS_ADMIN: "{{ tns_admin }}"
PATH: "{{ oracle_home }}/bin:{{ oracle_home }}/OPatch:$PATH:/bin:/usr/bin::/usr/ccs/bin"

oracle_user: oracle # User that will own the Oracle Installations.
oracle_group: oinstall # Primary group for oracle_user.

Step 8:- Provide the PSU patch details

[oracle@ansible psu-apply]$ cd vars/ 
[oracle@ansible vars]$ cat main.yml
---
env:
ORACLE_HOME: "{{ oracle_home }}"
ORACLE_BASE: "{{ oracle_base }}"
TNS_ADMIN: "{{ tns_admin }}"
PATH: "{{ oracle_home }}/bin:{{ oracle_home }}/OPatch:$PATH:/bin:/usr/bin::/usr/ccs/bin"

# Latest opatch versions and files.
# Files should exist in path defined by oracle_stage_install.
opatch:
12.1.0.2:
version: 12.2.0.1.14
filename: p6880880_121010_Linux-x86-64.zip

# Database Proactive Patch for 12.1
psu:
12.1.0.2:
APR2018:
patchversion: 12.1.0.2.180417
filename: p27338041_121020_Linux-x86-64.zip
patchid: 27338041
si_patchid: 27338041

Step 9:- The list of tasks has been executed for applying patch in target ORACLE_HOME.

tasks/main.yml

---
role: psu-apply
#file: main.yml

- debug: var=patch_type

- name: Run pre-check tasks
include: pre_checks.yml
tags: pre_checks

- name: Extract PSU and One-off patches
include: extract_files.yml
when: not rollback_psu
tags: extract_files

- name: Stop EM Agent
shell: "{{ agent_home }}/bin/emctl stop agent"
become_user: "{{ oracle_user }}"
when: agent_home is defined and shutdown_listener
tags: stop_agent

- name: Apply Quarterly Patch
include: "{{ patch_type }}_{{ oracle_install_type }}.yml"
become_user: "{{ oracle_user }}"
when: not rollback_psu
tags: apply

- name: Rollback Quarterly Patch
include: "rollback_{{ oracle_install_type }}.yml"
become_user: "{{ oracle_user }}"
when: rollback_psu
tags: rollback

- name: Start EM Agent
shell: "{{ agent_home }}/bin/emctl start agent"
become: yes
become_user: "{{ oracle_user }}"
when: agent_home is defined and shutdown_listener
tags: start_agent

- name: Opatch lsinventory
shell: "{{ oracle_home }}/OPatch/opatch lsinventory -oh {{ oracle_home }}"
become_user: "{{ oracle_user }}"
with_items:
- "{{ oracle_home }}"
- "{{ grid_home|default(None) }}"
when: item is defined
register: lsinv_post

tasks/pre_checks.yml
---
- name: fail if required variables are not defined
fail: msg="{{ item }} is not defined for {{ ansible_hostname }}"
when: "{{ item }} is not defined"
tags: always
with_items:
- oracle_install_type
- oracle_version
- patch_type
- patch_name

- name: Check for zip file
stat:
path: "{{ oracle_stage_install }}"
get_checksum: false
get_md5: false
register: patch_st
failed_when: "not {{ patch_st.stat.exists }}"

- name: Check if OPatch zip file exists
stat:
path: "{{ oracle_stage_install }}"
register: opatch_st
failed_when: "not {{ opatch_st.stat.exists }}"

# - name: fail if PSU-Specific one-off patches are missing
# stat:
# path: "{{ oracle_stage_install }}/{{ item.filename }}"
# register: patch_st
# with_items: oneoff_patches[ quarterly_patches[patch_type][oracle_version][patch_name].patchversion ]|default([])
# when: "{{ oneoff_patches[ quarterly_patches[patch_type][oracle_version][patch_name].patchversion ] }} is defined"

# - name: Fail if missing PSU-Specific one-off patches
# fail: msg="Oops! Missing one-off patch {{ item.item.filename }}"
# with_items: patch_st.results
# when:
# - "{{ oneoff_patches[ quarterly_patches[patch_type][oracle_version][patch_name].patchversion ] }} is defined"
# - "not {{ item.stat.exists }}"

Step 10 :- Check the opatch version and inventory in ORACLE_HOME

[oracle@fhpasadbdr01 OPatch]$ ./opatch version
OPatch Version: 12.1.0.1.8

OPatch succeeded.

Step 11 :- Stop the database & listener services

Playbook file to apply patch :-

[oracle@ansible psu-apply]$ cat patch.yml
---
- hosts: database-servers
roles:
- role: psu-apply

Step 12:- Check ping status before applying a patch

[oracle@ansible psu-apply]$ ansible all -m ping

test | SUCCESS => {
"changed": false,
"ping": "pong"
}

Syntax check:-

[oracle@ansible psu-apply]$ ansible-playbook patch.yml --syntax-check

playbook: patch.yml

Step 13:- Run the playbook file to apply a database patch in ORACLE_HOME.

patch.yml file will update the opatch utility and apply PSU patch in ORACLE_HOME which we copied to /u01 directory

[oracle@ansible psu-apply]$ ansible-playbook patch.yml

PLAY [database-servers] **********************************************************

TASK [Gathering Facts] ***************************************************************************************************
ok: [test]

TASK [psu-apply : debug] *************************************************************************************************
ok: [test] => {
"patch_type": "psu"
}

TASK [psu-apply : fail if required variables are not defined] ************************************************************

skipping: [test] => (item=oracle_install_type)
skipping: [test] => (item=oracle_version)
skipping: [test] => (item=patch_type)
skipping: [test] => (item=patch_name)

TASK [psu-apply : Check for zip file] ************************************************************************************
ok: [test]

TASK [psu-apply : Check if OPatch zip file exists] ***********************************************************************
ok: [test]

TASK [psu-apply : Create Patch directory] ********************************************************************************
ok: [test]

TASK [psu-apply : Unzip Quarterly Patch] *********************************************************************************
skipping: [test]

TASK [psu-apply : Unzip oneoff patches] **********************************************************************************

TASK [psu-apply : Stop EM Agent] *****************************************************************************************
skipping: [test]

TASK [psu-apply : Apply Quarterly Patch] *********************************************************************************
included: /etc/ansible/roles/psu-apply/tasks/psu_si.yml for test

TASK [psu-apply : PSU SI | update opatch] *******************************************************************************
changed: [test]

TASK [psu-apply : PSU SI | Get list of patches in PSU] *****************************************************************
ok: [test]

TASK [psu-apply : PSU SI | Template patch list] *************************************************************************
ok: [test]

TASK [psu-apply : PSU SI | check for conflicts] *************************************************************************
changed: [test] => (item=CheckConflictAgainstOHWithDetail)
changed: [test] => (item=CheckSystemSpace)

TASK [psu-apply : debug] *************************************************************************************************
ok: [test] => {
"opatch_conflicts": {
"changed": true,
"msg": "All items completed",
"results": [
{
"_ansible_ignore_errors": null,
"_ansible_item_label": "CheckConflictAgainstOHWithDetail",
"_ansible_item_result": true,
"_ansible_no_log": false,
"_ansible_parsed": true,
"changed": true,
"cmd": "opatch prereq CheckConflictAgainstOHWithDetail -phBaseFile /u01/quarterly_patch_list_12.1.0.2_APR2018.txt",
"delta": "0:00:03.900157",
"end": "2018-08-10 23:13:51.674497",
"failed": false,
"invocation": {
"module_args": {
"_raw_params": "opatch prereq CheckConflictAgainstOHWithDetail -phBaseFile /u01/quarterly_patch_list_12.1.0.2_APR2018.txt",
"_uses_shell": true,
"argv": null,
"chdir": null,
"creates": null,
"executable": null,
"removes": null,
"stdin": null,
"warn": true
}
},
"item": "CheckConflictAgainstOHWithDetail",
"rc": 0,
"start": "2018-08-10 23:13:47.774340",
"stderr": "",
"stderr_lines": [],
"stdout": "Oracle Interim Patch Installer version 12.2.0.1.14\nCopyright (c) 2018, Oracle Corporation. All rights reserved.\n\nPREREQ session\n\nOracle Home : /u01/app/oracle/product/12.1.0/dbhome_1\nCentral Inventory : /u01/app/oraInventory\n from : /u01/app/oracle/product/12.1.0/dbhome_1//oraInst.loc\nOPatch version : 12.2.0.1.14\nOUI version : 12.1.0.2.0\nLog file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2018-08-10_23-13-48PM_1.log\n\nInvoking prereq \"checkconflictagainstohwithdetail\"\n\nPrereq \"checkConflictAgainstOHWithDetail\" passed.\n\nOPatch succeeded.",
"stdout_lines": [
"Oracle Interim Patch Installer version 12.2.0.1.14",
"Copyright (c) 2018, Oracle Corporation. All rights reserved.",
"",
"PREREQ session",
"",
"Oracle Home : /u01/app/oracle/product/12.1.0/dbhome_1",
"Central Inventory : /u01/app/oraInventory",
" from : /u01/app/oracle/product/12.1.0/dbhome_1//oraInst.loc",
"OPatch version : 12.2.0.1.14",
"OUI version : 12.1.0.2.0",
"Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2018-08-10_23-13-48PM_1.log",
"",
"Invoking prereq \"checkconflictagainstohwithdetail\"",
"",
"Prereq \"checkConflictAgainstOHWithDetail\" passed.",
"",
"OPatch succeeded."
]
},
{
"_ansible_ignore_errors": null,
"_ansible_item_label": "CheckSystemSpace",
"_ansible_item_result": true,
"_ansible_no_log": false,
"_ansible_parsed": true,
"changed": true,
"cmd": "opatch prereq CheckSystemSpace -phBaseFile /u01/quarterly_patch_list_12.1.0.2_APR2018.txt",
"delta": "0:00:03.471081",
"end": "2018-08-10 23:13:55.508123",
"failed": false,
"invocation": {
"module_args": {
"_raw_params": "opatch prereq CheckSystemSpace -phBaseFile /u01/quarterly_patch_list_12.1.0.2_APR2018.txt",
"_uses_shell": true,
"argv": null,
"chdir": null,
"creates": null,
"executable": null,
"removes": null,
"stdin": null,
"warn": true
}
},
"item": "CheckSystemSpace",
"rc": 0,
"start": "2018-08-10 23:13:52.037042",
"stderr": "",
"stderr_lines": [],
"stdout": "Oracle Interim Patch Installer version 12.2.0.1.14\nCopyright (c) 2018, Oracle Corporation. All rights reserved.\n\nPREREQ session\n\nOracle Home : /u01/app/oracle/product/12.1.0/dbhome_1\nCentral Inventory : /u01/app/oraInventory\n from : /u01/app/oracle/product/12.1.0/dbhome_1//oraInst.loc\nOPatch version : 12.2.0.1.14\nOUI version : 12.1.0.2.0\nLog file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2018-08-10_23-13-52PM_1.log\n\nInvoking prereq \"checksystemspace\"\n\nPrereq \"checkSystemSpace\" passed.\n\nOPatch succeeded.",
"stdout_lines": [
"Oracle Interim Patch Installer version 12.2.0.1.14",
"Copyright (c) 2018, Oracle Corporation. All rights reserved.",
"",
"PREREQ session",
"",
"Oracle Home : /u01/app/oracle/product/12.1.0/dbhome_1",
"Central Inventory : /u01/app/oraInventory",
" from : /u01/app/oracle/product/12.1.0/dbhome_1//oraInst.loc",
"OPatch version : 12.2.0.1.14",
"OUI version : 12.1.0.2.0",
"Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2018-08-10_23-13-52PM_1.log",
"",
"Invoking prereq \"checksystemspace\"",
"",
"Prereq \"checkSystemSpace\" passed.",
"",
"OPatch succeeded."
]
}
]
}
}

TASK [psu-apply : PSU SI | get list of database names on host] **********************************************************
changed: [test]

TASK [psu-apply : dictionary variable with latest PSU information for 12.1.0.2 and 11.2.0.4SI | shutdown databases running in oracle_home] ***************************************************

TASK [psu-apply : PSU SI | shutdown listener in oracle_home] ************************************************************
changed: [test]

TASK [psu-apply : PSU SI | opatch apply] ********************************************************************************
changed: [test]

TASK [psu-apply : debug] *************************************************************************************************
ok: [test] => {
"opatch_apply.stdout_lines": [
"Oracle Interim Patch Installer version 12.2.0.1.14",
"Copyright (c) 2018, Oracle Corporation. All rights reserved.",
"",
"",
"Oracle Home : /u01/app/oracle/product/12.1.0/dbhome_1",
"Central Inventory : /u01/app/oraInventory",
" from : /u01/app/oracle/product/12.1.0/dbhome_1//oraInst.loc",
"OPatch version : 12.2.0.1.14",
"OUI version : 12.1.0.2.0",
"Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2018-08-10_23-13-57PM_1.log",
"",
"Verifying environment and performing prerequisite checks...",
"OPatch continues with these patches: 19769480 20299023 20831110 21359755 21948354 22291127 23054246 24006101 24732082 25171037 25755742 26609783 26713565 26925311 27338041 ",
"",
"Do you want to proceed? [y|n]",
"Y (auto-answered by -silent)",
"User Responded with: Y",
"All checks passed.",
"",
"Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.",
"(Oracle Home = '/u01/app/oracle/product/12.1.0/dbhome_1')",
"",
"",
"Is the local system ready for patching? [y|n]",
"Y (auto-answered by -silent)",
"User Responded with: Y",
"Backing up files...",
"Applying sub-patch '19769480' to OH '/u01/app/oracle/product/12.1.0/dbhome_1'",
"",
"Composite patch 27338041 successfully applied.",
"Log file location: /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2018-08-10_23-13-57PM_1.log",
"",
"OPatch succeeded."
]
}

TASK [psu-apply : PSU SI | apply oneoff patches] ************************************************************************
skipping: [test]

TASK [psu-apply : PSU SI | startup databases] ***************************************************************************

TASK [psu-apply : PSU SI | 12.1 - startup pluggable databases] **********************************************************

TASK [psu-apply : PSU SI | Load Modified SQL Files into the Database] ***************************************************
included: /etc/ansible/roles/psu-apply/tasks/run_catbundle.yml for test

TASK [psu-apply : 11.2 - Load Modified SQL Files into the Database] ******************************************************

TASK [psu-apply : 12.1 - Load Modified SQL Files into the Database] ******************************************************

TASK [psu-apply : PSU SI | startup listener in oracle_home] *************************************************************
changed: [test]

TASK [psu-apply : Rollback Quarterly Patch] ******************************************************************************
skipping: [test]

TASK [psu-apply : Start EM Agent] ****************************************************************************************
skipping: [test]

TASK [psu-apply : Opatch lsinventory] ************************************************************************************
changed: [test] => (item=/u01/app/oracle/product/12.1.0/dbhome_1/)
changed: [test] => (item=)

OPatch succeeded.",
"stdout_lines": [
"Oracle Interim Patch Installer version 12.2.0.1.14",
"Copyright (c) 2018, Oracle Corporation. All rights reserved.",
"",
"",
"Oracle Home : /u01/app/oracle/product/12.1.0/dbhome_1",
"Central Inventory : /u01/app/oraInventory",
" from : /u01/app/oracle/product/12.1.0/dbhome_1//oraInst.loc",
"OPatch version : 12.2.0.1.14",
"OUI version : 12.1.0.2.0",
"Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2018-08-10_23-24-05PM_1.log",
"",
"Lsinventory Output file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2018-08-10_23-24-05PM.txt",
"",
"--------------------------------------------------------------------------------",
"Local Machine Information::",
"Hostname: test.localdomain.com",
"ARU platform id: 226",
"ARU platform description:: Linux x86-64",
"",
"Installed Top-level Products (1): ",
"",
"Oracle Database 12c 12.1.0.2.0",
"There are 1 products installed in this Oracle Home.",
"",
"",
"Interim patches (1) :",
"",
"Patch 27338041 : applied on Fri Aug 10 23:20:33 EDT 2018",
"Unique Patch ID: 22036385",
"Patch description: \"Database Patch Set Update : 12.1.0.2.180417 (27338041)\"",
" Created on 16 Mar 2018, 02:05:00 hrs PST8PDT",
"Sub-patch 26925311; \"Database Patch Set Update : 12.1.0.2.180116 (26925311)\"",
"Sub-patch 26713565; \"Database Patch Set Update : 12.1.0.2.171017 (26713565)\"",
"Sub-patch 26609783; \"Database Patch Set Update : 12.1.0.2.170814 (26609783)\"",
"Sub-patch 25755742; \"Database Patch Set Update : 12.1.0.2.170718 (25755742)\"",
"Sub-patch 25171037; \"Database Patch Set Update : 12.1.0.2.170418 (25171037)\"",
"Sub-patch 24732082; \"Database Patch Set Update : 12.1.0.2.170117 (24732082)\"",
"Sub-patch 24006101; \"Database Patch Set Update : 12.1.0.2.161018 (24006101)\"",
"Sub-patch 23054246; \"Database Patch Set Update : 12.1.0.2.160719 (23054246)\"",
"Sub-patch 22291127; \"Database Patch Set Update : 12.1.0.2.160419 (22291127)\"",
"Sub-patch 21948354; \"Database Patch Set Update : 12.1.0.2.160119 (21948354)\"",
"Sub-patch 21359755; \"Database Patch Set Update : 12.1.0.2.5 (21359755)\"",
"Sub-patch 20831110; \"Database Patch Set Update : 12.1.0.2.4 (20831110)\"",
"Sub-patch 20299023; \"Database Patch Set Update : 12.1.0.2.3 (20299023)\"",
"Sub-patch 19769480; \"Database Patch Set Update : 12.1.0.2.2 (19769480)\"",
"---------------------------------------------------------",
"OPatch succeeded."

]
}
]
}

PLAY RECAP **********************************************************
test : ok=19 changed=7 unreachable=0 failed=0

Step 14:- Start the database and listener

Step 15 :- Check the Opatch lsinventory  of ORACLE_HOME

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
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

 

Oracle Automation-Creating Oracle 12c Database Using Ansible Tool

Description:-

In this article we are going to see Oracle Automation-Oracle Database Creation Using Ansible Tool

Let’s start the Demo:-

Steps to create Oracle database using Ansible :-

Step 1:- Download the ansible modules from  github site.Click Here

Step 2:-Move the ZIP file to ansible server.

[root@ansible Desktop]# unzip ansible-oracle-master.zip
Archive: ansible-oracle-master.zip
1bbb35d690c102e23488537754ff8d1e0a99dc15
creating: ansible-oracle-master/
[root@ansible Desktop]# ls -lrt
total 4272
drwxrwxr-x. 8 ansible ansible 4096 Jun 26 2015 ansible-1.9.2
-rwxrw-rw-. 1 ansible ansible 75962 Jan 8 2018 ansible-oracle-modules-master.zip
-rwxrw-rw-. 1 ansible ansible 1642329 Jul 19 23:21 get-pip.py
-rwxrw-rw-. 1 ansible ansible 927525 Jul 25 02:59 ansible-1.9.2.tar.gz
-rwxrw-rw-. 1 ansible ansible 1357336 Jul 25 03:14 ansible-1.9.2-1.mga5.noarch.rpm
drwxr-xr-x. 7 root root 4096 Jul 30 11:12 ansible-oracle-master
drwxrwxr-x. 3 ansible ansible 4096 Jul 30 18:04 ansible-oracle-modules-master
-rwxrw-rw-. 1 ansible ansible 349507 Jul 30 22:33 ansible-oracle-master.zip

Step 3:- Create a single instance database on filesystem level

Sample file:- 

[root@ansible ]# cd ansible-oracle-master

[root@ansible ansible-oracle-master]# cat single-instance-db-on-fs.yml

[root@ansible ansible-oracle-master]#cat single-instance-db-on-fs.yml

- name: Host configuration
hosts: "{{ hostgroup }}"
become: yes
roles:
- common
- orahost
- cxoracle

- name: Database Server Installation & Database Creation
hosts: "{{ hostgroup }}"
become: yes
roles:
- oraswdb-install
- oradb-manage-db

- name: Database Server Installation & Database Creation
hosts: database-servers
user: oracle 
sudo: yes
roles:
- oraswdb-install
- oradb-create

Note :I have already installed all prerequisites and oracle binaries hence i am not using that role oraswdb-install

Step 4:- Go to  ansible-oracle-master/roles folder.Check oradb-create role is present for database creation.

[root@ansible ansible-oracle-master]# cd roles/
[root@ansible roles]# ls -lrt
total 0
drwxr-xr-x. 5 root root 61 Jul 30 11:12 oraswracdb-clone
drwxr-xr-x. 3 root root 21 Jul 30 11:12 orasw-meta
drwxr-xr-x. 5 root root 49 Jul 30 11:12 oraswgi-opatch
drwxr-xr-x. 6 root root 60 Jul 30 11:12 oraswgi-manage-patches
drwxr-xr-x. 5 root root 64 Jul 30 11:12 oraswgi-install
drwxr-xr-x. 6 root root 72 Jul 30 11:12 oraswgi-clone
drwxr-xr-x. 6 root root 60 Jul 30 11:12 oraswdb-manage-patches
drwxr-xr-x. 6 root root 60 Jul 30 11:12 oraswdb-install
drwxr-xr-x. 6 root root 61 Jul 30 11:12 orahost-storage
drwxr-xr-x. 4 root root 49 Jul 30 11:12 orahost-ssh
drwxr-xr-x. 5 root root 65 Jul 30 11:12 orahost-logrotate
drwxr-xr-x. 4 root root 49 Jul 30 11:12 orahost-cron
drwxr-xr-x. 7 root root 76 Jul 30 11:12 orahost
drwxr-xr-x. 5 root root 49 Jul 30 11:12 oraemagent-install
drwxr-xr-x. 5 root root 44 Jul 30 11:12 oradb-manage-users
drwxr-xr-x. 5 root root 44 Jul 30 11:12 oradb-manage-tablespace
drwxr-xr-x. 5 root root 44 Jul 30 11:12 oradb-manage-services
drwxr-xr-x. 5 root root 44 Jul 30 11:12 oradb-manage-roles
drwxr-xr-x. 5 root root 44 Jul 30 11:12 oradb-manage-redo
drwxr-xr-x. 5 root root 44 Jul 30 11:12 oradb-manage-pdb
drwxr-xr-x. 5 root root 44 Jul 30 11:12 oradb-manage-parameters
drwxr-xr-x. 5 root root 44 Jul 30 11:12 oradb-manage-grants
drwxr-xr-x. 6 root root 76 Jul 30 11:12 oradb-manage-db
drwxr-xr-x. 5 root root 49 Jul 30 11:12 oradb-failover
drwxr-xr-x. 4 root root 33 Jul 30 11:12 oradb-delete
drwxr-xr-x. 5 root root 60 Jul 30 11:12 oradb-datapatch
drwxr-xr-x. 5 root root 49 Jul 30 11:12 oradb-create
drwxr-xr-x. 4 root root 49 Jul 30 11:12 oraasm-manage-diskgroups
drwxr-xr-x. 5 root root 49 Jul 30 11:12 oraasm-createdg
drwxr-xr-x. 4 root root 33 Jul 30 11:12 cxoracle
drwxr-xr-x. 5 root root 49 Jul 30 11:12 common

[root@ansible roles]# cd oradb-create
[root@ansible oradb-create]# ls -lrt
total 4
drwxr-xr-x. 2 root root 4096 Jul 30 11:12 templates
drwxr-xr-x. 2 root root 40 Jul 30 11:12 tasks
drwxr-xr-x. 2 root root 21 Jul 30 11:12 defaults
[root@ansible oradb-create]# ls -lrt templates/
total 332
-rwxr-xr-x. 1 root root 6119 Jul 30 11:12 netca.rsp.12.2.0.1.j2
-rw-r--r--. 1 root root 6131 Jul 30 11:12 netca.rsp.12.1.0.2.j2
-rw-r--r--. 1 root root 6131 Jul 30 11:12 netca.rsp.12.1.0.1.j2
-rwxr-xr-x. 1 root root 5954 Jul 30 11:12 netca.rsp.11.2.0.4.j2
-rwxr-xr-x. 1 root root 5954 Jul 30 11:12 netca.rsp.11.2.0.3.j2
-rw-r--r--. 1 root root 1283 Jul 30 11:12 dotprofile-db.j2
-rw-r--r--. 1 root root 29254 Jul 30 11:12 dbca-create-db.rsp.12.2.0.1.j2
-rwxr-xr-x. 1 root root 78121 Jul 30 11:12 dbca-create-db.rsp.12.1.0.2.j2
-rwxr-xr-x. 1 root root 78122 Jul 30 11:12 dbca-create-db.rsp.12.1.0.1.j2
-rwxr-xr-x. 1 root root 47467 Jul 30 11:12 dbca-create-db.rsp.11.2.0.4.j2
-rwxr-xr-x. 1 root root 47394 Jul 30 11:12 dbca-create-db.rsp.11.2.0.3.j2
[root@ansible oradb-create]# ls -lrt tasks/
total 8
-rwxr-xr-x. 1 root root 4014 Jul 30 11:12 main.yml
-rw-r--r--. 1 root root 706 Jul 30 11:12 listener.yml
[root@ansible oradb-create]# ls -lrt defaults
total 8
-rw-r--r--. 1 root root 6860 Jul 30 11:12 main.yml

Roles in ansible tool :-

Roles expect files to be in certain directory names. Roles must include at least one of these directories, however it is perfectly fine to exclude any which are not being used. When in use, each directory must contain a main.yml file, which contains the relevant content:

Role directory structure :-

roles/
   common/
     tasks/
     handlers/
     files/
     templates/
     vars/
     defaults/
     meta/
   webservers/
     tasks/
     defaults/
     meta/
  • tasks – contains the main list of tasks to be executed by the role.
  • defaults – default variables for the role 
  • templates- contains templates which can be deployed via this role.

Install tree rpm to see the execution of .yml file :-

[root@ansible roles]# yum install tree
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package tree.x86_64 0:1.6.0-10.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
==========================================================================================================================
Package Arch Version Repository Size
==========================================================================================================================
Installing:
tree x86_64 1.6.0-10.el7 ol7_latest 46 k

Transaction Summary
==========================================================================================================================
Install 1 Package
Total download size: 46 k
Installed size: 83 k
Is this ok [y/d/N]: y
Downloading packages:
tree-1.6.0-10.el7.x86_64.rpm SUCCESS

Step 5:- Run the tree command and see the execution of .yml files to create a database

[root@ansible roles]# tree oraswdb-install
oraswdb-install
├── defaults
│   └── main.yml
├── meta
│   └── main.yml
├── tasks
│   ├── 11.2.0.3.yml
│   ├── 11.2.0.4.yml
│   ├── 12.1.0.1.yml
│   ├── 12.1.0.2.yml
│   ├── 12.2.0.1.yml
│   ├── 18.3.0.0.yml
│   ├── curl.yml
│   ├── get_url.yml
│   ├── init.yml
│   ├── install-home-db.yml
│   ├── main.yml
│   ├── remove-home.yml
│   └── systemd.yml
└── templates
├── db-install.rsp.11.2.0.3.j2
├── db-install.rsp.11.2.0.4.j2
├── db-install.rsp.12.1.0.1.j2
├── db-install.rsp.12.1.0.2.j2
├── db-install.rsp.12.2.0.1.j2
├── db-install.rsp.18.3.0.0.j2
├── dbora.j2
├── dotprofile-home.j2
├── glogin.sql.j2
├── oracle-rdbms-service.j2
├── oraInst.loc.j2
└── run-db-install.sh.j2

4 directories, 27 files

Step 6:- I have copied the single-instance-db-on-fs.yml file and named as dbcreation.yml as per the host entry and environment.

Change the main.yml entry present in defaults directory  as below:-

[root@ansible ~]# cd /home/ansible/Desktop/ansible-oracle-master/roles/oradb-create/defaults

The below data is used to create a new database using ansible tool.

[root@ansible ansible-oracle-master]# cat dbcreation.yml 

- name: Database Server Installation & Database Creation
hosts: db
user: oracle 
sudo: yes
roles:
- oradb-create

Step 7:- Run the playbook dbcreation.yml file to create a database.

[root@ansible ansible-oracle-master]# ansible-playbook dbcreation.yml

PLAY [Database Server Installation & Database Creation] *******************************************************

GATHERING FACTS ***************************************************************
ok: [test]

TASK: [oradb-create | set fact] ***********************************************
ok: [test] => (item={'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'})

TASK: [oradb-create | listener | Create responsefile for listener configuration] ***
ok: [test] => (item={'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'})

TASK: [oradb-create | listener | Create listener] **************************************************
ok: [test] => (item={'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'})

TASK: [oradb-create | Check if database is already created] *************************************************************
changed: [test] => (item={'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'})

TASK: [oradb-create | Copy custom dbca Templates to ORACLE_HOME/assistants/dbca/templates] ***
skipping: [test] => (item=[{'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'}])

TASK: [oradb-create | Create responsefile for dbca] ****************************************************
ok: [test] => (item=[{'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'}, {u'cmd': u'grep orcl:/u01/app/oracle/product/12.1.0/dbhome_1 /etc/oratab |wc -l', u'end': u'2018-07-30 17:41:58.213870', u'stderr': u'', u'stdout': u'0', u'changed': True, u'rc': 0, 'item': {'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'}, u'warnings': [], u'delta': u'0:00:00.060829', 'invocation': {'module_name': u'shell', 'module_args': u'grep orcl:/u01/app/oracle/product/12.1.0/dbhome_1 /etc/oratab |wc -l'}, 'stdout_lines': [u'0'], u'start': u'2018-07-30 17:41:58.153041'}])

TASK: [oradb-create | Create database(s)] ******************************************

changed: [test] => (item=[{'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'}, {u'cmd': u'grep orcl:/u01/app/oracle/product/12.1.0/dbhome_1 /etc/oratab |wc -l', u'end': u'2018-07-30 17:41:58.213870', u'stderr': u'', u'stdout': u'0', u'changed': True, u'rc': 0, 'item': {'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'}, u'warnings': [], u'delta': u'0:00:00.060829', 'invocation': {'module_name': u'shell', 'module_args': u'grep orcl:/u01/app/oracle/product/12.1.0/dbhome_1 /etc/oratab |wc -l'}, 'stdout_lines': [u'0'], u'start': u'2018-07-30 17:41:58.153041'}])

TASK: [oradb-create | debug var=item] **************************************
ok: [test] => (item=[u'Copying database files', u'1% complete', u'3% complete', u'11% complete', u'18% complete', u'26% complete', u'37% complete', u'Creating and starting Oracle instance', u'40% complete', u'45% complete', u'50% complete', u'55% complete', u'56% complete', u'60% complete', u'62% complete', u'Completing Database Creation', u'66% complete', u'70% complete', u'73% complete', u'85% complete', u'96% complete', u'100% complete', u'Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/orcl/orcl.log" for further details.']) => {
"item": [
"Copying database files",
"1% complete",
"3% complete",
"11% complete",
"18% complete",
"26% complete",
"37% complete",
"Creating and starting Oracle instance",
"40% complete",
"45% complete",
"50% complete",
"55% complete",
"56% complete",
"60% complete",
"62% complete",
"Completing Database Creation",
"66% complete",
"70% complete",
"73% complete",
"85% complete",
"96% complete",
"100% complete",
"Look at the log file \"/u01/app/oracle/cfgtoollogs/dbca/orcl/orcl.log\" for further details."
],
"var": {
"item": [
"Copying database files",
"1% complete",
"3% complete",
"11% complete",
"18% complete",
"26% complete",
"37% complete",
"Creating and starting Oracle instance",
"40% complete",
"45% complete",
"50% complete",
"55% complete",
"56% complete",
"60% complete",
"62% complete",
"Completing Database Creation",
"66% complete",
"70% complete",
"73% complete",
"85% complete",
"96% complete",
"100% complete",
"Look at the log file \"/u01/app/oracle/cfgtoollogs/dbca/orcl/orcl.log\" for further details."
]
}
}

TASK: [oradb-create | Add dotprofile (1)] *****************************************
ok: [test] => (item={'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'})

test : ok=10 changed=2 unreachable=0 failed=0

Step 8:- Check the database and listener status in remote host test.localdomain.com

[oracle@test ~]$ ps -ef|grep pmon
oracle 1943 1875 0 19:19 pts/5 00:00:00 grep --color=auto pmon
oracle 22982 1 0 Jul29 ? 00:00:04 ora_pmon_test
oracle 64776 1 0 18:15 ? 00:00:00 ora_pmon_orcl

[oracle@test ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 30-JUL-2018 19:20:35
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 26-JUL-2018 15:59:24
Uptime 4 days 3 hr. 21 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.localdomain.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=test.localdomain.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/test/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "test.localdomain.com" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testXDB.localdomain.com" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
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

Oracle Automation-Create a DBA User Using Ansible Tool

Steps to create DBA user using ansible tool :-

 

 

 

Presteps have to be done in SOURCE (ansible) server,

[root@ansible ansible-oracle-modules-master]# cat /etc/ansible/hosts

[database-servers]
test
[root@ansible ansible-oracle-modules-master]# ansible all -m ping
test | success >> {
"changed": false,
"ping": "pong"
}

Presteps have to be done in  TARGET (TEST database) server,

Step 1:- Install PIP package in TARGET side.

[root@test oracle]# curl "https://bootstrap.pypa.io/get-pip.py" -o "get-pip.py"
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 1604k 100 1604k 0 0 71531 0 0:00:22 0:00:22 --:--:-- 203k


[root@test oracle]# python get-pip.py
Collecting pip
Downloading https://files.pythonhosted.org/packages/5f/25/e52d3f31441505a5f3af41213346e5b6c221c9e086a166f3703d2ddaf940/pip-18.0-py2.py3-none-any.whl (1.3MB)
100% |████████████████████████████████| 1.3MB 164kB/s
Collecting wheel
Downloading https://files.pythonhosted.org/packages/81/30/e935244ca6165187ae8be876b6316ae201b71485538ffac1d718843025a9/wheel-0.31.1-py2.py3-none-any.whl (41kB)
100% |████████████████████████████████| 51kB 156kB/s
Installing collected packages: pip, wheel
Successfully installed pip-18.0 wheel-0.31.1

Step 2:- Install and upgrade the cx_oracle package,

[root@test oracle]# pip -V
pip 18.0 from /usr/lib/python2.7/site-packages/pip (python 2.7)

[root@test oracle]# python -m pip install cx_Oracle --upgrade
Collecting cx_Oracle
Downloading https://files.pythonhosted.org/packages/3b/09/6b10675a6db7c7da1b8d23225f0a95b2a45248c56a1e8f711d59809278d3/cx_Oracle-6.4.1-cp27-cp27mu-manylinux1_x86_64.whl (590kB)
100% |████████████████████████████████| 593kB 256kB/s
Installing collected packages: cx-Oracle
Successfully installed cx-Oracle-6.4.1

Step 3:- Import the cx_oracle package,

[root@test oracle]# python
Python 2.7.5 (default, Sep 5 2016, 02:30:38)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-9)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> exit()

SOURCE :-

Step 1:- Download oracle user from github site.Click Here

Step 2:- Move the ZIP file to ansible server.

Step 3:- Create a directory library where .yml is created.

Step 4:- Copy the oracle_user role to library directory

[root@ansible ansible-oracle-modules-master]# mkdir -p library
[root@ansible ansible-oracle-modules-master]# cp oracle_user /home/ansible/Desktop/ansible-oracle-modules-master/library/

Step 5:- Modify the variables as per your environment.

[root@ansible ansible-oracle-modules-master]# cat usercreation.yml
- hosts: database-servers
user: root
gather_facts: false

vars:
oracle_home: /u01/app/oracle/product/12.1.0/dbhome_1/
hostname: test.localdomain.com
service_name: test.localdomain.com
user: system
password: oracle
oracle_env:
ORACLE_HOME: "{{ oracle_home }}"
LD_LIBRARY_PATH: "{{ oracle_home }}/lib"

dba_user:
- schema: india
schema_password: india
state: present
default_tablespace: users
grants:
- "dba"
tasks:

- name: Create DBA user
oracle_user: hostname={{ hostname }} service_name={{ service_name }} mode=normal user={{ user }} password={{ password }} schema={{ item.0.schema }} schema_password={{ item.0.schema_password }} state={{ item.0.state }} default_tablespace={{ item.0.default_tablespace }} grants={{ item.1 }}
environment: "{{ oracle_env }}"
with_subelements:
- dba_user
- grants

In TARGET side,check the user in TEST database

Step 6:- Run the playbook file usercreation.yml

Output:-

[root@ansible ansible-oracle-modules-master]# ansible-playbook usercreation.yml

PLAY [database-servers] *******************************************************

TASK: [Create DBA user] *******************************************************
changed: [test] => (item=({'schema_password': 'india', 'state': 'present', 'default_tablespace': 'users', 'schema': 'india'}, 'dba'))

PLAY RECAP ********************************************************************
test : ok=1 changed=1 unreachable=0 failed=0

TARGET :-

Step 7 :- Check the user INDIA is created in TARGET database.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
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

Installation and Configuration of Ansible

Installation and Configuration of Ansible

Introduction to 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 : minimal, consistent, secure, highly reliable, and easy to learn.Ansible is Written on Python language.It also available as Open Source.

Why to use Ansible as automation tool ?

  • There are many other IT automation tools available, including more mature ones like Puppet and Chef, so why would we choose Ansible? The main reason is simplicity.
  • Ansible is using YAML syntax. It’s simple configuration language.
    Puppet and Chef are using Ruby Syntax. which is more difficult to learn.
  • Ansible is an agentless tool.Instead of install an agent on every target machine, Ansible just requires that target systems have Python (on Linux servers) or PowerShell (on Windows servers) and SSH.But puppet and chef have to install agent on each target machines.

Architecture Diagram :-

High Level Steps :-

  • Install Python software
  • Install PIP to install Cx_Oracle package to perform SQL commands
  • Install ansible rpm
  • Configure SSH to connect remote hosts
  • Run the ansible command to access remote hosts.

Step 1:- Check the Current version of Python in OEL 7:-

[root@ansible ~]$ python -V
Python 2.7.5

Note:- Minimum version of python 2.7.6 is needed to install ansible.

Step 2:- Download the Python 2.7.6 package

[root@ansible ansible]# wget -c https://www.python.org/ftp/python/2.7.6/Python-2.7.6.tgz --no-check-certificate
--2018-07-19 02:16:25-- https://www.python.org/ftp/python/2.7.6/Python-2.7.6.tgz
Resolving www.python.org (www.python.org)... 151.101.184.223, 2a04:4e42:2c::223
Connecting to www.python.org (www.python.org)|151.101.184.223|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 14725931 (14M) [application/octet-stream]
Saving to: ‘Python-2.7.6.tgz’

100%[============================================================================================================>] 14,725,931 240KB/s in 65s

2018-07-19 02:17:32 (221 KB/s) - ‘Python-2.7.6.tgz’ saved [14725931/14725931]


Step 3:- Unzip the downloaded python package

[root@ansible ~]$ tar -xvzf Python-2.7.6.tgz 

[root@ansible ~]$ cd Python-2.7.6

[ansible@ansible Python-2.7.6]$ ls -lrt
total 956
-rw-r--r--. 1 ansible ansible 12749 Nov 10 2013 LICENSE
drwxr-xr-x. 2 ansible ansible 4096 Nov 10 2013 Include
drwxr-xr-x. 2 ansible ansible 20 Nov 10 2013 Grammar
drwxr-xr-x. 22 ansible ansible 4096 Nov 10 2013 Demo
drwxr-xr-x. 46 ansible ansible 8192 Nov 10 2013 Lib
drwxr-xr-x. 22 ansible ansible 4096 Nov 10 2013 Tools
-rw-r--r--. 1 ansible ansible 97308 Nov 10 2013 setup.py
drwxr-xr-x. 5 ansible ansible 4096 Nov 10 2013 RISCOS
-rw-r--r--. 1 ansible ansible 53972 Nov 10 2013 README
drwxr-xr-x. 2 ansible ansible 4096 Nov 10 2013 Python
-rw-r--r--. 1 ansible ansible 34829 Nov 10 2013 pyconfig.h.in
drwxr-xr-x. 2 ansible ansible 4096 Nov 10 2013 PCbuild
drwxr-xr-x. 10 ansible ansible 4096 Nov 10 2013 PC
drwxr-xr-x. 2 ansible ansible 4096 Nov 10 2013 Parser
drwxr-xr-x. 3 ansible ansible 4096 Nov 10 2013 Objects
drwxr-xr-x. 9 ansible ansible 4096 Nov 10 2013 Modules
drwxr-xr-x. 5 ansible ansible 4096 Nov 10 2013 Misc
-rw-r--r--. 1 ansible ansible 44172 Nov 10 2013 Makefile.pre.in
drwxr-xr-x. 11 ansible ansible 4096 Nov 10 2013 Mac
-rwxr-xr-x. 1 ansible ansible 7122 Nov 10 2013 install-sh
-rw-r--r--. 1 ansible ansible 133573 Nov 10 2013 configure.ac
-rwxr-xr-x. 1 ansible ansible 426595 Nov 10 2013 configure
-rwxr-xr-x. 1 ansible ansible 35639 Nov 10 2013 config.sub
-rwxr-xr-x. 1 ansible ansible 44851 Nov 10 2013 config.guess
drwxr-xr-x. 16 ansible ansible 4096 Nov 10 2013 Doc

Step 4:- Configuring python 2.7.6 software

Create the below directory ‘/home/ansible/python’ and configure,install the software.

[ansible@ansible Python-2.7.6]# ./configure --prefix=/home/ansible/python

[ansible@ansible Python-2.7.6]# make

[ansible@ansible Python-2.7.6]# make install

After installation,the following files are created under ‘/home/ansible/python’

[ansible@ansible Python-2.7.6]# ls -lrt /home/ansible/python
total 4
drwxr-xr-x. 3 ansible ansible 22 Jul 19 21:10 include
drwxr-xr-x. 4 ansible ansible 59 Jul 19 21:10 lib
drwxr-xr-x. 3 ansible ansible 16 Jul 19 21:10 share
drwxr-xr-x. 2 ansible ansible 4096 Jul 19 21:10 bin

Now,check the new python version 2.7.6

[root@ansible ~]$ cd python/bin/
[root@ansible bin]$ python -V
Python 2.7.6

[root@ansible bin]# which python
/home/ansible/bin/python

Step 5:- Download PIP package

[root@ansible ~]$ curl "https://bootstrap.pypa.io/get-pip.py" -o "get-pip.py"
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0
100 1603k 100 1603k 0 0 164k 0 0:00:09 0:00:09 --:--:-- 222k

[root@ansible ansible]# python get-pip.py
Collecting pip
Downloading https://files.pythonhosted.org/packages/0f/74/ecd13431bcc456ed390b44c8a6e917c1820365cbebcb6a8974d1cd045ab4/pip-10.0.1-py2.py3-none-any.whl (1.3MB)
100% |████████████████████████████████| 1.3MB 709kB/s
Collecting wheel
Downloading https://files.pythonhosted.org/packages/81/30/e935244ca6165187ae8be876b6316ae201b71485538ffac1d718843025a9/wheel-0.31.1-py2.py3-none-any.whl (41kB)
100% |████████████████████████████████| 51kB 1.2MB/s
Installing collected packages: pip, wheel
Successfully installed pip-10.0.1 wheel-0.31.1

[root@ansible ansible]# python -m pip install cx_Oracle --upgrade
Collecting cx_Oracle
Downloading https://files.pythonhosted.org/packages/3b/09/6b10675a6db7c7da1b8d23225f0a95b2a45248c56a1e8f711d59809278d3/cx_Oracle-6.4.1-cp27-cp27mu-manylinux1_x86_64.whl (590kB)
100% |████████████████████████████████| 593kB 1.2MB/s
Installing collected packages: cx-Oracle
Successfully installed cx-Oracle-6.4.1

Check the PIP version :-

[root@ansible ansible]# pip -V
pip 10.0.1 from /usr/lib/python2.7/site-packages/pip (python 2.7)

We need the Cx_Oracle Package for executing SQL’s , performing any task inside SQL prompt.

[root@ansible ansible]# python
Python 2.7.5 (default, Sep 5 2016, 02:30:38)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-9)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> exit()

Installation of Ansible software :-

Step 6:-To get Ansible for Linux, first ensure that the RHEL7 EPEL repository is installed:

[root@ansible ~]# rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
Retrieving https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
warning: /var/tmp/rpm-tmp.dwLJVq: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:epel-release-7-11 ################################# [100%]

Step 7:- Once the repository is installed, install Ansible with yum:

[root@ansible ~]# yum install ansible

Step 8:- Check the ansible version and configuration file path

[root@ansible ~]$ ansible --version
ansible 2.6.1
config file = /etc/ansible/ansible.cfg
configured module search path = [u'/home/ansible/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
ansible python module location = /usr/lib/python2.7/site-packages/ansible
executable location = /bin/ansible
python version = 2.7.5 (default, Sep 5 2016, 02:30:38) [GCC 4.8.5 20150623 (Red Hat 4.8.5-9)]

Step 9:- Configuring ansible hosts and create inventory file.

The ‘databaseservers‘ in the brackets indicates as group names to list number of remote hosts.

[root@ansible ~]# vi /etc/ansible/hosts

Added the below remote host entry to the /etc/ansible/hosts file

[database-servers]
demo.localdomain.com

Step 10:- List the remote hosts which are able to use ansible commands

[root@ansible ~]$ ansible --list-host all
hosts (1):
192.168.86.141

[ansible@ansible ~]$ ansible --list-host database-servers
hosts (1):
192.168.86.141

SSH Key creation :-

Step 11:- Create the SSH user equivalence in the ansible server.

[ansible@ansible ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/ansible/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/ansible/.ssh/id_rsa.
Your public key has been saved in /home/ansible/.ssh/id_rsa.pub.
The key fingerprint is:
3f:58:1b:ca:6c:2a:66:0d:63:ea:c3:18:a3:45:45:43 ansible@ansible.localdomain.com
The key's randomart image is:
+--[ RSA 2048]----+
| oE |
| .. |
| . |
| . |
| . S o |
|o . + o = o |
|.* o + * + |
|o + + .o . |
| ..+ .. |
+-----------------+

[ansible@ansible ~]$ cd .ssh/
[ansible@ansible .ssh]$ ls -lrt
total 8
-rw-r--r--. 1 ansible ansible 413 Jul 20 19:40 id_rsa.pub
-rw-------. 1 ansible ansible 1675 Jul 20 19:40 id_rsa

Step 12:- Copy the SSH key to the remote servers

[root@ansible .ssh]# ssh-copy-id root@demo.localdomain.com
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@demo.localdomain.com's password:

Number of key(s) added: 1

Now try logging into the machine, with: "ssh 'root@demo.localdomain.com'"
and check to make sure that only the key(s) you wanted were added.

Step 13:- Now,we are able to ping the remote hosts,

[root@ansible .ssh]# ansible all -m ping
demo.localdomain.com | SUCCESS => {
"changed": false,
"ping": "pong"

Step 14 :- To check the partitions on all remote hosts

[root@ansible ~]# ansible -m command -a "df -h" database-servers
demo.localdomain.com | SUCCESS | rc=0 >>
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_rac1-lv_root
36G 19G 16G 55% /
tmpfs 2.0G 276K 2.0G 1% /dev/shm
/dev/sda1 485M 82M 378M 18% /boot

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
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