RMAN恢复案例三 — 所有控制文件丢失(1)

congkutu8778

墨衍会员 · AI 创作全网分发码龄11年

关注

转载最新推荐文章于 2026-03-29 10:36:45 发布 140 阅读 AI 写同款· GEO 优化 ›

所有控制文件丢失，需要从备份的控制文件恢复。

1. 数据库基本信息

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\SYSTEM01.DBF
D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\UNDOTBS01.DBF
D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\USERS01.DBF

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\opt\oracle9i\ArchiveLog\ORA92US
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence           9
SQL> select name from v$archived_log;

NAME
--------------------------------------------------------------------------------
D:\OPT\ORACLE9I\ARCHIVELOG\ORA92US\ARC00008.001

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\CONTROL01.CTL
D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\CONTROL02.CTL
D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\CONTROL03.CTL

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

2. 启动controlfile autobackup

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

3. 全备之后删除所有控制文件和数据文件

RMAN> backup database format 'D:\ora_bak\RMAN\ORA92US\df_%d_%s_%p.bak';

Starting backup at 2008-04-09 13:20:03
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\SYSTEM01.DBF
input datafile fno=00002 name=D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\UNDOTBS01.DBF
input datafile fno=00003 name=D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 2008-04-09 13:20:04
channel ORA_DISK_1: finished piece 1 at 2008-04-09 13:20:39
piece handle=D:\ORA_BAK\RMAN\ORA92US\DF_ORA92US_3_1.BAK comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36
Finished backup at 2008-04-09 13:20:39

Starting Control File and SPFILE Autobackup at 2008-04-09 13:20:39
piece handle=D:\OPT\ORACLE9I\PRODUCT\9.2.0\DATABASE\C-3206997025-20080409-00 comment=NONE
Finished Control File and SPFILE Autobackup at 2008-04-09 13:20:41

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

删除所有控制文件和数据文件。

4. 恢复过程

C:\Documents and Settings\p485224>rman target sys/oracle

Recovery Manager: Release 9.2.0.1.0 - Production

connected to target database (not started)

RMAN> startup

Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 04/09/2008 13:25:01
ORA-00205: error in identifying controlfile, check alert log for more info

//尝试启动失败，此时oracle已经处于nomount状态下

RMAN> restore controlfile from autobackup
2> ;

Starting restore at 2008-04-09 13:25:21

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/09/2008 13:25:22
RMAN-06495: must explicitly specify DBID with SET DBID command

//必须指定DBID!

RMAN> set dbid=3206997025;

executing command: SET DBID

RMAN> restore controlfile from autobackup;

Starting restore at 2008-04-09 13:25:55

using channel ORA_DISK_1
channel ORA_DISK_1: looking for autobackup on day: 20080409
channel ORA_DISK_1: autobackup found: c-3206997025-20080409-00
channel ORA_DISK_1: controlfile restore from autobackup complete
replicating controlfile
input filename=D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\CONTROL01.CTL
output filename=D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\CONTROL02.CTL
output filename=D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\CONTROL03.CTL
Finished restore at 2008-04-09 13:26:04

RMAN> sql 'alter database mount';

sql statement: alter database mount

RMAN> restore database;

Starting restore at 2008-04-09 13:26:25

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 D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\SYSTEM01.DBF
restoring datafile 00002 to D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\UNDOTBS01.DBF
restoring datafile 00003 to D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ORA_BAK\RMAN\ORA92US\DF_ORA92US_3_1.BAK tag=TAG20080409T132003 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 2008-04-09 13:27:02

RMAN> recover database;

Starting recover at 2008-04-09 13:27:08
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 9 is already on disk as file D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\REDO03.LOG
archive log filename=D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\REDO03.LOG thread=1 sequence=9
media recovery complete
Finished recover at 2008-04-09 13:27:08

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 04/09/2008 13:27:16
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

database opened

RMAN> list incarnation of database;

List of Database Incarnations
DB Key Inc Key DB Name DB ID            CUR Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORA92US 3206997025       NO 1          2008-03-17 15:57:21
2       2       ORA92US 3206997025       YES 1615082    2008-04-09 13:27:23

RMAN>

PS:如何获取DBID

1. 连接RMAN后可获得

C:\Documents and Settings\p485224>rman target sys/oracle

Recovery Manager: Release 9.2.0.1.0 - Production

connected to target database: ORA92US (DBID=3206997025)

2. 从v$database中获得

SQL> select dbid from v$database;

DBID
----------
3206997025

3. controlfile 自动备份如果采用的是默认的命名规则，则名称中包含DBID

D:\OPT\ORACLE9I\PRODUCT\9.2.0\DATABASE\C-3206997025-20080409-00

4. eygle介绍的一种方法，没有试验过

SQL> select eygle.get_dbid('/opt/oracle/oradata/conner','user02.dbf') from dual;

EYGLE.GET_DBID('/OPT/ORACLE/OR
------------------------------
3152029224

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9765498/viewspace-232256/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9765498/viewspace-232256/

Oracle DB 使用RMAN执行恢复 • 在丢失关键或非关键数据文件后执行完全恢复 • 使用增量更新的备份进行恢复 • 切换到映像副本进行快速恢复 • 将数据库还原到新主机上 • 使用备份控制文件进行恢复阅读详情

RMAN恢复案例三 — 所有控制文件丢失(1)

相关推荐