Thursday, August 7, 2014

Goldengate Filter data based on Before image and and after Value in Extract.

Links to this post
Below is example to filter data based on Existing value or before image in column and New updated value .

This is used in 11.2 OGG. In OGG 12C use @BEFORE to Get before image.

So X_COMMAND will appear as "REFRESH" If status change from Inactive to Active.

X_COMMAND=@IF (((@STRFIND(before.STATUS, "Inactive") > 0 ) and (@STRFIND(STATUS, "Active") >0 )) > 0 , "REFRESH", @CASE(@GETENV("GGHEADER", "OPTYPE"), "SQL COMPUPDATE", "UPDATE", "PK UPDATE", "UPDATE"))


You can also use this code for compare Exist and new value.
((@STRFIND(before.STATUS, "Inactive") > 0 ) and (@STRFIND(STATUS, "Active") >0 )) > 0 . 

Monday, June 30, 2014

Goldengate Filter data using Lookup Table and SQLEXEC .

Links to this post

This is true that You can use multiple SQLEXEC in Goldengate.

Below code shows How to filter data from SOURCE Table using Lookup table and get filter value dynamically .

This code check If Old value ORG_ID for source_table is exist in LOOKUP_PARAMETER_TAB.
to filter using lookup table ,I used FILTER and get lookup value from SQLEXEC.if value match it execute another SQLEXEC where it
execute stored procedure and Get dynamic parameters using @GETVAL function and pass to Procedure.
TABLE "INV"."SOURCE_TABLE",FILTER (ORG_ID = @GETVAL(lookup.XYZ)),SQLEXEC (ID lookup, QUERY "select distinct (MASTER_ORG_ID) XYZ from ABC.LOOKUP_PARAMETER_TAB mp  where mp.MASTER_ORG_ID= :p_organization_id_X", PARAMS (p_organization_id_X=ORG_ID),BEFOREFILTER , TRACE),SQLEXEC (ID ABC.PACKAGE_TO_EXEC.PROCEDURE_TO_EXEC,SPNAME ABC.PACKAGE_TO_EXEC.PROCEDURE_TO_EXEC,PARAMS (P_DBNAME=@GETENV("DBENVIRONMENT","DBNAME"),P_TRANSACTION_ID=@GETENV("TRANSACTION", "XID"),P_COMMAND=@CASE(@GETENV("GGHEADER", "OPTYPE"), "INSERT", "INSERT", "SQL COMPUPDATE", "UPDATE", "PK UPDATE", "UPDATE","DELETE","DELETE"),P_INVENT_ITEM_ID=INVENT_ITEM_ID,P_ORGANIZATION_ID=ORG_ID),AFTERFILTER, TRACE);

Tuesday, June 17, 2014

Goldengate Stored procedure parameter in SQLEXEC with GETENV

Links to this post

Here GG is extracting ABC.DUMMY table and while It does that, It also does following.

- check Where ORGANIZATION_ID =83,
- if true above execute XYZ.DUMMY_PKG (PACKAGE) to DUMMY_PROCEDURE_NAME (procedure),GG has bug for fully qualify Name of schema and Packagename as prefix.
-while executing above Procedure , It gets value run-time and pass value to procedure.

~database Name using GETENV("DBENVIRONMENT","DBNAME").
~Object Name
~Inventory_item_id (comes within procedure )
~Get Command type INSERT/UPDATE/DELETE ,again case function wrote to convert SQL COMPUPDATE / PKUPDATE to UPDATE string.
~Get Transaction XID of database.

TABLE ABC.DUMMY,WHERE (ORGANIZATION_ID = 83),SQLEXEC (ID XYZ.DUMMY_PKG.DUMMY_PROCEDURE_NAME,SPNAME XYZ.DUMMY_PKG.DUMMY_PROCEDURE_NAME,PARAMS (P_DBNAME=@
GETENV("DBENVIRONMENT","DBNAME"),P_OBJECT_NAME='DUMMY',P_INVENTORY_ITEM_ID=INVENTORY_ITEM_ID,P_ORGANIZATION_ID=ORGANIZATION_ID,P_COMMAND=@CASE(@GETENV("GGHEADER", "OPTYPE"), "INSERT", "INSERT", "SQL COMPUPDATE", "UPDATE", "PK UPDATE", "UPDATE","DELETE","DELETE"),P_TRANSACTION_ID=@GETENV("TRANSACTION", "XID")));

Friday, May 30, 2014

ExaLogic | List VM on Compute Node.

Links to this post

Part of ELLC Tool, Exapatch has variety of Commands that can be Handy for Admin also.

we can query Exapatch to see How many VMs are running on which Compute Nodes.

/exalogic-lctools/bin/exapatch -a listVMs
Logging to file /var/log/exapatch_20140529151107.log
Compute-Node: 10.10.100.101:
        000aaa00000600004995bfe5e81dcbdf (ExalogicControlOpsCenterPC1)
        000aaa0000060000d299f9c94afe41da (ExalogicControl)

Compute-Node: 10.10.100.102:
        000aaa00000600001f012b58d1fb85b6 (dummyvn02)
        000aaa0000060000cc95187b5e532439 (ExalogicControlOpsCenterPC2)

Compute-Node: 10.10.100.103:
        000aaa0000060000063eb61a02ae11c1 (dummyvn04)
        000aaa000006000021013dc081e56c98 (dummyvn18)

Compute-Node: 10.10.100.104:
        000aaa00000600006955f56fefc87683 (dummyvn01)

Compute-Node: 10.10.100.105:
        000aaa00000600007f2ee56ea19ed478 (dummyvn09)

Compute-Node: 10.10.100.106:
        000aaa0000060000268c78fb303c0e25 (dummyvn03)

Compute-Node: 10.10.100.107:
        000aaa00000600003056e4719030bef3 (dummyvn50)

Compute-Node: 10.10.100.108:
        000aaa000006000044490976c7f0c9a6 (dummyvn08)

Wednesday, May 21, 2014

Exadata Bug-list fixed from Image 11.2.3.3 and Later.

Links to this post
As the Exadata X4-2 with 11.2.3.3 and later has below bugs fixed.

Fixes:
-----

9964936     ENHANCE EXADATA ASR TO FILE AUTO SRS FOR (PREDICTIVE) BATTERY FAILURES
11065811     FOR CELLDISK IMPORT REQUIRED PUBLISH EVENT TO ASM + DEL ENDIANNES FRM OWNER FILE
11683510     BATTERY TEMP NORMAL CLEAR MSG RECEIVED WITHOUT ANY ALERT
11838804     ACCEPT MULTIPLE DNS SERVERS FOR ILOM WHERE APPLICABLE IN IPCONF
12357450     FIX TIMESTAMPS IN ALERTS MINED FROM SEL ON V1 SYSTEMS
12708278     CELLSRV SHOULD LOG GD NAME AND OFFSET FOR IO ERRORS
13361797     RS FAILED TO RESTART MS IN RARE SCENARIOS
13495012     ALTER CELL SNMPSUBSCRIBER COMMAND SETS TYPE=ASR ON WRONG ENTRY
13498201     REMOVE SPURIOUS [SERV CELLSRV HANG DETECTED] AFTER CELLSRV CRASH
13521330     NEED TO IMPLEMENT "LIST DATABASE" FOR THE EM IORM UI
13618724     REMOVE ERROR MESSAGE PREFIX FROM ALERT TEXT
13725681     DCLI -K CREATES DIRECTORY /~/.SSH ON SOLARIS 10 NODES.
13737794     VARIABLE-BINDINGS ORDER OF CELLCLI TESTMESSAGE IS NOT VALID.
13807139     IMPROVE RS AND CELLSRV STARTUP IP ERROR REPORTING AND DIAGNOSTICS
13822165     ADD SHOW BANNER AND HIDE STDERR ARGUMENTS TO DCLI
13838283     BETTER ERROR MESSAGE FOR CREATE CELLDISK ON PHYSICAL DISKS IN FAILURE STATUS
13923317     COMMAND PARSING ANOMALY IN CELLCLI RESULTS IN UNEXPECTED SETTINGS
13934957     CELL PATCHING PREREQ-CHECK SHOULD FAIL IF IPCONF -VERIFY IS NOT OK
13934966     CELL PATCHING PREREQ-CHECK SHOULD FAIL IF LIST ALERTHISTORY" SHOW ALERTS
13935080     PATCH SHOULD NOT STATE IT FAILED IF PREREQS ARE NOT OK.
13938302     ALERT IS NOT CREATED IF VALUE OF CL_TEMP METRIC TRESPASSED BUILD-IN THRESHOLD
13973225     MS SHOULD GENERATE ALERTS / TRAPS WHEN SAS LANES IN THE SAS EXPANDER FAIL
13977078     ASR - GRID CONTROL AND ASR TRAP DESTINATION ENTRY REMOVED
14008398     HANDLE INVALID MODEL FROM DMIDECODE MORE GRACEFULLY
14043671     CELLSRV SHUTDOWN WITHOUT FORCE FAILS IF A DISKGROUP IS DISMOUNTED
14045900     SUMMARY TEXT FOR CALIBRATE COMMAND DOES NOT REPORT ERROR 
14065914     FLASH LOGGING FEATURE SHOULD HAVE METRIC FOR BUFFER ALLOCATION FAILURES
14092566     TRACK FLASH CACHE STATE WHEN NOT CACHING
14103957     FLASH CACHE/LOG NOT CREATED ON RESTORED FLASH DISK
14107147     RESYNC TIME SHOULD NOT BE PART OF PATCH TIMEOUT
14148776     ALTER CELLDISK NAME - NEW NAME IS NOT REFLECTED BY LIST FLASHCACHE AND CACHEDBY
14165314     DUPLICATE FS ALERTS SHOULD BE SUPPRESSED
14177001     CUSTOMIZED BATTERY LEARN CYCLE IN EXADATA
14192222     CELL SERVER MODEL SHOULD REFLECT HC OR HP
14199144     MS SENDS ALERTS FROM SNMP TRAP FROM NON-LOCAL SPS
14222004     RENAMING A CELL REGENERATES NEW TEMPERATURE ALERTS AND DONOT CLEAR OLD ALERTS
14239811     MS PD STATUS SHOULD SAY 'FAILED' INSTEAD OF 'CRITICAL'
14244206     MS SCHEDULED BBU RELEARN FAILS TO CHECK BBU STATUS NOR RETRIES 
14263653     NEED PERMANENT FIX FOR 14263651
14305629     DISK INSERTION PROCESSING TOO LONG
14311898     WRONG IORM PLAN OBJECTIVE IS DISPLAYED AFTER DOWNGRADING FROM 11.2.3.2
14312177     SLOW FLASH WITH FLASHLOG ON IT CAUSES CELL SERVICES TO FAIL STARTUP
14313375     ERROR MSG ON /TMP/OC4JPATCH/7439847 : NO SUCH FILE OR DIRECTORY
14356436     ALTER FLASHCACHE FLUSH NEEDS BETTER ERROR MESSAGE FOR WTFC
14366869     CELLSRV DIES WITH SIGSEGV IN FLASHLOGSTORE::SCANACTIVETABLE ALONG LRGSAIOV TEST
14368098     MS: FD WITH CRITICAL PD STATUS STILL SHOWN AS NORMAL
14378866     INCREASE NUMBER OF GRIDDISKS ALLOWED PER BATCH TO AVOID A DOUBLE REBALANCE
14464028     CELLDISK INVALID ERROR WHEN FLASHCACHE CREATED WITH NOT PRESENT STATUS CD 
14480010     FLASH LOG NEEDS IMPROVED CONCURRENCY FOR RE-ENABLING DISKS
14502930     CRITICAL ALERT GENERATED WHEN USB IS REBUILT SUCCESSFULLY 
14505249     IORM OBJECTIVE BALANCED AND LOW_LATENCY DON'T KICK IN FOR SOLO WORKLOAD MODE
14555001     CREATE FLASHCACHE ALL CREATES FLASHCACHE OF SIZE 128M WITH NOT NORMAL CELLDISKS 
14569694     PATCHMGR -CLEANUP SHOULD CLEANING UP _PATCH_HCTAP_/ OR PROVIDE INSTRUCTIONS TO
14588372     ASR - EXADATA FAILING TO SEND SNMP PACKET DUE TO JAVA NULL POINTER ERROR
14610867     FLASH LOG REDO LOG WRITE HISTOGRAM NEEDS TO BE MORE EASILY EXPORTED
14612318     ADD CELLCLI AND SCRIPT SUPPORT TO REPLACE AND RE-ENABLE BBU
14621505     EXADATA GRIDDISK AUTO CREATION FAILED
14646784     JAVA.UTIL.NOSUCHELEMENTEXCEPTION IN MS LOG WHEN EMAIL DELIVERY RETRY FAILS
14674689     FLASH LOG ACTIVE TABLE SIZE SHOULD BE INCREASED
14692944     NEED DETAILED STATS WHY CELL IOS ARE NOT CACHED
14747900     IORM_DATABASE METRICS SHOW DBUA DATABASE METRICS
14758854     FLASHCACHE SIZE CHANGES WHEN CORRUPT CELLDISK IS MADE NORMAL
14769540     DESCRIBE GRIDDISK DOES NOT LIST THE CACHEDBY ATTRIBUTE
14770723     EXPOSE LIFE LEFT ON EACH AURA2 CARD AS A PERCENTAGE 
14803349     DOM CONFINEMENT SHOULD CONSIDER PARTNERSHIP
14808660     CELLSRV FAILED TO START, HIT ORA-600[FLASHLOGPIECELIST::ADDPIECE]
14841844     ASM DOES NOT HAVE ITS OWN DB_* DATABASE METRIC
15871310     CREATE GRIDDISK ALL COMMAND FAILS WHEN A CELLDISK IS NOT NORMAL
15897446     ASR-EXADATA CELL LOCATION OF MIB CAUSING FIFO ERRORS
15963552     POKE FROM CELLSRV IS MISSING WHEN THERE IS NO ASM METADATA
15974057     ALWAYS FLUSH FLASHCACHE FOR WRITEBACK MODE WHEN DOWNGRADING BELOW 11.2.3.2.0
15994904     CELL NEEDS EIGHTH RACK SUPPORT
16001442     FAILED CELLCLI COMMANDS HAVE ZERO EXIT STATUS
16006228     DBSERVER_BACKUP.SH TAR COMMAND SHOULD CORRECTLY HANDLE SPARSE FILES
16028248     LUN AND PHYSICALDISK INFOR NOT CORRECT FOR LIST CELLDISK AFTER RENAME
16064753     ALTER FLASHCACHE ALL WITH A FLASHCACHE ATTRIBUTE REPORTS SUCCESS
16065180     FLASHCACHEMODE SET TO WRITETHROUGH FOR WRB FLASHCACHE
16067726     MS HANG AFTER [OSSMISC:OSSMISC_TIMER_TICKS] WHEN TIME JUMPS BACKWARDS
16074182     ADD "LIST DATABASE" COMMAND TO CELLCLI AND MS
16074653     CPU IMPROVEMENTS FOR FLASHACACHE
16081052     FLASH LOG DISK SIZE SHOULD BE CORRECTLY VALIDATED
16081421     HARD DISK FAILED TO MOUNT AUTOMATICALLY AFTER FLASH DISK FAILURE AND REPLACEMENT
16092303     MS SERVER.XML FILE TRUNCATED AS PART OF A CELL POWER CYCLE
16105593     CELL RPM UPGRADE CHECKING ALL TRACE FILES, CAUSING EXCESSIVE DELAY
16174361     ALTER FLASHCACHE ALL FLUSH DOES NOT FLUSH PEER FAILURE OR POOR PERF CELLDISKS
16193439     ASR/ SNMP TRAP SYSTEM IDENTIFIER FAILURE TO BE SENT ON HALRT FAULTS
16213900     "LIST LUN LUN_NAME" COMMAND FAILS WHEN CELLSRV IS STOPPED
16231174     MS ILLEGALMONITORSTATEEXCEPTION DURING ERASE
16232311     FLASH DISK ALERT SHOULD INCLUDE THE SERIAL NUMBER OF THE WHOLE CARD
16246710     SUNDIAG SHOULD RETRIEVE LSIDIAG_FULL WHEN AURA2.X PROBLEM DETECTED
16278024     FORCE DROP OF WBFC CD GD SHOULD CHECK FOR ASM REDUNDANCY
16278105     REDUCE NUMBER OF CONCURRENT IOS IN A CELL
16371635     CLUSTER WIDE CRASH AFTER ALL CELLSRV CRASHED WITH ORA-600 [KGKPLOALLOC1]
16392070     USE PSID TO UNIQUELY IDENTIFY THE INFINIBAND HCA FOR FIRMWARE CHECK AND UPDATES
16411024     NEED SUPPRESS DISK POWER STATE CHANGE ALERTS
16411732     QUERY/CREATE TABLE ETC. FAILING WITH ORA-27614: SMART I/O FAILED DUE TO AN ERROR
16413066     LIST CELL DOES NOT DISPLAY ALL ATTRIBUTES ON RE-CREATING CELL
16417471     CELLCLI LIST METRICCURRENT FC_IO_ERRS - CELL-02016: METRIC DOES NOT EXIST: FC_IO
16463547     OSS SUPPORT FOR PERMANENT KEEP ACROSS CELLSRV BOUNCE
16472221     WBFC: ORA-600 [FCCGETGDCLS_1] DURING FDOM OFFLINE
16472355     WBFC: CELLSERV HANG DURING FDOM OFFLINE/ONLINE 
16481592     PATCHMGR CLEANUP COLLECTING IRRELEVANT CONTENT
16487249     KERNEL SYSTEM TIME DRIFTING TOO FAST FOR NTP
16495446     UNCORE FREQUENCY IS NOT DISABLED ON X3-2 DB NODES 
16501767     DURING IMAGE UPGRADE TO 11.2.3.2.1 CELL NODE DOES NOT COME UP AFTER LAST REBOOT
16508451     IMPROVE LOG FILE STITCHING BY PATCHMGR
16510225     RE-IMAGE DOESN'T ENABLE ALL CPU CORES
16537444     EXADATA: CELLSRV HANG HAPPENED IN CELLTRANSITION
16538569     CHECKDEVEACHBOOT -FIX MDALL FIX GRUB CAN FAIL WHEN MD4 NOT SYNC'ED
16585329     CONFIGURE LOGROTATE TO ROTATE AND BZIP2 ALL .LOG/.TRC FILES IN /VAR/LOG/CELLOS
16586268     CRASHCORE FILE IS OVERWRITTEN EVERY TIME IN CASE OF OS CRASHDUMP
16590105     FSCK CHECKS NOT DISABLED IN FRESH IMAGE
16591877     ASMDISKGROUPNAME, ASMDISKNAME IS NOT POPULATED CORRECTLY AFTER ASM DG RECREATION
16605828     TEST CASE FOR BUG15882436
16684067     CELLSRV 1M REMOTE RECEIVE PORT BUFFER DEPLETION
16688320     UPON SENDASRTRAP FAILURE, MS FAILED TO SEND REMAINING SNMP SUBSCRIBERS
16688982     WBFC: ASSIGNING FLASH CD IS 10 MINS DELAY AFTER CELLSRV RESTART
16694632     IORM PLAN RESET DOES NOT FREE SUB HEAP EXTENTS
16696321     LNX64-11204-OSS: CELLSRV HITS ORA-600 [DISKIOSCHED::SETPLAN:DB OTHER]
16696985     IMPROVE ALERT LOG MESSAGES WHEN NETWORK ISN'T AVAILABLE
16699385     LNX64-11204-CSS: 160 DB IN ONE CLUSTER, NODE REBOOT AFTER CSSD CRASH
16704019     A "DISK REMOVED" ALERT IS SENT WHEN A DISK ACTUALLY FAILS
16705313     DCLI HANDLES HEAD PIPE INCORRECTLY
16717229     CHANGE DEFAULT VALUE FOR VM/MIN_FREE_KBYTES TO 500MB
16745871     DISABLE THE BUILT-IN CELL AMBIENT THRESHOLD
16768684     FLASHLOG NEEDS PERFORMANCE IMPROVEMENTS BASED ON SLOB RESULTS
16769818     VERIFY-TOPOLOGY NEEDS TO ACCOMODATE NODE_DESC CHANGES & LACK OF SPINE SWITCH
16774368     DISABLE EOIB IN EXADATA IMAGE TO AVOID EXCESSIVE SM LOGGING / LOG WRAP
16775584     MS: "LIST FLASHCACHECONTENT" RETURNS DUPLICATE KEEP OBJECTS
16777412     EXPOSE FLASHCACHE BYPASS REASON METRICS (BUG 14692944 ) VIA MS
16777594     GCW:PATCHMGR CLEANUP DIDN'T HAPPEN WHEN ANY PID MATCH INSTALL.PID
16777751     DCLI DOES NOT CAPTURE REMOTE HOST IDENTIFICATION CHANGED ERROR
16782749     FIX WBFC WRITE METRICS AND ADD REDIRTY METRIC
16796626     TURN OFF OSWATCHER MAKING EXAWATCHER THE ONLY ONE IN USE
16807611     MS FILE DELETION LOGGING AND ROLLING RENAME WRAP ISSUES
16809426     EXADATA ABSOLUTE SERVICE TIME VIOLATION DETECTED ON ONE DISK AFFECTING OTHERS
16815398     REDUCE MTU SIZE ON DB NODES IB INTERFACE TO 7000
16836361     CD_IO_LOAD METRIC VALUES ARE INCORRECT AND TOO HIGH WHEN LOAD IS LOW
16845112     CELLCLI COMMANDS FAIL WITH CELL-2664: FAILED TO CREATE FLASHCACHE ERRORS
16849845     WRB: CELLSRV HANG DURING FDOM FAILURE USING SETPCI
16858835     OUTOFMEMORYERROR RS-7445 [SERV MS NOT RESPONDING] [IT WILL BE RESTARTED]
16864784     TEST NETWORK STATUS
16887059     REMOVE OFED_INFO (RPM OFED-SCRIPTS)
16903390     ORA-600 [PREDICATEDISK::WRITE_5]
16917575     HOTSPARE NOT RECLAIMED WHEN UPGRADING TO 11.2.3.2.1 
16921398     MISSING ARCFOUR CIPHER IN SSHD_CONFIG BREAKS SNAPSHOT
16932116     RESOURCE CONTROL NEEDS MULTIPLE RUNS TO GET CURRENT STATUS 
16949685     TEST DISKGROUP MOUNT WITH ONE INVALID IP IN CELLIP.ORA
16954519     UPDATE COPYRIGHT YEAR 2012 IN CELLCLI BANNER
16964406     DONT LOAD MLX4_EN DRIVER
16973508     WFC: FLASHCACHE SIZE WAS ROUNDED TO MULTIPLE OF 16MB
16977810     SYSTEM DISK IMAGE FAILED DUE TO MD4 NOT DEGRADED
16988043     DISCONTINUE CHECKSWPROFILE.SH
16992011     ALL MDS SHOWS "REMOVED" AFTER REBOOT
16998810     /OPT/ORACLE.EXAWATCHER/ARCHIVE DIRECTORY SHOULD BE ALLOWED TO BE A SYMLINK
17039567     NEED TO UPDATE /ETC/SYSCONFIG/KDUMP KDUMP_COMMANDLINE_APPEND LINE
17084429     RECLAIMDISKS.SH FAILED ABRUPTLY AND -RESTORE OPTION FAILS TO RESTORE.
17088220     ENABLE HWCHECKER IN SOLARIS EXADATA COMPUTE NODE
17157638     PARALLEL DML PRODUCES INCORRECT SQL RESULT
17214800     SUNDIAG SHOULD REFER TO EXAWATCHER INSTEAD OF OSWATCHER
17251471     SPEED UP SINGLE THREAD
17277236     ALTER LUN REENABLE ON FLASH LOG DISK CAN RESULT IN CELLSRV CRASH
17278319     SUNDIAG ENHANCEMENTS FOR ASR, ILOM, EXAWATCHER, CELL CONFINEMENT, NETWORK DATA
17285226     WBFC: CELLSRV HANG IN FLASHCACHECORE.H DUE TO DIRTY LRU QUEUE UPDATE
17295207     REPLACE ALL CURRENT USAGE OF DATE TO DATE FORMAT +'%F %T %Z' FOR LOGS/MESSAGES.
17307247     OSWATCHER NEEDS TO COLLECT MEGARAID FWTERMLOG PERIODICALLY
17313339     SYSTEM DISK REPLACEMENT FAILS ON HP V1 EQUIPMENT
17330822     LNX64-12.1-ASM,CELLSRV CRASH WITH ORA-600[~PREDICATEMAPELEMENT3]
17336036     MISLEADING ERROR ABOUT MISSING XML FILE WHEN UBIOSCONFIG FAILS
17346692     EIGHTH-RACK CONFIGURATION CHANGED TO ENABLED AFTER RESCUE
17349857     NEED TO SET BOOTWITHPINNEDCACHE TO 1 SO THAT EXADATA SYSTEM CAN BOOT WITH PINNED
17362109     ENABLE AUTOMATIC FW UPDATES ON LINUX DB NODES
17371176     DISABLE BUILDS OF NON-UEK OPTIONS FOR DB NODE UPDATE 
17383646     SERIALIZE THE INFINICHECK EXECUTION 
17390553     IORM LIMIT DIRECTIVE CAUSES OVER THROTTLING
17404812     CONCURRENTMODIFICATIONEXCEPTION IN CONFINETRANSITION
17417506     IO ERROR DETAILS FROM CACHE OBJECT
17444979     INFINICHECK FAILS IN EXPANSION STORAGE ONLY MODE
17451210     CELLCLI SERIALIZES CELLMONITOR COMMANDS TOO MUCH
17472203     ADD ALERT DESCRIPTION FOR CONFINED ALERTS; SO EMAILS DO NOT SHOW 'HARDWARE ALERT
17475687     LIST FLASHCACHE CAN DISPLAY VALUES NOT YET POPULATED BY SYNCDISKONCE
17484677     SOME NULL POINTER EXCEPTIONS IN MS
17489799     ROLLBACK FAILED ON V1 BECAUSE OF MISSING USB DEVICES
17504127     EXCESSIVE TRACE ENTRIES WHEN MS RECEIVES A SNMP TRAP
17510981     BLACKLIST EDAC MODULE FROM LOADING
17511671     CALIBRATE FAILED IN CREATING THE RAND_ALL.LUN FILE.
17511684     SET WATCHDOG_THRESH TO 30 AND SET PRINTK TO "4 4 1 7"
17512231     IMPROVE PATCHMGR ERROR MESSAGE WHEN DETECTING ACTIVE ALERTS IN ALERT HISTORY

Tuesday, March 11, 2014

GC buffer busy acquire in RAC

Links to this post

This is classic case when same Update runs from multiple Instance in RAC at same time and Execution plan is worst of all. this will create Heavy block contention.

These wait events used to be "buffer busy" in 10g but in 11g It has splited and granual at detail level.

we first analyzed AWR from all 3 Nodes for same Time frame and we found that "GC buffer busy acquire" and " Read by another session"


ON INSTANCE 1

ON INSTANCE 2

ON INSTANCE 3


So I ran few queries which can tell us more about what happen during this time-frame.
You may also find this query at Daily Performance Report.


SELECT sql_id,
text,
elapsed_time,
CPU_TIME,
EXECUTIONS,
round(elapsed_time/Executions,0) avg_elp_time,
PX_SERVERS,
DISK_READ_BYTES,
DISK_WRITE_BYTES,
IO_INTERCONNECT_BYTES,
OFFLOAD_ELIGIBLE_BYTES,
CELL_SMART_SCAN_ONLY_BYTES,
FLASH_CACHE_READS,
ROWS_PROCESSED
FROM (SELECT x.sql_id,
SUBSTR ( dhst.sql_text, 1, 30) text,
ROUND ( x.elapsed_time / 1000000,0)  elapsed_time,
ROUND ( x.cpu_time / 1000000,0)  CPU_TIME,
x.executions_delta       EXECUTIONS,
ROUND (X.DISK_READ_BYTES/1048576,0)        DISK_READ_BYTES,
ROUND (X.DISK_WRITE_BYTES/1048576,0)       DISK_WRITE_BYTES,
ROUND (X.IO_INTERCONNECT_BYTES/1048576,0)  IO_INTERCONNECT_BYTES,
ROUND (X.OFFLOAD_ELIGIBLE_BYTES/1048576,0) OFFLOAD_ELIGIBLE_BYTES,
X.FLASH_CACHE_READS                        FLASH_CACHE_READS,
ROUND (X.cell_smart_scan_only_BYTES/1048576,0)  CELL_SMART_SCAN_ONLY_BYTES,
(x.ROWS_PROCESSED) ROWS_PROCESSED,
(X.PX_SERVERS) PX_SERVERS,
row_number () OVER (PARTITION BY x.sql_id ORDER BY 0) rn
FROM dba_hist_sqltext dhst,
(SELECT dhss.sql_id                       sql_id,
SUM (dhss.cpu_time_delta)                 cpu_time,
SUM (dhss.elapsed_time_delta)             elapsed_time,
SUM (dhss.executions_delta)               executions_delta,
SUM (dhss.PHYSICAL_READ_BYTES_DELTA)      DISK_READ_BYTES,
SUM (dhss.PHYSICAL_WRITE_BYTES_DELTA)     DISK_WRITE_BYTES,
SUM (dhss.IO_INTERCONNECT_BYTES_DELTA)    IO_INTERCONNECT_BYTES,
SUM (dhss.IO_OFFLOAD_ELIG_BYTES_DELTA)    OFFLOAD_ELIGIBLE_BYTES,
SUM (dhss.OPTIMIZED_PHYSICAL_READS_DELTA) FLASH_CACHE_READS,
SUM (dhss.IO_OFFLOAD_RETURN_BYTES_DELTA)  cell_smart_scan_only_BYTES,
SUM (dhss.ROWS_PROCESSED_DELTA)      ROWS_PROCESSED,
SUM (dhss.PX_SERVERS_EXECS_DELTA) PX_SERVERS
FROM dba_hist_sqlstat dhss
WHERE dhss.snap_id IN
(SELECT snap_id
FROM dba_hist_snapshot
-----change snap_id here. 
WHERE SNAP_ID > 31822 AND SNAP_ID<= 31826)
GROUP BY dhss.sql_id) x
WHERE x.sql_id = dhst.sql_id
AND ROUND ( x.elapsed_time / 1000000, 3) > 3600)
WHERE rn = 1 and EXECUTIONS> 0
ORDER BY ELAPSED_TIME DESC;

---output

SQL_ID        TEXT                            ELAPSED_TIME  CPU_TIME  EXECUTIONS AVG_ELP_TIME   PX_SERVERS  DISK_READ_BYTES DISK_WRITE_BYTES IO_INTERCONNECT_BYTES OFFLOAD_ELIGIBLE_BYTES CELL_SMART_SCAN_ONLY_BYTES  FLASH_CACHE_READS ROWS_PROCESSED
------------- ------------------------------ ------------- --------- ----------- ------------ ------------ ---------------- ---------------- --------------------- ---------------------- -------------------------- -------------------- --------------
6vbxrnpxwc1mz BEGIN Sp_XYZ_DUMMY_Reversa             53686       830          98          548            0           344717                0                344824                    0                        107                    0             98
g25x6rr6x4yv7 UPDATE ABC_DUMMY_XYZACTION             44306       571          88          503            0            30440                0                 30426                    0                        -14                    0             88
74d8zqzh802xq SELECT MAX (BUSINESS_DATE || B         42335       186       14930            3            0             8293                0                  8293                    0                          0                    0          14921
g75678tr0ddmw BEGIN SP_PASSIVEPERIOD_CALC(:1         30266       150       10679            3            0             5567                0                  5567                    0                          0                    0          10678
cpz2fp6466vus SELECT CPT_TOTALHOLD_AMT FROM           9040       255         211           43            0           314231                0                314231                    0                          0                    0            209
7d4xjr17waxy7 BEGIN SP_CARD_ISSUENCE_SRT_TO_          4701       533         107           44            0           164865                0                164865                    0                          0                    0            103
c8hgnxkkr7jvz SELECT abc_ROW_ID, abc_SSNFAIL          4685       532         103           45            0           164861                0                164861                    0                          0                    0            103

For Top Hot object by Physical Read.   (Hot Object)

SELECT * FROM (
SELECT do.OWNER||'.'||do.OBJECT_NAME||'..['||do.OBJECT_TYPE||']' AS OBJECTS,
DHSS.INSTANCE_NUMBER AS INST,
SUM(DHSS.LOGICAL_READS_DELTA) LOGICAL_READ,
SUM(DHSS.PHYSICAL_READS_DELTA) PHY_READ,
SUM(DHSS.PHYSICAL_WRITES_DELTA) PHY_WRIT,
SUM(DHSS.ITL_WAITS_DELTA) ITL_WT,
SUM(DHSS.ROW_LOCK_WAITS_DELTA) ROW_LCK_WT
from dba_hist_seg_stat DHSS, DBA_OBJECTS DO
WHERE SNAP_ID > 31822 AND SNAP_ID<= 31826
--WHERE DHSS.SNAP_ID > 20135 AND DHSS.SNAP_ID<= 20183
AND DHSS.OBJ#=DO.OBJECT_ID
group by do.OWNER||'.'||do.OBJECT_NAME||'..['||do.OBJECT_TYPE||']',DHSS.INSTANCE_NUMBER
order BY PHY_READ DESC
) WHERE ROWNUM <=40;


OBJECTS                                             INST         LOGICAL_READ             PHY_READ    PHY_WRIT   ITL_WT ROW_LCK_WT
-------------------------------------------------- ----- -------------------- -------------------- ----------- -------- ----------
corpABC.ABC_DUMMY_XYZACTION..[TABLE]                2             22388144             16357126        1797        0          0
corpABC.ABC_DUMMY_XYZACTION..[TABLE]                1             19444032             14708504        2055        0          0
corpABC.ABC_DUMMY_XYZACTION..[TABLE]                3             16945392             12908672        1367        0          0
corpABC.ABC_CAF_INFO_ENTRY..[TABLE]                 1              7789840              7788316          98        0          0
corpABC.ABC_CAF_INFO_ENTRY..[TABLE]                 2              6969952              6968505          97        0          0
corpABC.ABC_CAF_INFO_ENTRY..[TABLE]                 3              6354976              6353643         104        0          0
corpABC.ABC_APPL_PAN..[TABLE]                       3              1149808              1069323         227        0          0
corpABC.ABC_APPL_PAN..[TABLE]                       2               903440               803870         256        0          0
corpABC.XYZACTIONLOG..[TABLE]                       3              1867424               629380         689        0          0
corpABC.XYZACTIONLOG..[TABLE]                       1           1197310176               436078         723        0          0
corpABC.XYZACTIONLOG..[TABLE]                       2              1909632               372565        1255        0          0
SQL_ID affected by "gc buffer busy acquire"
SQL> SELECT INSTANCE_NUMBER,SQL_ID,COUNT(EVENT) FROM DBA_HIST_ACTIVE_SESS_HISTORY WHERE SNAP_ID > 31822 AND SNAP_ID<= 31826 AND EVENT LIKE '%busy acquire%' group by sql_id,INSTANCE_NUMBER order by 2 desc;

INSTANCE_NUMBER SQL_ID                COUNT(EVENT)
-------------   ------------- --------------------
            3   g25x6rr6x4yv7                 619
            2   g25x6rr6x4yv7                 548
            1   g25x6rr6x4yv7                 192
SQL_ID affected by "read by other session"
SQL> SELECT SQL_ID,COUNT(EVENT) FROM DBA_HIST_ACTIVE_SESS_HISTORY WHERE SNAP_ID > 31822 AND SNAP_ID<= 31826 AND EVENT LIKE '%read by%' group by sql_id order by 2 desc;

INSTANCE_NUMBER SQL_ID                COUNT(EVENT)
-------------   ------------- --------------------
            3   g25x6rr6x4yv7                 764
            2   g25x6rr6x4yv7                 658
            1   g25x6rr6x4yv7                 169
Now lets see what is plan of Most affected SQL_ID '
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('g25x6rr6x4yv7'));


--------------------
UPDATE ABC_DUMMY_XYZACTION SET ABC_TOTALHOLD_AMT = TRIM(TO_CHAR(:B4, '77777777777')), ABC_DUMMY_VALIDFLAG = 'N',
ABC_XYZACTION_FLAG = 'R' WHERE ABC_RRN = :B3 AND ABC_TXN_DATE = :B2 AND ABC_INST_CODE = :B1

Plan hash value: 4161612620
----------------------------------------------------------------------------------------------
| Id  | Operation          | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                         |       |       | 12704 (100)|          |
|   1 |  UPDATE            |     ABC_DUMMY_XYZACTION |       |       |            |          |
|   2 |   TABLE ACCESS FULL|     ABC_DUMMY_XYZACTION |     1 |   106 | 12704   (1)| 00:02:33 |
----------------------------------------------------------------------------------------------


And Of-course it will generate GC wait event.  As you see "corp.ABC.ABC_DUMMY_XYZACTION"  is top Objects for Physical Reads. We created index on few columns which solved issue.

Wednesday, February 19, 2014

RMAN 04006 and ORA 17627 | ORA 01017 invalid username/password while RMAN duplicate.

Links to this post

While duplicate database By RMAN using active database command.
I had trouble of connecting from RMAN to non-mounted standby instance.
It was connecting to sqlplus perfectly.

I did copy password file from primary. made connection from tns to non-mounted instance using SQLPLUS.
it worked fine but when i tries from RMAN it gaves error.


ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server

Then I recreated password file with below command IGNORECASE=Y is the one which i was missing.
You also need to put sec_case_sensitive_logon=false in your non-mounted standby instance init file.

export ORACLE_SID=DUMMY
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=sys password entries=10 force=y/n ignorecase=y 



This works like a charm. ...!!!