Shulou(Shulou.com)06/01 Report--

The control file (control file) is a fairly small file (up to about 64m) that contains a directory of other files needed by Oracle. The parameter file tells the instance the location of the control file, and the control file tells the sample database and the location of the online redo log file. The control file also tells Oracle other things, such as information about the checkpoint that has occurred, the database name (which must match the db_name parameter), the timestamp when the database was created, the history of archiving the redo log (which sometimes makes the control file larger), RMAN information, and so on.

The control file should be saved through hardware (RAID), or through Oracle if mirroring is not supported. There should be more than one copy, and they should be saved on different disks to prevent the loss of control files in the event of a disk failure. Losing control files is not fatal, but it can make recovery much more difficult.

If all the control files are lost and there are no backups, we can open the database by rebuilding the control files. Where the rebuild control file requires at least the following information:

1. Database name

two。 Character set

3. Data file name

4. Initialization parameters, including MAXLOGFILES, MAXLOGMEMBERS, MAXDATAFILES, MAXINSTANCES, MAXLOGHISTORY, etc.

1. Environment prepares database version

We tested it in Oracle11g.

Click (here) to collapse or open

SQL >

SQL > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11. 2. . 3. 0-Production

PL / SQL Release 11. 2. . 3. 0-Production

CORE 11. 2. . 3. 0 Production

TNS for Linux: Version 11. 2. . 3. 0-Production

NLSRTL Version 11. 2. . 3. 0-Production

SQL >

Delete control file

1. Get the control file path by querying control_files initialization parameters

Click (here) to collapse or open

SQL >

SQL > show parameter control_files

NAME TYPE VALUE

-

Control_files string / u01 / app / oracle / oradata / HOEGH /

Control01. Ctl, / u01 / app / oracle

/ oradata / HOEGH / control02. Ctl

SQL >

two。 Then, use the rm command to delete the control file

Click (here) to collapse or open

[oracle @ HOEGH ~] $rm / U01 / app / oracle / oradata / HOEGH / control01. Ctl

[oracle @ HOEGH ~] $rm / U01 / app / oracle / oradata / HOEGH / control02. Ctl

[oracle @ HOEGH ~] $

3. At this point, force the database to shut down, then restart the database and report an ORA-00205 error. It should be noted that when the shutdown immediate command is executed at this time, the database cannot be shut down normally and can only be closed to the mounted state; you need to use the shutdown abort command to force the database to be shut down.

Click (here) to collapse or open

SQL >

SQL > shutdown immediate

Database closed.

ORA-00210: cannot open the specified control file

ORA-00202: control file:\'/ u01/app/oracle/oradata/HOEGH/control01.ctl\'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SQL > select status from v$instance

STATUS

-

MOUNTED

SQL >

SQL > shutdown abort

ORACLE instance shut down.

SQL >

SQL >

SQL > startup

ORACLE instance started.

Total System Global Area 941600768 bytes

Fixed Size 1348860 bytes

Variable Size 515902212 bytes

Database Buffers 419430400 bytes

Redo Buffers 4919296 bytes

ORA-00205: error in identifying control file, check alert log for more info

SQL >

two。 Get the database name

First generate a parameter file in text format

Click (here) to collapse or open

SQL >

SQL > create pfile from spfile

File created.

SQL >

Open the parameter file and view the db_name parameter value, which is the database name.

Click (here) to collapse or open

[oracle @ hoegh dbs] $cat initHOEGH. Ora

HOEGH. _ _ db_cache_size = 419430400

HOEGH. _ _ java_pool_size = 4194304

HOEGH. _ _ large_pool_size = 4194304

HOEGH. _ _ oracle_base =\'/ u01/app/oracle\'# ORACLE_BASE set from environment

HOEGH. _ _ pga_aggregate_target = 377487360

HOEGH. _ _ sga_target = 566231040

HOEGH. _ _ shared_io_pool_size =

HOEGH. _ _ shared_pool_size = 130023424

HOEGH. _ _ streams_pool_size =

*. Audit_file_dest =\'/ u01/app/oracle/admin/HOEGH/adump\'

*. Audit_trail =\'db\'

*. Compatible =\ '11.2.0.0.0\'

*. Control_files =\'/ u01/app/oracle/oradata/HOEGH/control01.ctl\',\'/ u01/app/oracle/oradata/HOEGH/control02.ctl\'

*. Db_block_size = 8192

*. Db_domain =\'\'

*. Db_name =\ 'HOEGH\'

*. Diagnostic_dest =\'/ u01/app/oracle\'

*. Dispatchers =\'(PROTOCOL=TCP) (SERVICE=HOEGHXDB)\'

*. Memory_max_target = 943718400

*. Memory_target = 943718400

*. Open_cursors = 300

*. Processes = 150

*. Remote_login_passwordfile =\ 'EXCLUSIVE\'

*. Undo_tablespace =\ 'UNDOTBS1\'

[oracle @ hoegh dbs] $

3. Boot to nomount state and get character set

Since you need to execute the query statement select userenv ('language') from dual; to get the character set, you need to start the database to the nomount state.

Click (here) to collapse or open

SQL >

SQL > startup nomount

ORACLE instance started.

Total System Global Area 941600768 bytes

Fixed Size 1348860 bytes

Variable Size 515902212 bytes

Database Buffers 419430400 bytes

Redo Buffers 4919296 bytes

SQL >

SQL > select userenv (\ 'language\') from dual

USERENV (\ 'LANGUAGE\')

AMERICAN_AMERICA. US7ASCII

SQL >

SQL >

4. Get the data file name

Get a list of data files through the ls command.

Click (here) to collapse or open

[oracle @ hoegh HOEGH] $ls-lh

Total 1. 8G

-rw-r-1 oracle oinstall 314M May 30 11:07 example01.dbf

-rw-r-1 oracle oinstall 51m May 30 11:07 redo01.log

-rw-r-1 oracle oinstall 51m May 30 11:07 redo02.log

-rw-r-1 oracle oinstall 51m May 30 11:07 redo03.log

-rw-r-1 oracle oinstall 541M May 30 11:07 sysaux01.dbf

-rw-r-1 oracle oinstall 721m May 30 11:07 system01.dbf

-rw-r-1 oracle oinstall 30m Oct 13 2014 temp01.dbf

-rw-r-1 oracle oinstall 96m May 30 11:07 undotbs01.dbf

-rw-r-1 oracle oinstall 5.1m May 30 11:07 users01.dbf

[oracle @ hoegh HOEGH] $

5. Generate a script to create a control file

In this way, you already have the basic information needed to create the control file, so let's generate the script to create the control file.

Click (here) to collapse or open

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE\ "HOEGH\" NORESETLOGS ARCHIVELOG

MAXLOGFILES 5

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 226

LOGFILE

GROUP 1\'/ u01/app/oracle/oradata/HOEGH/redo01.log\ 'SIZE 50m

GROUP 2\'/ u01/app/oracle/oradata/HOEGH/redo02.log\ 'SIZE 50m

GROUP 3\'/ u01/app/oracle/oradata/HOEGH/redo03.log\ 'SIZE 50m

DATAFILE

\'/ u01/app/oracle/oradata/HOEGH/system01.dbf\'

\'/ u01/app/oracle/oradata/HOEGH/sysaux01.dbf\'

\'/ u01/app/oracle/oradata/HOEGH/undotbs01.dbf\'

\'/ u01/app/oracle/oradata/HOEGH/users01.dbf\'

\'/ u01/app/oracle/oradata/HOEGH/example01.dbf\'

\'/ u01/app/oracle/oradata/HOEGH/temp01.dbf\'

CHARACTER SET US7ASCII

6. Rebuild control file

It should be noted that an error is reported when executing the above creation script, and the system prompts that the temporary file does not belong to the data file, as shown below:

Click (here) to collapse or open

SQL > @ / U01 / app / oracle / oradata / HOEGH / CreateControlFile. Sql

ORA-01081: cannot start already-running ORACLE-shut it down first

CREATE CONTROLFILE REUSE DATABASE\ "HOEGH\" NORESETLOGS ARCHIVELOG

*

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-01160: file is not a data file

ORA-01110: data file:\'/ u01/app/oracle/oradata/HOEGH/temp01.dbf\'

SQL >

Modify the script and re-execute it, and after rebuilding the control file, the database opens to the mount state.

Click (here) to collapse or open

SQL >

SQL > @ / U01 / app / oracle / oradata / HOEGH / CreateControlFile. Sql

ORACLE instance started.

Total System Global Area 941600768 bytes

Fixed Size 1348860 bytes

Variable Size 515902212 bytes

Database Buffers 419430400 bytes

Redo Buffers 4919296 bytes

Control file created.

SQL >

SQL > select status from v$instance

STATUS

-

MOUNTED

SQL >

7. Open the database

When opening the database, an error will be reported, indicating that the system01 data file needs to perform media recovery, and we can perform recover database.

Click (here) to collapse or open

SQL >

SQL > alater database open

SP2-0734: unknown command beginning\ "alater dat...\"-rest of line ignored.

SQL >

SQL > alter database open

Alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1:\'/ u01/app/oracle/oradata/HOEGH/system01.dbf\'

SQL >

SQL > recover database

Media recovery complete.

SQL >

SQL > alter database open

Database altered.

SQL >

SQL > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11. 2. . 3. 0-Production

PL / SQL Release 11. 2. . 3. 0-Production

CORE 11. 2. . 3. 0 Production

TNS for Linux: Version 11. 2. . 3. 0-Production

NLSRTL Version 11. 2. . 3. 0-Production

SQL >

SQL > select tablespace_name from dba_tablespaces; TABLESPACE_NAME

-

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

EXAMPLE 6 rows selected. SQL >

8. Summary

Here is a summary of the steps to rebuild the control file:

1. Get the database name

two。 Get character set name

3. Get data file name

4. Rebuild control file

5. Perform media recovery

6. Open the database.

Hoegh

15.05.30

-- The End--

From "ITPUB blog", link: http://blog.itpub.net/30162081/viewspace-1677995/, if you need to reprint, please indicate the source, otherwise legal liability will be investigated.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.