{"rsdb":{"rid":"155947","subhead":"","postdate":"0","aid":"117064","fid":"57","uid":"1","topic":"1","content":"

ORA-01555 \u5feb\u7167\u8fc7\u65e7\uff0c\u662f\u6570\u636e\u5e93<\/a>\u4e2d\u5f88\u5e38\u89c1\u7684\u4e00\u4e2a\u9519\u8bef\uff0c\u6bd4\u5982\u5f53\u6211\u4eec\u7684\u4e8b\u52a1\u9700\u8981\u4f7f\u7528undo\u6765\u6784\u5efaCR\u5757\u7684\u65f6\u5019\uff0c\u800c\u6b64\u65f6\u5bf9\u5e94\u7684undo \u5df2\u7ecf\u4e0d\u5b58\u5728\u4e86\uff0c \u8fd9\u4e2a\u65f6\u5019\u5c31\u4f1a\u62a5ORA-01555\u7684\u9519\u8bef\u3002<\/p>

\u4e34\u65f6\u7684\u5904\u7406\u65b9\u6cd5\u5982\u4e0b:<\/p>

\u4ee5\u4e0b\u4e3a\u865a\u62df\u673a\u6a21\u62df\u64cd\u4f5c\uff0c\u5efa\u8bae\u6570\u636e\u5e93<\/a>\u5b89\u88c5\u7684\u65f6\u5019\u8fd9\u4e2a\u53c2\u6570\u4e00\u5b9a\u8981\u63d0\u524d\u8c03\u6574\u4f18\u5316\u4e00\u4e0b\uff0c\u4e0d\u8981\u4f7f\u7528\u9ed8\u8ba4\u503c\u3002<\/p>

[root@ysdb1 ~]# su - oracle
[oracle@ysdb1 ~]$ sqlplus \/ as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 23 10:39:44 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@ysdb1>show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900 --\u9ed8\u8ba415\u5206\u949f
undo_tablespace string UNDOTBS1
SYS@ysdb1>
SYS@ysdb1>alter system set undo_retention=10800 scope=both;
System altered.<\/p>

--\u67e5\u770b\u8868\u7a7a\u95f4\u5bb9\u91cf<\/p>

SYS@ysdb1>col tablespace_name for a15
SYS@ysdb1>col free_rate for a15
SYS@ysdb1>SELECT a.tablespace_name,
ROUND (a.total_size) \"total_size(MB)\",
ROUND (a.total_size) - ROUND (b.free_size, 3) \"used_size(MB)\",
ROUND (b.free_size, 3) \"free_size(MB)\",
ROUND (b.free_size \/ total_size * 100, 2) || '%' free_rate
FROM ( SELECT tablespace_name, SUM (bytes) \/ 1024 \/ 1024 total_size
FROM dba_data_files
GROUP BY tablespace_name) a,
( SELECT tablespace_name, SUM (bytes) \/ 1024 \/ 1024 free_size
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+);
TABLESPACE_NAME total_size(MB) used_size(MB) free_size(MB) FREE_RATE
--------------- -------------- ------------- ------------- ---------------
SYSAUX 600 178.625 421.375 70.23%
UNDOTBS1 200 56.062 143.938 71.97%
USERS 5 1 4 80%
SYSTEM 700 287.5 412.5 58.93%
UNDOTBS2 200 6.687 193.313 96.66%<\/p>

SYS@ysdb1> SELECT tablespace_name, status, SUM (bytes) \/ 1024 \/ 1024 \"Bytes(M)\"
FROM dba_undo_extents
GROUP BY tablespace_name, status;
TABLESPACE_NAME STATUS Bytes(M)
TABLESPACE_NAME STATUS Bytes(M)
--------------- --------- ----------
UNDOTBS1 UNEXPIRED 11
UNDOTBS2 UNEXPIRED 2.125
UNDOTBS1 EXPIRED 44.0625
UNDOTBS2 EXPIRED 3.5625<\/p>

1.\u51fa\u73b0ORA-01555\u9519\u8bef\uff0c\u901a\u5e38\u67092\u79cd\u60c5\u51b5\uff1a<\/p>

1)SQL\u8bed\u53e5\u6267\u884c\u65f6\u95f4\u592a\u957f\uff0c\u6216\u8005UNDO\u8868\u7a7a\u95f4\u8fc7\u5c0f\uff0c\u6216\u8005\u4e8b\u52a1\u91cf\u8fc7\u5927\uff0c\u6216\u8005\u8fc7\u4e8e\u9891\u7e41\u7684\u63d0\u4ea4\uff0c\u5bfc\u81f4\u6267\u884cSQL\u8fc7\u7a0b\u4e2d\u8fdb\u884c\u4e00\u81f4\u6027\u8bfb\u65f6\uff0cSQL\u6267\u884c\u540e\u4fee\u6539\u7684\u524d\u955c\u50cf\uff08\u5373UNDO\u6570\u636e\uff09\u5728UNDO\u8868\u7a7a\u95f4\u4e2d\u5df2\u7ecf\u88ab\u8986\u76d6\uff0c\u4e0d\u80fd\u6784\u9020\u4e00\u81f4\u6027\u8bfb\u5757\uff08CR blocks\uff09\u3002 \u8fd9\u79cd\u60c5\u51b5\u6700\u591a\u3002<\/p>

2)SQL\u8bed\u53e5\u6267\u884c\u8fc7\u7a0b\u4e2d\uff0c\u8bbf\u95ee\u5230\u7684\u5757\uff0c\u5728\u8fdb\u884c\u5ef6\u8fdf\u5757\u6e05\u9664\u65f6\uff0c\u4e0d\u80fd\u786e\u5b9a\u8be5\u5757\u7684\u4e8b\u52a1\u63d0\u4ea4\u65f6\u95f4\u4e0eSQL\u6267\u884c\u5f00\u59cb\u65f6\u95f4\u7684\u5148\u540e\u6b21\u5e8f\u3002 \u8fd9\u79cd\u60c5\u51b5\u5f88\u5c11\u3002<\/p>

2.\u7b2c1\u79cd\u60c5\u51b5\u89e3\u51b3\u7684\u529e\u6cd5\uff1a<\/p>

1)\u589e\u52a0UNDO\u8868\u7a7a\u95f4\u5927\u5c0f<\/p>

2)\u589e\u52a0undo_retention \u65f6\u95f4\uff0c\u9ed8\u8ba4\u53ea\u670915\u5206\u949f<\/p>

3)\u4f18\u5316\u51fa\u9519\u7684SQL\uff0c\u51cf\u5c11\u67e5\u8be2\u7684\u65f6\u95f4\uff0c\u9996\u9009\u65b9\u6cd5<\/p>

4)\u907f\u514d\u9891\u7e41\u7684\u63d0\u4ea4<\/p>","orderid":"0","title":"Oracle 11g RAC ORA-01555 \u5feb\u7167\u8fc7\u65e7\u62a5\u9519\u5904\u7406","smalltitle":"","mid":"0","fname":"\u6570\u636e\u5e93\u7f16\u7a0b","special_id":"0","bak_id":"0","info":"0","hits":"64","pages":"1","comments":"0","posttime":"2017-01-02 08:14:58","list":"1483316098","username":"admin","author":"","copyfrom":"","copyfromurl":"","titlecolor":"","fonttype":"0","titleicon":"0","picurl":"http:\/\/www.cppentry.com\/upload_files\/","ispic":"0","yz":"1","yzer":"","yztime":"0","levels":"0","levelstime":"0","keywords":"Oracle<\/A> 11g<\/A> RAC<\/A> ORA-01555<\/A> \u5904\u7406<\/A>","jumpurl":"","iframeurl":"","style":"","template":"a:3:{s:4:\"head\";s:0:\"\";s:4:\"foot\";s:0:\"\";s:8:\"bencandy\";s:0:\"\";}","target":"0","ip":"14.17.22.31","lastfid":"0","money":"0","buyuser":"","passwd":"","allowdown":"","allowview":"","editer":"","edittime":"0","begintime":"0","endtime":"0","description":"Oracle 11g RAC ORA-01555 \u5feb\u7167\u8fc7\u65e7\u62a5\u9519\u5904\u7406","lastview":"1490463791","digg_num":"0","digg_time":"0","forbidcomment":"0","ifvote":"0","heart":"","htmlname":"","city_id":"0"},"page":"1"}