ORA-38760: This database instance failed to turn on flashback database( ORA-38701 ORA-27037)

Problem Description
Whenever you try to startup your database then it fails with ORA-38760.
SQL> startup
ORACLE instance started.

Total System Global Area 1161966444 bytes
Fixed Size 1787966 bytes
Variable Size 1145750568 bytes
Database Buffers 225163524 bytes
Redo Buffers 1262132 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database.

In the Alert log there goes entry like this.

Errors in file /oracle/admin/agprod/udump/agprod_ora_409616.trc:
ORA-38701: Flashback database log 402 seq 402 thread 1: "/DBarchive/flash_recovery_area/AGPROD/flashback/o1_mf_47cqbhb5_.flb"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

The analogous of this error is during database running state whenever you delete the current flashbacklog, the database will crash with the following errors:

ORA-38701: Flashback database log 401 seq 401 thread 1:
"/DBarchive/flash_recovery_area/AGPROD/flashback/o1_mf_47cqbhb5_.flb"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Fri  25 00:20:12 2011
RVWR: terminating instance due to error 38701
Instance terminated by RVWR, pid = 5721

Cause of The Problem
The above errors occurred due to a well identified bug. If there is any I/O error that is preventing write to the flashback logs, the instance will crash. Losing the current flashback log will cause the instance to crash. Also during database startup if flashback logs are inaccessible then instance will crush.

Solution of The Problem
Solution A)-Upgarde Database Version:
This bug is affected from database version 10.1.0.1 to 10.1.0.4 or greater versions
It is fixed in database version 10gR2. So upgrade your database version.

Solution B)-Disable Flashback and restart the instance.
1)If you don't have the situation to upgrade the database then start the database in mount stage.
SQL> startup mount;

2)Turn off the Flashback
SQL> alter database flashback off;

3)Turn on the Flashback
SQL> alter database flashback on;

4)Startup the database
SQL>alter database open;

Or, SQL>shutdown
SQL>startup

Note:
After disabling flashback if you have guaranteed restore point then you still might get the error ORA-38760: This database instance failed to turn on flashback database while starting up the database. In this case you have to drop the guaranteed restore point.

You can see the available guaranteed restore point by,
select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# from v$restore_point;
Then drop the guaranteed restore point by,
DROP RESTORE POINT NAME_OF_THE_GUARANTEED_RESTORE_POINT;
Now start your database.

ORA-01033: ORACLE initialization or shutdown in progress

Problem
While connecting to database user get the error,
ORA-01033: ORACLE initialization or shutdown in progress

Cause of The Problem
SYSDBA used issued STARTUP command to the database and while starting up database, connecting to database as normal user will get the error ORA-01033.

There may be the scenario that SHUTDOWN command waits a long time.
Solution of The Problem
Scenario 01:
Based on the situation STARTUP may take fewer minutes. As a normal user what more you do is to wait few minutes and try again. You will succeed if database is open state.

Here is the waiting result.
C:\Documents and Settings\Queen>sqlplus nikunj/a

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 21 15:34:23 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress


Enter user-name: nikunjdw
Enter password: *****
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Enter user-name: nikunjdw
Enter password: *****

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

At third attempt I became succeed.

Scenario 02:
If you have SYSDBA privilege then connect to database as SYSDBA and see the status of the database.
SQL> conn / as sysdba
Connected.
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

As we can see that database gradually became in usable state. Someone issued STARTUP command and it took some time to be in READ WRITE state.

Scenario 03:
If shutdown takes more time suppose SHUTDOWN NORMAL.... then issue,
SHUTDOWN ABORT;
and later normal startup,

Database Startup Fails With Errors ORA-01078 And ORA-27046 Or ORA-01078

Problem :
-----------------------
Database startup fails with error

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '......'

or,

SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '.......'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 2558)

Cause :
------------------------------
The error can be happened in many scenarios.

1)The ORACLE_SID environmental variable is set improperly.

2)The error occurred as database could not find the spfile and pfile on the default location or specified location.(if startup pfile= is specified)

3)The spfile exists in default location but it is corrupted and hence reported ORA-01078.

4)If spfile exists in non-default location and we started by STARTUP pfile= where inside pfile it holds the location of spfile=location then error reported ORA-1078 along with ORA-27046. This scenario is explained in How to start your database with non-default spfile.

Solution -
------------------------------
At first check whether your environment variable ORACLE_SID is set properly or not. On unix it is case sensitive. So dbase and Dbase is not same.

Is there is no way to repair or modify an spfile so try to solve the problem in following order.

1)If you have good backup of spfile then restore it. From RMAN you can easily do it if you have autobackup of controlfile. It is described in How to restore spfile by RMAN

2)You can get your pfile at the location $ORACLE_HOME/admin/$ORACLE_SID/pfile/. A typical file name is init.ora.418200821147. From that location first copy to another location like in /oradata2/pfile and then edit the pfile as necessary and try to make a spfile from that.
$SQL / as sysdba
$CREATE SPFILE FROM PFILE='/oradata2/pfile';

3)If the spfile backup does not exist then look at the database alert log file which is located in $ORACLE_HOME/admin/$ORACLE_SID/bdump. In the alert log file the list of non-default parameters listed. So create a pfile from spfile and later create spfile.

4)If you have corrupted spfile then from that, using binary utilities like strings available on unix open that and create pfile. And then create spfile from that. Like,

i)Follow either a or b part.

a)$strings /oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledbase.ora >/export/home/pfile.ora

Now edit the /export/home/pfile.ora if any wrong character and then create spfile from that.
SQL>!vi /export/home/pfile.ora
SQL>create spfile from pfile='/export/home/pfile.ora';
SQL>startup

b)$strings /oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledbase.ora
copy the usable contents inside it Open another file and paste contents into it.

SQL>!vi /export/home/oracle/test.txt

Create spfile from pfile.

SQL> create spfile from pfile='/export/home/oracle/test.txt';
File created.

Then start the database.

ORA-01012: not logged on

Problem Description
No other user can connect to database. Whenever user with sysdba privilege try to login to database it shows connected but it does not allow to happen any query to database instead it fails with ORA-01012: not logged on as below.

oracle:/home/oracle CIDMP> $sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed 16 10:30:16 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected.

SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01012: not logged on

Sometimes connecting as sysdba shows database is in idle instance but whenever you issue startup it says ORA-01081: cannot start already-running ORACLE - shut it down first. 

SQL> conn / as sysdba
Connected to an idle instance.

SQL> desc v$instance
ERROR:
ORA-01012: not logged on

SQL> conn / as sysdba
Connected to an idle instance.

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first

Cause of the Problem
Note that this problem is not same as SP2-0640: Not connected which is sql*plus message and it raised whenever you try to run query without log in to database. Just like below.

SQL> conn nikunj/a
Connected.
SQL> conn piyush/e
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> select instance from v$thread;
SP2-0640: Not connected

"The ORA-01012: not logged on" error occurred due to heavy load in the database. If there is maximum number of sessions connected to the database(Which is in turn defined by PROCESSES parameter) and database is flooded with concurrent load then database does not allow sysdba privileged user as well as other users to be connected to the database. If sysdba privileged user try to connect to database then above error ORA-01012 comes.

Solution of the problem
The solution is free up the sessions. You can do it in whatever ways you want. Like,
1)Shut down application server sessions: You can shut down application server and thus will release the sessions.

2)Shut down database server: Shuttting down database server will shutdown database and all sessions will be release.

3)Kill oracle process: You can kill oracle process and then you need to start the oracle database again which will also work.

ORA-01102: cannot mount database in EXCLUSIVE mode

Problem:

I had used DBCA to create Oracle database and after doing all; had started the database from SQL and got the error message:


SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size                  2213776 bytes
Variable Size             402655344 bytes
Database Buffers         1191182336 bytes
Redo Buffers                7360512 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode

I checked the Alert Log file which gave me the hint to correct the issue

---------(I looked for the pmon process;)--------

[oracle@geoppdb01 ~]$ ps -ef|grep pmon
oracle   11758     1  0 Jan05 ?        00:00:00 ora_pmon_mydb
oracle   17754     1  0 14:17 ?        00:00:00 ora_pmon_MYDB
oracle   17795 17715  0 14:19 pts/1    00:00:00 grep pmon

Cause:

ORACLE_SID environment variable is case sensitive and in the .bash_profile I had set this as uppercase, despite the fact that  it was lower case.

Solution:

I had first changed the ORACLE_SID variable as lowercase, and then shutt down the two instances and startup solved my problem.
SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size                  2213776 bytes
Variable Size             402655344 bytes
Database Buffers         1191182336 bytes
Redo Buffers                7360512 bytes
Database mounted.
Database opened.
SQL>

Cannot see Oracle ODBC driver in datasource administrator in Windows 64 bit

Problem:

I cannot see Oracle ODBC driver in datasource administrator in Windows 2003 64 bit. Here you can see that the ODBC driver is avialable. But i am not able to get this in my windows environment.

Cause:

64 bit 32 bit incompatibility issue for odbc driver, data source administrator and application.

Solution:

I have Windows 2003 64 bit, Oracle 10.2 32 bit Client and 64 bit application.
After googleing around,
I found that there is two versions of odbc datasource administrator:
The default one, is 64 bit which we use Start menu for opening it.
In order to open the 32 bit version I used: %windir%\SysWOW64\odbcad32.exe.
With the help of 32 bit version datasource administrator, I could see my 32 bit odbc driver listed.
I extracted this rule of thumb: If your application is 32 bit, then your data source must use Oracle 32 bit client as well.
Last words, beware of 3 pieces:

1- your application (which uses DSN definition),

2- Oracle client

3-data source administrator

All pieces must have same version, in my case it is 64 bit and therefore I installed 64 bit client.
Further reading:
http://support.microsoft.com/kb/942976

ORA-01001 ERROR when creating trigger

Case : 

FRM-40735: ON-ERROR TRIGGER ORA-01001 FRM-42100 WHEN SEARCHING PARTIALLY PEOPLE

Problem:
In Users form, when trying to query by person field like ‘%name%’ an error message shows up:

FRM-40735: ON-ERROR TRIGGER RAISED UNHANDLED EXCEPTION ORA-01001
At alert_SID.log:
ORA-07445: exception encountered: core dump [qerixGetKey()+562] [SIGSEGV] [Address not mapped to object] [0x10] [] []
Cause:
Initialization parameters didn’t set properly for 10g.

Solution:
Add the follwing to initSID.ora file:
_b_tree_bitmap_plans = FALSE