I have seen this error so many times that I thought I would note down the resolution (for me anyway)
When logging into the database you get:
ORA-01034: ORACLE not available. ORA-27101: shared memory realm does not exist
To solve you connect to sqlplus as follows:
sqlplus /nolog
Login in as a sys dba:
connect sys/sys as sysdba
When connected, startup the database using:
startup
The database will startup and can be connected to as normal.
I have not long finished an interesting project which handled, amongst other things, taking data in and out of Oracle Spatial and putting it in an ESRI Geodatabase (File based) without ArcSDE being in the middle.
It was not a straight Oracle 10g database but rather one that had business logic built in but ultimately it talked with the SDO API to get the features just not using a a spatial filter to limit the amount of data it took out.
The most interesting thing about the code was that it communicated with the client using XML with GML for any geometries that arose. It also communication directly using HTTP so the client did not need any special software or libraries installed. It made for a great deployment model as all requests were handled using straight HTTP calls.
Here’s hoping that I get a chance to write against this API in the future. It was a refreshing change.