Ariel Schapiro’s Blog

These days I had to do a migration from SQL Server to Oracle 8i (installed on Microsoft Windows Server 2003). Since I had no experience as an Oracle DBA and it didn’t result as simply as I expected, I’ll show the path I followed to achieve that goal.
First, some concepts:
In Oracle, you have to create a service for your new database, so instead of one service running like in SQL Server, you have one per Database:

As you can see in the picture, the last three services belong to different databases.The easiest way:
The easiest way to create a database in Oracle 8i may be using the Oracle Database Configuration Assistant (ODCA) which will guide you through the steps and will create everything automatically: the service, the files (tnsnames.ora, listener.ora and the database files obviously), etc.

My story:

I had a problem trying to use the ODCA when it came to the point of executing the actions: “ORA-12560: TNS: Protocol Adapter Error.”.
The ORACLE_SID was set ok, the services where fine, the rest of the files where all right too, so I asked for some help online.. but we couldn´t get it to work anyway…

My solution:
So I decided to use the “save information to a batch file” option at the last step of the wizard to debug whatever that was going wrong.
I couldn’t make a connection without specifying the host string, so I added it to every connection command on the script created by the wizard. So in order to create my new oracle database, named “NDB”, I followed the following steps:

• Run the Oracle Database Configuration Assistant for creation of a Database and followed these steps:
  • Type of Databse: Typical.
  • Source files: Create new database files.
  • Primary type of application: Multipurpouse.
  • Concurrently connected users: default value.
  • Options: default.
  • Names:
    • Global Database Name: NDB
    • SID: NDB
  • In the last step, select “save information to a batch file”.
    • Select the location and name for the creation script. For this example we are using “D:\Oracle\Ora81\DATABASE\scripts\NDB\ndb.bat”

• Go to the script folder, in this case “D:\Oracle\Ora81\DATABASE\scripts\NDB\”.
• Edit ndb.bat file:
  • Delete from line 4 to the end leaving the last line, so the file looks like this:

set ORACLE_SID=NDB
D:\Oracle\Ora81\bin\oradim -new -sid NDB -startmode manual -pfile “D:\Oracle\admin\NDB\pfile\init.ora”
D:\Oracle\Ora81\bin\svrmgrl @D:\Oracle\Ora81\DATABASE\scripts\NDB\NDBrun.sql
D:\Oracle\Ora81\bin\svrmgrl @D:\Oracle\Ora81\DATABASE\scripts\NDB\NDBrun1.sql
D:\Oracle\Ora81\bin\oradim -edit -sid NDB -startmode auto

• Save ndb.bat.
• Edit DOOLSCArun.sql:
  • Open DOOLSCArun.sql for edition.
  • Add to every occurrence of “connect INTERNAL/password” the string “ @ndb” so it becomes “connect INTERNAL/password @ndb”.
  • Save DOOLSCArun.sql.
• Edit DOOLSCArun1.sql making the same replacement as above.

Filling the databases:
Once you´ve created the databases, you can use some migration tools to complete the job:

• AdventNet SwisSQL: a great tool to migrate SQL Server stored procedures to Oracle PL/SQL. The first stored procedures you can migrate may be the table and constraints creation ones. This tool parses the stored procedures mapping data types, object names, etc.
• SQL Server Dumper: a tool to create the inserts out of the SQL Server databases, which you can use in combination with AdventNet SwisSQL to translate data types.
  
• PLSQL Developer: a great tool, such as TOAD. A programming IDE for depveloping and testing stored procedures, schemas and SQL scripts.

A detail you should be aware of is that in oracle, tables, constraints, columns have a limit of 30 characters! That meant some changes in my databases. So it’s better to know it in advance.