DBA Hub

📋Steps in this guide1/4

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

This article describes how to manage tablespace thresholds in Enterprise Manager Cloud Control using EMCLI, rather than using the web interface.

oracle miscconfigurationintermediate
by OracleDba
12 views
1

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
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
2

Get Thresholds

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
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"
3

Modify Thresholds

The verb is used to set thresholds. There are a lot of variations, but here are some examples. Multiple thresholds can be set at once using a parameter file. The thresholds can be set with a single command as follows.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
## 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

# 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

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

Help

The usage of the commands referenced in this article can displayed using the following commands. You can also check out all the other job verbs in the Help Command Output . For more information see: - EMCLI : All Articles Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
emcli help get_threshold
emcli help modify_threshold

emcli help

Comments (0)

Please to add comments

No comments yet. Be the first to comment!