手工完全恢复（六）

 

案例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