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. 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-24
57766127_TS-SYSTEM_FNO-1_0HMUP6IP_.DBF

D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\O1_MF_SYSAUX_DATA_D-BOSTON_I-24
57766127_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-245
7766127_TS-USERS_FNO-4_0MMUP6L7_.DBF

NAME
--------------------------------------------------------------------------------

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

D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\O1_MF_TS1_DATA_D-BOSTON_I-24577
66127_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]