SSTS Blog

On occasion I have had to rebuild EM Dbconsole. Using the documented procedures of emca requires that the database be in quiescent mode during the drop of the repository. The process below avoids this constraint:

I got some step missed this evening and took some time check my previous notes

(don't miss any step!)

rm -fr $ORACLE_HOME/<hostname>_<instance_name>
rm -fr $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_<hostname>_<instance_name>

Always On is something I have been researching lately. Several new features are added including the ability to read from the secondary, backup from the secondary, grouping databases into a cohesive group for failover, etc.

As I was reading I came across a quick setup link that brings you through the steps of setting up Always On. This will be useful for first timers:

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/12/17/sql-server-denali-alwayson-hadr-step-by-setup-setup-guide.aspx

And, here's another setup example that is a bit more detailed:

http://www.brentozar.com/archive/2011/07/how-set-up-sql-server-denali-availability-groups/

I've heard the argument that you don't need to worry about fragmentation with Oracle and Locally managed tablespaces. However, I have come across a real world situation where it needed to be dealt with. The situation involved areference table that was being re-populated in full each night and it had grown to several times the size than was required (288MB vs 44MB). Since the table was a lookup table it was being joined with much larger tables in queries and these were wreaking havoc on the database.

In essence, the table involved was synchronized with an external source via the following process:

• Begin transaction
• Delete all rows from the target table
• Select all rows from the source and insert into the target table
• Commiit transaction

 After dealing with the situation I decided to be proactive about detecting the situation moving forward. To do so, I wrote the following query:

select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE",
 round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE"
 from all_tables
 where
  ((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)) > (num_rows*avg_row_len/1024/1024)
  and  owner not in ('SYS', 'SYSTEM')
  and ((blocks*8/1024)) > 10
  

The above query can be used to detect tables that have more wasted space than actual used space and that are at least 10MB in size (you can adjust as needed).

After detecting tables as such you can use the ALTER TABLE... SHRINK space command on the table(s). You need to enable row moviement on the tables in order to run the SHRIN SPACE command.

i.e.

ALTER TABLE ENABLE ROW MOVEMENT

ALTER TABLE SHRINK SPACE

 

 

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:

 #COLUMN_VALUE#

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

 

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,...