Friday, January 30, 2015

Rac One Node and Data Guard Broker some thoughts on bugs, notes.....


Currently we are moving user acceptance testing db's from regular single instances with ZFS cross-site mirroring to Super Cluster with RAC One and Data Guard.

The initial setup of this was finished a couple of months ago

and apart from Martin Bach (@MartinDBA) suggestions here and here and in real life together with Marcin Przepiorowski (@pioro) in Ireland in march last year.

 I also used following metalink note



Bug 17781373 : ORAAGENT DOES NOT SET LOCAL_LISTENER WHEN LISTENER_NETWORKS IS SET IN SPFILE

This exactly looked like what happened I could easily reproduce this by removing listener_networks

The workaround suggested was to put the nodeVIP as local listener, but that wasn't what the dgbroker wanted because it's static connect identifier was set to the this VIP ...and I want to avoid at all cost to modify it after role changes or relocates of the Rac One instances.

so I finally changed the LISTENER_NETWORKS parameter from


((NAME=network1)(LOCAL_LISTENER=RACONE)(REMOTE_LISTENER=scan01-uat:1521)), ((NAME=network_dg)(LOCAL_LISTENER=DG_VIP)(REMOTE_LISTENER=REMOTE_NET2))

To this :


((NAME=network1)(LOCAL_LISTENER=NODE_VIP,RACONE)(REMOTE_LISTENER=scan01-ora:1521)), ((NAME=network_dg)(LOCAL_LISTENER=DG_VIP)(REMOTE_LISTENER=REMOTE_NET2))




Note that NODE_VIP , RACONE , DG_VIP and REMOTE_NET2 resolve differently on each node in the RAC One Cluster.

eg.
on node1 one this maps to :


DG_VIP, NODE01_LOCAL_NET2=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node01dg-vip)(PORT = 1522))
  )


on node2 
DG_VIP, NODE02_LOCAL_NET2=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node02dg-vip)(PORT = 1522))
  )
...

also if you use alter system to change this don't forget the quotes

eg

alter system set listener_networks='(NAME=network1)(LOCAL_LISTENER=NODE_VIP,RACONE)(REMOTE_LISTENER=scan01-ora:1521))', '((NAME=network_dg)(LOCAL_LISTENER=DG_VIP)(REMOTE_LISTENER=REMOTE_NET2))';


While discussing some other things with my ex colleague Freek D'Hooge you can follow him on twitter and on his blog he pointed me to a note

How to Configure A Second Listener on a Separate Network in 11.2 Grid Infrastructure (Doc ID 1063571.1)

which says following

" Listeners specified by the LISTENER_NETWORKS parameter should not be used in the LOCAL_LISTENER and REMOTE_LISTENER parameters. Otherwise, cross registration will happen and connections will be redirected cross networks."


however this is not explicitly mentioned in the first note mentioned in this post.
For the time being I left the remote_listener as is and will test on newly created databases in order to remove it from already migrated uat databases...
Thanks Freek , Martin and Marcin for your insights ;-)  you guys rock !



PS I the host names in this blogpost are made up for privacy reasons .

Monday, January 26, 2015

fun facts of the day : SPFILE vs PFILE

Nothing spectacular 
some random observations made today


PFILE vs INIT.ora

when you startup with init.ora with deprecated parameters you get this 


 SQL>  startup nomount pfile='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initU041P15.ora'ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecatedORA-32006: REMOTE_OS_AUTHENT initialization parameter has been deprecatedORA-32006: USER_DUMP_DEST initialization parameter has been deprecatedORACLE instance started. 


same thing with spfile


ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 8554127360 bytes
Fixed Size                  2198680 bytes
Variable Size            1006637928 bytes
Database Buffers         7532969984 bytes
Redo Buffers               12320768 bytes

Friday, January 23, 2015

opatch more powerfull then you think

This is very basic but nevertheless handy so decided to share it ;-)

with the new QFSDP out for Super Cluster I was wondering if they added the super cluster critical issues DB16 to the patch and couldn't find it in the documentation


If you unpack the patch and you do for example it returns you all the bugs solved by this patch, which can be very handy.....
opatch lspatches -bugs 20059375
 patch_id:20059375
sub-patches:17628006,17741631,17943261,18006299,18136151,18293775,18552960,18642122,18825509,19061859,19307276,19495869,19698715,19893780
unique_patch_id:18363959
date_of_patch:29 Dec 2014, 03:27:46 hrs PST8PDT
patch_description:DATABASE PATCH FOR EXADATA (JAN2015 - 11.2.0.4.15) : (20059375)
component:oracle.rdbms.crs,11.2.0.4.0,optional; oracle.ovm,11.2.0.4.0,optional; oracle.xdk.rsf,11.2.0.4.0,optional; oracle.rdbms.rsf,11.2.0.4.0,optional; oracle.rdbms.dbscripts,11.2.0.4.0,optional; oracle.rdbms.util,11.2.0.4.0,optional; oracle.oraolap,11.2.0.4.0,optional; oracle.precomp.common,11.2.0.4.0,optional; oracle.rdbms.rman,11.2.0.4.0,optional; oracle.xdk.parser.java,11.2.0.4.0,optional; oracle.ctx,11.2.0.4.0,optional; oracle.rdbms,11.2.0.4.0,optional
platform:23,Sun SPARC Solaris (64-BIT)
executable:ORACLE_HOME/bin/proc; ORACLE_HOME/bin/rman; ORACLE_HOME/bin/renamedg; ORACLE_HOME/bin/oracle; ORACLE_HOME/lib/liborasdk.so.11.1; ORACLE_HOME/lib/libclntsh.so.11.1
prereq_oneoffs:17628006; 17741631; 17943261; 18006299; 18136151; 18293775; 18552960; 18642122; 18825509; 19061859; 19307276; 19495869; 19698715; 19893780
instance_shutdown:true
online_rac_installable:true
overlay_oneoffs:17628006; 17741631; 17943261; 18006299; 18136151; 18293775; 18552960; 18642122; 18825509; 19061859; 19307276; 19495869; 19698715; 19893780
patch_type:singleton
product_family:db
auto:false
bug:19972566, DB-11.2.0.4-MOLECULE-015-CPUJAN2015
bug:18641461, Fix for bug 18641461
bug:19972568, DB-11.2.0.4-MOLECULE-016-CPUJAN2015
bug:19972569, DB-11.2.0.4-MOLECULE-017-CPUJAN2015
bug:19487147, CREATE EDITION ERRORS OUT WITH ORA-38807
bug:19402853, ORA-600 [17182] SIGNALED ON A PARALLEL SLAVE
bug:18430495, HEAVY CONTENTION FOR GROUP TABLE LATCHES
bug:20074391, PSU 11.2.0.3.13 POST-DEINSTALLATION  CATBUNDLE RETURNS ORA-20000: ORU-10027
bug:17957017, ADD MORE DIAGNOSTICS FOR ORA-600 [QESMAGETTBLSEG1]
bug:19197175, Fix for bug 19197175
bug:19488514, DEADLOCK ON CONCURRENT APPEND INSERTS AND PARTITION DDL
bug:19291380, DBUA FAILS WITH ORA-38804 WHEN THERE IS NO ORA$BASE EDITION IN SOURCE DATABASE
bug:13640676, CANNOT EXCHANGE TABLE PARTITION WITH TEXT INDEX CREATED BEFORE 11.2.0.2 UPGRADE
bug:16667538, POSSIBLE FREED MEMORY ACCESS IN KKSMAPCURSOR
bug:18938517, SINGLE ROW INSERT INTO A CLUSTER TABLE DOES A LOT OF DB FILE SEQUENTIAL READ
bug:18863094, SKGXP NOT RETURNING COMPLETED RDMA WHEN ONE IS PENDING AT THE HEAD OF THE QUEUE
snip ...
patch_id:17628006
unique_patch_id:18263291
date_of_patch:6 Nov 2013, 11:39:51 hrs UTC
patch_description:DATABASE PATCH FOR EXADATA (NOV 2013 - 11.2.0.4.1) : (17628006)
component:oracle.rdbms.rsf,11.2.0.4.0,optional; oracle.rdbms.dbscripts,11.2.0.4.0,optional; oracle.rdbms,11.2.0.4.0,optional
platform:23,Sun SPARC Solaris (64-BIT)
executable:ORACLE_HOME/bin/oracle; ORACLE_HOME/bin/orion
instance_shutdown:true
online_rac_installable:true
patch_type:singleton
product_family:db
auto:false
bug:17313525, ORION WRITE HAS CORE DUMP ON X4-2 SOLARIS RACK
bug:17546761, GRIDDISK  RESILVERING TAKES FOREVER.
bug:17265217, X2-8; INCONSISTENT ASMDEACTIVATIONOUTCOME DURING SCRUB RESILVERING
bug:17080436, DUMP MAP ELEMENTS OF COMPLETED BUFFERS IN KCFIS
bug:14852021, SPAWRIO COMPUTES INCORRECT %READS AND %OPT
bug:13364795, COLUMNAR SMART SCAN CAN FAIL TO RECOVER FROM A CORRUPT BLOCK
bug:16863422, ORA-600 [KXDAM_NOTIFY_CELL;IOCTLFAILED] DURING RPM UPGRADE
bug:16837842, OUT OF MEMORY ERROR (ORA-4030) WITH KCFIS REQUESTS
bug:17446237, KCFIS BLOCK REPAIR SHOULD BE SUPPRESSED UPON GETTING 'BLOCK TO BE RESILVERED'
bug:17465741, ORA-600 KCFIS_DECR_OVERHEADMEM_LEN_1 WITH LARGE FILE CREATION / HIGH REDUNDANCY
bug:17443671, EXADATA ERROR;'OUTPUT BUFFER FOR READ CONTROL MESSAGE PAYLOAD TOO SMALL'

Btw DB16 is related to optimisations in speed on SPARC ... (altough some of them also available for linux ;-)

9308965 RAW HAZARDS SEEN WITH RDBMS CODE ON SOLARIS T5 - Patch is NOT yet available for 11.2.0.4.0 
17391636 EVAL OF UTF8 LIKE PREDICATE IS ORDERS OF MAGNITUDE SLOWER THAN ASCII LIKE - Patch available for 11.2.0.4. 
13846337 QESASIMPLEMULTICOLKEYCOMPARE NOT OPTMIZED FOR SOLARIS SPARC64 - Patch available for 11.2.0.4. 
12660972 CHECKSUM CODE NEEDS REVISTING IN LIGHT OF NEW PROCESSORS - Patch available for 11.2.0.4.