DBA_FEATURE_USAGE_STATISTICS

# Did some research into view DBA_FEATURE_USAGE_STATISTICS. It is based on the following joins.

create or replace view dba_feature_usage_statistics as
select samp.dbid, fu.name, samp.version, detected_usages, total_samples,
decode(to_char(last_usage_date, 'MM/DD/YYYY, HH:MI:SS'),
NULL, 'FALSE',
to_char(last_sample_date, 'MM/DD/YYYY, HH:MI:SS'), 'TRUE',
'FALSE')
currently_used, first_usage_date, last_usage_date, aux_count,
feature_info, last_sample_date, last_sample_period,
sample_interval, mt.description
from wri$_dbu_usage_sample samp, wri$_dbu_feature_usage fu,
wri$_dbu_feature_metadata mt
where
samp.dbid = fu.dbid and
samp.version = fu.version and
fu.name = mt.name and
fu.name not like '_DBFUS_TEST%' and /* filter out test features */
bitand(mt.usg_det_method, 4) != 4 /* filter out disabled features */


Tables are initialised by procedure DBMS_FEATURE_REGISTER_ALLFEAT which calls the DBMS_FEATURE_USAGE package.

E.g

/**********************
* Audit Options
**********************/

declare
DBFUS_AUDIT_STR CONSTANT VARCHAR2(1000) :=
'select count(*), NULL, NULL from audit$ where exists ' ||
'(select 1 from v$parameter where name = ''audit_trail'' and ' ||
'upper(value) != ''FALSE'' and upper(value) != ''NONE'')';
begin
dbms_feature_usage.register_db_feature
('Audit Options',
dbms_feature_usage.DBU_INST_ALWAYS_INSTALLED,
NULL,
dbms_feature_usage.DBU_DETECT_BY_SQL,
DBFUS_AUDIT_STR,
'Audit options in use.');
end;

#Looks like feature usage collection is triggered by procedure DBMS_FEATURE_WCR_CAPTURE based on details in wri$_dbu_usage_sample, so updating that table can trigger collection.

update wri$_dbu_usage_sample set last_sample_date = last_sample_date -7, last_sample_date_num = last_sample_date_num - sample_interval
where dbid=1336168478 and version = '10.2.0.4.0';

An open database is not really open ?

SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size 2155464 bytes
Variable Size 637537336 bytes
Database Buffers 1476395008 bytes
Redo Buffers 21798912 bytes
Database mounted.
Database opened.
SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');
BEGIN dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE'); END;

*
ERROR at line 1:
ORA-01033: ORACLE initialization or shutdown in progress
ORA-06512: at "SYS.DBMS_ISCHED", line 3598
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2881
ORA-06512: at line 1
Process ID: 6652282
Session ID: 1655 Serial number: 5


SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');

PL/SQL procedure successfully completed.

SQL>

ORA-00600: internal error code, arguments: [kcvhvdf_1], [], [], [], [], [], [],

Received the following in 11.1.0.7.2 on AIX 5.3. SR raised ...

Cause was trying to duplicate a RAC DB with one thread down. Workaround was to start the second thread.


SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [kcvhvdf_1], [], [], [], [], [], [],
[], [], [], [], []

Allow 11g to access UTL_SMTP

SELECT owner, name, referenced_name FROM DBA_DEPENDENCIES
WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR')
AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');

begin
dbms_network_acl_admin.create_acl (
acl => 'utl_smtp.xml',
description => 'utl_smtp',
principal => 'ORADBA',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
commit;
end;
/


begin
dbms_network_acl_admin.add_privilege (
acl => 'utl_smtp.xml',
principal => 'USER2',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null);
commit;
end;
/

begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => 'mysmtpserver',
lower_port => 1,
upper_port => 1024);
commit;
end;
/

Oracle 11.1.0.7.2 DB cannot write to 11.1.0.7.1 ASM.

In the case below 11.1.0.7.2 DB could not write to 11.1.0.7.1 ASM.

If you see any of the following errors, check to confirm if DB ORACLE_HOME is at exactly the same patch level as ASM ORACLE_HOME.

SQL> create tablespace asm_test;
create tablespace asm_test
*
ERROR at line 1:
ORA-19510: failed to set size of 12800 blocks for file
"+ASM_ORADATA41/t01dwh/datafile/asm_test.2558.708719699" (block size=8192)
ORA-17505: ksfdrsz:1 Failed to resize file to size 12800 blocks
ORA-15061: ASM operation not supported [41]


SQL> create spfile='+ASM_ORADATA41/t01dwh/spfilet01dwh.ora_1263913779030' FROM pfile='/opt/oracle/product/11.1.0/db_1/dbs/inittl01dwh1.ora';
create spfile='+ASM_ORADATA41/t01dwh/spfilet01dwh.ora_1263913779030' FROM pfile='/opt/oracle/product/11.1.0/db_1/dbs/inittl01dwh1.ora'
*
ERROR at line 1:
ORA-01078: failure in processing system parameters
ORA-17505: ksfdrsz:1 Failed to resize file to size 5 blocks
ORA-15061: ASM operation not supported [41]


SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 4292558848 bytes
Fixed Size 2160600 bytes
Variable Size 1593837608 bytes
Database Buffers 2684354560 bytes
Redo Buffers 12206080 bytes
ORA-00600: internal error code, arguments: [kccgtt_1], [3], [], [], [], [], [], [], [], [], [], []


ORA-00202: control file: '+ASM_ORADATA42/control02.ctl'
ORA-17505: ksfdrsz:1 Failed to resize file to size 2114 blocks
ORA-15061: ASM operation not supported [41]
Control file not expanded from 1982 to 2114 blocks for upgrade:denied by OS
Errors in file /opt/oracle/diag/rdbms/t01dwh/tl01dwh1/trace/tl01dwh1_ora_119470.trc (incident=18204):
ORA-00600: internal error code, arguments: [kccgtt_1], [3], [], [], [], [], [], [], [], [], [], []

ASM diagnostics Report

# From Metalink Note 470211.1

SPOOL ASM_FIRST.HTML
SET MARKUP HTML ON
SET ECHO ON
SET PAGESIZE 200
SELECT * FROM V$ASM_DISKGROUP;
SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
SELECT * FROM V$ASM_OPERATION;
SELECT * FROM V$VERSION;
SHOW PARAMETER ASM
SHOW PARAMETER CLUSTER
SHOW PARAMETER INSTANCE_TYPE
SHOW PARAMETER INSTANCE_NAME
SHOW PARAMETER SPFILE
SPOOL OFF
EXIT

Auditing feature usage in Oracle

select name, detected_usages from dba_feature_usage_statistics where dbid = (select dbid from v$database) and version = '10.2.0.4.0';

select name, highwater from dba_high_water_mark_statistics where dbid = (select dbid from v$database) and version = '10.2.0.4.0';