SSTS Blog

I have an Application Express application that I use as a job status display. I wanted to color code the display based on the status code of the job run. i.e. a failed job should display the row details as red, yellow for jobs that have not yet run, blue for jobs in progress, etc.

In order to achieve color coded rows I created a report based off of an SQL Query, then created a Template (Generic Columns column template). Within the template definition I used Column Template Conditions based on PL/SQL Expressions. I then assigned expressions based on the status column value. For example, for Failed/Incomplete statuses:

'#STATUS#' = 'FAILED' or '#STATUS#' = 'INCOMPLETE'

Then in the Column Template HTML:

<td headers="#COLUMN_HEADER_NAME#" #ALIGNMENT# style="background:RED;" class="t18standard">#COLUMN_VALUE#</td>

The template editor allows up to four different template conditions to be specific which was enough to suit my needs. This greatly improved the visual effect of the page and quickly points out problems that need to be addressed.

Recently, I went through a restore for a very large database that took several days to complete. I was literally tied to my shell and couldn't afford to sever my internet connection and was unable to leave the house. I new of a command called screen and had used it before. However, I was in crisis mode and couldn't review it's use at the time and had already started the recovery and didn't want to restart the process. Today I reviewed screen and know it will come in handy. Screen allows you to re-attach to a secure shell session after a connection failure. Since we do a lot of remote work connection failures occur all the time. For Windows, using Remote Desktop this isn't a big deal since you can re-attach. But, for Linux/Unix the work-around isn't as well known.

Basically, to start a screen session just type the screen command. This starts a new shell (sort of a facade) that can be re-attached to if you need to disconnect or if your connection suddenly goes away.

To re-attached you need to get the session identifier so you can re-attach. To see a list of sessions use screen -ls. This command will list screen sessions that can be attached to.

To attach to a screen session: screen -r <identifier from screen -ls>

 

Recently, I attempted to upgrade a 10G database on Linux for 11.2. Before upgrading production I ran the procedure on a test database (on another server) with no problems. However, in the production environment the catupgrd.sql script failed. This happened over the weekend so I opened a severity 1 SR and began working with Oracle. I sent all the required information but Oracle was unable to get me past the issue. I only had about 8 hours before needing to rollback because this is an 8TB database. The script has gotten far enough to require an RMAN restore. This restore/recovery took about 60 hours to complete so this was quite painful. I continued working with Oracle on the SR to try and get to the root of the problem. It turns out that natively compiled PL/SQL (which is being used in this environment) stored the compiled binaries in /dev/shm on Linux. There was an error in the alertlog referring to /dev/shm but Oracle did not identify the cause within the 8 hour window I had for the upgrade. Turns out that the other machine that was used to test the upgrade had /dev/shm enabled but the production machine did not.

Takeways:

1. Make sure /dev/shm is enabled when upgrading a Linux environment that uses natively compiled stored procedures
2. Consistency at the OS level in your environment is a must

I was working today and trying to load some data from a new vendor. Several of the columns were declared as varchar2(4000) yet when sqlldr ran I was seeing the error "field in datafile exceeds maximum length". My first thought was that I may have a filed with more than 4000 characters. But, after examining one of the problem data lines I knew that was not the case. After a quick search I found that sqlldr defaults string lengths to 255 character unless specified otherwise in the control file. I had come across this before but had forgotten since the vast majority of data that I deal with doesn't run into this restriction. To resolve the issue I declared the long string columns as CHAR(4000) in the control file.

I often get requests to extract some database from the database and paste the results into an Excel Spreadsheet. These requests are often repeated so something I have started to do is paste the query that was run into the spreadsheet into a second tab. This saves the time of looking for the query that was run. Many times the follow up request is weeks or months later and it's hard to recall what query was run and this can burn a lot of time.

Over the past week I have been working with the Pentaho Data Integration tool. Overall, so far so good. I have a fair amount of experience with Microsoft SSIS and so far PDI stack up pretty well and in some cases (aka Secure FTP) it exceeds SSIS capabilities. There also some nice features built in that allow for interfacing with Mail and Social Media that are worth some exploration. Extension is also possible via implementing java classes that implement the required interface(s).

This has bit me twice in the past month. I have two client Oracle installations that were originally setup and someone created a link and assigned the linked path to the ORACLE_HOME. This caused major issues when trying to setup dbconsole. To be explicit.

The hard path to the oracle software is /u01/app/oracle/oracle/product/10.2.0/db_1

The path with the link was /u01/app/oracle/product/10.2.0/db_1

Notice the repeated oracle/ in the hard path?

The emca dbconsole setup process was totally confused by this. In some cases it reported success but anything that required database access failed to run. In my case the SYSMAN schema (where the repository gets created) was not created. I'm told this is because emca is connecting without the listener and a comparison is done on the environment of the shell of emca against the environment when oracle was installed. If the environment (namely the ORACLE_HOME) is different emca runs into problems. This would be a lot easier to deal with if emca indicating what the problem is, but in some cases it actually reports success.

So, the point is here that using links in your ORACLE_HOME path is really something to be avoided.

Native SSIS does not support secure ftp. Therefore, a typical way around this is to use an execute process task in SSIS that calls pscp (or similar process) to transfer files. In Development you may be running as a different user than the SQL Agent process. Secure FTP/copy when run the first time may result in the need to deposit a digital fingerprint in a file under the user account that is performing the secure copy. When you invoke in BIDS the first time you will see a popup window when this happens asking for permission. However, when you run from an SQL Agent job the question goes unanswered and the job hangs. In order to acquire the digital fingerprint file for the SQL Agent user account invoke pscp and use Windows RUNAS and specify the user that the SQL Agent service runs as.

i.e.

RUNAS /user:DOMAIN\sqlgentaccount "C:\Tools\pscp.exe -l user -pw password "E:\source_file"
"remote_acct@remote_ip:/dir/dest_file""

There may be a way to avoid this using a pscp option. If you know of one please post your idea.

 

It's always a good idea to validate your business proposition by answering a simple question. In our case I will tackle the question of why it might be a good idea to hire a Remote DBA service.

Let's face it, Database professionals are expensive to hire and maintain on your staff. Some companies cannot afford to pay a full time salaried database professional yet they use databases in their organization and the skill becomes a need and they need somewhere to turn. If your business is lucky enough to be able to afford a database guy it sure would be nice to have another when he/she is in vacation/sick/etc. And, truth be told, no one knows it all and having some extra minds in the fold comes in handy when a problem has your staff stumped.

Having a Remote DBA service procured and ready to help can be a strategic move for your business. Feel free to browse our service offerings or our project listing and drop us a line if you would like to explore the idea of hiring a Remote DBA Service.

If two database sessions are deadlocked SQL Server will by default select the lowest cost rollback as the "deadlock victim". There is a way to influence SQL Server if you would like a process to avoid being a deadlock victim. Simply, add a call to your code "SET DEADLOCK_PRIORTY HIGH". This can either be called from your database session or from within a stored procedure that you call. Not, that this should be used in specific situations. If you use it everywhere you will defeat the purpose since if two sessions have this setting and run into a deadlock situation SQL Server will default back to the lowest cost algorithm.

 

This one has bit me on more than one occasion. While running emca the password prompt for dbsnmp user keeps cycling over and over again. The fix is to apparently enclose the password in quotation marks (i.e. "password") upon entry. Not sure why this is needed but it does allow you to get passed the problem. I suspect this is somehow related to the password that is being used.

I'm sure you've heard all the Cloud buzz but what does it mean to you?

First, let's try and clear the air as to what this Cloud thing is. Every computing system has three primary resource components (these are CPU, Disk, and Memory). The Cloud allows you to enhance your capacity to any or all of these components. In some cases a Cloud provider (like Google and Salesforce.com) will provide a software application built on top of the aforementioned resource components. This is sometimes called Software As A Servers (or SAAS).

There are several providers of Cloud services. The largest providers are Amazon, Microsoft, Rackspace, and VMWare.  The largest SAAS providers are Google and Salesforce.com.

If Cloud services interest you but you don't know where to start I would suggest you start with a small focused project or task and work from there. Taking this approach makes the whole thing less intimidating and allows or some short term success (or failure) and limits your risk. For example, let's say you want to have an offsite copy of some critical data. Not all of us have remote locations that we can copy data to, but "renting" some hard drive space in the Cloud makes this achievable where it may not have been before. An office fire could destroy everything and having an automated solution where the critical data is prepared and copied each day is a great insurance policy for your business.

If you're looking for help feel free to drop us a line and we can discuss what you have in mind.

 

 

• Utilizing SSIS to pull Used Equipment for resale and generate HTML file to be posted on company website
• Debugging perl module that pulls in Marketdata from Bloomberg
• Oracle SAN migrations from EMC to NetApp
• Oracle EM DBconsole configuration
• SQL Server Index rebuilds as dictated by SSIS proprietary index evaluation
• Deployed this new Blogging extension to our website (like it?)

Tried SQL Developer to access a Sybase database for the first time today. All  I had to do was go to Tools | Preferences | Third Party JDBC Drivers and add my jTDS driver. After that just configured a connection to use this driver and I was in. This worked fine for qerying data and viewing stored procedures, etc. I was not able to edit the...

Instead of moving it is much quicker (especially if your TEMP tablespace is large) to create a scratch TEMP tablespace, set the scratch as the default, and then recreate the TEMP tablespace. You will need to drop the original TEMP tablespace, but the creation of the TEMP datafile is instantaneous. once you have the new TEMP tablespace created you...

Moving a database in SQL Server can be done by:

1. Offlining the database
2. Copying the MDF file
3. Attaching the datafile at the new server

You do this by  calling the sp_attach_single_file_db procedure
i.e.
exec sp_attach_single_file_db @dbname='MY_DB', @physname='H:\SQL2\MY_DB.mdf'
SQL Server will automatically create a new logfile...

• Planning for a EMC to NetApp SAN migration for a large Oracle environment
• Oracle Apex installation along with SSTS System Monitoring application install
• Implementing an ETL process to pull Equity Beta calculations
• Implementing an ETL process to pull used equipment listings and posting results in HTML format
• Troubleshooting performance issues,...

This is one of those tasks that you tend to forget. After you create reports in Microsoft SSRS you need to assign assess to users in your domain. A common way of accomplishing this is to assign these via a Windows domain user or group. Most users will need the Browser role that allows them to view your reports. You can assign this at a folder...

ok, this one has burned some of my time a couple times now.
When you are configuring Enterprise Manager in a Windows environment you need to modify the Windows Local Security Policy to "Allow Logon as Batch" for whatever user is running the Oracle DB Console service. Otherwise, you will be unable to assign preferred credentials and thus will be...

If you are running Oracle on Windows and are using a Windows domain account subject to password expiry you need to remember a few follow up tasks after your password has been changed.

• Make sure any Windows scheduled tasks have the new password. You should only need to change one scheduled task and all get the change
• Go into EM/DBConsole preferences...