仅仅丢失一个普通用户数据文件的恢复B(脱机恢复)

*仅仅丢失一个普通用户数据文件的恢复B(脱机恢复)

请大家在不同的题目下跟贴，共同讨论
  
再来一个：
  
*仅仅丢失一个普通用户数据文件的恢复B(脱机恢复)
准备工作
按照下面的输入，如果全部恢复，应该可以看到insert into test1 values(13)，因为insert into test1 values(14)没提交。
SQL> conn lunar/lunar
Connected.
SQL> insert into test1 values(13);
  
1 row created.
  
SQL> commit;
  
Commit complete.
  
SQL> insert into test1 values(14);
  
1 row created.
  
Shutdown immediate，然后模拟数据文件丢失
单开一个session，执行shutdown immediate（保证insert into test1 values(14);没有被隐士提交）
  
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.
  
E:/>sqlplus internal
  
SQL*Plus: Release 8.1.7.0.0 - Production on 星期一 10月 21 02:36:07 2002
  
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
  
连接到:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
  
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>
模拟数据文件丢失，然后用热备覆盖这个文件
mount数据库
E:/>sqlplus internal
  
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Oct 21 02:42:47 2002
  
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
  
Connected to an idle instance.
  
SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SQL> startup mount
ORACLE instance started.
  
Total System Global Area   25856028 bytes
Fixed Size                    75804 bytes
Variable Size               8925184 bytes
Database Buffers           16777216 bytes
Redo Buffers                  77824 bytes
Database mounted.
SQL>
使损坏的数据文件脱机
SQL> alter database datafile 'D:/BACKUPDB/USERS01.DBF' offline;
  
Database altered.
  
恢复数据文件
SQL> recover datafile 'D:/BACKUPDB/USERS01.DBF';
ORA-00279: change 424116 generated at 10/20/2002 20:42:04 needed for thread 1
ORA-00289: suggestion : D:/BACKUPDB/ARCHIVE/BACKUPT001S00001.ARC
ORA-00280: change 424116 for thread 1 is in sequence #1
  
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 424125 generated at 10/20/2002 20:44:14 needed for thread 1
ORA-00289: suggestion : D:/BACKUPDB/ARCHIVE/BACKUPT001S00002.ARC
ORA-00280: change 424125 for thread 1 is in sequence #2
ORA-00278: log file 'D:/BACKUPDB/ARCHIVE/BACKUPT001S00001.ARC' no longer needed
for this recovery
  
ORA-00279: change 424135 generated at 10/20/2002 21:55:35 needed for thread 1
ORA-00289: suggestion : D:/BACKUPDB/ARCHIVE/BACKUPT001S00003.ARC
ORA-00280: change 424135 for thread 1 is in sequence #3
ORA-00278: log file 'D:/BACKUPDB/ARCHIVE/BACKUPT001S00002.ARC' no longer needed
for this recovery
  
ORA-00279: change 424139 generated at 10/20/2002 21:57:42 needed for thread 1
ORA-00289: suggestion : D:/BACKUPDB/ARCHIVE/BACKUPT001S00004.ARC
ORA-00280: change 424139 for thread 1 is in sequence #4
ORA-00278: log file 'D:/BACKUPDB/ARCHIVE/BACKUPT001S00003.ARC' no longer needed
for this recovery
  
ORA-00279: change 424143 generated at 10/20/2002 21:57:54 needed for thread 1
ORA-00289: suggestion : D:/BACKUPDB/ARCHIVE/BACKUPT001S00005.ARC
ORA-00280: change 424143 for thread 1 is in sequence #5
ORA-00278: log file 'D:/BACKUPDB/ARCHIVE/BACKUPT001S00004.ARC' no longer needed
for this recovery
  
ORA-00279: change 444145 generated at 10/20/2002 22:01:23 needed for thread 1
ORA-00289: suggestion : D:/BACKUPDB/ARCHIVE/BACKUPT001S00006.ARC
ORA-00280: change 444145 for thread 1 is in sequence #6
ORA-00278: log file 'D:/BACKUPDB/ARCHIVE/BACKUPT001S00005.ARC' no longer needed
for this recovery
  
ORA-00279: change 464194 generated at 10/20/2002 22:24:10 needed for thread 1
ORA-00289: suggestion : D:/BACKUPDB/ARCHIVE/BACKUPT001S00007.ARC
ORA-00280: change 464194 for thread 1 is in sequence #7
ORA-00278: log file 'D:/BACKUPDB/ARCHIVE/BACKUPT001S00006.ARC' no longer needed
for this recovery
  
ORA-00279: change 484347 generated at 10/21/2002 00:16:14 needed for thread 1
ORA-00289: suggestion : D:/BACKUPDB/ARCHIVE/BACKUPT001S00008.ARC
ORA-00280: change 484347 for thread 1 is in sequence #8
ORA-00278: log file 'D:/BACKUPDB/ARCHIVE/BACKUPT001S00007.ARC' no longer needed
for this recovery
  
ORA-00279: change 484401 generated at 10/21/2002 00:30:27 needed for thread 1
ORA-00289: suggestion : D:/BACKUPDB/ARCHIVE/BACKUPT001S00009.ARC
ORA-00280: change 484401 for thread 1 is in sequence #9
ORA-00278: log file 'D:/BACKUPDB/ARCHIVE/BACKUPT001S00008.ARC' no longer needed
for this recovery
  
ORA-00279: change 484453 generated at 10/21/2002 00:33:51 needed for thread 1
ORA-00289: suggestion : D:/BACKUPDB/ARCHIVE/BACKUPT001S00010.ARC
ORA-00280: change 484453 for thread 1 is in sequence #10
ORA-00278: log file 'D:/BACKUPDB/ARCHIVE/BACKUPT001S00009.ARC' no longer needed
for this recovery
  
ORA-00279: change 484508 generated at 10/21/2002 00:38:40 needed for thread 1
ORA-00289: suggestion : D:/BACKUPDB/ARCHIVE/BACKUPT001S00011.ARC
ORA-00280: change 484508 for thread 1 is in sequence #11
ORA-00278: log file 'D:/BACKUPDB/ARCHIVE/BACKUPT001S00010.ARC' no longer needed
for this recovery
  
ORA-00279: change 504514 generated at 10/21/2002 00:51:00 needed for thread 1
ORA-00289: suggestion : D:/BACKUPDB/ARCHIVE/BACKUPT001S00012.ARC
ORA-00280: change 504514 for thread 1 is in sequence #12
ORA-00278: log file 'D:/BACKUPDB/ARCHIVE/BACKUPT001S00011.ARC' no longer needed
for this recovery
  
Log applied.
Media recovery complete.
SQL>
  
使恢复的数据文件联机
SQL> alter database datafile  'D:/BACKUPDB/USERS01.DBF'  online;
  
Database altered.
  
打开数据库
SQL> alter database open;
  
Database altered.
  
SQL>
  
这时需要重新启动数据库，并完全恢复数据库
SQL> conn lunar/lunar
Connected.
SQL> select count(*) from test;
select count(*) from test
                      *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn internal
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
重新启动数据库，
SQL> startup
ORACLE instance started.
  
Total System Global Area   25856028 bytes
Fixed Size                    75804 bytes
Variable Size               8925184 bytes
Database Buffers           16777216 bytes
Redo Buffers                  77824 bytes
Database mounted.
Database opened.
SQL>
用recover database再次恢复数据库
SQL>  conn internal
Connected.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 1 - file is in use or recovery
ORA-01110: data file 1: 'D:/BACKUPDB/SYSTEM01.DBF'
  
重新使恢复的表空间联机
SQL> alter database datafile 'D:/BACKUPDB/USERS01.DBF'  online;
  
Database altered.
  
SQL> conn lunar/lunar
Connected.
SQL> select * from test1;
  
          A
----------
          1
          2
          3
          4
          5
          6
          7
          8
          9
         10
         11
  
          A
----------
         12
         13
  
13 rows selected.
  
SQL>
验证恢复结果：完全恢复
说明：
1.  用热备覆盖这个文件   
2.  mount数据库   
3.  使损坏的数据文件脱机   
4.  恢复数据文件   
5.  使恢复的数据文件联机   
6.  打开数据库   
7.  这时需要重新启动数据库，并完全恢复数据库   
8.  重新启动数据库，   
9.  用recover database再次恢复数据库   
10.  重新使恢复的表空间联机  

http://www.cnoug.org/viewthread.php?tid=86&highlight=%2Blunar