逻辑dataguard同步同步表空间的建立失败

linfeng_oracle

操作系统：window7   同一台机器上
数据库版本：10.2.0.1
主库信息：
db_file_name_convert：F:\oracle\product\10.2.0\oradata\logstdby, F:\oracle\product\10.2.0\oradata\primydb2
log_archive_config：DG_CONFIG=(PRIMYDB2,LOGSTDBY)
log_archive_dest_1：LOCATION=E:\archive\primydb2 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMYDB2
log_archive_dest_2：SERVICE=db_logstdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=LOGSTDBY

备库信息：
log_archive_config：DG_CONFIG=(PRIMYDB2,LOGSTDBY)
log_archive_dest_1：location=F:\oracle\product\10.2.0\oradata\LOGSTDBY\arc valid_for=(online_logfiles,all_roles) db_unique_name=LOGSTDBY
log_archive_dest_2：SERVICE=db_primydb2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMYDB2
log_archive_dest_3：location=F:\oracle\product\10.2.0\oradata\LOGSTDBY\std valid_for=(standby_logfiles,standby_role) db_unique_name=LOGSTDBY


主库建立新的表空间ts_test：
SQL>create tablespace ts_test datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMYDB2\ts_test.dbf'size 10m autoextend off;
SQL>alter system archive log current;

备库同步失败：
逻辑备库alert日志：
Tue Jul 31 20:18:48 2012
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: 所创建的文件已存在
OSD-04010: 指定了 <create> 选项, 但文件已经存在
LOGSTDBY id: XID 0x0002.019.00000124, hSCN 0x0000.00088b36, lSCN 0x0000.00088b36, Thread 1, RBA 0x000d.00000f7d.80, txnCscn 0x0000.00088b39, PID 3968, ORACLE.EXE (P004)
LOGSTDBY Apply process P004 pid=28 OS id=3968 stopped
Tue Jul 31 20:18:48 2012
LOGMINER: End mining logfile: F:\ORACLE\PRODUCT\10.2.0\ORADATA\LOGSTDBY\STD\ARC00015_0790077590.001
Tue Jul 31 20:18:48 2012
Errors in file f:\oracle\product\10.2.0\admin\logstdby\bdump\logstdby_lsp0_8980.trc:
ORA-12801: 并行查询服务器 P004 中发出错误信号
ORA-01119: 创建数据库文件 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMYDB2\ts_test.dbf' 时出错
ORA-27038: 所创建的文件已存在
OSD-04010: 指定了 <create> 选项, 但文件已经存在

从日志中可以看到在备库中建立数据文件的路径与主库的一样，所以报文件存在错误。

请问下这是哪里配置错误而导致的吗？

linfeng_oracle

OK，有空多研究下Data Guard Concepts and Administration Guide

newway

google上有一篇文章介绍了通过存储过程的方式来实现自动复制表空间数据文件【未验证】http://uhesse.com/2009/04/16/adding-tablespacesdatafiles-with-logical-standby-db-present/

[ 本帖最后由 newway 于 2012-7-31 22:03 编辑 ]

newway

Explanation
-----------

The db_file_name_convert and log_file_name_convert parameters do not function
on a logical standby.  You must use the following procedure to create the datafile
in the correct directory structure.

=============================================================
action:

1.  Temporarily bypass the database guard so you can make modifications to
    the logical standby database.
        
      Oracle 9i:
        SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_ON;

      Oracle 10g+:
        SQL> ALTER SESSION DISABLE GUARD;

2.  Execute the create tablespace command using the correct file specification:

        SQL> ALTER TABLESPACE t_table ADD DATAFILE '/oradata/mtsmith.dbf' SIZE 100M REUSE;
        
3.  Renable the database guard:

      Oracle 9i:
        SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_OFF;

      Oracle 10g+:
        SQL> ALTER SESSION ENABLE GUARD;

4.  Prepare to skip the failed DDL statement by finding the XIDUSN, XIDSLT, and
    XIDSQN values for the failed DDL statement using the DBA_LOGSTDBY_EVENTS view:

        SQL> SELECT XIDUSN, XIDSLT, XIDSQN FROM DBA_LOGSTDBY_EVENTS
          2> WHERE EVENT_TIME = (SELECT MAX(EVENT_TIME) FROM DBA_LOGSTDBY_EVENTS);

5.  Skip the failed DDL statement by using the DBMS_LOGSTDBY.SKIP_TRANSACTION procedure
    with the values found in step number 4:

        SQL> EXECUTE DBMS_LOGSTDBY.SKIP_TRANSACTION( /*xidusn*/, /*xidslt*/,
        /*xidsqn*/);

6.  Start log apply services on the logical standby database:

        SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
=====================================================================

Starting with Oracle 10g you can also define a SKIP-Handler which can also automatically
create a new Datafile in another Folder. You can find Details on this in the
Oracle® Data Guard Concepts and Administration Guide in the Chapter
"Managing a Logical Standby Database" of each Version.

[ 本帖最后由 newway 于 2012-7-31 21:30 编辑 ]