primary主库上创建表空间无法同步到物理standby和逻辑standby

环境是dataguard 一个主库 一个物理standby 一个逻辑standby

在主库创建表空间，无法同步到物理standby和逻辑standby
SQL>  create tablespace test datafile 'F:\APP\ADMINISTRATOR\ORADATA\CTW\test.dbf' size 10M;
表空间已创建。
SQL>

日志中报如下错误：
物理standby：
Primary database is in MAXIMUM PERFORMANCE mode
RFS[7]: Selected log 4 for thread 1 sequence 70 dbid 1369339915 branch 853518795
Wed Sep 17 12:30:18 2014
Media Recovery Waiting for thread 1 sequence 71 (in transit)
Wed Sep 17 12:30:19 2014
RFS[7]: Selected log 5 for thread 1 sequence 71 dbid 1369339915 branch 853518795
Wed Sep 17 12:30:19 2014
Archived Log entry 15 added for thread 1 sequence 70 ID 0x519ebf0b dest 1:
Recovery of Online Redo Log: Thread 1 Group 5 Seq 71 Reading mem 0
  Mem# 0: F:\APP\ADMINISTRATOR\ORADATA\ORCL1\SREDO02.LOG
Wed Sep 17 14:41:55 2014
File #10 added to control file as 'UNNAMED00010' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
MRP0: Background Media Recovery terminated with error 1274
Errors in file f:\app\administrator\diag\rdbms\orcl1\orcl1\trace\orcl1_pr00_6092.trc:
ORA-01274: 无法添加数据文件 'F:\APP\ADMINISTRATOR\ORADATA\CTW\TEST.DBF' - 无法创建文件
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 3138238
Errors in file f:\app\administrator\diag\rdbms\orcl1\orcl1\trace\orcl1_pr00_6092.trc:
ORA-01274: 无法添加数据文件 'F:\APP\ADMINISTRATOR\ORADATA\CTW\TEST.DBF' - 无法创建文件
Wed Sep 17 14:44:44 2014、


逻辑standby：
Thread 1 advanced to log sequence 3 (LGWR switch)
  Current log# 3 seq# 3 mem# 0: D:\ORCL2\REDO03.LOG
Wed Sep 17 13:52:36 2014
Archived Log entry 2 added for thread 1 sequence 2 ID 0xd7c5d588 dest 1:
Wed Sep 17 14:42:00 2014
 create tablespace test datafile 'F:\APP\ADMINISTRATOR\ORADATA\CTW\test.dbf' size 10M
ORA-1119 signalled during:  create tablespace test datafile 'F:\APP\ADMINISTRATOR\ORADATA\CTW\test.dbf' size 10M...
LOGSTDBY Apply process AS03 server id=3 pid=39 OS id=7176 stopped
Errors in file f:\app\administrator\diag\rdbms\orcl2\orcl2\trace\orcl2_as03_7176.trc:
ORA-01119: 创建数据库文件 'F:\APP\ADMINISTRATOR\ORADATA\CTW\test.dbf' 时出错
Wed Sep 17 14:42:00 2014
Errors in file f:\app\administrator\diag\rdbms\orcl2\orcl2\trace\orcl2_lsp0_7448.trc:
ORA-26808: 应用进程 AS03 意外停止。
ORA-01119: 创建数据库文件 'F:\APP\ADMINISTRATOR\ORADATA\CTW\test.dbf' 时出错
Wed Sep 17 14:42:00 2014
LOGSTDBY Apply process AS04 server id=4 pid=40 OS id=7100 stopped
Wed Sep 17 14:42:00 2014
LOGSTDBY Apply process AS02 server id=2 pid=38 OS id=6696 stopped
Wed Sep 17 14:42:00 2014
LOGSTDBY Apply process AS05 server id=5 pid=41 OS id=7264 stopped
Wed Sep 17 14:42:00 2014
LOGSTDBY Analyzer process AS00 server id=0 pid=36 OS id=7524 stopped
Wed Sep 17 14:42:00 2014
LOGSTDBY Apply process AS01 server id=1 pid=37 OS id=6532 stopped
Wed Sep 17 14:42:03 2014
LOGMINER: session#=1, builder MS01 pid=33 OS id=4572 sid=79 stopped
Wed Sep 17 14:42:03 2014
LOGMINER: session#=1, reader MS00 pid=32 OS id=8184 sid=11 stopped
Wed Sep 17 14:42:03 2014
LOGMINER: session#=1, preparer MS02 pid=34 OS id=7532 sid=134 stopped
LOGSTDBY status: ORA-16222: 逻辑备用数据库自动重试上一个操作
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
LOGMINER: Memory Size = 30M, Checkpoint interval = 150M
LOGMINER: SpillScn 3138220, ResetLogScn 1
LOGMINER: summary for session# = 1
LOGMINER: StartScn: 0 (0x0000.00000000)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 3138105 (0x0000.002fe239)
LOGMINER: session_flag 0x1

物理standby不能同步的原因是因为STANDBY_FILE_MANAGEMENT设置为MANUAL
解决方法如下
SQL> alter database create datafile  'F:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\UNNAMED00010' as 'F:\APP\ADMINISTRATOR\ORADATA\ORCL1\test.dbf';
数据库已更改。

SQL> alter database recover managed standby database using current logfile disco
nnect from session;
数据库已更改。

SQL>  ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=auto;
系统已更改。
SQL>
检查物理备库发现test表空间已经同步

逻辑standby不能同步原因是主库的数据文件目录和逻辑standby的文件目录不一样，需要把目录转换
解决方案如下：
SQL> CREATE OR REPLACE PROCEDURE SYS.HANDLE_TBS_DDL (
  2    OLD_STMT  IN  VARCHAR2,
  3    STMT_TYP  IN  VARCHAR2,
  4    SCHEMA    IN  VARCHAR2,
  5    NAME      IN  VARCHAR2,
  6    XIDUSN    IN  NUMBER,
  7    XIDSLT    IN  NUMBER,
  8    XIDSQN    IN  NUMBER,
  9    ACTION    OUT NUMBER,
 10    NEW_STMT  OUT VARCHAR2
 11  ) AS
 12  BEGIN
 13
 14  -- All primary file specification that contains a directory
 15  -- /usr/orcl/primary/dbs
 16  -- should go to /usr/orcl/stdby directory specification
 17
 18
 19    NEW_STMT := REPLACE(OLD_STMT,
 20                       'F:\APP\ADMINISTRATOR\ORADATA\CTW\test.dbf',
 21                       'D:\orcl2\test.dbf');
 22
 23    ACTION := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;
 24
 25  EXCEPTION
 26    WHEN OTHERS THEN
 27      ACTION := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;
 28      NEW_STMT := NULL;
 29  END HANDLE_TBS_DDL;
 30  /
过程已创建。

SQL>  ALTER DATABASE STOP LOGICAL STANDBY APPLY;
数据库已更改。

SQL> EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'TABLESPACE', proc_name => 'sys.handle_tbs_ddl');
PL/SQL 过程已成功完成。

SQL>  ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
数据库已更改。
SQL>

检查逻辑standby发现test表空间已经同步