oravirt/ansible-oracle-modules

Oracle modules for Ansible

ansible-oracle-modules

Oracle modules for Ansible

• If you have any questions/requests just create an issue and I'll look into it
• I've also included a playbook (test-modules.yml) that'll give you an idea on how the modules can be used.

To use the modules, create a 'library' directory next to your top level playbooks and put the different modules in that directory. Then just reference them as you would any other module. For more information, check out: http://docs.ansible.com/developing_modules.html

Most (if not all) requires cx_Oracle either on your controlmachine or on the managed node.

The default behaviour for the modules using cx_Oracle is this:

• If neither username or password is passed as input to the module(s), the use of an Oracle wallet is assumed.
• In that case, the cx_Oracle.makedsn step is skipped, and the connection will use the '/@<service_name>' format instead.
• You then need to make sure that you're using the correct tns-entry (service_name) to match the credential stored in the wallet.

These are the different modules:

oracle_user

pre-req: cx_Oracle

• Creates & drops a user.
• Grants privileges only (can not remove them with oracle_user, use oracle_grants for that)

oracle_tablespace

pre-req: cx_Oracle

• Manages normal(permanent), temp & undo tablespaces (create, drop, make read only/read write, offline/online)
• Tablespaces can be created as bigfile, autoextended

oracle_grants

pre-req: cx_Oracle

• Manages privileges for a user
• Grants/revokes privileges
• Handles roles/sys privileges properly. Does NOT yet handle object privs. They can be added but they are not considered while revoking privileges
• The grants can be added as a string (dba,'select any dictionary','create any table'), or in a list (ie.g for use with with_items)

oracle_role

pre-req: cx_Oracle

• Manages roles in the database

oracle_parameter

pre-req: cx_Oracle

• Manages init parameters in the database (i.e alter system set parameter...)
• Also handles underscore parameters. That will require using mode=sysdba, to be able to read the X$ tables needed to verify the existence of the parameter.

Note: When specifying sga-parameters the database requests memory based on granules which are variable in size depending on the size requested, and that means the database may round the requested value to the nearest multiple of a granule. e.g sga_max_size=1500M will be rounded up to 1504 (which is 94 granules of 16MB). That will cause the displayed value to be 1504M, which has the effect that the next time the module is is run with a desired value of 1500M it will be changed again. So that is something to consider when setting parameters that affects the SGA.

oracle_services

pre-req: cx_Oracle (if GI is not running)

• Manages services in an Oracle database (RAC/Single instance)

Note: At the moment, Idempotence only applies to the state (present,absent,started, stopped). No other options are considered.

oracle_pdb

pre-req: cx_Oracle

• Manages pluggable databases in an Oracle container database
• Creates/deletes/opens/closes the pdb
• saves the state if you want it to. Default is yes
• Can place the datafiles in a separate location

oracle_sql

pre-req: cx_Oracle

• 2 modes: sql or script
• Executes arbitrary sql or runs a script

Note: Should be considered as experimental, or an alpha-release

oracle_asmdg

pre-req: cx_Oracle

• Manages ASM diskgroup state. (absent/present)
• Takes a list of disks and makes sure those disks are part of the DG. If the disk is removed from the disk it will be removed from the DG.
• Also manages attributes

Note:

• Supports redundancy levels, but does not yet handle specifying failuregroups

oracle_asmvol

• Manages ASM volumes. (absent/present)

oracle_ldapuser

pre-req: cx_Oracle, ldap, re

• Syncronises users/role grants from LDAP/Active Directory to the database

oracle_privs

pre-req: cx_Oracle, re

• Manages system and object level grants
• Object level grant support wildcards, so now it is possible to grant access to all tables in a schema and maintain it automatically!

oracle_jobclass

pre-req: cx_Oracle

• Manages DBMS_SCHEDULER job classes

oracle_jobschedule

pre-req: cx_Oracle, re

• Manages DBMS_SCHEDULER job schedules

oracle_jobwindow

pre-req: cx_Oracle, datetime

• Manages DBMS_SCHEDULER windows

oracle_job

pre-req: cx_Oracle, re

• Manages DBMS_SCHEDULER jobs

oracle_rsrc_consgroup

pre-req: cx_Oracle, re

• Manages resource manager consumer groups including its mappings and grants

oracle_awr

pre-req: cx_Oracle, datetime

• Manages AWR snapshot settings

oracle_facts

pre-req: cx_Oracle

• Gathers facts about Oracle database

oracle_gi_facts

• Gathers facts about Grid Infrastructure cluster configuration

oracle_stats_prefs

pre-req: cx_Oracle

• Managing DBMS_STATS global preferences

oracle_redo

pre-rec: cx_Oracle

• Manage redo-groups and their size in RAC or single instance environments
• NOTE: For RAC environments, the database needs to be in ARCHIVELOG mode. This is not required for SI environments.

oracle_db

pre-rec: cx_Oracle

• Create/remove databases (cdb/non-cdb)
• Can be created by passing in a responsefile or just by using parameters

ansible-oracle-modules's Issues

Data type issue in oracle_parameter ?

(item={u'scope': u'spfile', u'name': u'compatible', u'value': u'12.1.0.2'}) => {"changed": false, "failed": true, "msg": "Blergh, something went wrong while changing the value - ORA-02017: integer value required\n sql: alter system set compatible = 12.1.0.2 scope=spfile sid='*'", "p": {"name": "compatible", "scope": "spfile", "value": "12.1.0.2"}}

oracle_user: ORA-01031: insufficient privileges when running as system

12.1.0.2 non-cdb

Ansible code:

- name: Create oracle schema
  local_action:
    module: oracle_user
    hostname: "{{ ansible_fqdn }}"
    service_name: "{{ oracle_sid }}"
    user: system
    password: "{{ oracle_syspassword }}"
    schema: "{{ ec2_tag_oracleschema }}"
    schema_password: "{{ ec2_tag_oracleschema }}"
    grants:
      - create session
      - resource
      - unlimited tablespace
  become: no
  when: ec2_tag_oracleschema is defined
  environment:
    ORACLE_HOME: /usr/lib/oracle/12.1/client64
    LD_LIBRARY_PATH: /usr/lib/oracle/12.1/client64/lib

Running first time creates the user just fine, but when re-running:

fatal: [10.250.0.140 -> localhost]: FAILED! => {"changed": false, "failed": true, "msg": "ORA-01031: insufficient privileges\n: sql: select password from sys.user$ where name = upper('test123')"}

oracle_pdb generates invalid SQL

It is caused by the "copy" clause. After removing it like follows, it works.

#createsql += ' file_name_convert = (%s) copy' % (quoted)
createsql += ' file_name_convert = (%s)' % (quoted)

The following is the error.

The full traceback is:
File "/tmp/ansible_gSvzei/ansible_module_oracle_pdb.py", line 279, in execute_sql
cursor.execute(sql)

fatal: [db]: FAILED! => {
"changed": false,
"invocation": {
"module_args": {
"datafile_dest": null,
"file_name_convert": "/pdb1/, /cx_oracle_test_1/",
"hostname": "",
"mode": "********dba",
"name": "cx_oracle_test_1",
"oracle_home": null,
"password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
"pdb_admin_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
"pdb_admin_username": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
"port": "1521",
"save_state": true,
"service_name": null,
"service_name_convert": null,
"sourcedb": "cdb12c",
"state": "present",
"unplug_dest": null,
"user": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER"
}
},
"msg": "Something went wrong while executing sql - ORA-00922: missing or invalid option sql: create pluggable database cx_oracle_test_1 admin user ******** identified by ******** file_name_convert = ('/pdb1/',' /cx_oracle_test_1/') copy"
}

Oracle Client library cannot be loaded error while using oracle_sql module

Hello,
Thank you for the modules, You guys are simply Awesome!!!

I am getting below error message when I try to use the oracle_sql module.

My set up Ansible Master running on Linux host which has separate set of users based up on environment and those are connecting to remote DB servers and executing the .SQL files or sql statements by using shell on remote server. I would liek to avoid this shell and use this module instead but I am getting below error. Looks like this module didn't source the environment variables. I tried multiple scenarios didn't find much help.

"msg": "Could not connect to database - DPI-1047: 64-bit Oracle Client library cannot be loaded: \"libclntsh.so: cannot open shared object file: No such file or directory\". See https://oracle.github.io/odpi/doc/installation.html#linux for help, connect descriptor: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = X.X.X.X)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl))))))"

Please let me know if you ever face this kind of issue or please guide me through on how to handle remote server execution with remote users other than user "Oracle"

Thanks and Regards
Raj

DPI-1047 on CentOS 7.4.1708, Python 2.7.5, cx_Oracle 6.2.1, and oracle-instantclient12.2

I'm running into this issue when trying out a playbook. I saw a similar issue and tried adding the environment variables as shown in the playbook but to no avail.

Command:
ansible-playbook -i hosts oracle.yml -e "user=**** password=*** service_name=***"

oracle.yml contents

- hosts: oracle

tasks:
- name: "Run SQL"
oracle_sql:
username: "{{user}}"
password: "{{password}}"
service_name: "{{service_name}}"
hostname: "***"
port: "1521"
sql: 'select username from dba_users'
environment:
ORACLE_HOME: /usr/lib/oracle/12.2/client64
LD_LIBRARY_PATH: /usr/lib/oracle/12.2/client64/lib

ERROR:
fatal: []: FAILED! => {"changed": false, "msg": "Could not connect to database - DPI-1047: 64-bit Oracle Client library cannot be loaded: "libclntsh.so: cannot open shared object file: No such file or directory". See https://oracle.github.io/odpi/doc/installation.html#linux for help, connect descriptor: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=***)))"}

Handling empty string as value in oracle_parameter

failed: [ -> localhost] (item={u'scope': u'spfile', u'name': u'os_authent_prefix', u'value': u''}) => {
    "changed": false, 
    "failed": true, 
    "invocation": {
        "module_args": {
            "hostname": "xxx", 
            "mode": "normal", 
            "name": "os_authent_prefix", 
            "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER", 
            "port": "1521", 
            "scope": "spfile", 
            "service_name": "xxx", 
            "sid": "*", 
            "state": "present", 
            "user": "c##dbauser", 
            "value": ""
        }, 
        "module_name": "oracle_parameter"
    }, 
    "msg": "Error: Missing parameter name or value", 
    "p": {
        "name": "os_authent_prefix", 
        "scope": "spfile", 
        "value": ""
    }
}

Unable to get booleans to be assigned correctly

Hi,
I can run the code fine but all booleans are not properly send across.
So I keep running into this error:
failed: [default] => (item={u'datafile': u'/u02/oradata/retail/centraloffice/tbs_01.dbf', u'name': u'centralofficedb', u'autoextend': u'yes', u'state': u'present', u'next': u'5M', u'content': u'permanent', u'maxsize': u'500M', u'size': u'100M'}) => {"failed": true, "item": {"autoextend": "yes", "content": "permanent", "datafile": "/u02/oradata/retail/centraloffice/tbs_01.dbf", "maxsize": "500M", "name": "centralofficedb", "next": "5M", "size": "100M", "state": "present"}, "msg": "value of autoextend must be one of: yes,on,1,true,1,no,off,0,false,0, got: yes "}

So here on purpose I set the value in the yml to yes and it still says autoextend is not set but it got value yes.

So somehow its not converting, i have had this this issue also with big file and using the test.yml.

Any ideas?
Thanks

Support for authentication with Oracle Wallet

Currently modules require specifying hostname/port/servicename+username/password, but they should be able to also authenticate using wallets, then no need to specify passwords in playboks.

oracle_user: ORA-00959 when tablespace is not specified

12.1.0.2 non-cdb

Ansible code:

- name: Create oracle schema
  local_action:
    module: oracle_user
    hostname: "{{ ansible_fqdn }}"
    service_name: "{{ oracle_sid }}"
    user: sys
    password: "{{ oracle_syspassword }}"
    mode: sysdba
    schema: "{{ ec2_tag_oracleschema }}"
    schema_password: "{{ ec2_tag_oracleschema }}"
    grants:
      - create session
      - resource
      - unlimited tablespace
  become: no
  when: ec2_tag_oracleschema is defined
  environment:
    ORACLE_HOME: /usr/lib/oracle/12.1/client64
    LD_LIBRARY_PATH: /usr/lib/oracle/12.1/client64/lib

Running first time creates the user just fine, but when re-running:

fatal: [10.250.0.140 -> localhost]: FAILED! => {"changed": false, "failed": true, "msg": "Blergh, something went wrong while altering the schema - ORA-00959: tablespace 'NONE' does not exist\n sql: alter user test123 identified by test123 default tablespace None temporary tablespace temp account unlock"}

oracle_pdbs/roles/grants: Object privileges only work with 1 privilege?

Hi,

If I try to add object privileges to a created role in a PDB, but it only succeeds if I have 1 single privilege.

With 2 it already fails.

Maybe I am missing the syntax here, but to my understanding it should work as follows:

oracle_pdbs:
...
roles:
- name: myRole1
grants:
- select on dba_users
- select on dba_advisor_findings

...

As soon as I have the 2nd line of "select on..." in there, the task fails:
"msg": "Something went wrong while executing sql - ORA-00905: missing keyword sql: grant select on dba_users,select on dba_advisor_findings to myRole1"}

Does this have to be splitted differently or did I miss something?

Thanks a lot for helping me out here.

Best regards,
Matthias.

oracle_user module - parameter 'state'

Hi,

as per documentation state parameter is:

    required: False
    default: present
    choices: ['present','absent','locked','unlocked']

If we pass parameter locked or unlocked and user is not present user will be created. This would mean that 'unlocked' is redundant as it has the same function as 'present'. Does it make sense to throw an error when user doesn't exist.

For example let's say we would like to automate that users can unlock themselves without admins have to log in and do it for them. If we use that parameter users would get instantaneous access to the DB without ever been granted one.

Also to the same point should there be 'expired' option in there so that user gets created as expired and is forced to change password on the next login.

Thank you.
n.

Please submit upstream

This is a great set of modules, and I am afraid that many people who really need that are not aware of this repository.
I suggest that you submit these upstream so that it reaches a much broader audience.

oracle_grants / oracle_user - unable to grant read/write on a directory

This might be another case of me not knowing what I'm doing, but I can't seem to figure out how to grant read permissions to a directory... I think it's because directory permissions can't be stringed together with other grants.

SQL> grant create session, read on directory imp_dir to backup_role;
grant create session, read on directory imp_dir to backup_role
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege

Not sure what the right direction is here... I'm no oracle expert, but this is the only grant that I've come across that seems to need to be executed individually. I don't know if including a directories grant section is worth while, or just handling it externally of these modules. I tried doing it in the create_user module as well, but it didn't seem to work either... sorry for bring such a bother - if I was better at Python, I'd try to fix it myself 😄

Here is my variable for reference:

- role: backup_role
    state: present
    grants:
      - create session
      - exp_full_database
      - create job
      - flashback any table
      - read on directory import_dir
      - write on directory import_dir
    privs:
      - select:sys.dba_users

oracle_user module, requires sysdba

Thanks for the grat modules. I recently started using them, and have found that when the user is created the first time, a DBA role is enough. When the playbook is rerun, though, it fails. as it's accessing a SYS view.

dba_connection:
  user: system
  password: xxxxx

passw_user:
   schema: userpw
   schema_password: userpw
   state: present
   default_tablespace: users
   grants:
        - "connect"

tasks:

• name: Create Application user
  become: true
  become_user: oracle
  oracle_user:
  hostname: "{{ inventory_hostname }}"
  service_name: "{{ service_name }}"
  mode: normal
  user: "{{ dba_connection.user }}"
  password: "{{ dba_connection.password }}"
  schema: "{{ item.schema }}"
  schema_password: "{{ item.schema_password }}"
  state: "{{ item.state }}"
  default_tablespace: "{{ item.default_tablespace }}"
  grants: "{{ item.grants }}"
  environment: oracle_env
  with_items:
  • "{{ passw_user }}"

The first time it works, creates the user. The rerun fails with:

failed: [dev-oracledb] (item={u'schema_password': u'userpw', u'state': u'present', u'grants': [u'connect'], u'default_tablespace': u'users', u'schema': u'userpw'}) => {"failed": true, "item": {"default_tablespace": "users", "grants": ["connect"], "schema": "userpw", "schema_password": "userpw", "state": "present"}, "msg": "ORA-01031: insufficient privileges\n: sql: select password from sys.user$ where name = upper('userpw')"}

is it really necessary to access sys.user$ ?

The variable pdb get the exception

in the file oracle_facts:

try:
    if database['CDB'] == 'YES':
        pdb = query_result("SELECT con_id, rawtohex(guid) guid_hex, name, open_mode FROM v$pdbs ORDER BY name")
    except:
        pdb = []

pdb will get the exception at the function "facts.update"
Maybe we can set the pdb to be [] first

re-runnign oracle_user module returns error

Hi,
I started testing your modules today and I created a simple test to create a user. Running the playbook runs successfully and creates the user, but when I re-run the playbook with no change the playbook returns ""msg": "ORA-01031: insufficient privileges: sql: select password from sys.user$ where name = upper('dbauser2')""

Here is my playbook

---

• hosts: 127.0.0.1
  connection: local
  user: opc

sudo: yes

sudo_user: oracle

gather_facts: false

vars:

ora_user: oracle

  oracle_home: /usr/lib/oracle/18.3/client64
  hostname: dbhost1
  service_name: srv1
  set_init_mode: sysdba
  set_init_user: sys
  user: system
  password: syspwd
  oracle_env:
         ORACLE_HOME: "{{ oracle_home }}"
         LD_LIBRARY_PATH: "{{ oracle_home }}/lib"

  dba_user:
       - schema: dbauser2
         schema_password_hash: B7C930D09B3AF263 # passwd = dbauser
         state: present
         default_tablespace: users
         grants: 
              - "dba"

tasks:

     - name: Create DBA user
       local_action: 
               module: oracle_user
               hostname: "{{ hostname }}"
               service_name: "{{ service_name }}"
               user: "{{ user }}"
               password: "{{ password }}" 
               schema: "{{ item.0.schema }}"
               schema_password_hash: "{{ item.0.schema_password_hash }}"
               state: "{{ item.0.state }}"
               default_tablespace: "{{ item.0.default_tablespace }}"
               grants: "{{ item.1 }}"
       environment: "{{ oracle_env }}"
       with_subelements: 
         - "{{ dba_user }}"
         - grants

I executed the query "select password from sys.user$ where name = upper('dbauser2')" as sys and it runs succesfully.

Is there additional setup needed that I'm missing?

oracle_db: Change state for archivelog & flashback database

The procedure ensure_db_state need some changes.
I tried the following but it doesn't worked on my side...

diff --git a/oracle_db b/oracle_db
index b94df39..cbbdfc6 100644
--- a/oracle_db
+++ b/oracle_db
@@ -555,7 +555,8 @@ def ensure_db_state (module,msg,oracle_home,db_name,db_unique_name,sid, archivel
         flcomp = 'NO'
         flsql = alterdb_sql +  ' no force logging'

-    if flashback == True:
+    # flashback can't be enabled with noarchivelog as target
+    if flashback == True and archivelog == True:
         fbcomp = 'YES'
         fbsql = alterdb_sql + ' flashback on'
     else:

@@ -575,14 +576,26 @@ def ensure_db_state (module,msg,oracle_home,db_name,db_unique_name,sid, archivel
         change_db_sql.append(deftempsql)

     if log_check_[0][0] != archcomp:
+
+        # Archivelog changed to False
+        # => Disable Flashback before
+        if archivelog == False:
+             change_restart_sql.append(fbsql)
+
         change_restart_sql.append(archsql)

+        if archivelog == True and log_check_[0][2] != fbcomp:
+            # possible change of Flashback Database after archivelogmode
+            change_restart_sql.append(fbsql)
+    else:
+
+        # possible change of Flashback Database
+        if log_check_[0][2] != fbcomp:
+            change_db_sql.append(fbsql)
+
     if log_check_[0][1] != flcomp:
         change_db_sql.append(flsql)

-    if log_check_[0][2] != fbcomp:
-        change_db_sql.append(fbsql)
-
     if len(change_db_sql) > 0 or len(change_restart_sql) > 0:
         if len(change_db_sql) > 0: # <- Apply changes that does not require a restart
             for sql in change_db_sql:

I stopped my work at this point, because I didn't really work and I didn't have more time for this feauture.

oracle_awr should allow for interval of 0 to disable

Currently we run the following to stop AWR snapshots on our SE instances... I would like to mange this with Ansible instead :)

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS ( INTERVAL => 0);

It appears the code prevents any values < 10, I would like 0 to be the exception!

Thanks

oracle_db: Module doesn't deal with archivelog etc if output=verbose

The module doesn't deal with archivelog etc if output=verbose.
It will be dealt if the configuration is applied again

Add module to create profiles

Add module to create and manage profiles

Support for sysasm

How can I get sysasm to work? Thanks

scope=spfile in oracle_parameter

If scope=spfile check V$SPPARAMETER for a current value not v$parameter.

Unable to execute module from macOS

From ansible, I see:

The cx_Oracle module is required. 'pip install cx_Oracle' should do the trick. If cx_Oracle is installed, make sure ORACLE_HOME & LD_LIBRARY_datafile is set

However, if I run a simple python script, it succeeds:

# myscript.py

from __future__ import print_function

import cx_Oracle

# Connect as user "hr" with password "welcome" to the "oraclepdb" service running on this computer.
connection = cx_Oracle.connect("hr", "welcome", "localhost/oraclepdb")

cursor = connection.cursor()
cursor.execute("""
    SELECT username
  FROM dba_users""")
for username in cursor:
    print("Values:", username)

print("done")

running with /usr/local/Cellar/ansible/2.5.4/libexec/bin/python myscript.py after installing the deps like this: /usr/local/Cellar/ansible/2.5.4/libexec/bin/python -m pip install cx_Oracle --upgrade.

Not sure why the module is failing.

Examples require Oracle Clusterware

The use of srvctl to check the database status only works if the Oracle clusterware is in place. That's not the case for single instance deployments.

Something like the below would always work:

ps -fea | grep pmon | grep

---

• name: Check if database is running
  become: true
  become_user: oracle
  shell: "ps -fea | grep pmon | grep {{ oracle_sid }} | grep -v grep"
  environment: oracle_env
  register: check_db_up
  tags: check

• name: Check if database is running
  debug: msg={{ check_db_up.stdout }}
  failed_when: check_db_up.stdout == ""
  tags: check

oracle_tablespace - maxsize

line 515 and 545 are using the wrong divisor - should be v_divisor_maxsize

v_maxsize_current := ((rec.maxbytes)/v_divisor_nextsize);

oracle_sql run pl/sql package issue

Hi,

I am trying to run a basic sql package, but to me it seems, the parser is cutting off the line after the first semicolon. Did I miss something here again?

 - name: apply application fixes by SQL
   oracle_sql:
     username: "{{ user_name }}"
     mode: "{{ user_mode }}"
     password: "{{ user_password }}"
     service_name: "{{ service_name }}"
     script: "{{ script_name }}"
   environment: "{{oracle_env}}"

The script currently only consists of this one command:

BEGIN
DBMS_STATS.set_global_prefs ( pname => 'CONCURRENT', pvalue => 'ALL');
END;
/

The output is as follows:

TASK [apply application fixes by SQL] ************************************************************************************************************************************
fatal: []: FAILED! => {"changed": false, "msg": "Something went wrong while executing sql - ORA-06550: line 3, column 71:\nPLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:\n\n := . ( % ; sql: \nBEGIN\n DBMS_STATS.set_global_prefs ( pname => 'CONCURRENT', pvalue => 'ALL')"}

I already tried several syntax variations, but none made it work for me. What am I missing?

Best regards,
Matthias.

oracle_user - cannot create an external user without a password

I think this bit of code needs to be reworked... if a password is not provided (which neither are required) then it returns False before the authentication is checked... externally identified users don't need a password as they are authenticated by the OS - https://oracle-base.com/articles/misc/os-authentication

if not (schema_password):
       if not (schema_password_hash):
           msg[0] = 'Error: Missing schema password or password hash'
           return False

   if authentication_type == 'password':
       if (schema_password_hash):
           sql = 'create user %s identified by values \'%s\' ' % (schema, schema_password_hash)
       else:
           sql = 'create user %s identified by %s '% (schema, schema_password)
   elif authentication_type == 'global':
       sql = 'create user %s identified globally ' % (schema)
   elif authentication_type == 'external':
       sql = 'create user %s identified externally ' % (schema)

Ability to change the SYS password with oracle_user

I am thinking about implementing a solution to change the SYS password with ansible-oracle.
The problem is simple:
How to change a password for a user which is used to change the password?

What do you think about a variable 'password_old'. This variable is used, when a login with the usual password leads to a ORA-01017. This is needed for each user password change, because we don't know, if a change for SYS is coming or not.

The drawback are false alarm entries in the auditlog.

What do you think about this idea?

Unable to Create user

The error appears to have been in '/home/oracle/abc.yml': line 99, column 173, but may
be elsewhere in the file depending on the exact syntax problem.

The offending line appears to be:

Error starts with documentation.

Create a new schema on a remote db by running the module on the controlmachine (i.e: delegate_to: localhost)

oracle_user: hostname=remote-db-server service_name=orcl user=system password=manager schema=myschema schema_password=mypass default_tablespace=test state=present grants="'create session', create any table'"
^ here

Please help.

oracle_services: state should be properly reflected

If a new service is created with state=started, the module needs to run twice (in 2 tasks).
The first task creates the service, the second starts the service
Same when removing a service (state=absent)
First task stops the service, second task removes it.

state=started/absent should do this is 1 run.

oracle_users password not masked

I'm using a vaulted password, but it's displaying it in clear text in the log... not sure if there is an easy way around that or not.

TASK [oracle_common : | users | create database users] ***************************************************************************************************
skipping: [server1] => (item={u'profile': u'db_mon_profile', u'schema_password': u'Oracle12c', u'authentication_type': u'password', u'state': u'present', u'grants': [u'prtg_role'], u'default_tablespace': u'data', u'schema': u'prtg'})
skipping: [server1] => (item={u'profile': u'db_mon_profile', u'schema_password': u'Oracle12c', u'authentication_type': u'password', u'state': u'present', u'grants': [u'splunk_role'], u'default_tablespace': u'data', u'schema': u'splunk'})

oracle_user resets the permissions instead adds additional

Hi,

There might be a small small issue with the oracle_grants and oracle_user. We first create user and then add grants to the user. If user already existed after executing the script we get only permissions that we requested and others removed.

For example:
- before execution user has: CREATE SESSION, ROLE1
- execution adds user permissions to ROLE2
- after execution user has: CREATE SESSION, ROLE2

If we run it the second time it removes all the permissions from the user.

Everything works perfectly when user does not exist already!

Thanks.
-N.

oracle_user overwrites profile if not specified

I just tried to use oracle_user to update password. I did not specify profile, so it defaulted to 'default'.
This had the unexpected side-effect of changing the profile of the user when I only intended to update the password.
I think default for profile should be None, maybe only resolve default to 'default' if creating user. Will try to fix myself and submit pull-request.

oracle_pdb needs fixes for rac

The module needs to take RAC into consideration when ensuring state (e.g read_only -> read_write, mount -> open ).
Currently only deals with 'current' instance.

oracle_pdb Add support for create_pdb_clone clause

Creating a PDB by cloning a non-CDB is available starting with Oracle Database 12c Release 1 (12.1.0.2). See https://docs.oracle.com/database/121/SQLRF/statements_6010.htm#SQLRF55686

oracle_pdb connect string is invalid

When oracle_pdb calls cx_Oracle.makedsn(), it specifies the container database as service_name.

dsn = cx_Oracle.makedsn(host=hostname, port=port, service_name=service_name)

It generates an invalid conn string and causes this error.

"msg": "Could not connect to database - ORA-12514: TNS:listener does not currently know of service requested in connect descriptor, connect descriptor: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=cdb12c)))"

The call to makedsn() should specify the container database as sid, not service_name, like follows.

dsn = cx_Oracle.makedsn(host=hostname, port=port, sid=service_name)

oracle_db parameter honorControlFileInitParam

From 12.2 onward dbca ignore parameter for controlfile position (see for example https://www.spotonoracle.com/?cat=14).
For take into account controfile position you must use -honorControlFileInitParam. Here the code I'd like to add:

command = "%s/bin/dbca -createDatabase -silent " % (oracle_home)
if major_version > '12.1':
	command += ' -honorControlFileInitParam '

Thanks

oracle_jobs - scheduler attributes

I'm unable to do the following using the module:

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.GATHER_FIXED_OBJ_STATS'
,attribute => 'RAISE_EVENTS'
,value => SYS.DBMS_SCHEDULER.JOB_FAILED);

Is there a way that I'm missing to accomplish this type of thing?

use tnsnames file for remote connection without wallet

Hi,

Is there a way to use tnsnames file without using oracle wallet for remote databases. When hostname is not specified this error is reported as there is no local DB:

fatal: [localhost]: FAILED! => {"changed": false, "msg": "Could not connect to database - ORA-12541: TNS:no listener, connect descriptor: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PINGY12)))"}

Thanks!

• N.

Using this modules with SOLARIS 11/10 installation.

Hello!

Have you test this modules in Solaris?, any suggestion?

The same question for the roles about creating and RAC.

Thanks in advance!

running ansible in --check mode just skips

is it possible to rework the modules to function in check mode?

oracle_jobs/oracle_sql

This is a 2 part issue... I was hoping to accomplish this using a module - but maybe it is not easily doable.

If not, then I was trying to put it into file and run it... but that doesn't seem to work either (error below)

`DECLARE
L_N_JOB_COUNT NUMBER := 0;
L_V_AUD_PARM_VALUE VARCHAR2(30);
L_V_AUD_TS_NAME VARCHAR2(30);
L_V_AUD_TS_CONF_NAME VARCHAR2(30);

BEGIN
EXECUTE IMMEDIATE 'SELECT VALUE FROM V$PARAMETER WHERE NAME = ''audit_trail''' INTO L_V_AUD_PARM_VALUE;
EXECUTE IMMEDIATE 'SELECT TABLESPACE_NAME FROM ALL_TABLES WHERE TABLE_NAME = ''AUD$''' INTO L_V_AUD_TS_NAME;
EXECUTE IMMEDIATE 'SELECT PARAMETER_VALUE FROM DBA_AUDIT_MGMT_CONFIG_PARAMS WHERE PARAMETER_NAME = ''DB AUDIT TABLESPACE'' AND AUDIT_TRAIL = ''STANDARD AUDIT TRAIL''' INTO L_V_AUD_TS_CONF_NAME;

IF (L_V_AUD_PARM_VALUE <> 'DB') THEN
DBMS_OUTPUT.put_line('audit_trail value not set to DB - it is ' || L_V_AUD_PARM_VALUE || '.');
END IF;

-- keep AUD$ tablespace name if different from config name
--IF (L_V_AUD_TS_NAME <> L_V_AUD_TS_CONF_NAME) THEN
IF (L_V_AUD_TS_NAME <> 'AUD_DATA') THEN
DBMS_OUTPUT.PUT_LINE('Moving AUDIT_TRAIL_AUD_STD location to AUD_DATA.'); --' || L_V_AUD_TS_NAME || '.');
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_LOCATION_VALUE => 'AUD_DATA'--L_V_AUD_TS_NAME
);
END IF;

-- initialize AUD$ cleanup if not done so already
IF NOT DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_AUDIT_MGMT.init_cleanup(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
DEFAULT_CLEANUP_INTERVAL => 24 /* hours */);
END IF;

-- report AUD$ cleanup intialization status
IF DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.put_line('AUDIT_TRAIL_AUD_STD has been initialized.');
ELSE
DBMS_OUTPUT.put_line('AUDIT_TRAIL_AUD_STD has NOT been initialized.');
END;`

Error:
`The full traceback is:
File "/tmp/ansible_ZUoQ7U/ansible_module_oracle_sql.py", line 107, in execute_sql
cursor.execute(sql)

failed: [dbdevdb00chuk02.marketpipe.com -> localhost] (item={u'script': u'/home/tdebusk/techops-ansible/conf.d/roles/oracle_common/tasks/scripts/audit_setup.sql'}) => {
"changed": false,
"invocation": {
"module_args": {
"hostname": "dbdevdb00chuk02.marketpipe.com",
"mode": "sysdba",
"password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
"port": "1521",
"script": "/home/tdebusk/techops-ansible/conf.d/roles/oracle_common/tasks/scripts/audit_setup.sql",
"service_name": "dbdev",
"sql": null,
"user": "sys"
}
},
"item": {
"script": "/home/tdebusk/techops-ansible/conf.d/roles/oracle_common/tasks/scripts/audit_setup.sql"
},
"msg": "Something went wrong while executing sql - ORA-06550: line 2, column 27:\nPLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:\n\n * & = - + ; < / > at in is mod remainder not rem\n <an exponent (**)> <> or != or ~= >= <= <> and or like like2\n like4 likec between || multiset member submultiset sql: DECLARE\n L_N_JOB_COUNT NUMBER := 0"
}
`

Add SSL authentication possibility in oracle_ldapuser

Add possibility to create users with LDAP DN set, so it could be used for example when authenticating with SSL certificates.

CREATE USER jamesbond IDENTIFIED EXTERNALLY AS 'CN=jamesbond,...';

oracle_grants - not idempotent - flags all roles grants as changed on each run

There is a good chance I am just doing this wrong... so feel free to correct me :)

Here are my vars:

oracle_roles:
  - { name: "prtg_role", state: present, role_grants: [ "create session",
                                                        "select on sys.dba_data_files",
                                                        "select on sys.dba_free_space",
                                                        "select on sys.dba_segments",
                                                        "select on sys.dba_tablespaces",
                                                        "select on sys.v_$archived_log",
                                                        "select on sys.v_$open_cursor",
                                                        "select on sys.v_$parameter",
                                                        "select on sys.v_$recovery_file_dest",
                                                        "select on sys.v_$session",
                                                        "select on sys.v_$sesstat",
                                                        "select on sys.v_$sga_target_advice",
                                                        "select on sys.v_$statname" ] }

Here are my tasks:

- name: Create role
  oracle_role:
    hostname: "{{ ansible_fqdn }}"
    service_name: "{{ oracle_sid }}"
    user: sys
    mode: sysdba
    password: "{{ password }}"
    role: "{{ item.name }}"
    state: "{{ item.state }}"
  environment: "{{ oracle_env }}"
  with_items: "{{ oracle_roles }}"
  delegate_to: localhost
  become: no

- name: Add grants to role
  oracle_grants:
    hostname: "{{ ansible_fqdn }}"
    service_name: "{{ oracle_sid }}"
    user: sys
    mode: sysdba
    password: "{{ password }}"
    role: "{{ item.0.name }}"
    grants: "{{ item.1 }}"
    state: "{{ item.0.state }}"
  environment: "{{ oracle_env }}"
  with_subelements:
         - "{{ oracle_roles }}"
         - role_grants
  delegate_to: localhost
  become: no

Here is the output I get on each run:

TASK [oracle_common : Create application role] ***********************************************************************************************************
ok: [dbdevdb01  -> localhost] => (item={u'state': u'present', u'name': u'prtg_role', u'role_grants': [u'create session', u'select on sys.dba_data_files', u'select on sys.dba_free_space', u'select on sys.dba_segments', u'select on sys.dba_tablespaces', u'select on sys.v_$archived_log', u'select on sys.v_$open_cursor', u'select on sys.v_$parameter', u'select on sys.v_$recovery_file_dest', u'select on sys.v_$session', u'select on sys.v_$sesstat', u'select on sys.v_$sga_target_advice', u'select on sys.v_$statname']})

TASK [oracle_common : Add grants to role] ****************************************************************************************************************
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'create session'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.dba_data_files'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.dba_free_space'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.dba_segments'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.dba_tablespaces'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.v_$archived_log'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.v_$open_cursor'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.v_$parameter'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.v_$recovery_file_dest'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.v_$session'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.v_$sesstat'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.v_$sga_target_advice'))
changed: [dbdevdb01 -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.v_$statname'))

It does not seem to be checking if the role already has the priv, it just updates it an marks it as changed, even though the role already had the priv granted.

Thanks for your time! I love this module, you should get it added to the core ansible project!!!

Python modules should have a .py extension

I'd recommend renaming the Python modules by adding the .py extension. Without them, they are not taken into account by the ansible-doc utiliy, and possibly other functionality might be missing when installing them on some library path.

Context, background and further explanation, see ansible/ansible#48797

Thank you.

oracle_grants: grants to roles are alternately granted and revoked on ansible runs

Given the playbook below, the roles that are listed for the grants are added the first time I run it. The next time they're removed. And the next, added again.

I think there's some not-quite-right logic around computing/applying differences. I added some logging to the module and output the total_sql var just before it is executed and the first time it is:

DEBUG:__main__:total_sql: ['grant IMP_FULL_DATABASE,CONNECT to test_role']

Next time:

DEBUG:__main__:total_sql: ['grant IMP_FULL_DATABASE,CONNECT to test_role', 'revoke imp_full_database,connect from test_role']

The sql is executed in order so the revoke just wipes out the grant. Technically, the ansible task shouldn't run the second time since the grants are already applied.

Note that system privilege grants don't suffer from this problem, i.e. adding - "CREATE TABLE" to the list of grants works as expected when run multiple times.

The playbook

#!/usr/bin/env ansible-playbook
---
- hosts: centosora1
  remote_user: root
  become: yes
  become_user: oracle

  vars:
    oracle_home: /u01/app/oracle/11.2.0.4/db1 # centosora1
    hostname: centosora1
    service_name: oradb
    user: sys
    password: 
    mode: sysdba
    oracle_env:
      ORACLE_HOME: "{{ oracle_home }}"
      LD_LIBRARY_PATH: "{{ oracle_home }}/lib"

    oracle_roles:
      - name: test_role
        state: present
        grants:
        - "CONNECT"
        - "IMP_FULL_DATABASE"
        object_privs:

  tasks:
    # this just creates the role, nothing wrong with this one.
    - name: Create role
      oracle_role:
        hostname: "{{ hostname }}"
        service_name: "{{ service_name }}"
        mode: "{{ mode }}"
        user: "{{ user }}"
        password: "{{ password }}"
        role: "{{ item.name }}"
      environment: "{{ oracle_env }}"
      with_items: "{{ oracle_roles }}"

    - name: Add grants to role
      oracle_grants:
        hostname: "{{ hostname }}"
        service_name: "{{ service_name }}"
        user: sys
        mode: sysdba
        password: "{{ password }}"
        role: "{{ item.name }}"
        grants: "{{ item.grants }}"
        object_privs: "{{ item.object_privs }}"
        state: "{{ item.state }}"
      environment: "{{ oracle_env }}"
      with_items:
        - "{{ oracle_roles }}"

oracle_user: implement update_password=on_create option

Ansible user module has a useful option update_password=on_create, would be good to have it in this module also so the user password is only updated during create user and not changed afterwards.

Add module to manage DBMS_STATS parameters global and table level

Use spool for both sql and sqlfile

Hello,

Thanks you for this great module.
I am trying to use it in order to do some pre-checks, post-checks in my project and want to use spool in my queries. The goal is to compare the query result in spool file to a value. An use case is:
select value from v$parameter where name='open_links'
I want to check the value returned by this query.

Thanks for your support.

M. Macalou

De-couple oracle_home management - put into its own structure

Right now, a ORACLE_HOME is based on the configuration in the oracle_databases structure.

A new structure (oracle_homes) will be created where the oracle_home and its possible patches will be dealt with.
Another list will contain the homes (e.g oracle_homes_installed) a specific configuration should have installed.
oracle_databases will only contain a reference to the oracle_homes structure.