Pro Oracle Database 11g Administration (22 page)

90

CHAPTER 5 ■ MANAGING CONTROL FILES AND ONLINE REDO LOGS

REMOVABLE RECOVERY FILES

DATAFILE

You can view database information stored in the control file via the V$DATABASE view: SQL> select name, open_mode, created, current_scn from v$database; Here is the output for this example:

NAME OPEN_MODE CREATED CURRENT_SCN

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

ORC11G READ WRITE 01-JAN-10 5077636

Every Oracle database must have at least one control file. When you start your database in nomount mode, the instance is aware of the location of the control files from the CONTROL_FILES parameter:

-- locations of control files are known to the instance

SQL> startup nomount;

At this point, the control files haven’t been touched by any processes. When you alter your database into mount mode, the control files are read and opened for use:

-- control files opened

SQL> alter database mount;

If any of the control files listed in the CONTROL_FILES initialization parameter aren’t available, then you can’t mount your database.

When you successfully mount your database, the instance is aware of the locations of the datafiles and online redo logs, but hasn’t yet opened them. After you alter your database into open mode, the datafiles and online redo logs are opened:

-- datafiles and online redo logs opened

SQL> alter database open;

The control file is created when the database is created. As you saw in Chapter 2, you should create at least two control files when you create your database (to avoid a single point of failure). If possible, you should have multiple control files stored on separate storage devices controlled by separate controllers.

Oracle writes to all control files specified by the CONTROL_FILES initialization parameter. If Oracle can’t write to one of the control files, an error is thrown:

ORA-00210: cannot open the specified control file

If one of your control files becomes unavailable, shut down your database and resolve the issue before restarting. Fixing the problem may mean resolving a storage-device failure or modifying the CONTROL_FILES initialization parameter to remove the control-file entry for the control file that isn’t available.

92

CHAPTER 5 ■ MANAGING CONTROL FILES AND ONLINE REDO LOGS

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/initO11.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 damaged 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 varies slightly. The next two subsections detail these different scenarios.

Spfile Scenario

If your database is open, you can quickly determine whether you’re using an spfile with the following SQL statement:

SQL> show parameter spfile

Here is some sample output:

NAME TYPE VALUE

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

spfile string /oracle/app/oracle/product/11. 2.0/db_1/dbs/spfileO11R2.ora When you’ve determined that you’re using an spfile, use the following steps to add a control file: 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 /ora01/dbfile/O11R2/control01.ctl 94

CHAPTER 5 ■ MANAGING CONTROL FILES AND ONLINE REDO LOGS

2. Alter your CONTROL_FILES parameter to include the new control file that you want to add, but limit the scope of the operation to the spfile (you can’t modify this parameter in memory). Make sure you also include any control files listed in step 1:

SQL> alter system set control_files='/ora01/dbfile/O11R2/control01.ctl',

'/ora01/dbfile/O11R2/control02.ctl' scope=spfile;

3. Shut down your database:

SQL> shutdown immediate;

4. Copy an existing control file to the new location and name. In this example, a new control file named control02.ctl is created via the OS cp command: $ cp /ora01/dbfile/O11R2/control01.ctl /ora01/dbfile/O11R2/control02.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 /ora01/dbfile/O11R2/control01.ctl,

/ora01/dbfile/O11R2/control02.ctl

Init.ora Scenario

Run the following statement to verify that you’re using an init.ora file. If you’re not using an spfile, the VALUE column is blank:

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string

To add a control file when using a text init.ora file, perform the following steps: 1. Shut down your database:

SQL> shutdown immediate;

2. Edit your init.ora file with an OS utility (such as vi), and add the new controlfile location and name to the CONTROL_FILES parameter. This example opens the init.ora file using vi, and adds control02.ctl to the CONTROL_FILES parameter: $ vi $ORACLE_HOME/dbs/initO11R2.ora

Listed next is the CONTROL_FILES parameter after control02.ctl is added: control_files='/ora01/dbfile/O11R2/control01.ctl',

'/ora01/dbfile/O11R2/control02.ctl'

95

CHAPTER 5 ■ MANAGING CONTROL FILES AND ONLINE REDO LOGS

3. From the OS, copy the existing control file to the location and name of the control file being added:

$ cp /ora01/dbfile/O11R2/control01.ctl /ora01/dbfile/O11R2/control02.ctl 4. Start up your database:

SQL> startup;

You can view the control files in use by displaying the CONTROL_FILES parameter: SQL> show parameter control_files

For this example, here is the output:

NAME TYPE VALUE

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

control_files string /ora01/dbfile/O11R2/control01.ctl,

/ora01/dbfile/O11R2/control02.ctl

■
Note
See Chapter 4 for an example of re-creating a control file from a trace file.

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 /ora01/dbfile/O11R2/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:

/ora01/dbfile/O11R2/control01.ctl

You’re moving the control file to this location:

/ora02/dbfile/O11R2/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

96

CHAPTER 5 ■ MANAGING CONTROL FILES AND ONLINE REDO LOGS

control_files='/ora02/dbfile/O11R2/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 /ora01/dbfile/O11R2/control01.ctl /ora02/dbfile/O11R2/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 /ora02/dbfile/O11R2/control01.ctl Removing a Control File

You may run into a situation where 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-00202: control file: '/ora01/dbfile/O11R2/control02.ctl'

ORA-27037: unable to obtain file status

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='/ora01/dbfile/O11R2/control01.ctl'

scope=spfile;

In this example, 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” to add more control files to your database.

3. Stop and start your database:

SQL> shutdown immediate;

SQL> startup;

97