Starting Oracle on Windows

Posted: March 28, 2008 | Author: prodlife | Filed under: tips |19 Comments

I never thought that starting up Oracle is difficult. Usually it looks something like this:
sqlplus / as sysdba startup

Except when Oracle is used on Windows. We have one or two dev servers installed on Windows, but in the three years I’ve been working as a DBA, I never had to restart one. Today, I connected to one of these machines, noticed it was down and attempted to restart it as usual:
C:\Documents and Settings\chen>set ORACLE_SID=ORCL C:\Documents and Settings\chen>sqlplus / as sysdba

ERROR: ORA-12560: TNS:protocol adapter error Enter user-name:

C:\Documents and Settings\chen>set | grep ORA ORACLE_HOME=D:\app\chen\product\11.1.0\db_1 ORACLE_SID=ORCL
C:\Documents and Settings\chen>sqlplus

Enter user-name: / as sysdba ERROR: ORA-12560: TNS:protocol adapter error Enter user-name:
At that point I recalled that Windows has these Oracle “service” things. So I started the service (from UI! how humiliating!) and surprise:
C:\Documents and Settings\chen>sqlplus

Enter user-name: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Seems that Windows will not let me connect to an idle instance to start it. Very strange.

19 Comments on “Starting Oracle on Windows”

cristiancudizio says:

March 28, 2008 at 8:38 am

that’s not so strange. You are lucky to not have to work with Oracle on Win.
Oracle on Windows is not multi-process but multi-threaded. When you start the service you start the process (it then by default starts the oracle instance creating the threads).
By my experience i think that Oracle on windows does not works well as on the *NIX platforms.

– Cristian

Reply
philippe says:

March 28, 2008 at 9:52 am

did you check if the osuser that you are logged on with, is in the ora_dba group on the windows server ?

also check in sqlnet.ora that
SQLNET.AUTHENTICATION_SERVICES=(NTS)

Reply
philippe says:

March 28, 2008 at 9:58 am

btw indeed the service needs to be started before you can startup the database in sqlplus. you can specify in the registry whether you let the service also start the instance or not (ORA__AUTOSTART true or false)

Reply
Rob van Wijk says:

March 28, 2008 at 11:45 am

> (from UI! how humiliating!)

🙂

You can avoid the humiliation by typing “net start ” and “net stop “. After seeing and remembering the name of the service, of course.

Regards,
Rob.

Reply
Freek says:

March 28, 2008 at 12:26 pm

Chen,

You can login to an idle instance on windows, but at least the oracle windows service should be running.
Depending of the configuration in the windows registry, starting the service will also start the database.
The same is true for stopping the database, but both 9i as 10g have problems with this on windows 2003.
When you stop the service, the db is also stopped, but when you stop the server (which causes of course the services to stop), the db is not stopped (resulting in an inconsistent db).

Reply
mdinh says:

March 28, 2008 at 8:12 pm

It’s has been a while for me, but can’t you start Oracle by starting the service?

Reply
mdinh says:

March 28, 2008 at 8:13 pm

Duh! I did not read response #5.

Reply
prodlife says:

March 29, 2008 at 2:47 am

Wow. Lots of comments.

So, to sum things up:
If you leave the service up, you can start and stop the instance like a normal admin.
Once you stopped the service, you need to start the service before you can connect to instance.
And you can use “net start” if you want to pretend that this is a real operating system.

No wonder MS folks think Oracle is difficult!

Reply
Piero says:

June 3, 2008 at 4:05 pm

I know that this tread is old!!
But have you tried with
set oracle_sid=ORCL , with oracle_sid wrote in lowercase, I use everytime this becouse once I wrote in uppercase and it doesn’t work!!
Hope helps to someone!!
Piero

Reply
LDS says:

March 6, 2009 at 10:43 am

On Windows, what the service does when started/stopped can be controlled using the “Administration Assistant for Windows”. If you select your database here and then “Startup/shutdown options” from the right-click menu, you can select
1) If to start the instance when the service is started (leave the service to startup type -> Automatic but don’t start the instance to get a *nix like experience)
2) If the instance has to be shutdown when the service is stopped, and how (normal/immediate/abort)
Strangely, this options have no sensible defaults (i.e. the instance is not shutdown when the service is stopped)
This settings should be available in the registry also, but I find easier to manage them here.

Also this ustility allows to manage OS database administrators and operators and external users.

Reply
Iggy Fernandez says:

March 9, 2009 at 3:00 am

Hi, Chen,

From Sam Alapati’s book, I learned that the ORADIM utility can be used to change the registry settings that control how the service is started; for example, you can specify that the database should not be started when the service is started. The ORADIM utility can also be used to start and stop the service and the database.

C:\Documents and Settings\IGNATIUS>oradim
ORADIM: [options]. Refer to manual.
Enter one of the following command:
Create an instance by specifying the following options:
-NEW -SID sid | -SRVC srvc | -ASMSID sid | -ASMSRVC srvc [-SYSPWD pass]
[-STARTMODE auto|manual] [-SRVCSTART system|demand] [-PFILE file | -SPFILE]
[-SHUTMODE normal|immediate|abort] [-TIMEOUT secs] [-RUNAS osusr/ospass]
Edit an instance by specifying the following options:
-EDIT -SID sid | -ASMSID sid [-SYSPWD pass]
[-STARTMODE auto|manual] [-SRVCSTART system|demand] [-PFILE file | -SPFILE]
[-SHUTMODE normal|immediate|abort] [-SHUTTYPE srvc|inst] [-RUNAS osusr/ospass]
Delete instances by specifying the following options:
-DELETE -SID sid | -ASMSID sid | -SRVC srvc | -ASMSRVC srvc
Startup services and instance by specifying the following options:
-STARTUP -SID sid | -ASMSID sid [-SYSPWD pass]
[-STARTTYPE srvc|inst|srvc,inst] [-PFILE filename | -SPFILE]
Shutdown service and instance by specifying the following options:
-SHUTDOWN -SID sid | -ASMSID sid [-SYSPWD pass]
[-SHUTTYPE srvc|inst|srvc,inst] [-SHUTMODE normal|immediate|abort]
Query for help by specifying the following parameters: -? | -h | -help

The ORADIM utility is documented in the Platform Guide for Microsoft Windows.

Yes, all these platform (and version) differences make life difficult. But I firmly believe that no DBA should be expected to know them. The procedures for shutting down a database should always be documented. An organization that does not document such procedures is asking for trouble. If anybody asks me how to shut down their database, my answer is “I don’t know.” Is this Windows? Is this Linux? Is this VMS? Is this Oracle 7.3? Is this Oracle 10g? Is this RAC? Is there ASM? Is there SunCluster involved? Is there HP ServiceGuard involved? Is there Veritas VCS involved? These are some of the technical complexities. There can also be procedural complexities.

Neither would I know how to start their database 🙂

Iggy

Reply
prodlife says:

March 9, 2009 at 3:11 am

@Iggy
100% agree on documenting procedures.
Although I admit we never went as far as documenting our startup/shutdown procedures. Sadly, we are still working on the installation documents 😦

Turns out that teaching DBAs to write is just as difficult as teaching writers how to be a DBA.

Reply
neil says:

May 8, 2009 at 11:10 pm

I have been having the similar issues continuing from the second reboot of my Windows Svr 2003 box. The database for whatever reason won’t start automatically, despite the autostart/shutdown settings in the registry:
Value 16
Name: ORA_ORCL_AUTOSTART
Type: REG_EXPAND_SZ
Data: TRUE
Value 17
Name: ORA_ORCL_SHUTDOWN
Type: REG_EXPAND_SZ
Data: TRUE
Value 18
Name: ORA_ORCL_SHUTDOWNTYPE
Type: REG_EXPAND_SZ
Data: normal –changed this setting from default
Value 19
Name: ORA_ORCL_SHUTDOWN_TIMEOUT
Type: REG_EXPAND_SZ
Data: 90

I am not a DBA(obviously) and didn’t have time to become one in order to run this Oracle instance successfully 🙂 . I am trying to run this to support a VMware Virtual Center installation for testing. Is there some magic that i’m missing that’ll get this running automatically after a reboot?
at this point i have to execute these commands after a reboot (how do you point sqlplus to a script file to batch this out?):
sqlplus “/ as sysdba”,
then from the SQL prompt”
shutdown
startup

Thanks in advance!!
Neil

Reply
- prodlife says:
  
  May 11, 2009 at 5:16 pm
  
  I thought it was clear from the post that I’m not a windows expert, and cannot really help here.
  I hope you can find the solution elsewhere.
  
  Reply
marc says:

July 21, 2009 at 2:54 pm

@neil
I guess your problem lies here:

>Name: ORA_ORCL_SHUTDOWNTYPE
>Type: REG_EXPAND_SZ
>Data: normal –changed this setting from default

Change that value to IMMEDIATE. With NORMAL you won’t get a clean shutdown before your server reboots.

You don’t need SQLPLUS for your batchfile. Just use the ORADIM-utility with the appropriate parameters like posted by Iggy:

oradim -shutdown -sid -shuttype srvc,inst -shutmode immediate

oradim -startup -sid -starttype srvc,inst

marc

Reply
girlgeek says:

April 27, 2011 at 4:54 pm

Hi Chen,
I wanted to do a quick startup of my toy windows DB. I was perfectly willing to use the GUI – where did the icon escape to? I found it, but I’m just agreeing with you, it would have been faster, to use the NET START command, but for that I really needed a good ‘list services’ command to recall the name. How much faster the procedure would have been with a proper set of command line commands.

Be Well,
Claudia

Reply
bvwatson says:

March 2, 2012 at 12:06 pm

re: net start …

under Win7 (and probably Vista), the User Access Control (UAC) setting can cause an “Error 5” to be thrown when you use the “net start” command from the command line.

System error 5 has occurred.
Access is denied.

This drove me crazy. Apparently, the UAC got set to one of Microsoft’s “recommended” settings (there are 4 choices, 2 are recommended), which disables net start. (And this occurs even if you are logged in as an administrator). The only option there is to use the GUI, which of course means no scripting.

To fix it: I turned UAC off (set it to the lowest setting, which is “not recommended” by Microsoft). Problem solved.

Another suggested fix is to launch the cmd.exe with “Run as administrator”. I don’t know if that works… it just seemed wrong to me to have two ways to run the command line, but maybe it works for you.

Reply
geet says:

March 21, 2012 at 1:25 pm

hi,
plz help m out
im trying to strat my database and not getting what to do after this

C:\Documents and Settings\chen>sqlplus

SQL*Plus: Release 11.1.0.6.0 – Production on Thu Mar 27 23:03:26 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Seems that Windows will not let me connect to an idle instance to start it. Very strange.

Reply
Fabio says:

August 23, 2012 at 9:08 am

Hi, I had also a similar problem and I solved it by setting AUTOSTART registry entry to TRUE.
Bye

Reply