Shulou(Shulou.com)06/01 Report--

The link to the original text is: http://docs.oracle.com/cd/E11882_01/server.112/e25494/control.htm#ADMIN006

1. Control file management

Chapter content:

What is a control file

L Control File Guide

L create a control file

Troubleshooting after creating a control file

L backup control files

L restore control files using the current copy

L delete control file

L Control File data Dictionary View

1.1. What is a control file?

Each Oracle database has a control file, which is a small binary file that records the physical structure of the database. The control file contains the following:

L database name

Name and location of associated data file and redo log file

L timestamp of database creation

L current log serial number

L checkpoint information

When the database is open, the Oracle database server must be writable to the control file. Without the control file, the database will not be mounted and recovery will be difficult.

Control files for the Oracle database are created at the same time as the database. By default, at least one copy of the control file is created during database creation. On some operating systems, the default is to create multiple copies. You should create two or more copies of control files during database creation. If you lose the control file or want to change specific information in the control file, you can also create the control file later.

1.2. Guide to control documents

This section describes guidelines for managing database control files and contains the following topics:

L specify the file name of the control file

L controls the multiplexing of files on different disks

L backup control files

L manage the size of control files

1.2.1. Specify the file name of the control file

You can use the CONTROL_FILES initialization parameter in the database initialization parameter file to specify the control file name. The database instance recognizes and opens the control files listed in the CONTROL_FILES parameters during database startup, and writes and maintains these control files during database operation.

If you do not specify a file for CONTROL_FILES before creating the data:

If you do not use OMF (Oracle Managed Files), the database creates a control file and uses the default file name, which is related to the operating system.

If OMF (Oracle Managed Files) is used, the initialization parameters of OMF specify the name and location of the control file.

If you use Oracle automatic Storage Management (Oracle ASM), you can configure some Oracle ASM file names in the DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST initialization parameters. Oracle ASM then automatically creates the control file in the appropriate location.

1.2.2. Control the multiplexing of files on different disks

Each Oracle database should have at least two control files, each stored on a different physical disk. If the control file is corrupted due to a disk failure, the associated instance must be closed. When the disk is repaired, you can restore the corrupted control file using a complete copy of the control file on other disks, and then restart the instance. In this case, media recovery is not required.

Under the multiplexing control file, the database does the following:

The database writes all control file names listed in the initialization parameter CONTROL_FILES.

During a database operation, the database reads only the first file listed in the CONTROL_FILES parameter.

If any control file becomes unavailable during the database operation, the database instance becomes unavailable and the operation should be aborted.

Note:

Oracle strongly recommends that your database have at least two control files, and that they are located on separate physical disks.

If the redo log is multiplexed, the control file can be multiplexed by placing a copy of the control file on each disk where the redo log group members are located. By storing control files in these locations, you can minimize the risk of losing all control files and all redo log groups in the event of a disk failure.

1.2.3. Backup control file

Backup control files are very important, including in the initial state of the database and each time changes are made to the physical structure of the database. The structural changes are as follows:

L add, delete or rename data files

L add or remove tablespaces, or change the read / write status of tablespaces

L add or delete redo log files or redo log groups

The method of backing up control files is described in backup Control Files.

1.2.4. Manage the size of the control file

When you create a database using the CREATE DATABASE statement, the control file size is determined by the values of parameters such as MAXDATAFILES,MAXLOGFILES,MAXLOGMEMBERS,MAXLOGHISTORY and MAXINSTANCES. Increasing the values of these parameters increases the size of the control files for the related database.

1.3. Create a control file

This section describes how to create a control file, including the following topics:

L create initialization control file

Add additional copies, rename and move control files

L create a new control file

1.3.1. Create initialization control file when you execute the CREATE DATABSE statement, the database automatically creates the initialization control file. The name of the control file is specified by the CONTROL_FILES parameter in the parameter file. In the CONTROL_FILES parameter, you need to specify the control file name as the full path, specific to the operating system. The following is an example of CONTROL_FILES initialization parameters:

If the specified control file name already exists during the creation of the database, you must use the CONTROLFILE REUSE clause in the CREATE DATABASE statement, otherwise an error will be prompted. At the same time, if the size of the control file specified in the old control file is inconsistent with that specified in the parameter, you cannot use the allow clause.

The size and number of control files vary between Oracle database distributions. The size of the control file can be affected by configuring parameters such as MAXDATAFILES,MAXLOGFILES,MAXLOGMEMBERS,MAXLOGHISTORY and MAXINSTANCES.

You can modify the CONTROL_FILES parameter value later to add the control file, change the control file name, or move the location of the existing control file.

1.3.2. Add additional copies, rename and move control files

You can add a backup of the control file by copying an existing control file to a new path and then modifying the CONTROL_FILES parameter. Similarly, you can rename the control file by changing the control file name or moving the control file to another path. In both cases, to ensure the consistency of the control file, shut down the database before copying the control file.

Add a method to control file backup or rename a control file:

1. Shut down the database

two。 Use the operating system command to copy the existing control file to a new path

3. Edit the CONTROL_FILES parameter value in the initialization parameter file, add a new control file name, or modify the existing control file name.

4. Restart the database

1.3.3. Create a new control file

This section discusses when and how to create a new control file.

1.3.3.1. When to create a new control file

You need to create a new control file in the following situations:

L all control files in the database are permanently corrupted without control file backup

L you want to change the database name

For example, in a distributed environment, you want to change a database name that conflicts with other database names.

Note:

You can use the DBNEWID application to change the database name and DBID (database internal identifier).

When the compatibility level is set to a value earlier than 10.2.0, you must change an area of the database configuration that is related to any of the following parameters in the CREATE DATABASE or CREATE CONTROLFILE command: MAXLOGFILES,MAXLOGMEMBERS,MAXLOGHISTORY and MAXINSTANCES. If the compatibility is 10.2.0 or later, you do not have to create a new control file when making this change; if necessary, the control file automatically expands to accommodate the new configuration information.

For example, suppose you set MAXLOGFILES to 3 when you create a database or recreate a control file. Suppose you now want to add the fourth redo log filegroup to the database using the ALTER DATABASE command. If compatibility is set to 10.2.0 or later, you can do so and the control file will automatically expand to accommodate the new log file information. However, if the compatibility setting is earlier than 10.2.0, your ALTER DATABASE command will generate an error and you must first create a new control file.

1.3.3.2. CREATE CONTROLFILE statement

You can use the CREATE CONTROLFILE statement to create a new control file for the database. The following statement creates a new control file for the prod database (previously a database with a different database name):

If the database is open, close the database as normally as possible. The IMMEDIATE or ABORT clause can only be used as a last resort.

3. Back up all data files and redo log files in the database.

4. Start a new instance, but do not mount or open the database:

7. Edit the CONTROL_FILES initialization parameters for the database, specifying all control files for the database created in step 5 (excluding backup control files). If you want to rename the database, edit the DB_NAME parameter in the instance parameter file to specify a new name.

8. Perform database recovery if necessary. If you have not recovered the database, please skip to step 9.

If you are creating a control file as part of the recovery, restore the database. If you create a new control file using the NORESETLOGS clause (step 5), you can use a complete, closed database for recovery.

If the new control file is created using the RESETLOGS clause, you must specify USING BACKUP CONTROL FILE. If you lose online or archived redo logs or data files, please use the steps to recover these files.

9. Open the database using one of the following methods:

If you do not perform a restore, or perform a complete, closed database recovery in step 8, open the database normally.

If you specified RESETLOGS when creating the control file, use the ALTER DATABASE statement to specify RESETLOGS. The database is now open and available.

1.4. Troubleshooting after creating a control file after executing the CREATE CONTROLFILE statement, you may encounter some errors. This section describes the most common control file errors:

L check for missing or additional files

L handle errors during CREATE CONTROLFILE

1.4.1. Check for missing or extra files after creating a new control file and using it to open the database, check the alarm log to see if the database detects that the data dictionary is inconsistent with the control file, for example, the data file in the data dictionary cannot be found in the control file.

If the data file listed in the data dictionary does not exist in the control file, the database creates a placeholder entry in the control file with the name MISSINGnnnn, where nnnn is the decimal file number. MISSINGnnnn is marked offline in the control file and requires media recovery.

If the actual data file corresponding to the MISSINGnnnn is read-only or offline, you can make the data file accessible by renaming the MISSINGnnnn to the name of the actual data file. If the MISSINGnnnn corresponds to a data file that is not read-only or offline, you cannot use the rename operation to make the data file accessible because the data file is excluded by the results of RESETLOGS and media recovery is required. In this case, you must delete the tablespace that contains the data file.

Conversely, if the data file listed in the control file does not exist in the data dictionary, the database removes the reference to the data file in the new control file. In both cases, the database generates an explanatory message in the alarm log to let you know what the database has done.

1.4.2. Handling errors during CREATE CONTROLFILE if you try to mount and open the database after creating a new control file, the Oracle database will send you an error (usually ORA-01173,ORA-01176,ORA-01177,ORA-01215 or ORA-01216) because you omitted a file in the CREATE CONTROLFILE statement or contain files that should not be listed. In this case, you should restore the files backed up in step 3 and repeat the process in step 4 with the correct file name.

1.5. Backup control files use the ALTER DATABASE BACKUP CONTROLFILE statement to back up your control files. You have two choices:

L back up the control file to the binary file (a copy of the existing control file) using the following statement:

Generate SQL statements that can be used to recreate the control file:

This command writes the SQL script to the trace file, which can be crawled and edited to reproduce the control file. You can determine the name and location of the trace file by viewing the alarm log.

1.6. Using the current copy to restore control files this section describes how to restore control files from the current backup or multiplexing.

1.6.1. Using a copy of the control file to restore the control file this procedure assumes that one of the control files specified in the CONTROL_FILES parameter is corrupted, the control file directory is still accessible, and there is multiplexing of the control file.

1. When the instance is shut down, use the operating system command to overwrite the abnormal control file with the normal copy:

two。 Start SQL * Plus and open the database:

1.6.2. Using a control file backup to restore a permanent media failure this procedure assumes that one of the control files specified in the CONTROL_FILES parameter is inaccessible due to a permanent media failure and you have a reused copy of the control file.

1. When the instance is shut down, use the operating system command to copy the current copy of the control file to the new accessible location:

two。 Edit the CONTROL_FILES parameter in the initialization parameters file and replace the exception location with the new location:

3. Start SQL * Plus and open the database:

If you have a reuse control file, you can quickly start the database by editing the CONTROL_FILES initialization parameters. Remove the exception control file from the CONTROL_FILES configuration and you can restart the database immediately. You can then perform a rebuild of the exception control file, edit the CONTROL_FILES initialization parameters to include the recovered control file, and then shut down and restart the database.

1.7. Delete control file you want to delete the control file from the database, for example, if the location of the control file is no longer appropriate. Keep in mind that the database should always have at least two control files.

1. Shut down the database.

two。 Edit the CONTROL_FILES parameter in the database initialization parameters file to delete the old control file name.

3. Restart the database.

Note:

This operation does not physically delete unwanted control files from disk. After deleting the control file from the database, use the operating system command to delete the unnecessary file.

1.8. Control File data Dictionary View the following view displays information about the control file:

View

Description

V$DATABASE

Display database information from a control file

V$CONTROLFILE

List the name of the control file

V$CONTROLFILE_RECORD_SECTION

Displays information about the recording section of the control file

V$PARAMETER

Displays the name of the control file specified in the CONTROL_FILES initialization parameters

This example lists the name of the control file:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.