Pickleball spielen

Having just reviewed this article I can confirm that it is still valid also for 12c: The exact same commands can be used to change logfile sizes with that version.

If you deal with Oracle Databases for years on a daily basis like I do, you sometimes forget that things that seem elementary to you ain’t necessarily so for others, especially newbies in the Oracle realm. I noticed that when I saw some discussions in forums about redo logfiles and their sizes.

When I go back in time to 1998, that’s where I was the first time responsible for an Oracle Database (version 7, but some things never change). I inherited that Database from the previous DBA who did the setup, creating quite small log groups of a couple 100 KBs in size – which was not uncommon these days,  because Oracle templates mentioned that size as „medium“, if I recall it right.

I got log switches almost every 10 seconds or faster, but didn’t think about it since nobody complained and everything seemed to run fine. Then I attended an Oracle University course and I learned that this was way too many log switches in that short time and that I should increase the log files. Believe me, that was an exciting thing to do for me those days! I sat there, fingers crossed (mentally, because I had to type in the commands) and did it veeeery carefully on our production database then.

Some things to consider with changing the size of online redo logs:

• You do it for performance reasons – mostly because your original files are too small and you get log switches too often
• It is an Online Operation – no end user will even notice about it – apart from maybe better performance afterwards
• You will not put your Database at risk with it – if you do it right 🙂

Let me give you an example:

SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

 GROUP#    MEMBERS STATUS                   MB
---------- ---------- ---------------- ----------
 1          1 INACTIVE                 50
 2          1 INACTIVE                 50
 3          1 CURRENT                  50


SQL> select group#,member from v$logfile;

 GROUP# MEMBER
---------- ------------------------------------------------------------
 3 /u01/app/oracle/oradata/orcl/redo03.log
 2 /u01/app/oracle/oradata/orcl/redo02.log
 1 /u01/app/oracle/oradata/orcl/redo01.log

This is my configuration as done by the DBCA. Now I want to get 100m sized logfiles and I want them mirrored for security reason. First, I create new log groups of the desired size:

SQL> alter database add logfile
     ('/u01/app/oracle/oradata/orcl/redo_g4m1.rdo',
      '/u01/app/oracle/oradata/orcl/redo_g4m2.rdo')
     size 100m;

Database altered.

SQL> alter database add logfile
     ('/u01/app/oracle/oradata/orcl/redo_g5m1.rdo',
      '/u01/app/oracle/oradata/orcl/redo_g5m2.rdo')
     size 100m;

Database altered.
SQL> alter database add logfile
     ('/u01/app/oracle/oradata/orcl/redo_g6m1.rdo',
      '/u01/app/oracle/oradata/orcl/redo_g6m2.rdo')
     size 100m;

Database altered.

SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

 GROUP#    MEMBERS STATUS                   MB
---------- ---------- ---------------- ----------
 1          1 INACTIVE                 50
 2          1 INACTIVE                 50
 3          1 CURRENT                  50
 4          2 UNUSED                  100
 5          2 UNUSED                  100
 6          2 UNUSED                  100

6 rows selected.

I like the above naming convention, because I can determine by the name easily what member of what group I see and because the suffix rdo is not so easy misinterpreted as a mere ascii textfile that can be deleted if space is needed – believe me, that has happened many times and made the DBA of that site curse and suffer 🙂

Now I am going to drop the small files – with SQL commands first!

SQL> alter database drop logfile group 1;

Database altered.

That was easy, wasn’t it? Notice that the OS file is not gone yet  – we have to delete them manually (if they are not OMF). All groups that have the status INACTIVE can be dropped that way.

SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

 GROUP#    MEMBERS STATUS                   MB
---------- ---------- ---------------- ----------
 2          1 INACTIVE                 50
 3          1 CURRENT                  50
 4          2 UNUSED                  100
 5          2 UNUSED                  100
 6          2 UNUSED                  100

SQL> alter database drop logfile group 2;

Database altered.

If the status is CURRENT or ACTIVE, it won’t be possible to drop the group – you can do nothing wrong here!

SQL>  alter database drop logfile group 3;

 alter database drop logfile group 3

*

ERROR at line 1:

ORA-01623: log 3 is current log for instance orcl (thread 1) - cannot drop

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'

CURRENT means that LGWR is actually using this group to write the redo entries into. If a log switch happens, the status can become ACTIVE or INACTIVE:

SQL> alter system switch logfile;

System altered.

SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

 GROUP#    MEMBERS STATUS                   MB
---------- ---------- ---------------- ----------
 3          1 ACTIVE                   50
 4          2 CURRENT                 100
 5          2 UNUSED                  100
 6          2 UNUSED                  100

ACTIVE means, the checkpoint associated to the redo entries in that group is not yet finished. It is in other words still needed for a possible instance recovery and cant be dropped therefore. But we can force the checkpoint:

SQL> alter system checkpoint;

System altered.

SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

 GROUP#    MEMBERS STATUS                   MB
---------- ---------- ---------------- ----------
 3          1 INACTIVE                 50
 4          2 CURRENT                 100
 5          2 UNUSED                  100
 6          2 UNUSED                  100

SQL> alter database drop logfile group 3;

Database altered.

Now I am finished with my SQL commands and I can continue with deleting the files of the small groups from the OS. Never delete the OS files before the SQL drop commands! The instance was OPEN all the time and no user was in any way concerned by this whole action.

By the way, I mirrored the log groups to protect my database against really serious problems that would arise otherwise, if a whole log group gets lost – for example because someone deletes a file from the OS. You would want to have the mirrored files on different devices, of course 🙂