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

olsnodes make CPU spikes

After I read on otn forums.
They told they see high cpu usage, when use "olsnodes" command.

$ time olsnodes
node01
node02

real 0m0.153s
user 0m0.082s
sys 0m0.068s

And then ... solve this by delete * files at ORA_CRS_HOME/log/hostname/client/ path.

$ rm -f $ORA_CRS_HOME/log/node01/client/*

$ time olsnodes
node01
node02

real 0m0.037s
user 0m0.017s
sys 0m0.014s

$ ls $ORA_CRS_HOME/log/node01/client/*
css.log

$ time olsnodes
node01
node02

real 0m0.045s
user 0m0.016s
sys 0m0.015s

$ ls $ORA_CRS_HOME/log/node01/client/*
css1.log css.log

What about idea?

When use "olsnodes" command successfully, it scans $CRS_HOME/log/hostname/client PATH and generates a new cssxxx.log file.

metalink:729349.1 ...said it's a bug:

Olsnodes produces CPU spikes with many css*.log files in ORA_CRS_HOME/log/nodename/client directory

fixed in 11.1.0.7 and 10.2.0.4 CRS bundle Patch #2 onwards. Bundle Patch information is available in Note 756671.1

Try to check on 11.1.0.7 :

$ crsctl query crs softwareversion
Oracle Clusterware version on node [test01] is [11.1.0.7.0]

$ ls $ORA_CRS_HOME/log/test01/client/ | wc -l
32738

$ time olsnodes
test01
test02

real 0m0.030s
user 0m0.015s
sys 0m0.014s

OK... No problem on 11.1.0.7

check RAC Option in Oracle Binary

It's a simple thing for someone to check RAC is linked in the Oracle Binary.

when we need to convert Single database to RAC database... we may find Error

ORA-00439: feature not enabled: Real Application Clusters

and then we may resolve it by relink library (rac_on)

By the way, we can check Oracle binary before;)

cd $ORACLE_HOME/rdbms/lib

nm -r libknlopt.a | grep -c kcsm.o

returns 0, then RAC is not linked.

returns more than 0, then RAC is linked.

Example:

$ cd $ORACLE_HOME/rdbms/lib

$ nm -r libknlopt.a | grep -c kcsm.o

1

RAC is linked in the Oracle Binary

$ cd $ORACLE_HOME/rdbms/lib

$ nm -r libknlopt.a | grep -c kcsm.o

0

RAC is not linked in the Oracle Binary

shell script (one line)->

if [ -f $ORACLE_HOME/rdbms/lib/libknlopt.a ]; then C=`nm -r $ORACLE_HOME/rdbms/lib/libknlopt.a | grep -c kcsm.o` ; if [ $C -gt 0 ] ; then echo 'RAC is linked in $ORACLE' ; else echo 'RAC is not linked in $ORACLE' ;fi fi

That's step to check...

Check the device ASMLib on multi-path

It's just my curious with this forum on oracle. when you check multi-path device with ASMLib, perhaps you can confuse:

Example (Assume EMC Storage):

# /etc/init.d/oracleasm querydisk -d /dev/emcpowera1
Device "/dev/emcpowera1" is marked an ASM disk with the label "ARC1"

# /etc/init.d/oracleasm querydisk -d ARC1
Disk "ARC1" is a valid ASM disk on device [8, 38]

That show major,minor numbers -> 8, 38
So, check on device multi-path:

# ls -la /dev/emcpowera1
brw------- 1 root root 120, 1 Jun 11 17:01 /dev/emcpowera1

That made... confuse ;) because on /dev/emcpowera1 show major,minor numbers -> 120, 1

What's that mean? Because the ASMLib scanned on single path to show.
Really?

# ls -la /dev/sd* | grep 38
brw-rw---- 1 root disk 8, 38 Jun 11 17:01 /dev/sdc1

# powermt display dev=emcpowera
Pseudo name=emcpowera
Owner: default=SP A, current=SP A Array failover mode: 1
==============================================================================
---------------- Host --------------- - Stor - -- I/O Path - -- Stats ---
### HW Path I/O Paths Interf. Mode State Q-IOs Errors
==============================================================================
1 qla2xxx sdc SP A0 active alive 0 0
1 qla2xxx sde SP A1 active alive 0 0
1 qla2xxx sdg SP B0 active alive 0 0
1 qla2xxx sdi SP B1 active alive 0 0

That shows... single paths(sdc,sde,sdg,sdi) on /dev/emcpowera ;)

How we exclude scan on single paths? that's good question ... read on this site.

Excluding Single Path Disks

The system administrator configures ASMLib to ignore the single path disks. In the ASMLib configuration, he edits the ORACLEASM_SCANEXCLUDE variable to look like so:

ORACLEASM_SCANEXCLUDE="sdb sdc"

Here, the system administrator has been more specific. ASMLib should ignore exactly the disks /dev/sdb and /dev/sdc. It should not ignore other SCSI disks. While scanning, ASMLib will ignore those paths, only seeing the /dev/multipath disk. Once again, Oracle will use the multipath disk.

So, make it on example:

Edit /etc/sysconfig/oracleasm file.

# vi /etc/sysconfig/oracleasm
ORACLEASM_SCANEXCLUDE=""
change to =>
ORACLEASM_SCANEXCLUDE="sd"

And then restart:
#/etc/init.d/oracleasm restart

So, Check again:

# /etc/init.d/oracleasm querydisk /dev/emcpowera1
Device "/dev/emcpowera1" is marked an ASM disk with the label "ARC1"

# /etc/init.d/oracleasm querydisk -d ARC1
Disk "ARC1" is a valid ASM disk on device [120, 1]

# ls -al /dev/emcpowera1
brw------- 1 root root 120, 1 Jun 12 18:33 /dev/emcpowera1

On ASMLib show major,minor numbers -> 120, 1; that like on multi-path device (asmlib no scan on single paths) ;)