Oracle fake index

Recently following the document ID 456468.1, I made a verification of the integrity of the catalog. Anyway, they have appeared a couple of errors indicating a problem with an object identifier:


This object exist in dba_objects, the query:

Continue reading

ora-01591 lock held by in-doubt distributed transaction

It is possible that an application displays in its log an ORA-01591 error, which according to official documentation is due to a 2-phase trasacción that can not be completed.

Such transactions are used, for example, when a commit using tables obtained from a dblink becomes.It verifies that you can make a remote commits, another local and finally performs in the 2 locations atomically.

Well when connecting with a dblink is lost during a transaction, the transaction is in an uncertain state and if another session attempts to modify the objects involved see the ORA-01591 error.

Continue reading

Oracle password expired, Oracle 11g

Since the release of Oracle Database 11g, it has reinforced security by default. This should in principle be positive, but there are some situations where some behaviors may be unpleasant.

The most obvious example can be found in the modification of the profile (PROFILE) DEFAULT, which is applied to any user created by default.

Continue reading

ORA-07445 [kkecdn()+9776] After apply Pacth Set

Recently, to solve a production problem identified as a bug (solventable with Path Set has been necessary to update BD Oracle version to (that matter we are going to last Patch Set).

After installing the Path Set for Linux 64 bits, which went smoothly and update catalogs databases, everything seemed to go perfectly.

At the moment the COMPATIBLE parameter remained with the original version

Problems occurred about a day later when a report stopped working, the alert displays:

Continue reading

RMAN restore read only data files, including read write datafiles

When performing a restore of a copy with RMAN (Oracle Database), may appear notices as:

datafile #NUM not processed because file is read-only

This is because it has been used the command:

restore database;

Continue reading

Oracle search string in database

In some cases, fortunately not too many, I had to search for a string (usually something related to configuration) in a schema of the database.

Gets complicated when there is a huge amount of tables and also no way to contact the developer, is as hell.

Well good for these cases I want to share a PL / SQL script homegrown (as almost everyone who put in the blog), which facilitates the issue fairly:

Continue reading

Oracle listener automatic registration of services

Starting with Oracle Database version 8i, need not be recorded BD services in the listener.ora file, the DB can automatically register.

This functionality works perfectly if we have the listener on port 1521 (default), if we make a change there’s no port to connect to the database. This is because the value of the parameter which defaults LOCAL_LISTENER:

Continue reading

RMAN restore backup, from tape to another server

A good exercise to verify that a backups made it correctly, is prepare a script that does a restoring on demand to another server (frequently in preproducction environment)

I tell a restoring and not a clone (something really amazing that you can clone a DB without stop it until current time), because the cloned needs the origin DB (surely production) has to remain in open state and in case of disaster this is not the scenario.

The restoring show here was made without external RMAN catalog (is using only information of controlfile as catalog), this forces to have enable the option: Continue reading

Automatic start Oracle database linux RedHat

A small script to automatically start an Oracle DB:

# chkconfig: 2345 80 20
# description: Oracle Database
start() {
su - oracle <<EOF
lsnrctl start
sqlplus / as sysdba<<EOO
stop() {
su - oracle <<EOF
sqlplus / as sysdba<<EOO
shutdown immediate
lsnrctl stop
case "$1" in
echo $"Usage: $0 {start|stop}"

Start the database as “oracle” user, you have to have all correctemente configured environment variables.

You can install this script with chkconfig, because the head is prepared for this