Datapump export with error ORA-28112

Today I was implementing datapump export to one of my database where I came across this error below.

. . exported "SYSMAN"."MGMT_TARGET_TYPE_VERSIONS"        54.52 KB     395 rows
. . exported "SYSMAN"."OCS_TARGET_ASSOC_DEFS"            70.78 KB     537 rows
. . exported "SYSMAN"."OCS_TEMPLATES_DEFS"               59.57 KB     316 rows
. . exported "SYSMAN"."AQ$_MGMT_ADMINMSG_BUS_S"          7.820 KB       3 rows
ORA-31693: Table data object "SYSMAN"."MGMT_IP_REPORT_DEF" failed to load/unload and is being skipped due to error:
ORA-28112: failed to execute policy function
. . exported "SYSMAN"."MGMT_TASK_QTABLE"                 19.21 KB      32 rows
. . exported "SYSMAN"."AQ$_MGMT_HOST_PING_QTABLE_S"      7.789 KB       2 rows
ORA-31693: Table data object "SYSMAN"."MGMT_JOB" failed to load/unload and is being skipped due to error:
ORA-28112: failed to execute policy function
. . exported "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_S"         7.781 KB       2 rows
. . exported "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_S"         7.781 KB       2 rows
. . exported "SYSMAN"."BHV_TARGET_ASSOC_DEFS"            11.68 KB      34 rows
. . exported "SYSMAN"."DB_USER_PREFERENCES"              7.671 KB      14 rows


This error happens when we do an export of grid control OMS database.
Running export as SYS or SYSTEM  may not be a problem and other exports may run without error.

User running the export might have the required privileges to run the export such as EXP_FULL_DATABASE, CONNECT, DBA, etc., but still we face the above error.

Users Running Export should have EXEMPT ACCESS POLICY privilege to export all rows as that user is then exempt from VPD policy enforcement.  SYS is always exempted from VPD or Oracle Label Security policy enforcement, regardless of the export mode, application, or utility that is used to extract data from the database.

So the workaround would be to grant the exempt access policy to the user running the export.

SQL> grant exempt access policy to <username>;

Grant succeeded.


Now the export comletes without any error. :-)

SCP without password prompt

Many a times, we are in need of copying files from one server to another for our work to continue. SCP is a powerful unix utility to do file copy in a secure way. Each time when you do an scp, it prompts for the password of the target server's user. This is very good in terms of security, but might be annoying when you do a large set of file copy.

This password prompt would halt your work by some means. For e.g., you might had a script configured to pass files from source server to target server where scp does the file copy. Here if you want to pass the password each and everytime, the automated script is of no mean.

Let's see how to do scp without a password prompt.

This is to do scp without password in scripts.

First step is to create a key pair between the servers.

The syntax to create the key pair is:

$ ssh-keygen -t rsa

In response, you should see:

Generating public/private rsa key pair

Enter file in which to save the key ...

Press Enter to accept this.

In response, you should see:

Enter passphrase (empty for no passphrase):

You don't need a passphrase, so press Enter twice.

In response, you should see:

Your identification has been saved in ...

Your public key has been saved in ...

Note the name and location of the public key just generated. It always ends in .pub.

Copy the public key just generated to all of your remote Linux boxes. You can use scp or FTP or whatever to make the copy. Assuming you're using root--again, see my warning in step 1--the key must be contained in the file /root/.ssh/authorized_keys. Or, if you are logging in as a user, for example, selva, it would be in ~/.ssh/authorized_keys. Notice that the authorized_keys file can contain keys from other PCs. So, if the file already exists and contains text, you need to append the contents of your public key file to what already is there.

$ cat id_rsa.pub >>~/.ssh/authorized_keys

$ chmod 700 ~/.ssh/authorized_keys

With the above procedure completely done, you can copy your files without the prompt for the password.