Multiplex Redo Logs

[jnrpeardba]

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...]

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...

[jnrpeardba]

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]

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...]

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...

[jnrpeardba]

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...]