21 October 2013

Notes: Deploy Oracle 11g Agent from Grid

Login to grid application server:
            cd $ORACLE_HOME/sysman/prov/resources/scripts
            /usr/bin/bash ./sshUserSetup.sh -user oracle -hosts hostname
            (don't specify passphase)
            ssh hostname date (check that it works)

Each agent installation has 3 steps: Connectivity, Pre-reqs and then install. In the case you know everything is set up properly, but the steps are still failing, to skip one or both of the first two steps:
            Set step1/2 to false in 
            /opt/oracle/middleware/oms11g/sysman/prov/agentpush/agentpush.properties

Cleanup the old installation on zasap1:
            $ORACLE_HOME/oui/bin/runInstaller -silent -detachHome ORACLE_HOME="/opt/oracle/middleware/agent11g"

Logon as "SYSMAN" to the 11g Grid Control Console and deploy the 11g Grid Control Agent:
            Select the "Deployments"/ "Install Agent" / "Fresh Install" link

Once deployed:
            Install Opatch
            Add it to the PATH
            Apply patches


// posted by Tatjana (Tanja) Kane @ 10/21/2013 0 comments

04 October 2013

Setting up Log Miner (notes)

Check utl_file_dir parameter is set (restart instance is required)

--Build dictionary to join object IDs with the real names:
EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/opt/oracle/diag/rdbms/db/DB/trace', OPTIONS=DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

--Add logfile(s)
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME ='/data1/oradata/archivelogs/DB/arch_1_23566_823518714.arc', -
           OPTIONS =dbms_logmnr.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME ='/data1/oradata/archivelogs/DB/arch_1_23567_823518714.arc', -
           OPTIONS =dbms_logmnr.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME ='/data1/oradata/archivelogs/DB/arch_1_23568_823518714.arc', -
           OPTIONS =dbms_logmnr.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME = '/data1/oradata/archivelogs/DB/arch_1_23569_823518714.arc', -
           OPTIONS =dbms_logmnr.ADDFILE);

 -- Check min and max time in the logs
select LOG_ID,FILENAME,
TO_CHAR(LOW_TIME,'DD-MON HH24:MI:SS'),
TO_CHAR(HIGH_TIME,'DD-MON HH24:MI:SS') 
from v$logmnr_logs;
                                                         
-- Start log mining
EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => '/opt/oracle/diag/rdbms/db/DB/trace/dictionary.ora', -
STARTTIME = to_date('26-Sep-2013 15:02:40', 'DD-MON-YYYY HH24:MI:SS'), -
ENDTIME   = to_date('26-Sep-2013 15:15:00', 'DD-MON-YYYY HH24:MI:SS')); 

-- If you want to remove the log file from the mining group
select ' EXECUTE DBMS_LOGMNR.REMOVE_LOGFILE(''',filename,''');' from v$logmnr_logs;

-- Mining
select operation,seg_owner,seg_name,count(*)  from v$logmnr_contents group by seg_owner,seg_name,operation;

select OPERATION, SQL_REDO, OBJECT_ID,REDO_VALUE,
SEG_OWNER,SEG_NAME, START_TIMESTAMP
FROM v$logmnr_contents
…


// posted by Tatjana (Tanja) Kane @ 10/04/2013 0 comments

Oracle Resource Manager: implementing modified simple plan (NOTES)

I set up a Resource Manager recently to manage the CPU allocation on our "Schema as a Service" RAC system. Each application purchases a slice of the system's CPU allocation, and we are managing and/or monitoring the allocation through RM.

Create a simple plan 

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN = 'P01_PLAN',
   CONSUMER_GROUP1 = 'ORA$AUTOTASK_SUB_PLAN', GROUP1_PERCENT = 5,
   CONSUMER_GROUP2 = 'ORA$DIAGNOSTICS', GROUP2_PERCENT = 5,
   CONSUMER_GROUP3 = 'OLTP', GROUP3_PERCENT = 14,
   CONSUMER_GROUP4 = 'Small apps',   GROUP4_PERCENT = 7,
   CONSUMER_GROUP5 = 'Medium Apps', GROUP5_PERCENT = 7,
   CONSUMER_GROUP6 = 'DSS', GROUP6_PERCENT = 7
);
END;
/

This creates 3 levels of consumer groups. 
Percentage were calculated based on the number of applications in each group.

Assign services to the consumer groups :
exec dbms_resource_manager.clear_pending_area;
exec dbms_resource_manager.create_pending_area;

 exec dbms_resource_manager.set_consumer_group_mapping( attribute = dbms_resource_manager.service_name, -
value = 'DBSRVP01', consumer_group = 'MEDIUM APPS');
exec dbms_resource_manager.set_consumer_group_mapping( attribute = dbms_resource_manager.service_name, -
value = 'DBSRVP02', consumer_group = 'SMALL APPS' );
exec dbms_resource_manager.set_consumer_group_mapping( attribute = dbms_resource_manager.service_name, -
value = 'DBSRVP03', consumer_group = 'SMALL APPS');
exec dbms_resource_manager.set_consumer_group_mapping( attribute = dbms_resource_manager.service_name, -
value = 'DBSRVP04', consumer_group = 'OLTP');
exec dbms_resource_manager.set_consumer_group_mapping( attribute = dbms_resource_manager.service_name, -
value = 'DBSRVP05', consumer_group = 'OLTP');
exec dbms_resource_manager.set_consumer_group_mapping( attribute = dbms_resource_manager.service_name, -
value = 'DBSRVP06', consumer_group = 'OLTP');
exec dbms_resource_manager.set_consumer_group_mapping( attribute = dbms_resource_manager.service_name, -
value = 'DBSRVP07', consumer_group = 'OLTP');
exec dbms_resource_manager.set_consumer_group_mapping( attribute = dbms_resource_manager.service_name, -

 value = 'DBSRVP08', consumer_group = 'DSS');

Put a hard core limit on Medium Apps:
EXEC dbms_resource_manager.create_plan_directive(-
plan='P01_PLAN', group_or_subplan= 'Medium Apps', -
max_utilization_limit = 7, comment= 'Put a hard core on Medium apps');
exec dbms_resource_manager.submit_pending_area;

--Allow privileges
exec dbms_resource_manager_privs.grant_switch_consumer_group( -
grantee_name = 'PUBLIC', consumer_group = 'SMALL APPS', grant_option = FALSE);
exec dbms_resource_manager_privs.grant_switch_consumer_group( -
grantee_name = 'PUBLIC', consumer_group = 'MEDIUM APPS', grant_option = FALSE);
exec dbms_resource_manager_privs.grant_switch_consumer_group( -
grantee_name = 'PUBLIC', consumer_group = 'OLTP', grant_option = FALSE);
exec dbms_resource_manager_privs.grant_switch_consumer_group( -

 grantee_name = 'PUBLIC', consumer_group = 'DSS', grant_option = FALSE);

Manage the RM:
exec dbms_resource_manager_privs.grant_system_privilege(grantee_name = 'USERNAME', admin_option = true);

Switch the plan on:
alter system set resource_manager_plan = 'P01_PLAN' sid = '*';

Switch the plan off:
alter system set resource_manager_plan = 'INTERNAL_PLAN' sid = '*';

MonitoringOverview

Select the current plan:
select name, cpu_managed from v$rsrc_plan where is_top_plan = 'TRUE';

Check which plans have been enabled/disabled in the past:
SELECT name plan_name,
to_char(start_time, 'DD-MON-YY HH24:MM') start_time,
to_char(end_time, 'DD-MON-YY HH24:MM') end_time, window_name
FROM v$rsrc_plan_history order by 2 desc;

To check to which resource group the session belongs to:
select sid, username, service_name, resource_consumer_group from v$session
where RESOURCE_CONSUMER_GROUP!='_ORACLE_BACKGROUND_GROUP_';

CPU Monitoring

Throthling:
select to_char(begin_time, 'HH:MI') time,
 60 * (select value from v$osstat where stat_name = 'NUM_CPUS') total,
 60 * (select value from v$parameter where name = 'cpu_count') db_total,
 sum(cpu_consumed_time) / 1000 consumed,
 sum(cpu_wait_time) / 1000 throttled
from gv$rsrcmgrmetric_history
group by begin_time order by begin_time;

Waits:

Throttling by Resource Manager can be monitored by the wait event “resmgr:cpu quantum”, or scheduler” wait class. These waits can be seen by generating an AWR report and examining the “Foreground Wait Events”.

“dbtime_in_wait” shows the percentage of database time spent in Resource Manager waits.
“time_waited” shows the actual wait time in microseconds.

select to_char(h.begin_time, 'HH:MI') time,
 h.average_waiter_count, h.dbtime_in_wait, h.time_waited
from v$waitclassmetric_history h, v$system_wait_class c
where h.wait_class_id = c.wait_class_id and c.wait_class = ' SCHEDULER'
order by h.begin_time;

CPU

Actual CPU utilization per Consumer Group:
select to_char(begin_time, 'HH:MI') time,
consumer_group_name,
 60 * (select value from v$osstat where stat_name = 'NUM_CPUS') total,
 60 * (select value from v$parameter where name = 'cpu_count') db_total,
cpu_consumed_time / 1000 consumed,
cpu_consumed_time / (select value from v$parameter where name =
 'cpu_count') / 600 cpu_utilization,
cpu_wait_time / 1000 throttled
from v$rsrcmgrmetric_history
order by begin_time;
"throttled" should be very low or zero.

Check current resource groups, CPU consumption, queing for CPU (queue length should be zero or close):
SELECT name, active_sessions, queue_length,
  consumed_cpu_time, cpu_waits, cpu_wait_time
  FROM v$rsrc_consumer_group;

The same, just sessions queing for the CPU time:
SELECT se.sid sess_id, co.name consumer_group,
 se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time
 FROM v$rsrc_session_info se, v$rsrc_consumer_group co
 WHERE se.current_consumer_group_id = co.id;

Privileges

If the session is not in the resource group it should be, first check that it has privileges to switch to that group and when it mapped:
select grantee, granted_group from DBA_RSRC_CONSUMER_GROUP_PRIVS
order by granted_group;

select r.sid,
r.mapped_consumer_group,
r.mapping_attribute,
c.consumer_group original_consumer_group
from v$rsrc_session_info r, dba_rsrc_consumer_groups c
where r.orig_consumer_group_id = c.consumer_group_id;

--Any SIDs that switched from one group to another:
select r.sid,
c1.consumer_group original_consumer_group,
c2.consumer_group current_consumer_group
from v$rsrc_session_info r, dba_rsrc_consumer_groups c1,
dba_rsrc_consumer_groups c2
where r.orig_consumer_group_id = c1.consumer_group_id
and r.current_consumer_group_id = c2.consumer_group_id
and r.orig_consumer_group_id != r.current_consumer_group_id;


List groups that have AUTO switch properties:
select plan, group_or_subplan, switch_time, switch_io_megabytes, switch_io_reqs
from dba_rsrc_plan_directives
where plan in (select name from v$rsrc_plan)
 and (switch_time is not null
 or switch_io_megabytes is not null
 or switch_io_reqs is not null);


Other views in DBA_RSRC% and (G)V$RSRC%


// posted by Tatjana (Tanja) Kane @ 10/04/2013 0 comments

This page is powered by Blogger. Isn't yours?