停库前执行alter system checkpoint 很重要!

近期几次停一个库迁文件时, 发现srvctl stop instance xxx 停库后, 迁完文件在open时报

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 113 failed verification check
ORA-01110: data file 113: '+DATA2/isddw/datafile/isdh36m201006032.30232.732834105'
ORA-01207: file is more recent than control file - old control file


检查没有发现diskgroup有disk offline(以前遇到过有disk offline时,也报类似的错, online后recover成功),没有发现有datafile offline
尝试了以下几个方法均不行:
1) 直接recover database noparallel
2)尝试逐个使用单个的控制文件
3)尝试将所有节点的asm instance停掉,只启动节点1的asm, 再open 
4)尝试alter system check datafiles; 
5) alter database recover datafile 113 ? 
6)recover database using backup controlfile;
(controle_files参数仍是当前的控制文件) 发现不能自动寻找online redolog

7) 最后recreate control file解决,具体步骤见   
   . alter database backup controfile to trace
   . 修改trc, 重建controfile file
   . 执行recover database noparallel; auto
   . open db
   . 重建temp tbs, redo log file







这个错误是因cache中的信息没有及时写入controlfile导致, 避免的方法是在每次停库前,对DB作一次checkpoint, 即

ALTER SYSTEM CHECKPOINT;

要注意的是alter system switch logfile; 也会触发checkpoint,但只会对执行sql的那个节点,不会对RAC中的所有节点

checkpoint_clause

Specify CHECKPOINT to explicitly force Oracle Database to perform. a checkpoint, ensuring that all changes made by committed transactions are written to datafiles on disk. You can specify this clause only when your instance has the database open. Oracle Database does not return control to you until the checkpoint is complete.

GLOBAL In an Oracle Real Application Clusters (RAC) environment, this setting causes Oracle Database to perform. a checkpoint for all instances that have opened the database. This is the default.

LOCAL In an Oracle RAC environment, this setting causes Oracle Database to perform. a checkpoint only for the thread of redo log file groups for the instance from which you issue the statement.

Forcing a Checkpoint: Example The following statement forces a checkpoint:

ALTER SYSTEM CHECKPOINT;

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

转载于:http://blog.itpub.net/94384/viewspace-684442/