topic Re: Moving an Oracle DB with ArcSDE to a new server in Data Management Questions

Moving an Oracle DB with ArcSDE to a new server

AhmedHesham — Sun, 18 Aug 2013 08:18:49 GMT

I have a task of moving an oracle database with SDE (geodatabase) to a new server. I don't have experience in Oracle so I need detailed explanation if possible, so I need to know what is the best way to do this task.

The source server is Windows Server 2008 R2 The source database server is Oracle10g The destination server is Windows Server 2008 R2 the destination database server in Oracle11g

Thank you in advance,

Re: Moving an Oracle DB with ArcSDE to a new server

MarcoBoeringa — Sun, 18 Aug 2013 09:56:21 GMT

I have a task of moving an oracle database with SDE (geodatabase) to a new server. I don't have experience in Oracle so I need detailed explanation if possible, so I need to know what is the best way to do this task.

The source server is Windows Server 2008 R2 The source database server is Oracle10g The destination server is Windows Server 2008 R2 the destination database server in Oracle11g

Thank you in advance,

I am always slightly surprised to read these kind of questions. Yes, admittedly, I have taken on tasks for which I wasn't initially qualified too, nor had experience in, but I always started out reading a ton of (ESRI) documentation about the subjects and software I needed to take on, and familiarizing myself with the software I needed to use on (test) machines, before I started posting any specific question about issues I may have encountered.

You can't just start from scratch and hope for a "cookbook" to solve your issues...

Start reading the Online Help and ESRI's extensive (whitepaper) online documentation (e.g. here and here), get some course in Oracle or familiarize yourself with it.

Re: Moving an Oracle DB with ArcSDE to a new server

AhmedHesham — Mon, 19 Aug 2013 11:35:58 GMT

Thank you Marco for your reply.

I have posted this question after reading many pages and trying many ways that all lead me to failure, and I have very little time to finish this task.
I expected that any one will help me by giving the steps that should be done and then I'll research each step. I didn't expect to be blamed for sending a question here.

Thank you for not answering my question,

Re: Moving an Oracle DB with ArcSDE to a new server

AsrujitSengupta — Mon, 19 Aug 2013 14:20:47 GMT

Ahmed,

Below are some basic steps that you can consider (it may differ sightly based on environment and your need):

1. Create a complete backup of the database on current machine using datapump

2. Create a new Oracle database on a new machine

3. Copy the dump created in step 1 to the new machine.

4. Create all necessary user tablespaces.

5. Grant these two permissions to PUBLIC by logging as 'sys' user in SQLPLUS:
SQL> grant execute on dbms_lock to public;
SQL> grant execute on dbms_pipe to public;

6. Import the Oracle dump file into the new Oracle database.

7. Check if there are any invalid objects for SDE schema, then re-compile those invalid objects by logging as 'sys' user in SQLPLUS:

SQL> select object_name,object_type from dba_objects where status='INVALID' and owner='SDE';

If you find any 'INVALID' objects, recompile then using following command;

SQL> exec dbms_utility.compile_schema(schema=>'SDE');

8. Install the correct (if required) version of ArcSDE on new machine.

Hope this helps 🙂 !

Re: Moving an Oracle DB with ArcSDE to a new server

MarcoBoeringa — Mon, 19 Aug 2013 15:19:18 GMT

I have posted this question after reading many pages and trying many ways that all lead me to failure, and I have very little time to finish this task.
I expected that any one will help me by giving the steps that should be done and then I'll research each step. I didn't expect to be blamed for sending a question here.

Thank you for not answering my question,

Well, you're welcome 😉 (joke)... Sorry, but you provided very little background, not even what version of ArcSDE geodatabase / ArcGIS you are running and what procedures you tried, nor specific issues you encountered we might be able to answer.

Maybe these Help pages are of use, and if you specify what exact issues or error messages you encountered, some of us might be of further help:

A quick tour of setting up a geodatabase in Oracle

Methods for moving a geodatabase in Oracle

Moving a geodatabase using an XML document

And maybe this OTN Community thread with rather detailed instructions on duplicating a database is of some use:

TIP 04: Duplicating a Database in 10g by Joel Pèrez
https://forums.oracle.com/thread/247436

Re: Moving an Oracle DB with ArcSDE to a new server

MichaelVolz — Mon, 19 Aug 2013 16:14:27 GMT

Ahmed:

Do you currently have mxds that are using feature classes and tables from the existing SDE database? If so, will the replacement database have the same name so it can be referenced in the same way in the SDE connections that are saved in the mxd?

This was a task my organization had to go through when creating a replacement SDE database as it had a different name (I'm not sure how you can get around this unless you can turn off the old database and immediately turn on the new database - This is a risky procedure as you might have issues with the new database). As such, we also needed a script that would reroute the SDE connections to the new server, so both SDE databases could be available (This allowed us some time to trouble-shoot any connection issues before retiring the old server).

Re: Moving an Oracle DB with ArcSDE to a new server

AhmedHesham — Thu, 29 Aug 2013 06:01:32 GMT

Ahmed,

Below are some basic steps that you can consider (it may differ sightly based on environment and your need):

1. Create a complete backup of the database on current machine using datapump

2. Create a new Oracle database on a new machine

3. Copy the dump created in step 1 to the new machine.

4. Create all necessary user tablespaces.

5. Grant these two permissions to PUBLIC by logging as 'sys' user in SQLPLUS:
SQL> grant execute on dbms_lock to public;
SQL> grant execute on dbms_pipe to public;

6. Import the Oracle dump file into the new Oracle database.

7. Check if there are any invalid objects for SDE schema, then re-compile those invalid objects by logging as 'sys' user in SQLPLUS:

SQL> select object_name,object_type from dba_objects where status='INVALID' and owner='SDE';

If you find any 'INVALID' objects, recompile then using following command;

SQL> exec dbms_utility.compile_schema(schema=>'SDE');

8. Install the correct (if required) version of ArcSDE on new machine.

Hope this helps 🙂 !

ty Asrujit, that was very helpful

Re: Moving an Oracle DB with ArcSDE to a new server

AhmedHesham — Thu, 29 Aug 2013 06:08:28 GMT

Well, you're welcome 😉 (joke)... Sorry, but you provided very little background, not even what version of ArcSDE geodatabase / ArcGIS you are running and what procedures you tried, nor specific issues you encountered we might be able to answer.

Maybe these Help pages are of use, and if you specify what exact issues or error messages you encountered, some of us might be of further help:

A quick tour of setting up a geodatabase in Oracle

Methods for moving a geodatabase in Oracle

Moving a geodatabase using an XML document

And maybe this OTN Community thread with rather detailed instructions on duplicating a database is of some use:

TIP 04: Duplicating a Database in 10g by Joel Pèrez
https://forums.oracle.com/thread/247436

ty Marco for ur help

Ahmed:

Do you currently have mxds that are using feature classes and tables from the existing SDE database? If so, will the replacement database have the same name so it can be referenced in the same way in the SDE connections that are saved in the mxd?

This was a task my organization had to go through when creating a replacement SDE database as it had a different name (I'm not sure how you can get around this unless you can turn off the old database and immediately turn on the new database - This is a risky procedure as you might have issues with the new database). As such, we also needed a script that would reroute the SDE connections to the new server, so both SDE databases could be available (This allowed us some time to trouble-shoot any connection issues before retiring the old server).

ty Michael, yes I have mxds but we changed the database name. So we r working on updating all the mxds with the new data source and also changing the connection string of our applications working on the database