redo log file

[Roshan]

Hi,

when I am opening the database, it is taking time. I checked the alert log as shown below:

ORA11G_test>tail -f alert_CICLONE.log
  Current log# 1 seq# 5253 mem# 0: /u02/clone/CI/onlinelog1/redo_g1_m1.log
  Current log# 1 seq# 5253 mem# 1: /u02/clone/CI/onlinelog2/redo_g1_m2.log
Thu Sep 01 16:50:36 2016
Thread 1 advanced to log sequence 5254 (LGWR switch)
  Current log# 2 seq# 5254 mem# 0: /u02/clone/CI/onlinelog1/redo_g2_m1.log
  Current log# 2 seq# 5254 mem# 1: /u02/clone/CI/onlinelog2/redo_g2_m2.log
Thu Sep 01 16:50:49 2016
Thread 1 advanced to log sequence 5255 (LGWR switch)
  Current log# 3 seq# 5255 mem# 0: /u02/clone/CI/onlinelog1/redo_g3_t2_m1.log
  Current log# 3 seq# 5255 mem# 1: /u02/clone/CI/onlinelog2/redo_g3_t2_m2.log
Thu Sep 01 16:51:17 2016
Thread 1 cannot allocate new log, sequence 5256
Checkpoint not complete
  Current log# 3 seq# 5255 mem# 0: /u02/clone/CI/onlinelog1/redo_g3_t2_m1.log
  Current log# 3 seq# 5255 mem# 1: /u02/clone/CI/onlinelog2/redo_g3_t2_m2.log


Do I need to increase the size of redo?
SQL> select group#,sequence#,bytes,archived,status from v$log;

    GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
         1       5261  104857600 NO  CURRENT
         2       5258  104857600 NO  ACTIVE
         3       5259  104857600 NO  ACTIVE
         4       5260  104857600 NO  ACTIVE

[ErmanArslansOracleBlog]

The time to start the database is related with the "redo" and "undo".
Increasing the "redolog" file size, is someting different.
During the start database of the database, both redo and undo is used to put your database in a consistent state. So it depends on the db activity and it depends on how you shutdown your database.
For ex: If you shut down your db normally (shutdown normal), your startup should be faster.
Because, after a "shutdown normal"; no instance recovery is required on the next database startup.
So, for a fast startup, being on a consistent state is important.

Having a big redolog file  is irrelevant with the startup..
Having a big redolog file can be done for performance reasons not for a fast db startup.
However, it seems you still need to have more redolog files, as all of them are active, so your database may hang... (again not for db startup)

[ErmanArslansOracleBlog]

Keep this in mind as well;

having a faster disk for redo&undo however; can decrease the startup time..

[Roshan]

Thanks for the explanation Erman.

I added another redo log group and redo log file and it started well.

Thanks :)

[Roshan]

Hello Erman,

how is undo and redo put database in a consistent state during startup?

[ErmanArslansOracleBlog]

Hi Roshan,

Undo is used to rollback the changes and redo is used to roll forward the changes.
so, when your database is inconsisten state, you do a recovery to put it in a consistent state.
This is called recovery, yes... During a startup, oracle may do an instance recovery, so you don't see it but it recovers the database using undo and redo.
Instance recovery is done while starting the database after a shutdown abort operation , which does not checkpoint the open data files. So, in this situation instance recovery is necessary before the database can be reopened. The other shutdown modes do not require instance recovery before the database can be opened.

Also there are cases where you need to do a recovery (manuel or auto / point in time, complete, incomplete)

Suppose, you have restored yesterday's backup and want to roll forward it to roll forward your database to the current time.
So, that 's where you do a recovery...

You can refer this link for further info : https://docs.oracle.com/cd/B19306_01/server.102/b14220/backrec.htm