primary主库上创建表空间无法同步到物理standby和逻辑standby
来源:互联网 发布:ptc三维软件 编辑:程序博客网 时间:2024/06/05 16:56
环境是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表空间已经同步
在主库创建表空间,无法同步到物理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表空间已经同步
0 0
- primary主库上创建表空间无法同步到物理standby和逻辑standby
- primary同步数据到逻辑standby时报违反唯一约束(ora-00001)导致逻辑standby同步停止
- 物理DG之Primary增删表空间(数据文件)对Standby的影响
- DG - 逻辑Standby创建
- dataguard 创建逻辑STANDBY
- 创建逻辑standby数据库
- 物理standby转换为逻辑standby的步骤
- 逻辑 standby
- oracle之data guard(1)—物理standby和primary切换
- 监控primary/standby数据库
- 创建物理standby (data guard)
- Oracle Data Guard 创建物理Standby数据库
- standby 创建
- 创建逻辑备用库(Logical Standby Database)
- 不能在逻辑STANDBY上创建JOB
- STANDBY
- 物理standby高级管理
- DataGuard - 物理Standby管理
- java的文件读写操作
- Ego20140918003——Golang Import使用
- oracle DBLink
- Debian安装软件是Debian GNU/Linux 5.0.4 _Lenny_ - Official amd64 DVD Binary-1 20100131-22:09
- LeetCode - Single Number
- primary主库上创建表空间无法同步到物理standby和逻辑standby
- 厦门大学智能多媒体实验室2012 - Summer School日程表
- 用户积分排名算法
- Keil C51编译及连接技术
- openstack 运行时调度思路
- Codeforces Round #266 (Div. 2)-C,D
- Simple Factory模式实例
- poj 3923 模拟
- java软件开发技术面试2015---持续更新中。。。