Managing Control,Redolog and Archived Redolog Files

           MANAGING CONTROL FILES AND REDO LOGFILES

Control File:

Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database. The control file includes:

·         The database name

·         Names and locations of associated data files and redo log files

·         The timestamp of the database creation

·         The current log sequence number

·         Checkpoint information

·         Table space information

·         The log file information .

·         Archive log file information .

·         Backup set and backup piece information .

·         Backup data file and redo log information .

The control file must be available for writing by the Oracle Database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.

The control file of an Oracle Database is created at the same time as the database. By default, at least one copy of the control file is created during database creation. On some operating systems the default is to create multiple copies. You should create two or more copies of the control file during database creation. You can also create control files later, if you lose control files or want to change particular settings in the control files.

Provide Filenames for the Control Files:

You specify control file names using the CONTROL_FILES initialization parameter in the database initialization parameter file. The instance recognizes and opens all the listed file during startup, and the instance writes to and maintains all listed control files during database operation.

If you do not specify files for CONTROL_FILES before database creation:

·         If you are not using Oracle Managed Files, then the database creates a control file and uses a default filename. The default name is operating system specific.

·         If you are using Oracle Managed Files, then the initialization parameters you set to enable that feature determine the name and location of the control files,

·         If you are using Oracle Automatic Storage Management (Oracle ASM), you can place incomplete Oracle ASM filenames in the DB_CREATE_FILE_DEST and  DB_RECOVERY_FILE_DEST initialization parameters. Oracle ASM then automatically creates control files in the appropriate places. 

Multiplex Control Files on Different Disks

Every Oracle Database should have at least two control files, each stored on a different physical disk. If a control file is damaged due to a disk failure, the associated instance must be shut down. Once the disk drive is repaired, the damaged control file can be restored using the intact copy of the control file from the other disk and the instance can be restarted. In this case, no media recovery is required.

The behavior of multiplexed control files is this:

·         The database writes to all filenames listed for the initialization parameter CONTROL_FILES  in the database initialization parameter file.

·         The database reads only the first file listed in the CONTROL_FILES parameter during database operation.

·         If any of the control files become unavailable during database operation, the instance becomes inoperable and should be aborted.

Note:

Oracle strongly recommends that your database has a minimum of two control files and that they are located on separate physical disks.

Multiplexing Control File

Steps:

      1.      Shutdown the Database.

SQL>SHUTDOWN IMMEDIATE;

    2.      Copy the control file from old location to new location using operating system command. For example.

$cp /u01/oracle/ica/control.ora  /u02/oracle/ica/control.ora

    3.      Now open the parameter file and specify the new location like this

CONTROL_FILES=/u01/oracle/ica/control.ora

Change it to

CONTROL_FILES=/u01/oracle/ica/control.ora,/u02/oracle/ica/control.ora

    4.      Start the Database

Now Oracle will start updating both the control files and, if one control file is lost you can copy  it from another location.

Back Up Control Files

It is very important that you back up your control files. This is true initially, and every time you change the physical structure of your database. Such structural changes include:

·         Adding, dropping, or renaming data files

·         Adding or dropping a tablespace, or altering the read/write state of the tablespace

·         Adding or dropping redo log files or groups

Manage the Size of Control Files

The main determinants of the size of a control file are the values set for the  MAXDATAFILES,  MAXLOGFILES,  MAXLOGMEMBERS,  MAXLOGHISTORY,  and MAXINSTANCES parameters in the CREATE DATABASE statement that created the associated database. Increasing the values of these parameters increases the size of a control file of the associated database.

Creating Control Files

The initial control files of an Oracle Database are created when you issue the CREATE DATABASE  statement. The names of the control files are specified by the CONTROL_FILES parameter in the initialization parameter file used during database creation. The filenames specified in  CONTROL_FILES should be fully specified and are operating system specific. The following is an example of a CONTROL_FILES initialization parameter:

CONTROL_FILES = (/u01/oracle/prod/control01.ctl, /u02/oracle/prod/control02.ctl,

                 /u03/oracle/prod/control03.ctl)

If files with the specified names currently exist at the time of database creation, you must specify the CONTROLFILE REUSE clause in the CREATE DATABASE statement, or else an error occurs. Also, if the size of the old control file differs from the SIZE parameter of the new one, you cannot use the REUSE clause.

Creating Additional Copies, Renaming, and Relocating Control Files

You can create an additional control file copy for multiplexing by copying an existing control file to a new location and adding the file name to the list of control files. Similarly, you rename an existing control file by copying the file to its new name or location, and changing the file name in the control file list. In both cases, to guarantee that control files do not change during the procedure, shut down the database before copying the control file.

To add a multiplexed copy of the current control file or to rename a control file:

·         Shut down the database.

·         Copy an existing control file to a new location, using operating system commands.

·          Edit the CONTROL_FILES parameter in the database initialization parameter file to add the new control file name, or to change the existing control filename.

·          Restart the database.

When to Create New Control Files

It is necessary for you to create new control files in the following situations:

·         All control files for the database have been permanently damaged and you do not have a control file backup.

·         You want to change the database name.

For example, you would change a database name if it conflicted with another database name in a distributed environment.

The compatibility level is set to a value that is earlier than 10.2.0, and you must make a change to an area of database configuration that relates to any of the following parameters from the CREATE DATABASE or CREATE CONTROLFILE commands: MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES. If compatibility is 10.2.0 or later, you do not have to create new control files when you make such a change; the control files automatically expand, if necessary, to accommodate the new configuration information.

For example, assume that when you created the database or re-created the control files, you set MAXLOGFILES to 3. Suppose that now you want to add a fourth redo log file group to the database with the ALTER DATABASE command. If compatibility is set to 10.2.0 or later, you can do so and the control files automatically expand to accommodate the new log file information. However, with compatibility set earlier than 10.2.0, your ALTER DATABASE command would generate an error, and you would have to first create new control files.

Changing the Name of a Database

If you ever want to change the name of database or want to change the setting of MAXDATAFILES, MAXLOGFILES,MAXLOGMEMBERS then you have to create a new control file.

Creating A New Control File

Follow the given steps to create a new controlfile

Steps

1.      First generate the create controlfile statement

SQL>alter database backup controlfile to trace;

After giving this statement oracle will write the CREATE CONTROLFILE statement in a trace file. The trace file will be randomly named something like ORA23212.TRC and it is created in USER_DUMP_DEST directory.

2.      Go to the USER_DUMP_DEST directory and open the latest trace file in text editor. This file will contain the CREATECONTROLFILE statement. It will have two sets of statement one with RESETLOGS and another without RESETLOGS. Since we are changing the name of the Database we have to use RESETLOGS option of CREATE CONTROLFILE statement. Now copy and paste the statement in a file. Let it be c.sql

3.      Now open the c.sql file in text editor and set the database name from ica to prod shown in an example below

CREATE CONTROLFILE

   SET DATABASE prod   

   LOGFILE GROUP 1 ('/u01/oracle/ica/redo01_01.log',

                    '/u01/oracle/ica/redo01_02.log'),

           GROUP 2 ('/u01/oracle/ica/redo02_01.log',

                    '/u01/oracle/ica/redo02_02.log'),

           GROUP 3 ('/u01/oracle/ica/redo03_01.log',

                    '/u01/oracle/ica/redo03_02.log')

   RESETLOGS

   DATAFILE '/u01/oracle/ica/system01.dbf' SIZE 3M,

            '/u01/oracle/ica/rbs01.dbs' SIZE 5M,

            '/u01/oracle/ica/users01.dbs' SIZE 5M,

            '/u01/oracle/ica/temp01.dbs' SIZE 5M

   MAXLOGFILES 50

   MAXLOGMEMBERS 3

   MAXLOGHISTORY 400

   MAXDATAFILES 200

   MAXINSTANCES 6

   ARCHIVELOG;

4.      Start and do not mount the database.

SQL>STARTUP NOMOUNT;

5.      Now execute c.sql script

SQL> @/u01/oracle/c.sql 

6.      Now open the database with RESETLOGS

SQL>ALTER DATABASE OPEN RESETLOGS;

Cloning an Oracle Database.

You have a Production database running in one server. The company management wants to develop some new modules and they have hired some programmers to do that. Now these programmers require access to the Production database and they want to make changes to it. You as a DBA can’t give direct access to Production database so you want to create a copy of this database on another server and wants to give developers access to it.

Let us see an example of cloning a database

We have a database running the production server with the following files

PARAMETER FILE located in /u01/oracle/ica/initica.ora

CONTROL FILES=/u01/oracle/ica/control.ora
BACKGROUND_DUMP_DEST=/u01/oracle/ica/bdump
USER_DUMP_DEST=/u01/oracle/ica/udump
CORE_DUMP_DEST=/u01/oracle/ica/cdump
LOG_ARCHIVE_DEST_1=”location=/u01/oracle/ica/arc1”
DATAFILES =

     /u01/oracle/ica/sys.dbf
     /u01/oracle/ica/usr.dbf
     /u01/oracle/ica/rbs.dbf
     /u01/oracle/ica/tmp.dbf
     /u01/oracle/ica/sysaux.dbf

LOGFILE=

     /u01/oracle/ica/log1.ora
     /u01/oracle/ica/log2.ora

Now you want to copy this database to SERVER  2 and in SERVER 2 you don’t have /u01 filesystem. In SERVER 2 you have /d01filesystem.

To Clone this Database on SERVER 2 do the following.

Steps :-

1.      In SERVER 2 install the same version of o/s and same version Oracle as in SERVER 1.

2.      In SERVER 1 generate CREATE CONTROLFILE statement by typing the following command

 SQL>alter database backup controlfile to trace;

 Now, go to the USER_DUMP_DEST directory and open the latest trace file. This file will contain steps and as well as CREATECONTROLFILE statement. Copy the CREATE CONTROLFILE statement and paste in a file. Let the filename be cr.sql

 The CREATE CONTROLFILE Statement will look like this.

CREATE CONTROLFILE

   SET DATABASE prod   

   LOGFILE GROUP 1 ('/u01/oracle/ica/log1.ora'

           GROUP 2 ('/u01/oracle/ica/log2.ora'

  DATAFILE '/u01/oracle/ica/sys.dbf' SIZE 300M,

            '/u01/oracle/ica/rbs.dbf' SIZE 50M,

            '/u01/oracle/ica/usr.dbf' SIZE 50M,

            '/u01/oracle/ica/tmp.dbf' SIZE 50M,

            ‘/u01/oracle/ica/sysaux.dbf’ size 100M;

   MAXLOGFILES 50

   MAXLOGMEMBERS 3

   MAXLOGHISTORY 400

   MAXDATAFILES 200

   MAXINSTANCES 6

  ARCHIVELOG;

 3. In SERVER 2 create the following directories

$cd /d01/oracle

$mkdir ica

$mkdir arc1

$cd ica

$mkdir bdump udump cdump

 Shutdown the database on SERVER 1 and transfer all datafiles, logfiles and  control file to SERVER 2 in /d01/oracle/icadirectory.

Copy parameter file to SERVER 2 in /d01/oracle/dbs directory and copy all archive log files to SERVER 2 in /d01/oracle/ica/arc1 directory.

 Copy the cr.sql script file to /d01/oracle/ica directory. 

4.      Open the parameter file SERVER 2 and change the following parameters

CONTROL FILES=//d01/oracle/ica/control.ora
BACKGROUND_DUMP_DEST=//d01/oracle/ica/bdump
USER_DUMP_DEST=//d01/oracle/ica/udump
CORE_DUMP_DEST=//d01/oracle/ica/cdump
LOG_ARCHIVE_DEST_1=”location=//d01/oracle/ica/arc1”

5.      Now, open the cr.sql file in text editor and change the locations like this

CREATE CONTROLFILE

   SET DATABASE prod   

   LOGFILE GROUP 1 ('//d01/oracle/ica/log1.ora'

           GROUP 2 ('//d01/oracle/ica/log2.ora'

  DATAFILE '//d01/oracle/ica/sys.dbf' SIZE 300M,

            '//d01/oracle/ica/rbs.dbf' SIZE 50M,

            '//d01/oracle/ica/usr.dbf' SIZE 50M,

            '//d01/oracle/ica/tmp.dbf' SIZE 50M,

            ‘//d01/oracle/ica/sysaux.dbf’ size 100M;

   MAXLOGFILES 50

   MAXLOGMEMBERS 3

   MAXLOGHISTORY 400

   MAXDATAFILES 200

   MAXINSTANCES 6

   ARCHIVELOG;

In SERVER 2 export ORACLE_SID environment variable and start the instance

$export ORACLE_SID=ica

$sqlplus

Enter User:/ as sysdba

SQL> startup nomount;

6.      Run cr.sql script to create the controlfile

SQL>@/d01/oracle/ica/cr.sql

7.      Open the database

SQL>alter database open;

                DIFFERENCE BETWEEN RESETLOGS  AND NORESETLOGS

NORESETLOGS:

The NORESETLOGS option does not clear the redo log files during startup and the online redo logs to be used for recovery. Only used in scenario where MANUAL RECOVERY is started, CANCEL is used, and then RECOVER DATABASE is started

RESETLOGS

CAUTION: Never use RESETLOGS unless necessary.
Once RESETLOGS is used then the redo log files cannot be used and any completed transactions in those redo logs are lost!!
Before using the RESETLOGS option take an offline backup of the database.
The RESETLOGS option clears all the online redo logs and modifies all the online data files to indicate no recovery is needed. After resetting the redo logs none of the existing log files or data file backups can be used. In the control file, the log sequence number is modified, which is very important for recovery purposes. The recovery will be applied only to the log files whose sequence number is greater than log sequence number in the control file. One has to be very cautious when using RESETLOGS option. It is important to remember that all datafiles must be online otherwise they will become useless once the database is up.

Note that after you perform any kind of incomplete recovery, the logs are always reset. Essentially, the RESETLOGS option reinitializes the redo log files, erasing all the redo information they currently have, and resets the log sequence number to 1. To apply any archived redo logs to a datafile, the SCNs and time stamps in the database files have to match the SCNs and time stamps in the headers of the archived redo log files, and when you perform a RESETLOGS operation, the datafiles are stamped with new SCN and time stamp information, making it impossible for the older

archived redo logs to be applied to them by mistake.

The RESETLOGS option is used under these circumstances:

• When you use a backup control file to recover

• When you perform an incomplete recovery, rather than a complete recovery

• When you recover using a control file created with the RESETLOGS option

If you were to do the incomplete recovery using an SCN, the SET UNTIL command would be  modified  as SET UNTIL SCN nnnn. If you were to use an archived log sequence number, the command would be SET UNTIL LOGSEQ=nnnn THREAD=nnnn, where LOGSEQ is the log you want to recover to.

                ONLINE REDO LOGS MANAGEMENT

REDO LOG:

The online redo logs store all changes made to the database. Every Oracle database must have at least 2 redo logfile groups. Oracle writes all statements except, SELECT statement, to the logfiles. This is done because Oracle performs deferred batch writes i.e. it does write changes to disk per statement instead it performs write in batches. So in this case if a user updates a row, Oracle will change the row in db_buffer_cache and records the statement in the logfile and give the message to the user that  row is updated.  Actually the row is not yet written back to the datafile but still it give the message to the user that row is updated. After 3 seconds the row is actually written to the datafile. This is known as deferred batch writes. 

Since Oracle defers writing to the datafile there is chance of power failure or system crash before the row is written to the disk. That’s why Oracle writes the statement in redo logfile so that in case of power failure or system crash oracle can re-execute the statements next time when you open the database.

If you operate your database in NOARCHIVELOG mode, your redo log consists of on-line redo log files only. If you run your database in ARCHIVELOG mode, your redo log consists of both on-line and archived redo log files. The archived redo log files can be either off-line, stored on a tape device for instance, or on-line on disk.

Contents of the Online Redo Log

Each redo log files contains redo record entries. Each redo record entry is a set of changes. Each change is a description of changes made to a single block for a data segment block and a rollback segment block. These changes allow reconstruction of the database by being reappliable in the future. The online redo log allows recovery of both data and rollback data. On transaction commit the LGWR writes redo records from the redo log buffer to one of the online redo log files. A system change number (SCN) is assigned to be able to identify each redo record in the future.

The Process of Online Redo Log File Writes

Oracle needs atleast two online redo log files. When the last available online redo log is complete then the LGWR cycles to the first online redo log file. Note that only the active redo log files are required for recovery. Whenever a log switch occurs a new log sequence number is assigned to the redo log. Thus if and when the redo log is archived the log keeps that log sequence number.

SQL>  select GROUP#,THREAD#,SEQUENCE#,BLOCKSIZE,MEMBERS,STATUS from v$log;

    GROUP#    THREAD#  SEQUENCE#  BLOCKSIZE    MEMBERS    STATUS

----------          ----------        ----------        ----------            ----------         ----------------

         1                 1                  13                 512                  1                       ACTIVE

         2                 1                  14                 512                1                       CURRENT

         3                 1                  12                 512                 1                       INACTIVE

status:

1. ACTIVE: the redo logfile is required for recovery, it can be archived or not archived

2.CURRENT: the current transaction is using the current redo log file.

3.INACTIVE: the redo logfile is not required for recovery, it can be archived or not archived.

4.UNUSED: the redo logfile is newly created or not yet used.(the deleted redo log group has recreate with same name by using reuse clause.

5. INVALID: status will be cleared when those Redo Log Files will be initialized, which means when Log Writer will write to them for the first time.

Online Redo Log Failure

In the case where the LGWR cannot write to the current redo log the redo log is marked as STALE. Normal redo log files are marked as ACTIVE, INACTIVE or CURRENT.

alter system switch logfile means oracle will stop writing to the current redo log file and start writing to the new redo log file.

SQL> alter system switch logfile;

System altered.

SQL> select GROUP#,THREAD#,SEQUENCE#,BLOCKSIZE,MEMBERS,STATUS from v$log;

    GROUP#    THREAD#                 SEQUENCE#      STATUS

----------               ----------               ----------              ----------------

         1                    1                             13                           INACTIVE

         2                    1                             14                            ACTIVE

         3                    1                             15                           CURRENT

you can check the redo logfile switch information from alter log file also.

Sequence of Storage                 

• Prior to Commit or Rollback.
1. Write the database block buffer to the datafiles if required.
2. Write the redo log buffer to the redo log files if required.
3. Write the changes to the rollback segment.
• Commit.
1. Clears the rollback segment.
• Rollback.
1. Apply the rollback segment to the redo buffer or the redo log files.
2. Apply the rollback segment to the database buffer or the datafiles.

Multiplexing Redo Log Files

To protect against a failure involving the redo log itself, Oracle Database allows a multiplexed redo log, meaning that two or more identical copies of the redo log can be automatically maintained in separate locations. For the most benefit, these locations should be on separate disks. Even if all copies of the redo log are on the same disk, however, the redundancy can help protect against I/O errors, file corruption, and so on. 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.

Multiplexing is implemented by creating groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of the group. Each redo log group is defined by a number, such as group 1, group 2, and so on.

A_LOG1 and B_LOG1 are both members of Group 1, A_LOG2 and B_LOG2 are both members of Group 2, and so forth. Each member in a group must be exactly the same size. Each member of a log file group is concurrently active—that is, concurrently written to by LGWR—as indicated by the identical log sequence numbers assigned by LGWR.  first LGWR writes concurrently to both  A_LOG1 and B_LOG1. Then it writes concurrently to both A_LOG2 and B_LOG2, and so on. LGWR never writes concurrently to members of different groups (for example, to A_LOG1 and B_LOG2).

How many Redo Logs

The required minimum is two redo logs. The best option is to maintain as few redo logs as possible without restricting the LGWR. The most restricting effects involve the LGWR waiting for completion of redo log archiving or checkpointing. There are a number of CREATE DATABASE parameters which can restrict the maximum redo log configuration.

• MAXLOGFILES - maximum number of groups of online redo log files.
• LOG_FILES - minimum number of log files.
• MAXLOGMEMBERS - maximum duplicated redo log members within a redo log group.

Adding a New Redo Logfile Group

To add a new Redo Logfile group to the database give the following command

SQL> alter database add logfile group 4 '/u01/app/oracle/oradata/mydb/redo04.log' size 10m;

Database altered.

Note: You can add groups to a database up to the MAXLOGFILES setting you have specified at the time of creating the database. If you want to change MAXLOGFILE setting you have to create a new controlfile.

Adding Members to an existing group

To add new member to an existing group give the following command

SQL> alter database add logfile member '/u01/app/oracle/oradata/mydb/redo0401.log' to group 4;

Database altered.

Note: You can add members to a group up to the MAXLOGMEMBERS setting you have specified at the time of creating the database. If you want to change MAXLOGMEMBERS setting you have create a new controlfile

Important: Is it strongly recommended that you multiplex logfiles i.e. have at least two log members, one member in one disk and another in second disk, in a database.

Dropping Members from a group

You can drop member from a log group only if the group is having more than one member and if it is not the current group. If you want to drop members from the current group, force a log switch or wait so that log switch occurs and another group becomes current. To force a log switch give the following command

SQL>alter system switch logfile;

The following command can be used to drop a logfile member

SQL> alter database drop logfile member '/u01/app/oracle/oradata/mydb/redo0401.log';

Database altered.

Note: When you drop logfiles the files are not deleted from the disk. You have to use O/S command to delete the files from disk.

Dropping Logfile Group

Similarly, you can also drop logfile group only if the database is having more than two groups and if it is not the current group.

SQL>alter database drop logfile group 4;

Note: When you drop logfiles the files are not deleted from the disk. You have to use O/S command to delete the files from disk.

Create redolog group with same name which we had deleted earlier

SQL> alter database add logfile group 4('/u01/app/oracle/oradata/mydb/redo04.log') size 10m reuse;

Database altered.

In this situation we can see the status of new redo logfile as UNUSED.

Resizing Logfiles

You cannot resize logfiles. If you want to resize a logfile create a new logfile group with the new size and subsequently drop the oldlogfile group.

Renaming or Relocating Logfiles

To Rename or Relocate Logfiles perform the following steps

For Example, suppose you want to move a  logfile  from  ‘/u01/oracle/ica/log1.ora’ to‘/u02/oracle/ica/log1.ora’, then do the following

Steps

1.      Shutdown the database

SQL>shutdown immediate;

2.      Move the logfile from Old location to new location using operating system command

$mv /u01/oracle/ica/log1.ora  /u02/oracle/ica/log1.ora

3.      Start and mount the database

SQL>startup mount

4.      Now give the following command to change the location in controlfile

        SQL>alter database rename file ‘/u01/oracle/ica/log1.ora’ to ‘/u02/oracle/ica/log2.ora’;

5.      Open the database

SQL>alter database open;

 Clearing REDO LOGFILES

A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used reinitialize the file without shutting down the database.

The following statement clears the log files in redo log group number 3:

ALTER DATABASE CLEAR LOGFILE GROUP 3;

This statement overcomes two situations where dropping redo logs is not possible:

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

If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

This statement clears the corrupted redo logs and avoids archiving them. The cleared redo logs are available for use even though they were not archived.

If you clear a log file that is needed for recovery of a backup, then you can no longer recover from that backup. The database writes a message in the alert log describing the backups from which you cannot recover

Viewing Information About Logfiles

To See how many logfile groups are there and their status type the following query.

SQL>SELECT * FROM V$LOG;

GROUP# THREAD#   SEQ   BYTES  MEMBERS  ARC STATUS     FIRST_CHANGE# FIRST_TIM

------ ------- ----- -------  -------  --- ---------  ------------- ---------

     1       1 20605 1048576        1  YES ACTIVE          61515628 21-JUN-07

     2       1 20606 1048576        1  NO  CURRENT         41517595 21-JUN-07

     3       1 20603 1048576        1  YES INACTIVE        31511666 21-JUN-07

     4       1 20604 1048576        1  YES INACTIVE        21513647 21-JUN-07

 To See how many members are there and where they are located give the following query

To View Log file Names:

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE                   MEMBER                                                                              IS_

----------          ------        -------                ---------------------------------------------                       ---

         3                          ONLINE            /u01/app/oracle/oradata/mydb/redo03.log                NO

         2                          ONLINE            /u01/app/oracle/oradata/mydb/redo02.log                NO

         1                          ONLINE            /u01/app/oracle/oradata/mydb/redo01.log               NO

To create redologfiles and to increase size of the redolog files down time is not required you can do it online.

                                MANAGING ARCHEVED REDO LOGS

ARCHIVED REDO LOG:

Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log. The archiver process is used when the system is being operated in an archivelog mode—that is, the changes logged to the redo log files are being saved and not being overwritten by new changes. If you run your database in the no archivelog mode, Oracle will overwrite the redo log files with new redo log records. When you choose to run the instance in an archivelog mode, no such overwriting can take place—each filled log will be saved or archived in a special location.

Note: In NOARCHIVE LOG mode the database can only be restored and not recovered. All recovery is performed from the archive logs and not the redo logs. When the database is not in archive mode only full database backups are available, ie. individual tablespaces cannot be backed up and restored.

ARchiving performance can be improved by creating multple archiver processes (ARCn). Set the LOG_ARCHIVE_MAX_PROCESSES parameter in the init.ora file between 1 and 10. The archiver process will archive the redo log files to the location you specify in the SPFILE or the

init.ora file. If a huge number of changes are being made to your database, and your logs are consequently filling up very quickly, you can use multiple archiver processes up to a maximum of 30. The LOG_ARCHIVE_MAX_PROCESSES parameter in the initialization file will determine how many archiver processes Oracle will initially start. If the log writer process is writing logs faster than the default single archiver process can archive them, the LGWR process automatically starts a new ARCn process, thus raising the number of processes from the default of 2. Multiple archive buffers (LOG_ARCHIVE_BUFFERS) can help to force the ARCn processes to read the archive log at the same time that they write the output log.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=8;

How to keep the database in archive log mode.

we have to check the below init.ora parameters

1. log_archive_dest_1

2. log_archive_format

SQL> show parameter log_archive_dest_1

NAME                                           TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_1                   string

SQL> show parameter log_archive_format

NAME                                            TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_format                   string      %t_%s_%r.dbf

Running a Database in NOARCHIVELOG Mode:

When you run your database in NOARCHIVELOG mode, you disable the archiving of the redo log. The database control file indicates that filled groups are not required to be archived. Therefore, when a filled group becomes inactive after a log switch, the group is available for reuse by LGWR.

NOARCHIVELOG mode protects a database from instance failure but not from media failure. Only the most recent changes made to the database, which are stored in the online redo log groups, are available for instance recovery. If a media failure occurs while the database is in NOARCHIVELOG mode, you can only restore the database to the point of the most recent full database backup. You cannot recover transactions subsequent to that backup.

Running a Database in ARCHIVELOG Mode:

When you run a database in ARCHIVELOG mode, you enable the archiving of the redo log. The database control file indicates that a group of filled redo log files cannot be reused by LGWR until the group is archived. A filled group becomes available for archiving immediately after a redo log switch occurs.

The archiving of filled groups has these advantages:

·         A database backup, together with online and archived redo log files, guarantees that you can recover all committed transactions in the event of an operating system or disk failure.

·         If you keep an archived log, you can use a backup taken while the database is open and in normal system use.

·         You can keep a standby database current with its original database by continuously applying the original archived redo logs to the standby.

If all databases in a distributed database operate in ARCHIVELOG mode, you can perform coordinated distributed database recovery. However, if any database in a distributed database is in NOARCHIVELOG mode, recovery of a global distributed database (to make all databases consistent) is limited by the last full backup of any database operating in NOARCHIVELOG mode.

Specifying Archive Destinations

You can choose whether to archive redo logs to a single destination or multiplex them. If you want to archive only to a single destination, you specify that destination in the LOG_ARCHIVE_DEST initialization parameter. If you want to multiplex the archived logs, you can choose whether to archive to up to ten locations (using the LOG_ARCHIVE_DEST_n parameters) or to archive only to a primary and secondary destination (using LOG_ARCHIVE_DEST andLOG_ARCHIVE_DUPLEX_DEST). 

Method	Initialization Parameter	Host	Example
1	LOG_ARCHIVE_DEST_n where: n is an integer from 1 to 10	Local or remote	LOG_ARCHIVE_DEST_1 = 'LOCATION=/disk1/arc' LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1'
2	LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST	Local only	LOG_ARCHIVE_DEST = '/disk1/arc' LOG_ARCHIVE_DUPLEX_DEST = '/disk2/arc'

Process Of Enable Archived redo log files:

1.Check the Archive log is enable or disable.

SQL> archive log list

Database log mode              No Archive Mode      /**** this is indicates that the archive log mode is not enabled *****/

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     33

Current log sequence           35

2. If you have to enable Archive log, first you need to set the parameters:

SQL> alter system set log_archive_dest_1='location=/u02/arch' scope=spfile;

System altered.

SQL> alter system set log_archive_format='arch_%s_%t_%r.arc' scope=spfile;

System altered.

3.After setting those parameters bring down the database:

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

4.To enable the archivelog bring the database in to mount state:

SQL> startup mount

ORACLE instance started.

Total System Global Area  605450240 bytes

Fixed Size                  2228880 bytes

Variable Size             432016752 bytes

Database Buffers          167772160 bytes

Redo Buffers                3432448 bytes

Database mounted.

5.Enable archive log:

SQL> alter database archivelog;   /**** commmand to enable archivelog mode****/

Database altered.

6. check the database is archived or not:

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u02/arch

Oldest online log sequence     33

Next log sequence to archive   35

Current log sequence           35

7.open the database:

SQL> alter database open;

Database altered.

Process Of Disable Archived redo log files:

1.Check the Archive log is enable or disable.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u02/arch

Oldest online log sequence     35

Next log sequence to archive   37

Current log sequence           37

2. If you have to disable Archive log, first you need toreset the parameters:

SQL> alter system set log_archive_dest_1=' ' scope=spfile;

System altered.

3.After setting those parameters bring down the database:

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

4.To disable the archivelog bring the database in to mount state:

SQL> startup mount

ORACLE instance started.

Total System Global Area  605450240 bytes

Fixed Size                  2228880 bytes

Variable Size             432016752 bytes

Database Buffers          167772160 bytes

Redo Buffers                3432448 bytes

Database mounted.

5.Disable archive log:

SQL> alter database noarchivelog;   /**** commmand to disable archivelog mode****/

Database altered.

6. check the database is archived or not:

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Disabled

Archive destination            /u02/arch

Oldest online log sequence     33

Next log sequence to archive   35

Current log sequence           35

7.open the database:

SQL> alter database open;

Database altered.

it won't generate the archive log files in the old locaton as archive log mode is disabled.