Oracle Internet Directory administration cheatsheet

What tools and files help one administer Oracle Internet Directory (OID)? Here’s a rundown on command line, GUI, OEM and log files offerings. This post applies to OID installed with Oracle Identity Manager 10.1.0.4.0 and 10.1.4.2.0. I haven’t used earlier versions.

opmnctl found in $ORACLE_HOME/opmn/bin will stop and start all services that are part of the Oracle application server (OAS) installation, of which OID is part. I will use $OH rather than $ORACLE_HOME for the rest of this post.

Just want to use OID and don’t want the over head of OAS? Use oidmon to start the monitoring component of oid services and then use oidctl to stop and start specific OID components. Both are found in $OH/bin.

Need to update OID entries from the command line? $OH/bin has several ldap compliant ldap tools: ldapsearch, ldapmodify, ldapadd, ldapdelete, ldapbind, ldapcompare and more.

You can configure OID replication with the OID gui installer. But after installing, you can administer OID replication with remtool, found in $OH/ldap/bin. The remtool utility allows you to configure OID replication done with LDAP and OID replication done with trigger based Advanced Replication.

If you have multiple OID directories, you may want to compare the contents to see whether the entries match, or whether subsets of OID directories match. Located in $OH/ldap/bin, oidcmprec allows one to do these comparisons and even reconcile two different directories by updating a target to match the source!

You can quickly generate LDIF (LDAP data interchange format or lightweight directory interchange format) text files containing the using the ldifwrite utility in $OH/ldap/bin. Such files can be used with any LDAP compliant server, not just Oracle’s.

$OH/ldap/bin has a number of other utilities, some which are shell scripts, some executables. There are password changing utilities, bulk updating utilities and configuration utilities.

For X-windows style administration, try $OH/bin/oidadmin.

Naturally, Oracle Enterprise Manager (OEM) has an interface for OID, but you’ll have to run OID with Oracle Application server to be able to do this. Running OID just with oidctl is not sufficient. When running just with oidctl, you can use OEM to monitor whether OID is up or down, but there are no screens for additional administration.

Lastly, you may wonder where OID specific log files are located. Most can be found in $OH/ldap/log. oidmon, the ldap server, replication components and remtool all output their log files here.

file permissions: 10.2.0 on unix, vista issues

A critical element in computer problem trouble shooting is checking correctness of permissions. Are file permissions correct? What about database permissions? Group membership of operating system ids? And so on. We computer geeks will be doing more of this now that software comes installed with increased attention to security and permissions.

Here are two new wrinkles I ran across today, both involved with installing Oracle.

On a unix host I was trying to enable a unix user id to be able to login and use sqlplus and export/import utilities, but I did not want this id to belong to the dba unix group. I was working on Solaris with Oracle 10.2.0.4.0. However, each time the user invoked sqlplus, “Permission denied” errors resulted. Clearly a permission problem, but I did not want to perform trial and error chmod commands on various files. Metalink yielded an exact match for my problem. Permission Denied Errors for users other than “oracle”, docid 443638.1. Turns out that in Oracle 10.2.0 and higher, the Oracle installers sets file permissions quite restrictively for security reasons. There is a script that one can run to relax permissions. It’s $ORACLE_HOME/install/changePerm.sh.

The other permission issue concerned installing an Oracle client on Vista. (Side note: you have to do Oracle 10.2.0.3 or higher to get Oracle client to work on Vista.) I was using an automated script to install the client and had added to the script some file copy statements to get the correct sqlnet.ora, tnsnames.ora and ldap.ora files on the client machine. Vista didn’t like that since the User Account Control (UAC) feature was turned on. UAC blocks file copy/delete via scripts, and throws up lots of confirmation dialogs if you are manipulating files via GUI tools. But you can disable UAC. My Digital Life and The How To Geek explain how on their blogs. For good measure, I’ll copy and paste what they provided below. Involves updating registry keys.

Disable UAC
C:\Windows\System32\cmd.exe /k %windir%\System32\reg.exe ADD HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System /v EnableLUA /t REG_DWORD /d 0 /f

Enable UAC
C:\Windows\System32\cmd.exe /k %windir%\System32\reg.exe ADD HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System /v EnableLUA /t REG_DWORD /d 1 /f

windows, sysinternals, processes and disk space

If you work on both unix and windows, you may find yourself wishing for unix tools like du, ps, df, proc and fuser to use on windows systems. Such tools make it easier to investigate process activity and disk utilization.

Sysinternals and vbscript to the rescue!

Sysinternals is the website run by Mark Russinovich, now at Microsoft. The utility process explorer gives you everything ps does and more! With process explorer, you can quickly find if a particular process is locking a file, for example. There’s a windows version of du that works really well. With du, you can find out what directory is consuming space very quickly. And there’s many other tools that I have yet to explore.

Lastly, the unix utility df makes it easy to see space consumed by each mountpoint from the command line. In windows, you can do this via the GUI by going to My Computer and checking there. But there’s no easy way to do this from the command line with the built-in operating system commands. Here’s a vscript file to help. To run it, place the code in a file, say get_drive_utilization.vbs. Then invoke cscript from the command prompt like this:
C:\> cscript get_drive_utilization.vbs

' Windows scripting host script in VB Script
' to list Used, Free, Total and % Free on all fixed disk drives.

Function ShowDriveList

	Dim fso, d, dc, s, n
	Set fso = CreateObject("Scripting.FileSystemObject")
	Set dc = fso.Drives

	s = s & "DriveLetter | ShareName | DriveType | Used | Free | Total | % Free " & Chr(10) 

	For Each d in dc
		n = ""

		If d.DriveType = 3 Then
			n = d.ShareName
		ElseIf d.IsReady Then
			n = d.VolumeName
		Else
			n = "[Drive not ready]"
		End If

		Select Case d.DriveType
			Case 0: t = "Unknown"
			Case 1: t = "Removable"
			Case 2: t = "Fixed"
			Case 3: t = "Network"
			Case 4: t = "CD-ROM"
			Case 5: t = "RAM Disk"
		End Select

		If t = "Fixed" Then
			s = s & d.DriveLetter & " | "
			free_space = FormatNumber(d.AvailableSpace/(1024*1024*1024), 2)
			total_space = FormatNumber(d.totalsize/(1024*1024*1024), 2)
			used_space = formatnumber(((d.totalsize - d.availablespace)/(1024*1024*1024)), 2)
			pct_free = formatnumber(free_space / total_space, 2)
'			s = s & n & " | " & t & " | U: " & used_space & " GB | F: " & free_space & _
'				" GB | T: " & total_space & " GB | %Free: " & pct_free & Chr(10)
			s = s & n & " | " & t & " | " & used_space & " GB | " & free_space & _
				" GB | " & total_space & " GB | " & pct_free & Chr(10) 

		End If

	Next

	ShowDriveList = s

End Function

a = showdrivelist
wscript.stdout.write a

Flashback – Wow that was easy

I got a call from a developer today with a problem. Seems an end user had inadvertently updated records incorrectly using an admin screen that facilitates bulk updates. Could I make available the data in a particular table as it was 45 minutes ago? This was Oracle 9i.

Flashback query to the rescue! After reviewing the flashback feature, I was able to do the following:
1. Create a dummy user in the target database.
2. Run exp using the flashback_time parameter to export data from about 45 minutes ago.
3. Ran imp using the newly created export dump and targeted the new user schema.
4. Ran a grant statement so that the developer could do selects on both the real table and the dummy table with old data in order to do comparisons.

Easy! Kinda like a Rachel Ray recipe…

Alter that session for a work around!

This is ugly, but sometimes you need to get code working after a migration from 9i to 10g that somehow got overlooked or where you have run out of time to tune sql or statistics.

In the past 4 years, I’ve seen this deployed twice to fix small issues that went unnoticed during testing.

In one case, a query that had been tuned for the 9i optimizer did not work well with the 10g optimizer enabled. The rest of the queries did work fine. In another case, a seldom used delete trigger included a query against v$session that began using parallel query with the 10g optimizer. As a result, the select into clause of the query failed when the select into began fetching multiple rows.

The ugly workaround to get things working fast? Revert the optimizer only within the poorly performing block of code.

alter session set optimizer_features_enable=”9.2.0″

Use execute immediate for PL/SQL code.
execute immediate ‘alter session set optimizer_features_enable=”9.2.0″ ‘;

If you want to be extra careful, you can query the value of optimizer_features_enable, store it in a variable, and then run alter session again to reset the value back to the original setting immediately after the problem code completes.

Note that this is a temporary solution, and it would be better to identify the root cause of the problem and get things to work with 10g.

Automate Outlook Appointments w./ MS-Access

Do you keep birthdays in an MS Access database? Or any other database, for that matter? Have you wanted to get them into recurring Outlook appointments so you could remind yourself of the birthdays? Were you too lazy to enter them manually?

Here’s VBA code to automate a one-time addition of them into Outlook.

A few notes:
1. You’ll need to be familiar with the VBA programming environment. If you are in Outlook, alt-F11 gets you there fast.

2. Outlook VBA projects appear to be stored in a single file. Makes sense because Outlook does not fit the document based approach of Excel and Word. However, you can import and export projects, and I’m sure there is a way to control the location of the Outlook project if you really want to.

3. Be sure to have the DAO library references on in order for this to work. A default Outlook VBA project won’t have these turned on. You do this in the VBA IDE by choosing Tools – References and searching for the DAO library and checking it off.

Here’s the code.

Sub add_bdays()
Dim the_sql As String
Dim myDB
Dim ol
Dim myItem
Dim body_text As String

' Here's my SQL statement. Yours will vary, of course.
' Note the format command needed to order dates within the year.
the_sql = "SELECT Format([DOB],""mm/dd"") AS month_day, MainList.DOB, MainList.DOD, MainList.LName, MainList.FName, MainList.Street1, MainList.Street2, MainList.City, MainList.State, MainList.Zip, MainList.Country, MainList.HPhone, MainList.EMail "
the_sql = the_sql & "FROM MainList "
the_sql = the_sql & "WHERE (((MainList.DOB) Is Not Null)) "
the_sql = the_sql & "ORDER BY Format([DOB],""mm/dd"")"

Set ol = CreateObject("Outlook.Application")
Set myDB = DBEngine.OpenDatabase("D:\path\to_your\database.mdb")

Set rs = myDB.OpenRecordset(the_sql)
rs.MoveFirst

Do Until rs.EOF
Set myItem = ol.CreateItem(olAppointmentItem)
myItem.Subject = "BIRTHDAY: " & rs.fname & " " & rs.lname
myItem.Duration = 1
myItem.Start = CDate(rs.month_day & "/09 9:00:00 AM")
myItem.Location = "Ben's desk"
body_text = myItem.Subject

body_text = body_text & Chr(13) & rs.Street1
body_text = body_text & Chr(13) & rs.Street2
body_text = body_text & Chr(13) & rs.City & ", " & rs.State & " " & rs.Zip
body_text = body_text & Chr(13) & rs.Country
body_text = body_text & Chr(13) & rs.HPhone
body_text = body_text & Chr(13) & rs.Email

myItem.Body = body_text
' Code for setting appointment recurrence.
Set objrecurrence = myItem.GetRecurrencePattern
objrecurrence.RecurrenceType = olRecursYearly

' I found I had to specify when the recurrence start date was. Otherwise the start date got entered incorrectly.

objrecurrence.PatternStartDate = CDate(rs.month_day & "/09")
objrecurrence.PatternEndDate = #12/31/2015#

myItem.ReminderMinutesBeforeStart = 2880
myItem.ReminderSet = True

myItem.Save

Set objrecurrence = Nothing
Set myItem = Nothing
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set myItem = Nothing

End Sub

SSIS Cheatsheet

I infrequently use SSIS, but once in awhile I need to edit a package. This is harder than it was with SQL Server 2000, in which one could crank up Enterprise Manager, go to the target instance, drill down to a DTS package and start editing away, with changes saved directly to the database.

Here’s my newly created cheat sheet:

=================================================================
DISCLAIMER
=================================================================
SSIS has lots of functionality and this is just a very bare bones description of opening an existing package. Note that this does not discuss deploying multiple packages all at once (deployment wizard) or details on editing and debugging SSIS packages. Nor does it weigh the merits of
storing SSIS packages on the file system vs in the database vs SSIS store.

=================================================================
BIG PICTURE
=================================================================
SSIS Packages are eventually get stored and executed within SQL Server. They can be stored on the file system or within the SQL Server database, typically in MSDB.

SSIS Packages get edited in Visual Studio. To do that, you must get a copy of the SSIS package on to the file system. Several ways to do that. From within Visual Studio, you can create a new SSIS package, or you can choose Project – Add existing package and get one directly from SQL Server or a file system location. Or you can go to SQL Server Management Studio and export there first and then go into Visual Studio.

When done editing in Visual Studio, one must deploy to SQL Server. There are a few ways to do that. One can use the deployment wizard, or import the package using SQL Server Management Studio’s import package, or use the command line dtutil tool. There is also a dtexec for commandline execution of SSIS packages.

Once deployed, SSIS packages can then be run in SQL Server via jobs managed by SQL Server Agent.

===============================================================
SIMPLE WAY TO EDIT EXISTING PACKAGE
===============================================================
Step 1: Start Visual Studio and Make a New Project
File – New Project
Integration Services Project
Use Location to create directory structure meaningful for your project and team’s coding conventions.

Step 2: Add an Existing Package to the Project
Project – Add and Existing Package
In the Add Copy of Existing Package dialog:
Package Location is SQL Server
Server (local) if you are logged on to the SQL Server machine. If working from laptop, identify the SQL Server instance and include port if necessary
(e.g. USEOMAPD357,1113)
Authentication: Windows
Package Path: Click on . and locate package.
Then click OK.
Nothing will change on screen, but a file will be on the file system containing the package which you can open in the next step.

Step 3: Open the package for editing.
File – Open
locate the newly exported package.

Step 4: Edit the package.
Use Visual Studio as needed to edit.
You can test the package as needed.
You will find the SSIS programming metaphor is roughly similar to DTS and other Microsoft visual editing tools.
Save changes to package and they will update the file on the operating system.

Step 5: Deploy the package
Start SQL Server Management Studio.
Connect to Integration Services
Click on Stored Packages
Click on MSDB
Right Click and choose Import Package.
Package Location will be File System
Use Package Path to identify the file you edited in Step 4 above.
Click on Package Name and provide a name or accept the existing one.
Make protection level Encrypt Sensitive Data with User Key.

Suffering from Enlarged Oracle Homes?

Are you a diligent DBA who applies quarterly CPU patches whenever possible? Are you noticing how the $ORACLE_HOME directory gets larger and larger over time? That’s ’cause the opatch utility creates backup files needed for rolling back patches in a directory called $ORACLE_HOME/.patch_storage. Opatch will not eliminate old files here when you apply patches. However, starting in Oracle 10.2.0.x and higher, there is now a switch for opatch: opatch util cleanup. This will remove backup files from older patches that are no longer needed. Search on metalink for more details. This cleared up 2GB for me yesterday!

Application Express

I had time for fiddling around with Oracle Application Express (APEX) and could justify it as part of Oracle 11g product evaluation. Some notes follow.

When you use the database creation assistant with 11g, it generates a script called apex.sql, which in turn executes $ORACLE_HOME/apex/catapx.sql. That seemed to work OK, except there is a fair amount of configuration afterward. And Oracle Text really needs to be installed in order for apex help files to display correctly.

So here’s my list of stuff to do if you want to install Application Express.
1. Install Oracle Text first. $ORACLE_HOME/ctx/admin/catctx.sql.
2. Run $ORACLE_HOME/apex/catapx.sql. Review the log file and make sure the entry in DBA_REGISTRY is valid.
3. Review and then execute the steps in the Oracle Application Express documentation. You want the Application Express Installation Guide. It explains quite a bit, including how you can run either the Oracle http web server (Apache) or the web server that’s inside the database called embedded PL/SQL gateway. Pick which web server you want and follow the appropriate instructions. Be sure to remember the password you supply when running $ORACLE_HOME/apex/apxconf.sql. You’ll need it to do admin work with APEX.
4. Something not mentioned anywhere that I saw was the listener.ora change you need to ensure that embedded pl/sql gateway works. Adding this in the DESCRIPTION_LIST worked for me:
(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Thanks Tim Hall of www.oracle-base.com for pointing this out on your web site!
5. At point I was able to log in using http://[hostname]:8080/apex/apex_admin, using user ADMIN and the password supplied in step 3 above!

Transactional DDL

I found this thread on comp.databases.oracle.server interesting.

It’s a discussion of whether or not Oracle should support the ability to rollback a DDL statement. I never considered this to be a possibility or even something that I’d need. But I like the comparison amongst database products from different vendors. Such comparisons can help one understand better the general problems that RDBMS tools solve. One thing that came up in the thread was whether SQL Server supports transactional DDL. I had to verify for myself, and sure enough it does. Here’s a sample sqlcmd session:

1> begin transaction
2> create table foo (f1 varchar(10))
3> go
1> rollback
2> go
1> select * from foo
2> go
Msg 208, Level 16, State 1, Server USEOMAPD357, Line 1
Invalid object name ‘foo’.
1> begin transaction
2> create table foo (f1 varchar(10))
3> go
1> insert into foo values (’harry’)
2> go

(1 rows affected)
1> select * from foo
2> go
f1
———-
harry

(1 rows affected)
1> rollback
2> go
1> select * from foo
2> go
Msg 208, Level 16, State 1, Server USEOMAPD357, Line 1
Invalid object name ‘foo’.
1> exit