有全部在线日志和归档日志的文件丢失恢复

QL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     32

Next log sequence to archive   34

Current log sequence           34

数据库处于归档模式

SQL> create tablespace aisj datafile '/s01/orabase/oradata/mes/aisj01.dbf' size 10m;

Tablespace created.

SQL> create table aisj_test(a number, b varchar2(10)) tablespace aisj;

Table created.

QL> insert into aisj_test values(1,'2');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from aisj_test;

         A B

---------- ----------

         1 2

SQL> alter system switch logfile;

System altered.

SQL> /

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> host mv /s01/orabase/oradata/mes/aisj01.dbf /s01/orabase/oradata/mes/aisj01.dbf_bak

模拟数据丢失

SQL> startup

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size                  2232960 bytes

Variable Size             553651584 bytes

Database Buffers          276824064 bytes

Redo Buffers                2396160 bytes

Database mounted.

ORA-01157: 无法标识/锁定数据文件 6 - 请参阅 DBWR 跟踪文件

ORA-01110: 数据文件 6: '/s01/orabase/oradata/mes/aisj01.dbf'

数据库打不开

SQL> shutdown immediate

ORA-01109: 数据库未打开

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size                  2232960 bytes

Variable Size             553651584 bytes

Database Buffers          276824064 bytes

Redo Buffers                2396160 bytes

Database mounted.

SQL> alter database create datafile 6 as '/s01/orabase/oradata/mes/aisj02.dbf';

Database altered.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: 文件 6 需要介质恢复

ORA-01110: 数据文件 6: '/s01/orabase/oradata/mes/aisj02.dbf'

SQL> recover datafile 6;

ORA-00279: 更改 1861176 (在 09/12/2013 13:30:15 生成) 对于线程 1 是必需的

ORA-00289: 建议:

/s01/orabase/fast_recovery_area/MES/archivelog/2013_09_12/o1_mf_1_34_932nffcg_.a

rc

ORA-00280: 更改 1861176 (用于线程 1) 在序列 #34 中

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: 更改 1861568 (在 09/12/2013 13:31:25 生成) 对于线程 1 是必需的

ORA-00289: 建议:

/s01/orabase/fast_recovery_area/MES/archivelog/2013_09_12/o1_mf_1_35_932nfgq5_.a

rc

ORA-00280: 更改 1861568 (用于线程 1) 在序列 #35 中

ORA-00279: 更改 1861573 (在 09/12/2013 13:31:26 生成) 对于线程 1 是必需的

ORA-00289: 建议:

/s01/orabase/fast_recovery_area/MES/archivelog/2013_09_12/o1_mf_1_36_932nfjxd_.a

rc

ORA-00280: 更改 1861573 (用于线程 1) 在序列 #36 中

Log applied.

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> conn aisj/aisj

Connected.

SQL> select * from aisj_test;

         A B

---------- ----------

         1 2

恢复完成

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21124603/viewspace-772624/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21124603/viewspace-772624/