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

客官°小女子只卖身不卖艺 2022-08-12 02:59 121阅读 0赞

环境是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表空间已经同步