Out-of-place Upgrading single instance DB from 11.2.0.3 to 11.2.0.4.160719

OS: AIX 7.1 # install 11.2.0.4 on new oracle home # as user oracle on both hosts mkdir -p /oraapp/11.2.0.4/oracle/product/dbhome_1 # upgrade an existing database # Cancel Netca # modify .profile cp .profile .profile.`date +%Y%m%d` vi .profile # modify ORACLE_BASE and ORACLE_HOME # compile invalid objects, purge recyclebin and gather dictionary stats @?/rdbms/admin/utlrp set lines… Continue reading Out-of-place Upgrading single instance DB from 11.2.0.3 to 11.2.0.4.160719

Out-of-place Upgrading GI and DB from 11.2.0.3 to 11.2.0.4.160719

OS: AIX 7.1 # prerequisites 1 make sure GUI is available for installation 2 make sure /oraapp has at least 50G free space 3 media list p13390677_112040_AIX64-5L_[1-4]of7.zip # AIX 11.2.0.4 p23274134_112040_AIX64-5L.zip # 11.2.0.4.160719 GI + DB PSU (or 23615403 combo patch) p21354456_112040_AIX64-5L.zip # 21354456 one-off patch p6880880_112000_AIX64-5L.zip # opatch 11.2.0.3.10 # as root on both… Continue reading Out-of-place Upgrading GI and DB from 11.2.0.3 to 11.2.0.4.160719

AgentBreathingPoint error reported in db2diag.log

db2diag.log is reporting error: 2016-07-18-15.57.15.851435+480 I6417014418A499 LEVEL: Error PID : 3408416 TID : 294906 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : CMSDB APPHDL : 0-46228 APPID: 192.168.52.5.53954.160715200812 AUTHID : CMS EDUID : 294906 EDUNAME: db2agent (CMSDB) 0 FUNCTION: DB2 UDB, base sys utilities, sqeAgent::AgentBreathingPoint, probe:10 CALLED : DB2 UDB, common communication,… Continue reading AgentBreathingPoint error reported in db2diag.log

Analyzing db2 snapshot and db2diag.log

# analyzing db2 snapshot # check sql statement occurrence in a single snapshot file sed -n ‘/Dynamic SQL statement text/,/Memory usage for application/p’ snapshot_1.txt | sed ‘/Dynamic SQL statement text/d’ | sed ‘/Memory usage for application/d’ | sed ‘/Agent process\/thread ID/d’ | sed ‘/^\s*$/d’ | sort | uniq -c | sort -k1n | grep -v… Continue reading Analyzing db2 snapshot and db2diag.log

Update statement wouldn’t use primary index

In execution plan part of sqlt report, we can see SYS_OP_C2C filter, which is the implicit function which is used to convert the column between nchar and char. That’s exactly why the update statement won’t use primary index. CREATE UNIQUE INDEX “ETLUSER”.”PK_CSTP_DEAL_DAY” ON “ETLUSER”.”CSTP_DEAL_DAY” (“GID_ID”) <<<UNIQUE INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536… Continue reading Update statement wouldn’t use primary index