联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
oracle官方建议，如果修改过数据库结构后，需要立即重新备份数据库，我想通过试验验证该知识点。
试验过程是使用rman备份数据库，然后添加表空间，添加数据文件，创建表在新表空间和新数据文件上，然后关闭数据库，删除新添加的数据文件，再使用rman备份来恢复数据库。操作过程如下：
1、当前数据库表空间已经数据文件情况
SQL> select name from v$tablespace;
NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
SQL> select file#,name from v$datafile;
FILE# NAME
———- ————————————————–
1 /opt/oracle/oradata/test/system01.dbf
2 /opt/oracle/oradata/test/undotbs01.dbf
3 /opt/oracle/oradata/test/sysaux01.dbf
4 /opt/oracle/oradata/test/users01.dbf
5 /opt/oracle/oradata/test/user32g.dbf
2、rman备份数据库
[oracle@ECP-UC-DB1 ~]$ $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Sat Aug 13 21:44:36 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TEST (DBID=2056006906)
RMAN> backup database format ‘/tmp/%U’ plus archivelog delete input;
Starting backup at 2011-08-13 21:46:46
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=3 recid=1 stamp=757977034
input archive log thread=1 sequence=4 recid=2 stamp=758152833
input archive log thread=1 sequence=5 recid=3 stamp=758320953
input archive log thread=1 sequence=6 recid=4 stamp=758321218
input archive log thread=1 sequence=7 recid=5 stamp=758412073
input archive log thread=1 sequence=8 recid=6 stamp=758574035
input archive log thread=1 sequence=9 recid=7 stamp=758665608
input archive log thread=1 sequence=10 recid=8 stamp=758757646
input archive log thread=1 sequence=11 recid=9 stamp=758844058
input archive log thread=1 sequence=12 recid=10 stamp=758930497
input archive log thread=1 sequence=13 recid=11 stamp=759027608
input archive log thread=1 sequence=14 recid=12 stamp=759102408
channel ORA_DISK_1: starting piece 1 at 2011-08-13 21:46:50
channel ORA_DISK_1: finished piece 1 at 2011-08-13 21:47:35
piece handle=/opt/oracle/flash_recovery_area/TEST/backupset/2011_08_13/o1_mf_annnn_TAG20110813T214648_74f02bg3_.bkp tag=TAG20110813T214648 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/opt/oracle/oradata/test/archivelog/1_3_757860476.dbf recid=1 stamp=757977034
archive log filename=/opt/oracle/oradata/test/archivelog/1_4_757860476.dbf recid=2 stamp=758152833
archive log filename=/opt/oracle/oradata/test/archivelog/1_5_757860476.dbf recid=3 stamp=758320953
archive log filename=/opt/oracle/oradata/test/archivelog/1_6_757860476.dbf recid=4 stamp=758321218
archive log filename=/opt/oracle/oradata/test/archivelog/1_7_757860476.dbf recid=5 stamp=758412073
archive log filename=/opt/oracle/oradata/test/archivelog/1_8_757860476.dbf recid=6 stamp=758574035
archive log filename=/opt/oracle/oradata/test/archivelog/1_9_757860476.dbf recid=7 stamp=758665608
archive log filename=/opt/oracle/oradata/test/archivelog/1_10_757860476.dbf recid=8 stamp=758757646
archive log filename=/opt/oracle/oradata/test/archivelog/1_11_757860476.dbf recid=9 stamp=758844058
archive log filename=/opt/oracle/oradata/test/archivelog/1_12_757860476.dbf recid=10 stamp=758930497
archive log filename=/opt/oracle/oradata/test/archivelog/1_13_757860476.dbf recid=11 stamp=759027608
archive log filename=/opt/oracle/oradata/test/archivelog/1_14_757860476.dbf recid=12 stamp=759102408
Finished backup at 2011-08-13 21:47:36
Starting backup at 2011-08-13 21:47:37
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/opt/oracle/oradata/test/system01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/test/sysaux01.dbf
input datafile fno=00002 name=/opt/oracle/oradata/test/undotbs01.dbf
input datafile fno=00005 name=/opt/oracle/oradata/test/user32g.dbf
input datafile fno=00004 name=/opt/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2011-08-13 21:47:37
channel ORA_DISK_1: finished piece 1 at 2011-08-13 21:48:12
piece handle=/tmp/06mjtuvp_1_1 tag=TAG20110813T214737 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 2011-08-13 21:48:14
channel ORA_DISK_1: finished piece 1 at 2011-08-13 21:48:15
piece handle=/tmp/07mjtv0s_1_1 tag=TAG20110813T214737 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2011-08-13 21:48:15
Starting backup at 2011-08-13 21:48:15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=15 recid=13 stamp=759102495
channel ORA_DISK_1: starting piece 1 at 2011-08-13 21:48:16
channel ORA_DISK_1: finished piece 1 at 2011-08-13 21:48:17
piece handle=/opt/oracle/flash_recovery_area/TEST/backupset/2011_08_13/o1_mf_annnn_TAG20110813T214815_74f050vl_.bkp tag=TAG20110813T214815 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/opt/oracle/oradata/test/archivelog/1_15_757860476.dbf recid=13 stamp=759102495
Finished backup at 2011-08-13 21:48:17
3、添加表空间/数据文件
SQL> create tablespace xff datafile ‘/opt/oracle/oradata/test/xifenfei01.dbf’ size 10m autoextend on next 10m maxsize 5G;
Tablespace created.
SQL> alter tablespace users add datafile ‘/opt/oracle/oradata/test/user02.dbf’ size 10m autoextend on next 10m maxsize 5G;
Tablespace altered.
4、创建测试表
SQL> create table chf.t_1 tablespace xff
2 as
3 select * from all_objects;
Table created.
SQL> create table chf.t_2 tablespace users
2 as
3 select * from all_objects;
Table created.
SQL> select count(*) from chf.t_1;
COUNT(*)
———-
49855
SQL> select count(*) from chf.t_2;
COUNT(*)
———-
49856
SQL> select file#,name from v$datafile;
FILE# NAME
———- ————————————————–
1 /opt/oracle/oradata/test/system01.dbf
2 /opt/oracle/oradata/test/undotbs01.dbf
3 /opt/oracle/oradata/test/sysaux01.dbf
4 /opt/oracle/oradata/test/users01.dbf
5 /opt/oracle/oradata/test/user32g.dbf
6 /opt/oracle/oradata/test/xifenfei01.dbf
7 /opt/oracle/oradata/test/user02.dbf
7 rows selected.
5、关闭数据库
SQL> shutdown abort
ORACLE instance shut down.
6、删除相关数据文件
[oracle@ECP-UC-DB1 ~]$ cd /opt/oracle/oradata/test/
[oracle@ECP-UC-DB1 test]$ ll
total 1066968
drwxr-xr-x 2 oracle oinstall 4096 Aug 13 21:48 archivelog
-rw-r—– 1 oracle oinstall 7061504 Aug 13 21:56 control01.ctl
-rw-r—– 1 oracle oinstall 7061504 Aug 13 21:56 control02.ctl
-rw-r—– 1 oracle oinstall 7061504 Aug 13 21:56 control03.ctl
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:55 redo01.log
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:46 redo02.log
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:48 redo03.log
-rw-r—– 1 oracle oinstall 335552512 Aug 13 21:48 sysaux01.dbf
-rw-r—– 1 oracle oinstall 513810432 Aug 13 21:55 system01.dbf
-rw-r—– 1 oracle oinstall 20979712 Aug 13 06:00 temp01.dbf
-rw-r—– 1 oracle oinstall 26222592 Aug 13 21:55 undotbs01.dbf
-rw-r—– 1 oracle oinstall 10493952 Aug 13 21:55 user02.dbf
-rw-r—– 1 oracle oinstall 10493952 Aug 13 21:55 user32g.dbf
-rw-r—– 1 oracle oinstall 5251072 Aug 13 21:55 users01.dbf
-rw-r—– 1 oracle oinstall 10493952 Aug 13 21:55 xifenfei01.dbf
[oracle@ECP-UC-DB1 test]$ rm xifenfei01.dbf
[oracle@ECP-UC-DB1 test]$ rm user*.dbf
[oracle@ECP-UC-DB1 test]$ ll
total 1031036
drwxr-xr-x 2 oracle oinstall 4096 Aug 13 21:48 archivelog
-rw-r—– 1 oracle oinstall 7061504 Aug 13 21:56 control01.ctl
-rw-r—– 1 oracle oinstall 7061504 Aug 13 21:56 control02.ctl
-rw-r—– 1 oracle oinstall 7061504 Aug 13 21:56 control03.ctl
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:55 redo01.log
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:46 redo02.log
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:48 redo03.log
-rw-r—– 1 oracle oinstall 335552512 Aug 13 21:48 sysaux01.dbf
-rw-r—– 1 oracle oinstall 513810432 Aug 13 21:55 system01.dbf
-rw-r—– 1 oracle oinstall 20979712 Aug 13 06:00 temp01.dbf
-rw-r—– 1 oracle oinstall 26222592 Aug 13 21:55 undotbs01.dbf
6、开启数据库
SQL> startup
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 2082784 bytes
Variable Size 125831200 bytes
Database Buffers 75497472 bytes
Redo Buffers 6303744 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/opt/oracle/oradata/test/users01.dbf’
7、alert.log文件报错
Sat Aug 13 21:58:22 2011
ALTER DATABASE OPEN
Sat Aug 13 21:58:22 2011
Errors in file /opt/oracle/admin/test/bdump/test_dbw0_25268.trc:
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/opt/oracle/oradata/test/users01.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sat Aug 13 21:58:22 2011
Errors in file /opt/oracle/admin/test/bdump/test_dbw0_25268.trc:
ORA-01157: cannot identify/lock data file 5 – see DBWR trace file
ORA-01110: data file 5: ‘/opt/oracle/oradata/test/user32g.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sat Aug 13 21:58:22 2011
Errors in file /opt/oracle/admin/test/bdump/test_dbw0_25268.trc:
ORA-01157: cannot identify/lock data file 6 – see DBWR trace file
ORA-01110: data file 6: ‘/opt/oracle/oradata/test/xifenfei01.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sat Aug 13 21:58:22 2011
Errors in file /opt/oracle/admin/test/bdump/test_dbw0_25268.trc:
ORA-01157: cannot identify/lock data file 7 – see DBWR trace file
ORA-01110: data file 7: ‘/opt/oracle/oradata/test/user02.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN…
8、登录rman
[oracle@ECP-UC-DB1 test]$ $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Sat Aug 13 22:00:03 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TEST (DBID=2056006906, not open)
9、rman还原预览
RMAN> restore datafile 4,5,6,7 preview;
Starting restore at 2011-08-13 22:01:29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
data file 6 will be created automatically during restore operation
data file 7 will be created automatically during restore operation
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————-
4 Full 602.67M DISK 00:00:30 2011-08-13 21:47:38
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20110813T214737
Piece Name: /tmp/06mjtuvp_1_1
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——————- —-
4 Full 1177858 2011-08-13 21:47:37 /opt/oracle/oradata/test/users01.dbf
5 Full 1177858 2011-08-13 21:47:37 /opt/oracle/oradata/test/user32g.dbf
using channel ORA_DISK_1
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
——- ———- ———– ———— ——————-
6 2.00K DISK 00:00:01 2011-08-13 21:48:16
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20110813T214815
Piece Name: /opt/oracle/flash_recovery_area/TEST/backupset/2011_08_13/o1_mf_annnn_TAG20110813T214815_74f050vl_.bkp
List of Archived Logs in backup set 6
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——————- ———- ———
1 15 1177835 2011-08-13 21:46:46 1177874 2011-08-13 21:48:15
Media recovery start SCN is 1177858
Recovery must be done beyond SCN 1178068 to clear data files fuzziness
Finished restore at 2011-08-13 22:01:30
10、还原数据文件
RMAN> restore datafile 4,5,6,7;
Starting restore at 2011-08-13 22:02:04
using channel ORA_DISK_1
creating datafile fno=6 name=/opt/oracle/oradata/test/xifenfei01.dbf
creating datafile fno=7 name=/opt/oracle/oradata/test/user02.dbf
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /opt/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /opt/oracle/oradata/test/user32g.dbf
channel ORA_DISK_1: reading from backup piece /tmp/06mjtuvp_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/06mjtuvp_1_1 tag=TAG20110813T214737
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 2011-08-13 22:02:08
11、查看恢复后的数据文件情况
[oracle@ECP-UC-DB1 ~]$ cd /opt/oracle/oradata/test
[oracle@ECP-UC-DB1 test]$ ll
total 1066968
drwxr-xr-x 2 oracle oinstall 4096 Aug 13 21:48 archivelog
-rw-r—– 1 oracle oinstall 7061504 Aug 13 22:03 control01.ctl
-rw-r—– 1 oracle oinstall 7061504 Aug 13 22:03 control02.ctl
-rw-r—– 1 oracle oinstall 7061504 Aug 13 22:03 control03.ctl
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:55 redo01.log
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:46 redo02.log
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:48 redo03.log
-rw-r—– 1 oracle oinstall 335552512 Aug 13 21:58 sysaux01.dbf
-rw-r—– 1 oracle oinstall 513810432 Aug 13 21:58 system01.dbf
-rw-r—– 1 oracle oinstall 20979712 Aug 13 06:00 temp01.dbf
-rw-r—– 1 oracle oinstall 26222592 Aug 13 21:58 undotbs01.dbf
-rw-r—– 1 oracle oinstall 10493952 Aug 13 22:02 user02.dbf
-rw-r—– 1 oracle oinstall 10493952 Aug 13 22:02 user32g.dbf
-rw-r—– 1 oracle oinstall 5251072 Aug 13 22:02 users01.dbf
-rw-r—– 1 oracle oinstall 10493952 Aug 13 22:02 xifenfei01.dbf
12、恢复数据文件
RMAN> recover datafile 4,5,6,7;
Starting recover at 2011-08-13 22:04:21
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 2011-08-13 22:04:25
13、open数据库
RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
14、验证rman恢复结果
[oracle@ECP-UC-DB1 test]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sat Aug 13 22:05:25 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select file#,name from v$datafile;
FILE# NAME
———- ————————————————–
1 /opt/oracle/oradata/test/system01.dbf
2 /opt/oracle/oradata/test/undotbs01.dbf
3 /opt/oracle/oradata/test/sysaux01.dbf
4 /opt/oracle/oradata/test/users01.dbf
5 /opt/oracle/oradata/test/user32g.dbf
6 /opt/oracle/oradata/test/xifenfei01.dbf
7 /opt/oracle/oradata/test/user02.dbf
7 rows selected.
SQL> select count(*) from chf.t_1;
COUNT(*)
———-
49855
SQL> select count(*) from chf.t_2;
COUNT(*)
———-
49856
注：如果在rman的还原过程中，没有自动创建数据文件，需要用命令创建alter database datafile n或者alter database datafile ‘path’，然后进行恢复
加我QQ
