How to recover an SAP instance when a control file got corrupted?

This article answers the following queries:

·         What are the scenarios in which contol file should be backed up in oracle?

·         How to backup control file in oracle?

·         How to recover an instance when one of the  control files got corrupted ?

·         How multiple control files are managed in Oracle ?

---------------------------------------------------------------------------------------

Scenarios in which control file to be backed up  in Oracle

It is very important to backup control file whenever there is a change in the physical structure of the database. Some of the examples of structural changes are

• Adding or dropping or renaming of datafiles
• Adding or dropping a tablespace
• Altering the read/write state of the tablespace
• Adding or dropping redolog files or groups

Control file backup procedure  in Oracle

ALTER DATABASE BACKUP CONTROLFILE statement is used to backup control files in Oracle.

There are 2 options to backup control files.

1. Backup the control file to a binary file (duplicate of existing control file) using the below statement:

ALTER DATABASE BACKPUP CONTROLFILE  TO  ‘/oracle/backup/control.bkp’;

2. Produce SQL statements that can later be used to re-create control file.

ALTER DATABASE BACKUP CONTROLFILE  TO TRACE;

Above command writes  a SQL script to the database trace file where it can be captured and edited to regenerate the control file.

Recovering an instance when one of the  control files got corrupted

Oracle recommends to have atleast 2 control files each stored on a different disk.

(Please note, for SAP applications , 3 control files are generally maintained in an oracle database at different locations)

If a control file got corrupted due to a disk failure the respective instance should be shut down. Once the disk drive is repaired, the corrupted control file can be restored using the copy of control file from other disk and the instance can be brought up. In these cases, media recovery is not required.

How multiple control files are managed in Oracle?

• All the control files that are listed in the CONTROL_FILES parameter are updated by the database.
• The database reads only the first file listed in the CONTROL_FILES parameter during database operation
• If any of the control files got corrupted or become unavailable during database operation, the instance won’t function and it should be shutdown and recovered as mentioned earlier

Related Links :

Control file and its functions in Oracle

Steps to be followed to rename or move control files location

Enter your email address:


Delivered by FeedBurner

Steps to be followed to rename or move control files location

This article answers the following queries :

·         How to find out the current location of control files?

·         Can we rename or move control file location? What is the parameter for that ?

·         What is the process to be followed to rename or move control file location?

-----------------------------------------------------------------------------------------

Query to find current location of control files :

SQL > select name from v$controlfile;

After executing the above query, System outputs  the location of all control files in the system

Usually there will be 3 control files in oracle database( for SAP applications).

Can we rename or move control files location? If so, what is that parameter?

Yes. If required, control files location can changed with the control_files instance parameter.

Please execute the below query to display the current value of control_files parameter

SQL > show parameter control_files

The above command outputs the current value. This value can be changed with the ALTER SYSTEM command. In other words the current location of the control files can be changed  using ALTER SYSTEM command.

Steps to be followed to rename or move control files location

1)      Alter the control_files parameter using the ALTER SYSTEM command

Syntax:

SQL> ALTER SYSTEM SET control_files=’<New controlfile1 path or newcontrol file name>’, <New controlfile2 path or newcontrol file name>’, <New controlfile13 path or newcontrol name>’ SCOPE=SPFILE;

 Eg: In the below command set the paths or names  for various control files  as per your requirement

SQL> ALTER SYSTEM SET control_files=’D:\ORACLE\..\..\..\Newname1.CTL’, 

’D:\ORACLE\..\..\..\Newname2.CTL’,  ’D:\ORACLE\..\..\..\Newname3.CTL’

SCOPE=SPFILE;

2)     Shutdown the database

SQL > shutdown immediate;

3)     Rename the physical file in the OS

SQL >  HOST  MOVE <old controlfile with path> <new controlfile with path >

 Eg:

SQL > HOST  MOVE  D:\ORACLE\ORADATA\CONTROL01.CTL  D:\ORACLE\ORADATA\NEW_CONTROL01.CTL

4)     Start the database

SQL > startup

Related Links :

Control file and its functions in Oracle

How to recover an SAP instance when a control file got corrupted?

Enter your email address:


Delivered by FeedBurner

Control file and its functions in Oracle

This article answers the following queries :

·         What is the control file in oracle?

·         What are the functions of control file in oracle ?

·         What are the  control file contents ?

·         Can database function properly without control file?

·         Can we edit control file ?

·         Who can modify control file ?

·         At what time control file will be created?

What is control file in Oracle and what are its functions ?

Control file is a small binary file which is necessary for the database to start and operate successfully.

What are the functions of control file ?

Each control file is associated with only one database.  Oracle continuosly updates control file during database use so this file must be available for writing whenever the database is in the open state.  Control file will be created at the same time of database creation.

Whenever a datafile or a redolog file is added to , dropped or renamed in the database, the control file is updated to reflect the physical structural change.

These changes are recorded in control file which facilitates :

• Oracle to identify the files that are required or available in case database recovery is needed
• Oracle to identify the datafiles and redolog files to open during database startup.

Can database function properly without control file?

No. Database cannot function properly if  the control file is not available.

Can we edit control file ? Who can modify control file ?

Please note control file cannot be edited by database administrator or by any user. Control file information can only be modified by Oracle.

Contents of control files :

• Control file contains information such as
• Database name
• Timestamp of database creation
• Tablespace information
• The log history
• Archive log information
• Current log sequence number
• Checkpoint information
• Names and locations of associated datafiles and redolog files
• Datafile copy information
• Datafile offline ranges
• Backup set and backup related information
• Backup datafile and redolog information

Related links :

Steps to be followed to rename or move control files location

How to recover an SAP instance when a control file got corrupted?

Enter your email address:


Delivered by FeedBurner