Oracle – How to Setup a New Instance from Command Line

command lineoraclewindows

I'm trying to familiarise myself with the process for setting up new instances/databases from the commandline with the aim of building a tool for deploying databases.

The process we are thinking of using is

  1. (optionally) create a new instance to deploy to
  2. import some data using impdp
  3. run upgrade scripts on the database to get it to the desired version

The bit I'm currently focusing on (+ have just cracked) is the instance creation, although I'd be interested to hear comments on other parts of it + any information on alternative approaches or things that are a good idea! Im very new to databases aswell as oracle and am having to figure things out mostly from scratch.

The key requirement for all this is that is should be done in a way that can be scripted so we can automate the process.

I'm working on windows with 12c standard edition one and installed just the database software.

Best Answer

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:

LSNRCTL> start
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.

Automatic registration

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:

sqlplus name/pass@serviceName

do this instead:

sqlplus name/pass@//localhost/serviceName

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...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=1521)))

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.

Related Question