I am working in DWH env and are having too much logs. I have put all tablespaces and objects in nologging, still there is too much redo log generated. It was about 40 GB in two days.
I understand that nologging does not stop logging of RBS. It also appears that long transactions ( long cursors) also wrote a lot of RB ( hence redo for this) even when there is no DML.
How can I reduce the rollback segment write up and so the redo generation.
Create new set of REDO logs with Bigger size and drop the existing.
I am not sure how to Reduce redo logging though
These are the steps to change the redo log size.
If you want to change the logfile size from 500k to 2M then we can follow these steps.
1) Create a new redo log group that has a file size of 2M
(repeat this step for the 3 groups)
2) Force a couple of log switches so that the current redo log is one of the 2M ones.
3) Drop 500K redo log groups
At present what is your logfile size?
Large amount of Redo
Thanks for your reply. My question was not how to handle large amount of redo but how to reduce the redo.
I have already put everthing in nologging mode. Still there is too much redo. I also undersand that it is because writes in rollback segments and not because of DML.
Can somebody tell me how to reduce write on rollback segments when there are large batch jobs.
If you are using SQL*LOADER to load data into table, add UNRECOVERABLE command before LOAD DATA command to reduce redo writes.
Click Here to Expand Forum to Full Width