20 | 10 | 2017
Latest Articles
Popular Articles

Administration

About nologging and direct path load

User Rating:  / 0
PoorBest 

About nologging and direct path load

SQL> create tablespace TS_LOGGING datafile '/u02/oradata/SILVER/TS_LOGGING_01.dbf' size 4G LOGGING;

Tablespace created.

SQL> create tablespace TS_NOLOGGING datafile '/u02/oradata/SILVER/TS_NOLOGGING_01.dbf' size 4G NOLOGGING;

Tablespace created.

SQL> create user I_LOVE_REDO identified by Echoes4YOU;

User created.

SQL> grant create session, resource,unlimited tablespace to I_LOVE_REDO;

Grant succeeded.

SQL> grant select any dictionary to I_LOVE_REDO;

Grant succeeded.

SQL> conn I_LOVE_REDO/Echoes4YOU;

Connected.

 

Let' s create a few tables in the tablespaces created here above. We note the the logging atribute is inherited from the tablespace

 

SQL> create table T1 (c1 number, c2 date default sysdate, c3 varchar2(100)) tablespace TS_LOGGING;

Table created.

SQL> create table T2 (c1 number, c2 date default sysdate, c3 varchar2(100)) tablespace TS_NOLOGGING;

Table created.

SQL> create table T3 (c1 number, c2 date default sysdate, c3 varchar2(100)) tablespace TS_NOLOGGING;

Table created.

SQL> select table_name,logging from user_tables;

TABLE_NAME                     LOGGING
------------------------------ -------
T3                             NO
T2                             NO
T1                             YES

 

/* REDO BEFORE FIRST LOAD */

SQL> select value from v$sesstat s, v$statname n
2 where n.name = 'redo size'
3 and s.statistic# = n.statistic#
4 and s.sid = ( select sid from v$mystat where rownum = 1 );

VALUE
----------
31584

 

/* FIRST LOAD */

SQL> insert into T1 (c1,c3) select level,dbms_random.string('U',100) from dual connect by level < 1000001;

1000000 rows created.

SQL> commit;

Commit complete.

 

/* REDO AFTER FIRST LOAD */

SQL> select value from v$sesstat s, v$statname n
2 where n.name = 'redo size'
3 and s.statistic# = n.statistic#
4 and s.sid = ( select sid from v$mystat where rownum = 1 );

VALUE
----------
134163996

SQL> select 134163996 - 31584 from dual;

134163996-31584
---------------
134132412

 

/* SECOND LOAD, STILL IN LOGGING MODE */

SQL> insert into T2 (c1,c3) select level,dbms_random.string('U',100) from dual connect by level < 1000001;

1000000 rows created.

SQL> commit;

Commit complete.

 

/* REDO AFTER SECOND LOAD */

SQL> select value from v$sesstat s, v$statname n
2 where n.name = 'redo size'
3 and s.statistic# = n.statistic#
4 and s.sid = ( select sid from v$mystat where rownum = 1 );

VALUE
----------
268296964

SQL> select 268296964 - 134132412 from dual;

268296964-134132412
-------------------
134164552

 

/* THIRTH LOAD, THE REAL NOLOGGING DIRECT PATH LOAD */

SQL> insert /*+ APPEND */ into T3 (c1,c3) select level,dbms_random.string('U',100) from dual connect by level < 1000001;

1000000 rows created.

SQL> commit;

Commit complete.

 

/* REDO AFTER THIRTH LOAD */

SQL> select value from v$sesstat s, v$statname n
2 where n.name = 'redo size'
3 and s.statistic# = n.statistic#
4 and s.sid = ( select sid from v$mystat where rownum = 1 );

VALUE
----------
268573112

SQL> select 268573112 - 268296964 from dual;

268573112-268296964
-------------------
276148 ----> This is less than the redo for the first and second run

 

SQL> column name format a40

SQL> set pages 9999
SQL> set lines 300
SQL> select file#,name,unrecoverable_change#, unrecoverable_time from v$datafile;

FILE# NAME                                                UNRECOVERABLE_CHANGE# UNRECOVER
---------------- ---------------------------------------- --------------------- ---------
1 /u02/oradata/SILVER/system01.dbf                        0
2 /u02/oradata/SILVER/sysaux01.dbf                        0
3 /u02/oradata/SILVER/undotbs01.dbf                       0
4 /u02/oradata/SILVER/users01.dbf                         0
5 /u02/oradata/SILVER/TS_LOGGING_01.dbf                   0
6 /u02/oradata/SILVER/TS_NOLOGGING_01.dbf                 1772099               23-AUG-16
7 /u02/oradata/SILVER/ts_indx_01.dbf                      0
8 /u02/oradata/SILVER/ts_indx_02.dbf                      0

8 rows selected.

 

The database can operate in force logging mode however by default force logging is not enabled, hence nologging operations like the one here shown are allowed.

Advice : prior to the creation of a standby database consider to enable force logging

 

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

 

SQL> alter database force logging;

Database altered.

SQL> alter database no force logging;

Database altered.