Redo Logs :2

Multiplexing Redo Logs:

Oracle Database lets you multiplex the redo log files of an instance to safeguard against damage to any single file. When redo log files are multiplexed, LGWR concurrently writes the same redo log information to multiple identical redo log files, thereby eliminating a single point of redo log failure.

Multiplexed redo log files are called groups. Each redo log file in a group is called a member. Redo01 and redo01a are both members of Group 1, redo02 and redo02a are both members of Group 2, and so forth. Each member in a group must be exactly the same size.

Whenever LGWR cannot write to a member of a group, the database marks that member as INVALID and writes an error message to the LGWR trace file and to the database alert file to indicate the problem with the inaccessible files

Multiplexed redo log should be symmetrical: all groups of the redo log should have the same number of members. However, the database does not require that a multiplexed redo log be symmetrical. For example, one group can have only one member, and other groups can have two members. This configuration protects against disk failures that temporarily affect some redo log members but leave others intact.

Creating Redo Log Groups:

To create new redo log group we can issue like this---

Alter database add logfile size 500k;
Or
Alter database add logfile group 1 size 500k;

Creating Redo Log Members:

In some cases, it might not be necessary to create a complete group of redo log files. A group could already exist, but not be complete because one or more members of the group were dropped (for example, because of a disk failure). In this case, you can add new members to an existing group.

To create redo log members we can issue like this…

Alter database add logfile member to group 1;
Filename should be specified, sizes need not to be..size is determined by existing members.
Renaming Redo Log:

This should be done when your DB is in mount state…move the file by using UNIX commands... (mv) and open your DB for normal operations.

mv $ORACLE_HOME/oradata/test/redo01.log $ORACLE_HOME/oradata/test/redo02.log

Dropping Redo Groups:

An instance requires at least two groups of redo log files, regardless of the number of members in the groups. (A group comprises one or more members.)
You can drop a redo log group only if it is inactive. If you need to drop the current group, first force a log switch to occur.
Make sure a redo log group is archived (if archiving is enabled) before dropping it.

Drop a group like this…

Alter database drop logfile group 1;

Dropping Redo Log Member:

To drop a redo log member, you must have the alter database system privilege.

You can drop a redo log member only if it is not part of an active or current group. If you want to drop a member of an active group, first force a log switch to occur.

Drop a member like this…

Alter database drop logfile member ;

When a redo log member is dropped from the database, the operating system file is not deleted from disk. Rather, the control files of the associated database are updated to drop the member from the database structure. After dropping a redo log file, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped redo log file.

Viewing Redo Log Information:

v$log --- Displays the redo log file information from the control file
v$logfile --- Identifies redo log groups and members and member status
v$log_history --- Contains log history information

Redo Logs

Redo Logs:

Redo logs are most important when you are recovering your DB. Every instance of DB has an associated redo logs to protect the DB in case of instance failure.

Content of Redo Log:

Redo log files are filled with redo records. A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database. For example, if you change a salary value in an employee table, you generate a redo record containing change vectors that describe changes to the data segment block for the table, the undo segment data block, and the transaction table of the undo segments.

Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.

Redo records are buffered in a circular fashion in the redo log buffer of the SGA and are written to one of the redo log files by the Log Writer (LGWR) database background process. Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file, and assigns a system change number (SCN) to identify the redo records for each committed transaction.

Active and Inactive Redo:

Oracle Database uses only one redo log files at a time to store redo records written from the redo log buffer. The redo log file that LGWR is actively writing to is called the current redo log file.

Redo log files that are required for instance recovery is called active redo log files. Redo log files that are no longer required for instance recovery are called inactive redo log files.

Log Switch and Sequence Number:

A log switch is the point at which the database stops writing to one redo log file and begins writing to another. Normally, a log switch occurs when the current redo log file is completely filled and writing must continue to the next redo log file. But you can configure log switches to occur at regular intervals, regardless of whether the current redo log file is completely filled. You can also force log switches manually

Oracle Database assigns each redo log file a new log sequence number every time a log switch occurs and LGWR begins writing to it.

Monitoring Alert Log File

• It is important for a DBA to check alert log file regularly to detect problems before they become serious.
• Internal errors like ORA-600 and block corruption errors like ORA-1578 or ORA-1498.
• Locations of alert log file is given as $ORACLE_HOME/admin/TEST/bdump (background_dump_dest).
• Alert log file consist of
  a) checkpoint start and end times
  Note: the values are written into alert log file only if LOG_CHECKPOINTS_TO_ALERT parameter is TRUE.
  b) Incomplete checkpoints
  c) Time to perform archiving
  d) Instance recovery start and complete times
  e) Deadlock and timeout errors
• Along with alert log file in bdump, some other trace files created by background process which in turn helps us to diagnose and troubleshoot.
• Tracing can be enabled at Instance Level and at Session Level.
• Instance Level: This can be enabled or disabled by SQL_TRACE parameter. Default value will be False.
• Session Level: Following statement enables writing into a trace file
  Execute dbms_system.set_sql_trace_in_session (10, 22, TRUE);
  Where 10 and 22 are the SID and SERIAL# in v$session view.

How to monitor tablespaces proactively from back-end in 10g

• MMON background process monitors the free space in each tablespace and sends out the alerts.
• we create a tablespace for testing
  create tablespace test datafile '/apps/oracle/oracle/product/10.2.0/oradata/orcl/test01.dbf' size 100m extent management local uniform size 10m;
  
  Tablespace created.
• this pl/sql procedure explains us to alert when the tablespace is 80% full and 95% full
  execute DBMS_SERVER_ALERT.SET_THRESHOLD(-
  > DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,DBMS_SERVER_ALERT.OPERATOR_GE,'80',-
  > DBMS_SERVER_ALERT.OPERATOR_GE,'95',1,1,NULL,-
  > DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,'TEST');
  
  PL/SQL procedure successfully completed.
• by querying from this table we can see which tablespace is the most effected.
  select reason from dba_outstanding_alerts;
  
  for example if test tablespace is more than 80% we can an entry here.
• we can add a datafile to test tablespace so that we can avoid any further complications.
  alter tablespace test add datafile ‘'/apps/oracle/oracle/product/10.2.0/oradata/orcl/test02.dbf’ size 50m;
  Tablespace altered
• by quering again from this table we can see that test tablespace alert is gone from the view. ( for example)
  select reason from dba_outstanding_alerts;
• all cleared alerts will be shown up in dba_alert_history view
  select reason, resolution from dba_alert_history;