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

54 阅读 0 评论 36 点赞

我是靠谱客的博主活力早晨，最近开发中收集的这篇文章主要介绍primary主库上创建表空间无法同步到物理standby和逻辑standby，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

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

在主库创建表空间，无法同步到物理standby和逻辑standby
SQL> create tablespace test datafile 'F:APPADMINISTRATORORADATACTWtest.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:APPADMINISTRATORORADATAORCL1SREDO02.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:appadministratordiagrdbmsorcl1orcl1traceorcl1_pr00_6092.trc:
ORA-01274: 无法添加数据文件 'F:APPADMINISTRATORORADATACTWTEST.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:appadministratordiagrdbmsorcl1orcl1traceorcl1_pr00_6092.trc:
ORA-01274: 无法添加数据文件 'F:APPADMINISTRATORORADATACTWTEST.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:ORCL2REDO03.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:APPADMINISTRATORORADATACTWtest.dbf' size 10M
ORA-1119 signalled during: create tablespace test datafile 'F:APPADMINISTRATORORADATACTWtest.dbf' size 10M...
LOGSTDBY Apply process AS03 server id=3 pid=39 OS id=7176 stopped
Errors in file f:appadministratordiagrdbmsorcl2orcl2traceorcl2_as03_7176.trc:
ORA-01119: 创建数据库文件 'F:APPADMINISTRATORORADATACTWtest.dbf' 时出错
Wed Sep 17 14:42:00 2014
Errors in file f:appadministratordiagrdbmsorcl2orcl2traceorcl2_lsp0_7448.trc:
ORA-26808: 应用进程 AS03 意外停止。
ORA-01119: 创建数据库文件 'F:APPADMINISTRATORORADATACTWtest.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:APPADMINISTRATORPRODUCT11.2.0DBHOME_1DATABASEUNNAMED00010' as 'F:APPADMINISTRATORORADATAORCL1test.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:APPADMINISTRATORORADATACTWtest.dbf',
21 'D:orcl2test.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表空间已经同步