Setting up an Oracle backend for Plastic SCM

Monday, March 01, 2010 2 Comments


As I mentioned on the Plastic SCM 2.9 announcement, now Plastic supports a new backend: Oracle.
Plastic SCM stores all data and metadata on standard database backends, which is great for data integrity and also to allow you running custom reports by simply running standard SQL queries (something you couldn’t obviously 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 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 but it will be run by the Plastic process itself) and on Linux it will use a normal Firebird server.
What I’m going to setup here is the following scenario: Plastic 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 check how to do it:
  • Stop the Plastic server
  • Edit/create a db.conf file on the server directory with the right Oracle connection instructions
  • Check the server can connect against the Oracle instance
  • Check the client can see the new databases

    Stop the Plastic server
    If you’re on Windows this is a trivial 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 db.conf
    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.
    Let’s check the contents you’ll have to put for the Oracle connection:

    Note: remember <ConnectionString> and <AdminConnectionString> must go on one complete line each, it has been splitted here for the sake of 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’ve to specify the kind of backend you’re going to use: that’s the line ‘ProviderName’ and we specify oracle.
    Second you’ve two connection strings: one for the ‘regular operations’ and one for the ‘administrative ones’. What does it mean? Plastic 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) when 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’ve the DatabaseCreationCommands which lets you customize the way in which databases are created.

    By default every Plastic repository will be a tablespace, and we’ll create a user associated to it. You can find the create tablespace and create user sentences there and you can modify them to better adjust to what you really want to achieve.
    For instance, the tablespace is created as an small one, but probably you want to create it as a bigfile and also adjust the initial size to something bigger than 10Mb and autoextend with a larger amount too.

    Check the server can start with the new configuration parameters
    Once you’ve edited db.conf the next step is to start up the Plastic server again and check 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 (and it will work if you set up everything correctly) I’m going to show you a small trick which is very useful for diagnostics: just run

    plasticd --console


    And the server will start in console mode. Wait until you read a message telling the server is up and running on check the errors if any.
    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 you can start up the service/daemon, you can run it with the regular services or ./plasticsd method.

    Check 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 must 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's database is a very heavyweight object, which implies new processes and a lot of resources consumption.

    One of the most important consequences of that 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 to set 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’ve just uploaded to our YouTube channel shows how to set up an Oracle backend on a Linux Plastic server. Check it to see the steps I just have described in action.


  • Updated articles are maintained in the knowledge base of Codice Software at: http://www.plasticscm.com/infocenter/technical-articles.aspx

    2 comments:

    1. Hi,
      I've got a question regarding a setup with Oracle.
      How can I create a repository without an administrative user within an aexisitng tablespace?
      I have a tablespace created by our DBA and the DBAs don't want to allow an application to create tablespaces.

      regards
      Gerald

      ReplyDelete
    2. Wow! I think you should ask your DBA 'cause I'm sure I can't teach him Oracle... :P

      In the oracle configuration we separate a "create database" definition and a "connection" definition.

      ReplyDelete