Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, We will explain the topic How to Increase the Number of Control Files in Oracle Databese?.
A. If we are using SPFile parameter file.
1. The control_files parameter in our spfile parameter file is updated as follows.
1 2 3 4 5 | SQL> ALTER SYSTEM SET CONTROL_FILES= '/oracle/ora11g/data_ONURDB/ONURDB/control01.ctl' , '/oracle/ora11g/data_ONURDB/ONURDB/control02.ctl', '/oracle/ora11g/data_ONURDB/ONURDB/control03.ctl' SCOPE=SPFILE; System altered. |
2. The database is closed.
1 2 3 4 | SQL> SHUTDOWN IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. |
3. A new control file is created in the specified directory.
1 | -bash-3.2$ cp control02.ctl control03.ctl |
4. The database opens.
1 2 3 4 5 6 7 8 9 10 | SQL> startup; ORACLE instance started. Total System Global Area 1653518336 bytes Fixed Size 2253784 bytes Variable Size 989858856 bytes Database Buffers 654311424 bytes Redo Buffers 7094272 bytes Database mounted. Database opened. |
5. It is checked whether the parameter file is updated or not.
1 2 3 4 5 6 7 8 9 | SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /oracle/ora11g/data_ONURDB/ONU RDB/control01.ctl, /oracle/ora 11g/data_ONURDB/ONURDB/control 02.ctl, /oracle/ora11g/data_ON URDB/ONURDB/control03.ctl |
When we try to open our database with pfile without updating our pfile parameter file (init<ORACLE_SID>.ora), it opens without problems, but it does not see the newly added control_file.
B. If we are using PFILE,
1. The database is closed.
1 2 3 4 | SQL> SHUTDOWN IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. |
2. The Pfile parameter file is opened with the vi text editor and the control_files are updated.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | ONURDB.__db_cache_size=654311424 ONURDB.__java_pool_size=16777216 ONURDB.__large_pool_size=33554432 ONURDB.__oracle_base='/oracle'#ORACLE_BASE set from environment ONURDB.__pga_aggregate_target=637534208 ONURDB.__sga_target=1023410176 ONURDB.__shared_io_pool_size=0 ONURDB.__shared_pool_size=301989888 ONURDB.__streams_pool_size=0 *.audit_file_dest='/oracle/admin/ONURDB/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/oracle/ora11g/data_ONURDB/ONURDB/control01.ctl','/oracle/ora11g/data_ONURDB/ONURDB/control02.ctl', '/oracle/ora11g/data_ONURDB/ONURDB/control03.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='ONURDB' *.diagnostic_dest='/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=ONURDBXDB)' *.log_archive_dest_1='location=/oracle/ora11g/backup_ONURDB' *.log_archive_format='ONURDB_%t_%s_%r.arc' *.memory_target=1655701504 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' |
3. A new control file is created in the specified directory.
1 | -bash-3.2$ cp control02.ctl control03.ctl |
In this step, if you do not create a control file named control03.ctl in the specified directory, but want to use the control file created in A, an error will be received when opening the database.
The reason is that the checkpoint information in the control files is not the same. The error is as follows.
1 2 3 4 5 6 7 8 9 10 11 | SQL> STARTUP PFILE = '/oracle/ora11g/dbs/initONURDB.ora'; ORACLE instance started. Total System Global Area 1653518336 bytes Fixed Size 2253784 bytes Variable Size 989858856 bytes Database Buffers 654311424 bytes Redo Buffers 7094272 bytes ORA-00214: control file '/oracle/ora11g/data_ONURDB/ONURDB/control01.ctl' version 19149 inconsistent with file '/oracle/ora11g/data_ONURDB/ONURDB/control03.ctl' version 19135 |
4. The database opens.
1 2 3 4 5 6 7 8 9 10 | SQL> STARTUP PFILE = '/oracle/ora11g/dbs/initONURDB.ora'; ORACLE instance started. Total System Global Area 1653518336 bytes Fixed Size 2253784 bytes Variable Size 989858856 bytes Database Buffers 654311424 bytes Redo Buffers 7094272 bytes Database mounted. Database opened. |
