oracle无归档误删除数据打开

---------------------数据库非归档模式下误删除文件，数据库关闭后可以这样打开（丢失数据）-----------------

SQL> select name from v$datafile;                                                                                                                     

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/duxiu/system01.dbf
/opt/oracle/oradata/duxiu/undotbs01.dbf
/opt/oracle/oradata/duxiu/sysaux01.dbf
/opt/oracle/oradata/duxiu/users01.dbf

SQL> create tablespace test01 datafile '/opt/oracle/oradata/duxiu/test01.dbf' size 50M;                                                               

Tablespace created.

SQL> shutdown immediate                                                                                                                               
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> host mv /opt/oracle/oradata/duxiu/test01.dbf  /opt/oracle/oradata/duxiu/test01bk.dbf 

SQL> host ls /opt/oracle/oradata/duxiu/test01.dbf                                                                                                     
ls: /opt/oracle/oradata/duxiu/test01.dbf: 没有那个文件或目录

SQL> startup                                                                                                                                          
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218968 bytes
Variable Size              67110504 bytes
Database Buffers          209715200 bytes
Redo Buffers                7168000 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/opt/oracle/oradata/duxiu/test01.dbf'

 

SQL> select OPEN_MODE from v$database;                                                                                                                

OPEN_MODE
----------
MOUNTED

SQL> alter database datafile '/opt/oracle/oradata/duxiu/test01.dbf' offline drop;                                                                     

Database altered.

SQL> select FILE_NAME,FILE_ID,STATUS from dba_data_files where TABLESPACE_NAME='TEST01';                                                              

FILE_NAME                                   FILE_ID STATUS
---------------------------------------- ---------- ---------
/opt/oracle/oradata/duxiu/test01.dbf              5 AVAILABLE

SQL> drop tablespace test01 including contents and datafiles;                                                                                         

Tablespace dropped.

SQL> select FILE_NAME,FILE_ID,STATUS from dba_data_files where TABLESPACE_NAME='TEST01';                                                              

no rows selected

SQL> set linesize 800 pagesize 800                                                                                                                    
SQL> select * from scott.emp;                                                                                                                         

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980/12/17        818                    20
      7499 ALLEN      SALESMAN        7698 1981/02/20       1618        300         30
      7521 WARD       SALESMAN        7698 1981/02/22       1268        500         30
      7566 JONES      MANAGER         7839 1981/04/02       2993                    20
      7654 MARTIN     SALESMAN        7698 1981/09/28       1268       1400         30
      7698 BLAKE      MANAGER         7839 1981/05/01       2868                    30
      7782 CLARK      MANAGER         7839 1981/06/09       2468                    10
      7839 KING       PRESIDENT            1981/11/17       5018                    10
      7844 TURNER     SALESMAN        7698 1981/09/08       1518          0         30
      7900 JAMES      CLERK           7698 1981/12/03        968                    30
      7902 FORD       ANALYST         7566 1981/12/03       3018                    20
      7934 MILLER     CLERK           7782 1982/01/23       1818                    10

12 rows selected.

--------------------------------------------------------------------

数据库正常打开