SSTS Blog

Below is a great introduction to the Change Data capture functionality in SQL Server. CDC is a great way to capture data changes to your database without having to code your own triggers and logic. CDC reads the transaction log and so is asynchronous and minimizes contention within SQL Server. CDC can be configured as wide spread as the entire database or can be a specific as one column on one table in your database.

Check out the link below for an understanding on how to implement CDC.

https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/

If you have a reason to change the port that the EM web server is running try the command below (in this case changing to port 1158)

emca -reconfig ports -DBCONTROL_HTTP_PORT 1158

Postgres by default does not allow all network clients access to the databases by simply providing a username and password. The clients must be "registered" via the pg_hba.conf file. See http://www.postgresql.org/docs/8.4/static/auth-pg-hba-conf.html for details. After changes are made to pg_hba.conf the server must be signaled to reload the configuration files using pg_ctl reload

If you add new logins to your environment and want the associated database permissions to flow through to replicas you must create the logins with common sid identifiers. To do so query for the sid for the login from the syslogins table. After you have the sid you can create the login on the replicas as follows:

create login <login_name>, password='<password>', sid='<sid_from_primary>'

After you do this database permissions will flow through from the primaries to the replicas and in the event of fail over users will have their appropriate access.

Snapshot Isolation allows for readers to access data without blocking writers. This is more like how Oracle works and can improve overall throughput of your system. Keep in mind that snapshot isolation will place greater demands on tempdb as before images of data will be stored there in order to support this functionality.

With that said, this is the process to enable snapshot isolation on a database:

ALTER DATABASE <my_db> SET allow_snapshot_isolation ON
ALTER DATABASE <my_db> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE <my_db> SET read_committed_snapshot ON
ALTER DATABASE <my_db> SET MULTI_USER

To verify:
SELECT is_read_committed_snapshot_on, snapshot_isolation_state_desc,snapshot_isolation_state FROM sys.databases WHERE name='<my_db>'

Here is a query to monitor index rebuild progress:

;WITH cte AS
(
SELECT
object_id,
index_id,
partition_number,
rows,
ROW_NUMBER() OVER(PARTITION BY object_id, index_id, partition_number ORDER BY partition_id) as rn
FROM sys.partitions
)
SELECT
object_name(cur.object_id) as TableName,
cur.index_id,
cur.partition_number,
PrecentDone =
CASE
WHEN pre.rows = 0 THEN 0
ELSE
((cur.rows * 100.0) / pre.rows)
END,
pre.rows - cur.rows as MissingRows
FROM cte as cur
INNER JOIN cte as pre on (cur.object_id = pre.object_id) AND (cur.index_id = pre.index_id) AND (cur.partition_number = pre.partition_number) AND (cur.rn = pre.rn +1)
ORDER BY 4

If you have a need to change the passwords for the sysman or dbsnmp users you will also need to edit configuration files used by Enterprise Manager. These files are $ORACLE_HOME/<hostname>.<instance_name>/sysman/config/emomes.properties and $ORACLE_HOME/<hostname>.<instance_name>/sysman/emd/targets.xml

The passwords may appear as encrypted. You can replace with clear text if you change the encrypted directives to FALSE. 

AWR snashots are a useful way to gain insight into what was going on in the database in recent history. Viewing snapshot reports can supply very detailed metrics for a specific interval (default is hourly). One limitation here is when you would like to analyze a specific metric over multiple snapshots. A way to accomplish this type analysis is to query the AWR tables directly. In my case I wanted to review session logical reads over all snapshots to get a feel for overall workload of the database over time. One thing to be aware of is that some AWR values are cumulative and so must be compared to the previous snapshot in order to see what accumulated over the interval. Using analytic functions here allows you to do this easily. See the example below and along with a link for more details on the AWR tables.

select s.END_INTERVAL_TIME, value-lag(value) over(partition by stat_name order by s.BEGIN_INTERVAL_TIME) session_logical_reads

from DBA_HIST_SNAPSHOT s

join dba_hist_sysstat ss on s.snap_id = ss.snap_id

where ss.stat_name like '%session logical reads%'

http://www.nocoug.org/download/2008-08/a-tour-of-the-awr-tables.nocoug-Aug-21-2008.abercrombie.html#script-find-expensive

On occasion I have had to move rman backup pieces around due to disk space shortages, etc. In order to avoid complexities if a restore is required you should re-"register" these backup pieces with rman so that it knows where they are. The CATALOG BACKUPPIECE command serves this purpose.

syntax:

CATALOG BACKUPPIECE '/path_to_backup_piece/filename1',  '/path_to_backup_piece/filename2', ...

After you are done you should follow up with a crosscheck backup command.

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.