An example of Oracle 10 Gb database recovery control file damaged or lost control file

 

If the control file is lost or damaged, how can this problem be solved?

View the settings in the parameter file:

SQL> show parameter control_files;

Name type value
-----------------------------------------------------------------------------
Control_files string c: \ oracle \ product \ 10.2.0 \ orada
Ta \ keymen \ control01.ctl, c: \ or
ACLE \ product \ 10.2.0 \ oradata \ Ke
Ymen \ control02.ctl, c: \ oracle \
Product \ 10.2.0 \ oradata \ keymen \
Control03.ctl
SQL>

View the dynamic performance view of an Oracle Database

SQL> select name from V $ controlfile;

Name
--------------------------------------------------------
----------------------------------------
C: \ oracle \ product \ 10.2.0 \ oradata \ keymen \ control01.ctl
C: \ oracle \ product \ 10.2.0 \ oradata \ keymen \ control02.ctl
C: \ oracle \ product \ 10.2.0 \ oradata \ keymen \ control03.ctl

Backing up control files
Use the alter Database Backup controlfile statement to back up your control files. You have two options:

Back up the control file to a binary file (duplicate of existing control file) using the following statement:

Alter Database Backup controlfile to 'e: \ oraclebackup \ Control. bkp ';

Produce SQL statements that can later be used to re-create your control file:

Alter Database Backup controlfile to trace;

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

 

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

**************************************** **************************************

Experiment 1: delete a control file and keep only one control file
**************************************** **************************************

Edit the c: \ oracle \ product \ 10.2.0 \ admin \ keymen \ pfile \ init. ora file, only in
######################################## ###
# File configuration
######################################## ###
Control_files = ("C: \ oracle \ product \ 10.2.0 \ oradata \ keymen \ control01.ctl ")
Db_recovery_file_dest = c: \ oracle \ product \ 10.2.0/flash_recovery_area
Db_recovery_file_dest_size = 2147483648

SQL> shutdown immediate;
ORA-01507: Database not mounted

Oracle instance shut down.
SQL> startup open pfile = 'C: \ oracle \ product \ 10.2.0 \ admin \ keymen \ pfile \ init. ora ';
Oracle instance started.

Total system global area 612368384 bytes
Fixed size 1250428 bytes
Variable Size 167775108 bytes
Database buffers 436207616 bytes
Redo buffers 7135232 bytes
The database has been loaded.
The database has been opened.
SQL>

SQL> show parameter control_files;

Name type value
-----------------------------------------------------------------------------
Control_files string c: \ oracle \ product \ 10.2.0 \ orada
Ta \ keymen \ control01.ctl
SQL>

Oracle Database starts from Oracle9i (including Oracle10g, of course). When the database is started, it will automatically search for spfile to read spfile.
For startup database. When the spfile file is lost or damaged, Oracle will find the pfile file to start the database.

If the pfile file is lost or damaged, the database cannot be started completely.

If you need to specify that the database uses the pfile file instead of the spfile file, you must
Startup open pfile = 'C: \ oracle \ product \ 10.2.0 \ admin \ keymen \ pfile \ init. ora ';

**************************************** **************************************** *

Experiment 2: delete all control files,
**************************************** **************************************** *
SQL> shutdown immediate;
The database has been closed.
The database has been detached.
The Oracle routine has been disabled.
SQL>

Delete all control files using operating system commands

SQL> startup
The Oracle routine has been started.

Total system global area 612368384 bytes
Fixed size 1250428 bytes
Variable Size 226495364 bytes
Database buffers 377487360 bytes
Redo buffers 7135232 bytes
ORA-00205 :?????????, ??????, ???????

SQL> alter session set nls_language = 'American ';

Session altered.

SQL> alter database Mount;
Alter database Mount
*
Error at line 1:
ORA-00205: Error in identifying control file, check Alert Log for more info

In the nomount status, use the SQL statement in the trace file of the control file to recreate the control file.
Currently, the database status only controls the loss of files, and log files are also used.

SQL> Create controlfile reuse Database "keymen" noresetlogs archivelog
2 maxlogfiles 16
3 maxlogmembers 3
4 maxdatafiles 100
5 maxinstances 8
6 maxloghistory 292
7. logfile
8 Group 1 'C: \ oracle \ product \ 10.2.0 \ oradata \ keymen \ redo01.log' size 50 m,
9 Group 2 'C: \ oracle \ product \ 10.2.0 \ oradata \ keymen \ redo02.log 'size 50 m,
10 group 3 'C: \ oracle \ product \ 10.2.0 \ oradata \ keymen \ redo03.log 'size 50 m
11 -- standby logfile
12 datafile
13 'C: \ oracle \ product \ 10.2.0 \ oradata \ keymen \ system01.dbf ',
14 'C: \ oracle \ product \ 10.2.0 \ oradata \ keymen \ undotbs01.dbf ',
15 'C: \ oracle \ product \ 10.2.0 \ oradata \ keymen \ sysaux01.dbf ',
16 'C: \ oracle \ product \ 10.2.0 \ oradata \ keymen \ users01.dbf ',
17 'C: \ oracle \ product \ 10.2.0 \ oradata \ keymen \ example01.dbf ',
18 'C: \ oracle \ product \ 10.2.0 \ oradata \ keymen \ testtbs1.dbf'
19 Character Set zhs16gbk
20;

Control File Created.

SQL> -- commands to re-create incarnation table
SQL> -- below log names must be changed to existing filenames on
SQL> -- disk. Any one log file from each branch can be used
SQL> -- re-create incarnation records.
SQL> -- alter database register logfile 'C: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ keymen \ Archiv
0_06 \ o1_mf_1_1 _ % u_.arc ';
SQL> -- alter database register logfile 'C: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ keymen \ Archiv
0_06 \ o1_mf_1_1 _ % u_.arc ';
SQL> -- alter database register logfile 'C: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ keymen \ Archiv
0_06 \ o1_mf_1_1 _ % u_.arc ';
SQL> -- alter database register logfile 'C: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ keymen \ Archiv
0_06 \ o1_mf_1_1 _ % u_.arc ';
SQL> -- alter database register logfile 'C: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ keymen \ Archiv
0_06 \ o1_mf_1_1 _ % u_.arc ';
SQL> -- rediscovery is required if any of the datafiles are restored backups,
SQL> -- or if the last shutdown was not normal or immediate.
SQL> recover Database
ORA-00283: Recovery session canceled due to errors
ORA-00264: no recovery required

SQL> -- all logs need archiving and a log switch is needed.
SQL> alter system archive log all;

System altered.

SQL> -- database can now be opened normally.
SQL> alter database open;

Database altered.

SQL> -- commands to add tempfiles to temporary tablespaces.
SQL> -- online tempfiles have complete space information.
SQL> -- Other tempfiles may require adjustment.
SQL> alter tablespace temp add tempfile 'C: \ oracle \ product \ 10.2.0 \ oradata \ keymen \ temp01.dbf'
2 size 20971520 reuse autoextend on next 655360 maxsize 32767 m;

Tablespace altered.

SQL> select open_mode from V $ database;

Open_mode
----------
Read Write

The control file is re-created, and the database status is intact. No data is lost and all log files are available.
Next, use RMAN to back up the entire database immediately.