How to remove a non-used option marked as INVALID in dba_registry

If you have invalid components in your database table dba_registry, and those options are not used, you should mark them as removed to avoid misunderstandings.

set lines 200
col comp_name format a50
col version format a20
col status format a20
col parameter format a30
col value format a30
select comp_id, comp_name,version,status
from dba_registry;


COMP_ID                        COMP_NAME                                          VERSION              STATUS
------------------------------ -------------------------------------------------- -------------------- --------------------
RAC                            Oracle Real Application Clusters                   11.2.0.4.0           INVALID

16 rows selected.

Verify with v$option:

select value from v$option where parameter = 'Real Application Clusters';

VALUE
------
FALSE

To remove, execute

 exec dbms_registry.removed('RAC');

Registry is now updated:

COMP_ID                        COMP_NAME                                          VERSION              STATUS
------------------------------ -------------------------------------------------- -------------------- --------------------
RAC                            Oracle Real Application Clusters                   11.2.0.4.0           REMOVED

16 rows selected.

How to fix error ORA-04063: package body "OLAPSYS.CWM2_OLAP_METADATA_REFRESH" has errors when installing OLAP into a 11g database

You are installing OLAP.
You receive the following error at the end of the olap.sql script:

SQL> @?/olap/admin/olap.sql SYSAUX TEMP;

SQL> execute cwm2_olap_metadata_refresh.mr_refresh;
BEGIN cwm2_olap_metadata_refresh.mr_refresh; END;

*
ERROR at line 1:
ORA-04063: package body "OLAPSYS.CWM2_OLAP_METADATA_REFRESH" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"OLAPSYS.CWM2_OLAP_METADATA_REFRESH"
ORA-06512: at line 1

Solution:
Drop the OLAPSYS user and rerun the script:

$ sqlplus '/ as sysdba'
SQL> drop user OLAPSYS cascade
SQL> @?/olap/admin/olap.sql SYSAUX TEMP;
SQL> @?/rdbms/admin/utlrp.sql

Sources: Doc ID 726253.1 "Errors: ORA-04063, ORA-06508, ORA-06512 When Adding OLAP Using the Script 'olap.sql SYSAUX TEMP;'"

How to add a line feed to your spool file

If you want to spool out a file from you sqlplus session, and need a line feed in your string, you can use the function

char(int)

to concatenate a line feed into your string. Integer represents the decimal value of the character you'd like to send to the database.

Example:

set trimspool on
set lines 200
set pages 0
set heading off
set verify off
set echo off
set feedback off
spool rowcount.sql
select 'spool rowcount.out' from dual;
select 'prompt rowcount for ' || owner || '.' || table_name || chr(10)|| 'select count(*) from ' || owner || '.'  || table_name || ';'
from dba_tables
where owner='SCOTT';
select 'exit' from dual;
exit

The chr(10) will return a line feed (may not be apparent in the code below but there is a line feed in there):

SYS@fsprod SQL> select chr(10) from dual;

C
-

The chr(10) corresponds to the hexadecimal value of A, which in turns corresponds to a line feed under the characterset that I happen to use in this database, which is WE8ISO8859P1.


The result is a file which looks like this:

spool rowcount.out
prompt rowcount for SCOTT.EMP
select count(*) from SCOTT.EMP;

prompt rowcount for SCOTT.DEPT
select count(*) from SCOTT.DEPT;

prompt rowcount for SCOTT.BONUS
select count(*) from SCOTT.BONUS;
.
.
.
exit

Sources: ISO code pages

How to check for existence of and installation of Data Mining in the database,

In this short article, I am following the document "How To Manually Install Data Mining In Oracle 11g? (Doc ID 818314.1)"

select * from v$option where PARAMETER = 'Data Mining';

PARAMETER     VALUE
------------  ------------
Data Mining   FALSE

In my case, Data Mining is not installed. Data Mining files need to be installed and the Oracle executable needs to be linked with Data Mining in the Oracle Home.

Oracle states some important facts about Data Mining in 11g:

"As per the overview of changes of Data Mining in 11g , there are some substantial changes with the Data Mining option in 11g"

* Oracle Data Mining 11gRelease 1 (11.1) has a tight integration with Oracle Database. Data Mining metadata and PL/SQL packages have been migrated from DMSYS to SYS. The DMSYS schema no longer exists in Oracle Database 11g Release 1 (11.1) fresh installations.
* If the database has been upgraded from earlier version, then DMSYS schema could be there, in which case the Document Data Mining Adminstration Guide should be followed the relevant steps to migrate data to SYS schema and then drop DMSYS schema.
* There is no longer an 'odm' directory in the ORACLE_HOME, and no entry for Data Mining in the DBA_REGISTRY but there is a value in V$OPTION (as displayed above)

To enable Data Mining:

1. Shut down all oracle processes running out of your Oracle_home (db, listener)
2. relink the oracle executable

chopt enable dm

Writing to /u01/oracle/product/11204/install/enable_dm.log...
/usr/bin/make -f /u01/oracle/product/11204/rdbms/lib/ins_rdbms.mk dm_on ORACLE_HOME=/u01/oracle/product/11204
/usr/bin/make -f /u01/oracle/product/11204/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/oracle/product/11204

3. Start up the database again
4. cd $ORACLE_HOME/rdbms/admin
5. Run the necessary files to finish the installation of data mining:

sqlplus / as sysdba
SQL>spool enable_dm.log
SQL>@catodm.sql
SQL>@dbmsodm.sql
SQL>@prvtodm.plb

6. Confirm that Data Mining is indeed installed:

select * from v$option where PARAMETER = 'Data Mining';

PARAMETER     VALUE
------------  ------------
Data Mining   TRUE

How to identifiy whether or not Oracle Configuration Manager is in use

Change directory to $ORACLE_HOME

# cd $ORACLE_HOME
# ls ccr
bin  config  doc  hosts  inventory  lib  README.pdf  state

If the directory ccr exist, OCM has been installed.

Change directory to ccr/bin:

# cd ccr/bin
# ls -la
drwxr-xr-x 2 oracle dba  4096 Feb 29 16:11 .
drwxr-xr-x 9 oracle dba  4096 Feb 29 16:11 ..
-rwx------ 1 oracle dba 19169 Feb 29 16:11 common
-rw------- 1 oracle dba 47285 Feb 29 16:11 deployPackages
-rwx------ 1 oracle dba  9063 Feb 29 16:11 emocmrsp
-rwx------ 1 oracle dba  5881 Feb 29 16:11 emSnapshotEnv
-rwx------ 1 oracle dba  6116 Feb 29 16:11 lockfile
-rwxr----- 1 oracle dba  1630 Feb 29 16:11 ocmJarUtil
-rw-r--r-- 1 oracle dba  3539 Feb 29 16:11 OCMJarUtil.class
-rwx------ 1 oracle dba 58374 Feb 29 16:11 setupCCR
-rw-r--r-- 1 oracle dba   903 Feb 29 16:11 strip_path.awk

If the file emCCR exists, OCM has been configured.

If not, like in my case above, OCM is installed but not configured for this server.

How to enable and disable Automatic SQL tuning

Enable:

BEGIN
dbms_auto_task_admin.enable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;

Disable:

BEGIN
dbms_auto_task_admin.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;

If desirable, Oracle also lets you enable or disable all automated maintenance tasks for all windows. Do this by calling the ENABLE or DISABLE procedure with no arguments:

EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;

Sources:
dbms_auto_task_admin
Configuring Automated Maintenance Tasks

How to remove dbms_scheduler jobs

To delete an obsolete scheduler job, use the procedure dbms_scheduler.drop_job procedure.
In a recent case, a large number of obsolete dbms_Scheduler jobs were laying around in the database, and needed a cleanup.
Most of them were not enabled, and all stakeholders deemed it safe to remove the jobs.

To loop through the jobs owned by the schema, I used the following simple script:

connect scott/tiger
set serveroutput on
set trimspool on
spool /tmp/drop_mva_scheduler_jobs.log
DECLARE

  CURSOR C1 IS
  SELECT JOB_NAME
  FROM USER_SCHEDULER_JOBS
  WHERE ENABLED='FALSE';

BEGIN
 FOR l_rec IN C1 LOOP
   dbms_output.put_line('Now dropping job: ' || l_rec.job_name);
   dbms_scheduler.drop_job(job_name=>'' || l_rec.job_name || '' );
 END LOOP;
END;
/
exit

Sources: Oracle Documentation