Plugin info

name: ora-ts-alloc

Allocates tablespace capacity when low.

Prerequisites

• Linux
  • User Groups: oinstall, dba
• Windows
  • Group Memeberships: ORA_HOMENAME_DBA

Important, if the connection to the database/ASM takes place over BEQ, the process resource limits for myrmex-ad must match those set for the oracle database/ASM processes. Set these limits in the systemd unit file for myrmex-ad and then reload and restart the myrmex-ad service. Refer to https://www.man7.org/linux/man-pages/man5/systemd.exec.5.html#PROCESS_PROPERTIES (opens new window) for the process limit directives and their ulimit equivalents.

Event Subscriptions

• myrmex/ora-ts-status/sid/tablespace/low

Events

• myrmex/ora-ts-alloc/[sid:target db]/[tablespace:tablespace name]/error contains limit enforcement or no available space errors
  • Dimensions
    • sid: the SID the tablespace belongs to
    • tablespace: the name of the tablespace
• myrmex/plugins/error/plugin/msg set any other errors encountered by the plugin execution failure
  • Dimensions
    • plugin the name of the plugin that failed

Alerts

• Oracle tablespace storage auto allocation limit, when allocation limit enforced
• Oracle tablespace storage auto allocation out of space, when no available space to allocate datafiles

Allocation Algorithm

Allocation policies depend on tablespace type, bigfile vs smallfile, and if Oracle managed files are used.

TEMPORARY and UNDO tablespaces are excluded by default

BigFile Tablespaces

The allocation algorithm takes the following actions:

• switches on autoextend option if off
• sets autoextend maxsize to configuration parameter autoextend_max_size if higher than current value

alter database datafile '<data_file_name>' autoextend on maxsize <autoextend_max_size>

If neither of above actions can be satisfied, an alert is emitted containing:

• tablespace name
• bigfile filename
• bigfile mount point
• bigfile mount free space
• bigfile mount size

SmallFile Tablespaces with OMF

The allocation algorithm takes the following actions:

• calculates the total real available space on all datafiles in OMF destination path:

  (maxsize - size) + free, capped by available space on OMF destination mount

• compares real available space to limit in tablespace_free_limit parameter and

  • if less, calculates the free space on the OMF destination mount and compares with parameter initial_size

    • if less, alerts with the following information

      • tablespace name
      • OMF destination
      • OMF mount point
      • OMF mount size
      • OMF mount free space

    • if more, adds datafile with size set to parameter initial_size with autoextend on and maxsize set to parameter autoextend_max_size if defined

      alter tablespace <tablespace_name> add datafile 
            size <initial_size> autoextend on [maxsize <autoextend_max_size>]
      

  • if more, alerts that could not allocate more space with the following information:

    • tablespace name
    • total tablespace datafiles max size
    • total tablespace datafiles size
    • total tablespace datafiles free space
    • tablespace real available space
    • the allocation limit value

SmallFile Tablespaces without OMF

The allocation algorithm takes the following actions:

• calculates the total real available space on all datafiles capped by free space on their respective mount points
• compares total real available space to limit in tablespace_free_limit parameter and
  • if less,
    • for each path in alloc_paths parameter
      • calculates the free space on the path mount point

        • if there is available space add datafile with size set to parameter initial_size with autoxtend on and maxsize set to parameter autoextend_max_size if defined under path and stops

          alter tablespace <tablespace_name> add datafile '<alloc_path>/<tablespace_name>_<seq>.dbf'
                size <initial_size> autoextend on [maxsize <autoextend_max_size>]
          

        • if no available space moves to next path

    • if no space available on any path alert with the following information
      • tablespace name
      • for each path:
        • path
        • path mount point
        • path mount size
        • path mount free space
  • if more, alerts that could not allocate more space with the following information:
    • tablespace name
    • total tablespace datafiles max size
    • total tablespace datafiles size
    • total tablespace datafiles free space
    • tablespace real available space
    • the allocation limit value

Configuration

Plugin is configured with a yaml file:

• timeout: execution timeout in duration format (opens new window). Defaults to 1m

• allocators: list of databases that will allocate tablespace storage

  • name: database name to allocate tablespace space. matches the ora-ts-status event source (database) tag. It does not affect the connection parameters. Defaults to oracle_sid if empty. This is also the source for any events raised by this plugin.
  • oracle_home: set this only if you are logging on via a local (BEQ) connection. Not required when connecting via Oracle Net
  • oracle_sid: set this only if you are logging on via a local (BEQ) connection. Not required when connecting via Oracle Net
  • oracle_logon: the connection string just like in sqlplus. If the username or password contain the characters '@', '/', ' ' escape them in double quotes just like you would do for sqlplus; for example:

  oracle_logon: '"user@name"/"passw@rd"@//localhost:1521/TEST'
  // same applies for secrets
  oracle_logon: '"user@name"/"${polaris.db.test.pwd}"@//localhost:1521/TEST'
  

  • remote_db: set to true of the allocator is not runing on the same host as the database. In this case, it will only allocate for tablespaces on ASM or bigfile tablespaces
  • suppress_alerts: do not emit alerts when no storage could be allocated. default is false
  • allowed_tablespaces: expression which indicates which tablespaces we can allocate space for
  • default_big_file_policy: default allocation policy for big file tablespaces
    • autoextend_max_size: the autoextend max bytes to set if current lower than this
  • big_file_policies: list of per tablespace name big file policies matching on tablespace name regular expression or name list
    • name_regexp: tablespace names matching regular expression
    • names: list of tablespaces by name
    • autoextend_max_size: the autoextend max bytes to set if current lower than this
  • default_small_file_policy: default allocation policy for small file tablespaces
    • omf: set to true if using OMF, default is false
    • alloc_paths: list of filesystem or ASM paths to create new datafiles on. required if omf is false
    • initial_size: the initial datafile size
    • tablespace_free_limit: optional, do not add datafiles if real available space > this value. default is 32g
  • small_file_policies: list of per tablespace name small file policies matching on tablespace name regular expression or name list
    • name_regexp: tablespace names matching regular expression
    • names: list of tablespaces by name
    • omf: set to true if using OMF, default is false
    • alloc_paths: list of filesystem or ASM paths to create new datafiles on. required if omf is false
    • initial_size: the initial datafile size
    • autoextend_max_size: optional maxsize limit for autoextend datafile
    • tablespace_free_limit: optional, do not add datafiles if real available space > this value. default is 32g

Example Configuration

# list of databases that will allocate tablespace storage
allocators:
    # same as ora-ts-status instance_name configuration. 
  - name: ORCL
    # optional, required only when oracle_logon uses BEQ
    oracle_home: /u01/app/oracle/product/12.2.0.1/dbhome_1
    # optional, required only when oracle_logon uses BEQ
    oracle_sid: ORCL
    # the connection string just like in sqlplus
    oracle_logon: / as sysdba
    # do not emit alerts when no storage could be allocated. default is false
    suppress_alerts: false
    # optional, defines the tablespace names that the plugin is allowd allocate storage for. defaults to any tablespace. 
    # temporary and undo tablespaces are always excluded from storage allocation.
    #
    # the list of allowed tablesapces is defined through an expression that operates on the tablespace name.
    # supported expression operators and types:
    #   - Comparators                    : > >= < <= == !=
    #   - Regexp Comparators             : =~ !~
    #   - Membership                     : IN
    #   - Logical ops                    : || &&
    #   - Prefixes                       : !
    #   - String constants               : single quotes eg 'foobar'
    #   - Parenthesis                    : () controls order of evaluation 
    #   - Arrays                         : anything separated by , within parenthesis (1, 2, 'foo')
    #
    # the following parameters are passed by the system by name and can be used as operands in expressions:
    #   - current tablespace             : ts
    #
    # examples:
    # - allocate for tablespace USERS, EXAMPLE
    #    ts IN ('USERS', 'EXAMPLE')
    # - allocate for tablespaces that begin with DATA or INDEX
    #    ts =~ '^(DATA|INDEX).+'  
    # - allocate for tablespace that begin with DATA or INDEX and tablespace USERS
    #    ts =~ '^(DATA|INDEX).+' || ts == 'USERS'
    # - allocate for all tablespace excluding SYSTEM and any tablespce containing the word EXAMPLE
    #    !(ts == 'SYSTEM' || ts =~ 'EXAMPLE')
    allowed_tablespaces: ts != 'SYSTEM' && ts !~ 'EXAMPLE'

    # default allocation policy for big file tablespaces.
    default_big_file_policy:
      # the autoextend maxbytes to set if current lower than this
      autoextend_max_size: 5000g

    # list of per tablespace name big file policies matching on tablespace name regular expression or name list
    big_file_policies:  
       # tablespaces matching this name reg exp
      - name_regexp: "^INDEX.+"
      # tablespaces by name
      # - names: 
      #   - TBS1
      #   - TBS2
      # the autoextend maxbytes to set if current lower than this
      autoextend_max_size: 1000g 

    # default allocation policy for small file tablespaces
    default_small_file_policy:
      # if using oracle managed files, default is false
      omf: true 
      # list of filesystem or ASM paths to create new datafiles on. required if omf is false
      # alloc_paths: 
      #   - /path/1
      #   - /path/2
      # the initial datafile size
      initial_size: 4g
      # optional, do not add datafiles if real available space > this value. default is 32g
      tablespace_free_limit: 8g 

    # list of per tablespace name small file policies matching on tablespace name regular expression or name list
    small_file_policies:
       # tablespaces matching this name reg exp
       - name_regexp: "^INDEX.+"
       # tablespaces by name
       # - names:
       #   - TBS1
       #   - TBS2
      # if using oracle managed files, default is false
      omf: false
      alloc_paths:
        - /path/1
        - /path/2
      initial_size: 4g
      # optional, maxsize for autoextend datafiles
      autoextend_max_size: 500g
      # optional, do not add datafiles if real available space > this value. default is 32g
      tablespace_free_limit: 8g

Validate Configuration

ora-ts-alloc --run-conf /path/to/config/file.yaml --validate

Testing

Run the plugin from the command line to allocate capacity to tablespace. Specify option --simulate, to instruct the plugin not to perform any allocations and only print to stdout the PL/SQL commands to allocate capacity.

ora-ts-alloc --run-conf /path/to/config/file.yaml --run-events/path/to/low/capacity/events.json

Example test event file

[
    {
        "namespace": [
            {
                "value": "myrmex"
            },
            {
                "value": "ora-ts-status"
            },
            {
                "name": "sid",
                "value": "suwpdb1"
            },
            {
                "name": "tablespace",
                "value": "USERS"
            },
            {
                "value": "low"
            }
        ],
        "timestamp": "2019-05-10T18:25:43.511Z",
        "data": 1
    }
]

List events emitted by this plugin

ora-ts-status --events