Learn "NOLOGGING" with Tablespace "force logging" mode

After I learned, with...

If the database, or that tablespace, is in "force logging" mode, the nologging will not work.

Check Database is Archive Mode:

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 101
Next log sequence to archive 103
Current log sequence 103

Check REDO Size with "create table" logging:

SQL> @redo-new

OLD_VALUE
----------
1860

SQL> create table T_LOGGING tablespace TBS1 as select * from dba_objects;

Table created.

SQL> @redo-diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 1860) OLD_VALUE

OLD_VALUE
----------
8622128

Check REDO Size with "create table" nologging:

SQL> @redo-new

OLD_VALUE
----------
784

SQL> create table T_NOLOGGING nologging tablespace TBS1 as select * from dba_objects;

Table created.

SQL> @redo-diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 784) OLD_VALUE

OLD_VALUE
----------
105448

Check REDO Size with "create table" nologging on Tablespace "force logging" mode:

SQL> select tablespace_name, force_logging from dba_tablespaces where tablespace_name='TBS1';

TABLESPACE_NAME FOR
------------------------------ ---
TBS1 NO

SQL> alter tablespace TBS1 force logging;

Tablespace altered.

SQL> select tablespace_name, force_logging from dba_tablespaces where tablespace_name='TBS1';

TABLESPACE_NAME FOR
------------------------------ ---
TBS1 YES

SQL> @redo-new

OLD_VALUE
----------
788

SQL> create table T_NOLOGGING_F nologging tablespace TBS1 as select * from dba_objects;

Table created.

SQL> @redo-diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 788) OLD_VALUE

OLD_VALUE
----------
8640936

That's right, NOLOGGING isn't work on database or tablespace in "force logging" mode.

redo-new.sql:

column OLD_VALUE new_value OLD_VALUE
select value OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';

redo-diff.sql:

select (value - &OLD_VALUE) OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';

Learn "NOLOGGING" from forums

I often learn something(oracle) and find idea from OTN Forums.

And This Topic(Wait Events "log file parallel write" / "log file sync" during CREATE INDEX), It wrote about nologging option, “log file sync” and “log file parallel write” waits, and it's replied by Jonathan Lewis and Uwe Hesse.

Jonathan Lewis noted 2 points with "create index" nologging option:

it's "only" an index, so you could always rebuild it in the event of media corruption, but if you had lots of indexes created nologging this might cause an unreasonable delay before the system was usable again - so you should decide on a fallback option, such as taking a new backup of the tablespace as soon as all the nologging operatons had completed.

If the database, or that tablespace, is in "force logging" mode, the nologging will not work.

INSERT/UPDATE/DELETE will generate redo log on NOLOGGING mode ?

After I read about Redo & Undo in Expert Oracle Database Architecture book by Kyte, He told about NOLOGGING...

Nologging doesn't mean all operations in that object will not generate redo log.

And Francisco Munoz A 's Paper told ... Nologging will generate a minimal number of redo log entries in order to protect the data dictionary.

That make my curious about nologging mode with DML. I use it on some tables and some indexes... So I should know a real thing about it by myself.

Francisco Munoz A 's Paper and Oracle Docs Idea ... INSERT/UPDATE/DELETE will generate a real redo with table/index NOLOGGING mode, except INSERT /*+APPEND+/

Begin test... NOLOGGING + DML on 11g Archivelog Mode.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled

Script:

--new.sql
column OLD_VALUE new_value OLD_VALUE
select value OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';

--diff.sql
select (value - &OLD_VALUE) OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';

- Create TABLE -

SQL> @begin

OLD_VALUE
----------
0

SQL> create table T_NOLOG nologging as select * from all_objects;

Table created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 0) OLD_VALUE

OLD_VALUE
----------
133120

REDO SIZE=133120

SQL> @begin

OLD_VALUE
----------
133120

SQL> create table T_LOG logging as select * from all_objects;

Table created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 133120) OLD_VALUE

OLD_VALUE
----------
8748596

REDO SIZE=8748596

Create table with NOLOGGING... not generate redo log (just generate redo log for data dictionary)

After create table... It's time to test with DML:

- DELETE -

SQL> @begin

OLD_VALUE
----------
8881716

SQL> DELETE FROM T_NOLOG ;

70999 rows deleted.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 8881716) OLD_VALUE

OLD_VALUE
----------
27076168

REDO SIZE=27076168

SQL> @begin

OLD_VALUE
----------
35958052

SQL> DELETE FROM T_LOG;

71000 rows deleted.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 35958052) OLD_VALUE

OLD_VALUE
----------
27076692

REDO SIZE=27076692

- INSERT -

SQL> @begin

OLD_VALUE
----------
63034912

SQL> INSERT INTO T_NOLOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 63034912) OLD_VALUE

OLD_VALUE
----------
8493412

REDO SIZE=8493412

SQL> @begin

OLD_VALUE
----------
71528324

SQL>INSERT INTO T_LOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 71528324) OLD_VALUE

OLD_VALUE
----------
8493360

REDO SIZE=8493360

- UPDATE -

SQL> @begin

OLD_VALUE
----------
80021684

SQL> UPDATE T_NOLOG SET OBJECT_ID=1;

71000 rows updated.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 80021684) OLD_VALUE

OLD_VALUE
----------
24671048

REDO SIZE=24671048

SQL> @begin

OLD_VALUE
----------
104692732

SQL> UPDATE T_LOG SET OBJECT_ID=1;

71000 rows updated.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 104692732) OLD_VALUE

OLD_VALUE
----------
20911424

REDO SIZE=20911424

On DML INSERT/UPDATE/DELETE ... Oracle generated redo log on nologging mode not difference... on logging mode.

And I need to know about it on INSERT /*+ APPEND */:

- INSERT "APPEND" hints -

- table NOLOGGING mode and not use APPEND hints

SQL> @begin

OLD_VALUE
----------
125604156

SQL> INSERT INTO T_NOLOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 125604156) OLD_VALUE

OLD_VALUE
----------
8586036

REDO SIZE=8586036

SQL> @begin

OLD_VALUE
----------
142830588

SQL> INSERT /*+ APPEND */ INTO T_NOLOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 142830588) OLD_VALUE

OLD_VALUE
----------
29448

REDO SIZE=29448


- table LOGGING mode, and use APPEND hints

SQL> @begin

OLD_VALUE
----------
134190192

SQL> INSERT /*+ APPEND */ INTO T_LOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 134190192) OLD_VALUE

OLD_VALUE
----------
8640396

REDO SIZE=8640396

*** make table logging to nologging ***

SQL> alter table t_log nologging ;

Table altered.

SQL> @begin

OLD_VALUE
----------
142874676

SQL> INSERT /*+ APPEND */ INTO T_LOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 142874676) OLD_VALUE

OLD_VALUE
----------
27956

REDO SIZE=27956

APPEND hints on table "LOGGING" mode "select logging from dba_tables" (NO) ... not difference (generate redo)

If "alter table nologging" before, and then insert (append)

So, Oracle will generate a minimal number of redo log... with INSERT /*+ APPEND */ when table be nologging ... select logging from dba_tables (NO)

That just testing... But helpful to understand something ;)

reference: http://docs.google.com/View?id=dhg2wncg_30mpkpd5cc

ORA-26040: Data block was loaded using the NOLOGGING option

After I recover my database, I found below error...
What's going on?

select count(*) from TEMP01;

*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 20)
ORA-01110: data file 4: '+DATA1/db/datafile/users.260.670078195'
ORA-26040: Data block was loaded using the NOLOGGING option

what is ORA-26040?
and this error can not solve by rman "BLOCKRECOVER".

If find out on internet you will find;

Error: ORA-26040
Text: Data block was loaded using the NOLOGGING option
---------------------------------------------------------------------------
Cause: Trying to access data in block that was loaded without
redo generation using the NOLOGGING/UNRECOVERABLE option

Action: Drop the object containing the block.

that's a idea to solve it, right!... Absolutely.

I asked myself How can I know before my database error like that.

This cause ... I use NOLOGGING mode, NOLOGGING mode which will prevent the table from being rolled forward after future changes are applied.

I haven't found ORA-26040 error (after test) when I insert/delete/update with NOLOGGING.

But I have found ... exactly, when I create new table with NOLOGGING.

EXAMPLE:

create table TEMP01 nologging as select * from all_objects;

How can I know, before datafiles fail and error ORA-26040.

Using rman to check by "report unrecoverable" command,
Anyway It'll be better when use rman to backup/recovery as well.

when I found this error, I will backup datafiles by rman.

SQL> create table TEMP01 nologging as select * from all_objects;

Table created.

SQL> select table_name, tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME
------------------------------ ---------------------
TEMP01 USERS

$ rman target /

RMAN> report unrecoverable;

using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
4 full or incremental +DATA1/db/datafile/users.260.670078195

RMAN> backup datafile 4;
.
.
Finished backup

RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------

Not Found

I don't sure why i use insert/update/delete with NOLOGGING mode and not found error.

SQL> create table TEMP02 logging as select * from all_objects where rownum <=1 ; Table created.

SQL> insert into TEMP02 nologging select * from all_objects;

414 rows created.

SQL> commit;

Commit complete.

$ rman target /

RMAN> report unrecoverable;

using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
Not Found

That make sure; If I need to leave from UNRECOVERABLE with creating new table, I should use LOGGING mode.

However, check... check and check database [use RMAN with "report unrecoverable" and backup ... backup] ... that make database leave from UNRECOVERABLE as well.