Oracle Corporation
  ORACLE.COM     PARTNERS     BUY     DOWNLOAD     SUPPORT     CONTACT US   
SEARCH


Legal | Privacy
Managing Control Files

Managing Control Files

Purpose

This module describes how to remove and re-create control files.

Topics

This module discusses the following:

Removing a Control File
  Removing a Control File When Using an Initialization Parameter File
  Removing a Control File When Using a Server Parameter File

Re-creating a Control File
  Creating a New Control File From an Existing Control File
  Creating a New Control File Without an Existing Control File

Viewing Screenshots

Move your mouse over this icon to show all screenshots. You can also move your mouse over each individual icon to see only the screenshot associated with it.

Removing a Control File

Back to List of Topics

You can remove a control file so that the Oracle server no longer uses it. The approach you follow depends on whether you are using a text initialization parameter file (also referred to as an init.ora file) or a server parameter file (also referred to as an SPFILE).

Removing a Control File When Using an Initialization Parameter File

Back to List of Topics

Complete the following steps to remove a control file from your database when using a text initialization parameter file:

1.

Invoke SQL*Plus. Connect as a user with SYSDBA privileges.

 
2.

Query V$CONTROLFILE or use the SHOW PARAMETER command to obtain a list of the control files currently in use:

SELECT name FROM v$controlfile;

SHOW PARAMETER CONTROL_FILES

Move your mouse over this icon to see the image

 
3.

Edit the initialization parameter file and remove the name of the control file that you no longer want to use from the CONTROL_FILES initialization parameter.
Note: This does not remove the file from the operating system.

 
4.

Shut down the instance using the NORMAL, IMMEDIATE, or TRANSACTIONAL option. 

SHUTDOWN IMMEDIATE

Move your mouse over this icon to see the image

 
5.

Start the instance, mount and open the database:

STARTUP PFILE=<pfile name>

Move your mouse over this icon to see the image

 
6.

Query V$CONTROLFILE or use the SHOW PARAMETER command to verify your changes:

SELECT name FROM v$controlfile;

SHOW PARAMETER CONTROL_FILES

Move your mouse over this icon to see the image

 
7.

Optional: Delete the control file you no longer want to use from the operating system.

 

Removing a Control File When Using a Server Parameter File

Back to List of Topics

Complete the following steps to remove a control file from your database when using a server parameter file:

1.

Invoke SQL*Plus. Connect as a user with SYSDBA privileges.

 
2.

Query V$CONTROLFILE or use the SHOW PARAMETER command to obtain a list of the control files currently in use:

SELECT name FROM v$controlfile;

SHOW PARAMETER CONTROL_FILES

Move your mouse over this icon to see the image

 
3.

Use the ALTER SYSTEM command to change the value of the CONTROL_FILES parameter in your server parameter file. You must issue the command supplying only the names of the control files you want to continue to use. Do not list the control file you no longer want to use. You must include the SCOPE=SPFILE option because CONTROL_FILES is a static parameter.

ALTER SYSTEM SET CONTROL_FILES=<control files> SCOPE=SPFILE;

Move your mouse over this icon to see the image

 
4.

Shut down the instance using the NORMAL, IMMEDIATE, or TRANSACTIONAL option. 

SHUTDOWN IMMEDIATE

Move your mouse over this icon to see the image

 
5.

Start the instance, mount and open the database:

STARTUP

Move your mouse over this icon to see the image

 
6.

Query V$CONTROLFILE or use the SHOW PARAMETER command to verify your changes:

SELECT name FROM v$controlfile;

SHOW PARAMETER CONTROL_FILES

Move your mouse over this icon to see the image

 
7.

Optional: Delete the control file you no longer want to use from the operating system.

 

Re-creating Your Control File

Back to List of Topics

You can use the CREATE CONTROLFILE statement to re-create a control file in one of the following cases:

All copies of your existing control files have been lost through media failure
You want to change the name of the database
You want to change the maximum number of redo log file groups, redo log file members, archived redo log files, data files, or instances that can concurrently have the database mounted and open

In this module, you will learn how to generate a SQL script containing the CREATE CONTROLFILE command from an existing control file. In addition, you will learn how to create a new control file if you have lost all of your control files and do not have a SQL script that you previously created.

Creating a New Control File From an Existing Control File

Back to List of Topics

You can use a command to generate a SQL script to re-create your control file. Follow the steps outlined below to create this script and re-create your control file:

1.

Invoke SQL*Plus and connect as a user with SYSDBA privileges.

 
2.

Issue the following command to create a trace file containing the commands needed to re-create the control file:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Move your mouse over this icon to see the image

 
3.

The trace file is created in the destination specified by the USER_DUMP_DEST initialization parameter. The file will be named ora_nnnnn.trc.

You can determine the location for the trace file by querying V$PARAMETER or using the SHOW PARAMETER command as shown:

SELECT value
FROM v$parameter
WHERE name = 'user_dump_dest';

SHOW PARAMETER USER_DUMP_DEST

Move your mouse over this icon to see the image

Move your mouse over this icon to see the image

 
4.

The trace file contains the commands to re-create your control file. Copy the trace file to a new file and remove the header information using an operating system editor. Use option #1 when all of your redo log files are available. Remove all of the information related to option #2. Your new file should begin with the STARTUP NOMOUNT command.

Move your mouse over this icon to see the image

 
5.

Shut down the instance with the NORMAL, IMMEDIATE, or TRANSACTIONAL options.

SHUTDOWN IMMEDIATE

Move your mouse over this icon to see the image

 
6.

Make a whole consistent (full or complete) backup of your database using Recovery Manager (RMAN) or operating system commands.

 
7.

Rename or move your existing control files to a new location using operating system commands.

 
8.

Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to SHARED or re-create your password file before re-creating the control file. If the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to EXCLUSIVE, the Oracle server returns an error when you attempt to re-create the control file.

 
9.

Connect to your database as a user with SYSDBA privileges.

 
10.

Execute the script you created in step 4. The database will be open when the script completes execution.

@<create control file script>

Move your mouse over this icon to see the image

 
11.

Make a whole consistent (full or complete) backup of your database using Recovery Manager (RMAN) or operating system commands.

 

Creating a New Control File Without an Existing Control File

Back to List of Topics

If you do not have a SQL script file to re-create the control file, you can manually create the script file containing the CREATE CONTROLFILE command. The CREATE CONTROLFILE syntax follows:

Move your mouse over this icon to see the image

Complete the following steps to create your control file:

1.

Use an editor to create a script containing the CREATE CONTROLFILE command.

 
2.

Make a whole (full or complete) backup of your database using RMAN or operating system commands.

 
3.

Invoke SQL*Plus and connect to your database as a user with SYSDBA privileges. Start the instance in NOMOUNT mode: 

STARTUP NOMOUNT

Move your mouse over this icon to see the image

 
4.

Execute the script file you created containing the CREATE CONTROLFILE command:

@<create control file script>

Move your mouse over this icon to see the image

 
5.

Perform media recovery using the following command:

RECOVER DATABASE;

Move your mouse over this icon to see the image

 
6.

Open the database:

ALTER DATABASE OPEN;

Move your mouse over this icon to see the image

 
7.

Make a whole (full or complete) backup of your database using RMAN or operating system commands.

 

Move your mouse over this icon to hide all screenshots
E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy