sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

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

时间:2016-12-04 22:22   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 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官方解决办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1364.html

Oracle研究中心

关键词:

ORA-1119解决笔记

logical standby同步create tablespace操作报错ORA-1119