How to Create Readonly User (for specific Schema) in Oracle Database

1) create user bolinf_readonly identified by readonly;

2) select username from dba_users
    where username like '%BOLINF%';

3) grant create session to BOLINF_READONLY

4) grant select any table to BOLINF_READONLY

5) create or replace trigger log_on_after_bolinf
    after logon ON BOLINF_READONLY.SCHEMA
    BEGIN
    EXECUTE IMMEDIATE 'alter session set CURRENT_SCHEMA = BOLINF';
    END;

Enjoy. . . . .
:) 

Login page error: 404 - The url /OA_HTML/AppsLogin not found

This blog speaks about the Login Page Issue on R12.1.1 instance.

Suddenly the login page was throwing an error - 404 /OA_HTML/AppsLogin was not found on the server

The access_log shows no errors. error.log from $LOG_HOME/ora/10.1.3/Apache shows

[Tue Jan 18 11:27:51 2011] [error] [client 123.238.255.152] [ecid: 1295330271:127.0.0.1:10897:0:2,0] File does not exist: /oracle/CLON/inst/apps/CLON_obaserver/portal/OA_HTML/AppsLogin
[Tue Jan 18 11:27:54 2011] [error] [client 123.238.255.152] [ecid: 1295330274:127.0.0.1:10897:0:3,0] File does not exist: /oracle/CLON/inst/apps/CLON_obaserver/portal/OA_HTML/AppsLogin
[Tue Jan 18 11:30:10 2011] [error] [client 123.238.255.152] [ecid: 1295330410:127.0.0.1:10899:0:2,0] File does not exist: /oracle/CLON/inst/apps/CLON_obaserver/portal/OA_HTML/AppsLogin


I recreated the jar files using adadmin

and compiled the jsp using

cd $FND_TOP/patch/115/bin
perl ojspCompile.pl --compile --flush -p 20 -log /tmp/ojspc_error2.log
Nothing worked out.

Fix:
This problem occurred because wsrp_service.wsdl was owned by root but the services were being started by applmgr.

Ensure that the file
$ORA_CONFIG_HOME/10.1.3/j2ee/oacore/application-deployments/oacore/html/server-wsdl/wsrp_service.wsdl

it shows it is owned by root. Somebody started the services of Apps as root user.

Changed the owner of the file to Apps Owner.

Restarted the service.

Oracle Application Object Library (FND): Active Users Data Collection

1. Login to Oracle E-Business Suite
2. Select the responsibility "Oracle Diagnostics Tool"
3. Select application "Oracle Application Object Library" from the "Application" list of values
4. Click the "Advanced" tab
5. Scroll down to group "Data Collection"
6. Select test name "Active Users"
7. Input Parameters (* required)

Responsibility Id (LOV) *

8. Output contains

Product Installation Status and Patchset Level

Parameters

Key Profile Options

Self Service Web Application Users

Forms-Based Products Users

Concurrent Programs Users

References

Find SID from Concurrent Program Request ID

SELECT sid
FROM v$session
WHERE paddr LIKE
(SELECT addr
FROM v$process
WHERE spid =
(SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER(<your request id>)
)
);

ORA-04063: package body "APPS.AP_ACCTG_DATA_FIX_PKG" has errors

Symptoms

After applying GDF patch 11787050:R12.AP.B or any other patch requiring undo accounting, running the script "ap_undo_acctg.sql" throws the below error:

ERROR
-----------------------
ORA-04063: package body "APPS.AP_ACCTG_DATA_FIX_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"APPS.AP_ACCTG_DATA_FIX_PKG"
ORA-06512: at line 38

When trying to compile "APPS.AP_ACCTG_DATA_FIX_PKG", following errors are shown:

AP_ACCTG_DATA_FIX_PKG PLS-00394: wrong number of values in the INTO list of a FETCH statement

Cause

A technical dependency has been introduced between the
files apacpays.pls, apacpayb.pls and apgdfalb.pls with the
bugfix 9716573.

Due to the technical dependency introduced, the AP_ACCTG_DATA_FIX_PKG does not compile when there's a descrepancy between the datafix package and other packages.
Hence results in the package being invalid.

This is explained in the below internal bug:
Internal BUG 11772495 - RCA: REMOVING TECHNICAL DEP BETWEEN APACPAYB.PLS & APGDFALB.PLS

Solution

1. Download and review the readme and pre-requisites for Patch 11772495:R12.AP.B (to fix the package apacpayb.pls) and Patch.12582979:R12.AP.B (to upgrade the version of apgdfalb.pls).

2. Ensure that you have taken a backup of your system before applying
the recommended patch.

3. Apply the patch in a test environment.
4. Confirm the following file versions:
ap patch/115/sql apacpayb.pls 120.12.12010000.28
ap patch/115/sql apacpays.pls 120.6.12010000.10
ap patch/115/sql apgdfalb.pls 120.1.12010000.39
ap patch/115/sql apgdfals.pls 120.1.12010000.13
You can use the commands like the following:
strings -a $XX_TOP/filename |grep '$Header'

5. Check if the AP_ACCTG_DATA_FIX_PKG is now valid and retest running the script.

6. Migrate the solution as appropriate to other environments.

Step by Step Create read only user in oracle

Step to Create Read only User in Oracle

create role read_only; --- Give new Role name

Oracle Auditing is configured to use the Database Audit Trail, type the following command:

grant select on DBA_AUDIT_TRAIL to read_only;

create user gaurav identified by password; -- Create User

Oracle Auditing is configured to use the XML Audit Trail, type the following command:

grant select on V_$XML_AUDIT_TRAIL to gaurav;

grant connect to gaurav;

grant read_only to gaurav;

SQL> spool table.sql

SQL> SELECT 'GRANT SELECT ON APPS.' ||TABLE_NAME || ' TO read_only;' FROM DBA_TABLES WHERE OWNER='APPS';
'GRANTSELECTONAPPS.'||TABLE_NAME||'TO read_only;'
----------------------------------------------------------------------
GRANT SELECT ON APPS.HYA_PERSONAL_VAR TO read_only;
GRANT SELECT ON APPS.HFM_ERRORLOG TO read_only;
GRANT SELECT ON APPS.HSX_DATASOURCES TO read_only;

'GRANTSELECTONAPPS.'||TABLE_NAME||'TO read_only;'
----------------------------------------------------------------------
GRANT SELECT ON APPS.HSV_ACTIVITY_USERS TO read_only;
GRANT SELECT ON APPS.HSV_ACTIVITY_SESSIONS TO read_only;
GRANT SELECT ON APPS.HSV_USERS_ON_SYSTEM TO read_only;
GRANT SELECT ON APPS.HSV_ACTIVITY_KILL_USERS TO read_only;
GRANT SELECT ON APPS.HSV_ACTIVITY_NO_ACCESS TO read_only;

8145 rows selected.

SQL> SELECT 'GRANT SELECT ON APPS.' ||VIEW_NAME || ' TO read_only;' FROM DBA_VIEWS WHERE OWNER='APPS';

no rows selected

SQL> spool off

SQL>@table.sql


Creating Trigger:-

create or replace trigger log_on_after_gaurav
after logon ON gaurav.SCHEMA
BEGIN
EXECUTE IMMEDIATE 'alter session set CURRENT_SCHEMA = APPS';
END;
/

It's Done. . . .

Sample .bash_profile text file edited for Oracle installation

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
unset USERNAME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/8.1.7; export ORACLE_HOME
ORACLE_SID=prod1ora; export ORACLE_SID
PATH=$PATH:/u01/app/oracle/product/8.1.7/bin; export PATH
CLASSPATH=.:/u01/app/oracle/product/8.1.7/jdbc/lib/classes111.zip; export CLASSPATH
LD_LIBRARY_PATH=/u01/app/oracle/product/8.1.7/lib; export LD_LIBRARY_PATH
LD_ASSUME_KERNEL=2.2.5; export LD_ASSUME_KERNEL
umask 022