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.