Logical standby 错误(ORA-01119))

Logical standby 错误(ORA-01119))
今天检查一台测试库的时候发现备库报ora-01119错误
此数据库环境:
OS Version:Oracle Linux Server release 6.5
DB Version: oracle 11.2.0.4
Alert 日志如下：
Fri Aug 14 16:32:50 2015
Fri Aug 14 16:32:50 2015
LOGSTDBY Apply process AS04 started with server id=4 pid=39 OS id=62922
LOGSTDBY Apply process AS02 started with server id=2 pid=37 OS id=62918
Fri Aug 14 16:32:50 2015
LOGSTDBY Apply process AS05 started with server id=5 pid=40 OS id=62924
Fri Aug 14 16:32:50 2015
LOGSTDBY Apply process AS01 started with server id=1 pid=36 OS id=62916
Fri Aug 14 16:32:50 2015
LOGSTDBY Apply process AS03 started with server id=3 pid=38 OS id=62920
create tablespace jm datafile '+DATA/netdata/datafile/jm01.dbf' size 2G autoextend off
ORA-1119 signalled during: create tablespace jm datafile '+DATA/netdata/datafile/jm01.dbf' size 2G autoextend off...
LOGSTDBY Apply process AS01 server id=1 pid=36 OS id=62916 stopped
Errors in file /u01/app/oracle/diag/rdbms/snetdata/netdata/trace/netdata_as01_62916.trc:
ORA-01119: 创建数据库文件 '+DATA/netdata/datafile/jm01.dbf' 时出错
Errors in file /u01/app/oracle/diag/rdbms/snetdata/netdata/trace/netdata_lsp0_62906.trc:
ORA-26808: Apply process AS01 died unexpectedly.
ORA-01119: 创建数据库文件 '+DATA/netdata/datafile/jm01.dbf' 时出错
LOGSTDBY Apply process AS02 server id=2 pid=37 OS id=62918 stoppedLOGSTDBY Apply process AS03 server id=3 pid=38 OS id=62920 stopped
LOGSTDBY Apply process AS04 server id=4 pid=39 OS id=62922 stoppedLOGSTDBY Apply process AS05 server id=5 pid=40 OS id=62924 stopped

在主库中增加了表空间，备库中未增加表空间
查metlink中文档解释如下
Cause
 1. If database is OMF on both primary and standby then no issues in creating datafile at logical standby side.

2. If no OMF then Apply terminate with (ORA-1119) below errors, Because logcal standby will not honour the db_file_name_convert.
如果你的主库和你的备库都使用的是OMF来管理的话，就会导致此错误
Solution
 SOLUTION
==========

1. Use OMF as a proactive measure.

2. If not run skip handler.

For example,

1. Stop SQL apply,

alter database stop logical standby apply;

2. Create skip handler,

SQL> CREATE OR REPLACE PROCEDURE SYS.HANDLE_TBS_DDL (
 OLD_STMT IN VARCHAR2,
 STMT_TYP IN VARCHAR2,
 SCHEMA IN VARCHAR2,
 NAME IN VARCHAR2,
 XIDUSN IN NUMBER,
 XIDSLT IN NUMBER,
 XIDSQN IN NUMBER,
 ACTION OUT NUMBER,
 NEW_STMT OUT VARCHAR2
 ) AS
 BEGIN
 -- All primary file specification that contains a directory
 -- /usr/orcl/primary/dbs
 -- should go to /usr/orcl/stdby directory specification
 NEW_STMT := REPLACE(OLD_STMT,
 'D:\APP\NSELVAKU\ORADATA\BOSTON\BOSTON\DATAFILE\','D:\APP\NSELVAKU\ORADATA\BOSTON\',
 'D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\');
 ACTION := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;
 EXCEPTION
 WHEN OTHERS THEN
 ACTION := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;
 NEW_STMT := NULL;
 END HANDLE_TBS_DDL;
 /

Procedure created.

SQL> EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'TABLESPACE',proc_name => 'sys.handle_tbs_ddl');

PL/SQL procedure successfully completed.

3. Start the SQL Apply,

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

Database altered.

4. Check the v$datafile,

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\O1_MF_SYSTEM_DATA_D-BOSTON_I-24
57766127_TS-SYSTEM_FNO-1_0HMUP6IP_.DBF

D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\O1_MF_SYSAUX_DATA_D-BOSTON_I-24
57766127_TS-SYSAUX_FNO-2_0IMUP6JS_.DBF

D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\O1_MF_UNDOTBS1_DATA_D-BOSTON_I-
2457766127_TS-UNDOTBS1_FNO-3_0LMUP6L4_.DBF

D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\O1_MF_USERS_DATA_D-BOSTON_I-245
7766127_TS-USERS_FNO-4_0MMUP6L7_.DBF

NAME
--------------------------------------------------------------------------------

D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\O1_MF_EXAMPLE_DATA_D-BOSTON_I-2
457766127_TS-EXAMPLE_FNO-5_0JMUP6KL_.DBF

D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\O1_MF_TS1_DATA_D-BOSTON_I-24577
66127_TS-TS1_FNO-6_0KMUP6KS_.DBF

D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\O1_MF_TS2_7H5FOZW2_.DBF
D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\O1_MF_TS3_7H5FTGNG_.DBF
D:\APP\NSELVAKU\ORADATA\CHICAGO\CHICAGO\DATAFILE\DATA04.ORA

9 rows selected.

实际操作如下解决
SQL> alter database stop logical standby apply;

Database altered.

SQL> CREATE OR REPLACE PROCEDURE SYS.HANDLE_TBS_DDL (
OLD_STMT IN VARCHAR2,
STMT_TYP IN VARCHAR2,
SCHEMA IN VARCHAR2,
NAME IN VARCHAR2,
XIDUSN IN NUMBER,
XIDSLT IN NUMBER,
XIDSQN IN NUMBER,
ACTION OUT NUMBER,
NEW_STMT OUT VARCHAR2
) AS
BEGIN
NEW_STMT := REPLACE(OLD_STMT,'+DATA/netdata/datafile/','/u02/oradata/netdata/');
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;
EXCEPTION
WHEN OTHERS THEN
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;
NEW_STMT := NULL;
END HANDLE_TBS_DDL;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20  

Procedure created.

SQL>  EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'TABLESPACE',proc_name => 'sys.handle_tbs_ddl');

PL/SQL procedure successfully completed.

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

Database altered.

注意这里两个参数：+DATA/netdata/datafile/为主库的路径,/u02/oradata/netdata/为备库的路径
检查日志
LOGSTDBY Apply process AS03 started with server id=3 pid=45 OS id=63912
Sat Aug 15 10:47:40 2015
LOGSTDBY Apply process AS04 started with server id=4 pid=46 OS id=63914
create tablespace jm datafile '/u02/oradata/netdata/jm01.dbf' size 2G autoextend off
Sat Aug 15 10:48:43 2015
Completed: create tablespace jm datafile '/u02/oradata/netdata/jm01.dbf' size 2G autoextend off
Sat Aug 15 10:48:54 2015
LOGMINER: End   mining logfile for session 1 thread 1 sequence 245, /u02/archive/1_245_882661788.dbf
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 246, /u02/archive/1_246_882661788.dbf
Sat Aug 15 10:49:54 2015
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_1
******************************************************************
Thread 1 advanced to log sequence 9 (LGWR switch)
  Current log# 3 seq# 9 mem# 0: /u02/oradata/netdata/group_3.261.882674015
Sat Aug 15 10:49:55 2015
ARC0: LGWR is actively archiving destination LOG_ARCHIVE_DEST_1
Archived Log entry 8 added for thread 1 sequence 8 ID 0x2042dad2 dest 1:
Sat Aug 15 10:50:08 2015
LOGMINER: End   mining logfile for session 1 thread 1 sequence 246, /u02/archive/1_246_882661788.dbf
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 247, /u02/archive/1_247_882661788.dbf
Sat Aug 15 10:50:19 2015
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_1
******************************************************************
Thread 1 advanced to log sequence 10 (LGWR switch)
  Current log# 1 seq# 10 mem# 0: /u02/oradata/netdata/group_1.269.882674261
Sat Aug 15 10:50:40 2015
Thread 1 advanced to log sequence 11 (LGWR switch)
  Current log# 2 seq# 11 mem# 0: /u02/oradata/netdata/group_2.267.882674055
Sat Aug 15 10:50:58 2015
Thread 1 advanced to log sequence 12 (LGWR switch)
  Current log# 3 seq# 12 mem# 0: /u02/oradata/netdata/group_3.261.882674015
LOGMINER: summary for session# = 1
LOGMINER: StartScn: 0 (0x0000.00000000)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 4222937 (0x0000.00406fd9)
LOGMINER: session_flag: 0x0
LOGMINER: Read buffers: 16
LOGMINER: Memory LWM: limit 10M, LWM 24M, 80%
LOGMINER: Memory Release Limit: 1M
Sat Aug 15 10:47:20 2015
LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=38 OS id=63896 sid=26 started
Sat Aug 15 10:47:20 2015
LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=39 OS id=63898 sid=409 started
Sat Aug 15 10:47:20 2015
LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=40 OS id=63900 sid=27 started
Sat Aug 15 10:47:37 2015
LSP2 started with pid=41, OS id=63904
Sat Aug 15 10:47:38 2015
LOGMINER: Begin mining logfile during commit scan for session 1 thread 1 sequence 245, /u02/archive/1_245_882661788.dbf
Sat Aug 15 10:47:40 2015
LOGMINER: End mining logfiles during commit scan for session 1
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 245, /u02/archive/1_245_882661788.dbf
Sat Aug 15 10:47:40 2015
LOGSTDBY Analyzer process AS00 started with server id=0 pid=42 OS id=63906
Sat Aug 15 10:47:40 2015
LOGSTDBY Apply process AS05 started with server id=5 pid=47 OS id=63916
Sat Aug 15 10:47:40 2015
LOGSTDBY Apply process AS01 started with server id=1 pid=43 OS id=63908
Sat Aug 15 10:47:40 2015
LOGSTDBY Apply process AS02 started with server id=2 pid=44 OS id=63910
Sat Aug 15 10:47:40 2015
LOGSTDBY Apply process AS03 started with server id=3 pid=45 OS id=63912
Sat Aug 15 10:47:40 2015
LOGSTDBY Apply process AS04 started with server id=4 pid=46 OS id=63914
create tablespace jm datafile '/u02/oradata/netdata/jm01.dbf' size 2G autoextend off
Sat Aug 15 10:48:43 2015
Completed: create tablespace jm datafile '/u02/oradata/netdata/jm01.dbf' size 2G autoextend off
Sat Aug 15 10:48:54 2015
LOGMINER: End   mining logfile for session 1 thread 1 sequence 245, /u02/archive/1_245_882661788.dbf
LOGMINER: Begin mining log

已经正常传输同步了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24486203/viewspace-1772652/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24486203/viewspace-1772652/