ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-01207(01991)

ORA-01207(01991)

原创 Linux操作系统 作者:pangzi913 时间:2008-11-22 18:45:57 0 删除 编辑

ORA-01207(01991)

故障现象:

ORA-01122: 数据库文件 1 验证失败

ORA-01110: 数据文件 1:

'F:ORACLEPRODUCT10.2.0DB_1ORADATAORCLSYSTEM01.DBF'

ORA-01207: 文件比控制文件更新 - 旧的控制文件

ORA-01207:文件比控制文件更新 - 旧的控制文件


这个错误是oracle db最常见的错误了,引起的原因很多,但是最主要的一个原因是数据库服务器突然掉电,然后重启启动数据库报错。

究其这个问题产生的原因,其实就是控制文件中记录的db信息太老,导致数据库在启动检测时出现不一致。

控制文件中记录了整个数据库的全部信息,具体包括数据文件的,日志文件等等。

那么为什么会出现控制文件记录的数据库信息太老呢,原因很简单:根据oracle db运行原理,数据库在运行期间,由于检查点发生等原因会不断的更新控制文件,同时数据库在关闭和重启过程中都会更新控制文件的内容,但是数据库服务器突然的掉电,会导致当前的db信息无法适时更新到控制文件中,再次启动数据库后,当oracle检测控制文件和其它文件信息是否一致时,就出现了这个错误。

我做这个实验的方法是:1、先备份数据库,关闭后备份的(备份1)。2、打开数据库,在里面建表。3、备份修改后的数据库(备份2)。4、删除数据库所有文件。5、使用备份1的控制文件,使用备份2的日志文件和数据问题。系统一定会报ORA-01207这个错。

参考:ORA-01207: old control file完全解决方案

http://www.ixdba.net/article/da/363.html

关于Oracle 01122,01110,01207的错误和解决

http://percywang.itpub.net/post/25150/241574

orapwd命令中entries参数的作用

http://publish.it168.com/2006/04 ... l?positioncode=1545


根据oracle文档的解释:

Cause: The control file change sequence number in the datafile is greater than the number in the control file. This implies that the wrong control file is being used. Note that repeatedly causing this error can make it stop happening without correcting the real problem. Every attempt to open the database will advance the control file change sequence number until it is great enough.

Action: Use the current control file or do BACKUP CONTROLFILE RECOVERY to make the control file current. Be sure to follow all restrictions on doing a BACKUP CONTROLFILE RECOVERY.

Solution:

1. Check alert file,kill monitor process

2. Startup mount,

3. “alter database backup control to trace”

4. Find the trace file,and open it

5. Startup nomount

6. recreate controlfile using resetlogs

7. “recover database using backup controlfile”

8. “alter database open resetlogs;”

9. “alter tablespace temp add tempfile '.dbf' reuse;”





详细解决方法:

1.SQL>shutdown abort 如果数据库是打开状态,强行关闭

2.SQL>conn sys as sysdba

3. SQL> startup

ORACLE instance started.



Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 145750508 bytes

Database Buffers 25165824 bytes

Redo Buffers 262144 bytes

Database mounted.

ORA-01122: database file 1 failed verification check

ORA-01110: data file 1:

'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPESYSTEM01.DBF'

ORA-01207: file is more recent than controlfile - old controlfile

4.SQL>alter database backup controlfile to trace as 'c:aa.sql';

数据库已更改。

;

5.SQL>shutdown immediate 如果数据库是打开状态,则关闭

6.SQL>startup nomount;

生成的f:aa文件;

其实在这个文件中的已经告诉你咋样恢复你的数据库了,找到STARTUP NOMOUNT字样,然后下面可以看到类似语句,这个文件有好几个类似的生成控制文件语句,主要针对不懂的环境执行不同的语句,象我的数据库没有做任何备份,也不是在归档模式,就执行这句(可以单独执行下面的语句,也可以保存为sql文件,注意中间不能有空行)

--STARTUP NOMOUNT不执行

CREATE CONTROLFILE REUSE DATABASE "ORCLTAPE" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 454

LOGFILE

GROUP 1 'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEREDO01.LOG' SIZE 10M,

GROUP 2 'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEREDO02.LOG' SIZE 10M,

GROUP 3 'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEREDO03.LOG' SIZE 10M

-- STANDBY LOGFILE



DATAFILE

'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPESYSTEM01.DBF',

'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEUNDOTBS01.DBF',

'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPESYSAUX01.DBF',

'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEUSERS01.DBF'

CHARACTER SET ZHS16GBK

7.SQL>RECOVER DATABASE (恢复指定表空间、数据文件或整个数据库)

我没有执行这步。

8.SQL>ALTER DATABASE OPEN 打开数据库

执行alter database open resetlogs;



在另次实验中,报如下错,需要重建密码文件:

ORACLE instance started.

Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 145750508 bytes

Database Buffers 25165824 bytes

Redo Buffers 262144 bytes

ORA-01991: invalid password file

'D:oracleproduct10.1.0Db_1DATABASEPWDorcltape.ORA'

SQL>host

C:Documents and SettingsAdministrator>orapwd file=d:oracleproduct10.1.0Db_

1DATABASEPWDorcltape.ORA password=oracle entries=10

创建PASSWORD文件内容。两次必须参数 file 与password 。entries表示拥有sys和system权限的用户个数。

Exit 输入后回到SQL提示符界面。

SQL>

SQL> @c:gh.sql(sql中含有空格,报错)

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

SP2-0042: unknown command "DATAFILE" - rest of line ignored.

SP2-0734: unknown command beginning "'D:ORACLE..." - rest of line ignored.

SP2-0734: unknown command beginning "'D:ORACLE..." - rest of line ignored.

SP2-0734: unknown command beginning "'D:ORACLE..." - rest of line ignored.

SP2-0044: For a list of known commands enter HELP

and to leave enter EXIT.

SP2-0734: unknown command beginning "'D:ORACLE..." - rest of line ignored.

SP2-0734: unknown command beginning "CHARACTER ..." - rest of line ignored.

1 CREATE CONTROLFILE REUSE DATABASE "ORCLTAPE" NORESETLOGS ARCHIVELOG

2 MAXLOGFILES 16

3 MAXLOGMEMBERS 3

4 MAXDATAFILES 100

5 MAXINSTANCES 8

6 MAXLOGHISTORY 454

7 LOGFILE

8 GROUP 1 'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEREDO01.LOG' SIZE 10M,



9 GROUP 2 'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEREDO02.LOG' SIZE 10M,



10 GROUP 3 'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEREDO03.LOG' SIZE 10M

11* -- STANDBY LOGFILE

出现上面的报错是由于,下面的命令中有空格行,把红色字和空行删除:

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCLTAPE" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 454

LOGFILE

GROUP 1 'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEREDO01.LOG' SIZE 10M,

GROUP 2 'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEREDO02.LOG' SIZE 10M,

GROUP 3 'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEREDO03.LOG' SIZE 10M

-- STANDBY LOGFILE



DATAFILE

'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPESYSTEM01.DBF',

'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEUNDOTBS01.DBF',

'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPESYSAUX01.DBF',

'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEUSERS01.DBF'

CHARACTER SET ZHS16GBK

;



SQL> @c:gh.sql -- Set #1. NORESETLOGS case


Control file created.

把空格删除后,执行命令成功。


SQL> @c:kill.sql --Set #2. RESETLOGS case

Control file created.

SQL> alter database open;

alter database open

*ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open



SQL> alter database open resetlogs;

Database altered.

alter database backup controlfile to trace as 'c:aa.sql'; 生成的文件内容如下:

-- The following are current System-scope REDO Log Archival related

-- parameters and can be included in the database initialization file.

--

-- LOG_ARCHIVE_DEST=''

-- LOG_ARCHIVE_DUPLEX_DEST=''

--

-- LOG_ARCHIVE_FORMAT=ARC%S_%R.%T

--

-- DB_UNIQUE_NAME="orcltape"

--

-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'

-- LOG_ARCHIVE_MAX_PROCESSES=2

-- STANDBY_FILE_MANAGEMENT=MANUAL

-- STANDBY_ARCHIVE_DEST=%ORACLE_HOME%RDBMS

-- FAL_CLIENT=''

-- FAL_SERVER=''

--

-- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'

-- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY'

-- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'

-- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY'

-- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'

-- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'

-- LOG_ARCHIVE_DEST_STATE_10=ENABLE



--

-- Below are two sets of SQL statements, each of which creates a new

-- control file and uses it to open the database. The first set opens

-- the database with the NORESETLOGS option and should be used only if

-- the current versions of all online logs are available. The second

-- set opens the database with the RESETLOGS option and should be used

-- if online logs are unavailable.

-- The appropriate set of statements can be copied from the trace into

-- a script. file, edited as necessary, and executed when there is a

-- need to re-create the control file.

--

-- Set #1. NORESETLOGS case

--

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- Additional logs may be required for media recovery of offline

-- Use this only if the current versions of all online logs are

-- available.



-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE



STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCLTAPE" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 454

LOGFILE

GROUP 1 'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEREDO01.LOG' SIZE 10M,

GROUP 2 'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEREDO02.LOG' SIZE 10M,

GROUP 3 'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEREDO03.LOG' SIZE 10M

-- STANDBY LOGFILE



DATAFILE

'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPESYSTEM01.DBF',

'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEUNDOTBS01.DBF',

'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPESYSAUX01.DBF',

'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEUSERS01.DBF'

CHARACTER SET ZHS16GBK

;



-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE 'D:ORACLEPRODUCT10.1.0FLASH_RECOVERY_AREAORCLTAPEARCHIVELOG2008_05_13O1_MF_1_1_%U_.ARC';

-- ALTER DATABASE REGISTER LOGFILE 'D:ORACLEPRODUCT10.1.0FLASH_RECOVERY_AREAORCLTAPEARCHIVELOG2008_05_13O1_MF_1_1_%U_.ARC';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE



-- All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;



-- Database can now be opened normally.

ALTER DATABASE OPEN;



-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE 'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPETEMP01.DBF' REUSE;

-- End of tempfile additions.

--

-- Set #2. RESETLOGS case

--

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- The contents of online logs will be lost and all backups will

-- be invalidated. Use this only if online logs are damaged.



-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE



STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCLTAPE" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 454

LOGFILE

GROUP 1 'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEREDO01.LOG' SIZE 10M,

GROUP 2 'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEREDO02.LOG' SIZE 10M,

GROUP 3 'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEREDO03.LOG' SIZE 10M

-- STANDBY LOGFILE



DATAFILE

'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPESYSTEM01.DBF',

'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEUNDOTBS01.DBF',

'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPESYSAUX01.DBF',

'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPEUSERS01.DBF'

CHARACTER SET ZHS16GBK

;



-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE 'D:ORACLEPRODUCT10.1.0FLASH_RECOVERY_AREAORCLTAPEARCHIVELOG2008_05_13O1_MF_1_1_%U_.ARC';

-- ALTER DATABASE REGISTER LOGFILE 'D:ORACLEPRODUCT10.1.0FLASH_RECOVERY_AREAORCLTAPEARCHIVELOG2008_05_13O1_MF_1_1_%U_.ARC';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE USING BACKUP CONTROLFILE



-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;



-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE 'D:ORACLEPRODUCT10.1.0ORADATAORCLTAPETEMP01.DBF' REUSE;

-- End of tempfile additions.

--







方法2:

模拟ORA-01207错误很简单,不再描述:



u 思路:用旧的控制文件恢复,最后用resetlogs打开数据库。

u 具体步骤:

1:startup mount;

2:recover database using backup controlfile until cancel;

然后根据情况,指定archive log和redo file。(需要 有归档日志文件)

3:alter database open resetlogs;

由于我这个测试环境,还没归档日志文件的备份。所以利用第一次备份的日志文件,是恢复不了数据库的。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7633405/viewspace-501442/,如需转载,请注明出处,否则将追究法律责任。

下一篇: office 2007激活
请登录后发表评论 登录
全部评论

注册时间:2008-11-22

  • 博文量
    40
  • 访问量
    63322