Administration
About nologging and direct path load
User Rating: 
/ 0
- Details
-
Parent Category: Articles
-
Created on Thursday, 22 September 2016 11:50
-
Last Updated on Thursday, 22 September 2016 12:01
-
Published on Thursday, 22 September 2016 11:50
-
Written by Guy Lambregts
-
Hits: 5133
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.
