数据库版本10g 操作系统平台linux 4.7

对于数据文件丢失的情况,往往想到的是rman恢复,如restore datafile n,然后再recover datafile n,这种方法依赖于备份,以及备份后的archivedlog,本文介绍一种不同的方法,主要是通过archivedlog来恢复,不过这种方法有些限制,下面举例说明下

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 22 16:57:55 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

'HELLO,WELCOMETOCOMETOSQLPLUS'
--------------------------------
hello,welcome to come to sqlplus

SQL> alter tablespace users add datafile '/u01/oradata/crm/user02.dbf' size 100m autoextend on maxsize 1024m;

Tablespace altered.

SQL> select file#,name from v$datafile;

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         1
/u01/oradata/crm/system01.dbf

         2
/u01/oradata/crm/sfcrm_ioc02.dbf

         3
/u01/oradata/crm/sysaux01.dbf

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         4
/u01/oradata/crm/users01.dbf

         5
/u01/oradata/crm/sfcrm01.dbf

         6
/u01/oradata/crm/sfcrm02.dbf

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         7
/u01/oradata/crm/sfcrm_ioc01.dbf

         8
/u01/oradata/crm/undotbs02_01.dbf

         9
/u01/oradata/crm/undotbs02_02.dbf

..........................中间省略
     FILE#
----------
NAME
--------------------------------------------------------------------------------
        67
/u01/oradata/crm/sf_cdh17.dbf

        68
/u01/oradata/crm/sf_cos12.dbf

        69
/u01/oradata/crm/sf_cos13.dbf

     FILE#
----------
NAME
--------------------------------------------------------------------------------
        70
/u01/oradata/crm/sf_cos14.dbf

        71
/u02/oradata/crm/sf_cos15.dbf

        72
/u01/oradata/crm/user02.dbf

72 rows selected.

SQL> alter database datafile 72 offline;

Database altered.

SQL> !mv /u01/oradata/crm/user02.dbf /u01/oradata/crm/user02.dbf-bk

SQL> alter database datafile 72 online;
alter database datafile 72 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 72 - see DBWR trace file
ORA-01110: data file 72: '/u01/oradata/crm/user02.dbf'

SQL> alter database create datafile '/u01/oradata/crm/user02.dbf' as '/u02/oradata/crm/user02.dbf';

-----这里我们将数据文件名更改了位置,如果不想更改可以更改成原来的位置和名字

Database altered.

SQL> recover automatic datafile 72;
Media recovery complete.

--------------注意这里,并不是简单的reover,完全取决于自数据文件创建后的所有archivedlog
SQL> alter database datafile '/u02/oradata/crm/user02.dbf' online;

Database altered.

SQL> select file#,name from v$datafile where file#=72;

     FILE#
----------
NAME
--------------------------------------------------------------------------------
        72
/u02/oradata/crm/user02.dbf

SQL> shutdown immediate
ORA-01031: insufficient privileges
SQL> conn sys/sys as sysdba
Connected.

'HELLO,WELCOMETOCOMETOSQLPLUS'
--------------------------------
hello,welcome to come to sqlplus

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 1375731712 bytes
Fixed Size                  2083880 bytes
Variable Size             369099736 bytes
Database Buffers          989855744 bytes
Redo Buffers               14692352 bytes
Database mounted.
Database opened.
SQL>

数据库能正常打开及关闭.这种方法适合刚创建不久的数据文件,如果数据文件已经创建很久了,那么恢复就需要很多的archivedlog,而且恢复速度可能会很慢.这种情况建议还是使用rman的方法,这样更快!