What are Redo Logs, Size estimation and How to Resize and/or Add Redo logs

What are Redo Logs?
Each Oracle database has atleast one redo log. This redo log records all changes made in datafiles and allows to replay the SQL Statements. Changes are written ti Redo Logs before they are written to datafiles. In case we need to restore a data file to the time before a change was done on the database we can use the redo information stored in Redo Logs to restore the data file.

How estimate the correct size Redo Logs?
The best way to estimate the size of Redo Logs is to check in alert log and see how frequently log switch occurs and the optimal switch time should be between 20-30 minutes to get better database performance. Oracle recommends a minimum redo log size of 100MB for any production database running 8.1.7 and higher releases of Oracle.

Check in the alert log and see for messages about how frequently the log sequence# are changing, log check pointing and any incomplete redo log switch completion messages. When you examine your alert.log for log switches, you should do it when there are heavier loads in the database. Below is a sample snapshot of redo log switches in alert log:

Fri Jan 27 09:57:40 2004
ARC0: Evaluating archive log 1 thread 1 sequence 174
ARC0: Beginning to archive log 1 thread 1 sequence 174
Creating archive destination LOG_ARCHIVE_DEST_1:
'/u02/app/oracle/product/9.2.0/dbs/arch/arc_1_174.src'
ARC0: Completed archiving log 1 thread 1 sequence 174
Fri Jan 27 09:57:52 2004
Thread 1 advanced to log sequence 176
Current log# 3 seq# 176 mem# 0: /u02/oradata/redo/redo03.log
Fri Jan 27 09:57:52 2004
ARC1: Evaluating archive log 2 thread 1 sequence 175
ARC1: Beginning to archive log 2 thread 1 sequence 175
Creating archive destination LOG_ARCHIVE_DEST_1:
'/u02/app/oracle/product/9.2.0/dbs/arch/arc_1_175.src'
ARC1: Completed archiving log 2 thread 1 sequence 175
Fri Jan 27 09:58:07 2004

The above snapshot from alert log file shows that the current redo logs are not big enough.

Since the oracle recommended optimal log switch time is between 20-30minutes and if the log switch is occuring more frequently please check the v$log view for the current of redo logs and based on how frequently it is switching on an average, you will need to create redo log groups with bigger size and then switch the redo log a few times with “ALTER SYSTEM SWITCH LOGFILE;” so that the CURRENT redo log is one of the bigger redo log file and then drop the redo log groups with smaller size with "ALTER DATABASE DROP LOGFILE...." command.

The default redo log filesize on newer versions of oracle is 100MB by default and oracle recommends to spread the redo logs over multiple physical devices for optimal redo log switch performance.

Please keep in mind that too frequent checkpoints and log file switch completion problems will affect database performance. If there are waits on log file switch completion, you will need to add one or more redo log groups also to resolve this wait event in the Statspack report.


How To Resize and/or Add Redo Logs?

Note: Below steps are applicable from versions 9.2.0.1 and up

1. Review information on existing redo logs.

SQL> SELECT a.group#, b.member, a.status, a.bytes
FROM v$log a, v$logfile b
WHERE a.group#=b.group#;

2. Add new groups

SQL> ALTER DATABASE ADD LOGFILE group 4 ('/log01A.dbf', '/log01B.dbf ') SIZE 512M;
SQL> ALTER DATABASE ADD LOGFILE group 5 ('/log02A.dbf', '/log02B.dbf ') SIZE 512M;
SQL> ALTER DATABASE ADD LOGFILE group 6 ('/log03A.dbf', '/log03B.dbf ') SIZE 512M;

3. Check the status on all redo logs again.

SQL> SELECT a.group#, b.member, a.status, a.bytes FROM v$log a, v$logfile b
WHERE a.group#=b.group#4;

Drop the online redo log groups that are not needed. You must have the ALTER DATABASE system privilege.

Note: Before dropping an online redo log group, consider the following restrictions and precautions:

a. An instance requires at least two groups of online redo log files, regardless of the number of members in the groups. (A group is one or more members.)

b. You can drop an online redo log group only if it is INACTIVE. If you need to drop the current group, first force a log switch to occur.

c. Make sure an online redo log group is archived (if archiving is enabled) before dropping it. This can be determined by:

GROUP# ARC STATUS
--------- --- ----------------
1 YES ACTIVE
2 NO CURRENT
3 YES INACTIVE
4 YES UNUSED
5 YES UNUSED
6 YES UNUSED

d.Check that the group is inactive and archived before dropping it .

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
e. After dropping an online redo log group, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped online redo log files. For more information
refer to Note 395062.1


How to determine the optimal size for redo logs (revisited)?

You can use the V$INSTANCE_RECOVERY view column OPTIMAL_LOGFILE_SIZE to determine the size of your online redo logs. This field shows the redo log file size in megabytes that is considered optimal based on the current setting of FAST_START_MTTR_TARGET. If this field consistently shows a value greater than the size of your smallest online log, then you should configure all your online logs to be at least this size.

Note: The redo log file size affects the MTTR. In some cases, you may be able to refine your choice of the optimal FAST_START_MTTR_TARGET value by re-running the MTTR Advisor with your suggested optimal log file size.


Note: Some of the information presented in this post are in reference to Oracle Metalink notes: 1038851.6, 602066.1 and some based on knowledge. And the information was presented referring to people who do not have access to Oracle Metalink.

Block Checking SQL

Below is a quick SQL to check for blocks in the database:

set pagesize 100

set linesize 250

col module for a20

col event for a26

col last_call for a18

col objectname for a30

col username for a10
select /*+RULE */s.username username , s.sid,s.serial#, p.spid,o.OBJECT_NAME objectname, s.process,substr(s.module,1,20) module, 

to_char(sysdate - s.last_call_et/60/60/24,'DD-MON-YYYY HH24:MI') last_call,

substr(w.event,1,24) event,

decode(block ,0,'NO','YES') BLOCKER,

decode(request,0,'NO','YES') WAITER

from v$lock l, v$session s, v$process p, v$session_wait w,v$locked_object lb, dba_objects o where

(l.request > 0 or l.block > 0 ) and s.sid=l.sid and p.addr=s.paddr and s.last_call_et > 600 and w.sid=s.sid and s.sid = lb.SESSION_ID and lb.OBJECT_ID = o.OBJECT_ID order by last_call_et desc;

Note: The above is SQL is used during some of my daily tasks it can be modified for custom information retrieval. The SQL is just for a quick use if needed and for informational purposes.

Disaster Recovery (DR) Startup and Shutdown Commands

Steps to shutdown a DR Database:
Note: Before shutting down a DR Database it is good to make sure that both Production and DR Databases are in sync.

SQL to check if both PROD and DR are in sync and the SQL needs to be executed on the PROD:

SQL> select max(sequence#), max(sequence#) "Applied log" from v$archived_log where applied='YES';

Example Output:
MAX(SEQUENCE#) APPLIED LOG
-------------- -----------
32412 32412

DR Shutdown commands:

SQL> alter database recover managed standby database cancel;

SQL> shutdown immediate;

DR Startup command:

SQL> startup nomount;

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect from session;

SQL to get tablespace information

Here is a quick SQL to get information regarding tablespaces in a database:

select df.tablespace_name "Tablespace",

totalusedspace "Used MB",

(df.totalspace - tu.totalusedspace) "Free MB",

df.totalspace "Total MB",

round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"

from

(select tablespace_name,

round(sum(bytes) / 1048576) TotalSpace

from dba_data_files 

group by tablespace_name) df,

(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name

from dba_segments 

group by tablespace_name) tu

where df.tablespace_name = tu.tablespace_name ;

Note: The above SQL is for informational only that I used on a daily basis

To Get Active Sessions and SQL Info

Here is a quick SQL to get session and SQL information:

select /*+RULE */ SID,SPID,ses.SERIAL#,ses.USERNAME,ses.module,SQL_TEXT,pro.TERMINAL

from v$sqlarea sql, v$session ses,v$process pro where HASH_VALUE=SQL_HASH_VALUE and status = 'ACTIVE' and

ses.username is not null and

ses.PADDR = pro.ADDR

order by sql_text;
The above SQL statement can be customized based on requirement and if you need more information.
Note: The above SQL is for informational only that I used on a daily basis