13 Replies Latest reply: Mar 4, 2013 8:28 PM by Hemant K Chitale RSS

    Logging

    khurram82
      How to enable logging on partition table in oracle 11G .




      Regards
        • 1. Re: Logging
          sb92075
          khurram82 wrote:
          How to enable logging on partition table in oracle 11G .



          logging of what to where?
          • 2. Re: Logging
            khurram82
            As the table contains blob and we want to generate redo log information
            • 3. Re: Logging
              damorgan
              What makes you think you are not doing that by default? Did you disable logging? If so how?

              And in what version of the product?
              • 4. Re: Logging
                khurram82
                As partition table contains nologging clause and when we backup the database and restored the database on some other machine and applied archive logs then it does not contain the blob data partially

                Edited by: khurram82 on Mar 1, 2013 7:57 AM
                • 5. Re: Logging
                  damorgan
                  NOLOGGING? Not in any database I would manage. I would strongly advise that you re-enable logging immediately unless your data has no value. My general rule as DBA is the following:
                  ALTER DATABASE FORCE LOGGING;
                  which keeps people from deciding to compromise system integrity for what try to justify as being faster performance.

                  In your case my first vote would be the above FORCE LOGGING statement. My second choice would be that you re-enable logging on that table. My third choice would be to not have my fingerprints on what is happening in your database. ;-)
                  • 6. Re: Logging
                    khurram82
                    we have run alter database force logging and also alter table tablename logging but still when we query
                    dba_tables and check logging column , it shows null values , So my question how to enable logging
                    • 7. Re: Logging
                      sb92075
                      khurram82 wrote:
                      we have run alter database force logging and also alter table tablename logging but still when we query
                      dba_tables and check logging column , it shows null values , So my question how to enable logging
                      did you check LOGGING column from NEW session after issuing the DDL above?
                      • 8. Re: Logging
                        damorgan
                        Please post the DML you are using to check these things. We have no idea where you are looking and what you are seeing.
                        • 9. Re: Logging
                          khurram82
                          select logging from dba_tables where owner = 'XXX , it will return YES , null values against few tables

                          regards
                          • 10. Re: Logging
                            mbobak
                            Dan,

                            I wouldn't be so quick to demonize NOLOGGING. I think it serves a purpose. When doing large data loads, judicious use of NOLOGGING can safe massive time and massive volumes of I/O.

                            Also, note that even if a table does have NOLOGGING set, all changes are still going to log, unless you're doing direct load inserts. (i.e. INSERT /*+ APPEND */ or CTAS.)

                            Finally, with a clear understanding of NOLOGGING and it's implications to your backup and recovery strategy, you should be able to design a system that enjoys the performance benefits of NOLOGGING, while minimizing the risk to database integrity.

                            -Mark
                            • 11. Re: Logging
                              Hemant K Chitale
                              null values against few tables
                              If it is a partitioned table, the LOGGING attribute (YES or NO/NONE) will be against each Partition of the table.
                              You should query USER_TAB_PARTITIONS for such a table.
                              (If it is SubPartitioned, you should query USER_TAB_SUBPARTITIONS).

                              Logging is a Segment Level attribute. For Partitioned/SubPartitioned Tables, the Tables are not the segments, it is the Partitions/SubPartitions that are the segments.

                              (LOGGING appears as YES or NO or NULL in USER_TABLES and YES or NONE in USER_TAB_PARTITIONS).


                              Hemant K Chitale

                              Edited by: Hemant K Chitale on Mar 4, 2013 10:36 AM
                              • 12. Re: Logging
                                khurram82
                                I agree with you , my table is partition table but how to enable logging against each partition once it is created with nologging option



                                Regards
                                • 13. Re: Logging
                                  Hemant K Chitale
                                  It is still an ALTER TABLE tablename MODIFY PARTITION partitionname NOLOGGING;

                                  Hemant K Chitale