由于客户需要用真实的数据测试，所以我一开始作了rman全数据库备份，包括日志归档的备份，但是最后的时候，日志备份出现了备份失败的错误，不过没有关系，我可以只要恢复到rman备份的时刻就可以了，共112g
RMAN>run{
1>allocate channel c1 type disk;
2>allocate channel c2 type disk;
3>backp
4> format = '/oracle/r_back/%d_%s_%t'
5>(database);
6>backup archivelog all;
7>release channel c1;
8>release channel c2;
9>}
后来备份完成以后,在前天恢复的
RMAN> sql 'alter session set nls_date_format="dd mm yyyy hh24:mi:ss"';
using target database controlfile instead of recovery catalog
sql statement: alter session set nls_date_format="dd mm yyyy hh24:mi:ss"
RMAN>run{
1>allocae channel c1 type disk;
2>allocae channel c2 type disk;
3>allocae channel c3 type disk;
4>allocae channel c4 type disk;
5>allocae channel c5 type disk;
6>allocae channel c6 type disk;
7>allocae channel c7 type disk;
8>set until time '16 10 2006 09:00:00';
9>restore database;
10>recover database;
11>release channel c1;
12>release channel c2;
13>release channel c3;
14>release channel c4;
15>release channel c5;
16>release channel c6;
17>release channel c7;
18>}
恢复完成，但是最后的时候,提示日志没有恢复成功,这个无所谓的,因为我没有当时需要恢复日志的要求,
后来,就开始打开了,
可是打开的时候出现了问题
$ sqlplus
SQL*Plus: Release 9.2.0.1.0 - Production on Sun Oct 22 09:17:16 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
错了呵呵
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/MOSS/system01.dbf'
system01.dbf数据文件需要介质恢复,于是试探一下恢复整个数据库,因为提示这个问题的话,可能所有的数据文件都需要介质恢复
SQL> recover database;
ORA-00279: change 8998462319508 generated at 10/17/2006 09:17:27 needed for
thread 1
ORA-00289: suggestion : /oracle/products/9.2/dbs/arch1_12671.dbf
ORA-00280: change 8998462319508 for thread 1 is in sequence #12671
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/oracle/products/9.2/dbs/arch1_12671.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/oracle/products/9.2/dbs/arch1_12671.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
SQL> recover database;
ORA-00279: change 8998462319508 generated at 10/17/2006 09:17:27 needed for
thread 1
ORA-00289: suggestion : /oracle/products/9.2/dbs/arch1_12671.dbf
ORA-00280: change 8998462319508 for thread 1 is in sequence #12671
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
糟糕,原来的一些自动备份的跟踪文件跟删掉了,晕....
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/MOSS/system01.dbf'
介质恢复一下数据文件吧,可能也会找不到跟踪文件
SQL>
SQL> conn / as sysdba
Connected.
SQL> recover database datafile '/oracle/oradata/MOSS/system01.dbf';
ORA-00274: illegal recovery option DATAFILE
SQL> recover datafile '/oracle/oradata/MOSS/system01.dbf';
ORA-00279: change 8998462319509 generated at 10/17/2006 09:17:27 needed for
thread 1
ORA-00289: suggestion : /oracle/products/9.2/dbs/arch1_12671.dbf
ORA-00280: change 8998462319509 for thread 1 is in sequence #12671
Specify log: {=suggested | filename | AUTO | CANCEL}
auot
ORA-00308: cannot open archived log 'auot'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/oracle/products/9.2/dbs/arch1_12671.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/oracle/products/9.2/dbs/arch1_12671.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
晕,真的找不到了,怎么办呢,先备份一下控制文件
SQL> alter database backup controlfile to trace;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/MOSS/system01.dbf'
SQL> recover datafile '/oracle/oradata/MOSS/system01.dbf'
ORA-00279: change 8998462319509 generated at 10/17/2006 09:17:27 needed for
thread 1
ORA-00289: suggestion : /oracle/products/9.2/dbs/arch1_12671.dbf
ORA-00280: change 8998462319509 for thread 1 is in sequence #12671
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> recovery database until cancel;
SP2-0734: unknown command beginning "recovery d..." - rest of line ignored.
SQL> l
1* alter database open
SQL> recover database until cancel;
ORA-00279: change 8998462319508 generated at 10/17/2006 09:17:27 needed for
thread 1
ORA-00289: suggestion : /oracle/products/9.2/dbs/arch1_12671.dbf
ORA-00280: change 8998462319508 for thread 1 is in sequence #12671
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/oracle/products/9.2/dbs/arch1_12671.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/oracle/products/9.2/dbs/arch1_12671.dbf'
ORA-27037: unable to obtain file status
SVR4 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/oradata/MOSS/system01.dbf'
怎么办呢,文件都丢失了,那不就惨了,难道还需要把冷备份的数据,覆盖回去么?不行,这个阶段找不到跟踪文件是日志里面存在记录,于是,决定清空日志
SQL>
^H^H^H^H^H00308
SP2-0042: unknown comm00308" - rest of line ignored.
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> l
1* alter database clear unarchived logfile group 1
SQL> c /1/2
1* alter database clear unarchived logfile group 2
SQL> /
Database altered.
SQL> l
1* alter database clear unarchived logfile group 2
SQL> c /2/3
1* alter database clear unarchived logfile group 3
SQL> /
Database altered.
SQL> l
1* alter database clear unarchived logfile group 3
SQL> c /3/4
1* alter database clear unarchived logfile group 4
SQL> /
Database altered.
SQL> l
1* alter database clear unarchived logfile group 4
SQL> c /4/5
1* alter database clear unarchived logfile group 5
SQL> /
Database altered.
SQL> l
1* alter database clear unarchived logfile group 5
SQL> c /5/6
1* alter database clear unarchived logfile group 6
SQL> /
Database altered.
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: '/oracle/oradata/MOSS/system01.dbf'
不好意思,还没有介质恢复数据文件,呵呵.........
SQL> recover datafile '/oracle/oradata/MOSS/system01.dbf'
Media recovery complete.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/oracle/oradata/MOSS/undotbs01.dbf'
SQL> recover datafile '/oracle/oradata/MOSS/undotbs01.dbf'
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: '/oracle/oradata/MOSS/cwmlite01.dbf'
SQL> recover datafile '/oracle/oradata/MOSS/cwmlite01.dbf'
Media recovery complete.
SQL> alter database open
2 ;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/oracle/oradata/MOSS/drsys01.dbf'
SQL> recover datafile '/oracle/oradata/MOSS/drsys01.dbf'
Media recovery complete.
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/oracle/oradata/MOSS/example01.dbf'
SQL>
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/oracle/oradata/MOSS/indx01.dbf'
SQL> recover datafile 6;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/oracle/oradata/MOSS/odm01.dbf'
SQL>
SQL> recover datafile 7;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/oracle/oradata/MOSS/tools01.dbf'
SQL> recover datafile 8;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: '/oracle/oradata/MOSS/users01.dbf'
SQL> recover datafile
9;
SP2-0226: Invalid line number
SQL> recover datafile 9;
Media recovery complete.
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 10 needs media recovery
ORA-01110: data file 10: '/oracle/oradata/MOSS/xdb01.dbf'
SQL> recover datafile 10;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 11 needs media recovery
ORA-01110: data file 11: '/oracle/oradata/MOSS/MOSS.dbf'
SQL> alter datafile 11;
alter datafile 11
*
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> recover datafile 11;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 12 needs media recovery
ORA-01110: data file 12: '/oracle/oradata/MOSS/rman_ts.dbf'
SQL> recover datafile 12;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 13 needs media recovery
ORA-01110: data file 13: '/oracle/oradata/MOSS/MOSS_CC1.dbf'
SQL> recover datafile 13;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 14 needs media recovery
ORA-01110: data file 14: '/oracle/oradata/MOSS/MOSS_INDEX.dbf'
SQL> alter datafile 14;
alter datafile 14
*
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> recover datafile 14;
Media recovery complete.
SQL> recover datafile 15;
Media recovery complete.
SQL> recover datafile 16;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 17 needs media recovery
ORA-01110: data file 17: '/oracle/oradata/MOSS/MOSS_SP1.dbf'
SQL> recover datafile 17;
Media recovery complete.
SQL> recover datafile 18;
Media recovery complete.
SQL> recover datafile 19;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 20 needs media recovery
ORA-01110: data file 20: '/oracle/oradata/MOSS/MOSS_LY1.dbf'
SQL> recover datafile 20;
Media recovery complete.
SQL> recover datafile 21;
Media recovery complete.
SQL> recover datafile 22;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 23 needs media recovery
ORA-01110: data file 23: '/oracle/oradata/MOSS/perfstat.dbf'
SQL> recover datafile 23;
Media recovery complete.
SQL> recover datafile 24;
ORA-01179: file 24 does not exist
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 25 needs media recovery
ORA-01110: data file 25: '/oracle/oradata/MOSS/BILL_.dbf'
SQL> recover datafile 25;
Media recovery complete.
SQL> recover datafile 26;
ORA-01179: file 26 does not exist
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 27 needs media recovery
ORA-01110: data file 27: '/oracle/oradata/MOSS/MOSS_MONTH1.dbf'
SQL> recover datafile 27;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 28 needs media recovery
ORA-01110: data file 28: '/oracle/oradata/MOSS/MOSS_MONTH2.dbf'
SQL> recover datafile 28;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 29 needs media recovery
ORA-01110: data file 29: '/oracle/oradata/MOSS/MOSS_MONTH3.dbf'
SQL> recover datafile 29;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 30 needs media recovery
ORA-01110: data file 30: '/oracle/oradata/MOSS/IBAS_DATA.dbf'
SQL> recover datafile 30;
Media recovery complete.
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 31 needs media recovery
ORA-01110: data file 31: '/oracle/oradata/MOSS/MOSS_MONTH4.dbf'
SQL> recover datafile 31;
Media recovery complete.
SQL> recover datafile 32;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 33 needs media recovery
ORA-01110: data file 33: '/oracle/oradata/MOSS/MOSS_MONTH6.dbf'
SQL> recover datafile 33;
Media recovery complete.
SQL> recover datafile 34;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 35 needs media recovery
ORA-01110: data file 35: '/oracle/oradata/MOSS/MOSS_MONTH8.dbf'
SQL> recover datafile 35;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 36 needs media recovery
ORA-01110: data file 36: '/oracle/oradata/MOSS/MOSS_MONTH9.dbf'
SQL> recover datafile 36;
Media recovery complete.
SQL> recover datafile 37;
Media recovery complete.
SQL> recover datafile 38;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 39 needs media recovery
ORA-01110: data file 39: '/oracle/oradata/MOSS/MOSS_MONTH12.dbf'
SQL> recover datafile 39;
Media recovery complete.
SQL> recover datafile 40;
ORA-01179: file 40 does not exist
SQL> alter database open;
Database altered.
SQL>
ok 问题解决,三个小时已经过去了,呵呵.............
马上联系开发人员,确定数据没有出错,马上rman备份了一次,算是放心了
