There are three methods for restoring and backing up SDE databases (conclusion)

Source: Internet
Author: User
Tags metabase
There are three methods for restoring and backing up the SDE database.

1. Overall backup, recovery, or migration of the Oracle9i + SDE spatial database using the IMP and exp commands (method 1)


Suppose: now we need to migrate the space database on the orisde server to the dessde server. When the SDE is created, the default users on both servers are SDE and the password is SDE. There is another SDE data user rasterdata on the orisde server. The password is rasterdata.
1. Back up all data under the SDE user (the user created by default when SDE is created): for example
Exp SDE/SDE @ orisde file = C:/SDE. dmp
2. The same method is used to back up SDE data for other operations. For example:
Exprasterdata/rasterdata @ orisde file = C: rasterdata. dmp
3. Stop the service on the dessde Server:
A) Method 1: Open CMD and enter the following language name: sdemon-O shutdown-s dessde (Service name)-p sde (SDE user password), for example:

Note: the second line of the statement indicates that the SDE service has been successfully stopped.
B) Method 2: Stop the ArcSDE service on the control panel.
Note: Make sure that dessde is disabled! To perform the following operations!
4. Open the Oracle console on dessde and log on to dessde as a DBA. Then, delete all objects under solution SDE, including: table, index, view, synonym, sequence, cluster, source type, user type. As shown in:

In general, after you delete tables, indexes, and views, the following five items are no longer available. However, you must be sure to delete all the content under these eight items.
5. Create a rasterdata user on the dessde shortcle server and grant the user the same tablespace, role, and system permissions on the orisde server.
6. Import SDE. dmp to the orisde database, for example:
Imp sde/SDE fromuser = SDE file = C:/SDE. dmp touser = SDE
7. rasterdata. dmp
IMP rasterdata/rasterdata fromuser = rasterdata file = C:/rasterdata. dmp touser = rasterdata
8. Restart the SDE service on the dessde server.
A) Method 1: Run CMD and enter the following language name: sdemon-o start-s dessde-P SDE

Note: The second sentence that appears indicates that the startup is successful!
B) Method 2: Find the ArcSDE service from the control panel/service and start the service.
9. Open arccatalog, establish a connection with dessde, and check whether data can be viewed. If yes, it indicates that the SDE database has been successfully migrated.

Here we will focus on the recovery of Sde users and their data (Steps 3, 4, and 5 ). Because the SDE user stores the correspondence between all the tables of all SDE data users, whether the recovery is successful depends on whether the SDE database migration is successful. For other backup and recovery operations, refer to Oracle backup and recovery operations.
Do not delete the SDE user and tablespace on the new server. When creating the SDE service, there will be a wise_err.log file for the user to view the creation results. From the above we can see that more than 30 tables have been created, you have also created a Geodatabase log (creating Geodatabase schema). If you are not sure about the tables and logs, it is best not to delete SDE users and tablespaces, otherwise, the data recovery under the SDE user will be difficult to succeed (this may be because the table is not completely created), even if it succeeds, the SDE service is also difficult to start (this may be due to a log file problem ). If you do not believe it, try it. (If it fails, you can reinstall SDE )!
Of course, this can only be used for database recovery or migration between SDE versions of the same version (9.0 and 9.1 are regarded as the same version). database migration between SDE spatial databases of different versions will be discussed separately.
The following is a method provided by dreambird to batch delete all objects of a user in Oracle:
In fact, I am not a master of Oracle, or those experts will be angry. Haha, my qq125861109. Welcome to the discussion. In addition, I optimized the above Code because some tables cannot be deleted when they have a primary-foreign key relationship. I have tested it and it is absolutely usable. It can be run in Oracle's PLSQL developer.
Type name_list is table of varchar2 (40 );
Type type_list is table of varchar2 (20 );

Tab_name name_list: = name_list ();
Tab_type type_list: = type_list ();

SQL _str varchar2 (500 );
SQL _str: = 'select uo. object_name, UO. object_type from user_objects uo where uo. object_type not in (''index'', ''lob') order by uo. object_type DESC ';
Execute immediate SQL _str bulk collect into tab_name, tab_type;

For I in tab_name.first .. tab_name.last Loop
If tab_type (I) = 'table' then
SQL _str: = 'drop' | tab_type (I) | ''| tab_name (I) | 'cascade constraints ';
SQL _str: = 'drop' | tab_type (I) | ''| tab_name (I );
End if;
Execute immediate SQL _str;
End loop;
In the future, we will consider batch processing to solve the SDE backup problem.

Ii. Use the sdeexport and sdeimport commands to back up, migrate, or restore the SDE Database


This method involves four steps: Back up the metadata table, back up the data table, restore the metadata table, and restore the data table.

Before migrating or recovering the SDE database, you must first understand how SDE, as a space database engine, organizes space data, that is, the contents of the metadata table and the contents of the data table. The SDE user is the default user of the SDE database edge. All metadata tables are stored under this user. The role of metadata tables is to store the relationship between data tables, about 7 data tables can form a element class. The relationship between them depends on the meta table of Sde. Therefore, the backup of the SDE database is divided into two steps: Back up the metadata table and back up the data table.

This method uses the SDE command sdeimport and sdeexport to back up and import data tables, that is, the method can be used to migrate databases between different databases or restore a certain element class (for details about the sdeimport and sdeexport commands, see the help documentation provided by SDE ).
First, back up the metadata table

You can use the sdeexmport command to back up the metabases. However, this method uses the export tool provided by arccatalog to back up the metabases. Connect to the SDE database to be backed up, right-click the feature dataset to be backed up or a connection, and then click Export/XML workspace document, as shown in:

Note: Right-click the SDE database connection (medium sdeto Sy) and right-click the feature dataset (such as upmis. Planning History Database) to back up the metadata table. Right-click on the database connection (of course, the user who connects to the database must have the permission to operate all the data, such as the SDE user), and back up all the metadata table information, right-click the feature dataset and back up the metadata table corresponding to the feature dataset.
Select to back up only the metadata table (schema only). If you select to back up the metadata (such as the data source in the SDE), the metadata will also be backed up. As shown in:

Second, back up the data table
The command used to back up the data table is sdeexport. Run the command in cmd. If I want to back up the JMD element class under upmissde, the command is as follows:
Sdeexport-t jmd-f d:/JMD-u upmissde-p pwd-v sde. Default
-T: Table Name (element class name),-F (storage path),-U (user name),-P (password),-V (version name ).
Of course, if the backup is not performed on the SDE host, you need to add the-I (port) and-s (host name) parameters.
Back up all the elements to be backed up.
Again, restore the table on the target database.
You can also use the sdeimport command. In this method, the arccatalog import tool is also used to restore the meta table.
Use a user name (such as SDE) to establish a connection to the SDE database. Double-click the connection and right-click the database connection. Select import/XML workspace document.
It should be noted that, when users log on, the restored meta table records the user's data table relationship. For example, during the backup, I used the DLG feature dataset backed up by upmissde. When I restored the meta table, I logged on as an SDE user. After the restoration, an SDE will appear under the SDE user. DLG (originally upmissde. DLG), which is the uniqueness of this method. data migration can be performed between different users and SDE versions and different databases (Oracle and sqlserver.
Finally, restore the data table
We also use the sdeimport command for restoration. If I want to restore the JMD just backed up to the SDE user, the command is as follows:
Sdeimport-O append-t jmd-f d:/JMD-u upmissde-PWD-v sde. Default
Of course, if the backup is not performed on the SDE host, you need to add the-I (port) and-s (host name) parameters. Back up all the elements to be backed up.
The reason for selecting append for-O parameters is that when arccatalog is used to restore the metabase, the table related to the metabase is also created, therefore, you cannot select create as the parameter after-o.
If it is difficult to back up and recover multiple elements, you can write a batch processing program. Haha.

The disadvantage of this method is that it cannot process data of multiple versions. The experiment is as follows:
C:/> sdeexport-T pl-f d:/nsyncbin/PL-u vertest-P vertest-V version

ArcSDE 9.1 Oracle9i build 1269 Thu Mar 3 18:54:58 PST 2005
Sdex File Export Administration Utility
Exporting ArcSDE object to "D:/nsyncbin/PL" in sdex 9.0 export format...
Exporting table "PL ".
Spatial column "shape"
2 features converted.
2 features exported.

C:/> sdeimport-O append-T pl-v sde. Default-f d:/nsyncbin/PL-u vertest-P ver

ArcSDE 9.1 Oracle9i build 1269 Thu Mar 3 18:54:58 PST 2005
Sdex File Import Administration Utility
Importing sdex from D:/nsyncbin/PL...
Importing spatial column "shape"
Rowid colume "objectid" exists in export file's attribute. Drop
2 records read.
2 records stored.

C:/> sdeimport-O append-T pl-V version-f d:/nsyncbin/PL-u vertest-P vertest

ArcSDE 9.1 Oracle9i build 1269 Thu Mar 3 18:54:58 PST 2005
Sdex File Import Administration Utility
Importing sdex from D:/nsyncbin/PL...
Importing spatial column "shape"
Rowid colume "objectid" exists in export file's attribute. Drop
2 records read.
2 records stored.


Before the import, under the vertest user, SDE. in the default version, there is an element in the PL layer. The OID is 1. As vertest, a new version is created, and a new element is created in the version. The OID is 2. In this case, only one OID = 1 element exists in the default version, and the oId = 1 and 2 elements exist in the version.
Then use the sdeexport command to export (as shown above)

Next, use sdeimport to import data to the default version of The vertest user. When you view data in the default version, there are three elements: oid = 1, 3, and 4.
Then, import the data to the vertest user's version. In this case, the version has four elements: oid = 1, 2, and 6.
After the data in the version is submitted to the default version, the default has six elements: oid = 1, 2, 4, 5, and 6, and no conflict is reported during the conflict check. This indicates that when you use this command to back up and restore a multi-version SDE database, there are also limitations.

The third backup method will be available soon. Stay tuned!



Iii. Recovery of Sde databases of the same version



Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.