好久没有动脑子了,最近一直在跑步、踢球,练就成了四肢发达、头脑简单的高级动物,当同学把rman恢复错误信息给我发过来之后,都不知道怎么下手了,更囧的是一个项目组的哥们让我帮忙看看数据库问题,parameter参数竟然瞬间忘了,整天文档、文章的,荒废了。为了调整,我决定没事找事,OK,咱看一下下面的问题。(以下信息是在我的测试机上还原的)
同学发过来的问题如下:
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-03002:
failure of switch command at 09/28/2014 07:59:23
RMAN-20230:
datafile copy not found in the repository
RMAN-06015:
error while looking up datafile copy name: /oracle/FIRSOUL/datafile/test2.dbf
这个懂得数据库或者rman的人大多能看出来,switch失败,找不到那个文件副本,我就看了一下备份脚本,脚本如下:
RMAN>
run
2> {
3>
allocate channel C1 type disk maxpiecesize 2G format
"/oracle/backup/0/%d_%Y_%M_%D_%U.BAK";
4>
allocate channel C2 type disk maxpiecesize 2G format
"/oracle/backup/0/%d_%Y_%M_%D_%U.BAK";
5>
sql 'alter system archive log current';
6>
backup incremental level=0 database plus archivelog delete input;
backup
current controlfile format '/oracle/backup/0/ctl_%d_%Y_%M_%D.bak';
backup
spfile format '/oracle/backup/0/spfile_%d_%Y_%M_%D.bak';
7>
8> 9> delete noprompt obsolete
REDUNDANCY=2 device type disk;
10> crosscheck backup;
11> release channel C1;
12> release channel C2;
13>
}
List backup查看,并没有发现备份文件6、8,查看备份脚本没有跳过该文件,但备份日志中出现一下信息
file 6
is excluded from whole database backup
file 8
is excluded from whole database backup
说明这两个文件没有备份,那么在set newname时,也就不能写。那么他之前没有写,但是只写了一个restore database,报错如下:
Starting
recover at 28-SEP-14
using
channel ORA_DISK_1
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-03002:
failure of recover command at 09/28/2014 13:37:57
RMAN-06094:
datafile 6 must be restored
既然没有备份,那么我还原的时候应该可以啊,备份的时候也没有skip,怎么就不备份了呢,通过查看下面视图,发现test2、test4用rman时是不备份的。但是在控制文件信息中也记录着没有备份的表空间、数据文件信息,所以要用skip tablespace跳过没备份的表空间。
sys@FIRSOUL>
select * from v$tablespace;
TS# NAME INC BIG FLA ENC
----------
----------- --- --- --- ---
0 SYSTEM YES NO
YES
1 SYSAUX YES NO
YES
2 UNDOTBS1 YES NO
YES
3 TEMP NO
NO YES
4 USERS YES NO
YES
5 TEST1 YES NO
YES
6 TEST2 NO
NO YES
7 TEST3 YES NO
YES
8 TEST4 NO
NO YES
9 TEST5 YES NO
YES
官方解释如下:Indicates whether the tablespace is included in full database
backups using theBACKUP DATABASERMAN command (YES) or not (NO).NOonly if theCONFIGURE EXCLUDERMAN command was
used for this tablespace
提示:更改及取消更改表空间是否使用rman备份语句如下,通过rman命令
CONFIGURE
EXCLUDE FOR TABLESPACE 'TEST4';
CONFIGURE
EXCLUDE FOR TABLESPACE 'TEST4' clear;
完整恢复命令:
RMAN>
run
2> {
3>
set newname for datafile 1 to '/oracle/FIRSOUL/datafile/system01.dbf';
4>
set newname for datafile 2 to '/oracle/FIRSOUL/datafile/sysaux01.dbf';
5>
set newname for datafile 3 to '/oracle/FIRSOUL/datafile/undotbs1.dbf';
6>
set newname for datafile 4 to '/oracle/FIRSOUL/datafile/users.dbf';
7>
set newname for datafile 5 to '/oracle/FIRSOUL/datafile/test1.dbf';
8>
set newname for datafile 7 to '/oracle/FIRSOUL/datafile/test3.dbf';
9>
set newname for datafile 9 to '/oracle/FIRSOUL/datafile/test5.dbf';
10>
restore database skip tablespace test2,test4;
11>
switch datafile all;
12>
recover database skip tablespace test2,test4;
13>
}
关于skip [forever] tablespace说明
Use an
optional SKIPTABLESPACE 'tablespace_name' argument to
avoid
restoring specified tablespaces, which is useful when you want to
avoid
restoring tablespaces containing temporary data.
If you
specify SKIP FOREVER TABLESPACE, then RMAN specifies the DROP
option
of ALTER DATABASE DATAFILE ... OFFLINE when taking the
datafiles
that belong to the tablespace offline before the restore. The DROP
option
indicates that RMAN does not intend to recover these files and
intends
to drop their tablespaces from the database after the database is
opened
again. In other words, FOREVER indicates that RMAN never
intends
to do anything with the skipped tablespaces again.
开始由于无法看到原库或者没有仔细看备份日志,导致我们在还原时有些盲目,网络上的资料对于细节类的东西介绍的比较少,所以好多时候我们就会钻里边出不来。以上步骤是我在测试服务器上还原的问题,由于原库数据量较大,每做一次测试(例如restore操作)一个下午或者一天就没了,为了提高效率,我们应该对相应的文档、日志仔细查看、步骤考虑周全。其实归根结底还是rman不熟悉造成,还有就是有些时候我们盲目去动手,一句话,日志是个好东西啊。就一个简单问题,可能憋的太久了,感觉还是很有兴趣的,继续加油。