参数未配置正确导致访问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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24486203/viewspace-1710402/