How to Manage Control Files in Oracle
You can create and manage database structures and storage components.
- Managing Control Files
- Managing the Redo Log
- Managing Archived Redo Log Files
- Managing Tablespaces
- Managing Data Files and Temp Files
- Transporting Data
- Managing Undo
- Using Oracle Managed Files
1.Managing Control Files
1.1 What Is a Control File?
Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database.
The control file includes:
-
The database name
-
Names and locations of associated data files and redo log files
-
The timestamp of the database creation
-
The current log sequence number
-
Checkpoint information
1.2 Guidelines for Control Files
1.2.1 Provide File Names for the Control Files
If you do not specify files for CONTROL_FILES
before database creation:
-
If you are not using Oracle Managed Files, then the database creates a control file and uses a default file name. The default name is operating system specific.
-
If you are using Oracle Managed Files, then the initialization parameters you set to enable that feature determine the name and location of the control files.
-
If you are using Oracle Automatic Storage Management (Oracle ASM), you can place incomplete Oracle ASM file names in the
DB_CREATE_FILE_DEST
andDB_RECOVERY_FILE_DEST
initialization parameters. Oracle ASM then automatically creates control files in the appropriate places.
1.2.2 Multiplex Control Files on Different Disks
The behavior of multiplexed control files is this:
-
The database writes to all file names listed for the initialization parameter
CONTROL_FILES
in the database initialization parameter file. -
The database reads only the first file listed in the
CONTROL_FILES
parameter during database operation. -
If any of the control files become unavailable during database operation, the instance becomes inoperable and should be terminated.
1.2.3 Back Up Control Files
It is very important that you back up your control files. This is true initially, and every time you change the physical structure of your database.
Such structural changes include:
-
Adding, dropping, or renaming data files
-
Adding or dropping a tablespace, or altering the read/write state of the tablespace
-
Adding or dropping redo log files or groups
1.2.4 Manage the Size of Control Files
The main determinants of the size of a control file are the values set for the MAXDATAFILES
, MAXLOGFILES
, MAXLOGMEMBERS
, MAXLOGHISTORY
, and MAXINSTANCES
parameters in the CREATE DATABASE
statement that created the associated database.
Increasing the values of these parameters increases the size of a control file of the associated database.
1.3 Creating Control Files
1.3.1 Creating Initial Control Files
The initial control files of an Oracle Database are created when you issue the CREATE DATABASE
statement.
CONTROL_FILES = (/u01/oracle/prod/control01.ctl,
/u02/oracle/prod/control02.ctl,
/u03/oracle/prod/control03.ctl)
1.3.2 Creating Additional Copies, Renaming, and Relocating Control Files
To add a multiplexed copy of the current control file or to rename a control file:
- Shut down the database.
- Copy an existing control file to a new location, using operating system commands.
- Edit the
CONTROL_FILES
parameter in the database initialization parameter file to add the new control file name, or to change the existing control file name. - Restart the database.
1.3.3 Creating New Control Files
1.3.3.1 When to Create New Control Files
You must create new control files in certain situations.
It is necessary for you to create new control files in the following situations:
-
All control files for the database have been permanently damaged and you do not have a control file backup.
-
You want to change the database name.
1.3.3.2 The CREATE CONTROLFILE Statement
You can create a new control file for a database using the CREATE CONTROLFILE
statement.
The following statement creates a new control file for the prod
database (a database that formerly used a different database name):
CREATE CONTROLFILE
SET DATABASE prod
LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',
'/u01/oracle/prod/redo01_02.log'),
GROUP 2 ('/u01/oracle/prod/redo02_01.log',
'/u01/oracle/prod/redo02_02.log'),
GROUP 3 ('/u01/oracle/prod/redo03_01.log',
'/u01/oracle/prod/redo03_02.log')
RESETLOGS
DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
'/u01/oracle/prod/rbs01.dbs' SIZE 5M,
'/u01/oracle/prod/users01.dbs' SIZE 5M,
'/u01/oracle/prod/temp01.dbs' SIZE 5M
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXLOGHISTORY 400
MAXDATAFILES 200
MAXINSTANCES 6
ARCHIVELOG;
1.3.3.3 Creating New Control Files
1.Make a list of all data files and redo log files of the database.
[oracle@oracle-db-19cs ~]$
[oracle@oracle-db-19cs ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 2 20:21:47 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show user;
USER is "SYS"
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> set pagesize 200 linesize 200
SQL>
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u02/oradata/CDB1/redo03.log
/u02/oradata/CDB1/redo02.log
/u02/oradata/CDB1/redo01.log
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/pdbseed/system01.dbf
/u02/oradata/CDB1/pdbseed/sysaux01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdbseed/undotbs01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf
/u02/oradata/CDB1/pdb2/system01.dbf
/u02/oradata/CDB1/pdb2/sysaux01.dbf
/u02/oradata/CDB1/pdb2/undotbs01.dbf
/u02/oradata/CDB1/pdb2/users01.dbf
/u02/oradata/CDB1/pdb1/data01.dbf
/u02/oradata/CDB1/pdb1/TBS_MAXWELL_01.dbf
/u02/oradata/CDB1/pdb1/undotbs02.dbf
/u02/oradata/CDB1/F4C9A1F8230F2E5CE0538A08A8C02747/datafile/o1_mf_system_kyv81s6t_.dbf
/u02/oradata/CDB1/pdb1/TBS_TOOLS01.dbf
/u02/oradata/CDB1/F4C9A1F8230F2E5CE0538A08A8C02747/datafile/o1_mf_sysaux_kyv81s6v_.dbf
/u02/oradata/CDB1/F4C9A1F8230F2E5CE0538A08A8C02747/datafile/o1_mf_undotbs1_kyv81s6w_.dbf
/u02/oradata/CDB1/F4C9A1F8230F2E5CE0538A08A8C02747/datafile/o1_mf_users_kyv82cng_.dbf
/u02/oradata/CDB1/pdb1/rman01.dbf
24 rows selected.
SQL> select value from v$parameter where name = 'control_files';
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u02/oradata/CDB1/control01.ctl, /u02/oradata/CDB1/control02.ctl
SQL>
2.Shut down the database.
If the database is open, shut down the database normally if possible. Use the IMMEDIATE
or ABORT
clauses only as a last resort.
3.Back up all data files and redo log files of the database.
4.Start up a new instance, but do not mount or open the database.
STARTUP NOMOUNT
5.Create a new control file for the database using the CREATE CONTROLFILE
statement
6.Store a backup of the new control file on an offline storage device.
7.Edit the CONTROL_FILES
initialization parameter for the database to indicate all of the control files now part of your database as created in step 5 (not including the backup control file). If you are renaming the database, edit the DB_NAME
parameter in your instance parameter file to specify the new name.
8.Recover the database if necessary. If you are not recovering the database, skip to step 9.
If the new control file was created using the RESETLOGS
clause, you must specify USING BACKUP CONTROL FILE
. If you have lost online redo logs, archived redo log files, or data files, use the procedures for recovering those files.
9.Open the database using one of the following methods:
If you did not perform recovery, or you performed complete, closed database recovery in step 8, open the database normally.
ALTER DATABASE OPEN;
If you specified RESETLOGS
when creating the control file, use the ALTER DATABASE
statement, indicating RESETLOGS
.
ALTER DATABASE OPEN RESETLOGS
1.5 Backing Up Control Files
Use the ALTER DATABASE BACKUP CONTROLFILE
statement to back up your control files.
- Back up the control file to a binary file (duplicate of existing control file) using the following statement:
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';
- Produce SQL statements that can later be used to re-create your control file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
1.6 Recovering a Control File Using a Current Copy
You can recover your control file from a current backup or from a multiplexed copy.
1.6.1 Recovering from Control File Corruption Using a Control File Copy
If a control file becomes corrupted, then you can recover it using a control file copy.
This method assumes that one of the control files specified in the CONTROL_FILES
parameter is corrupted, that the control file directory is still accessible, and that you have a multiplexed copy of the control file.
1.With the instance shut down, use an operating system command to overwrite the bad control file with a good copy:
% cp /u03/oracle/prod/control03.ctl /u02/oracle/prod/control02.ctl
2.Start SQL*Plus and open the database:
SQL> STARTUP
1.6.2 Recovering from Permanent Media Failure Using a Control File Copy
If there is permanent media failure, then you can recover by using a control file copy.
This method assumes that one of the control files specified in the CONTROL_FILES
parameter is inaccessible due to a permanent media failure and that you have a multiplexed copy of the control file.
1.With the instance shut down, use an operating system command to copy the current copy of the control file to a new, accessible location:
% cp /u01/oracle/prod/control01.ctl /u04/oracle/prod/control03.ctl
2.Edit the CONTROL_FILES
parameter in the initialization parameter file to replace the bad location with the new location:
CONTROL_FILES = (/u01/oracle/prod/control01.ctl,
/u02/oracle/prod/control02.ctl,
/u04/oracle/prod/control03.ctl)
3.Start SQL*Plus and open the database:
SQL> STARTUP
If you have multiplexed control files, you can get the database started up quickly by editing the CONTROL_FILES
initialization parameter. Remove the bad control file from CONTROL_FILES
setting and you can restart the database immediately. Then you can perform the reconstruction of the bad control file and at some later time shut down and restart the database after editing the CONTROL_FILES
initialization parameter to include the recovered control file.
1.7 Dropping Control Files
You can drop control files, but the database should have at least two control files at all times.
You want to drop control files from the database, for example, if the location of a control file is no longer appropriate.
- Shut down the database.
- Edit the
CONTROL_FILES
parameter in the database initialization parameter file to delete the old control file name. - Restart the database.
1.8 Control Files Data Dictionary Views
ou can query a set of data dictionary views for information about control files.
The following views display information about control files:
View | Description |
---|---|
| Displays database information from the control file |
| Lists the names of control files |
| Displays information about control file record sections |
| Displays the names of control files as specified in the |
SQL> SELECT NAME FROM V$CONTROLFILE;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u02/oradata/CDB1/control01.ctl
/u02/oradata/CDB1/control02.ctl
SQL>
相关文章
- PL/SQL Developer的安装以及与64位Oracle Database进行连接
- 《oracle每日一练》Oracle DBLink连接数过多的问题(Ora-02020)
- mybatis oracle两种方式批量插入数据
- cx_Oracle连接oracle数据库
- Unable to acquire Oracle environment handle 问题的解决
- Linux Oracle to Exadata configuration
- Oracle表空间名称是区分大小写
- Oracle to MySQL Goldengate实现增量迁移
- oracle 空值处理,排序过滤
- oracle 不是group by表达式
- 连接ORACLE数据库,是不是必须要安装oracle客户端的运行时
- Oracle迁移到PPAS(PostgreSQL)时的日期计算问题
- Atitit oracle新特性5 6 7 8 9 10 11 12 18 19 20 attilax总结 目录 1.1. :ora 20c1 1.2. Oracle Database 19c 的
- 已解决:Failure to find com.oracle:ojdbc6:jar:11.2.0.4.0-atlassian-hosted
- 已解决FutureWarning: The default value of regex will change from True to False in a future version. In
- Oracle查看锁表和解锁
- Oracle 经常使用的改动语句
- 一脸懵逼学习oracle
- Oracle中关于提出问题
- How to gathering Database Statistics in Oracle
- Oracle 12c中SQLPlus操作使用(包含实验二ORACLE SQL*PLUS环境与查询的详细操作解释)