Integrating with ORACLE Enterprise Manager

Integrating with ORACLE Enterprise Manager

By Frank Magliozzi and John Symington
Enterprise Manager (EM) is the latest tool from Oracle for routine database administration. Most Database Administrators (DBAs) probably still do the type of work supported by EM using SQLDBA, which is character based and has long been bundled with the database server. Between SQLDBA and EM we have had Server Manager (SVRMGR), which had a graphical user interface but little additional functionality.
Now, with Enterprise Manager, we finally have a database management tool that gives us both basic controls and the ability to add functionality we may require. EM also has the capability of administering all Oracle databases from one console. The console is a Windows product, and at the writing of this article is only available under Windows NT.
Enterprise Manager, like SQLDBA and SVRMGR, does come bundled with the Oracle server (from version 7.3 on) and should not be confused with Oracle's Performance Pack, which is a quite expensive add-on.
Why Integrate with Enterprise Manager?
Our own integration exercise was part of the development of OSCAR (Oracle System Change Administration Repository), a 3rd party tool offering integrated database and application version control and change management. For us, EM integration would, at the very least, make it easier for the DBA to use the product, as they would not need to log in twice. At best, this would enable us to make use of EM's information and services, saving significant effort for us and overhead for users.
We believe for a majority of DBAs the most important benefit of EM integration will be to streamline the activities that are undertaken by a DBA. A single easy-to-use interface and shared user credentials should make it easier to use third party tools, most of which can offer some degree of EM integration. Provided tool vendors are able to deliver a similar look and feel to that of EM, DBA productivity should improve.
Enterprise Manager APIs
In this article we will provide a very brief introduction to Enterprise Manager integration, with a few simple examples.
Customization of and integration with EM can occur through its open OLE Application Programming Interface (API). The simplest interface is one in which an icon can be placed on the EM desktop allowing for the launching of third party or home-grown applications. The other APIs offer the following facilities:

Ability to tap into the security access established when a user logs into EM, as well as the connection details of other services the user has defined.
Ability to Manage jobs. This includes the following:

Job initiation;
Job scheduling;
Job deletion; and
Jobs notification on completion/failure of jobs.

Ability to access details of:

Nodes and services defined in the EM console, and their status.
Objects defined by the DBA in their respective groups.

How Integration is Possible
The simplest integration available is that of making other applications launchable from EM. Under Windows NT, this can be achieved by registering the Launchable application as part of the EM group. The following is sample registration database entries to register the NOTEPAD application with EM:
REGEDIT

; Register will the EM Console
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\NotepadEditor = NotepadEditor
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\NotepadEditor\Types = DATABASE
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\NotepadEditor\Palettes = ThirdPartyApps
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\NotepadEditor\Name = Notepad Editor
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\NotepadEditor\Name\US = Notepad Editor
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\NotepadEditor\Executable = NOTEPAD.EXE
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\NotepadEditor\ExeType = EXEC
The above code is applied against the Windows NT registry by saving it in a file with a .REG extension, and executing the following command from FileManager Run: regedt32 Filename.REG, where Filename.REG is the file with the above entries. The above definitions suffice for the simple cases where no further interaction is to take place between EM and the user application, in the above case NOTEPAD.
We initially setup our own palette, and headed it "Third Party Applications." This was done by creating a text file with an extension of ".REG" and running it using "REGEDT32." The file's contents are as follows:
REGEDIT
; Register will the EM Console
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\NotepadEditor = NotepadEditor
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\NotepadEditor\Types = DATABASE
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\NotepadEditor\Palettes = ThirdPartyApps
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\NotepadEditor\Name = Notepad Editor
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\NotepadEditor\Name\US = Notepad Editor
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\NotepadEditor\Executable = NOTEPAD.EXE
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\NotepadEditor\ExeType = EXEC
REGEDIT
; Third Party Apps palette
HKEY_CLASSES_ROOT\OracleSmpConsole\Palettes\ThirdPartyApps = ThirdPartyApps
HKEY_CLASSES_ROOT\OracleSmpConsole\Palettes\ThirdPartyApps\Name =  Third Party Apps
HKEY_CLASSES_ROOT\OracleSmpConsole\Palettes\ThirdPartyApps\Name\US =  Third Party Apps
We then used this as the palette for all our console launchable applications.
Acquiring Context from EM:
By context, we mean the inheritance of database access privileges. Context is acquired by developing a program that can act as an OLE Server. In the development of OSCAR, we chose to make this program the main menu of the application. This program is also where the OSCAR user is prompted for DB authorization details when launched from outside EM. We wanted the EM user to bypass this authorization process, thus achieving security integration with EM.
An OLE Server application can acquire user credentials from EM through the API's provided by EM. The first we tried, used an OLE method by the name of "SetLogonInfo," which EM calls to pass logon information from the console to the interfacing application on startup of the application. Using C++ this is achieved by declaring an OLE function as shown in the following example:
void CSetLogonInfoDoc::SetLogonInfo(LPCTSTR p_username, LPCTSTR p_password, LPCTSTR p_service, short p_context) 
{
		
	CString buffer = (CString)"SetLogonInfo()\n" + \
                          (CString)"username : " + (CString)p_username + (CString)"\n" + \
		      (CString)"password : " + (CString)p_password + (CString)"\n" + \
		       (CString)"service  : " + (CString)p_service;

	AfxMessageBox((LPCTSTR)buffer);
}
In the above, the username, password, and service are made available through the variables p_username, p_password, & p_services respectively. EM also makes available the context the application was launched in, i.e.: Database, Node, Listener, Nameserver.
The registration entries for the above application are as follows:
REGEDIT
; This .REG file may be used by your SETUP program.
;   If a SETUP program is not available, the entries below will be
;   registered in your InitInstance automatically with a call to
;   CWinApp::RegisterShellFileTypes and COleObjectFactory::UpdateRegistryAll.


HKEY_CLASSES_ROOT\SetLogonInfo.Document = SetLog Document
HKEY_CLASSES_ROOT\SetLogonInfo.Document\protocol\StdFileEditing\server = SETLOGONINFO.EXE
HKEY_CLASSES_ROOT\SetLogonInfo.Document\protocol\StdFileEditing\verb\0 = &Edit
HKEY_CLASSES_ROOT\SetLogonInfo.Document\Insertable =
HKEY_CLASSES_ROOT\SetLogonInfo.Document\CLSID = {1B394160-137F-11D0-B2B8-524153480000}

HKEY_CLASSES_ROOT\CLSID\{1B394160-137F-11D0-B2B8-524153480000} = SetLog Document
HKEY_CLASSES_ROOT\CLSID\{1B394160-137F-11D0-B2B8-524153480000}\DefaultIcon = SETLOGONINFO.EXE,1
HKEY_CLASSES_ROOT\CLSID\{1B394160-137F-11D0-B2B8-524153480000}\LocalServer32 = SETLOGONINFO.EXE
HKEY_CLASSES_ROOT\CLSID\{1B394160-137F-11D0-B2B8-524153480000}\ProgId = SetLogonInfo.Document
HKEY_CLASSES_ROOT\CLSID\{1B394160-137F-11D0-B2B8-524153480000}\MiscStatus = 32
HKEY_CLASSES_ROOT\CLSID\{1B394160-137F-11D0-B2B8-524153480000}\AuxUserType\3 = SetLogonInfo
HKEY_CLASSES_ROOT\CLSID\{1B394160-137F-11D0-B2B8-524153480000}\AuxUserType\2 = SetLog
HKEY_CLASSES_ROOT\CLSID\{1B394160-137F-11D0-B2B8-524153480000}\Insertable = 
HKEY_CLASSES_ROOT\CLSID\{1B394160-137F-11D0-B2B8-524153480000}\verb\1 = &Open,0,2
HKEY_CLASSES_ROOT\CLSID\{1B394160-137F-11D0-B2B8-524153480000}\verb\0 = &Edit,0,2
HKEY_CLASSES_ROOT\CLSID\{1B394160-137F-11D0-B2B8-524153480000}\InprocHandler32 = ole32.dll

; for all apps....values in Types= must match value returned by VaoObject::VaoTypeToIntString
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\SetLogonInfoApp = SetLogonInfo
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\SetLogonInfoApp\Types = DATABASE
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\SetLogonInfoApp\Palettes = ThirdPartyApps
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\SetLogonInfoApp\Name = Set Logon Info
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\SetLogonInfoApp\Name\US = Set Logon Info
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\SetLogonInfoApp\Executable = SETLOGONINFO.EXE
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\SetLogonInfoApp\ExeType = OLE_AUTOMATION
HKEY_CLASSES_ROOT\OracleSmpConsole\Applications\SetLogonInfoApp\CLSID = {1B394160-137F-11D0-B2B8-524153480000}
Of the above entries, the first two blocks of entries are actually generated by the C++ compiler, and it is only the last block that has to be added to enable the launching from EM.
We will not explain the first two blocks, as they thoroughly covered in MSVC++ online help. The following is a line by line description of the last block:

Declaration of the application;
Declaration of the type of application;
Declaration of which palette the icon is to appear in EM;
Definition of the name of the application;
Definition of the US language description of the application;
Definition of the executable filename;
Definition of the type of app it is calling;
Definition of the object ID.

The other way that security information can be gained is through a call to the GetLogonInfo API in EM. This can be done using the following code:
void CSetLogonInfoDoc::GetLogonInfo()
{

    BSTR m_username = NULL;
    BSTR m_password = NULL; 
    BSTR m_service  = NULL;
    BOOL                retVal;
    DISPID              dispid;

    COleDispatchDriver *pDriver = GetOleInterface(("OracleSmpRepositoryCtrl"));

    LPTSTR lpszMethod = _T("GetRepLogonInfo");
    pDriver->m_lpDispatch->GetIDsOfNames(IID_NULL, &lpszMethod,1, 
                 LOCALE_SYSTEM_DEFAULT, &dispid);
  
    static BYTE BASED_CODE param_types[] = {VTS_PBSTR VTS_PBSTR VTS_PBSTR };

    // Now, call the server method.
    pDriver->InvokeHelper(dispid, DISPATCH_METHOD, VT_BOOL, (void *) &retVal, 
                          param_types, (LPCTSTR) &m_username,
                          (LPCTSTR) &m_password,	
                          (LPCTSTR) &m_service );

    if (retVal)
    {

        // The dispatch method succeeded.  Now, process the returned data.
    
        CString buffer = (CString)"GetLogonInfo()\n" + \
                     (CString)"username : " + (CString)m_username + (CString)"\n" + \
                     (CString)"password : " + (CString)m_password + (CString)"\n" + \
                     (CString)"service  : " + (CString)m_service;

        AfxMessageBox((LPCTSTR)buffer);

        // Now free the variables.
        SysFreeString(m_username);
        SysFreeString(m_password);
        SysFreeString(m_service);
    }
    else
    {
        // The server method failed.
        AfxMessageBox((LPCTSTR)"Dispatch method failed");
    }

    ClearOleInterface(pDriver);
}

// This function returns the an Ole interface to the server name specified.
COleDispatchDriver* 
CSetLogonInfoDoc::GetOleInterface(const CString& ServerName) const
{
    IUnknown*   pUnknown;
    CLSID       clsid;
    HRESULT     result;
    LPDISPATCH  pDispatch;

    // Get the CLSID of the Object.
    result = CLSIDFromProgID((LPCTSTR)ServerName, &clsid);
    if (FAILED(result))
    {
        AfxMessageBox(_T("Failed on call to CLSIDFromProgID."));
        return(NULL);
    }

    // Get the IUnknown of the EM object.
    result = GetActiveObject(clsid, NULL, &pUnknown);
       if (FAILED(result))
    {
        AfxMessageBox(_T("Failed on call to GetActiveObject."));
        return(NULL);
    }

    // Call QueryInterface to get the IDispatch of the service.
       result = pUnknown->QueryInterface(IID_IDispatch, (LPVOID *) &pDispatch);
       if (FAILED(result))
    { 
        AfxMessageBox(_T("Failed on call to QueryInterface."));
        return(NULL);
    }

    // Now, attach the LPDISPATCH to the Ole Dispatch Driver.
    COleDispatchDriver *pdrv = new COleDispatchDriver;
    pdrv->AttachDispatch(pDispatch);
    return pdrv;
}   


// Clear Ole Interface.
void CSetLogonInfoDoc::ClearOleInterface(COleDispatchDriver *pdrv) const
{
    pdrv->ReleaseDispatch();
    delete pdrv;
    pdrv = NULL;    
}
A full explanation of the above code would require discussion of the intricacies of OLE Automation, but in summary:

The GetLogonInfo attempts to identify the object exposed by EM called OracleSmpRepositoryCtrl.
Upon making contact with the object the contact attempts to call a method called GetRepLogonInfo() to gain access to the preferred credentials set in EM.

The following is an image of EM with the Third Party Palette visible, and within it the SetLogonInfo program icon. This icon is the one that looks like a key:
[screen shot here]
The following image shows the menu item selection of GetLogon Credentials:
[screen shot here]
Summary
Integration with Enterprise Manager is not difficult, and is worthwhile even with the limited number of integration points currently available. Oracle intends to provide much more sophisticated integration capabilities in the future, which may make more seamless extension of EM functionality by third party products possible. Oracle's establishment of EM as a common DBA environment should send clear signals to third party developers and Oracle DBAs alike that integration and the Windows platform represent the inevitable future of DBA tools.
About the Authors
Frank Magliozzi is an independent Oracle DBA/Developer specializing in 3GL and 4GL tools, which includes C++, PRO*C, Dev/2000, Power Objects, Java/Script, WebServer. His recent exposure includes interfacing 3rd party applications into Oracle Enterprise Manager. He can be contacted by e-mail on [email protected].
Frank Magliozzi
Eurosoft Pty Ltd
50 Henty St., Reservoir
Vic 3073, Australia
Phone: +619 470 4072
Mobile: +61419 595 230
John Symington is technical director of Common Sense Computing, a developer of third party tools for database administration. He can be contacted by e-mail at [email protected], and some of the work he directs can be seen on http://www.comsense.com.
John Symington
Common Sense Computing Pty Ltd
10 Illowa St, East Malvern
Vic 3145 Australia
Phone: +61 3 9885 5829
Fax:  +61 3 9885 0116
This is a copy of an article published @ http://www.ioug.org/