How to configure Plastic SCM with an Oracle Database backend

Since Plastic SCM 2.9, we have supported Oracle database backends. Plastic SCM stores all data and metadata on standard database backends, which is great for data integrity. It also allows you to run custom reports by simply running standard SQL queries, something you obviously couldn't do if we were using some cumbersome ad-hoc file based storage. So far, we do support SQL Server (check how to configure it here), MySql (check here for the instructions on how to set it up) and Firebird, which is the one included by default. When you install a Plastic SCM server on both Windows and Linux it will use a Firebird backend by default. On Windows, it will use an embedded Firebird instance -- which means there won't be a separate database server process; it will be run by the Plastic SCM process itself -- and on Linux it will use a normal Firebird server. What I'm going to set up here is the following scenario: the Plastic SCM server will work with a separate Oracle server (configured on a different machine) as the following graphic shows:

Of course, we could run the Plastic server on the same machine where the Oracle server is installed, but this will give us extra CPU power since we'll be using two servers instead of one! :-) Let's see how to do it:

  • Stop the Plastic server.
  • Edit/create a db.conf file on the server directory with the right Oracle connection instructions.
  • See if the server can connect against the Oracle instance.
  • See if the client can see the new databases.


Stop the Plastic server
If you're on Windows, this is an easy step. Just go to services and stop Plastic SCM. If you're on Linux it's not hard either: su to root and go to the Plastic SCM server installation directory (typically /opt/PlasticSCM/server) and run:

# cd /opt/PlasticSCM/server
# sudo ./plasticsd stop


Edit/create a db.conf file
If you're on Linux, you'll have a db.conf file at the server's directory (/opt/PlasticSCM/server/db.conf). You'll just have to edit it. In case you're on Windows, by default, the file won't be there, so just create a new one. Here's what you'll have to put in the file for the Oracle connection:

Note: The lines <ConnectionString>....</ConnectionString>, <AdminConnectionString>....</AdminConnectionString>, and <DatabaseCreationCommands>....</DatabaseCreationCommands> must each go on one complete line. They have been split here for the sake of visual clarity.

<DbConfig>
  <ProviderName>oracle</ProviderName>
  <ConnectionString>Direct=true;User={0};Password={0};
    Data Source=oracle.codicefactory.com;Port=1521;SID=orcl
  </ConnectionString>

  <AdminConnectionString>Direct=true;User Id=SYS;
    Password=oracle;Data Source=oracle.codicefactory.com;
    SID=orcl;Connect Mode=sysdba
  </AdminConnectionString>

  <DatabaseCreationCommands>
      create smallfile tablespace @PlasticDatabase datafile
    '@PlasticDatabase.dbf' size 10M reuse autoextend on next 10M;
    create user @PlasticDatabase identified by @PlasticDatabase
    default tablespace @PlasticDatabase temporary tablespace Temp account
    unlock quota unlimited on @PlasticDatabase;
    grant connect, resource, create session, create table, 
    create view, create any index to @PlasticDatabase;
  </DatabaseCreationCommands>
</DbConfig>

First of all, you have to specify the kind of backend you're going to use. That's the line 'ProviderName'. Here, we specify Oracle.

Second, you have two connection strings: one for the regular operations and one for the administrative ones. What does that mean? Plastic SCM will always connect to Oracle using the first connection string except when it has to create new repositories (for instance, during the first start-up with the new backend). Then it will use the AdminConnectionString.

Why we do this? Because if you're using an Oracle backend chances are you're setting it up on a corporate server, which means your IT department will have tight control on it, and they won't probably like the idea of having an application running with high permissions continuously. So this way, we clearly separate the way in which connections are established, which will make your IT team happy.

Then we have the DatabaseCreationCommands which lets you customize the way in which databases are created.

By default, every Plastic SCM repository will be a tablespace, and we'll create a user associated with it. You can find the create tablespace and create user sentences there. You can modify them to better fit what you really want to achieve.

For instance, the tablespace is created as a small one, but you'll probably want to create it as a bigger file, adjust the initial size to something bigger than 10MB, and autoextend with a larger amount too.

 

See if the server can start with the new configuration parameters

Once you've edited db.conf, the next step is to start up the Plastic SCM server again and check if everything is up and running.

While you can directly use the plasticsd script to restart your daemon on Linux, or go to services and start the service again (it will work if you set everything up correctly), I'm going to show you a small trick which is very useful for diagnostics. Just run

plasticd --console

The server will start in console mode. Wait until you read a message saying the server is up and running and see if there are any errors.

In case you need detailed information, check the loader.log.txt file, which will contain the errors.

Note: Something very useful for diagnostics is modifying your logger configuration (loader.log.conf) to make plasticd output the log on the console, and then rapidly check if something is wrong. Remember to set it back to file logging once you're done!

Once you've checked this, you can start up the service/daemon and you can run it with the regular services or ./plasticsd method.

 

See if you can connect to the new database

If you run a cm lrep command against your server, now you should see your new empty databases being created. You should be able to create new repositories too.

Some important notes for database administrators: Plastic SCM does not create a new Oracle database to store the repositories' data. Instead of that, it creates a new tablespace inside the existing database instance. The reason for doing that is that an Oracle database is a very heavyweight object, which implies new processes and a lot of resource consumption.

One of the most important consequences is the encoding that Plastic SCM will use. In Oracle, the encoding is established in the CREATE DATABASE sentence, and after that, it is very tricky to change it. The simplest case is that the new encoding that you want to set is a strict superset of the old encoding. In this case an ALTER DATABASE CHARACTER SET statement should work fine.

Thus, Plastic SCM will use the encoding defined by the database instance in which the tablespaces are created. Take this into account in case you want to use Arabic, Asian, or other non-standard symbols in your version control system.

In order to know which encoding is configured in your database, mount the target database instance and execute the following query in sqlplus:

select value from nls_database_parameters 
where parameter='NLS_CHARACTERSET';

We recommend setting the encoding to utf8 for a better user experience.

Further information here:
http://download.oracle.com/docs/cd/B10500_01/server.920/a96529/ch2.htm
Here you will find a list of encodings and their description:
http://download.oracle.com/docs/cd/B10500_01/server.920/a96529/appa.htm#967868

Screencast
The following screencast we uploaded to our YouTube channel shows how to set up an Oracle backend on a Linux Plastic SCM server. Check it out to see the steps I just have described in action.