2

I am a bit new to Oracle, having used mostly SQL, so some of my understanding of how oracle works and what users/connection strings to use might be wrong or non existant.

I am trying to use roundhouse to create/manage an Oracle database. For now I am trying to get to work on my dev machine running Windows 7 and VS 2010. I have a simple sandbox project where I am trying to create a test database. I have installed Oracle 11g standard edition on my machine.

First question is, would I even be able to create a brand new database (the Oracle db tells me they are called 'Schemas' in Oracle) using roundhouse? From my understanding, unlike SQL server, there is no Master database with an sa user who can create any database.

I found the wiki page for Oracle on the roundhouse site, but it only talks about modifying an existing Oracle database, not creating a new one. I am having trouble finding examples where a brand new database was created in Oracle using roundhouse before creating the required tables, stored procs etc.

If it is possible, what do I need to do / setup? How should I set up my configuration file? Given is my current configuration file.

<?xml version="1.0" encoding="utf-8" ?>
<Project DefaultTargets="DBDeploy" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <!-- Visit http://roundhouse.googlecode.com/ for details -->

  <UsingTask AssemblyFile="roundhouse.tasks.dll" TaskName="roundhouse.tasks.Roundhouse" />

  <PropertyGroup>
    <DBServer>dev-mac-303.qtest.local</DBServer>
    <DBName>RoundhouseSandbox.OracleRoundhouse</DBName>
    <Environment>DEV</Environment>
    <DBConnectionString>Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dev-mac-303.qtest.local)(PORT = 1521)))(CONNECT_DATA = (SID = DEVEL)));User Id=mytest;Password=mytestdevel;Persist Security Info=false;</DBConnectionString>
  </PropertyGroup>


  <Target Name="ApplyDatabaseChanges">
    <Roundhouse
        ServerName="$(DBServer)"
        DatabaseName="$(DBName)"
        ConnectionString="$(DBConnectionString)"
        ConnectionStringAdmin="$(DBConnectionString)"
        CommandTimeout="60"
        CommandTimeoutAdmin="300"
        VersionFile="OracleRoundhouse.dll"
        AlterDatabaseFolderName="alterDatabase"
        UpFolderName="mytest\updates"
        RunFirstAfterUpFolderName="mytest\runFirstAfterUp"
        FunctionsFolderName="mytest\functions"
        ViewsFolderName="mytest\views"
        SprocsFolderName="mytest\storedprocedures"
        IndexesFolderName="mytest\indexes"
        PermissionsFolderName="mytest\permissions"
        SchemaName="Change"
        VersionTableName="Version"
        ScriptsRunTableName="ScriptsRun"
        ScriptsRunErrorsTableName="ScriptsRunErrors"
        EnvironmentName="$(Environment)"
        DoNotCreateDatabase="false"
        OutputPath="C:\temp\test.mytest.Database\ChangeTracking\"
        WarnOnOneTimeScriptChanges="false"
        Silent="true"
        DatabaseType="roundhouse.databases.oracle.OracleDatabase, roundhouse.databases.oracle"
        WithTransaction="false"
        RunAllAnyTimeScripts="false"
        DisableTokenReplacement="false"
        Debug="true"
        />
  </Target>

  <Target Name = "DBDeploy"
          DependsOnTargets="
        ApplyDatabaseChanges;
      "
     >
  </Target>

</Project>
Chaitanya
  • 5,203
  • 8
  • 36
  • 61
  • 1
    You don't create "brand new databases" often in Oracle, forget the concept of multiple databases that exists with SQL Server, MySQL and others. One database instances hosts one database, and one database only. If you want to create a brand new database, use `dbca` that comes with Oracle and sets up everything. – Mat Mar 18 '13 at 11:30
  • Ah, I see. Fair enough. But is there anyway of creating the oracle database instance using Roundhouse? If not Roundhouse, then some other tool perhaps? Assuming that the required oracle components are installed on the machine, how would you automatically create an instance without using the oracle gui? – Chaitanya Mar 18 '13 at 13:12
  • I don't understand why you'd want to create database instances automatically. You can script instance creation (IIRC dbca has the option to generate scripts that match the options you selected). No idea if Roundhouse supports this. – Mat Mar 18 '13 at 13:15
  • This is part of automating our deployment. We might even leverage this for integration testing later. Basically, every sprint, we want to reset our staging environment to how production looks (which doesn't have our database instance) and deploy it using automated tools. This way when we are ready to go to production we can use the automated deployment tool which as been tested as opposed to frantically making sure that we are installing the right database, granting the correct permissions etc. etc. – Chaitanya Mar 18 '13 at 13:27
  • 1
    Well as I said you can script a database creation. But the easiest would be to keep a permanent staging database and use a specific schema for your tests. Drop the schema when you want to rebuild from scratch. – Mat Mar 18 '13 at 13:30
  • If you can script this, you ought to be able to pass a custom create database script, but it may not be possible. – ferventcoder Mar 19 '13 at 02:06

2 Answers2

4

I've done Oracle with RoundhousE on an application that's now in production.

RoundhousE is not a good fit for creating an Oracle database from nothing. The Oracle databases I've seen use a schema as the primary logical division; in other words, in Oracle, you'll use schemas in much the same way you would databases in SQL Server. Furthermore, the database itself is generally already set up, and you don't create new databases. This is thought of more like an installation step; you can't even form a network connection until the database is set up. Roughly speaking:

SQL Server      |  Oracle                             
----------------+------------------------------------
Server install  |  Server install & database set up
Database        |  Schema

So don't have RoundhousE set up the Oracle database. Assume that's already done; think of it as an install step. You wouldn't expect RoundhousE to install SQL Server or configure it.

That said, RoundhousE has a facility for specifying a create script. You might be able to leverage this to create your schema, but RoundhousE does not yet have the ability to specify a drop script (to my knowledge). You will need a separate tool for that.

I ended up using SQL*Plus Instant Client to manage creation and dropping of schemas within my build scripts: http://www.oracle.com/technetwork/topics/winsoft-085727.html. You'll need instantclient-sqlplus-nt-11.2.0.3.0.zip and one of instantclient-basiclite-nt-11.2.0.3.0.zip or instantclient-basic-nt-11.2.0.3.0.zip. (Newer versions are fine; just make sure you get matching versions.) Unzip them to the same directory. (On a side note, you can use the basic-nt or basiclite-nt instead of installing Oracle Client; you just need to make sure your application can find the DLLs.) You'll need to make command line calls for this to work. When RoundhousE gets drop scripts, I intend to dump this if I can.

Once you have a schema in place, RoundhousE works fairly well. Do check the bug lists over on Github and Google Code, though. There are a few weird issues. Once you get past them, though, it's well worth all the effort you've put into it. I wrote most of the database deployment build scripts almost 2 years ago, and they're one of the things that's stood the test of time on the project. They're written in PowerShell, though, so I don't know how this will fare in MSBuild.

Update:

In the years since I wrote this, Flyway has gotten popular. Consider using it or other tools, as they may have superior support for Oracle. RoundhousE hasn't fixed a lot of the issues that were identified several years ago. It seems primarily focused on supporting SQL Server.

jpmc26
  • 28,463
  • 14
  • 94
  • 146
1

Looking at where you are coming from, it's probably time to upgrade.

From what I remember about Oracle and RH support, this might be possible. There is a switch for an administration connection string - CSA (connection string admin) that would handle the schema creation for you. And yes, Oracle has different lingo (database is same as sql server's server, schema is sql server's database).

It's been awhile since I checked on the support for Oracle, but if you take a look you may find some more information.

ferventcoder
  • 11,952
  • 3
  • 57
  • 90