Database.fi

Just a real quick one, since I need to do it right away…

EM CC 12c installation blew up and need to re-create, since here are the instructions to remove an existing OEM CC (or OEM GC) repository of a failed installation using the following command:

$/sysman/admin/emdrep/bin/RepManager -dbUser sys -dbPassword -dbRole sysdba -reposName sysman -reposPassword -action dropall [-mwHome ] [-mwOraHome ]


For Oracle Enterprise Manager Database Control (single instance) repository use this command:
emca -deconfig dbcontrol db -repos drop -cluster -SYS_PWD sys1 -SYSMAN_PWD SYS_PASSWORD


HTH,
Pasi

Hello, it’s been a while since last post. Too busy

Here is just a small example of how to perform analysis on a specific time period. Of course with AWR it would be so simple, but if you do not have access to AWR (Standard Edition Oracle for example), you just have to make do with Statspack.

Let’s say that we have a problem which occurs late at night at customer site.
It would be helpful to see what happens between 21 and 23 at night.

Of course we can just make statspack do snaps every 5 minutes and then turn it off later:
BEGIN
DBMS_SCHEDULER.CREATE_JOB ( job_name => 'STATSPACK_COLLECTION',
job_type => 'STORED_PROCEDURE',
job_action => 'statspack.snap',
repeat_interval => 'FREQ=MINUTELY; BYMINUTE=00,05,10,15,20,25,30,35,40,45,50,55',
enabled => TRUE,
comments => 'Statspack Collection'
);
END;
/

But it is way too easy to forget to turn it off and also you would allocate space from perfstat for no good reason. You’d probably end up filling up perfstat tablespace / quota.

The elegant way is to run snaps only for the time period you are interested in.
For that, we need to add a schedule to dbms_scheduler, create a program and tie them together as a job.

We need to create a schedule:
- Starts at 21:00
- Ends at 23:00
- When the schedule is active, Oracle must run statspack.snap every 5 minutes

Fine. How do we do that?

We need to create a schedule like this:

-- run every 5 minute from 21 PM to 23 PM
begin
dbms_scheduler.create_schedule(
schedule_name => 'INTERVAL_EVERY_5_MINUTES_2H',
start_date => trunc(sysdate)+21/24,
repeat_interval => 'freq=MINUTELY;interval=5',
end_date => trunc(sysdate)+23/24,
comments => 'Today from 21 to 23 PM, 5 minute interval');
end;
/


Also we need a program (Oracle scheduler term) to run statspack.snap:
-- Program to run statspack.snap
begin
dbms_scheduler.create_program
(program_name=> 'STATSPACK_SNAP',
program_type=> 'STORED_PROCEDURE',
program_action=> 'statspack.snap',
enabled=>true,
comments=>'Procedure to create statspack snap.'
);
end;
/



begin
-- Create job which will run with the new schedule and program.
dbms_scheduler.create_job
(job_name => 'STATSPACK_COLLECTION',
program_name=> 'STATSPACK_SNAP',
schedule_name=>'INTERVAL_EVERY_5_MINUTES_2H',
enabled=>true,
auto_drop=>false,
comments=>'Run statspack every 5 minutes from 21 to 23 PM today');
end;
/


That should do it.
It will run statspack collection from 21 to 23 PM with 5 minute interval.

Tomorrow you’ll be able to create spreport.sql (statspack reports) from that time period and dig deeper into the problem.

Hope this helps,
Pasi