Oracle/Database/Broken Oracle

Oracle Service Does Not Start Database
I am running Oracle 10.2.0.1

After reboot when I try to connect locally using sqlplus I get the following error:

ERROR: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist

or

ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I get the following message when connecting as sysdba

C:\Documents and Settings\Administrator>sqlplus "/ as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 2 17:39:43 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance.

Idle instance? I found another article "Oracle - Why won't the mount and open happen automatically" that seemed to indicate what the problem was, but following their directions, using oradim, did not solve the autostart problem, but did provide a way to manually start the DB.

This article "AUTO-STARTING AN ORACLE DATABASE ON BOOTUP VIA INSTANCE MANAGER" gave directions on creating a batch file to auto start the DB. This is a Windows Server box! There should be a way to have the service autostart the DB.

One article mentioned setting the ORACLE_HOME to fix this problem. According to this article I should set my ORACLE_HOME to C:\oracle\product\10.2.0 but this caused SQLPLUS to fail. When I set ORACLE_HOME to C:\oracle\product\10.2.0\db_1 SQLPLUS worked, but it did not solve the autostart problem. I am getting really tired of restarting this box.

Oracle will not start up automatically had some good information, but still no luck with the autostart.

This article mentioned the -srvcstart parameter

Creating an Instance -SRVCSTART system | demand indicates whether to start the Oracle Database service on computer restart.

I modified the service with oradim and added the -srvcstart, rebooted, no love.

http://www.orafaq.com/forum/t/23928/0/

probably you should change sqlnet.ora (located in $(ORA_HOME)networkadmin)

parameter: SQLNET.AUTHENTICATION_SERVICES = (NONE)

Then restart the databases (or the server) Worked for me.

C:\Documents and Settings\Administrator>sqlplus "/ as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 2 18:11:17 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> startup open ORACLE instance started. Total System Global Area 289406976 bytes Fixed Size                 1248600 bytes Variable Size             79692456 bytes Database Buffers         205520896 bytes Redo Buffers               2945024 bytes Database mounted. Database opened.

http://www.digitalpoint.com/lists/75192.html

OK, how are you trying to connect in order to startup the database ? The service from what you say appears to be started but the instance has not (note the ORADIM utility has a startup option to automatically start the instance with the service)

http://www.orafaq.com/forum/t/23928/0/

Open Services in Control Panel Select TNSListener Open properties On the 'Log On' tab, put your operating system login name and password. Save the changes.

Do the same for Oracleservice.

Restart the services or computer and you'll be up and running.

Good luck.

Calvie

http://forums.oracle.com/forums/thread.jspa;jsessionid=8d92200830debaf3d204e9f048a784f985812c941a9c.e34QbhuKaxmMai0MaNeMb3eKaN90?messageID=644610&#644610

Hi Ben,

From the error message i could able to find that parameter files are missing. If u r missing ur spfile then no problem. Follow the steps,

sqlplus /nolog conn / as sysdba; Connected to an idle instance. rename init.***** to init.ora in d:\oracle\product\10.1.0\admin\db_name\pfile then from sql prompt follow the steps sql> startup pfile='d:\oracle\product\10.1.0\admin\db_name\pfile\init.ora' sql> create spfile from pfile='d:\oracle\product\10.1.0\admin\db_name\pfile\init.ora'

next time onwards u will not any problem.

http://www.experts-exchange.com/Databases/Oracle/Q_21250658.html

http://www.experts-exchange.com/Web/Application_Servers/Oracle_iAS/Q_20825680.html

Postinstallation Database Creation on Windows

Windows Service Not Starting Your Database? 

This article has several good ideas how to solve this problem.

"The last reason why I don't use this autostart feature is that when I posted this problem to Oracle (oracleservice start problem), the first suggestion from Oracle support was : "don't use the autostart feature of the Oracle service, set it to manual"." 

Creating an Oracle 9i database from the command line only

"What is the difference between a virus and Oracle? The answer:  a virus is free"  - Allen Kaye Steele

"Hacking and Plinking" - Ken Holm

(10:47:33) Kenneth: oracle is yucky (10:47:39) Kenneth: I feel violated just by looking at it (10:47:57) Lars Rasmussen: take a shower & cash the check - you'll feel better. (10:48:14) Lars Rasmussen: :)

Manual Creation of database in windows with oracle 9i (Step-by-Step)

C:OracleOra81bin>tnsping eam

Windows Oracle Service not starting? Nothing new found here.

Check & verify Oracle windows services. The suggestions here are similar to what was mentioned before.

1. Check Task Manager for the ORACLE.EXE process. If it is present, then the service started. 2. Check the Alert Log for the database. If the problem is not with the database, there will be no indication in the log that the database even tried to start. 3. Check the oradim.log in the $ORACLE_HOME/database directory for errors. Check the date on the log file as versions before 9i did not date/time stamp the entries

I think I found the solution! "Thread: DB not open, with Windows Service". "everything was resolved when installing patch "p4547817_10202_WINNT" (See metalink)" Unfortunately I do not have an account with MetaLink.

At this point I am giving up on this. I will just manually start the service with a batch script.

Oracle F M E R Notes
lsnrctl status lsnrctl services listener.log tnsping

sqlplus scott/tiger@cambridge sqlplus scott/tiger@localhost:1521:cambridge cambridge is the SID ORA-12545: connect failed because target host or object does not exist ORA-12514: TNS: listener does not currently know of service requested in connect descriptor. glossary tnsname: alias of database SID: oracle instance id service name: name pointing to instance(s), or other resources different tnsnames can point to a single oracle instance. They are just aliases. lsnrctl show current_listener lsnrctl start/stop/status ADD windows user to ora_dba gorup under windows groups

Startup/Shutdown DB
Start database, then listener listener.ora tnsnames.ora "/ as sysdba" Oracle OraDblOg_home1 set ORACLE_SID=MYTEST sqlplus "/ as sysdba" SQL> startup * leave listener running * alter database backup controlfile to trace; SQL> show parameter dump user_dump_dest show parameter control

Create Database
Database configuration assistant
 * 1) create database
 * 2) general purpose DB
 * 3) global & SID are same 'MYTEST';
 * 4) defaults
 * 5) use same passwords for sys,system,dbsnmp,sysman
 * 6) filesystem storage mechenism
 * 7) db file location from template defaults
 * 8) defaults
 * 9) defaults
 * 10) defaults (memory)
 * 11) defaults
 * ok

additional notes
error ORA-12560: TNS: protocol adapter error

Error ORA-01054: ORACLE not available Error ORA-27101: shared memory realm does not exist

set oracle_sid=mytest sqlplus /nolog connect / as sysdba

connect sys/pswd as sysdba

ORA-01031: insufficient privileges

sqlplus "sys/passwd" as sysdba startup

show user #who am i grant sysdba, sysoper to rene;

keywords
oracle