Archive for October, 2013

11.2.0.4 patchset released for AIX & HP-UX few days back. Patchset number is 13390677. Below is screen shot for same.

11.2.0.4_Patchset

Advertisements

There was some discussion going on about the fifth digit in version is getting updated or not after applying PSU patch. So, i thought of mentioning this topic here. Answer to this discussion is NO. As per DOC 861152.1, fifth digit is not updated after applying PSU. But i have seen a bit change in behaviour of “opatch lsinventory” command in pre 11.2.0.3 & post 11.2.0.3 DB’s. Database versions prior to 11.2.0.3 doesn’t show the PSU version information in “optach lsinventory” command as shown below:


/u01/app/oranine/product/11.2.0/dbhome_1/rdbms/admin => opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.5
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oranine/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oranine/product/oraInventory
   from           : /u01/app/oranine/product/11.2.0/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.5
OUI version       : 11.2.0.1.0
Log file location : /u01/app/oranine/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2013-10-16_12-23-49PM_1.log

Lsinventory Output file location : /u01/app/oranine/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2013-10-16_12-23-49PM.txt

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

Oracle Database 11g                                                  11.2.0.1.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch  9952216      : applied on Wed Oct 16 09:34:55 GMT+01:00 2013
Unique Patch ID:  12948321
   Created on 17 Sep 2010, 03:23:29 hrs PST8PDT
   Bugs fixed:
     9068088, 9363384, 8865718, 8898852, 8801119, 9054253, 8725286, 8974548
     9093300, 8909984, 8755082, 8780372, 9952216, 8664189, 8769569, 7519406
     9471411, 9302343, 8822531, 7705591, 8650719, 9637033, 8883722, 8639114
     8723477, 8729793, 8919682, 8856478, 9001453, 8733749, 8565708, 8735201
     8684517, 8870559, 8773383, 8981059, 8812705, 9488887, 8813366, 9242411
     8822832, 8897784, 8760714, 8775569, 8671349, 8898589, 9714832, 8642202
     9011088, 9369797, 9170608, 9165206, 8834636, 8891037, 8431487, 8570322
     8685253, 8872096, 8718952, 8799099, 9032717, 9399090, 9713537, 9546223
     8588519, 8783738, 8834425, 9454385, 8856497, 8890026, 8721315, 8818175
     8674263, 9145541, 8720447, 9272086, 9467635, 9010222, 9102860, 9197917
     8991997, 8661168, 8803762, 8769239, 9654983, 8546356, 8706590, 8778277
     9058865, 8815639, 9971778, 9971779, 9027691, 9454036, 9454037, 9454038
     9255542, 8761974, 9275072, 8496830, 8702892, 8818983, 8475069, 8875671
     9328668, 8891929, 8798317, 8782959, 9971780, 8774868, 8820324, 8544696
     8702535, 9406607, 9952260, 8268775, 9036013, 9363145, 8933870, 8405205
     9467727, 8822365, 9676419, 8761260, 8790767, 8795418, 8913269, 8717461
     8861700, 9531984, 8607693, 8330783, 8780281, 8784929, 8780711, 9341448
     9015983, 8828328, 9119194, 8832205, 8665189, 8717031, 9482399, 9676420
     9399991, 8821286, 8633358, 9321701, 9231605, 9655013, 8796511, 9167285
     8782971, 8756598, 9390484, 8703064, 9066116, 9007102, 9461782, 9382101
     8505803, 9352237, 8753903, 9216806, 8918433, 9057443, 8790561, 8795792
     8733225, 9067282, 8928276, 8837736, 9210925

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

OPatch succeeded.

We can use the below command to check the latest version of PSU that is applied on database. Below is output from 11.2.0.1 database on which PSU version 3 is applied.


/u01/app/oranine/product/11.2.0/dbhome_1/rdbms/admin => opatch lsinventory -bugs_fixed | egrep 'PSU|PATCH SET UPDATE'
8974548    9952216   Wed Oct 16 09:34:55 GMT+01:00 2013BACKOUT BUG 7438445 FROM PSU 11.1.0.7.1 RELEASE LA
9352237    9952216   Wed Oct 16 09:34:55 GMT+01:00 2013DATABASE PSU 11.2.0.1.1
9654983    9952216   Wed Oct 16 09:34:55 GMT+01:00 2013DATABASE PSU 11.2.0.1.2 (INCLUDES CPUJUL2010)
9952216    9952216   Wed Oct 16 09:34:55 GMT+01:00 2013DATABASE PSU 11.2.0.1.3 (INCLUDES CPUOCT2010)

Now, below is output from 11.2.0.3 database which has a patch description section which contains the information about the latest PSU applied. In 11.2.0.3 the fifth digit also not updated but at least information is displayed.


/u01/app/oranine/product/11.2.0.3/rdbms/admin => opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.5
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oranine/product/11.2.0.3
Central Inventory : /u01/app/oranine/product/oraInventory
   from           : /u01/app/oranine/product/11.2.0.3/oraInst.loc
OPatch version    : 11.2.0.3.5
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oranine/product/11.2.0.3/cfgtoollogs/opatch/opatch2013-10-16_07-05-24AM_1.log

Lsinventory Output file location : /u01/app/oranine/product/11.2.0.3/cfgtoollogs/opatch/lsinv/lsinventory2013-10-16_07-05-24AM.txt

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

Oracle Database 11g                                                  11.2.0.3.0
There are 1 products installed in this Oracle Home.


Interim patches (2) :

Patch  16902043     : applied on Wed Oct 16 06:42:40 GMT+01:00 2013
Unique Patch ID:  16738638
Patch description:  "Database Patch Set Update : 11.2.0.3.8 (16902043)"
   Created on 26 Sep 2013, 03:40:47 hrs PST8PDT
Sub-patch  16619892; "Database Patch Set Update : 11.2.0.3.7 (16619892)"
Sub-patch  16056266; "Database Patch Set Update : 11.2.0.3.6 (16056266)"
Sub-patch  14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)"
Sub-patch  14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch  13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch  13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch  13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
   Bugs fixed:
     12960925, 14088346, 14038787, 14469008, 16710324, 14301592, 13834065
     12834027, 16703112, 12764337, 13772618, 14390252, 14263036, 10133521
     13561750, 13588248, 12815057, 12894807, 16344758, 14841812, 12748538
     13742435, 13773133, 13742434, 12829021, 9659614, 17333199, 13742433
     12585543, 17333197, 17333198, 13742438, 13742437, 13561951, 14841558
     13742436, 12905058, 13503598, 12582664, 16344871, 13632717, 13098318
     13343438, 12861463, 16362358, 13913630, 14523004, 14188650, 13026410
     12849688, 13080778, 14467061, 12747437, 16530565, 13103913, 13457582
     13737746, 17333203, 13742464, 12873183, 14128555, 13645917, 13099577
     17333200, 13036331, 17333202, 12693626, 14548763, 12656535, 13907462
     12678920, 13624984, 12401111, 17082364, 16742095, 13377816, 13855490
     13338048, 13910420, 12913474, 13489024, 12755116, 13860201, 11063191
     11877623, 12964067, 16306019, 13250244, 15905421, 12847466, 12797765
     13791364, 14613900, 14755945, 14393728, 13038684, 13582702, 12617123
     14023636, 12923168, 13035360, 13814739, 14791477, 13420224, 16175381
     12646784, 12857027, 13340388, 14409183, 12583611, 11868640, 14589750
     12794305, 12821418, 13972394, 10350832, 14512189, 13584130, 14480674
     14480675, 12998795, 13680405, 14480676, 14095982, 12588744, 13467683
     10242202, 12594032, 13645875, 16794238, 13001379, 16794239, 14351566
     14189694, 12797420, 13787482, 13257247, 14751895, 14841409, 13041324
     13366202, 13385346, 13011409, 13326736, 13981051, 14664355, 16794243
     16794244, 16794241, 15862018, 16794242, 15862017, 15862016, 13466801
     16794240, 14205448, 9397635, 15862019, 15841373, 12899768, 16619892
     12791981, 13725395, 14063281, 14571027, 14063280, 13362079, 13732226
     13384182, 13719081, 8547978, 15862020, 15862021, 13642044, 15862023
     13496884, 15862022, 14220725, 13958038, 15862024, 9703627, 9858539
     14053457, 12940620, 13923995, 12780098, 13945708, 13354082, 16024441
     14062797, 14207163, 14062796, 14052474, 14062795, 14062794, 13534412
     14062793, 13579992, 14062792, 16056266, 17230530, 12345082, 13060271
     12784406, 13550185, 14176879, 14007968, 12880299, 16314469, 16314468
     16314467, 16314466, 12612118, 13502183, 13059165, 11071989, 15869211
     13593999, 9706792, 16294378, 13916709, 13397104, 13524899, 13848402
     9761357, 12621588, 13657605, 16314470, 14110275, 13936424, 16710363
     12796518, 9873405, 12535346, 12974860, 14727310, 12971775, 14472647
     14398795, 16014985, 13686047, 13605839, 13696216, 13493847, 12312133
     16902043, 13807411, 16382353, 13591624, 12917230, 13685544, 13857111
     12983611, 12938841, 13786142, 14207317, 13499128, 14546673, 14127231
     11708510, 13699124, 14040433, 14198511, 16299830, 13440516, 14546575
     13705338, 14762511, 12755231, 12662040, 14003090, 12658411, 13596521
     9547706, 16368108, 14262913, 12718090, 13724193, 14695377, 13790109
     12959852, 16382448, 14035825, 12919564, 12780983, 12912137, 17332800
     13483354, 12950644, 13454210, 16372203, 14258925, 13544396, 13903046
     13810393, 13923374, 13063120, 13572659, 13370330, 16231699, 12731940
     13427062, 9095696, 14275605, 12772404, 14459552, 13911821, 13464002
     13914613, 15853081, 13528551, 13612575, 13072654, 12620823, 14076523
     14668670, 13358781, 13632809, 16694777, 13649031, 14263073, 13040943
     16279401, 14226599, 14138130, 13527323, 13804294, 13492735, 12925089
     13015379, 12395918, 13843646, 7509451, 13332439, 13718279, 13035804
     13812031, 6690853, 13616375, 13092220, 11715084, 14273397, 14644185
     14191508, 13559697, 13448206, 13419660, 13399435, 14546638, 13070939
     12845115, 12976376, 11840910, 13723052, 13566938, 13430938, 12748240
     16212405, 12865902, 12879027, 12744759, 16279211, 10263668, 13476583
     13484963, 15910002, 13554409

Patch  12973504     : applied on Wed Oct 02 18:49:33 GMT+01:00 2013
Unique Patch ID:  14204385
   Created on 21 Oct 2011, 07:20:00 hrs PST8PDT
   Bugs fixed:
     11772716, 9777682



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

OPatch succeeded.

Hope, this will help in future discussions on this topic.

While applying a patch on a database i got the below error:


/u01/app/oranine/product/patch/9952216 => opatch apply
Oracle Interim Patch Installer version 11.2.0.3.5
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oranine/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oranine/product/11.2.0/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.5
OUI version       : 11.2.0.1.0
Log file location : /u01/app/oranine/product/11.2.0/dbhome_1/cfgtoollogs/opatch/9952216_Oct_16_2013_08_18_25/apply2013-10-16_08-18-25AM_1.log

Applying interim patch '9952216' to OH '/u01/app/oranine/product/11.2.0/dbhome_1'
Verifying environment and performing prerequisite checks...
OiiolLogger.addFileHandler:Error while adding file handler - /u01/app/oraInventory/logs/OPatch2013-10-16_08-18-26-AM.log
java.io.FileNotFoundException: /u01/app/oraInventory/logs/OPatch2013-10-16_08-18-26-AM.log (No such file or directory)
Unable to lock Central Inventory.  OPatch will attempt to re-lock.
Do you want to proceed? [y|n]
n
User Responded with: N
Unable to lock Central Inventory.  Stop trying per user-request?
OPatchSession cannot load inventory for the given Oracle Home /u01/app/oranine/product/11.2.0/dbhome_1. Possible causes are:
   No read or write permission to ORACLE_HOME/.patch_storage
   Central Inventory is locked by another OUI instance
   No read permission to Central Inventory
   The lock file exists in ORACLE_HOME/.patch_storage
   The Oracle Home does not exist in Central Inventory

OPatch failed: ApplySession failed to prepare the system. Unable to lock Central Inventory.  Stop trying per user-request?
Log file location: /u01/app/oranine/product/11.2.0/dbhome_1/cfgtoollogs/opatch/9952216_Oct_16_2013_08_18_25/apply2013-10-16_08-18-25AM_1.log

OPatch failed with error code 22

As per error i checked and found that i have read, write permission on inventory location. Then i checked the inventory.xml file in ContentsXML folder located in central inventory location and found that there is no entry for this ORACLE_HOME. Below are steps to attach a ORACLE_HOME to central inventory:


./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/u01/app/oranine/product/11.2.0/dbhome_1" ORACLE_HOME_NAME="OraDb11g_home4"
********************************************************************************

Your platform requires the root user to perform certain pre-installation
OS preparation.  The root user should run the shell script 'rootpre.sh' before
you proceed with Oracle installation.  rootpre.sh can be found at the top level
of the CD or the stage area.

Answer 'y' if root has run 'rootpre.sh' so you can proceed with Oracle
installation.
Answer 'n' to abort installation and then ask root to run 'rootpre.sh'.

********************************************************************************

Has 'rootpre.sh' been run by root? [y/n] (n)
y

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 190 MB.   Actual 1350 MB    Passed
Checking swap space: 0 MB available, 150 MB required.    Failed <<<<

>>> Ignoring required pre-requisite failures. Continuing...

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-10-16_10-17-01AM. Please wait .../mnt/software/Ora11201/database =>
/mnt/software/Ora11201/database => The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/app/oranine/product/oraInventory
'AttachHome' was successful.

Once ORACLE_HOME is attached with central inventory then the patch applied successfully.

Most of us have encounterd the situation where to count rows of each tables in a particular schema. Straight forward approach is to run a query which will generate a script for this task and then run that script. Below is small block which is much easier to run and can be customized according to requirment.

DECLARE
   CURSOR c1
   IS
      SELECT   owner, table_name
        FROM   dba_tables
       WHERE   owner = 'MYUSER';

   row_coun   NUMBER (10);
BEGIN
   FOR cur IN c1
   LOOP
      EXECUTE IMMEDIATE   'select count(*) from '
                       || cur.owner
                       || '.'
                       || cur.table_name
         INTO   row_coun;

      DBMS_OUTPUT.put_line (RPAD (cur.table_name, 20) || CHR (9) || row_coun);
   END LOOP;
END;

Since, it’s using DBA_TABLES, so above code should be run with user having read access this view.