Applying 11G R2 GI PSU 11.2.0.2.3

2011/08/12 by 2 Comments

GI PSU 11.2.0.2.3在最近的一次CPU July中被释出，该Patch Set Update包含了最新的CPU，且GI和Database PSU的都包含在其中，可以直接从<Patch 12419353: GI PSU 11.2.0.2.3 (INCLUDES DATABASE PSU 11.2.0.2.3)>页面下载到，该PSU所修复的Bug包括:

CPU molecules in GI PSU 11.2.0.2.3:

GI PSU 11.2.0.2.3 contains the following new CPU 11.2.0.2 molecules:

12586486 - DB-11.2.0.2-MOLECULE-004-CPUJUL2011

12586487 - DB-11.2.0.2-MOLECULE-005-CPUJUL2011

12586488 - DB-11.2.0.2-MOLECULE-006-CPUJUL2011

12586489 - DB-11.2.0.2-MOLECULE-007-CPUJUL2011

12586490 - DB-11.2.0.2-MOLECULE-008-CPUJUL2011

12586491 - DB-11.2.0.2-MOLECULE-009-CPUJUL2011

12586492 - DB-11.2.0.2-MOLECULE-010-CPUJUL2011

12586493 - DB-11.2.0.2-MOLECULE-011-CPUJUL2011

12586494 - DB-11.2.0.2-MOLECULE-012-CPUJUL2011

12586495 - DB-11.2.0.2-MOLECULE-013-CPUJUL2011

12586496 - DB-11.2.0.2-MOLECULE-014-CPUJUL2011

5.2 Bugs Fixed in GI PSU 11.2.0.2.3
GI PSU 11.2.0.2.3 contains all fixes previously released in GI PSU 11.2.0.2.2
(see Section 5.3 for a list of these bug fixes) and the following new fixes:

Note:

ACFS is not supported on HP and therefore the bug fixes for ACFS do not apply to the HP GI PSU 3. 

Automatic Storage Management

6892311 - PROVIDE REASON FOR MOUNT FORCE FAILURE WITHOUT REQUIRING PST DUMP

9078442 - ORA-19762 FROM ASMCMD CP COPYING FILE WITH DIFFERENT BYTE ORDER FROM FILESYSTEM

9572787 - LONG WAITS FOR ENQ: AM CONTENTION FOLLOWING CELL CRASH CAUSED CLUSTERWIDE OUTAGE

9953542 - TB_SOL_SP: HIT 7445 [KFKLCLOSE()+20] ERROR WHEN DG OFFLINE

10040921 - HUNG DATABASE WORKLOAD AND BACKGROUNDS AFTER INDUCING WRITE ERRORS ON AVD VOLUME

10155605 - 11201-OCE:DISABLE FC IN ONE NODE, ASM DISKGOUP FORCE DISMOUNTED IN OTHER NODES.

10278372 - TB:X:CONSISTENTLY PRINT "WARNING: ELAPSED TIME DID NOT ADVANCE" IN ASM ALERT LOG

10310299 - TB:X:LOST WRITES DUE TO RESYNC MISSING EXTENTS WHEN DISK GO OFFLINE DURING REBAL

10324294 - DBMV2: DBFS INSTANCE WAITS MUCH FOR "ASM METADATA FILE OPERATION"

10356782 - DBMV2+: ASM INSTANCE CRASH WITH ORA-600 : [KFCGET0_04], [25],

10367188 - TB:X:REBOOT 2 CELL NODES,ASM FOREGROUND PROCESS HIT ORA-600[KFNSMASTERWAIT01]

10621169 - FORCE DISMOUNT IN ASM RECOVERY MAY DROP REDO'S AND CAUSE METADATA CORRUPTIONS

11065646 - ASM MAY PICK INCORRECT PST WHEN MULTIPLE COPIES EXTANT

11664719 - 11203_ASM_X64:ARB0 STUCK IN DG REBALANCE

11695285 - ORA-15081 I/O WRITE ERROR OCCURED AFTER CELL NODE FAILURE TEST

11707302 - FOUND CORRUPTED ASM FILES AFTER CELL NODES FAILURE TESTING.

11707699 - DATABASE CANNOT MOUNT DUE TO ORA-00214: CONTROL FILE INCONSISTENCY

11800170 - ASM IN KSV WAIT AFTER APPLICATION OF 11.2.0.2 GRID PSU

11800854 - BUG TO TRACK LRG 5135625

12620422 - FAILED TO ONLINE DISKS BECAUSE OF A POSSIBLE RACING RESYNC

Buffer Cache Management

11674485 - LOST DISK WRITE INCORRECTLY SIGNALLED IN STANDBY DATABASE WHEN APPLYING REDO

Generic

9748749 - ORA-7445 [KOXSS2GPAGE]

10082277 - EXCESSIVE ALLOCATION IN PCUR OF "KKSCSADDCHILDNO" CAUSES ORA-4031 ERRORS

10126094 - ORA-600 [KGLLOCKOWNERSLISTDELETE] OR [KGLLOCKOWNERSLISTAPPEND-OVF]

10142788 - APPS 11I PL/SQL NCOMP:ORA-04030: OUT OF PROCESS MEMORY

10258337 - UNUSABLE INDEX SEGMENT NOT REMOVED FOR "ALTER TABLE MOVE"

10378005 - EXPDP RAISES ORA-00600[KOLRARFC: INVALID LOB TYPE], EXP IS SUCCESSFUL

10636231 - HIGH VERSION COUNT FOR INSERT STATEMENTS WITH REASON INST_DRTLD_MISMATCH

12431716 - UNEXPECTED CHANGE IN MUTEX WAIT BEHAVIOUR IN 11.2.0.2.2 PSU (HIGHER CPU POSSIBLE

High Availability

9869401 - REDO TRANSPORT COMPRESSION (RTC) MESSAGES APPEARING IN ALERT LOG

10157249 - CATALOG UPGRADE TO 11.2.0.2 FAILS WITH ORA-1

10193846 - RMAN DUPLICATE FAILS WITH ORA-19755 WHEN BCT FILE OF PRIMARY IS NOT ACCESSIBLE

10648873 - SR11.2.0.3TXN_REGRESS - TRC - KCRFW_REDO_WRITE

11664046 - STBH: WRONG SEQUENCE NUMBER GENERATED AFTER DB SWITCHOVER FROM STBY TO PRIMARY

Oracle Portable ClusterWare

8906163 - PE: NETWORK AND VIP RESOURCES FAIL TO START IN SOLARIS CONTAINERS

9593552 - GIPCCONNECT() IS NOT ASYNC 11.2.0.2GIBTWO

9897335 - TB-ASM: UNNECCESSARY OCR OPERATION LOG MESSAGES IN ASM ALERT LOG WITH ASM OCR

9902536 - LNX64-11202-MESSAGE: EXCESSIVE GNS LOGGING IN CRS ALERT FILE WHEN SELFCHECK FAIL

9916145 - LX64: INTERNAL ERROR IN CRSD.LOG, MISROUTED REQUEST, ASSERT IN CLSM2M.CPP

9916435 - ROOTCRS.PL FAILS TO CREATE NODEAPPS DURING ADD NODE OPERATION

9939306 - SERVICES NOT COMING UP AFTER SWITCHOVER USING SRVCTL START DATABASE

10012319 - ORA-600 [KFDVF_CSS], [19], [542] ON STARTUP OF ASM DURING ADDNODE

10019726 - MEMORY LEAK 1.2MB/HR IN CRSD.BIN ON NON-N NODE

10056713 - LNX64-11202-CSS: SPLIT BRAIN WHEN START CRS STACK IN PARALLEL WITH PRIV NIC DOWN

10103954 - INTERMITTENT "CANNOT COMMUNICATE WITH CRSD DAEMON" ERRORS

10104377 - GIPC ENSURE INITIAL MESSAGE IS NOT LOST DURING ESTABLISH PHASE

10115514 - SOL-X64-11202: CLIENT REGISTER IN GLOBAL GROUP MASTER#DISKMON#GROUP#MX NOT EXIT

10190153 - HPI-SG-11202 ORA.CTSSD AND ORA.CRSD GOES OFFLINE AFTER KILL GIPC ON CRS MASTER

10231906 - 11202-OCE-SYMANTEC:DOWN ONE OF PRIVAE LINKS ON NODE 3,OCSSD CRASHED ON NODE 3

10233811 - AFTER PATCHING GRID HOME, UNABLE TO START RESOURCES DBFS AND GOLDEN

10253630 - TB:X:HANG DETECTED,"WAITING FOR INSTANCE RECOVERY OF GROUP 2" FOR 45 MINUTES

10272615 - TB:X:SHUTDOWN SERVICE CELLD ON 2 CELL NODES,CSSD ABORT IN CLSSNMRCFGMGRTHREAD

10280665 - TB:X:STOP CELLD ON 2 CELL NODES,CSSD ABORT IN CLSSNMVVERIFYPENDINGCONFIGVFS

10299006 - AFTER 11.2.0.2 UPGRADE, ORAAGENT.BIN CONNECTS TO DATABASE WITH TOO MANY SESSIONS

10322157 - 11202_GIBONE: PERM OF FILES UNDER $CH/CRS/SBS CHANGED AFTER PATCHED

10324594 - STATIC ENDPOINT IN THE LEASE BLOCKS OVERWRITTEN DURING UPGRADE

10331452 - SOL-11202-UD: 10205->11202 NETWORK RES USR_ORA_IF VALUE MISSED AFTER UPGRADE

10357258 - SOL-11202-UD: 10205->11202 [IPMP] HUNDREDS OF DUP IP AFTER INTRA-NODE FAILOVER

10361177 - LNX64-11203-GNS: MANY GNS SELF CHECK FAILURE ALERT MESSAGES

10385838 - TB:X:CSS CORE DUMP AT GIPCHAINTERNALSEND

10397652 - AIX-11202-GIPC:DISABLE SWITCH PORT FOR ONE PRIVATE NIC,HAIP DID NOT FAILOVER

10398810 - DOUBLE FREE IN SETUPWORK DUE TO TIMING

10419987 - PEER LISTENER IS ACCESSING A GROCK THAT IS ALREADY DELETED

10621175 - TB_RAC_X64:X: CLSSSCEXIT: CSSD SIGNAL 11 IN THREAD GMDEATHCHECK

10622973 - LOSS OF LEGACY FEATURES IN 11.2

10631693 - TB:X:CLSSNMHANDLEVFDISCOVERACK: NO PENDINGCONFIGURATION TO COMPLETE. CSS ABORT

10637483 - TB:X:REBOOT ONE CELL NODE, CSS ABORT AT CLSSNMVDDISCTHREAD

10637741 - HARD STOP DEPENDENCY CAN CAUSE WRONG FAIL-OVER ORDER

10638381 - 11202-OCE-SYMANTEC: HAIP FAIL TO START WHEN PRIVATE IP IS PLUMBED ON VIRTUAL NIC

11069614 - RDBMS INSTANCE CRASH DUE TO SLOW REAP OF GIPC MESSAGES ON CMT SYSTEMS

11071429 - PORT 11GR2 CRS TO EL6

11654726 - SCAN LISTENER STARTUP FAILS IF /VAR/OPT/ORACLE/LISTENER.ORA EXISTS.

11663339 - DBMV2:SHARED PROCESS SPINNING CAUSES DELAY IN PRIMARY MEMBER CLEANUP

11682409 - RE-USING OCI MEMORY ACROSS CONNECTIONS CAUSES A MEMORY CORRUPTION

11698552 - SRVCTL REPORT WRONG STATUS FOR DATABASE INSTANCE.

11741224 - INCORRECT ACTIVE VERSION CHECK WHILE ENABLING THE BATCH FUNCTIONALITY

11744313 - LNX64-11203-RACG: UNEXPECTED CRSD RESTART DURING PARALLEL STACK START

11775080 - ORA-29701/29702 OCCURS WHEN WORKLOAD TEST RUNNING FOR A LONG TIME AND IS RESTART

11781515 - EVMD/CRSD FAIL TO START AFTER REBOOT, EVEN AFTER CRSCTL START CLUSTERWARE

11807012 - LNX64-11203-RACG: DB SERVICE RUNS INTO "UNKNOWN" STATE AFTER STACK START

11812615 - LNX64-11203-DIT: INCONSISTENT PERMISSION BEFORE/AFTER ROOTCRS.PL -UNLOCK/-PATCH

11828633 - DATABASE SERVICE DID NOT FAIL OVER AND COULD NOT BE STARTED AFTER NODE FAILURE

11840629 - KERNEL CRASH DUMP AND REBOOT FAIL INSIDE SOLARIS CONTAINER

11866171 - ENABLE CRASHDUMP WHEN REBOOTING THE MACHINE (LINUX)

11877079 - HUNDREDS OF ORAAGENT.BIN@HOSTNAME SESSSIONS IN 11.2.0.2 DATABASE

11899801 - 11202_GIBTWO_HPI:AFTER KILL ASM PMON, POLICY AND ADMIN DB RUNNING ON SAME SERVER

11904778 - LNX64-OEL6-11202: CRS STACK CAN'T BE START AFTER RESTART

11933693 - 11.1.0.7 DATABASE INSTANCE TERMINATED BY 11.2.0.2 CRS AGENT

11936945 - CVU NOT RECOGNIZING THE OEL6 ON LINUX

12332919 - ORAAGENT KEEPS EXITING

12340501 - SRVCTL SHOWS INSTANCE AS DOWN AFTER RELOCATION

12340700 - EVMD CONF FILES CAN HAVE WRONG PERMISSIONS AFTER INSTALL

12349848 - LNX64-11203: VIPS FELL OFFLINE WHEN BRING DOWN 3/4 PUBLIC NICS ONE BY ONE

12378938 - THE LISTENER STOPS WHEN THE ORA.NET1.NETWORK'S STATE IS CHANGED TO UNKNOWN

12380213 - 11203_110415:ERROR EXCEPTION WHILE INSTALLATION 11202 DB WITH DATAFILES ON 11203

12399977 - TYPO IN SUB PERFORM_START_SERVICE RETURNS ZERO (SUCCESS) EVEN WHEN FAILED

12677816 - SCAN LISTENER FAILD TO STARTUP IF /VAR/OPT/ORACLE/LISTENER.ORA EXIST

Oracle Space Management

8223165 - ORA-00600 [KTSXTFFS2] AFTER DATABASE STARTUP

9443361 - WRONG RESULTS (ROWDATA) FOR SELECT IN SERIAL FROM COMPRESSED TABLE

10061015 - LNX64-11202:HIT MANY ORA-600 ARGUMENTS: [KTFBHGET:CLSVIOL_KCBGCUR_9] DURING DBCA

10132870 - INDEX BLOCK CORRUPTION - ORA-600 [KCBZPBUF_2], [6401] ON RECOVER

10324526 - ORA-600 [KDDUMMY_BLKCHK] [6106] WHEN UPDATE SUBPARTITION OF TABLE IN TTS

Oracle Transaction Management

10053725 - TS11.2.0.3V3 - TRC - K2GUPDATEGLOBALPREPARECOUNT

10233732 - ORA-600 [K2GUGPC: PTCNT >= TCNT] OCCURS IN A DATABASE LINK TRANSACTION

Oracle Universal Storage Management

9867867 - SUSE10-LNX64-11202:NODE REBOOT HANG WHILE ORACLE_HOME LOCATED ON ACFS

9936659 - LNX64-11202-CRS: ORACLE HOME PUT ON ACFS, DB INST FAILS TO RESTART AFTER CRASH

9942881 - TIGHTEN UP KILL SEMANTICS FOR 'CLEAN' ACTION.

10113899 - AIX KSPRINTTOBUFFER TIMESTAMPS NEEDS TIME SINCE BOOT AND WALL_CLOCK TIMES

10266447 - ROOTUPGRADE.SH FAILS: 'FATAL: MODULE ORACLEOKS NOT FOUND' , ACFS-9121, ACFS-9310

11789566 - ACFS RECOVERY PHASE 2

11804097 - GBM LOCK TAKEN WHEN DETERMINING WHETHER THE FILE SYSTEM IS MOUNTED AND ONLINE

11846686 - ACFSROOT FAILS ON ORACLELINUX-RELEASE-5-6.0.1 RUNNUNG A 2.6.18 KERNEL

12318560 - ALLOW IOS TO RESTART WHEN WRITE ERROR MESG RETURNS SUCCESS

12326246 - ASM TO RETURN DIFF VALUES WHEN OFFLINE MESG UNSUCCESSFUL

12378675 - AIX-11203-HA-ACFS: HIT INVALID ASM BLOCK HEADER WHEN CONFIGURE DG USING AIX LVS

12398567 - ACFS FILE SYSTEM NOT ACCESSIBLE

12545060 - CHOWN OR RM CMD TO LOST+FOUND DIR IN ACFS FAILS ON LINUX

Oracle Utilities

9735282 - GETTING ORA-31693, ORA-2354, ORA-1426 WHEN IMPORTING PARTITIONED TABLE

Oracle Virtual Operating System Services

10317487 - RMAN CONTROLFILE BACKUP FAILS WITH ODM ERROR ORA-17500 AND ORA-245

11651810 - STBH: HIGH HARD PARSING DUE TO FILEOPENBLOCK EATING UP SHARED POOL MEMORY

XML Database

10368698 - PERF ISSUE WITH UPDATE RESOURCE_VIEW DURING AND AFTER UPGRADING TO 11.2.0.2

5.3 Bugs Fixed in GI PSU 11.2.0.2.2
This section describes bugs fixed in the GI PSU 11.2.0.2.2 release.

ACFS

10015603 - KERNEL PANIC IN OKS DRIVER WHEN SHUTDOWING CRS STACK

10178670 - ACFS VOLUMES ARE NOT MOUNTING ONCE RESTARTED THE SERVER

10019796 - FAIL TO GET ENCRYPTION STATUS OF FILES UNTIL DOING ENCR OP FIRST

10029794 - THE DIR CAN'T READ EVEN IF THE DIR IS NOT IN ANY REALM

10056808 - MOUNT ACFS FS FAILED WHEN FS IS FULL

10061534 - DB INSTANCE TERMINATED DUE TO ORA-445 WHEN START INSTANCE ON ALL NODES

10069698 - THE EXISTING FILE COULD CORRUPT IF INPUT INCORRECT PKCS PASSOWRD

10070563 - MULTIPLE WRITES TO THE SAME BLOCK WITH REPLICATION ON CAN GO OUT OF ORDER

10087118 - UNMOUNT PANICS IF ANOTHER USER IS SITTING IN A SNAPSHOT ROOT DIRECTORY

10216878 - REPLI-RELATED RESOURCE FAILED TO FAILOVER WHEN DG DISMOUNTED

10228079 - MOUTING DG ORA-15196 [KFC.C:25316] [ENDIAN_KFBH] AFTER NODE REBOOT

10241696 - FAILED TO MOUNT ACFS FS TO DIRECTORY CREATED ON ANOTHER ACFS FS

10252497 - ADVM/ACFS FAILS TO INSTALL ON SLES10

9861790 - LX64: ADVM DRIVERS HANGING OS DURING ACFS START ATTEMPTS

9906432 - KERNEL PANIC WHILE DISMOUNT ACFS DG FORCE

9975343 - FAIL TO PREPARE SECURITY IF SET ENCRYPTION FIRST ON THE OTHER NODE

10283549 - FIX AIX PANIC AND REMOVE -DAIX_PERF

10283596 - ACFS:KERNEL PANIC DURING USM LABEL PATCHING - ON AIX

10326548 - WRITE-PROTETED ACFS FILES SHOULD NOT BE DELETED BY NON-ROOT USER

ADVM

10045316 - RAC DB INSTALL ON SHARED ACFS HANGS AT LINKING PHASE

10283167 - ASM INSTANCE CANNOT STARTUP DUE TO EXISTENCE OF VMBX PROCESS

10268642 - NODE PANIC FOR BAD TRAP IN "ORACLEADVM" FOR NULL POINTER

10150020 - LINUX HANGS IN ADVM MIRROR RECOVERY, AFTER ASM EVICTIONS

Automatic Storage Management

9788588 - STALENESS REGISTRY MAY GET CLEARED PREMATURELY

10022980 - DISK NOT EXPELLED WHEN COMPACT DISABLED

10040531 - ORA-600 [KFRHTADD01] TRYING TO MOUNT RECO DISKGROUP

10209232 - STBH: DB STUCK WITH A STALE EXTENT MAP AND RESULTS IN DATA CORRUPTIONS

10073683 - ORA-600 [KFCBINITSLOT40] ON ASM ON DBMV2 WITH BP5

9715581 - DBMV2: EXADATA AUTO MANAGEMENT FAILED TO BRING UP DISKS ONLINE

10019218 - ASM DROPPED DISKS BEFORE DISK_REPAIR_TIME EXPIRED

10084145 - DBMV2: ORA-600 [1427] MOUNTING DISKGROUP AFTER ALL CELLS RESTARTED

11067567 - KEPT GENERATING "ELAPSED TIME DID NOT ADVANCE " IN ASM ALERT LOG

10356513 - DISK OFFLINED WITH NON ZERO TIMEOUT EXPELLED IMMEDIATELY

10332589 - TB:X:MOUNT NORMAL REDUNDANCY DG, FAILED WITH ORA-00600:[KFCINITRQ20]

10329146 - MARKING DIFFERENT SR BITS FROM MULTIPLE DBWS CAN CAUSE A LOST WRITE

10299224 - TB:X:PIVOTING AN EXTENT ON AN OFFLINE DISK CAN CAUSE STALE XMAPS IN RDBMS

10245086 - ORA-01210 DURING CREATE TABLESPACE

10230571 - TB:X:REBOOT ONE CELL NODE, RBAL HIT ORA-600[17183]

10228151 - ASM DISKGROUPS NOT GETTING MOUNTED

10227288 - DG FORCIBLY DISMOUNTED AFTER ONE FG LOST DUE TO "COULD NOT READ PST FOR GRP 4"

10222719 - ASM INSTANCE HANGS WITH RBAL PROCESS WAITS ON "NO FREE BUFFER"

10102506 - DISK RESYNC TAKES A LONG TIME EVEN WITH NO STALE EXTENTS

10094201 - DISK OFFLINE IS SLOW

10190642 - ORA-00600: [1433] FOLLOWED BY INSTANCE CRASH WITH ASM ON EXADATA

11067567 - 11202_gibtwo: kept generating "elapsed time did not advance " in asm alert log

Buffer Cache Management

9651350 - ora-00308 and ora-27037 when ora-8103 without event 10736 been set

10110863 - trace files is still generated after applying patch:9651350

10205230 - tb_x64: hit ora-00600: [kclwcrs_6]

10332111 - sql running long in active standby

CRS Group

CLEANUP

9949676 - GNSD.BIN CORE DUMP AFTER KILL ASM PMON ON ALL NODES AT SAME TIME

9975837 - GNS INCORRECTLY PROCESSES IPV6 LOOKUP REQUESTS

10007185 - GNS DUMPS CORE IN CLSKGOPANIC AT CLSKPDVA 717

10028343 - GNS CAN NOT BE RELOCATED AFTER PUBLIC RESTARTED

CRS

9876201 - OHASD AGENT CORE DUMP AT EONSHTTP.C:162

10011084 - 11202 STEP3 MODIFY BINARY AFTER INSTALLATION CANNOT EXCUTE SUCCESSFULLY

10028235 - 'CLSNVIPAGENT.CPP', LINE 1522: ERROR: FORMAL ARGUMENT TYPE OF ...

10045436 - 'ORA.LISTENER.LSNR' FAILED TO BE FENCED OFF DURING CRSD CLEANUP

10062301 - VALUE FOR FIELD 'CLUSTER_NAME' IS MISSING IN CRSCONFIG_PARAMS

10110969 - PORTABILITY ISSUES IN FUNCTION TOLOWER_HOST

10175855 - FAILED TO UGPRADE 11.2.0.1 + ARU 12900951 -> 11.2.0.2

9891341 - CRSD CORE DUMP IN PROATH_MASTER_EXIT_HELPER AT PROATH.C:1834

11655840 - RAC1 DB' STATE_DETAILS IS WRONG AFTER KILL GIPCD

10634513 - OHASD DUMPS CORE WHEN PLUG IN UNPLUGGED PRIVATE NETWORK NIC

10236074 - ASM INSTANCES CRASH SEVERAL TIMES DURING PARALLEL CRS STARTUP

10052529 - DB INST OFFLINE AFTER STOP/START CRS STACK ON ALL NODES IN PARALLEL

10065216 - VIRTUAL MEMORY USAGE OF ORAROOTAGENT IS BIG(1321MB) AND NOT DECREASING

10168006 - ORAAGENT PROCESS MEMORY GROWTH PERIODICALLY.

CSS

9907089 - CSS CORE DUMP DURING EXADATA ROLLING UPGRADE

9926027 - NODE REBOOTED AFTER CRS CLEAN-UP SUCCEEDED 11202 GI + 10205 RAC DB

10014392 - CRSCTL DELETE NODE FAILS WITH CRS-4662 & CRS-4000

10015460 - REMOVAL OF WRONG INCARNATION OF A NODE DUE TO MANUAL SHUTDOWN STATE

10040109 - PMON KILL LEAD TO OS REBOOT

10048027 - ASM UPGRADE FAILS

10052721 - 11201- 11202 NON-ROLLING,CRSCTL.BIN CORE AT CLSSNSQANUM, SIGNAL 11

10083789 - A NODE DOESNT INITIATE A RECONFIG DUE TO INCORRECT RECONFIG STATE

9944978 - FALSE CSS EVICTION AFTER PRIVATE NIC RESUME

9978195 - STOP DB ACTION TIMED OUT AND AGENT EXITS DUE TO FAILURE TO STOP EVENT BRIDGE

10248739 - AFTER APPLY THE PATCH, THE NODE EVICTED DURING START CRS STACK

CVU

9679401 - OUI PREREQ CHECKS FAILED FOR WRONG OWNSHIP OF RESOLV.CONF_`HOST`

9959110 - GNS INTEGRITY PREREQUISITE FAILED WITH PRVF-5213

9979706 - COMP OCR CHECK FAILS TO VERIFY SIZE OF OCR LOCATION

10029900 - CVU PRE NODEADD CHECK VD ERROR

10033106 - ADDNODE.SH SHOULD INDICATE WHAT HAPPENS WHEN ERROR OCCURRING

10075643 - UNABLE TO CONTINUE CONFIG.SH FOR CRS UPGRAD

10083009 - GIPCD FAILS TO RETRIEVE INFORMATION FROM PEERS DUE TO INVALID ENDPOINT

GIPC

9812956 - STATUS OF CRSD AND EVMD GOES INTERMEDIATE FOR EVER WHEN KILL GIPC

9915329 - ORA-600 [603] IN DB AND ORA-603 IN ASM AFTER DOWN INTER-CONNECT NIC

9944948 - START RESOUCE HAIP FAILED WHEN RUN ROOT.SH

9971646 - ORAROOTAGENT CORE DUMPED AT NETWORKHAMAINTHREAD::READROUTEDATA

9974223 - GRID INFRASTRUCTURE NEEDS MULTICAST COMMUNICATION ON 230.0.1.0 ADDRESSES WORKING

10053985 - ERROR IN NETWORK ADDRESS ON SOLARIS 11

10057680 - OHASD ORAROOTAGENT.BIN SPIN CPU AFTER SIMULATE ASM DISK ERROR

10078086 - ROOTUPGRADE.SH FAIL FOR 'CRSCTL STARTUPGRADE' FAIL,10205-> 11202

10260251 - GRID INSTALLATION FAILS TO START HAIP DUE TO CHANGE IN NETWORK INTERFACE NAME

10111010 - CRSD HANGS FOR THE HANAME OF PEER CRSD

11782423 - OHASD.BIN TAKES CPU ABOUT 95% ~ 100%

11077756 - STARTUP FAILURE OF HAIP CAUSES INSTALLATION FAILURE

10375649 - DISABLE HAIP ON PRIMECLUSTER

10284828 - INTERFACE UPDATES GET LOST DURING BOUNCE OF CRSD PROCESS

10284693 - AIX EPIPE FAILURE

10233159 - NEED 20 MINS TO STARTUP CRS WHEN 1/2 GIPC NICS DOWN

10128191 - LRGSRG9 AND LRGSRGE FAILURE

GNS

9864003 - NODE REBOOT DUE TO 'ORA.GNS' FAILED TO BE FENCED OFF DURING CRSD

GPNP

9336825 - GPNPD FLUSH PROFILE PUSH ERROR MESSAGES IN CRS ALERT LOG

10314123 - GPNPD MAY NOT UPDATE PROFILE TO LATEST ON START

10105195 - PROC-32 ACCESSING OCR; CRS DOES NOT COME UP ON NODE

10205290 - DBCA FAILED WITH ERROR ORA-00132

10376847 - [ORA.CRF] [START] ERROR = ERROR 9 ENCOUNTERED WHEN CONNECTING TO MOND

IPD-OS

9812970 - IPD DO NOT MARK TYPE OF DISKS USED FOR VOTING DISK CORRECTLY

10057296 - IPD SPLIT BRAIN AFTER CHANGE BDB LOCATION

10069541 - IPD SPLIT BRAIN AFTER STOPPING ORA.CRF ON MASTER NODE

10071992 - UNREASONABLE VALUES FOR DISK STATISTICS

10072474 - A NODE IS NOT MONITORED AFTER STOP AND START THE ORA.CRF ON IT

10073075 - INVALID DATA RECEIVED FROM THE CLUSTER LOGGER SERVI

10107380 - IPD NOT STARTED DUE TO SCRFOSM_GET_IDS FAILED

OCR

9978765 - ROOTUPGRADE.SH HANG AND CRSD CRASHED ON OTHER NODES,10205-> 11202

10016083 - 'OCRCONFIG -ADD' NEEDS HELPFUL MESSAGE FOR ERROR ORA-15221

OPSM

9918485 - EMCONFIG FAIL WITH NULLPOINTEREXCEPTION AT RACTRANSFERCORE.JAVA

10018215 - RACONE DOES NOT SHUTDOWN INSTANCE DURING RELOCATION

10042143 - ORECORE11 LWSFDSEV CAUSED SEGV IN SRVM NATIVE METHODS

OTHERS

9963327 - CHMOD.PL GETS CALLED INSTEAD OF CHMOD.EXE

10008467 - FAILS DUE TO WRONG VERSION OF PERL USED:

10015210 - OCTSSD LEAK MEMORY 1.7M HR ON PE MASTER DURING 23 HOURS RUNNI

10027079 - CRS_SHUTDOWN_SYNCH EVENT NOT SENT IN SIHA

10028637 - SCLS.C COMPILE ERRORS ON AIX UNDECLARED IDENTIFIERS

10029119 - 11201-11202 CRS UPGRADE OUI ASKS TO RUN ROOTUPGRADE.SH

10036834 - PATCHES NOT FOUND ERROR WHILE UPGRADING GRID FROM 11201 TO 11202

10038791 - HAS SRG SRV GETTING MANY DIFS FOR AIX ON LABEL 100810 AND LATER

10040647 - LNX64-112022-UD; AQ AND RLB DO NOT WORK AFTER UPGRADING FROM 11201

10044622 - EVMD FAILED TO START AFTER KILL OHASD.BIN

10048487 - DIAGCOLLECTION CANNOT RETRIEVE IPD REPORTS

10073372 - DEINSTALL FAILED TO DELETE CRS_HOME ON REMOTE NODE IF OCR VD ON NFS

10089120 - WRONG PROMPT MESSAGE BY DEINSTALL COMMAND WHILE DELETING CRS HOME

10124517 - CRS STACK DOES NOT START AUTOMATICALLY AFTER NODE REBOOT

10157622 - 11.2.0.2 GI BUNDLE 1 HAS-CRS TRACKING BUG

RACG

10036193 - STANDBY NIC DOESN'T WORK IF DOWN PUBLIC NIC

10146768 - NETWORK RESOURCE FAILS TO START WITH IPMP ON SOLARIS 11

USM Miscellaneous

10146744 - ORA.REGISTRY.ACFS BECOME UNKOWN AND ACFS FS DISMOUNT

10283058 - RESOURCES ACFS NEEDS AN OPTION TO DISALLOW THE MOUNTING OF FILE SYSTEMS ON RESOURCE START

10193581 - ROOT.SH CRS-2674: START OF 'ORA.REGISTRY.ACFS' FAIL

10244210 - FAIL TO INSTALL ADVM/ACFS ON SOLARIS CONTAINER

10311856 - APPLY ASSERTION FAILURE:PBOARDENTRY>USRGBOARDRECENTRY_RECORD

Generic

9591812 - incorrect wait events in 11.2 ("cursor: mutex s" instead of "cursor: mutex x")

9905049 - ebr: ora-00600: internal error code, arguments: [kqlhdlod-bad-base-objn]

10052141 - exadata database crash with ora-7445 [_wordcopy_bwd_dest_aligned] and ora-600 [2

10052956 - ora-7445 [kjtdq()+176]

10157402 - lob segment has null data after long to lob conversion in parallel mode

10187168 - obsolete parent cursors if version count exceeds a threshold

10217802 - alter user rename raises ora-4030

10229719 - qrmp:12.2:ora-07445 while performing complete database import on solaris sparc

10264680 - incorrect version_number reported after patch for 10187168 applied

10411618 - add different wait schemes for mutex waits

11069199 - ora-600 [kksobsoletecursor:invalid stub] quering pq when pq is disabled

11818335 - additional changes when wait schemes for mutex waits is disabled

High Availability

10018789 - dbmv2-bigbh:spin in kgllock caused db hung and high library cache lock

10129643 - appsst gsi11g m9000: ksim generic wait event

10170431 - ctwr consuming lots of cpu cycles

Oracle Space Management

6523037 - et11.1dl: ora-600 [kddummy_blkchk] [6110] on update

9724970 - pdml fails with ora-600 [4511]. ora-600 [kdblkcheckerror] by block check

10218814 - dbmv2: ora-00600:[3020] data block corruption on standby

10219576 - ora-600 [ktsl_allocate_disp-fragment]

Oracle Transaction Management

10358019 - invalid results from flashback_transaction_query after applying patch:10322043

Oracle Utilities

10373381 - ora-600 [kkpo_rcinfo_defstg:objnotfound] after rerunning catupgrd.sql

Oracle Virtual Operating System Services

10127360 - dg4msql size increasing to 1.5gb after procedure executed 250 times

Server Manageability

11699057 - ora-00001: unique constraint (sys.wri$_sqlset_plans_tocap_pk) violated

针对Grid Infrastructure的大致升级步骤如下，具体见该PSU的readme文档，见(6 Appendix A: Manual Steps for Apply/Rollback Patch)
部分：

1.升级Opatch，Patch 12419353 requires OPatch version 11.2.0.1.4.
可以从这里下载到11.2上最新的opatch工具，解压后直接覆盖原Opatch目录即可

2.关闭相关的RAC数据库  

<ORACLE_HOME>/bin/srvctl stop database –d <db-unique-name> 

3.Umount所有的ACFS文件系统

4. 切换到oracle用户,停止所有节点上的相关资源
su  - oracle
$<ORACLE_HOME>/bin/srvctl stop home -o <ORACLE_HOME> -s <status file location> -n <node name>

5.在所有节点上运行rootcrs.pl脚本停止GI相关资源
su - root
<GI_HOME>/crs/install/rootcrs.pl -unlock
Using configuration parameter file: /g01/11.2.0/grid/crs/install/crsconfig_params
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'vrh1'
CRS-2673: Attempting to stop 'ora.crsd' on 'vrh1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'vrh1'
CRS-2673: Attempting to stop 'ora.asm' on 'vrh1'
CRS-2677: Stop of 'ora.asm' on 'vrh1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'vrh1' has completed
CRS-2677: Stop of 'ora.crsd' on 'vrh1' succeeded
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'vrh1'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'vrh1'
CRS-2673: Attempting to stop 'ora.ctssd' on 'vrh1'
CRS-2673: Attempting to stop 'ora.evmd' on 'vrh1'
CRS-2673: Attempting to stop 'ora.asm' on 'vrh1'
CRS-2677: Stop of 'ora.asm' on 'vrh1' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'vrh1'
CRS-2677: Stop of 'ora.evmd' on 'vrh1' succeeded
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'vrh1' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'vrh1' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'vrh1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'vrh1'
CRS-2677: Stop of 'ora.cssd' on 'vrh1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'vrh1'
CRS-2673: Attempting to stop 'ora.diskmon' on 'vrh1'
CRS-2677: Stop of 'ora.diskmon' on 'vrh1' succeeded
CRS-2677: Stop of 'ora.gipcd' on 'vrh1' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'vrh1'
CRS-2677: Stop of 'ora.drivers.acfs' on 'vrh1' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'vrh1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'vrh1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
Successfully unlock /g01/11.2.0/grid

6.解压下载的PSU补丁包
unzip p12419353_112020_Linux-x86-64.zip 

7.
在所有节点上实施对GI的Patch

 <GI_HOME>/OPatch/opatch napply -oh <GI_HOME> -local /tmp/12419353

Invoking OPatch 11.2.0.1.6

Oracle Interim Patch Installer version 11.2.0.1.6
Copyright (c) 2011, Oracle Corporation.  All rights reserved.

UTIL session

Oracle Home       : /g01/11.2.0/grid
Central Inventory : /g01/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.6
OUI version       : 11.2.0.2.0
Log file location : /g01/11.2.0/grid/cfgtoollogs/opatch/opatch2011-08-12_00-07-39AM.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   12419353  

Do you want to proceed? [y|n]

Backing up files...
Applying interim patch '12419353' to OH '/g01/11.2.0/grid'

Patching component oracle.crs, 11.2.0.2.0...
Copying file to "/g01/11.2.0/grid/crs/install/crsconfig_lib.pm"
Copying file to "/g01/11.2.0/grid/crs/install/crspatch.pm"
Copying file to "/g01/11.2.0/grid/crs/install/s_crsconfig_lib.pm"

Patching component oracle.usm, 11.2.0.2.0...
Patches 12419353 successfully applied.
Log file location: /g01/11.2.0/grid/cfgtoollogs/opatch/opatch2011-08-12_00-07-39AM.log

OPatch succeeded.

<GI_HOME>/OPatch/opatch napply -oh <GI_HOME> -local /tmp/12419331

Invoking OPatch 11.2.0.1.6

Oracle Interim Patch Installer version 11.2.0.1.6
Copyright (c) 2011, Oracle Corporation.  All rights reserved.

UTIL session

Oracle Home       : /g01/11.2.0/grid
Central Inventory : /g01/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.6
OUI version       : 11.2.0.2.0
Log file location : /g01/11.2.0/grid/cfgtoollogs/opatch/opatch2011-08-12_00-10-46AM.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   12419331  

Backing up files...
Applying interim patch '12419331' to OH '/g01/11.2.0/grid'
ApplySession: Optional component(s) [ oracle.sysman.console.db, 11.2.0.2.0 ] ,
[ oracle.sysman.oms.core, 10.2.0.4.3 ] , [ oracle.rdbms.dv, 11.2.0.2.0 ] ,
[ oracle.sysman.plugin.db.main.repository, 11.2.0.2.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms.rsf, 11.2.0.2.0...

Patching component oracle.rdbms, 11.2.0.2.0...
Copying file to "/g01/11.2.0/grid/psu/11.2.0.2.3/catpsu.sql"
Copying file to "/g01/11.2.0/grid/psu/11.2.0.2.3/catpsu_rollback.sql"
Copying file to "/g01/11.2.0/grid/cpu/scripts/patch_8837510.sql"
Copying file to "/g01/11.2.0/grid/cpu/scripts/emdb_recomp_invalids.sql"

Patching component oracle.ldap.rsf, 11.2.0.2.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.2.0...

Patching component oracle.rdbms.rman, 11.2.0.2.0...
Patches 12419331 successfully applied.
Log file location: /g01/11.2.0/grid/cfgtoollogs/opatch/opatch2011-08-12_00-10-46AM.log

OPatch succeeded.

后续对DB HOME升级的步骤可以参照以下文档摘录 

8.Run the pre script for DB component of the patch.

As the database home owner execute:

$<UNZIPPED_PATCH_LOCATION>/12419353/custom/server/12419353/custom/scripts/prepatch.sh -dbhome <ORACLE_HOME>

9.Apply the DB patch.

As the database home owner execute:

$<ORACLE_HOME>/OPatch/opatch napply -oh <ORACLE_HOME> -local <UNZIPPED_PATCH_LOCATION>/12419353/custom/server/12419353
$<ORACLE_HOME>/OPatch/opatch napply -oh <ORACLE_HOME> -local <UNZIPPED_PATCH_LOCATION>/12419331

10.Run the post script for DB component of the patch.

As the database home owner execute:

$<UNZIPPED_PATCH_LOCATION>/12419353/custom/server/12419353/custom/scripts/postpatch.sh -dbhome <ORACLE_HOME>

11.Run the post script.

As the root user execute:

#<GI_HOME>/rdbms/install/rootadd_rdbms.sh
If this is a GI Home, as the root user execute:

#<GI_HOME>/crs/install/rootcrs.pl -patch
If this is an Oracle Restart Home, as the root user execute:

#<GI_HOME>/crs/install/roothas.pl -patch

12.Start the CRS managed resources that were earlier running from DB homes.

If this is a GI Home environment, as the database home owner execute:

$<ORACLE_HOME>/bin/srvctl start home -o <ORACLE_HOME> -s <status file location> -n <node name>
If this is an Oracle Restart Home environment, as the database home owner execute:

$<ORACLE_HOME>/bin/srvctl start home -o <ORACLE_HOME> -s <status file location>
Filed Under: Oracle, Oracle 11g新特性, Oracle补丁patch Tagged With: , , ,

CRS-4258: Addition and deletion of voting files are not allowed because the voting files are on ASM

2011/08/04 by 2 Comments

客户的一套11.2.0.1 RAC系统采用ASM diskgroup 存放ocr和votedisk，该REG diskgroup中的某个LUN disk由于硬件的原因损坏了，导致冗余的votedisk表决磁盘有一个处于OFFLINE状态，客户希望能删除该OFFLINE的votedisk并新增一个可用的。

在删除该votedisk文件时出现了CRS-4258的错误，错误如下:

crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. OFFLINE  5b3380d6367e4f94bf19e9db5f2f684e ()  []
 2. ONLINE   6802e6d139354fb3bf95725dd01a02fd (/dev/ocr2) [REG]
 3. ONLINE   a433d51ebd2d4facbfc8e95b017f5393 (/dev/asm-disk1) [REG]
 4. ONLINE   3784d344bffa4f6ebff21c4dd3c873bd (/dev/asm-disk2) [REG]
Located 4 voting disk(s).

crsctl delete css votedisk 5b3380d6367e4f94bf19e9db5f2f684e
CRS-4258: Addition and deletion of voting files are not allowed because the voting files are on ASM

居然无法移除ASM存储上的voting files，太搞笑了。

客户在MOS上找到了CRS-4258相关问题的Note:

CRS-4258: Addition and Deletion of Voting Files are not Allowed Because the Voting Files are on ASM in 11gR2 [ID 1060146.1]
Applies to:
Oracle Server - Enterprise Edition - Version: 11.2.0.1 to 11.2.0.1 - Release: 11.2 to 11.2
Information in this document applies to any platform.
Symptoms

CRS-4258: Addition and deletion of voting files are not allowed because the voting files are on ASM in 11gR2.


Changes
Stale voting files are seen after accidently dropping one of ASM disks belonging to the ASM diskgroup where voting files are stored.
And CRS-4258 occurs when trying to delete the stale voting files using crsctl delete css votedisk FUID.

[root@grid]# crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 5b91aad0a2184f3dbfa8f970e8ae4d49 (/dev/oracleasm/disks/ASM10) [PLAY]
2. ONLINE 53b1b40b73164f9ebf3f498f6d460187 (/dev/oracleasm/disks/ASM9) [PLAY]
3. OFFLINE 82dfd04b96f14f6dbf36f5a62b118f61 () []

[root@grid]# crsctl delete css votedisk 82dfd04b96f14f6dbf36f5a62b118f61
CRS-4258: Addition and deletion of voting files are not allowed because the voting files are on ASM
Cause

1. Seeing stale voting files is due to bug 9024611.

2. "delete" command is not available , only "replace" command is available when voting files are stored on  ASM diskgroup.    

    Please see Oracle Clusterware Administration and Deployment Guide11g Release 2 (11.2)

Solution


1. This issue is permanently fixed in 11.2.0.2.0.

2. Apply patch 9024611. Please contact Oracle support if this patch is not available on your platform.

3. If CSS has stale voting files even after applying patch 9024611, do the following workaround -

WORKAROUND:
Do something to trigger ASM to try to relocate the voting file.

e.g)  $ crsctl replace votedisk  +asm_disk_group   --- Put available ASM diskgroup

        $ crsctl query css votedisk         --- Check if voting files are all online on the new ASM diskgroup
        $ crsctl replace votedisk +PLAY    -- Put the original ASM diskgroup where voting files were 

4. If the workaround above cannot be followed for any reason then you can request the patch for unpublished bug 9409327 for your platform.

References
BUG:9294664 - NOT ABLE TO REMOVE THE VOTEDISK WHICH IS OFFILNE

Hdr: 9294664 11.2.0.1 PCW 11.2.0.1 ADMUTL PRODID-5 PORTID-226 9024611
Abstract: NOT ABLE TO REMOVE THE VOTEDISK WHICH IS OFFILNE

PROBLEM:
--------
crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
1. ONLINE   9f7f4f7f798d4f69bfe31653894421a2 (ORCL:GRID1) [GRID]
2. OFFLINE  a9b785a59c3c4f67bf15babc67ffb79a () []
3. OFFLINE  29988f37fa794f12bfea3f3672c99609 () []
4. ONLINE   a8b3a040195c4f54bfce8ef21bd4fa07 (ORCL:GRID3) [GRID]
5. ONLINE   a1e4fbd9df6f4f67bf8fc12fe9780721 (ORCL:GRID2) [GRID]
Located 5 voting disk(s).


[root@sdc-drrac01 grid]# crsctl delete css votedisk 
a9b785a59c3c4f67bf15babc67ffb79a
CRS-4258: Addition and deletion of voting files are not allowed because the 
voting files are on ASM

DIAGNOSTIC ANALYSIS:
--------------------
Ct is performing some voting disk failover scenarios in which he has removed 
the 2 votedisk which were on ASM buy drop disk using asmlib and after that 
recreating the disk again and start the cluster in exclusive mode and start 
the ASM and mount the diskgourp So that rebalancing has been done but after 
that

 crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
1. ONLINE   9f7f4f7f798d4f69bfe31653894421a2 (ORCL:GRID1) [GRID]
2. OFFLINE  a9b785a59c3c4f67bf15babc67ffb79a () []
3. OFFLINE  29988f37fa794f12bfea3f3672c99609 () []
4. ONLINE   a8b3a040195c4f54bfce8ef21bd4fa07 (ORCL:GRID3) [GRID]
5. ONLINE   a1e4fbd9df6f4f67bf8fc12fe9780721 (ORCL:GRID2) [GRID]
Located 5 voting disk(s).

and not able to drop the vote disk which is offiline 

WORKAROUND:
-----------
n/a

RELATED BUGS:
-------------
as per bug 9024611 tried the workaround:

but while running 

crsctl css votedisk delete 

we got syntax error and found that there is no command with crsctl css ...

这个Note说明不能移除ASM存储内voting files的问题在11.2.0.2.0上已经解决了，也可以通过安装one-off patch 9024611来修复。

但是实际在11.2.0.2上测试可以发现仍旧无法删除ASM上的voting files:

root@rh2 ~]# crsctl query crs  releaseversion
Oracle High Availability Services release version on the local node is [11.2.0.2.0]

[root@rh2 ~]# crsctl query crs  activeversion
Oracle Clusterware active version on the cluster is [11.2.0.2.0]


[grid@rh2 ~]$ /s01/grid/OPatch/opatch lsinventory
Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation.  All rights reserved.


Oracle Home       : /s01/grid
Central Inventory : /s01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.1
OUI version       : 11.2.0.2.0
OUI location      : /s01/grid/oui
Log file location : /s01/grid/cfgtoollogs/opatch/opatch2011-08-04_18-50-34PM.log

Patch history file: /s01/grid/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /s01/grid/cfgtoollogs/opatch/lsinv/lsinventory2011-08-04_18-50-34PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1): 

Oracle Grid Infrastructure                                           11.2.0.2.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


Rac system comprising of multiple nodes
  Local node = rh2
  Remote node = rh3

--------------------------------------------------------------------------------

OPatch succeeded.


[root@rh2 ~]# crsctl delete css votedisk a433d51ebd2d4facbfc8e95b017f5393

CRS-4258: Addition and deletion of voting files are not allowed because the voting files are on ASM

又是一个伪修复的Bug….!!

无法,寄希望与replace能解决问题，结果发现：

crsctl replace votedisk +DATA
Failed to create voting files on disk group DATA.
Change to configuration failed, but was successfully rolled back.
CRS-4000: Command Replace failed, or completed with errors.

方法四中指出的unpublished bug 9409327(Patch 9409327: OFFLINE VF ENTRY REMAINS AFTER PATCH FOR BUG 9024611)，目前仅在IBM AIX on POWER Systems (64-bit)的11.2.0.1上有对应的补丁。

Filed Under: Oracle, Oracle 11g新特性, Oracle ASM, Oracle Bug Tagged With: , , , , ,

Enterprise Manager show database status is unavailable

2011/08/03 by 1 Comment

客户的一套11.2.0.1 RAC数据库部署了 Enterprise Manager 11g以方便管理，但系统运行一段时间后EM就变得无法正常使用了，页面显示”the database status is currently unavailable…….”，但实际上数据库和监听都处于正常可用的状态。

sysman_password

作为乙方来说对Enterprise Manager并不太熟悉，虽然EM是一个十分强大的图形化监控工具，但是我们很少有机会在生产环境中用到它，这一点可能和甲方的DBA不同。

针对该EM不可用的问题首先想到了尝试重启dbconsole，以验证该问题是否可重现。

emctl stop  dbconsole
emctl start dbconsole

重启dbconsole后问题依旧,查MOS发现引发该EM不可用问题的可能性有多种，通过$ORACLE_HOME/_/sysman/log/emoms.log的EM相关日志可以进一步定位该问题:

tail -500 emoms.log

 at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
        at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
        at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
        at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
        at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
        at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
        at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:234)
        at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:29)
        at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:879)
        at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
        at java.lang.Thread.run(Thread.java:595)
2011-08-02 14:39:46,131 [EMUI_14_39_46_/console/aboutApplication] ERROR em.console doGet.363 - java.lang.IllegalStateException: Response has already been committed,
be sure not to write to the OutputStream or to trigger a commit due to any other action before calling this method.
java.lang.IllegalStateException: Response has already been committed, be sure not to write to the OutputStream or to trigger a commit due to any other action before calling this method.
        at com.evermind.server.http.EvermindHttpServletResponse.sendRedirect(EvermindHttpServletResponse.java:1371)
        at oracle.sysman.emSDK.svlt.EMServlet.myDoGet(EMServlet.java:812)
        at oracle.sysman.emSDK.svlt.EMServlet.doGet(EMServlet.java:343)
        at oracle.sysman.eml.app.Console.doGet(Console.java:157)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
        at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)
        at oracle.sysman.eml.app.EMRepLoginFilter.doFilter(EMRepLoginFilter.java:107)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)
        at oracle.sysman.db.adm.inst.HandleRepDownFilter.doFilter(HandleRepDownFilter.java:146)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
        at oracle.sysman.eml.app.BrowserVersionFilter.doFilter(BrowserVersionFilter.java:122)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
        at oracle.sysman.emSDK.svlt.EMRedirectFilter.doFilter(EMRedirectFilter.java:102)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
        at oracle.sysman.eml.app.ContextInitFilter.doFilter(ContextInitFilter.java:327)
        at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621)
        at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
        at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
        at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
        at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
        at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
        at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
        at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
        at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:234)
        at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:29)
        at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:879)
        at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
        at java.lang.Thread.run(Thread.java:595)
2011-08-02 14:39:46,150 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:39:46,157 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean01.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:39:46,182 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean04.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:39:51,352 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:39:51,394 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean03.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:39:51,509 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:39:51,537 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean03.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:39:56,757 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:03,574 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:03,603 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean02.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:03,729 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:03,800 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean02.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:09,328 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:09,361 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean03.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:11,946 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:16,285 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:16,307 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:16,321 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean01.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:16,368 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean04.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:16,437 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:16,472 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean01.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:16,507 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:16,536 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean04.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:17,187 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:17,189 [EMUI_14_40_17_/console/aboutApplication] ERROR svlt.PageHandler handleRequest.639 - java.lang.IllegalStateException: Response has already been committed
java.lang.IllegalStateException: Response has already been committed
        at com.evermind.server.http.EvermindHttpServletResponse.resetBuffer(EvermindHttpServletResponse.java:1892)
        at com.evermind.server.http.ServletRequestDispatcher.unprivileged_forward(ServletRequestDispatcher.java:249)
        at com.evermind.server.http.ServletRequestDispatcher.access$100(ServletRequestDispatcher.java:51)
        at com.evermind.server.http.ServletRequestDispatcher$2.oc4jRun(ServletRequestDispatcher.java:193)
        at oracle.oc4j.security.OC4JSecurity.doPrivileged(OC4JSecurity.java:284)
        at com.evermind.server.http.ServletRequestDispatcher.forward(ServletRequestDispatcher.java:198)
        at oracle.sysman.emSDK.svlt.PageHandler.render(PageHandler.java:1113)
        at oracle.sysman.emSDK.svlt.PageHandler.myRender(PageHandler.java:1173)
        at oracle.sysman.emSDK.svlt.PageHandler.handleRequest(PageHandler.java:597)
        at oracle.sysman.emSDK.svlt.EMServlet.myDoGet(EMServlet.java:787)
        at oracle.sysman.emSDK.svlt.EMServlet.doGet(EMServlet.java:343)
        at oracle.sysman.eml.app.Console.doGet(Console.java:157)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
        at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)
        at oracle.sysman.eml.app.EMRepLoginFilter.doFilter(EMRepLoginFilter.java:107)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)
        at oracle.sysman.db.adm.inst.HandleRepDownFilter.doFilter(HandleRepDownFilter.java:146)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
        at oracle.sysman.eml.app.BrowserVersionFilter.doFilter(BrowserVersionFilter.java:122)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
        at oracle.sysman.emSDK.svlt.EMRedirectFilter.doFilter(EMRedirectFilter.java:102)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
        at oracle.sysman.eml.app.ContextInitFilter.doFilter(ContextInitFilter.java:327)
        at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621)
        at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
        at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
        at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
        at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
        at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
        at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
        at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
        at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:234)
        at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:29)
        at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:879)
        at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
        at java.lang.Thread.run(Thread.java:595)
2011-08-02 14:40:17,189 [EMUI_14_40_17_/console/aboutApplication] ERROR em.console doGet.363 - java.lang.IllegalStateException: Response has already been committed, be sure not to write to the OutputStream or to trigger a commit due to any other action before calling this method.
java.lang.IllegalStateException: Response has already been committed, be sure not to write to the OutputStream or to trigger a commit due to any other action before calling this method.
        at com.evermind.server.http.EvermindHttpServletResponse.sendRedirect(EvermindHttpServletResponse.java:1371)
        at oracle.sysman.emSDK.svlt.EMServlet.myDoGet(EMServlet.java:812)
        at oracle.sysman.emSDK.svlt.EMServlet.doGet(EMServlet.java:343)
        at oracle.sysman.eml.app.Console.doGet(Console.java:157)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
        at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)
        at oracle.sysman.eml.app.EMRepLoginFilter.doFilter(EMRepLoginFilter.java:107)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)
        at oracle.sysman.db.adm.inst.HandleRepDownFilter.doFilter(HandleRepDownFilter.java:146)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
        at oracle.sysman.eml.app.BrowserVersionFilter.doFilter(BrowserVersionFilter.java:122)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
        at oracle.sysman.emSDK.svlt.EMRedirectFilter.doFilter(EMRedirectFilter.java:102)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
        at oracle.sysman.eml.app.ContextInitFilter.doFilter(ContextInitFilter.java:327)
        at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621)
        at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
        at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
        at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
        at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
        at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
        at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
        at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
        at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:234)
        at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:29)
        at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:879)
        at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
        at java.lang.Thread.run(Thread.java:595)
2011-08-02 14:40:21,661 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:21,691 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean03.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:21,822 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:21,886 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean03.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:27,141 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:33,941 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:33,975 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean02.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:34,092 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:34,124 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean02.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:42,088 [PAFStartupThread] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:42,332 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:42,584 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:42,611 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean01.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:46,594 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:46,622 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean01.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:46,653 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:46,683 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean04.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:46,738 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:46,766 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean01.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:46,793 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:46,822 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean04.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:48,244 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

从以上em日志中可以发现最显著的问题为ORA-28000，显然是EM相关的数据库用户被lock住了。10g中与EM相关的数据库用户包括SYSMAN和DBSNMP，分别查询该2个用户的状态:

SQL> select account_status,lock_date,username from dba_users where username in ('SYSMAN','DBSNMP');

ACCOUNT_STATUS                   LOCK_DATE       USERNAME
-------------------------------- --------------- ------------------------------
OPEN                                             DBSNMP
LOCKED(TIMED)                    02-JUL-11       SYSMAN

发现SYSMAN用户在7月份被锁定了，而且是LOCKED(TIMED)，说明是因为登陆密码不正确多次登陆后被锁的。LOCKED(TIMED)的相关信息以下Note:

DBA_USERS.ACCOUNT_STATUS shows LOCKED(TIMED) instead of LOCKED even if PASSWORD_LOCK_TIME set to unlimited [ID 284344.1]
  	Modified 18-FEB-2011     Type PROBLEM     Status PUBLISHED

In this Document
  Symptoms
  Cause
  Solution
  References

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.1 - Release: 9.2 to 11.2
Information in this document applies to any platform.
Checked for relevance on 21-Sep-2010
Symptoms
DBA_USERS.ACCOUNT_STATUS shows LOCKED(TIMED) instead of LOCKED for a user with a profile with a PASSWORD_LOCK_TIME
set to unlimited and number of failed logins > FAILED_LOGIN_ATTEMPTS

For eg.

create profile my_profile limit failed_login_attempts 3 password_lock_time unlimited ;

SQL> create user my_user identified by my_user default tablespace users temporary tablespace temp profile my_profile ;

User created.

SQL> grant connect to my_user ;

(after 3 failed login attempts)

USERNAME PROFILE ACCOUNT_STATUS
------------------------------ ------------------------------ --------------
MY_USER MY_PROFILE LOCKED(TIMED)
Cause
Up until Oracle RDBMS version 11.1.0.7, after a number of failed logins > FAILED_LOGIN_ATTEMPTS, the account
status of the user will always be LOCKED(TIMED).
One would only see LOCKED if the account was explicitly locked during create or alter user/role
(ie after using CREATE USER or ALTER USER statements)

eg.

alter user my_lock account lock ;

USERNAME PROFILE ACCOUNT_STATUS
-------------------------------
MY_USER MY_PROFILE LOCKED

Solution
Expected behaviour is
1. Oracle release is  FAILED_LOGIN_ATTEMPTS

2. Oracle release is >= 11.2 and PASSWORD_LOCK_TIME = unlimited:
DBA_USERS.ACCOUNT_STATUS = LOCKED whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS

3. Oracle release is >= 11.2 and PASSWORD_LOCK_TIME =
DBA_USERS.ACCOUNT_STATUS = LOCKED(TIMED) whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS

于是我尝试将SYSMAN用户解锁，并重启EM并再次尝试登陆:

SQL> alter user sysman account unlock;
User altered.

重启EM后重新登陆，发现问题仍未解决，再次检查账户信息

SQL> select account_status,lock_date,username from dba_users where username in ('SYSMAN','DBSNMP');

ACCOUNT_STATUS                   LOCK_DATE       USERNAME
-------------------------------- --------------- ------------------------------
OPEN                                             DBSNMP
LOCKED(TIMED)                    02-AUG-11       SYSMAN

发现SYSMAN用户又被LOCK住了，显然是有人在数据库内修改了SYSMAN的密码后，没有重新配置EM中的SYSMAN密码，导致每次EM启动时都会尝试用错误的密码登陆数据库，最终造成SYSMAN用户被LOCKED(TIMED)住，并出现”ORA-28000: the account is locked”错误。

找出根本原因后问题就显得十分简单了，我们只需要关闭EM，并同步数据库和EM中的SYSMAN密码，再重启EM就可以了:

1.
首先关闭EM
export ORACLE_SID=
export ORACLE_UNQNAME=
cd $ORACLE_HOME/bin
./emctl stop dbconsole

2.
修改DB中的SYSMAN密码并解锁用户，并验证登陆正常
alter user sysman identified by  ;

alter user sysman account unlock;

conn sysman/password

3.
更新EM中的SYSMAN密码
export ORACLE_SID=
export ORACLE_UNQNAME=
cd $ORACLE_HOME/bin
./emctl setpasswd dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
https://maclean.askmac.cn:1158/em/console/aboutApplication
Please enter new repository password:
Repository password successfully updated.

4.
启动EM
export ORACLE_SID=
export ORACLE_UNQNAME=
cd $ORACLE_HOME/bin
./emctl start dbconsole
Filed Under: Oracle, Oracle 11g新特性 Tagged With: , ,

Oracle RAC内部错误:ORA-00600[kjbmprlst:shadow]一例

2011/07/25 by 2 Comments

一套Linux x86-64上的11.2.0.1 4节点RAC系统中LMS GCS服务进程遭遇到内部错误ORA-00600[kjbmprlst:shadow],导致节点实例意外终止,具体日志如下:

Fri Jul 08 02:04:43 2011
Errors in file /u01/app/oracle/diag/rdbms/PROD/PROD1/trace/PROD1_lms1_536.trc  (incident=1011732):
ORA-00600: internal error code, arguments: [kjbmprlst:shadow], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/PROD/PROD1/incident/incdir_1011732/PROD1_lms1_536_i1011732.trc
Fri Jul 08 02:04:44 2011
Trace dumping is performing id=[cdmp_20110708020444]
Errors in file /u01/app/oracle/diag/rdbms/PROD/PROD1/trace/PROD1_lms1_536.trc:
ORA-00600: internal error code, arguments: [kjbmprlst:shadow], [], [], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/PROD/PROD1/trace/PROD1_lms1_536.trc:
ORA-00600: internal error code, arguments: [kjbmprlst:shadow], [], [], [], [], [], [], [], [], [], [], []
LMS1 (ospid: 536): terminating the instance due to error 484
Fri Jul 08 02:04:45 2011
opiodr aborting process unknown ospid (27387) as a result of ORA-1092
System state dump is made for local instance
System State dumped to trace file /u01/app/oracle/diag/rdbms/PROD/PROD1/trace/PROD1_diag_513.trc
Fri Jul 08 02:04:54 2011
Termination issued to instance processes. Waiting for the processes to exit
Fri Jul 08 02:04:58 2011
ORA-1092 : opitsk aborting process

该ORA-00600[kjbmprlst:shadow]错误定位为11.2.0.1上的Bug 10121589或Bug 9458781：

Bug 10121589  ORA-600 [kjbmprlst:shadow] can occur in RAC
Affects:

    Product (Component)	Oracle Server (Rdbms)
    Range of versions believed to be affected 	Versions BELOW 12.1
    Versions confirmed as being affected 	

        11.2.0.1 

    Platforms affected	Generic (all / most platforms affected)

Fixed:

    This issue is fixed in	

        12.1 (Future Release)
        11.2.0.2 Bundle Patch 2 for Exadata Database
        11.2.0.1 Bundle Patch 7 for Exadata Database 

Symptoms:

Related To:

    Internal Error May Occur (ORA-600)
    ORA-600 [kjbmprlst:shadow] 

    RAC (Real Application Clusters) / OPS 

Description

    An ORA-600 [kjbmprlst:shadow] can occur if the fix for bug 9979039
    is present.

    Note:
     One off patches for 10200390 should also include this fix.

Bug 9458781  Missing close message to master leaves closed lock dangling crashing the instance with assorted Internal error

Affects:

    Product (Component)	Oracle Server (Rdbms)
    Range of versions believed to be affected 	Versions >= 11.2.0.1 but BELOW 11.2.0.2
    Versions confirmed as being affected 	

        11.2.0.1 

    Platforms affected	Generic (all / most platforms affected)

Fixed:

    This issue is fixed in	

        11.2.0.2 (Server Patch Set)
        11.2.0.1 Bundle Patch 4 for Exadata Database 

Symptoms:

Related To:

    Instance May Crash
    Internal Error May Occur (ORA-600)
    ORA-600 [KJBMPRLST:SHADOW]
    ORA-600 [KJBMOCVT:RID]
    ORA-600 [KJBRREF:PKEY]
    ORA-600 [KJBRASR:PKEY] 

    RAC (Real Application Clusters) / OPS 

Description

    A lock is closed without sending a message to the master.
    This causes closed lock dangling at the master crashing the instance with different internal errors.

    Reported internal errors so far are :
    - KJBMPRLST:SHADOW
    - KJBMOCVT:RID
    - KJBRREF:PKEY
    - KJBRASR:PKEY

该kjbmprlst:shadow内部函数用以管理kjbm shadow锁(/libserver10.a/kjbm.o )信息,存在某个已关闭的lock没有及时message给master node的代码漏洞,目前除了安装补丁外没有已验证的workaround办法(disable drm似乎是无效的):

oradebug lkdebug (track resources, take dumps)
KCL history
KJBL history
KJL history

PCM (GCS) and non-PCM (GES) resources are kept separate and use separate code paths.
GES:
Resource table: kjr and kjrt
Lock table: kjlt
Processes: kjpt
GCS:
Resource table: kjbr
Lock table: kjbl

DLM Structures (continued)
/* PCM resource structure */
typedef struct kjbr {                                /* 68 bytes on sun4u */
  kjsolk       hash_q_kjbr;                             /* hash list : hp */
  ub4          resname_kjbr[2];	                     /* the resource name */
  kjsolk       scan_q_kjbr; /* chain to lmd scan q of grantable resources */
  kjsolk       grant_q_kjbr;                 /* list of granted resources */
  kjsolk       convert_q_kjbr;       /* list of resources being converted */
  ub4          diskscn_bas_kjbr;         /* scn(base) known to be on disk */
  ub2          diskscn_wrap_kjbr;        /* scn(wrap) known to be on disk */
  ub2          writereqscn_wrap_kjbr;    /* scn(wrap) requested for write */
  ub4          writereqscn_bas_kjbr;     /* scn(base) requested for write */
  struct kjbl *sender_kjbr;                 /* lock elected to send block */
  ub2          senderver_kjbr;                  /* version# of above lock */
  ub2          writerver_kjbr;                  /* version# of lock below */
  struct kjbl *writer_kjbr;                /* lock elected to write block */
  ub1          mode_role_kjbr; /* one of 'n', 's', 'x' && one of 'l' or 'g' */
  ub1          flags_kjbr;                        /* ignorewip, free etc. */
  ub1          rfpcount_kjbr;                      /* refuse ping counter */
  ub1          history_kjbr;                /* resource operation history */
  kxid         xid_kjbr;                          /* split transaction ID */
} kjbr ;

/* kjbl - PCM lock structure
** Clients and most of the DLM will use the KJUSER* or KJ_* modes and kscns  */

typedef struct kjbl {                                /* 52 bytes on sun4u */
  union {                     /* discriminate lock@master and lock@client */
    struct {                                           /* for lock@master */
      kgglk        state_q_kjbl;             /* link to chain to resource */
      kjbopqi     *rqinfo_kjbl;                             /* target bid */
      struct kjbr *resp_kjbl;                   /* pointer to my resource */
    } kjbllam;                                 /* KJB Lock Lock At Master */
    struct {                                           /* for lock@client */
      ub4         disk_base_kjbl;        /* disk version(base) for replay */
      ub2         disk_wrap_kjbl;        /* disk version(wrap) for replay */
      ub1         master_node_kjbl;                   /* master instance# */
      ub1         client_flag_kjbl;     /* flags specific to client locks */
      ub2         update_seq_kjbl;               /* last update to master */
    } kjbllac;                                 /* KJB Lock Lock At Client */
  } kjblmcd;                        /* KJB Lock Master Client Discrimnant */
  void  *remote_lockp_kjbl;           /* pointer to client lock or shadow */
  ub2    remote_ver_kjbl;                         /* remote lock version# */
  ub2        ver_kjbl;                                     /* my version# */
  ub2        msg_seq_kjbl;                         /* client->master seq# */
  ub2        reqid_kjbl;                         /* requestid for convert */
  ub2        creqid_kjbl; /* requestid for convert that has been cancelled */
  ub2        pi_wrap_kjbl;                     /* scn(wrap) of highest pi */
  ub4        pi_base_kjbl;                     /* scn(base) of highest pi */
  ub1        mode_role_kjbl; /* one of 'n', 's', 'x' && one of 'l' or 'g' */
  ub1        state_kjbl;       /* _L|_R|_W|_S, notify, which q, lock type */
  ub1        node_kjbl;                       /* instance lock belongs to */
  ub1        flags_kjbl;                                /* lock flag bits */
  ub2        rreqid_kjbl;                               /* save the reqid */
  ub2         write_wrap_kjbl;        /* last write request version(wrap) */
  ub4         write_base_kjbl;        /* last write request version(base) */
  ub4         history_kjbl;                     /* lock operation history */
} kjbl;

PCM DLM locks that are owned by the local instance are allocated and embedded in an LE structure.
PCM DLM locks that are owned by remote instances and mastered by the local instance are allocated in SHARED_POOL.

PCM Locks and Resources
Fields of interest in the kclle structure: kcllerls or releasing; kcllelnm or name(id1,id2);
kcllemode or held-mode; kclleacq or acquiring; kcllelck or DLM lock.

Fields of interest in the kjbr structure: resname_kjbr[2] or resource name; grant_q_kjbr or grant queue;
convert_q_kjbr or convert queue; mode_role_kjbr, which is a bitwise merge of grant mode and
role-interpreted NULL(0x00), S(0x01), X(0x02), L0 Local (0x00), G0 Global without PI (0x08), G1 Global with PI (0x018).

The field mode_role_kjbl in kjbl is a bitwise merge of grant, request, and lock mode: 0x00 if grant NULL;
0x01 if grant S; 0x02 if grant X; 0x04 lock has been opened at master; 0x08 if global role (otherwise local);
0x10 has one or more PI; 0x20 if request CR; 0x40 if request S; 0x80 if request X.

Someone has to keep a list of all buffers and where they are mastered
This is called Global Resource Directory (GRD)
GRD is present on all the instances of the cluster
To find out the master:
select  b.dbablk, r.kjblmaster master_node
from x$le l, x$kjbl r, x$bh b
where b.obj =
and b.le_addr = l.le_addr
and l.le_kjbl = r.kjbllockp

Oracle Support宣称可以通过11.2.0.2 (Server Patch Set)或11.2.0.1 Bundle Patch 4 for Exadata Database修复该bug，但是有迹象表明在11.2.0.2上仍可能发生该ORA-00600[kjbmprlst:shadow]内部错误，同时该bug更多地发生在超过2个节点的RAC系统中。

 

Filed Under: Oracle, Oracle 11g新特性, Oracle Bug, Oracle RAC Tagged With: , , , , ,

利用UDEV服务解决RAC ASM存储设备名

2011/07/10 by 23 Comments

在<Why ASMLIB and why not?>我们介绍了使用ASMLIB作为一种专门为Oracle Automatic Storage Management特性设计的内核支持库(kernel support library)的优缺点，同时建议使用成熟的UDEV方案来替代ASMLIB。

这里我们就给出配置UDEV的具体步骤，还是比较简单的:

1.确认在所有RAC节点上已经安装了必要的UDEV包

[root@rh2 ~]# rpm -qa|grep udev
udev-095-14.21.el5

2.通过scsi_id获取设备的块设备的唯一标识名，假设系统上已有LUN sdc-sdp

for i in c d e f g h i j k l m n o p ;
do
echo "sd$i" "`scsi_id -g -u -s /block/sd$i` ";
done

sdc 1IET_00010001
sdd 1IET_00010002
sde 1IET_00010003
sdf 1IET_00010004
sdg 1IET_00010005
sdh 1IET_00010006
sdi 1IET_00010007
sdj 1IET_00010008
sdk 1IET_00010009
sdl 1IET_0001000a
sdm 1IET_0001000b
sdn 1IET_0001000c
sdo 1IET_0001000d
sdp 1IET_0001000e 

以上列出于块设备名对应的唯一标识名

3.创建必要的UDEV配置文件，

首先切换到配置文件目录

[root@rh2 ~]# cd /etc/udev/rules.d

定义必要的规则配置文件

[root@rh2 rules.d]# touch 99-oracle-asmdevices.rules 

[root@rh2 rules.d]# cat 99-oracle-asmdevices.rules
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="1IET_00010001", NAME="ocr1", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="1IET_00010002", NAME="ocr2", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="1IET_00010003", NAME="asm-disk1",  OWNER="grid",  GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="1IET_00010004", NAME="asm-disk2",  OWNER="grid",  GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="1IET_00010005", NAME="asm-disk3",  OWNER="grid",  GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="1IET_00010006", NAME="asm-disk4",  OWNER="grid",  GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="1IET_00010007", NAME="asm-disk5",  OWNER="grid",  GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="1IET_00010008", NAME="asm-disk6",  OWNER="grid",  GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="1IET_00010009", NAME="asm-disk7",  OWNER="grid",  GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="1IET_0001000a", NAME="asm-disk8",  OWNER="grid",  GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="1IET_0001000b", NAME="asm-disk9",  OWNER="grid",  GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="1IET_0001000c", NAME="asm-disk10", OWNER="grid",  GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="1IET_0001000d", NAME="asm-disk11", OWNER="grid",  GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="1IET_0001000e", NAME="asm-disk12", OWNER="grid",  GROUP="asmadmin", MODE="0660"

Result 为/sbin/scsi_id -g -u -s %p的输出--Match the returned string of the last PROGRAM call. This key may be
used in any following rule after a PROGRAM call.
按顺序填入刚才获取的唯一标识名即可

OWNER为安装Grid Infrastructure的用户，在11gr2中一般为grid，GROUP为asmadmin
MODE采用0660即可

NAME为UDEV映射后的设备名，
建议为OCR和VOTE DISK创建独立的DISKGROUP，为了容易区分将该DISKGROUP专用的设备命名为ocr1..ocrn的形式
其余磁盘可以根据其实际用途或磁盘组名来命名

4.将该规则文件拷贝到其他节点上
[root@rh2 rules.d]# scp 99-oracle-asmdevices.rules Other_node:/etc/udev/rules.d

5.在所有节点上启动udev服务，或者重启服务器即可

[root@rh2 rules.d]# /sbin/udevcontrol reload_rules
[root@rh2 rules.d]# /sbin/start_udev
Starting udev:                                            [  OK  ]

6.检查设备是否到位

[root@rh2 rules.d]# cd /dev
[root@rh2 dev]# ls -l ocr*
brw-rw---- 1 grid asmadmin 8, 32 Jul 10 17:31 ocr1
brw-rw---- 1 grid asmadmin 8, 48 Jul 10 17:31 ocr2

[root@rh2 dev]# ls -l asm-disk*
brw-rw---- 1 grid asmadmin 8,  64 Jul 10 17:31 asm-disk1
brw-rw---- 1 grid asmadmin 8, 208 Jul 10 17:31 asm-disk10
brw-rw---- 1 grid asmadmin 8, 224 Jul 10 17:31 asm-disk11
brw-rw---- 1 grid asmadmin 8, 240 Jul 10 17:31 asm-disk12
brw-rw---- 1 grid asmadmin 8,  80 Jul 10 17:31 asm-disk2
brw-rw---- 1 grid asmadmin 8,  96 Jul 10 17:31 asm-disk3
brw-rw---- 1 grid asmadmin 8, 112 Jul 10 17:31 asm-disk4
brw-rw---- 1 grid asmadmin 8, 128 Jul 10 17:31 asm-disk5
brw-rw---- 1 grid asmadmin 8, 144 Jul 10 17:31 asm-disk6
brw-rw---- 1 grid asmadmin 8, 160 Jul 10 17:31 asm-disk7
brw-rw---- 1 grid asmadmin 8, 176 Jul 10 17:31 asm-disk8
brw-rw---- 1 grid asmadmin 8, 192 Jul 10 17:31 asm-disk9
Filed Under: Oracle, Oracle 11g新特性, Oracle ASM Tagged With: , , , ,

cursor_sharing=’SIMILAR’将被废弃

2011/06/29 by 4 Comments

根据metalink文档<ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting [ID 1169017.1]>在11g中将逐渐废弃cursor_sharing参数的SIMILAR选项，原因是在今后的版本中Exact和Force选项可以满足游标共享的需求了，使用SIMILAR选项可能引发额外的version_count过多或cursor pin s on X等待事件。

We recommend that customers discontinue setting cursor_sharing = SIMILAR due to the many problematic situations customers have experienced using it. The ability to set this will be removed in version 12 of the Oracle Database (the settings of EXACT and FORCE will remain available). Instead, we recommend the use of Adaptive Cursor Sharing in 11g. A number of customers have seen an increase in the number of child cursors since migrating to Oracle Database 11g Release 2. This can lead to many problems including complete CPU saturation of a machine requiring a database instance bounce or general database performance issues in the form of waits on mutexes and ‘library cache lock’.
From Oracle versions 9.0 through 11.1, an oracle instance would limit the number child cursors in the shared pool associated with a single parent to 1024 before it would mark the parent OBSOLETE effectively invalidating it and all the children. Then a new parent with one child would be created and used going forward. But this would only limit the degradation of performance for some environments rather than fix something that could be addressed more effectively through improved application coding. (The attempt to address this from the database side also introduced other issues like bug 5177766). The child limit was removed by Oracle development because it was only masking an application problem at the expense of database performance for better designed applications. In addition, the obsolete code would not work in cases when SQL was wrapped within PL/SQL. The fundamental problem that obsolete code was masking is application code that was written incorrectly with regards to the ability to be shared. For example,  it is not written with user binds or the literal characteristics differ to a high degree.
Therefore, setting cursor_sharing = SIMILAR is highly discouraged in Oracle Database 11g Release 2 (and generally has not been recommended for most environments even in earlier versions) for several reasons:
1) This parameter is generally overly restrictive in what it actually allows to be shared. SIMILAR tells oracle to try and share cursors by replacing all literals with binds for legacy applications, but directs that sharing only be performed when all the replaced literal values were exactly the same (in the case of predicates referencing columns with histograms or using inequality operators such as BETWEEN, <, and !=)
2) This parameter seems to bypass a lot of the improvements made with Oracle Database 11g’s Adaptive Cursor Sharing feature and other abilities in the Cost Based Optimizer code to make better decisions on what execution plans should and should not be shared.
3) Having many child cursors all associated with 1 parent cursor could perform much worse than having many parent cursors that would be seen with having the default setting of cursor_sharing = EXACT (or FORCE). The scenario of many thousands of child cursors associated with 1 parent results in a potential bottleneck for searches for matching cursors (soft parsing) within the library cache.
The cursor_sharing parameter was introduced as a workaround for legacy applications that could not scale because they had not yet been redesigned to use bind variables. It has been presumed that most applications have been redesigned since then. If you are still using such an application, our recommendation is to set cursor_sharing = FORCE. This setting maximizes cursor sharing while leveraging the Adaptive Cursor Sharing framework to generate multiple execution plans based on different literal value ranges if necessary.

What do you need to do?

Change the cursor_sharing to either FORCE or EXACT, keeping in mind the effects of either.

this is due to the setting in the init.ora:
cursor_sharing=SIMILAR

Please set immediately
alter system set cursor_sharing=’FORCE’ scope=both;

to reduce the number of versions for a single sql statement whic is overloading your shared_pool.

377847.1 for SIMILA not FORCE are not the same

When bind variables are peeked. The parse engine makes a decision as to the ‘safety’ of these peeked values for creating plans based upon whether it is felt that different values could produce different plans.

The usual (but not the only) reason for such different plans is the use of CURSOR_SHARING=SIMILAR and the presence of histogram column statistics on the column that the bind is being compared with when using the Cost Based Optimizer (CBO). If there are histograms on the column, then the bind value may be deemed to be ‘unsafe’ because there is the potential chance that the different values could produce a different explain plan and the selection of a single plan for all values may not be ‘safe’ in terms of performance. If the bind is deemed ‘unsafe’ then multiple children are created for each set of different bound values so that different plans can be associated with them. This occurs in a few scenarios but the most common is with histogram stats on an equality predicate.

CURSOR_SHARING=SIMILAR

With CURSOR_SHARING=SIMILAR whenever the optimizer looks at a replaced bind value to make a decision then that bind is checked to see if it should be considered unsafe. The check made is :

Is the operator NEITHER of   ‘=’ or ‘!=’
OR
Are there Column Histograms present on the column.

If either of these are true then the bind is deemed to be unsafe and a new cursor will be created (So binds used in non equality predicates (eg >, <, >=, <=, LIKE) are unsafe). To check for whether a bind is considered unsafe see:

Note:261020.1 High Version Count with CURSOR_SHARING = SIMILAR or FORCE

With histogram stats on an equality predicate, this can cause severe problems (in terms of sharing) if there is, for example, a histogram on a main key column.eg:

select … from orders where orderid=’literal’;

If  there is a histogram on “orderid” then the bind will likely get marked unsafe and a new child will be produced for every single value of ‘literal’. The SQL would only be shared if the value of ‘literal’ matches exactly to a value used already.

Remember that if literals are converted to binds due to CURSOR_SHARING then they are subject to this checking, though unsafe binds are really only an issue if CURSOR_SHARING is SIMILAR.

In an OLTP type environment it would be sensible to only use histogram statistics on columns that need it (i.e. where there are only a few distinct values with heavy skew and where different plans are needed for different predicate values). This way most SQL is shared and the absence of histograms keeps predicates safe except where needed.

CURSOR_SHARING = FORCE

If CURSOR_SHARING = FORCE binds can still be “unsafe” (in terms of performance) if used by the optimizer in decisions but this should not then affect shareability of the SQL since CURSOR_SHARING=FORCE does not care about unsafe literals, but the cursor should stil lbe shared. e.g.: In the above example with orderid=’literal’, without histograms, the CBO does not need to look at ‘literal’ to determine the selectivity of the predicate and so the bind does not get marked unsafe.
If there is histograms, the predicate is marked as unsafe, but since FORCE uses the same plan whatever the circumstance, this does not matter. Only where non data literals for whom different values alter the actual meaning of the SQL (e.g. order by 1 versus order by 2) will an unsafe predicate have an affect on plans.

Note that, prior to 11g, unsafe literals are NOT covered by ‘bind mismatch’ in V$SQL_SHARED_CURSOR  as this is for user bind metadata mismatches. ie: different max bind lengths or bind type mismatches.
In 11g R2 (and 11.1.0.7 Patchset) a new column has been added to V$SQL_SHARED_CURSOR to check if literal replacement is used with CURSOR_SHARING=SIMILAR. The new column HASH_MATCH_FAILED  is set to “Y” if sharing fails due to a hash mismatch, such as the case with mismatched histogram data or a range predicate marked as unsafe by literal replacement. The unshared child cursors may have histogram data
on key columns used in equality predicates, or range predicates with literals which the optimizer has marked
as unsafe.

From the optimizer point of view, these additional plans for ‘unsafe’ bind variables explain why multiple good plans may occur for peeked bind variables, even though the standard behavior for binds is to use peeked binds for the initial parse and then use the resultant plan for all other iterations. With unsafe binds, different plans for different peeked values can occur.

Background Information

This issue has been coming more in to focus with dynamic sampling in 10.2 since the default was changed from 1 to 2. When optimizer_dynamic_sampling is greater than 1 then Dynamic sampling emulates statistics + histograms. If histograms are created, then binds may be marked as unsafe and produce different plans for different values. With optimizer_dynamic_sampling > 1 a predicate can create a new version of a sql statement for each different value, even if there are no histograms (or even statistics) on a table (since dynamic sampling may create these in the background).

 

在11g中Oracle官方已经不再推荐使用SIMILAR选项，对于已经升级到11g的仍在使用cursor_sharing=’SIMILAR’的用户，建议尽早修改应用做到绑定变量，这样可以最稳妥的将cusror_sharing设置为EXACT，对于无法做到绑定变量的应用那么FORCE还会是一个和好的折中选择。

在版本12g中我们将不再看到SIMILAR选项。

 

Note #1: Forcing cursor sharing among similar (but not identical)
statements can have unexpected results in some DSS applications
and in applications using stored outlines.

Note #2: Setting CURSOR_SHARING to FORCE causes an increase in the
maximum lengths (as returned by DESCRIBE) of any selected
expressions that contain literals (in a SELECT statement).
However, the actual length of the data returned will not change.

Cursor_sharing 相关的BUG 列表：

 

NB Bug Fixed Description
14456124 12.1.0.0 Predicate push may not occur with cursor sharing
14053457 11.2.0.4, 12.1.0.0 ORA-917 parsing SQL with indicator binds with CURSOR_SHARING
12723295 11.2.0.4, 12.1.0.0 ORA-600 [qerixGetKey:optdesc] with function based index and CURSOR_SHARING
9877960 11.2.0.4, 12.1.0.0 ORA-600 or similar using CURSOR_SHARING with HS connections
14087914 12.1.0.0 Wrong results from ExistsNode with CURSOR_SHARING
+ 13550185 11.2.0.2.BP17, 11.2.0.3.4, 11.2.0.3.BP06, 12.1.0.0 Hang / SGA memory corruption / ORA-7445 [kglic0] when using multiple shared pool subpools
13023854 11.2.0.4, 12.1.0.0 Long parse time / hang for SQL with nested CASE expressions with CURSOR_SHARING enabled
12862170 12.1.0.0 INSERT ALL fails with ORA-600[kkslhsh1] with CURSOR_SHARING enabled / High Version Count on HASH_MATCH_FAILED
12797420 11.2.0.3.3, 11.2.0.3.BP07, 12.1.0.0 “library cache: mutex X” waits on DB instance handle with CURSOR_SHARING
12649442 11.2.0.2.8, 11.2.0.2.BP18, 11.2.0.3, 12.1.0.0 ORA-7445 [kxscod] with CURSOR_SHARING=FORCE or SIMILAR
12596444 11.2.0.2.7, 11.2.0.2.BP17, 11.2.0.3.BP08, 12.1.0.0 Cursor not shared with CURSOR_SHARING if SQL has a CASE expression or set operation (UNION)
12534597 12.1.0.0 Bind Peeking is disabled for remote queries
12374212 11.2.0.2.8, 11.2.0.2.BP18, 11.2.0.3, 12.1.0.0 Assorted dump , internal errors, memory corruptions with cursor_sharing = force
12345980 11.2.0.3, 12.1.0.0 high parse time with cursor_sharing=force when session_cached_cursors set
12334286 11.2.0.3, 12.1.0.0 High version counts with CURSOR_SHARING=FORCE (BIND_MISMATCH and INCOMP_LTRL_MISMATCH)
11858021 11.2.0.3, 12.1.0.0 ORA-600 [kpoal8-1] using DG4DRDA with CURSOR_SHARING=force
11806961 11.2.0.3, 12.1.0.0 ORA-600 [kkspsc0: basehd] using CURSOR_SHARING
11738259 11.2.0.2.7, 11.2.0.2.BP17, 11.2.0.3, 12.1.0.0 ORA-600 [kghssgfr2] using CURSOR_SHARING=FORCE
11714159 11.2.0.3, 12.1.0.0 ORA-917 occurs with CURSOR_SHARING even if patch:9877980 is applied – superseded
11076030 11.2.0.3, 12.1.0.0 Wrong results for XDB when CURSOR_SHARING enabled
11069199 11.2.0.2.2, 11.2.0.2.BP06, 11.2.0.3, 12.1.0.0 ORA-600 [kksObsoleteCursor:invalid stub] with CURSOR_SHARING = SIMILAR | FORCE if fix 10187168 present
11063191 11.2.0.2.7, 11.2.0.2.BP17, 11.2.0.3.2, 11.2.0.3.BP04, 12.1.0.0 ORA-4031 with hint /*+ CURSOR_SHARING_EXACT */ – excessive “KKSSP^nn” memory
10126094 11.2.0.2.3, 11.2.0.2.BP08, 11.2.0.3, 12.1.0.0 ORA-600 [kglLockOwnersListAppend-ovf] from literal replacement on SQL issued from PLSQL
10013170 11.2.0.3, 12.1.0.0 ORA-600 [736] from literal replacement with a “WAIT n” clause
* 9877980 11.2.0.2.8, 11.2.0.2.BP18, 11.2.0.3, 12.1.0.0 ORA-7445[kkslMarkLiteralBinds] / Assorted Errors on 11.2.0.2 if cursor sharing is enabled – Affects RMAN
9877964 11.2.0.3, 12.1.0.0 ORA-600 [19003] raised by LIKE :BIND in query
9680430 11.2.0.3, 12.1.0.0 High version count with CURSOR_SHARING = FORCE due to CBO transformation
9548104 11.2.0.2, 12.1.0.0 OERI [qcsfbdnp:1] instead of ORA-1788 with cursor sharing
9413962 11.2.0.2, 12.1.0.0 Many child cursors / ORA-600 [opixrb-3.0] [2005] [ORA-02005] binding literal for remote RPI
9411496 11.2.0.2, 12.1.0.0 ORA-979 on GROUP BY query with CURSOR_SHARING set
9362218 11.2.0.2, 12.1.0.0 Literals replaced by binds when CURSOR_SHARING=EXACT
9348402 12.1.0.0 OERI [kks-hash-collision] can occur with CURSOR_SHARING=FORCE|SIMILAR
9223586 11.2.0.2, 12.1.0.0 Problems with variable length NCHAR literals with cursor sharing
9031183 11.2.0.2, 12.1.0.0 ORA-1722 with CURSOR_SHARING=SIMILAR and with NCHAR
9008904 11.2.0.2, 12.1.0.0 Dump (audTransFro) with CURSOR_SHARING
8913729 11.2.0.2, 12.1.0.0 ORA-979 with CURSOR_SHARING=SIMILAR or FORCE
8545377 11.2.0.2, 12.1.0.0 ORA-1780 with CURSOR_SHARING on XML queries
8246445 11.2.0.2, 12.1.0.0 Query rewrite not working for multi-MV rewrite with literal replacement
5751866 11.2.0.2 Wrong Results with CASE and CURSOR_SHARING
9767674 10.2.0.5.5 Dump [kkslmtl] using CURSOR_SHARING – superceded
8794693 11.2.0.2 Dump [kkscsmtl] using literal replacement (CURSOR_SHARING)
8491399 11.2.0.1 Adaptive Cursor Sharing does not match the correct cursor version for queries using CHAR datatype
8453245 11.2.0.1 Many child cursors with CURSOR_SHARING = FORCE
8264642 11.2.0.1 ORA-600 [kkexbindopn0] with CURSOR_SHARING = SIMILAR
7651092 11.2.0.1 ORA-1000 with Literal Replacement, EXECUTE IMMEDIATE and CURSOR_SHARING_EXACT hint (affects DBMS_STATS)
7516867 10.2.0.5, 11.1.0.7.1, 11.2.0.1 Intermittent Wrong results from literal replacement with fix for bug 6163785
7272297 10.2.0.4.1, 10.2.0.5, 11.1.0.7, 11.2.0.1 Memory corruption / OERI[17114] / OERI[17125] with literal replacement
7212120 11.1.0.7, 11.2.0.1 Session cursor cache not used properly when CURSOR_SHARING=force/similar
6337716 10.2.0.5, 11.1.0.7, 11.2.0.1 Wrong max column size for NULL strings with literal replacement
5757106 10.2.0.4, 11.1.0.7, 11.2.0.1 OERI[15851] selecting aggregate of a constant with literal replacement
4071519 10.2.0.5, 11.1.0.7, 11.2.0.1 GROUP BY query with CURSOR_SHARING fails with ORA-1802
3461251 11.1.0.7, 11.2.0.1 V$SQL_SHARED_CURSOR shows all N with CURSOR_SHARING
7296258 10.2.0.5, 11.1.0.7.1 Intermittent Wrong results from literal replacement and remote objects
6163785 10.2.0.5, 11.1.0.7 Intermittent Wrong Results with dblink and cursor_sharing
8202234 Intermittent Wrong Results with dblink and cursor_sharing
5863277 10.2.0.4, 11.1.0.6 ORA-1008 from SQL on second run when cursor_sharing=similar/force
5762750 10.2.0.4, 11.1.0.6 ORA-907 when cursor_sharing is enabled
5476507 10.2.0.4, 11.1.0.6 OERI[15868] / OERI[15160] can occur with cursor sharing
5364819 10.2.0.4, 11.1.0.6 OERI[kkslpbp:1] when using literal replacement
5254759 10.2.0.4, 11.1.0.6 ORA-12801/ORA-1008 occurs on a parallel query with bind variables
5177766 10.2.0.4, 11.1.0.6 OERI[17059] with SESSION_CACHED_CURSORS
5155885 10.2.0.4, 11.1.0.6 OERI[kkslgbv0] with CURSOR_SHARING=similar
+ 5146740 10.2.0.4, 11.1.0.6 Wrong results with bind variables/CURSOR_SHARING
5082178 10.2.0.4, 11.1.0.6 Bind peeking may occur when it should not
5055175 10.2.0.3, 11.1.0.6 Dump [kkslpkp] using literal replacement with timezone literals – superceded
4867724 10.2.0.5, 11.1.0.6 Literal replacement limits column names to 30 characters
4698156 10.2.0.3, 11.1.0.6 ORA-12850 querying GV$ views when CURSOR_SHARING=FORCE
4607460 9.2.0.8, 10.2.0.3, 11.1.0.6 Dump [opipls] when CURSOR_SHARING = SIMILAR | FORCE
4513695 10.2.0.4, 11.1.0.6 Poor performance for SELECT with ROWNUM=1 with literal replacement
4458226 9.2.0.8, 10.1.0.5, 10.2.0.2, 11.1.0.6 High version count with cursor_sharing=force
4456646 10.2.0.2, 11.1.0.6 Dump (kxscod/memcmp) using literal replacement
4451881 9.2.0.8, 10.2.0.2, 11.1.0.6 OERI[kkslhsh1] from Insert as SELECT
4436832 10.2.0.4, 11.1.0.6 False ORA-979 “not a group by expression” with literal replacement
4359367 10.1.0.5, 10.2.0.2, 11.1.0.6 High version_count with cursor sharing
4254094 10.2.0.3, 11.1.0.6 OERI[qerrmObnd1][932] possible for queries over DB links
4202503 10.2.0.4, 11.1.0.6 Parse errors possible using CONTAINS with cursor_sharing=similar/force
4200541 10.2.0.3, 11.1.0.6 Parse error when CURSOR_SHARING=FORCE|SIMILAR with user binds
3880881 9.2.0.8, 10.2.0.2, 11.1.0.6 Dump (kkslMarkLiteralBinds) using cursor sharing
2837580 10.2.0.4, 11.1.0.6 Dump if SQL has > ~35000 literals
13349665 10.2.0.5.6 ORA-600 [kkslmtl-valnotfound] with fix for bug 9767674 (eg After applying PSU 10.2.0.5.5)
3980673 9.2.0.8, 10.1.0.5, 10.2.0.1 Literal replacement may dump (kkslpbp) if user binds have names like SYS_B_xx
3842253 9.2.0.7, 10.1.0.4, 10.2.0.1 Dump (kgghash) using literal replacement
3819834 9.2.0.7, 10.1.0.4, 10.2.0.1 Dump (kkslflb) with literal replacement
3818541 9.2.0.7, 10.1.0.4, 10.2.0.1 CURSOR_SHARING=force does not work if SQL has an NVL or DECODE predicate
3737955 10.1.0.4, 10.2.0.1, 9.2.0.7 Long parse times for long inlists / many AND/OR terms
3668572 9.2.0.6, 10.1.0.4, 10.2.0.1 ORA-979 when CURSOR_SHARING = force|similar with inline view and GROUP BY
3645694 10.1.0.5, 10.2.0.1 Poor plan from SQL with literal replacement and LIKE .. ESCAPE ..
3406977 9.2.0.6, 10.1.0.3, 10.2.0.1 High version count in V$SQL due to binds marked as non-data with CURSOR_SHARING=FORCE
3148830 9.2.0.6, 10.1.0.3, 10.2.0.1 Select over database link fails with OERI[opixmp-7] if CURSOR_SHARING=similar
3128363 9.2.0.6, 10.1.0.4 OCIAttrGet returns wrong size when CURSOR_SHARING=SIMILAR or FORCE
3007574 9.2.0.5 OERI:15212 using CURSOR_SHARING=FORCE with database links
5526018 Dump [kkslpbp] / OERI[kkslpbp:1] with materialized view and literal replacement
3132071 9.2.0.6, 10.1.0.2 Wrong results possible with CURSOR_SHARING=FORCE|SIMILAR
3045623 9.2.0.5, 10.1.0.2 OERI[kkslhsh1] possible with CURSOR_SHARING=FORCE
2958096 9.2.0.5, 10.1.0.2 Using XMLType / objects over DBLINKS with CURSOR_SHARING may dump
2843601 9.2.0.5, 10.1.0.2 DML across a dblink fails with ORA-24370 if cursor_sharing is enabled
2643579 9.2.0.4, 10.1.0.2 ORA-7445 [kxspoac] using CURSOR_SHARING=FORCE (or similar)
2620541 9.2.0.5, 10.1.0.2 ORA-7445 [KXSPOAC] can occur on DML using CURSOR_SHARING
2508702 9.2.0.3, 10.1.0.2 ORA-7445 [KKSLHIB] with CURSOR_SHARING=FORCE with INTO clause
2474192 9.2.0.3, 10.1.0.2 OERI:[OPIBND1] on CREATE of TEXT INDEX with CURSOR_SHARING
2442125 9.2.0.3, 10.1.0.2 Wrong results from PQ which compares a CHAR column to a BIND variable
2308292 9.0.1.4, 9.2.0.2, 10.1.0.2 ORA-7445 [kkslhib] for INTO <bind> using CURSOR_SHARING and SESSION_CACHED_CURSORS
2273604 9.0.1.4, 9.2.0.2, 10.1.0.2 CURSOR_SHARING=FORCE may not share cursors using a BETWEEN clause with CBO
2052836 8.1.7.4, 9.0.1.4, 9.2.0.2, 10.1.0.2 Client dump possible using CALL statement with CURSOR_SHARING=FORCE
4197915 9.2.0.8 CAST( x as number(n)) fails in PQ slaves with ORA-1727 with cursor_sharing
3614299 9.2.0.6 Dump adding a subscriber with a rule containing literals with CURSOR_SHARING = FORCE|SIMILAR
2442097 9.2.0.2 INSERTS into USER-DEFINED TYPES with CURSOR_SHARING=FORCE does not share cursors
2237610 9.2.0.2 OERI:[kgskbwt1] / OERI:[kskbind1] possible using CURSOR_SHARING
2300719 9.0.1.4, 9.2.0.1 Spin possible in PRSGNT when CURSOR_SHARING=SIMILAR
2262665 8.1.7.4, 9.0.1.4, 9.2.0.1 ORA-7445 [kkslpbp] possible with CURSOR_SHARING=FORCE
2259787 9.0.1.4, 9.2.0.1 Dump possible in EXPCMPBND() with CURSOR_SHARING and GROUPING SETS
2154645 9.0.1.3, 9.2.0.1 Spin in KKSFBC possible when CURSOR_SHARING=FORCE
1947974 9.0.1.2, 9.2.0.1 False ORA-1008 possible using EXECUTE IMMEDIATE with CURSOR_SHARING=SIMILAR
1840199 9.0.1.2, 9.2.0.1 OERI:EVAGGSID-1 possible using user defined aggregates (UDAG) with CURSOR_SHARING=FORCE|SIMILAR
1782025 8.1.7.4, 9.2.0.1 Client may dump/error using DML RETURNING with CURSOR_SHARING=FORCE|SIMILAR
1777504 8.1.7.4, 9.0.1.4, 9.2.0.1 Hang possible with CURSOR_SHARING=FORCE (or SIMILAR in 9i)
1764925 9.0.1.4, 9.2.0.1 OERI:QCTSTC2O1 possible using CURSOR_SHARING = SIMILAR
2159152 8.1.7.4, 9.0.1.0 Cursors not shared with CURSOR_SHARING=FORCE if it has a transitive predicate
1538450 8.1.7.3, 9.0.1.0 Dump in KXSPOAC possible CURSOR_SHARING=FORCE set
1365873 8.1.7.2, 9.0.1.0 OERI:17182 / CGA corruption with CURSOR_SHARING=FORCE
1358871 8.1.7.3, 9.0.1.0 CURSOR_SHARING=FORCE may dump if first bind is a literal
984251 8.1.7.2, 9.0.1.0 CURSOR_SHARING=FORCE can cause ORA-29909 when using an ANCILLARY OPERATOR
2485018 OERI:[KKSLGOP1] from SQL issued over a DBLINK with CURSOR_SHARING
2221407 8.1.7.4 ORA-3106 / OERI:15212 using BINDS over DBLINK with CURSOR_SHARING=FORCE
1545473 8.1.7.2, 9.0.1.0 ORA-979 possible using CURSOR_SHARING=FORCE with GROUP BY & ORDER BY & LITERALS
1111796 8.1.7.0 Dump possible from CURSOR_SHARING=FORCE
984132 8.1.7.0 Literal in cursor expression can CORE DUMP with CURSOR_SHARING=FORCE
Filed Under: Oracle, Oracle 11g新特性 Tagged With: , ,

11g默认审计选项

2011/06/29 by 1 Comment

11g默认启用强大的审计选项，AUDIT_TRAIL参数的缺省值为DB，这意为着审计数据将记录在数据库中的AUD$审计字典基表上。Oracle官方宣称默认启用的审计日志不会对绝大多数产品数据库的性能带来过大的负面影响，同时Oracle公司还推荐使用基于OS文件的审计日志记录方式(OS audit trail files)。

注意因为在11g中CREATE SESSION将被作为受审计的权限来被记录，因此当SYSTEM表空间因磁盘空间而无法扩展时将导致这部分审计记录无法生成，这将最终导致普通用户的新会话将无法正常创建，普通用户将无法登陆数据库。在这种场景中仍可以使用SYSDBA身份的用户创建会话，在将审计数据合适备份后删除一部分记录，或者干脆TRUNCATE AUD$都可以解决上述问题。

当AUDIT_TRAIL设置为OS时，审计记录文件将在AUDIT_FILE_DEST参数所指定的目录中生成。全部这些文件均可以随时被删除或复制。

注意在默认情况下会以AUTOEXTEND ON自动扩展选项创建SYSTEM表空间，因此系统表空间在必要情况下还是会自动增长的，我们所需注意的是磁盘上的剩余空间是否能够满足其增长需求，以及数据文件扩展的上限，对于普通的8k smallfile表空间而言单个数据文件的最大尺寸是32G。

以下权限将对所有用户审计:


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn


SQL> select privilege,success,failure from dba_priv_audit_opts;

PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------
CREATE EXTERNAL JOB                      BY ACCESS  BY ACCESS
CREATE ANY JOB                           BY ACCESS  BY ACCESS
GRANT ANY OBJECT PRIVILEGE               BY ACCESS  BY ACCESS
EXEMPT ACCESS POLICY                     BY ACCESS  BY ACCESS
CREATE ANY LIBRARY                       BY ACCESS  BY ACCESS
GRANT ANY PRIVILEGE                      BY ACCESS  BY ACCESS
DROP PROFILE                             BY ACCESS  BY ACCESS
ALTER PROFILE                            BY ACCESS  BY ACCESS
DROP ANY PROCEDURE                       BY ACCESS  BY ACCESS
ALTER ANY PROCEDURE                      BY ACCESS  BY ACCESS
CREATE ANY PROCEDURE                     BY ACCESS  BY ACCESS

PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER DATABASE                           BY ACCESS  BY ACCESS
GRANT ANY ROLE                           BY ACCESS  BY ACCESS
CREATE PUBLIC DATABASE LINK              BY ACCESS  BY ACCESS
DROP ANY TABLE                           BY ACCESS  BY ACCESS
ALTER ANY TABLE                          BY ACCESS  BY ACCESS
CREATE ANY TABLE                         BY ACCESS  BY ACCESS
DROP USER                                BY ACCESS  BY ACCESS
ALTER USER                               BY ACCESS  BY ACCESS
CREATE USER                              BY ACCESS  BY ACCESS
CREATE SESSION                           BY ACCESS  BY ACCESS
AUDIT SYSTEM                             BY ACCESS  BY ACCESS

PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER SYSTEM                             BY ACCESS  BY ACCESS

23 rows selected.

以下语句也将对所有用户审计:

SQL> select audit_option,success,failure from dba_stmt_audit_opts;

AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER SYSTEM                             BY ACCESS  BY ACCESS
SYSTEM AUDIT                             BY ACCESS  BY ACCESS
CREATE SESSION                           BY ACCESS  BY ACCESS
CREATE USER                              BY ACCESS  BY ACCESS
ALTER USER                               BY ACCESS  BY ACCESS
DROP USER                                BY ACCESS  BY ACCESS
PUBLIC SYNONYM                           BY ACCESS  BY ACCESS
DATABASE LINK                            BY ACCESS  BY ACCESS
ROLE                                     BY ACCESS  BY ACCESS
PROFILE                                  BY ACCESS  BY ACCESS
CREATE ANY TABLE                         BY ACCESS  BY ACCESS

AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER ANY TABLE                          BY ACCESS  BY ACCESS
DROP ANY TABLE                           BY ACCESS  BY ACCESS
CREATE PUBLIC DATABASE LINK              BY ACCESS  BY ACCESS
GRANT ANY ROLE                           BY ACCESS  BY ACCESS
SYSTEM GRANT                             BY ACCESS  BY ACCESS
ALTER DATABASE                           BY ACCESS  BY ACCESS
CREATE ANY PROCEDURE                     BY ACCESS  BY ACCESS
ALTER ANY PROCEDURE                      BY ACCESS  BY ACCESS
DROP ANY PROCEDURE                       BY ACCESS  BY ACCESS
ALTER PROFILE                            BY ACCESS  BY ACCESS
DROP PROFILE                             BY ACCESS  BY ACCESS

AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
GRANT ANY PRIVILEGE                      BY ACCESS  BY ACCESS
CREATE ANY LIBRARY                       BY ACCESS  BY ACCESS
EXEMPT ACCESS POLICY                     BY ACCESS  BY ACCESS
GRANT ANY OBJECT PRIVILEGE               BY ACCESS  BY ACCESS
CREATE ANY JOB                           BY ACCESS  BY ACCESS
CREATE EXTERNAL JOB                      BY ACCESS  BY ACCESS

28 rows selected.

当前数据库中的现有的审计记录:


SQL> select action_name,count(*) from dba_audit_trail group by action_name;

ACTION_NAME                    COUNT(*)
---------------------------- ----------
LOGOFF BY CLEANUP                    40
LOGON                               460
LOGOFF                              377
ALTER USER                            2
SYSTEM GRANT                         12
ALTER SYSTEM                         10
CREATE PUBLIC SYNONYM                 5
ALTER DATABASE                        2
CREATE DATABASE LINK                  1
DROP PUBLIC SYNONYM                   5

10 rows selected.
Filed Under: Oracle, Oracle 11g新特性 Tagged With: , , ,

11.2.0.2 asmcmd lsdg show incorrect diskgroup number

2011/06/28 by Leave a Comment

今天在给ASM扩磁盘组的时候发现11.2.0.2上asmcmd中lsdg命令所显示的磁盘组数不正确，现象如下:

Node A lsdg正常:
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576    142976    50596                0           50596              0             Y  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     51200    50742                0           50742              0             N  FRA/

Node B lsdg仅显示DATA磁盘组
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576    142976    50596                0           50596              0             Y  DATA/

从Node B节点上ASM实例的动态视图查询显示正常:

SQL> select name from v$asm_diskgroup;

NAME
------------------------------
DATA
FRA


ASM diag info:
SQL> select sysdate "Date and Time" from dual;

Date and Time
--------------------
28-JUN-2011 23:30:27

SQL> 
SQL> select * from v$asm_diskgroup order by 1;
select * from v$asm_disk order by 1, 2, 3;
select * from gv$asm_operation order by 1;
select * from v$version where banner like '%Database%' order by 1;
select * from gv$asm_client order by 1;

prompt

prompt ASM Disk Groups
prompt ===============

select group_number  "Group"
,      name          "Group Name"
,      state         "State"
,      type          "Type"
,      total_mb/1024 "Total GB"
,      free_mb/1024  "Free GB"
from   v$asm_diskgroup
/

prompt
prompt ASM Disks
prompt ==============

col "Group"          form 999
col "Disk"           form 999
col "Header"         form a9
col "Mode"           form a8

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE           TYPE      TOTAL_MB    FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY                                                DATABASE_COMPATIBILITY                                       V
------------ ------------------------------ ----------- ---------- -------------------- --------------- ------- ---------- ---------- ----------- ------------ ----------------------- -------------- ------------- ------------------------------------------------------------ ------------------------------------------------------------ -
           0 FRA                                      0       4096                    0 DISMOUNTED                       0          0           0            0              0               0             0 0.0.0.0.0                                                    0.0.0.0.0                                                    N
           1 DATA                                   512       4096              1048576 MOUNTED         EXTERN      142976      50596           0        92380              0           50596             0 11.2.0.0.0                                                   10.1.0.0.0                                                   Y

SQL> col "Redundancy"     form a10
col "Failure Group"  form a10
col "Path"           form a19

 select group_number  "Group"
,      disk_number   "Disk"
,      header_status "Header"

GROUP_NUMBER DISK_NUMBER COMPOUND_INDEX INCARNATION MOUNT_S HEADER_STATU MODE_ST STATE           REDUNDA LIBRARY                                                              OS_MB   TOTAL_MB    FREE_MB HOT_USED_MB COLD_USED_MB NAME                    FAILGROUP                      LABEL
------------ ----------- -------------- ----------- ------- ------------ ------- --------------- ------- ---------------------------------------------------------------- ---------- ---------- ---------- ----------- ------------ ------------------------------ ------------------------------ -------------------------------
PATH                                                                                                                                                                    UDID                                                              PRODUCT                          CREATE_DATE          MOUNT_DATE           REPAIR_TIMER      READS    WRITES  READ_ERRS WRITE_ERRS  READ_TIME WRITE_TIME BYTES_READ BYTES_WRITTEN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------- -------------------- -------------------- ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------
P HASH_VALUE  HOT_READS HOT_WRITES HOT_BYTES_READ HOT_BYTES_WRITTEN COLD_READS COLD_WRITES COLD_BYTES_READ COLD_BYTES_WRITTEN V SECTOR_SIZE FAILGRO
- ---------- ---------- ---------- -------------- ----------------- ---------- ----------- --------------- ------------------ - ----------- -------
           0           0              0  3915932260 CLOSED  MEMBER       ONLINE  NORMAL          UNKNOWN System                                                               51200           0          0           0            0
/dev/raw/raw8                                                                                                                                                             28-JUN-2011 17:08:16 28-JUN-2011 17:08:25             0
           0                                                                                                                  N         512 REGULAR

           1           0       16777216  3915932274 CACHED  MEMBER       ONLINE  NORMAL          UNKNOWN System                                                        8096        8096       2827           0         5269 DATA_0000                      DATA_0000
/dev/raw/raw1                                                                                                                                                             29-MAR-2011 22:39:53 31-MAY-2011 06:42:41             0         26          1          0          0    .948212    .001678    1118208          4096
  1452020686          0          0              0                 0          0           0               0                  0 Y         512 REGULAR

           1           1       16777217  3915932273 CACHED  MEMBER       ONLINE  NORMAL          UNKNOWN System                                                        8096        8096       2861           0         5235 DATA_0001                      DATA_0001
/dev/raw/raw2                                                                                                                                                             29-MAR-2011 22:39:53 31-MAY-2011 06:42:41             0         15          0          0          0    .868808          0    1105920             0
   559735068          0          0              0                 0          0           0               0                  0 N         512 REGULAR

           1           2       16777218  3915932272 CACHED  MEMBER       ONLINE  NORMAL          UNKNOWN System                                                        8096        8096       2859           0         5237 DATA_0002                      DATA_0002
/dev/raw/raw3                                                                                                                                                             29-MAR-2011 23:36:28 31-MAY-2011 06:42:41             0         14          0          0          0    .738089          0      57344             0
  2179807744          0          0              0                 0          0           0               0                  0 N         512 REGULAR

           1           4       16777220  3915932270 CACHED  MEMBER       ONLINE  NORMAL          UNKNOWN System                                                        8192        8192       2896           0         5296 DATA_0004                      DATA_0004
/dev/raw/raw5                                                                                                                                                             21-APR-2011 00:57:45 31-MAY-2011 06:42:41             0         14          0          0          0    .738083          0      57344             0
  3104258115          0          0              0                 0          0           0               0                  0 N         512 REGULAR

           1           5       16777221  3915932271 CACHED  MEMBER       ONLINE  NORMAL          UNKNOWN System                                                        8096        8096       2856           0         5240 DATA_0005                      DATA_0005
/dev/raw/raw4                                                                                                                                                             30-MAY-2011 20:36:59 31-MAY-2011 06:42:41             0         15          0          0          0    .911934          0    1105920             0
  2075303794          0          0              0                 0          0           0               0                  0 N         512 REGULAR

           1           7       16777223  3915932269 CACHED  MEMBER       ONLINE  NORMAL          UNKNOWN System                                                               51200       51200      18149           0        33051 DATA_0007                      DATA_0007
/dev/raw/raw6                                                                                                                                                             28-JUN-2011 17:09:22 28-JUN-2011 17:09:22             0         39          5          0          0    .998594   6.022413     159744         20480
,      mode_status   "Mode"
  2166785847          0          0              0                 0         18           0           73728                  0 N         512 REGULAR

           1           8       16777224  3915932268 CACHED  MEMBER       ONLINE  NORMAL          UNKNOWN System                                                               51200       51200      18148           0        33052 DATA_0008                      DATA_0008
/dev/raw/raw7                                                                                                                                                             28-JUN-2011 17:09:58 28-JUN-2011 17:09:58             0         25          0          0          0    .760427          0     102400             0
  1385034587          0          0              0                 0          6           0           24576                  0 N         512 REGULAR


8 rows selected.

SQL> ,      state         "State"
,      redundancy    "Redundancy"
,      total_mb      "Total MB"
,      free_mb       "Free MB"
,      name          "Disk Name"
,      failgroup     "Failure Group"
,      path          "Path"
from   v$asm_disk
order by group_number
,        disk_number

/

prompt
prompt Instances currently accessing these diskgroups
prompt ==============================================

no rows selected

SQL> 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

SQL> 
select c.group_number  "Group"
,      g.name          "Group Name"
,      c.instance_name "Instance"

   INST_ID GROUP_NUMBER INSTANCE_NAME                                                    DB_NAME  STATUS       SOFTWARE_VERSION                                            COMPATIBLE_VERSION
---------- ------------ ---------------------------------------------------------------- -------- ------------ ------------------------------------------------------------ ------------------------------------------------------------
         1            1 +ASM1                                                            +ASM     CONNECTED    11.2.0.2.0                                                  11.2.0.2.0
         1            2 PROD1                                                            PROD     CONNECTED    11.2.0.2.0                                                  11.2.0.0.0
         1            1 PROD1                                                            PROD     CONNECTED    11.2.0.2.0                                                  11.2.0.0.0
         2            1 +ASM2                                                            +ASM     CONNECTED    11.2.0.2.0                                                  11.2.0.2.0

SQL> SQL> 
SQL> SQL> ASM Disk Groups
SQL> ===============
SQL> SQL>   2    3    4    5    6    7    8  from   v$asm_client c
,      v$asm_diskgroup g
where  g.group_number=c.group_number
/


     Group Group Name                State           Type      Total GB Free GB
---------- ------------------------- --------------- ------- ---------- -------
         1 DATA                      MOUNTED         EXTERN     139.625      49
         0 FRA                       DISMOUNTED                       0       0

SQL> SQL> 
SQL> ASM Disks
SQL> ==============
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3  prompt
  4    5    6    7    8    9   10   11   12   13   14   15  SQL> prompt Report the Percentage of Imbalance in all Mounted Diskgroups
prompt ==============================================
select dfail, count(dfail) from
(
select disk, count(failgroup) as dfail
from x$kfdpartner, v$asm_disk where
number_kfdpartner=disk_number and grp=group_number

Group Disk Header    Mode     State           Redundancy   Total MB    Free MB Disk Name                      Failure Gr Path
----- ---- --------- -------- --------------- ---------- ---------- ---------- ------------------------------ ---------- -------------------
    0    0 MEMBER    ONLINE   NORMAL          UNKNOWN             0          0                                           /dev/raw/raw8
    1    0 MEMBER    ONLINE   NORMAL          UNKNOWN          8096       2827 DATA_0000                      DATA_0000  /dev/raw/raw1
    1    1 MEMBER    ONLINE   NORMAL          UNKNOWN          8096       2861 DATA_0001                      DATA_0001  /dev/raw/raw2
    1    2 MEMBER    ONLINE   NORMAL          UNKNOWN          8096       2859 DATA_0002                      DATA_0002  /dev/raw/raw3
    1    4 MEMBER    ONLINE   NORMAL          UNKNOWN          8192       2896 DATA_0004                      DATA_0004  /dev/raw/raw5
    1    5 MEMBER    ONLINE   NORMAL          UNKNOWN          8096       2856 DATA_0005                      DATA_0005  /dev/raw/raw4
    1    7 MEMBER    ONLINE   NORMAL          UNKNOWN         51200      18149 DATA_0007                      DATA_0007  /dev/raw/raw6
    1    8 MEMBER    ONLINE   NORMAL          UNKNOWN         51200      18148 DATA_0008                      DATA_0008  /dev/raw/raw7

8 rows selected.

SQL> SQL> 
SQL> Instances currently accessing these diskgroups
SQL> ==============================================
SQL> SQL>   2    3    4    5    6    7  group by disk, failgroup
)
group by dfail; 

select g.name as "GROUP", d.name as "DISK", d.failgroup, fcnt, pcnt,
decode(pcnt - fcnt, 0, 'MUST', 'SHOULD') as action from

Group Group Name                Instance
----- ------------------------- ----------------------------------------------------------------
    1 DATA                      +ASM2

SQL> SQL> 
SQL> Report the Percentage of Imbalance in all Mounted Diskgroups
SQL> ==============================================
SQL>   2    3    4    5    6    7    8  (select gnum, DISK1, failgroup, count(failgroup) as fcnt from
(select gnum, DISK1
from
(
select d.group_number as gnum, disk as disk1,
count(distinct failgroup) as dfail
from x$kfdpartner, v$asm_disk_stat d where
number_kfdpartner=disk_number and grp=d.group_number

no rows selected

SQL> SQL>   2    3    4    5    6    7    8    9   10   11  and active_kfdpartner=1
 12  group by d.group_number, disk
 13  ), v$asm_disk_stat
 14  where dfail < 3
 15  and disk1=disk_number
 16  and gnum=group_number),
 17  x$kfdpartner, v$asm_disk_stat d where
 18  number_kfdpartner=disk_number and grp=d.group_number and grp=gnum
 19  and disk1=disk
 20  and active_kfdpartner=1
 21  group by gnum, disk1, failgroup),
 22  (select grp, disk, count(disk) as pcnt from x$kfdpartner where
 23  active_kfdpartner=1 group by grp, disk),
 24  v$asm_diskgroup_stat g, v$asm_disk_stat d
 25  where gnum=grp and gnum=g.group_number and gnum=d.group_number and
 26  disk=disk1 and disk=disk_number and
 27  ((fcnt = 1 and (pcnt - fcnt) > 3) or ((pcnt - fcnt) = 0))
 28  /

no rows selected

SQL> 
SQL> col TYPE form a15
SQL> col FILE_NUMBER form 9999 head FILE_NUM
SQL> col GROUP_NUMBER form 9999 head GR_NUM
SQL> col GB for 9999.99
SQL> 
SQL> select GROUP_NUMBER   ,
  2   FILE_NUMBER          ,
  3   COMPOUND_INDEX       ,
  4   INCARNATION          ,
  5   BLOCK_SIZE           ,
  6   BLOCKS               ,
  7   BYTES/1024/1024/1024 GB ,
  8   TYPE                 ,
  9   STRIPED              ,
 10   CREATION_DATE        ,
 11   MODIFICATION_DATE
 12  from v$asm_file
 13  where TYPE != 'ARCHIVELOG'
 14  /


GR_NUM FILE_NUM COMPOUND_INDEX INCARNATION BLOCK_SIZE     BLOCKS       GB TYPE            STRIPE CREATION_DATE        MODIFICATION_DATE
------ -------- -------------- ----------- ---------- ---------- -------- --------------- ------ -------------------- --------------------
     1      253       16777469   747096005        512          3      .00 ASMPARAMETERFIL COARSE 29-MAR-2011 22:40:05 29-MAR-2011 22:00:00
                                                                          E

     1      255       16777471   747096007       4096      66591      .25 OCRFILE         COARSE 29-MAR-2011 22:40:07 28-JUN-2011 19:00:00
     1      256       16777472   747100091       8192     116481      .89 DATAFILE        COARSE 29-MAR-2011 23:48:10 28-JUN-2011 18:00:00
     1      257       16777473   747100091       8192     122881      .94 DATAFILE        COARSE 29-MAR-2011 23:48:11 28-JUN-2011 18:00:00
     1      258       16777474   747100093       8192      78721      .60 DATAFILE        COARSE 29-MAR-2011 23:48:12 28-JUN-2011 18:00:00
     1      259       16777475   747100093       8192     883201     6.74 DATAFILE        COARSE 29-MAR-2011 23:48:12 28-JUN-2011 18:00:00
     1      260       16777476   747100215      16384       1545      .02 CONTROLFILE     FINE   29-MAR-2011 23:50:14 28-JUN-2011 19:00:00
     1      261       16777477   747100215      16384       1545      .02 CONTROLFILE     FINE   29-MAR-2011 23:50:14 28-JUN-2011 19:00:00
     1      262       16777478   747100217        512     102401      .05 ONLINELOG       COARSE 29-MAR-2011 23:50:16 28-JUN-2011 22:00:00
     1      263       16777479   747100219        512     102401      .05 ONLINELOG       COARSE 29-MAR-2011 23:50:18 28-JUN-2011 22:00:00
     1      264       16777480   747100219        512     102401      .05 ONLINELOG       COARSE 29-MAR-2011 23:50:19 28-JUN-2011 20:00:00
     1      265       16777481   747100223        512     102401      .05 ONLINELOG       COARSE 29-MAR-2011 23:50:22 28-JUN-2011 20:00:00
     1      266       16777482   747100227       8192    1889537    14.42 TEMPFILE        COARSE 29-MAR-2011 23:50:27 28-JUN-2011 23:00:00
     1      267       16777483   747100231       8192      44241      .34 DATAFILE        COARSE 29-MAR-2011 23:50:31 28-JUN-2011 19:00:00
     1      268       16777484   747100469       8192     144001     1.10 DATAFILE        COARSE 29-MAR-2011 23:54:29 28-JUN-2011 18:00:00
prompt
     1      269       16777485   747100547        512     102401      .05 ONLINELOG       COARSE 29-MAR-2011 23:55:47 28-JUN-2011 19:00:00
     1      270       16777486   747100549        512     102401      .05 ONLINELOG       COARSE 29-MAR-2011 23:55:48 28-JUN-2011 19:00:00
     1      271       16777487   747100549        512     102401      .05 ONLINELOG       COARSE 29-MAR-2011 23:55:49 28-JUN-2011 19:00:00
     1      272       16777488   747100553        512     102401      .05 ONLINELOG       COARSE 29-MAR-2011 23:55:52 28-JUN-2011 19:00:00
     1      273       16777489   747100553        512          9      .00 PARAMETERFILE   COARSE 29-MAR-2011 23:55:53 12-MAY-2011 17:00:00
prompt free ASM disks and their paths
     1      274       16777490   754587291       8192       2561      .02 DATAFILE        COARSE 23-JUN-2011 15:34:50 28-JUN-2011 18:00:00
     1      419       16777635   754281899       4096         91      .00 DUMPSET         COARSE 20-JUN-2011 02:44:58 20-JUN-2011 02:00:00
     1      657       16777873   755047049       8192     256001     1.95 TEMPFILE        COARSE 28-JUN-2011 23:17:28 28-JUN-2011 23:00:00
     1      658       16777874   755032881       8192    8171521    62.34 DATAFILE        COARSE 28-JUN-2011 19:21:20 28-JUN-2011 22:00:00

24 rows selected.

SQL> SQL> 
SQL> free ASM disks and their paths
SQL> prompt ===========================
===========================
SQL> select header_status , mode_status, path from V$asm_disk
  2  where header_status in ('FORMER','CANDIDATE')
  3  /

show parameter asm
show parameter size
show parameter proc
show parameter cluster
show parameter instance_type
show parameter instance_name

show parameter pfile

show sga

spool off
no rows selected

SQL> SQL> 
NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
asm_diskgroups                       string
asm_diskstring                       string          /dev/raw/raw*
asm_power_limit                      integer         10
asm_preferred_read_failure_groups    string
SQL> 
NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
db_cache_size                        big integer     0
large_pool_size                      big integer     12M
max_dump_file_size                   string          unlimited
sga_max_size                         big integer     272M
shared_pool_reserved_size            big integer     7969177
shared_pool_size                     big integer     0
sort_area_size                       integer         65536
workarea_size_policy                 string          AUTO
SQL> 
NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
processes                            integer         100
SQL> 
NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
cluster_database                     boolean         TRUE
cluster_database_instances           integer         4
cluster_interconnects                string
SQL> 
NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
instance_type                        string          asm
SQL> 
NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
instance_name                        string          +ASM2
SQL> SQL> 
NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
spfile                               string          +DATA/rh-cluster/asmparameterf
                                                     ile/registry.253.747096005
SQL> SQL> 
Total System Global Area  283930624 bytes
Fixed Size                  2225792 bytes
Variable Size             256539008 bytes
ASM Cache                  25165824 bytes

原来是因为lsdg只列出已经mount的diskgroup，因为在Node B上FRA磁盘组没有mount所以不被列出，我本来以为是Bug造成的。
感谢Liqin Zhang网友的指出。

Filed Under: Oracle, Oracle 11g新特性, Oracle ASM Tagged With: , ,

Oracle等待事件kfk:async disk IO

2011/06/11 by 1 Comment

kfk: async disk IO等待事件是ASM下异步的System I/O等待事件，kfk内核层面在disk_asynch_io=true时被激活。当rbal或其他ASM相关后台进程在维护ASM磁盘组时可能进入kfk: async disk IO等待。

SQL> col name for a20
SQL> col PARAMETER1 for a10
SQL> col PARAMETER2 for a10
SQL> col PARAMETER3 for a10
SQL> col WAIT_CLASS for a15

SQL> select name,parameter1,parameter2,parameter3,wait_class from v$event_name where name='kfk: async disk IO';

NAME                 PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS
-------------------- ---------- ---------- ---------- ---------------
kfk: async disk IO   count      intr       timeout    System I/O

SQL> select * from v$version;    

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select name,value from v$system_parameter where name in ('instance_type','asm_power_limit');

NAME                 VALUE
-------------------- ----------
instance_type        asm
asm_power_limit      10

SQL> conn / as sysasm
Connected.

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 8;
Statement processed.
SQL> alter diskgroup data check all;

Diskgroup altered.

SQL> oradebug event 10046 trace name context off;
Statement processed.

SQL> oradebug tracefile_name;
/s01/orabase/diag/asm/+asm/+ASM1/trace/+ASM1_ora_29405.trc

=====================trace=====================
PARSING IN CURSOR #140442102181424 len=30 dep=0 uid=0 oct=193 
lid=0 tim=1313673029551496 hv=2849532521 ad='6bd58b50' sqlid='ft5h7dunxhum9'
alter diskgroup data check all
END OF STMT
PARSE #140442102181424:c=1999,e=14171,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1313673029551493
WAIT #140442102181424: nam='Disk file operations I/O' ela= 573 FileOperation=2 fileno=0 filetype=15 obj#=-1 
WAIT #140442102181424: nam='Disk file operations I/O' ela= 33 FileOperation=2 fileno=0 filetype=15 obj#=-1 
WAIT #140442102181424: nam='Disk file operations I/O' ela= 29 FileOperation=2 fileno=0 filetype=15 obj#=-1 
WAIT #140442102181424: nam='kfk: async disk IO' ela= 941 count=1 intr=0 timeout=4294967295 obj#=-1

fdp_checkDsk(): 20
----- Abridged Call Stack Trace -----
ksedsts()+461<-kfdp_checkDsk()+476<-kfdCheck()+1649<-kfgCheck()+477<-kfxdrvAl
terOne()+5976<-kfxdrvAlter()+2287<-kfxdrvEntry()+1306<-opiexe()+20028<-opiosq
0()+3993<-kpooprx()+274<-kpoal8()+800<-opiodr()+910<-ttcpip()+2289<-opitsk()+
1670<-opiino()+966<-opiodr()+910
<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252<-main()+201<
-__libc_start_main()+244<-_start()+36
----- End of Abridged Call Stack Trace -----
WAIT #140442102181424: nam='rdbms ipc reply' ela= 1610 from_process=19 timeou
t=2147483647 p3=0 obj#=-1 tim=1313673029798048
kfdp_checkDsk(): 21
----- Abridged Call Stack Trace -----
ksedsts()+461<-kfdp_checkDsk()+476<-kfdCheck()+1649<-kfgCheck()+477<-kfxdrvAl
terOne()+5976<-kfxdrvAlter()+2287<-kfxdrvEntry()+1306<-opiexe()+20028<-opiosq
0()+3993<-kpooprx()+274<-kpoal8()+800<-opiodr()+910<-ttcpip()+2289<-opitsk()+
1670<-opiino()+966<-opiodr()+910
<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252<-main()+201<
-__libc_start_main()+244<-_start()+36
----- End of Abridged Call Stack Trace -----
WAIT #140442102181424: nam='rdbms ipc reply' ela= 1677 from_process=19 timeou
t=2147483647 p3=0 obj#=-1 tim=1313673029885645
kfdp_checkDsk(): 22
----- Abridged Call Stack Trace -----
ksedsts()+461<-kfdp_checkDsk()+476<-kfdCheck()+1649<-kfgCheck()+477<-kfxdrvAl
terOne()+5976<-kfxdrvAlter()+2287<-kfxdrvEntry()+1306<-opiexe()+20028<-opiosq
0()+3993<-kpooprx()+274<-kpoal8()+800<-opiodr()+910<-ttcpip()+2289<-opitsk()+
1670<-opiino()+966<-opiodr()+910
<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252<-main()+201<
-__libc_start_main()+244<-_start()+36
----- End of Abridged Call Stack Trace -----
WAIT #140442102181424: nam='rdbms ipc reply' ela= 1350 from_process=19 timeou
t=2147483647 p3=0 obj#=-1 tim=1313673029970397
kfdp_checkDsk(): 23
----- Abridged Call Stack Trace -----
kfdp_checkDsk(): 24
----- Abridged Call Stack Trace -----
ksedsts()+461<-kfdp_checkDsk()+476<-kfdCheck()+1649<-kfgCheck()+477<-kfxdrvAl
terOne()+5976<-kfxdrvAlter()+2287<-kfxdrvEntry()+1306<-opiexe()+20028<-opiosq
0()+3993<-kpooprx()+274<-kpoal8()+800<-opiodr()+910<-ttcpip()+2289<-opitsk()+
1670<-opiino()+966<-opiodr()+910
<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252<-main()+201<
-__libc_start_main()+244<-_start()+36
----- End of Abridged Call Stack Trace -----
Filed Under: Oracle, Oracle 11g新特性, Oracle ASM Tagged With: , , , ,

DataGuard与异构平台

2011/05/31 by 5 Comments

DataGuard对主备库异构平台的支持一直是让很多人纠结的问题，我们在学习Oracle数据卫士时必要优先阅读的官方文档是<Oracle Data Guard Concepts and Administration 10g Release 2>，在这个文档中给出了极为苛刻的硬件环境限制条件:

All members of a Data Guard configuration must run an Oracle image that is built for the same platform.

For example, this means a Data Guard configuration with a primary database on a 32-bit Linux on Intel system
can have a standby database that is configured on a 32-bit Linux on Intel system. However, a primary database
on a 64-bit HP-UX system can also be configured with a standby database on a 32-bit HP-UX system,
as long as both servers are running 32-bit images.

以上主要提出了2点要求：即平台(platform)和字长(word size)都必须一致，注意这里不管是physical standby还是logical standby都要求遵守。

官方文档这样撰写的原因是Oracle并不推荐用户在异构平台上搭建DataGuard，如果有用户大量地部署异构平台上的DataGuard可能给后续的服务和支持带来麻烦；所以除非是找不到其他可用的硬件了，否则不推荐采用异构平台搭建的DataGuard环境来提供高可用性。

实际情况是在10g中已经有少数几个异构平台组合可以兼容physical standby或logical standby，而在11g中更增加了对physical standby支持的几种异构平台组合(As of Oracle Database 11g Data Guard provides increased flexibility for Data Guard configurations in which the primary and standby systems may have different CPU architectures, operating systems (for example, Windows & Linux), operating system binaries (32-bit/64-bit), or Oracle database binaries (32-bit/64-bit). For specific information about mixed-platform support, see the My Oracle Support note 413484.1)。

具体10g/11g DataGuard可以利用的异构平台组合，见下列图表:

10g
 Physical Standby Logical Standby
Heterogeneous
Support		 No Win<->Linux Only
Different
Word-size
(32 / 64 bit)		 Win32<->Win64
Linux32<->Linux64		 Win32->Win64  

Linux32->Linux64 (1 way only)
Heterogeneous AND Word-size No No

 

11g
 Physical Standby Logical Standby
Heterogeneous
Support		 Win<–>Linux
Solaris <–>AIX
Solaris<–>Linux		 Win<->Linux Only
Different
Word-size
(32 / 64 bit)		 Win32<->Win64
Linux32<->Linux64		 Win32->Win64 Linux32->Linux64 (1 way only)
Heterogeneous AND Word-size Win32<->Linux64 Win32->Linux64
(1 way only)

 

而在metalink文档<Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration [ID 413484.1]>和<Data Guard Support for Heterogeneous Primary and Logical Standbys in Same Data Guard Configuration [ID 1085687.1]>中列出了更为详尽的平台间的兼容信息，在这里一并引用:

Physical Standbys

In addition to general support when using the same Oracle platform, Data Guard Redo Apply (physical standby) can support specific mixed Oracle Platform combinations.  Oracle Platform IDs, platform names, and which combinations of platform ID(s) that can be combined to form a supported Data Guard configuration using Redo Apply are listed in the table below.  Platform combinations not listed in the table below are not supported using Data Guard Redo Apply.

Table Notes

  1. Prior to Data Guard 11g, the Data Guard Broker did not support different word-size in the same Data Guard configuration, thus requiring management from the SQL*Plus command line for mixed word-size Data Guard configurations.  This restriction is lifted from Data Guard 11g onward.
  2. Both primary and standby databases must be set at the same compatibility mode as the minimum release (if specified) in the table below.
  3. A standby database cannot be open read-only in any environment that has binary-level PL/SQL-related incompatibilities between primary and standby databases.  Support Note 414043.1 is referenced in the table below for any platform combinations where this is the case (the note provides instructions for eliminating incompatibilities post role transition).  It is possible to access a standby database in such environments in Oracle Database 11g by temporarily converting it to a Snapshot Standby database, or in Oracle Database 10g by opening the standby read/write as described in the Data Guard 10g Concepts and Administration guide: Using a Physical Standby Database for Read/Write Testing and Reporting. Both procedures require following the steps in note 414043.1 before making the database available to users.
  4. Please be sure to read Support Notes when referenced in the table below.
  5. RMAN generally supports instantiation of a physical standby database for the supported platform combinations. Please see Support Note 1079563.1 for details.
  6. Platforms in a supported combination may operate in either the primary or standby role.
  7. Enterprise Manager can not be used for standby database creation or other administrative functions in any configuration where PLATFORM_IDs are not identical. Oracle recommends using the Data Guard Broker command line interface (DGMGRL) to administer mixed platform combinations from Oracle Database 11g onward and SQL*Plus command line for configurations that pre-date Oracle Database 11g.
PLATFORM_ID PLATFORM_NAME
Release name		 PLATFORM_IDs supported within the same Data Guard configuration when using Data Guard Redo Apply (Physical Standby)
2 Solaris[tm] OE (64-bit)
Solaris Operating System (SPARC) (64-bit)		 2
6 – Oracle 11.2.0.2 onward, primary database must be non-RAC and non-TDE
3 HP-UX (64-bit)
HP-UX PA-RISC		 3
4 – Oracle 10g onward, see Support Notes 395982.1 and 414043.1
4 HP-UX IA (64-bit)
HP-UX Itanium		 4
3 – Oracle 10g onward, see Support Notes 395982.1 and 414043.1
5 HP Tru64 UNIX
HP Tru64 UNIX		 5
6 IBM AIX on POWER Systems (64-bit) 2 – Oracle 11.2.0.2 onward, primary database must be non-RAC and non-TDE
6
7 Microsoft Windows (32-bit)
Microsoft Windows (x86)		 7
8, 12  – Oracle 10g onward, see Support Note 414043.1
10 – Oracle 11g onward
11, 13 – Oracle 11g onward, see Support Note 414043.1
8 Microsoft Windows IA (64-bit)
Microsoft Windows (64-bit Itanium)		 7 – Oracle 10g onward, see Support Note 414043.1
8
12 – Oracle 10g onward
11, 13 – Oracle 11g onward
9 IBM zSeries Based Linux
z/Linux		 9
18 (64-bit zSeries only)
10 Linux (32-bit)
Linux x86		 7 – Oracle 11g onward
10
11, 13 – Oracle 10g onward, see Support Note 414043.1
11 Linux IA (64-bit)
Linux Itanium		 10 – Oracle 10g onward, see Support Note 414043.1
11
13 – Oracle 10g onward
7 – Oracle 11g onward, see Support Note 414043.1
8, 12 – Oracle 11g onward
12 Microsoft Windows 64-bit for AMD
Microsoft Windows (x86-64)		 7 – Oracle 10g onward, see Support Note 414043.1
8 – Oracle 10g onward
12
11, 13 – Oracle 11g onward
13 Linux 64-bit for AMD
Linux x86-64		 7 – Oracle 11g onward, see Support Note 414043.1
10 – Oracle 10g onward, see Support Note 414043.1
11 – Oracle 10g onward
8, 12 – Oracle 11g onward
13
20 – Oracle 11g onward
15 HP Open VMS
HP OpenVMS Alpha
HP IA OpenVMS
OpenVMS Itanium		 15
16 Apple Mac OS
Mac OS X Server		 16
17 Solaris Operating System (x86)
Solaris Operating System (x86)		 17
20 – Oracle 10g onward, see Support Note 414043.1
18 IBM Power Based Linux
Linux on Power		 9 (64-bit zSeries only)
18
20 Solaris Operating System (AMD64)
Solaris Operating System (x86-64)		 13 – Oracle 11g onward
17 – Oracle 10g onward, see Support Note 414043.1
20

Logical Standby

In addition to general support when using the same Oracle platform, Data Guard SQL Apply (logical standby) can support specific mixed Oracle Platform combinations as of Oracle Database 11g.  Oracle Platform IDs, platform names, and which combinations of platform ID(s) that can be combined to form a supported Data Guard configuration using SQL Apply are listed in the table below.  Platform combinations not listed in the table below are not supported using Data Guard SQL Apply.

Table Notes

  1. All mixed platform combinations for SQL Apply in the table below are supported from Oracle Database 11g onward.
  2. Prior to Data Guard Broker 11g, the Data Guard Broker did not support different word-size in the same Data Guard configuration, thus requiring all management from the SQL*Plus command line.  This restriction is lifted from Data Guard 11g onward.
  3. Both primary and standby databases must be set at the same compatibility mode as the minimum release (if specified) in the table below.
  4. Please be sure to read Support Notes when referenced in the table below.
  5. RMAN generally supports instantiation of a physical standby database for the supported platform combinations. Please see Support Note 1079563.1 for details.
  6. Platforms in a supported combination may operate in either the primary or standby role unless otherwise specified.
  7. Enterprise Manager can not be used for standby database creation or other administrative functions in any configuration where PLATFORM_IDs are not identical. Oracle recommends using the Data Guard Broker command line interface (DGMGRL) to administer mixed platform combinations from Oracle Database 11g onward and SQL*Plus command line for configurations that pre-date Oracle Database 11g.
PLATFORM_ID PLATFORM_NAME
Release name		 PLATFORM_IDs supported within the same Data Guard configuration when using Data Guard SQL Apply (Logical Standby)
2 Solaris[tm] OE (64-bit)
Solaris Operating System (SPARC) (64-bit)		 2
3 HP-UX (64-bit)
HP-UX PA-RISC		 3, 4
4 HP-UX IA (64-bit)
HP-UX Itanium		 3, 4
5 HP Tru64 UNIX
HP Tru64 UNIX		 5
6 AIX-Based Systems (64-bit)
AIX5L		 6
7 Microsoft Windows (32-bit)
Microsoft Windows (x86)		 7, 10
8, 12 – Replication can only occur from a 32-bit primary to a 64-bit standby, once a role transition has promoted the 64-bit system to the primary role, the original 32-bit primary is not supported as a standby database.
8 Microsoft Windows IA (64-bit)
Microsoft Windows (64-bit Itanium)		 7 – Replication can only occur from a 32-bit primary to a 64-bit standby, once a role transition has promoted the 64-bit system to the primary role, the original 32-bit primary is not supported as a standby database.
8, 11, 12, 13
9 IBM zSeries Based Linux
z/Linux		 9
10 Linux (32-bit)
Linux x86		 7, 10
11, 13 – Replication can only occur from a 32-bit primary to a 64-bit standby, once a role transition has promoted the 64-bit system to the primary role, the original 32-bit primary is not supported as a standby database.
11 Linux IA (64-bit)
Linux Itanium		 10 – Replication can only occur from a 32-bit primary to a 64-bit standby, once a role transition has promoted the 64-bit system to the primary role, the original 32-bit primary is not supported as a standby database.
8, 11, 13
12 Microsoft Windows 64-bit for AMD
Microsoft Windows (x86-64)		 7 – from Oracle 11g onward.  Replication can only occur from a 32-bit primary to a 64-bit standby, once a role transition has promoted the 64-bit system to the primary role, the original 32-bit primary is not supported as a standby database.
8, 12
13 Linux 64-bit for AMD
Linux x86-64		 10 – Replication can only occur from a 32-bit primary to a 64-bit standby, once a role transition has promoted the 64-bit system to the primary role, the original 32-bit primary is not supported as a standby database.
8, 11, 13
15 HP Open VMS
HP OpenVMS Alpha
HP IA OpenVMS
OpenVMS Itanium		 15
16 Apple Mac OS
Mac OS X Server		 16
17 Solaris Operating System (x86)
Solaris Operating System (x86)		 17
18 IBM Power Based Linux
Linux on Power		 18
20 Solaris Operating System (AMD64)
Solaris Operating System (x86-64)		 20

Reference:

<Cross Platform Database Migrations>-Owen Ireland

<Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration [ID 413484.1]>

<Data Guard Support for Heterogeneous Primary and Logical Standbys in Same Data Guard Configuration [ID 1085687.1]>


Filed Under: Oracle, Oracle 11g新特性 Tagged With: , , , ,

沪ICP备14014813号-2

沪公网安备 31010802001379号