oracle 11.2.0.4
参考以下文档:
How to Recover from errors ORA-01171 ORA-01122ORA-01251 ORA-01186 (文档 ID 333620.1)
OERR: ORA-1178"file %s created before last CREATE CONTROLFILE, cannot recreate"Reference Note (文档 ID 18751.1)
-------------------------------------------------------------------------- Cause: Attempted to use ALTER DATABASE CREATE DATAFILE to recreate a datafile that existed at the last CREATE CONTROLFILE command. The information needed to recreate the file was lost with the control file that existed when the file was added to the database. Action: Find a backup of the file, and recover it. Do incomplete recovery to time before file was originally created.
select controlfile_created from v$database; select creation_time from v$datafile where name = 'K:\ORAREP92\ORADATA\ORCL10G\USERS01.DBF';如果creatio_time小于controlfile_created时间,那么就会在使用alter database create datafile 时报错ora-01178
此时如果该数据文件有备份则可以将备份的数据文件来做恢复。
例外的现象也可以参考以下链接:
使用bbed解决ORA-01178 file Ncreated before last CREATE CONTROLFILE, cannot recreate
http://www.xifenfei.com/2013/07/%E4%BD%BF%E7%94%A8bbed%E8%A7%A3%E5%86%B3ora-01178-file-n-created-before-last-create-controlfile-cannot-recreate.html
如果恢复数据文件至原文件不同的地方,则需要使用alter database rename file 来重命名文件,再执行recover
如果数据库处于read only状态会出现ora-01511,参考以下文档可解决
ORA-01511:error in renaming log/data files ORA-01121: cannot rename data file – file isin use or recovery ORA-01110: data file :‘datafile.dbf’
ORA-01511: error in renaming log/data files ORA-01121: cannot rename data file – file is in use or recovery ORA-01110: data file <string>:‘datafile.dbf’
To Move the datafile orrename the datafile, follow this Steps And also related with theabove mentioned Errors
1. Loginto SQLPlus.
2. Connectas SYS DBA with
CONNECT / AS SYSDBA
3. Shutdowndatabase with
SHUTDOWN
4. Renameor/and move the datafiles at operating system level.
5. StartOracle database in mount state with
STARTUP MOUNT
6. Modifythe name or location of datafiles in Oracle dictionary using followingcommand syntax:
ALTER DATABASE RENAME FILE ‘<fully qualified path to originaldata file name>’ TO ‘<new or original fully qualified path to new ororiginal data file name>’;
7. OpenOracle database instance completely with
ALTER DATABASE OPEN
If the datafiles that need to be changed or moved do not belongto SYSTEM tablespaces, and do not contain active rollback segments or temporarysegments, there is another workaround that does not require database instanceto be shutdown. Instead, only the particular tablespace that contains the datefiles is taken offline.
1. Loginto SQLPlus.
2. Connectas SYS DBA with
CONNECT / AS SYSDBA .
3. Makeoffline the affected tablespace with
ALTER TABLESPACE <tablespace name> OFFLINE;
4. Modifythe name or location of datafiles in Oracle data dictionary using following syntax:
ALTER TABLESPACE <tablespace name> RENAME DATAFILE‘<fully qualified path to original data file name>’ TO ‘<new ororiginal fully qualified path to new or original data file name>’;
5. Bringthe tablespace online again with
ALTER TABLESPACE alter tablespace <tablespacename> ONLINE;