测试条件:
1.RMAN的配置如下:
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;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 500 M;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/oracle/product/9.2.0/dbs/snapcf_DBAP01.f'; # default
2.数据库的备份没有了,但归档的和未归档的日志文件都在;
3.新建的数据文件丢失了,这个数据文件还从来没有备份过;
4.当前的所有控制文件都丢失了;
要求:把丢失的数据文件恢复回来.
完整的测试过程:
创建一个Tablespace,在move一个Table上去:
SQL> create tablespace ts_home
2 datafile '/u02/oradata/DBAP01/ts_home01.dbf' size 10m;
Tablespace created.
Elapsed: 00:00:01.37
SQL> alter table scott.t6 move tablespace ts_home;
Table altered.
Elapsed: 00:00:03.05
SQL> select tablespace_name from dba_segments where segment_name='T6';
TABLESPACE_NAME
------------------------------
TS_HOME
Elapsed: 00:00:00.09
SQL> select count(*) from scott.t6;
COUNT(*)
----------
28750
Elapsed: 00:00:00.03
SQL>
在RMAN中关闭数据库:
RMAN> shutdown abort
Oracle instance shut down
RMAN>
到相应的目录删除数据文件和所有控制文件及备份的数据文件:
bash-2.03$ ls -l
total 20105652
-rw-r----- 1 oracle oinstall 1199816704 Mar 2 12:57 df_DBAP01_584024242_153_1.bak
-rw-r----- 1 oracle oinstall 1155883008 Mar 2 12:58 df_DBAP01_584024287_154_1.bak
-rw-r----- 1 oracle oinstall 1054892032 Mar 2 12:59 df_DBAP01_584024332_155_1.bak
-rw-r----- 1 oracle oinstall 984891392 Mar 2 13:00 df_DBAP01_584024387_156_1.bak
-rw-r----- 1 oracle oinstall 891461632 Mar 2 13:01 df_DBAP01_584024442_157_1.bak
-rw-r----- 1 oracle oinstall 1571045376 Mar 2 13:02 df_DBAP01_584024498_158_1.bak
-rw-r----- 1 oracle oinstall 1541619712 Mar 2 13:03 df_DBAP01_584024563_159_1.bak
-rw-r----- 1 oracle oinstall 1889370112 Mar 2 13:04 df_DBAP01_584024618_160_1.bak
-rw-r----- 1 oracle oinstall 1536 Nov 23 09:04 orapwDBAP01
bash-2.03$ rm *.*
bash-2.03$ ls -l
total 4
-rw-r----- 1 oracle oinstall 1536 Nov 23 09:04 orapwDBAP01
bash-2.03$ cd /u02/oradata/DBAP01/
bash-2.03$ ls -l
total 25625792
-rw-r----- 1 oracle oinstall 2482176 Mar 2 13:23 control01.ctl
-rw-r----- 1 oracle oinstall 2482176 Mar 2 13:23 control02.ctl
-rw-r----- 1 oracle oinstall 2482176 Mar 2 13:23 control03.ctl
... ...
-rw-r----- 1 oracle oinstall 10493952 Mar 2 13:20 ts_home01.dbf
... ...
bash-2.03$ rm control01.ctl
bash-2.03$ rm control02.ctl
bash-2.03$ rm control03.ctl
bash-2.03$ rm ts_home01.dbf
bash-2.03$
启动数据库到nomount状态:
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 322929752 bytes
Fixed Size 730200 bytes
Variable Size 201326592 bytes
Database Buffers 117440512 bytes
Redo Buffers 3432448 bytes
RMAN>
转存控制文件:
RMAN> restore controlfile from autobackup;
ing restore at 02-MAR-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: looking for autobackup on day: 20060302
channel ORA_DISK_1: autobackup found: c-3635722556-20060302-03
channel ORA_DISK_1: controlfile restore from autobackup complete
replicating controlfile
input filename=/u02/oradata/DBAP01/control01.ctl
output filename=/u02/oradata/DBAP01/control02.ctl
output filename=/u02/oradata/DBAP01/control03.ctl
Finished restore at 02-MAR-06
RMAN>
修改数据库到mount状态:
RMAN> alter database mount;
database mounted
RMAN>
此时的数据库中是没有ts_home的信息的:
SQL> select * from v$tablespace where name='TS_HOME';
no rows selected
Elapsed: 00:00:00.02
SQL> select name from v$datafile where name like '%ts_home%';
no rows selected
Elapsed: 00:00:00.02
SQL>
恢复数据库:
RMAN> recover database;
Starting recover at 02-MAR-06
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 1 is already on disk as file /u02/oradata/DBAP01/redo01.log
archive log filename=/u02/oradata/DBAP01/redo01.log thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/02/2006 13:34:26
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u02/oradata/DBAP01/redo01.log'
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 25: '/u02/oradata/DBAP01/ts_home01.dbf'
RMAN>
错误信息提示没有数据文件记录在控制文件中:
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 25: '/u02/oradata/DBAP01/ts_home01.dbf'
创建数据文件:
SQL> alter database create datafile 25 as '/u02/oradata/DBAP01/ts_home01.dbf';
Database altered.
Elapsed: 00:00:00.40
SQL>
继续做recover:
RMAN> recover database;
Starting recover at 02-MAR-06
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 1 is already on disk as file /u02/oradata/DBAP01/redo01.log
archive log filename=/u02/oradata/DBAP01/redo01.log thread=1 sequence=1
media recovery complete
Finished recover at 02-MAR-06
RMAN>
数据恢复成功,确认数据文件是否恢复回来:
SQL> select name from v$datafile where name like '%ts_home%';
NAME
--------------------------------------------------
/u02/oradata/DBAP01/ts_home01.dbf
Elapsed: 00:00:00.01
SQL> select * from v$tablespace where name='TS_HOME';
TS# NAME INC
---------- ------------------------------ ---
30 TS_HOME YES
Elapsed: 00:00:00.00
SQL>
以resetlogs的方式大开数据库:
RMAN> alter database open resetlogs;
database opened
RMAN>
确认数据是否恢复回来:
SQL> l
1* select segment_name from dba_segments where tablespace_name='TS_HOME'
SQL> /
SEGMENT_NAME
---------------------------------------------------------------------------------
T6
Elapsed: 00:00:00.27
SQL> select count(*) from scott.t6;
COUNT(*)
----------
28750
Elapsed: 00:00:00.05
SQL>
至此,数据库恢复成功。在这个过程中,我们不需要去找出数据文件的创建时间,RMAN会自动把数据库恢复到创建数据文件时的那个时间点。