备份数据文件,模拟磁盘损坏时,还原恢复数据文件.
首先,查询数据文件序号,备份数据文件,可根据数据文件序号指定备份的数据文件.
SQL> set linesize 1000
SQL> select file_name,file_id from dba_data_files; FILE_NAME FILE_ID
------------------------------------------------------------------------------------------
/data2/orcl/users01.dbf 4
/data2/orcl/undotbs01.dbf 3
/data2/orcl/sysaux01.dbf 2
/data2/orcl/system01.dbf 1
1.1查询数据文件序号
备份数据文件,可单个数据文件备份,即镜像备份,如backup as copy datafile 1 ;也可以将多个数据文件作为备份集进行备份: datafile 1,2,3,4
[oracle@TEST144239 ~]$ rman target sys/Sina.2015 Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 14 11:52:47 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1420421951) RMAN> backup as copy datafile 1
2> format '/data2/backup/df1_%d_%U'; Starting backup at 14-OCT-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=76 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=133 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/data2/orcl/system01.dbf
output file name=/data2/backup/df1_ORCL_data_D-ORCL_I-1420421951_TS-SYSTEM_FNO-1_1vqjmdom tag=TAG20151014T115254 RECID=6 STAMP=893073252
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:26
Finished backup at 14-OCT-15 Starting Control File and SPFILE Autobackup at 14-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2015_10_14/o1_mf_s_893073260_c1vnhhjp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-OCT-15 RMAN> list copy ;
specification does not match any control file copy in the repository
List of Datafile Copies
======================= Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
6 1 A 14-OCT-15 1690358 14-OCT-15
Name: /data2/backup/df1_ORCL_data_D-ORCL_I-1420421951_TS-SYSTEM_FNO-1_1vqjmdom
Tag: TAG20151014T115254 RMAN> backup datafile 1,2,3,4 format '/data2/backup/df_%d_%T_%U'; Starting backup at 14-OCT-15
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/data2/orcl/system01.dbf
input datafile file number=00003 name=/data2/orcl/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 14-OCT-15
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=/data2/orcl/sysaux01.dbf
input datafile file number=00004 name=/data2/orcl/users01.dbf
channel ORA_DISK_2: starting piece 1 at 14-OCT-15
channel ORA_DISK_2: finished piece 1 at 14-OCT-15
piece handle=/data2/backup/df_ORCL_20151014_22qjme43_1_1 tag=TAG20151014T115858 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:35
channel ORA_DISK_1: finished piece 1 at 14-OCT-15
piece handle=/data2/backup/df_ORCL_20151014_21qjme43_1_1 tag=TAG20151014T115858 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
Finished backup at 14-OCT-15 Starting Control File and SPFILE Autobackup at 14-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2015_10_14/o1_mf_s_893073655_c1vnvsmo_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-OCT-15
1.2备份数据文件
1. 删除数据文件
[root@TEST144239 backup]# su - oracle
[oracle@TEST144239 ~]$ sqlplus "/ as sysdba" SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 14 14:48:09 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> ! rm /data2/orcl/users01.dbf 2.查看下是否删除:
[oracle@TEST144239 orcl]$ ll
总用量 1712960
-rw-r----- 1 oracle oinstall 9846784 10月 14 14:50 control01.ctl
-rw-r----- 1 oracle oinstall 9748480 10月 12 15:18 control01.ctl.bak
drwxr-xr-x 3 oracle oinstall 4096 10月 10 10:48 recover_log -rw-r----- 1 oracle oinstall 52429312 10月 14 04:00 redo01.log
-rw-r----- 1 oracle oinstall 52429312 10月 14 11:37 redo02.log
-rw-r----- 1 oracle oinstall 52429312 10月 14 14:50 redo03.log
-rw-r----- 1 oracle oinstall 723525632 10月 14 14:50 sysaux01.dbf
-rw-r----- 1 oracle oinstall 754982912 10月 14 14:49 system01.dbf
-rw-r----- 1 oracle oinstall 30416896 10月 14 14:28 temp01.dbf
-rw-r----- 1 oracle oinstall 94380032 10月 14 14:50 undotbs01.dbf
2.1试验(删除数据文件)
SQL> startup mount force;
ORACLE instance started. Total System Global Area 1937457152 bytes
Fixed Size 2229584 bytes
Variable Size 1241516720 bytes
Database Buffers 687865856 bytes
Redo Buffers 5844992 bytes
Database mounted. [oracle@TEST144239 orcl]$ rman
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 14 15:51:41 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target sys/Sina.2015@study
connected to target database: ORCL (DBID=1420421951, not open) RMAN> run{
2> set newname for datafile 4 to '/data2/orcl/user01.dbf';
3> restore database;
4> switch datafile all;
5> recover database;
6> alter database open;} executing command: SET NEWNAME Starting restore at 14-OCT-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=6 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=67 device type=DISK channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /data2/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /data2/orcl/user01.dbf
channel ORA_DISK_1: reading from backup piece /data2/backup/df_ORCL_20151014_22qjme43_1_1
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to /data2/orcl/system01.dbf
channel ORA_DISK_2: restoring datafile 00003 to /data2/orcl/undotbs01.dbf
channel ORA_DISK_2: reading from backup piece /data2/backup/df_ORCL_20151014_21qjme43_1_1
channel ORA_DISK_1: piece handle=/data2/backup/df_ORCL_20151014_22qjme43_1_1 tag=TAG20151014T115858
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:02
channel ORA_DISK_2: piece handle=/data2/backup/df_ORCL_20151014_21qjme43_1_1 tag=TAG20151014T115858
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:01:12
Finished restore at 14-OCT-15 datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=893088213 file name=/data2/orcl/user01.dbf Starting recover at 14-OCT-15
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3 starting media recovery
media recovery complete, elapsed time: 00:00:16 Finished recover at 14-OCT-15 database opened SQL> select file#,name,status from v$datafile where file#=4; FILE# NAME STATUS
4 /data2/orcl/user01.dbf ONLINE SQL> select * from scott.dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
2.2恢复数据文件