Oracle Tips and Tricks — David Fitzjarrell

April 5, 2019

Recovery Room

Filed under: recovery — dfitzjarrell @ 14:45

"There are no wrong roads to anywhere."
-- Norton Juster, The Phantom Tollbooth

The common wisdom is that any operation declared NOLOGGING won’t contribute to the redo stream, and that includes operations on tables created NOLOGGING. [It cannot be stressed enough that NOLOGGING operations in production databases should NOT be executed if for no other reason than recoverability.] Yet there are operations that will defy the NOLOGGING directive, generating the usual amount of redo. Insert /*+ append */ operations obey NOLOGGING, as do truncates. Conventional inserts and deletes, however, don’t as illustrated with this example. It starts with creating a table NOLOGGING and performing an insert /*+ append */ into it; the statements used are shown below:


--
-- Create a table using the NOLOGGING
-- option
--
-- It will soon be evident that not
-- all operations/inserts/deletes
-- obey that instruction
--
create table nologging_tbl(
id      number,
tname   varchar2(35),
ddl_dt  date)
nologging;

--
-- Insert data
--
-- Using 'append' allows Oracle to
-- observe the nologging aspect of
-- the table
--
insert /*+ append */ into nologging_tbl
select rownum, table_name, sysdate
from all_tables;

Using Log Miner to return the redo generated shows that no rows are returned:


SQL > --
SQL > -- Start checking which inserts and
SQL > -- deletes obey NOLOGGING
SQL > --
SQL > -- The APPEND hint inserts are nologging
SQL > --
SQL > select sql_redo
  2  from v$logmnr_contents
  3  where seg_name= upper('nologging_tbl')
  4  and sql_redo like 'insert%'
  5  /

no rows selected

SQL > 

Truncating the table also shows no redo is generated:


SQL > 
SQL > 
SQL > --
SQL > -- So is truncate, but it should be
SQL > -- since it's DDL, not DML
SQL > --
SQL > truncate table nologging_tbl;

Table truncated.

SQL > 
SQL > select sql_redo
  2  from v$logmnr_contents
  3  where seg_name= upper('nologging_tbl')
  4  and sql_redo like 'insert%'
  5  /

no rows selected

SQL > 

Now let’s execute a conventional, plain-vanilla insert (with no append hint) and see if we stil get no redo:


SQL > 
SQL > --
SQL > -- This insert will generate redo
SQL > --
SQL > -- Even though we told Oracle not
SQL > -- to for this table
SQL > --
SQL > insert into nologging_tbl
  2  select rownum, table_name, sysdate
  3  from all_tables;

2358 rows created.

SQL > 
SQL > commit;

Commit complete.

SQL > 
SQL > select sql_redo
  2  from v$logmnr_contents
  3  where seg_name = upper('nologging_tbl')
  4  and sql_redo like 'insert%'
  5  /

SQL_REDO                                                                        
--------------------------------------------------------------------------------
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('1','TYPE_MIS
C$',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                         
                                                                                
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('2','ATTRCOL$
',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                           
                                                                                
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('3','ASSEMBLY
$',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                          
                                                                                
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('4','LIBRARY$
',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                           

insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('5','VIEWTRCO
L$',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                         
                                                                                
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('6','ICOLDEP$
',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                           
                                                                                
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('7','OPQTYPE$
',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                           
                                                                                
...                                                                                
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('2358','YAMPO
OZA',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                        


2358 rows selected.

SQL > 

Deletes fare no better:


SQL > 
SQL > --
SQL > -- This also generates redo
SQL > --
SQL > delete from nologging_tbl;

2358 rows deleted.

SQL > 
SQL > select sql_redo
  2  from v$logmnr_contents
  3  where seg_name = upper('nologging_tbl')
  4  and sql_redo like 'delete%'
  5  /

SQL_REDO                                                                        
--------------------------------------------------------------------------------
delete from "SCOTT"."NOLOGGING_TBL" where "ID" = '704' and "TNAME" = 'WRI$_ADV_A
CTIONS' and "DDL_DT" = TO_DATE('25-MAR-19', 'DD-MON-RR') and ROWID = 'AAAX2BAAGA
AAACzAAU';                                                                      
                                                                                
delete from "SCOTT"."NOLOGGING_TBL" where "ID" = '705' and "TNAME" = 'WRI$_ADV_R
ATIONALE' and "DDL_DT" = TO_DATE('25-MAR-19', 'DD-MON-RR') and ROWID = 'AAAX2BAA
GAAAACzAAV';                                                                    
                                                                                
delete from "SCOTT"."NOLOGGING_TBL" where "ID" = '706' and "TNAME" = 'WRI$_ADV_R
EC_ACTIONS' and "DDL_DT" = TO_DATE('25-MAR-19', 'DD-MON-RR') and ROWID = 'AAAX2B
AAGAAAACzAAW';                                                                  
                                                                                
...

delete from "SCOTT"."NOLOGGING_TBL" where "ID" = '1275' and "TNAME" = 'WM$HINT_T
ABLE$' and "DDL_DT" = TO_DATE('25-MAR-19', 'DD-MON-RR') and ROWID = 'AAAX2BAAGAA
AAC/ADJ';                                                                       
                                                                                

2338 rows selected.

SQL > 

Remember that the table was created NOLOGGING and still redo was generated for selected operations. Of course the create table statement is logged so the table is recoverable by RMAN. As illustrated conventional inserts are logged, and there are at least three ways an /*+ append */ hint can be circumvented which will cause redo to be generated. The three that immediately come to mind are:


1 Row-level triggers
2 Foreign key constraints that are enabled
3 Non-unique indexes that are used to enforce unique constraints

There could be other ways the append hint will be essentially disregarded; the three listed above either convert array processing (one of the benefits of the /*+ append */ hint) into row-level processing (1,3) or impact the process by performing row-level checking (2). Of course making inserts and deletes NOLOGGING operations isn’t the best idea as that impacts recoverability. And ANY operation executed NOLOGGING in a production database is a disaster just waiting to happen. And the DBA’s job isn’t to create disaster, it’s to prevent disaster from happening.

Sometimes anywhere isn’t where you wanted to be.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at WordPress.com.

%d bloggers like this: