How to take backup of the SDE database and steps to export and import the database

6564
13
04-30-2014 09:25 PM
NCSCMNCSCM
New Contributor III
Hi,
In our organization, system configuration for ArcSDE 10.2.1:

Database: Oracle 11g versions:11.2.0.1
Operating System: Windows Server 2008 R2 SP1
ArcGIS for Desktop: 10.2.1

We were taking sde back-up using export command of oracle database "exp system/password file=filelocation.dmp full=y".
After this, while we were trying to import the dmp file to the test machine (with same configuration as mentioned above), the following error occurs: "Error executing stored procedure sde.pinfo_util.get_sde_idORA-06508:PL/SQL: could not find program unit being called" (kindly see the attached error message)

Before importing we were creating the user called sde with default tablespace SDE and temp tablespace, after creation of SDE user, we were using import command "imp system/password file=location fromuser=sde touser=sde"

Kindly suggest whether we were using correct syntax or else let us know the correct procedures/steps to export and import the sde geodatabase from one machine to another.

Thanks in advance

Muruganandam R
NCSCM
0 Kudos
13 Replies
WilliamCraft
MVP Regular Contributor
We use IMP and EXP often to refresh the SDE schema and other user schemas for our 11g test databases.  I think you have the syntax right, and it looks like you were able to export and import fine.  The error looks to occur in ArcGIS Desktop.  I would first suggest that you try recompiling any invalid objects then try again to connect.  Run this in SQLplus as SYS user:

EXEC DBMS_UTILITY.compile_schema(schema => 'SDE');

Post back with your results.
0 Kudos
NCSCMNCSCM
New Contributor III
We use IMP and EXP often to refresh the SDE schema and other user schemas for our 11g test databases.  I think you have the syntax right, and it looks like you were able to export and import fine.  The error looks to occur in ArcGIS Desktop.  I would first suggest that you try recompiling any invalid objects then try again to connect.  Run this in SQLplus as SYS user:

EXEC DBMS_UTILITY.compile_schema(schema => 'SDE');

Post back with your results.


Hi,

Thanks for your reply.still we are facing same problem even after executing the above said command.

Regards,
Muruganandam
Ncscm
0 Kudos
WilliamCraft
MVP Regular Contributor
Have you granted permissions to the SDE user and to PUBLIC on the database into which you imported?
grant CREATE SESSION to sde;
grant CREATE VIEW to sde;
grant CREATE ANY INDEX to sde;
grant CREATE TABLE to sde;
grant CREATE TRIGGER to sde;
grant CREATE PROCEDURE to sde;
grant CREATE SEQUENCE to sde;
grant CREATE INDEXTYPE to sde;
grant CREATE LIBRARY to sde;
grant CREATE PUBLIC SYNONYM to sde;
grant CREATE OPERATOR to sde;
grant DROP PUBLIC SYNONYM to sde;
grant ADMINISTER DATABASE TRIGGER to sde;
GRANT EXECUTE ON dbms_pipe TO public;
GRANT EXECUTE ON dbms_lock TO public;
GRANT EXECUTE ON dbms_lob TO public;
GRANT EXECUTE ON dbms_utility TO public;
GRANT EXECUTE ON dbms_sql TO public;
GRANT EXECUTE ON utl_raw TO public;


After that, you may need to recompile these:
alter package sde.pinfo_util compile;
alter package sde.lock_util compile;
alter package sde.version_user_ddl compile;
0 Kudos
NCSCMNCSCM
New Contributor III
Hi Crafty,

Thanks for your reply, we have granted all the necessary permission as per your code, but when we are trying to compile the SDE.pinfo_util, its through error message "Object Pinfo_util does not exist"

For your reference, we have attached the error message.

Note: we just grant the permission, we didnt import the dump file yet, kindly just us should we want import the dmp file and then need to compile?.

Regards,
Muruganandam
0 Kudos
WilliamCraft
MVP Regular Contributor
Yes, you need to import the packages along with the data from the dump file before you can compile them.  The privileges above for the SDE user are system privileges and can be granted before the dump file is imported, with the exception of the GRANT EXECUTE lines which need to be run after the import completes but before the compiles are done.  I looked at your export command that you provided earlier and it seemed like you exported the entire SDE schema; but can you confirm that is the case?
0 Kudos
NCSCMNCSCM
New Contributor III
Yes, you need to import the packages along with the data from the dump file before you can compile them.  The privileges above for the SDE user are system privileges and can be granted before the dump file is imported, with the exception of the GRANT EXECUTE lines which need to be run after the import completes but before the compiles are done.  I looked at your export command that you provided earlier and it seemed like you exported the entire SDE schema; but can you confirm that is the case?


Yes, we were exporting the entire SDE Schema
0 Kudos
NCSCMNCSCM
New Contributor III
Yes, we were exporting the entire SDE Schema


Hi Crafty,

The actual problem we are facing is,
In our organization, we need to take regular backup of the sde database from oracle server for disaster recovery.
On test basis, I am trying to import the sde backup into the another machine, after importing I am unable to connect sde user in ArcCatalog and I am getting the earlier said error message.
So, now I don't know whether I am following the right procedure or not, so, me the correct steps or command to export and import the sde datafiles.

Regards
Muruganandam
0 Kudos
WilliamCraft
MVP Regular Contributor
The process you described is something we follow as well, and it works fine for us.  The error you showed suggests the absence or invalidity of a particular package which is part of the SDE schema.  I am not sure why that is happening.  However, one thing to note is that Oracle 11g R2 (11.2.0.1) is no longer supported beginning at ArcGIS 10.2.1.  The 10.2 release of ArcGIS was the last supported release with your version of Oracle database.  What is the ArcGIS version of your geodatabase?  From what you posted initially, it looks like it could also be 10.2.1 in addition to your desktop software.  Are both Oracle databases at the same Oracle release?
0 Kudos
NCSCMNCSCM
New Contributor III
The process you described is something we follow as well, and it works fine for us.  The error you showed suggests the absence or invalidity of a particular package which is part of the SDE schema.  I am not sure why that is happening.  However, one thing to note is that Oracle 11g R2 (11.2.0.1) is no longer supported beginning at ArcGIS 10.2.1.  The 10.2 release of ArcGIS was the last supported release with your version of Oracle database.  What is the ArcGIS version of your geodatabase?  From what you posted initially, it looks like it could also be 10.2.1 in addition to your desktop software.  Are both Oracle databases at the same Oracle release?


ArcSDE 10.1 for Oracle 11g
0 Kudos