This discussion is archived
1 2 3 4 Previous Next 49 Replies Latest reply: Jan 21, 2013 11:08 AM by 864103 Go to original post RSS
  • 30. Re: nologging
    jgarry Guru
    Currently Being Moderated
    damorgan wrote:
    How can a "test" system be someone else's production? A system either is a line-of-business system or it is not.
    Two obvious things: As Tom Kyte points out (I think normally to say you need archivelog and recovery) non-prod is someone's prod; and sandbox systems for users to try complex operations without screwing up production. Yes, sometimes they do run into expectations issues due to noarchivelog or plan changes. That's life. You can have plan-change issues in production too.

    >
    The bottom line on any discussion is a risk-reward determination. What risk is the customer willing to accept to compromise on an optimum configuration.
    Risk/Reward/Cost/Benefit is biased towards costs when accountants get involved.

    >
    If your customer is willing to give you three hours, three days, three week, three months to recover a system and restore it to operational capacity then perhaps NOARCHIVE, NOLOGGING, and No RMAN can be justified.
    In reality, it's "hey, you want your special db refreshed, let me know a day in advance." Then I push a few buttons and it all happens automatically overnight. Sometimes their special screwups can be refreshed on demand, depends what they did. They tend to think in Excel analogies after all, my job to make it happen or put it in terms they understand why not. People want their own copies of the database and don't really know how to ask for a subset that reflects their needs. "Duplicate Database" doesn't always reflect that either.

    >
    But to post advice in these forums based on these unusual system requirements may well lead people astray who's employers purchased the Oracle database because they want robust sytems, systems that are operational 7x24x365, systems that are their to meet business needs on demand.
    If they miss the part about making explicit SLA's (which I for one repeat ad naseum), not much we can do about it. It's certainly better than "Best Practice" advice, which isn't.

    >
    I don't even run my laptop in NOARCHIVE mode. I'm not even running the 12c Beta in NOARCHIVE mode. And I can not believe that you can truly justify doing so in your environment. I would be very interested in hearing the business arguments that appear to make this a reasonable choice juxtaposed against the cost of purchasing sufficient SSDs to eliminate the risk.
    Not buying SSDs is much cheaper than buying SSDs. Existing systems are often perfectly adequate (and yes, sometimes not - we just acquired a aix/cobol/vsam oriented division, should be interesting times). Acquiring storage is not linear, you have periodic budget cycles, different groups jockeying for space, various replication propagation methods beyond just Oracle, labor/administration, etc. etc. It's not just running down to Fry's and scooping up some T-sized drives.
  • 31. Re: nologging
    damorgan Oracle ACE Director
    Currently Being Moderated
    I don't know how large the adoption of technologies that force archive logging ... DataGuard and Streams ... but while it is growing I suspect it far to low even in North America and Europe.

    What I find absolutely incredible is the number of DBAs that find it acceptable to work late evenings, weekends, and holidays and haven't the ability (or courage) to sit down with management and explain to them the very small cost of purchasing the hardware that would improve performance and mitigate the risk.

    I just completed a consulting contract in October for one of the world's largest retailer's e-commerce divisions: They were running everything on 1U-type pizza box servers with a SAN so old EMC was trying to figure out how take their name off the box. For years management refused to spend money on upgrading the hardware ... why spend money when everything is "working." Of course the DBA team was 3X larger than it needed to be because so much DBA time was spent hand-holding the aging hardware ... and there were the occasional outages ... but hey "it was working."

    They now have the right hardware and amazingly enough the cost was $0 once they realized the amount of money being wasted on business-as-usual operations.
  • 32. Re: nologging
    rp0428 Guru
    Currently Being Moderated
    >
    What I find absolutely incredible is the number of DBAs that find it acceptable to work late evenings, weekends, and holidays and haven't the ability (or courage) to sit down with management and explain to them the very small cost of purchasing the hardware that would improve performance and mitigate the risk.
    >
    On my consulting jobs I usually find that current DBA staff HAS sat down with management and explained that to them.

    Incredibly enough, management seems to pay more attention when they hire an expensive consultant that essentially tells them the same thing.

    Many a time the DBAs just roll their eyes when I make the same recommendations, in writing, in my summary report knowing that they have been saying some of the same things for months or years.

    Ah, one of the 'perks' of being a consultant and the base of my favorite definition

    CONSULTANT: someone who borrows your watch to tell you what time it is.
  • 33. Re: nologging
    damorgan Oracle ACE Director
    Currently Being Moderated
    You are correct. Being a consultant for so many years I forgot that much of the time I get paid to read other people's watches for them.

    But on the other hand a tremendous amount of the time I walk into situations where the DBAs don't just say NO! Let me give you an example without naming names. Client's DBAs spent a week struggling to perform a restoration when disk was lost including in some cases manually retyping contents of lookup tables from paper reports. When I suggested that what they should have done was tell management they could not recover it they were horrified. My report to their management said they were extremely lucky and that most likely the next time the system would be toast. New hardware was purchased.

    What I find is that most often the problem is that DBAs talk to their management in tech terms rather than dollars. Any conversation that can be had in money will always trump the same conversation held in performance metrics. A discussion in MTR will never overwhelm an argument from someone who thinks in FTEs.

    Part of what consultants do is say what the internal DBA said ... but do it in the proper language. When I wanted to move the customer referenced above to ODAs I demonstrated that a $50K ODA cost less than a $20K Dell ... and it is really easy to do if you give it some thought. And it doesn't take a lot of thought to make honest number prove the point.
  • 34. Re: nologging
    Dizwell Newbie
    Currently Being Moderated
    Clearly we have different sorts of consulting gigs.

    You seem to inhabit a world in which businesses are desperate to spend money and just need to be told on what.

    Lucky you.

    I deal with businesses that have problems that need a resolution -and quite often, there won't be much cash to implement it. (This may come as a shock to you, I realise). Quite often there will be all sorts of constraints the business knows itself to be under, which prevent the 'splash some cash' approach.

    Bizarrely, there are even businesses out there that want actually to exploit all their resources before shelling out for more. An imaginative workaround in software can be just as effective as a bunch of new hard disks, and a lot more cost-effective besides.

    In any event, I think that if a management team doesn't notice when their data has been missing for a week, and doesn't realise their DBAs are re-keying it from paper reports, is probably facing a problem rather more fundamental than simply not having DBAs that can't say 'NO'. I doubt all the new hardware spend in the world will resolve that particular business problem!
  • 35. Re: nologging
    Dizwell Newbie
    Currently Being Moderated
    Aren't (or won't they been soon) a significant portion of mission-critical databases Data Guarded? Certainly, that's mostly true in our world. In which case, you don't get a choice whether you do NOLOGGING or not.
    You can perfectly well run Data Guard with nologging tables present. You just have to deal with them when the standby is activated. By definition, you'd have used nologging for something that is recoverable without redo (i.e., can be re-loaded from external sources, for example). So there's nothing to stop you running with one or two reporting tables (say) in nologging mode and, at the point of activation of the standby, you drop or truncate them and re-create them. You can also use incremental rman backups to refresh the standby datafiles before the point of activation, if that's the sort of thing you want to try.

    I'm not suggesting that's what most people would do (or actually do). Just that it's syntactically and logically allowed and you are under no obligation to set FORCE LOGGING, even with Data Guard. You always have choices! :-)
  • 36. Re: nologging
    864103 Newbie
    Currently Being Moderated
    thanks guys.
  • 37. Re: nologging
    damorgan Oracle ACE Director
    Currently Being Moderated
    Businesses everywhere want to get business done at the lowest reasonable cost. If you can prove financial savings by spending money ... they will find it ... and they will spend it.

    Consider this (from a US perspective of course) ... every DBA costs $100K/year. If I go to management and say "I want you to spend $150K on new hardware but you can eliminate one DBA position" the math after three years shows a savings of $150K with lower costs for recruiting, training, desk space, hardware, and software.

    The one thing I expect everyone here will find universal agreement in is that most IT management is far better at managing people than technology. If you want to get their investment you have to speak their language ... and, like it or not, that language is expressed in spreadsheet cells.
  • 38. Re: nologging
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    damorgan wrote:
    Businesses everywhere want to get business done at the lowest reasonable cost. If you can prove financial savings by spending money ... they will find it ... and they will spend it.

    Consider this (from a US perspective of course) ... every DBA costs $100K/year. If I go to management and say "I want you to spend $150K on new hardware but you can eliminate one DBA position" the math after three years shows a savings of $150K with lower costs for recruiting, training, desk space, hardware, and software.
    Just to offer a different perspective, I have occasionally been on sites where DBAs have had to spend literally days doing something massively labour-intensive (and, on rare occasions, system-threatening) because DBA's were viewed as a sunk cost - and not paid the appropriate overtime - while getting a new piece of equipment to do a job properly, safely, and quickly, would be new expenditure.

    Regards
    Jonathan Lewis
  • 39. Re: nologging
    864103 Newbie
    Currently Being Moderated
    hi Jonathan Lewis,

    just to be clear , due to till now i'm confusing cuz the tom said if your in archive mod and your table in nologging and use append in hint
    there is no redo will generated ??? but i tested it gerenrated redo ,
    so there is no way to avoid generated redo but if you use nologging the redo that generated will be decreased ???

    i will be high appreciated if you are response on my question ...

    thanks
  • 40. Re: nologging
    damorgan Oracle ACE Director
    Currently Being Moderated
    Again from a US perspective as that is the only one I can speak to with any authority ... if you are paid $100,000/yr to work 40 hours per week for 50 weeks per year ... and you are fool enough to work 60-75 hours per week (I know many that do) for that same income ... congratulations you just cut your income to roughly what you would get as an intermediate level SQL developer. A choice every DBA is free to make if they wish.

    I could be wrong on this but I believe the origin of the international labor movement, a century ago, had something to do with labour not wanting to be taken advantage of. How things have changed.

    But to address your point directly Jonathan you are correct. If an organization has 6 DBAs management feels free to work them as hard as the fools are willing to work and it is hard to blame them for trying. My point was slightly different. My point was that if I can show management how to cut back to 5 DBAs (thus the savings) in exchange for purchasing the new hardware ... they will buy the hardware and reassign the desk.

    Now the way I wrote this was rather cruel in that I posited one of my fellow employees queuing up for unemployment benefits. So rework it to not expanding the team, or rework it to not replace someone that has voluntarily left the organization ... the point I believe is still valid ... Oracle has been selling "lower TCO" (Total Cost of Ownership) with great success to management for decades and I don't see Larry worrying about how he'll pay for a new winch on his boat. Offsetting hardware costs with lower FTEs will almost always sell.
  • 41. Re: nologging
    damorgan Oracle ACE Director
    Currently Being Moderated
    It is impossible to eliminate redo ... you can only minimize it. Very simply, and please correct me Jonathan if I get this incorrect ... the undo transaction will always be logged.

    But back to the point of my original post ... I have in my experience worked on some of the most massive high transaction rate databases on the planet and never once found a justification for nologging. Those I have seen advocate it have almost always chosen it because they didn't want to do the real work necessary to address the underlying issue.
  • 42. Re: nologging
    Dizwell Newbie
    Currently Being Moderated
    And I think that sums it up. You have worked "on some of the most massive *high transaction rate* databases", so your experience is transactional and OLTP, not warehousing or OLAP environments. Those are frequently not transactional in nature because they often have data available for re-loading from external, transactionally-protected sources. So your experience does not warrant the nonsense suggestion that nologging is never justified.

    I can't work out whether I take more umbrage at your ill-informed sweeping generalisations, or your casually giving offence to anyone who has contemplated these issues deeply and decided, on appropriate occasions, that switching on nologging is precisely the right thing to do to address the 'underlying issue'.

    I realise you are chasing your 15,000th post, but quality, not quantity, Daniel. Just because you've never seen fit to use an option Oracle provides and supports, it doesn't mean no-one else has.
  • 43. Re: nologging
    864103 Newbie
    Currently Being Moderated
    me too wait Jonathan's response for this issue due to i read all articals from TOM and i know the tom is good DBA
    but i tested and it generated redo
    NAME                                                                  VALUE
    ---------------------------------------------------------------- ----------
    redo size                                                            172472
    
    SQL> l
      1  select name,a.value
      2  from v$sesstat a, v$sysstat b
      3  where b.statistic#=a.statistic#
      4* and b.name = 'redo size' and sid =95
    
    
    SQL> alter table x nologging;
    
    Table altered.
    
    SQL>
    SQL>
    SQL>
    SQL> insert /*+ APPEND */ into x
      2  select * from y;
    
    107 rows created.
    
    
    NAME                                                                  VALUE
    ---------------------------------------------------------------- ----------
    redo size                                                            174976
    so it is increased but TOM's said it not generated redo , my database in archive mode
    but i think it generate redo but less than that one in logging .
  • 44. Re: nologging
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    861100 wrote:
    hi Jonathan Lewis,

    just to be clear , due to till now i'm confusing cuz the tom said if your in archive mod and your table in nologging and use append in hint
    there is no redo will generated ??? but i tested it gerenrated redo ,
    so there is no way to avoid generated redo but if you use nologging the redo that generated will be decreased ???
    Thumbnaill sketch:

    If you are running the database, or the tablespace, with FORCE LOGGING then nologging doesn't apply.

    If the table appears at either end of a referential integrity constraint, or has a trigger on it, then nologging doesn't apply (there may be a couple of other reasons that I don't recall at present).

    Nologging applies only for direct path loading, or such things as Create table as select, alter table move, alter index rebuild, create index.

    When you succeed in loading a table nologging, but have to maintain indexes at the same time, the changes to the indexes will be logged in the normal way, even if they have need declared as nologging.

    The nologging directive applies to the data changes for the object, NOT to the metadata, so any changes relating to (e.g.) space allocation will result in some logging activity. For example, if you are using freelist management then Oracle bumps the highwater mark by 5 blocks every time it needs some new space. this will result in a redo record saying something like "the five blocks from xxx.yyy to xxx.zzz are corrupt".

    Try to keep the nologging and /*+ append */ concepts separate. The /*+ append */ hint will load data above the high water mark of the table - which means Oracle doesn't need to generate UNDO, which reduces the amount of redo - but /*+ append */ doesn't have to be used in conjunction with nologging.

    Example - insert (as select) 45,000 rows into a table, reporting "redo size" and "undo change vector size"
    Baseline insert:
    redo size                                                            1,459,804
    undo change vector size                                                111,704
    
    insert /*+ append */
    redo size                                                            1,326,620
    Note that there was no undo, and the redo has dropped accordingly
    Now changing the table to NOLOGGING
    redo size                                                            1,459,788
    undo change vector size                                                111,704
    
    /*+ append */
    redo size                                                                2,240
    undo change vector size                                                    492
    We still get logged - until we use the append hint, which makes the insert a direct path load above the highwater mark - so we get (virtually) no redo or undo.


    Regards
    Jonathan Lewis

    Edited by: Jonathan Lewis on Jan 21, 2013 11:23 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points