Aug 01

Tracing an Oracle EXPDP

With the EXPDP command, you can add the TRACE= parameter.

The TRACE parameter takes a 7-digit hexadecimal mask.  The first three digits enable tracing for a specific Data Pump component, while the last four digits are usually: 0300.

Normally, TRACE=480300 is sufficient.

To trace EVERYTHING (main component plus worker threads), use TRACE=1FF0300.

 

Taken from Oracle Document ID 286496.1:

 

— Summary of Data Pump trace levels:
— ==================================

Trace   DM   DW  ORA  Lines
level  trc  trc  trc     in
(hex) file file file  trace                                         Purpose
——- —- —- —- —— ———————————————–
10300    x    x    x  SHDW: To trace the Shadow process (API) (expdp/impdp)
20300    x    x    x  KUPV: To trace Fixed table
40300    x    x    x  ‘div’ To trace Process services
80300    x            KUPM: To trace Master Control Process (MCP)      (DM)
100300    x    x       KUPF: To trace File Manager
200300    x    x    x  KUPC: To trace Queue services
400300         x       KUPW: To trace Worker process(es)                (DW)
800300         x       KUPD: To trace Data Package
1000300         x       META: To trace Metadata Package
——- ‘Bit AND’
1FF0300    x    x    x  ‘all’ To trace all components          (full tracing)

 

Jul 29

Oracle EXPDP metrics

Is your Oracle EXPDP performing poorly?  Oracle has included an undocumented option called METRICS in the EXPDP command.  Simply add METRICS=Y to your EXPDP command line, and you will get performance data in your logfile.

 

;;;
Export: Release 11.2.0.4.0 – Production on Thu Jul 28 20:24:45 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
Starting “SYS”.”SYS_EXPORT_FULL_01″: “/******** AS SYSDBA” directory=oraexports dumpfile=test.dmp logfile=test.log full=y metrics=y
Startup took 1 seconds
Estimate in progress using BLOCKS method…
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Estimated 705 TABLE_DATA objects in 4 seconds
Total estimation using BLOCKS method: 3.691 GB
Processing object type DATABASE_EXPORT/TABLESPACE
Completed 18 TABLESPACE objects in 2 seconds
Processing object type DATABASE_EXPORT/PROFILE
Completed 2 PROFILE objects in 0 seconds

Jun 12

Read-consistent backups in Oracle

In order to get Read-Consistent backups in Oracle, you need to add the following line to your parameter file or command line:

flashback_time=systimestamp

 

Otherwise, you can get inconsistencies in your SDE versioning state-tree.

Apr 30

Using python to repair data sources

This is really here for me to remember these.  But two lines of python code can be used to change the data source of your current MXD.  Thanks to Malcolm Lobley of Delo Solutions for this tip.

 

mxd = arcpy.mapping.MapDocument(“CURRENT”)

mxd.replaceWorkspaces(“”, “”, r’Database Connections\Connection To SDE.sde’, “SDE_WORKSPACE”)

 

 

May 15

Using DBMS_MONITOR

— create logon trigger for one user;
CREATE OR REPLACE TRIGGER sde.logon_trace_trig AFTER LOGON ON sde.SCHEMA
BEGIN
dbms_session.set_identifier(‘SDE’);
END ;
/
show errors;

— create logon trigger for all users:
CREATE OR REPLACE TRIGGER logon_sess_id_trig AFTER LOGON ON database
BEGIN
dbms_session.set_identifier(substr(user, instr(user, ‘\’) + 1));
END ;
/
show errors;

— to use these, two options:
— If the trigger has set the CLIENT_ID
dbms_monitor.client_id_trace_enable(‘SDE’, TRUE, TRUE);
— then disable:
dbms_monitor.client_id_trace_disable(‘SDE’);
— Better way to get sid, serial# with tracefile name
select s.sid, s.serial#, s.username, s.module, a.value || ‘\’ || d.instance_name || ‘_ora_’ || p.spid || ‘.trc’ trace_file_path from v$parameter a, v$process p,v$session s, v$instance d
where s.paddr=p.addr and s.module = ‘ArcMap.exe’ and a.name = ‘user_dump_dest’;

— or, if you didn’t set an identifier, you can still trace with dbms_monitor:
select sid, serial# from v$session where username = ‘SDE’;
— Start tracing:
dbms_monitor.session_trace_enable(sid, serial#, TRUE, TRUE);
— then disable:
dbms_monitor.session_trace_disable(sid, serial#);

Mar 26

MongoDB Series, Part 1: Installation and Configuration

With the surging popularity of NoSQL options, Geographic Database Consulting will take a deeper look at utilizing MongoDB with ArcGIS. This chapter will focus on the installation and configuration of MongoDB on an Oracle/RedHat linux server.

Create a YUM Repository

The easiest method of installing MongoDB on Oracle/RedHat Linux is via the 10Gen YUM repository. Create a file under /etc/yum.repos.d/10gen.repo.

For 64-bit linux, add the following lines to the repo file:

[10gen]

name=10gen Repository

baseurl=http://downloads-distro.mongodb.org/repo/redhat/os/x86_64

gpgcheck=0

enabled=1

 

For 32-bit linux, add the following lines to the repo file:

[10gen]

name=10gen Repository

baseurl=http://downloads-distro.mongodb.org/repo/redhat/os/i686

gpgcheck=0

enabled=1

 

Installing MongoDB

With your YUM repository set up, installing MongoDB is as easy as:

yum install mongo-10gen mongo-10gen-server

 

Upgrading MongoDB

If you’ve installed an older version, and are ready to upgrade to the latest stable release of MongoDB, you can use the following command:

yum update mongo-10gen mongo-10gen-server

Configuring MongoDB

Once installed, MongoDB is configured using the file /etc/mongod.conf. In this file, you can specify what parameters the MongoDB server uses at runtime. The basic parameters you will want to consider changing for testing purposes will be:

logpath  — specifies the path to your mongod.log file;

dbpath   — specifies the path to the Mongodb data storage;

There are several other parameters available, and in a production system you will want to learn more about them. They are beyond the scope of this blog post.

 

Starting and Stopping MongoDB

You can start MongoDB manually with the following command:

service mongod start

Similarly, you can stop MongoDB manually with:

service mongod stop

To start/stop MongoDB automatically on system reboots, use:

chkconfig mongod on

Using MongoDB

From a linux command prompt, enter the command:

mongo

Some basic Mongo shell commands:

show databases       — list all available databases

use arcgis           — use a database called arcgis, or create one.

db.stats()           — Show basic stats about the current database;

db.collection.find() — Run a query against the current db/collection

 

For a full reference of the Mongo shell JavaScript commands, please visit: http://docs.mongodb.org/manual/reference/javascript/

Part 2 of the Geographic Database Consulting series on MongoDB will focus on integrating MongoDB with ArcGIS…

Mar 07

PostgreSQL Series, Part 2: Learning the Basics

Continuing with our series of utilizing PostgreSQL for your Geodatabase storage needs. Part 1 covered the installation and configuration of creating an empty geodatabase. Part 2 is focused on learning some of the basics: data access, how to backup/restore, and general database maintenance.

Geographic Database Consulting highly recommends installing the version of PostgreSQL from EnterpriseDB.com. They have a great community of resources and tutorials, as well as commercial support should you need it.

Being a long-time Oracle DBA, I am a professed command-line junkie. So most of these tips will revolve around doing things via command-line rather than the pgAdmin GUI. While the GUI is extremely useful, I learn better when doing things manually. So that is how I will teach it…

Accessing your data

Command Line

In Oracle, I use “sqlplus” religiously. The PostgreSQL equivalent is “psql”. To ensure your command line environment is set up correctly, you should source the “pg_env.sh” shell script, such as:

source /opt/PostgreSQL/9.1/pg_env.sh

This sets some environment variables such as your PATH, MANPATH, PGDATA directory, etc.

Once your environment is ready, you can access your database using the basic “psql” command:

 

[jeff@jeff-linux ~]$ psql -d sde -U sde

Password for user sde:

psql.bin (9.1.6)

Type “help” for help.

sde=#

 

In this example, the “-d” option specifies the database to which we want to connect (the SDE database). The “-U” option specifies our username, again SDE. Running a “psql -?” will show you all the command line options available.

Once you’re connected to your database, PSQL has some very cool command line shortcuts:

\d – List all tables, views, sequences, etc

\dt – List all tables only

\dv – List all views only

\d [TABLE] – Describe table

\dp [PATTERN] – List privileges

\di – List indexes

\? – Give you a list of ALL the command line shortcuts and options.

\q – Quit your command line session

 

Of course, from here you have all the basics of SQL commands:

sde=# select count(*) from parcels;

-[ RECORD 1 ]

count | 96422

 

If you have your features stored using PG_GEOMETRY, you can easily access your feature coordinates:

sde=# select objectid, AsText(shape) from sewerfitting where objectid < 10 order by 1;

objectid | astext

———-+——————————————

1 | POINT(1554694.43960908 452484.361655176)

2 | POINT(1539518.99997894 453961.00012055)

3 | POINT(1539889.00010729 453950.999992535)

4 | POINT(1538106.00004163 453966.00005655)

5 | POINT(1527366.99996763 471819.999945179)

6 | POINT(1534405.51543018 463114.31212908)

7 | POINT(1535457.74813516 451090.000053883)

8 | POINT(1556346.00005862 452487.999927178)

9 | POINT(1555743.99993007 452401.000119105)

(9 rows)

 

Overall, I really like the PSQL command environment. It provides everything I use in SQLPLUS on a daily basis, and once you learn the shortcuts, they are fantastic. Wish SQL PLUS had them…

PGADMIN GUI

For those that want a GUI, the pgAdmin tools are very full-featured. You can download them from the following URL:

http://www.pgadmin.org/

 

Backup and Restore

This is the single most important operation on any database. Beyond using these tips, you should dig deeper into the PostgreSQL documentation to make sure you are adequately protecting your database investment.

PostgreSQL offers a couple different types of backups, depending on your needs.

PG_DUMP

The “pg_dump” command is the simplest form of backup for PostgreSQL.

pg_dump -U sde sde -f /data/backups/pg_sde_2013_03_07.sql

This creates an ascii text file of the SDE database with all the CREATE TABLE and INSERT statements to fully recreate the database.

PG_DUMPALL

PostgreSQL provides a command to dump ALL the databases in your installation.

pg_dumpall > /data/backups/pg/pg_dumpall_2013_03_07.sql

You can confirm which databases are included in the backup with a simple grep command:

#grep “^[\]connect” /data/backups/pg/pg_dumpall_2013_03_07.sql

Returns:

\connect blank_sde

\connect postgres

\connect sde

\connect template1

\connect template_postgis

Restoring a database

Restoring a database is as simple as replaying the SQL file generated by the PG_DUMP or PG_DUMPALL:

psql –U sde –f /data/backups/pg_sde_2013_03_07.sql sde

Automating Backups

PostgreSQL provides a script which can be used to automate backups on Linux. The script is provided at the following URL:

http://wiki.postgresql.org/wiki/Automated_Backup_on_Linux

 

Data Maintenance

PostgreSQL databases require periodic maintenance known as vacuuming. Vacuuming accomplishes several tasks:

  • Recovers disk space occupied by updated or deleted rows.
  • Updates data statistics used by the query planner.
  • Protects against loss of very old data due to transaction ID wraparound.

Automated Vacuum Daemon

By default, in your POSTGRESQL.CONF file, there is a parameter called AUTOVACUUM=ON. This will take care of the basics, and should be left at the default settings unless you find a specific reason to change.

Manual Vacuuming

If you have some feature classes that are heavily updated, you can choose to manually vacuum those tables. For example, you could choose to manually vacuum your SDE_STATE_LINEAGES table every couple hours.

psql -U sde -d sde -c “vacuum verbose analyze sde_state_lineages”

Or you can vacuum the whole database with:

Psql –U sde –d sde –c “vacuum verbose analyze”

NOTE: you should avoid using “vacuum full” on your SDE_STATE_LINEAGES table (or any table during the day). Using “vacuum full” requires the table to be locked.

Feb 01

PostgreSQL Series, Part 1: Installation and Configuration

Geographic Database Consulting is running a multi-part series on utilizing PostgreSQL for your Geodatabase storage needs. This chapter will focus on the installation and configuration of PostgreSQL for hosting a Geodatabase.

For this blog, I will be installing PostgreSQL on a Virtual Machine running Oracle Linux 64-bit, which is binary compatible with Redhat. You can find the ESRI ArcSDE 10.1 System Requirements here:

http://resources.arcgis.com/en/help/system-requirements/10.1/index.html#//015100000075000000

PostgreSQL is a free and open-source Relational DataBase Management System (RDBMS). We highly recommend the version available from EnterpriseDB.com, as commercial support is available should you ever need it. You can find the Downloads available at the following URL:

http://www.enterprisedb.com/downloads/postgres-postgresql-downloads

After selecting the appropriate PostgreSQL download, you should save the file…

Go into the Downloads folder, and you will need to make the file “executable”:

# chmod 744 postgresql-9.1.6-1-linux-x64.run

Then run the installer with the following command:

# ./postgresql-9.1.6-1-linux-x64.run

Walking thru the setup program is pretty simple.

Select an appropriate path, and hit Next>

Select an appropriate path for data files, and hit Next>

Type in a password for the POSTGRES operating system user, and hit Next>

The default port number 5432 should be fine. Hit Next>

Default locale should be fine. Hit Next>

Hit Next> to perform the installation.

The installer goes thru a typical progression, and will present with a “Finish” screen…

The “Stack Builder” is a nice tool that will help you install PostGIS, and any other add-ons you might want. For this guide, I will run the Stack Builder, to install the PostGIS extension. Hit Finish.

In some cases, the Stack Builder won’t run correctly, depending on who’s logged in, and who’s running the installer, etc. If it doesn’t start correctly, you can always start it thru the Menus, but selecting Applications->

 

When the Stack Builder starts, select your new PostgreSQL 9.1 on Port 5432. Hit Next>

Open Categories->Spatial Extensions. Highlight PostGIS. Hit Next>

Select an appropriate Download directory, and hit Next>

The Stack Builder will download the PostGIS installer, and give you the option to run the installer…

If you want to run the installer, leave the checkbox empty, and hit Next>

This kicks off the PostGIS installer.

Select Next>

Since this is a clean install, leave the box unchecked, and hit Next>

Type in your postgres user password (as entered above), and hit Next>

Select Next>

The installer will go thru a few steps, including creating a template database.

When complete, it will present you with a Finish dialog.

Select Finish. Also select Finish on the Stack Builder dialog box. This ends the PostgreSQL installation.

Now it’s time to configure PostgreSQL, and create an empty Geodatabase.

Open a command prompt. As root, run the following command:

# source /opt/PostgreSQL/9.1/pg_env.sh

This will set the appropriate environment variables.

 

Next, you will need to modify your “pg_hba.conf” configuration file. By default, PostgreSQL won’t allow network connections to it, so we need to change that. The “pg_hba.conf” file is usually located in your data directory. So following our example, it’s under /opt/PostgreSQL/9.1/data/pg_hba.conf. I like using “vi” (no flames, please), so as root:

vi /opt/PostgreSQL/9.1/data/pg_hba.conf

Add the following line to the end of the file:

host all all 192.168.0.0/24 trust

Change the values as appropriate for your network. This line will allow access to all databases from all 192.168.0.X IP addresses.

Restart PostgreSQL for the change to take affect…

# service postgresql-9.1 restart

Should give the following output:

Restarting PostgreSQL 9.1:

waiting for server to shut down…. done

server stopped

waiting for server to start…. done

server started

PostgreSQL 9.1 restarted successfully

 

Next, start pgAdmin3.

Double click on the PostgreSQL 9.1 (localhost). Enter the password, and if you want, select the checkbox to store password.

Expand the PostgreSQL 9.1 on Localhost. Right click on Databases, and select New Database…

We’re going to create a new database called Geodatabase. Set the owner to postgres. Go to the Definition tab.

Set the Template to “template_postgis”. This will allow us to use the POSTGIS geometry storage if we want.

Hit OK to create the database.

Back in pgAdmin III, we want to right click the Login Roles, and select New Login Role…

Create a role named SDE. Go to the Definition tab.

Set the password. (It’s not SDE, I promise! J ) Go to the Role privileges tab.

Make sure SDE can create database objects. Optionally, make SDE a Superuser. Nothing to change on the rest of the tabs. Hit OK.

Next, we need to enable the ST_GEOMETRY storage type by copying ESRI’s shared library.

Find the ST_GEOMETRY.SO library in the DatabaseSupport directory under ArcGIS\Desktop10.1.

Copy that file to your Linux box using whatever tools you have available (scp, filezilla, etc). We need to figure out where to put the file…

As root, run the pg_config command, as follows:

# /opt/PostgreSQL/9.1/bin/pg_config

Look for the line that says “PKGLIBDIR = “. That is the directory where we need to copy the st_geometry.so shared library.

BINDIR = /opt/PostgreSQL/9.1/bin

DOCDIR = /opt/PostgreSQL/9.1/doc/postgresql

HTMLDIR = /opt/PostgreSQL/9.1/doc/postgresql

INCLUDEDIR = /opt/PostgreSQL/9.1/include

PKGINCLUDEDIR = /opt/PostgreSQL/9.1/include/postgresql

INCLUDEDIR-SERVER = /opt/PostgreSQL/9.1/include/postgresql/server

LIBDIR = /opt/PostgreSQL/9.1/lib

PKGLIBDIR = /opt/PostgreSQL/9.1/lib/postgresql

LOCALEDIR = /opt/PostgreSQL/9.1/share/locale

MANDIR = /opt/PostgreSQL/9.1/share/man

SHAREDIR = /opt/PostgreSQL/9.1/share/postgresql

SYSCONFDIR = /opt/PostgreSQL/9.1/etc/postgresql

PGXS = /opt/PostgreSQL/9.1/lib/postgresql/pgxs/src/makefiles/pgxs.mk

CONFIGURE = ‘–with-libs=/usr/local/openssl/lib:/usr/local/ldap-2.4.23/lib:/usr/local/lib’ ‘–with-includes=/usr/local/openssl/include:/usr/local/ldap-2.4.23/include:/usr/local/include’ ‘–prefix=/mnt/buildfarm/pginstaller.auto/server/staging/linux-x64’ ‘–with-ldap’ ‘–with-openssl’ ‘–with-perl’ ‘–with-python’ ‘–with-tcl’ ‘–with-tclconfig=/opt/ActiveTcl-8.5/lib’ ‘–with-pam’ ‘–with-krb5’ ‘–enable-thread-safety’ ‘–with-libxml’ ‘–with-ossp-uuid’ ‘–docdir=/mnt/buildfarm/pginstaller.auto/server/staging/linux-x64/doc/postgresql’ ‘–with-libxslt’ ‘–with-libedit-preferred’ ‘–with-gssapi’ ‘LD_LIBRARY_PATH=/usr/local/openssl/lib:/usr/local/ldap-2.4.23/lib:/usr/local/lib’

CC = gcc

CPPFLAGS = -D_GNU_SOURCE -I/usr/local/include/libxml2 -I/usr/local/openssl/include -I/usr/local/ldap-2.4.23/include -I/usr/local/include

CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv

CFLAGS_SL = -fpic

LDFLAGS = -L/usr/local/lib -L/usr/local/openssl/lib -L/usr/local/ldap-2.4.23/lib -L/usr/local/lib -Wl,–as-needed -Wl,-rpath,’/mnt/buildfarm/pginstaller.auto/server/staging/linux-x64/lib’,–enable-new-dtags

LDFLAGS_EX =

LDFLAGS_SL =

LIBS = -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -ledit -lcrypt -ldl -lm

VERSION = PostgreSQL 9.1.6

[root@jtb-oracle ~]#

 

I have Cygwin installed, and just use SCP:

$ scp st_geometry.so root@192.168.0.20:/opt/PostgreSQL/9.1/lib/postgresql/

We need to make the library executable, so as root, run the following command:

chmod +rx /opt/PostgreSQL/9.1/lib/postgresql/st_geometry.so

Now, fire up trustly ol’ ArcCatalog. Create a new Spatial Database Connection.

When you select “OK”, it will show you as connected, but there is nothing in our database yet…

In ArcToolbox, go to Data Management Tools->Geodatabase Administration->Enable Enterprise Geodatabase. Start the tool by double clicking.

Select your Database Connection, and a valid ESRI Authorization File.

If everything is configured correctly, you should get a successful message:

 

To test, I created a new database user called DEMO, giving DEMO the same privs as SDE.

 

Create a new database connecting using the DEMO user:

 

You can import a new feature class. For my test, I will import some Interstates.

By default, the new geodatabase will use the ST_GEOMETRY spatial type for storage. If you want to use the POSTGIS storage, you can change your Environment setting to use the PG_GEOMETRY config keyword.

Running a quick query to test:

PostgreSQL with PostGIS is correctly configured and hosting a geodatabase…

 

Jan 20

Life in the Fast Lane

Geographic Database Consulting president Jeff Buturff recently wrote a guest blog post for SSPInnovations.com. You can view that blog post here:

http://www.sspinnovations.com/blog/2013/01/14/life-fast-lane

Jan 09

UDL Properties on 64-bit Windows

Since ArcGIS is still a 32-bit application, it requires that you install the 32-bit Oracle client. If you are trying to use a UDL file on 64-bit windows, and want to use the 32-bit Oracle Provider for OleDB, you need to open the UDL file with the 32-bit Ole DB Providers properties. Do that using the following command:

c:\windows\SysWOW64\rundll32.exe "c:\Program Files (x86)\Common Files\System\ole db\oledb32.dll",OpenDSLFile MyFile.udl

Using that command will give you the proper 32-bit Ole DB providers, and allow you to create/modify/test your UDL file.

 

Older posts «