Recently after I configured database on testing machine and configure the forms to run on middle tier on testing machine (all windows environment), I came across the error ORA-12638.

After little bit of brains storming and checking online, I found out that one of the way to solve this error is to disable the Oracle Advanced Security Authentication and let database use the supplied user name and password to authenticate.
It can be disable by updating the sqlnet.ora file with the following entry:
SQLNET.AUTHENTICATION_SERVICES = (NONE)

Note : Default entry in my case was SQLNET.AUTHENTICATION_SERVICES= (NTS)
Guys do check out other method and let me […]

[Read more →]

Today while exporting I came to know that Oracle XE does not support Bitmap Indexes.

Then to test it out, I login into Oracle XE’s SQL*Plus console and tried to run a create bitmap index DDL sql and result was still negative as follows.
 
So I was convince that it does not support Bit-mapped Indexes..

Bookmark this post

[…]

[Read more →]

Recently while doing R&D, I came across the ADR Command Interpreter (ADRCI).
Introduced in Oracle Database Release 11g, it is a command-line tool that one can use to manage diagnostic data.
Diagnostic data includes incident and problem descriptions, trace files, dumps, health monitor reports, alert log entries, and more.
ADRCI has a rich command set, and can be used in interactive mode or within scripts.
In addition, ADRCI can execute scripts of ADRCI commands in the same way that SQL*Plus executes scripts of SQL and PL/SQL commands.
It enables us to:

View diagnostic data within the Automatic Diagnostic Repository (ADR).
View Health Monitor reports.
Package […]

[Read more →]

Yesterday I was doing some r&d and tried to find trace file for my session.
After lot of trouble I was able to find them.
Last time its was a simple task, I just need to look at $ORACLE_BASE/SID/udump directory of the database server.
But now trace files are stored in the the trace directory under Automatic Diagnostic Repository (ADR) home.
To get the location of individual trace files we can use data dictionary views as follow:
To find the trace file for your current session:

SELECT value
FROM v$diag_info
WHERE name = ‘Default Trace File’;
This query will return the full path to the trace file.

To find all trace files […]

[Read more →]

While installing the database some time due some problem, everything is installed but enterprise manager does not work.
In another common case (I am not really talking about production servers here) due to some reason like change in ip addresses and so on, enterprise manager stops working.
Whatever the case, In simple words there is a requirement that we need to drop the existing setup of enterprise manager and create new one with new configuration.
Note here I am only talking about dropping and then creating again which I normally do with my development server off course there are other options too.
First drop […]

[Read more →]

Just putting into words what I understand about how Oracle database handles the reading and writing of data.
Did some brain storming in a hope that it will be useful to somebody.
Reading in Oracle is completely handled by Server processes.
All instruction (reading or writing) from client’s processes first goes to server process.
Reading the Data

The server process first checks the buffer cache for the presence of data.
If not found then only copy the data from datafile to buffer cache.
Then send the data to the client.

DML operations
INSERT
A space is found in the block in the buffer cache and data is inserted into […]

[Read more →]

As per my experience when working with PL/SQL code, DBMS_OUTPUT is one of the most frequently used package to display debugging information.
But frequently when using 10G R1 and earlier versions, we come across something like as follows:
ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
Single line limit of 255 bytes exceeded.
ORA-20000: ORU-10027: buffer overflow, limit of bytes
Maximum amount of 1 million bytes per session exceeded.
10G R2 increases the single length line limit to 32,767 bytes and the overall session limit is removed altogether.
Note: Now myself and my team use our own procedure called LOGIT for debugging, I will […]

[Read more →]

Check out following:
SQL> select * from test;
COL1
———-
1
1
1
1
1
SQL> alter table test add constraint test_idx primary key(col1) disable;
Table altered.
SQL> create index test_idx on test(col1);
Index created.
SQL> alter table test enable novalidate constraint test_idx;
Table altered.
SQL> insert into test values(1);
insert into test values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.TEST_IDX) violated

Bookmark […]

[Read more →]

Recently at my client place I implemented RMAN script for maintenance of backups through RMAN, so that my team don’t have to use GUI to do the same.
When running the script everthing was smooth, except at the end of the script result was showing error ORA-19569 as follows:
RMAN> run
2> {
3> allocate channel c1 device type disk format ‘F:\rmanbackup\incrementalbackup\%d_DB_%u_%s_%p’;
4> delete archivelog until time ’sysdate-7′;
5> delete noprompt obsolete;
6> }
7>
using target database controlfile instead of recovery catalog
allocated channel: c1
channel c1: sid=202 devtype=DISK
deleted archive log
archive log filename=D:\ARCHIVE\BIZWIZ\ARC67474_0566156678.001 recid=106781 stamp=635438642
Deleted 2 objects
RMAN retention policy will be applied to the command
RMAN retention policy is set to […]

[Read more →]

In this blog entry let me introduce you to a new capability in Oracle 10g called Block Change Tracking, which can help DBA’s do faster incremental backups via RMAN (Recovery Manager).
Once this new capability in Oracle 10g is enabled it start recording the modified since last backup and stores the log of it in a block change tracking file.
Later while doing backup RMAN uses this file to determine which blocks are needed to be backed up?
Logically as we can see, this process improves the performance as RMAN does not have to scan whole datafile to detect which block in it […]

[Read more →]