dataifle误删除的模拟recover

最新推荐文章于 2024-01-23 09:44:35 发布

原创最新推荐文章于 2024-01-23 09:44:35 发布 · 143 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

Oracle 专栏收录该内容

9 篇文章

订阅专栏

本文详细介绍了Oracle数据库的热备份操作及恢复流程，包括使用热备份表空间、执行事务提交、关闭数据库、删除数据文件等关键步骤，并通过实例演示了从备份文件中恢复数据的过程，特别关注了在恢复过程中遇到的错误处理和使用RECOVER TABLESPACE命令进行恢复的技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle 恢复试习一：
　1. 热备份tablespace users.
2. a) scott login,
b) create table tt( seq number);
c) declare
i number;
begin
for i in 1..20
loop
insert into tt values(i);
end loop;
commit;
end;
d) alter system switch logfile;
e) insert into tt values(21);
alter system switch logfile;
...
直到所有online redo file都做了archived(测试archived log的应用)
f) commit;
3. shutdown immediate;
4. rm datafile users;

恢复如下：
SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 121636432 bytes
Database Buffers 159383552 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/oracle/oracle/product/10.2.0/oradata/orcl/users01.dbf'

SQL> host cp /oracle/backup/users01.dbf /oracle/oracle/product/10.2.0/oradata/orcl/

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/oracle/oracle/product/10.2.0/oradata/orcl/users01.dbf'

SQL> recover tablespace users;
ORA-00279: change 546700 generated at 05/14/2013 20:51:07 needed for thread 1
ORA-00289: suggestion : /oracle/oracle/product/10.2.0/db_1/flash_recovery_area/ORCL/archivelog/2013_05_14/o1_mf_1_6_%u_.arc
ORA-00280: change 546700 for thread 1 is in sequence #6

--- ORA-00289　：寻archived log file恢复...因为

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.
SQL> alter database open;

Database altered.

注：
scn的查看：
1.select file#,checkpoint_change# from v$datafile_header;
2.select file#,checkpoint_change# from v$datafile;
v$datafile_header的信息来自各数据文件的文件头.
v$datafile的信息则来自control file
3.select sequence#,first_change#,next_change# from v$archived_log;
4.select dbms_flashback.get_system_change_number from dual;-- system scn