ITPub博客

首页 > Linux操作系统 > Linux操作系统 > logical standby database问题解决---转

logical standby database问题解决---转

原创 Linux操作系统 作者:andyxu 时间:2009-07-31 16:42:49 0 删除 编辑

工程师check logstdby数据库状态报告发现自10月29号来logical standby  database就没有应用日志了.

select * from dba_logstdby_log;查看结果发现真是如此.

tail -n 1000 alert*.log

发现里面报不能alter database datafile "" resize xxM.

原来是在主库中扩展数据文件,备库不支持的dml语句报错.

在备库中.

alter database stop logical standby apply;

//alter session disable guard;

手工扩展datafile大小.

alter database datafile '' autoextend on;

alter database start logical standby apply

tail -f alert*.log

发现还是报错,select * form. dba_logstdby_process和dba_logstdby_event;中发现

在执行delete * from qr$*** 表报错.

原来qr$*** 表在主库中有1000多万条数据,如果用的是最大性能的逻辑数据库,oracle官方建议数据量不能太大.

只能查找资料如何处理,发现有下面好东东.按下面处理完后,问题解决.

A.9.1.2 Recovering from DML Failures
Do not use the SKIP_TRANSACTION procedure to filter DML failures. Not only is the DML that is seen in the events table skipped, but so is all the DML associated with the transaction. This will cause multiple tables.
DML failures usually indicate a problem with a specific table. For example, assume the failure is an out-of-storage error that you cannot resolve immediately. The following steps demonstrate one way to respond to this problem.
1. Bypass the table, but not the transaction, by adding the table to the skip list:
SQL> EXECUTE DBMS_LOGSTDBY.SKIP('DML','SCOTT','EMP');
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
From this point on, DML activity for the SCOTT.EMP table is not applied. After you correct the storage problem, you can fix the table, provided you set up a database link to the primary database that has administrator privileges to run procedures in the DBMS_LOGSTDBY package.
2. Using the database link to the primary database, drop the local SCOTT.EMP table and then re-create it, and pull the data over to the standby database.
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE('SCOTT','EMP','PRIMARYDB');
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
3. To ensure a consistent view across the newly instantiated table and the rest of the database, wait for SQL Apply to catch up with the primary database before querying this table. Refer to Section 9.4.6, "Adding or Re-Creating Tables On a Logical Standby Database" for a detailed example.
A.9.2 Troubleshooting SQL*Loader Sessions
Oracle SQL*Loader provides a method of loading data from different

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

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    167
  • 访问量
    291938