Control file is a fairly small file (up to 64M), which contains a directory of other files required by Oracle. The parameter file tells the location of the instance control file, and the control file tells the location of the example database and the online redo log file. The control file also tells Oracle about other things, such as information about checkpoints that have occurred, database name (which must match the db_name parameter), timestamp of creating the database, history of archiving redo logs (sometimes this will make the control file larger), RMAN information, etc.
Control files should be saved through hardware (RAID) multiplexing. If mirroring is not supported, they should be saved through Oracle multiplexing. There should be more than one copy, and they should be saved on different disks to prevent the loss of control files in case of disk failure. Losing control files is not fatal, but it makes recovery much more difficult.
If all control files are lost and there is no backup, we can open the database by rebuilding the control file. Among them, at least the following information is required to rebuild the control file:
1. Database name
2. Character set
3. Data file name
4. Initialization parameters, including MAXLOGFILES, MAXLOGMEMBERS, MAXDATAFILES, maximinstances, MAXLOGHISTORY, etc;
1. Environmental preparation
Database version
We tested it in Oracle11g.
Click (here) to collapse or open
SQL> SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL>
Delete control file
1. Query control_files initialization parameter to obtain the control file path;
Click (here) to collapse or open
-
SQL> SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /u01/app/oracle/oradata/HOEGH/ control01.ctl, /u01/app/oracle /oradata/HOEGH/control02.ctl SQL>
2. Then, use the rm command to delete the control file;
Click (here) to collapse or open
-
[oracle@HOEGH ~]$ rm /u01/app/oracle/oradata/HOEGH/control01.ctl [oracle@HOEGH ~]$ rm /u01/app/oracle/oradata/HOEGH/control02.ctl [oracle@HOEGH ~]$
3. At this time, forcibly close the database, then restart the database and report ORA-00205 error. It should be noted that when the shutdown immediate command is executed, the database cannot be shut down normally and can only be shut down to the mounted state; You need to use the shutdown abort command to forcibly shut down the database.
Click (here) to collapse or open
SQL> SQL> shutdown immediate Database closed. ORA-00210: cannot open the specified control file ORA-00202: control file: \'/u01/app/oracle/oradata/HOEGH/control01.ctl\' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 SQL> select status from v$instance; STATUS ------------ MOUNTED SQL> SQL> shutdown abort ORACLE instance shut down. SQL> SQL> SQL> startup ORACLE instance started. Total System Global Area 941600768 bytes Fixed Size 1348860 bytes Variable Size 515902212 bytes Database Buffers 419430400 bytes Redo Buffers 4919296 bytes ORA-00205: error in identifying control file, check alert log for more info SQL>
2. Get database name
Firstly, a parameter file in text format is generated;
Click (here) to collapse or open
SQL> SQL> create pfile from spfile; File created. SQL>
Open the parameter file and view dB_ The name parameter value is the database name.
Click (here) to collapse or open
[oracle@hoegh dbs]$ cat initHOEGH.ora HOEGH.__db_cache_size=419430400 HOEGH.__java_pool_size=4194304 HOEGH.__large_pool_size=4194304 HOEGH.__oracle_base=\'/u01/app/oracle\'#ORACLE_BASE set from environment HOEGH.__pga_aggregate_target=377487360 HOEGH.__sga_target=566231040 HOEGH.__shared_io_pool_size=0 HOEGH.__shared_pool_size=130023424 HOEGH.__streams_pool_size=0 *.audit_file_dest=\'/u01/app/oracle/admin/HOEGH/adump\' *.audit_trail=\'db\' *.compatible=\'11.2.0.0.0\' *.control_files=\'/u01/app/oracle/oradata/HOEGH/control01.ctl\',\'/u01/app/oracle/oradata/HOEGH/control02.ctl\' *.db_block_size=8192 *.db_domain=\'\' *.db_name=\'HOEGH\' *.diagnostic_dest=\'/u01/app/oracle\' *.dispatchers=\'(PROTOCOL=TCP) (SERVICE=HOEGHXDB)\' *.memory_max_target=943718400 *.memory_target=943718400 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile=\'EXCLUSIVE\' *.undo_tablespace=\'UNDOTBS1\' [oracle@hoegh dbs]$
3. Start to the unmount state and obtain the character set
Because the query statement select needs to be executed userenv('language') from dual; To get the character set, you need to start the database to the unmount state.
Click (here) to collapse or open
SQL> SQL> startup nomount ORACLE instance started. Total System Global Area 941600768 bytes Fixed Size 1348860 bytes Variable Size 515902212 bytes Database Buffers 419430400 bytes Redo Buffers 4919296 bytes SQL> SQL> select userenv(\'language\') from dual; USERENV(\'LANGUAGE\') ---------------------------------------------------- AMERICAN_AMERICA.US7ASCII SQL> SQL>
4. Get data file name
Get the list of data files through the ls command.
Click (here) to collapse or open
[oracle@hoegh HOEGH]$ ls -lh total 1.8G -rw-r----- 1 oracle oinstall 314M May 30 11:07 example01.dbf -rw-r----- 1 oracle oinstall 51M May 30 11:07 redo01.log -rw-r----- 1 oracle oinstall 51M May 30 11:07 redo02.log -rw-r----- 1 oracle oinstall 51M May 30 11:07 redo03.log -rw-r----- 1 oracle oinstall 541M May 30 11:07 sysaux01.dbf -rw-r----- 1 oracle oinstall 721M May 30 11:07 system01.dbf -rw-r----- 1 oracle oinstall 30M Oct 13 2014 temp01.dbf -rw-r----- 1 oracle oinstall 96M May 30 11:07 undotbs01.dbf -rw-r----- 1 oracle oinstall 5.1M May 30 11:07 users01.dbf [oracle@hoegh HOEGH]$
5. Generate and create control file script
In this way, we have all the basic information required to create the control file. Let's generate the script for creating the control file.
Click (here) to collapse or open
STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE \"HOEGH\" NORESETLOGS ARCHIVELOG MAXLOGFILES 5 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 226 LOGFILE GROUP 1 \'/u01/app/oracle/oradata/HOEGH/redo01.log\' SIZE 50M, GROUP 2 \'/u01/app/oracle/oradata/HOEGH/redo02.log\' SIZE 50M, GROUP 3 \'/u01/app/oracle/oradata/HOEGH/redo03.log\' SIZE 50M DATAFILE \'/u01/app/oracle/oradata/HOEGH/system01.dbf\', \'/u01/app/oracle/oradata/HOEGH/sysaux01.dbf\', \'/u01/app/oracle/oradata/HOEGH/undotbs01.dbf\', \'/u01/app/oracle/oradata/HOEGH/users01.dbf\', \'/u01/app/oracle/oradata/HOEGH/example01.dbf\', \'/u01/app/oracle/oradata/HOEGH/temp01.dbf\' CHARACTER SET US7ASCII ;
6. Rebuild control documents
It should be noted that an error will be reported when executing the above creation script, and the system will prompt that the temporary file does not belong to the data file, as shown below:
Click (here) to collapse or open
SQL> @/u01/app/oracle/oradata/HOEGH/CreateControlFile.sql ORA-01081: cannot start already-running ORACLE - shut it down first CREATE CONTROLFILE REUSE DATABASE \"HOEGH\" NORESETLOGS ARCHIVELOG * ERROR at line 1: ORA-01503: CREATE CONTROLFILE failed ORA-01160: file is not a data file ORA-01110: data file : \'/u01/app/oracle/oradata/HOEGH/temp01.dbf\' SQL>
Modify the script and execute it again. After rebuilding the control file, the database will be opened to mount state.
Click (here) to collapse or open
SQL> SQL> @/u01/app/oracle/oradata/HOEGH/CreateControlFile.sql ORACLE instance started. Total System Global Area 941600768 bytes Fixed Size 1348860 bytes Variable Size 515902212 bytes Database Buffers 419430400 bytes Redo Buffers 4919296 bytes Control file created. SQL> SQL> select status from v$instance; STATUS ------------ MOUNTED SQL>
7. Open the database
When opening the database, an error will be reported, indicating that the system01 data file needs to perform media recovery. We can just execute recover database.
Click (here) to collapse or open
SQL> SQL> alater database open; SP2-0734: unknown command beginning \"alater dat...\" - rest of line ignored. SQL> SQL> alter database open; alter database open * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: \'/u01/app/oracle/oradata/HOEGH/system01.dbf\' SQL> SQL> recover database; Media recovery complete. SQL> SQL> alter database open; Database altered. SQL> SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL> SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS EXAMPLE 6 rows selected. SQL>
8. Summary
The steps to rebuild the control file are summarized below:
1. Obtain the database name;
2. Get the character set name;
3. Obtain the data file name;
4. Rebuild control documents;
5. Perform media recovery;
6. Open the database.