ITPUX技术网

交流 . 资讯 . 分享
Make progress together!
Linux操作系统下Oracle11g R2 RAC 安装配置教程
Oracle数据库培训-备份恢复-性能优化-集群容灾
Oracle数据库DBA高级工程师培训视频

Oracle数据文件物理删除后的恢复

内容发布:luashin| 发布时间:2017-6-8 00:11:36
Oracle数据文件物理删除后的恢复
    做系统管理的都是这样,难免会误删文件,某天要是把某个Oracle数据文件删除,那该如何恢复呢?(这里数据库是OPEN的,并且未关闭)
建立测试表空间
创建测试用户
插入测试数据
删除数据文件
恢复数据库文件
建立测试表空间

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/member/system01.dbf
/opt/oracle/oradata/member/sysaux01.dbf
/opt/oracle/oradata/member/undotbs01.dbf
/opt/oracle/oradata/member/users01.dbf

SQL> create tablespace test datafile '/opt/oracle/oradata/member/test01.dbf' size 10m;
Tablespace created.

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/member/system01.dbf
/opt/oracle/oradata/member/sysaux01.dbf
/opt/oracle/oradata/member/undotbs01.dbf
/opt/oracle/oradata/member/users01.dbf
/opt/oracle/oradata/member/test01.dbf

2.创建测试账户
SQL> create user test identified by test default tablespace test;
SQL> grant connect,resource to test;

3.插入测试数据
SQL> conn test/test
SQL> create table t1(id int);
SQL> insert into t1 values(1);
SQL> select * from t1;
        ID
----------
        1

4.删除数据文件
[oracle@db2 ~]$ rm -f /opt/oracle/oradata/member/test01.dbf
[oracle@db2 ~]$ sqlplus test/test
SQL> create table t2 as select * from t1;
create table t2 as select * from t1
                                *
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/opt/oracle/oradata/member/test01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME                    TABLESPACE_NAME
------------------------------ ------------------------------
T1                            TEST
数据文件被删除了,这怎么办呢?这个时候千万别重启数据库,否则数据就丢失了

5.数据文件恢复
[oracle@db2 ~]$ ps -ef |grep dbw0
oracle    3309    1  0 12:07 ?        00:00:00 ora_dbw0_member
oracle    6217  5105  0 15:29 pts/0    00:00:00 grep dbw0
#找到ora_dbw0_SID的进程号3309

[oracle@db2 ~]$ cd /proc/3309/fd
#以上3309就是进程号,然后执行ls -al查看文件的链接

可以看到文件27就是被删除的文件
[oracle@db2 fd]$ cp 27 /opt/oracle/oradata/member/test01.dbf

查看test表空间状态
SQL> select name,status from v$datafile;
NAME                      STATUS
----------------------------------------  -------
/opt/oracle/oradata/member/system01.dbf    SYSTEM
/opt/oracle/oradata/member/sysaux01.dbf    ONLINE
/opt/oracle/oradata/member/undotbs01.dbf    ONLINE
/opt/oracle/oradata/member/users01.dbf    ONLINE
/opt/oracle/oradata/member/test01.dbf    ONLINE

下线test01表文件
SQL> alter database datafile '/opt/oracle/oradata/member/test01.dbf' offline;
SQL> recover datafile '/opt/oracle/oradata/member/test01.dbf';
Media recovery complete.

SQL> alter database datafile '/opt/oracle/oradata/member/test01.dbf' online;
Database altered.

#以上就成功恢复了,若是出现
SQL> recover datafile '/opt/oracle/oradata/member/test01.dbf';
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 5: '/opt/oracle/oradata/member/test01.dbf'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/opt/oracle/oradata/member/test01.dbf'
则有可能是/opt/oracle/oradata/member/test01.dbf文件的权限问题引起,在root用户下

[root@db2 ~]# chown -R oracle.oinstall /opt/oracle/oradata/member/test01.dbf  
再recover datafile '/opt/oracle/oradata/member/test01.dbf'

实验完成后,删除测试用户及测试表空间
SQL> drop user test cascade;
SQL> drop tablespace test INCLUDING CONTENTS AND DATAFILES;



上一篇:CentOS 7.2安装Oracle 12c RAC
下一篇:使用Oracle PROFILE控制会话空闲时间
回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表