Nologging operasyonlar

Tablespace düzeyindeki nologging/logging ifadesi sadece bu tablespace içinde create edilecek objelerin initial logging clause'larını buradan inherit etmeleri içindir yani;

tablespace nologging ise ->

• create table a default tablespace tbs_nologging logging dersem tablo logging olur
• create table a default tablespace tbs_nologging dersem tablo nologging olarak create edilir
• alter table a logging dersem tablo yine logging olur ve değişmez.
  

Tablespace deki ifadenin etkisi sadece bu kadardır.

Bir tablo nologging ise ->

• Direct path operasyonlar redo üretmez. Dolayısıyla backup alınmazsa recover edilemez
• Direct path olmayan normal işlemler redo üretir
• Force logging yapılmışsa direct path işlemler redo üretir

Database noarchive modda ise

• Tablo logging modda ise direct path load redo üretmez çünkü ihtiyaç yoktur

Database arşiv modda ise

• Tablo logging modda ve direct path load yapılıyorsa redo üretir
• Tablo nologging modda ve direct path load yapılıyorsa redo üretmez
  

Direct path load

• Buffer cache'i geç, diskten diske işlem yap demektir
• SQL Layer atlar, undo üretmez
• Datayı alır database bloklarını formatlar ve bunu doğrudan diske yazar
  
• Her zaman redo üretme demek değildir!!! Yukarıdaki koşullara göre değişir

Table Mode    Insert Mode     ArchiveLog mode      result
-----------   -------------   -----------------    ----------
LOGGING       APPEND          ARCHIVE LOG          redo generated
NOLOGGING     APPEND          ARCHIVE LOG          no redo
LOGGING       no append       ""                   redo generated
NOLOGGING     no append       ""                   redo generated
LOGGING       APPEND          noarchive log mode   no redo
NOLOGGING     APPEND          noarchive log mode   no redo
LOGGING       no append       noarchive log mode   redo generated

NOLOGGING     no append       noarchive log mode   redo generated

-- tom kyte yorum...
q1) nologging applies ONLY to direct path operations.

the DDL of the create table is always logged.

An insert /*+ append */ into the table (direct path load) could skip logging.
An insert /* without append */ into the table will always be logged.
A create table as select can skip logging of the table data - BUT NOT the ddl
An alter table t move can skip logging.
An update, insert (noappend), delete will always log.

Tablespace logging clause için örnek
--------------------------------------

ops$tkyte@ORA920> create tablespace log_test
2  datafile size 1m
3  NOLOGGING;

Tablespace created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create table t1 ( x int ) tablespace log_test;

Table created.

ops$tkyte@ORA920> create table t2 ( x int ) tablespace log_test logging;

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select table_name, logging
2    from user_tables
3   where tablespace_name = 'LOG_TEST';

TABLE_NAME                     LOG
------------------------------ ---
T1                             NO
T2                             YES

so, a logging clause on T2 overrides the DEFAULT setting inherited from the tablespace.
further:

ops$tkyte@ORA920>
ops$tkyte@ORA920> alter tablespace log_test logging;

Tablespace altered.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create table t3 ( x int ) tablespace log_test;

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select table_name, logging
2    from user_tables
3   where tablespace_name = 'LOG_TEST';

TABLE_NAME                     LOG
------------------------------ ---
T1                             NO
T2                             YES
T3                             YES

the logging attribute only affects objects created AFTER the change -- T1 is still "no logging"

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> drop tablespace log_test including contents;

Direct path load örnek
----------------------

ops$tkyte@ORA920> create tablespace test_logging;
Tablespace created.

ops$tkyte@ORA920> column file_name new_val f
ops$tkyte@ORA920> select file_name from dba_data_files where tablespace_name = 'TEST_LOGGING';

FILE_NAME
------------------------------
/usr/oracle/ora920/OraHome1/or
adata/ora920/o1_mf_test_log_zg
h7ddvj_.dbf


ops$tkyte@ORA920> create table t1 TABLESPACE TEST_LOGGING as select * from all_objects where 1=0;
Table created.

ops$tkyte@ORA920> alter table t1 nologging;
Table altered.

ops$tkyte@ORA920> insert /*+ append */ into t1 select * from all_objects;
31662 rows created.

ops$tkyte@ORA920> commit;
Commit complete.

there, we have 31662 rows...


ops$tkyte@ORA920>
ops$tkyte@ORA920> alter tablespace test_logging begin backup;

Tablespace altered.

ops$tkyte@ORA920> !cp &F /tmp/backup.dmp

ops$tkyte@ORA920> alter tablespace test_logging end backup;

Tablespace altered.

backed up that datafile at this point in time...

ops$tkyte@ORA920> create table t2 TABLESPACE TEST_LOGGING as select * from all_objects where 1=0;
Table created.

ops$tkyte@ORA920> alter table t2 nologging;
Table altered.

ops$tkyte@ORA920> insert /*+ append */ into t2 select * from all_objects;
31663 rows created.

ops$tkyte@ORA920> commit;
Commit complete.

ops$tkyte@ORA920> insert /*+ append */ into t1 select * from all_objects;
31663 rows created.

ops$tkyte@ORA920> commit;
Commit complete.

same thing for T2 at this point in time and added rows to T1 as well...


ops$tkyte@ORA920> alter tablespace test_logging OFFLINE;
Tablespace altered.

ops$tkyte@ORA920> !rm -f &F

simulated "file gone away"

ops$tkyte@ORA920> alter tablespace test_logging ONLINE;
alter tablespace test_logging ONLINE
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 15 - see DBWR trace file
ORA-01110: data file 15: '/usr/oracle/ora920/OraHome1/oradata/ora920/o1_mf_test_log_zgh7ddvj_.dbf'


ops$tkyte@ORA920> !cp /tmp/backup.dmp &F

ops$tkyte@ORA920> alter tablespace test_logging ONLINE;
alter tablespace test_logging ONLINE
*
ERROR at line 1:
ORA-01113: file 15 needs media recovery
ORA-01110: data file 15: '/usr/oracle/ora920/OraHome1/oradata/ora920/o1_mf_test_log_zgh7ddvj_.dbf'


ops$tkyte@ORA920> alter database recover datafile '&F' ;
old   1: alter database recover datafile '&F'
new   1: alter database recover datafile
'/usr/oracle/ora920/OraHome1/oradata/ora920/o1_mf_test_log_zgh7ddvj_.dbf'

Database altered.

restored from backup and:

ops$tkyte@ORA920> alter tablespace test_logging ONLINE;

Tablespace altered.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select count(*) from t1;
select count(*) from t1
                  *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 15, block # 440)
ORA-01110: data file 15: '/usr/oracle/ora920/OraHome1/oradata/ora920/o1_mf_test_log_zgh7ddvj_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option


ops$tkyte@ORA920> select count(*) from t2;
select count(*) from t2
                  *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 15, block # 522)
ORA-01110: data file 15: '/usr/oracle/ora920/OraHome1/oradata/ora920/o1_mf_test_log_zgh7ddvj_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

both tables are nuked....



ref : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9549144668497

INSERT APPEND ÖRNEK

our NOLOGGING isn't NOLOGGING.  that last insert is really not any different then:

INSERT /*+ APPEND */  INTO TEST SELECT * FROM DBA_OBJECTS HI_THERE;

nologging is just a table alias in that context.  you MEANT alter table.

Here we go, in archive log mode database, using the infinitely easier autotrace:

ops$tkyte@ORA9I> drop table t;
Table dropped.

ops$tkyte@ORA9I> create table t as select * from all_objects where 1=0;
Table created.

ops$tkyte@ORA9I> set autotrace on statistics
ops$tkyte@ORA9I> insert into t select * from all_objects;

44876 rows created.


Statistics
----------------------------------------------------------
      334  recursive calls
     3528  db block gets
   408390  consistent gets
     3729  physical reads
  4980348  redo size
      786  bytes sent via SQL*Net to client
      808  bytes received via SQL*Net from client
        4  SQL*Net roundtrips to/from client
        2  sorts (memory)
        0  sorts (disk)
    44876  rows processed

ops$tkyte@ORA9I> commit;

Commit complete.

ops$tkyte@ORA9I> insert /*+ APPEND */ into t select * from all_objects;

44876 rows created.


Statistics
----------------------------------------------------------
      227  recursive calls
      151  db block gets
   407437  consistent gets
     2369  physical reads
  5081144  redo size
      772  bytes sent via SQL*Net to client
      822  bytes received via SQL*Net from client
        4  SQL*Net roundtrips to/from client
        1  sorts (memory)
        0  sorts (disk)
    44876  rows processed

ops$tkyte@ORA9I> commit;

Commit complete.

no difference - in archive log, append surpressed ROLLBACK but NOT redo, so...

ops$tkyte@ORA9I> ALTER TABLE T NOLOGGING;

Table altered.

ops$tkyte@ORA9I> insert /*+ APPEND */ into t select * from all_objects;

44876 rows created.


Statistics
----------------------------------------------------------
      411  recursive calls
      136  db block gets
   407471  consistent gets
     1586  physical reads
    14608  redo size
      773  bytes sent via SQL*Net to client
      822  bytes received via SQL*Net from client
        4  SQL*Net roundtrips to/from client
        6  sorts (memory)
        0  sorts (disk)
    44876  rows processed

ops$tkyte@ORA9I> commit;

Commit complete.

ops$tkyte@ORA9I> set autotrace off


there you go, redo way down (but time for me to go off and backup my database since I
broke the redo chain for these blocks!)