流复制时ORA-04031问题

tsx197

A机：.AIX，oracle10.0.2.0.4，主机，DB自动分配SGA，流池手动设置500M；
B机：CENTOS5.5，oracle10.0.2.0.4，备机，DB自动分配SGA，流池手动设置500M；
做全库的流复制，目前流复制实施在6天前完成，过程中无报错。昨天上班发现一直没开始做同步，发现主机的Capture进程非正常中止。进一步查明发现是流池太小
证据1：
SQL> SELECT CAPTURE_NAME,QUEUE_NAME,RULE_SET_NAME,NEGATIVE_RULE_SET_NAME,STATUS FROM DBA_CAPTURE;
CAPTURE_NAME                   QUEUE_NAME
------------------------------ ------------------------------
RULE_SET_NAME                  NEGATIVE_RULE_SET_NAME         STATUS
------------------------------ ------------------------------ --------
CAPTURES_SRC                   CAPTURE_SRC
RULESET$_3                                                    ABORTED
证据2：
SQL> SELECT CAPTURE_NAME, QUEUE_NAME, STATUS, CAPTURED_SCN, APPLIED_SCN FROM DBA_CAPTURE;
CAPTURE_NAME                   QUEUE_NAME                     STATUS
------------------------------ ------------------------------ --------
CAPTURED_SCN APPLIED_SCN
------------ -----------
CAPTURES_SRC                   CAPTURE_SRC                    ABORTED
证据3：
SQL>SELECT CAPTURE_NAME,ERROR_NUMBER,ERROR_MESSAGE STATUS FROM DBA_CAPTURE;
CAPTURE_NAME  ERROR_NUMBER                                              STATUS
------------------ ----------------------------------------------------------------------------------------------
CAPTURES_SRC     4031          ORA-04031: unable to allocate 48 bytes of shared memory ("streams pool","unknown object","streams pool","kol vstring")

附：
附录1-主库SGA情况：
SQL> select * from v$Sgainfo;
NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      2113472 No
Redo Buffers                       14663680 No
Buffer Cache Size                6643777536 Yes
Shared Pool Size                 1.0050E+10 Yes
Large Pool Size                    16777216 Yes
Java Pool Size                     16777216 Yes
Streams Pool Size                  33554432 Yes
Granule Size                       16777216 No
Maximum SGA Size                 1.6777E+10 No
Startup overhead in Shared Pool   117440512 No
Free SGA Memory Available                 0
11 rows selected.
SQL> show sga;
Total System Global Area 1.6777E+10 bytes
Fixed Size                  2113472 bytes
Variable Size            1.0117E+10 bytes
Database Buffers         6643777536 bytes
Redo Buffers               14663680 bytes
附录2-告警日志情况
       目前，A机的告警文件从实施到现在的记录在附件中；B机的告警日志文件都没有相关的报错（从流复制的实施后一直没有ORA报错或是告警信息）。
附录3-后续情况
       把流池修改到512M后，流复制中的报错改为：ORA-04031: unable to allocate 40 bytes of shared memory ("streams pool","unknown object","streams pool","kol vstring")。当想把流池改到更大，比方1024M，768M，600M，550M，都出现以下报错：
SQL>ALTER SYSTEM SET STREAMS_POOL_SIZE = 550M;
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

问题1：目前怎样处理让流复制开始同步？
问题2：相隔了6天的数据，有十G左右的数据量，流复制能同步过吗？同步后会有什么影响吗？

[ 本帖最后由 tsx197 于 2012-6-26 13:07 编辑 ]

Maclean Liu(刘相兵

请在问题时段 做一个AWR 并上传

teapot

只要归档存在，可以复制，如果归档已经删除，那要恢复归档
500MB的Stream_pool还是小，有内存加到2G左右

tsx197

取不到AWR，要求输入DBID，重开一个终端查DBID又报错。
SQL> conn / as sysdba
Connected.
SQL> @awrrpt

Current Instance
~~~~~~~~~~~~~~~~
select d.dbid            dbid
*
ERROR at line 1:
ORA-04031: unable to allocate 3896 bytes of shared memory ("shared
pool","select d.dbid            dbi...","sga heap(1,0)","kglsim object batch")


begin
*
ERROR at line 1:
ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","begin
:rpt_options := 0;
end;","sga heap(1,0)","kglsim object batch")



Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text
select 'Type Specified: ',lower(nvl('text','html')) report_type from dual
*
ERROR at line 1:
ORA-04031: unable to allocate 3896 bytes of shared memory ("shared
pool","select 'Type Specified: ',lo...","sga heap(1,0)","kglsim object batch")


ERROR:
ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","BEGIN
DBMS_OUTPUT.ENABLE(NUL...","sga heap(1,0)","kglsim object batch")




Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  from dba_hist_database_instance wr, v$database cd, v$instance ci
       *
ERROR at line 13:
ORA-04031: unable to allocate 4064 bytes of shared memory ("shared
pool","select distinct
(case...","sga heap(1,0)","kglsim heap")



Enter value for dbid:

附查DBID的报错：
SQL> conn / as sysdba
Connected.
SQL>  select dbid from v$database;
select dbid from v$database
*
ERROR at line 1:
ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","kglsim object batch")


SQL> select dbid from v$database;
select dbid from v$database
*
ERROR at line 1:
ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","select dbid from v$database","sga heap(1,0)","kglsim object batch")

[ 本帖最后由 tsx197 于 2012-6-26 16:08 编辑 ]

Maclean Liu(刘相兵

action plan:

sqlplus / as sysdba

oradebug setmypid;
oradebug unlimit;
oradebug dump heapdump 536870914;

oradebug tracefile_name


上传压缩打包后以上获得的 TRACE


之后

alter system flush shared_pool;

alter system flush shared_pool;


@?/rdbms/admin/awrrpt

tsx197

先上传AWR文件。trc今天无法上传了，被限制了上传大小

[ 本帖最后由 tsx197 于 2012-6-27 14:19 编辑 ]