Upgrade a geodatabase in Oracle

Available with Standard or Advanced license.

The goal of upgrading an enterprise geodatabase is to update the geodatabase system tables, stored procedures, types, and functions to take advantage of new functionality and bug fixes.

Install a new version of the ArcGIS client or apply a service pack, patch, or hot fix to an existing installation and upgrade the geodatabase.

Before you upgrade

When you upgrade any enterprise system, including a geodatabase, plan ahead. Test the new version on a development or test server to ensure that it works with all your client applications.

When you have determined that the new system works the way you expected, schedule the upgrade; be sure the necessary staff are available to perform the upgrade and that they have the permissions necessary to complete their assigned tasks.

Be aware of the following:

• The ArcSDE application server for Oracle is no longer available beginning with ArcGIS 10.3. You must migrate all application server connections used in maps or services to use direct connections before you can upgrade your geodatabase to 10.3.
• You must upgrade the master sde geodatabase before you can upgrade any user-schema geodatabases.
• Upgrades from beta versions of the software are not supported.
• You can upgrade directly from a 9.3.1, 10, 10.1, 10.2, 10.2.1, or 10.2.2 geodatabase to a 10.3 geodatabase if your database is at a supported release.
• If your geodatabase is at release 9.3 or earlier, you must first upgrade to a supported geodatabase release and then upgrade to 10.3.
• Once a geodatabase has been upgraded, previous versions of ArcGIS may not be able to connect since some older versions of ArcGIS cannot read newer versions of the geodatabase. Releases prior to ArcGIS 10 will not be able to connect to a 10.3 geodatabase. ArcGIS 10 clients will be able to connect to the 10.3 geodatabase.
• There is no formal mechanism to downgrade a geodatabase to a previous version. If after upgrading to a newer version you want to move back to an older version of the geodatabase, you must restore the old database from backup.

The following is a checklist of steps to complete before you upgrade your geodatabase:

1. Be sure the version of Oracle you want to use is supported with the ArcGIS 10.3. See Oracle database requirements for ArcGISfor information.
2. Check to see if your geodatabase can be upgraded. To do this, install the ArcGIS client version you want to move to onto one machine. If you install ArcGIS for Desktop, you can connect to the geodatabase and open the Database Properties dialog box to see if a geodatabase upgrade is possible. If you install ArcGIS for Server or ArcGIS Engine with the Geodatabase Update extension, you can use the ArcPy Describe function to determine if the geodatabase can be upgraded.

   # Open Python.
   cd /arcgis/server/tools
   ./python
   
   # Create a connection to the geodatabase.
   arcpy.CreateDatabaseConnection_management("/tmp/",
                                             "egdb_connection.sde",
                                             "ORACLE",
                                             "myogdb",
                                             "DATABASE_AUTH",
                                             "sde",
                                             "mysdepassword",
                                             "SAVE_USERNAME")
   
   # Import ArcPy and check the geodatabase release.
   import arcpy
   
   isCurrent = arcpy.Describe('/tmp/egdb_connection.sde').currentRelease
   
   print isCurrent
   

   If false is returned, you can upgrade your geodatabase. If true is returned, you do not need to upgrade your geodatabase. Do not proceed with the subsequent steps.

3. Make sure that the Oracle Text component is installed.

   The Text component is installed by default in Oracle; however, if you did not do a default installation, the Text component may not have been installed.

   To see if it is installed, execute the following SQL statement while logged in as SYSTEM or a user with DBA privileges in the database:

   SELECT owner, object_name
   FROM all_objects
   WHERE object_type = 'PACKAGE'
     AND object_name = 'CTX_DDL';
   

   If no record is returned, the Text component is not installed. Run the Oracle installation to install the Text component.

4. Create a backup of the database.
5. Remove any custom functionality you may have added to the geodatabase system tables outside ArcGIS such as triggers or additional indexes.

   The upgrade procedure cannot take into account customizations you make to the system tables. If such customizations prevent the alteration of a system table's schema, the upgrade will fail.

6. Grant the geodatabase administrator the permissions necessary to upgrade a geodatabase.

   See Privileges for geodatabases in Oracle for a list of required privileges.

7. If you access data directly from SQL, replace the existing st_shapelib on your Oracle server with the new version of the library. The new st_shapelib file can be found in the DatabaseSupport files directory in your ArcGIS client installation location.

   Be sure to copy the library appropriate to your Oracle server operating system.

   If you place the library in a different directory on the Oracle server than it was previously, you must reconfigure your extproc to point to the new library location and restart the Oracle listener. See Configure the Oracle extproc to access the geodatabase with SQL for more information.

8. If you are using the ST_Raster storage type in your geodatabase, replace the existing libst_raster_ora file on your Oracle server with the new version of the library. The new libst_raster_ora file can be found in the DatabaseSupport files directory in your ArcGIS client installation location.

   Be sure to copy the library appropriate to your Oracle server operating system.

9. Make sure that there are no users connected to the geodatabase you are upgrading. If you are upgrading the sde master geodatabase, also make sure no one is connected to any of the user-schema geodatabases in the Oracle database.

   You can see a list of the users who are currently connected to your geodatabase by opening the Connected Users tab of the Geodatabase Administration dialog box in ArcGIS for Desktop.

You can now upgrade your geodatabase.

Upgrade the geodatabase

You can use the Upgrade Geodatabase tool in ArcGIS for Desktop or a Python script run on the ArcGIS client computer to upgrade your geodatabase.

Note:

You must use current connection syntax when you connect to the geodatabase to upgrade it. The upgrade process will fail if you use the older connection syntax. See Connect to Oracle from ArcGIS for information on connection syntax.

Use the Upgrade Geodatabase tool

You can open the Upgrade Geodatabase tool from the Database Properties dialog box by clicking the Upgrade Geodatabase button on the General tab, or open the tool directly. If you open the tool from Database Properties, the Input geodatabase text box is prepopulated with the geodatabase connection information.

Esri recommends that you leave both the Pre-requisites check and Upgrade geodatabase options checked. That way, the tool checks to see if the prerequisites to upgrade have been met before continuing with the geodatabase upgrade.

The prerequisite check detects other active connections to the geodatabase, determines whether the connecting user has sufficient privileges to upgrade the geodatabase, ensures that the database can support XML columns, makes sure all datasets can be opened, and ascertains that the database and libraries are at the same release. If any prerequisites are not met, the tool terminates. You must correct any problems before you run the upgrade procedure again.

The results of this check are reported on the geoprocessing tool dialog box. If the check (or upgrade) fails, results are also written to the GDBUpgrade.log file, which can be found in c:\Users\<user name>\AppData\Local\ESRI\<ArcGIS product>.

If all checks are passed, the tool proceeds with the upgrade. The status for the prerequisites check and the upgrade are shown on the geoprocessing tool progress dialog box. If the upgrade fails, information is written to the GDBUpgrade.log file. Additional information is written to the sdesetup.log file, which can be found in the system TEMP directory.

Run a script

To upgrade the geodatabase, copy one of these example scripts into a text editor. Alter any variable values to match the information at your site.

• If you have an existing database connection file that uses a direct connection and connects as the sde user, copy the script that applies to your operating system, paste it into a text editor, alter it to use information specific to your site, save and close the file, then run it:

  # Name: upgradesdegdb_example.py
  # Description: Connect from a Windows computer 
  # with an existing database connection file 
  # and upgrade an enterprise geodatabase
  # Author: Esri
  
  # Import arcpy module
  import arcpy
   
  # Local variables:
  Output_Workspace = "C:\\ArcGIS\connection_files\<Connection file>"
  Default_gdb = "C:\\ArcGIS\connection_files\<Connection file>"
  
  # Process: Upgrade Geodatabase
  arcpy.UpgradeGDB_management(Default_gdb, "PREREQUISITE_CHECK", "UPGRADE")
  

  # Name: upgradesdegdb_example.py
  # Description: Connect from a Linux or UNIX computer 
  # with an existing database connection file 
  # and upgrade an enterprise geodatabase
  # Author: Esri
  
  # Import arcpy module
  import arcpy
   
  # Local variables:
  Output_Workspace = "<user>/connections/<Connection_file>"
  Default_gdb = "<user>/connections/<Connection_file>"
  
  # Process: Upgrade Geodatabase
  arcpy.UpgradeGDB_management(Default_gdb, "PREREQUISITE_CHECK", "UPGRADE")
  

• If you do not have a database connection file on the computer from which you are scripting the upgrade, you can copy the following script and provide your site-specific information at the command line:

  """
  Name: upgrade_gdb.py
  Description: Provide connection information to an Enterprise geodatabase 
  and upgrade the geodatabase
  Type upgrade_gdb.py -h or upgrade_gdb.py --help for usage
  Author: Esri
  """
  
  # Import system modules
  import arcpy, os, optparse, sys
  
  
  # Define usage and version
  parser = optparse.OptionParser(usage = "usage: %prog [Options]", version="%prog 1.0 for " + arcpy.GetInstallInfo()['Version'] )
  
  #Define help and options
  parser.add_option ("--DBMS", dest="Database_type", type="choice", choices=['SQLSERVER', 'ORACLE', 'POSTGRESQL', 'DB2','INFORMIX','DB2ZOS',''], default="", help="Type of enterprise DBMS:  SQLSERVER, ORACLE, or POSTGRESQL.")
  parser.add_option ("-i", dest="Instance", type="string", default="", help="DBMS instance name")
  parser.add_option ("--auth", dest="account_authentication", type ="choice", choices=['DATABASE_AUTH', 'OPERATING_SYSTEM_AUTH'], default='DATABASE_AUTH', help="Authentication type options (case-sensitive):  DATABASE_AUTH, OPERATING_SYSTEM_AUTH.  Default=DATABASE_AUTH")
  parser.add_option ("-u", dest="User", type="string", default="", help="Geodatabase administrator user name")
  parser.add_option ("-p", dest="Password", type="string", default="", help="Geodatabase administrator password")
  parser.add_option ("--upgrade", dest="Upgrade", type="choice", choices=['TRUE', 'FALSE'], default="FALSE", help="Upgrade Options (case-sensitive):  TRUE=Perform Pre-requisite check and upgrade geodatabase, FALSE=Perform Pre-requisite check only.  Default=FALSE")                   
  parser.add_option ("-D", dest="Database", type="string", default="none", help="Database name:  Not required for Oracle")
  
  
  # Check if value entered for option
  try:
  	(options, args) = parser.parse_args()
  
  	
  #Check if no system arguments (options) entered
  	if len(sys.argv) == 1:
  		print "%s: error: %s\n" % (sys.argv[0], "No command options given")
  		parser.print_help()
  		sys.exit(3)
  
  	#Usage parameters for spatial database connection to upgrade
  	account_authentication = options.account_authentication.upper()
  	username = options.User.lower() 
  	password = options.Password	
  	do_upgrade = options.Upgrade
  	database = options.Database.lower()
  	database_type = options.Database_type.upper()
  	instance = options.Instance
  	
  	if (database_type == ""):
  		print "\nDatabase type must be specified!\n"
  		parser.print_help()
  		sys.exit(3)
  	
  	if (database_type == "SQLSERVER"):
  		database_type = "SQL_SERVER"
  	
  	# Get the current product license
  	product_license=arcpy.ProductInfo()
  	
  	# Checks required license level to upgrade
  	if product_license.upper() == "ARCVIEW" or product_license.upper() == 'ENGINE':
  		print "\n" + product_license + " license found!" + "  Enterprise geodatabase upgrade requires an ArcGIS for Desktop Standard or Advanced, ArcGIS Engine with the Geodatabase Update extension, or ArcGIS for Server license."
  		sys.exit("Re-authorize ArcGIS before upgrading.")
  	else:
  		print "\n" + product_license + " license available!  Continuing to upgrade..."
  		arcpy.AddMessage("+++++++++")
  	
  	# Local variables
  	instance_temp = instance.replace("\\","_")
  	instance_temp = instance_temp.replace("/","_")
  	instance_temp = instance_temp.replace(":","_")
  	Conn_File_NameT = instance_temp + "_" + database + "_" + username     
  	
  	if os.environ.get("TEMP") == None:
  		temp = "c:\\temp"	
  	else:
  		temp = os.environ.get("TEMP")
  	
  	if os.environ.get("TMP") == None:
  		temp = "/usr/tmp"		
  	else:
  		temp = os.environ.get("TMP")  
  	
  	Connection_File_Name = Conn_File_NameT + ".sde"
  	Connection_File_Name_full_path = temp + os.sep + Conn_File_NameT + ".sde"
  	
  	# Check for the .sde file and delete it if present
  	arcpy.env.overwriteOutput=True
  	if os.path.exists(Connection_File_Name_full_path):
  		os.remove(Connection_File_Name_full_path)
  	
  	print "\nCreating Database Connection File...\n"	
  	# Process: Create Database Connection File...
  	# Usage:  out_file_location, out_file_name, DBMS_TYPE, instnace, database, account_authentication, username, password, save_username_password(must be true)
  	arcpy.CreateDatabaseConnection_management(out_folder_path=temp, out_name=Connection_File_Name, database_platform=database_type, instance=instance, database=database, account_authentication=account_authentication, username=username, password=password, save_user_pass="TRUE")
          for i in range(arcpy.GetMessageCount()):
  		if "000565" in arcpy.GetMessage(i):   #Check if database connection was successful
  			arcpy.AddReturnMessage(i)
  			arcpy.AddMessage("\n+++++++++")
  			arcpy.AddMessage("Exiting!!")
  			arcpy.AddMessage("+++++++++\n")
  			sys.exit(3)            
  		else:
  			arcpy.AddReturnMessage(i)
  			arcpy.AddMessage("+++++++++\n")
  			
  	# Check whether geodatabase needs upgrade
  	isCurrent = arcpy.Describe(Connection_File_Name_full_path).currentRelease
  	
  	if isCurrent == True:
  		print "The geodatabase is already at the current release and cannot be upgraded!"
  		sys.exit("Upgrade did not run.")
  	
  	
  	# Process: Upgrade geodatabase...
  	try:
  		if do_upgrade.lower() == "true":
  			print "Upgrading Geodatabase...\n"
  			arcpy.UpgradeGDB_management(input_workspace=Connection_File_Name_full_path, input_prerequisite_check="PREREQUISITE_CHECK", input_upgradegdb_check="UPGRADE")
  			for i in range(arcpy.GetMessageCount()):
  				arcpy.AddReturnMessage(i)
  			arcpy.AddMessage("+++++++++\n")
  	
  		else:
  			print "Running Pre-Requisite Check...\n"
  			arcpy.UpgradeGDB_management(input_workspace=Connection_File_Name_full_path, input_prerequisite_check="PREREQUISITE_CHECK", input_upgradegdb_check="NO_UPGRADE")
  			for i in range(arcpy.GetMessageCount()):
  				arcpy.AddReturnMessage(i)
  			arcpy.AddMessage("+++++++++\n")
  		
  	        
  	except:
  		for i in range(arcpy.GetMessageCount()):
  			arcpy.AddReturnMessage(i)
  		
  	if os.path.exists(Connection_File_Name_full_path):
  		os.remove(Connection_File_Name_full_path)
  	
  #Check if no value entered for option	
  except SystemExit as e:
  	if e.code == 2:
  		parser.usage = ""
  		print "\n"
  		parser.print_help()
  		parser.exit(2)
  

  For example, if you saved the text file as gdbupgrade, your database is myogdb, and you are upgrading the sde master geodatabase, type the following at a command prompt:

  gdbupgrade --DBMS ORACLE -i myogdb --auth DATABASE_AUTH -u sde -p mysdepassword --upgrade TRUE