OraCsdP: Oracle Version : Upgrade / Downgrade & Pay for Them !

1] Downgrade a Database from EE to SE

Goal	Downgrade from Enterprise Edition to Standard Edition
Version	Oracle 11.2.0.3 Linux RH 5.6
ORACLE_HOME	EE /oracle/11GDB SE /oracle/11GDBSE
ORACLE_SID	MY_DB
Log as	SYS
Reference	http://www.oracle.com/us/products/database/ enterprise-edition/comparisons/index.html

A] UNSUPPORTED

This is the un-supported method for lazy DBA

when you don’t want to deal with expdp/impdp FULL=Y mode.

To see the official export/import method :

Converting An Enterprise Edition Database To Standard Edition[Article ID 139642.1]

1.0a Pre-requisite [EE]

Check for Invalid Objects

SQL> select owner,count(1) from dba_objects where status != 'VALID' group by owner order by 2 desc ;

Export in case of failure, we will have to reverse back to the Supported Process

Take a SE expdp FULL=Y (/oracle/11GDBSE/bin/expdp ) connected to the EE instance.

SQL> CREATE OR REPLACE DIRECTORY EXP_DIR as '<path>' ;

$ time expdp system/<psw>@MY_DB FULL=y DIRECTORY=EXP_DIR DUMPFILE=MY_DB.dmp PARALLEL=2 LOGFILE=e_MY_DB.log flashback_time="\"to_timestamp(to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')\""

Export: Release 11.2.0.3.0 - Production on Thu Apr 11 14:15:44 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

. . exported "DBASBI"."T_CLIENT" 2.280 GB 219288 rows

. . exported "SYSMAN"."MGMT_VIOLATIONS" 0 KB 0 rows

. . exported "SYSMAN"."MGMT_VIOLATION_CONTEXT" 0 KB 0 rows

Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:

/rman/MY_DB/MY_DB.dmp

Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 15:40:45

1.0b Remove Enterprise Edition Users [EE]

Deinstallation of Oracle Spatial keeping Oracle Locator in 11.2

In 11.2 If you have a database where the Oracle Spatial objects have been installed and you see Spatial in the DBA_REGISTRY you can follow these steps to remove Spatial but keep Locator features:

$ sqlplus / as sysdba

SQL> set pages 5000 lines 150

col comp_name format a25

col version format a25

col status format a15

SQL> select comp_name, version, status from dba_registry where comp_name='Spatial';

COMP_NAME VERSION STATUS

------------------------- ------------------------------ --------

Spatial 11.2.0.3.0 INVALID

sqlplus /nolog

conn / as sysdba

spool mddins.log

start ?/md/admin/mddins.sql

SQL> !grep -i ora- mddins.log | egrep -v -- "Rem|--|ORA-00942|ORA-06550|ORA-29809"

ORA-29844: duplicate operator name specified

ORA-29830: operator does not exist

ORA-01430: column being added already exists in table

ORA-06512: at line 2

The Spatial component will show status REMOVED:

SQL> spool off

SQL> select comp_name, version, status from dba_registry where comp_name='Spatial';

COMP_NAME VERSION STATUS

------------------------- ------------------------------ --------

Spatial 11.2.0.3.0 REMOVED

MDSYS user will continue to exist in the database:

Oracle Multimedia has to be with a status of VALID:

SQL> select comp_name, version, status from dba_registry where upper(comp_name) like '%MEDIA';

COMP_NAME VERSION STATUS

------------------------- ------------------------- ---------------

Oracle Multimedia 11.2.0.3.0 VALID

Deinstallation of Oracle OLAP

How To Remove The OLAP Option In 10g And 11g [ID 332351.1]

spool remove_olap.log

-- Remove OLAP Catalog

@/oracle/11GDB/olap/admin/catnoamd.sql

-- Remove OLAP API

@/oracle/11GDB/olap/admin/olapidrp.plb

@/oracle/11GDB/olap/admin/catnoxoq.sql

-- Deinstall APS - OLAP AW component

@/oracle/11GDB/olap/admin/catnoaps.sql

@/oracle/11GDB/olap/admin/cwm2drop.sql

spool off

!grep -i ora- remove_olap.log | grep -v 'does not exist'

-- Recompile invalids

@?/rdbms/admin/utlrp.sql

SQL> col comp_name for a40

SQL> set pages 5000 lines 150

SQL> SELECT COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;

COMP_NAME VERSION STATUS

---------------------------------------- ------------------------------ --------

OWB 11.2.0.3.0 VALID

Oracle Enterprise Manager 11.2.0.3.0 VALID

Spatial 11.2.0.3.0 REMOVED

Oracle Multimedia 11.2.0.3.0 VALID

Oracle XML Database 11.2.0.3.0 VALID

Oracle Text 11.2.0.3.0 VALID

Oracle Expression Filter 11.2.0.3.0 VALID

Oracle Rules Manager 11.2.0.3.0 VALID

Oracle Workspace Manager 11.2.0.3.0 VALID

Oracle Database Catalog Views 11.2.0.3.0 VALID

Oracle Database Packages and Types 11.2.0.3.0 VALID

JServer JAVA Virtual Machine 11.2.0.3.0 VALID

Oracle XDK 11.2.0.3.0 VALID

Oracle Database Java Packages 11.2.0.3.0 VALID

14 rows selected.

SQL> select owner,count(1) from dba_objects where status != 'VALID' group by owner order by 2 desc ;

OWNER COUNT(1)

------------------------------ ----------

MDSYS 3

SYS 2

FLOWS_FILES 1

1.1 Run Dictionnary scripts in New SE ORACLE_HOME

[ EE ORACLE_HOME ]

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 11 16:43:27 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate

[ SE ORACLE_HOME ] Copy Init file & password file, then startup Instance

$ cp -p /oracle/11GDB/dbs/initMY_DB.ora /oracle/11GDBSE/dbs/initMY_DB.ora

$ cp -p /oracle/11GDB/dbs/orapwMY_DB .

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 11 16:44:45 2013

Connected to an idle instance.

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 534462464 bytes

Fixed Size 2230072 bytes

Variable Size 411044040 bytes

Database Buffers 113246208 bytes

Redo Buffers 7942144 bytes

Database mounted.

Database opened.

SQL>

[ SE ORACLE_HOME ] Run Catalog scripts

Pre-requisite from Best Practices for running catalog, catproc and utlrp script [ID 863312.1]

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 11 16:44:45 2013

ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE=MEMORY;
ALTER SYSTEM SET AQ_TM_PROCESSES=0 SCOPE=MEMORY;
ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='' SCOPE=MEMORY;

SQL> spool catproc.log

SQL> @?/rdbms/admin/catproc.sql

SQL> spool off

SQL> !grep -i ora- catproc.log | egrep -v -- "conflicts with another|ORA-02303|Rem|--|ORA-00955|ORA-00942|ORA-01434|ORA-04043|ORA-01418"

DOC> The following PL/SQL block will cause an ORA-20000 error and

ORA-29809: cannot drop an operator with dependent objects

ORA-02311: cannot alter with COMPILE option a valid type with type or table

ORA-06512: at line 5

SQL> rem ORA-31604: invalid NAME parameter "PARSE" for object

ORA-20002: Unable to create table IMPDP_STATS: already exists

ORA-06512: at "SYS.DBMS_STATS", line 12220

ORA-06512: at line 2

ORA-04098: trigger 'SYS.AW_DROP_TRG' is invalid and failed re-validation

MY_DB <host>:/home/oracle >

SQL> spool catalog.log

SQL> @?/rdbms/admin/catalog.sql

SQL> spool off

SQL> !grep -i ora- catlog.log | egrep -v -- "conflicts with another|ORA-02303|Rem|--|ORA-00955|ORA-00942|ORA-01434|ORA-04043|ORA-01418"

DOC> The following statement will cause an "ORA-01722: invalid number"

2 'Creating this constraint should give ORA-02436. Rewrite it before 2000 AD.'

DOC> The following PL/SQL block will cause an ORA-20000 error and

SQL> REM Expect ORA-1921 for CREATE ROLE exp_full_database if this file is run

SQL> REM Expect ORA-1921 for CREATE ROLE imp_full_database if this file is run

SQL> exit

1.2 Compile every objects

SHUTDOWN IMMEDIATE

STARTUP

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN 2013-04-11 18:04:36

…

DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_END 2013-04-11 18:05:26

…

OBJECTS WITH ERRORS

-------------------

…

ERRORS DURING RECOMPILATION

---------------------------

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

SQL>

1.3 Check Installation

col comp_name for a40

set pages 5000 lines 150

SELECT COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;

COMP_NAME VERSION STATUS

---------------------------------------- ------------------------------ ----------

OWB 11.2.0.3.0 VALID

Oracle Enterprise Manager 11.2.0.3.0 VALID

OLAP Catalog 11.2.0.3.0 INVALID

Spatial 11.2.0.3.0 INVALID

Oracle Multimedia 11.2.0.3.0 VALID

Oracle XML Database 11.2.0.3.0 VALID

Oracle Text 11.2.0.3.0 VALID

Oracle Expression Filter 11.2.0.3.0 VALID

Oracle Rules Manager 11.2.0.3.0 VALID

Oracle Workspace Manager 11.2.0.3.0 VALID

Oracle Database Catalog Views 11.2.0.3.0 VALID

Oracle Database Packages and Types 11.2.0.3.0 VALID

JServer JAVA Virtual Machine 11.2.0.3.0 VALID

Oracle XDK 11.2.0.3.0 VALID

Oracle Database Java Packages 11.2.0.3.0 VALID

OLAP Analytic Workspace 11.2.0.3.0 INVALID

Oracle OLAP API 11.2.0.3.0 INVALID

SQL> select owner,count(1) from dba_objects where status != 'VALID' group by owner order by 2 desc ;

OWNER COUNT(1)

------------------------------ ----------

MDSYS 29

OLAPSYS 28

PUBLIC 27

SYS 2

FLOWS_FILES 1

Additional Note :

At that point, an export may fail into :

Estimate in progress using BLOCKS method...

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS []

ORA-31642: the following SQL statement fails:

BEGIN "SYS"."DBMS_CUBE_EXP".SCHEMA_CALLOUT(:1,0,1,'11.02.00.00.00'); END;

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: at "SYS.DBMS_METADATA", line 1749

ORA-06550: line 1, column 8:

PLS-00201: identifier 'SYS.DBMS_CUBE_EXP' must be declared

ORA-06550: line 1, column 8:

PL/SQL: Statement ignored

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: at "SYS.KUPW$WORKER", line 8996

Some more steps need to be executed [Ora-39127: Unexpected Error From Call To Export_string :=Sys.Dbms_cube_exp [ID 811934.1] ]

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 17 09:43:23 2013

Connected to:

Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> create table sys.exppkgact$_backup as select * from sys.exppkgact$;

Table created.

SQL> delete from sys.exppkgact$ where package = 'DBMS_CUBE_EXP' and schema= 'SYS';

3 rows deleted.

SQL> commit;

Commit complete.

SQL> exit

1.5 Adapt SQL*NET Files :

Listener.ora & Tnsnames.ora

$ cd $TNS_ADMIN

$ cp -p /oracle/11GDB/network/admin/*.ora .

$ diff listener.ora listener.ora_ori_EE

5c5

< (ORACLE_HOME = /oracle/11GDBSE)

---

> (ORACLE_HOME = /oracle/11GDB)

$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 11-APR-2013 18:13:07

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host>)(PORT=1521)))

The command completed successfully

We are done.

However, in case you run into :

SQL> SELECT dbms_registry.time_stamp('CATALOG') AS timestamp FROM DUAL;

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP CATALOG 2013-06-20 08:35:45

1 row selected.

BEGIN dbms_registry_sys.validate_components; END;

ERROR at line 1:

ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors

ORA-06508: PL/SQL: could not find program unit being called:

"SYS.DBMS_REGISTRY_SYS"

ORA-06512: at line 1

Go to B] !!

The only way to properly convert from an Enterprise Edition back to a Standard Edition is through an Export/Import operation. This way you will get rid of all Enterprise Edition specific Data Dictionary objects, as the SYS schema objects are not exported. The Standard Edition EXP utility is preferred to be used to export the data.

After the export dump has been created, a new Standard Edition database needs to be created which will be used to import the data into.

After the Import in the Standard Edition database, you only need to drop all user schemas related to Enterprise Edition features, such as the MDSYS account (used with Oracle Spatial).

B] SUPPORTED Method

1.0 Pre-requisite [EE]

Get Create Tablespaces SQL script and Grants on SYS objects

select 'create tablespace '||tablespace_name||' DATAFILE ''</path>'||lower(tablespace_name)||'.dbf'' SIZE 16M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED ;'

from dba_tablespaces where tablespace_name not in ('SYSTEM','UNDO','SYSAUX','TEMP','USERS','XDB') order by 1

Be careful to Temp TS : CREATE TEMPORARY TABLESPACE SBITEMP TEMPFILE '/<path>/sbitemp.dbf' SIZE 16M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED TABLESPACE GROUP '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- SE

select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||' ; '

from dba_tab_privs where owner in ('SYS','ORDSYS','EXFSYS','DMSYS','CTXSYS','ORDPLUGINS','LBACSYS','XDB','SI_INFORMTN_SCHEMA','DIP','DBSNMP','WMSYS') and not exists ( select '1' from dba_directories where table_name = directory_name )

and grantee not in ('MDSYS',

'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE', 'CTXSYS', 'DBA', 'DBSNMP', 'DELETE_CATALOG_ROLE', 'EXECUTE_CATALOG_ROLE', 'EXFSYS', 'EXP_FULL_DATABASE', 'GATHER_SYSTEM_STATISTICS', 'HS_ADMIN_ROLE', 'IMP_FULL_DATABASE', 'LOGSTDBY_ADMINISTRATOR', 'MDSYS', 'OEM_MONITOR', 'ORACLE_OCM', 'ORDSYS', 'OUTLN', 'PUBLIC', 'SELECT_CATALOG_ROLE', 'SYSTEM', 'WMSYS', 'XDB', 'XDBADMIN' )

union

select 'grant '||privilege||' on directory '||owner||'.'||table_name||' to '||grantee||' ; '

from dba_tab_privs where owner = 'SYS' and exists ( select '1' from dba_directories where table_name = directory_name )

and grantee not in ('MDSYS',

order by 1 ;

1.1 EXPDP FULL=Y [SE]

Using SE expdp :

$ time expdp system@<MY_DB> FULL=y DIRECTORY=<MY_DB>_DP_DIR DUMPFILE=<MY_DB>_EE.dmp PARALLEL=2 LOGFILE=e_<MY_DB>_EE.log EXCLUDE=SCHEMA:"in$'MDSYS'$" flashback_time="\"to_timestamp(to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')\""

1.2 Create the new SE DB

Remove the EE Database

Create the new SE DB, see other Administration post.

Get Initialization parameter values from the EE Init File.

1.3 Import the objects back [SE]

Create tablespaces according to the SQL got in 1.0

import

$ time impdp system DIRECTORY=<MY_DB>_DP_DIR DUMPFILE=<MY_DB>_EE.dmp LOGFILE=i_<MY_DB>_EE.log

<MY_DB> srv-app-orapp-1:/WORK_EXP/oracle/<MY_DB> > grep -i ora- i_<MY_DB>_EE.log | egrep -v 'exist|ORA-29339|ORA-39083|ORA-39082' | more

ORA-00439: feature not enabled: Enterprise User Security

ORA-21560: argument 2 is null, invalid, or out of range

ORA-31685: Object type MATERIALIZED_VIEW:"SYSMAN"."MGMT_ECM_MD_ALL_TBL_COLUMNS" failed due to insufficient privileges. Failing sql is:

ORA-00001: unique constraint (SYS.I_JOB_JOB) violated

1.4 Post-downgrade actions

Special parameters

alter system set sec_case_sensitive_logon = FALSE ;

alter system set max_shared_servers = 8 ;

alter system set shared_servers = 4 ;

alter system set memory_target = 1024M scope = spfile ;

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Adapt Listener.ora & Tnsnames.ora

Execute SQL script got in 1.0 to grant rights on SYS objects

2] Oracle Licensing

Know what you should pay for – with the query given by Oracle Support :

- the query

ora10 sr-adm-1:/oracle/dba/bao/compte_licence > cat option_usage.sql

---------------------------------------------------------------------------------

------- Name : option_usage.sql

------- Usage : sqlplus -s <UserName/Password> @option_usage.sql >

------- <output file>

------- Description : This script reports Database Options/Management Packs

------- usage for Oracle Database 11g Release 2 only. You need

------- DBA role to execute the script.The report is based on

------- DBA_FEATURE_USAGE_STATISTICS view. Note that the

------- feature usage data in the view is updated once a week,

------- so it may take up to 7 days for the report to show

------- recent usage of options and/or packs.

-------

----------------------------------------------------------------------------------

-- arg 1 : number of past days

-- arg 2 : detected_usages > arg2

define v_days=&1

define v_usag=&2

SET LINESIZE 180;

SET PAGESIZE 1000;

SET FEEDBACK OFF;

SET COLSEP '|';

WHENEVER SQLERROR EXIT SQL.SQLCODE;

COL "Host Name" FORMAT A30;

COL "Option/Management Pack" FORMAT A60;

COL "Used" FORMAT A5;

with features as(

select a OPTIONS, b NAME from

(

select 'Active Data Guard' a, 'Active Data Guard - Real-Time Query on Physical Standby' b from dual

union all

select 'Advanced Compression', 'HeapCompression' from dual

union all

select 'Advanced Compression', 'Backup BZIP2 Compression' from dual

union all

select 'Advanced Compression', 'Backup DEFAULT Compression' from dual

union all

select 'Advanced Compression', 'Backup HIGH Compression' from dual

union all

select 'Advanced Compression', 'Backup LOW Compression' from dual

union all

select 'Advanced Compression', 'Backup MEDIUM Compression' from dual

union all

select 'Advanced Compression', 'Backup ZLIB, Compression' from dual

union all

select 'Advanced Compression', 'SecureFile Compression (user)' from dual

union all

select 'Advanced Compression', 'SecureFile Deduplication (user)' from dual

union all

select 'Advanced Compression', 'Data Guard' from dual

union all

select 'Advanced Compression', 'Oracle Utility Datapump (Export)' from dual

union all

select 'Advanced Compression', 'Oracle Utility Datapump (Import)' from dual

union all

select 'Advanced Security', 'ASO native encryption and checksumming' from dual

union all

select 'Advanced Security', 'Transparent Data Encryption' from dual

union all

select 'Advanced Security', 'Encrypted Tablespaces' from dual

union all

select 'Advanced Security', 'Backup Encryption' from dual

union all

select 'Advanced Security', 'SecureFile Encryption (user)' from dual

union all

select 'Change Management Pack', 'Change Management Pack (GC)' from dual

union all

select 'Data Masking Pack', 'Data Masking Pack (GC)' from dual

union all

select 'Data Mining', 'Data Mining' from dual

union all

select 'Diagnostic Pack', 'Diagnostic Pack' from dual

union all

select 'Diagnostic Pack', 'ADDM' from dual

union all

select 'Diagnostic Pack', 'AWR Baseline' from dual

union all

select 'Diagnostic Pack', 'AWR Baseline Template' from dual

union all

select 'Diagnostic Pack', 'AWR Report' from dual

union all

select 'Diagnostic Pack', 'Baseline Adaptive Thresholds' from dual

union all

select 'Diagnostic Pack', 'Baseline Static Computations' from dual

union all

select 'Tuning Pack', 'Tuning Pack' from dual

union all

select 'Tuning Pack', 'Real-Time SQL Monitoring' from dual

union all

select 'Tuning Pack', 'SQL Tuning Advisor' from dual

union all

select 'Tuning Pack', 'SQL Access Advisor' from dual

union all

select 'Tuning Pack', 'SQL Profile' from dual

union all

select 'Tuning Pack', 'Automatic SQL Tuning Advisor' from dual

union all

select 'Database Vault', 'Oracle Database Vault' from dual

union all

select 'WebLogic Server Management Pack Enterprise Edition', 'EM AS Provisioning and Patch Automation (GC)' from dual

union all

select 'Configuration Management Pack for Oracle Database', 'EM Config Management Pack (GC)' from dual

union all

select 'Provisioning and Patch Automation Pack for Database', 'EM Database Provisioning and Patch Automation (GC)' from dual

union all

select 'Provisioning and Patch Automation Pack', 'EM Standalone Provisioning and Patch Automation Pack (GC)' from dual

union all

select 'Exadata', 'Exadata' from dual

union all

select 'Label Security', 'Label Security' from dual

union all

select 'OLAP', 'OLAP - Analytic Workspaces' from dual

union all

select 'Partitioning', 'Partitioning (user)' from dual

union all

select 'Real Application Clusters', 'Real Application Clusters (RAC)' from dual

union all

select 'Real Application Testing', 'Database Replay: Workload Capture' from dual

union all

select 'Real Application Testing', 'Database Replay: Workload Replay' from dual

union all

select 'Real Application Testing', 'SQL Performance Analyzer' from dual

union all

select 'Spatial' ,'Spatial (Not used because this does not differential usage of spatial over locator, which is free)' from dual

union all

select 'Total Recall', 'Flashback Data Archive' from dual

)

select t.o "Option/Management Pack",

t.u "Used",

d.DBID "DBID",

d.name "DB Name",

i.version "DB Version",

i.host_name "Host Name",

to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') "ReportGen Time"

from

(select OPTIONS o, DECODE(sum(num),0,'NO','YES') u

from

(

select f.OPTIONS OPTIONS, case

when f_stat.name is null then 0

when ( ( f_stat.currently_used = 'TRUE' and

f_stat.detected_usages > &v_usag and

(sysdate - f_stat.last_usage_date) < &v_days and

f_stat.total_samples > 0

)

(f_stat.detected_usages > &v_usag and

(sysdate - f_stat.last_usage_date) < &v_days and

f_stat.total_samples > 0)

) and

( f_stat.name not in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)')

(f_stat.name in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)') and

f_stat.feature_info is not null and trim(substr(to_char(feature_info), instr(to_char(feature_info), 'compression used: ',1,1) + 18, 2)) != '0')

)

then 1

else 0

end num

from features f,

sys.dba_feature_usage_statistics f_stat

where f.name = f_stat.name(+)

) group by options) t,

v$instance i,

v$database d

where t.u = 'YES'

order by 2 desc,1

;

exit

- the shell

$ cat report_oracle_lic.ksh

#!/bin/ksh

# +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

# All environments

# Licensing

# launched: cron

# unix user: oracle

# arg 1 : number of past days

# arg 2 : detected_usages > arg2

# +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

export ORACLE_HOME=/oracle/10GDB

export PATH=/oracle/10GDB/bin:/oracle/10GDB/OPatch:/oracle/10GDB/OPatch:/usr/linux/bin:/usr/java14/bin:/usr/sbin:/sbin:/usr/bin:/bin:/usr/ucb:/usr/local/bin:/commun:/usr/vac/bin:/usr/vacpp/bin:.

export LIBPATH=/usr/lib:/usr/vac/lib:/oracle/10GDB/lib:/oracle/10GDB/network/lib

# +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

# 1. init

# +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

# test args

if [ $# -lt 2 ] ; then echo "Missing arguments" echo "$0 window_days nber_of_detected_usages" ; return 1 ; fi

Cmd=option_usage

ina_svc_psw=psw

export REP_SCRIPTS=/oracle/dba/bao/compte_licence

log_dir=${REP_SCRIPTS}/log

if [ ! -d ${log_dir} ] ; then mkdir -p ${log_dir} ; fi

lic_gen=${log_dir}/${Cmd}_$(date '+%d%m%y_%H%M').html

msg_sub="[Oracle] $(date '+%d/%m/%y') Licensing Option/Pack $1 past days ; $2+ usage"

db_1="MY INSTANCE LIST

dest_list="my_mail@dom.fr"

EMAIL_HEAD="To: ${dest_list} \nFrom: \"DBA MONITORING \"\nSubject: ${msg_sub} \nMIME-Version: 1.0\nContent-Type: Multipart/Mixed; boundary=Message-Boundary-$boundary\n\n--Message-Boundary-$boundary\nContent-Type: text/html; charset=us-ascii\nContent-Transfer-Encoding: 7bit \n\n";

echo $EMAIL_HEAD>${lic_gen}

print "\n** ${msg_sub} **\n"

# +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

# 1. Licensing report

# +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

# 1.1 formatage entete html

for BDc in ${db_1} ; do

print "** ${BDc} **"

sqlplus -s -MARKUP "HTML ON" ina_svc/${ina_svc_psw}@${BDc} @${REP_SCRIPTS}/${Cmd}.sql $1 $2 | sed -e "/<br>/d" -e 's/td {font:10pt/td {text-align:center;font:10pt/g' -e 's/font:10pt/font:9pt/g' | egrep -iv "USER|Session|^old|^new|procedure|rows|selected" >> ${lic_gen}

if (( $? != 0 )); then return 1 ; fi

done

# +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

# 2. Mail

# +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

sendmail -t ${dest_list} < ${lic_gen}

print "\n${NOMSCRIPT} ** end of script **\n"

return 0

- get the Option used at least Once ( 0 ) from the last year ( 365 Days )

ora10 sr-adm-1:/oracle/dba/bao/compte_licence > ./report_oracle_lic.ksh 365 0

** [Oracle] 08/06/15 Licensing Option/Pack 365 past days ; 0+ usage **

** DB1 **

…

** end of script **

Option/Management Pack	Used	DBID	DB Name	DB Version	Host Name	ReportGen Time
Advanced Compression	YES	1645003607	DB1	11.2.0.3.0	Srv1	08/06/2015 11:32:27
Diagnostic Pack	YES	1645003607	DB1	11.2.0.3.0	Srv1	08/06/2015 11:32:27
Tuning Pack	YES	1645003607	DB1	11.2.0.3.0	Srv1	08/06/2015 11:32:27

Option/Management Pack	Used	DBID	DB Name	DB Version	Host Name	ReportGen Time
Advanced Security	YES	2299686464	DB2	10.2.0.5.0	Srv2	08/06/2015 11:32
Partitioning	YES	2299686464	DB2	10.2.0.5.0	Srv2	08/06/2015 11:32

Here, I wonder why the Advanced Security Option was used, because the Last Usage date was a long time ago … The Currently Used = ‘YES’ field has an explanation :

SQL> select name, DETECTED_USAGES, description from DBA_FEATURE_USAGE_STATISTICS where name = 'Advanced Security'

NAME DETECTED_USAGES DESCRIPTION

--------------------------------------------------------------------------------

Advanced Security 92 External Global users are configured.

SQL> select USG_DET_LOGIC from SYS.WRI$_DBU_FEATURE_METADATA where name = 'Advanced Security';

USG_DET_LOGIC

--------------------------------------------------------------------------------

select count(*), NULL, NULL from dba_users where password in ('GLOBAL', 'EXTERNA

SQL> select count(*), NULL, NULL from dba_users where password in ('GLOBAL', 'EXTERNAL') ;

COUNT(*) N N

---------- - -

OraCsdP

Categories

vendredi 12 avril 2013

Oracle Version : Upgrade / Downgrade & Pay for Them !