9 Replies Latest reply on Aug 5, 2014 12:59 AM by rp0428

    Post DB 12c install, how to configure redo log files, i.e. for an OEM DB

    Roddy Rodstein

      Greetings,

       

      Thank you all in advance for your support! I am not a DBA, so I really appreciate you support!

       

      We are trying to apply the OEM R4 12c Database repository requirements for a small site to our Oracle 12c Database before we install Oracle Enterprise Manager 12c R4. We are unable to getting the redo log files that we create (see below - ALTER DATABASE ADD LOGFILE) to be used by the database during the OEM installation. For example, when installing OEM with the below settings, we get a error message during the database setup complaining that the redo log file are not big enough, i.e. the database is using the default redo log file not the redo log file we created.

       

      Could you please help with the missing sql commands to get the database to actually use the redo log file that we are creating?

       

      Small Site Minimum Database Settings

      ParameterMinimum Value
      processes300
      pga_aggregate_target*1024 MB
      sga_target*2 GB
      redo log file size300 MB
      shared_pool_size600 MB
      db_securefilePERMITTED

      Post database installation we run the following commands to apply the database requirements for OEM R4:

      sqlplus / AS SYSDBA
      ALTER SYSTEM SET processes=300 SCOPE=SPFILE;
      System altered.

       

      ALTER SYSTEM SET pga_aggregate_target=1024M SCOPE=SPFILE;
      System altered.

       

      ALTER SYSTEM SET sga_target=2G SCOPE=SPFILE;
      System altered.

      ALTER SYSTEM SET shared_pool_size=600M SCOPE=SPFILE;
      System altered.

      ALTER SYSTEM SET db_securefile=PERMITTED SCOPE=BOTH;

      System altered.

      ALTER DATABASE force logging;

       

      System altered.
      ALTER TABLESPACE users FORCE LOGGING;
      System altered.
      ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/product/redo_logs/log1c.rdo', '/u01/app/oracle/product/redo_logs/log2c.rdo', '/u01/app/oracle/product/redo_logs/log3c.rdo') SIZE 100M;
      System altered.

       

      SHUTDOWN IMMEDIATE;
      SQL> CREATE SPFILE FROM PFILE;
      SQL> STARTUP;
      ORACLE instance started.

       

      Total System Global Area 3206836224 bytes
      Fixed Size            2217632 bytes
      Variable Size         1744832864 bytes
      Database Buffers     1442840576 bytes
      Redo Buffers           16945152 bytes
      Database mounted.
      Database opened.

       

      SQL> quit


      With this command: ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/product/redo_logs/log1c.rdo', '/u01/app/oracle/product/redo_logs/log2c.rdo', '/u01/app/oracle/product/redo_logs/log3c.rdo') SIZE 100M; the redo log files are indeed created, but the DB does not use them.


      Respectfully,

      Roddy

        • 1. Re: Post DB 12c install, how to configure redo log files, i.e. for an OEM DB

          SQL> alter system switch logfile;

           

           

          System altered.

           

           

          SQL> /

           

           

          System altered.

           

           

          SQL> /

           

           

          System altered.

           

           

          SQL>

          • 2. Re: Post DB 12c install, how to configure redo log files, i.e. for an OEM DB
            FreddieEssex

            If the redo log files were created the database will use them.  Just because you created a new redo log group doesn't mean that Oralce will now just use these.  You will also have your previous redo logs.  You may have to drop your older redo logs and recreate them at the same size.

             

            Post the results of the following:

             

            select GROUP#, SEQUENCE#, BYTES/1024/1024, members from v$log;
            select GROUP#, STATUS, MEMBER from v$logfile;
            
            • 3. Re: Post DB 12c install, how to configure redo log files, i.e. for an OEM DB
              Roddy Rodstein

              Hi Freddie,

               

              Thank you for your support!

               

              Respectfully,

              Roddy

               

              SQL> select GROUP#, SEQUENCE#, BYTES/1024/1024, members from v$log; 

               

               

                  GROUP#  SEQUENCE# BYTES/1024/1024 MEMBERS

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

                1    1   50       1

                2    0   50       1

                3    0   50       1

                4    0  300       3

               

               

              SQL> select GROUP#, STATUS, MEMBER from v$logfile;

               

               

                  GROUP# STATUS

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

              MEMBER

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

                3

              /u01/app/oracle/oradata/omr/redo03.log

               

               

                2

              /u01/app/oracle/oradata/omr/redo02.log

               

               

                1

              /u01/app/oracle/oradata/omr/redo01.log

               

               

               

               

                  GROUP# STATUS

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

              MEMBER

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

                4

              /u01/app/oracle/product/redo_logs/log1c.rdo

               

               

                4

              /u01/app/oracle/product/redo_logs/log2c.rdo

               

               

                4

              /u01/app/oracle/product/redo_logs/log3c.rdo

               

               

               

               

              6 rows selected.

               

               

              SQL>

              • 4. Re: Re: Post DB 12c install, how to configure redo log files, i.e. for an OEM DB
                EdStevens

                Roddy Rodstein wrote:

                 

                Hi Freddie,

                 

                Thank you for your support!

                 

                Respectfully,

                Roddy

                 

                SQL> select GROUP#, SEQUENCE#, BYTES/1024/1024, members from v$log;

                 

                 

                    GROUP#  SEQUENCE# BYTES/1024/1024 MEMBERS

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

                  1    1  50      1

                  2    0  50      1

                  3    0  50      1

                  4    0  300      3

                 

                 

                SQL> select GROUP#, STATUS, MEMBER from v$logfile;

                 

                 

                    GROUP# STATUS

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

                MEMBER

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

                  3

                /u01/app/oracle/oradata/omr/redo03.log

                 

                 

                  2

                /u01/app/oracle/oradata/omr/redo02.log

                 

                 

                  1

                /u01/app/oracle/oradata/omr/redo01.log

                 

                 

                 

                 

                    GROUP# STATUS

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

                MEMBER

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

                  4

                /u01/app/oracle/product/redo_logs/log1c.rdo

                 

                 

                  4

                /u01/app/oracle/product/redo_logs/log2c.rdo

                 

                 

                  4

                /u01/app/oracle/product/redo_logs/log3c.rdo

                 

                 

                 

                 

                6 rows selected.

                 

                 

                SQL>

                So it appears you have 3 pre-existing redo groups with one member each, and you created a fourth group with 3 members.  The only problem here is not operational but management.  The 3 pre-existing redo groups are not multiplexed (only one member file each) and so are very vulnerable.  Add a couple of more groups just like the one you already added, then drop the original ones.

                 

                Back to your original "problem", there is no problem.  Oracle will write to one redo group until it is full, then switch to writing to the next until it is full, then switch .... and so on and so forth.  I see not evidence that the db is "not using" your new group.

                 

                On another tack .. kudos to you for naming your redo log files with the 'rdo' extension.  That used to be the default until 10g when Oracle inexplicably switched to a default of '.log'. I once got burned when an SA delete "a bunch of log files" to free up disk space.  Fortunately mine were multiplexed across multiple disks, so recovery was easy.  If they had not been multiplexed (and across multiple file systems) I'd have had a much bigger problem.

                 

                That said, having multiplexed redo files all in the same directory is little better than not multiplexing at all.  You have all of yours in the same directory ....

                 

                And your naming is counter intuitive to what most people do.  Given that redo groups are numbered, most people will name their individual files to indicate which group they are in, and use the alpha suffix to indicate different members of the same group:

                 

                Correcting just the file names, in your setup

                Group 1

                /u01/app/oracle/product/redo_logs/redo1a.log

                /u01/app/oracle/product/redo_logs/redo1b.log

                /u01/app/oracle/product/redo_logs/redo1c.log

                 

                Group 2

                /u01/app/oracle/product/redo_logs/redo2a.log

                /u01/app/oracle/product/redo_logs/redo2b.log

                /u01/app/oracle/product/redo_logs/redo2c.log

                 

                But better, for multiplexed files:

                 

                Group 1

                 

                /oradata1/redo/redo1a.log

                /oradata2/redo/redo1b.log

                /oradata3/redo/redo1c.log

                 

                Group 2

                 

                /oradata1/redo/redo2a.log

                /oradata2/redo/redo2b.log

                /oradata3/redo/redo2c.log

                 

                The actual details of the directory naming are not that important,  My point is two-fold.  First, for max protection, the different members of a group should be on separate disks/file systems.  Second, I really don't like (as you are doing) putting them under ORACLE_BASE, or even on the same file system as ORACLE_BASE.

                • 5. Re: Post DB 12c install, how to configure redo log files, i.e. for an OEM DB
                  Roddy Rodstein

                  Hi Ed,

                   

                  Thank you for your reply, and tips!

                   

                  We reckon we still need to change how we setup the redo log files for the OEM installer, to avoid the below OEM installer message.

                   

                  How can we setup the redo log file to satisfy the OEM installer program?

                   

                  Thank you,

                  Roddyoem-db-error.gif

                  • 6. Re: Re: Post DB 12c install, how to configure redo log files, i.e. for an OEM DB
                    FreddieEssex

                    I would drop and re-create all your redo logs so you have come consistency.

                     

                    You currently have 3 groups with 1 member, and 1 group with 3 members.  The size of groups 1-3 is 50mb and group 4 is 300mb.

                     

                    As per Ed's post keep your naming convention for group file members consistent and intuitive (currently group 4 members are named as 1c, 2c, 3c...not ideal) as well as multiplexing them on seperate filesystems.  With you current setup if you have some issue with the filesystem you've lost all of your redo logs....a big no no.  The point of multiplexing is to protect you against disk failure and human error.

                     

                    Drop and recreate using the below syntax:

                     

                    alter database drop logfile group 1;
                    alter database add logfile group 1 ('/u01/app/oracle/product/redo_logs/log1a.rdo','/u01/app/oracle/product/redo_logs/log1b.rdo','/u01/app/oracle/product/redo_logs/log1c.rdo') size 300m reuse;
                    
                    

                     

                    If you get an error when dropping the logfile group then "alter system switch logfile;" until you can drop the group, before recreating.

                     

                    Do the above for all 4 groups you should see them all created with a size of 300mb.

                    • 7. Re: Post DB 12c install, how to configure redo log files, i.e. for an OEM DB
                      Difladermaus

                      I am working with Roddy on this. Tahnk you for the information so far.

                       

                      Okay started here.

                       

                      select GROUP#, STATUS, MEMBER from v$logfile;

                       

                       

                          GROUP# STATUS

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

                      MEMBER

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

                               3

                      /u01/app/oracle/oradata/omr/redo03.log

                               2

                      /u01/app/oracle/oradata/omr/redo02.log

                               1

                      /u01/app/oracle/oradata/omr/redo01.log

                       

                      SQL> alter database drop logfile group 1;

                      Database altered.

                       

                      SQL> select GROUP#, SEQUENCE#, BYTES/1024/1024, members from v$log;

                          GROUP#  SEQUENCE# BYTES/1024/1024    MEMBERS

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

                               2          5              50          1

                               3          3              50          1

                       

                      ##These are setting for small OEM db

                      SQL> ALTER SYSTEM SET processes=300 SCOPE=SPFILE;

                      System altered.

                      SQL> ALTER SYSTEM SET pga_aggregate_target=1024M SCOPE=SPFILE;

                      System altered.

                      SQL> ALTER SYSTEM SET sga_target=2G SCOPE=SPFILE;

                      System altered.

                      SQL> ALTER SYSTEM SET shared_pool_size=600M SCOPE=SPFILE;

                      System altered.

                      SQL> ALTER SYSTEM SET db_securefile=PERMITTED SCOPE=BOTH;

                      System altered.

                      SQL> ALTER DATABASE force logging;

                      Database altered.

                      SQL> ALTER TABLESPACE users FORCE LOGGING;

                      Tablespace altered.

                      SQL> ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/product/redo_logs/log1a.rdo', '/u01/app/oracle/product/redo_logs/log1b.rdo', '/u01/app/oracle/product/redo_logs/log1c.rdo') SIZE 300M;

                      Database altered.

                      SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

                      Database altered.

                      SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/app/oracle/product/redo_logs/log1a.rdo','/u01/app/oracle/product/redo_logs/log1b.rdo','/u01/app/oracle/product/redo_logs/log1c.rdo') SIZE 300M REUSE;

                      Database altered.

                       

                      SQL> ALTER DATABASE DROP LOGFILE GROUP 2;               Would not delete, tried running switch log 4-5 times, but had same issue, either current or needed for group 2

                      ALTER DATABASE DROP LOGFILE GROUP 2

                      *

                      ERROR at line 1:

                      ORA-01623: log 2 is current log for instance omr (thread 1) - cannot drop

                      ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/omr/redo02.log'

                       

                      SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

                      Database altered.

                       

                      SQL>  alter system switch logfile;

                      System altered.

                       

                      ALTER DATABASE DROP LOGFILE GROUP 2

                      *

                      ERROR at line 1:

                      ORA-01624: log 2 needed for crash recovery of instance omr (thread 1)

                      ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/omr/redo02.log'

                       

                      Current status

                      SQL> select GROUP#, SEQUENCE#, BYTES/1024/1024, members from v$log;

                          GROUP#  SEQUENCE# BYTES/1024/1024    MEMBERS

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

                               2         13              50          1

                               1         12             300          3

                       

                      SQL> select GROUP#, STATUS, MEMBER from v$logfile;

                          GROUP# STATUS

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

                      MEMBER

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

                               2

                      /u01/app/oracle/oradata/omr/redo02.log

                               1

                      /u01/app/oracle/product/redo_logs/log1a.rdo

                               1

                      /u01/app/oracle/product/redo_logs/log1b.rdo

                       

                          GROUP# STATUS

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

                      MEMBER

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

                               1

                      /u01/app/oracle/product/redo_logs/log1c.rdo

                       

                       

                       

                       

                      Can I remove Group 2 and just have Group 1 with 3 - 300MB files? OR what would be my sequence of commands to just get 3 - 300MB log files for the OEM requirements?

                       

                      Regards,

                      Jeff

                      • 8. Re: Post DB 12c install, how to configure redo log files, i.e. for an OEM DB
                        EdStevens

                        You ALWAYS have to have a minimum of 2 log groups.  Oracle will not let you drop a group if said drop would leave you with only on group.

                        Perhaps when you switched log file 'three or four times', you switched it all the way back around to the group you were trying to drop.

                        You're making this way harder than it is.

                        Make sure you currently have a minimum of three groups.  If you don't, add one.  I don't care what size they are, where they are located, just make sure you have three groups.

                        Next, drop one of the groups.  If it won't drop because it is busy, ONE switch log file.  after the switch you may have to wait several seconds until the group gets archived.

                        Once you've dropped one group, recreate it to the spec you need.  Multiplexed.  Properly named.  Properly located.  Proper size.

                        Repeat the above until you are left with a minimum of three groups that meet the spec needed, and you've gotten rid of all the 'ash and trash' groups.

                        • 9. Re: Post DB 12c install, how to configure redo log files, i.e. for an OEM DB
                          rp0428
                          Can I remove Group 2 and just have Group 1 with 3 - 300MB files? OR what would be my sequence of commands to just get 3 - 300MB log files for the OEM requirements?

                          EdStevens already ANSWERED that question in his first reply. He gave you STEP BY STEP instructions.

                           

                          Go back and reread his reply and follow the instructions you were given.