欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2024-01-21 08:20:40
...

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