ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DBF删除和改名的恢复操作

DBF删除和改名的恢复操作

原创 Linux操作系统 作者:oracle_mao 时间:2013-11-19 18:50:28 0 删除 编辑

结论:关于普通表空间和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;

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> !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;

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   10485760       1280          8192

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;

     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           OFFLINE   10485760       1280          8192

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> alter database datafile 4 online;

Database altered.

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           ONLINE    10485760       1280          8192

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

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


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;

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> !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;

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> !rm -rf /u01/app/oracle/oradata/dong/users01.dbf

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:18:48 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

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

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


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

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

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

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


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;

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> !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

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

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

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

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

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

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 16:48:45 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

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

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


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

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


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;

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> !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

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

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

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

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> 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> !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

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:05:17 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

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

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


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;

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> !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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 索引的nologging
请登录后发表评论 登录
全部评论

注册时间:2011-03-28

  • 博文量
    94
  • 访问量
    752087