在归档模式下,恢复一个被offline drop的datafile的方法
来源:互联网 发布:最好的软件开发学校 编辑:程序博客网 时间:2024/05/24 00:40
参考自:
HOW TO RECOVER OFFLINE DROPPED DATAFILE IN ARCHIVELOG MODE (文档 ID 286355.1)
如下的实验基于oracle 11.2.0.4 linux x86-64bit完成
[oracle@rhel63single u02]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 15 20:33:17 2015Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /u02/archOldest online log sequence 126Next log sequence to archive 128Current log sequence 128SQL> select file_id from dba_data_files; FILE_ID---------- 4 3 2 1 5 6 7 8 9 10 1111 rows selected.SQL> select name from v$dbfile;NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/test/users01.dbf/u01/app/oracle/oradata/test/undotbs01.dbf/u01/app/oracle/oradata/test/sysaux01.dbf/u01/app/oracle/oradata/test/system01.dbf/u01/app/oracle/oradata/test/ten01.dbf/u01/app/oracle/oradata/test/tb_test_01.dbf/u01/app/oracle/oradata/test/ts1.dbf/u01/app/oracle/oradata/test/ts2.dbf/u01/app/oracle/oradata/test/test01.dbf/u01/app/oracle/oradata/test/test_uni_sz_2m_01.dbf/u01/app/oracle/oradata/test/test_uni_sz_1m_01.dbf11 rows selected.SQL> set lines 290SQL> col file_name format a60SQL> select FILE_NAME,file_Id from v$dbfile;FILE_NAME FILE_ID------------------------------------------------------------ ----------/u01/app/oracle/oradata/test/users01.dbf 4/u01/app/oracle/oradata/test/undotbs01.dbf 3/u01/app/oracle/oradata/test/sysaux01.dbf 2/u01/app/oracle/oradata/test/system01.dbf 1/u01/app/oracle/oradata/test/ten01.dbf 5/u01/app/oracle/oradata/test/tb_test_01.dbf 6/u01/app/oracle/oradata/test/ts1.dbf 7/u01/app/oracle/oradata/test/ts2.dbf 8/u01/app/oracle/oradata/test/test01.dbf 9/u01/app/oracle/oradata/test/test_uni_sz_2m_01.dbf 10/u01/app/oracle/oradata/test/test_uni_sz_1m_01.dbf 1111 rows selected.SQL> alter database datafile 9 offline drop;Database altered.SQL> select file#, status from v$datafile where file#='9'; FILE# STATUS---------- ------- 9 RECOVERSQL> select file#, status from v$datafile_header where file#='9'; FILE# STATUS---------- ------- 9 OFFLINESQL> alter system switch logfile ;System altered.SQL> /System altered.SQL> ///System altered.SQL> System altered.SQL> System altered.SQL> SQL> SQL> SQL> /System altered.SQL> SQL> SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /u02/archOldest online log sequence 132Next log sequence to archive 134Current log sequence 134SQL> recover datafile 9;ORA-00279: change 3155176 generated at 02/15/2015 20:34:05 needed for thread 1ORA-00289: suggestion : /u02/arch/1_128_807882551.dbfORA-00280: change 3155176 for thread 1 is in sequence #128Specify log: {<RET>=suggested | filename | AUTO | CANCEL}auto --------------->敲入autoLog applied.Media recovery complete.SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 133 52428800 512 1 YES INACTIVE 3155684 15-FEB-15 3155687 15-FEB-15 2 1 134 52428800 512 1 NO CURRENT 3155687 15-FEB-15 2.8147E+14 3 1 132 52428800 512 1 YES INACTIVE 3155681 15-FEB-15 3155684 15-FEB-15SQL> select file#,status from v$datafile where file#=9; FILE# STATUS---------- ------- 9 OFFLINESQL> select file#,status from v$datafile_header where file#=9; FILE# STATUS---------- ------- 9 OFFLINESQL> alter database datafile 9 online;Database altered.SQL> select file#,status from v$datafile where file#=9; FILE# STATUS---------- ------- 9 ONLINESQL>
知识点:
1.The only case in which the offline dropped datafile can not be online is
when you have added to many datafiles in the database after offline drop
2.在非归档模式下,为了让一个datafile 变成offline,必须带drop关键字。
drop关键字不会把datafile从database 中 remove掉。
To do that, you must drop the tablespace in which the datafile resides. Until you
do so, the datafile remains in the data dictionary with the status RECOVER or OFFLINE.
若是database处于归档模式,Oracle会忽略掉drop 关键字.
0 0
- 在归档模式下,恢复一个被offline drop的datafile的方法
- 使用alter database datafile 'XXX' offline drop 是否能够恢复(非归档模式下)
- alter database datafile '' offline drop以后的恢复
- alter database datafile offline 和offline drop的区别
- alter database datafile offline drop 和 alter tablespace drop datafile 的区别
- oracle跳过丢失的归档恢复datafile
- 一次归档日志被删除导致offline的datafile 无法访问的问题
- 在oracle database中,一个datafile 可以被drop的条件
- 非归档数据文件offline的恢复
- 在归档模式下有备份,丢失数据文件的恢复
- 在归档模式下删除非系统文件的恢复
- 利用 BBED 恢复非归档模式下 OFFLINE 数据文件
- 归档模式下恢复被删除的数据文件
- Oracle恢复(二)------非归档模式下的恢复
- oracle里tablespace offline和datafile offline的区别
- RMAN在非归档模式下的增量备份和恢复
- Oracle之RMAN数据库在非归档模式下的备份和恢复
- RMAN之非归档日志模式下的数据文件恢复
- POJ 1258 Agri-Net
- 在oracle database中,一个datafile 可以被drop的条件
- 欢迎使用CSDN-markdown编辑器
- 【Jason's_ACM_解题报告】Division
- HDU 1874 畅通工程续
- 在归档模式下,恢复一个被offline drop的datafile的方法
- 【Jason's_ACM_解题报告】Maximum Product
- ARM7异常与中断
- PL/SQL 总结
- VC的快捷键以及调试方法技巧
- 【Jason's_ACM_解题报告】Fractions Again?!
- HDU 2112 HDU Today
- 【Jason's_ACM_解题报告】Prime Ring Problem
- HDU:1043 八数码问题