I have multiple databases writing to NFS for backups, and the NFS will
be going down for MW.
Is there any way to suspend RMAN jobs w/o downtiming all the
databases, or w/o suspending individual backup jobs?

NetComrade:

> I have multiple databases writing to NFS for backups, and the NFS will
> be going down for MW.
> Is there any way to suspend RMAN jobs w/o downtiming all the
> databases, or w/o suspending individual backup jobs?

Put em in cron!  Or some kind of external scheduler!

Let's see ... if your datbase is having issues and you have something
that is supposed to run but depends on the database ... hmmm ...

If you have something outside the database that doesn't depend on the
database can you put together something that does some checking and
alerting when needed ... hmmm ...

On Wed, 28 Apr 2010 06:52:36 -0700, NetComrade wrote:

> I have multiple databases writing to NFS for backups, and the NFS will
> be going down for MW.
> Is there any way to suspend RMAN jobs w/o downtiming all the databases,
> or w/o suspending individual backup jobs?


I use the following "kill 'em all" snippet:


declare
cursor csr(usr varchar2) is select sid,serial# from v$session 
                            where username=usr and
                            status != 'KILLED';
cmd varchar2(40):='alter system kill session ''';
err_msg varchar2(80);
begin
for c in csr(upper('&usr')) loop
   begin
      execute immediate cmd||c.sid||','||c.serial#||'''';
   exception
      when others then
         dbms_output.put_line('Killing of the SID:'||c.sid||' failed.');
         err_msg:=substr(SQLERRM,1,80);
         dbms_output.put_line(err_msg);
    end;
end loop;
end;
/

It shouldn't be a problem to modify it to fit your purpose. I named this 
little snippet "cnorris.sql" because it delivers the fatal roundhouse 
kick to the all sessions belonging to the targeted user.

-- 
http://mgogala.byethost5.com

On Apr 28, 2:52=A0pm, John Hurley <hurleyjo...@yahoo.com> wrote:
> NetComrade:
>
> > I have multiple databases writing to NFS for backups, and the NFS will
> > be going down for MW.
> > Is there any way to suspend RMAN jobs w/o downtiming all the
> > databases, or w/o suspending individual backup jobs?
>
> Put em in cron! =A0Or some kind of external scheduler!
>
> Let's see ... if your datbase is having issues and you have something
> that is supposed to run but depends on the database ... hmmm ...
>
> If you have something outside the database that doesn't depend on the
> database can you put together something that does some checking and
> alerting when needed ... hmmm ...


We do all oracle related jobs through grid control, including custom
monitoring scripts, that way we don't have to hunt down outputs from
cron.
If my database is having issues, my grid has nothing to do with it,
it's on different host and a different database.
If my database is not available we fail back to prompt to toubleshoot
it, but we ensure our alerting works otherwise.
With 20-30 databases to track all over the place, cron is really not a
good option. Plus, how is modifying cron on 2-3 dozen hosts easier
then clicking through a few jobs in a single interface?

On Apr 28, 3:25=A0pm, Mladen Gogala <n...@email.here.invalid> wrote:
> On Wed, 28 Apr 2010 06:52:36 -0700, NetComrade wrote:
> > I have multiple databases writing to NFS for backups, and the NFS will
> > be going down for MW.
> > Is there any way to suspend RMAN jobs w/o downtiming all the databases,
> > or w/o suspending individual backup jobs?
>
> I use the following "kill 'em all" snippet:
>
> declare
> cursor csr(usr varchar2) is select sid,serial# from v$session
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 where username=3D=
usr and
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 status !=3D 'KILL=
ED';
> cmd varchar2(40):=3D'alter system kill session ''';
> err_msg varchar2(80);
> begin
> for c in csr(upper('&usr')) loop
> =A0 =A0begin
> =A0 =A0 =A0 execute immediate cmd||c.sid||','||c.serial#||'''';
> =A0 =A0exception
> =A0 =A0 =A0 when others then
> =A0 =A0 =A0 =A0 =A0dbms_output.put_line('Killing of the SID:'||c.sid||' f=
ailed.');
> =A0 =A0 =A0 =A0 =A0err_msg:=3Dsubstr(SQLERRM,1,80);
> =A0 =A0 =A0 =A0 =A0dbms_output.put_line(err_msg);
> =A0 =A0 end;
> end loop;
> end;
> /
>
> It shouldn't be a problem to modify it to fit your purpose. I named this
> little snippet "cnorris.sql" because it delivers the fatal roundhouse
> kick to the all sessions belonging to the targeted user.
>
> --http://mgogala.byethost5.com

Well, this is killing sessions per a certain criteria. What I want to
do is suspend all jobs.. I guess downtiming databases will be the way
to go, or identifying all jobs that should run during a MW

On Apr 28, 1:39=A0pm, NetComrade <netcomr...@gmail.com> wrote:
> On Apr 28, 2:52=A0pm, John Hurley <hurleyjo...@yahoo.com> wrote:
>
> > NetComrade:
>
> > > I have multiple databases writing to NFS for backups, and the NFS wil=
l
> > > be going down for MW.
> > > Is there any way to suspend RMAN jobs w/o downtiming all the
> > > databases, or w/o suspending individual backup jobs?
>
> > Put em in cron! =A0Or some kind of external scheduler!
>
> > Let's see ... if your datbase is having issues and you have something
> > that is supposed to run but depends on the database ... hmmm ...
>
> > If you have something outside the database that doesn't depend on the
> > database can you put together something that does some checking and
> > alerting when needed ... hmmm ...
>
> We do all oracle related jobs through grid control, including custom
> monitoring scripts, that way we don't have to hunt down outputs from
> cron.
> If my database is having issues, my grid has nothing to do with it,
> it's on different host and a different database.
> If my database is not available we fail back to prompt to toubleshoot
> it, but we ensure our alerting works otherwise.
> With 20-30 databases to track all over the place, cron is really not a
> good option. Plus, how is modifying cron on 2-3 dozen hosts easier
> then clicking through a few jobs in a single interface?

Well obviously, in the situation you have here, you can write a spider
script to modify everything everywhere a lot easier with shells,
assuming one could have tight control over the crons.  It's common
(er, ought to be common) in cron based backups to have a marker file
tested to see if the backup should run, just like it is common in unix
startup scripts for everything in the rc's.

I don't know anything about grid, but I have seen with dbconsole the
same problem as http://technology.amis.nl/blog/2892/how-to-stop-running-rma=
n-jobs-in-oem-grid-control
which kind of suggests to me there might be something about
mgmt_job_engine that could handle your issue.  Searching for that on
MOS finds some interesting things, maybe a support call would be
worthwhile, even if just to give a wishlist item.  It might be a
hindsight forehead-slapper for them, or there may be an answer.  Or
they may not care, DBA 2.0 have already had everything thought out for
them!

jg
--
@home.com is bogus.
http://news.slashdot.org/story/10/04/27/2113224/Arizona-Papers-Please-Law-M=
ay-Hit-Tech-Workers?art_pos=3D26

On Apr 28, 7:30=A0pm, joel garry <joel-ga...@home.com> wrote:
> On Apr 28, 1:39=A0pm, NetComrade <netcomr...@gmail.com> wrote:

> > We do all oracle related jobs through grid control, including custom
> > monitoring scripts, that way we don't have to hunt down outputs from
> > cron.
....
> > With 20-30 databases to track all over the place, cron is really not a
> > good option. Plus, how is modifying cron on 2-3 dozen hosts easier
> > then clicking through a few jobs in a single interface?
>
> Well obviously, in the situation you have here, you can write a spider
> script to modify everything everywhere a lot easier with shells,
> assuming one could have tight control over the crons. =A0It's common
> (er, ought to be common) in cron based backups to have a marker file
> tested to see if the backup should run, just like it is common in unix
> startup scripts for everything in the rc's.
>
> I don't know anything about grid, but I have seen with dbconsole the
> same problem ashttp://technology.amis.nl/blog/2892/how-to-stop-running-rm=
an-jobs-in-...
> which kind of suggests to me there might be something about
> mgmt_job_engine that could handle your issue. =A0Searching for that on
> MOS finds some interesting things, maybe a support call would be
> worthwhile, even if just to give a wishlist item. =A0

This was actually very helpful.. Thanks Joel.
What is MOS?
And I avoid calling support like plague.. usually wasted time.
The quality of support dropped significantly, no-one calls you back,
no-one bothers to read SR notes, and worst thing, are no-one is
actually knowledgeable on the subject they're supposed to be experts
in.


DECLARE
BEGIN
FOR jguid IN
--modify this query for whatever jobs you want to suspend, e.g.
job_name like 'RMAN_%', or job_name like 'RMAN_GROUP%'
(SELECT job_id
FROM mgmt_job
WHERE job_name  like 'RMAN_%'
AND job_owner =3D 'SYSMAN'
AND parent_job_id is null
AND is_library=3D0)
LOOP
BEGIN
--mgmt_job_engine.suspend_job(jguid.job_id);
--mgmt_job_engine.resume_job (jguid.job_id);
--dbms_output.put_line(jguid.job_id);
END;
END LOOP;
END;

On 29 Apr, 21:32, NetComrade <netcomr...@gmail.com> wrote:
<snip>
> What is MOS?

My Oracle Support, formerly "Metalink".

https://support.oracle.com

> And I avoid calling support like plague.. usually wasted time.
> The quality of support dropped significantly, no-one calls you back,
> no-one bothers to read SR notes, and worst thing, are no-one is
> actually knowledgeable on the subject they're supposed to be experts
> in.
<snip>

HTH
-g