Oracle: hash_area_size and sort_area_size

higher value for hash_area_size, though is desirable, does not affect the hashing performance in a big way. Reason could be that memory is only used for creating hashes and then the records are flushed to the temp segments (disk).

Whereas in case of sorting, due to complex logic performed for sorting all the records, data moves back and forth multiple time between memory and disk, thus requiring higher sort_area_size for minimizing these iterations. Temp space requirement also falls to a good extent.

One needs to set workarea_size_policy to MANUAL and specify the desired sort and hash area sizes. if hash_area_size is not defined then it defaults to 2*sort_area_size.

Solaris : Troubleshooting Memory & CPU Consumption

Use the following command to see the TOP 5 processes which are consuming system memory.
prstat -s size -n 5

output shows the resource statistics for each thread of a server application:

prstat -L -p 3295

Processes consuming the most CPU resource:

prstat -s cpu -a -n 8

PERL : diff two files

use strict;

open TXT1, "1.txt" or die "$!";
open TXT2, "2.txt" or die "$!";
my %diff;

$diff{$_}=1 while ();

while(){
 print unless $diff{$_};
}

close TXT2;
close TXT1;

Remove ^M from files transferred from Windows to Linux

Inside vi [in ESC mode] type:

:%s/^M//g (to make the ^M -> CTRL+V then CTRL+M)

Oracle ODBC : Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

While configuring DSN using Oracle ODBC driver, one might encounter error "Driver's SQLAllocHandle on SQL_HANDLE_ENV failed". Cause being, Permissions of the remote desktop software did not allow the user access via the remote desktop software.

Do the following to resolve the issue:

1. Select Administration Tools , Local Security Setting and Local Policy.
2. Then select "User Rights Assignment"
3. Double click on "Create Global Objects"
4. Select Add User or Group.
5. Make sure Object Types Group Box is checked.
6. Select Locations and highlight the name of the server you are working on (Not the Domain).
7. Enter "Remote Desktop Users" or "Everyone" (without the quotes) into the Object Names Box.
8. Select OK.
9. Add the same users to the group "Power User".
10. If this does not work, after step 8 a reboot may be required.

Oracle: Spooling Column of type Long

Set the following sqlplus options for spooling a long column. This will avoild truncation of lines to a particular width.

SQL> set trimspool on
SQL> set linesize 20000
SQL> set long 100000000
SQL> set longchunksize 2000
SQL> set pagesize 0

Oracle : Support for Very Large Memory (VLM) Configurations

Oracle Database for Windows supports Very Large Memory (VLM) configurations in Windows to access more than the 4 gigabyte (GB) of RAM traditionally available to Windows applications.

The requirements for taking advantage of this support are:

1. The computer on which Oracle Database is installed must have more than 4 GB of memory.

2. The operating system must be configured to take advantage of Physical Address Extensions (PAE) by adding the /PAE switch in boot.ini. See Microsoft Knowledge Base article Q268363 for instructions on modifying boot.ini to enable PAE.

3. It is advisable (though not necessary) to enable 4GT support by adding the /3GB parameter in boot.ini. See Microsoft Knowledge Base article Q171793 for additional requirements and instructions on modifying boot.ini to enable 4GT.

4. The user account under which Oracle Database runs (typically the LocalSystem account), must have the "Lock memory pages" Windows 2000 and Windows XP privilege.

5. USE_INDIRECT_DATA_BUFFERS=TRUE must be present in the initialization parameter file for the database instance that will use VLM support. If this parameter is not set, then Oracle Database 10g Release 1 (10.1) behaves in exactly the same way as previous releases.

6. Initialization parameters DB_BLOCK_BUFFERS and DB_BLOCK_SIZE must be set to values you have chosen for Oracle Database.

7. Registry parameter AWE_WINDOW_MEMORY must be created and set in the appropriate key for your Oracle home. This parameter is specified in bytes and has a default value of 1 GB. AWE_WINDOW_MEMORY tells Oracle Database how much of its 3 GB address space to reserve for mapping in database buffers.

8. Once this parameter is set, Oracle Database can be started and will function exactly the same as before except that more database buffers are available to the instance. In addition, disk I/O may be reduced because more Oracle Database data blocks can be cached in the System Global Area (SGA).

9. If DB_BLOCK_SIZE is large, however, the default AWE_WINDOW_MEMORY value of 1 GB may not be sufficient to start the database.

Reference Note:
http://download-west.oracle.com/docs/cd/B14117_01/win.101/b10113/architec.htm#sthref58

Reset OC4J Admin Password

Reset the oc4jadmin password using the following procedure while you are logged in as the user who installed the Oracle Application Server instance:

1. Stop OC4J and the Application Server Control.

   Enter the following command in the Oracle home of the application server instance:

   (UNIX) ORACLE_HOME/opmn/bin/opmnctl stopproc ias-component=OC4J
   (Windows) ORACLE_HOME\opmn\bin\opmnctl stopproc ias-component=OC4J
   
   

2. Locate and open the following file in a text editor:

   (UNIX)ORACLE_HOME/j2ee/home/config/system-jazn-data.xml
   (Windows)ORACLE_HOME\j2ee\home\config\system-jazn-data.xml
   
   

3. Locate the line that defines the credentials property for the oc4jadmin user.

   The following example shows the section of system-jazn-data.xml with the encrypted credentials entry in boldface type:

   
   
      jazn.com
      
       .
       .
             oc4jadmin
            OC4J Administrator
            OC4J Administrator
               {903}4L50lHJWIFGwLgHXTub7eYK9e0AnWLUH
         
   

4. Replace the existing encrypted password with the new password.

   Be sure to prefix the password with an exclamation point (!). For example:

   !mynewpassword123

ORA-27047: unable to read the header block of file

If while restoring backup, Db errors out with ORA-27047, it implied that the block header is corrupt.

Following three actions can be taken to restore DB:

1. resize the database file in the source system and again take the cold backup. Resizing reformats the block header.
2. use the existing control files to startup the database and then resize the file.
3. If control files can not be reused, because of the change in file location where db files will be restored, just take the cold backup copy of only the resized file from the source system. replace the existing corrupt header file with the newly backup copy and create the control file.
revert back to the original corrupt header file and try to open db with the newly created control file. It will fail with checkpoint mismatch asking for some file needing recovery.
Give the command "recover database using backup control file". When asked for the achive logs, supply the redo log file (try with each of the log files), it will recover the DB.

Oracle BITMAP Index Structure

Index block with PCTFREE 50

select extent_id, file_id,block_id from dba_extents where segment_name='IND_BMP_50' order by extent_id

EXTENT_ID FILE_ID BLOCK_ID
---------- ---------- ----------
0 1074 53953
1 1115 53841
2 1196 53745
3 1742 447857
4 1744 446833


SQL > alter system dump datafile 1742 block 447857

row#0[6126] flag: ------, lock: 0, len=1906
col 0; len 1; (1): 80 <-key value
col 1; len 6; (6): 2b 40 d2 29 00 08 <-Starting Rowid
col 2; len 6; (6): 2b 40 d2 c1 00 1f <-Ending Rowid
col 3; len 1886; (1886): <-Bitmap for the key value


row#1[4221] flag: ------, lock: 0, len=1905
col 0; len 1; (1): 80
col 1; len 6; (6): 2b 40 d2 c1 00 30
col 2; len 6; (6): 2b 40 d3 48 00 67
col 3; len 1885; (1885):

There are two rows in the block. Totallying their column size :
1+6+6+1886+1+6+6+1885 = 3797kb
So, block is occupying approx 50% of the block (8192kb)



Index block with PCTFREE 10

SQL> select extent_id, file_id,block_id from dba_extents where segment_name='IND_BMP_10' order by extent_id;

EXTENT_ID FILE_ID BLOCK_ID
---------- ---------- ----------
0 1074 63057
1 1115 62945
2 1196 62849
3 1742 457377
4 1744 456177
5 1786 429665
6 1809 411889


alter system dump datafile 1742 block 457377

row#0[4495] flag: ------, lock: 0, len=3537
col 0; len 1; (1): 80 <-key value
col 1; len 6; (6): b3 c6 f6 2b 00 28 <-Starting rowid
col 2; len 6; (6): b3 c6 f6 ce 00 37 <-Ending rowid
col 3; len 3517; (3517): <-Bitmap for the key value


row#1[957] flag: ------, lock: 0, len=3538
col 0; len 1; (1): 80
col 1; len 6; (6): b3 c6 f6 ce 00 48
col 2; len 6; (6): b3 c6 f7 74 00 37
col 3; len 3518; (3518):


There are two rows in the block. Totallying their column size:
1+6+6+3517+1+6+6+3518 = 7161 kb
So, block is occupying approx 90% of the block (8192kb)

MSSQL : Collation - a way out of case-insensitiveness

Sql Server Databases by default are case-insensitive, both for the object names as well as the data contained in them.

To change a database to case sensitive one, its collation needs to be changed.

To check for the existing collation, check the properties of database and look for a row with heading "Collation"
by default collation is set to "SQL_Latin1_General_CP1_CI_AS". This collation renders database as case-insensitive.

To make your database case-sensitive (as some third party software would expect them to be), we need to change the Collation for the Database.

There are many collations available. you can check them by executing the following statement
select * from ::fn_helpcollations()

Collation "Latin1_General_BIN" is found to work fine for the purpose of making Database case-sensitive.

To change collation, execute an alter statement against the Database

Alter Database TESTDB collate Latin1_General_BIN

Note that the existing tables and their columns would still follow the earlier collation unless recreated or rebuilt.

Collation on specific Tables and their columns as also be changed using 'Alter Table alter column varchar(10) collate Latin1_General_BIN null

MSSQL : Deleting duplicate rows

Select the duplicate key values into a holding table. For example:

SELECT col1, col2, col3=count(*)
INTO holdkey
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

Select the duplicate rows into a holding table, eliminating
duplicates in the process. For example:
SELECT DISTINCT t1.*
INTO holddups
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2

At this point, the holddups table should have unique PKs,
however, this will not be the case if t1 had duplicate PKs,
yet unique rows
SELECT col1, col2, count(*)
FROM holddups
GROUP BY col1, col2

If count(*) returns more than 1 for certain rows,
determine which of the rows to delete which have the
duplicate keys but unique rows and then only process further.

Delete the duplicate rows from the original table.
For example:
DELETE t1
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2


Put the unique rows back in the original table.
For example:
INSERT t1 SELECT * FROM holddups
    

DAC/Informatica src files connection

Informatica Server Variable > $PMSourceFileDir must be set exactly the
same as the DAC System Properties > InformaticaParamatereFileLocation as
follows: C:\oracleBI\DAC\Informatica\parameters (NB***no spaces in folder
name****)

Copy all files from OracleBI\dwrep\bin\Informatica\LkpFiles to the location specified in $PMSourceFilesDir

Copy all files from OracleBI\dwrep\bin\Informatica\SrcFiles to the location specified in $PMSourceFilesDir

Both Informatica and DAC must be able to access the folder specified above.

AWE and /3GB Switch for Windows

Windows 32-bit Operating Systems can only address upto 4GB of available memory. In order to access more than 4 gb of install memory we have to go for Addressing Windowing Extensions. For this set /PAE in the boot.init file and reboot the server.

As per blog http://blogs.msdn.com/oldnewthing/archive/2004/08/19/217087.aspx :

The two are independent. AWE is how programs access physical memory. PAE is how the CPU accesses physical memory. AWE requires PAE in order to allocate more than 4GB of physical memory. You need PAE in order to access more than 4GB of physical memory, AWE or no AWE


For systems having up to 4GB of RAM, Server spares only 2GB for user mode memory (rest of 2GB is for kernel). We can strech up to 3GB by using tag /3GB in boot.ini.

Note: /3GB switch would limit use of memory to only 3GB even if system has 16 GB RAM installed.

Windows 2003 Enterprise Edition can by default address up to 16GB of RAM.

How to update boot.init
Go to My Computer.
Right click -> Properties
Go to Advanced Tab
Startup and Recovery -> Settings
Edit.

Informatica shared memory

Informatica Server setup has a Load Manager where you can define the Shared Memory. This the total memory which server allocates to a particular session (defined by session parameter DTM Memory Buffer) default value 12000000.

MS SQL Server is too case sensitive

Databases are case sensitive, we all know. MS Sql takes it a level further (good or bad ?). It is case sensitive even to the table names.

Don't believe me, try out yourself !!!

Restore MSSQL DB from mdf,ldf and ndf files

MS Sql DB comprises mdf, ndf and ldf data files. If you happen to have taken a backup of the database (.bak), then restoring it on to a different instance is quite straight forward (using SQL Server Management Studio).
But in case you just have the database files with you, then one needs to attach it to the instance. Attach is a better technical term for this than restore.

Following is the way we can attach the Database to an instance:

use master
GO

EXEC sp_attach_db @dbname = N'NewDB',
@filename1 = N'C:\Program Files\MasterDB.mdf',
@filename2 = N'C:\Program Files\MasterDB_log.ldf',
@filename3 = N'C:\Program Files\DATA2.ndf',
@filename4 = N'C:\Program Files\DATA3.ndf'