案例5：将数据文件恢复到新的位置
1、模拟环境
SQL> create table lxtb01(id int) tablespace lx01;
SQL> insert into lxtb01 values(1);
1 row created.
SQL> insert into lxtb01 values(2);
1 row created.
SQL> select * from lxtb01;
ID
----------
1
2
SQL> commit;
Commit complete.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> !
[oracle@solaris10 ~]$rm /u01/app/oracle/oradata/anny/lx01.dbf
[oracle@solaris10 ~]$exit
exit
SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1279964 bytes
Variable Size 58722340 bytes
Database Buffers 251658240 bytes
Redo Buffers 2912256 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/anny/lx01.dbf'
SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
6 FILE NOT FOUND
2、对数据文件进行恢复，并恢复到新的位置
SQL> alter database datafile 6 offline;
Database altered
[oracle@solaris10 oradata]$cp /disk1/backup/anny/cold_bak/lx01.dbf /disk1/oradata/anny/——新的位置
SQL> alter database open;
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/anny/lx01.dbf'
2 to '/disk1/oradata/anny/lx01.dbf';
Database altered.
SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile 6 online;
Database altered.
SQL> select * from scott.lxtb01;
ID
----------
1
2
SQL> col file_name for a50
SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- --------------------------
5 /u01/app/oracle/oradata/anny/test01.dbf TEST
4 /u01/app/oracle/oradata/anny/user01.dbf USERS
3 /u01/app/oracle/oradata/anny/sysaux01.dbf SYSAUX
2 /u01/app/oracle/oradata/anny/lx02.dbf LX02
1 /u01/app/oracle/oradata/anny/system01.dbf SYSTEM
6 /disk1/oradata/anny/lx01.dbf LX01
7 /u01/app/oracle/oradata/anny/undotbs01.dbf UNDOTBS
8 /u01/app/oracle/oradata/anny/lx03.dbf LX03
9 /u01/app/oracle/oradata/anny/lx04.dbf LX04
10 /u01/app/oracle/oradata/anny/index01.dbf INDEXES
12 /u01/app/oracle/oradata/anny/cuug01.dbf CUUG
11 /u01/app/oracle/oradata/anny/test02.dbf TEST
12 rows selected.
3、将数据文件迁移到原来的位置
SQL> alter tablespace lx01 offline;
Tablespace altered.
[oracle@solaris10 ~]$cp /disk1/oradata/anny/lx01.dbf /u01/app/oracle/oradata/anny/lx01.dbf——将文件拷回来
SQL> alter database rename file '/disk1/oradata/anny/lx01.dbf'
2 to '/u01/app/oracle/oradata/anny/lx01.dbf';
Database altered.
SQL> alter tablespace lx01 online;
Tablespace altered.
SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ----------------------
5 /u01/app/oracle/oradata/anny/test01.dbf TEST
4 /u01/app/oracle/oradata/anny/user01.dbf USERS
3 /u01/app/oracle/oradata/anny/sysaux01.dbf SYSAUX
2 /u01/app/oracle/oradata/anny/lx02.dbf LX02
1 /u01/app/oracle/oradata/anny/system01.dbf SYSTEM
6 /u01/app/oracle/oradata/anny/lx01.dbf LX01
7 /u01/app/oracle/oradata/anny/undotbs01.dbf UNDOTBS
8 /u01/app/oracle/oradata/anny/lx03.dbf LX03
9 /u01/app/oracle/oradata/anny/lx04.dbf LX04
10 /u01/app/oracle/oradata/anny/index01.dbf INDEXES
12 /u01/app/oracle/oradata/anny/cuug01.dbf CUUG
11 /u01/app/oracle/oradata/anny/test02.dbf TEST
12 rows selected.
4、删除旧文件
[oracle@solaris10 ~]$rm /disk1/oradata/anny/lx01.dbf
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
