oracle导入错误1119,【案例】Oracle报错ORA-1119产生原因和MOS官方解决办法

天萃荷净

Oracle研究中心案例分析：运维DBA反映Oracle数据库在进行logical standby同步create tablespace操作报错ORA-1119。

本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客

本文链接地址: logical standby ORA-1119

问题：logical standby 同步create tablespace操作，遇到如下问题：

ORA-1119 signalled during: create tablespace ts_test datafile'F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMYDB2\ts_test.dbf'size 10m autoextend off...

LOGSTDBY stmt: create tablespace ts_test datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMYDB2\ts_test.dbf'size 10m autoextend off

LOGSTDBY status: ORA-01119: 创建数据库文件 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMYDB2\ts_test.dbf' 时出错

ORA-27038: 所创建的文件已存在

我们知道，create tablespace属于ddl操作，可能有人会问，是不是logical standby不支持ddl？

其实是支持的，只是针对这种情况，不应该这样操作而已。

下面是mos提供的一个solution：

Cause

1. If databse is OMF on both primary and standby then no issues in creating datafile at logical standby side.

2. If no OMF then Apply terminate with (ORA-1119) below errors, Because logcal standby will not honour the db_file_name_convert.

Solution

SOLUTION

==========

1. Use OMF as a proactive measure.

2. If not run skip handler.

For example,

1. Stop SQL apply,

alter database stop logical standby apply;

2. Create skip handler,

SQL> CREATE OR REPLACE PROCEDURE SYS.HANDLE_TBS_DDL (

OLD_STMT IN VARCHAR2,

STMT_TYP IN VARCHAR2,

SCHEMA IN VARCHAR2,

NAME IN VARCHAR2,

XIDUSN IN NUMBER,

XIDSLT IN NUMBER,

XIDSQN IN NUMBER,

ACTION OUT NUMBER,

NEW_STMT OUT VARCHAR2

) AS

BEGIN

-- All primary file specification that contains a directory

-- /usr/orcl/primary/dbs

-- should go to /usr/orcl/stdby directory specification

NEW_STMT := REPLACE(OLD_STMT,

'D:\APP\NSELVAKU\ORADATA\BOSTON\BOSTON\DATAFILE\','D:\APP\NSELVAKU\ORADATA\BOSTON\',

'D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\');

ACTION := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;

EXCEPTION

WHEN OTHERS THEN

ACTION := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;

NEW_STMT := NULL;

END HANDLE_TBS_DDL;

/

Procedure created.

SQL> EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'TABLESPACE',proc_name => 'sys.handle_tbs_ddl');

PL/SQL procedure successfully completed.

3. OracleоStart the SQL Apply,

SQL>ALTER DATABASE START LOGICAL STANDBY APPLY;

Database altered.

4. Check the v$datafile,

SQL> select name from v$datafile;

NAME

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

D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\O1_MF_SYSTEM_DATA_D-BOSTON_I-2457766127_TS-SYSTEM_FNO-1_0HMUP6IP_.DBF

D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\O1_MF_SYSAUX_DATA_D-BOSTON_I-2457766127_TS-SYSAUX_FNO-2_0IMUP6JS_.DBF

D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\O1_MF_UNDOTBS1_DATA_D-BOSTON_I-2457766127_TS-UNDOTBS1_FNO-3_0LMUP6L4_.DBF

D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\O1_MF_USERS_DATA_D-BOSTON_I-2457766127_TS-USERS_FNO-4_0MMUP6L7_.DBF

D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\O1_MF_EXAMPLE_DATA_D-BOSTON_I-2457766127_TS-EXAMPLE_FNO-5_0JMUP6KL_.DBF

D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\O1_MF_TS1_DATA_D-BOSTON_I-2457766127_TS-TS1_FNO-6_0KMUP6KS_.DBF

D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\O1_MF_TS2_7H5FOZW2_.DBF

D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\O1_MF_TS3_7H5FTGNG_.DBF

D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\DATA04.ORA

9 rows selected.

另外还有几篇相关文档，大家也可以看看参考下，如下：

ORA-18008: DDL Not Applied in Logical Standby [ID 233730.1]

SKIPPING PARTITION DDL ON YOUR LOGICAL STANDBY DATABASE [ID 417597.1]

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-1119产生原因和MOS官方解决办法