topic Re: How to take backup of the SDE database and steps to export and import the databas in Data Management Questions

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

NCSCMNCSCM — Thu, 01 May 2014 04:25:04 GMT

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

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

WilliamCraft — Thu, 01 May 2014 10:03:42 GMT

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.

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

NCSCMNCSCM — Thu, 01 May 2014 11:14:21 GMT

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

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

WilliamCraft — Sat, 11 Dec 2021 07:01:02 GMT

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;

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

NCSCMNCSCM — Fri, 02 May 2014 04:06:00 GMT

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

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

WilliamCraft — Fri, 02 May 2014 08:57:22 GMT

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?

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

NCSCMNCSCM — Fri, 02 May 2014 11:29:03 GMT

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

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

NCSCMNCSCM — Fri, 02 May 2014 11:39:19 GMT

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

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

WilliamCraft — Fri, 02 May 2014 12:11:22 GMT

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?

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

NCSCMNCSCM — Fri, 02 May 2014 12:14:14 GMT

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

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

NCSCMNCSCM — Fri, 02 May 2014 12:16:50 GMT

ArcSDE 10.1 for Oracle 11g

Oracle database in both machine are same version too

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

WilliamCraft — Fri, 02 May 2014 12:20:58 GMT

So here is what I have gathered so far. Can you please tell me if this is correct?

Production Environment
RDBMS: Oracle 11.2.0.1
ArcGIS for Desktop: 10.2.1
Geodatabase Version: 10.1

Test Environment
RDBMS: Oracle 11.2.0.1
ArcGIS for Desktop: 10.2.1
Geodatabase Version: 10.1 (it would have to be the same since you're importing from production)

From production, you export SDE schema into one or more Oracle dump files using EXP. Then, you import the dump file(s) into your test database using IMP and no errors occur. Is this right?

Please note that your Geodatabase version is not necessarily the same thing as your version of ArcSDE. For example, you could have 10.2.1 ArcSDE command line tools but still have a 10.1 SP1 geodatabase. I just to make sure that when you said "10.1" you are referring to your Geodatabase version and not ArcSDE tools.

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

NCSCMNCSCM — Mon, 26 May 2014 12:17:51 GMT

Hi Crafty,

Finally we brought our data's from rman backup, thanks for your help!!!

Regards
Muruganandam R

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

AlexandrePaiva1 — Wed, 21 Jun 2017 20:59:44 GMT

So, using the RMAN fix the problem?