Re: Redo Log maintenance

AS I'm sure others will I suggest you read the Oracle concepts guide.

you are however confusing two distinct issues.

1. redo. This is information about database transactions used for recovery purposes. Oracle will gurantee that if you commit a transaction then the data is secure, even though it may not yet have been written to the datafiles. The redo log is this mechanism. You can think of it as a chronological list of transactions applied to the database.
2. rollback. these are database objects used for read consistency. It also of course allows you to rollback uncommitted transactions. you cxan think of redo as a copy of the data before a session started changing it. this copy will be stored until the user commits or rolls back the changes.

It is quite likely that your redo log files of 1mb *are* causing a performance problem. First of all you almost certainly want at least 3 of them. secondly they are likely too small. The oracle defaults for a lot of database files and parameters are way off what people are likely to actually want. this is one of those occasions. I'd start with three groups of at least 5mb each.

--
Niall Litchfield
Oracle DBA
Audit Commission UK

"Wade Chy" <wchy65_at_hotmail.com> wrote in message
news:36df3e33.0110101313.749c634d_at_posting.google.com...


> Hi All

> I am helping somebody maintain the redo log. The database has 2 redo

> log files 1 mb each. They are filling up quick. I have to create 2

> more. If I just create 2 more log files, will it work or I have to

> create the rollback tablespaces and segments for it?

> The reason I am confused about it is because I tried to create a

> database with GUI Oracle Database Configuration Assistant. I saved the

> scripts for creating the database later. It saved 1 batch file and 4

> sql scripts. The first script creates the database with 2 log files

> sized 1024 k with the following statements:

>

> CREATE DATABASE app

> LOGFILE 'E:\test_data\app\redo\redo01.log' SIZE 1024K,

>     'E:\test_data\app\redo\redo02.log' SIZE 1024K

> MAXLOGFILES 32

> MAXLOGMEMBERS 2

> MAXLOGHISTORY 1

> DATAFILE 'E:\test_data\app\system01.dbf' SIZE 10M REUSE

> MAXDATAFILES 254

> MAXINSTANCES 1

> CHARACTER SET WE8ISO8859P1

> NATIONAL CHARACTER SET WE8ISO8859P1;

>

> And the the 2nd script that creates the tablespaces has the following

> statements:

>

> REM ********** TABLESPACE FOR ROLLBACK **********

> CREATE TABLESPACE RBS DATAFILE 'E:\test_data\app\rbs01.dbf' SIZE 12M

> REUSE

> DEFAULT STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 2 MAXEXTENTS 121

> PCTINCREASE 0);

> ALTER DATABASE DATAFILE 'E:\test_data\app\rbs01.dbf' AUTOEXTEND ON;

>

>

> CREATE PUBLIC ROLLBACK SEGMENT RB0 TABLESPACE RBS

> STORAGE (INITIAL 100K NEXT 250K);

> CREATE PUBLIC ROLLBACK SEGMENT RB1 TABLESPACE RBS

> STORAGE (INITIAL 100K NEXT 250K);

> ALTER ROLLBACK SEGMENT "RB0" ONLINE;

> ALTER ROLLBACK SEGMENT "RB1" ONLINE;

>

> It creates a rollback tablespace and 2 rollback segments. What is the

> relation or difference between the log files and the rollback

> tablespce? Which one does the transactions use, the 'rbs01.dbf' or the

> 'E:\test_data\app\redo\redo01.log' SIZE 1024K,

> 'E:\test_data\app\redo\redo02.log' SIZE 1024K?

> Also is there any data dictionary view I can look at to find out if

> the redo log is filling out quickly and there are a lot of log

> switches. I am trying to find out if the smaller logfiles and low

> number of logfile is creating a performance problem.

> Please help.

> Thank you in advance.