先
SQL> recover datafile 10;
然后
SQL> alter database open;
SQL> recover datafile 10;
然后
SQL> alter database open;
解决方案 »
- 关于一个sql语句查询问题,用程序解决也行!!!!
- dts导出成功但是数据没有导入
- oracel 10G 的“方案” 如何 理解?
- 一个用参数作表名的问题 急……
- MAX简单问题
- 关于sql连接查表的问题
- 为什么我删掉了几个表后, System 表空间的使用率还是那么多呢?
- 物理网络畅通、配置正常ORACLE客户端连不到服务器?急急急 再线等待
- 问题是关于工资系统中缴费工资的讨论,欢迎有财务经验的看看
- oracle 如何设置使得用户每次登录都要求必须修改密码?
- 急!!!!!如何我在MS sqlserver中建的所有表全部倒到oracle中,希望大家指点,包括默认值,关联
- 如何不安装客户端,直接连oracle9.01数据库??、
4.3丢失多个数据文件,实现整个数据库的恢复
4.3.1 OS备份方案
OS备份归档模式下损坏(丢失)多个数据文件,进行整个数据库的恢复
1、连接数据库,创建测试表并插入记录
SQL*Plus: Release 8.1.6.0.0 - Production on Tue May 6 13:46:32 2003
(c) Copyright 1999 Oracle Corporation. All rights reserved.
SQL> connect internal/password as sysdba;
Connected. SQL> create table test(a int);
Table created
SQL> insert into test values(1);
1 row inserted
SQL> commit;
Commit complete 2、备份数据库,备份除临时数据文件后的所数据文件
SQL> @hotbak.sql 或在DOS下 svrmgrl @hotbak.sql 3、继续在测试表中插入记录
SQL> insert into test values(2);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test;
A
---------------------------------------
1
2
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered. 4、关闭数据库,模拟丢失数据文件
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down C:\>del D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF
C:\>del D:\ORACLE\ORADATA\TEST\INDX01.DBF
C:\>del D:\ORACLE\ORADATA\TEST\TOOLS01.DBF
C:\>del D:\ORACLE\ORADATA\TEST\RBS01.DBF
模拟媒体毁坏(这里删除多个数据文件) 5、启动数据库,检查错误
SQL> STARTUP
ORACLE instance started.
Total System Global Area 102020364 bytes
Fixed Size 70924 bytes
Variable Size 85487616 bytes
Database Buffers 16384000 bytes
Redo Buffers 77824 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF' 详细信息可以查看报警文件
ORA-1157 signalled during: ALTER DATABASE OPEN...
Thu May 08 09:39:36 2003
Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。 Thu May 08 09:39:36 2003
Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\TEST\RBS01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。 Thu May 08 09:39:36 2003
Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'D:\ORACLE\ORADATA\TEST\TOOLS01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。 Thu May 08 09:39:36 2003
Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\ORACLE\ORADATA\TEST\INDX01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。 通过查询v$recover_file可以看到
SQL> select * from v$recover_file; FILE# ONLINE ERROR CHANGE# TIME
---------- ------- ------------------ ---------- -----------
1 ONLINE FILE NOT FOUND 0
2 ONLINE FILE NOT FOUND 0
5 ONLINE FILE NOT FOUND 0
6 ONLINE FILE NOT FOUND 0
有四个数据文件需要恢复 6、拷贝备份回到原地点(restore),开始恢复数据库(recover)
restore过程:
C:\>copy D:\DATABAK\SYSTEM01.DBF D:\ORACLE\ORADATA\TEST\
C:\>copy D:\DATABAK\TEST\INDX01.DBF D:\ORACLE\ORADATA\TEST\
C:\>copy D:\DATABAK\TEST\TOOLS01.DBF D:\ORACLE\ORADATA\TEST\
C:\>copy D:\DATABAK\TEST\RBS01.DBF.DBF D:\ORACLE\ORADATA\TEST\ Recover过程:
SQL> recover database;
ORA-00279: change 1073849 generated at 05/08/2003 08:58:35 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00311.ARC
ORA-00280: change 1073849 for thread 1 is in sequence #311 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1073856 generated at 05/08/2003 09:03:27 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00312.ARC
ORA-00280: change 1073856 for thread 1 is in sequence #312
ORA-00278: log file 'D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00311.ARC' no
longer needed for this recovery ORA-00279: change 1073858 generated at 05/08/2003 09:11:43 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00313.ARC
ORA-00280: change 1073858 for thread 1 is in sequence #313
ORA-00278: log file 'D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00312.ARC' no
longer needed for this recovery ORA-00279: change 1073870 generated at 05/08/2003 09:11:46 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00314.ARC
ORA-00280: change 1073870 for thread 1 is in sequence #314
ORA-00278: log file 'D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00313.ARC' no
longer needed for this recovery Log applied.
Media recovery complete. 7、打开数据库,检查数据库的数据(完全恢复)
SQL> alter database open;
Database altered.
SQL> select * from test;
A
---------------------------------------
1
2 说明:
1、只要有备份与归档存在,就可以实现数据库的完全恢复(不丢失数据)
2、适合于丢失大量数据文件,或包含系统数据文件在内的数据库的恢复
3、恢复过程在mount下进行,如果恢复成功,再打开数据库,down机时间可能比较长一些。