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.
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.
"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.
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...
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
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 ??/
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?
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.