# Author: Esri # Import the required modules import arcpy, smtplib, sys try: # Set an admin connection variable. adminConn = 'Database Connections/admin2.sde' # Set a few environment variables arcpy.env.workspace = adminConn arcpy.env.overwriteOutput = True # For demo purposes we will block connections to the geodatabase during schema rec/post/compress. arcpy.AcceptConnections(adminConn, False) # Disconnect any connected users. arcpy.DisconnectUser(adminConn, 'ALL') # Get a list of versions to pass into the ReconcileVersions tool. # Only reconcile versions that are children of Default verList = arcpy.da.ListVersions(adminConn) versionList = [ ver.name for ver in verList if ver.parentVersionName == 'sde.DEFAULT' ] # Execute the ReconcileVersions tool. try: arcpy.ReconcileVersions_management(adminConn, "ALL_VERSIONS", "sde.DEFAULT", versionList,
# Prepare actual message MESSAGE = """\ From: {0:s} To: {1:s} Subject: {2:s} {3:s} """.format(FROM, ", ".join(TO), SUBJECT, MSG) # Send the mail server = smtplib.SMTP(SERVER) server.sendmail(FROM, TO, MESSAGE) server.quit() #block new connections to the database. arcpy.AcceptConnections('Database Connections/admin.sde', False) # wait 15 minutes time.sleep(900) #disconnect all users from the database. arcpy.DisconnectUser('Database Connections/admin.sde', "ALL") # Get a list of versions to pass into the ReconcileVersions tool. versionList = arcpy.ListVersions('Database Connections/admin.sde') # Execute the ReconcileVersions tool. arcpy.ReconcileVersions_management('Database Connections/admin.sde', "ALL_VERSIONS", "sde.DEFAULT", versionList, "LOCK_ACQUIRED", "NO_ABORT", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST",
def clean_up(): arcpy.AcceptConnections(WORKSPACE_SA, True) log.write(datetime.datetime.now().strftime("%H:%M:%S %m/%d/%Y ") + "Script finished.\n") log.close()
#time.sleep(900) # Disconnect all users from the database. #arcpy.DisconnectUser('Database Connections/admin.sde', "ALL") # Get a list of versions to pass into the ReconcileVersions tool. versionList = arcpy.ListVersions( 'C:\Users\daniel.scott\Desktop\SDE\SDE@SRV-SQLHA02@CL_DVC.sde') print versionList # Execute the ReconcileVersions tool. print "Reconciling" arcpy.ReconcileVersions_management( 'C:\Users\daniel.scott\Desktop\SDE\SDE@SRV-SQLHA02@CL_DVC.sde', "ALL_VERSIONS", "sde.DEFAULT", versionList, "LOCK_ACQUIRED", "NO_ABORT", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "KEEP_VERSION", "c:/temp/reconcilelog.txt") # Run the compress tool. print "compressing" arcpy.Compress_management( 'C:\Users\daniel.scott\Desktop\SDE\SDE@SRV-SQLHA02@CL_DVC.sde') # Allow the database to begin accepting connections again arcpy.AcceptConnections( 'C:\Users\daniel.scott\Desktop\SDE\SDE@SRV-SQLHA02@CL_DVC.sde', True) # Get a list of datasets owned by the admin user print "rebuilding indexes" # Rebuild indexes and analyze the states and states_lineages system tables arcpy.RebuildIndexes_management(workspace, "SYSTEM", "ALL") print "analysing datasets" arcpy.AnalyzeDatasets_management(workspace, "SYSTEM", "ANALYZE_BASE", "ANALYZE_DELTA", "ANALYZE_ARCHIVE")
logger.info("\n Reconcile Log File Path : " + reconcilelogfile) # set the workspace env.workspace = connSDE #workspace variable wrkspc = env.workspace ##-------------------------------------------------- ##-----Allow connections to the geodatabase. Previuos code executions code have ##-------executed unsuccessfully leading to the fact that the geodatabase is a state that cannot accept connections ##-------Ensures no manual intervention via creationof geodatabase connection as DBA to allow connections is NEEDED ##---------------------------------------------------------- #Accept new connections to the database. arcpy.AcceptConnections(connSDE, True) ##--- ##---Find Connected Users ##--- # get a list of connected users userList = arcpy.ListUsers(connSDE) ##--- ##---Pass the list of connected users ##--- ## # get a list of user names from the list of named tuples returned from ListUsers ## userNames = [u.Name for u in userList] ## ## # take the userNames list and make email addresses by appending the appropriate suffix.
''' Created on Mar 12, 2013 @author: kyleg ''' import arcpy import ListUsersProd ListUsersProd.main() admin_workspace = r"Database Connections\SDEPROD_SDE.sde" arcpy.env.workspace = admin_workspace user_name = "GIS" users = arcpy.ListUsers(admin_workspace) # for item in users: if item.Name == user_name: print(item.ID, item.Name, item.ClientName, item.IsDirectConnection) arcpy.DisconnectUser(admin_workspace, item.ID) arcpy.AcceptConnections(admin_workspace, True)
def force_online(retry): print('Trying to force online.') try: check_db_SQL = ( '''select num_prop_value from [sde].[SDE_server_config] where prop_name = 'Status' ''' ) check_db_return = arcpy.ArcSDESQLExecute(conn_string).execute( check_db_SQL) if check_db_return == 1: print('{0} on {1} is online. There were {2} tries.'.format( source_db, source_db_type, retry)) mail_body = '{0} on {1} is online. There were {2} tries.'.format( source_db, source_db_type, retry) mail_subject = 'Success! {0} on {1} online'.format( source_db, source_db_type) mail_priority = '5' if retry > 0: send_message(mail_body, mail_subject, mail_priority) else: print('{0} on {1} is offline. Trying....'.format( source_db, source_db_type)) arcpy.AcceptConnections(conn_string, True) if retry % 5 == 0: mail_body = ( '{0} on {1} is offline and multiple attempts have been made to bring it back online. ' .format(source_db, source_db_type) + 'There have been {0} attempts made so far. This will continue trying until successful.\n\n' .format(retry) + 'Additional notifications will be sent if required.') mail_subject = 'FAILURE! {0} on {1} offline'.format( source_db, source_db_type) mail_priority = '1' send_message(mail_body, mail_subject, mail_priority) retry += 1 time.sleep(300) force_online(retry) else: retry += 1 time.sleep(60) force_online(retry) hard_fail = 0 except Exception as error: hard_fail += 1 print('Trouble connecting to SQL DB {0} on {1}.'.format( source_db, source_db_type)) if hard_fail == 1: mail_body = ( 'Unable to check database status for {0} on {1} at this time.\n\nError: {2}' .format(source_db, source_db_type, error)) mail_subject = ( 'Geodatabase Online Check Failed: {0} on {1}'.format( source_db, source_db_type)) mail_priority = '1' else: if hard_fail % 5 == 0: mail_body = ( 'Unable to check database online status for {0} on {1} at this time. This is attempt #{2}.\n\nError: {3}' .format(source_db, source_db_type, error, hard_fail)) mail_subject = ( 'Geodatabase Online Check Failed {0} Times: {1} on {2}'. format(hard_fail, source_db, source_db_type)) mail_priority = '1' send_message(mail_body, mail_subject, mail_priority) time.sleep(900) force_online(retry) return
def accept_connections(): print("Accept connetions") _accept_connections = True arcpy.AcceptConnections(sedFileSde, _accept_connections)
"""Refresh the Database to make sure it's accepting connections again.""" import arcpy # connect to the database as the dba admin arcpy.env.overwriteOutput = True arcpy.AcceptConnections(r'<path_to_connection_file>\<file_name>.sde', True) print("\tDatabase accepting Connections.")
def clean_up(): arcpy.AcceptConnections(WORKSPACE_SDE, True) log.write(datetime.datetime.now().strftime("%H:%M:%S %m/%d/%Y ") + "Users may now reconnect.\n") log.close()
def block_connections(): print("Block connections") _accept_connections = False arcpy.AcceptConnections(sedFileSde, _accept_connections)
config.read(r'BuildingFootprint_config_template.ini') # Set log path log_path = config.get("PATHS", "distribute_log_path") log = open(log_path, "a") # Define zip, sde, metadata, and missing bbl txt file paths zip_dir_path = config.get("PATHS", "zip_dir_path") zip_path = os.path.join(zip_dir_path, "Building_Footprints.zip") sde_path = config.get("PATHS", "sde_path") lyr_dir_path = config.get("PATHS", 'lyr_dir_path') template_path = config.get("PATHS", 'template_path') missing_bbls_path = config.get("PATHS", 'missing_bbls_path') # Disconnect all users arcpy.AcceptConnections(sde_path, False) arcpy.DisconnectUser(sde_path, "ALL") # Set start time StartTime = datetime.datetime.now().replace(microsecond=0) # Set translator path for exporting metdata from SDE print("Setting arcdir") Arcdir = arcpy.GetInstallInfo("desktop")["InstallDir"] translator = Arcdir + "Metadata/Translator/ARCGIS2FGDC.xml" xslt_geoprocess = Arcdir + "Metadata/Stylesheets/gpTools/remove geoprocessing history.xslt" xslt_storage = Arcdir + "Metadata/Stylesheets/gpTools/remove local storage info.xslt" print("Arcdir set") # Allow for overwriting in SDE PROD environment
def main(argv=None): # Ask for admin/publisher user name and password username = "******" password = base64.b64decode("UzNydmljZTE=") # Ask for server name serverName = "gtg2014.portlandmaine.gov" serverPort = 6080 folder = "BaseMaps" def stopStartServices(stopOrStart): for item in dataObj['services']: if item['serviceName'] in EVERGREENSERVICES: fullSvcName = item['serviceName'] + "." + item['type'] # Construct URL to stop or start service, then make the request stopOrStartURL = "/arcgis/admin/services/" + folder + fullSvcName + "/" + stopOrStart httpConn.request("POST", stopOrStartURL, params, headers) # Read stop or start response stopStartResponse = httpConn.getresponse() if (stopStartResponse.status != 200): httpConn.close() print "Error while executing stop or start. Please check the URL and try again." return else: stopStartData = stopStartResponse.read() # Check that data returned is not an error object if not assertJsonSuccess(stopStartData): if str.upper(stopOrStart) == "START": print "Error returned when starting service " + fullSvcName + "." else: print "Error returned when stopping service " + fullSvcName + "." print str(stopStartData) else: print "Service " + fullSvcName + ": " + str.upper( stopOrStart) httpConn.close() # Get a token token = getToken(username, password, serverName, serverPort) if token == "": print "Could not generate a token with the username and password provided." return # Construct URL to read folder if str.upper(folder) == "ROOT": folder = "" else: folder += "/" folderURL = "/arcgis/admin/services/" + folder # This request only needs the token and the response formatting parameter params = urllib.urlencode({'token': token, 'f': 'json'}) headers = { "Content-type": "application/x-www-form-urlencoded", "Accept": "text/plain" } # Connect to URL and post parameters httpConn = httplib.HTTPConnection(serverName, serverPort) httpConn.request("POST", folderURL, params, headers) # Read response response = httpConn.getresponse() if (response.status != 200): httpConn.close() print "Could not read folder information." return else: data = response.read() # Check that data returned is not an error object if not assertJsonSuccess(data): print "Error when reading folder information. " + str(data) else: print "Processed folder information successfully. Now processing services..." # Deserialize response into Python object dataObj = json.loads(data) httpConn.close() # Loop through each service in the folder and stop or start it if stopStartServices("stop"): arcpy.env.workspace = "C:\\MaintenanceScripts\\Connections\\[email protected]" workspaceSDE = arcpy.env.workspace workspaceSA = "C:\\MaintenanceScripts\\Connections\\[email protected]" inPath = "sde.portgis.EvergreenLotsHMIS" outPath = "EvergreenGraves" try: arcpy.AcceptConnections(WORKSPACE_SA, False) arcpy.DisconnectUser(WORKSPACE_SA, "ALL") if arcpy.Exists(outPath): arcpy.Delete_management(outPath) print "Deleted " + outPath print "Copying view to feature class..." arcpy.FeatureClassToFeatureClass_conversion( inPath, workspaceSDE, outPath) arcpy.AcceptConnections(WORKSPACE_SA, True) except Exception: print "Error copying view to feature class. Re-accepting connection..." arcpy.AcceptConnections(WORKSPACE_SDE, True) stopStartServices("start") return
start_time = time.time() LogFile.write( "This is the enterprise GDB maintenance log.\nThe maintenance script will compress a list of enterprise GDBs\nupdate statistics and rebuild table indexes.\n\nStarting maintenance.\n" ) ErrorCount = 0 ## Begin script # Compress EGDBs for each connection file found in the owner directory ownerDB = os.listdir(OwnerFileDir) for DB in ownerDB: try: arcpy.env.workspace = OwnerFileDir print "Disconnecting users from " + DB + "\n" LogFile.write("Disconnecting users from," + DB + "\n") arcpy.AcceptConnections(DB, False) arcpy.DisconnectUser(DB, "ALL") print "Compressing " + DB + "\n" LogFile.write("Compressing," + DB + "\n") arcpy.Compress_management(DB) print "Allowing connections to " + DB + "\n" LogFile.write("Allowing connections to," + DB + "\n") arcpy.AcceptConnections(DB, True) except arcpy.ExecuteError: msgs = arcpy.GetMessages(2) arcpy.AddError(msgs) print msgs LogFile.write(msgs) ErrorCount = ErrorCount + 1 print "Allowing connections to " + DB + "\n" LogFile.write("Allowing connections to," + DB + "\n")
Compresses, rebuilds indexes, and analyzes system tables Recreates child versions TODO:WIP """ # Import system modules import arcpy, os # Set workspace workspace = r'c:\Temp\test_editpermissions_sa.sde' # Set the workspace environment arcpy.env.workspace = workspace # Block new connections to the database. arcpy.AcceptConnections(workspace, False) # Wait 15 minutes # time.sleep(900) # Disconnect all users from the database. arcpy.DisconnectUser(workspace, "ALL") # Use a list comprehension to get a list of version names where the owner # is the current user and make sure sde.default is not selected. verList = [ ver.name for ver in arcpy.da.ListVersions() if ver.isOwner == True and ver.name.lower() != 'sde.default' ] # Reconcile and Post versions to sde.DEFAULT
import arcpy # Many ArcGIS SDE maintenence tasks cannot be performed while users are connected to DB # This script will disconnect users and reconnect them after you run maintenence tasks # One could just comment out the AcceptConnections method to do maintenence through catalog # then just run the accepting connections method once they are finished. #block new connections to the database. arcpy.AcceptConnections("Database Connections\your.sde", False) print 'Refusing Connections' #disconnect all users from the database. arcpy.DisconnectUser("Database Connections\your.sde", "ALL") print 'Disconnected all users' # perform server maintenence here! #Allow the database to begin accepting connections again arcpy.AcceptConnections("Database Connections\your.sde", True) print "Accepting connections"
error_Log.write( "\n" + str(error_count) + "\nError encountered when writing SDE user list to .txt file.\nDetails:\n" + str(e) + '\nError occured at: ' + str(script_fails_time) + '\n======================================\n') error_Log.close() with open(maintenance_log_FileName, 'a+') as maintenance_Log: maintenance_Log.write('\nWrite user list to file: failure!') maintenance_log.close() pass print("Blocking new connections to database...") try: # method to prevent any additional connections to the DB arcpy.AcceptConnections(db, False) with open(maintenance_log_FileName, 'a+') as maintenance_Log: maintenance_Log.write('\nStop any new connection: SUCCESS!') maintenance_log.close() print("...done blocking new connections to database!\n ") except Exception as e: print(e) error_count = error_count + 1 script_fails_time = datetime.datetime.now() with open( "C:\\GIS_Scripts\\SDE_Maintenance\\Update_2018-08-14\\Error_Log.txt", 'a+') as error_Log: error_Log.write(
# Export all zips to web directory print("Copying all zips to web directory") m_bytes_prod_web = os.path.join(temp_env, prod_version, 'web') for dir in bytes_dirs: if dir is not 'web': zip_folder_path = os.path.join(temp_env, prod_version, dir) for f in os.listdir(zip_folder_path): if f.endswith('.zip'): shutil.copy(os.path.join(zip_folder_path, f), os.path.join(m_bytes_prod_web, f)) print("All zips are now available in web folder") # Disconnect users from Production SDE to prohibit any schema locks if necessary arcpy.AcceptConnections(sde_prod_env, False) arcpy.DisconnectUser(sde_prod_env, "ALL") ''' Export all necessary files from Bytes web directory to Production, Archive, and M: drive ''' # Assign item list variable holding bytes files. bytes_dir_list = os.listdir(temp_env) # Loop through MapPLUTO Bytes Production directories for those that match current production version. for item in bytes_dir_list: if prod_version == item or prod_version.upper() == item: bytes_file_path = os.path.join(temp_env, item)
proj_2278 = Proj(init='EPSG:2278', preserve_units=True) proj_4326 = Proj(init='EPSG:4326') try: settings = Settings(yml_file) if not __debug__: # must start Python with - o to use download_parcel_ftp(settings) shape_file_loc = find_parcel_shape(settings.download_path, settings.database) if not shape_file_loc: logging.error(f"Could not locate {settings.database}") exit(-1) # # arcgis stuff for multi-users arcpy.AcceptConnections(settings.workspace, False) arcpy.DisconnectUser(settings.workspace, "ALL") arcpy.env.workspace = settings.workspace # Manage FC names for copy # # if first time, create featuredataset HGAC ds = ec_arcpy_util.find_dataset("".join(("*", settings.data_set))) if ds is None: sr_2278 = arcpy.SpatialReference(2278) ds = arcpy.CreateFeatureDataset_management(settings.workspace, settings.data_set, sr_2278) # # do a little clean up
# Prepare actual message message = """\From: %s\nTo: %s\nSubject: %s\n\n%s """ % (FROM, ", ".join(TO), SUBJECT, TEXT) try: server = smtplib.SMTP("172.21.1.147") server.sendmail(FROM, TO, message) server.close() log.write(str(time.asctime()) + ": Successfully sent email.\n") except: log.write(str(time.asctime()) + ": Failed to send email.\n") # Error handling for majority of script try: # Block new connections to the database. arcpy.AcceptConnections(sdeWorkspace, False) log.write(str(time.asctime()) + ": Database connections blocked.\n") # Get a list of database connections then disconnect users userList = arcpy.ListUsers(sdeWorkspace) log.write(str(time.asctime()) + ": " + str(userList)) if len( userList ) > 1: # Always "1" connection because script initiates connection to the gdb log.write("\n" + str(time.asctime()) + ": There are " + str(len(userList) - 1) + " database connections.\n") arcpy.DisconnectUser(sdeWorkspace, "ALL") log.write(str(time.asctime()) + ": User(s) have been disconnected.\n") else: log.write("\n" + str(time.asctime()) + ": There are no connections to the geodatabase.\n")