标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,598)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
月归档:十一月 2012
v$sgainfo中Free SGA Memory Available的各种情况解释
今天网友问到了v$sgainfo中的Free SGA Memory Available的一些情况,开始我也比较迷糊,为什么会出现Free SGA Memory Available的值不为0,通过查询一些试验和测试,对这个问题进行了有力的说明
数据库版本10G
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production
当前SGA各组件信息
SQL> select name,bytes/1024/1024 from v$sgainfo; NAME BYTES/1024/1024 -------------------------------- --------------- Fixed SGA Size 1.20853043 Redo Buffers 6.7890625 Buffer Cache Size 192 Shared Pool Size 88 Large Pool Size 4 Java Pool Size 4 Streams Pool Size 8 Granule Size 4 Maximum SGA Size 304 Startup overhead in Shared Pool 36 Free SGA Memory Available 0 11 rows selected.
sga配置
SQL> show parameter sga; NAME TYPE VALUE ------------------------------------ ----------- --------------- lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 304M sga_target big integer 304M
在10g中,当采用asmm管理时,如果sga_max_size=sga_target,则Free SGA Memory Available为0
修改sga_target
SQL> alter system set sga_target=290M; System altered.
再次查询v$sgainfo
SQL> select name,bytes/1024/1024 from v$sgainfo; NAME BYTES/1024/1024 -------------------------------- --------------- Fixed SGA Size 1.20853043 Redo Buffers 6.7890625 Buffer Cache Size 180 Shared Pool Size 88 Large Pool Size 4 Java Pool Size 4 Streams Pool Size 8 Granule Size 4 Maximum SGA Size 304 Startup overhead in Shared Pool 36 Free SGA Memory Available 12 11 rows selected.
再次查看sga_target值
SQL> show parameter sga; NAME TYPE VALUE ------------------------------------ ----------- -------------- lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 304M sga_target big integer 292M
在10g中,当采用asmm管理时,如果sga_max_size>sga_target,则Free SGA Memory Available为sga_max_size-sga_target
找出sga_target修改为292M而不是290M原因
SQL> select component, granule_size from v$sga_dynamic_components; COMPONENT GRANULE_SIZE ---------------------------------------------------------------- ------------ shared pool 4194304 large pool 4194304 java pool 4194304 streams pool 4194304 DEFAULT buffer cache 4194304 KEEP buffer cache 4194304 RECYCLE buffer cache 4194304 DEFAULT 2K buffer cache 4194304 DEFAULT 4K buffer cache 4194304 DEFAULT 8K buffer cache 4194304 DEFAULT 16K buffer cache 4194304 DEFAULT 32K buffer cache 4194304 ASM Buffer Cache 4194304 13 rows selected. SQL> select 4194304/1024/1024 from dual; 4194304/1024/1024 ----------------- 4 SQL> select 292/4 from dual; 292/4 ---------- 73
因为sga的内存分配是按照GRANULE为单位进行的,而在该库中sga对应组件的GRANULE为4M,所以我们修改的290M的最近的GRANULE整数倍为292M
sga_target为0的情况
SQL> show parameter sga_target; NAME TYPE VALUE ------------------------------------ ----------- ---------- sga_target big integer 0 SQL> select name,bytes/1024/1024 from v$sgainfo; NAME BYTES/1024/1024 -------------------------------- --------------- Fixed SGA Size 1.20846176 Redo Buffers 6.7890625 Buffer Cache Size 180 Shared Pool Size 88 Large Pool Size 4 Java Pool Size 4 Streams Pool Size 8 Granule Size 4 Maximum SGA Size 292 Startup overhead in Shared Pool 36 Free SGA Memory Available 0 11 rows selected.
在10g中,如果不采用asmm内存管理模式,Free SGA Memory Available为0
11g数据库版本
SQL> select * from v$version; BANNER ----------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production
查询v$sgainfo信息
SQL> select name,bytes/1024/1024 from v$sgainfo; NAME BYTES/1024/1024 -------------------------------- --------------- Fixed SGA Size 1.28236008 Redo Buffers 6.03515625 Buffer Cache Size 20 Shared Pool Size 116 Large Pool Size 4 Java Pool Size 4 Streams Pool Size 8 Shared IO Pool Size 0 Granule Size 4 Maximum SGA Size 299.320313 Startup overhead in Shared Pool 52.2684898 Free SGA Memory Available 140
oracle内存相关参数设置
SQL> show parameter memory; NAME TYPE VALUE ------------------------------------ ----------- ------------ hi_shared_memory_address integer 0 memory_max_target big integer 300M memory_target big integer 300M shared_memory_address integer 0 SQL> show parameter pga; NAME TYPE VALUE ------------------------------------ ----------- ------------ pga_aggregate_target big integer 0
数据库动态组件内存分配
SQL> select COMPONENT,CURRENT_SIZE/1024/1024 CURRENT_SIZE from V$MEMORY_DYNAMIC_COMPONENTS; COMPONENT CURRENT_SIZE ---------------------------------------------------------------- ------------ shared pool 116 large pool 4 java pool 4 streams pool 8 SGA Target 160 DEFAULT buffer cache 20 KEEP buffer cache 0 RECYCLE buffer cache 0 DEFAULT 2K buffer cache 0 DEFAULT 4K buffer cache 0 DEFAULT 8K buffer cache 0 DEFAULT 16K buffer cache 0 DEFAULT 32K buffer cache 0 Shared IO Pool 0 PGA Target 140 ASM Buffer Cache 0 16 rows selected.
初步怀疑:在11g的amm内存管理模式下Free SGA Memory Available和PGA Target相等
尝试修改pga_aggregate_target值
SQL> alter system set pga_aggregate_target=150M; System altered.
再次查询v$sgainfo
SQL> select name,bytes/1024/1024 from v$sgainfo; NAME BYTES/1024/1024 -------------------------------- --------------- Fixed SGA Size 1.28236008 Redo Buffers 6.03515625 Buffer Cache Size 8 Shared Pool Size 116 Large Pool Size 4 Java Pool Size 4 Streams Pool Size 8 Shared IO Pool Size 0 Granule Size 4 Maximum SGA Size 299.320313 Startup overhead in Shared Pool 52.2684898 Free SGA Memory Available 152
查询动态组件内存分布
SQL> select COMPONENT,CURRENT_SIZE/1024/1024 CURRENT_SIZE from V$MEMORY_DYNAMIC_COMPONENTS; COMPONENT CURRENT_SIZE ---------------------------------------------------------------- ------------ shared pool 116 large pool 4 java pool 4 streams pool 8 SGA Target 148 DEFAULT buffer cache 8 KEEP buffer cache 0 RECYCLE buffer cache 0 DEFAULT 2K buffer cache 0 DEFAULT 4K buffer cache 0 DEFAULT 8K buffer cache 0 DEFAULT 16K buffer cache 0 DEFAULT 32K buffer cache 0 Shared IO Pool 0 PGA Target 152 ASM Buffer Cache 0 16 rows selected.
进一步证明:在11g的amm内存管理模式下Free SGA Memory Available和PGA Target相等
设置pga_aggregate_target为150M,PGA Target为152M原因分析
SQL> select COMPONENT,GRANULE_SIZE from V$MEMORY_DYNAMIC_COMPONENTS; COMPONENT GRANULE_SIZE ---------------------------------------------------------------- ------------ shared pool 4194304 large pool 4194304 java pool 4194304 streams pool 4194304 SGA Target 4194304 DEFAULT buffer cache 4194304 KEEP buffer cache 4194304 RECYCLE buffer cache 4194304 DEFAULT 2K buffer cache 4194304 DEFAULT 4K buffer cache 4194304 DEFAULT 8K buffer cache 4194304 DEFAULT 16K buffer cache 4194304 DEFAULT 32K buffer cache 4194304 Shared IO Pool 4194304 PGA Target 4194304 ASM Buffer Cache 4194304 16 rows selected.
原理同上述10g中的sga_target分析,不再重复,主要就是:150M不能被4M整除,所以取最近的152M
整体总结
1.如果不采用asmm和amm,Free SGA Memory Available为0
2.如果采用asmm,当sga_max_size=sga_target,则Free SGA Memory Available为0
3.如果采用asmm,当sga_max_size>sga_target,则Free SGA Memory Available为sga_max_size-sga_target
4.如果采用amm,Free SGA Memory Available和PGA Target相等
WARNING: Subscription for node down event still pending
监听日志中出现很多”WARNING: Subscription for node down event still pending”警告
--监听日志中内容 …… 26-NOV-2012 09:35:48 * ping * 0 WARNING: Subscription for node down event still pending …… 26-NOV-2012 09:45:49 * ping * 0 WARNING: Subscription for node down event still pending …… --监听日志大小 $ ls -l /oracle/product/10g/network/log/listener.log -rw-r----- 1 oracle dba 229526148 Nov 26 14:20 /oracle/product/10g/network/log/listener.log --总计条数 $ grep "Subscription for node down event still pending" \ > /oracle/product/10g/network/log/listener.log|wc -l 77306
数据库版本和平台
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio NLSRTL Version 10.2.0.5.0 - Production
监听日志配置
$ more /oracle/product/10g/network/admin/listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /oracle/product/10g) (PROGRAM = extproc) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) )
解决方法
--监听文件中增加 SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFF --reload监听 lsnrctl reload
补充说明
SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFF will prevent the messages from being written to the log file and may also prevent the TNS Listener from hanging periodically. Setting SUBSCRIBE_FOR_NODE_DOWN_<listener_name> to OFF disables a necessary RAC functionality(Oracle Notification Service[ONS],fast application notification[FAN]). The above workaround is recommended only for non-RAC environments. The issue may be present in all 10g and newer installations.
具体参考:372959.1和340091.1