RE: Oracle License Audit
From: Patterson, Joel <jpatterson_at_entint.com>
Date: Wed, 9 Oct 2013 09:15:56 -0400
Message-ID: <C1117B1AA0340645894671E09A7891F71569177BCD_at_EIHQEXVM2.ei.local>
Here are a couple scripts I use. By no means would consider it a catch all. I've gone around the block with oracle support on this, and this is pretty much the best that came out of it.
WHERE a.rno = 1
;
Date: Wed, 9 Oct 2013 09:15:56 -0400
Message-ID: <C1117B1AA0340645894671E09A7891F71569177BCD_at_EIHQEXVM2.ei.local>
Here are a couple scripts I use. By no means would consider it a catch all. I've gone around the block with oracle support on this, and this is pretty much the best that came out of it.
Runs on 10g, 11g. Don't know what is in 12c yet. As you can see, the 'name's of what I decided where extra cost itimes are hard coded. Massage to you own taste. You can query DBA_FEATURE_USAGE_STATISTICS for the full list, have not done so in 12c yet.
SELECT a.*
FROM (
SELECT dfs.*,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY version
DESC) rno
FROM DBA_FEATURE_USAGE_STATISTICS dfs
WHERE detected_usages > 0
AND name IN
(
'Advanced Security',
'Automatic Database Diagnostic Monitor',
'Data Mining',
'Label Security',
'Partitioning (user)',
'RMAN - Tape Backup',
'Real Application Clusters (RAC)',
'SQL Access Advisor',
'SQL Tuning Advisor',
'SQL Tuning Set',
'Spatial',
'Transparent Gateway'
)
) a
WHERE a.rno = 1
;
select a.name, a.version, a.detected_usages, a.currently_used, a.first_usage_date,
a.last_usage_date, a.description
from (select dba_feature_usage_statistics.*,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY name, version) rno
from dba_feature_usage_statistics
where detected_usages > 0
and name in (
'Advanced Security', 'Automatic Database Diagnostic Monitor',
'Data Mining', 'Label Security',
'Partitioning (user)', 'RMAN - Tape Backup',
'Real Application Clusters (RAC)', 'SQL Access Advisor',
'SQL Tuning Advisor', 'SQL Tuning Set', 'Spatial',
'Transparent Gateway'
)) a WHERE a.rno = 1
col comp_id format a10
col comp_name format a40
col object_name format a30
col owner format a15
col status format a10
col version format a15
select comp_id, comp_name, status, version from dba_registry;
prompt Checking for invalid objects
select object_name, owner, object_type, status
from dba_objects
where status = 'INVALID'
order by owner, object_type, object_name;
Joel Patterson
Database Administrator
904 928-2790
-- Joel Patterson Sr. Database Administrator | Enterprise Integration Phone: 904-928-2790 | Fax: 904-733-4916 http://www.entint.com/ http://www.entint.com/ http://www.facebook.com/pages/Enterprise-Integration/212351215444231 http://twitter.com/#!/entint http://www.linkedin.com/company/18276?trk=tyah http://www.youtube.com/user/ValueofIT This message (and any associated files) is intended only for the use of the addressee and may contain information that is confidential, subject to copyright or constitutes a trade secret. If you are not the intended recipient, you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message, is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and deleting it from your computer. Messages sent to and from us may be monitored. Any views or opinions presented are solely those of the author and do not necessarily represent those of the company. [v.1.1] From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of edwin devadanam Sent: Wednesday, October 09, 2013 5:17 AM To: Rui.Amaral_at_tdsecurities.com; 'norman.dunbar.capgemini_at_environment-agency.gov.uk'; howard.latham_at_gmail.com; ORACLE-L Subject: Re: Oracle License Audit am planning to write script to avoid licensing issue(12.1.3 Ebiz application and 11.2.0.3 DB) any help please? thanks, Edwin.K On Wednesday, March 2, 2011 7:44 PM, "Amaral, Rui" <Rui.Amaral_at_tdsecurities.com> wrote: Ditto with Norman on this one as well. On top of that if they do run scripts (and with your network guys assistance, port scans [yes that has been done in my scenario]) they may find developer boxes with standard or enterprise editions running instead of developer editions. It's up to your management and oracle reps to determine how to handle that situation, ie., allowing a certain period of time to track them down and move them to a licensed server. Rui Amaral -----Original Message----- From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dunbar, Norman (Capgemini) Sent: Wednesday, March 02, 2011 5:53 AM To: howard.latham_at_gmail.com; ORACLE-L Subject: RE: Oracle License Audit In summary: Oracle will come to your place of work and ask to see valid license details for all your products - installed or in use, makes no difference. (See below!) They may/will run various scripts to audit what you have running/installed - as is their wont. You will then be told that you are under/over/perfectly licensed for all the products you have installed or are in use. If under, you will have to pay up - or get a bl--dy good case for why you don't think you should pay! If perfect, all fine and dandy. If over licensed, it could happen, I rather suspect you won't be seeing a refund from Oracle. Hard lines - they will say - should have got it right from the start! I know of/lived through a couple of these audits: A large governmental department in the UK got hammered for being seriously under licensed. To the tune of around 3 million Sterling. Another one got hammered because they had installed the default options on a number of Oracle versions. This, according to the docs at the time was fine as "you only have to license the products you actually use". Oh no you don't - regardless of what the install docs said, you have an on-line doc that says, somewhere on page 38, that you license everything you INSTALL. After much wailing, consultancy and gnashing of teeth, said agency got screwed to the tune of "a lot". Said agency did get a letter of intent from Oracle to allow them to continue paying for USED products (for already installed versions of Oracle) but all new installs would be licensed by INSTALLED products. Things to beware of: 1. The Tuning and Diagnostics are installed and can't be uninstalled. If you use them, the registry knows and you need to license them. So much for paying for INSTALLED products then - this doesn't compute! 2. If you patch 10205 (or it may be 10203) there is a bug. The patch kit re-enables all the options you specifically have turned off and rebuilds the binaries to include them. This is an Oracle bug - but that doesn't help the audit - you have these products installed so must pay for them - even if you carefully turned the options off when installing originally and rebuilt the binaries with these options removed, the damned patch puts them all back! 3. Partitioning is in use by SYSTEM. Regardless. Turn it off and it still gets installed. Luckily, the registry differentiates between user and system use. 4. Etc. It's a nightmare! I'm currently running Oracle supplied auditing script on a monthly basis on every (known) server at this location - HP and Linux - and for all versions of Oracle above 7.3 (Don't ask!) - happy to share my cron tasks and code. This script is the one that the audit guys will use and as far as I remember, the results go to Egypt to be analysed. Not sure how that works now, after the revolution. Cheers. Norm. Norman Dunbar Contract Senior Oracle DBA Capgemini Database Team (EA) Internal : 7 28 2051 External : 0113 231 2051 >> -----Original Message----- >> From: oracle-l-bounce_at_freelists.org >> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Howard Latham >> Sent: 02 March 2011 09:31 >> To: ORACLE-L >> Subject: Oracle License Audit >> >> Click here >> <https://www.mailcontrol.com/sr/wQw0zmjPoHdJTZGyOCrrhg== >> fvsb2U8i9oDn6!NSUa3xEGqDfzJ+GRHJbN74uIqGSYIPCELtzovJ!KJwA==> >> to report this email as spam. >> >> >> >> We have just had a letter in Which Oracle Invite themselves to audit >> our licenses. >> Does anybody know what this entails? >> It's particularly inconvenient as our DR site is kicking us out in 30 >> days in favour of the London Olympics. >> >> >> -- >> Howard A. Latham >> >> >> Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk -- http://www.freelists.org/webpage/oracle-l NOTICE: Confidential message which may be privileged. Unauthorized use/disclosure prohibited. If received in error, please go to www.td.com/legal for instructions. AVIS : Message confidentiel dont le contenu peut être privilégié. Utilisation/divulgation interdites sans permission. Si reçu par erreur, prière d'aller au www.td.com/francais/avis_juridique pour des instructions. -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 09 2013 - 15:15:56 CEST