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

ZALBB

但是你忘了RMAN会使用控制文件里记录的日志文件的路径，找你备份之后的那个备份
ALTER SYSTEM ARDHIVE LOG CURRENT！
之后使用此日志里记录的信息来做恢复

我刚才做了测试，按照你的步骤操作到恢复控制文件，之后MOUNT，
使用RMAN来恢复，结果和你的一样。 看这步骤：

F:\ORACLE10\BIN>sqlplus

SQL*Plus: Release 10.1.0.2.0 - Production on ??? 7? 18 23:22:14 2004

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>
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.restoreControlfileTo(cfname=>'F:\ORADATA\ORACLE10\CONTROL01.CTL');
  8    sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'D:\ORACLE\rmanbak\DB_ORACLE1
034153187528320040718', params=>null);
  9    sys.dbms_backup_restore.deviceDeallocate;
10  END;
11  /

PL/SQL procedure successfully completed.

SQL>

窗口（RMAN）2执行

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 2004-07-18 23:24:44
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=162 devtype=DISK

datafile 4 not processed because file is offline
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to F:\ORADATA\ORACLE10\SYSTEM01.DBF
restoring datafile 00002 to F:\ORADATA\ORACLE10\UNDOTBS01.DBF
restoring datafile 00003 to F:\ORADATA\ORACLE10\SYSAUX01.DBF
restoring datafile 00005 to F:\ORADATA\ORACLE10\EXAMPLE01.DBF
restoring datafile 00006 to F:\ORADATA\ORACLE10\DEL_TS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ORACLE\RMANBAK\DB_ORACLE1033153187522720040718 tag=TAG20040718T230707
channel ORA_DISK_1: restore complete
Finished restore at 2004-07-18 23:26:05

RMAN>  recover database;

Starting recover at 2004-07-18 23:26:23
using channel ORA_DISK_1
datafile 4 not processed because file is offline

starting media recovery

unable to find archive log
archive log thread=1 sequence=13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/18/2004 23:26:26
RMAN-06054: media recovery requesting unknown log: thread 1 seq 13 lowscn 1820682

RMAN>  alter database open resetlogs;

database opened

RMAN>

ZALBB

RMAN会根据DBID来找到自动备份控制文件（CONTROLFILE AUTOBACKUP）备份集来进行恢复：

1、
RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;  -- AUTOBACKUP 是 ON 的
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\ORACLE10\DATABASE\SNCFORACLE10.ORA'; # default

2、做一个全库备份

F:\ORACLE10\BIN>rman target /

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

connected to target database: ORACLE10 (DBID=2255754647)

RMAN> run
2> {allocate channel d1 type disk;
3>  backup full database
4>  include current controlfile
5>  format 'D:\ORACLE\rmanbak\db_%d_%s_%p_%t_%T';
6>  release channel d1;
7> }

using target database controlfile instead of recovery catalog
allocated channel: d1
channel d1: sid=159 devtype=DISK

Starting backup at 2004-07-18 23:57:44
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00001 name=F:\ORADATA\ORACLE10\SYSTEM01.DBF
input datafile fno=00003 name=F:\ORADATA\ORACLE10\SYSAUX01.DBF
input datafile fno=00005 name=F:\ORADATA\ORACLE10\EXAMPLE01.DBF
input datafile fno=00002 name=F:\ORADATA\ORACLE10\UNDOTBS01.DBF
input datafile fno=00006 name=F:\ORADATA\ORACLE10\DEL_TS01.DBF
channel d1: starting piece 1 at 2004-07-18 23:57:44
channel d1: finished piece 1 at 2004-07-18 23:58:40
piece handle=D:\ORACLE\RMANBAK\DB_ORACLE10_37_1_531878264_20040718 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:56
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
including current controlfile in backupset
channel d1: starting piece 1 at 2004-07-18 23:58:40
channel d1: finished piece 1 at 2004-07-18 23:58:43
piece handle=D:\ORACLE\RMANBAK\DB_ORACLE10_38_1_531878320_20040718 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:03
Finished backup at 2004-07-18 23:58:43

Starting Control File and SPFILE Autobackup at 2004-07-18 23:58:43
piece handle=C:\WINNT\SYSTEM32\C-2255754647-20040718-01 comment=NONE
Finished Control File and SPFILE Autobackup at 2004-07-18 23:58:46

released channel: d1

RMAN> EXIT


Recovery Manager complete.

3、关闭数据库，删除数据文件、控制文件

F:\ORACLE10\BIN>sqlplus

SQL*Plus: Release 10.1.0.2.0 - Production on ??? 7? 18 23:59:27 2004

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Enter user-name: / AS SYSDBA

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> EXIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options


4、启动数据库（NOT MOUNTED状态）

F:\ORACLE10\BIN>sqlplus

SQL*Plus: Release 10.1.0.2.0 - Production on ??? 7? 19 00:05:57 2004

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Enter user-name: / AS SYSDBA
Connected to an idle instance.

SQL> STARTUP NOMOUNT;
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
SQL> EXIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

5、使用RMAN来恢复

F:\ORACLE10\BIN>rman target /

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

connected to target database: oracle10 (not mounted)

RMAN> set dbid 2255754647;   --- 设置DBID是关键

executing command: SET DBID

RMAN> run
2> {allocate channel d1 type disk;
3>  restore controlfile from autobackup;
4>  alter database mount;
5>  restore database;
6>  recover database;
7>  alter database open resetlogs;
8>  release channel d1;
9> }

using target database controlfile instead of recovery catalog
allocated channel: d1
channel d1: sid=159 devtype=DISK

Starting restore at 2004-07-19 00:20:24

channel d1: looking for autobackup on day: 20040719
channel d1: looking for autobackup on day: 20040718
channel d1: autobackup found: c-2255754647-20040718-01 --找到AUTOBACKUP的备份集
channel d1: controlfile restore from autobackup complete
output filename=F:\ORADATA\ORACLE10\CONTROL01.CTL
output filename=F:\ORADATA\ORACLE10\CONTROL02.CTL
output filename=F:\ORADATA\ORACLE10\CONTROL03.CTL
Finished restore at 2004-07-19 00:20:29

database mounted

Starting restore at 2004-07-19 00:20:33

channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to F:\ORADATA\ORACLE10\SYSTEM01.DBF
restoring datafile 00002 to F:\ORADATA\ORACLE10\UNDOTBS01.DBF
restoring datafile 00003 to F:\ORADATA\ORACLE10\SYSAUX01.DBF
restoring datafile 00005 to F:\ORADATA\ORACLE10\EXAMPLE01.DBF
restoring datafile 00006 to F:\ORADATA\ORACLE10\DEL_TS01.DBF
channel d1: restored backup piece 1
piece handle=D:\ORACLE\RMANBAK\DB_ORACLE10_37_1_531878264_20040718 tag=TAG20040718T235744
channel d1: restore complete
Finished restore at 2004-07-19 00:21:50

Starting recover at 2004-07-19 00:21:50

starting media recovery

unable to find archive log
archive log thread=1 sequence=1
released channel: d1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/19/2004 00:21:53
RMAN-06054: media recovery requesting unknown log: thread 1 seq 1 lowscn 1823201 --需要在线日志

RMAN> alter database open resetlogs;

database opened

RMAN>
此实验是在本机上操作的，若是不同机器（RMAN与目标数据库不同机器），会不会也有此结果？

RMAN是如何找到自动备份控制见文件的备份集？

Fenng

我举的例子只是特例，如果你的备份计划做的好，也就出现不了这种情况了

Fenng

最初由 ZALBB 发布
[B]窗口（RMAN）2执行

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

[/B]

我不知道是不是因为版本有关（因为10GRMAN的功能增加了不少）

我的数据库版本是9201
操作得到的信息如下：

C:\WUTemp>rman target sys/tuning

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: DEMO (not mounted)

RMAN> alter database mount;

using target database controlfile instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/19/2004 08:39:54
ORA-00205: error in identifying controlfile, check alert log for more info

RMAN>

这个信息是因为初始化参数控制文件的指向有问题，修改之后

RMAN> alter database mount;

-这里可以mount 起来
database mounted

RMAN> restore database;

Starting restore at 19-JUL-04

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/19/2004 08:43:41
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 9 found to restore
RMAN-06023: no backup or copy of datafile 8 found to restore
RMAN-06023: no backup or copy of datafile 7 found to restore
RMAN-06023: no backup or copy of datafile 6 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

这个和我昨天得到的信息是一致的

西门吹牛

又见帅哥大做了。

Fenng

你做第一个例子的时候
rman>show all ;

我怀疑是你已经配置了控制文件自动备份

Fenng

手边有一本《Oracle9i rman 备份与恢复技术》(买回来一直没看)

翻看了一下，里面提到了DBMS_BACKUP_RESTORE 的一些信息，但也没有详细的说明

不过一些功能可以通过DeBug得到，有时间真要研究一下这玩意儿

Fenng

最初由 ZALBB 发布
[B]

RMAN是如何找到自动备份控制见文件的备份集？ [/B]

这个是由Rman自身的功能做到的

如果不特定指定设备和Format等信息，
Rman会到$ORACLE_HOME/dbs （不同平台稍有不同）去查找自动备份的控制文件

不信的话，你可以把你相关目录下自动备份的控制文件删掉，你看还能找到不 ?

如果你分配了设备，并指定具体位置，那么在set dbid 之后，rman 就会到你指定的位置去查找对应dbid 的那个控制文件自动备份



我的这个例子在开始有些模糊的地方，具体应该是：在没有使用catalog ，而使用控制文件作备份并丢失所有控制文件的情况下。可以应用本例。而且也是唯一的办法。

我发现那本书的确不错

ZALBB

最初由 Fenng 发布
[B]你做第一个例子的时候
rman>show all ;

我怀疑是你已经配置了控制文件自动备份 [/B]

没有，我对你的第一个帖子开始不想用那个包，
但不知道如何恢复控制文件，后来实在没辙，
只有用你那个方法（那个包）来恢复控制文件，
之后才是我的想法。不过我忘了说明，我机器上的版本是10G。


后来我特意配置了CONTROLFILE AUTOBACKUP ON
之后，才可以不使用该包来恢复的。
今天有时间在9I上测试一把，看看是不是如我所想的步骤。

但此方法（AUTOBACKUP）始终有个缺陷，就是要指明
DBID（忘记了就麻烦了），否则，AUTOBACKUOP不知道
找哪个备份集恢复控制文件，若是人为指定的备份集来恢复，
就是你使用的那个包的方法，就不存在该问题。

Fenng

最初由 ZALBB 发布
[B]

没有，我对你的第一个帖子开始不想用那个包，
但不知道如何恢复控制文件，后来实在没辙，
只有用你那个方法（那个包）来恢复控制文件，
之后才是我的想法。不过我忘了说明，我机器上的版本是10G。


后来我特意配置了CONTROLFILE AUTOBACKUP ON
之后，才可以不使用该包来恢复的。
今天有时间在9I上测试一把，看看是不是如我所想的步骤。

但此方法（AUTOBACKUP）始终有个缺陷，就是要指明
DBID（忘记了就麻烦了），否则，AUTOBACKUOP不知道
找哪个备份集恢复控制文件，若是人为指定的备份集来恢复，
就是你使用的那个包的方法，就不存在该问题。 [/B]

是的．dbid 比较烦人，不过如果计划周详的话，这样的事情很少发生