Oracle Logbook

May 11, 2012

Turning off archive log generation for some tables

Filed under: Uncategorized — heliodias @ 10:10 pm

We have a client that are creating 250GB of archive on dailly basis,

And he want to decrease this volume of archive.

Well, the solution is turn off the archive at specifics tables, the catch is that Oracle doesn´t allow us to do it. Below I´ll show how I did it:

SQL> SELECT NAME,VALUE FROM V$STATNAME T1,V$MYSTAT T2
WHERE T1.STATISTIC#=T2.STATISTIC#
AND UPPER (NAME) LIKE ‘%REDO%’
ORDER BY 1;

NAME                                          VALUE
—————————————- ———-
IMU Redo allocation size                          0
redo blocks read for recovery                     0
redo blocks written                               0
redo buffer allocation retries                    0
redo entries                                      1
redo log space requests                           0
redo log space wait time                          0
redo log switch interrupts                        0
redo ordering marks                               0
redo size                                       400
redo subscn max counts                            0
redo synch time                                   0
redo synch writes                                 0
redo wastage                                      0
redo write time                                   0
redo writer latching time                         0
redo writes                                       0

17 rows selected.

 /* As one may see, no previous redo at all*/

SQL> INSERT INTO REDO_OFF (SELECT ROWNUM FROM DBA_OBJECTS) ;

107692 rows created.

SQL> COMMIT;
Commit complete.

/*Now let´s see how redo had been generate*/

SQL> SELECT NAME,VALUE FROM V$STATNAME T1,V$MYSTAT T2
WHERE T1.STATISTIC#=T2.STATISTIC#
AND UPPER (NAME) LIKE ‘%REDO%’
ORDER BY 1;

NAME                                          VALUE
—————————————- ———-
IMU Redo allocation size                          0
redo blocks read for recovery                     0
redo blocks written                               0
redo buffer allocation retries                    0
redo entries                                      2
redo log space requests                           0
redo log space wait time                          0
redo log switch interrupts                        0
redo ordering marks                               0
redo size                                       540
redo subscn max counts                            0
redo synch time                                   0
redo synch writes                                 1
redo wastage                                      0
redo write time                                   0
redo writer latching time                         0
redo writes                                       0

17 rows selected.

/* Without redo, of course we don´t have archive either */

/* Now let´s do it again, but at a archived on table */

SQL> INSERT INTO L3_REDO_ON (SELECT ROWNUM FROM DBA_OBJECTS) ;

107693 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT NAME,VALUE FROM V$STATNAME T1,V$MYSTAT T2
WHERE T1.STATISTIC#=T2.STATISTIC#
AND UPPER (NAME) LIKE ‘%REDO%’
ORDER BY 1;

NAME                                          VALUE
—————————————- ———-
IMU Redo allocation size                      11512
redo blocks read for recovery                     0
redo blocks written                               0
redo buffer allocation retries                    0
redo entries                                   1752
redo log space requests                           0
redo log space wait time                          0
redo log switch interrupts                        0
redo ordering marks                               0
redo size                                   1752392
redo subscn max counts                            0
redo synch time                                   0
redo synch writes                                 4
redo wastage                                      0
redo write time                                   0
redo writer latching time                         0
redo writes                                       0

17 rows selected.

SQL>

/*Lot of redo and archive as we expected*/

No there isn´t any hidden parameter to turn the archive off.

The trick is that the “turned off archives tables” were at a second instance, with the ALL ARCHIVE LOG DISABLED, and I just created synonyms point out to the second instance.

So for the applications , and users it will be transparent , and you could “turn off” the archive generation at the desired tables.

Of course at the second instance you still have the REDO generation, that´s why I didn´t claim that the REDO was being turning off, instead I said THE ARCHIVE would be off.

Advertisements

Blog at WordPress.com.