Starting Oracle on Windows

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

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Mar 27 22:56:25 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

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

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Mar 27 22:57:11 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

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

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.

Advertisements

19 Comments on “Starting Oracle on Windows”

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

  2. philippe says:

    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)

  3. philippe says:

    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)

  4. Rob van Wijk says:

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

  5. Freek says:

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

  6. mdinh says:

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

  7. mdinh says:

    Duh! I did not read response #5.

  8. prodlife says:

    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!

  9. Piero says:

    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

  10. LDS says:

    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.

  11. Iggy Fernandez says:

    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

  12. prodlife says:

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

  13. neil says:

    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

    • prodlife says:

      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.

  14. marc says:

    @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

  15. girlgeek says:

    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

  16. bvwatson says:

    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.

  17. geet says:

    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.

  18. Fabio says:

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s