Using Control Files for Backup and Recovery

In this second part of a three-part series on backing up and recovering an Oracle database, you’ll learn about multiplexing control files and enabling ARCHIVELOG mode. This article is excerpted from chapter 30 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 9781590597705).

Multiplexing Control Files

As you may remember from Chapter 28, the control file maintains the metadata for the physical structure of the entire database. It stores the name of the database, the names and locations of the tablespaces in the database, the locations of the redo log files, information about the last backup of each tablespace in the database, and much more. It may be one of the smallest yet most critical files in the database. If you have two or more multiplexed copies of the control file and you lose one, it is a very straightforward recovery process. However, if you have only one copy and you lose it due to corruption or hardware failure, the recovery procedure becomes very advanced and time consuming.

By default, Oracle Database XE creates only one copy of the control file. To multiplex the control file, you need to follow a few simple steps. First, identify the location of the existing control file using the Home ➤ Administration ➤ About Database page, or use the following query:

select value from v$parameter
where name = ‘control_files';

On Linux you will see something similar to the following:

——————————————–
VALUE
——————————————-
/usr/lib/oracle/xe/oradata/XE/control.dbf
——————————————–

The next step is to alter the SPFILE (see Chapter 28 for a discussion on types of parameter files) to add the location for the second control file. We will use the location /u01/app/oracle/controlfile to store the second copy of the control file. Here is the SQL statement you use to add the second location:

alter system set control_files =
‘/usr/lib/oracle/xe/oradata/XE/ control.dbf’,
‘/u01/app/oracle/controlfile/control2.dbf’
scope=spfile;

Be sure to use SCOPE=SPFILE here, as in the example, since you cannot dynamically change the CONTROL_FILES parameter while the database is open. Next, you must shut down the database as follows:

shutdown immediate

On Linux, you use your favorite GUI or operating system command line to make a copy of the first control file in the second location:

cp /usr/lib/oracle/xe/oradata/XE/control.dbf
/u01/app/oracle/controlfile/control2.dbf

Finally, restart the database using this command at the SQL> prompt:

startup

Checking the dynamic performance view V$PARAMETER again, you can see that there are now two copies of the control file:

——————————————–
VALUE
——————————————
/usr/lib/oracle/xe/oradata/XE/control.dbf,
/u01/app/oracle/controlfile/control2.dbf
——————————————–

As with the members of a redo log file group, any changes to the control file are made to all copies. As a result, the loss of one control file is as easy as shutting down the database (if it is not down already), copying the remaining copy of the control file to the second location, and restarting the database.

{mospagebreak title=Enabling ARCHIVELOG Mode}

A database in ARCHIVELOG mode automatically backs up a filled online redo log file after the switch to the next online redo log file. Although this requires more disk space, there are two distinct advantages to using ARCHIVELOG mode:

  1. After media failure, you can recover all committed transactions up to the point in time of the media failure if you have backups of all archived and online redo log files since the last backup, the control file from the most recent backup, and all datafiles from the last backup.
  2. You can back up the database while it is online. If you do not use ARCHIVELOG mode, you must shut down the database to perform a database backup. This is an important consideration when you must have your database available to users 24 hours a day, 7 days a week.

By default, an Oracle Database XE installation is in NOARCHIVELOG mode. If your database is used primarily for development and you make occasional full backups of the database, this may be sufficient. However, if you use your database in a production environment, you should use ARCHIVELOG mode to ensure that no user transactions are lost due to a media failure. To enable ARCHIVELOG mode, perform the following steps. First, connect to the database with SYSDBA privileges, and shut down the database:

shutdown immediate

Next, start up the database in MOUNT mode. This mode reads the contents of the control file and starts the instance but does not open the datafiles:

startup mount

——————————————–
ORACLE instance started.

Total System Global Area 146800640 bytes Fixed Size 1257668 bytes Variable Size 88084284 bytes Database Buffers 54525952 bytes Redo Buffers 2932736 bytes Database mounted.

——————————————–

Next, enable ARCHIVELOG mode with this command:

alter database archivelog;

Finally, open the database:

alter database open;

The Oracle Database XE home page’s Usage Monitor section now indicates the new status of the database, as you can see in Figure 40-3.


Figure 40-3. Database status after enabling ARCHIVELOG mode

After you perform one full backup of the database, the archived and online log files will ensure that you will not lose any committed transactions due to media failure. In addition, to save disk space you can purge (or move to tape and then purge) all archived redo log files and previous backups created before the full backup. Only those archived redo log files created since the last full backup are needed to recover the database when a media failure occurs; the combination of a full backup and subsequent archived redo log files will ensure that you will not lose any committed transactions. The previous full backups and subsequent archived redo log files created before the latest full backup will only be useful if you need to restore the database to a point in time before the most recent full backup.

[gp-comments width="770" linklove="off" ]

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort