Archive

Archive for October, 2014

moving oracle software to a new path

October 10, 2014 Leave a comment

Installed oracle software in /u01/app/oracle/product/12.1.0.2 and need to change it to /u01/app/oracle/product/12.1.0.2/db_1

shut down all databases and listeners

Enter the following to back up the database

cd /u01/app/oracle/product/12.1.0.2

tar cf – * | gzip > /u01/app/oracle/product/12.1.0.2/product/archive/DBHome.tar.gz

delete the Oracle home from the Central Inventory, use the following command:

./runInstaller -silent -detachHome ORACLE_HOME=”/u01/app/oracle/product/12.1.0.2″

Delete the Oracle home:

cd /u01/app/oracle/product
rm -rf /product/12.1.0.2

Restore the original Oracle home and update the Central Inventory

mkdir -p /u01/app/oracle/product/12.1.0.2/db_1

gunzip < /product/archive/DBHome.tar.gz | tar xf –

Attach this Oracle home to the Central Inventory:

./runInstaller -silent -attachHome ORACLE_HOME=”/u01/app/oracle/product/12.1.0.2/db_1″

Advertisements
Categories: Oracle

ORA-39181: Only partial table data may be exported due to fine grain access control

October 9, 2014 Leave a comment

when performing a full database export and export the rman catalog schema in 12.1.0.2 i can across the following error


ORA-39181: Only partial table data may be exported due to fine grain access control on "RMANCAT"."BCF"
. . exported "RMANCAT"."BCF" 0 KB 0 rows
ORA-39181: Only partial table data may be exported due to fine grain access control on "RMANCAT"."BP"
. . exported "RMANCAT"."BP" 0 KB 0 rows
ORA-39181: Only partial table data may be exported due to fine grain access control on "RMANCAT"."BS"
. . exported "RMANCAT"."BS" 0 KB 0 rows
ORA-39181: Only partial table data may be exported due to fine grain access control on "RMANCAT"."BSF"
. . exported "RMANCAT"."BSF" 0 KB 0 rows

This error occurs when a unprivileged user tries to export the tables which are enabled with Fine Grain Access Control policies. You can verify these kind of tables using the below query.


select * from dba_policies where
object_owner='RMANCAT' and object_name='BSF';

To resolve this issue

I granted the priviledge to the role DATAPUMP_EXP_FULL_DATABASE and then granted the role to the exporting user


grant EXEMPT ACCESS POLICY to DATAPUMP_EXP_FULL_DATABASE;
grant DATAPUMP_EXP_FULL_DATABASE to OPS$ORACLE

or

you could

Disable the VPD policy

Categories: Oracle