SQL>
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL>
删除控制文件
1.通过查询control_files初始化参数,获取控制文件路径;
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>
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>
二、获取数据库名
首先生成文本格式的参数文件;
SQL>
SQL> create pfile from spfile;
File created.
SQL>
由于需要执行查询语句select userenv('language') from dual;来获取字符集,因此需要将数据库启动到nomount状态。
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>
四、获取数据文件名称
通过ls命令获取数据文件列表。
[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]$
五、生成创建控制文件脚本
这样,创建控制文件所需的基本信息都已经有了,我们来生成创建控制文件脚本。
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
;
六、重建控制文件
需要注意的是,在执行上述创建脚本时会报错,系统提示临时文件不属于数据文件,如下所示:
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>
修改脚本并重新执行,重建控制文件后,数据库会打开到mount状态。
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>
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.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL>
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
6 rows selected.
SQL>