dtutil, named instance and package import

I was trying to get an SSIS package imported into a named instance using the command line utility dtutil and couldn’t get it to work.

This command had been working just fine with a default instance.
dtutil /File userdbbackup.dtsx /DestServer hostname /Copy DTS;"MSDB\Maintenance Plansuser\dbbackup"

But when running the same command on a named instance, I got the following:
dtutil /File userdbbackup.dtsx /DestServer hostnameinstancename /Copy DTS;"MSDB\Maintenance Plans\userdbbackup"

Microsoft (R) SQL Server SSIS Package Utilities
Version 10.0.1600.22 for 32-bit
Copyright (C) Microsoft Corp 1984-2004. All rights reserved.

Could not create SSIS Service Storage object because of error 0×800706BA.
Error (0×800706BA) while checking for the existence of package “Maintenance Plansuserdbbackup” in the SSIS package store.
Could not create SSIS Service Storage object because of error 0×800706BA.
Description: The RPC server is unavailable.

The solution was changing the DTS flag to SQL in the /COPY parameter. I also had to change the folder location by removing MSDB.
dtutil /File userdbbackup.dtsx /DestServer hostname /Copy SQL;"\Maintenance Plans\userdbbackup"

This means the package is getting stored within MSDB in the SQL Server rather than the SSIS Package Store.

Note that the commands above do not specify username and password. The commands were run on the local host with a privileged user that had a trusted connection to the DB.

Here are some links on the topic of getting the SSIS Package Store to work with Named Instances:  Robert Davis explains and Tim Cullen covers similar ground.

If I understand the above links correctly, if I manipulate the XML files as they indicate, the dtutil /COPY DTS can be made to work with named instances.

By the way, this concise dtutil tutorial helped steer me in the right direction. It even mentioned dtexec and dtexecui, both new to me.

SQL Server connection protocols and named instances

I’ve been working on a project that involves automating tasks on SQL Server installs. Things got a bit messy when working with multiple instances on the same host.

To automate sql scripts via a VB.NET program, I ended up using shared memory connections. The code will always run locally on the host where the instances reside, so no problem there. I learned the following about SQL Server connection string behavior:

1. This will always use the default instance with a shared memory connection. Parentheses are required.
Data Source=(local); Integrated Security=True

2. This will allow a connection to a named instance via shared memory. Note there are no parentheses.
Data Source=HOSTNAME\NAMEDINSTANCE; Integrated Security=True

3. This will force a connection over TCP/IP on the port specified, provided the SQL Server Network Configuration has been set to the port indicated.
Data Source=HOSTNAME\NAMEDINSTANCE,port; Integrated Security=True

The above connection strings can be used in data access code in VB.NET and other languages.

Want to force a shared memory connection from sqlcmd? You can append the prefix lpc:
sqlcmd -S lpc:HOSTNAME\NAMEDINSTANCE -E

Although
sqlcmd -S HOSTNAME\NAMEDINSTANCE -E
does the same thing when executed from the local host. When executed remotely, you likely will need the port to appended after NamedInstance.

For other SQL Server protocols such as Named Pipes, there must be additional abbreviations, but I haven’t dug into that…

Lastly, one can tell from within SQL Server what protocols are used by what sessions. The view sys.dm_exec_connections shows that, specifically the net_transport column. See the below query which joins to sysprocesses.

select d.net_transport, s.spid, s.program_name, s.nt_username, s.hostprocess, s.lastwaittype, d.client_tcp_port, d.protocol_type, d.protocol_version
from sysprocesses s
inner join sys.dm_exec_connections d
on s.spid = d.session_id

Patching Blues

No matter what people tell you, keeping Oracle database on most recent security patches can be tricky.

Here’s how my efforts went this afternoon.

I wanted to apply the Jan 2010 Oracle security CPU patch to a instance running Oracle APEX. Jan 2010 CPU patch has a specific APEX patch as well as an Oracle database patch. I learned the hard way that the Oracle database patch needed to be applied first after encountering errors in the APEX patch. Maybe I should have guessed that there was a dependency here, but I glossed over the “Known Issues” section in the APEX patch and it wasn’t mentioned elsewhere…

I got everything completed on my target Windows machine: DB patch then Apex patch.

On to an Oracle database running on Solaris. I applied the Jan 2010 CPU DB patch. Went fine. Ooops, no it didn’t. At the end it spit out some errors that said they were covered in Metalink note 353150.1. Turns out the Metalink note says the errors are false positives, but it took about 15 minutes of reading through the note and running a small test to verify that.

So on to the APEX patch on the Solaris DB. My test instance had been running just fine, but it ran out of shared memory when applying the patch. I retreated momentarily and shutdown some other instances on the machine, add a bit more to the MEMORY_TARGET parameter and fired up the instance. I applied the patch again.

Still errors on Solaris. I looked at the Jan 2010 CPU documentation again. For Solaris, it appears that the bug fix I need is only in the new PSU Jan 2010 patch and not the CPU one. I hadn’t tried a PSU patch, but since this is a lab instance, I figured I’d try it out.

I uploaded the PSU patch and started in with that. The opatch commands kept coming back with an error, so I learned that the 11.1.0.6 opatch utility was no good for PSU patches. I needed the most recent opatch utility. I downloaded and applied that.

So ran the PSU patch on Solaris, which took over 1 hour on fairly fast hardware. I followed the post installation steps, which all went fine. Interestingly, the PSU patch did not have the same OS issue that the CPU patch during the verification section. Then I went back to the APEX patch on the same Solaris database instance. That went through without a hitch!

In sum, to patch Oracle APEX on windows and Solaris, I had to
> Apply Oracle on Windows CPU database patch.
> Apply APEX patch. Windows now done.
> On Solaris, download and apply new OPatch.
> On Solaris, apply PSU patch, including post-install steps.
> On Solaris apply Apex patch. Solaris now done.

Automating all this is possible, but certainly requires work if you do the scripting yourself, or money if you pay for OEM’s patching option or for a product like GridApp.

Oracle Apex 4.0 Demonstration Site Available

Oracle has made available a demonstration site of Apex 4.0. It’s hosted in the Amazon Elastic Compute cloud, adding further credibility to Apex as a cloud computing platform. Sign up here.

I signed up for an account and took a 15 minute test drive. Here a few quick things I noticed:

1. There are a number of screens under a new Team Development tab. These are all straightforward, but provide ready made data entry screens and reports for the information needed to track a development project. Bug lists, new feature lists, tasks, etc. It’s very handy to have these right in the product.

2. I was most curious to try out the new interactive report features. Compound expressions for filters are available. These were implemented as a separate choice from the existing filter feature. I imagine doing this means that existing 3.x interactive reports will run without any changes in 4.0. The new feature uses an interface similar to the 3.x one for doing computed columns. When creating an expression, one references existing fields in the report by using a letter instead of the column name. It allows one to put in complex AND and OR logic with lots of functions as well. I also tried the GROUP BY feature, which worked fine and was intuitive to deploy. Finally, I saw that under save report there is a “Public” checkbox, which would allow a user to make a saved report that is visible to other users. That is really needed.

3. The initial screen for editing a page now uses a tree view to navigate all the objects on a page. Once you get to an object you wish to edit, the edit pages look quite similar to the 3.x ones.

4. I made a few quick graphs and as promised a newer version of AnyChart is available. The graphs look great!

I hope to port some existing apps to the trial web site over the next few weeks. Note that Oracle will erase the whole site in order to put a phase II beta, so be prepared for your work to get erased if you try this site out!

TKProf alternatives

A recent thread on oracle-l listed several alternatives to the venerable tkprof tool. All of these alternatives have been around for several years, but they were new to me.

I haven’t used any of them yet, but I’m posting this so that I have them at the ready when needed!

trcanlzr.sql -> a tool available on My Oracle Support (MOS) at Doc ID 224270.1. It’s a souped up version of tkprof, apparently.

SQLTXPLAIN -> On MOS at Doc ID 215187.1. It’s a tool that analyzes one SQL statement at a time.

TVD$XTAT -> http://antognini.ch/top/downloadable-files. Created by a gentleman named Christian Antognini.

OraSRP -> http://www.oracledba.ru/orasrp. Created by a fellow named Egor Starostin.

Method-R profiler -> This is a commercial tool available at http://www.method-r.com/software/profiler from the Method-R company.

Managing OEM Repository Growth

In my shop, we completed installing OEM agents on all the nodes to be monitored a few months back. However, the MGMT_TABLESPACE continues to grow. I figured that could be reasonable since I didn’t know the details of data retention parameters that govern when data gets purged. If a purge parameter was set to 6 months, but we had only reached our high number of targets 3 months ago, then naturally the database would keep growing.

I found these two links that explain purge parameters nicely.
How to change retention parameters for metrics history

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=430830.1

How to change retention parameters for job history

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=604978.1

There are three tables that contain the bulk of the data in the OEM tablespace MGMT_TABLESPACE. They are MGMT_METRICS_RAW, MGMT_METRICS_1HOUR and MGMT_METRICS_1DAY. They contain partitions that are named after time intervals, and OEM takes care of adding and dropping them. The default retention period for the raw data is 7 days, for the 1hour aggregate is 31 days and for the 1day aggregate is 365 days.

If you want to change the 7 day default period for MGMT_METRICS_RAW to 3 days, you must insert a record into the MGMT_PARAMETERS table to add the parameter mgmt_raw_keep_window.
INSERT INTO MGMT_PARAMETERS (PARAMETER_NAME, PARAMETER_VALUE) VALUES (‘mgmt_raw_keep_window’,'3′);
The companion parameters for the other two tables are mgmt_hour_keep_window and mgmt_day_keep_window.

Make Your Reservation for that Port

Not a port of call, not port wine, but computer ports!

A colleague of mine tracked this down on the microsoft site.

To ensure that your Oracle Listener or SQL Server database starts correctly, you want to avoid other software starting up and grabbing the port needed by Oracle or SQL Server. In my shop, we’ve seen backup software, among others, grab a port and then prevent SQL Server from starting.

Here’s the link on how to do this:

http://support.microsoft.com/kb/812873/en-us

It involves making a registry edit to the key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters and then adding a new ReservedPorts. The port numbers added here will only be used if specifically requested by software.

This will not prevent conflicts if you configure two separate programs to request the same port. But it will save you from Windows automatically assigning the port number in question to software that did not specifically request a port.

OEM: Obscure Tools for Tough Problems

Two aspects of OEM (Oracle Enterprise Manager) that I’m getting more acquainted with…

Scripting
Even though OEM automates tasks and provides a GUI interface to make tasks easier, you still need scripting. You can use the OEM gui to fire off a SQL statement against 50 different databases. But what if you need to change the value OEM stores for the password for the database user with which OEM monitors all those instances? What if you want to change the monitoring threshold on a tablespace in 20 databases?

You need OEM’s command line scripting interface to pull off tasks like those. It’s called, gasp, emcli. You can get it configured quite quickly on your OEM management server. It can also be run from remote laptops, but that seems like a security risk to me. Interestingly, the emcli in OEM version 10.2.0.5.0 seems to have a wider variety of features than does emcli in 10.2.0.4.0 OEM. An example is a bunch of switches in the change password command that make it more secure.

Enterprise Manager Command Line Interface manual provides all the detailed documentation needed.

Diagnosing Problems: EM Diag Kit
Keeping OEM happy involves endless trips to Metalink to identify issues with various Oracle versions and configurations. Lately I’ve been trying to solve why the OEM agent stops and restarts itself on some hosts. My efforts with the folks at Metalink led today to a utility called the EMDiag Kit. This little dynamo scans the OEM repository to help the support folks figure out what’s going on with various OEM components. If you think a little EM diagnosis is needed on your OEM system, hop on to Metalink and check out article EMDiagkit Download and Master Index, Doc ID 421053.1.

The Elusive Oracle ILM Assistant

A few years back, I began seeing Oracle web sites trumpeting the arrival of Oracle Information Lifecycle Management or ILM. ILM was a buzz word in computer trade magazines. The concept seemed to boil down to: you have tons and tons of data, here are some ways to classify and manage it all appropriately, and get rid of older stuff or move it out of the way so your system isn’t bogged down any longer.

Back in the days of Oracle 9i, Oracle ILM appeared to use existing features to enable the Oracle database to put older data on cheaper hard disks, even if the old data and the current data resided in the same table. That was accomplished by using partitions within tables. The web sites also mentioned something about an ILM Assistant, but I never spent enough time to be able to track it down.

Now Oracle 11g database is current and at last I’ve come face to face with the ILM Assistant. It’s still pretty hard to find.

Read a quick overview of ILM here. And this download web page is where you can get hold of the install sql scripts. Here’s a user guide. And Chapter 5 of the VLDB Guide has information as well.

How do you do the install?
1. You need to have Oracle APEX working on your target instance.
2. If you want to run the ILM Assistant with the demonstration data, you need to install the Example schema SH. The example schemas come on a separate download from the Oracle Database installer.
3. Then obtain the ILM Assistant installation zip files mentioned above. You simply follow the steps to run sqlplus to execute sql scripts. Same for the demo data if you want that.
4. Once that is in place, you can access ILM Assistant from a web browser.

I don’t have enough time to figure out just how much value ILM assistant adds to the DBA/IT Cost/Man Hour/Quality equation, but the tool does list out partitions and features, and also provides a methodology for creating an information lifecycle. There are dedicated tools from other vendors that just work on ILM. It’s hard to tell whether Oracle ILM offerings amount to much or provide only a thin veneer on partitioning. I may have an opportunity to find out as we grapple with a large database at work.

Backup and Recovery with 11g Database Control

11G Oracle Enterprise Manager Database Control now has an extensive GUI based interface to RMAN. You can view RMAN backups, run RMAN backups, check on status of jobs, schedule RMAN jobs, all within the OEM db control GUI.

But wait, there’s more!

There is also something called Data Recovery Advisor (DRA), which will AUTOMATICALLY detect database failures that require recovery, and will suggest multiple recovery approaches. It can then automatically create the needed RMAN script and then run it for you if you wish! Of course, you need to be in archive log mode and you need to have a viable RMAN backup complete.

This tool (DRA) can also be accessed from the RMAN prompt with commands like LIST FAILURE; ADVISE FAILURE, REPAIR DATABASE;

PLEASE NOTE: OEM 11g Database Control is different than OEM grid control. Highest version OEM grid control is 10.2.0.5. Database control is the standalone version of OEM grid control. The features discussed here are only available in the 11G standalone database control, not the 10g grid control. Fortunately, you can have both OEM 10g grid control monitoring your 11g database PLUS run the 11g database control. At least it worked for me. I’d like to double-check this with Oracle.

How do you get OEM 11g database control working? Use the emca tool.
If you don’t have it working already, these 10g instructions work for 11g:
Recreate Database Control Repository, by Jeff Hunter (thanks Jeff!)
You might hit this problem, which I did in my test db:
Meta link doc id (733710.1) EMCA on 11g Db Fails With: Listener is Not Up

How do you stop and start database control?
emctl start dbconsole
emctl stop dbconsole
emctl status dbconsole

How do you access database control once running?

https://host:port/em

How do you see DRA within OEM 11g Database Control?
Once you are in 11g Database Control, go to the “Availability” link to see all the backup and recovery options.
DRA can be accessed via the Availability : Perform Recovery : Oracle Advised Recovery section
It can also be launched from “Advisor Central”, a link found in the “Related Links” section on the bottom of many pages.

More links on DRA
Tim Hall’s great DRA review
Using DRA from 2 Day DBA guide
Repairing Data Corruptions from DBA guide