Creating an oracle database for SQL Server people.
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.