This forum is currently locked. You can't register or post questions at this time. (read more)

Multiplex Redo Logs

All posts relating to Oracle database administration.

Moderator: Tim...

jnrpeardba
Advisor
Posts: 401
Joined: Wed May 04, 2011 3:14 pm

Multiplex Redo Logs

Postby jnrpeardba » Sat Sep 29, 2012 9:39 am

Hi Tim,

Hopefully this is a simple question but basically I'd like to muliplex the redo logs

I have this setup

Code: Select all

    GROUP#    THREAD#  SEQUENCE# Size in MB    MEMBERS ARCHIVED   STATUS
---------- ---------- ---------- ---------- ---------- ---------- --------------                                                                                        --
         1          1          7         50          1 YES        INACTIVE
         2          1          8         50          1 NO         CURRENT
         3          2          5         50          1 NO         CURRENT
         4          2          4         50          1 YES        ACTIVE

    GROUP# STATUS  TYPE    MEMBER                                                       
---------- ------- ------- ------------------------------------------------------------
         2         ONLINE  +DATA/cdrprod/redo02.log                                     
         1         ONLINE  +DATA/cdrprod/redo01.log                                     
         3         ONLINE  +DATA/cdrprod/redo03.log                                     
         4         ONLINE  +DATA/cdrprod/redo04.log


I have 2 ASM disk groups +DATA and +BACKUP

If I do the following will the redo's then be multiplexed?

Code: Select all

alter database add logfile member '+BACKUP/cdrprod/redo01b.log' to group 1;
alter database add logfile member '+BACKUP/cdrprod/redo02b.log' to group 2;
alter database add logfile member '+BACKUP/cdrprod/redo03b.log' to group 3;
alter database add logfile member '+BACKUP/cdrprod/redo04b.log' to group 4;


I'd like to get this right, before I get in a state with number of threads and groups etc...
Currently I see 4 groups for 2 threads (instances) which is correct and both databases restart without any redo warning messages

Many thanks

Jnrpeardba

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Multiplex Redo Logs

Postby Tim... » Sat Sep 29, 2012 12:53 pm

Hi.

Yes. Adding a member to an existing group will multiplex that group. The new file will have the same details (group and thread) as the existing member.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

jnrpeardba
Advisor
Posts: 401
Joined: Wed May 04, 2011 3:14 pm

Re: Multiplex Redo Logs

Postby jnrpeardba » Sat Sep 29, 2012 2:07 pm

Hi Tim,

Thanks for that and now my RACCHECK report does not complain about the redo logs not being multiplexed. All seems fine.

One final point, which I should have included in the previous post is the sizings of my logs. Currently they are set to 50MB (default) but I wanted them to be an initial minimum value of 1500MB

I think this is what I need to do to increase them and also have them multiplexed. Can you confirm whether this is the correct method to use:

Code: Select all

add logfile group 5 ('+DATA/cdprod/redo05a.log','+BACKUP/cdprod/redo05b.log) size 1500M;
add logfile group 6 ('+DATA/cdprod/redo06a.log','+BACKUP/cdprod/redo06b.log) size 1500M;

add logfile group 7 ('+DATA/cdprod/redo07a.log','+BACKUP/cdprod/redo07b.log) size 1500M;
add logfile group 8 ('+DATA/cdprod/redo08a.log','+BACKUP/cdprod/redo08b.log) size 1500M;


But if I do this how will I know if groups 5 & 6 go to thread1 and groups 7 & 8 use thread number 2? How does Oracle determine which groups use which thread?

Many thanks if you can clarify that for me

Jnrpeadba

jnrpeardba
Advisor
Posts: 401
Joined: Wed May 04, 2011 3:14 pm

Re: Multiplex Redo Logs

Postby jnrpeardba » Sat Sep 29, 2012 4:18 pm

Hi Tim,

Just had a look can I do something like this which will give me 2 redo log groups per thread and ensure that I have two threads.
If this works I can then drop groups 1-4 successfully, without the database complaining about insufficient groups per thread.

Code: Select all

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ('+DATA/cdrprod/redo05a_prod.log','+BACKUP/cdrprod/redo05b_prod.log') SIZE 1500M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 ('+DATA/cdrprod/redo06a_prod.log','+BACKUP/cdrprod/redo06b_prod.log') SIZE 1500M;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 ('+DATA/cdrprod/redo07a_prod.log','+BACKUP/cdrprod/redo07b_prod.log') SIZE 1500M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 ('+DATA/cdrprod/redo08a_prod.log','+BACKUP/cdrprod/redo08b_prod.log') SIZE 1500M;


I would then hope to end up with something looking like this

thread 1 - group 5 & 6 size 1500MB for each redo log member and they are multiplexed
thread 2 - group 7 & 8 size 1500MB for each redo log member and they are multiplexed

2 threads and 4 groups which would satisfy the cluster

Thanks again,

Jnrpeardba

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Multiplex Redo Logs

Postby Tim... » Sat Sep 29, 2012 4:25 pm

Hi.

When you create new groups you should include the THREAD clause, to specify the thread for the group.

You should also consider having more than 2 groups per thread. I usually have at least 3 groups per thread. At least 2 members per group.

Size depends on requirement. If you log switch too often you will be check pointing too often.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

jnrpeardba
Advisor
Posts: 401
Joined: Wed May 04, 2011 3:14 pm

Re: Multiplex Redo Logs

Postby jnrpeardba » Sat Sep 29, 2012 5:53 pm

Thanks Tim,

Perfect. That's the first time I have really understood about the redo logs and how they work - many thanks

Code: Select all

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> select group#, thread#, sequence#, bytes / 1024 / 1024 "Size in MB",
members, archived, status from v$log;  2

    GROUP#    THREAD#  SEQUENCE# Size in MB    MEMBERS ARCHIVED   STATUS
---------- ---------- ---------- ---------- ---------- ---------- ----------------
         5          1         23       1500          2 YES        INACTIVE
         6          1         24       1500          2 YES        INACTIVE
         7          1         25       1500          2 NO         CURRENT
         8          2          9       1500          2 YES        INACTIVE
         9          2         10       1500          2 YES        INACTIVE
        10          2         11       1500          2 NO         CURRENT

6 rows selected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 3423965184 bytes
Fixed Size                  2224424 bytes
Variable Size            1929383640 bytes
Database Buffers         1476395008 bytes
Redo Buffers               15962112 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
oracle@rvecrcdrprod >


Instance started without any issues

Jnrpeardba

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Multiplex Redo Logs

Postby Tim... » Sat Sep 29, 2012 6:51 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 2 guests