Viewing Control File Names and Locations

If your database is in a nomount state, a mounted state, or an open state, you can view the names and locations of the control files, as follows:

SQL> show parameter control_files

You can also view control file location and name information by querying the V$CONTROLFILEview. This query works while your database is mounted or open:

SQL> select name from v$controlfile;

If, for some reason, you can’t start your database at all, and you need to know the names and locations of the control files, you can inspect the contents of the initialization (parameter) file to see where they’re located. If you’re using an spfile, even though it’s a binary file, you can still open it with a text editor. The safest approach is to make a copy of the spfile and then inspect its contents with an OS editor:

$ cp $ORACLE_HOME/dbs/spfileo12c.ora $ORACLE_HOME/dbs/spfileo12c.copy
$ vi $ORACLE_HOME/dbs/spfileo12c.copy

You can also use the strings command to search for values in a binary file:

$ strings spfileo12c.ora | grep -i control_files

If you’re using a text-based initialization file, you can view the file directly, with an OS editor, or use the grep command:

$ grep -i control_files $ORACLE_HOME/dbs/inito12c.ora

Adding a Control File

Adding a control file means copying an existing control file and making your database aware of the copy by modifying your CONTROL_FILES parameter. This task must be done while your database is shut down. This procedure only works when you have a good existing control file that can be copied. Adding a control file isn’t the same thing as creating or restoring a control file.

If your database uses only one control file, and that control file becomes damaged, you need to either restore a control file from a backup (if available) and perform a recovery or re-create the control file. If you’re using two or more control files, and one becomes damaged, you can use the remaining good control file(s) to quickly get your database into an operating state.

If a database is using only one control file, the basic procedure for adding a control file is as follows:

1. Alter the initialization file CONTROL_FILES parameter to include the new location and name of the control file.
2. Shut down your database.
3. Use an OS command to copy an existing control file to the new location and name.
4. Restart your database.

Depending on whether you use an spfile or an init.ora file, the previous steps vary slightly. The next two sections detail these different scenarios.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /ora01/app/oracle/product/12.1
                                                 .0.1/db_1/dbs/spfileo12c.ora

SQL> show parameter control_files

NAME                       TYPE        VALUE
-------------------------- ----------- ------------------------------
control_files              string      /u01/dbfile/o12c/control01.ctl
                                               ,/u01/dbfile/o12c/control02.ctl

SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

SQL> show parameter control_files

NAME                       TYPE        VALUE
-------------------------- ----------- ------------------------------
control_files              string      /u01/dbfile/o12c/control01.ctl
                                       ,/u01/dbfile/o12c/control02.ctl

Moving a Control File

You may occasionally need to move a control file from one location to another. For example, if new storage is added to the database server, you may want to move an existing control file to the newly available location.

The procedure for moving a control file is very similar to adding a control file. The only difference is that you rename the control file instead of copying it. This example shows how to move a control file when you’re using an spfile:

1. Determine the CONTROL_FILES parameter’s current value:

   SQL> show parameter control_files

   The output shows that this database is using only one control file:

   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   control_files                        string      /u01/dbfile/o12c/control01.ctl

2. Alter your CONTROL_FILES parameter to reflect that you’re moving a control file. In this example the control file is currently in this location:

   /u01/dbfile/o12c/control01.ctl

   You’re moving the control file to this location:

   /u02/dbfile/o12c/control01.ctl

   Alter the spfile to reflect the new location for the control file. You have to specify SCOPE=SPFILE because the CONTROL_FILES parameter can’t be modified in memory:

   SQL> alter system set
        control_files='/u02/dbfile/o12c/control01.ctl' scope=spfile;

3. Shut down your database:

   SQL> shutdown immediate;

4. At the OS prompt, move the control file to the new location. This example uses the OS mv command:

   $ mv /u01/dbfile/o12c/control01.ctl /u02/dbfile/o12c/control01.ctl

5. Start up your database:

   SQL> startup;

You can verify that the new control file is being used by displaying the CONTROL_FILES parameter:

SQL> show parameter control_files

Here is the output for this example:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u02/dbfile/o12c/control01.ctl

Removing a Control File

You may run into a situation in which you experience a media failure with a storage device that contains one of your multiplexed control files:

ORA-00205: error in identifying control file, check alert log for more info

In this scenario, you still have at least one good control file. To remove a control file, follow these steps:

1. Identify which control file has experienced media failure by inspecting the alert.log for information:

   ORA-00210: cannot open the specified control file
   ORA-00202: control file: '/u01/dbfile/o12c/control02.ctl'

2. Remove the unavailable control file name from the CONTROL_FILES parameter. If you’re using an init.ora file, modify the file directly with an OS editor (such as vi). If you’re using an spfile, modify the CONTROL_FILES parameter with the ALTER SYSTEM statement. In this spfile example the control02.ctl control file is removed from the CONTROL_FILES parameter:

   SQL> alter system set control_files='/u01/dbfile/o12c/control01.ctl'
        scope=spfile;

3. This database now has only one control file associated with it. You should never run a production database with just one control file. See the section “Adding a Control File,” earlier in this chapter, for details on how to add more control files to your database.
4. Stop and start your database:

   SQL> shutdown immediate;
   SQL> startup;

Displaying Online Redo Log Information

Use the V$LOG and V$LOGFILE views to display information about online redo log groups and corresponding members:

COL group#     FORM 99999
COL thread#    FORM 99999
COL grp_status FORM a10
COL member     FORM a30
COL mem_status FORM a10
COL mbytes     FORM 999999
--
SELECT
 a.group#
,a.thread#
,a.status grp_status
,b.member member
,b.status mem_status
,a.bytes/1024/1024 mbytes
FROM v$log     a,
     v$logfile b
WHERE a.group# = b.group#
ORDER BY a.group#, b.member;

Here is some sample output:

GROUP# THREAD# GRP_STATUS MEMBER                         MEM_STATUS  MBYTES
------ ------- ---------- ------------------------------ ---------- -------
     1       1 INACTIVE   /u01/oraredo/o12c/redo01a.rdo                  50
     1       1 INACTIVE   /u02/oraredo/o12c/redo01b.rdo                  50
     2       1 CURRENT    /u01/oraredo/o12c/redo02a.rdo                  50
     2       1 CURRENT    /u02/oraredo/o12c/redo02b.rdo                  50

When you’re diagnosing online redo log issues, the V$LOG and V$LOGFILE views are particularly helpful. You can query these views while the database is mounted or open. Table 5-1 briefly describes each view.

The STATUS column of the V$LOG view is especially useful when you’re working with online redo log groups. Table 5-2 describes each status and its meaning for the V$LOG view.

The STATUS column of the V$LOGFILE view also contains useful information. This view offers information about each physical online redo log file member of a log group. Table 5-3 provides descriptions of the each status and its meaning for each log file member.

It’s important to differentiate between the STATUS column in V$LOG and the STATUS column in V$LOGFILE. The STATUS column in V$LOG reflects the status of the log group. The STATUS column in V$LOGFILE reports the status of the physical online redo log file member. Refer to these tables when diagnosing issues with your online redo logs.

Determining the Optimal Size of Online Redo Log Groups

Try to size the online redo logs so that they switch anywhere from two to six times per hour. The V$LOG_HISTORY contains a history of how frequently the online redo logs have switched. Execute this query to view the number of log switches per hour:

select count(*)
,to_char(first_time,'YYYY:MM:DD:HH24')
from v$log_history
group by to_char(first_time,'YYYY:MM:DD:HH24')
order by 2;

Here is a snippet of the output:

  COUNT(*) TO_CHAR(FIRST
---------- -------------
         1 2012:10:23:23
         3 2012:10:24:03
        28 2012:10:24:04
        23 2012:10:24:05
        68 2012:10:24:06
        84 2012:10:24:07
        15 2012:10:24:08

From the previous output, you can see that a great deal of log switch activity occurred from approximately 4:00 am to 7:00 am . This could be due to a nightly batch job or users’ in different time zones updating data. For this database the size of the online redo logs should be increased. You should try to size the online redo logs to accommodate peak transaction loads on the database.

The V$LOG_HISTORY derives its data from the control file. Each time there is a log switch, an entry is recorded in this view that details information such as the time of the switch and the system change number (SCN). As stated, a general rule of thumb is that you should size your online redo log files so that they switch approximately two to six times per hour. You don’t want them switching too often because there is overhead with the log switch. Oracle initiates a checkpoint as part of a log switch. During a checkpoint the database writer background process writes modified (also called dirty) blocks to disk, which is resource intensive.

Then again, you don’t want online redo log files never to switch, because the current online redo log contains transactions that you may need in the event of a recovery. If a disaster causes a media failure in your current online redo log, you can lose those transactions that haven’t been archived.

You can also query the OPTIMAL_LOGFILE_SIZE column from the V$INSTANCE_RECOVERY view to determine if your online redo log files have been sized correctly:

SQL> select optimal_logfile_size from v$instance_recovery;

Here is some sample output:

OPTIMAL_LOGFILE_SIZE
--------------------
349

This column reports the redo log file size (in megabytes) that is considered optimal, based on the initialization parameter setting of FAST_START_MTTR_TARGET. Oracle recommends that you configure all online redo logs to be at least the value of OPTIMAL_LOGFILE_SIZE. However, when sizing your online redo logs, you must take into consideration information about your environment (such as the frequency of the switches).

Determining the Optimal Number of Redo Log Groups

Oracle requires at least two redo log groups in order to function. But, having just two groups sometimes isn’t enough. To understand why this is so, remember that every time a log switch occurs, it initiates a checkpoint. As part of a checkpoint the database writer writes all modified (dirty) blocks from the SGA to the data files on disk. Also recall that the online redo logs are written to in a round-robin fashion and that eventually the information in a given log is overwritten. Before the log writer can begin to overwrite information in an online redo log, all modified blocks in the SGA associated with the redo log must first be written to a data file. If not all modified blocks have been written to the data files, you see this message in the alert.log file:

Thread 1 cannot allocate new log, sequence <sequence number>
Checkpoint not complete

Another way to explain this issue is that Oracle needs to store in the online redo logs any information that would be required to perform a crash recovery. To help you visualize this, see Figure 5-2.

At time 1, Block A is read from Data File AA into the buffer cache and modified. At time 2 the redo-change vector information (how the block changed) is written to the log buffer. At time 3 the log-writer process writes the Block A change-vector information to online redo log 1. At time 4 a log switch occurs, and online redo log 2 becomes the current online redo log.

Now, suppose that online redo log 2 fills up quickly and another log switch occurs, at which point the log writer attempts to write to online redo log 1. The log writer isn’t allowed to overwrite information in online redo log 1 until the database writer writes Block A to Data File AA. Until Block A is written to Data File AA, Oracle needs information in the online redo logs to recover this block in the event of a power failure or shutdown abort. Before Oracle overwrites information in the online redo logs, it ensures that blocks protected by redo have been written to disk. If these modified blocks haven’t been written to disk, Oracle temporarily suspends processing until this occurs. There are a few ways to resolve this issue:

• Add more redo log groups.
• Lower the value of FAST_START_MTTR_TARGET. Doing so causes the database writer process to write older modified blocks to disk in a shorter time frame.
• Tune the database-writer process (modify DB_WRITER_PROCESSES).

If you notice that the Checkpoint not complete message is occurring often (say, several times a day), I recommend that you add one or more log groups to resolve the issue. Adding an extra redo log gives the database writer more time to write modified blocks in the database buffer cache to the data files before the associated redo with a block is overwritten. There is little downside to adding more redo log groups. The main concern is that you could bump up against the MAXLOGFILES value that was used when you created the database. If you need to add more groups and have exceeded the value of MAXLOGFILES, then you must re-create your control file and specify a high value for this parameter.

If adding more redo log groups doesn’t resolve the issue, you should carefully consider lowering the value of FAST_START_MTTR_TARGET. When you lower this value, you can potentially see more I/O because the database writer process is more actively writing modified blocks to data files. Ideally, it would be nice to verify the impact of modifying FAST_START_MTTR_TARGET in a test environment before making the change in production. You can modify this parameter while your instance is up; this means you can quickly modify it back to its original setting if there are unforeseen side effects.

Finally, consider increasing the value of the DB_WRITER_PROCESSES parameter. Carefully analyze the impact of modifying this parameter in a test environment before you apply it to production. This value requires that you stop and start your database; therefore, if there are adverse effects, downtime is required to change this value back to the original setting.

Adding Online Redo Log Groups

If you determine that you need to add an online redo log group, use the ADD LOGFILE GROUP statement. In this example the database already contains two online redo log groups that are sized at 50M each. An additional log group is added that has two members and is sized at 50MB:

alter database add logfile group 3
('/u01/oraredo/o12c/redo03a.rdo',
 '/u02/oraredo/o12c/redo03b.rdo') SIZE 50M;

In this scenario I highly recommend that the log group you add be the same size and have the same number of members as the existing online redo logs. If the newly added group doesn’t have the same physical characteristics as the existing groups, it’s harder to accurately determine performance issues.

For example, if you have two log groups sized at 50MB, and you add a new log group sized at 500MB, this is very likely to produce the Checkpoint not complete issue described in the previous section. This is because flushing all modified blocks from the SGA that are protected by the redo in a 500MB log file can potentially take much longer than flushing modified blocks from the SGA that are protected by a 50MB log file.

Resizing and Dropping Online Redo Log Groups

You may need to change the size of your online redo logs (see the section “Determining the Optimal Size of Online Redo Log Groups,” earlier in this chapter). You can’t directly modify the size of an existing online redo log (as you can a data file). To resize an online redo log, you have to first add online redo log groups that are the size you want, and then drop the online redo logs that are the old size.

Say you want to resize the online redo logs to be 200MB each. First, you add new groups that are 200MB, using the ADD LOGFILE GROUP statement. The following example adds log group 4, with two members sized at 200MB:

alter database add logfile group 4
('/u01/oraredo/o12c/redo04a.rdo',
 '/u02/oraredo/o12c/redo04b.rdo') SIZE 200M;

After you’ve added the log files with the new size, you can drop the old online redo logs. A log group must have an INACTIVE status before you can drop it. You can check the status of the log group, as shown here:

SQL> select group#, status, archived, thread#, sequence# from v$log;

You can drop an inactive log group with the ALTER DATABASE DROP LOGFILE GROUP statement:

SQL> alter database drop logfile group <group #>;

If you attempt to drop the current online log group, Oracle returns an ORA-01623 error, stating that you can’t drop the current group. Use the ALTER SYSTEM SWITCH LOGFILE statement to switch the logs and make the next group the current group:

SQL> alter system switch logfile;

After a log switch the log group that was previously the current group retains an active status as long as it contains redo that Oracle requires to perform crash recovery. If you attempt to drop a log group with an active status, Oracle throws an ORA-01624 error, indicating that the log group is required for crash recovery. Issue an ALTER SYSTEM CHECKPOINT command to make the log group inactive:

SQL> alter system checkpoint;

Additionally, you can’t drop an online redo log group if doing so leaves your database with only one log group. If you attempt to do this, Oracle throws an ORA-01567 error and informs you that dropping the log group isn’t permitted because it would leave you with fewer than two log groups for your database (as mentioned earlier, Oracle requires at least two redo log groups in order to function).

Dropping an online redo log group doesn’t remove the log files from the OS. You have to use an OS command to do this (such as the rm Linux/Unix command). Before you remove a file from the OS, ensure that it isn’t in use and that you don’t remove a live online redo log file. For every database on the server, issue this query to view which online redo log files are in use:

SQL> select member from v$logfile;

Before you physically remove a log file, first switch the online redo logs enough times that all online redo log groups have recently been switched; doing so causes the OS to write to the file and thus give it a new timestamp. For example, if you have three groups, make sure you perform at least three log switches:

SQL> alter system switch logfile;
SQL> /
SQL> /

Now, verify at the OS prompt that the log file you intend to remove doesn’t have a new timestamp. First, go to the directory containing the online redo log files:

$ cd  /u01/oraredo/o12c

Then, list the files to view the latest modification date:

$ ls -altr

When you’re absolutely sure the file isn’t in use, you can remove it. The danger in removing a file is that if it happens to be an in-use online redo log, and the only member of a group, you can cause serious damage to your database. Ensure that you have a good backup of your database and that the file you’re removing isn’t used by any databases on the server.

Adding Online Redo Log Files to a Group

You may occasionally need to add a log file to an existing group. For example, if you have an online redo log group that contains only one member, you should consider adding a log file (to provide a higher level of protection against a single–log file member failure). Use the ALTER DATABASE ADD LOGFILE MEMBER statement to add a member file to an existing online redo log group. You need to specify the new member file location, name, and group to which you want to add the file:

SQL> alter database add logfile member '/u02/oraredo/o12c/redo01b.rdo'
     to group 1;

Make certain you follow standards with regard to the location and names of any newly added redo log files.

Removing Online Redo Log Files from a Group

Occasionally, you may need to remove an online redo log file from a group. For example, your database may have experienced a failure with one member of a multiplexed group, and you want to remove the apostate member. First, make sure the log file you want to drop isn’t in the current group:

SELECT a.group#, a.member, b.status, b.archived, SUM(b.bytes)/1024/1024 mbytes
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
GROUP BY a.group#, a.member, b.status, b.archived
ORDER BY 1, 2;

If you attempt to drop a log file that is in the group with the CURRENT status, you receive the following error:

ORA-01623: log 2 is current log for instance o12c (thread 1) - cannot drop

If you’re attempting to drop a member from the current online redo log group, then force a switch, as follows:

SQL> alter system switch logfile;

Use the ALTER DATABASE DROP LOGFILE MEMBER statement to remove a member file from an existing online redo log group. You don’t need to specify the group number because you’re removing a specific file:

SQL> alter database drop logfile member '/u01/oraredo/o12c/redo04a.rdo';

You also can’t drop the last remaining log file of a group. A group must contain at least one log file. If you attempt to drop the last remaining log file of a group, you receive the following error:

ORA-00361: cannot remove last log member .  .  .

Moving or Renaming Redo Log Files

Sometimes, you need to move or rename online redo log files. For example, you may have added some new mount points to the system, and you want to move the online redo logs to the new storage. You can use two methods to accomplish this task:

• Add the new log files in the new location, and drop the old log files.
• Physically rename the files from the OS.

If you can’t afford any downtime, consider adding new log files in the new location and then dropping the old log files. See the section “Adding Online Redo Log Groups,” earlier in this chapter, for details on how to add a log group. See also the section “Resizing and Dropping Online Redo Log Groups,” earlier in this chapter, for details on how to drop a log group.

Alternatively, you can physically move the files from the OS. You can do this with the database open or closed. If your database is open, ensure that the files you move aren’t part of the current online redo log group (because those are actively written to by the log writer background process). It’s dangerous to try to do this task while your database is open because on an active system, the online redo logs may be switching at a rapid rate, which creates the possibility of attempting to move a file while it’s being switched to be the current online redo log. Therefore, I recommend that you only try to do this while your database is closed.

The next example shows how to move the online redo log files with the database shut down. Here are the steps:

1. Shut down your database:

   SQL> shutdown immediate;

2. From the OS prompt, move the files. This example uses the mv command to accomplish this task:

   $ mv /u02/oraredo/o12c/redo02b.rdo /u01/oraredo/o12c/redo02b.rdo

3. Start up your database in mount mode:

   SQL> startup mount;

4. Update the control file with the new file locations and names:

   SQL> alter database rename file '/u02/oraredo/o12c/redo02b.rdo'
        to '/u01/oraredo/o12c/redo02b.rdo';

5. Open your database:

   SQL> alter database open;

You can verify that your online redo logs are in the new locations by querying the V$LOGFILE view. I recommend as well that you switch your online redo logs several times and then verify from the OS that the files have recent timestamps. Also check the alert.log file for any pertinent errors.

Making Architectural Decisions

When you implement archivelog mode, you also need a strategy for managing the archived log files. The archive redo logs consume disk space. If left unattended, these files will eventually use up all the space allocated for them. If this happens, the archiver can’t write a new archive redo log file to disk, and your database will stop processing transactions. At that point, you have a hung database. You then need to intervene manually by creating space for the archiver to resume work. For these reasons, there are several architectural decisions you must carefully consider before you enable archiving:

• Where to place the archive redo logs and whether to use the fast recovery area to store them
• How to name the archive redo logs
• How much space to allocate to the archive redo log location
• How often to back up the archive redo logs
• When it’s okay to permanently remove archive redo logs from disk
• How to remove archive redo logs (e.g., have RMAN remove the logs, based on a retention policy)
• Whether multiple archive redo log locations should be enabled
• When to schedule the small amount of downtime that’s required (if a production database)

As a general rule of thumb, you should have enough space in your primary archive redo location to hold at least a day’s worth of archive redo logs. This lets you back them up on a daily basis and then remove them from disk after they’ve been backed up.

If you decide to use a fast recovery area (FRA) for your archive redo log location, you must ensure that it contains sufficient space to hold the number of archive redo logs generated between backups. Keep in mind that the FRA typically contains other types of files, such as RMAN backup files, flashback logs, and so on. If you use a FRA, be aware that the generation of other types of files can potentially impact the space required by the archive redo log files.

You need a strategy for automating the backup and removal of archive redo log files. For user-managed backups, this can be implemented with a shell script that periodically copies the archive redo logs to a backup location and then removes them from the primary location. As you will see in later chapters, RMAN automates the backup and removal of archive redo log files.

If your business requirements are such that you must have a certain degree of high availability and redundancy, then you should consider writing your archive redo logs to more than one location. Some shops set up jobs to copy the archive redo logs periodically to a different location on disk or even to a different server.

Setting the Archive Redo File Location

Before you set your database mode to archiving, you should specifically instruct Oracle where you want the archive redo logs to be placed. You can set the archive redo log file destination with the following techniques:

• Set the LOG_ARCHIVE_DEST_N database initialization parameter.
• Implement a FRA.

These two approaches are discussed in detail in the following sections.

log_archive_dest_1='location=/u01/oraarch/o12c'
log_archive_format='o12c_%t_%s_%r.arc'

SQL> alter system set log_archive_dest_1='location=/u01/oraarch/o12c' scope=both;
SQL> alter system set log_archive_format='o12c_%t_%s_%r.arc' scope=spfile;

SQL> alter system set log_archive_format='%r_%y_%dk.arc' scope=spfile;

SQL> startup nomount;
ORA-19905: log_archive_format must contain %s, %t and %r

$ cd $ORACLE_HOME/dbs
$ mv spfile<SID>.ora spfile<SID>.old.ora

$ vi spfile<SID>.old.ora

$ vi inito12c.ora

SQL> show parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------
log_archive_dest                     string
log_archive_dest_1                   string      location=/u01/oraarch/o12c
log_archive_dest_10                  string

select
 dest_name
,destination
,status
,binding
from v$archive_dest;

DEST_NAME            DESTINATION                    STATUS    BINDING
-------------------- ------------------------------ --------- ---------
LOG_ARCHIVE_DEST_1   /u01/oraarch/o12c              VALID     OPTIONAL
LOG_ARCHIVE_DEST_2                                  INACTIVE  OPTIONAL

SQL> alter system set db_recovery_file_dest_size=200g scope=both;

SQL> alter system set db_recovery_file_dest='/u01/fra' scope=both;

SQL> archive log list;

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST

SQL> show parameter db_recovery_file_dest

<SID>/archivelog/<YYYY_MM_DD>

SQL> alter system set log_archive_dest_1='location=/u01/oraarch/o12c';
SQL> alter system set log_archive_dest_2='location=USE_DB_RECOVERY_FILE_DEST';

Thinking “Un-Oraclethodox” FRA Thoughts

Oracle recommends that you use a FRA for archive redo logs and RMAN backups. However, I usually don’t implement a FRA in production environments—not for the archive redo logs, not for the RMAN backup files, not for any types of files. Why is that?

When you enable a FRA, if you don’t set the initialization parameter LOG_ARCHIVE_DEST_N, then, by default, the archive redo logs are written to the FRA. It’s the same with RMAN backups: if you don’t specifically configure an RMAN channel disk location, then, by default, the RMAN backup files are written to the FRA.

When you use a FRA (as described earlier), the disk space consumed by the archive redo logs and RMAN backups must fit in the disk space assigned to the FRA. What happens if you have an unexpected spike in the amount of redo generated for a database or if an unforeseen issue arises with the RMAN backups that results in the consuming of unanticipated amounts of disk space?

With regard to the RMAN backups, if the FRA fills up, the RMAN backups abort. An RMAN backup failure isn’t catastrophic, because usually you can quickly resolve space issues and manually run another backup. In most situations this doesn’t compromise your database availability; in the event of a backup failure, you still have a previous RMAN backup that you can use to restore and recover your database.

However, in the event that the archive redo log destination fills up, and the archiver can’t write to the file system, your database will hang. In many 24–7 mission-critical environments, this type of downtime is unacceptable and will jeopardize your ability to keep your job.

I find it easier to control the space allocated to the archive redo logs by using a dedicated mount point for these critical files. If possible, don’t share the disk space allocated to the archive redo logs with the RMAN backups. A problem with the RMAN backups can cause disk space issues for the archive redo logs, and you want the space consumed by the archive redo logs to be as stable and predictable as possible.

For production environments, set the LOG_ARCHIVE_DEST_1 parameter to be a specific location that is separate from any other types of database files. This isn’t always possible: you may have a server for which you have no choice but to share mount points for the various file types. But, whenever possible, try to isolate your archive redo logs from other types of database files. The archive redo log files are the mechanism for recovering your database.

You should be able to estimate how much space is consumed and allow for some wiggle room. Yet, the reality is that sometimes very unpredictable events happen, such as an application process’s erroneously getting stuck in a loop and generating enormous amounts of redo. This can quickly consume more disk space than anticipated. It shouldn’t happen, but it does, and when you’re the DBA who’s called at 2:30 am , you design defensively.

I’m not saying, “Don’t use a FRA.” Rather, you should think carefully about any database feature that you enable and what impact it may have on database availability.

Enabling Archivelog Mode

After you’ve set the location for your archive redo log files, you can enable archiving. To enable archiving, you need to connect to the database as SYS (or a user with the SYSDBA privilege) and do the following:

$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

You can confirm archivelog mode with this query:

SQL> archive log list;

You can also confirm it as follows:

SQL> select log_mode from v$database;
  
LOG_MODE
------------
ARCHIVELOG

Disabling Archivelog Mode

Usually, you don’t disable archivelog mode for a production database. However, you may be doing a big data load and want to reduce any overhead associated with the archiving process, and so you want to turn off archivelog mode before the load begins and then reenable it after the load. If you do this, be sure you make a backup as soon as possible after  reenabling archiving.

To disable archiving, do the following as SYS (or a user with the SYSDBA privilege):

$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database noarchivelog;
SQL> alter database open;

You can confirm archivelog mode with  this query:

SQL> archive log list;

You can also confirm the log mode, as follows:

SQL> select log_mode from v$database;
  
LOG_MODE
------------
NOARCHIVELOG

Reacting to a Lack of Disk Space in Your Archive Log Destination

The archiver background process writes archive redo logs to a location that you specify. If, for any reason, the archiver process can’t write to the archive location, your database hangs. Any users attempting to connect receive this error:

ORA-00257: archiver error. Connect internal only, until freed.

As a production-support DBA, you never want to let your database get into that state. Sometimes, unpredictable events happen, and you have to deal with unforeseen issues.

In this situation your database is as good as down and completely unavailable. To fix the issue, you have to act quickly:

• Move files to a different location.
• Compress old files in the archive redo log location.
• Permanently remove old files.
• Switch the archive redo log destination to a different location (this can be changed dynamically, while the database is up and running).

Moving files is usually the quickest and safest way to resolve the archiver error. You can use an OS utility such as mv to move old archive redo logs to a different location. If they’re needed for a subsequent restore and recovery, you can let the recovery process know about the new location. Be careful not to move an archive redo log that is currently being written to. If an archived redo log file appears in V$ARCHIVED_LOG, that means it has been completely archived.

You can use an OS utility such as gzip to compress archive redo log files in the current archive destination. If you do this, you have to remember to uncompress any files that may be later needed for a restore and recovery. Be careful not to compress an archive redo log that is currently being written to.

Another option is to use an OS utility such as rm to remove archive redo logs from disk permanently. This approach is dangerous because you may need those archive redo logs for a subsequent recovery. If you do remove archive redo log files, and you don’t have a backup of them, you should make a full backup of your database as soon as possible. Again, this approach is risky and should only be done as a last resort; if you delete archive redo logs that haven’t been backed up, then you chance not being able to perform a complete recovery.

If another location on your server has plenty of space, you can consider changing the location to which the archive redo logs are being written. You can perform this operation while the database is up and running; for example,

SQL> alter system set log_archive_dest_1='location=/u02/oraarch/o12c';

After you’ve resolved the issue with the primary location, you can switch back the original location.

When the archive redo log file destination is full, you have to scramble to resolve it. This is why a good deal of thought should precede enabling archiving for 24–7 production databases.

For most databases, writing the archive redo logs to one location is sufficient. However, if you have any type of disaster recovery or high-availability requirement, then you should write to multiple locations. Sometimes, DBAs set up a job to back up the archive redo logs every hour and copy them to an alternate location or even to an alternate server.

Backing Up Archive Redo Log Files

Depending on your business requirements, you may need a strategy for backing up archive redo log files. Minimally, you should back up any archive redo logs generated during a backup of a database in archivelog mode. Additional strategies may include

• periodically copying archive redo logs to an alternate location and then removing them from the primary destination
• copying the archive redo logs to tape and then deleting them from disk
• using two archive redo log locations
• using Data Guard for a robust disaster recovery solution

Keep in mind that you need all archive redo logs generated since the begin time of the last good backup to ensure that you can completely recover your database. Only after you’re sure you have a good backup of your database should you consider removing archive redo logs that were generated prior to the backup.

If you’re using RMAN as a backup and recovery strategy, then you should use RMAN to backup the archive redo logs. Additionally, you should specify an RMAN retention policy for these files and have RMAN remove the archive redo logs only after the retention policy requirements are met (e.g., back up files at least once before removing from disk) (see Chapter 18 for details on using RMAN).