ITPub博客

首页 > 数据库 > Oracle > 归档模式,恢复没有备份的数据文件

归档模式,恢复没有备份的数据文件

原创 Oracle 作者:pxbibm 时间:2014-10-28 15:02:04 0 删除 编辑

场景:

1.数据库开启归档;

2.创建数据文件之后的所有归档日志都在线;

3.数据文件或者表空间没有进行过备份,数据库也没有全库备份,数据文件异常丢失;


步骤:

创建测试用的表空间:
SQL> create tablespace bbb datafile '/opt/oracle/oradata/R11203/bbb.dbf' size 100m;

SQL> create table test_b (id number(10)) tablespace bbb;

SQL> insert into test_b values (1);

SQL> commit;
SQL>select name,file# from v$datafile;

NAME                                                                         FILE#

--------------------------------------------------------------------------------
/opt/oracle/oradata/R11203/aaa.dbf                                       10

/opt/oracle/oradata/R11203/bbb.dbf                                       11

11 rows selected.


SQL> host

删除数据文件,模拟异常丢失

bash-4.2$ ls -al /opt/oracle/oradata/R11203/bbb.dbf

-rw-rw----   1 oracle    dba        10493952 Apr  4 09:53  /opt/oracle/oradata/R11203/bbb.dbf

bash-4.2$ mv /opt/oracle/oradata/R11203/bbb.dbf  /opt/oracle/oradata/R11203/bbb.dbf.bak

bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 409:55:03 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 -64bit Production

With the Partitioning, OLAP, Data Mining and Real
Application Testing options

SQL> alter tablespace bbb read only;

alter tablespace bbb read only

*

ERROR at line 1:

ORA-01116: error in opening database file 11

ORA-01110: data file 11:
'/opt/oracle/oradata/R11203/bbb.dbf'

ORA-27041: unable to open file

HPUX-ia64 Error: 2: No such file or directory

Additional information: 3

SQL> shutdown immediate;

ORA-01116: error in opening database file 11

ORA-01110: data file 11:
'/opt/oracle/oradata/R11203/bbb.dbf'

ORA-27041: unable to open file

HPUX-ia64 Error: 2: No such file or directory

Additional information: 3

SQL> select status from v$instance;

STATUS

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

OPEN


SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>/

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>

停机

SQL> shutdown immediate;

ORA-01116: error in opening database file 11

ORA-01110: data file 11:
'/opt/oracle/oradata/R11203/bbb.dbf'

ORA-27041: unable to open file

HPUX-ia64 Error: 2: No such file or directory

Additional information: 3

SQL> shutdown abort;

ORACLE instance shut down.

把数据库启动到mount状态

SQL> startup mount;

ORACLE instance started.


Total System Global Area  329859072 bytes

FixedSize                 2182336 bytes

VariableSize            285213504 bytes

DatabaseBuffers           37748736bytes

RedoBuffers               4714496 bytes

Database mounted.

使用alter database create datafile <> as ....的方式,重建这个丢失的数据文件:

SQL> alter database create datafile 11;

Database altered.

通过归档日志和redo log对数据文件进行恢复

SQL> recover datafile 11;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select * from test_b;

       ID

----------

        1

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

请登录后发表评论 登录
全部评论
  • 博文量
    240
  • 访问量
    2160262