Exporting Oracle 10g schema and importing to 11g

[hgate73]

I've been tasked to take a database system currently in use and set it up to run at other locations. The problem is that I have barely *any* database experience! Most of my knowledge is in routing and server administration.

The database is a proprietary application, and it is the only copy in existence. It's running on a DL380 server and performs automated backups every hour. It was designed by a professional DBA, but he's no longer here, and I'm the "it" guy now. I've been working on getting the front-end (Access) working for about a week now, reading a lot of stuff on Google.

How can I recreate the database at other locations? Is there some way to export the layout ("schema"??) and import it elsewhere? I need to completely duplicate the database structure, dependencies, user names, etc. I do have full access (sysdba).

Caveat: the old version is 10.2 or something, and the new version is 11.1.0g.

 

[Thargy]

The DBCA tool has the ability to save a db as a pair of files.

You could save the 10 db in this way, and then using the 11 DBCA use them to make a new db.

For a novice, this is probably the easiest way to achieve what you want.

You will need to read the documentation about the DBCA, which is why I have deliberately not gone into details.

Regards

T

 

[hgate73]

Okay, I grabbed a full database dump (dump_all.dmp) from the production system and then imported it (imp) into the new system. There were a couple table warnings but nothing serious.

However, I can't connect using the Access front-end. I get an error, "ora-12154: tns: could not resolve the connect identifier specified."

I've taken the following steps:
1. Both test computers are directly connected to each other through a router, using the original IP addressing scheme and system names.
2. tnsnames.ora correctly points to the Oracle server.
3. I checked the registry to ensure the TNS_ADMIN value was set to the c:\program files\[name of program]\instantclient_10_2\

What am I missing?

 

[Thargy]

Can you shut down the old db to make sure it's really incapable of responding, and then TNSPING the new db? What happens?

Regards

T

 

[hgate73]

It turns out it was a problem with the versions of Oracle. The Access front-end comes bundled with the 10.2 ODBC instantclient, but the database is 11g, and the two aren't talking. Rolling the database back to 10g solved the problem.

 

[Thargy]

I'd suggest then that you install the 11g instant client on a machine, load the access database separately and try again. Incompatibility with Access can be dealt with, and isn't really a good reason to abandon an upgrade.

Regards

T

 

[hgate73]

Well, I have absolutely zero experience with Oracle and Access, and I work for the Army overseas. They've tasked me to "get this thing running" for other locations, and since I don't know how to roll new ODBC drivers OR to successfully convert the version 10db to a version 11db without errors (importing 10 to 11 gives me a lot of errors), I decided to drop 11 for now (the Army has licenses for both) and go with what's "tried and true.

 

[Thargy]

As the man on the front line, I defer to your immediate needs.

Since it's easy to be an armchair warrior and our guys are currently getting killed in Afghanistan, do whatever you have to do for your guys (presumably the US army) to keep folks safe, equipped to do their job, and above all, uninjured and alive!

Sitting in a warm centrally-heated house in Blightey is no place to be making erudite but impractical suggestions for the military. Naturally I will do anything I can do to help.

Regards

T

 

[hgate73]

Thanks theargslayer, I appreciate it

These servers are destined for various locations throughout Afghanistan and Iraq, and since I'll be setting them up I need them to "just work."