Getting started with ansible-oracle

I thought I’d write a quick post on how to get started with ansible-oracle.

The reason I decided to use roles when putting this together was to make it easily reusable, and to be able to pick and choose which roles you want to use. If you want to do everything from the ground up you can, and also if you already have a properly configured server and just want to install Oracle and create a single instance database on a filesystem you can absolutely do just that, by using just the oraswdb-install and oradb-create roles.

So, we’re going to do both. And we’re just going to go with the defaults and create a single instance database with datafiles on a filesystem.

Note: The installation will be without a configured listener. I have not gotten around to fixing the listener issue with installations without GI.

1. Configuring the host + installing the database server & creating a database

First off, we’re going to take a newly installed machine and configure it from ground up.

  • Oracle version 12.1.0.2
  • ORACLE_HOME will be /u01/app/oracle/12.1.0.2/orcl
  • One database will be created, ‘orcl’
  • Datafiles/fra will reside in /u01/oradata & /u01/fra respectively. The u01 directory is created by Ansible and oradata + fra will be created by dbca
  • The Oracle software (linuxamd64_12102_database_1of2.zip, linuxamd64_12102_database_2of2.zip) has been downloaded and the files are placed in /tmp on the control-machine.

And now, on to the good stuff.

Clone the repository

[miksan@ponderstibbons git]$ git clone https://github.com/oravirt/ansible-oracle.git
Cloning into 'ansible-oracle'...
remote: Counting objects: 642, done.
remote: Compressing objects: 100% (21/21), done.
remote: Total 642 (delta 6), reused 0 (delta 0)
Receiving objects: 100% (642/642), 185.27 KiB | 287.00 KiB/s, done.
Resolving deltas: 100% (257/257), done.
[miksan@ponderstibbons git]$

Add the group & host to the inventory.

I’m going to call the hostgroup ‘mygroup’

[miksan@ponderstibbons git]$ cd ansible-oracle/
[miksan@ponderstibbons ansible-oracle]$ vi inventory/hosts
[miksan@ponderstibbons ansible-oracle]$ cat inventory/hosts 
[mygroup]
oradb03
[miksan@ponderstibbons ansible-oracle]$

Create the playbook

So we’re going to add the follwing roles to the play

  • common – this just installs a few packages I’d like to have available (like collectl, rlwrap). Needs to run as root
  • orahost – this will configure the user, kernel settings, create directories etc. Needs to run as root
  • oraswdb-install – this installs the server software
  • oradb-create – this creates the database
[miksan@ponderstibbons ansible-oracle]$ vi my-single-instance-install.yml
[miksan@ponderstibbons ansible-oracle]$ cat my-single-instance-install.yml
--- 
 - name: Host configuration
   hosts: mygroup
   user: ansible
   sudo: yes
   roles:
    - common
    - orahost
 - name: Oracle Database server installation & Database Creation 
   hosts: mygroup
   user: ansible
   sudo: yes 
   sudo_user: oracle 
   roles: 
    - oraswdb-install 
    - oradb-create

Run the playbook

[miksan@ponderstibbons ansible-oracle]$ time ansible-playbook my-single-instance-install.yml -i inventory/hosts

PLAY [Host configuration] *****************************************************

GATHERING FACTS *************************************************************** 
The authenticity of host 'oradb03 (192.168.0.53)' can't be established.
RSA key fingerprint is 2c:f8:0a:00:61:ee:81:5a:d8:16:e6:78:e2:c3:a2:4b. 
Are you sure you want to continue connecting (yes/no)? yes 
ok: [oradb03] 
 
TASK: [common | Install EPEL Repo] ******************************************** 
ok: [oradb03] 
 
TASK: [common | Get newest repo-file for OL6 (public-yum)] ******************** 
ok: [oradb03] 
 
TASK: [common | Install common packages] ************************************** 
changed: [oradb03] => (item=screen,facter,procps,module-init-tools,ethtool,bc,bind-utils,nfs-utils,make,sysstat,openssh-clients,compat-libcap1,twm,collectl,rlwrap,tigervnc-server,ntp,expect,git) 
 
TASK: [common | Start and enable NTP] ***************************************** 
ok: [oradb03] 
 
TASK: [orahost | User | Add group(s)] ***************************************** 
changed: [oradb03] 
 
TASK: [orahost | User | Add Oracle user] ************************************** 
changed: [oradb03]

......SKIP.....
.
.
.
 "stdout": "Copying database files\n1% complete\n3% complete\n11% complete\n18% complete\n37% complete\nCreating and starting Oracle instance\n40% complete\n45% complete\n50% complete\n55% complete\n56% complete\n60% complete\n62% complete\nCompleting Database Creation\n66% complete\n70% complete\n73% complete\n85% complete\n96% complete\n100% complete\nLook at the log file \"/u01/app/oracle/cfgtoollogs/dbca/orcl/orcl.log\" for further details."
 }, 
 "oradbcreate.stdout_lines": "{{ oradbcreate.stdout_lines }}"
}

TASK: [oradb-create | Check if database is registered] ************************ 
skipping: [oradb03]

TASK: [oradb-create | debug var=srvctlconfig.stdout_lines] ******************** 
skipping: [oradb03]

PLAY RECAP ******************************************************************** 
oradb03 : ok=56 changed=36 unreachable=0 failed=0 

real 18m46.308s
user 0m1.897s
sys 0m1.187s

2. Installing the database server & creating a database

Assumptions

  • The user is oracle and its primary group is dba
  • All prereqs are taken care of (kernel-settings, packages etc)
  • Oracle version 12.1.0.2
  • ORACLE_HOME will be /u01/app/oracle/12.1.0.2/orcl
  • One database will be created, ‘orcl’
  • Datafiles/fra will reside in /u01/oradata & /u01/fra respectively
  • The Oracle software (linuxamd64_12102_database_1of2.zip, linuxamd64_12102_database_2of2.zip) has been downloaded and the files are placed in /tmp on your control-machine.

So you also have to make sure that you have:

  • Created the /u01 directory with the proper permissions (oracle:dba)

Create the playbook

This time we’re getting rid of the host-config roles and we’ll just use the database specific roles

[miksan@ponderstibbons ansible-oracle]$ vi my-single-instance-install.yml
[miksan@ponderstibbons ansible-oracle]$ cat my-single-instance-install.yml
--- 
 - name: Oracle Database server installation & Database Creation 
   hosts: mygroup
   user: ansible
   sudo: yes 
   sudo_user: oracle 
   roles: 
    - oraswdb-install 
    - oradb-create

Run the playbook

[miksan@ponderstibbons ansible-oracle]$ time ansible-playbook my-single-instance-install.yml -i inventory/hosts 

PLAY [Oracle Database server installation & Database Creation] **************** 
GATHERING FACTS *************************************************************** 
ok: [oradb03]

TASK: [oraswdb-install | Check if DB-server is already installed] ************* 
changed: [oradb03] => (item={'key': 'orcl', 'value': {'oracle_init_params': 'open_cursors=300,processes=700', 'delete_db': False, 'is_racone': 'false', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'oracle_db_name': 'orcl', 'redolog_size_in_mb': 100, 'storage_type': 'FS', 'oracle_edition': 'EE', 'oracle_version_db': '12.1.0.2', 'oracle_database_type': 'MULTIPURPOSE', 'oracle_db_passwd': 'Oracle123', 'is_container': 'false', 'oracle_db_mem_percent': 30}})

TASK: [oraswdb-install | Add new dotprofile (DB)] ***************************** 
ok: [oradb03] => (item={'key': 'orcl', 'value': {'oracle_init_params': 'open_cursors=300,processes=700', 'delete_db': False, 'is_racone': 'false', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'oracle_db_name': 'orcl', 'redolog_size_in_mb': 100, 'storage_type': 'FS', 'oracle_edition': 'EE', 'oracle_version_db': '12.1.0.2', 'oracle_database_type': 'MULTIPURPOSE', 'oracle_db_passwd': 'Oracle123', 'is_container': 'false', 'oracle_db_mem_percent': 30}})

TASK: [oraswdb-install | Add oraInst.loc] ************************************* 
ok: [oradb03]

TASK: [oraswdb-install | Create stage directory (base)] *********************** 
changed: [oradb03]

TASK: [oraswdb-install | Create stage directory (version specific)] *********** 
changed: [oradb03] => (item={'version': '12.1.0.2', 'filename': 'linuxamd64_12102_database_1of2.zip'})
ok: [oradb03] => (item={'version': '12.1.0.2', 'filename': 'linuxamd64_12102_database_2of2.zip'})

TASK: [oraswdb-install | Create rsp stage directory] ************************** 
changed: [oradb03]

TASK: [oraswdb-install | Create ORACLE_INVENTORY directory] ******************* 
changed: [oradb03]

TASK: [oraswdb-install | Create ORACLE_BASE directory] ************************ 
changed: [oradb03]

TASK: [oraswdb-install | Create ORACLE_HOME directory] ************************ 
changed: [oradb03] => (item={'key': 'orcl', 'value': {'oracle_init_params': 'open_cursors=300,processes=700', 'delete_db': False, 'is_racone': 'false', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'oracle_db_name': 'orcl', 'redolog_size_in_mb': 100, 'storage_type': 'FS', 'oracle_edition': 'EE', 'oracle_version_db': '12.1.0.2', 'oracle_database_type': 'MULTIPURPOSE', 'oracle_db_passwd': 'Oracle123', 'is_container': 'false', 'oracle_db_mem_percent': 30}})

TASK: [oraswdb-install | Transfer oracle installfiles to server (www)] ******** 
skipping: [oradb03] => (item={'version': '12.1.0.2', 'filename': 'linuxamd64_12102_database_1of2.zip'})
skipping: [oradb03] => (item={'version': '12.1.0.2', 'filename': 'linuxamd64_12102_database_2of2.zip'})

TASK: [oraswdb-install | Transfer oracle installfiles to server (local)] ****** 
changed: [oradb03] => (item={'version': '12.1.0.2', 'filename': 'linuxamd64_12102_database_1of2.zip'})
changed: [oradb03] => (item={'version': '12.1.0.2', 'filename': 'linuxamd64_12102_database_2of2.zip'})

TASK: [oraswdb-install | Extract files to stage-area] ************************* 
changed: [oradb03] => (item={'version': '12.1.0.2', 'filename': 'linuxamd64_12102_database_1of2.zip'})
changed: [oradb03] => (item={'version': '12.1.0.2', 'filename': 'linuxamd64_12102_database_2of2.zip'})

... SKIP ...
.
.
.

 "stdout": "Copying database files\n1% complete\n3% complete\n11% complete\n18% complete\n37% complete\nCreating and starting Oracle instance\n40% complete\n45% complete\n50% complete\n55% complete\n56% complete\n60% complete\n62% complete\nCompleting Database Creation\n66% complete\n70% complete\n73% complete\n85% complete\n96% complete\n100% complete\nLook at the log file \"/u01/app/oracle/cfgtoollogs/dbca/orcl/orcl.log\" for further details."
 }, 
 "oradbcreate.stdout_lines": "{{ oradbcreate.stdout_lines }}"
}

TASK: [oradb-create | Check if database is registered] ************************ 
skipping: [oradb03]

TASK: [oradb-create | debug var=srvctlconfig.stdout_lines] ******************** 
skipping: [oradb03]

PLAY RECAP ******************************************************************** 
oradb03 : ok=25 changed=19 unreachable=0 failed=0 


real 11m4.946s
user 0m0.965s
sys 0m0.507s

And after a while you’ll have yourself a database installed.

miksan@ponderstibbons ansible-oracle]$ ansible oradb03 -m shell-a "ps -fu oracle"
oradb03 | success | rc=0 >>
UID PID PPID C STIME TTY TIME CMD
oracle 10669 1 0 14:04 ? 00:00:01 ora_pmon_orcl
oracle 10671 1 0 14:04 ? 00:00:03 ora_psp0_orcl
oracle 10673 1 2 14:04 ? 00:05:40 ora_vktm_orcl
oracle 10677 1 0 14:04 ? 00:00:00 ora_gen0_orcl
oracle 10679 1 0 14:04 ? 00:00:00 ora_mman_orcl
oracle 10683 1 0 14:04 ? 00:00:00 ora_diag_orcl
oracle 10685 1 0 14:04 ? 00:00:04 ora_dbrm_orcl
oracle 10687 1 0 14:04 ? 00:00:00 ora_vkrm_orcl
oracle 10689 1 0 14:04 ? 00:00:29 ora_dia0_orcl
oracle 10691 1 0 14:04 ? 00:00:01 ora_dbw0_orcl
oracle 10693 1 0 14:04 ? 00:00:00 ora_lgwr_orcl
oracle 10695 1 0 14:04 ? 00:00:05 ora_ckpt_orcl
oracle 10697 1 0 14:04 ? 00:00:00 ora_lg00_orcl
oracle 10699 1 0 14:04 ? 00:00:00 ora_smon_orcl
oracle 10701 1 0 14:04 ? 00:00:00 ora_lg01_orcl
oracle 10703 1 0 14:04 ? 00:00:00 ora_reco_orcl
oracle 10705 1 0 14:04 ? 00:00:00 ora_lreg_orcl
oracle 10707 1 0 14:04 ? 00:00:00 ora_pxmn_orcl
oracle 10709 1 0 14:04 ? 00:00:21 ora_mmon_orcl
oracle 10711 1 0 14:04 ? 00:00:18 ora_mmnl_orcl
oracle 10713 1 0 14:04 ? 00:00:00 ora_d000_orcl
oracle 10715 1 0 14:04 ? 00:00:00 ora_s000_orcl
oracle 10733 1 0 14:05 ? 00:00:00 ora_tmon_orcl
oracle 10735 1 0 14:05 ? 00:00:00 ora_tt00_orcl
oracle 10741 1 0 14:05 ? 00:00:01 ora_smco_orcl
oracle 10850 1 0 14:05 ? 00:00:00 ora_aqpc_orcl
oracle 10857 1 0 14:05 ? 00:00:00 ora_p000_orcl
oracle 10859 1 0 14:05 ? 00:00:00 ora_p001_orcl
oracle 10861 1 0 14:05 ? 00:00:00 ora_p002_orcl
oracle 10863 1 0 14:05 ? 00:00:00 ora_p003_orcl
oracle 10865 1 0 14:05 ? 00:00:00 ora_p004_orcl
oracle 10867 1 0 14:05 ? 00:00:00 ora_p005_orcl
oracle 10869 1 0 14:05 ? 00:00:00 ora_p006_orcl
oracle 10871 1 0 14:05 ? 00:00:00 ora_p007_orcl
oracle 10873 1 0 14:05 ? 00:00:04 ora_cjq0_orcl
oracle 11062 1 0 14:05 ? 00:00:00 ora_qm02_orcl
oracle 11068 1 0 14:05 ? 00:00:00 ora_q003_orcl
oracle 11070 1 0 14:05 ? 00:00:00 ora_q004_orcl
oracle 33056 1 0 18:05 ? 00:00:00 ora_w003_orcl
oracle 33074 1 0 18:05 ? 00:00:00 ora_w000_orcl

[miksan@ponderstibbons ansible-oracle]$]

And there it is.

Next time we’ll set up a single instance with ASM storage, and also take a look at how we tweak parameters without altering the files with the default settings.

Advertisements

29 thoughts on “Getting started with ansible-oracle

  1. Pingback: Ansible-oracle, the next step | oravirt

  2. Pingback: ansible-oracle, the RAC edition | oravirt

  3. Hello Miakel, I am trying to create single instance database but it is failing with following error. FAILED! => {“failed”: true, “msg”: “The conditional check ‘master_node and item.1.stdout != \”1\”‘ failed

      • Thanks a lot Micke for yoru quick reply. Basically, I separated out the oracle software install and DB creation process in two different steps.after some struggle i was able to test the installation process. now i am trying to create database on remote server and it is failing at TASK [oradb-create : Create responsefile for dbca] ***************************** with ERROR fatal: [10.25.13.94]: FAILED! => {“failed”: true, “msg”: “The conditional check ‘master_node and item.1.stdout != \”1\”‘ failed. The error was: error while evaluating conditional (master_node and item.1.stdout != \”1\”):

    • Ok, looks like there is a missing default in the oradb-create role. I’ll fix that asap.
      In the meantime, try adding: -e master_node=true on the command line as part of your ansible-playbook command.
      Let me know how that goes.

      /M

  4. Thanks Micke, I moved forward with the original error by adding master_node: true in group_vars. I also tried adding -e in the command line but it is failing at the creating response file. with following error:
    “warnings”: []}], “msg”: “AnsibleUndefinedVariable: {{ hostgroup }}-hub: ‘hostgroup’ is undefined”}

    Do you mind if you send you few questions on your email id? I need some clarification w.r.t changes i have to make in the configuration file to accommodate my environment?

  5. Looks like this error is related to variables defined in default main.yml.
    hostgroup_hub: “{{ hostgroup }}-hub”
    hostgroup_leaf: “{{ hostgroup }}-leaf”

    After reading your blog it looks to me this is related flex cluster deployment however in my case i am trying to install single instance stand alone database. Now sure if i can comment it out.

    • Thanks a lot again. I will send that in moment. In the mean time could you please throw some light on the above error? Should i remove those variables since i am testing on non-rac system.

  6. I removed those two variables from default main.yml and i got the sample database created on the remote system. still few steps are failing but now i am getting the handle of this. I will send you few questions / suggestion through email. But it’s indeed excellent piece of work.

  7. Great!

    However, you shouldn’t really have to remove variables. But I’m guessing this is because you’ve broken out part of the roles to a separate playbook and apparently I don’t have all defaults set for each role and that is why you’re getting these errors.
    What you should do though is have a group_vars/ set up with the defaults you want for your config which would override the role defaults. And then you would run the playbook using those defaults.

    Not having the necessary defaults in each role is a bug though, and I will fix that.

    Thanks!
    /M

  8. Thanks for clarifying. I had to break the roles because i am trying to use this first time os debugging will become easy and second is i had dependency on the root where i don’t have sudo to root privilege. By breaking up this i can get the software installed independently and then run the root manually and create the database. I am struggling to get this completed successfully as few steps are still failing particularly the dotprofile task.

  9. Hi Micke,

    Finally i got clean software installation and also got sample database created on one of the remote system:
    I need small clarification related to database parameter. Is there any provision to set the database block size, redo log in separate mount points and disable the FRA?
    I have a situation where i need to create database with 16K block size, place the redo log in separate directories /redo1 and /redo2 and not using FRA. I couldn’t find any variables in the default main.yml file

    Raj

    • Awesome!
      I’ve also fixed the missing default parameters in oradb-create.

      At the moment I only use the ‘seed-template’ way of creating the db using dbca, so unfortunately there is no way of specifying different mount points specifically for redo or setting a different blocksize (unless I’ve missed something in the template parameters).
      When I get the time i will also make sure it is possible to use non-seed templates.

      As for disabling the FRA I guess you could try to set the parameters in oracle_databases.oracle_init_params and see where that takes you. My experience is that setting parameters as part of the installation doesn’t work very well though, a lot of parameters are just silently ignored.

      I usually use the modules I’ve written (https://github.com/oravirt/ansible-oracle-modules) to customise the database (initparams, tablespaces, services etc) after initial creation.

      regards
      /Micke

  10. Thanks Micke for fixing this. I am testing another role i.e.oradb-delete and running into issue again with error FAILED! => {“failed”: true, “msg”: “‘oracle_databases’ is undefined”}

    I tried to define oracle_databases name in group_vars but its not working. There should be some way to pass the oracle_database name which i want to delete.

  11. No problem!

    I’ve added the oracle_databases list to the default variables of oradb-delete, and it uses the same parameters as the default for oradb-create, but has the state set to ‘absent’ instead of present.

    You really should set up your own ‘group_vars/your-specific-config’ and call that from the playbook. It gets messy when trying to alter the role defaults in the /defaults/main.yml file, and they’re not really meant to be changed but rather be over-ridden by group_vars/host_vars

    You can pass the oracle_databases list as a json structure on the commandline, but that is sort of messy as well

    • Thanks Micke it did work and I got the database deleted. By the way, Can i define the oracle_databases list as a json structure in the group_vars/host_vars ? I am sorry i may be asking very silly question as i am not very well versed with Python/ansible. I am in very initial stage and trying to learn both technologies. Another question is what is the significance of state = absent in the main.yml ?

  12. Ask away, I’m happy to help!

    What you would do is add the oracle_databases list (in a yaml format, as it looks in the defaults/main.yml) file to your group_vars/yourconfig. You could add it as a json structure but there is no need for that.

    The state=present/absent is there to define the state of the database, i.e should it be created or deleted. In the oradb-delete role I only delete databases which have state=absent, and in oradb-create I only create databases which have state=present.

  13. Thanks Micke, I am trying to test ansible-oracle-modules. I have installed cx_oracle and wrote few small programs to test sql query. However I am unable to use ansible_oracle_modules. I am trying to create user on remote database using oracle_grants. Do I need to write playbook to use this module? Appreciate if you can give give small example to use the module. I have already downloaded and also created library directory but unable to move forward.

    • You don’t necessarily have to write a playbook to use the modules but personally I think that is the best way of doing it.
      There is a ‘test-modules.yml’ playbook that you could use as a base for what it is you’re trying to do.
      That playbook doesn’t cover all modules but it should give you an idea on how to proceed.

      Also, you’d use oracle_user to create the user and then oracle_grants to assign grants. However, the oracle_grants module currently only handles system grants and not object grants. Will be fixed when I get the time.

  14. Hello Micke,

    Wish you a very happy and prosperous new year 2017 to you and your family!!! Although this is not related to any of the oracle module however I am trying to run simple sql script on remote machine but it is failing as it is not able to get the environment variables.Have you ever seen this error? “stderr”: “/bin/sh: sqlplus: command not found”, “stdout”: “”, “stdout_lines”: [], “warnings”: []}

    When i login to the remote machine as oracle user i can invoke the surplus without any issue but when i try to shell: “sqlplus / as sysdba @test.sql through playbook it is not executing the default environment variables as a result it is failing.

    Raj

  15. Hi,

    Thank you, and the same to you and your family!

    Yes, you’ll need to use the ‘environment’ keyword in your playbook (http://docs.ansible.com/ansible/playbooks_environment.html).
    So you basically set ORACLE_HOME (and any other variable you want to) and then you reference that in your sqlplus task via ‘environment’.
    I use ‘environment’ in my cxoracle role (https://github.com/oravirt/ansible-oracle/blob/master/roles/cxoracle/tasks/main.yml)

    /M

  16. Hello Micke
    I have question related to oracle modules.
    I have been testing this on and off for long time and it works for me however i wanted to tweak little bit some of the settings. Followings settings are defined in /roles/oraswdb-install/defaults. Can we override these settings without modifying in the main.yml.?

    My second question around user named ansible and sudo. I have oracle user which has certain task to execute but not all command can be executed so in this case should i set sudo to no and run the playbook? What is the purpsoe of ansible user

    oracle_databases:
    – home: db_1
    oracle_version_db: 11.2.0.4
    oracle_edition: SE
    oracle_db_name: TEST
    oracle_db_type: SI

  17. Hi,
    Glad to hear that it works!

    Yes, you can easily override the defaults. You just need to create a file under group_vars/ (e.g group_vars/raj-hostgroup).
    Whatever is in that file overrides all the defaults for all roles. (An example config can be found in https://github.com/oravirt/ansible-oracle/blob/master/group_vars/vbox-si-fs)
    Also, make sure that the group under which your host (or hosts) is located in the inventory matches the group_vars/raj-hostgroup
    i.e
    [raj-hostgroup]
    host1
    host2
    etc….

    So the easiest way to override a default structure (e.g oracle_databases) is to copy the structure from the defaults/main.yml to your group_vars config, and then modify it to suit your needs.

    As for the Ansible user, that is the deploy-user I use. It doesn’t have to be a user called ansible, it just needs to be a user that have sudo permissions on the target host(s).

    So the playbook still needs to have sudo: true, but you can use any user you want (as long as it actually can sudo)

    regards
    /M

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s