When you install Oracle Xe on a windows machine (mainly server OS’s like Windows 2003) you may notice that the database is not always starting after a reboot. This ussualy happenes when the Oracle XE was installed under a different user than the one you are currently logged in as.
It is easy to start the database by going to Start-> All Programs -> Oracle Database 10g Express Edition – Run SQL Command Line and simply type “STARTUP”.
However, if you want to automate this process you can use the StartDB.bat.
1) Navigate to your Oracle XE directory (typically under C:\oraclexe\app\oracle\product\10.2.0\server\BIN)
2) Edit the StartDB.bat file – If the Oracle Service andthe listener is started you can delete the first two lines (net start OracleXETNSListener and net start OracleServiceXE) and save the file.
3) Go to Start > All Programs > Accessories > System Tools > Scheduled Tasks and Use the Windows Scheduler to execute the batch file “When my computer Starts”.
Oradim messages are not displayed in the command prompt (you can still find the results in oradim.log under C:\oraclexe\app\oracle\product\10.2.0\server\database) so it might be wise to try the command first using the command prompt.
If you get ORA-01031: insufficient privileges when you trying to start the db (you probably will if you are logged on as different user than the user who installed Oracle XE) all you need to do is add the user to the ORA_DBA windows group.
1) Right click on My Computer and click Manage.
2) Navigate to Local Users and Groups and click on Groups.
3) Double click on the ORA_DB group and add the user you are currently logged in as.
4) Re run the oradim -startup -sid XE -starttype inst > nul 2>&1 to verify that it works.
Even Oracle veterans may encounter some challenges when it comes to setting an odbc using the Oracle XE Client. As every DBA will tell you, you will need to configure the tnsnames.ora (using net configuration Assistant or by going to oracle home\network\admin and edit it manually). After that, theTNS Service name in the ODBC will be populated with the entries from the tnsnames.ora file. You will only be required to choose the appropriate entry and you should be good to go.
This was the case in all the latest Oracle releases However If you will download the Oracle XE client you will quickly discover that there is no tnsnames.ora under the Oracle client XE directory structure. In addition, the network\admin directory is not there as well.
Oracle documentation explains (In their usual user friendly way) how to connect to the Data Base using a connection string (username/password@[//]host[:port][/service_name] where service name is usually XE and port is 1521) but it is not that obvious how to configure the odbc to use this.
The solution is simple enough (once you find it). All that needs to be done is to type the connection string on the ODBC’s “ TNS Service Name” field. Just add the line 127.0.0.1/XE (no need to use the drop down) and test the connection.
If the Data Base is on a different machine, you can replace 127.0.0.1 with the address of the remote server.
If you get the following error on Oracle XE:
ORA-12520: TNS:listener could not find available handler for requested type of server
This is probably because the value for the processes parameter is too low.
For some reason, Oracle set the default value of the processes parameter on Oracle XE to 40. If your application opens many process or several queries are executed at the same time, the ORA-12520 could be displayed.
The solution for this issue is simple. All you need to do is give the processes parameter an higher value.
open sql plus (All Programs -> Oracle Database 10g Express Edition -> Run SQL Command Line) and run the following command:
Alter system set processes=100 scope=spfile;
Restart the DB:
if needed, an higher number can be provided.