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 [1], 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" [2] 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 [3] had some good information, but still no luck with the autostart.

This article mentioned the -srvcstart parameter

Creating an Instance [4]
-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 [5]

Windows Service Not Starting Your Database? [6]

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"." [7]

Creating an Oracle 9i database from the command line only [8]

"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) [9]

C:OracleOra81bin>tnsping eam [10]

Windows Oracle Service not starting? [11] Nothing new found here.

Check & verify Oracle windows services. [12] 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" [13]. "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 <listner name>
  listener.log

tnsping <address>

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
12. 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