If when using Oracle on the Windows platform, you get the following error message when trying to login:
ORA-12514: TNS:Listener does not currently know of service requested in connect descriptor
Your tns listener is probably running but the actual Windows service may not be running and you may also see oracle.exe running as a process but not taking up much system resources.
If the Windows service is set to automatically start when the server boots but the database does not start, you may have an improper registry setting or you may have a bad service.
Things to check
1. Check Task Manager for the Oracle.exe process, if it is visible then the service has 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.
If there are no errors in the logs then try and start the database.
C:> sqlplus "/ as sysdba"
connected to an idle instance
If the database starts, then the problem is in the service.
Checking the Windows Service
Open the registry using regedit. Remember to backup the registry before making any changes.
Look for 2 keys:
ORA_SID_AUTOSTART and ORA_SID_SHUTDOWN - the SID part is your database name
Both keys should be set to TRUE, if the ORA_SID_AUTOSTART is set to false then the server starts but does not start the database. The ORA_SID_SHUTDOWN should be set to true so that if the server is shutdown the service will shutdown the database as well.
If the service now fails after rebooting you will need to recreate the service and use the oradim utility, this is not going to be discussed here but more information about it can be found on the web.
Create an automatic startup script
If on the other hand you want to just create a script to start the database when the machine starts up then use the script below:
sqlplus -S "/ as sysdba" @startup.sql
save this as a .bat file - e.g. startup.bat
The code above moves the computer to the C drive then goes to the folder oracle-db/BIN which is the location of my sqlplus.exe, it then starts sqlplus in silent mode (-S) and runs the sql file startup.sql
In the startup.sql file put the following code:
Now you can either double click on the startup.bat file and your database should startup or use the Windows Scheduled Task wizard which can run the file for you once the machine has started or once the user has logged on.