- Introduction
- Requiements
- Usage
- Common
- Directory
- Role
- System Parameters
- Tablespaces
- User
- Return value
- Author
Handle Oracle stuff using Ansible with this library.
Supported objects and operations:
- Directory
- create and delete
- modify path
- Roles
- create and delete
- grant and revoke system privileges
- grant and revoke roles
- System parameters
- set and reset, even hidden parameters
- Tablespaces
- create and delete
- add and modify datafiles
- Users
- create, update, delete
- lock and unlock
- grant and revoke roles
- grant and revoke system privileges
- Check mode All modules support Ansible's check mode. If check mode is enabled all SQL statements that would be executed are returned and can be checked. One can register the output of each module and check the results sql variable to see all statements.
- Python cx_Oracle 5.2 or greater must be installed on the Ansible controller node.
For all modules the following parameters must be passed. On of either oracle_sid and oralce_service must be passed but not both. oracle_pass can be omitted if defined in environment variable ORACLE_PASS. To connect as SYSDBA or SYSOPER set oracle_mode to the corresponding value, omit for normal connection.
As cx_Oracle is required one should use local action to avoid the installation of cx_Oracle on all database systems.
oracle_host: <hostname or ip of database>
oracle_port: <port>
oracle_user: <username>
oracle_pass: <password>
oracle_mode: <[SYSDBA|SYSOPER]>
oracle_sid: <SID>
oracle_service: <service_name>- name: Ensure directory
oracle_directory:
name: DATA_PUMP_DIR
path: /u01/app/oracle/admin/ORCL/dpdump
oracle_host: db.example.com
oracle_port: 1521
oracle_user: system
oracle_pass: manager
oracle_sid: ORCL- oracle_role:
name: APP_ROLE
roles:
- CONNECT
- SELECT ANY DICTIONARY
sys_privs:
- CREATE TABLE
- CREATE INDEX
state: present
oracle_host: db.example.com
oracle_port: 1521
oracle_user: system
oracle_pass: manager
oracle_sid: ORCLProvided value is compared to column value AND display_value of v$system_parameter.
- oracle_system_parameter:
name: db_create_file_dest
value: /u01/app/oracle/oradata/ORCL
state: present
oracle_host: db.example.com
oracle_port: 1521
oracle_user: system
oracle_pass: manager
oracle_sid: ORCLEnsure tablespace USERS exist, can grow up to 2G in 16M steps. Tablespace will be created if it does not exist.
- name: Ensure tablespace users is present
oracle_tablespace:
name: USERS
state: present
init_size: 100M
autoextend: true
next_size: 16M
max_size: 2G
data_path: /home/oracle/
oracle_host: db.example.com
oracle_user: system
oracle_pass: manager
oracle_sid: ORCLEnsure tablespace TEST does not exist.
- name: Ensure tablespace users is present
oracle_tablespace:
name: TEST
state: absent
oracle_host: db.example.com
oracle_user: system
oracle_pass: manager
oracle_sid: ORCL- oracle_user:
name: pinky
password: pinky
default_tablespace: DATA
temporary_tablespace: TEMP
roles:
- CONNECT
- SELECT ANY DICTIONARY
sys_privs:
- UNLIMITED TABLESPACE
state: unlocked
oracle_host: db.example.com
oracle_port: 1521
oracle_user: sys
oracle_pass: topsecret
oracle_mode: SYSDBA
oracle_sid: ORCLEvery module returns the object it handled as well as all SQL statements executed so one can use Ansible's register function for further processing. The dict attribute is named like the object itself, like user in the oracle_user module.
Example for the result of a changed user
{
"_ansible_no_log": false,
"changed": true,
"invocation": {
"module_args": {
"_ansible_check_mode": true,
"default_tablespace": "DATA",
"name": "PINKY",
"oracle_host": "db.example.com",
"oracle_mode": "SYSDBA",
"oracle_pass": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
"oracle_port": "1521",
"oracle_service": null,
"oracle_sid": "ORCL",
"oracle_user": "sys",
"password": "1234567890ABCDEF",
"password_mismatch": false,
"roles": [
"DBA"
],
"state": "present",
"sys_privs": null,
"temporary_tablespace": "TEMP"
},
"module_name": "oracle_user"
},
"item": {
"default_tablespace": "DATA",
"name": "PINKY",
"password": "1234567890ABCDEF",
"roles": [
"DBA"
],
"temporary_tablespace": "TEMP"
},
"sql": [
"GRANT DBA TO PINKY",
],
"user": {
"account_status": "OPEN",
"default_tablespace": "DATA",
"name": "PINKY",
"password": "1234567890ABCDEF",
"roles": [
"DBA"
],
"sys_privs": null,
"temporary_tablespace": "TEMP"
}
}