DBA工作备忘录之三:rman备份,没用catalog ,但控制文件丢失,怎么解决?

mugen

Switch of datafile 28 complete to datafile copy /test/test/dbfile28
  checkpoint is 608609130
Switch of datafile 29 complete to datafile copy /test/test/dbfile29
  checkpoint is 608609130
Switch of datafile 30 complete to datafile copy /test/test/dbfile30
  checkpoint is 568697736
Switch of datafile 31 complete to datafile copy /test/test/dbfile31
  checkpoint is 608609130
Switch of datafile 32 complete to datafile copy /test/test/dbfile32
  checkpoint is 608609130

biti_rainy

最初由 mugen 发布
[B]

duplicate 后的克隆数据库的check_point time都是 7.4号的。
why？
我是7。30
号做的备份？ [/B]

所有的数据文件都是那个时候的？如果不是，那就自己仔细想想仔细再查查，别只整你哪个 表连接查询，那只针对 online  datafile，是否有 offline 的啥的还不知道呢。


我看你还不如立即检查现在数据库的checkpoint_time 然后全部重新做！

mugen

已经可以了。因为30号文件的scn不一样。我把这个文件offline，然后开始recove，就从备份之后的log开始进行恢复了。。。。。。。。

mugen

因为这个30号文件的scn是5。16号的，我想一定5。16号的时候那个表空间就offline了，结果备份的时候就把这个文件的scn也记下来了。恢复的时候从最早的这个scn开始去找log

mugen

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

ORA-00279: change 609491399 generated at 07/29/2005 21:37:55 needed for thread
1
ORA-00289: suggestion : /oradata/archive/1_3249.dbf
ORA-00280: change 609491399 for thread 1 is in sequence #3249
ORA-00278: log file '/oradata/archive/1_3248.dbf' no longer needed for this
recovery



ORA-00279: change 609513547 generated at 07/29/2005 23:02:19 needed for thread
1
ORA-00289: suggestion : /oradata/archive/1_3250.dbf
ORA-00280: change 609513547 for thread 1 is in sequence #3250
ORA-00278: log file '/oradata/archive/1_3249.dbf' no longer needed for this
recovery


ORA-00279: change 609514493 generated at 07/29/2005 23:04:47 needed for thread
1
ORA-00289: suggestion : /oradata/archive/1_3251.dbf
ORA-00280: change 609514493 for thread 1 is in sequence #3251
ORA-00278: log file '/oradata/archive/1_3250.dbf' no longer needed for this
recovery




Broadcast message from root@hga (tty) at 17:00:00 ...

echo The hour is `date` .

ORA-00279: change 609518209 generated at 07/29/2005 23:30:50 needed for thread
1
ORA-00289: suggestion : /oradata/archive/1_3252.dbf
ORA-00280: change 609518209 for thread 1 is in sequence #3252
ORA-00278: log file '/oradata/archive/1_3251.dbf' no longer needed for this
recovery


ORA-00279: change 609518611 generated at 07/29/2005 23:33:49 needed for thread
1
ORA-00289: suggestion : /oradata/archive/1_3253.dbf
ORA-00280: change 609518611 for thread 1 is in sequence #3253
ORA-00278: log file '/oradata/archive/1_3252.dbf' no longer needed for this
recovery


ORA-00279: change 609604056 generated at 07/30/2005 02:02:09 needed for thread
1
ORA-00289: suggestion : /oradata/archive/1_3254.dbf
ORA-00280: change 609604056 for thread 1 is in sequence #3254
ORA-00278: log file '/oradata/archive/1_3253.dbf' no longer needed for this
recovery




Broadcast message from root@hga (tty) at 18:00:00 ...

echo The hour is `date` .

ORA-00279: change 609665527 generated at 07/30/2005 06:58:20 needed for thread
1
ORA-00289: suggestion : /oradata/archive/1_3255.dbf
ORA-00280: change 609665527 for thread 1 is in sequence #3255
ORA-00278: log file '/oradata/archive/1_3254.dbf' no longer needed for this
recovery


ORA-00279: change 609698069 generated at 07/30/2005 10:02:36 needed for thread
1
ORA-00289: suggestion : /oradata/archive/1_3256.dbf
ORA-00280: change 609698069 for thread 1 is in sequence #3256
ORA-00278: log file '/oradata/archive/1_3255.dbf' no longer needed for this
recovery


ORA-00279: change 609716779 generated at 07/30/2005 11:43:15 needed for thread
1
ORA-00289: suggestion : /oradata/archive/1_3257.dbf
ORA-00280: change 609716779 for thread 1 is in sequence #3257
ORA-00278: log file '/oradata/archive/1_3256.dbf' no longer needed for this
recovery


ORA-00279: change 609719206 generated at 07/30/2005 11:58:21 needed for thread
1
ORA-00289: suggestion : /oradata/archive/1_3258.dbf
ORA-00280: change 609719206 for thread 1 is in sequence #3258
ORA-00278: log file '/oradata/archive/1_3257.dbf' no longer needed for this
recovery


ORA-00279: change 609759784 generated at 07/30/2005 16:02:33 needed for thread
1
ORA-00289: suggestion : /oradata/archive/1_3259.dbf
ORA-00280: change 609759784 for thread 1 is in sequence #3259
ORA-00278: log file '/oradata/archive/1_3258.dbf' no longer needed for this
recovery


ORA-00308: cannot open archived log '/oradata/archive/1_3259.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 30 will be lost if RESETLOGS is done
ORA-01110: data file 30: '/test/test/dbfile30'


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01245: offline file 30 will be lost if RESETLOGS is done
ORA-01110: data file 30: '/test/test/dbfile30'


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01245: offline file 30 will be lost if RESETLOGS is done
ORA-01110: data file 30: '/test/test/dbfile30'


SQL> alter tablespace verts_or_tab offline drop;
alter tablespace verts_or_tab offline drop
                                      *
ERROR at line 1:
ORA-02141: invalid OFFLINE option


SQL> alter tablespace vrts_or_tab offline;
alter tablespace vrts_or_tab offline
*
ERROR at line 1:
ORA-01109: database not open


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01245: offline file 30 will be lost if RESETLOGS is done
ORA-01110: data file 30: '/test/test/dbfile30'


SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784

CHECKPOINT_CHANGE#
------------------
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784

CHECKPOINT_CHANGE#
------------------
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784
         568697736
         609759784
         609759784
         609759784

CHECKPOINT_CHANGE#
------------------
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784
         609759784

43 rows selected.



SQL> alter database datafile 30 offline drop;

Database altered.



SQL> alter database open resetlogs;

Database altered.






SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2034025376 bytes
Fixed Size                   742304 bytes
Variable Size             939524096 bytes
Database Buffers         1073741824 bytes
Redo Buffers               20017152 bytes
Database mounted.
Database opened.
SQL>

tangke17

我是windows2000加oracle817,我用rman就错
RMAN>run {
2> allocate channel c1 type disk;
3> backup full tag 'fullbackup' format 'f:\kd%_s_%p.dbf'
4> database include current controlfile;
5> sql 'alter system archive log current';
6> release channel c1;
7> }

RMAN-03022：正在编译命令：allocate
RMAN-03026：在释放通道资源时出现错误恢复
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure during compilation of command
RMAN-03013: command type: allocate
RMAN-06172: not connected to recovery catalog database

最初由 biti_rainy 发布
[B]

1：  你可以写三个语句恢复三遍
2： 你可以恢复出一个，拷贝改名做成三个

你说呢？ [/B]

tangke17

我照你说的测试，到sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'D:\KDE\DEMO_01FR79OT_1_1.DBF',
params=>null); 总是报如下错误：

SQL> DECLARE
  2     devtype varchar2(256);
  3     done boolean;
  4     BEGIN
  5  sys.dbms_backup_restore.restoreBackupPiece
  6  (
  7  handle=>
  8  'E:\hotbackup\TK_572795266_1_ORA8I.BCK',
  9  param=>null);
10  end;
11
12  /
sys.dbms_backup_restore.restoreBackupPiece
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00306: wrong number or types of arguments in call to 'RESTOREBACKUPPIECE'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

最初由 Fenng 发布
[B][php]SQL> DECLARE
  2  devtype varchar2(256);
  3  done boolean;
  4  BEGIN
  5  devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
  6  sys.dbms_backup_restore.restoreSetDatafile;
  7  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'d:\oracle\o
radata\demo\SYSTEM01.DBF');
  8  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'d:\oracle\o
radata\demo\UNDOTBS01.DBF');
  9  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'d:\oracle\o
radata\demo\DRSYS01.DBF');
10  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'d:\oracle\o
radata\demo\EXAMPLE01.DBF');
11  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'d:\oracle\o
radata\demo\INDX01.DBF');
12  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,toname=>'d:\oracle\o
radata\demo\ODM01.DBF');
13  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>07,toname=>'d:\oracle\o
radata\demo\TOOLS01.DBF');
14  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>08,toname=>'d:\oracle\o
radata\demo\USERS01.DBF');
15  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>09,toname=>'d:\oracle\o
radata\demo\XDB01.DBF');
16  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'D:\KDE\DEMO_
01FR79OT_1_1.DBF', params=>null);
17  sys.dbms_backup_restore.deviceDeallocate;
18  END;
19  /

PL/SQL procedure successfully completed.


--我们的情形是所有的数据文件都丢失了，那就如法炮制 ...........


SQL> startup force mount;
ORACLE instance started.

Total System Global Area  152115804 bytes
Fixed Size                   453212 bytes
Variable Size             100663296 bytes
Database Buffers           50331648 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> Recover database using backup controlfile until cancel ;
ORA-00279: change 243854 generated at 07/18/2004 20:57:03 needed for thread 1
ORA-00289: suggestion : D:\KDE\ARC00002.001
ORA-00280: change 243854 for thread 1 is in sequence #2

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

D:\KDE\ARC00002.001
--手工指定
ORA-00279: change 244089 generated at 07/18/2004 20:58:18 needed for thread 1
ORA-00289: suggestion : D:\KDE\ARC00003.001
ORA-00280: change 244089 for thread 1 is in sequence #3
ORA-00278: log file 'D:\KDE\ARC00002.001' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;
Database altered.
最后，不得不resetlogs
然后检查数据，用户可能不得不忍受一些数据的丢失。
[/php]

然后，打扫战场，马上进行数据库的全备份。和用户商议制定并完善备份计划。 [/B]

johnson2002

我做该实验的结果更奇怪,过了好长时间系统没有反应,也没生成control文件! 不知道为什么？？
oracle@primary $ls -rlt
total 545412
-rw-r-----    1 oracle   dba      557948928 May  6 19:16 ORACLE_04hidmus_4_1.dbf
oracle@primary $pwd
/home/oracle/orabak


DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
sys.dbms_backup_restore.restoreSetDatafile;
sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/home/oracle/control01.ctl');
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/home/oracle/orabak/ORACLE_04hidmus_4_1.dbf', params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;


还有，我看了你们的操作步骤，我有几个疑问：
1)恢复完控制文件后，还要分别使用DBMS_BACKUP_RESTORE包来一个个单独恢复数据文件，这就必须记住备份时datafileno与datafilename之间的对应关系，可是很多人都不会保留list backup的输出或第一步全库备份的输出信息，这时候该怎么办呢？
2）  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'dracleoradatademoSYSTEM01.DBF')
     这里是一个datafile对应一个一行吗？？ 还是一个tablespace对应一行？？

最初由 Fenng 发布
[B][php]SQL> DECLARE
  2  devtype varchar2(256);
  3  done boolean;
  4  BEGIN
  5  devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
  6  sys.dbms_backup_restore.restoreSetDatafile;
  7  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'d:\oracle\o
radata\demo\SYSTEM01.DBF');
  8  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'d:\oracle\o
radata\demo\UNDOTBS01.DBF');
  9  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'d:\oracle\o
radata\demo\DRSYS01.DBF');
10  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'d:\oracle\o
radata\demo\EXAMPLE01.DBF');
11  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'d:\oracle\o
radata\demo\INDX01.DBF');
12  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,toname=>'d:\oracle\o
radata\demo\ODM01.DBF');
13  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>07,toname=>'d:\oracle\o
radata\demo\TOOLS01.DBF');
14  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>08,toname=>'d:\oracle\o
radata\demo\USERS01.DBF');
15  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>09,toname=>'d:\oracle\o
radata\demo\XDB01.DBF');
16  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'D:\KDE\DEMO_
01FR79OT_1_1.DBF', params=>null);
17  sys.dbms_backup_restore.deviceDeallocate;
18  END;
19  /

PL/SQL procedure successfully completed.


--我们的情形是所有的数据文件都丢失了，那就如法炮制 ...........


SQL> startup force mount;
ORACLE instance started.

Total System Global Area  152115804 bytes
Fixed Size                   453212 bytes
Variable Size             100663296 bytes
Database Buffers           50331648 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> Recover database using backup controlfile until cancel ;
ORA-00279: change 243854 generated at 07/18/2004 20:57:03 needed for thread 1
ORA-00289: suggestion : D:\KDE\ARC00002.001
ORA-00280: change 243854 for thread 1 is in sequence #2

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

D:\KDE\ARC00002.001
--手工指定
ORA-00279: change 244089 generated at 07/18/2004 20:58:18 needed for thread 1
ORA-00289: suggestion : D:\KDE\ARC00003.001
ORA-00280: change 244089 for thread 1 is in sequence #3
ORA-00278: log file 'D:\KDE\ARC00002.001' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;
Database altered.
最后，不得不resetlogs
然后检查数据，用户可能不得不忍受一些数据的丢失。
[/php]

然后，打扫战场，马上进行数据库的全备份。和用户商议制定并完善备份计划。 [/B]

johnson2002

在做第一步全库备份前，加了一步添加了一个表空间，结果做到（前面的步骤都没问题）
Recover database using backup controlfile until cancel ;时老要找log_1_9.arc，
实际上归档日志才到8.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/oradata/archivelog/
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8
SQL>
SQL> Recover database using backup controlfile until cancel ;
ORA-00279: change 3587962 generated at 05/26/2006 16:50:53 needed for thread 1
ORA-00289: suggestion : /home/oracle/oradata/archivelog/log_1_8.arc
ORA-00280: change 3587962 for thread 1 is in sequence #8


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 3588288 generated at 05/26/2006 16:55:22 needed for thread 1
ORA-00289: suggestion : /home/oracle/oradata/archivelog/log_1_9.arc
ORA-00280: change 3588288 for thread 1 is in sequence #9
ORA-00278: log file '/home/oracle/oradata/archivelog/log_1_8.arc' no longer
needed for this recovery


ORA-00308: cannot open archived log
'/home/oracle/oradata/archivelog/log_1_9.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


恢复失败。幸亏我启用了控制文件自动备份，通过备份的控制文件来恢复数据库，也失败了。
RMAN> set DBID=1461422279;
executing command: SET DBID
RMAN> restore controlfile from autobackup;

Starting restore at 2006-05-26 19:19:57

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=12 devtype=DISK
channel ORA_DISK_1: looking for autobackup on day: 20060526
channel ORA_DISK_1: autobackup found: c-1461422279-20060526-02
channel ORA_DISK_1: controlfile restore from autobackup complete
replicating controlfile
input filename=/home/oracle/oradata/oracle/control01.ctl
Finished restore at 2006-05-26 19:20:05

RMAN> alter database mount;

database mounted

RMAN> recover database;

Starting recover at 2006-05-26 19:22:44
using channel ORA_DISK_1

starting media recovery

archive log filename=/home/oracle/oradata/archivelog/log_1_8.arc thread=1 sequence=8
unable to find archive log
archive log thread=1 sequence=9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/26/2006 19:22:46
RMAN-06054: media recovery requesting unknown log: thread 1 scn 3588288

ghuijun

GFDHDFH