This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

Lock/Unlock OEM Grid Control Accounts From Excel Macro


As a complimentary post to my previous blog post on Reporting All Oracle User Accounts Through OEM Grid Control, I have decided to post the code for an Excel VBA macro which uses three buttons to call the specific OEM Grid Control URLs that will enable you Lock/UnLock or Edit an Oracle user account using the OEM Grid Control user interface, but from a link on an Excel spreadsheet.

This sounds confusing so maybe I should explain the reason behind this.
I needed to know about all Oracle user accounts throughout the Oracle landscape so that I could compare them to a dump of Active Directory accounts.
The AD accounts get locked when people leave, but the leavers process didn’t always include the DBA in the ‘cc list.
So, armed with the Excel spreadsheet of AD users, I needed to marry the list to the Oracle accounts and then go and lock the Oracle accounts where the AD account had been locked/expired.

Sound OK so far?
Good.  So once I’ve got the Excel sheet with the ~200 Oracle accounts I need to lock, here’s how it could be done using OEM Grid Control (you can give the spreadsheet to your 1st line support people, if they have OEM access).

Three buttons on the spreadsheet:  Lock, UnLock and Edit.
Just paste the code into the sheet’s VBA section, then change the text “<your_oem_server>” and add the three buttons and hook them to the sub routines.
I should mention that this was an Oracle Enterprise Manager Grid Control 10g implementation.

Enjoy.

Private Sub CommandButton1_Click()
' Call to open IE with the URL to lock the user based on the data on the current sheet on the current selected row.

Dim str_URLpart1 As String
Dim str_URLpart2 As String
Dim str_URLpart3 As String
Dim str_URLpart4 As String
Dim str_URLpart5 As String
Dim str_complete_URL As String
Dim str_sys As String
Dim str_user As String
Dim IE As Object

str_URLpart1 = "https://<your_oem_server>/em/console/database/security/user?oname="
str_URLpart2 = "&amp;event=lockUser&amp;cancelURL=%2Fem%2Fconsole%2Fdatabase%2FdatabaseObjectsSearch%3Fevent%3Dredisplay%26target%3D"
str_URLpart3 = "%26type%3Doracle_database%26objectType%3DUSER%26otype%3DUSER&amp;backURL=%2Fem%2Fconsole%2Fdatabase%2FdatabaseObjectsSearch%3Fevent%3Dredisplay%26target%3D"
str_URLpart4 = "%26type%3Doracle_database%26objectType%3DUSER%26otype%3DUSER&amp;otype=USER&amp;target="
str_URLpart5 = "&amp;type=oracle_database"
str_user = Range("D" &amp; ActiveCell.Row).Value
str_sys = Range("A" &amp; ActiveCell.Row).Value
str_complete_URL = str_URLpart1 + str_user + str_URLpart2 + str_sys + str_URLpart3 + str_sys + str_URLpart4 + str_sys + str_URLpart5

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
IE.Visible = True

' Send the form data To URL As POST binary request
IE.Navigate str_complete_URL

' Statusbar
Application.StatusBar = "URL is loading. Please wait..."

' Wait while IE loading...
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop

Application.StatusBar = ""

End Sub

Private Sub CommandButton2_Click()

' Call to open IE with the URL to unlock the user based on the data on the current sheet on the current selected row.

Dim str_URLpart1 As String
Dim str_URLpart2 As String
Dim str_URLpart3 As String
Dim str_URLpart4 As String
Dim str_URLpart5 As String
Dim str_complete_URL As String
Dim str_sys As String
Dim str_user As String
Dim IE As Object

str_URLpart1 = "https://<your_oem_server>/em/console/database/security/user?oname="
str_URLpart2 = "&amp;event=unlockUser&amp;cancelURL=%2Fem%2Fconsole%2Fdatabase%2FdatabaseObjectsSearch%3Fevent%3Dredisplay%26target%3D"
str_URLpart3 = "%26type%3Doracle_database%26objectType%3DUSER%26otype%3DUSER&amp;backURL=%2Fem%2Fconsole%2Fdatabase%2FdatabaseObjectsSearch%3Fevent%3Dredisplay%26target%3D"
str_URLpart4 = "%26type%3Doracle_database%26objectType%3DUSER%26otype%3DUSER&amp;otype=USER&amp;target="
str_URLpart5 = "&amp;type=oracle_database"
str_user = Range("D" &amp; ActiveCell.Row).Value
str_sys = Range("A" &amp; ActiveCell.Row).Value
str_complete_URL = str_URLpart1 + str_user + str_URLpart2 + str_sys + str_URLpart3 + str_sys + str_URLpart4 + str_sys + str_URLpart5

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
IE.Visible = True

' Send the form data To URL As POST binary request
IE.Navigate str_complete_URL

' Statusbar
Application.StatusBar = "URL is loading. Please wait..."

' Wait while IE loading...
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop

Application.StatusBar = ""

End Sub

Private Sub CommandButton3_Click()

' Call to open IE with the URL to edit the user based on the data on the current sheet on the current selected row.

Dim str_URLpart1 As String
Dim str_URLpart2 As String
Dim str_URLpart3 As String
Dim str_URLpart4 As String
Dim str_URLpart5 As String
Dim str_complete_URL As String
Dim str_sys As String
Dim str_user As String
Dim IE As Object

str_URLpart1 = "https://<your_oem_server>/em/console/database/security/user?oname="
str_URLpart2 = "&amp;event=edit&amp;cancelURL=%2Fem%2Fconsole%2Fdatabase%2FdatabaseObjectsSearch%3Fevent%3Dredisplay%26target%3D"
str_URLpart3 = "%26type%3Doracle_database%26objectType%3DUSER%26otype%3DUSER&amp;backURL=%2Fem%2Fconsole%2Fdatabase%2FdatabaseObjectsSearch%3Fevent%3Dredisplay%26target%3D"
str_URLpart4 = "%26type%3Doracle_database%26objectType%3DUSER%26otype%3DUSER&amp;otype=USER&amp;target="
str_URLpart5 = "&amp;type=oracle_database"
str_user = Range("D" &amp; ActiveCell.Row).Value
str_sys = Range("A" &amp; ActiveCell.Row).Value
str_complete_URL = str_URLpart1 + str_user + str_URLpart2 + str_sys + str_URLpart3 + str_sys + str_URLpart4 + str_sys + str_URLpart5

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
IE.Visible = True

' Send the form data To URL As POST binary request
IE.Navigate str_complete_URL

' Statusbar
Application.StatusBar = "URL is loading. Please wait..."

' Wait while IE loading...
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop

Application.StatusBar = ""

End Sub

Virtualisation Conundrum

Whilst researching ideas for using my VMware ESXi test rig, I came across this blog site: https://weinshenker.net/blog/2011/07/26/oracle-redhat-vmware/
I’ve added it to my Fav’s as a *must read* once in a while.
It’s packed with some very interesting articles concerning Linux, Virtualisation and more importantly, Oracle.

It may have changed my mind about using Oracle Enterprise Linux.  I’m considering shifting up the Linux tree to RedHat, or experimenting with Fedora.

See the Wikipedia tree diagram https://upload.wikimedia.org/wikipedia/commons/8/8c/Gldt.svg on the right of the Wikipedia Linux Distro page.

Use EMDIAG REPVFY to fix OEM issues

EMDIAG is a diagnostics utility specifically for the Oracle Enterprise Manager system.
It is useful to diagnose issues with the repository of OEM as it doesn’t need the OMS to be running to use it.

The Oracle document 763072.1 (Oracle Enterprise Manager Grid Control Release Notes for Linux and Windows 10g Release 5) suggests upgrading the EMDIAG kit before performing a general OEM upgrade (it’s in the OEM pre-upgrade tasks list).

Follow the steps in document 421499.1 to install the EMDIAG kit into the C: directory (you should see the old one already installed there).

Follow the process in the document for upgrading the EMDIAG kit and check the Windows environment variable for the EMDIAG_HOME value.

Once installed, use the “repvfy -version” command in the %EMDIAG_HOME%bin directory to verify the current installed OEM software.

To use EMDIAG, set your OracleHome from the command line:

set ORACLE_HOME=C:OracleHomesoms10g

Change to the EMDIAG_HOME bin directory and run repvfy:

Now fix the errors where possible (generic operations that EMDIAG can perform to fix common problems):

repvfy -fix

Some of the problems cannot be fixed automatically.
You can get more details by running verify with greater detail:

repvfy verify TARGETS -level 9 -detail

NOTE: You may want to redirect to a text file in Windows (> c:TARGET_details.txt).

The detailed view may open a can of worms for you.
Good luck!

Report All Oracle User Accounts Through OEM Grid Control

You know the problem: You have Oracle Enterprise Manager Grid Control 10g installed and all your databases are configured in it.
It allows you to manage your entire landscape.  Great!

All of a sudden, a “leaver form” pops on your desk from HR with a long list of people who have left the company recently.
Do they have accounts in your Oracle databases?
If you have only one or two Oracle databases to check, then you can use the power of Grid Control to sort out the list in a few minutes.
What if you’ve got 50+ databases?
What if you’ve got 100+ databases?
Don’t they say: “The little jobs are the most time consuming”?

You may maintain an external spreadsheet of Oracle accounts, or you may not.  If you do, or even if you don’t, this article will show you how to configure a Oracle Enterprise Manager Grid Control 10g report that will show you all users accounts across the landscape at the click of a button.  You could use this technique to extend it to almost anything that can be done in an SQL or PL/SQL session.

Architecture overview:

We will be creating a new OEM Grid Control job called LIST_USER_ACOUNT_DETAILS, and a new report called LIST_USER_ACCOUNT_DETAILS.
The job is executed on the monitored database targets and it stores it’s output in the usual MGMT$ table in the OEM repository database.
The report simply pulls and re-connects the job output.

In OEM Grid Control create a new job called “LIST_USER_ACCOUNT_DETAILS”.
Add the database targets or target group.

Set the SQL script to be:

SET HEAD OFF;
SET PAGESIZE 9999;
SET LINESIZE 999;
SET TRIMOUT ON;
SET TRIMSPOOL ON;
SET FEEDBACK OFF;
SET COLSEP ' ';
COL MARKER FORMAT A2;
COL USERNAME FORMAT A30;
COL ACCOUNT_STATUS FORMAT A25;
COL LOCK_DATE FORMAT A12;
COL EXPIRY_DATE FORMAT A12;
COL HAS_REMOVED_ROLE FORMAT A30;
COL HAS_DBA_ROLE FORMAT A20;
WHENEVER SQLERROR EXIT FAILURE;
SELECT
'@|' marker,
username||'|' username,
account_status||'|' account_status,
'|'||TO_CHAR(lock_date,'DD-MM-RRRR')||'|' lock_date,
'|'||TO_CHAR(expiry_date,'DD-MM-RRRR')||'|' expiry_date,
'|'||DECODE((SELECT 'X' FROM dba_role_privs WHERE grantee = username AND granted_role ='REMOVED_ACCOUNTS'),'X','HAS ROLE "REMOVED_ACCOUNTS"','')||'|' has_removed_role,
'|'||DECODE((SELECT 'X' FROM dba_role_privs WHERE grantee = username AND granted_role ='DBA'),'X','HAS DBA ROLE!!','') has_dba_role
from dba_users;

Set the Parameters to be “-s” (silent):

Set the Credentials to use an account capable of querying DBA_USERS and DBA_ROLE_PRIVS (you could change this to use ALL_ views and use a lower priviledged account).
Set the schedule for the job. However frequently you wish to query the account details.

Submit the job for execution.

Once the job runs, the job output will be visible in the OEM MGMT$JOB_STEP_HISTORY table.
The actual SQLPlus output is stored in the OUTPUT column which is a CLOB field.
We have had to insert a marker in the SQL so that the output contains a ‘@|’ at the beginning of each line. This is used to determine the start of each record in the OUTPUT CLOB.
We just need to write a report to pull out the details.

Create a new report called “LIST_USER_ACCOUNT_DETAILS”.
On the “General” tab, select the “Use the specified target” option and enter the hostname of your OEM Grid Control repository server (DB server).
Select the “Run report using target privileges of the report owner (SYSMAN)” tick box.

On the “Elements” tab, add a “Table from SQL” item and then set the header, then paste in the statement below:

SELECT TARGET_NAME,JOB_TIME,ACCOUNT_DETAILS FROM (
SELECT '===============' TARGET_NAME,'================' JOB_TIME,'==================================================' ACCOUNT_DETAILS FROM DUAL
UNION
SELECT
TARGET_NAME,
TO_CHAR(END_TIME,'DD-MON-RRRR HH24:MI:SS') JOB_TIME,
-- non-regexp option -- TO_CHAR(REPLACE(SUBSTR(mh.OUTPUT,INSTR(mh.OUTPUT,'@| ',1,ct.x),INSTR(mh.OUTPUT,'@| ',1,ct.x+1) - INSTR(mh.OUTPUT,'@| ',1,ct.x)),'@| ','')) ACCOUNT_DETAILS
TO_CHAR(REGEXP_SUBSTR(mh.OUTPUT,'[^@]+', 1,ct.x)) ACCOUNT_DETAILS
FROM MGMT$JOB_STEP_HISTORY mh,
(SELECT rownum x FROM ALL_OBJECTS WHERE rownum<=5000) ct
WHERE TARGET_TYPE='oracle_database'
AND JOB_OWNER='SYSMAN'
AND JOB_NAME LIKE 'LIST_USER_ACCOUNT_DETAILS.%'
AND STEP_NAME='Command'
)
WHERE ACCOUNT_DETAILS IS NOT NULL
ORDER BY TARGET_NAME, JOB_TIME;

NOTE: In the report SQL, we have assumed a maximum number of 5000 user accounts across all database targets.
If you exceed this (if you have a lot of user accounts) then you will need to increase the number.
We have also assumed that SYSMAN is the owner of the job that created the output. You should change this if necessary.

You can choose to schedule the report if you wish (you should take into account the source job schedule).
It's probably best to secure the report so that only administrators can see it.

Test the report.

The output format consists of the following columns:
 TARGET NAME
 JOB TIME
 ACCOUNT DETAILS {
                 USERNAME
                 ACCOUNT STATUS
                 LOCK DATE
                 EXPIRY DATE
                 HAS REMOVED ROLE
                 HAS DBA ROLE
                }

The "HAS REMOVED ROLE" column relates to a "flag" role that I use to denote that an Oracle account has been de-activated purposely.
Instead of the account being deleted, it is retained for audit purposes and the REMOVED_ACCOUNTS role granted to it. You can choose to ignore or adapt this column.

Once you have the output, you can extract it to Excel for comparison with an Active Directory dump or any other means.

Optimal Oracle RMAN Backup Command Options


Just a very short post about an optimal Oracle RMAN backup command run block.
Here’s what *I* think provides an optimal backup for a generic setup.

— Check our existing files are available before we do some obsolete deleting.
crosscheck archivelog all;
crosscheck backup;

— Delete any obsolete files before we start so that we have max space available.
— I have “REDUNDANCY 1” set so that I retain the previous backup until the next backup is completed successfully (you need double disk/tape space available for this).
delete noprompt obsolete;

— Do the DB and ARCH log backups but put each datafile into it’s own piece file.
— I prefer this because should I need only a specific datafile restored and I have to go to tape, I only need to get the piece that’s relevant and this piece is only the datafile needed, saving tape restore time.
— For Oracle 9i remove the ‘as compressed backupset’ option.
backup as compressed backupset database filesperset 1 plus archivelog filesperset 1;

— Now only delete archive logs that are in two separate backups.  This is critical.
delete noprompt archivelog all backed up 2 times to disk;

— Delete the previous night’s backup from disk (obviously you need the space to be able to store two backups on disk).
delete noprompt obsolete;

— Finally, always a good idea to keep a list of datafiles and temp files that existed at the time of the backup.
report schema;

Make sure that you always save the RMAN backup logs.
I’ve found that they can be very useful during a restore scenario.

As a tip, you can also run a daily job to list what backups are required to restore a database using the “RESTORE DATABASE PREVIEW SUMMARY;” command.