|
‘Fear is not a
disease of the body; fear kills the soul.’ Gandhi |
Oracle Manageability
Infrastructure in the Oracle 10g database
Components of Manageability
Infrastructure
The components of Oracle
manageability Infrastructure are:
· Automated
Routine Administration tasks or Automated Tasks
· Server-Generated
alerts
· Advisory
Framework
· Automatic
Workload Repository
Automatic 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;
/
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;
/
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}.
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-Invoking the
ADDM advisor
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-De-queuing
alert
As sysdba, create a special
procedure to de-queue 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 de-queue 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-Setting
tablespace’s threshold
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-Examining
outstanding alerts
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-Cleaning
threshold
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;
/
|
“I refuse to accept
the view that mankind is so tragically bound to the starless
midnight of racism and war that the bright daybreak of peace and
brotherhood can never become reality. I believe that unarmed
truth and unconditional love will have the final word.” Martin
Luther King Jr. |
Questions:
Questions on
Oracle
Manageability Infrastructure in the Oracle 10g database
Q: What are the components
of Oracle manageability Infrastructure?
Q: Describe Automatic
Routine Administration tasks:
Q: Describe
Server-Generated alerts:
Q: Describe Advisory
Framework:
Q: What is Automatic
Workload Repository (AWR)?
Q: 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
Q: As sysdba, create a
special procedure to de-queue alert information from the ALERT_QUEUE.
Then give an object privilege to SYSTEM to use it.
Q: 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 two.
Q: How do you examine your
outstanding alerts and alert history?
Q: How do you disable your
ALERT_MYUSER and remove subscriber?
|