Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Getting Oracle backup into SQL Server Expand / Collapse
Author
Message
Posted Thursday, May 21, 2009 11:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 430, Visits: 978
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



Post #721519
Posted Thursday, May 21, 2009 11:35 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:39 PM
Points: 1,800, Visits: 1,546
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)
Post #721535
Posted Thursday, May 21, 2009 1:05 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 430, Visits: 978
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...



Post #721621
Posted Thursday, May 21, 2009 1:16 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:39 PM
Points: 1,800, Visits: 1,546
Do a search on ur computer by filename tnsnames.ora, u will find "SERVICE_NAME" in there
Post #721630
Posted Monday, July 13, 2009 7:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:55 AM
Points: 2,707, Visits: 2,316
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.
Post #752002
Posted Wednesday, August 5, 2009 11:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 430, Visits: 978
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



Post #765687
Posted Thursday, August 6, 2009 10:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:53 AM
Points: 417, Visits: 1,155
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.
Post #766362
Posted Thursday, August 6, 2009 12:16 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 430, Visits: 978
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



Post #766449
Posted Thursday, August 6, 2009 12:33 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:53 AM
Points: 417, Visits: 1,155
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
Post #766465
Posted Thursday, August 6, 2009 12:40 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 430, Visits: 978
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.



Post #766475
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse