Managing Oracle 12C Control Files with examples

A control file is a small binary file that stores information such as the database name, names and locations of data files, names and locations of online redo log files, current online redo log sequence number, checkpoint information, and names and locations of RMAN backup files (if using). You can query much of the information stored in the control file from data dictionary views. This example displays the types of information stored in the control file by querying V$CONTROLFILE_RECORD_SECTION:

SQL> select distinct type from v$controlfile_record_section;

TYPE
----------------------------
FILENAME
TABLESPACE
RMAN CONFIGURATION
BACKUP CORRUPTION
PROXY COPY
FLASHBACK LOG

...

You can view database-related information stored in the control file via the V$DATABASE view:

SQL> select name, open_mode, created, current_scn from v$database;



NAME      OPEN_MODE            CREATED   CURRENT_SCN

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

O12C      READ WRITE           27-SEP-14      319781

Every Oracle database 12C 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 initialization parameter in the spfile or init.ora file. When you issue a STARTUP NOMOUNT command, Oracle reads the parameter file and starts the background processes and allocates memory structures:

-- 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 Oracle database 12C 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 data files and online redo logs but hasn’t yet opened them. After you alter your database into open mode, the data files and online redo logs are opened:

-- datafiles and online redo logs opened

SQL> alter database open;

The control file is created when the Oracle database 12C is created. If possible you should have multiple control files stored on separate storage devices controlled by separate controllers.

After the database has been opened, Oracle will frequently write information to the control files, such as when you make any physical modifications (e.g., creating a tablespace, adding/removing/resizing a data file). 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 Oracle database 12c, 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.

SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> alter session set events 'immediate trace name controlf level 9';
SQL> oradebug tracefile_name

/orahome/app/oracle/diag/rdbms/o12c/O12C/trace/O12C_ora_15545.trc

SQL> select value from v$diag_info where name='Diag Trace';

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$CONTROLFILE view. 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 Oracle database 12C 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 $ORACLE_HOME/dbs/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.

Spfile Scenario

If your Oracle 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      /orahome/app/oracle/product/12

                      .1.0.1/db_1/dbs/spfileO12C.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      /u01/dbfile/O12C/control01.ctl

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='/u01/dbfile/O12C/control01.ctl',
   
   '/u01/dbfile/O12C/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 /u01/dbfile/O12C/control01.ctl /u01/dbfile/O12C/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      /u01/dbfile/O12C/control01.ctl

                            ,/u01/dbfile/O12C/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 control file 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/initO12C.ora

   Listed next is the CONTROL_FILES parameter after control02.ctl is added:

   control_files='/u01/dbfile/O12C/control01.ctl', 
   
                 '/u01/dbfile/O12C/control02.ctl'

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

   $ cp /u01/dbfile/O12C/control01.ctl /u01/dbfile/O12C/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      /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
   
   
   
   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;

   This Oracle database 12C 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 article, for details on how to add more control files to your database.

3. Stop and start your database:

   SQL> shutdown immediate;
   
   SQL> startup;

Вас заинтересует / Intresting for you:

RMAN: Using Incrementally Upda... 1818 views Игорь Воронов Wed, 04 Mar 2020, 16:44:00

Administrator- vs. Policy-Mana... 5760 views Боба Wed, 01 Jul 2020, 15:58:18

Oracle ASM - Automatic Storage... 2317 views Боба Wed, 08 Jul 2020, 05:21:54

Data Pump Architecture and Get... 1018 views dbstalker Fri, 04 Feb 2022, 18:17:13