RE: Question on LOGGING which not getting enabled permanently

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Mon, 21 Jan 2013 09:25:35 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF01FEDD93_at_WIN02.hotsos.com>

Also be aware that NOLOGGING only affects direct path operations (insert append and bulk operations for example). So it may or may not be an issue for your backups.

If you really want to enforce logging use FORCE LOGGING. This can be done at the tablespace level and the database level. To see if it's on at the database level: SELECT force_logging FROM v$database;
And for the tablespaces:
SELECT tablespace_name, force_logging FROM dba_tablespaces;

To set it:
ALTER DATABASE FORCE LOGGING;
ALTER TABLESPACE <name of tablespace> FORCE LOGGING;

To turn it off is "NO FORCE LOGGING".

This will override the setting of any table or partition level setting of logging. For example if set at the database level it will take precedence over the tablespace or table/partition level setting. Which is opposite of most parameters.

• Ric

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sriram Kumar Sent: Monday, January 21, 2013 6:33 AM
To: dbaorapps_at_gmail.com
Cc: oracle-l_at_freelists.org; ora-apps-dba_at_freelists.org Subject: Re: Question on LOGGING which not getting enabled permanently

Hi,
Tablespace logging level takes precedence if no explicit option is provided. check the tablespace level settings for the new partitions and do an alter tablespace logging to change that behaviour

best regards

sriram kumar

On Mon, Jan 21, 2013 at 4:31 PM, Apps DBA <dbaorapps_at_gmail.com> wrote:

> Hi Gurus,
> We have a scenario in the database related to LOGGING. We enabled
> LOGGING option for a table and had altered the INITRANS option from 2
> to 11 and 10 for Table and Indexes respectively. What we found that
> for all the existing partitions and indexes, the LOGGING got enabled
> but for any new partitions NOLOGGING still coming out to be.
> If NOLOGGING continue to remain, then it may impact the DB backup as
> well as future recoveries. Has anyone come across this or what other
> changes have to be done? Please advice.
>
> Thanks,
> A.Shiva
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jan 21 2013 - 16:25:35 CET