Oracle
Manageability Infrastructure in the Oracle 10g database
"Fear is
not a disease of the body; fear kills the soul."
Gandhi
|
Oracle
Manageability Infrastructure in the Oracle 10g database
The
components of Oracle manageability Infrastructure are:
Automated
tasks
Server-Generated
alerts
Advisory
Framework
Automatic
Workload Repository
Describe
Automatic Routine Administration tasks:
You
can use the Scheduler, to submit a task that need to be performed for keeping
the database in tune. To add a task, go to the Database Control home page, click
on the Administration tab, click the Jobs link in the Scheduler section, and
then create the task. You may add a task using PL/SQL. See the following
example: Assuming that you have already created the ONLINE_BKUP procedure to
perform online backup. You now want to add that task to the WEEKEND_WINDOW.
SQL>
BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
Job_name => "online_bkup",
Job_type
=>
"STORED_PROCEDURE",
Job_action =>
"myonline_backup",
Job_class
=>
"AUTO_TASKS_JOB_CLASS",
Scheduler_name=>
"WEEKEND_WINDOW");
END;
/
Describe
Server-Generated alerts:
If
a problem was detected, the Oracle server will send an (email) alert message
with possible corrective actions. The difference between Enterprise Manager
Alerts and Server-Generated alerts is mainly that the metrics threshold
validations are performed by MMON, which unlike Enterprise Manager should access
SGA. These alerts appear in DBA_OUTSTANDING_ALERTS and, when cleared, they go to
DBA_ALERT_HISTORY. To set alert thresholds, go to database home page, click
Manage Metrics in the Related links section. Then click the Edit Thresholds
button. You can also use the DBMS_SERVER_ALERT.SET_THRESHOLD procedure. For
example:
SQL>
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD
(
DBMS_SERVER_ALERT.CPU_TIME_PER_CALL,
DBMS_SERVER_ALERT.OPERATOR_GE, "8000",
DBMS_SERVER_ALERT.OPERATOR_GE, "10000", 1, 2, "school",
DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE, "payroll");
END;
/
Describe
Advisory Framework:
They
are server components that provide a DBA with useful feedback about a database
resource utilization and performance. The following are the list of advisors:
ADDM, SQL Tuning Advisor, SQL Access Advisor, PGA Advisor, SGA Advisor, Segment
Advisor, and UNDO Advisor. To open the Advisors Central page, go to the Database
Control home and click on the Advisor Central link in the list of Related Links.
The DBMS_ADVISOR package contains all constants and procedure declarations you
need for all advisors. There are Advisor views such as DBA_ADVISOR_{TASKS | LOG
| OBJECTS | RECOMMENDATIONS | ACTIONS}.
What
is Automatic Workload Repository (AWR)?
It
provides services to Oracle components to collect, maintain, process, and access
performance statistics for problem detection and self-tuning purposes. The MMON
(Manageability Monitor) background process will transfer the memory version of
the statistics every 60 minutes to disk on a regular basis and MMNL
(Manageability Monitor Light) whenever the buffer is full. The workload
repository resides in the SYSAUX tablespace. A baseline can be identified by
executing the DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE procedure. To run
Automatic Workload Repository Reports run the following SQL script.
SQL>
@$ORACLE_HOME/rdbms/admin/awrrpt
Hands-On
#1:
Manually
invoke the ADDM advisor to analyze the database between snapshots 60 and 66.
Then, use the task name to get the results from the analysis
Define
a binding variable to hold the task name and another variable to hold task id.
SQL>
VARIABLE tname VARCHAR2 (60)
SQL>
VARIABLE taskid NUMBER
Create
an advisor task of the particular ADDM type.
SQL>
EXEC dbms_advisor.create_task("ADDM", :taskid, :tname);
Set
the required parameters to run this specific type of task.
SQL>
EXEC dbms_advisor.set_task_parameter(:tname, "START_SNAPSHOT", 60);
SQL>
EXEC dbms_advisor.set_task_parameter(:tname, "END_SNAPSHOT", 66);
Execute
the task.
SQL>
EXEC dbms_advisor.execute_task(:tname);
Use
the task name to get the results from the analysis.
SQL>
SELECT dbms_advisor.get_task_report(:tname)
FROM
dba_advisor_tasks t
WHERE
t.task_name = :tname AND
t.owner = SYS_CONTEXT ("userenv", "session_user")
/
Hands-On
#2:
As
sysdba, create a special procedure to dequeue alert information from the
ALERT_QUE. Then give an object privilege to SYSTEM to use it.
Logging
into SQL*PLUS as sysdba
SQL>
CONNECT / as sysdba
Add
a new subscriber ALERT_MYUSERS to the internal ALERT_QUE queue.
SQL>
EXEC dbms_aqadm.add_subscriber (
"SYS.ALERT_QUE",
AQ$_AGENT("ALERT_MYUSERS","",0));
Grant
user SYSTEM the right to dequeue from the ALERT_QUE.
SQL>
BEGIN
dbms_aqadm.enable_db_access(
agent_name=>"ALERT_MYUSERS",db_username=>"SYSTEM");
END;
SQL>
BEGIN
dbms_aqadm.grant_queue_privilege
(
Privilege=>"DEQUEUE",
queue_name=>"ALERT_QUE",
Grantee=>"SYSTEM");
END;
Now,
write a Stored PL/SQL procedure that is used by user SYSTEM to dequeue alert
information from the ALERT_QUE.
SQL>
CREATE OR REPLACE PROCEDURE my_dequeue IS
dequeue_options
dbms_aq.dequeue_options_t;
message_properies
dbms_aq.message_properties_t;
message
ALERT_TYPE;
message_handle
RAW(16);
BEGIN
dequeue_options.consumer_name
:= "ALERT_MYUSERS";
dequeue_options.wait
:= dbms_aq.no_wait;
dequeue_options.naviagtion
:= dbms_aq.first_message;
dequeue_options.dequeue_mode
:= dbms_aq.remove;
dbms_aq.dequeue
(
queue_name
=>
"SYS.ALERT_QUE",
dequeue_options
=> dequeue_options,
message_properties
=> message_properties,
payload
=>
message,
msgid
=> message_handle);
Dbms_output.put_line("This
is my alert message dequeue"");
END;
/
Grant
"EXECUTE" object privilege on MY_DEQUEUE to SYSTEM.
SQL>
GRANT EXECUTE ON MY_DEQUEUE TO SYSTEM;
Hands-On
#3:
Set
the USER Commits Per Sec metric with a warning threshold set 3, and a critical
threshold set to 7. Your observation period should be for one minute, and the
number of consecutive occurrences should be set to 2.
SQL>
BEGIN
DBMS_SERVER_ALERT.set_threshold
(
DBMS_SERVER_ALERT.user_commits_sec,
DBMS_SERVER_ALERT.operator_ge, 3,
DBMS_SERVER_ALERT.operator_ge, 7,
1,2, "school",
DBMS_SERVER_ALERT.object_type_system, null);
END:
Check
that the metrics thresholds have been set.
SQL>
COL object_name FORMAT a30
SQL>
COL metrics_name FORMAT a30
SQL>
COL warning_value FORMAT a10
SQL>
COL critical_value FORMAT a10
SQL>
SELECT object_name, metrics_name, warning_value, critical_value
FROM
dba_thresholds;
Hands-On
#4:
How
to examine your outstanding alerts and alert history?
Create
a table and add a record to it. Then write a stored PL/SQL procedure to trigger
the alert. Then check the alert.
Create
a new table and insert one row in it.
SQL>
CREATE TABLE test_alert (c number);
SQL>
INSERT INTO test_alert VALUE (10);
SQL>
COMMIT;
Check
your outstanding alerts and the alert history before running your PL/SQL
procedure.
SQL>
SELECT reason FROM dba_outstanding_alerts;
SQL>
SELECT reason FROM dba_alert_history
WHERE
upper(reason) like "%COMMIT%"
ORDER
BY creation_time desc
/
Write
a script to generate a commit rate of five commits per second for three minutes
on your system.
SQL>
BEGIN
--
This will run for about 3 minutes on your machine.
FOR
this IN 1..300 LOOP
FOR that IN 1..5 LOOP
UPDATE test_alert SET c=200;
COMMIT;
END LOOP;
DBMS_LOCK.SLEEP(1); --
Go to sleep
END
LOOP:
END;
/
Open
a new SQL*PLUS session and check your outstanding alerts and the alert history
while your PL/SQL procedure is running.
SQL>
SELECT reason FROM dba_outstanding_alerts;
SQL>
SELECT reason FROM dba_alert_history
WHERE
upper(reason) like "%COMMIT%"
ORDER
BY creation_time desc
/
Because
this time the commit rate is maintained above the critical level for more than
two minutes, you should get a critical alert. Wait for three more minutes, and
check again and notice since the commit rate is now close to zero, the alert is
automatically cleared.
Hands-On
#4:
How
to clean up your threshold set up?
Do
the following statement as sysdba. Set threshold values to NULL.
SQL>
BEGIN
DBMS_SERVER_ALERT.set_threshold
(
DBMS_SERVER_ALERT.user_commits_sec,
NULL,
NULL,
NULL,
NULL,
1, 1, "SCHOOL",
DBMS_SERVER_ALERT.object_type_system, NULL);
END;
/
Disable
your ALERT_MYUSER and remove subscriber.
SQL>
EXEC dbms_aqadm.disable_db_access("ALERT_MYUSER","SYSTEM");
SQL>
BEGIN
dbms_aqadm.remove_subscriber(
"SYS.ALERT_QUE",AQ$_AGENT("ALERT_MYUSER","",0);
END;
/
|