Home > Database > Mysql Tutorial > 归档模式,恢复没有备份的数据文件

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

WBOY
Release: 2016-06-07 16:45:10
Original
912 people have browsed it

场景: 1.数据库开启归档; 2.创建数据文件之后的所有归档日志都在线; 3.数据文件或者表空间没有进行过备份,数据库也没有全库备

场景:

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

在CentOS 6.4下安装Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虚拟机中安装步骤

Debian 下 安装 Oracle 11g XE R2

本文永久更新链接地址:

linux

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template