在测试LOGICAL STANDBY的时候,发现逻辑应用出现错误。这里简单描述一下碰到的两个小错误。
说是两个错误,其实是由一个错误引起的。说是一个错误,其实也不是什么大问题,PRIMARY数据库的SYSAUX表空间满了,在后台alert文件中一直报错。
由于这个表空间满了对测试的影响不大,而且表空间存储在裸设备上,无法扩展文件的大小,只能增加新的文件。而当时正在测试物理STANDBY,就一直没有理会这个错误。
到现在开始测试逻辑STANDBY,这个问题就变得比较严重了:
SQL> select * from v$logstdby_state;
PRIMARY_DBID SESSION_ID REALTIME_APPLY STATE
------------ ---------- --------------- ------------------------------
1712482917 1 Y SQL APPLY NOT ON
数据库总处于SQL APPLY NOT ON状态,检查dba_logstdby_events视图,发现出现了下面的错误信息:
SQL> select status from dba_logstdby_events where status_code != 0;
STATUS
---------------------------------------------------------------------------------------
ORA-16111: log mining and apply setting up
ORA-00604: error occurred at recursive SQL level 1
ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX
ORA-06512: at "SYS.LOGMNR_KRVRDLUID3", line 1263
ORA-06512: at line 1
ORA-16222: automatic Logical Standby retry of last action
ORA-16111: log mining and apply setting up
ORA-00604: error occurred at recursive SQL level 1
ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX
ORA-06512: at "SYS.LOGMNR_KRVRDLUID3", line 1263
ORA-06512: at line 1
ORA-16111: log mining and apply setting up
ORA-00604: error occurred at recursive SQL level 1
ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX
ORA-06512: at "SYS.LOGMNR_KRVRDLUID3", line 1263
ORA-06512: at line 1
SYSAUX表空间里面存放的大部分内容都是系统辅助用户的,这里面也包括LOGMNR的数据字典信息。显然不解决这个问题是无法启动LOGMNR的:
于是在主数据库添加了表空间:
SQL> ALTER TABLESPACE SYSAUX ADD DATAFILE '/dev/vx/rdsk/datavg/rac11g_sysaux_2_4g' size 4095m;
表空间已更改。
由于逻辑STANDBY根本没有办法启动,于是在LOGICAL STANDBY数据库手工添加了这个表空间:
SQL> ALTER TABLESPACE SYSAUX ADD DATAFILE '+DATA/rac11g/rac11g_sysaux_2_4g' size 4095m;
表空间已更改。
再次启动逻辑应用,恢复正常:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
数据库已更改。
SQL> SELECT * FROM V$LOGSTDBY_STATE;
PRIMARY_DBID SESSION_ID REALTIME_APPLY STATE
------------ ---------- --------------- ------------------------------
1712482917 1 Y APPLYING
SQL> SELECT * FROM V$LOGSTDBY_STATE;
PRIMARY_DBID SESSION_ID REALTIME_APPLY STATE
------------ ---------- --------------- ----------------------------------------
1712482917 1 Y IDLE
但是没过多久就发现了第二个问题:
SQL> select * from v$logstdby_state;
PRIMARY_DBID SESSION_ID REALTIME_APPLY STATE
------------ ---------- --------------- ------------------------------
1712482917 1 Y SQL APPLY NOT ON
逻辑STANDBY应用又停止了,再次检查dba_logstdby_events视图,发现在源数据库上添加的数据文件导致了错误:
SQL> select status from dba_logstdby_events
2 where status_code != 0
3 and status_code != 604;
STATUS
---------------------------------------------------------------------------------------
ORA-16111: log mining and apply setting up
ORA-16222: automatic Logical Standby retry of last action
ORA-16111: log mining and apply setting up
ORA-16111: log mining and apply setting up
ORA-16222: automatic Logical Standby retry of last action
ORA-16111: log mining and apply setting up
ORA-16111: log mining and apply setting up
ORA-16128: User initiated stop apply successfully completed
ORA-16111: log mining and apply setting up
ORA-16222: automatic Logical Standby retry of last action
ORA-16111: log mining and apply setting up
ORA-01119: error in creating database file '/dev/vx/rdsk/datavg/rac11g_sysaux_2_4g'
ORA-27040: file create error, unable to create file
SVR4 Error: 2: No such file or directory
ORA-01119: error in creating database file '/dev/vx/rdsk/datavg/rac11g_sysaux_2_4g'
ORA-27040: file create error, unable to create file
SVR4 Error: 2: No such file or directory
ORA-16111: log mining and apply setting up
ORA-16222: automatic Logical Standby retry of last action
ORA-16111: log mining and apply setting up
已选择16行。
看来成也萧何败也萧何。可写逻辑STANDBY不支持数据库的参数STANDBY_FILE_MANAGEMENT,否则设置为手工方式,问题就可以解决。
那么就需要跳过这个添加数据文件的步骤,好在Oracle的官方文档上给出的例子就适用,只需要做一点修改就可以了:
SQL> create or replace procedure handle_tbs_ddl
2 (
3 v_old_stmt in varchar2,
4 v_stmt_type in varchar2,
5 v_schema in varchar2,
6 v_name in varchar2,
7 v_xidusn in number,
8 v_xidslt in number,
9 v_xidsqn in number,
10 v_action out number,
11 v_new_stmt out varchar2
12 ) as
13 begin
14 v_new_stmt := replace(v_old_stmt, '/dev/vx/rdsk/datavg/', '+DATA/rac11g/datafile');
15 v_action := dbms_logstdby.skip_action_replace;
16 exception
17 when others then
18 v_action := dbms_logstdby.skip_action_error;
19 v_new_stmt := null;
20 end;
21 /
过程已创建。
SQL> exec dbms_logstdby.skip(stmt => 'TABLESPACE', proc_name => 'HANDLE_TBS_DDL');
PL/SQL 过程已成功完成。
SQL> alter database start logical standby apply immediate;
数据库已更改。
SQL> select * from v$logstdby_state;
PRIMARY_DBID SESSION_ID REALTIME_APPLY STATE
------------ ---------- --------------- ------------------------------
1712482917 1 Y IDLE
至此,逻辑STANDBY无法启用的错误解决,其实最开始的时候如果在逻辑STANDBY上直接以另外一个名字添加数据文件,就不会引发第二个错误。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-613554/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-613554/