SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting Oracle backup into SQL Server


Getting Oracle backup into SQL Server

Author
Message
schleep
schleep
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1014 Visits: 1326
Hi all,

I've been handed a backup of an Oracle DB.
We need to get that data into a SQL Server DB.

I haven't the faintest idea how. I've installed Oracle 9i personal on a spare server.

I don't know the Oracle lingo for creating a DB, restoring a backup, etc.
It keeps asking me for a Service Name?

Can anyone point me in the right direction?

Thanks,

Paul



Mayank Khatri
Mayank Khatri
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2080 Visits: 1716
1)You can get the service name from your .ORA file (Where ever ur Oracle installation folder is) or if click on ur if u click on databases on ur enterprisemanager console, on right side of your grey console window it will give u serice name under TNS Descriptor or Service Name:

2)Assuming u have ur Oracle backup file(.dmp), u will have to restore it using "imp" utility. U can look up that in google.
From ur DOS Prompt,
A)C:\SET ORACLE_SID=
B)C:\SET ORACLE_HOME=c:\Oracle\Ora92
C)imp File=E:\UrBackupFilename.dmp Indexes=y Log=E:\Logfilename.log userid=UserName Pwd=UrPwd FROMUSER=UrSchemaName TouserUrcurrentSchemaName ignore=y rows=n Statistics=Recalculate

Before U run Step (C), U have to create ur TableSpace and user in Oracle


3)After Step 2 is successfully done, where ever ur Sql is installed, u have to Install Oracle client tools and then use DTS import export utility to import tables and their rows into Sql server.

I just have summed up whole process, it will be lot more complicated when u actually do it. But u can use it to research more about each n every step in detail(to get proper syntax etc etc)
schleep
schleep
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1014 Visits: 1326
Thanks for the response.

These services are running under local system accounts.
OracleOraHome92Agent
OracleOraHome92TNSListener

That's a start, I guess.

I can't find the strings "service name", or "service name" in any .ORA file on the box on which I've installed Oracle.

I'm afraid I just don't know what the wizards are asking for.

I'll keep plugging away...



Mayank Khatri
Mayank Khatri
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2080 Visits: 1716
Do a search on ur computer by filename tnsnames.ora, u will find "SERVICE_NAME" in there
DNA_DBA
DNA_DBA
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3150 Visits: 2768
If those are the only oracle services you have then it sounds like you haven't actually got a database set up. There should be a tool installed that will guide you through creating a database (DataBase Configuration Assistant?). Once you have done this you can use the database name you created as the oracle_sid and do the import.
schleep
schleep
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1014 Visits: 1326
Alright, getting somewhere now.

I have the Oracle Management Server up and running, can connect using Oracle EM.

However, when I try to click on my database, I get a login screen. I have used the NT Administration Assistant to grant my NT account, which has Local Admin on the NT Server upon which Oracle is installed, OS DB Admin and OS DB Operator membership, at the server and Oracle DB level. Also made myself External OS user, and granted myself Local Roles in my DB.

So, what do I enter in the DB login screen in Oracle EM?

TIA,

P



rlondon
rlondon
SSChasing Mays
SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)

Group: General Forum Members
Points: 651 Visits: 1160
Do you actually have a database set up on your system? If not, use the Database Configuration Assistant to create a blank database then use the import command to import the .dmp file into the database. The database configuration assistant will ask you for a password during setup. Then you will be able to log in through EM.
schleep
schleep
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1014 Visits: 1326
Thanks for that.

Now, I have access to a DB I created, Test.
I have placed it in Mounted mode, which should enable recovery.

I right-click on the Test database, Backup Management, Recovery. The wizard launches, informs me that I can only recover the entire DB (good). The next screen (Configuration) asks for a predefined configuration, and the only possible option is Test, which of course does not point at the file/folder I'm trying to restore.

P



rlondon
rlondon
SSChasing Mays
SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)

Group: General Forum Members
Points: 651 Visits: 1160
Assuming your database backup is a *.dmp file, I would use the import utility at a command prompt instead of going through EM to restore the database. You can find out more on this utility at the following site: http://www.orafaq.com/wiki/Import/_Export_FAQ
schleep
schleep
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1014 Visits: 1326
Nice!!! The people who did the backup cleverly named the file h26b_full.Mar04 -- no doubt so we could tell it was done on March 4 -- which we already knew from the date on the file...

It's 20 GB. I don't wanna make it unusable. Do you think there's any harm in trying to rename it to h26b_full.dmp? Or should I make a copy of the file before I try that sort of thing? I have a hard copy, but the decompression took most of a day.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search