Apps Patching error; FGA enabled ORA-28110

In this post I am not specifying any details about FGA (Fine Grained Auditing) - but some quick steps to resolve the issue

During one of my recent apps patching I ran into the following issue.

SELECT ROWNUM FROM PO.PO_ACCRUAL_RECONCILE_TEMP_ALL WHERE ROWNUM=1

Due to error:
ORA-28110: policy function or package APPS.CST_POLICY_SECURITY has error

As usual searched metalink and googled - except saying that disable FGA and continue patching but no detailed steps.

Detailed Steps

1) SQL to find details
select OBJECT_OWNER, OBJECT_NAME, POLICY_GROUP, POLICY_NAME
from dba_policies where object_name='PO_ACCRUAL_RECONCILE_TEMP_ALL'

2) Disable FGA for that object
SQL> exec dbms_rls.disable_grouped_policy('OBJECT_OWNER', 'OBJECT_NAME', 'POLICY_GROUP', 'POLICY_NAME');

SQL> exec dbms_rls.disable_grouped_policy('PO', 'PO_ACCRUAL_RECONCILE_TEMP_ALL', 'SYS_DEFAULT', 'CST_POLICY_SECURITY');
or login as PO user and execute statement
SQL> exec dbms_rls.disable_grouped_policy(NULL, 'PO_ACCRUAL_RECONCILE_TEMP_ALL', 'SYS_DEFAULT', 'CST_POLICY_SECURITY');

then restart the worker and after patching is done

3) Enable FGA for the above object
SQL> exec dbms_rls.enable_grouped_policy('OBJECT_OWNER', 'OBJECT_NAME', 'POLICY_GROUP', 'POLICY_NAME');

SQL> exec dbms_rls.enable_grouped_policy('PO', 'PO_ACCRUAL_RECONCILE_TEMP_ALL', 'SYS_DEFAULT', 'CST_POLICY_SECURITY');
or login as PO user and execute
SQL> exec dbms_rls.enable_grouped_policy(NULL, 'PO_ACCRUAL_RECONCILE_TEMP_ALL', 'SYS_DEFAULT', 'CST_POLICY_SECURITY');



Hope this helps someone for quick steps

Good Luck

From 10g Clone DB Home - Never Copy and relink Oracle Binaries.

Prior to 10g - we always used to just copy oracle binaries from source to target (PROD to DEV) and relink. But with 10g, there are lot of files and places where the information is hard-coded.

So use the following way to Clone Oracle Home - And never do copy and relink, its not supported/recommended. The same applied even to 11g.

You can get more information in Oracele Installation Document - under Installer.
This method is also useful if the Oracle home that you are cloning patches applied to it. When you clone this Oracle home, the new Oracle home will have the patch updates as well.


1) Copy Oracle Home from Source to Target
(cd /u01/app/oracle/10.2//db_1 ;tar -cf - .) | ( cd /u01/app/oracle/10.2//db_1; tar -xvfp - ) >> /tmp/prod_dev_dbcopy1.log 2>&1 &

2) Get soft link list on Source

cd /u01/app/oracle/10.2//db_1
find . -type l -exec ls -la {} \; | grep eb_rda0_p| awk '{print " ln -s " $11 " " $9 }' > /tmp/createlink.sh
find . -type l -exec ls -la {} \; | grep eb_rda0_p| awk '{print " rm " $9 }'> /tmp/removelink.sh

3) Remove Softlink on Target
sh /tmp/removelink.sh

4) Recreate Softlinks on Target
edit the temp file with correct path for new oracle home
sh /tmp/createlink.sh

5) Backup oraInventory on Target Server
to get Inventory location do "cat /var/opt/oracle/oraInst.loc"

6) Detach/Remove Existing Oracle Home in OraInventory.
cd $ORACLE_HOME/oui/bin
runInstaller -silent -detachHome ORACLE_HOME="/u01/app/oracle/10.2//db_1" ORACLE_HOME_NAME="OraDb10g"

7) Edit $ORACLE_HOME/clone/config/cs.properties
add "-ignoreSysPrereqs" at the end

8) Set the following Environment variables.
export PATH=$ORACLE_HOME/perl/bin:$PATH:.
export PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3:$ORACLE_HOME/appsutil/perl

9) run Perl clone script
cd $ORACLE_HOME/clone/bin
perl clone.pl ORACLE_HOME="/u01/app/oracle/10.2//db_1" ORACLE_HOME_NAME="OraDb10g"

if for any case you have issues with perl - then you can run the following also
$ORACLE_HOME/oui/bin/runInstaller -silent -clone ORACLE_HOME="/u01/app/oracle/10.2//db_1" ORACLE_HOME_NAME="OraDb10g"


Log File

$perl clone.pl ORACLE_HOME="/u01/app/oracle/10.2//db_1" ORACLE_HOME_NAME="OraDb10g"
./runInstaller -silent -clone -waitForCompletion "ORACLE_HOME=/u01/app/oracle/10.2//db_1" "ORACLE_HOME_NAME=OraDb10g" -noConfig -nowait -ignoreSysPrereqs

Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

>>> Ignoring required pre-requisite failures. Continuing...

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2009-01-23_11- 47-03PM. Please wait ...Oracle Universal Installer, Version 10.2.0.3.0 Production
Copyright (C) 1999, 2006, Oracle. All rights reserved.

You can find a log of this install session at:
/u01/app/oracle/10.2/ oraInventory/logs/cloneActions2009-01-23_11-47-03PM.log
.................................................................................................... 100% Done.

Installation in progress (Fri Jan 23 23:48:22 EST 2009)
.............................. .................................................. 80% Done.
Install successful

Linking in progress (Fri Jan 23 23:48:58 EST 2009)
Link successful

Setup in progress (Sat Jan 24 00:12:00 EST 2009)
Setup successful

End of install phases.(Sat Jan 24 00:12:26 EST 2009)
WARNING:
The following configuration scripts need to be executed as the "root" user.
#!/bin/sh
#Root script to run
/u01/app/oracle/10.2//db_ 1/root.sh
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts

The cloning of OraDb10g_q592 was successful.
Please check '/u01/app/oracle/10.2/ oraInventory/logs/cloneActions2009-01-23_11-47-03PM.log' for more details.

Any Comments, let me know

Thanks