MAINTAINING ONLINE REDO LOG FILES

Before adding group and members first check how many group already in the database using the view

SQL > select * from V$LOG;

SQL > select * from V$LOGFILE;

ADDING ONLINE REDO LOG FILE GROUPS

In some cases you may need to create additional log file group. To solve availability problem this can be done by SQL command.

ALTER DATABASE ADD LOGFILE GROUP 4

('D:\ORACLE\ORADATA\ORCL\REDO04A.LOG' ,

 'D:\ORACLE\ORADATA\ORCL\REDO04B.LOG')

SIZE 1M;

ADDING ONLINE REDO LOG FILE MEMBER

We can add new members to existing online redo log file groups using the following SQL command:

ALTER DATABASE ADD LOGFILE MEMBER

 'D:\ORACLE\ORADATA\ORCL\REDO04C.LOG' TO GROUP 4 ,

 'D:\ORACLE\ORADATA\ORCL\REDO03B.LOG' TO GROUP 3

When adding member use the fully specified name of the log file members,

Otherwise the files are created in a default directory of the database server.

DROPPING ONLINE REDO LOGFILE GROUPS

Online redo log file group can be dropped by the SQL command

ALTER DATABASE DROP LOGFILE GROUP 4;

RESTRICTIONS:

While deleting online redo log file group we must consider the following restrictions

1. An instance requires at least 2 group of online redo log files.
2. An ACTIVE and CURRENT group cannot be dropped.
3. When an online redo log file group is dropped, the operating system file are nat deleted.

NOTE: If database is in archive mode group can not be dropped until archival process not completely done. Even group is in INACTIVE MODE

DROPPING ONLINE REDO LOG FILE MEMBERS

We can drop online redo log file member by using SQL statement

ALTER DATABASE DROP LOGFILE MEMBER

'D:\ORACLE\ORADATA\ORCL\REDO02B.LOG';

RESTRICTIONS:

While deleting online redo log file members we must consider the following restrictions

1. We cannot drop the last valid member of any group
2. If the group is current, you must force switch log file before you can drop the member.
3. If the database is in archived mode and the log file group to which the member belongs is not archived, the member can not be dropped
4. when a online redo log file member is dropped, the operation system file cannot be deleted.

REALLOCATING OR RENAMING ONLINE REDO LOG FILES

The location of online redo log files can be changed by renaming the online redo log files.

Before renaming the online redo log files, ensure that the new online redo log file can exists.

Relocate or rename online redo log files in one of the 2 following ways:

1. Add new members and drop old members
2. Alter database rename file command

ALTER DATABASE RENAME FILE COMMAND use following steps:

STEP # 1:

Shutdown the database

SQL > SHUTDOWN;

STEP # 2:

Copy the online redo log file to the new location in operating system

STEP # 3:

To start database at mount

SQL > STARTUP MOUNT;

STEP # 4:

Rename the online redo log file member using the

SQL >ALTER DATABASE RENAME FILE

           ‘D:\ORACLE\ORADATA\ORCL\REDO03C.LOG’

           TO
           ‘D:\ORACLE\ORADATA\REDO03B.LOG’;

CLEARING ONLINE REDO LOG FILES

An online redo log file might become corrupt while the database is open , and ultimately stop database activity because activity cannot continue.

In this situation ALTER DATABASE CLEAR LOGFILE command can be used to reinitialize the online redo log file with out shutdown the database.

The command can overcome two situations where dropping online redo log file is not possible:

• If the are only two log groups
• The corrupt online redo log file belongs to the current group

Use the UNARCHIVED keyword in the command to clear the corrupted online file to avoid archiving.

`

ONLINE REDO LOG FILE CONFIGURATION

To determine the appropriate number of online redo log files for a database, you must test different configurations.

Oracle server multiplexed group can contain different no. of members this is called asymmetric configuration (means all members in a group placed on same disk)

In this case we get temporary result because of disk failure

Oracle recommended try to built symmetric configuration (means member of any group also placed on different location)

By doing this if one member is not available the other is available and instance does not shutdown

Separate archive log file and online redo log files on different disks to reduce contention b/w the ARCn and LGWR

Data files and online files should be placed on different disk to reduce contention b/w LGWR and DBWn

SIZE OF ONLINE REDO LOG FILE

The minimum size of online redo log file is 50KB  and

Maximum size is specifying in the operating system.

SQL > Select * from V$log;

SQL > Select * from V$logfile;

In the V$log views the status column having the values

1. UNUSED:

Indicates that the online redo log file group has never been written to

2. CURRENT:

Indicates the current online redo log file group. This implies that the online redo log file group is active.

3. ACTIVE:

Indicates that the online redo log file group is active but is not the current online        redo log file group. It is needed for crash recovery.

4. INACTIVE:

Indicates that the online redo log file group is no longer needed for instance recovery.

In the V$logfile views the status column having the values

1. INVALID:

Indicates that the file is inaccessible

2. STALE:

Indicates that the contents of the file are incomplete

3. DELETED:

Indicates that the file is no longer used

4. BLANK

Indicates that the file is in use.

Configuring Flashback Database through SQL

In order to configure the Flashback Database feature, we need to step through a series of operations, as follows:

1. Check that our database is in the archive log mode by either querying the  $DATABASE view, or by simply issuing the following command:

SQL> ARCHIVE LOG LIST

The preceding output reveals that the database is indeed running in the archive log mode. If it isn’t, we can turn archive logging on with the ALTER DATABASE statement shown in the following code, after first shutting down the database and starting it up initially in the mount mode:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN

2. Set up a flash recovery area, as we learn in the FLASH RECOVERY AREA CHAPTER

3. Set the DB_FLASHBACK_RETENTION_TARGET initialization parameter to specify how far back you can flashback your database. The following code sets the Flashback target to 1 day (1,440 minutes):

SQL> SHOW PARAMETER RETENTION

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440;

4. Shut down and restart the database in the mount exclusive mode. If we are using a single instance, a simple MOUNT command can be used:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

5. Enable the Flashback Database feature:

SQL> ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE OPEN;

6. Use the ALTER DATABASE OPEN command to open the database and then confirm that the Flashback Database feature is enabled, by querying the V$DATABASE view:

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

Oracle Flashback Concept

FLASHBACK LEVELS

In Oracle Database 10g, you have access to flashback techniques at the row, table, and database levels, as follows:

Row level

We can use Flashback techniques to undo erroneous changes to individual rows. There are three types of row-level Flashback techniques, and all of them rely on undo data stored in the undo tablespace:

• Flashback Query: Allows us to view old row data based on a point in time or an SCN.

we can view the older data and, if necessary, retrieve it and undo erroneous changes.

• Flashback Versions Query: Allows us to view all versions of the same row over a period of time so that you can undo logical errors. It can also provide an audit history of changes, effectively allowing us to compare present data against historical data without performing any DML activity.

• Flashback Transaction Query: Lets us view changes made at the transaction level. This technique helps in analysis and auditing of transactions, such as when a batch job runs twice and you want to determine which objects were affected. Using this technique, we can undo changes made by an entire transaction during a specified period.

Table level

There are two main Flashback features available at the table level:

• Flashback Table: Restores a table to a point in time or to a specified SCN without restoring data files. This feature uses DML changes to undo the changes in a table. The Flashback Table feature relies on undo data.

• Flashback Drop: Allows us to reverse the effects of a DROP TABLE statement, without resorting to a point-in-time recovery. The Flashback Drop feature uses the Recycle Bin to restore a dropped table.

Database level

• Flashback Database: The Flashback Database feature allows us to restore an entire database to a point in time, thus undoing all changes since that time. For example, we can restore a dropped schema or an erroneously truncated table. Flashback Database mainly uses flashback logs to retrieve older versions of the data blocks; it also relies, to a much smaller extent, on archived redo logs to completely recover a database without restoring data files and performing traditional media recovery.

As we can see, Oracle’s Flashback technology employs a variety of techniques. The row-level Flashback techniques and Flashback Table use undo data  Flashback Drop use new concept of Recycle Bin and Flashback Database rely on the new concept of Flashback log data, respectively, to undo errors at various levels. We will focus on these techniques in this chapter.

Flashback vs.Traditional Recovery Techniques

Unlike traditional recovery techniques, the primary use of Flashback techniques isn’t to recover from a media loss, but to recover from human errors. For example, we may accidentally change the wrong set of data or drop a table. Or we may just want to query historical data and perform change analysis. In some extreme cases, we may want to revert the entire database to a previous point in time.

Note: If we have a damaged disk drive, or if there is physical corruption (not logical corruption due to application or user errors) in our database, we must still use the traditional methods of restoring backups and using archived redo logs to perform the recovery.

Traditionally, the only way to recover from human error was to employ traditional backup and restore techniques. The process of restoring the database files and then rolling forward through all the redo logs could often involve significant downtime, however, and Flashback technology offers us a much more efficient and much faster way to recover from logical errors, in most cases while the database is still online and available to users. Furthermore, Flashback techniques allow us to selectively restore certain objects. With traditional techniques, we have no choice but to recover the entire database.

FLASHBACK DATABASE

Before Oracle Database 10g, if we suffered logical database corruption, we would undertake traditional point-in-time recovery techniques, restoring data file backup copies and then using archived redo logs to advance the database forward. This was often time-consuming and cumbersome. No matter how limited the extent of the corruption, we would need to restore entire data files and apply the archived redo logs.

Note: Oracle can check data block integrity by computing checksums before writing the data blocks to disk. When the block is subsequently read again, the checksum for the data block is computed again, and if the two checksums differ, there is likely corruption in the data block. By setting the DB_BLOCK_CHECKSUM initialization parameter to FULL, we can make the database perform the check in the database buffer cache itself, thus eliminating the possibility of corruption at the physical disk level. The DB_BLOCK_CHECKSUM parameter is FALSE by default.

In Oracle Database 10g, the Flashback Database feature restores data files but without requiring backup data files and using just a fraction of the archived redo log information. A Flashback Database operation simply reverts all data files of the database to a specified previous point in time. With Flashback Database, the time it takes to recover is directly proportional to the number of changes that we need to undo. Thus, it is the size of the error and not the size of the database that determines the time it takes to recover. This means that we can recover from logical errors in a fraction of the time—perhaps as little as a hundredth of the time, depending on the size of the database— that it would take using traditional methods.

Note: Flashing back a database is possible only when there is no media failure. If you lose a data file or it becomes corrupted, you’ll have to recover using a restored data file from backups.

We can use Flashback Database in the following situations:

Ø  To retrieve a dropped schema

Ø  When a user error affects the entire database

Ø  When we truncate a table in error

Ø  When a batch job performs only partial changes

The Flashback Database feature uses flashback database logs, which are stored in the new flash recovery area, to undo changes to a point in time just before a specified target time or SCN. Since the specified target time and the actual recovery time may differ slightly, we then use archived redo logs to recover the database over the short period of time between the target time and the actual recovery time.

Once the Flashback Database feature is enabled, we simply use the FLASHBACK DATABASE command to return the database to its state at a previous time, SCN, or log sequence. We can issue the FLASHBACK DATABASE command from either RMAN or SQL*Plus. The only difference is that RMAN will automatically retrieve the necessary archived redo logs, whereas in SQL*Plus we may have to supply the archived redo logs, unless we use the SET AUTORECOVERY ON feature in SQL*Plus. We’ll take a look at the whole Flashback Database process in more detail shortly, but first let’s look at how to enable (and disable) the Flashback Database feature.

Tip Since we need the current data files in order to apply changes to them, we can’t use the Flashback Database feature in cases where a data file has been damaged or lost.