Oracle处于recover状态的数据文件是无法使用exp导出数据的

来源：互联网发布：相机标定算法编辑：程序博客网时间：2024/06/10 01:17

1、启动数据库，检查数据库现有数据文件

SQL> startup

ORACLE instance started.

Total System Global Area 413372416 bytes

Fixed Size 2253784 bytes

Variable Size 314575912 bytes

Database Buffers 92274688 bytes

Redo Buffers 4268032 bytes

Database mounted.

Database opened.

SQL> select name from v$datafile;

NAME

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

/u01/app/oracle/oradata/orcl/system01.dbf

/u01/app/oracle/oradata/orcl/sysaux01.dbf

/u01/app/oracle/oradata/orcl/undotbs01.dbf

/u01/app/oracle/oradata/orcl/users01.dbf

2、创建测试表空间及测试数据

SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test.dbf' size 5m;

Tablespace created.

SQL> create user a identified by aaa default tablespace test;

User created.

SQL> grant dba,connect,resource to a;

Grant succeeded.

SQL> conn a/aaa

Connected.

SQL> create table a (id int);

Table created.

SQL> insert into a values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from a;

----------

SQL> conn / as sysdba

Connected.

SQL> select name from v$datafile;

NAME

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

/u01/app/oracle/oradata/orcl/system01.dbf

/u01/app/oracle/oradata/orcl/sysaux01.dbf

/u01/app/oracle/oradata/orcl/undotbs01.dbf

/u01/app/oracle/oradata/orcl/users01.dbf

/u01/app/oracle/oradata/orcl/test.dbf

3、将新创建的数据文件offline，检查状态为recover

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test.dbf' recover;

alter database datafile '/u01/app/oracle/oradata/orcl/test.dbf' recover

ERROR at line 1:

ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test.dbf' offline;

Database altered.

SQL> select status,name from v$datafile;

STATUS

-------

NAME

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

SYSTEM

/u01/app/oracle/oradata/orcl/system01.dbf

ONLINE

/u01/app/oracle/oradata/orcl/sysaux01.dbf

ONLINE

/u01/app/oracle/oradata/orcl/undotbs01.dbf

STATUS

-------

NAME

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

ONLINE

/u01/app/oracle/oradata/orcl/users01.dbf

RECOVER

/u01/app/oracle/oradata/orcl/test.dbf

此时尝试导出数据报错如下，无法正常导出：

[oracle@db ~]$ exp a/aaa file='/home/oracle/a.dmp'

Export: Release 11.2.0.4.0 - Production on Wed Apr 20 20:52:19 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified users ...

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user A

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user A

About to export A's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export A's tables via Conventional Path ...

. . exporting table A

EXP-00056: ORACLE error 376 encountered

ORA-00376: file 5 cannot be read at this time

ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/test.dbf' ---处于recover状态的数据文件，导出时会报错

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully with warnings.

4、将OFFLINE的数据文件重新ONLINE后，导出正常

[oracle@db ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 20 20:53:02 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database datafile 5 online;

alter database datafile 5 online

ERROR at line 1:

ORA-01113: file 5 needs media recovery

ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/test.dbf'

SQL> recover datafile 5;

Media recovery complete.

SQL> alter database datafile 5 online;

Database altered.

[oracle@db ~]$ exp a/aaa file='/home/oracle/a.dmp'

Export: Release 11.2.0.4.0 - Production on Wed Apr 20 20:53:43 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified users ...

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user A

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user A

About to export A's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export A's tables via Conventional Path ...

. . exporting table A 1 rows exported --导出一行数据

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully without warnings.

0 0