I started working on a simple migration of Oracle data to SQL Server and had a hard time finding a single document explaining the entire process. Having almost no experience with Oracle, any steps that involved Oracle took me a long time to complete. So here are the steps that I followed.
First, you need to run a script to create a Replication Administrative Schema User. There is a script that is provided when you install SQL Server 2008. It is located at :\Program FilesMicrosoft SQL Server\MSSQLInstalloracleadmin.sql. You need to copy this script to the Oracle server. In this example, I copied the script to the root of the C drive. Then you need to run the script using sqlplus. Here are the steps to run it using sqlplus on a Windows Server 2008 R2 server with Oracle installed.
Open a Command Prompt.Type sqlplus.Logon with an account with DBA privileges on the Oracle server.Run the script by typing @ and then the path to the script in quotes. For my example this would be @”c:oracleadmin.sql”.You will be prompted for a name and password for the new user. Also, you will be prompted for the default tablespace.
Next, you need to give the Replication Administrative Schema User you just created SELECT permissions on the tables you want to access in SQL Server.
You can grant the SELECT permissions in the Database Control console.
On the Oracle server, you need to login to the Database Control with a user that has DBA permissions.Click on the Server tab. Under Security, click Users.Select the user you created, and then click Edit.Click on the Object Privileges tab. In the Select Object Type list, select Table, and then click Add.Under Select Table Objects, click the flashlight icon.In the Schema list, select a schema.Click Go.Select the tables you want to be able to access in SQL Server.Under Available Privileges, click SELECT, and then click Move.Click OK, and then click Apply.
Next, you need to install the Oracle client networking software and the Oracle OLE DB provider on the SQL Server Distributor, so that the Distributor can make connections to the Oracle Publisher. You should install the most recent version of the Oracle client software. You may choose to install different options depending on your needs, but these are the steps that I followed when I installed the software.
I chose the Administrator option to ensure all the components I needed were installed. The Oracle Universal Installer will guide you through the rest of the steps to install the client software. I also installed the networking software which didn’t start automatically after I installed the client software. You can start this wizard by navigating to Start | All Programs | Oracle – OraClient11g_home1 | Configuration and Migration Tools | Net Configuration Assistant.
When I opened the Oracle Net Configuration Assistant, I chose the Local Net Service Name configuration option.
To access an Oracle database across the network you use a net service name. The net service name is usually the global database name. On the Net Service Name Configuration, Service Name page, you need to enter this name. I selected the option to use TCP to communicate with the database over the network. On the Net Service Configuration, TCP/IP Protocol page, you need to enter the name of your Oracle server, and you can change the port number if you want, but I kept the default.
On the Net Service Name Configuration, Test page, you should perform a test. When I performed this test, I got an error. To get around the error, I had to change the login to the Replication Administrative Schema user I created. Then, the test succeeded.
For the rest of the wizard, I accepted the defaults, and then restarted the computer.
Windows Management and Scripting 