Flex ASM环境中crsd无法启动造成Grid Infrastructure (GI) 启动失败 (Doc ID 2504902.1)

2017/04/05 by Leave a Comment

适用于:

Oracle Database Cloud Service – 版本 N/A 和更高版本
Oracle Database – Enterprise Edition – 版本 12.2.0.1 和更高版本
Oracle Database Cloud Schema Service – 版本 N/A 和更高版本
Oracle Database Exadata Cloud Machine – 版本 N/A 和更高版本
Oracle Cloud Infrastructure – Database Service – 版本 N/A 和更高版本
本文档所含信息适用于所有平台

 

 

症状

在一个Flex ASM环境里, Grid Infrastructure (GI) 启动失败, 而这时其它的一个或者多个节点上GI正在运行，
并且 “crsctl stat res -t -init” 的输出显示除了ora.crsd以外其它资源都是起来的。
这时 ora.crsd 的状态是offline 或者 intermediate。

集群的 alert.log 报如下错误:2018-04-05 15:16:53.918 [CRSD(2697)]CRS-8500: Oracle Clusterware CRSD process is starting with operating system process ID 2697
2018-04-05 15:17:00.608 [CRSD(2697)]CRS-1013: The OCR location in an ASM disk group is inaccessible. Details in /u01/app/grid/diag/crs/sp1frhodb102/crs/trace/crsd.trc.
2018-04-05 15:17:00.615 [CRSD(2697)]CRS-0804: Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage Storage layer error [Insufficient quorum to open OCR devices] [0]]. Details at (:CRSD00111:) in /u01/app/grid/diag/crs/sp1frhodb102/crs/trace/crsd.trc.

 

跟踪文件 crsd.trc 报如下错误:2018-04-05 15:17:01.732 : CLSCRED:2919112768: (:CLSCRED0101:)clsCredDomInitRootDom: Using user given storage context for repository access.
2018-04-05 15:17:01.757 : OCRRAW:2919112768: 8033 Error 4 querying length of attr ASM_DISCOVERY_ADDRESS2018-04-05 15:17:01.761 : OCRRAW:2919112768: 8033 Error 4 querying length of attr ASM_STATIC_DISCOVERY_ADDRESS

2018-04-05 15:17:01.798 : CLSCRED:2919112768: (:CLSCRED1079:)clsCredOcrKeyExists: Obj dom : SYSTEM.credentials.domains.root.ASM.Self.076fa97b2ac84f70ff7035254e98f38d.root not found
2018-04-05 15:17:01.798 : OCRRAW:2919112768: 7755 Error 4 opening dom root in 0x4d37e30

2018-04-05 15:17:01.816 : OCRRAW:2919112768: kgfnConnect2: kgfnGetBeqData failed

2018-04-05 15:17:01.816*:kgfn.c@4933: kgfnConnect2: kgfnGetBeqData failed
2018-04-05 15:17:01.816 : CSSCLNT:2919112768: clsssinit: initialized context: (0x4f23d50) flags 0x104
2018-04-05 15:17:01.821 : CSSCLNT:2919112768: clsssterm: terminating context (0x4f23d50)
2018-04-05 15:17:01.862 : OCRRAW:2919112768: kgfnConnect2Int: cstr=(DESCRIPTION=(TCP_USER_TIMEOUT=1)(TRANSPORT_CONNECT_TIMEOUT=60)(EXPIRE_TIME=1)(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=tcp)(HOST=nn.nn.255.13))(PORT=1526)))(CONNECT_DATA=(SERVICE_NAME=+ASM)))

2018-04-05 15:17:01.862*:kgfn.c@6685: kgfnConnect2Int: cstr=(DESCRIPTION=(TCP_USER_TIMEOUT=1)(TRANSPORT_CONNECT_TIMEOUT=60)(EXPIRE_TIME=1)(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=tcp)(HOST=nn.nn.255.13)(PORT=1526)))(CONNECT_DATA=(SERVICE_NAME=+ASM)))
2018-04-05 15:17:01.862*:kgfn.c@6853: kgfnConnect2Int: OCISessionBegin failed
2018-04-05 15:17:03.139 : OCRRAW:2919112768: kgfnRecordErr 1017 OCI error:
ORA-01017: invalid username/password; logon denied

2018-04-05 15:17:03.139*:kgfn.c@1707: kgfnRecordErrPriv: 1017 error=ORA-01017: invalid username/password; logon denied

2018-04-05 15:17:03.140 : default:2919112768: clsCredDomClose: Credctx deleted 0x4d45890
2018-04-05 15:17:03.140 : OCRRAW:2919112768: kgfnConnect2: failed to connect

2018-04-05 15:17:03.140*:kgfn.c@5253: kgfnConnect2: failed to connect
2018-04-05 15:17:03.140 : OCRRAW:2919112768: kgfnConnect2Retry: failed to connect connect after 1 attempts, 143s elapsed

2018-04-05 15:17:03.140 : OCRRAW:2919112768: kgfo_kge2slos error stack at kgfoAl06: ORA-01017: invalid username/password; logon denied
ORA-27300: OS system dependent operation:sslssunreghdlr failed with status: 0
ORA-27301: OS failure message: Error 0
ORA-27302: failure occurred at: sskgpreset1
ORA-15077: could not locate ASM instance serving a required diskgroup

 

 

原因

 

通常有如下原因:

1) sqlnet.ora 里有
SQLNET.AUTHENTICATION_SERVICES=none这项设置使得crsd连接到其它节点上的远程ASM实例所需要的任何OS认证，都变得失效。

2) ASM 密码不对

3) ASMlistener 子网与为私有interconnect配置不匹配。
执行 “oifcfg getif” 能看到 private interconnect (cluster interconnect) 所在的子网。

这个问题的报错在SQLNET.AUTHENTICATION_SERVICES=all时也会出现。

 

 

解决方案

 

1) 如果是sqlnet.ora 里有 SQLNET.AUTHENTICATION_SERVICES=none 或 SQLNET.AUTHENTICATION_SERVICES=all 的情况

1) 从 Grid Home SQLNET.ORA 文件 (位于 $ORACLE_HOME/network/admin) 里清除 “SQLNET.AUTHENTICATION_SERVICES=none” 或 “SQLNET.AUTHENTICATION_SERVICES=all”

2) 以强制方式重启 CRS

crsctl stop crs -f
crsctl start crs

参考 “Unable to startup CRS as ASM failed to startup with “ORA-01017: invalid username/password; logon denied Document 1681849.1”

 2) ASM 密码不对的情况 (这是在 sqlnet.ora 文件没有问题的情况下的可能的故障原因)

1)  按 MOS 文章 ” How to recreate shared ASM password file in 12c GI cluster Document 1929673.1” 所指示的重建ASM 密码

2) 以强制方式重启 CRS
crsctl stop crs -f
crsctl start crs

 3) ASMlistener 子网(subnet)与所配置的 interconnect 不匹配

按文章 Document 283684.1 里段落 “C. For 12c and 18c Oracle Clusterware with Flex ASM” 的步骤 3，重建ASMlistener。

 

一个快速的规避方法是在本地节点上使用sqlplus手动启动。
如果手动启动asm几分钟后ora.crsd还是没有online, 则以root身份执行”crsctl start res ora.crsd -init” 。

Filed Under: Oracle

oracle数据库老版本安装服务

2017/03/17 by Leave a Comment

提供老版本的oracle数据库介质安装服务，包括： Oracle 7.3.4,  Oracle 8 , Oracle 8i , Oracle 9i等

 

此为商业收费服务。 服务咨询 手机: 13764045638  QQ: 47079569

 

 oracle817ntee.zip
 0-For AIX/0-64/Oracle/Oracle817CD1.nrg
 0-For AIX/0-64/Oracle/Oracle817CD2.nrg
 0-For AIX/0-64/Oracle/Oracle_816.nrg
 oracle817 for unix.ISO #oracle817_for_Intel UNIX (DGUX Intel,SCO UnixWare,Solaris Intel).ISO
 p2376472_8174_AIX
 p2376472_8174_AIX64.zip
 p2376472_8174_AIX.zip
 linux81701.tar
Oracle 7.3 7.3.0 February 1996 7.3.4 Object-relational database
Oracle8 Database 8.0.3 June 1997 8.0.6 Recovery Manager, Partitioning. First version available for Linux.[14]
Oracle8i Database 8.1.5.0 1998 8.1.7.4 August 2000 Native internet protocols and Java, Virtual Private Database
Oracle9i Database 9.0.1.0 2001 9.0.1.5 December 2003 Oracle Real Application Clusters (RAC), Oracle XML DB
Oracle9i Database Release 2 9.2.0.1 2002 9.2.0.8 April 2007 Advanced Queuing, Data Mining, Streams, Logical Standby

 

 

 

 

Filed Under: Oracle

legacy oracle database media installation service

2017/03/17 by Leave a Comment

We provide legacy oracle database media installation service,  the service includes :  Oracle 7.3.4 , Oracle 8 , Oracle 8i ,Oracle 9i.  This is business Service.

 

 oracle817ntee.zip
 0-For AIX/0-64/Oracle/Oracle817CD1.nrg
 0-For AIX/0-64/Oracle/Oracle817CD2.nrg
 0-For AIX/0-64/Oracle/Oracle_816.nrg
 oracle817 for unix.ISO #oracle817_for_Intel UNIX (DGUX Intel,SCO UnixWare,Solaris Intel).ISO
 p2376472_8174_AIX
 p2376472_8174_AIX64.zip
 p2376472_8174_AIX.zip
 linux81701.tar

 

Oracle 7.3 7.3.0 February 1996 7.3.4 Object-relational database
Oracle8 Database 8.0.3 June 1997 8.0.6 Recovery Manager, Partitioning. First version available for Linux.[14]
Oracle8i Database 8.1.5.0 1998 8.1.7.4 August 2000 Native internet protocols and Java, Virtual Private Database
Oracle9i Database 9.0.1.0 2001 9.0.1.5 December 2003 Oracle Real Application Clusters (RAC), Oracle XML DB
Oracle9i Database Release 2 9.2.0.1 2002 9.2.0.8 April 2007 Advanced Queuing, Data Mining, Streams, Logical Standby

 

This is business Service.

contact us:           service@parnassusdata.com

 

 

 

Filed Under: Oracle

Solix优化Oracle E-Business Suite性能

2017/03/13 by Leave a Comment

Solix优化Oracle E-Business Suite性能

Filed Under: Oracle

SQL SERVER中的Error: 823 Msg报错

2017/02/26 by Leave a Comment

如果自己搞不定可以找诗檀软件专业SQL SERVER数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 ： 13764045638   QQ号:47079569    邮箱：service@parnassusdata.com

 

 

SQL Server使用Windows平台的标准API例如 ReadFile, WriteFile, ReadFileScatter, WriteFileGather 来实施IO操作。 在实施IO的过程中，SQL SERVER服务器检测这些API的报错信息。若这些API因操作系统原因报错，则SQL SERVER会报错Error 823 Msg。

典型的报错信息如下：

Error: 823, Severity: 24, State: 2.
2010-03-06 22:41:19.55 spid58 The operating system returned error 1117 (The request could not be performed because of an I/O device error.) to SQL Server during a read at offset 0x0000002d460000 in file ‘e:\program files\Microsoft SQL Server\mssql\data\mydb.MDF’. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe, system-level error condition that threatens database integrity and must be corrected immediately. It is recommended to complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

 

若用户运行DBCC CHECKDB 检测数据库，则可能看到相关的数据文件有报错，但也有可能看不到。若执行该命令无任何报错，则可能遇到了一个系统临时发生的问题或磁盘问题。

原因:

823 报错通常说明存储子系统例如文件系统、驱动器或存储存在问题。当文件系统损坏导致数据文件损害时可能出现该问题。在读取数据时，SQL SERVER会在遇到823错误后再次发起读取请求，若再次读取成功，则查询不会失败；但是825告警会被写入到错误日志和事件日志中。

 

措施

 

 

可以检查MSDB里的suspect_pages表来发现存在问题的数据页面：

SQLCMD.EXE -S localhost\sqlexpress
1> use msdb;
2> select * from suspect_pages;
3> go
Changed database context to 'msdb'.
database_id file_id page_id event_type error_count last_update_date
----------- ----------- -------------------- ----------- ----------- -----------------------

(0 rows affected)


使用DBCC CHECKDB 命令来检测位于同一个存储单元上的所有数据库


1> dbcc checkdb([china])
2> go
DBCC results for 'china'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
DBCC results for 'sys.sysrscols'.
There are 1229 rows in 15 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 164 rows in 3 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysclones'.
There are 0 rows in 0 pages for object "sys.sysclones".
DBCC results for 'sys.sysallocunits'.
There are 195 rows in 3 pages for object "sys.sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.sysseobjvalues'.
There are 0 rows in 0 pages for object "sys.sysseobjvalues".
DBCC results for 'sys.syspriorities'.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for 'sys.sysdbfrag'.
There are 0 rows in 0 pages for object "sys.sysdbfrag".
DBCC results for 'sys.sysfgfrag'.
There are 0 rows in 0 pages for object "sys.sysfgfrag".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.syspru'.
There are 0 rows in 0 pages for object "sys.syspru".
DBCC results for 'sys.sysbrickfiles'.
There are 0 rows in 0 pages for object "sys.sysbrickfiles".
DBCC results for 'sys.sysphfg'.
There are 1 rows in 1 pages for object "sys.sysphfg".
DBCC results for 'sys.sysprufiles'.
There are 2 rows in 1 pages for object "sys.sysprufiles".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysdbreg'.
There are 0 rows in 0 pages for object "sys.sysdbreg".
DBCC results for 'sys.sysprivs'.
There are 177 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 2428 rows in 33 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscsrowgroups'.
There are 0 rows in 0 pages for object "sys.syscsrowgroups".
DBCC results for 'sys.sysextsources'.
There are 0 rows in 0 pages for object "sys.sysextsources".
DBCC results for 'sys.sysexttables'.
There are 0 rows in 0 pages for object "sys.sysexttables".
DBCC results for 'sys.sysextfileformats'.
There are 0 rows in 0 pages for object "sys.sysextfileformats".
DBCC results for 'sys.sysmultiobjvalues'.
There are 0 rows in 0 pages for object "sys.sysmultiobjvalues".
DBCC results for 'sys.syscolpars'.
There are 1015 rows in 18 pages for object "sys.syscolpars".
DBCC results for 'sys.sysxlgns'.
There are 0 rows in 0 pages for object "sys.sysxlgns".
DBCC results for 'sys.sysxsrvs'.
There are 0 rows in 0 pages for object "sys.sysxsrvs".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.sysusermsgs'.
There are 0 rows in 0 pages for object "sys.sysusermsgs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysrmtlgns'.
There are 0 rows in 0 pages for object "sys.sysrmtlgns".
DBCC results for 'sys.syslnklgns'.
There are 0 rows in 0 pages for object "sys.syslnklgns".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 34 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 224 rows in 5 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 425 rows in 3 pages for object "sys.sysiscols".
DBCC results for 'sys.sysendpts'.
There are 0 rows in 0 pages for object "sys.sysendpts".
DBCC results for 'sys.syswebmethods'.
There are 0 rows in 0 pages for object "sys.syswebmethods".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysaudacts'.
There are 0 rows in 0 pages for object "sys.sysaudacts".
DBCC results for 'sys.sysobjvalues'.
There are 225 rows in 20 pages for object "sys.sysobjvalues".
DBCC results for 'sys.syscscolsegments'.
There are 0 rows in 0 pages for object "sys.syscscolsegments".
DBCC results for 'sys.syscsdictionaries'.
There are 0 rows in 0 pages for object "sys.syscsdictionaries".
DBCC results for 'sys.sysclsobjs'.
There are 16 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 179 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 113 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysfoqueues'.
There are 0 rows in 0 pages for object "sys.sysfoqueues".
DBCC results for 'sys.syschildinsts'.
There are 0 rows in 0 pages for object "sys.syschildinsts".
DBCC results for 'sys.syscompfragments'.
There are 0 rows in 0 pages for object "sys.syscompfragments".
DBCC results for 'sys.sysftsemanticsdb'.
There are 0 rows in 0 pages for object "sys.sysftsemanticsdb".
DBCC results for 'sys.sysftstops'.
There are 0 rows in 0 pages for object "sys.sysftstops".
DBCC results for 'sys.sysftproperties'.
There are 0 rows in 0 pages for object "sys.sysftproperties".
DBCC results for 'sys.sysxmitbody'.
There are 0 rows in 0 pages for object "sys.sysxmitbody".
DBCC results for 'sys.sysfos'.
There are 0 rows in 0 pages for object "sys.sysfos".
DBCC results for 'sys.sysqnames'.
There are 98 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 100 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 112 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 19 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 3 rows in 1 pages for object "sys.sysbinsubobjs".
DBCC results for 'sys.syssoftobjrefs'.
There are 2 rows in 1 pages for object "sys.syssoftobjrefs".
DBCC results for 'sys.sqlagent_jobs'.
There are 0 rows in 0 pages for object "sys.sqlagent_jobs".
DBCC results for 'sys.sqlagent_jobsteps'.
There are 0 rows in 0 pages for object "sys.sqlagent_jobsteps".
DBCC results for 'sys.sqlagent_job_history'.
There are 0 rows in 0 pages for object "sys.sqlagent_job_history".
DBCC results for 'sys.sqlagent_jobsteps_logs'.
There are 0 rows in 0 pages for object "sys.sqlagent_jobsteps_logs".
DBCC results for 'sys.plan_persist_query_text'.
There are 0 rows in 0 pages for object "sys.plan_persist_query_text".
DBCC results for 'sys.plan_persist_query'.
There are 0 rows in 0 pages for object "sys.plan_persist_query".
DBCC results for 'sys.plan_persist_plan'.
There are 0 rows in 0 pages for object "sys.plan_persist_plan".
DBCC results for 'sys.plan_persist_runtime_stats'.
There are 0 rows in 0 pages for object "sys.plan_persist_runtime_stats".
DBCC results for 'sys.plan_persist_runtime_stats_interval'.
There are 0 rows in 0 pages for object "sys.plan_persist_runtime_stats_interval".
DBCC results for 'sys.plan_persist_context_settings'.
There are 0 rows in 0 pages for object "sys.plan_persist_context_settings".
DBCC results for 'sys.plan_persist_query_hints'.
There are 0 rows in 0 pages for object "sys.plan_persist_query_hints".
DBCC results for 'sys.plan_persist_query_template_parameterization'.
There are 0 rows in 0 pages for object "sys.plan_persist_query_template_parameterization".
DBCC results for 'sys.plan_persist_wait_stats'.
There are 0 rows in 0 pages for object "sys.plan_persist_wait_stats".
DBCC results for 'sys.persistent_version_store'.
There are 0 rows in 0 pages for object "sys.persistent_version_store".
DBCC results for 'sys.persistent_version_store_long_term'.
There are 0 rows in 0 pages for object "sys.persistent_version_store_long_term".
DBCC results for 'sys.wpr_bucket_table'.
There are 0 rows in 0 pages for object "sys.wpr_bucket_table".
DBCC results for 'largetable'.
There are 6291456 rows in 45924 pages for object "largetable".
DBCC results for 'largetabl2'.
There are 6291456 rows in 31937 pages for object "largetabl2".
DBCC results for '??'.
There are 0 rows in 0 pages for object "??".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'sys.filestream_tombstone_2073058421'.
There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421".
DBCC results for 'sys.syscommittab'.
There are 0 rows in 0 pages for object "sys.syscommittab".
DBCC results for 'sys.filetable_updates_2105058535'.
There are 0 rows in 0 pages for object "sys.filetable_updates_2105058535".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'china'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



检查Window的事件日志 看有无存储或IO子系统的报错，例如当你看到823报错时可能时间日志中也有
The driver detected a controller error on \Device\Harddisk4\DR4 这样的信息。基于这些信息确定出现问题的磁盘。
Filed Under: MSSQL SQL SERVER

如何诊断SQL SERVER中的MSG 824错误

2017/02/26 by Leave a Comment

如果自己搞不定可以找诗檀软件专业SQL SERVER数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 ： 13764045638   QQ号:47079569    邮箱：service@parnassusdata.com

 

 

如何诊断SQL SERVER中的MSG 824错误

 

若用户在SQL SERVER的错误日志或Windows事件日志中发现以下信息，一般说明了当读取或写入一个数据页面时可能出现了逻辑检测错误：

 

2009-11-02 15:46:42.90 spid51      
Error: 824, Severity: 24, State: 2.
2009-11-02 15:46:42.90 spid51      
SQL Server detected a logical consistency-based 
I/O error: incorrect pageid (expected 1:43686; actual 0:0). 
It occurred during a read of page (1:43686) in database ID 23 
at offset 0x0000001554c000 in file 'H:\MSSQL.SQL2008\MSSQL\DATA\my_db.mdf'.  
Additional messages in the SQL Server error log or system event log may provide more detail. 
This is a severe error condition that threatens database integrity and must be corrected immediately. 
Complete a full database consistency check (DBCC CHECKDB). 
This error can be caused by many factors; for more information, see SQL Server Books Online.

 

 

 

若一个应用程序在读取或修改数据时持续遇到上述错误，则会导致应用端报错且数据库连接会话会被中断。

 

 

原因

Sql server使用Windows的API例如ReadFile,WriteFile,ReadFileScatter,WriteFileGather 来实施IO操作，在做些IO操作时，SQL Server检测这些API系统调用的相关错误条件。若这些API因为操作系统错误而失败，那么SQL SERVER的报错是Error 823。但有些场景中虽然Windows系统API实际上成功调用，但基于I/O操作的数据传送可能遇到逻辑检测问题。这些逻辑检测错误会以Error 824告警。

 

824错误包含下面的信息：

  • 该IO操作涉及的数据库MDF文件
  • 该IO操作尝试发生的文件上的OFFSET偏移量
  • 该数据文件所数据的数据库
  • 该IO操作所发生的Page号
  • 这是一个读取或写入操作
  • 逻辑一致性检测失败的细节，即所作的检测类型，检测字段的预期值和实际值

 

这些逻辑一致性检测是由SQL SERVER发起的额外的完整性检测，以保证数据传输过程中特点的数据键值在I/O操作中得到必要的维护和验证。这些检测包括checksum校验，页面分裂，部分传输更新，错误的PageID，读取到陈旧数据，页面设计失败等。具体做的检测内容取决于数据库和服务级别的设置。

 

824错误信息一般说明对应IO路径下的底层存储系统或者硬件或驱动器存在问题。例如文件系统损坏导致的数据文件损害。

建议用户检测文件系统、驱动器或存储；若发现都没有问题， 则考虑基于备份恢复数据。 同时可以设置PAGE_VERIFY 来验证数据库

 

ALTER DATABASE [PageVerifyTest] SET PAGE_VERIFY CHECKSUM
GO

 

Filed Under: MSSQL SQL SERVER

12c vs 11g x$messages

2017/02/17 by Leave a Comment
ACMS initialization ACMS
ADR PDB Auto Purge Task MMON
ADR Space Management Statistics Flush MMON
ARCH initialization ARC*
ASH Progressive Flusher (KEWA) MMON
ASM Audittrail cleanup MMON
AWR PDB Auto Flush Task MMON
AWR PDB Auto Purge Task MMON
AWR Raw Metrics Capture GEN1
Action-Based process Test GEN0
Archiver disconnect ARCH
Auxilary ipc finish gen0 action GEN0
Auxilary ipc init gen0 action GEN0
Auxilary ipc intr gen0 action GEN0
Auxilary ipc message gen0 action GEN0
Auxilary ipc timedout gen0 action GEN0
BA container GEN0 action GEN0
CLI AutoPartition MMON
CLI Create All Slave Tasks GEN0
CLI Create One Slave Task GEN0
Check for async in-memory job messages CJQ0
Cleanup of unpinned KGL handles MMON
Column-Level Statistics flush MMON
DBRM ADG in-memory state refresh DBRM
DBWO timeout kcbifc DBW0
DBWR write buffers DBW*|BW*
DDE Periodic Dump Scheduler MMON
DMON do critical instance eval and registration DMON
DSKM fini DSKM
DSKM init DSKM
DSKM procures HCA loadavg and computes offloaded write thresh DSKM
DTrace based Kernel IO  Outlier  Processing GEN0
Direct NFSv4 RENEW lease operation GEN0
Execute on-demand tuning task (KESTS) MMON
Free java patching locks LCK0
GEN0 Master Check GEN0
Get java patching locks LCK0
Hang Manager parameters GEN0
ILM check MMON
ILM cleanup MMON
ILM row access flush MMON
ILM segment access flush MMON
IMCO ADO action IMCO
IMCO FastStart Defer Write Scheduler IMCO
IMCO Trickle Repopulate IMCO
IMCO action IMCO
IMCO cycle action IMCO
IMCO global dictionary action IMCO
IMCO worker action IMCO
Inactive Account Time Job GEN0
Index usage tracking statistics flush MMON
Initiate KSBCITST TEST
KEWR SlavePool Test MMON Main MMON
KJBFP PBR logfile scan LMFC
KJBFP PBR recovery LMFC
KJBFP PBR writer main LMFC
KJBFP increment PRI LMFC
KJBFP pbr logFile CLose *
KJFM update process heartbeat LCK*|DIA*|LGWR|CKPT|DBRM|IPC0
KQLM interrupt action LCK1
KQLM invalidation instance lock operation LCK1
KSB GEN1 init GEN1
KSGL initialize service IPC0
KSGL mount in IPC0 IPC0
KSGL node exit IPC0
KSGL node join IPC0
KSGL notify IPC0 IPC0
KSGL timeout IPC0
KSIPC Grp Refresh action IPC0
KSIPC MGA Segment Check IPC0
KSIPC finish action IPC0
KSIPC initialize server IPC0
KSIPC interrupt action IPC0
KSIPC msg action IPC0
KSIPC reconfig action IPC0
KSIPC shutdown action IPC0
KSIPC timeout action IPC0
KSM SGA slaves spawn GEN0
KSRMA RMA OP IPC0
KSRMA Recovery Log Allocation IPC0
KSRMA mount IPC0
KSU GUID MAC Address update GEN0
LGWR flush workers LGWR
LGWR initialization LGWR
MMON request to purge LTXID history table MMON
Monitor initialization TMON
Monitor wakeup TMON
Multi procs per DTP UTMU
Network Server forced NSS*
Network Server shutdown NSS*
PDB SGA init GEN0
PDB close abort GEN0
PMON notify IPC0 of process failure IPC0
PQ: Adjust Slave Pool MMON
Payload action to BG RMON
Process new DBs that join ASM locally DIA*
RMON BG Driver RMON
RMON Init Action RMON
RTTD initialization RTTD
Real-Time ADDM Trigger MMON
Redo writer quiesce IMC on standby LGWR
Refresh active service cache MMON
Report Capture Daemon MMON
Report Capture Test (KERPI) MMON
SGA deferred allocated granules Initialization MMAN
SGA deferred allocated granules move MMAN
SMON_SCN_TIME Copy to PDBs MMON
SQL Memory Management Calculation DBRM
Spawn processes on behalf of someone else GEN0
Standby media recovery info cleanup LGWR
Suspended session cleanup GEN0
Sweep PL/SQL incidents MMON
Switchover/PDB relocate message channel subscribe LGWR
TPZ initialization TPZ*
Test Driver wakeup RTTD
Test Process wakeup TPZ*
Timeout interrupt action RBAL
Triton Session Cleanup MMON
UMF Auto Task Pool Queue Server MMON
UMF Auto Task Pool Scheduler MMON
UTS Async Dump GEN0
Volume Resource Action GEN0
Wait event outlier detection GEN0
XStream timeout action GEN0
acquire enq during pdbopen by HARIM DBW0
action for buddy instance RMS0
action to cleanup buddy instance context RMS0
check for KJCI cross-instance requests *
cleaning up workload information for optimizer MMON
clear the dependent scn DBRM
dblink logon table cleanup MMON
enter / exit graph test specified wait *
event nfy timeout action GEN0
event outlier dump info. GEN0
extend quarantine area GEN0
flushing workload information for optimizer MMON
free PX memory chunks in background PXMN
get/release open thread enqueue DBW*|BW*
init function for LCK1 *
initiate block repair GEN0
kcb DW object cooling GEN0
kcbz background redodump GEN0
kcbz update TSE bh CKPT
kill client GEN0
ksim cache line update LCK0
ksim instance group membership notifier *
kxfp remote slave spawn recv function PXMN
light-weight checks for optimizer statistics advisor MMON
mira CKPT channel CKPT
mount/dismount all db files DBW*|BW*
pdb event stats action GEN0
periodic PDB tasks GEN0
pmon dtp init PMON|CLMN
prespawn clean check GEN0
prespawn init check GEN0
prespawn timeout check GEN0
register to node local process group RBAL
shutdown RMON process RMON
sync PDB DBW0
threshold reloading MMON
unit test DBW0
Filed Under: Oracle

oracle如何判定统计信息陈旧

2017/02/17 by Leave a Comment

本文原始地址:https://www.askmac.cn/?p=18742

注意自动收集统计信息是从10g开始的，10g以前版本默认不自动收集统计信息。

对于自动收集统计信息而言需要知道统计信息是否陈旧stale ，判定陈旧的标准是对应的表上的数据修改超过10%（删除或插入或更新10%或以上数据行）。 这里oracle是如何知道修改超过10%的？

 

SGA的shared pool存有SQL的statistics情况，对应的有SQL处理的行数，SMON进程定期将这些信息刷到表SYS.MON_MODS$基表中（参考拙作： https://www.askmac.cn/archives/smon-flush-dml-statistics-mon-mods.html）：SMON后台进程会每15分钟将SGA中的DML统计信息刷新到SYS.MON_MODS$基表中(SMON flush every 15 minutes to SYS.MON_MODS$)，
同时会将SYS.MON_MODS$中符合要求的数据MERGE合并到MON_MODS_ALL$中，并清空原MON_MODS$中的数据。
MON_MODS_ALL$作为dba_tab_modifications视图的数据来源，起到辅助统计信息收集的作用,详见拙作<Does GATHER_STATS_JOB gather all objects’ stats every time?>。

这样基于之前的统计信息中的表的行数（dba_tables.num_rows），对比 MON_MODS_ALL$(dba_tab_modifications)中的update、delete、insert、truncate信息就可以知道该表从上一次收集统计信息到现在做了多少百分比的修改，若该百分比超过10%则判定为stale陈旧，否则为不陈旧。陈旧的统计信息会在自动收集统计信息时再次被收集。

 

 

Filed Under: Oracle

DOP degree of parallelism的设计算法 ​​​​

2017/02/15 by Leave a Comment

直击oracle内核代码算法，对于11g以后的自动Parallelism算法一般只能用10053 trace来研究其算法。这里通过直接查看oracle源码设计文档，我们可以得到DOP degree of parallelism的设计算法

 

8358fa4fgy1fcr7ivo81lj20zq0kijwk

Filed Under: Oracle

12CR2 vs 11gR2 新增optimizer 参数列表

2017/02/14 by Leave a Comment
_optimizer_adaptive_plan_control 0
_optimizer_adaptive_plans_continuous FALSE
_optimizer_adaptive_plans_iterative FALSE
_optimizer_adaptive_random_seed 0
_optimizer_ads_for_pq FALSE
_optimizer_ads_result_cache_life 3600
_optimizer_ads_spd_cache_owner_limit 64
_optimizer_ads_use_partial_results TRUE
_optimizer_ads_use_spd_cache TRUE
_optimizer_aggr_groupby_elim TRUE
_optimizer_ansi_join_lateral_enhance TRUE
_optimizer_ansi_rearchitecture TRUE
_optimizer_band_join_aware TRUE
_optimizer_batch_table_access_by_rowid TRUE
_optimizer_bushy_cost_factor 100
_optimizer_bushy_fact_dim_ratio 20
_optimizer_bushy_fact_min_size 100000
_optimizer_bushy_join off
_optimizer_cbqt_or_expansion ON
_optimizer_cluster_by_rowid TRUE
_optimizer_cluster_by_rowid_batch_size 100
_optimizer_cluster_by_rowid_batched TRUE
_optimizer_cluster_by_rowid_control 129
_optimizer_control_shard_qry_processing 65534
_optimizer_cube_join_enabled TRUE
_optimizer_db_blocks_buffers 0
_optimizer_dsdir_usage_control 0
_optimizer_eliminate_subquery TRUE
_optimizer_enable_plsql_stats TRUE
_optimizer_enhanced_join_elimination TRUE
_optimizer_gather_feedback TRUE
_optimizer_gather_stats_on_load TRUE
_optimizer_generate_ptf_implied_preds TRUE
_optimizer_generate_transitive_pred TRUE
_optimizer_hll_entry 4096
_optimizer_hybrid_fpwj_enabled TRUE
_optimizer_inmemory_access_path TRUE
_optimizer_inmemory_autodop TRUE
_optimizer_inmemory_bloom_filter TRUE
_optimizer_inmemory_capture_stored_stats TRUE
_optimizer_inmemory_cluster_aware_dop TRUE
_optimizer_inmemory_gen_pushable_preds TRUE
_optimizer_inmemory_minmax_pruning TRUE
_optimizer_inmemory_pruning_ratio_rows 100
_optimizer_inmemory_quotient 0
_optimizer_inmemory_table_expansion TRUE
_optimizer_inmemory_use_stored_stats AUTO
_optimizer_interleave_or_expansion TRUE
_optimizer_key_vector_aggr_factor 75
_optimizer_key_vector_pruning_enabled TRUE
_optimizer_multi_table_outerjoin TRUE
_optimizer_multicol_join_elimination TRUE
_optimizer_nlj_hj_adaptive_join TRUE
_optimizer_null_accepting_semijoin TRUE
_optimizer_partial_join_eval TRUE
_optimizer_performance_feedback OFF
_optimizer_proc_rate_level BASIC
_optimizer_proc_rate_source DEFAULT
_optimizer_reduce_groupby_key TRUE
_optimizer_strans_adaptive_pruning TRUE
_optimizer_synopsis_min_size 2
_optimizer_undo_cost_change 12.2.0.1
_optimizer_union_all_gsets TRUE
_optimizer_unnest_scalar_sq TRUE
_optimizer_use_feedback_for_join FALSE
_optimizer_use_gtt_session_stats TRUE
_optimizer_use_histograms TRUE
_optimizer_use_table_scanrate HADOOP_ONLY
_optimizer_use_xt_rowid TRUE
_optimizer_vector_base_dim_fact_factor 200
_optimizer_vector_cost_adj 100
_optimizer_vector_fact_dim_ratio 10
_optimizer_vector_min_fact_rows 10000000
_optimizer_vector_transformation TRUE
optimizer_adaptive_plans TRUE
optimizer_adaptive_reporting_only FALSE
optimizer_adaptive_statistics FALSE
optimizer_features_enable 12.2.0.1
optimizer_inmemory_aware TRUE
Filed Under: Oracle

沪ICP备14014813号-2

沪公网安备 31010802001379号