# 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")
