Creating a new instance
The following command line call will create a new database + instance
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName sid -sysPassword pass -systemPassword pass
because I have a dedicated oracle user for my install I also had to use -serviceUserPassword. With regards to the passwords, despite being in silent mode if you miss these out, then you will be prompted for them rather than the program failing so while technically the minimum is
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName sid
you wont be able to script that - the program will just hang indefinitely.
this command's usage is slightly mysterious and if you get it wrong it provides the amazingly useful message:
'Not all required options provided for the command "createDatabase".Please refer to the manual for details.'
Thankfully if you trawl through the reams and reams of online documentation eventually you can find what information it needs here along with what all the options are and what they are for.
A further thing I found here is that it can fail but it doesnt tell you from the command prompt - you should check the log files that it makes (it will tell you where these go). Just because it says 100% complete doesnt mean it has actually worked! You could just have got 100% of the way to failure! In particular if it finishes very quickly then this is a warning sign - expect it to take at least a couple of minutes. I found I needed to run this as administrator the second time around - I suspect this is always the case but on the first run the UAC was disabled.
Starting the listener
This bit is once per-server, so doesnt need scripting for my needs. I thought it was worth mentioning though as a few more things were required to make my instance usable from other machines.
I found that after doing the above I couldnt connect to the database remotely but could from sqlplus locally. In someways this local access was a false positive as it turned out there was no listener running on the server (probably because of the software only install). You can determine if this is the case by looking to see if TNSLSNR.exe is running on the server.
Thankfully starting the listener is easy if it isnt already running. You use a command line program called lsnrctl. This is probably the nicest oracle command line program I've used. It tells you how to use it and just worked without any messing about. You can just use it interactively and type 'start' when it comes up.
Note you may need to run the above from an administrator shell If you see something like the following:
Starting tnslsnr: please wait...
Unable to OpenSCManager: err=5
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
then try doing it as admin.
After it started I still couldnt connect to the database externally. You can use lsnrctl status to determine if the listener knows about the database instance. If you get a message somewhere in the output that says
The listener supports no services
then it isnt working. I wanted to use automatic registration because it sounded like the way to go and it turned out from a bit of reading, that instances register with listeners when they start.
To restart an instance you do the following (docs here):
Use the sqlplus command line. You will need to login as a user with SYSDBA priveledges (eg system) syntax:
sqlplus name/pass as SYSDBA
then run 'shutdown' and wait for it to finish. then run 'startup' and wait for it to finish.
There is a good post here that also suggests another way of doing it
note if you haven't got a tnsnames.ora file you will need to specify the host in the connection string - this wont work:
do this instead:
Hey presto you should have your instance registered with the listener. You can verify this by running lsnrctl status again.
You should see something like this:
Service "horse" has 1 instance(s).
Instance "horse", status READY, has 1 handler(s) for this service...
Opening a firewall port
The final step in making the instances on the server accessible is to open a port on your firewall. You can determine the port you need to open from the lsnrctl status message - there should be a section that looks like this:
Listening Endpoints Summary...
1521 is the default port if you don't have a listener.ora file as mentioned here
To open a port on the firewall on our windows 7 server I used the following instructions.
After doing all of the above I could connect to the instance from my local machine. Sucess! Hopefully deploying more instances is just going to involve repeating the dbca command. Since the listener will already be up it should just register with it immediately. I'll try this out and post any further complications.