Database Vault Installation fails with TNS-12541: TNS:no listener

Problem Description
Listener is protected by a password. Now during Oracle Database Vault installation it fails with following errors:

[Thread-35] [ 2012-02-08 08:20:48.274 CEST ] [SQLEngine.setSpool:1946] Setting Spool = /lfs/oracle/cfgtoollogs/dbca/oravault/datavault.log
[Thread-35] [ 2012-02-08 08:20:48.275 CEST ] [SQLEngine.setSpool:1947] Is spool appendable? --> true
[Thread-35] [ 2012-02-08 08:20:48.275 CEST ] [DataVaultOptionStep.executeImpl:138] calling catmac dvownername :DBVOWNER
[Thread-35] [ 2012-02-08 08:20:48.275 CEST ] [DataVaultOptionStep.executeImpl:139] calling catmac getDVAcctMgrName() :DVCACCTMGR
[Thread-35] [ 2012-02-08 08:21:19.512 CEST ] [DataVaultOptionStep.executeImpl:161] Completed catmac. Loading NLS Data using DV API
[Thread-35] [ 2012-02-08 08:21:19.513 CEST ] [NetworkUtils.checkListenerStatus:488] Checking default listener status 
[Thread-35] [ 2012-02-08 08:21:19.513 CEST ] [NetworkUtils.checkListenerStatus:493] Checking listener status: LISTENER
[Thread-35] [ 2012-02-08 08:21:19.548 CEST ] [NetworkUtils.checkListenerStatus:499] lsnrct status output 

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 2012-02-08 08:21:19

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
[Thread-35] [ 2012-02-08 08:21:19.549 CEST ] [DataVaultOptionStep.executeImpl:208] JDBC connect string (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=rac1)))
load(error):java.sql.SQLException: ORA-12541: TNS:no listener

load(error):java.sql.SQLException: ORA-12154: TNS:could not resolve the connect identifier specified

[Thread-35] [ 2012-02-08 08:21:19.963 CEST ] [DataVaultOptionStep.executeImpl:237] Exception while calling NLSTask: null
java.lang.NullPointerException
at oracle.security.datval.dvca.util.DvcaNLSLoader.load(DvcaNLSLoader.java:339)
at oracle.security.datval.dvca.backend.LoadNLSTask.executeTask(LoadNLSTask.java:146)
at oracle.sysman.assistants.util.step.dboption.DataVaultOptionStep.executeImpl(DataVaultOptionStep.java:232)
at oracle.sysman.assistants.util.step.BasicStep.execute(BasicStep.java:210)
at oracle.sysman.assistants.util.step.Step.execute(Step.java:140)
at oracle.sysman.assistants.util.step.StepContext$ModeRunner.run(StepContext.java:2707)
at java.lang.Thread.run(Thread.java:595)
[Thread-35] [ 2012-02-08 08:21:19.964 CEST ] [BasicStep.configureSettings:304] messageHandler being set=oracle.sysman.assistants.util.UIMessageHandler@2af6a882
oracle.sysman.assistants.util.step.StepExecutionException
at oracle.sysman.assistants.util.step.dboption.DataVaultOptionStep.executeImpl(DataVaultOptionStep.java:239)
at oracle.sysman.assistants.util.step.BasicStep.execute(BasicStep.java:210)
at oracle.sysman.assistants.util.step.Step.execute(Step.java:140)
at oracle.sysman.assistants.util.step.StepContext$ModeRunner.run(StepContext.java:2707)
at java.lang.Thread.run(Thread.java:595)
[Thread-35] [ 2012-02-08 08:21:19.964 CEST ] [SQLEngine.done:2167] Done called
[Thread-35] [ 2012-02-08 08:21:19.964 CEST ] [SQLEngine.spoolOff:2013] Setting spool off = /lfs/oracle/cfgtoollogs/dbca/oravault/datavault.log

Cause of the Problem

The Listener was protected by a password.

Solution of the Problem
1) Disable the Listener password

2) Check manually the status of the listener and make sure that it is running

3) Run dbca to configure Database Vault.

ORA-47401: Realm violation for CREATE TABLE

Problem Description
Even SYSTEM user is assign DV_OWNER privilege and we ran EXEC DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('SYSTEM') still data pump export operation from another schema via SYSTEM user fails with ORA-47401 like below.

SQL> ! expdp schemas=idencraft  userid=system/sys

Export: Release 11.2.0.1.0 - Production on Mon Feb 13 13:36:10 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing optio
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_SCHEMA_07"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-47401: Realm violation for CREATE TABLE on SYSTEM.SYS_EXPORT_SCHEMA_07

Cause of the Problem
User system is not added to the Oracle Data Dictionary realm.

Solution of the Problem
Check the existing realm name in the database.

SQL>  select name , audit_options, enabled from  dvsys.dba_dv_realm;

NAME                                                                                       AUDIT_OPTIONS E
------------------------------------------------------------------------------------------ ------------- -
Database Vault Account Management                                                                      1 Y
Oracle Data Dictionary                                                                                 1 Y
Oracle Database Vault                                                                                  1 Y
Oracle Enterprise Manager                                                                              1 Y

Assign 'Oracle Data Dictionary' realm to SYSTEM user,

SQL>
BEGIN
DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
realm_name => 'Oracle Data Dictionary',
grantee => 'SYSTEM');
END;
/
PL/SQL procedure successfully completed.

Now do export operation.

SQL>  ! expdp schemas=idencraft  userid=system/sys

Export: Release 11.2.0.1.0 - Production on Mon Feb 13 13:46:45 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing optio
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  schemas=idencraft userid=system/********
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 50.56 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE

How to export/import in Oracle Database Vault Environment

Starting with Oracle Database 11g Release 2 (11.2.0.2) the Oracle Data Pump EXP and IMP utilities cannot be used in an Oracle Database Vault environment. But if you are prior to 11.2.0.2 you can still use exp and imp utilities. For example in 11.2.0.1 you can use exp and imp. From 11.2.0.2 and upwards the only supported tools are expdp and impdp.

Remember the following rules while working with data pump export or import tool in Oracle Database Vault Environment.

1) If your database version is not 11.2g then whenever a user want to export or import objects from a schema that is not protected by a realm then the user can do that with standard data pump privileges. So prior to Oracle database version 11.2g Oracle Database Vault authorization is not needed to export or import data in a schema that has no realm protection.

2) If the user wants to export or import objects from a schema that is protected by a realm and the user is the owner of the same schema then the user should be either participant or owner of the realm which is protecting the schema or the objects of the exported schema.

3) If the user wants to export or import objects protected by a realm from other schemas than his own, then the user must be granted Database Vault specific authorization by using the DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER procedure. Revoking these privileges can be done using the DVSYS.DBMS_MACADM.UNAUTHORIZE_DATAPUMP_USER procedure.

For example, to authorize the Data Pump to SYSTEM user to export and import objects for an entire database:

EXEC DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('SYSTEM');

Optionally, you can restrict SYSTEM activities to a specific schema or even a table, as shown in the following examples:

EXEC DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('SYSTEM', 'HR');

EXEC DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('DP_MGR', 'HR', 'EMPLOYEES');

4) If the user wants to do a full database export or import then, in addition to the authorization granted by the DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER procedure, the user must also have DV_OWNER role.

5) If the user is in release 11.2g and exporting data from another schema, protected or not by a database vault realm, then in addition to the conditions above, the DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER procedure should be used to grant privileges to the exporting user on the realm protecting the SYSMAN schema.

Here are the steps whenever you try to do export/import operation in Oracle Database Vault Environment.

Step 01:
Check whether the user has appropriate data pump privileges like EXP_FULL_DATABASE and IMP_FULL_DATABASE roles. If not grant the same privilege to user. These are only needed if exporting a different schema than his own. I have written a nice document that you can follow in http://arjudba.blogspot.com/2008/09/query-to-check-whether-user-has.html and http://arjudba.blogspot.com/2008/09/minimum-privilege-needed-to-take-data.html.

Step 02:
Grant Database Vault specific authorizations.

SQL> conn dv/Admin123#

//Authorize the user "scott" to export and import objects for an entire database: 
SQL> EXEC DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('SCOTT'); 

//Authorize the user "SCOTT" to export and import a specific schema (HR) or object (HR.EMPLOYEES):
SQL> EXEC DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('SCOTT', 'HR'); 
SQL> EXEC DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('SCOTT', 'HR', 'EMPLOYEES');  

Step 03:
If the user wants to do a full database export then after step 02 , grant DV_OWNER to the user .

GRANT DV_OWNER TO SYSTEM;

Step 04:
Ensure that the Allow Oracle Data Pump Operation rule set has been enabled by querying the DVSYS.DBA_DV_RULE_SET data dictionary view as follows:

SQL> SELECT ENABLED FROM DVSYS.DBA_DV_RULE_SET WHERE RULE_SET_NAME = 'Allow Oracle Data Pump Operation';

E
-
Y

Step 05:

If the objects that this user wants to import are associated with other objects that are protected by a realm, then authorize the user to have access to that realm.
For example, suppose user SYSTEM wants to export the SCOTT schema. However, the SCOTT schema is associated with the SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S table, and the SYSMAN schema is protected by a realm. So, before user SYSTEM can export the SCOTT schema, he must be authorized as follows:

First, ensure that this user is a realm participant. For example:

BEGIN
DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
realm_name => 'SYSMAN Realm',
grantee => 'SYSTEM',
auth_options => DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT);
END;
/

Next, run the following procedure to authorize the user for Oracle Data Pump operations:

EXEC DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('SYSTEM','SYSMAN');

Step 06
I have also noticed even after all above steps still it fails unless we add "Oracle Data Dictionary" realm to the user. For example to add SYSTEM user to the "Oracle Data Dictionary" realm issue the following:

BEGIN
DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
realm_name => 'Oracle Data Dictionary',
grantee => 'SYSTEM');
END;
/