-
moving partitions and generating redo
I have table with a lot of partitions.
The pctfree is set very high for this table. I can alter the table and
reset pctfree and than move each partition to reclaim my space.
Example of what I am doing:
alter table MTAS.MTAS_RESULT pctfree 0;
alter table MTAS.MTAS_RESULT move partition RESULT_20100831 parallel 8;
....
....
...
500 partitions ...
This generates way too much redo, which I don't want.
My questions:
1) is there anyway I can check a view that will show me which DDL is
generating redo. In my example above I would expect to see this
statement.
2) If I use the nologging option, will this avoid generating ALL redo for
this statement?
alter table MTAS.MTAS_RESULT move partition RESULT_20100831 parallel 8 nologging;
I am trying to verify if this is the case by seeing what is actually written to
the redo logs if that is possible
Thanks to all who answer
-
alter table move partition is indeed one of the few statements that will take advantage of "nologging" option.
Only alter table, create index and CTAS statements allow for nologging option.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Paul,
When u say take advantage of "nologging" does that mean no writing to
the redo logs at all or writing to the redo logs will be minimized as opposed
to not using the nologging option.
This is why I was looking for some query so I can exactly what is written
to the .arc files. I read some where that log miner allows you to do this.
I have to see if that is included in our licences agreement
Thanks
-
with nologging redo generation will be reduced very much but still oracle has to generate minimal redo(like redo for undo blocks).
Logminer is part of Oracle RDBMS Enterprise Edition, no additional license required.
Thanks,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
Originally Posted by vnktummala
with nologging redo generation will be reduced very much but still oracle has to generate minimal redo(like redo for undo blocks).
Logminer is part of Oracle RDBMS Enterprise Edition, no additional license required.
Thanks,
Thanks for the update. I new nologging did not mean "NO LOGGING" completely. Is there a way to measure how much logging will be generated?
As last time I did this test I filled up my /archive file system. Want to be
proactive and make sure that does not happen again.
-
Originally Posted by BeefStu
.... I new nologging did not mean "NO LOGGING" completely. Is there a way to measure how much logging will be generated?
Testing is the only alternative - identify a couple of partitions that trully represent your population, run process against them and monitor redo creation then you should be able to infer how much the whole project will generate and at what pace.
When doing partition maintenance I generally set special redo backup jobs running every 15 minutes or so.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
All:
Thanks for your input.
@Paul I was generating about 80GB of redo with out nologging for ever 5
partitions I was moving. I set the nologging option for each partition and
every 15 minutes I ran rman to remove the .arc files this solved my problem.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|