Great info... please expound...
Robert, September 29, 2008 - 6:56 pm UTC
Tom,
On Multiplexing ARCHIVED LOGS...
We have apparently decided that we will risk losing data and having down time by NOT multiplexing REDO.
The thinking was this...
We do have EMC mirroring on online and archived redo
Chance of o/s or file system corruption going undetected by EMC will be *very* rare.
We can 'afford' losing a little data and some down time vs. cost of extra disks, overhead, etc.
I raised this red flag when an archived redo log on my QA standby database was corrupted (at o/s level... presumably because I let the archlog location fill up. (I recovered in this case by copying over the archlog copy from the primary)).
Now granted, this is not as risky as running a production database in NOARCHIVELOG mode... so do you think this is totally stupid or can you see their point of view?
On DBA_JOBS vs. DBA_SCHEDULER
Why would DBA_JOBS NOT commiting affect (improve) its functionality?
Thanks,
Robert.
September 29, 2008 - 8:24 pm UTC
... We have apparently decided that we will risk losing data and having down time
by NOT multiplexing REDO. ...
that is called penny wise, pound foolish - or "not smart"
make sure you inform the end users "Hey guys, your data is so not relevant, we will (not might - WILL) lose all of your work someday - hahahha - isn't that grand"
You want to know how rare a SAN/Storage failure is - I'm sure they hardly ever happen (sarcasm).
How much will having multiplexed redo logs cost? (not much, if you can even measure it)
How much will NOT having them cost some day? (immeasurable - huge - you lose work, probably forever)
Ditto on the archives - what is the incremental cost of having them, coupled with the knowledge that "you are covered in the event of something you did not anticipate"
Vs
Not having them...
This is as risky as not running in archivelog mode, maybe even more so. At least in noarchivelog you are clearly stating "we will lose our work and we know it, we accept it". Here you have a false sense of security that everything is ok.
dbms_job...
I like to control my transactions. I want to commit when I am ready. What if you wanted to schedule a job in an evil trigger - it is non-transactional with the scheduler. When I queue a job, it is typically part of a larger transaction - the entire transaction is never just "schedule a job", there is other stuff going on.
And, if you just migrate everything over to the scheduler, you will have to hold a code review for each and every occurrence. Because you have just changed the transactional semantics in a huge way, you have to review that things are still "OK"
DBMS_JOB v/s DBMS_SCHEDULER
Anil, September 30, 2008 - 2:55 am UTC
Tom,
We had migrated a database from Oracle 9i (9.2) to Oracle 10g (10.2) and when I tried to schedule the job (in Oracle 10g) using DBMS_JOB it would not then I was forced to schdeule using DBMS_SCHEDULER. Though using DBMS_JOB it didn't throw any error but the job was not running.
Even now we are running the job using DBMS_SCHEDULER. Can you please let me know why I was not able to schedule using DBMS_JOB?
Thanks
Anil
DBMS_JOB v/s DBMS_SCHEDULER
A reader, September 30, 2008 - 4:29 am UTC
The last time I used DBMS_SCHEDULER the sky clouded over and it started to rain. This didn't happen when I used DBMS_JOB please can you explain why.
September 30, 2008 - 8:20 am UTC
You are in London after all - doesn't the sky cloud over all of the time?
Great stuff!!
Robert, September 30, 2008 - 10:32 am UTC
Tom,
Thanks a lot for the great info.
Please one follow up on my original question about SYSTEM and UNDO on ASSM...
Since you said not to put SYSTEM and UNDO on ASSM does that mean
1) we should rely totally on Oracle defaults for all SYSTEM tablespace segments?
or
2) tweak the segment/tablespace parameters ourselves in SYSTEM or UNDO tablespace?
Thanks!
Robert.
September 30, 2008 - 12:55 pm UTC
leave them be, as they are.
DBMS_JOB V/S DBMS_SCHEDULER
Anil, October 01, 2008 - 2:45 am UTC
Tom,
You said,
<<Start>>
then the parameters necessary for dbms_job were not set - it is not that dbms_job doesn't work, you didn't configure it to be "on"
job_queue_processes must have been zero.
<<End>>
Though we had set it to 10, it has not worked.
SQL> show parameter job_queue
NAME TYPE VALUE
------------------------------------ ----------- ------
job_queue_processes integer 10
Thanks
October 01, 2008 - 11:56 am UTC
it was not 10 when you tried before would be my guess.
Tell you what, create a job, use dbms_jobs to schedule it, and see what you see.
ASSM Follow Up....
Robert, October 07, 2008 - 9:02 am UTC
Hi Tom,
You said, above, NOT to use ASSM on SYSTEM and UNDO tablespaces.
Would you then recommend using ASSM on ALL OTHER tablespaces?
Can you think of any extenuating circumstances where you
would NOT use ASSM on non-system/undo tablespaces?
Thanks!
Robert.
October 08, 2008 - 9:21 pm UTC
Use ASSM when you want to waste a bit of space for increased concurrency - or want to take advantage of a feature that is only available with ASSM (like shrinking is only available with ASSM).
If you have a warehouse, ASSM doesn't make as much sense in general, there you want to pack as much as you can in, you have no concurrency issues.
If you have LOB tablespaces - ASSM doesn't make as much sense in general, little concurrency issues there.
DBMS_JOB not running
Gabor, October 08, 2008 - 10:39 am UTC
Anil, I have seen that several times before. You run DBMS_JOB.SUBMIT, but the job doesn't start!
Solution (as Tom mentioned above) : COMMIT;
I'd bet my hat that you didn't commit the DBMS_JOB.SUBMIT.
Multiplexing and Integrity of EMC Mirrored Disk Arrays
Robert, November 15, 2008 - 12:04 pm UTC
Tom,
In a discussion with Metalink analysts on this issue, they said that if EMC could guarantee that disks would not fail (i.e. mirrored disk array) that multiplexing was not necessary.
Also, they said that multiplexing was not necessary to avoid corruption because if corruption was written to one copy it would likewise be written to the other copy.
I have been back and forth with them for several days now and are beginning the think they are speaking double-talk.
For the sake of sanity will you please re-verify the facts on this for me.
Thank you,
Robert.
November 18, 2008 - 6:45 pm UTC
... they said that if EMC
could guarantee that disks would not fail ...
our multiplexing cannot guarantee that, nor can theirs.
... was not necessary to avoid corruption because
if corruption was written to one copy it would likewise be written to the other
copy.
....
and that is entirely "just wrong".
Look at it this way
as a percentage of your overall disk, how much is online redo? Given that online redo is so so so so very important - would extra redundancy be
a) better
b) worse
c) neither a nor b
(I go with a)
Thanks again Tom.
Robert, November 19, 2008 - 12:02 pm UTC
Mr. Tom Kyte,
Thank you again for your informed, scientific, no B.S., common sense, straightforward, experienced, and very generous council!!
Best Regards,
Robert.
What about multiplexing REDO with ASM?
Robert Wood, March 27, 2009 - 9:08 pm UTC
Hi Tom,
One of the other DBA's in my group said that when using ASM (externally mirrored) there is no longer a need to multiplex REDO (either ONLINE or ARCHIVED), the reason being is that one of the main reasons for multiplexing redo is gone... which is operator (DBA, SA) error... since we are no longer navigating around the O/S among these files where we could accidentally delete something.
I still think it is a good idea to multiplex the redo.
What are your thoughts?
Thank you,
Robert.
March 30, 2009 - 3:46 pm UTC
Robert, March 31, 2009 - 11:31 am UTC