oracle访问表空间失败,参数未配置正确导致访问tempfile表空间错误(ORA-01110: data file)...

又是一则关于db_covert参数未配置正确导致tempfile表空间错误

将主备DG配置好后，主备互切的后在切后的主库上导出数据出现以下问题：

[oracle@OLEDB-ST1 export]$ expdp netdata/netdata directory=bak dumpfile=netdata.dmp logfile=netdata.log schemas=netdata

Export: Release 11.2.0.4.0 - Production on Wed Jun 24 11:27:00 2015

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

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

ORA-01157: cannot identify/lock data file 202 - see DBWR trace file

ORA-01110: data file 202: '+DATA'

ORA-06512: at "SYS.DBMS_LOB", line 724

ORA-06512: at "SYS.DBMS_DATAPUMP", line 3964

ORA-06512: at line 1

查询主备db_covert参数

主(原备)

db_file_name_convert             string     +DATA/netdata/DATAFILE, /u02/o

radata/netdata, +DATA/pnetdata

SQL>  select file#,status,bytes/1024/1024,name from v$tempfile;

FILE# STATUS  BYTES/1024/1024 NAME

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

2 ONLINE         0 +DATA

1 ONLINE           100 /u02/oradata/netdata/temp01.dbf

备(原主)

db_file_name_convert             string     +DATA/netdata/DATAFILE, /u02/o

radata/netdata, +DATA/pnetdata

SQL> col name format A50

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

FILE# NAME

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

2 +DATA/netdata/tempfile/temp.262.882673291

查询一下临时表空间

SQL> select * from dba_temp_files;

select * from dba_temp_files

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 202 - see DBWR trace file

ORA-01110: data file 202: '+DATA'

加了一个临时数据文件

SQL> aLTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/netdata/temp01.dbf' size 100M autoextend off;

Tablespace altered.

SQL> set line 200

SQL>  select file#,status,bytes/1024/1024,name from v$tempfile;

FILE# STATUS  BYTES/1024/1024 NAME

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

2 ONLINE         0 +DATA

1 ONLINE           100 /u02/oradata/netdata/temp01.dbf

SQL> alter tablespace temp drop tempfile 2

2  ;

测试导出文件

[oracle@OLEDB-ST1 trace]$  expdp netdata/netdata directory=bak dumpfile=netdata.dmp logfile=netdata.log schemas=netdata

Export: Release 11.2.0.4.0 - Production on Wed Jun 24 12:26:11 2015

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

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

Starting "NETDATA"."SYS_EXPORT_SCHEMA_01":  netdata/******** directory=bak dumpfile=netdata.dmp logfile=netdata.log schemas=netdata

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

. . exported "NETDATA"."HR"                              5.546 KB      10 rows

Master table "NETDATA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for NETDATA.SYS_EXPORT_SCHEMA_01 is:

/data/backup/export/netdata.dmp

Job "NETDATA"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jun 24 12:26:48 2015 elapsed 0 00:00:36

总结:配置DG的时候一定要小心主备路径问题！！！

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