Re: 11g, Unable to do nolog operations

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Fri, 2 Oct 2009 13:11:24 -0700 (PDT)
Message-ID: <60b182bc-d6b6-4053-b5d3-c14e1fc9c1a6_at_g19g2000yqo.googlegroups.com>

On Oct 2, 4:41 am, "lyx..._at_gmail.com" <lyx..._at_gmail.com> wrote:
> Hi,
>
> I am using 11g R1 on OEL 4.0, I noticed it's not allowing nolog
> operations to me even though database and tablespaces are in nologging
> mode.
>
> I would appreciate if some one have a look.
>
> Connected to:
> Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
> With the Partitioning, Real Application Clusters, OLAP, Data Mining
> and Real Application Testing options
>
> SQL> archive log list
> Database log mode              No Archive Mode
> Automatic archival             Disabled
> Archive destination            /u01/app/oracle/product/11g/dbs/arch
> Oldest online log sequence     9
> Current log sequence           10
> SQL>
> SQL> select force_logging from v$database;
>
> FOR
> ---
> NO
>
> SQL> select tablespace_name,logging from dba_Tablespaces where
>   2  contents = 'PERMANENT';
>
> TABLESPACE_NAME                LOGGING
> ------------------------------ ---------
> SYSTEM                         NOLOGGING
> SYSAUX                         NOLOGGING
> TEST                           NOLOGGING
>
> SQL> conn /
> Connected.
>
> SQL> create table T NOLOGGING tablespace test as select * from
> dba_objects;
>
> Table created.
>
> SQL>
> SQL>
> SQL> select name,unrecoverable_time from v$datafile;
>
> NAME                                               UNRECOVER
> -------------------------------------------------- ---------
> +DG1/test/datafile/system.260.697440875
> +DG1/test/datafile/sysaux.265.697440889
> +DG1/test/datafile/undotbs1.258.697440903
> +DG1/test/datafile/test.261.699167731

The nologging parameter only applies to specific operations generally involving direct insert such as insert append and sqlldr direct load operations.

An individual transaction such as insert one row then commit will always be logged even for an object created as nologging.

There is no valid reason to set the system, sysaux, or an undo tablespace to nologging since Oracle provided features should be the only source of activity on these tablespaces.

For info on using nologging with create table and create index http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tables002.htm#i1106265

HTH -- Mark D Powell -- Received on Fri Oct 02 2009 - 15:11:24 CDT