rman恢复spfile的疑问

gdpr-dba

C:\Documents and Settings\Administrator>dir E:\recover_dest\OANET\AUTOBACKUP\201
2_05_02

驱动器 E 中的卷是 新加卷
卷的序列号是 E808-4F88
E:\recover_dest\OANET\AUTOBACKUP\2012_05_02 的目录
2012-05-02  23:05    <DIR>          .
2012-05-02  23:05    <DIR>          ..
2012-05-02  22:07        11,534,336 O1_MF_S_782258840_7T3XM9DG_.BKP
2012-05-02  22:24        11,534,336 O1_MF_S_782259874_7T3YMN79_.BKP
2012-05-02  23:05        11,534,336 O1_MF_S_782262314_7T40ZW2J_.BKP
               3 个文件     34,603,008 字节
               2 个目录 42,480,836,608 可用字节

--从上面可以看出在FRA里已经有5月2日的autobackup

RMAN> show all
2> ;
使用目标数据库控制文件替代恢复目录
RMAN 配置参数为:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
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 80 G;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\S
NCFOANET.ORA'; # default

RMAN>

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      E:\recover_dest
db_recovery_file_dest_size           big integer 20G

SQL> select dbid from v$database;
      DBID
----------
4052226987

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>

关闭数据库后删除spfile。

RMAN> startup nomount
启动失败: ORA-01078: failure in processing system parameters
LRM-00109: ???????????????? 'E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INITOANET.OR
A'
在没有参数文件的情况下启动 Oracle 实例以检索 spfile
Oracle 实例已启动
系统全局区域总计     369098752 字节
Fixed Size                     2213248 字节
Variable Size                110901888 字节
Database Buffers             251658240 字节
Redo Buffers                   4325376 字节

RMAN> show all
2> ;
使用目标数据库控制文件替代恢复目录
RMAN 配置参数为:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
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 ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

RMAN> set dbid=4052226987;
正在执行命令: SET DBID
RMAN> restore spfile from autobackup; --为什么找不到备份
启动 restore 于 03-5月 -12
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=2 devtype=DISK
通道 ORA_DISK_1: 寻找以下日期的自动备份: 20120503
通道 ORA_DISK_1: 寻找以下日期的自动备份: 20120502
通道 ORA_DISK_1: 寻找以下日期的自动备份: 20120501
通道 ORA_DISK_1: 寻找以下日期的自动备份: 20120430
通道 ORA_DISK_1: 寻找以下日期的自动备份: 20120429
通道 ORA_DISK_1: 寻找以下日期的自动备份: 20120428
通道 ORA_DISK_1: 寻找以下日期的自动备份: 20120427
通道 ORA_DISK_1: 没有找到 7 天之内的自动备份

RMAN> restore spfile from autobackup db_recovery_file_dest='E:\recover_dest'; --为什么找不到备份
启动 restore 于 03-5月 -12
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=5 devtype=DISK
恢复区目标: E:\recover_dest
用于搜索的数据库名 (或数据库的唯一名称): DUMMY
通道 ORA_DISK_1: 在恢复区域中未找到自动备份
由于未设置 DBID 而未尝试在恢复区域之外搜索自动备份

RMAN> restore spfile from autobackup db_recovery_file_dest='E:\recover_dest' db_
name='oanet';
启动 restore 于 03-5月 -12
使用通道 ORA_DISK_1
恢复区目标: E:\recover_dest
用于搜索的数据库名 (或数据库的唯一名称): OANET
通道 ORA_DISK_1: 在恢复区域中找到自动备份
通道 ORA_DISK_1: 已找到的自动备份: E:\RECOVER_DEST\OANET\AUTOBACKUP\2012_05_02\O
1_MF_S_782262314_7T40ZW2J_.BKP
通道 ORA_DISK_1: 从自动备份复原 SPFILE 已完成
完成 restore 于 03-5月 -12

RMAN>

问题1：
为什么set dbid和restore spfile from autobackup会找不到备份，我看网上都是这样做恢复的。

问题2：
为什么非要使用restore spfile from autobackup db_recovery_file_dest='E:\recover_dest' db_name='oanet';才可以找到autobackup呢？

问题3：
RMAN的autobackup格式是%F，那应该是c-IIIIIIIIII-YYYYMMDD-QQ这个格式才对吧，怎么我备份出来是这种格式O1_MF_S_782262314_7T40ZW2J_.BKP呢？

启动 Control File and SPFILE Autobackup 于 03-5月 -12
段 handle=E:\RECOVER_DEST\OANET\AUTOBACKUP\2012_05_03\O1_MF_S_782276453_7T4GSPDW
_.BKP comment=NONE
完成 Control File and SPFILE Autobackup 于 03-5月 -12

%F	Combines the DBID, day, month, year, and sequence into a unique and repeatable generated name. This variable translates into c-IIIIIIIIII-YYYYMMDD-QQ

[ 本帖最后由 gdpr-dba 于 2012-5-3 16:06 编辑 ]

Maclean Liu(刘相兵

"问题3：
RMAN的autobackup格式是%F，那应该是c-IIIIIIIIII-YYYYMMDD-QQ这个格式才对吧，怎么我备份出来是这种格式O1_MF_S_782262314_7T40ZW2J_.BKP呢？"

Answer:


as maclean said:

这是 FLASH RECOVERY AREA 下的特有格式， 10g以后 默认AUTOBACKUP 都会在 FLASH RECOVERY AREA 下

RMAN> show all;

RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE 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 ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\APP\MACLEAN.LIU\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFPROD.ORA'; # default

默认即 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default


如果没有修改过 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT 那么总是 AUTOBACKUP 到   FLASH RECOVERY AREA ， 且使用FLASH RECOVERY AREA  特有的格式 不会有DBID

RMAN> backup datafile 3;

Starting backup at 04-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=138 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=D:\APP\MACLEAN.LIU\ORADATA\PROD\UNDOTBS01.DBF
channel ORA_DISK_1: starting piece 1 at 04-MAY-12
channel ORA_DISK_1: finished piece 1 at 04-MAY-12
piece handle=D:\APP\MACLEAN.LIU\FLASH_RECOVERY_AREA\PROD\BACKUPSET\2012_05_04\O1_MF_NNNDF_TAG20120504T234054_7T7Y470L_.BKP tag=TAG20120504T234054 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
Finished backup at 04-MAY-12

Starting Control File and SPFILE Autobackup at 04-MAY-12
piece handle=D:\APP\MACLEAN.LIU\FLASH_RECOVERY_AREA\PROD\AUTOBACKUP\2012_05_04\O1_MF_S_782437270_7T7Y4QNO_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 04-MAY-12



如果我们手动指定一次 默认值 则会开始 AUTOBACKUP 到 $ORACLE_HOME/dbs 或 $ORACLE_HOME/database下 且使用 带有dbid的 文件名形式


RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
new RMAN configuration parameters are successfully stored

RMAN> backup datafile 3;

Starting backup at 04-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=D:\APP\MACLEAN.LIU\ORADATA\PROD\UNDOTBS01.DBF
channel ORA_DISK_1: starting piece 1 at 04-MAY-12
channel ORA_DISK_1: finished piece 1 at 04-MAY-12
piece handle=D:\APP\MACLEAN.LIU\FLASH_RECOVERY_AREA\PROD\BACKUPSET\2012_05_04\O1_MF_NNNDF_TAG20120504T234126_7T7Y56Q6_.BKP tag=TAG20120504T234126 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 04-MAY-12

Starting Control File and SPFILE Autobackup at 04-MAY-12
piece handle=D:\APP\MACLEAN.LIU\PRODUCT\11.2.0\DBHOME_1\DATABASE\C-187322083-20120504-02 comment=NONE
Finished Control File and SPFILE Autobackup at 04-MAY-12



如果我们使用 configure ...... clear 命令 清楚任何CONFIGURE CONTROLFILE AUTOBACKUP FORMAT 配置后 ，又会恢复备份到  FLASH RECOVERY AREA 且使用FRA 特有格式的状态：

RMAN> backup datafile 3;

Starting backup at 04-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=D:\APP\MACLEAN.LIU\ORADATA\PROD\UNDOTBS01.DBF
channel ORA_DISK_1: starting piece 1 at 04-MAY-12
channel ORA_DISK_1: finished piece 1 at 04-MAY-12
piece handle=D:\APP\MACLEAN.LIU\FLASH_RECOVERY_AREA\PROD\BACKUPSET\2012_05_04\O1_MF_NNNDF_TAG20120504T234347_7T7Y9MWJ_.BKP tag=TAG20120504T234347 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 04-MAY-12

Starting Control File and SPFILE Autobackup at 04-MAY-12
piece handle=D:\APP\MACLEAN.LIU\FLASH_RECOVERY_AREA\PROD\AUTOBACKUP\2012_05_04\O1_MF_S_782437443_7T7YB4G9_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 04-MAY-12

Maclean Liu(刘相兵

问题1：
为什么set dbid和restore spfile from autobackup会找不到备份，我看网上都是这样做恢复的。



Answer:


Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 4 23:26:49 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=187322083)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
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 ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\APP\MACLEAN.LIU\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFPROD.ORA'; # default

RMAN> backup datafile 2;

Starting backup at 04-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=140 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=D:\APP\MACLEAN.LIU\ORADATA\PROD\SYSAUX01.DBF
channel ORA_DISK_1: starting piece 1 at 04-MAY-12
channel ORA_DISK_1: finished piece 1 at 04-MAY-12
piece handle=D:\APP\MACLEAN.LIU\FLASH_RECOVERY_AREA\PROD\BACKUPSET\2012_05_04\O1_MF_NNNDF_TAG20120504T232725_7T7XBXHX_.BKP tag=TAG20120504T232725 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 04-MAY-12

Starting Control File and SPFILE Autobackup at 04-MAY-12
piece handle=D:\APP\MACLEAN.LIU\FLASH_RECOVERY_AREA\PROD\AUTOBACKUP\2012_05_04\O1_MF_S_782436461_7T7XCGDM_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 04-MAY-12


SQL> shutdown immediate;


delete spfile and pfile



RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: ???????????????? 'D:\APP\MACLEAN.LIU\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITPROD.ORA'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     159019008 bytes

Fixed Size                     1373264 bytes
Variable Size                 75500464 bytes
Database Buffers              75497472 bytes
Redo Buffers                   6647808 bytes

RMAN> restore spfile from autobackup;

Starting restore at 04-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=135 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120504
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120503
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120502
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120501
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120430
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120429
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120428
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/04/2012 23:31:12
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece



为什么 找不到 刚才 的backup?

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0


因为RMAN 临时启动使用的 INIT.ORA 中db_recovery_file_dest是空的 ， 即不知道fast_recovery_area在哪里？
那么rman 当然找不到你在 fast_recovery_area 下的spfile自动备份


手动编辑一个 $ORACLE_HOME/dbs/init$SID.ora or windows 下的 $ORACLE_HOME\database\init$SID.ora 加入三个必要的参数

db_name=PROD
db_recovery_file_dest='D:\APP\MACLEAN.LIU\FLASH_RECOVERY_AREA'
db_recovery_file_dest_size=9999M




RMAN>  restore spfile from autobackup;

Starting restore at 04-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=130 device type=DISK

recovery area destination: D:\APP\MACLEAN.LIU\FLASH_RECOVERY_AREA
database name (or database unique name) used for search: PROD
channel ORA_DISK_1: AUTOBACKUP D:\APP\MACLEAN.LIU\FLASH_RECOVERY_AREA\PROD\AUTOBACKUP\2012_05_04\O1_MF_S_782436461_7T7XCGDM_.BKP found in the recovery are
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring spfile from AUTOBACKUP D:\APP\MACLEAN.LIU\FLASH_RECOVERY_AREA\PROD\AUTOBACKUP\2012_05_04\O1_MF_S_782436461_7T7XCGDM_.BKP
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 04-MAY-12



为什么这里没有指定dbid 也能成功？ 因为restore from autobackup 优先使用了 FLASH_RECOVERY_AREA  

详见 http://t.askmaclean.com/thread-883-1-1.html 中的解释


问题2 我想不需要我再多解释了

gdpr-dba

原帖由 几番风雨 于 2012-5-4 11:35 发表
非catalog的rman备份信息记录在控制文件中，nomount没有加载控制文件，找不到备份这个可以理解吧？  问题1 2 应该可以解释了

问题3 准确的说控制文件的备份格式是 %F

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVI ...

谢谢你的回复，不过没有控制文件不代表找不到备份，吗，备份集的头部都有dbid的信息，rman应该可以根据dbid查找的。

几番风雨

非catalog的rman备份信息记录在控制文件中，nomount没有加载控制文件，找不到备份这个可以理解吧？  问题1 2 应该可以解释了

问题3 准确的说控制文件的备份格式是 %F

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default