哈哈,咱也支持一下!!!

在AIX版混的时间比较长,现在也搞数据库备份了.对数据库实在不熟,只有问的份,哪敢发帖子.

看到闲云都来了,咱也支持一把.

这是我做的数据库恢复试验,记录的忒细,为的是更好的理解过程.

水平有限,批评指正.

环境:AIX5.2+ORA9.2.0.1

第一天

1.        启动归档.
2.        配置RMAN.
3.        配置RMAN备份脚本:
0级备份:
RMAN> configure controlfile autobackup on;
2> run
3> {
4>   allocate channel d1 type disk format '/oraback/df_%t_%s_%p';
5>   backup incremental level 0
6>      filesperset 5
7>      (database include current controlfile);
8>   sql 'alter system archive log current';
9>   backup filesperset 20
10>      archivelog all delete input;
11>   release channel d1;
12> }
13>  allocate channel for maintenance type disk;
14>  delete noprompt obsolete;
15> run
16> {
17> allocate channel d2 device type disk;
18> backup current controlfile format '/oraback/control.cata';
19> release channel d2;
20> }
1级备份:
RMAN> configure controlfile autobackup on;
2> run
3> {
4>   allocate channel d1 type disk format '/oraback/df_%t_%s_%p';
5>   backup incremental level 1
6>      filesperset 5
7>      (database include current controlfile);
8>   sql 'alter system archive log current';
9>   backup filesperset 20
10>      archivelog all;
11>   release channel d1;
12> }
13> run
14> {
15> allocate channel d2 device type disk;
16> backup current controlfile format '/oraback/control.cata';
17> release channel d2;
18> }

我正在破坏----恢复--------再破坏---------再恢复

0级备份正常.
1级备份:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on d2 channel at 03/23/2006 13:11:52
ORA-19504: failed to create file "/oraback/control.cata"
ORA-27038: skgfrcre: file exists

控制文件已经存在,该信息是正常的,应该动态命名文件.

在1级备份完成后,手工删除归档文件.再次1级备份时出错:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 03/23/2006 13:15:59
RMAN-06059: expected archived log not found, lost of archived log compromises re
coverability
ORA-19625: error identifying file /oraback/ARC00010000000009.arc
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

那么0级备份是否出错呢?照样出错:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 03/23/2006 15:10:57
RMAN-06059: expected archived log not found, lost of archived log compromises re
coverability
ORA-19625: error identifying file /oraback/ARC00010000000009.arc
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

检查可恢复性:
RMAN> restore database validate;
Starting restore at 23-MAR-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=17 devtype=DISK
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: restored backup piece 1
piece handle=/oraback/df_585846602_29_1 tag=TAG20060323T151002 params=NULL
channel ORA_DISK_1: validation complete
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: restored backup piece 1
piece handle=/oraback/df_585846627_30_1 tag=TAG20060323T151002 params=NULL
channel ORA_DISK_1: validation complete
Finished restore at 23-MAR-06
以上报告是可恢复的.

再做一次1级备份,然后检查可恢复性:
RMAN> restore database validate;
Starting restore at 23-MAR-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=16 devtype=DISK
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: restored backup piece 1
piece handle=/oraback/df_585846602_29_1 tag=TAG20060323T151002 params=NULL
channel ORA_DISK_1: validation complete
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: restored backup piece 1
piece handle=/oraback/df_585846627_30_1 tag=TAG20060323T151002 params=NULL
channel ORA_DISK_1: validation complete
Finished restore at 23-MAR-06

报告还是可恢复的.

先不管上面的故障,先做恢复实验:
停止数据库,删除数据库文件.

一: 想使用CATALOG恢复
oracle@p630:[/ora92/9.2.0/oradata]rman target / catalog rman/rman

Recovery Manager: Release 9.2.0.1.0 - 64bit Production

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

connected to target database (not started)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04004: error from recovery catalog database: ORA-01034: ORACLE not availabl
e
ORA-27101: shared memory realm does not exist
IBM AIX RISC System/6000 Error: 2: No such file or directory

哈哈,CATALOG数据库都不可用,还恢复啥呀,所以,如果使用CATALOG,一定单独建立一个数据库.

二: 使用恢复文件
oracle@p630:[/ora92/9.2.0/oradata]rman target /
Recovery Manager: Release 9.2.0.1.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
connected to target database (not started)

RMAN> startup nomount;
Oracle instance started
Total System Global Area     639074816 bytes
Fixed Size                      741888 bytes
Variable Size                318767104 bytes
Database Buffers             318767104 bytes
Redo Buffers                    798720 bytes

RMAN> restore controlfile;
Starting restore at 23-MAR-06
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/23/2006 15:46:48
RMAN-06563: controlfile or SPFILE must be restored using FROM AUTOBACKUP

看看,控制文件自动备份多么重要!!! 也有个问题,在备份数据文件时已经指定了包含控制文件:include current controlfile,为什么不用它? 这个问题留在后研究.

RMAN> restore controlfile from autobackup;
Starting restore at 23-MAR-06
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/23/2006 15:47:26
RMAN-06495: must explicitly specify DBID with SET DBID command

一定要记住 DBID吆!!!

RMAN> set DBID=2069215961;
executing command: SET DBID
RMAN> restore controlfile from autobackup;
Starting restore at 23-MAR-06
using channel ORA_DISK_1
channel ORA_DISK_1: looking for autobackup on day: 20060323
channel ORA_DISK_1: autobackup found: c-2069215961-20060323-0b
channel ORA_DISK_1: controlfile restore from autobackup complete
replicating controlfile
input filename=/ora92/9.2.0/oradata/cata/control01.ctl
output filename=/ora92/9.2.0/oradata/cata/control02.ctl
output filename=/ora92/9.2.0/oradata/cata/control03.ctl
Finished restore at 23-MAR-06

查看数据库控制文件已经恢复了,哈哈! 同时也证明了即使使用CATALOG备份,也是能够使用控制文件进行恢复.因为备份信息同时保存在控制文件和恢复目录里.

进一步想,即使使用了恢复目录,也要重视在设置备份保留策略时对控制文件的配置,因为恢复目录只是比控制文件保存更长久的备份信息,而在恢复的时候,无论你从何处恢复控制文件,在恢复其他的数据文件时都依赖这个控制文件.是不是控制文件是核心的核心?

然后恢复数据文件:
RMAN> startup mount;
database is already started
database mounted
RMAN> restore database;
Starting restore at 23-MAR-06
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /ora92/9.2.0/oradata/cata/system01.dbf
restoring datafile 00003 to /ora92/9.2.0/oradata/cata/indx01.dbf
restoring datafile 00005 to /ora92/9.2.0/oradata/cata/users01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oraback/df_585846602_29_1 tag=TAG20060323T151002 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /ora92/9.2.0/oradata/cata/undotbs01.dbf
restoring datafile 00004 to /ora92/9.2.0/oradata/cata/tools01.dbf
restoring datafile 00006 to /ora92/9.2.0/oradata/cata/catalog01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oraback/df_585846627_30_1 tag=TAG20060323T151002 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 23-MAR-06

恢复数据库:
RMAN> recover database;
Starting recover at 23-MAR-06
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /ora92/9.2.0/oradata/cata/system01.dbf
destination for restore of datafile 00003: /ora92/9.2.0/oradata/cata/indx01.dbf
destination for restore of datafile 00005: /ora92/9.2.0/oradata/cata/users01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oraback/df_585847270_32_1 tag=TAG20060323T152109 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /ora92/9.2.0/oradata/cata/undotbs01.dbf
destination for restore of datafile 00004: /ora92/9.2.0/oradata/cata/tools01.dbf
destination for restore of datafile 00006: /ora92/9.2.0/oradata/cata/catalog01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oraback/df_585847285_33_1 tag=TAG20060323T152109 params=NULL
channel ORA_DISK_1: restore complete

starting media recovery
archive log filename=/oraback/ARC00010000000015.arc thread=1 sequence=15
archive log filename=/oraback/ARC00010000000016.arc thread=1 sequence=16
unable to find archive log
archive log thread=1 sequence=17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/23/2006 16:00:17
RMAN-06054: media recovery requesting unknown log: thread 1 scn 665625

看到log 17没有找到,也就是重做日志还没有生成归档文件,不能恢复.因此在备份数据文件之后,备份归档文件之前一定切换一下在线日志.因为RMAN备份是不备份在线日志的,否则在线日志中有数据的可就丢了.

RMAN> alter database open resetlogs;
database opened
RMAN>

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oraback
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1
使用alter database open resetlogs打开数据库后,可以看到日志文件重新编号.

再来一遍:


1.        停止数据库,删除文件
2.        rman target /
3.        startup nomount
4.        set DBID
5.        restore controlfile from autobackup
6.        alter database mount
7.        restore database出错:
RMAN> restore database;
Starting restore at 23-MAR-06
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/23/2006 17:08:01
RMAN-06026: some targets not found - aborting 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
究竟是什么原因?第一次恢复正常,第二次就不行了.

看看备份集:
RMAN> report obsolete;
Backup Set           26     23-MAR-06
Backup Piece       26     23-MAR-06          /oraback/df_585846602_29_1
Backup Set           27     23-MAR-06
Backup Piece       27     23-MAR-06          /oraback/df_585846627_30_1
Backup Set           28     23-MAR-06
Backup Piece       28     23-MAR-06          /ora92/9.2.0/dbs/c-2069215961-200
60323-0a
Backup Set           29     23-MAR-06
Backup Piece       29     23-MAR-06          /oraback/df_585847270_32_1
Backup Set           30     23-MAR-06
Backup Piece       30     23-MAR-06          /oraback/df_585847285_33_1
备份集怎么失效了.

查看控制文件的自动备份:
root@p630:[/oraback]ls -l /ora92/9.2.0/dbs
-rw-r-----   1 oracle   dba         1327104 Mar 23 16:12 c-2069215961-20060323-0b
看看时间戳,13:48分恢复控制文件时使用的是该文件,现在时间戳变为16:12分..说明在alter database open resetlogs打开数据库的时候自动备份了控制文件.在新建数据库任何实体时都自动备份控制文件这点可以理解.但覆盖了最后一次自动备份的控制文件是超乎我的意料.
因此在做实际恢复的时候,应该把该自动备份文件做个拷贝!!!以防再需要重复恢复时无一致的控制文件备份.另外在resetlogs打开数据库后,一定做一个数据库的全备份!!!

第二天
没有办法了,只能恢复到前一个控制文件备份看看

1.删除最新的控制文件备份,
2.重复以前恢复步骤, 该次使用是备份控制文件c-2069215961-20060323-0a,一切顺利完成.并且在alter database open RESETLOGS后重新生成了另一个控制文件的自动备份,而没有覆盖c-2069215961-20060323-0a.奇怪不?

问题一:使用backup database include current controlfile备份数据库,无控制文件的自动备份,到底能不能恢复数据库?
删除所有文件,删除自动备份的控制文件.

使用restore controlfile肯定不行,如果我们知道最后一个包括控制文件的数据集就好了:
RMAN> list backup;

using target database controlfile instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 03/24/2006 14:38:56
ORA-01507: database not mounted
哈哈,控制文件没有恢复,不可能得到结果.

那就从最新的备份文件往前一个一个地试:

RMAN> restore controlfile from '/oraback//df_585846602_29_1';

Starting restore at 24-MAR-06

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/ora92/9.2.0/oradata/cata/control01.ctl
output filename=/ora92/9.2.0/oradata/cata/control02.ctl
output filename=/ora92/9.2.0/oradata/cata/control03.ctl
Finished restore at 24-MAR-06

OK,控制文件恢复了.连DBID都不用设置!!!

RMAN> alter database mount;
RMAN> restore database;
Starting restore at 24-MAR-06

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /ora92/9.2.0/oradata/cata/system01.dbf
restoring datafile 00003 to /ora92/9.2.0/oradata/cata/indx01.dbf
restoring datafile 00005 to /ora92/9.2.0/oradata/cata/users01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oraback/df_585839184_13_1 tag=TAG20060323T130624 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /ora92/9.2.0/oradata/cata/undotbs01.dbf
restoring datafile 00004 to /ora92/9.2.0/oradata/cata/tools01.dbf
restoring datafile 00006 to /ora92/9.2.0/oradata/cata/catalog01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oraback/df_585839210_14_1 tag=TAG20060323T130624 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 24-MAR-06

OK,数据文件恢复了,但恢复的是前一个完全备份(注意备份集名).说明该数据备份集中包含的控制文件没有包含本次备份的信息!!!

RMAN> recover database;

Starting recover at 24-MAR-06
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /ora92/9.2.0/oradata/cata/system01.df
destination for restore of datafile 00003: /ora92/9.2.0/oradata/cata/indx01.dbf
destination for restore of datafile 00005: /ora92/9.2.0/oradata/cata/users01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oraback/df_585839472_20_1 tag=TAG20060323T131112 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /ora92/9.2.0/oradata/cata/undotbs01.f
destination for restore of datafile 00004: /ora92/9.2.0/oradata/cata/tools01.dbf
destination for restore of datafile 00006: /ora92/9.2.0/oradata/cata/catalog01.f
channel ORA_DISK_1: restored backup piece 1
piece handle=/oraback/df_585839488_21_1 tag=TAG20060323T131112 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /ora92/9.2.0/oradata/cata/system01.df
destination for restore of datafile 00003: /ora92/9.2.0/oradata/cata/indx01.dbf
destination for restore of datafile 00005: /ora92/9.2.0/oradata/cata/users01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oraback/df_585839714_26_1 tag=TAG20060323T131514 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /ora92/9.2.0/oradata/cata/undotbs01.f
destination for restore of datafile 00004: /ora92/9.2.0/oradata/cata/tools01.dbf
destination for restore of datafile 00006: /ora92/9.2.0/oradata/cata/catalog01.f
channel ORA_DISK_1: restored backup piece 1
piece handle=/oraback/df_585839730_27_1 tag=TAG20060323T131514 params=NULL
channel ORA_DISK_1: restore complete
starting media recovery

archive log thread 1 sequence 11 is already on disk as file /oraback/ARC0001000c
archive log thread 1 sequence 12 is already on disk as file /oraback/ARC0001000c
archive log filename=/oraback/ARC00010000000011.arc thread=1 sequence=11
archive log filename=/oraback/ARC00010000000012.arc thread=1 sequence=12
archive log filename=/oraback/ARC00010000000013.arc thread=1 sequence=13
archive log filename=/oraback/ARC00010000000014.arc thread=1 sequence=14
archive log filename=/oraback/ARC00010000000015.arc thread=1 sequence=15
archive log filename=/oraback/ARC00010000000016.arc thread=1 sequence=16
unable to find archive log
archive log thread=1 sequence=17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/24/2006 15:03:54
RMAN-06054: media recovery requesting unknown log: thread 1 scn 665625

RMAN> alter database open resetlogs;
database opened

哈哈,已经完全恢复.只要您保留有全部的归档文件,是可以恢复到最近的.

以前一直不知道include current controlfile子句的含义,以为只能使用控制文件的自动备份才能恢复.我也看到过他人使用DBMS_BACKUP_RESTORE数据包恢复控制文件的实验.其实使用包含控制文件的数据包照样能恢复!!!

太累了,休息休息!

[quote]原帖由 [i]sysjs[/i] 于 2006-3-24 15:59 发表
查看控制文件的自动备份:
[b]root@p630[/b]:[/oraback]ls -l /ora92/9.2.0/dbs
-rw-r-----   1 oracle   dba         1327104 Mar 23 16:12 c-2069215961-20060323-0b
看看时间戳,13:48分恢复控制文件时使用的是该文件 ... [/quote]

真够详细的，我可没耐心把所有的都粘贴复制下来，有什么trace文件吗？

我也在p630上鼓捣

[[i] 本帖最后由 闲云 于 2006-3-24 16:42 编辑 [/i]]

不错，思路清晰，可圈可点。如：

哈哈,CATALOG数据库都不可用,还恢复啥呀,所以,如果使用CATALOG,一定单独建立一个数据库.

因此在备份数据文件之后,备份归档文件之前一定切换一下在线日志

那就从最新的备份文件往前一个一个地试:

RMAN> restore controlfile from '/oraback//df_585846602_29_1';
＝＝＝＝
这些都是在实际过程中可能碰到的问题，很有参考性哦

哈哈... 还得了精华,继续努力ing.