Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

78

积分

0

好友

0

主题
1#
发表于 2012-6-26 13:04:41 | 查看: 7844| 回复: 5
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 编辑 ]

alert_testone.txt

4.15 KB, 下载次数: 828

2#
发表于 2012-6-26 13:54:23
请在问题时段 做一个AWR 并上传

回复 只看该作者 道具 举报

3#
发表于 2012-6-26 14:08:50
只要归档存在,可以复制,如果归档已经删除,那要恢复归档
500MB的Stream_pool还是小,有内存加到2G左右

回复 只看该作者 道具 举报

4#
发表于 2012-6-26 14:27:28
取不到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 编辑 ]

awrrpt_1_11421_11433.zip

34.76 KB, 下载次数: 927

回复 只看该作者 道具 举报

5#
发表于 2012-6-26 14:38:35
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

回复 只看该作者 道具 举报

6#
发表于 2012-6-26 16:41:41
先上传AWR文件。trc今天无法上传了,被限制了上传大小

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

awr20120626.html

348.08 KB, 下载次数: 779

testone_ora_1794090.zip.001.txt

10 MB, 下载次数: 784

回复 只看该作者 道具 举报

您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-26 15:16 , Processed in 0.056298 second(s), 24 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569