1 / 78

Administering your Oracle Geodatabase

July 25, 2012. Administering your Oracle Geodatabase. Travis Val Jim McAbee. Agenda. Administration Topics System Requirements and Configuration 10.1 Administration Options 10.1 Client and Connection Differences Installation, Migration and Upgrade Scenarios Managing Performance in 11g

aidan-cochran
Download Presentation

Administering your Oracle Geodatabase

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. July 25, 2012 Administering your Oracle Geodatabase Travis Val Jim McAbee

  2. Agenda • Administration Topics • System Requirements and Configuration • 10.1 Administration Options • 10.1 Client and Connection Differences • Installation, Migration and Upgrade Scenarios • Managing Performance in 11g • Some Notes on Advanced Configurations • Loading, Backup and Spatial Types • Pre-Requisites • Administration of Geodatabase Intro • Basic knowledge of database terminology • Working knowledge of ArcSDE Technology Basics

  3. System Requirements and Configuration

  4. Major Themes 9.x/10.0 to 10.1 • System Requirements • 9.3/10 to 10.1 biggest impact on client configuration 32 vs. 64 bit, instant client, etc… • Administration Automation • Command Line Tools: Oracle runs on Unix/Linux so they may be used – ArcSDE command line tools are separate install • 10.1 GDB Administrative interface and new GP tools • Upgrade/Migration • Datatypes (should be migrating out of LONG RAW to other types) • Oracle issues • Datapump imports into 11.2.0.2 or 11.2.0.3, 11.2.0.1 tends not to have issue (GDB_Items, etc..) – ORA-00600 possible

  5. ArcGIS and Oracle Versions • Oracle Editions • Enterprise - Standard One – Standard • ArcGIS Version Backwards Compatibility 10.1 10.0 9.3 9.1 9.2

  6. System Requirements: OS for Oracle 11g r2 10 • Check Operating System also…….including patch level requirements 10.1

  7. DBMS_PIPE, DBMS_LOCK, and other packages • What are they? • DBMS_PIPE: allows sessions to communicate through memory • DBMS_LOCK: Oracle’s api to create locks in DB from SQL • For ArcSDE granted execute to public • Remove privileges from public? • Tech Article #35408 – Can the public privilege be removed from Oracle’s dbms_pipe and dbms_lock packages? • 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; • GRANT EXECUTE ON dbms_crypto TO sde;

  8. Oracle configuration parameters • Oracle Memory: • System Global Area (SGA) and Program Global Area (PGA) • Memory areas used for - Interpreting SQL statements, Fetching data and Submitting edits to database • Use Automatic Shared Memory Management (ASMM) • Set SGA_TARGETthrough Oracle Enterprise Manager (OEM) • Cursor Configuration: • Cursor: Points to rows returned by a SQL statement • Used to avoid re-parsing similar statements with bind variables • Stored in PGA and used extensively by ArcSDE • open_cursors = 2000 (or larger to avoid ORA-1000 error) • Tech Article #27024 – What is an appropriate value for the Oracle init.ora parameter ‘open_cursors’

  9. Controlling Storage • Tablespace Management based on • Backup requirements - Activity - Size of segment • SDE.DBTUNE table • Specifies configuration keyword, storage type, storage parameters • To create a keyword: • Export DBTUNE table to a file, edit it and import back sdedbtune–o alter

  10. 10.1 Clients and Connections

  11. ArcGIS Geodata Management technology stack Behaviors &Complex Features ArcGIS clients(ArcObjects/API’s) Geodatabase ArcSDE (ArcSDE libraries & stored procedures) Spatially enabled database Versioning & Distributed Data Transaction Management, Spatial Types, Indexing Oracle (RDBMS) Database

  12. Connection Architectures “Direct Connect” – Recommended Method ArcSDElibraries OracleClient Geodatabase 1521 “Application Server” ArcSDE Libraries Database Server giomgr Geodatabase gsrvr 5151 “Application Server - standalone” ArcSDE Libraries Database Server giomgr OracleClient Geodatabase gsrvr 5151 1521

  13. 10.1 and Oracle Clients ArcGIS forDesktop ArcGIS forServer • Administrator • Runtime • Instant (now supported at 10.1) • Remember ArcGIS Server now64 bit at 10.1 32 64 32 bit Oracle Client 64 bit Oracle Client 64 bitOracle 64 bitOracle

  14. 10.1 Connection Functionality Geodatabase Database Spatial Data ST_Geometry* SDO_Geometry *ST_Geometry SQL function support only available for supported Operating Systems (where library compiled) 10 10.1 Query Layersintroduced @ 10.0 10.1 Clients 10.0 Clients GeodatabaseAdministrative Schema

  15. Connecting at 10.1 10.1 10

  16. 10.1 Oracle Instance Connection string If instance not listening on default port number, must use connection syntax that includes port number.

  17. Demo: Setting Up Instant Client

  18. 10.1 Administration

  19. Administrative and Maintenance Task more capabilities from desktop for GIS manager and staff 10 10.1 • Administrative task automation – more options at 10.1 • more functionality in Desktop and Server • SDE command line tools separate install & 64 bit (need 64 bit client)

  20. Inclusion of Geodatabase Toolset

  21. Multiple Geodatabase Configuration • Multiple Geodatabase for Different Tasks • At least two Geodatabases should be present typically – production and test • Other reasons for multiple geodatabases • Editing and publishing (web) • Production and test/development • Different application needs • Separate versioning management • Methods for configuring multiple Geodatabases in Oracle • Multiple instances on same server • Multiple instances on different servers • Project/Schema Geodatabase Production Publishing Test/Dev.

  22. Oracle Schema Geodatabase Admin An option for multiple Oracle Geodatabases • Multiple Oracle Geodatabases – multiple instances or within same instance • Geodatabase contained in specific User Schema • SDE “master” geodatabase present with Schema based Geodatabase in instances table • Rules • a user can own only one geodatabase • that user is the geodatabase administrator and requires those database privileges • that user can only own data in that geodatabase • users can only own data in one geodatabase

  23. Notes on Upgrades and Migration

  24. Administration Topics : Planning for New Configuration • What are some of the more advanced issues? • Long Raw to BLOB • Geodatabase Administrative schema changes • Differences between Oracle versions • Default Oracle settings are a good starting point except: • Database Cache / Memory, Max number of cursors a session can use, Data Storage • Estimate or measure load and storage: • Need to define a sample data, MXD and operations when possible • Oracle Enterprise Manager (OEM), Oracle Remote Diagnostic Agent (RDA)

  25. Upgrade Considerations • Always perform any upgrades in a test environment • Backup any custom files in %SDEHOME%\etc directory • dbinit.sde • dbtune.sde • Upgrade OS and Oracle first, if needed, then test • Upgrade ArcSDE, then test • Backup, Backup, Backup! • It is NOT a Backup unless it has been tested to make sure it works. • Check existing GDB for any errors or redundancies • Clean DBMS_PIPE - Values in the database pipe can cause connection problems • Upgrade to 10.x requires use of ArcObjects GP tool • New Geodatabase Administrative schema changes

  26. Upgrading Schema based Geodatabase • Must upgrade master first and not simultaneously • Must make a backup of the entire database; creating a backup of only a user's schema does not include the user-defined types and functions • Stopping the ArcSDE service on the master geodatabase disables service connections to user-schema geodatabases

  27. Migration Tool • LONG RAW data type being deprecated at Oracle 11g • Migrate Storage GP tool • Easily convert GIS datasets from a different storage format to ST_GEOMETRY in Oracle • Changes data “in place”, no new datasets are created • Must be the data owner to execute • Supports versioned & archived datasets

  28. Upgrade Geodatabase – from ArcCatalog

  29. Upgrade Geodatabase • Requires sdeuser upgrade permissions • Requires a direct connection to geodatabase • Pre-requisite check determines if geodatabase is upgradable • See “Preparing to upgrade a geodatabase in Oracle” topic for a full list of requirements # Process: Upgrade Geodatabase arcpy.UpgradeGDB_management(<Connection file>, "PREREQUISITE_CHECK", "UPGRADE")

  30. Installation, Migration, Upgrade Summary • Start with recommended minimum parameters for New and monitor Existing • Cursors and Memory (OPEN_CURSORS, SGA, etc..) • Check connections parameter in server_config table via sdeconfig export/import command or sql • Monitor usage, or ask DBA’s to monitor and adjust as necessary • Establish a performance baseline so future growth and changes can be measured • OEM, mxdperfstat, Windows performance monitor, ASH

  31. ESRI KB and Help Articles for Parameters/Upgrade • FAQ: What is an appropriate value for the Oracle init.ora parameter 'open_cursors'?http://resources.arcgis.com/content/kbase?fa=articleShow&d=27024 • Error: ORA-01000: maximum open cursors exceededhttp://resources.arcgis.com/content/kbase?fa=articleShow&d=28861 • HowTo: Identify a cursor leak in Oraclehttp://resources.arcgis.com/content/kbase?fa=articleShow&d=35090 • SQL Statements using st_geometry operators experience decreased performance in Oracle 11g http://resources.arcgis.com/content/kbase?fa=articleShow&d=35236 • Oracle Parameters help topicshttp://webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/oracle_1010194088.htm

  32. Spatial Types

  33. ESRI Spatial Type – ST_GEOMETRY • User Defined Type (UDT) used to store geographic features • Allows access to spatial data through SQL functions • Efficiency - Automatic geometry validation • Conforms to ISO and OGC standards • Available since ArcSDE 9.2

  34. Using SQL with ST_GEOMETRY • Needs an external dll • Extproc needs to be set up for • Executing SQL commands on a ST_GEOMETRY column • For using ST_GEOMETRY operators • Definition and label queries inside ArcMap • With SQL and ST you can • Create tables with a spatial attribute • Read and Analyze the spatial data • Insert, update and delete simple geometry data • Geodatabase behavior not supported through SQL

  35. Spatial Types and Functions • Creation of Features • through SQL • Analysis • Buffering Geometry • Convex Hull • Difference of Geometries • Intersection of Geometries • Symmetric Differences • Union • Minimum Distance • Aggregates • Feature Information

  36. ST_Geometry spatial type configuration • st_shapelib library • extproc.ora (11g) or listener.ora/tnsnames.ora configuration

  37. Setting up the ExtProc via Listener and Tnsnames tnsnames.ora file: EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO)) ) Key must match listener.ora file: LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=AKLAP.esri.com)(PORT=1521)) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST=AKLAP.esri.com)(PORT=1521)) ) ) New Entry Original Entry

  38. Setting up the ExtProc (10g) listener.ora file: LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST=AKLAP.esri.com)(PORT=1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = C:\oracle\product\10.2.0\db_1) (PROGRAM = extproc) (ENVS="EXTPROC_DLLS=C:\Program Files\ArcGIS\ArcSDE\ ora10gexe\bin\st_shapelib.dll") ) )

  39. extproc.ora @ 11g • Located in ORACLE_HOME\hs\admin directory • WindowsEXTPROC_DLLS=ONLY:C:\\mylibraries\\st_shapelib.dll • Unix/LinuxSET EXTPROC_DLLS=ONLY:/user/esrilibs/libst_shapelib.so

  40. Oracle ExtProc - for St_geometry • Modify the listener.ora and tnsnames.ora files • Oracle Documentation • Webhelp topic - Configuring the Oracle listener to access the geodatabase with SQL • Technical articles: • 32188 - Error:  ORA-28595: Extproc agent : Invalid DLL Path • 33003 -Error:  ORA-06522: libsg.so: cannot open shared object file: No such file or directory • 33004 - Bug:  ORA-06522: (path)\st_shapelib.dll: cannot open shared object file: No such file or directory • 38043 – Bug: Configure ST_GEOMETRY for use in Oracle 11G Release 2 on Windows • 39119 – How to:  Check if the Oracle extproc is set up correctly for direct ST_GEOMETRY SQL queries

  41. Demo: Configuring ST_Geometry

  42. Few notes on SDO_Geometry • Set following in SDE.DBTUNE table when working with data stored in SDO_Geometry • UNICODE_STRING set to “FALSE” (if VARCHAR vs. NVARCHAR is desired) • GEOMETRY_STORAGE set to “SDO_Geometry” • SDO_SRID (if same SRID is always used) • other parameters

  43. Managing Performance and Troubleshooting

  44. Managing Performance in 11g : Statistics • Table statistics • The distribution and contents of rows • What the optimizer uses to make execution plans • Index statistics • Information about the rows stored in IOTs, and other index metadata • System statistics • Internal object statistics

  45. Oracle table statistics • Describe segment characteristics for optimizer and DBA • Critical for maintaining performance • Current statistics increase Oracle IO performance • Update when data changes significantly: • Loading • Major editing • Analyze through ArcGIS Createstatistics on attributes, geometry, versioning delta tables and historical archive

  46. Optimizer Statistics Collection – statistics collection Segment Advisor – segment reorganization SQL Tuning Advisor – attempts to tune high-load SQL Oracle 11g – Automatic Maintenance • Tasks Automated at 11g

  47. Oracle automatic statistics gathering 10g vs. 11g Utilize optimizer_dynamic_sampling (KB32005)http://resources.arcgis.com/content/kbase?fa=articleShow&d=32005 Other options ArcCatalog and GP Tools sdetable –o update_dbms_stats –t roads –m estimate Temporary Tablespace - increase size for compute consider increasing the size of your temporary table space to compute statistics rather than estimate them because it provides more accurate statistics for the optimizer. Managing Performance in 11g : Statistics

  48. 11g Auto Statistics Gathering • Setting Stale Percentage

  49. Other Configuration Tips • D-Table indexes • need to be checked for errors and redundancy in the _IDX (KB31081) and _PK (KB24925) indexes • A-Table Index • Performance gains by altering the Adds table indexes (KB32184) • Cache the lobs • Most lobs can be cached to reduce trips to disk ST(KB33428), Network(34485), Raster(35521) • Index rebuild • Sweeper Indexes need to be rebuilt to reduce their number of blocks (KJ35409) • ST_GEOMETRY Spatial index shrink • Reduce the number of blocks used by the spatial index (KB33341) • Re-gather Stats • Do not build stats on the logfile tablesNote: Scripts to perform these tasks are included at the end of slide deck

More Related