Adding redo log file on primary database.

[Pradeep_3]

Dear all,

Please let me know, how to add redolog file on my primary database(can some explain me step by step). And what precaution should be taken on my primary as well as standby database.

Regards
Fenil Manek

[Ian Dennison_1]

Paresh

1. Is it Oracle?

2. Are you running in archive log mode already?

3. Are you adding new Redo Logs or creating them from scratch?

Share and Enjoy! Ian

[Pradeep_3]

Yes it is ORACLE. And my database is in archive log mode.
And it is already having three redolog files of 10m. I want to add two more.

Regards

[Massimo Bianchi]

Hi,



R/3 note no. 309526 20.11.2001 Page 1
________________________________________________________________________

Number 0309526
Version 0006 from 11.12.2000
Status Released for customer
Set by SAP AG on 11.12.2000

Language EN
Short text Expanding redo log files

Administrator SAP AG
Component XX-SER-TCC Technical Core Competence
________________________________________________________________________

Long text

Symptom
You want to expand redo log files.
Additional key words

Cause and preconditions
An Earylwatch or going live session recommended to adjust the size
ofredo log files.
Solution
To adjust the size of the redo log files, you must create new redo log
files and delete the previous files afterwards.

Create new log files by entering the following command:

alter database add logfile group 11
('/oracle//origloga/log_g11_m1.dbf',
'/oracle//mirrloga/log_g11_m2.dbf') size 50M;

This command creates log file group 11 with two members of size 50MB.
You must make sure that this group does not exist yet. Group '11' only
serves as an example.

Delete old redo log files by entering the following command:

alter database drop logfile group 11;

Again, group '11' only serves as an example; replace it with the group
to be deleted.

If the redo log file is being used, you cannot delete the file.Initiate
a log switch beforehand:

alter system switch logfile;

Source code corrections
________________________________________________________________________

Valid releases

Note is release independent
________________________________________________________________________
Page 2

Valid plattforms

DB system ORACLE
________________________________________________________________________



HTH,
Massimo

[Ian Dennison_1]

Well, Massimo has pretty much nailed the answer for you!

Null points for me, Ian

[Pradeep_3]

Dear Massimo

I am already having three redolog files of 10m.
Which i don't want to delete. And i want to add two more.
i.e after adding two redolog file i will have total five redolog files.
I understand that if my redolog file size is larger then. It takes more in the recovery.So i want five redolog files instead of a single redolog file of 50m.


Regards

[Massimo Bianchi]

hi Paresh,
it was only an example :)

You can adapt the size of the redo log at you need, and you don't need to delete the old ones.


This was an offical procedure from SAP, which uses oracle amd i love its notes, they are always very precise.

It is tru that with larger redolog recover will be a bit slower, but with today's server you will never notice a real difference between
10 and 50 M.

It changes when you go to 100M or higher values, but it not your case.

If you want a very simple command, just issue the following:

alter database add logfile 'FILENAME1' size 10M;
alter database add logfile 'FILENAME2' size 10M;


where FILENAME1 and FILENAME2 are the FULL name of the new redolog.

HTH,
Massimo

[Pradeep_3]

Dear Massimo

Thanks a lot for your precious advice.

But what i need to do for my standby database.


Regards.
Fenil

[Massimo Bianchi]

Hi, i found this from oracle:

Thread Status: Closed
From: Ministerio de Economia y Finanzas 28-Mar-03 15:22
Subject: standby database, add redolog?


standby database, add redolog?

I'm running a standby, and I just multiplexing the online redo logs in my prod db, How I add does files to the standby, in Oracle 8.1.7.

Please help me.



From: Oracle, Helen Schoone 31-Mar-03 13:19
Subject: Re : standby database, add redolog?
Hi. If you have added redo logs on the primary database, you need to refresh the standby controlfile. Additionally, if the directory location/name is different, you will need to reflect that in the LOG_FILE_NAME_CONVERT parameter.

Information can be found in Chapter 4 of the Oracle8i Standby Database Concepts and Administration under the section titled: Adding or Dropping Redo Logs on the Primary Database. Information on the LOG_FILE_NAME_CONVERT parameter can be found in Chapter 5 and in the Oracle8i Reference manual.
Regards,
Helen
Oracle Support Services


Massimo

[twang]

When you add or remove online redo logs, it is a physical change to the primary database. Therefore, it is necessary to refresh the controlfile at the standby database.

The steps include:

1. At the primary site, issue:
alter database create standby controlfile as 'xxx';
2. If the standby database is in managed recovery mode, issue:
recover managed standby database cancel;
3. Shutdown the standby database. Issue:
shutdown immediate
4. Copy the standby database controlfile from the primary site to the
standby database, overwriting the controlfile that exists at the standby
site.
5. Start the standby database instance without mounting the database.
Startup nomount pfile = 'xxx'
6. Mount the standby database.
Alter database mount standby database;
7. Identify and apply the logs in the gap sequence.
8. On the standby database, enable managed recovery. Issue:
recover managed standby database;

[Massimo Bianchi]

PROBLEM
-------
You can resize the online redolog on the Primary Database by creating another
redolog group (see Note: 1035935.6); however, how does this additional group
get propogated over to the Standby Database?


SOLUTION
--------
The goal is to prepare the Standby Database for when it will be activated.

You need only copy the new group of redolog on the standby database. This
will preserve the size of redolog file before activing the Standby Database.

If you configured the init.ora parameter LOG_FILE_NAME_CONVERT on your
Standby Database, then this will match with the directory of the Primary
Database, so you'll not need to do anything.

If the name of any of the redolog files differs from the Primary Ddatabase,
then follow these steps:
o startup nomout standby pfile=init.ora
o ALTER DATABASE STANDBY MOUNT
o ALTER DATABASE RENAME FILE '' TO ''
o RECOVER STANDBY DATABASE...

All of the steps above are valid in the situation where a member has been added
to the existing group of online redologs on the Primary Database as well.


RELATED DOCUMENTS
-----------------
Note: 1035935.6
Backup & recovery guide

[Yogeeraj_1]

hi,

to add to above replies,

Note that you can also multiplex them by simply creating groups with more then 1 member in them.


For example it might look like:


yd@MYDB.MU> alter database add logfile
group 4 ( '/d02/oradata/ora734/redo04.log',
'/d04/oradata/ora734/redo04.log' )
size 10m;

Database altered.


That added a group with 2 members. they are now multiplexed. You can just add members to existing groups as well.

The IMPORTANT thing is to make sure the members of the groups are on DIFFERENT physical devices -- else you are defeating the purpose of multiplexing them in the first place (recovery from media failure)

hope this helps too!

regards
Yogeeraj