Tablespace privileges error with AWS RDS Oracle

[landesjm]

I'm using the Oracle modules to manage a RDS Oracle database. I have a bigfile tablespace that I want to increase the MAXSIZE, but I get an ORA-01031: insufficient privileges message.

I'm connecting to the database as the RDS master user so I would not expect privileges to be an issue. I can successfully execute the following statement in the database as the RDS master user without privilege issues:

   alter tablespace DOMAIN_DATA AUTOEXTEND ON NEXT 1G MAXSIZE 30G;

Here is the original create_tablespace call which successfully created the tablespace (Note: RDS created a bigfile tablespace even though the bigfile parameter defaults to FALSE): tasks:

• name: create domain tablespace oracle_tablespace: hostname: "{{ RDS_HOST }}" service_name: "{{ RDS_SERVICE_NAME }}" user: "{{ RDS_USER }}" password: "{{ RDS_PASSWORD }}" tablespace: domain_data size: 5G autoextend: true next: 1G max: 15G

and here is where I increase the max: to 40G (notice that I've included setting the bigfile parameter to TRUE. I tried both TRUE and FALSE with the same outcome).

tasks:

• name: create domain tablespace oracle_tablespace: hostname: "{{ RDS_HOST }}" service_name: "{{ RDS_SERVICE_NAME }}" user: "{{ RDS_USER }}" password: "{{ RDS_PASSWORD }}" tablespace: domain_data bigfile: true size: 5G autoextend: true next: 1G max: 40G

Here is the playbook execution output:

play: TASK [create domain tablespace] **** play: task path: /ansible/playbooks/tax_rep.yaml:21 play: Using module file /ansible/playbooks/library/oracle_tablespace play: ESTABLISH LOCAL CONNECTION FOR USER: root play: EXEC /bin/sh -c '/usr/bin/python && sleep 0' play: The full traceback is: play: WARNING: The below traceback may not be related to the actual failure. play: File "/tmp/ansible_oracle_tablespace_payload_G_Fo4K/main.py", line 583, in ensure_tablespace_attributes play: {'tablespace': tablespace,'autoextend': str(autoextend), 'nextsize': nextsize, 'maxsize': maxsize, 'o_autoextend_changed': v_autoextend_change, 'o_nextsize_changed': v_nextsize_change,'o_maxsize_changed': v_maxsize_change}) play: fatal: [localhost]: FAILED! => { play: "changed": false, play: "invocation": { play: "module_args": { play: "autoextend": true, play: "bigfile": false, play: "content": "permanent", play: "datafile": null, play: "hostname": "RDS_HOST", play: "max": "40G", play: "maxsize": "40G", play: "mode": "normal", play: "next": "1G", play: "nextsize": "1G", play: "numfiles": null, play: "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER", play: "port": "1521", play: "service_name": "RDS_SERVICE_NAME", play: "size": "5G", play: "state": "present", play: "tablespace": "domain_data", play: "user": "deploy" play: } play: }, play: "msg": "ORA-01031: insufficient privileges\nORA-06512: at line 138" play: } play: play: PLAY RECAP ***** play: localhost : ok=2 changed=0 unreachable=0 failed=1

Any insight would be appreciated. thanks John Landes jmlandes@containerstore.com

[oravirt]

Ok, Unfortunately I don't have a RDS instance where I can test this right now, but:

play: "user": "deploy"

is that the rds master user?

[landesjm]

Yes, the "deploy" user is the RDS master user.

I did some follow on development work to come up with a workaround. I added an oracle_sql block that performs the ALTER TABLESPACE.

• name: alter domain tablespace oracle_sql: hostname: "{{ RDS_HOST }}" service_name: "{{ RDS_SERVICE_NAME }}" user: "{{ RDS_USER }}" password: "{{ RDS_PASSWORD }}" sql: "ALTER TABLESPACE domain_data AUTOEXTEND ON NEXT 1G MAXSIZE {{ DOMAIN_DATA_MAX }}"

[oravirt]

Ok, great! I’ll spin up a RDS instance as soon as I can to see if I can reproduce this.