測試把current redo log 和controlfile 删後恢復的問題

FelixTam

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

是這樣的
開始時庫是這樣的
redo log group 4,5,6
,我用
shutdown abort;
delete 了log group 4, 所有controlfile
startup nomount;

重建controlfile

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 5 'C:\ORACLE\REDO05.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 6 'C:\ORACLE\REDO06.LOG'  SIZE 50M BLOCKSIZE 512
DATAFILE
  'C:\ORACLE\WHOLE_DB_CP_42_T.BKP',
  'C:\ORACLE\WHOLE_DB_CP_43_T.BKP',
  'C:\ORACLE\WHOLE_DB_CP_47_T.BKP',
  'C:\ORACLE\WHOLE_DB_CP_44_T.BKP',
  'C:\ORACLE\WHOLE_DB_CP_46_T.BKP',
  'C:\ORACLE\ORADATA\UNDOTBS3.DBF'
CHARACTER SET AL32UTF8
;
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;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\WHOLE_DB_CP_42_T.BKP'


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


RMAN> recover database;

Starting recover at 06-FEB-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/06/2013 17:45:53
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-553: character set name is not recognized
我應如何繼續呢

Maclean Liu(刘相兵

recover database  until cancel using backup controlfile;

FelixTam

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1440460 generated at 02/06/2013 16:32:50 needed for thread 1
ORA-00289: suggestion : +data
ORA-00280: change 1440460 for thread 1 is in sequence #30

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '+data'
ORA-17503: ksfdopn:2 Failed to open file +data
ORA-15045: ASM file name '+data' is not in reference form

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: 'C:\ORACLE\WHOLE_DB_CP_42_T.BKP'
-----------------------------------------------------------------------RMAN> catalog db_recovery_file_dest;

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: +data/ORCL/spfileorcl.ora
File Name: +data/ORCL/FLASHBACK/log_1.309.806001405
File Name: +data/ORCL/CONTROLFILE/Current.261.805398403
File Name: +data/ORCL/CONTROLFILE/Current.260.805398403

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
no files cataloged

List of Files Which Where Not Cataloged
=======================================
File Name: +data/ORCL/spfileorcl.ora
  RMAN-07518: Reason: Foreign database file DBID: 0  Database Name:
File Name: +data/ORCL/FLASHBACK/log_1.309.806001405
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: +data/ORCL/CONTROLFILE/Current.261.805398403
  RMAN-07519: Reason: Error while cataloging. See alert.log.
File Name: +data/ORCL/CONTROLFILE/Current.260.805398403
  RMAN-07519: Reason: Error while cataloging. See alert.log
---------------------------------------------------------------------------
RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1860       CRITICAL OPEN      06-FEB-13     Control file needs media recovery
1762       CRITICAL OPEN      06-FEB-13     System datafile 1: 'C:\ORACLE\WHOLE_DB_CP_42_T.BKP' needs media recovery
8          HIGH     OPEN      06-FEB-13     One or more non-system datafiles need media recovery

analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete

Mandatory Manual Actions
========================
1. If you have the correct version of the control file, then shutdown the database and replace the old control file
2. If you restored the wrong version of data file C:\ORACLE\WHOLE_DB_CP_42_T.BKP, then replace it with the correct one
3. If you restored the wrong version of data file C:\ORACLE\WHOLE_DB_CP_43_T.BKP, then replace it with the correct one
4. If you restored the wrong version of data file C:\ORACLE\WHOLE_DB_CP_47_T.BKP, then replace it with the correct one
5. If you restored the wrong version of data file C:\ORACLE\WHOLE_DB_CP_44_T.BKP, then replace it with the correct one
6. If you restored the wrong version of data file C:\ORACLE\WHOLE_DB_CP_46_T.BKP, then replace it with the correct one
7. If you restored the wrong version of data file C:\ORACLE\ORADATA\UNDOTBS3.DBF, then replace it with the correct one
8. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair
-------------------------------------------------------------------------------------------------
覺得是由於controlfile 的SCN和其它DATAFILE的SCN不一致
現在的況好像是只有datafile 而redo,controlfile都不可用
想問如果只有datafile 的話,我可以把庫打開到datafile 最後的scn嗎??

NAME                                     CHECKPOINT_CHANGE#
---------------------------------------- ------------------
C:\ORACLE\WHOLE_DB_CP_42_T.BKP                      1440460
C:\ORACLE\WHOLE_DB_CP_43_T.BKP                      1440460
C:\ORACLE\WHOLE_DB_CP_47_T.BKP                      1440460
C:\ORACLE\WHOLE_DB_CP_44_T.BKP                      1440460
C:\ORACLE\WHOLE_DB_CP_46_T.BKP                      1440460
C:\ORACLE\ORADATA\UNDOTBS3.DBF                      1440460
或者至小可以把庫打開吧....有没有相關教學呢??
還有 的是實驗前無備份，所以備份集的控制文件不可用

FelixTam

C:\oracle\oradata>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Feb 7 00:14:53 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1333179138, not open)

RMAN> recover database until sequence 29;

Starting recover at 07-FEB-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK

starting media recovery
Oracle Error:
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: 'C:\ORACLE\WHOLE_DB_CP_42_T.BKP'

media recovery complete, elapsed time: 00:00:03

Finished recover at 07-FEB-13

RMAN> recover database;

Starting recover at 07-FEB-13
using channel ORA_DISK_1

starting media recovery

RMAN-08187: WARNING: media recovery until SCN 1440460 complete
Finished recover at 07-FEB-13

RMAN> list archivelog;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found ";": expecting one of: "all, from, high, like, low, scn, sequence, time, until, double-quoted-string, integer, single-
quoted-string"
RMAN-01007: at line 1 column 16 file: standard input

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
8       1    24      A 29-JAN-13
        Name: +DATA/orcl/archivelog/2013_02_05/thread_1_seq_24.262.806551185

1       1    25      A 05-FEB-13
        Name: +DATA/orcl/archivelog/2013_02_06/thread_1_seq_25.264.806689117

3       1    25      A 05-FEB-13
        Name: +DATA/orcl/archivelog/2013_02_06/thread_1_seq_25.274.806689821

2       1    26      A 06-FEB-13
        Name: +DATA/orcl/archivelog/2013_02_06/thread_1_seq_26.265.806689533

4       1    26      A 06-FEB-13
        Name: +DATA/orcl/archivelog/2013_02_06/thread_1_seq_26.269.806689821

5       1    27      A 06-FEB-13
        Name: +DATA/orcl/archivelog/2013_02_06/thread_1_seq_27.259.806689823

6       1    28      A 06-FEB-13
        Name: +DATA/orcl/archivelog/2013_02_06/thread_1_seq_28.258.806689933

7       1    29      A 06-FEB-13
        Name: +DATA/orcl/archivelog/2013_02_06/thread_1_seq_29.257.806689971


RMAN>

dbatravel

数据当前日志文件损坏
1、如果有备份，使用备份进行不完全恢复
2、添加隐藏参数就行强制性打开
     可以参考roger blog的文章
      http://www.killdb.com/2013/01/19/one-recover-case-2.html

wind

recover database until cancel using backup controlfile;

然后打cancel试一试。不完全恢复。

FelixTam

wind:
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1440465 generated at 02/07/2013 10:15:26 needed for thread 1
ORA-00289: suggestion : +data
ORA-00280: change 1440465 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
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: 'C:\ORACLE\WHOLE_DB_CP_42_T.BKP'


ORA-01112: media recovery not started

SQL>
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: 'C:\ORACLE\WHOLE_DB_CP_42_T.BKP'
是的,隱含參數是可以打開...但打開後就立刻自動關庫
隱含參數還是要相當功力呀~~我還是重新開新的庫再試吧

lunar

我也给两篇参考，老点儿，但是原理差不多：
http://blog.csdn.net/lunar2000/article/details/652798

http://blog.csdn.net/lunar2000/article/details/652773