8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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...