How to remove the schema OWF_MGR from a database

Applicable for Oracle version 12.1.0.2.0.

Workflow manager is a component used for Orchestrating of Oracle Warehouse Builder (OWB).
If you have a schema called OWF_MGR dangling in your database, it can be removed. I did it the following way:

Find the number of grants per user from OWF_MGR:

SELECT UNIQUE GRANTEE,COUNT(*)
FROM DBA_TAB_PRIVS
WHERE OWNER='OWF_MGR'
GROUP BY GRANTEE;

This query gave me to grantees: PUBLIC and the role WF_PLSQL_UI.

Revoke the privileges granted. Generate the revoke statements:

select 'revoke execute on '|| owner ||'.' || table_name || ' from WF_PLSQL_UI;' 
from  dba_tab_privs
where owner='OWF_MGR'
AND grantee='WF_PLSQL_UI';

Generate the same statements for PUBLIC.

Drop the role:

drop role WF_PLSQL_UI;

Finally, drop the user with the cascade option:

drop user owf_mgr cascade;

I found this information here

How to install APEX in an existing database

I have based my receipe on Tim Hall's instructions found at www.oracle-base.com, and I do not want to take credit for this. But the installation steps was executed by myself, and my notes may come in handy for others.

In my case, a developer needed access to the APEX_JSON package, and as confirmed with Oracle Development, the package is not available outside the APEX software stack. However, when APEX is installed in an existing database, you can start using the functionality in this package even if you don't need anything else that comes with APEX.

Here is how I performed the minimal installation:


1. Create a dedicated tablespace:

create bigfile tablespace apex datafile '/u02/oradata/testdb01/apex.dbf' size 128M autoextend on next 32M maxsize unlimited;

2. Download the latest APEX installation bundle from http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html#close, and upload it to your server

3. Unzip the file

4. change directory to the apex folder

5. Install the software.
The header of the apexins.sql file states its usage:

Arguments:
Position 1: Name of tablespace for Application Express application user
Position 2: Name of tablespace for Application Express files user
Position 3: Name of temporary tablespace or tablespace group
Position 4: Virtual directory for APEX images

sqlplus / as sysdba @apexins.sql APEX APEX TEMP /i/

6. Set the admin password:

sqlplus / as sysdba @apxchpwd.sql

7. Create the APEX_LISTENER and schema APEX_REST_PUBLIC_USER:

sqlplus / as sysdba @apex_rest_config.sql

There are additional, optional steps to be configured, depending on your needs. See this article at Oracle-base.com

How to check if Database Vault is enabled or disabled

To check whether or not the Database Vault option is enabled in your database, run the following query as a privileged user:

SELECT parameter, value
FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

The true/false value of the parameter will indicate whether or not it is enabled.

Source: Oracle Documentation>

How to remove the APEX option from the database

cd $ORACLE_HOME/apex
sqlplus / as sysdba
@apxremov.sql
drop public synonym htmldb_system;
drop PACKAGE HTMLDB_SYSTEM;

If you have older APEX installations, they may be left in the database, but not registred in the dba_registry.

I had an old APEX schema called APEX_030200, with several invalid objects:

OWNER	OBJECT_TYPE	COUNT(*)
PUBLIC	SYNONYM	3
APEX_030200	PACKAGE	2
APEX_030200	PACKAGE BODY	114
APEX_030200	PROCEDURE	3

This means that the script above won't work. You will get this output when attempting to remove the installation:

sqlplus / as sysdba @apxremov.sql
...
Error:
You can only use this script to remove Application Express

I found some useful information about these situations at this blog

Basically, you can simply drop the old schema directly.

Check first:

SELECT username, 'drop user ' || username || ' cascade;' AS remove_statement
  FROM dba_users
 WHERE     (username LIKE 'FLOWS_%' OR username LIKE 'APEX_%')
       AND username NOT IN ('FLOWS_FILES',
                            'APEX_PUBLIC_USER',
                            'APEX_LISTENER',
                            'APEX_REST_PUBLIC_USER',
                            'APEX_INSTANCE_ADMIN_USER')
       AND username NOT IN (SELECT schema s
                              FROM dba_registry
                             WHERE comp_id = 'APEX');

So cleaning up can be done as easily as this:

drop user APEX_030200 cascade;

How to handle message "Expected XDB Resources are not there" when OLAP API is invalid after an upgrade

Background:
After an upgrade, you have invalid components in you registry.
To fix the OLAP components you have followed "Oracle Olap API Invalid After Adding or Upgrading OLAP (Doc ID 466363.1)" and created the

xoq_validate_verbose

procedure in the SYS schema.

You run it with the following output:

exec xoq_validate_verbose
compatible:11.2.0.4.0 ok:True
No install errors were found so component remains valid. ok:True
expected XDB resources are not there. ok:False

PL/SQL procedure successfully completed. 

Now, reload the OLAP API:

sqlplus / as sysdba@$ORACLE_HOME/olap/admin/xoqrelod.sql 

If the output from the above script shows that the index XDB.XDB$ACL_XIDX is disabled, make sure to enable it first:

alter index xdb.XDB$ACL_XIDX enable;

Then rerun the xoqrelod.sql script.


Check the registry, OLAP API should now be valid:

select comp_id, comp_name, version,status 
from dba_registry
where comp_id='XOQ'
/

COMP_ID              COMP_NAME                                VERSION                        STATUS
-------------------- ---------------------------------------- ------------------------------ -----------
XOQ                  Oracle OLAP API                          11.2.0.4.0                     VALID

What are the two options XDK and XML in DBA_REGISTRY anyway?

What are the two options XDK and XML in DBA_REGISTRY anyway?

You see them amongst other components in the DBA_REGISTRY view:

select comp_id,comp_name, version,status from dba_registry;

COMP_ID       COMP_NAME          VERSION        STATUS
-------------------- ---------------------------------------- ------------------------------ --------------------
XDB       Oracle XML Database        12.1.0.2.0       VALID
XML       Oracle XDK          12.1.0.2.0       VALID

So what are they, and how are they connected?

The XDK documentation states:

"Oracle XML Developer's Kit (XDK) is a versatile set of components that enables you to build and deploy C, C++, and Java software programs that process Extensible Markup Language (XML)."

In other words, XDK is a toolbox for developers of XML-driven applications.

The XML DB documentation states:
"Oracle XML DB is a set of Oracle Database technologies related to high-performance handling of XML data...Oracle XML DB and the XMLType abstract data type make Oracle Database XML-aware. Storing XML data as an XMLType column or table lets the database perform XML-specific operations on the content."

In other words, XML DB is the framework for allowing XML to be stored and retrieved in the database. It has been a part of the Oracle RDBMS since version 9.2.

The XDK

"supports Oracle XML DB, which is a set of technologies used for storing and processing XML in Oracle Database."

With XDK and XML DB you can

"build applications that run in Oracle Database. You can also use XDK independently of Oracle XML DB.
XDK is fully supported by Oracle and comes with a commercial redistribution license. The standard installation of Oracle Database includes XDK."

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 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 enable OLAP in an Oracle 11gR2 database

First, check that the prerequisites are fulfilled. The components below must be installed and valid before proceeding.

select comp_id,  comp_name, version,status from dba_registry where comp_id in ('XDB','XML','JAVAVM','EXF');

COMP_ID    COMP_NAME                      VERSION    STATUS
---------- ------------------------------ ---------- ----------
XDB        Oracle XML Database            11.2.0.4.0 VALID
EXF        Oracle Expression Filter       11.2.0.4.0 VALID
JAVAVM     JServer JAVA Virtual Machine   11.2.0.4.0 VALID
XML        Oracle XDK                     11.2.0.4.0 VALID

If java virtual machine is not installed:

sqlplus / as sysdba
set echo on
spool JServer.log
@?/javavm/install/initjvm.sql;
@?/xdk/admin/initxml.sql;
@?/xdk/admin/xmlja.sql;
@?/rdbms/admin/catjava.sql;
spool off

If XML DB is not yet install, you need to follow "Master Note for Oracle XML Database (XDB) Install / Deinstall (Doc ID 1292089.1)" from Oracle Support


If Oracle Expression Filter is not installed:

sqlplus / as sysdba
@?/rdbms/admin/catexf.sql

1. Start by shutting down the database:

sqlplus / as sysdba
shutdown immediate

2. Prepare your ORACLE_HOME by relinking the "oracle" executable.

For Oracle 11R2 and above, use the chopt utility. It was provided to help DBAs add new options and implicitly relink the binaries.

chopt enable olap

Writing to /u01/oracle/product/11204/install/enable_olap.log...
/usr/bin/make -f /u01/oracle/product/11204/rdbms/lib/ins_rdbms.mk olap_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

For versions prior to 11gR2:

cd $ORACLE_HOME/rdbms/lib
  make -f ins_rdbms.mk olap_on
  make -f ins_rdbms.mk ioracle

3. Open your database:

sqlplus / as sysdba
startup

4. Set up OLAP

cd $ORACLE_HOME/olap/admin
sqlplus / as sysdba
spool install_olap.log
@olap.sql SYSAUX TEMP

5. Verify that OLAP was installed:

select * from v$option where parameter='OLAP';

PARAMETER                      VALUE
------------------------------ ------------------------------
OLAP                           TRUE

How to check if the OLAP option is installed in the database

select comp_name, version, status from dba_registry  where comp_name like '%OLAP%' order by 1 DESC;

Typical output if OLAP is installed:

COMP_NAME	VERSION	STATUS
Oracle OLAP API	11.2.0.4.0	VALID
OLAP Catalog	11.2.0.4.0	VALID
OLAP Analytic Workspace	11.2.0.4.0	VALID

You can also use:

select * from v$option where parameter='OLAP';

PARAMETER	VALUE
OLAP	TRUE

How to install and deinstall Oracle Spatial and Oracle Multimedia

The following advice is an excerpt from a Service Request with Oracle support services, on how to administer Spatial and Multimedia:

To remove Spatial:

 connect / as sysdba 
 drop user MDSYS cascade; 

To remove Multimedia:

 connect / as sysdba 
 @?/rdbms/admin/catcmprm.sql ORDIM 

You can reinstall both of these components later if needed by running the following scripts. Note, Multimedia must be installed first.

To Install Multimedia:

 connect / as sysdba 
 @?/ord/admin/ordinst.sql SYSAUX SYSAUX 
 @?/ord/im/admin/catim.sql 
 execute sys.validate_ordim; 

To install Spatial:

 connect / as sysdba 
 @?/md/admin/mdinst.sql 

How to check if spatial and multimedia is installed in the database

The following advice was given to me by Oracle Support services, to determine if either of these components are being used in the database:

 connect / as sysdba 

 set pagesize 10000 
 col owner format a12 
 col table_name format a35 
 col column_name format a25 
 col index_name format a25 

 -- Is Spatial being used? 

 select owner, index_name 
 from dba_indexes 
 where ityp_name = 'SPATIAL_INDEX'; 

 select owner, table_name, column_name 
 from dba_tab_columns 
 where data_type= 'SDO_GEOMETRY' 
 and owner != 'MDSYS'; 

If both of these queries return no rows, Spatial is not being used.

 -- Is Multimedia being used? 

 set serveroutput on; 
 @?/ord/im/admin/imremchk.sql 

If you're not using this component, you will get the message returned "Oracle Multimedia is not being used".

How to install Oracle Text

Based on "Manual Installation, Deinstallation and Verification of Oracle Text 11gR2 (Doc ID 970473.1)" from My Oracle Support

1. Install the CTXSYS schema:

SQL> connect SYS/password as SYSDBA
SQL> spool text_install.txt
SQL> @?/ctx/admin/catctx.sql change_on_install SYSAUX TEMP NOLOCK

2. Install the language-specific default preferences.
There is script which creates language-specific default preferences for every language Oracle Text supports in $O_H/ctx/admin/defaults Directory

Grep for your language, and you'll find your script easily:

myserver>grep Norwegian *
drdefn.sql:Rem      default preference for Norwegian

Execute it as follows:

SQL> connect "CTXSYS"/"change_on_install"
SQL> @?/ctx/admin/defaults/drdefn.sql "NORWEGIAN";
SQL> connect SYS/password as SYSDBA
SQL> alter user ctxsys account lock password expire;
SQL> spool off

3. Verify your installation by running:

connect SYS/password as SYSDBA

set pages 1000
col object_name format a40
col object_type format a20
col comp_name format a30
column library_name format a8
column file_spec format a60 wrap
spool text_install_verification.log

-- check on setup
select comp_name, status, substr(version,1,10) as version from dba_registry where comp_id = 'CONTEXT';
select * from ctxsys.ctx_version;
select substr(ctxsys.dri_version,1,10) VER_CODE from dual;

select count(*) from dba_objects where owner='CTXSYS';

-- Get a summary count
select object_type, count(*) from dba_objects where owner='CTXSYS' group by object_type;

-- Any invalid objects
select object_name, object_type, status from dba_objects where owner='CTXSYS' and status != 'VALID' order by object_name;

spool off

Valid output depends on your Oracle version, but for 11.2.0.3 it is:

COMP_NAME                      STATUS                                       VERSION
------------------------------ -------------------------------------------- ----------------------------------------
Oracle Text                    VALID                                        11.2.0.3.0
 

VER_DICT                                 VER_CODE
---------------------------------------- ----------------------------------------
11.2.0.3.0                               11.2.0.3.0
 

VER_CODE
----------------------------------------
11.2.0.3.0


  COUNT(*)
----------
       388


OBJECT_TYPE            COUNT(*)
-------------------- ----------
INDEX                        63
TYPE BODY                     6
INDEXTYPE                     4
PROCEDURE                     2
TYPE                         35
TABLE                        50
VIEW                         77
FUNCTION                      2
LIBRARY                       1
PACKAGE BODY                 63
OPERATOR                      6
PACKAGE                      74
LOB                           2
SEQUENCE                      3