Peasland Database Blog

I am working on upgrading multiple databases to the Oracle 11.2.0.3 version. Before the upgrade, I need to install this version on multiple machines. We’ve known for a long time that you can generate a response file and use this for a silent install, speeding up the installation. The way I’ve always created my response file is to open the .rsp sample in a text editor and then in another window, fire up the OUI. I walk through the OUI and then figure out where that screen’s items are in the response file and update the response file accordingly. I recently discovered (although it is completely obvious) that the 11gR2 OUI automatically records your responses as you work through the OUI. When you get to the Summary screen, there is an option to save the responses to a .rsp file.

After pressing the button and saving the response file, you can either Cancel the OUI or proceed with the Install.

Now to proceed with a silent install, do the following:

cd {Oracle media directory}

./runInstaller -silent -responseFile /dir/response_file_created_above.rsp

I got an alert from Enterprise Manager that my C: drive on one of my database servers was running short on space. The disk device is 20GB is size and had less than 1GB free. I removed old log files, but this still did not free up enough space. In looking around, I saw that my 11gR2 ORACLE_HOME directory was over 7GB in size. More investigation revealed that the %ORACLE_HOME%\.patch_storage directory was consuming over half of that! You can’t just go and delete this folder as it will stop your ability to apply future patches.

I discovered Metalink Note 550522.1 which gave me a nice feature of OPatch to clean up some of this space.  You can see this feature below:

C:\>%ORACLE_HOME%\OPatch\opatch util cleanup

Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1

Copyright (c) 2009, Oracle Corporation.  All rights reserved.

UTIL session

Oracle Home       : c:\oracle\product\11.2.0.2

Central Inventory : C:\Program Files\Oracle\Inventory

from           : n/a

OPatch version    : 11.2.0.1.1

OUI version       : 11.2.0.2.0

OUI location      : c:\oracle\product\11.2.0.2\oui

Log file location : c:\oracle\product\11.2.0.2\cfgtoollogs\opatch\opatch2013-03-

21_13-02-46PM.log

Patch history file: c:\oracle\product\11.2.0.2\cfgtoollogs\opatch\opatch_history

.txt

Invoking utility “cleanup”

OPatch will clean up ‘restore.sh,make.txt’ files and ‘rac,scratch,backup’ direct

ories.

You will be still able to rollback patches after this cleanup.

Do you want to proceed? [y|n]

y

User Responded with: Y

Size of directory “c:\oracle\product\11.2.0.2\.patch_storage” before cleanup is

3921310486 bytes.

Size of directory “c:\oracle\product\11.2.0.2\.patch_storage” after cleanup is 2

233118071 bytes.

UtilSession: Backup area for restore has been cleaned up. For a complete list of

files/directories

deleted, Please refer log file.

OPatch succeeded.

I’m sure that someone reading this will ask, “how did you not know that?”. But sometimes the simplest things come to us late in the game while others knew it early on.

I’ve been patching Oracle on Windows since the Windows NT days.  Everyone who patches Oracle on Windows knows that OPatch will often balk at the fact that some DLL files are locked by other processes. The easiest way for me to get around this is to just rename the DLL files that will be replaced by the patch. The OS won’t let you delete the DLL files, but you can rename them and then OPatch can proceed. In the early days of OPatch, I would run the apply, it would spit out the DLL that was locked. I would rename the DLL file and then run OPatch again to get the next DLL file that was locked. This process would repeat about 20 times until OPatch found no locked DLL files. At least now when you run OPatch, it gives you a full list of DLL files that are locked.

Sometimes I want to know process that is locking the DLL file. In the past, I would download one of those freely available utilities to show me the process(es) locking the file in question and terminate the process. But I don’t like freeware bloating up my servers so I often remove the utility when I am done. Until the next time I need that utility…

Someone recently pointed me to Metalink Note 294350.1 which contained one little nugget I was not privy to prior to reading this document. Apparently, Windows includes a command-line utility to show which processes are locking DLL files. I can now do:

tasklist /m

The output can be long, so it is beneficial to store the output in a file and then open up the file in a text editor:

tasklist /m > c:\oracle\task_list.txt
notepad c:\oracle\task_list.txt

I can then use the text editor’s search function to locate the DLL in question.

Now how is it that this little utility has escaped my attention all these years?

I had a developer ask me an interesting question recently. He was working on a problem where numeric values were stored in a table but when he queried that table in PL/SQL Developer, it would show trailing zeroes after the last digit. He wondered if this was contributing to the problem he was trying to debug. The developer needed to know if Oracle was storing those trailing zeroes.

My response was that Oracle does not store trailing zeroes. Oracle only stores the exponent and mantissa of the number.  Oracle does not right-pad the numeric value with zeroes. The developer now knew that his problem was not with the data in the database but rather with something his development platform was doing.

But was my statement true? Many times I’ve made a statement about how Oracle works internally but then had to go back and validate my statement or prove that it was false which inevitably leads to the correct statement.

To test out my statement, I created a simple table and inserted data into it.

SQL> create table test_tab (val number(38,5));
Table created.
SQL> insert into test_tab values (25);
1 row created.
SQL> insert into test_tab values (25.0);
1 row created.
SQL> insert into test_tab values (25.2);
1 row created.
SQL> insert into test_tab values (25.20);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_tab;
VAL
----------
        25
        25
      25.2
      25.2

In SQL*Plus, we do not see any trailing zeroes even though I explicitly added them in. The values 25 and 25.0 as well as 25.2 and 25.20 all look the same. But maybe this is just how SQL*Plus is displaying the values. So let’s dump the data block to see how exactly Oracle is storing these values.

SQL> select file_id,block_id,blocks
2  from dba_extents where segment_name='TEST_TAB';
FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
6        128          8
SQL> alter system dump datafile 6 block min 128 block max 135;
System altered.

I had to determine the file and block number for my segment I created. I then issued the command to dump the contents of the data blocks to a trace file. When you look in the trace file, search for the keyword “block_row_dump” and you can see the contents of these rows in dump below:

block_row_dump:
tab 0, row 0, @0x1f92
tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 2]  c1 1a
tab 0, row 1, @0x1f8c
tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 2]  c1 1a
tab 0, row 2, @0x1f85
tl: 7 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 3]  c1 1a 15
tab 0, row 3, @0x1f7e
tl: 7 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 3]  c1 1a 15
end_of_block_dump

We can see from the block dump the first value is 2 bytes long and consists of the hex characters “C1 1A”. The second row has the same exact values! This is important because it verifies my initial claim that Oracle is not storing any extra zeroes for the second row in the table. If there were an extra zero, the length would not be 2 bytes. For the third and fourth row, we can see the hex values are identical, “C1 1A 15″.

But let’s be sure that these hex values correspond to our data. To do that, we’ll use the DBMS_STATS.CONVERT_RAW_VALUE procedure.

SQL> set serveroutput on

SQL> declare

  2  n number;

  3  begin

  4  dbms_stats.convert_raw_value('C11A',n);

  5  dbms_output.put_line(n);

  6  end;

  7  /

25

 

PL/SQL procedure successfully completed.

 

SQL> declare

  2  n number;

  3  begin

  4  dbms_stats.convert_raw_value('C11A15',n);

  5  dbms_output.put_line(n);

  6  end;

  7  /

25.2

 

PL/SQL procedure successfully completed.

So the hex values “C1 1A” are the internal (raw) representation of ’25′ and “C1 1A 15″ is 25.2 as we would have expected.

The moral of this story is that sometimes when you think you know how Oracle is working internally, you may still have to devise a test case to validate your statements.