Export Direct=Y - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 39

Thread: Export Direct=Y

  1. #11
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by jmodic
    The main question I see here is: Why on earth would someone want to take full export of a database every six hours? And with CONSISTENT=Y on top of that!!??

    If this is your main mean of backup then your backup-recovery scenario simply sucks. If it is not, then why would you do it 4 times a day?
    Now, now jmodic. You've got to learn to roll your eyes, sigh, and move on.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  2. #12
    Join Date
    Jan 2001
    Posts
    3,131
    Originally posted by marist89
    Originally posted by Mr.Hanky

    COMMIT=Y

    With your buffer at 4 million bytes you will be commiting every 4 megs (roughly). This is better than the oracle default which is to try and import the entire table before a commit. You will also run the risk of blowing out your rollback segs, or tablespace if it is too small.
    Of course, you realize Mr.H is taking about import here and not export.

    And, if we check the docs ( http://technet.oracle.com/docs/produ...ch01.htm#46045 ) we notice that BUFFER does not apply for DIRECT export.
    I really have to break this habit of mine, it is a re-occuring problem. At least he will have a head start on the import.

    MH
    I remember when this place was cool.

  3. #13
    Join Date
    Feb 2001
    Posts
    99
    WOW

    Okay, I have done some initial testing of this. Here is my results.

    Machine Dell PowerEdge 8500, 4-p3'700s. Have opitmezed disk structures, multiple raid0+1's, basically built the ultimate dream system we theorized in our Oracle classes. I am only user on box at the moement.

    Export file size, approx 1.8gigs

    Did conventional export. consistent=y, full=y all other defaults take. Exp time was 24 minutes.

    Did Direct=y export, taking defaults, consistent=y, full=y
    Exp time was 3 minutes. yes, 3 minutes.

    Wow..think I will be modifying my current production backups to do direct vs conventional.

    Thoughts.

  4. #14
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by DcsoBob
    basically built the ultimate dream system
    wet dream: http://www.sun.com/servers/highend/sunfire15k/
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #15
    Join Date
    Feb 2001
    Posts
    99
    "Why the hell would I do a full export" Because my BOSS told me that we will do it that way. It is NOT our only backup scheme, it is just one piece. We are using autoarchive, we do hot backups, coldbackups, etc. The logical exports are just a piece of our scheme.

    I am fully understanding the concept of archive logs, redo logs, full recoverys, etc, etc. AND, I explained these concepts in full detail to my boss.

    I am a 24x7 jail. Our system has to be there to assist in inmate control. And, since we have been hit with one tornado already, we have to be prepared to go elsewhere. With an export, all I need is a box and oracle. up time is minimal.

    And yes, the exports are copied away to a new location after every export.

    Yes, once we get our upgrade to our system done, replication on a different geographcially seperated server will happen, thus negating the need for 6 hour exports.

    It all boils down to this Mr. Moderator. When the boss says do it, you do it. Or find new work.

    Thank you for the helpful input. One day, I hope to be an Oracle expert such as yourself. Until then, I will just keep learning.

  6. #16
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by DcsoBob
    Wow..think I will be modifying my current production backups to do direct vs conventional.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #17
    Join Date
    Feb 2001
    Posts
    290
    Hanky,
    Would you explian how commit, is useful in export ,

    i didnt find this parameter for exp in my DB, just kidding , may be you are confused...


    DscoBOB,

    As Hanky suggested you have to set the NLS_LANG ( this is OS environmental variable )

    Now you query the database as
    select * from v$nls_parameters;

    and check for character set.. with that, now you have to do

    set NLS_LANG=american_america.

    To my understanding the RECORDLENGTH parameter will be used in conjunction with DIRECT=Y, as this specifies the size of the I/O buffer.. ( multiples of OS file system block size )
    This improves the perfomrnace by working

    Hope this time you will succeed.. And why dont you consider
    Jurij Modic ideas ??/
    Madhu Reddy
    xdollor@yahoo.com

  8. #18
    Join Date
    Feb 2001
    Posts
    99
    I would be happy to consider Mr. mordics thoughts, if he had given one. His only input was criticism, not a suggestion.

    As I said, I have a reason for what I am doing. "Directed" to do it.

    I will be looking at the recordlength suggestion.

    I did review the oracle docs refering to the limitations of DIRECT vs Conventional.

    I did get the export to work after modifying the NLS parameter in my winodws env variables to do it. Now, just have to make sure that I can import the export!

    I would like to thank all those that gave suggestions. It has helped me considerably.

  9. #19
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Obviously I deeply offended you, Mr. Junior Member, Although it was not my intention to do so. From your initial post I concluded that export was your main backup scheme and that you might not be aware of the consequences. Obviously you are, however your boss is not, and you failed to explain to him why taking full exp 4 times a day is idiotic.

    You say you are 7x24 shop. OK, but there are couple of things that contradict this. For example, your shop obviously can afford to loose 6 hours worth of data! And you said you also do cold backlups - how can you do them in 7x24 schedule? And in the case of failure, if you are relying on exports obviously you can afford to be down for about half a day (appart from loosing 6 hours data). If your (conventional) export is taking an hour to export, then it will take at least 3 hours to import it. And before that you will have to create the whole new database to import into. This might take another 3 hours or even more.

    You say you are also doing hot backups, and that is the right way to go in 7x24, but if this is so, I realy can't understand the reason for doing full exp 4 times a day. Your boss might thing he has his valid reasons for this, but he definitelly is wrong, and you should put more effort to make him understand this.

    Once again - taking full exp every 6 hours is ludicrous if you ask me. I can't find any valid reason for doing it in 7x24 environment. And I can find strong reasons aginst doing it. But, hey, it's your database.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #20
    Join Date
    Feb 2001
    Posts
    99
    Not deeply offended, was just having a rather bad day that day, and your post was the last straw. I look to this forum for help. Normally, flames just roll off my back; I don't take them personally, but .....

    While we are a 24x7 shop, we do have a small maint window when things are slow. Thats 5-9am on a Sunday morning, the slowest time in our jail. So, we can go down once per month for cold backups, with nightly hot-backups. For legal reasons, data has to be available. Should our system go down, we go to manual operations. When we return, the officers will update the system with the manual records where possible.

    I should also explain that while the export is large, the VAST bulk of the export is audit data for the application. I am exporting 1.8 gigs at this time. However, one table in my system is proably 1.6 gigs of that. It is currently at 9mil records. Its a table used to log user actions within the application. again, legal requirements mean we need to show who has done what with an inmate. I will be working this week to initate an archiving scheme for this table that will reduce this table down to about 60days worth of data. That will be about 1.5 million rows. That should drop the export down to about 3 or 400megs. which does not strain my system.

    I cannot agree more that the exports are silly. However, someone prior to me ( pretty sure it was the vendor) convinced my boss that it was a safe form of backup and easy to recover from. Since our system went live a shade over a year ago, I have been in full development/bug fix mode. I have YET to be able to sit down and really adminster this system. Between righting pl/sql code to make the system do things that were not developed (because our users want more and we have limited budget!! ), app version testing, etc, etc, I have just not had the time to show the boss that the exports are unwarranted. Until I can setup a test server, copy the db and archive logs over, and show her it works, she will be unconvinced and continues to insist on it.

    You think 6 hours as bad. When I was first hired and taking my first oracle classes, she had me doing 4 hours. It took me about 8mths to get that to 6 hours. I think I have about got her talked into 12. While I have not convinced her yet, I am slowly whittling her down.

    SO, I continue my fight. As to having done enough to convince, I have argued the issue to the point of my boss saying -- I will not change this plan until such time as you can prove to me that the archives work as advertised. Don't bring it up again.-- Figure that is as far as I go until I can show her with timing test, etc.

    Until then, I have to do these exports, so I am attempting to reduce the load on my system and users. It appears that DIRECT is the way to go.

    Given the fact that you had to do these exports as well, what would you recommend.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width