moving partitions and generating redo
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: moving partitions and generating redo

  1. #1
    Join Date
    Jul 2006
    Posts
    195

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  3. #3
    Join Date
    Jul 2006
    Posts
    195
    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

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    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.

  5. #5
    Join Date
    Jul 2006
    Posts
    195
    Quote Originally Posted by vnktummala View Post
    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.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by BeefStu View Post
    .... 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.

  7. #7
    Join Date
    Jul 2006
    Posts
    195
    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
  •  


Click Here to Expand Forum to Full Width