iSelfSchooling - Oracle Manageability Infrastructure in the Oracle 10g database

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?