How to resize/recreate online redo log files

Oracle suggest to configure the size of redo log files to switch once an hour during presented load and they should no switch more frequently than every 20 minutes during peak time therefore, we need to resize our log files to  hold this redo information.

We cannot just resize our redo log files, we have to drop and recreate them so we require at least two groups of redo log files.

Let’s check how many log file do we per hour for past two days:

select to_char(first_time,'mm/dd/yy') logdate,

sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",

sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",

sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",

sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",

sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",

sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",

sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",

sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",

sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",

sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",

sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",

sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",

sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",

sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",

sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",

sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",

sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",

sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",

sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",

sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",

sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",

sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",

sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",

sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"

from v$log_history

where trunc(first_time)>sysdate-2

group by to_char(first_time,'mm/dd/yy')

order by 1

LOGDATE        MidN        1AM        2AM        3AM        4AM        5AM        6AM        7AM     8AM           9AM       10AM       11AM       Noon        1PM        2PM        3PM        4PM        5PM        6PM        7PM        8PM        9PM       10PM       11PM

-------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

06/16/13          8          7          7         10          8          7          7          7      10             7         13          8          7          8          7          7          8          7          7          7          7          7          7          8

06/17/13          7          7          7          8          8          7          7         10       7             7          9          7          8          7         10          0          0          0          0          0          0          0          0          0

Check the current size of redo log file:

SQL> SELECT a.group#, a.member, b.bytes/1024/1024 MByte FROM v$logfile a, v$log b WHERE a.group# = b.group#;

GROUP# MEMBER                         MBYTE

------ ------------------------------ -----

     4 D:\APP\FINPROD\REDO04.LOG         50

     3 D:\APP\FINPROD\REDO03.LOG         50

     2 D:\APP\FINPROD\REDO02.LOG         50

     1 D:\APP\FINPROD\REDO01.LOG         50

To determine the size of log files, simply take the median of switches per hour which is 10 multiply by 50MB to result 500MB.

Now let’s start our steps:

1.      Check the status of redo log files:

SQL> select group#, status from v$log;

GROUP# STATUS

------ ----------------

     1 INACTIVE

     2 INACTIVE

     3 INACTIVE

     4 CURRENT

Note:

In case you find any active status you would drop just issue:

SQL> alter system checkpoint global;

system altered.

2.      Drop all inactive log groups

SQL> alter database drop logfile group 4;

Database altered.

3.      Re-create dropped redo log group

SQL> alter database add logfile group 4 ('D:\APP\FINPROD\REDO04.LOG') size 500m reuse;

Database altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS

------ ---------- ---------- --- ----------------

     1      96646   52428800 NO  CURRENT

     2      96644   52428800 YES INACTIVE

     3      96645   52428800 YES INACTIVE

     4          0  524288000 YES UNUSED

4.      Switch redo log group to make the newly added group used (Current)

SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS

------ ---------- ---------- --- ----------------

     1      96646   52428800 YES ACTIVE

     2      96644   52428800 YES INACTIVE

     3      96645   52428800 YES INACTIVE

     4      96647  524288000 NO  CURRENT

5.      Repeat the same steps to rebuild all redo log groups

SQL> alter database drop logfile group 2;

Database altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS

------ ---------- ---------- --- ----------------

     1      96646   52428800 YES ACTIVE

     3      96645   52428800 YES INACTIVE

     4      96647  524288000 NO  CURRENT

SQL> alter database add logfile group 2 ('D:\APP\FINPROD\REDO02.LOG') size 500m reuse;

Database altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS

------ ---------- ---------- --- ----------------

     1      96646   52428800 YES ACTIVE

     2          0  524288000 YES UNUSED

     3      96645   52428800 YES INACTIVE

     4      96647  524288000 NO  CURRENT

SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS

------ ---------- ---------- --- ----------------

     1      96646   52428800 YES ACTIVE

     2      96648  524288000 NO  CURRENT

     3      96645   52428800 YES INACTIVE

     4      96647  524288000 YES ACTIVE

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 ('D:\APP\FINPROD\REDO03.LOG') size 500m reuse;

Database altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS

------ ---------- ---------- --- ----------------

     1      96646   52428800 YES ACTIVE

     2      96648  524288000 NO  CURRENT

     3          0  524288000 YES UNUSED

     4      96647  524288000 YES ACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS

------ ---------- ---------- --- ----------------

     1      96646   52428800 YES ACTIVE

     2      96648  524288000 YES ACTIVE

     3      96649  524288000 NO  CURRENT

     4      96647  524288000 YES ACTIVE

SQL> alter system checkpoint global ;

System altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS

------ ---------- ---------- --- ----------------

     1      96646   52428800 YES INACTIVE

     2      96648  524288000 YES INACTIVE

     3      96649  524288000 NO  CURRENT

     4      96647  524288000 YES INACTIVE

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 ('D:\APP\FINPROD\REDO01.LOG') size 500m reuse;

Database altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS

------ ---------- ---------- --- ----------------

     1          0  524288000 YES UNUSED

     2      96648  524288000 YES INACTIVE

     3      96649  524288000 NO  CURRENT

     4      96647  524288000 YES INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES ARC STATUS

------ ---------- ---------- --- ----------------

     1      96650  524288000 NO  CURRENT

     2      96648  524288000 YES INACTIVE

     3      96649  524288000 YES ACTIVE

     4      96647  524288000 YES INACTIVE

Restoring Previous Versions of Statistics

In some cases newly collected statistics leads to sub-optimal execution plan and you need to revert older statistics back when you had good performance.

to determine the history of statistics operations perform at a database

SQL> select start_time from DBA_OPTSTAT_OPERATIONS where operation='gather_database_stats(auto)' order by start_time desc;

START_TIME

---------------------------------------------------------------------------

03-JUN-13 12.00.02.564157 AM +03:00

31-MAY-13 12.05.51.805614 PM +03:00

31-MAY-13 08.00.03.118222 AM +03:00

27-MAY-13 12.00.09.220355 AM +03:00

24-MAY-13 12.07.34.262882 PM +03:00

24-MAY-13 08.00.08.300615 AM +03:00

20-MAY-13 12.00.03.268371 AM +03:00

17-MAY-13 12.05.10.604709 PM +03:00

17-MAY-13 08.00.03.196964 AM +03:00

13-MAY-13 12.00.08.494310 AM +03:00

10-MAY-13 12.01.03.120240 PM +03:00

10-MAY-13 08.00.03.093785 AM +03:00

06-MAY-13 12.00.02.676424 AM +03:00

13 rows selected.

Particularly you can check history statistics of a table by:

SQL>select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='ORDERS' and owner='XXXXX' order by 2 desc;
03/06/2013 12:48:33.316820 AM +03:00
03/06/2013 12:35:31.853101 AM +03:00
31/05/2013 4:09:15.617337 AM +03:00
27/05/2013 12:24:27.985570 AM +03:00
24/05/2013 4:19:12.392677 AM +03:00
20/05/2013 12:31:51.253413 AM +03:00
17/05/2013 4:15:53.924409 AM +03:00
17/05/2013 4:12:11.516809 AM +03:00
13/05/2013 12:22:08.997571 AM +03:00
10/05/2013 4:14:30.823023 AM +03:00
06/05/2013 12:20:52.902622 AM +03:00


Retrieve the actual number of records on the table which have the bad execution plan:
SQL>
----------
SQL> select count(*) from orders;
  COUNT(*)
     -------------
      16323926
let's check the last number of rows on the current statistic
SQL> select num_rows from dba_tables  where table_name='ORDERS' and owner='XXXX';
  NUM_ROWS
----------
  16295541

Now restore the statistics of the table gatherd on 31st-May 
SQL> execute dbms_stats.restore_table_stats('XXXX','ORDERS','31-May-2013 4:09:15.617337 AM +03:00');
PL/SQL procedure successfully completed.

again check the number of rows on 31st-May statistics.

SQL> select num_rows from dba_tables  where table_name='ORDERS' and owner='XXXX';
  NUM_ROWS
----------
  16045590

Finally, If you observe vacillation on the execution plan you can lock this table :

SQL> exec DBMS_STATS.LOCK_TABLE_STATS('XXXX','ORDERS');

How to drop a lost TEMPFILE

If you lost your tempfile due to drive fails or un-proper cloning:

SQL> select file#,status,name from v$tempfile;

     FILE# STATUS NAME    
--------------------------------------------------------
     1 ONLINE  E:\TEMP02.DBF
     2 ONLINE  E:\TEMP00

but when it comes to the dba_temp_files

SQL> select file_name,status from dba_temp_files;
select file_name,status from dba_temp_files
                             *
ERROR at line 1:
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 201: 'E:\TEMP02.DBF'

Simply drope TEMP02.DBF as follow:

SQL> alter database tempfile  'E:\TEMP02.DBF' drop;

Database altered.

SQL> select file_name,status from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
STATUS
-------
E:\TEMP00
ONLINE


Problem Solved !!!