转:primary添加数据文件与逻辑standby的ORA-16226

From:

http://space.itpub.net/110321/viewspace-610604

问题描述:

环境:oracle10g standby

primary使用alter database ... resize 1000m 修改数据文件体积,

逻辑standby出现ORA-16226:逻辑standby的ORA-16226:DDL skipped due to lack of support

解决办法:

SQL> alter database guard none;

SQL> alter database datafile '/data/CMS/users01.dbf' resize 1000m;

SQL> alter database guard all;

经验总结:

遇到logical standby不支持的ddl,可按上述几步在standby端执行DDL

参见以先下metalink及

http://blog.oracle.com.cn/html/66/t-35866.html
http://www.eygle.com/case/bug9203.htm

Problem Description -------------------

 Creating a tablespace on the primary and placing the datafile in a directory that does not exist on the logical standby causes logical apply services to stop. Looking at the logical standby alert log we see the following errors: 

LOGSTDBY event: ORA-01119: error in creating database file '' 

LOGSTDBY stmt: create tablespace mtsmith The above errors can also be seen in the dba_logstdby_events view on the logical standby. Setting db_file_name_convert on the logical standby and retrying the operation does not resolve the issue. 

Solution Description

 -------------------- 

Perform. the following steps to fix the problem:

 1. Temporarily bypass the database guard so you can make modifications to the logical standby database.

 SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_ON; 

2. Execute the create tablespace command using the correct file specification: 

SQL> ALTER TABLESPACE t_table ADD DATAFILE '/oradata/mtsmith.dbf' SIZE 100M REUSE; 

3. Renable the database guard:

 SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_OFF;

 4. Prepare to skip the failed DDL statement by finding the XIDUSN, XIDSLT, and XIDSQN values for the failed DDL statement using the DBA_LOGSTDBY_EVENTS view: 

SQL> SELECT XIDUSN, XIDSLT, XIDSQN FROM DBA_LOGSTDBY_EVENTS 2> WHERE EVENT_TIME = (SELECT MAX(EVENT_TIME) FROM DBA_LOGSTDBY_EVENTS); 

5. Skip the failed DDL statement by using the DBMS_LOGSTDBY.SKIP_TRANSACTION procedure with the values found in step number 4:

SQL> EXECUTE DBMS_LOGSTDBY.SKIP_TRANSACTION( , , );

 

6. Start log apply services on the logical standby database: SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

 Explanation 

-----------

 The db_file_name_convert and log_file_name_convert parameters do not function on a logical standby. You must use the above procedure to create the datafile in the correct directory structure. 

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

转载于:http://blog.itpub.net/230160/viewspace-620044/