ORA-06521 errors in my alert log

ORA-06521: PL/SQL: Error mapping function
ORA-06512: at "SYS.OLAPIHISTORYRETENTION", line 1

How do I stop getting these ORA-06521 errors?

Answer:  The Oracle documentation notes this on the ORA-06521 error:

Oracle Error : ORA-06521: PL/SQL: Error mapping function

Cause: An error was detected by PL/SQL trying to map the mentioned function dynamically.

Action: Check the stacked error (if any) for more details.

 A web search suggests that this ORA-06521 on SYS.OLAPIHISTORYRETENTION is caused by missing OLAP (executable) code.

In your case, you have installed the dbms_olap (OLAP/ PL/SQL) package, and when the OLAP PL/SQL attempts to call the executables, they do not exist, and this error is thrown:

ORA-06521: PL/SQL: Error mapping function
ORA-06512: at "SYS.OLAPIHISTORYRETENTION", line 1

First, see if Oracle thinks that the executables are installed by querying the data dictionary:

select * from v$option where parameter like '%OLAP%'

PARAMETER                                        VALUE
------------------------------------------------ ----------------
OLAP Window Functions                            TRUE OLAP                                             TRUE

select comp_name, version, status from dba_registry where comp_name like '%OLAP%'

COMP_NAME                        VERSION                        STATUS
-------------------------------- ------------------------------ -----------
OLAP Analytic Workspace          10.1.0.2.0                     VALID Oracle OLAP API                              10.1.0.2.0                     VALID OLAP Catalog                          10.1.0.2.0                     VALID   [/code]

 Next, check your OLAP directory and ensure that the executables exist, and that they have the proper read/execute permissions for the "oracle" user.

Hector Madrid offers these steps to check that the executables exist:

Scenario 1: OLAP option installed and upgraded, component name not installed:

Enter to your DBCA and:

Select configure database options
Select your target database
Skip the EM configuration in case the checkbox is not greyed, we don't need it now.
At the database components, check the Oracle OLAP option.
After a while, this will have run the required scripts and your database will be OLAP enabled.
Verify with the above queries.
Scenario 2: OLAP options not installed:

Install the OLAP from your media, explicitly select the components and install them.
 Afterwards apply the patch set to upgrade the newly installed components.
Proceed with scenario 1.
Also, check that you have set the proper OLAP initialization parameter, olap_page_pool_size.

To check the permissions on the binaries, go to the location of the executables.  In earlier releases of Oracle, the OLAP executable binaries are located in the $ORACLE_HOME/olap/bin directory.

In Oracle 11g, the binaries for the OLAP option are located in the $ORACLE_HOME/ORDM/PDM/OLAP/Install/11gR1_ALL  directory.  The primary executable is the Oracle OLAP Web Agent executable file, named oowa.exe.




=======================================================================

2nd Method

SQL> startup pfile=initTEST.ora
ORACLE instance started.
Total System Global Area  314572800 bytes
Fixed Size                  2083760 bytes
Variable Size             100664400 bytes
Database Buffers          205520896 bytes
Redo Buffers                6303744 bytes
Database mounted.

ORA-03113: end-of-file on communication channel

The alert log has
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC

QMNC started with pid=19, OS id=1220658

Mon Jan 24 16:56:35 2011

Errors in file /u01/oracle/admin/TEST/udump/test_ora_2367508.trc:

ORA-00604: error occurred at recursive SQL level 1
ORA-12663: Services required by client not available on the server
ORA-36961: Oracle OLAP is not available.
ORA-06512: at “SYS.OLAPIHISTORYRETENTION”, line 1
ORA-06512: at line 15

Mon Jan 24 16:56:35 2011

and the trace file has

Instance name: TEST

Redo thread mounted by this instance: 1

Oracle process number: 14

Unix process pid: 2367508, image: oracle@uatu009 (TNS V1-V3)

*** ACTION NAME:() 2011-01-24 16:56:34.754
*** MODULE NAME:(sqlplus@uatu009 (TNS V1-V3)) 2011-01-24 16:56:34.754
*** SERVICE NAME:(SYS$USERS) 2011-01-24 16:56:34.754
*** SESSION ID:(160.3) 2011-01-24 16:56:34.754

kwqmnich: current time::  8: 56: 34

kwqmnich: instance no 0 check_only flag 1

kwqmnich: initialized job cache structure

Error in executing triggers on database startup

*** 2011-01-24 16:56:35.565

ksedmp: internal or fatal error

ORA-00604: error occurred at recursive SQL level 1
ORA-12663: Services required by client not available on the server
ORA-36961: Oracle OLAP is not available.
ORA-06512: at “SYS.OLAPIHISTORYRETENTION”, line 1
ORA-06512: at line 15


Solution
—————-
query for the triggers that fires when the database startsup and disable them
SQL> SELECT TRIGGER_NAME,STATUS FROM DBA_TRIGGERS WHERE TRIGGERING_EVENT LIKE '%STARTUP%';

TRIGGER_NAME
——————————
AURORA$SERVER$STARTUP
OLAPISTARTUPTRIGGER

SQL> alter trigger AURORA$SERVER$STARTUP disable;
Trigger altered.

SQL> alter trigger OLAPISTARTUPTRIGGER disable;
Trigger altered.

OLAP Trigger may not be needed in this case for us

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area  314572800 bytes
Fixed Size                  2083760 bytes
Variable Size             100664400 bytes
Database Buffers          205520896 bytes
Redo Buffers                6303744 bytes
Database mounted.
Database opened.