重建控制文件异常关闭数据库无法开打

jason_yh

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1274188 bytes
Variable Size             130027188 bytes
Database Buffers          310378496 bytes
Redo Buffers                2916352 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCLSTD" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/oracle/app/oradata/orclstd/redo01.log'  SIZE 50M,
  9    GROUP 2 '/oracle/app/oradata/orclstd/redo02.log'  SIZE 50M,
10    GROUP 3 '/oracle/app/oradata/orclstd/redo03.log'  SIZE 50M
11  -- STANDBY LOGFILE
12  DATAFILE
13    '/oracle/app/oradata/orclstd/system01.dbf',
14    '/oracle/app/oradata/orclstd/undotbs01.dbf',
15    '/oracle/app/oradata/orclstd/sysaux01.dbf',
16    '/oracle/app/oradata/orclstd/users01.dbf',
17    '/oracle/app/oradata/orclstd/example01.dbf'
18  CHARACTER SET ZHS16GBK;

Control file created.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery if it was restored from backup, or END BACKUP if it was not
ORA-01110: data file 1: '/oracle/app/oradata/orclstd/system01.dbf'

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 423513 generated at 08/16/2013 11:04:29 needed for thread 1
ORA-00289: suggestion : /oracle/arch/1_11_823596877.dbf
ORA-00280: change 423513 for thread 1 is in sequence #11


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/oracle/arch/1_11_823596877.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log '/oracle/arch/1_11_823596877.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/app/oradata/orclstd/system01.dbf

SQL> select group#, first_change#  from v$log where status='CURRENT' ;

    GROUP# FIRST_CHANGE#
---------- -------------
         2        423512

SQL> select  CHECKPOINT_CHANGE#  from v$database;

CHECKPOINT_CHANGE#
------------------
            423512

SQL> select file# , CHECKPOINT_CHANGE#, LAST_CHANGE# from v$datafile;

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
         1             423513
         2             423513
         3             423513
         4             423513
         5             423513

SQL> select file# , CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             423513
         2             423513
         3             423513
         4             423513
         5             423513

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/app/oradata/orclstd/system01.dbf'

下面是alter日志
CREATE CONTROLFILE REUSE DATABASE "ORCLSTD" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oracle/app/oradata/orclstd/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/app/oradata/orclstd/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/app/oradata/orclstd/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/app/oradata/orclstd/system01.dbf',
  '/oracle/app/oradata/orclstd/undotbs01.dbf',
  '/oracle/app/oradata/orclstd/sysaux01.dbf',
  '/oracle/app/oradata/orclstd/users01.dbf',
  '/oracle/app/oradata/orclstd/example01.dbf'
CHARACTER SET ZHS16GBK
Fri Aug 16 11:05:24 CST 2013
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
kcv_lh_or_upgrade: 10.2 upgrading 1 incarnations
Setting recovery target incarnation to 1
Fri Aug 16 11:05:25 CST 2013
Successful mount of redo thread 1, with mount id 751659508
Fri Aug 16 11:05:25 CST 2013
Completed: CREATE CONTROLFILE REUSE DATABASE "ORCLSTD" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oracle/app/oradata/orclstd/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/app/oradata/orclstd/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/app/oradata/orclstd/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/app/oradata/orclstd/system01.dbf',
  '/oracle/app/oradata/orclstd/undotbs01.dbf',
  '/oracle/app/oradata/orclstd/sysaux01.dbf',
  '/oracle/app/oradata/orclstd/users01.dbf',
  '/oracle/app/oradata/orclstd/example01.dbf'
CHARACTER SET ZHS16GBK
Fri Aug 16 11:05:29 CST 2013
alter database open
ORA-1113 signalled during: alter database open...
Fri Aug 16 11:07:01 CST 2013
ALTER DATABASE RECOVER  database using backup controlfile until cancel  
Fri Aug 16 11:07:01 CST 2013
Media Recovery Start
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 5 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
ORA-279 signalled during: ALTER DATABASE RECOVER  database using backup controlfile until cancel  ...
Fri Aug 16 11:07:03 CST 2013
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Fri Aug 16 11:07:03 CST 2013
Media Recovery Log /oracle/arch/1_11_823596877.dbf
Errors with log /oracle/arch/1_11_823596877.dbf
ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Fri Aug 16 11:07:03 CST 2013
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Fri Aug 16 11:07:03 CST 2013
Media Recovery Log /oracle/arch/1_11_823596877.dbf
Errors with log /oracle/arch/1_11_823596877.dbf
ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Fri Aug 16 11:07:03 CST 2013
ALTER DATABASE RECOVER CANCEL
ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ...
Fri Aug 16 11:07:10 CST 2013
alter database open
ORA-1589 signalled during: alter database open...
Fri Aug 16 11:07:24 CST 2013
alter database open noresetlogs
Fri Aug 16 11:07:24 CST 2013
ORA-1588 signalled during: alter database open noresetlogs...
Fri Aug 16 11:07:31 CST 2013
alter database open resetlogs
ORA-1194 signalled during: alter database open resetlogs...

求解

zhuang3088

我觉得问题出在这“CREATE CONTROLFILE REUSE DATABASE "ORCLSTD" NORESETLOGS  ARCHIVELOG”

Maclean Liu(刘相兵

SQL> recover database using backup controlfile until cancel;

===》

你需要输入的是 online redologfile的位置

jason_yh

可以了 谢谢刘大

wengtf

你模拟的场景redo log 还没丢，不必用归档来恢复。

xteitxu

LZ  你为什么要重建CONTROLFILE?损坏？

jason_yh

wengtf 发表于 2013-8-16 13:41
你模拟的场景redo log 还没丢，不必用归档来恢复。

是的 redo还在 没有丢失
有点想不明白我redo还在 为什么打开的时候还得用resetlogs

jason_yh

xteitxu 发表于 2013-8-16 14:26
LZ  你为什么要重建CONTROLFILE?损坏？

做个测试嘛  

woihzh

下次写redo位置就好了