TimesTen Tip IPCS

So you are restarting the timesten daemon and for some reason, you are getting an error when you try to restart. Whenever you stop the process, always check it has released the process and memory. To check if it has stopped: We can see from the above it is still attached, First try the stop command again, if that fails, run the ttstatus command to find the pid. You can see there is no connections to the data store. Kill the pid's via the kill command. You should be OK to start it now.

UKOUG Conference 2015

UKOUG 2015 conference is nearly upon us, Make sure you have booked your tickets as you will have so much to take back. New Ideas,New Knowledge and also New Friends. Whatever you do, do not miss my presentation, 7th December 13:50 Hall 8b 🙂

Facilitating Innovation Through Analytics with Exalytics

Having access to business intelligence in near real time is important for intelligent business decisions. Rather than simply using aggregates, CIMA fully embraced in-memory technology and put its entire data warehouse into an Oracle TimesTen In-Memory Database on Exalytics. Dramatic reductions in query response time were achieved for Oracle Business Intelligence Enterprise Edition dashboard reports with 'Zero Application Code changes’. Based on this success, CIMA have been evolving their existing applications to further leverage in-memory technology and migrating other parts of the applications to use in-memory technology. This approach has enabled previously unachievable analysis, leading to greater knowledge of customers and challenges across the company. frame_0_delay-s http://www.tech15.ukoug.org/default.asp?p=12861&dlgact=shwprs&prs_prsid=11678&day_dayid=93 Also my picks. Try not to miss the keynotes, as well as the presentations below Ms Maria Colgan http://www.tech15.ukoug.org/default.asp?p=12861&dlgact=searchshwprs&prs_prsid=10912&day_dayid=93&src_dayid=93&prs_keywords= http://www.tech15.ukoug.org/default.asp?p=12861&dlgact=searchshwprs&prs_prsid=10914&day_dayid=93&src_dayid=93%2C+94%2C+95&prs_keywords= Mr Martin Widlake http://www.tech15.ukoug.org/default.asp?p=12861&dlgact=searchshwprs&prs_prsid=10677&day_dayid=93&src_dayid=93&prs_keywords= Mr Robin Moffatt http://www.tech15.ukoug.org/default.asp?p=12861&dlgact=searchshwprs&prs_prsid=11835&day_dayid=93&src_dayid=93%2C+94%2C+95&prs_keywords= http://www.tech15.ukoug.org/default.asp?p=12861&dlgact=searchshwprs&prs_prsid=11657&day_dayid=93&src_dayid=93%2C+94%2C+95&prs_keywords= Mr Mark Rittman http://www.tech15.ukoug.org/default.asp?p=12861&dlgact=searchshwprs&prs_prsid=11670&day_dayid=93&src_dayid=93%2C+94%2C+95&prs_keywords=

Standalone Oracle Database Patching with ASM

Some Useful links: Information Center: Patching and Maintaining Oracle Database Server/Client Installations (Doc ID 1351428.2) Introduction to Oracle Recommended Patches (Doc ID 756388.1) Oracle Recommended Patches -- Oracle Database (Doc ID 756671.1) Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets (Doc ID 1454618.1) Before you download any patches, you need to find out what is your current Software version. You should check all the Oracle Homes and patch them as needed. Notice that Grid Infrastructure (with ASM) must be Patched first as it must be on a same or higher level than any Oracle Database Homes. Log in to the servers as Oracle Software owner (usually oracle), set the proper Oracle environment and run the opatch utility. . oraenv -> +ASM $ORACLE_HOME/OPatch/opatch version (opatch version) $ORACLE_HOME/OPatch/opatch lspatches (show Patches only) $ORACLE_HOME/OPatch/opatch lsinventory (show Software details) . oraenv -> DB SID $ORACLE_HOME/OPatch/opatch version (opatch version) $ORACLE_HOME/OPatch/opatch lspatches (show Patches only) $ORACLE_HOME/OPatch/opatch lsinventory (show Software details) Repeat same steps on all the Oracle Database Homes. IN Action: Patch GRID_HOME and ORACLE HOME 1. [oracle@skynet ~]$. ./asm_profile.sh [oracle@skynet ~]$ cd $ORACLE_HOME [oracle@skynet grid]$ cd OPatch [oracle@skynet OPatch]$ ./opatch version OPatch Version: 11.2.0.3.4 OPatch succeeded. [oracle@skynet OPatch]$ ./opatch lspatches There are no Interim patches installed in this Oracle Home. [oracle@skynet OPatch]$ ./opatch lsinventory Oracle Interim Patch Installer version 11.2.0.3.4 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0/grid Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/11.2.0/grid/oraInst.loc OPatch version : 11.2.0.3.4 OUI version : 11.2.0.4.0 Log file location : /u01/app/oracle/product/11.2.0/grid/cfgtoollogs/opatch/opatch2015-10-14_09-19-58AM_1.log Lsinventory Output file location : /u01/app/oracle/product/11.2.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2015-10-14_09-19-58AM.txt -------------------------------------------------------------------------------- Installed Top-level Products (1): Oracle Grid Infrastructure 11g 11.2.0.4.0 There are 1 products installed in this Oracle Home. There are no Interim patches installed in this Oracle Home. -------------------------------------------------------------------------------- OPatch succeeded. [oracle@skynet ~]$ . ./db_profile.sh [oracle@skynet ~]$ cd $ORACLE_HOME [oracle@skynet dbhome_1]$ cd OPatch [oracle@skynet OPatch]$ ./opatch version OPatch Version: 11.2.0.3.4 OPatch succeeded. [oracle@skynet OPatch]$ ./opatch lspatches There are no Interim patches installed in this Oracle Home. [oracle@skynet OPatch]$ ./opatch lsinventory Oracle Interim Patch Installer version 11.2.0.3.4 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc OPatch version : 11.2.0.3.4 OUI version : 11.2.0.4.0 Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-10-14_09-28-19AM_1.log Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2015-10-14_09-28-19AM.txt -------------------------------------------------------------------------------- Installed Top-level Products (1): Oracle Database 11g 11.2.0.4.0 There are 1 products installed in this Oracle Home. There are no Interim patches installed in this Oracle Home. -------------------------------------------------------------------------------- OPatch succeeded. Lets download the Patch’s Now, log in to MOS and, click on "Patches & Updates", then choose "Recommended Patch Advisor". Under "Product", type "Real Application Clusters", choose proper Release "11.2.0.4" and Platform "Linux x86-64", now click on "Check recommendations when used with another product." and choose "Oracle Clusterware", "11.2.0.4" and "Linux x86-64", then "Search" Result should be this: "Patch 20485808: GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.4.6 (APR2015)" (This includes all the Patches, including Patches for the Database Homes) Click "Download" to get the Patch Click "Read Me" to read the Patch details and instructions. I would recommend to click on "View Related Knowledge to this Patch". Sometimes it has very useful information about the Patch itself, like "Known issues", Bug details etc. Before applying any Oracle patches, make sure you have the latest version of OPatch! The Patch ReadMe has a note about it, and you can download it from here: https://updates.oracle.com/download/6880880.html Choose Release: "11.2.0.0.0", Platform: "Linux x86-64", should get this to download: "OPatch patch of version 11.2.0.3.11 for Oracle software releases 11.2.0.x (JUN 2015)" When your download completes, copy these patches on all the Servers you plan to patch and you're ready for Patching. 1. Log in to the Server as Oracle Software Owner ("oracle"), run the following to check the databases) select Comp_name, status, Version From Dba_Registry order by Comp_name; (Check that all Components are VALID!) 2. Shutdown all the databases, then the asm, 3. Leave has/listener running. 4. . Set the environment for the Grid Infrastructure: . oraenv -> +ASM 5. Backup the current Opatch directory: [root@skynet grid]# mv OPatch OPatch14102015 3. Check OPatch version and then Update OPatch: [oracle@skynet OPatch]$ su Password: [root@skynet OPatch]# echo $ORACLE_HOME /u01/app/oracle/product/11.2.0/grid [oracle@skynet OPatch]$ cd /skynet1/source/kiran_oracle_patch_oct2015/OPatch [oracle@skynet OPatch]$ ls p6880880_112000_Linux-x86-64.zip [oracle@skynet OPatch]$ unzip p6880880_112000_Linux-x86-64.zip -d $ORACLE_HOME Archive: p6880880_112000_Linux-x86-64.zip creating: /u01/app/oracle/product/11.2.0/grid/OPatch/ inflating: /u01/app/oracle/product/11.2.0/grid/OPatch/opatchdiag inflating: /u01/app/oracle/product/11.2.0/grid/OPatch/opatch.pl creating: /u01/app/oracle/product/11.2.0/grid/OPatch/jlib/ .. .. . [root@skynet grid]# chown -R oracle:oinstall OPatch [oracle@skynet OPatch]$ ./opatch version OPatch Version: 11.2.0.3.12 OPatch succeeded. . Set the environment for the Oracle Infrastructure: . oraenv -> DB 1. Backup the current Opatch directory: [root@skynet grid]# mv OPatch OPatch14102015 3. Check OPatch version and then Update OPatch: [oracle@skynet OPatch]$ cd /skynet1/source/kiran_oracle_patch_oct2015/OPatch [oracle@skynet OPatch]$ ls p6880880_112000_Linux-x86-64.zip [oracle@skynet OPatch]$ unzip p6880880_112000_Linux-x86-64.zip -d $ORACLE_HOME Archive: p6880880_112000_Linux-x86-64.zip creating: /u01/app/oracle/product/11.2.0/grid/OPatch/ inflating: /u01/app/oracle/product/11.2.0/grid/OPatch/opatchdiag inflating: /u01/app/oracle/product/11.2.0/grid/OPatch/opatch.pl creating: /u01/app/oracle/product/11.2.0/grid/OPatch/jlib/ .. .. . [oracle@skynet OPatch]$ cd $ORACLE_HOME/OPatch [oracle@skynet OPatch]$ ./opatch version OPatch Version: 11.2.0.3.12 OPatch succeeded. 4. Create the OCM "dummy" response file: $ORACLE_HOME/OPatch/ocm/bin/emocmrsp -output /tmp/ocm.rsp (You don't need to specify any details) [oracle@skynet Grid]$ pwd /skynet1/source/kiran_oracle_patch_oct2015/Grid [oracle@skynet Grid]$ unzip p20996923_112040_Linux-x86-64.zip 5. Patch Oracle Grid Infrastructure and Oracle RAC Database Homes together, as "root" user: . oraenv -> +ASM $ORACLE_HOME/OPatch/opatch auto /stage/<UNZIPPED_PATCH_LOCATION> -ocmrf /tmp/ocm.rsp [oracle@skynet OPatch]$ cd /u01/app/oracle/product/11.2.0/grid/OPatch [oracle@skynet OPatch]$ su Password: [root@skynet OPatch]# [root@skynet OPatch]# ./opatch auto /skynet1/source/kiran_oracle_patch_oct2015/Grid/20996923 -ocmrf /tmp/ocm.rsp Executing /u01/app/oracle/product/11.2.0/grid/perl/bin/perl ./crs/patch11203.pl -patchdir /skynet1/source/kiran_oracle_patch_oct2015/Grid -patchn 20996923 -ocmrf /tmp/ocm.rsp -paramfile /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params This is the main log file: /u01/app/oracle/product/11.2.0/grid/cfgtoollogs/opatchauto2015-10-14_14-20-44.log This file will show your detected configuration and all the steps that opatchauto attempted to do on your system: /u01/app/oracle/product/11.2.0/grid/cfgtoollogs/opatchauto2015-10-14_14-20-44.report.log 2015-10-14 14:20:44: Starting Oracle Restart Patch Setup Using configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params Stopping RAC /u01/app/oracle/product/11.2.0/dbhome_1 ... Stopped RAC /u01/app/oracle/product/11.2.0/dbhome_1 successfully patch /skynet1/source/kiran_oracle_patch_oct2015/Grid/20996923/20760982 apply successful for home /u01/app/oracle/product/11.2.0/dbhome_1 patch /skynet1/source/kiran_oracle_patch_oct2015/Grid/20996923/20831122/custom/server/20831122 apply successful for home /u01/app/oracle/product/11.2.0/dbhome_1 Stopping CRS... Stopped CRS successfully patch /skynet1/source/kiran_oracle_patch_oct2015/Grid/20996923/20760982 apply successful for home /u01/app/oracle/product/11.2.0/grid patch /skynet1/source/kiran_oracle_patch_oct2015/Grid/20996923/20831122 apply successful for home /u01/app/oracle/product/11.2.0/grid patch /skynet1/source/kiran_oracle_patch_oct2015/Grid/20996923/20299019 apply successful for home /u01/app/oracle/product/11.2.0/grid Starting CRS... CRS-4123: Oracle High Availability Services has been started. Starting RAC /u01/app/oracle/product/11.2.0/dbhome_1 ... Started RAC /u01/app/oracle/product/11.2.0/dbhome_1 successfully opatch auto succeeded. You have new mail in /var/spool/mail/oracle *Note that this is the simplest way to Patch everything. Most of the time I have done this myself, but on more complex systems I choose to apply patches in several different steps, where first I patch Grid Infrastructure and if successful then Oracle Database Homes. 6. When Patching completes, check the screen and log files and confirm if it was successful. Check the software version: . oraenv -> +ASM $ORACLE_HOME/OPatch/opatch version (opatch version) $ORACLE_HOME/OPatch/opatch lspatches (show Patches only) $ORACLE_HOME/OPatch/opatch lsinventory (show Software details) [oracle@skynet OPatch]$ ./opatch version OPatch Version: 11.2.0.3.12 OPatch succeeded. [oracle@skynet OPatch]$ ./opatch lspatches 20299019;ACFS Patch set update : 11.2.0.4.6 (20299019) 20831122;OCW Patch Set Update : 11.2.0.4.7 (20831122) 20760982;Database Patch Set Update : 11.2.0.4.7 (20760982) OPatch succeeded. [oracle@skynet OPatch]$ ./opatch lsinventory Oracle Interim Patch Installer version 11.2.0.3.12 Copyright (c) 2015, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0/grid Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/11.2.0/grid/oraInst.loc OPatch version : 11.2.0.3.12 OUI version : 11.2.0.4.0 Log file location : /u01/app/oracle/product/11.2.0/grid/cfgtoollogs/opatch/opatch2015-10-14_15-09-05PM_1.log Lsinventory Output file location : /u01/app/oracle/product/11.2.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2015-10-14_15-09-05PM.txt -------------------------------------------------------------------------------- Local Machine Information:: Hostname: skynet.op.skynetglobal.net ARU platform id: 226 ARU platform description:: Linux x86-64 Installed Top-level Products (1): Oracle Grid Infrastructure 11g 11.2.0.4.0 There are 1 products installed in this Oracle Home. Interim patches (3) : Patch 20299019 : applied on Wed Oct 14 15:02:37 BST 2015 Unique Patch ID: 18573450 Patch description: "ACFS Patch set update : 11.2.0.4.6 (20299019)" Created on 27 Mar 2015, 15:26:30 hrs Bugs fixed: 17510275, 17172303, 16318126, 19690653, 17203009, 17376318, 17503605 20140148, 17611362, 17721778, 17164243, 19053182, 17696547, 17699423 17488768, 18915417, 18155334, 18321597, 19919907, 18168684, 17363999 17636008, 18143006, 17428148, 17070158, 17475946 Patch 20831122 : applied on Wed Oct 14 15:01:59 BST 2015 Unique Patch ID: 18923533 Patch description: "OCW Patch Set Update : 11.2.0.4.7 (20831122)" Created on 1 Jul 2015, 06:26:45 hrs PST8PDT Bugs fixed: 19270660, 18328800, 18691572, 20365005, 17750548, 17387214, 17617807 14497275, 17733927, 18180541, 18962892, 17292250, 17378618, 16759171 20110156, 17065496, 13991403, 17273020, 17155238, 18261183, 18053580 20218012, 17039197, 17947785, 16317771, 10052729, 20340620, 18199185 18399991, 20186278, 18024089, 20746251, 20246071, 14270845, 18414137 17001914, 17927970, 14378120, 16346413, 15986647, 18068871, 21222147 18143836, 16206997, 19168690, 20235511, 18343490, 16613232, 19276791 17722664, 12928658, 18520351, 16249829, 18226143, 18265482, 18229842 17172091, 17818075, 18231837, 14373486, 17483479, 18120545, 18729166 13843841, 21225209, 17405302, 18709496, 18330979, 18744838, 20531190 14525998, 18187697, 14385860, 18348155, 19479503, 12928592, 17516024 18370031, 17764053, 17551223, 14671408, 18272135, 14207615, 17500165 18875012, 18464784, 19558324, 18848125, 19241857, 14851828, 17955615 20315294, 14693336, 16284825, 17352230, 20014326, 17238586, 17089344 17405605, 17531342, 17159489, 17640316, 16543190, 17983675, 17481314 16281493, 18346135, 15986311, 17208793, 18700935, 18999857, 14076173 18428146, 18352845, 17435488, 18352846, 17391726, 17387779, 16206882 20141091, 17305100, 15832129, 19885321, 16901346, 17985714, 18536826 17780903, 18752378, 18946768, 16876500, 16875342, 19955755, 16429265 18336452, 17273003, 19319357, 17059927, 17046460, 18053631, 16867761 20235486, 15869775, 19642566, 17447588, 15920201 Patch 20760982 : applied on Wed Oct 14 14:56:51 BST 2015 Unique Patch ID: 18908105 Patch description: "Database Patch Set Update : 11.2.0.4.7 (20760982)" Created on 4 Jun 2015, 00:23:20 hrs PST8PDT Sub-patch 20299013; "Database Patch Set Update : 11.2.0.4.6 (20299013)" Sub-patch 19769489; "Database Patch Set Update : 11.2.0.4.5 (19769489)" Sub-patch 19121551; "Database Patch Set Update : 11.2.0.4.4 (19121551)" Sub-patch 18522509; "Database Patch Set Update : 11.2.0.4.3 (18522509)" Sub-patch 18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)" Sub-patch 17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)" Bugs fixed: 17288409, 21051852, 18607546, 17205719, 17811429, 17816865, 20506699 17922254, 17754782, 16934803, 13364795, 17311728, 17441661, 17284817 16992075, 17446237, 14015842, 19972569, 17449815, 17375354, 19463897 17982555, 17235750, 13866822, 18317531, 17478514, 18235390, 14338435 20803583, 13944971, 20142975, 17811789, 16929165, 18704244, 20506706 17546973, 20334344, 14054676, 17088068, 18264060, 17346091, 17343514 19680952, 18471685, 19211724, 13951456, 16315398, 18744139, 16850630 19049453, 18673304, 17883081, 19915271, 18641419, 18262334, 17006183 18277454, 16833527, 10136473, 18051556, 17865671, 17852463, 18554871 17853498, 18334586, 17588480, 17551709, 19827973, 17842825, 17344412 18828868, 17025461, 11883252, 13609098, 17239687, 17602269, 19197175 18316692, 17313525, 12611721, 19544839, 18964939, 17600719, 18191164 19393542, 17571306, 20777150, 18482502, 19466309, 17040527, 17165204 18098207, 16785708, 17174582, 16180763, 17465741, 16777840, 12982566 19463893, 12816846, 16875449, 17237521, 19358317, 17811438, 17811447 17945983, 18762750, 17184721, 16912439, 18061914, 17282229, 18331850 18202441, 17082359, 18723434, 19554106, 14034426, 18339044, 19458377 17752995, 17891943, 17258090, 17767676, 16668584, 18384391, 17040764 17381384, 15913355, 18356166, 14084247, 20506715, 13853126, 18203837 14245531, 16043574, 17848897, 17877323, 17468141, 17786518, 17912217 17037130, 18155762, 16956380, 17478145, 17394950, 18189036, 18641461 18619917, 17027426, 16268425, 19584068, 18436307, 17265217, 17634921 13498382, 20004087, 17443671, 18000422, 20004021, 17571039, 21067387 16344544, 18009564, 14354737, 18135678, 18614015, 18362222, 17835048 16472716, 17936109, 17050888, 17325413, 14010183, 18747196, 17761775 16721594, 17082983, 20067212, 21179898, 17302277, 18084625, 15990359 18203835, 17297939, 16731148, 17811456, 17215560, 13829543, 14133975 17694209, 18091059, 17385178, 8322815, 17586955, 17201159, 17655634 18331812, 19730508, 18868646, 17648596, 16220077, 16069901, 17348614 17393915, 17957017, 17274537, 18096714, 17308789, 18436647, 14285317 19289642, 14764829, 18328509, 17622427, 16943711, 14368995, 17346671 18996843, 17783588, 16618694, 17672719, 18856999, 18783224, 17851160 17546761, 17798953, 18273830, 19972566, 16384983, 17726838, 17360606 13645875, 18199537, 16542886, 17889549, 14565184, 17071721, 20299015 17610798, 20657441, 17397545, 18230522, 16360112, 19769489, 12905058 18641451, 12747740, 18430495, 17042658, 17016369, 14602788, 19972568 19788842, 18508861, 14657740, 17332800, 13837378, 19972564, 17186905 18315328, 19699191, 17437634, 19006849, 19013183, 17296856, 18674024 17232014, 16855292, 21051840, 14692762, 17762296, 17705023, 19121551 19854503, 19309466, 18681862, 20558005, 17390160, 18554763, 18456514 13955826, 16306373, 18139690, 17501491, 17299889, 17752121, 17889583 18673325, 18293054, 17242746, 17951233, 17649265, 18094246, 19615136 17011832, 16870214, 17477958, 18522509, 20631274, 16091637, 17323222 16595641, 16524926, 18228645, 18282562, 17596908, 17156148, 18031668 16494615, 17545847, 17614134, 13558557, 17341326, 17891946, 17716305 16392068, 19271443, 18092127, 17614227, 18440047, 14106803, 16903536 18973907, 18673342, 17389192, 16194160, 17006570, 17612828, 17721717 17570240, 17390431, 16863422, 18325460, 19727057, 16422541, 17267114 19972570, 18244962, 18765602, 18203838, 16198143, 17246576, 14829250 17835627, 18247991, 14458214, 21051862, 16692232, 17786278, 17227277 16042673, 16314254, 16228604, 16837842, 17393683, 17787259, 20331945 20074391, 15861775, 16399083, 18018515, 21051858, 18260550, 17036973 16613964, 17080436, 16579084, 18384537, 18280813, 20296213, 16901385 15979965, 18441944, 16450169, 9756271, 17892268, 11733603, 17587063 16285691, 16538760, 18180390, 18193833, 21051833, 17238511, 17824637 16571443, 18306996, 14852021, 18674047, 17853456, 12364061 -------------------------------------------------------------------------------- OPatch succeeded. [oracle@skynet OPatch]$ . oraenv -> DB SID $ORACLE_HOME/OPatch/opatch version (opatch version) $ORACLE_HOME/OPatch/opatch lspatches (show Patches only) $ORACLE_HOME/OPatch/opatch lsinventory (show Software details) [oracle@skynet OPatch]$ ./opatch version OPatch Version: 11.2.0.3.12 OPatch succeeded. [oracle@skynet OPatch]$ ./opatch lspatches 20831122;OCW Patch Set Update : 11.2.0.4.7 (20831122) 20760982;Database Patch Set Update : 11.2.0.4.7 (20760982) OPatch succeeded. [oracle@skynet OPatch]$ ./opatch lsinventory Oracle Interim Patch Installer version 11.2.0.3.12 Copyright (c) 2015, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc OPatch version : 11.2.0.3.12 OUI version : 11.2.0.4.0 Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-10-14_15-10-17PM_1.log Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2015-10-14_15-10-17PM.txt -------------------------------------------------------------------------------- Local Machine Information:: Hostname: skynet.op.skynetglobal.net ARU platform id: 226 ARU platform description:: Linux x86-64 Installed Top-level Products (1): Oracle Database 11g 11.2.0.4.0 There are 1 products installed in this Oracle Home. Interim patches (2) : Patch 20831122 : applied on Wed Oct 14 14:27:35 BST 2015 Unique Patch ID: 18923533 Patch description: "OCW Patch Set Update : 11.2.0.4.7 (20831122)" Created on 1 Jul 2015, 06:26:39 hrs PST8PDT Bugs fixed: 19270660, 18328800, 18691572, 20365005, 17750548, 17387214, 17617807 14497275, 17733927, 18180541, 18962892, 17292250, 17378618, 16759171 20110156, 17065496, 13991403, 17273020, 17155238, 18261183, 18053580 20218012, 17039197, 17947785, 16317771, 10052729, 20340620, 18199185 18399991, 20186278, 18024089, 20746251, 20246071, 14270845, 18414137 17001914, 17927970, 14378120, 16346413, 15986647, 18068871, 21222147 18143836, 16206997, 19168690, 20235511, 18343490, 16613232, 19276791 17722664, 12928658, 18520351, 16249829, 18226143, 18265482, 18229842 17172091, 17818075, 18231837, 14373486, 17483479, 18120545, 18729166 13843841, 21225209, 17405302, 18709496, 18330979, 18744838, 20531190 14525998, 18187697, 14385860, 18348155, 19479503, 12928592, 17516024 18370031, 17764053, 17551223, 14671408, 18272135, 14207615, 17500165 18875012, 18464784, 19558324, 18848125, 19241857, 14851828, 17955615 20315294, 14693336, 16284825, 17352230, 20014326, 17238586, 17089344 17405605, 17531342, 17159489, 17640316, 16543190, 17983675, 17481314 16281493, 18346135, 15986311, 17208793, 18700935, 18999857, 14076173 18428146, 18352845, 17435488, 18352846, 17391726, 17387779, 16206882 20141091, 17305100, 15832129, 19885321, 16901346, 17985714, 18536826 17780903, 18752378, 18946768, 16876500, 16875342, 19955755, 16429265 18336452, 17273003, 19319357, 17059927, 17046460, 18053631, 16867761 20235486, 15869775, 19642566, 17447588, 15920201 Patch 20760982 : applied on Wed Oct 14 14:26:47 BST 2015 Unique Patch ID: 18908105 Patch description: "Database Patch Set Update : 11.2.0.4.7 (20760982)" Created on 4 Jun 2015, 00:23:20 hrs PST8PDT Sub-patch 20299013; "Database Patch Set Update : 11.2.0.4.6 (20299013)" Sub-patch 19769489; "Database Patch Set Update : 11.2.0.4.5 (19769489)" Sub-patch 19121551; "Database Patch Set Update : 11.2.0.4.4 (19121551)" Sub-patch 18522509; "Database Patch Set Update : 11.2.0.4.3 (18522509)" Sub-patch 18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)" Sub-patch 17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)" Bugs fixed: 17288409, 21051852, 18607546, 17205719, 17811429, 17816865, 20506699 17922254, 17754782, 16934803, 13364795, 17311728, 17441661, 17284817 16992075, 17446237, 14015842, 19972569, 17449815, 17375354, 19463897 17982555, 17235750, 13866822, 18317531, 17478514, 18235390, 14338435 20803583, 13944971, 20142975, 17811789, 16929165, 18704244, 20506706 17546973, 20334344, 14054676, 17088068, 18264060, 17346091, 17343514 19680952, 18471685, 19211724, 13951456, 16315398, 18744139, 16850630 19049453, 18673304, 17883081, 19915271, 18641419, 18262334, 17006183 18277454, 16833527, 10136473, 18051556, 17865671, 17852463, 18554871 17853498, 18334586, 17588480, 17551709, 19827973, 17842825, 17344412 18828868, 17025461, 11883252, 13609098, 17239687, 17602269, 19197175 18316692, 17313525, 12611721, 19544839, 18964939, 17600719, 18191164 19393542, 17571306, 20777150, 18482502, 19466309, 17040527, 17165204 18098207, 16785708, 17174582, 16180763, 17465741, 16777840, 12982566 19463893, 12816846, 16875449, 17237521, 19358317, 17811438, 17811447 17945983, 18762750, 17184721, 16912439, 18061914, 17282229, 18331850 18202441, 17082359, 18723434, 19554106, 14034426, 18339044, 19458377 17752995, 17891943, 17258090, 17767676, 16668584, 18384391, 17040764 17381384, 15913355, 18356166, 14084247, 20506715, 13853126, 18203837 14245531, 16043574, 17848897, 17877323, 17468141, 17786518, 17912217 17037130, 18155762, 16956380, 17478145, 17394950, 18189036, 18641461 18619917, 17027426, 16268425, 19584068, 18436307, 17265217, 17634921 13498382, 20004087, 17443671, 18000422, 20004021, 17571039, 21067387 16344544, 18009564, 14354737, 18135678, 18614015, 18362222, 17835048 16472716, 17936109, 17050888, 17325413, 14010183, 18747196, 17761775 16721594, 17082983, 20067212, 21179898, 17302277, 18084625, 15990359 18203835, 17297939, 16731148, 17811456, 17215560, 13829543, 14133975 17694209, 18091059, 17385178, 8322815, 17586955, 17201159, 17655634 18331812, 19730508, 18868646, 17648596, 16220077, 16069901, 17348614 17393915, 17957017, 17274537, 18096714, 17308789, 18436647, 14285317 19289642, 14764829, 18328509, 17622427, 16943711, 14368995, 17346671 18996843, 17783588, 16618694, 17672719, 18856999, 18783224, 17851160 17546761, 17798953, 18273830, 19972566, 16384983, 17726838, 17360606 13645875, 18199537, 16542886, 17889549, 14565184, 17071721, 20299015 17610798, 20657441, 17397545, 18230522, 16360112, 19769489, 12905058 18641451, 12747740, 18430495, 17042658, 17016369, 14602788, 19972568 19788842, 18508861, 14657740, 17332800, 13837378, 19972564, 17186905 18315328, 19699191, 17437634, 19006849, 19013183, 17296856, 18674024 17232014, 16855292, 21051840, 14692762, 17762296, 17705023, 19121551 19854503, 19309466, 18681862, 20558005, 17390160, 18554763, 18456514 13955826, 16306373, 18139690, 17501491, 17299889, 17752121, 17889583 18673325, 18293054, 17242746, 17951233, 17649265, 18094246, 19615136 17011832, 16870214, 17477958, 18522509, 20631274, 16091637, 17323222 16595641, 16524926, 18228645, 18282562, 17596908, 17156148, 18031668 16494615, 17545847, 17614134, 13558557, 17341326, 17891946, 17716305 16392068, 19271443, 18092127, 17614227, 18440047, 14106803, 16903536 18973907, 18673342, 17389192, 16194160, 17006570, 17612828, 17721717 17570240, 17390431, 16863422, 18325460, 19727057, 16422541, 17267114 19972570, 18244962, 18765602, 18203838, 16198143, 17246576, 14829250 17835627, 18247991, 14458214, 21051862, 16692232, 17786278, 17227277 16042673, 16314254, 16228604, 16837842, 17393683, 17787259, 20331945 20074391, 15861775, 16399083, 18018515, 21051858, 18260550, 17036973 16613964, 17080436, 16579084, 18384537, 18280813, 20296213, 16901385 15979965, 18441944, 16450169, 9756271, 17892268, 11733603, 17587063 16285691, 16538760, 18180390, 18193833, 21051833, 17238511, 17824637 16571443, 18306996, 14852021, 18674047, 17853456, 12364061   -------------------------------------------------------------------------------- OPatch succeeded. etc. 7. Patch post-installation instructions. These are the steps you need to run on every Database part of the Oracle Database Homes you just patched. . oraenv -> DB SID cd $ORACLE_HOME/rdbms/admin sqlplus / as sysdba SQL> @catbundle.sql psu apply /u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_REPODEV_APPLY_2015Oct14_15_15_18.log /u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_SANADEV_APPLY_2015Oct14_15_19_13.log /u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_S82DEV1_APPLY_2015Oct14_15_57_27.log /u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_S82DEV2_APPLY_2015Oct14_16_02_20.log SQL> @utlrp.sql Check Patch status in the database: SQL> select * from dba_registry_history; SQL> set lines 140 pages 100 SQL> col Comp_name Format a40 SQL> col Status Format a12 SQL> select Comp_name, status, Version From Dba_Registry order by Comp_name; (Check that all Components are VALID!) !!! Notice that: If you are using the Oracle Recovery Manager, the catalog needs to be upgraded. Enter the following command to upgrade it: $ rman catalog username/password@alias RMAN> UPGRADE CATALOG; And you're done! 🙂  

Oracle OpenWorld 2015

Oracle Openworld is an annual Oracle convention for business decision-makers, IT management, and line of business end users. The conference normally takes place around mid-September, this year it was late October. Presentations take place by Oracle, Consulting companies as well as community members. I believe the presentations by the community provide the best information. Around February/March I submitted a paper to present, unfortunately was not selected this year. This year Oracle were looking for the word cloud in the topics. The extent people go to so that there paper is selected is amazing. I was speaking to one person and they submitted the same paper 3 times worded differently, 1 of them had the word cloud in the title and it was accepted. So it’s very important that you pay attention to what they are looking for and base your presentation around these areas. I have to say I really enjoy going to Openworld and this was my fourth time, you will always learn new things and also get to network with different Oracle product teams as well as community members all in 1 place. On my flight over, I think half the people on the plane were attending Openworld and it was great to pass the time speaking to various community members on the flight over to San Francisico. For me, the following announcements were important, If you are thinking of upgrading, I have marked the key benefits to upgrade with a ‘*’: 1) OBIEE 12C - Oracle BI 12c is now generally available. BI 12c is the strategic foundation of Oracle’s analytics platform, delivering significant new value and lower total cost of ownership. *Easy to upgrade: BI 12c offers a radically simple and robust upgrade from 11g, saving customers time and effort in moving to the new version. BI 12c includes a free utility to automate regression testing, the Baseline Validation Tool, which verifies data, visuals, catalog object metadata, and system-generated SQL across 11g and 12c releases. *Faster: Sophisticated in-memory processing includes BI Server optimizations and support for multiple in-memory data stores, while in-memory Essbase on Exalytics offers enhanced concurrency and scalability, as well as significant performance gains. *Friendlier: Usability updates throughout BI 12c demonstrate Oracle’s continued commitment to making analytics as fast, flexible, and friendly as they are powerful and robust. A new user interface simplifies the layout for the homepage, Answers, and Dashboards, making it easier for users to quickly see what’s important; HTML-5 graphics improve view rendering; and it’s easier for users to create new groups, calculations, and measures, for simpler, more direct interaction with results. (Chargeable) More Visual: A consistent set of Data Visualization capabilities are now available across Oracle BI Cloud Service and Oracle BI 12c, as well as the upcoming Oracle Data Visualization Cloud Service, offering customers a continuity of visual experience unmatched by our competitors. Business users can point and click to upload personal data and blend it with IT-managed data in BI 12c, which automatically infers connections between data sets. Visualizing data is as easy as dragging attributes onto the screen, with optimal visualizations automatically displayed – no modeling or upfront configuration required. Related data is automatically connected, so highlighting data in one visual highlights correlated data in every other visual, immediately showing patterns and revealing new insights. These insights, along with narrative comments, can be shared as interactive visual stories, enabling seamless collaboration that drives fact-based decisions. – This is an additional license if you want to use the Visual Analyzer (VA) which is a shame, you will need to speak to your local Oracle Representative for pricing. *More Advanced: Predictive analysis is more tightly integrated, enabling customers to more easily forecast future conditions based on existing data points, group elements that are statistically similar, and expose outliers. BI 12c includes the ability to run the free Oracle R distribution on BI Server, and extend existing analytics with custom R scripts, which can point to any engine (R, Oracle Database, Spark, etc.) without needing to change the BI RPD to deliver results. More Mobile: Keyword search (“BI Ask”) empowers users to literally talk to their data, asking questions and having visualizations automatically created as responses, opening up an easy entry point for authoring. Additionally, the interface for iOS has been completely redesigned; and Mobile BI for Android offers sharing and following for nearby devices, as well as the ability to project any dashboard or story to GoogleCast-enabled devices. Bigger Data: BI 12c enables customers to use new data, from local, Oracle, and Big Data sources, including personal files uploaded by users; direct access to data in Hyperion Financial Management and Hyperion Planning applications; and ODBC access to Cloudera Impala. Apache Spark will be accessible via native Spark SQL in an upcoming update. Higher ROI, Lower TCO: Streamlined administration and life cycle management reduce the time and resources required to manage BI 12c, decreasing costs and increasing value for this and future releases. Enhancements include separating metadata from configuration; simpler, more robust security; easier backup, restore, and disaster recovery; hot patching of metadata; and many more. 2) Oracle Database 12c Release 2 - Beta Continuing to lead the database industry with ongoing innovations, Oracle today introduced beta availability for Oracle Database 12c Release 2. With its unique multitenant architecture and dual format in-memory database technology, Oracle Database 12c is already helping customers embrace the cloud, deliver real-time analytics and harness Big Data. Oracle Database 12c Release 2 further simplifies the journey to the cloud and provides efficient delivery of database services, with greater consolidation density and more administrative agility at cloud scale. Focus areas that are planned for Oracle Database 12c Release 2 include: * Improved Agility and Capacity with Enhancements to Oracle Multitenant: Planned enhancements include online movement of pluggable databases between servers, data centers and the Oracle Cloud. * Refinements to Oracle Database In-Memory: Features to boost performance, automate management, now you can use in-memory on your active standby Enhanced Support for Big DataAnalytics: In addition to enhancements to Database In-Memory, plans include spatial performance improvements and improved support for graph analytics. Hundreds of other new features: Drivers for all the key programming languages and frameworks have been enhanced. The release includes improvements to all the other main areas of customer interest including security, high availability, scalability, performance, and manageability. If you were thinking of going, make sure you get ready for OpenWorld 2016, which will take place around 16th-20th September 2016.

EXPDP and IMPDP with Query and REMAP_TABLE

Here we have a table called requests under the SAMPLE schema, what we are going to do is export it and then re import changing its name and the tablespace with a filter. 1. Create export par file and export - 2. Create tablespace for reimport 3. Create import par file and import Summary We have exported and imported a table into a different tablespace, but at the same time renamed the tables using the REMAP_TABLE and REMAP_TABLESPACE parameters.

ORA-01950: no privileges on tablespace ‘X’

Problem: A user called JIM has left your organisation, you have revoked their roles to the database, 4 days later during a restart of your application, some people start reporting 'ORA-01950: no privileges on tablespace 'ORA-01950: no privileges on tablespace X', whats happened. You have checked the owners being used in the application, and tried to insert into the table a row using the user. They all have the roles that have permissions to the table space. They have unlimited quotas Lets look deeper to the schema We revoked JIM's access when he left but he still has objects in the application schema. For sure if new extents are going to be allocated for this object, it will fail with the no privilege error. It may still work for some users because the already allocated extent space for JIM can be used without any issues.

Identifying Top SQL using statspack

So you don't have diagnostic or the tuning pack for your databases, but you have started collecting information using Oracle Statspack that i mentioned in one of my previous posts. As well as running normal reports, you can also run sql directly against the tables to gather information on SQL Performance.   If you log into your DB using the user collecting the stats and run the following sql.
You will see a list like this:
Ideally you want to import this in Excel, then filter.  This allow you to order on any of the columns such as the sql that took the longest, to what sql was being executed repeatedly and taking time.  Thereafter find the sql using the sql hash value to see the full sql.  You can amend the sql to choose how many days you want to go back, the above sql looks back 24 hours.

ERROR: -14(asmlib /opt/oracle/extapi/64/asm/orcl/1/libasm.so version failed with 2

So you have decided to patch your OS, after patching you cannot start the ASM, when you look into the alert log for the ASM you see the following: Errors in file /u01/app/oracle/product/11.2.0/grid/log/diag/asm/+asm/+ASM/trace/+ASM_rbal_5702.trc: ORA-15183: ASMLIB initialization error [driver/agent not installed] WARNING: FAILED to load library: /opt/oracle/extapi/64/asm/orcl/1/libasm.so ERROR: no read quorum in group: required 2, found 0 disks NOTE: cache dismounting (clean) group 1/0xC9C86BB1 (DATA) NOTE: messaging CKPT to quiesce pins Unix process pid: 5733, NOTE: dbwr not being msg'd to dismount NOTE: lgwr not being msg'd to dismount NOTE: cache dismounted group 1/0xC9C86BB1 (DATA) NOTE: cache ending mount (fail) of group DATA number=1 incarn=0xc9c86bb1 NOTE: cache deleting context for group DATA 1/0xc9c86bb1 Tue Jun 09 17:28:12 2015 ERROR: -14(asmlib /opt/oracle/extapi/64/asm/orcl/1/libasm.so version failed with 2 ) Check the following as it could be the kernal: # uname -rm 2.6.18-406.el5 x86_64 # rpm -qa |grep asm oracleasm-2.6.18-371.3.1.el5-2.0.5-1.el5 oracleasm-support-2.1.8-1.el5 oracleasmlib-2.0.4-1.el5 We can see from the above commands that the kernal has changed to 2.6.18-406 and we only have the packages installed for 2.6.18-371.  We need to download the package. Navigate to : http://www.oracle.com/technetwork/server-storage/linux/downloads/rhel5-084877.html Scroll down to your architecture for example Intel EM64T (x86_64) Architecture Library and Tools From here we can see in the list the following : oracleasm-2.6.18-406.el5-2.0.5-1.el5.x86_64.rpm - This matches our kernal from our OS command earlier, download this. # rpm -Uvh oracleasm-2.6.18-406.el5-2.0.5-1.el5.x86_64.rpm warning: oracleasm-2.6.18-406.el5-2.0.5-1.el5.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159 Preparing... ########################################### [100%] 1:oracleasm-2.6.18-406.el########################################### [100%] # /etc/init.d/oracleasm start Initializing the Oracle ASMLib driver: [ OK ] Scanning the system for Oracle ASMLib disks: [ OK ] The ASM is up and running again!!!      

What is your hot tables in the database

The other day, an analyst came to me and asked can you tell me currently what are the hot tables, and I remembered the v$segment_statistics, this has some interesting data. Total Reads happening: SELECT t.owner,t.table_name,lr.value as logical_reads, pr.value as physical_reads, lr.value+pr.value AS total_reads FROM (SELECT owner,object_name,value FROM v$segment_statistics WHERE statistic_name='logical reads') lr, (SELECT owner,object_name,value FROM v$segment_statistics WHERE statistic_name='physical reads') pr, dba_tables t WHERE lr.owner=pr.owner AND lr.object_name=pr.object_name AND lr.owner=t.owner AND lr.object_name=t.table_name AND lr.value+pr.value > 79991 ORDER BY 5 desc; Total Writes: select segment_name,object_type,total_physical_writes from ( select owner||'.'||object_name as segment_name,object_type, value as total_physical_writes from v$segment_statistics where statistic_name in ('physical writes') order by total_physical_writes desc) where rownum <=20; A Jewel.