What should I do if the Oracle control file is lost?

Three methods: 1. Restore using RMAN backup

Automatic Backup of recovery Control Files
If the recovery directory is not used, you should configure automatic backup of the control file to restore the control file quickly as needed. Whether or not the quick recovery area is used to restore the control file

Commands are the same. However, if the quick recovery zone is used, RMAN implicitly checks copies of backups and images listed in the control file and restores the files in the quick recovery zone.

To improve the role of the restored control file in restoring the rest of the database.
Note: After the control file is restored, the tape backup is not automatically checked. If tape backup is used, after the control file is restored and the database is loaded, the backup on the tape must be checked.
To restore the control file from an automatic backup, the database must be in the nomount state. Then, use restore
Controlfile from autobackup command:
RMAN> shutdown immediate;
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;

 

Automatic Backup of recovery control files (continued)
RMAN automatically backs up the search control file. If an automatic backup is found, RMAN restores the control file from this backup to all the control file locations listed in the control_files initialization parameters.
If a directory is restored, you do not need to set the dbid or use the automatic backup of the control file to restore the control file. You can use the restore controlfile command without any parameters:
RMAN> restore controlfile;
When you perform this operation, the instance must be in the nomount state and rman must be connected to the recovery directory. The restored control file is written to all locations listed in the control_files initialization parameter.
If the spfile of the database is lost and needs to be restored from the automatic backup, the process is similar to restoring the control file from the automatic backup. You must first set the dbid of the database before using the restore spfile from autobackup command.
After the instance is started using the restored server parameter file, RMAN can restore the control file from an automatic backup. After restoring and loading the control file, you will have the backup information required to restore and restore the database.
After restoring the control file of the database from the backup, you must perform full media recovery before you can use the resetlogs option to open the database.

 

 

[Oracle @ yysf 2009_09_30] $ RMAN target/

Recovery Manager: Release 10.2.0.1.0-production on Wed Sep 30 17:08:44 2009

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total system global area 285212672 bytes

Fixed size 1218992 bytes
Variable Size 109053520 bytes
Database buffers 171966464 bytes
Redo buffers 2973696 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 30-sep-09
Using target database control file instead of recovery catalog
Allocated channel: ora_disk_1
Channel ora_disk_1: SID = 156 devtype = Disk

Recovery area destination:/Oracle/oradata
Database Name (or database unique name) used for search: orcl
Channel ora_disk_1: autobackup found in the recovery area
Channel ora_disk_1: autobackup found:/Oracle/oradata/orcl/autobackup/2009_09_30/o1_mf_s_698950847_5d67z0ww _. bkp
Channel ora_disk_1: Control File restore from autobackup complete
Output Filename =/Oracle/oradata/orcl/control01.ctl
Output Filename =/Oracle/oradata/orcl/control02.ctl
Output Filename =/Oracle/oradata/orcl/control03.ctl
Finished restore at 30-sep-09

RMAN> alter databaset Mount;

RMAN-00571: ========================================================== ==============================
RMAN-00569: ==================== error message stack follows ==========================
RMAN-00571: ========================================================== ==============================
RMAN-00558: Error encountered while parsing input commands
RMAN-01009: syntax error: Found "identifier": expecting one of: "clone, Database"
RMAN-01008: The bad identifier was: databaset
RMAN-01007: At Line 1 column 7 file: Standard Input

RMAN> alter database Mount;

Database mounted
Released channel: ora_disk_1

RMAN> recover database;

Starting recover at 30-sep-09
Starting implicit crosscheck backup at 30-sep-09
Allocated channel: ora_disk_1
Channel ora_disk_1: SID = 156 devtype = Disk
Crosschecked 1 objects
Finished implicit crosscheck backup at 30-sep-09

Starting implicit crosscheck copy at 30-sep-09
Using channel ora_disk_1
Finished implicit crosscheck copy at 30-sep-09

Searching for all files in the recovery area
Cataloging files...
Cataloging done

List of cataloged files
======================================
File Name:/Oracle/oradata/orcl/autobackup/2009_09_30/o1_mf_s_698950847_5d67z0ww _. bkp

Using channel ora_disk_1

Starting media recovery

Archive log thread 1 sequence 30 is already on disk as file/Oracle/oradata/orcl/redo03.log
Archive log filename =/Oracle/oradata/orcl/redo03.log thread = 1 sequence = 30
Media recovery complete, elapsed time: 00:00:03
Finished recover at 30-sep-09

RMAN> alter database open;

RMAN-00571: ========================================================== ==============================
RMAN-00569: ==================== error message stack follows ==========================
RMAN-00571: ========================================================== ==============================
RMAN-03002: failure of alter dB command at 17:10:12
The ORA-01589: must use resetlogs or noresetlogs option for database open

RMAN> alter database open resetlogs;

Database opened

RMAN>

2. Use the old control file that can be opened to generate a trace script and recreate the control file.
1. Create a pfile and add the old control file as the source old. CTL.

Orcl. _ db_cache_size = 79691776
Orcl. _ Java _pool_size = 4194304
Orcl. _ large_pool_size = 4194304
Orcl. _ shared_pool_size = 109051904
Orcl. _ streams_pool_size = 0
*. Audit_file_dest = '/Oracle/admin/orcl/adump'
*. Audit_trail = 'none'
*. Background_dump_dest = '/Oracle/admin/orcl/bdump'
*. Compatible = '10. 2.0.1.0'
*. Control_files = '/home/Oracle/old. CTL'
*. Core_dump_dest = '/Oracle/admin/orcl/cdump'
*. Db_block_size = 8192
*. Db_cache_size = 79691776
*. Db_create_file_dest = '/Oracle/oradata'
*. Db_domain =''
*. Db_file_multiblock_read_count = 16
*. Db_name = 'orcl'
*. Db_recovery_file_dest = '/Oracle/flash_recovery_area'
*. Db_recovery_file_dest_size = 2147483648
*. Dispatchers = '(Protocol = TCP) (Service = orclxdb )'
*. Java_pool_size = 4194304
*. Job_queue_processes = 10
*. Large_pool_size = 4194304
*. Log_archive_format = '% T _ % S _ % R. dbf'
*. Open_cursors = 300
*. Pga_aggregate_target = 66060288
*. Processses = 150
*. Remote_login_passwordfile = 'clusive'
*. Sga_target = 201326592
*. Shared_pool_size = 109051904
*. Streams_pool_size = 0
*. Undo_management = 'auto'
*. Undo_tablespace = 'undotbs1'
*. User_dump_dest = '/Oracle/admin/orcl/udump'

2. Use the old control file to mount the database,
SQL> startup Mount pfile = 'initorcl. ora ';
SQL> select status from V $ instance;

Status
------------
Mounted

3. Run the following script to generate the database CTL file:
SQL> alter Database Backup controlfile to trace;

When the database needs to be mounted,
Generate a TRC file and export it to the udump folder,
The TRC size is about 7 K.

 

4. Create a pfile and specify the location of the control file to be restored.
[Oracle @ yysf DBS] $ VI initorcl. ora

Orcl. _ db_cache_size = 79691776
Orcl. _ Java _pool_size = 4194304
Orcl. _ large_pool_size = 4194304
Orcl. _ shared_pool_size = 109051904
Orcl. _ streams_pool_size = 0
*. Audit_file_dest = '/Oracle/admin/orcl/adump'
*. Audit_trail = 'none'
*. Background_dump_dest = '/Oracle/admin/orcl/bdump'
*. Compatible = '10. 2.0.1.0'
*. Control_files = '/home/Oracle/New. CTL'
*. Core_dump_dest = '/Oracle/admin/orcl/cdump'
*. Db_block_size = 8192
*. Db_cache_size = 79691776
*. Db_create_file_dest = '/Oracle/oradata'
*. Db_domain =''
*. Db_file_multiblock_read_count = 16
*. Db_name = 'orcl'
*. Db_recovery_file_dest = '/Oracle/flash_recovery_area'
*. Db_recovery_file_dest_size = 2147483648
*. Dispatchers = '(Protocol = TCP) (Service = orclxdb )'
*. Java_pool_size = 4194304
*. Job_queue_processes = 10
*. Large_pool_size = 4194304
*. Log_archive_format = '% T _ % S _ % R. dbf'
*. Open_cursors = 300
*. Pga_aggregate_target = 66060288
*. Processses = 150
*. Remote_login_passwordfile = 'clusive'
*. Sga_target = 201326592
*. Shared_pool_size = 109051904
*. Streams_pool_size = 0
*. Undo_management = 'auto'
*. Undo_tablespace = 'undotbs1'
*. User_dump_dest = '/Oracle/admin/orcl/udump'
~
5. Check the TRC file and copy the part of the control file created:
Startup nomount
Create controlfile reuse Database "orcl" resetlogs archivelog
Maxlogfiles 16
Maxlogmembers 3
Maxdatafiles 100
Maxinstances 8
# Maxloghistory 292
Logfile
Group 1 (
'/Oracle/oradata/orcl/onlinelog/ow.mf_00005bbblfr4 _. log ',
'/Oracle/flash_recovery_area/orcl/onlinelog/ow.mf_4155bbblgwf _. log ',
'/Home/Oracle/damao. dbf'
) Size 50 m,
Group 2 (
'/Oracle/oradata/orcl/onlinelog/o1_mf_2_5bbbljjw _. log ',
'/Oracle/flash_recovery_area/orcl/onlinelog/o1_mf_2_5bbblkdc _. Log'
) Size 50 m,
Group 3 (
'/Oracle/oradata/orcl/onlinelog/ow.mf_3_5bbbllln _. log ',
'/Oracle/flash_recovery_area/orcl/onlinelog/o1_mf_3_5bbblmq0 _. Log'
) Size 50 m
-- Standby logfile
Datafile
'/Oracle/oradata/orcl/datafile/o1_mf_system_5bbbjkn1 _. dbf ',
'/Oracle/oradata/orcl/datafile/o1_mf_undotbs1_5bbbjkv1 _. dbf ',
'/Oracle/oradata/orcl/datafile/o1_mf_sysaux_5bbbjko0 _. dbf ',
'/Oracle/oradata/orcl/datafile/o1_mf_users_5bbbjkw8 _. dbf ',
'/Oracle/oradata/orcl/datafile/o1_mf_example_5bbbmdyq _. dbf'
Character Set al32utf8
;

Create a file recover. SQL

5. Use this script to generate a new control file
Startup nomount;
SQL> @ recover. SQL

[Oracle @ yysf ~] $ Sqlplus/As sysdba

SQL * Plus: Release 10.2.0.1.0-production on Sun Sep 13 21:47:46 2009

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the partitioning, OLAP and Data Mining options

SQL> shutdown abort;
Oracle instance shut down.

--- Place the database in nomount state with the established pfile ,---------
SQL> startup nomount pfile = '/Oracle/product/10.2.0/db_1/dbs/initorcl. ora ';
Oracle instance started.

Total system global area 201326592 bytes
Fixed size 1218508 bytes
Variable Size 117442612 bytes
Database buffers 79691776 bytes
Redo buffers 2973696 bytes

---- Use the created SQL script to recreate the control file ---------
SQL> @/home/Oracle/recover. SQL
ORA-01081: cannot start already-running Oracle-shut it down first

Control File Created.

6. Restore the database and open the database in resetlogs mode.
[Oracle @ yysf ~] $ Sqlplus/As sysdba

SQL * Plus: Release 10.2.0.1.0-production on Sun Sep 13 21:50:31 2009

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the partitioning, OLAP and Data Mining options

SQL> select status from V $ instance;

Status
------------
Mounted

SQL> recover database using backup controlfile;
ORA-00279: Change 813732 generated at 09/13/2009 21:21:13 needed for thread 1
ORA-00289: Suggestion:
/Oracle/flash_recovery_area/orcl/archivelog/2009_09_13/o1_mf_000026 _ % u _. Arc
ORA-00280: Change 813732 for thread 1 is in sequence #26

Specify log: {<RET> = suggested | filename | auto | cancel}
/Oracle/flash_recovery_area/orcl/onlinelog/o1_mf_2_5bbblkdc _. Log
Log applied.
Media recovery complete

SQL> alter database open resetlogs;

Database altered.

 

3. Manually write the script. Based on datafile, write the CTL file to generate the script. The remaining steps are the same as those of step 2.