11g, Unable to do nolog operations

[lyx...@gmail.com]

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

[Mark D Powell]

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 --

[steved...@gmail.com]

On Oct 2, 4:41 am, "lyx...@gmail.com" <lyx...@gmail.com> wrote:

I just tried your test on 11.1.0.7, and the datafile was reflected as
having a current unrecoverable time after the operation.

[lyx...@gmail.com]

Thanks Mark & Steve for looking into this.

Mark - I agree with you, that's why I created table in "TEST"
tablespace. Same nologging operation ( CTAS nologging ) is updating
unrecoverable_time in 9i & 10g.
Steve also confirmed in 11.1.0.7.
Even I created index in nologging mode but still no luck

Regards,

[Mladen Gogala]

On Fri, 02 Oct 2009 13:11:24 -0700, Mark D Powell wrote:

> 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.

You can't set UNDO tablespace to nologging. UNDO is always logged,
otherwise recovery would not be possible. As for setting system and sysaux
tablespaces to nologging, be prepared to encounter some unrecoverable
dictionary objects. In other words, your database might become
unrecoverable. Of course, such setting would also mean that the database
is unsupported, too.

--
http://mgogala.freehostia.com