How to resolve PLS-00201: identifier 'DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION'

During migration of an EBS (E-Business Suite) database, I was left with some packages in the APPS schema that couldn't be compiled.

SQL> alter package apps.ad_parallel_updates_pkg compile body;

Warning: Package Body altered with compilation errors.

SQL> show errors
Errors for PACKAGE BODY APPS.AD_PARALLEL_UPDATES_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
338/8    PL/SQL: Statement ignored
338/8    PLS-00201: identifier 'DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION'
         must be declared

I first tried granting execute on dbms_system directly to the user app - no luck.

Then I found the following blog post

https://blogs.oracle.com/mandalika/entry/resolving_pls_00201_identifier_dbms

Which points to the following solution:

SQL> create public synonym dbms_system for dbms_system;

Synonym created.

SQL> grant execute on dbms_system to apps;

Grant succeeded.

SQL> alter package apps.ad_parallel_updates_pkg compile body;

Package body altered.

SQL>

Cardinality, Selectivity, Projection and Selection - definitions

From Oracle documentation:

Cardinality

The ratio of distinct values to the number of table rows. A column with only two distinct values in a million-row table would have low cardinality

Selectivity

In a query, the measure of how many rows from a row set pass a predicate test, for example, WHERE last_name = 'Smith'. A selectivity of 0.0 means no rows, whereas a value of 1.0 means all rows. A predicate becomes more selective as the value approaches 0.0 and less selective (or more unselective) as the value approaches 1.0.

Projection

The ability of a SELECT statement to choose a *subset* of the columns in a given table.
In other words, this corresponds to the column listing in the SELECT statement.

Selection

The ability of a SELECT statement to choose a subset of rows in a given table.
In other words, selection corresponds to the WHERE clause of the statement.

Preparations to make before running dbms_redefinition on a large table

To avoid ORA-01555 "snapshot too old", you should take the following precaution before starting an online redefinition of a large table:

1. If possible, create a new BIGFILE undo tablespace:

CREATE BIGFILE UNDO TABLESPACE UNDOTBS2 DATAFILE
  '/u02/oradata/proddb01/undotbs02_01.dbf' SIZE 128M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
RETENTION NOGUARANTEE;

2. Make the new UNDO tablespace database default:

ALTER SYSTEM SET undo_tablespace='UNDOTBS2' SCOPE=BOTH;

3. Alter the retention of UNDO data to GUARANTEED retention:

ALTER TABLESPACE UNDOTBS2 RETENTION GUARANTEE;

4. If possible, create a new BIGFILE temporary tablespace:

CREATE BIGFILE TEMPORARY TABLESPACE TEMP2 TEMPFILE
'/u02/oradata/proddb01/temp2_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

5. Make the new temporary tablespace the database default:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP2";

6. Empty the recyclebin. As sysdba:

PURGE RECYCLEBIN;

7. Adjust the parameter undo_retention
Set the parameter high enough to sustain operation of the database for the amount of time you expect the redefintion to last.
For example, if you expect the redefinition to run for 48 hours, set the undo_retention to 172800 seconds:

alter system set undo_retention = 172800 scope=memory;

When the online redefinition has completed, return the UNDO tablespace to its normal retention:

ALTER TABLESPACE UNDOTBS2 RETENTION NOGUARANTEE;

About RETENTION GUARANTEED:

... specifies that Oracle Database should preserve unexpired undo data in all undo segments of tablespace even if doing so forces the failure of ongoing operations that need undo space in those segments. This setting is useful if you need to issue an Oracle Flashback Query or an Oracle Flashback Transaction Query to diagnose and correct a problem with the data.

About RETENTION NOGUARANTEE:

... returns the undo behavior to normal. Space occupied by unexpired undo data in undo segments can be consumed if necessary by ongoing transactions. This is the default

Source: Oracle Documentation

More about undo retention can be found here

Setting the minimum undo retention period

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 manually register the database with the listener

Sometimes there may be a delay before pmon register itself with the listener.

lsnrctl status will show

The listener supports no services
The command completed successfully

To manually force pmon to register, execute

alter system register;

How to handle RMAN DUPLICATE: Errors in krbm_getDupCopy during duplicate

I recently cloned a 11.2.0.4 database using tape backups as source.

The clone failed right before "open resetlogs". In other words, the database was restored and recovered, but DUPLICATE didn't succeed in opening it. So I did it manually, by recreating the controlfile using a template from the source database, and adjusted for the file layout on the auxiliary. The database opened nicely.

However, later during the day, the application team found that a new clone was needed due to some synch issue caused by a third-party replication tool. In other words, the entire database had to be copied from the latest version of our incremental level 1 backups and restored all over again.

No problem.

As usual, I started by saving away the spfile, password file and the block change tracking file located in $ORACLE_HOME/dbs. Following that, I mounted and dropped the database. All the files were automatically removed from disk by Oracle, and the instance terminated, as expected.

As I started to clone, I noticed some new messages in the alert log of the auxiliary database:

RMAN DUPLICATE: Errors in krbm_getDupCopy
Errors in file /u01/oracle/product/11204/admin/testdb01/diag/rdbms/testdb01/testdb01/trace/testdb01_ora_22544486.trc:
ORA-19625: error identifying file /u02/oradata/testdb01/system01.dbf
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

This error was repeated for every data file in the database and appended to the same trace file as in the message above.

I found the errors worth checking.

Fortunately, these errors can be ignored. MOS Doc ID 1476641.1 "RMAN DUPLICATE: Errors In Krbm_getDupCopy found in alert.log" explains

"This happens after a previous failed duplicate trial, if the files copied to auxiliary destination have been deleted."

and


"The messages in alert.log indicate that rman cannot use a previous datafile copy for those files and that the files must be copied again to auxiliary destination. Duplicate checks if there are datafile copies already created by a duplicate to avoid restoring the files again.

So this is a part of Oracle restore optimization concept:

"...from 11.2 RMAN always checks if the file copy exists at destination hosts to avoid copying it again"


Great, but how does RMAN do that?

"If duplicate fails between the first restore/copy and the zeroing of the dbid, a second duplicate will find the _rm_dup_@.dat file and the clone default channel will read it into memory, once determined the name of the datafilecopy to be created by the second duplicate it is compared with the existing datafilecopy from the previous duplicate. If the datafilecopy still exists and matches the vital information of the datafile (file number, database id, creation scn, database name) and its checkpoint scn is behind the until scn then the datafilecopy can be used by this new duplicate and restore/copy isnot necessary"


The solution is simple:

"As files have already been deleted from auxiliary destination, ignore those messages."

I could also have followed the following advice:

"If you don't want to see those messages in alert.log but datafiles have already been deleted, on Auxiliary host, delete the file $ORACLE_HOME/dbs/_rm_dup_.dat where dup_db is the name of the clone instance."