首頁 > 資料庫 > mysql教程 > 归档模式,恢复没有备份的数据文件

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

WBOY
發布: 2016-06-07 16:45:10
原創
912 人瀏覽過

场景: 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

相關標籤:
來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
最新問題
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板