8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

EMCLI : Manage Tablespace Thresholds using Enterprise Manager Command Line Interface (Cloud Control)

The Enterprise Manager Command Line Interface (EMCLI) allows you to script your Cloud Control setup, rather than using the Cloud Control console. This article describes how to manage tablespace thresholds in Enterprise Manager Cloud Control using EMCLI, rather than using the web interface.

For more information see:

Setup

You can perform these actions from anywhere with an EMCLI client, but for this example we're going to use the EMCLI client on the Cloud Control server. We use the following commands to connect to the OMS and sync the EMCLI client.

unset SSH_ASKPASS
export OMS_HOME=/u01/app/oracle/middleware
export AGENT_HOME=/u01/app/oracle/agent/agent_inst
alias emcli='${OMS_HOME}/bin/emcli'

emcli login -username=sysman
emcli sync

Get Thresholds

The get_threshold verb is used to display a variety of thresholds for a target, including the tablespace thresholds for a database. The -target_name parameter refers to the database name. In this example the -target_type parameter should be set to "oracle_database" or "rac_database" depending on the architecture. Without the -metric parameter all thresholds will be displayed. We on only interested in the tablespace notification metrics in this example.

export DATABASE_NAME=orcl

# Single instance database.
emcli get_threshold -target_name="${DATABASE_NAME}" -target_type="oracle_database" -metric="problemTbsp"

# RAC database.
emcli get_threshold -target_name="${DATABASE_NAME}" -target_type="rac_database" -metric="problemTbsp"

Modify Thresholds

The modify_threshold verb is used to set thresholds. There are a lot of variations, but here are some examples.

## Defaults
export DATABASE_NAME=orcl

# Blank the % Used defaults. Makes then "undefined".
emcli modify_threshold \
  -target_name="${DATABASE_NAME}" \
  -target_type="oracle_database" \
  -metric="problemTbsp" \
  -column="pctUsed" \
  -key_columns=";" \
  -warning_threshold=" " \
  -critical_threshold=" " \
  -force
        
# Blank the Bytes Free defaults. Makes then "undefined".
emcli modify_threshold \
  -target_name="${DATABASE_NAME}" \
  -target_type="oracle_database" \
  -metric="problemTbsp" \
  -column="bytesFree" \
  -key_columns=";" \
  -warning_threshold=" " \
  -critical_threshold=" " \
  -force

# Set default for % Used defaults. (85% warning. 97% critical)
emcli modify_threshold \
  -target_name="${DATABASE_NAME}" \
  -target_type="oracle_database" \
  -metric="problemTbsp" \
  -column="pctUsed" \
  -key_columns=";" \
  -warning_threshold="85" \
  -critical_threshold="97" \
  -force

# Set the Bytes Free defaults. (5G warning. 2G critical)
emcli modify_threshold \
  -target_name="${DATABASE_NAME}" \
  -target_type="oracle_database" \
  -metric="problemTbsp" \
  -column="bytesFree" \
  -key_columns=";" \
  -warning_threshold="5120" \
  -critical_threshold="2048" \
  -force


## Tablespace
export DATABASE_NAME=orcl
export TS_NAME=USERS

# Blank the % Used threshold for the tablespace. Makes then "undefined".
emcli modify_threshold \
  -target_name="${DATABASE_NAME}" \
  -target_type="oracle_database" \
  -metric="problemTbsp" \
  -column="pctUsed" \
  -key_columns="${TS_NAME};" \
  -warning_threshold=" " \
  -critical_threshold=" " \
  -force
        
# Blank the Bytes Free threshold for the tablespace. Makes then "undefined".
emcli modify_threshold \
  -target_name="${DATABASE_NAME}" \
  -target_type="oracle_database" \
  -metric="problemTbsp" \
  -column="bytesFree" \
  -key_columns="${TS_NAME};" \
  -warning_threshold=" " \
  -critical_threshold=" " \
  -force

# Set default for % Used threshold for the tablespace. (85% warning. 97% critical)
emcli modify_threshold \
  -target_name="${DATABASE_NAME}" \
  -target_type="oracle_database" \
  -metric="problemTbsp" \
  -column="pctUsed" \
  -key_columns="${TS_NAME};" \
  -warning_threshold="85" \
  -critical_threshold="97" \
  -force

# Set the Bytes Free threshold for the tablespace. (5G warning. 2G critical)
emcli modify_threshold \
  -target_name="${DATABASE_NAME}" \
  -target_type="oracle_database" \
  -metric="problemTbsp" \
  -column="bytesFree" \
  -key_columns="${TS_NAME};" \
  -warning_threshold="5120" \
  -critical_threshold="2048" \
  -force

Multiple thresholds can be set at once using a parameter file.

# Blank the % Used threshold for the tablespace. Makes then "undefined".
# Set the Bytes Free defaults. (5G warning. 2G critical)
# Set the Bytes Free threshold for the USERS tablespace. (50G warning. 20G critical)

cat > /tmp/param_file.txt <<EOF
START_RECORD 1
metric , problemTbsp
column , pctUsed
key_columns , ;
warning_threshold , " "
critical_threshold , " "
END_RECORD 1

START_RECORD 2
metric , problemTbsp
column , bytesFree
key_columns , ;
warning_threshold , 5120
critical_threshold , 2048
END_RECORD 2

START_RECORD 3
metric , problemTbsp
column , bytesFree
key_columns , USERS;
warning_threshold , 51200
critical_threshold , 20480
END_RECORD 3
EOF

The thresholds can be set with a single command as follows.

emcli modify_threshold \
        -target_name="${DATABASE_NAME}" \
        -target_type="oracle_database" \
        -input_file="FILE:/tmp/param_file.txt" \
        -force

Help

The usage of the commands referenced in this article can displayed using the following commands.

emcli help get_threshold
emcli help modify_threshold

You can also check out all the other job verbs in the Help Command Output.

emcli help

For more information see:

Hope this helps. Regards Tim...

Back to the Top.