DBF删除和改名的恢复操作

最新推荐文章于 2023-04-23 14:37:44 发布

cri3224

最新推荐文章于 2023-04-23 14:37:44 发布

阅读量454

点赞数

文章标签：操作系统数据库

结论：关于普通表空间和system表空间被rm的情况，处理的方法基本是一样的，都是需要restore datafile然后在recover datafile。有区别的就是如果是system dbf被删除，那数据库会崩。
小插曲：
关于dbf突然被误删除，如果是业务数据文件的话，那还好,可以restore+recover的方式恢复，而不影响其他用户访问其他dbf里的数据。但如果是system表空间的文件被删除的话，那基本db就崩了，但会等待一小会才会崩，而不是马上。这是因为linux的删除机制，(Linux 是通过 link 的数量来控制文件删除，只有当一个文件不存在任何 link 的时候，这个文件才会被删除。每个文件都有 2 个 link 计数器—— i_count 和 i_nlink。i_count 的意义是当前使用者的数量，i_nlink 的意义是介质连接的数量；或者可以理解为 i_count 是内存引用计数器，i_nlink 是硬盘引用计数器。再换句话说，当文件被某个进程引用时，i_count 就会增加；当创建文件的硬连接的时候，i_nlink 就会增加。对于 rm 而言，就是减少 i_nlink。这里就出现一个问题，如果一个文件正在被某个进程调用，而用户却执行 rm 操作把文件删除了，会出现什么结果呢？当用户执行 rm 操作后，ls 或者其他文件管理命令不再能够找到这个文件，但是进程却依然在继续正常执行，依然能够从文件中正确的读取内容。这是因为，`rm` 操作只是将 i_nlink 置为 0 了；由于文件被进程引用的缘故，i_count 不为 0，所以系统没有真正删除这个文件。i_nlink 是文件删除的充分条件，而 i_count 才是文件删除的必要条件。)
所以我们可以得出一个结论，就是普通dbf文件被删除并不会影响其他dbf的读取以及业务使用，但如果是system的话，就肯定会影响，而且会导致数据库崩溃。

业务dbf被改名
SQL> create user mao identified by mao account unlock default tablespace users;

User created.

SQL> grant connect,resource to mao;

Grant succeeded.

SQL> conn mao/mao
Connected.
SQL> create table t_mao (id int);

Table created.

SQL> insert into t_mao values(111111111111);
SQL> /
/
。。。。。
131072 rows created.

SQL>
262144 rows created.

SQL> commit;

Commit complete.

SQL> select table_name,tablespace_name from user_tables where table_name='T_MAO';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_MAO USERS

SQL> conn / as sysdba
Connected.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dong/system01.dbf
/u01/app/oracle/oradata/dong/sysaux01.dbf
/u01/app/oracle/oradata/dong/undotbs01.dbf
/u01/app/oracle/oradata/dong/users01.dbf

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     10
Next log sequence to archive   12
Current log sequence           12

将dbf改名，那一会就不需要restore了，直接recover即可。
SQL> !mv /u01/app/oracle/oradata/dong/users01.dbf /u01/app/oracle/oradata/dong/users01.dbf.bak

SQL> select name from v$datafile;

SQL> !ls -l /u01/app/oracle/oradata/dong/users01.dbf
可以发现此文件已经没有了。

SQL> select table_name,tablespace_name from dba_tables where table_name='T_MAO';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_MAO USERS

SQL> conn mao/mao
Connected.

SQL> select count(*) from t_mao;

COUNT(*)
----------
524288

SQL> conn / as sysdba
Connected.
SQL> grant dba to mao;

Grant succeeded.

SQL> conn mao/mao
Connected.
SQL> set autotrace traceonly
SQL> select * from t_mao where rownum=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 4144912178

----------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    13 |     2   (0)| 00:00:01 |
|* 1 | COUNT STOPKEY     |       |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T_MAO |   487K| 6184K|     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          4 consistent gets---都是在内存中读取，所以dbf删了，这里也可以读到数据。
          0 physical reads
          0 redo size
        425 bytes sent via SQL*Net to client
        420 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> update t_mao set id=22222222222222222222222244;
update t_mao set id=22222222222222222222222244
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/dong/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
--但对于修改就肯定不行了。

SQL> SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         10   52428800        512          1 YES INACTIVE               1050012 16-NOV-13      1059303 16-NOV-13
         2          1         11   52428800        512          1 YES INACTIVE               1059303 16-NOV-13      1081764 18-NOV-13
         3          1         12   52428800        512          1 NO CURRENT                1081764 18-NOV-13   2.8147E+14

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         13   52428800        512          1 NO CURRENT                1083148 18-NOV-13   2.8147E+14
         2          1         11   52428800        512          1 YES INACTIVE               1059303 16-NOV-13      1081764 18-NOV-13
         3          1         12   52428800        512          1 YES ACTIVE                 1081764 18-NOV-13      1083148 18-NOV-13

SQL> alter table t_tteqteq move to users;
alter table t_tteqteq move to users
*
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations

SQL> create table ttt(id int) tablespace users;
create table ttt(id int) tablespace users
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/dong/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

告警日志：
Thread 1 advanced to log sequence 13 (LGWR switch)
Current log# 1 seq# 13 mem# 0: /u01/app/oracle/oradata/dong/redo01.log
Mon Nov 18 14:56:12 2013
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/DONG/archivelog/2013_10_25/o1_mf_1_4_96mxrw7k_.arc
Archived Log entry 187 added for thread 1 sequence 12 ID 0x7bd66ee8 dest 1:
Mon Nov 18 14:58:16 2013
Errors in file /u01/app/oracle/diag/rdbms/dong/dong/trace/dong_m000_3812.trc:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/dong/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
此时查看dbf的状态为recover：
SQL> select name,status,bytes,blocks,block1_offset from v$datafile;

NAME                                               STATUS       BYTES     BLOCKS BLOCK1_OFFSET
-------------------------------------------------- ------- ---------- ---------- -------------
/u01/app/oracle/oradata/dong/system01.dbf          SYSTEM   817889280      99840          8192
/u01/app/oracle/oradata/dong/sysaux01.dbf          ONLINE   482344960      58880          8192
/u01/app/oracle/oradata/dong/undotbs01.dbf         ONLINE   214958080      26240          8192
/u01/app/oracle/oradata/dong/users01.dbf           RECOVER          0          0    4294967295
将dbf给移动回来。
SQL> !mv /u01/app/oracle/oradata/dong/users01.dbf.bak /u01/app/oracle/oradata/dong/users01.dbf

SQL> select name,status,bytes,blocks,block1_offset from v$datafile;

SQL> update mao.t_mao set id=22222222222222222222222244;
update mao.t_mao set id=22222222222222222222222244
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/dong/users01.dbf'

SQL> select file#,name,status,bytes,blocks,block1_offset from v$datafile;

     FILE# NAME                                               STATUS       BYTES     BLOCKS BLOCK1_OFFSET
---------- -------------------------------------------------- ------- ---------- ---------- -------------
         1 /u01/app/oracle/oradata/dong/system01.dbf          SYSTEM   817889280      99840          8192
         2 /u01/app/oracle/oradata/dong/sysaux01.dbf          ONLINE   482344960      58880          8192
         3 /u01/app/oracle/oradata/dong/undotbs01.dbf         ONLINE   214958080      26240          8192
         4 /u01/app/oracle/oradata/dong/users01.dbf           RECOVER   10485760       1280          8192
直接恢复dbf文件：
SQL> recover datafile 4;
Media recovery complete.
SQL> select file#,name,status,bytes,blocks,block1_offset from v$datafile;

SQL> alter database datafile 4 online;

Database altered.

SQL> select file#,name,status,bytes,blocks,block1_offset from v$datafile;

SQL> update mao.t_mao set id=22222222222222222222222244;

524288 rows updated.

SQL> commit;

Commit complete.

完毕。以上为普通业务dbf删除恢复的情况。
业务dbf被删除
[oracle@baobao ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 17:05:32 2013

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$datafile;

SQL> !mv /u01/app/oracle/oradata/dong/system01.dbf.bak /u01/app/oracle/oradata/dong/system01.dbf

SQL> !ls -l /u01/app/oracle/oradata/dong/system01.dbf
-rw-r-----. 1 oracle oinstall 828383232 11月 19 16:57 /u01/app/oracle/oradata/dong/system01.dbf

SQL> recover datafile 1;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select name from v$datafile;

SQL> !rm -rf /u01/app/oracle/oradata/dong/users01.dbf

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 19 17:18:48 2013

connected to target database: DONG (DBID=2075447482)

RMAN> restore datafile 4;

Starting restore at 19-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=138 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/dong/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/fulldb1119
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/19/2013 17:18:59
ORA-19870: error while restoring backup piece /home/oracle/fulldb1119
ORA-19573: cannot obtain exclusive enqueue for datafile 4

RMAN> exit

Recovery Manager complete.

[oracle@baobao ~]$ ps -ef |grep smon
oracle 5071 1 0 16:59 ? 00:00:00 ora_smon_dong
oracle 5343 2611 0 17:19 pts/0 00:00:00 grep smon
[oracle@baobao ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 17:19:22 2013

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/dong/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> !ps -ef |grep smonb
oracle 5348 5345 0 17:19 pts/1 00:00:00 /bin/bash -c ps -ef |grep smonb
oracle 5350 5348 0 17:19 pts/1 00:00:00 grep smonb

SQL> !ps -ef |grep smon
oracle    5071     1 0 16:59 ?        00:00:00 ora_smon_dong
oracle    5351 5345 0 17:19 pts/1    00:00:00 /bin/bash -c ps -ef |grep smon
oracle    5353 5351 0 17:19 pts/1    00:00:00 grep smon

SQL> shutdown immediate
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/dong/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2092498944 bytes
Fixed Size                  1337604 bytes
Variable Size             251660028 bytes
Database Buffers         1828716544 bytes
Redo Buffers               10784768 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@baobao ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 19 17:20:09 2013

connected to target database: DONG (DBID=2075447482, not open)

RMAN> restore datafile 4;

Starting restore at 19-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/dong/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/fulldb1119
channel ORA_DISK_1: piece handle=/home/oracle/fulldb1119 tag=TAG20131119T164123
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished restore at 19-NOV-13

RMAN> recover datafile 4;

Starting recover at 19-NOV-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/DONG/archivelog/2013_11_19/o1_mf_1_1_98p96htn_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/DONG/archivelog/2013_11_19/o1_mf_1_2_98p9lgy6_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/DONG/archivelog/2013_11_19/o1_mf_1_3_98p9pzhq_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/DONG/archivelog/2013_11_19/o1_mf_1_4_98pbhy62_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/DONG/archivelog/2013_11_19/o1_mf_1_1_98p96htn_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/flash_recovery_area/DONG/archivelog/2013_11_19/o1_mf_1_2_98p9lgy6_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-NOV-13

RMAN> alter database open;

database opened

以下为system dbf删除的情况：

其实和普通dbf的恢复区别就是system dbf删除之后，数据库会崩，所以我们需要启动到mount状态，然后restore datafile1 然后recover datafile1，然后alter database open即可。
[oracle@baobao ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 16:42:44 2013

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t19 as select * from dba_objects;

Table created.

SQL> alter system swith logfile;
alter system swith logfile
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

SQL> alter system switch logfile;

System altered.

SQL> select name from v$datafile;

SQL> !rm -rf /u01/app/oracle/oradata/dong/system01.dbf

SQL> !ls -l /u01/app/oracle/oradata/dong/system01.dbf
ls: 无法访问/u01/app/oracle/oradata/dong/system01.dbf: 没有那个文件或目录

SQL> create table t20 as select * from dba_objects;
create table t20 as select * from dba_objects
*
ERROR at line 1:
ORA-01565: error in identifying file
'/u01/app/oracle/oradata/dong/system01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> restore datafile 1;
SP2-0734: unknown command beginning "restore da..." - rest of line ignored.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@baobao ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 19 16:46:45 2013

connected to target database: DONG (DBID=2075447482)

RMAN> restore datafile 1;

Starting restore at 19-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/dong/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/fulldb1119
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/19/2013 16:46:54
ORA-19870: error while restoring backup piece /home/oracle/fulldb1119
ORA-19573: cannot obtain exclusive enqueue for datafile 1

RMAN> shutdown immediate

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of shutdown command at 11/19/2013 16:47:11
ORA-03113: end-of-file on communication channel

RMAN> exit

Recovery Manager complete.
[oracle@baobao ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 16:47:37 2013

Connected to an idle instance.

SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
SQL> exit
Disconnected
[oracle@baobao ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 16:47:44 2013

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 19 16:48:45 2013

connected to target database: DONG (DBID=2075447482, not open)

RMAN> restore datafile 1
2> ;

Starting restore at 19-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/dong/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/fulldb1119
channel ORA_DISK_1: piece handle=/home/oracle/fulldb1119 tag=TAG20131119T164123
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 19-NOV-13

RMAN> exit

Recovery Manager complete.

[oracle@baobao ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 16:50:07 2013

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover datafile 1;
Media recovery complete.
SQL> alter database open;

Database altered.
以上是将system dbf删除的情况。

system dbf被改名：
[oracle@baobao ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 16:50:07 2013

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover datafile 1;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL>
SQL>
SQL>
SQL> select name from v$datafile;

SQL> !mv /u01/app/oracle/oradata/dong/system01.dbf /u01/app/oracle/oradata/dong/system01.dbf.bak

SQL> create table t20 as select * from dba_objects;
create table t20 as select * from dba_objects
*
ERROR at line 1:
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/dong/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

SQL> !ps -ef |grep smon
oracle    4769     1 0 16:48 ?        00:00:00 ora_smon_dong
oracle    4966 4913 3 16:52 pts/1    00:00:00 /bin/bash -c ps -ef |grep smon
oracle    4968 4966 1 16:52 pts/1    00:00:00 grep smon

SQL> !ps -ef |grep smon
oracle    4769     1 0 16:48 ?        00:00:00 ora_smon_dong
oracle    4969 4913 0 16:52 pts/1    00:00:00 /bin/bash -c ps -ef |grep smon
oracle    4971 4969 0 16:52 pts/1    00:00:00 grep smon

SQL> !ps -ef |grep smon
oracle    4769     1 0 16:48 ?        00:00:00 ora_smon_dong
oracle    4972 4913 1 16:52 pts/1    00:00:00 /bin/bash -c ps -ef |grep smon
oracle    4974 4972 1 16:52 pts/1    00:00:00 grep smon

SQL> alter system switch logfile;

System altered.

SQL> !ps -ef |grep smon
oracle    4769     1 0 16:48 ?        00:00:00 ora_smon_dong
oracle    4975 4913 2 16:52 pts/1    00:00:00 /bin/bash -c ps -ef |grep smon
oracle    4977 4975 1 16:52 pts/1    00:00:00 grep smon

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@baobao ~]$ ps -ef |grep smn
oracle    4980 2611 0 16:53 pts/0    00:00:00 grep smn
[oracle@baobao ~]$ ps -ef |grep smon
oracle    4769     1 0 16:48 ?        00:00:00 ora_smon_dong
oracle    4982 2611 0 16:53 pts/0    00:00:00 grep smon
[oracle@baobao ~]$ ls /u01/app/oracle/oradata/dong/system01.dbf
ls: 无法访问/u01/app/oracle/oradata/dong/system01.dbf: 没有那个文件或目录
[oracle@baobao ~]$ ps -ef |grep smon
oracle    4769     1 0 16:48 ?        00:00:00 ora_smon_dong
oracle    4986 2611 0 16:53 pts/0    00:00:00 grep smon
[oracle@baobao ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 16:53:50 2013

ERROR:
ORA-01075: you are currently logged on

[oracle@baobao ~]$ ps -ef |grep smon
oracle 5018 2611 0 16:57 pts/0 00:00:00 grep smon
[oracle@baobao ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 16:59:37 2013

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

SQL> !ls -l /u01/app/oracle/oradata/dong/system01.dbf*
-rw-r-----. 1 oracle oinstall 828383232 11月 19 16:57 /u01/app/oracle/oradata/dong/system01.dbf.bak

SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 1: '/u01/app/oracle/oradata/dong/system01.dbf'
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/dong/system01.dbf'

SQL> !ps -ef |grep smon
oracle    5071     1 0 16:59 ?        00:00:00 ora_smon_dong
oracle    5223 5023 0 17:05 pts/1    00:00:00 /bin/bash -c ps -ef |grep smon
oracle    5225 5223 0 17:05 pts/1    00:00:00 grep smon

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 19 17:05:17 2013

connected to target database: DONG (DBID=2075447482, not open)

RMAN> recover datafile 1;

Starting recover at 19-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/19/2013 17:05:24
RMAN-06094: datafile 1 must be restored

RMAN> exit

Recovery Manager complete.

[oracle@baobao ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 19 17:05:32 2013

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$datafile;

SQL> !mv /u01/app/oracle/oradata/dong/system01.dbf.bak /u01/app/oracle/oradata/dong/system01.dbf

SQL> !ls -l /u01/app/oracle/oradata/dong/system01.dbf
-rw-r-----. 1 oracle oinstall 828383232 11月 19 16:57 /u01/app/oracle/oradata/dong/system01.dbf

SQL> recover datafile 1;
Media recovery complete.
SQL> alter database open;

Database altered.

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

转载于:http://blog.itpub.net/24500180/viewspace-777019/

cri3224

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
DBF删除和改名的恢复操作

结论：关于普通表空间和system表空间被rm的情况，处理的方法基本是一样的，都是需要restore datafile然后在recover datafile。有区别的就是如果是system dbf被删除，那数据库会崩。...
复制链接

扫一扫