Dropping Control Files in Oracle

Goal:

Dropping Control Files in Oracle

Description:

You can drop control files from the database. For example, you might want to do so if the location of a control file is no longer appropriate. Remember that the database must have at least two control files at all times.

Shut down the database.

Edit the CONTROL_FILES parameter in the database's initialization parameter file to delete the old control file's name.

Restart the database.

Note:

This operation does not physically delete the unwanted control file from the disk. Use operating system commands to delete the unnecessary file after you have dropped the control file from the database.

Recovering from Permanent Media Failure Using a Control File Copy

Goal:
Recovering from Permanent Media Failure Using a Control File Copy

Description:

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 multiplexed copy of the control file.

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/itbl/control01.ctl  /u04/oracle/itbl/control03.ctl

Edit the CONTROL_FILES parameter in the initialization parameter file to replace the bad location with the new location:

CONTROL_FILES = (/u01/oracle/itbl/control01.ctl,
                 /u02/oracle/itbl/control02.ctl, 
                 /u04/oracle/itbl/control03.ctl)

Start SQL*Plus and open the database:


SQL> STARTUP

Recovering a Control File Using a Current Copy

Goal:

Recovering a Control File Using a Current Copy

Description:

Recovering from Control File Corruption Using a Control File Copy

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 you have a multiplexed copy of the control file.

With the instance shut down, use an operating system command to overwrite the bad control file with a good copy:

% cp /u01/oracle/itbl/control03.ctl  /u01/oracle/itbl/control02.ctl

Start SQL*Plus and open the database:

SQL> STARTUP

Backup Control File To Binary File

Goal:

Backup Control File To Binary File

Statement:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/tmp/control.bkp';

Database altered.

SQL>ALTER DATABASE BACKUP CONTROLFILE TO 'D:\control.bkp' REUSE;


Using RMAN

RMAN> backup as copy current controlfile format 'c:\control.ctl';

Backup Control File To Text File

Goal:

Backup Control File To Text File

Statement:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'D:\control.bkp';

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/control.bkp' REUSE;


ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/control01.ctl' NORESETLOGS;

Creating a Controlfile in Oracle: Example

Goal:

Creating a Controlfile: Example This statement re-creates a control file. In this statement, database demo was created with the WE8DEC character set.

Statement:

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "itbl" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 32
    MAXINSTANCES 1
    MAXLOGHISTORY 449
LOGFILE
  GROUP 1 '/oracledb/oradata/itbl/t_log1.f'  SIZE 500K,
  GROUP 2 '/oracledb/oradata/itbl/t_log2.f'  SIZE 500K
# STANDBY LOGFILE
DATAFILE
  '/oracledb/oradata/itbl/t_db1.f',
  '/oracledb/oradata/itbl/dbu19i.dbf',
  '/oracledb/oradata/itbl/tbs_11.f',
  '/oracledb/oradata/itbl/smundo.dbf',
  '/oracledb/oradata/itbl/demo.dbf'
CHARACTER SET WE8DEC
;